Excel gegevens bereiken omvormen tot een tabel is de nieuwe manier om met data om te gaan. De lijst neemt een databank structuur aan en dat werkt stabieler dan een gegevens bereik dat in kolommen staat.
Deze werkwijze biedt je meer dan 10 voordelen!
Voor je het bereik omvormt naar een tabel, moet je volgende voorwaarden nakijken:
Er mogen geen LEGE lijnen in het bereik staan,
Er mogen geen LEGE kolommen in het bereik staan,
ELKE kolom die gebruikt wordt, moet bovenaan een titel hebben,
Alle titels moeten op dezelfde lijn staan,
Er mogen geen ‘samengestelde’ cellen zijn, noch in de titels noch in de data,
Er mogen geen subtotalen of eindtotalen in het bereik staan.
Onderstaande schermafbeelding toont een gegevens bereik dat NIET kan omgevormd worden tot een tabel.
|
|
Wat moeten we allemaal wijzigen vóór we deze gegevens gaan opmaken als tabel? - Tussen de titel (op lijn 1) en de gegevens moet er een lege lijn zijn zodat Excel duidelijk herkent waar de data begint, - Cel D E F in lijn 2 en lijn 17 zijn samengestelde cellen, deze moeten weg, - Lijnen 14, 16 en 27 moeten weg want dat zijn lege lijnen, - Lijn 3 bevat de titel rij, die moet blijven staan, maar lijn 18 moet weg want in een tabel mag er slechts 1 keer een titel rij zijn en die moet bovenaan staan, - Lijnen 15 en 28 moeten ook weg: die bevatten totalen en we gaan deze automatisch laten berekenen. |
Omdat bovenstaand voorbeeld nogal gebruik maakt van randen, tekstkleuren en valuta, is het aan te raden om de opmaak van deze cellen te wissen. Zodra je een bereik opmaakt als tabel ga je namelijk een kleuren schakering kiezen die ‘moderner’ aanvoelt.
NL: Om de opmaak te wissen: Selecteer alle cellen > 
START > Wissen > Opmaak wissen.
UK: Om de opmaak te wissen: Selecteer alle cellen > HOME > Clear > Clear Formats.
Klik ergens in een cel die in het bereik staat en maak er een tabel van.
START > Opmaken als tabel
Kies een kleurschakering: het heeft helemaal geen belang welke kleur je kiest en je kan ze achteraf heel eenvoudig wijzigen. Het is enkel de bedoeling dat de tabel een modern jasje krijgt. Ben je van plan om met ‘Voorwaardelijke opmaak’ te werken, hou deze kleuren dan eenvoudig en gebruik geen kleurschakeringen.
De titels van de kolommen worden automatisch omgezet naar tabel titels met filters.
Indien dit met jouw databank niet het geval is, herlees dan onderdeel 104, “Een bestaand bereik in orde zetten”.
Klik ergens in de tabel en scroll met het muiswiel naar beneden. Je zal merken dat de kolom letters A, B, C, D enzoverder, vervangen worden door de kolomtitels van je tabel. Dit vervangt als het ware de ‘titels blokkeren” die we vroeger gebruikten om titels vast te zetten. Dit werkt niet om kolommen vast te zetten als je naar rechts scrolt: dan moet je ‘titels blokkeren’ blijven gebruiken.
Druk op [Ctrl] + [Home] om naar de allereerste cel van de tabel te gaan.
Druk dan op [Ctrl] + [pijl naar beneden] om naar het eerste lege veld te gaan dat in deze kolom staat (1). Zijn er geen lege cellen in deze kolom, dan springt de cursor naar de laatste cel in deze kolom. Dat is waar we willen zijn om nieuwe gegevens toe te voegen.
Selecteer de cel beneden de onderste cel, begin te typen en druk op de [Tab] om naar de volgende kolom te springen (2). Het gebruik van de [Tab] is heel handig-: je hoeft niet altijd naar de muis te grijpen om te springen naar de volgende kolom. Kom je aan het einde van de lijn, druk dan nog eens op de [Tab] zodat er een nieuwe lijn toegevoegd wordt, de cursor springt naar links in deze nieuwe lijn zodat je het volgende nieuwe gegeven kan intypen.
223. Belangrijk: indien er meerdere legen cellen in deze kolom staan, druk dan telkens op [Ctrl] + [pijl naar beneden] tot je helemaal onderaan de tabel komt te staan.
224. Belangrijk: de [Tab] staat links op je toetsenbord en is herkenbaar aan de twee pijlen die erop staan: een pijl naar links en een pijl naar recht.
Waarschijnlijk het grootste voordeel van tabellen is de mogelijkheid om onderaan totalen per kolom te laten berekenen. Klik in de tabel en het lint ‘Hulpmiddelen voor tabellen’ verschijnt.
Daar kan je aanvinken ‘Totaalrij’.
Onderaan de tabel zal een extra rij ‘Totaal’ verschijnen. Op deze rij, ter hoogte van de kolom kan je berekeningen uitvoeren door op het neerwaarts pijltje te klikken.
Klik op de berekening die je wenst. De meest gebruikte worden opgesomd. Voor getallen kies je gemiddelde, som, maximum (hoogste) of minimum (laagste) getal in de kolom. Bevat de kolom namen of woorden, dan kan je tellen hoeveel gegevens erin staan.
Belangrijk: kies je voor Aantal, dan worden enkel de cellen die ingevuld zijn, geteld.
Heb je de Totaalrij aangezet met kolom berekeningen en filter je bovenaan de gegevens, dan worden de berekeningen herwerkt met de gegevens die het resultaat zijn van de filter. Zo kan je heel snel tabellen doorzoeken door filters te gebruiken en dan de cijfergegevens aflezen die op die data betrekking hebben.
De mogelijke berekeningen tussen ‘Geen’, en ‘Var’, zoals vermeld in Voordeel 5, zullen toegepast worden op de cellen die zichtbaar zijn. M.a.w. als je een filter hebt toegepast, dan zal het AANTAL bij Naam en SOM in de Salaris kolom (zie in bovenstaand voorbeeld) herrekend worden. Heb je in Voordeel 5 echter gekozen voor een berekening die in ‘Meer functies’ staat, dan heeft die berekening betrekking op ALLE gegevens in de tabel en niet enkel op degene die getoond worden.
Hieronder staan schermafdrukken met de resultaten die verschillen:
In beide gevallen is de lijst gefilterd, enkel de mensen die in België werken worden getoond.
Gebruik van SOM op de kolom Salaris berekent een totaal van 105.341, dus enkel voor de salarissen die in lijnen met België staan.
Gebruik van de ‘Meer functies...’ > SOM op de kolom Salaris berekent een totaal van 595.344, dus voor ALLE salarissen binnen de databank en dat is niet correct gezien de filter ‘België’ aanstaat.
In onderstaande tabel hebben we rechts van de kolom ‘Code’ een nieuwe kolom gemaakt. Klik ter hoogte van de titelrij, rechts van de titel ‘Code’ en begin direct de titel te typen. Je zal merken dat de opmaak van deze kolom dezelfde wordt als de rest van de tabel.
Wens je een berekening in deze nieuwe kolom te plaatsen, begin dan met het = (is gelijk aan teken) te typen. Vervolledig nu de berekening.
In onderstaande ‘Bonus’ kolom werden de volgende handelingen uitgevoerd:
Typ = > klik links in de ‘Salaris’ kolom op het getal 6200 > de naam van de kolom verschijnt > typ *10%
Druk op [ENTER] om de volledige kolom binnen deze tabel, te vullen. Dit is nog een voordeel van tabellen: bij het intypen van formules wordt die automatisch gekopieerd naar alle boven- en onderliggende cellen binnen de tabel.
Nu kan je de formule in de ‘Bonus’ kolom lezen als ... “het getal dat in ‘Salaris’ staat, vermenigvuldigen met 10 procent.”
Het is de bedoeling om de landnaam ‘VK’ te vervangen door ‘UK’.
|
Filter de gegevens waarvan je de celinhoud wenst de wijzigen. . |
Typ in de bovenste cel het nieuwe woord ‘UK’, [Enter], keer terug en dubbelklik op de vulgreep om alle onderstaande gegevens te vervangen.
|
Alle ‘VK’ gegevens zijn vervangen door ‘UK.’
|
|
|
Om cellen te selecteren, zouden de meeste Excel gebruikers op de letter D bovenaan de kolom klikken. Ze hebben dan 1.048.576 cellen geselecteerd, terwijl ze eigenlijk enkel de cellen binnen de tabel wensen. Voer je dan een bewerking uit, dan zal deze toegepast worden op al deze cellen. Dit kan problemen geven met het werkgeheugen van Excel en van de tabel, waardoor die traag wordt of zelfs vastloopt. |
|
|
Om enkel de data te selecteren: Klik in een cel binnen de kolom waarvan je alle data wilt selecteren (in het voorbeeld hiernaast werd er op ‘Colin’ geklikt) [Ctrl] + [spatiebalk] Alle data binnen deze kolom, binnen deze tabel is nu geselecteerd. De titel bovenaan en de (eventuele) totalen onderaan deze tabel zijn niet geselecteerd. Voer nu de bewerking uit die je wenst, bijvoorbeeld een ander lettertype. |
Wie regelmatig de functie =Vert.zoeken() gebruikt, die weet dat het bereik waarin gezocht wordt, opgegeven moet worden. In onderstaand voorbeeld is dat bereik $I$4 tot en met $J$7.
Worden er achteraf gegevens toegevoegd aan dit bereik, dan zal deze formule bijgewerkt moeten worden.
Maar als je dit bereik eerst omvormt naar een tabel, dan kan de tabel uitbreiden, zonder de formule bij te werken want =Vert.Zoeken() gaat in Alles zoeken t.t.z. de gehele tabel.
Doen dus! Formateer het bereik als tabel en herwerkt de =Vert.zoeken() formule:
225. Klik op Fx vóór de formule.
226. Klik in het tekstveld naast ‘Tabelmatrix’ en verwijder het bereik die er staat.
227. Selecteer de tabel helemaal dus zowel de titels en ALLE lijnen en kolommen.
228. Naast ‘Tabelmatrix’ staat er nu “Tabel1[#Alles]” (of de tabelnaam die jij gegeven hebt aan deze tabel).
229. Klik OK.
230. 
Vanaf nu kan je lijnen toevoegen aan Tabel1, de =Vert.zoeken() zal deze automatisch allemaal opnemen in zijn zoekgebied.
Maak jij ook nog draaitabellen aan de hand van gegevens bereiken? Lees dan zeker deze tip en bespaar veel tijd en ergernis bij het aanpassen en toevoegen van gegevens in de bron van de draaitabel.
Dankzij het gebruik van een tabel hoef je nooit meer te twijfelen of alle gegevens in de draaitabel zijn opgenomen.
Ga in de tabel staan waarmee je een draaitabel gaat maken.
231. Maak van je gegevens bereik een tabel.
232. Klik in de tabel.
233. Klik INVOEGEN > Draaitabel > tabel1 (of een andere naam) > in een Nieuw werkblad > OK.
234. Maak je draaitabel zoals je wilt.
235. Ga nu eens kijken bij ‘Hulpmiddelen voor draaitabellen’ > Opties > Andere gegevens bron.
236. 
Je ziet terug de tabel gegevens die door een mierenlijn omlijnd zijn. Scroll zeker eens naar beneden om alle lijnen te zien die in de gegevensbron zijn opgenomen.
237. Druk op Annuleer.
238. Ga terug naar de tabel en voeg lijnen toe zoals uitgelegd in tip 105.4.
239. Ga terug naar de draaitabel > in ‘Hulpmiddelen voor draaitabellen’, klik op ‘Verni.’ > ‘Alles vernieuwen’.
240. De draaitabel zal aangepast worden en de nieuwe gegevens zullen bijgerekend worden.
241. Klik terug op ‘Hulpmiddelen voor draaitabellen’ > Opties > Andere gegevens bron > scroll naar beneden en je merkt dat de net toegevoegde lijnen in de ‘tabel1’ bron zijn opgenomen.
Werkbladen die tabellen bevatten, kunnen niet gedeeld worden! Wil je dat proberen, dan zal Excel een foutboodschap tonen en aanraden om de tabellen terug naar een bereik om te vormen.
In sommige versies van Excel verschijnt deze boodschap niet.
Er is een oplossing: deel het werkblad vóór je tabellen aanmaakt.
Maar indien de tabellen al bestaan dan zit er niets anders op dan de volgende stappen te volgen:
242. Maak een nieuw werkboek.
243. Deel het werkboek via CONTROLEREN >
244. Gewoon delen 
245. Via de tab Geavanceerd kun je ook instellen of de wijzigingen moeten bewaard worden en voor hoelang.
246. Werkmap beveiligen en delen 
247. Vink aan ‘Delen met bijhouden van wijzigingen’ en geef al dan niet een paswoord om het te openen.
248. Bewaar het bestand > zet het op een netwerkschijf waar iedereen waarmee je het wenst te delen, toegang heeft.
249. Ga naar het originele bestand en kopieer de tabellen.
250. Plak ze in dit nieuwe – gedeelde – bestand.
251. Bewaar het nieuwe bestand en gebruik dit vanaf nu.
Werk je met lijsten of tabellen, dan is het heel eenvoudig om de gegevens te sorteren.
Je hoeft niet de volledige tabel te selecteren, maar klik enkel in een cel die binnen het gegevens bereik ligt.
Klik in een cel die in de kolom staat die je wilt sorteren > in het lint Gegevens> klik op het icoon A/Z (laag naar hoog) of Z/A (hoog naar laag).
In dit voorbeeld is de geselecteerde kolom D, want de cursor staat in D5. Onderstaande schermafbeelding toont enkel het bovenste gedeelte van de tabel.
De ganse tabel zal gesorteerd worden: de gegevens die links en rechts van de kolom staan, zullen de sortering volgen. In lijn 5 staan nu de gegevens van Castor.
.
Wil je deze tabel sorteren op meer dan 1 kolom dan volg je deze stappen:
252. Klik in een willekeurige cel, in de tabel.
253. In het lint GEGEVENS > klik op de knop Sorteren.
254. In het Sorteer scherm, klik je naast ‘Sorteren op’ op het afrolmenu. Dit bevat een lijst van de veldnamen in je tabel.
255. Selecteer de veldnaam waarop eerst moet gesorteerd worden.
256. Klik dan op ‘Niveau toevoegen’ waarna je naast ‘Vervolgens op’ de tweede veldnaam kiest voor de sortering.
257. Klik telkens op ‘Niveau toevoegen’ tot je alle veldnamen waarop je wilt sorteren, geselecteerd hebt.
258. Klik nu op OK om de sortering te starten.
In Excel 2016 wordt de laatst toegepaste sortering onthouden, ook al sluit je het bestand en sluit je Excel.
Sorteren volgens het alfabet wordt veel toegepast, maar is soms nutteloos.
Zo krijg je gek resultaat als je maanden alfabetisch sorteert:
|
De originele lijst:
|
Gesorteerd A/Z:
|
Dit zou het moeten zijn:
|
Zo sorteer je maanden correct:
259. Klik in een cel de een maandnaam bevat.
260. GEGEVENS > Sorteren 
261. In dit scherm ga je de ‘Volgorde’ volgens een ‘Aangepast lijst…’ kiezen
262. 
263. In deze lijst staan onder andere januari, februari, enzoverder, al ingevuld. Is dat niet het geval dan heb je niet de Nederlandse versie van Windows geïnstalleerd. Zie dan tip 106.4.
264. 
265. Klik de lijn met ‘januari, februari’ aan.
266. Klik op OK > in het sorteer scherm klik je nogmaals op OK
267. De lijst is nu correct gesorteerd.
Een lijst met personeelsgegevens, bevat ook de naam van de dienst waar de mensen werken. Deze moet gesorteerd worden volgens de hoogste naar de laagste functie: dus eerst Directie, dan Management, dan Finance, dan Admin, enzoverder. Dus helemaal niet volgens het alfabet.
268. Klik in een cel binnen de kolom Afdeling.
269. GEGEVENS > Sorteren
270. In dit scherm ga je de ‘Volgorde’ volgens een ‘Aangepast lijst…’ kiezen
271.
272. Typ eerst de lijst in het rechter vak van dit scherm:
273. Typ Directie > Enter
274. Typ Management > Enter
275. Type Finance > Enter
276. Type Admin > Enter
277. Klik op de knop Toevoegen
278. De lijst verschijnt nu links binnen dit scherm
279. Klik erop
280. Klik op OK > in het sorteer scherm klik je nogmaals op OK
281. De lijst wordt nu gesorteerd volgens deze lijst, Directie bovenaan.
Opmerking: in stap d. stopt de lijst bij Admin. Nochtans staan er nog afdelingen binnen deze databank. De afdelingsnamen die we niet opnamen in de lijst, worden onderaan de gesorteerde lijst toegevoegd, gesorteerd van A naar Z.
IP adressen sorteren op de normale manier zal niet lukken want Excel ziet deze als tekst.
Selecteer de cellen die de IP adressen bevatten:
GEGEVENS > Tekst naar kolommen > Gescheiden > Overige is een . (punt) > Voltooien
Dan GEGEVENS > Sorteer op kolom C, dan D, dan E en dan F.
Daarna gebruik je het &-teken om het IP adres terug samen te stellen.
