Eerder leerden we gebieden te benoemen, maar alle voorbeelden gingen uit van een vaste lijst. Wat als een lijstje elke dag kan aangroeien? Gaan we dan telkens de lijst opnieuw definiëren?
Nee hoor, we zullen in de blog van vandaag ervoor zorgen dat Excel begrijpt over hoeveel cellen het gaat.
Het meest eenvoudige voorbeeld in deze blog gaat uit van de nieuwe ‘Table’ functionaliteit van Excel. Nadien zien we een complexere oplossing (just for fun)
Maak een lijst met de naam van enkele landen en zet een titel boven de lijst. Maak er via ‘Insert’ een ‘Table’ van (of gebruik CTRL-T).
Selecteer de landen (zonder titel) en kies ‘Define Name’. Geef het gebied een naam (bv ‘Landen’).
In ‘Refers to’ zie je de verwijzing naar de tabel en de kolom ‘Landen’ staan. Door de combinatie van een tabel en een kolom weet Excel nu dat nieuwe toegevoegde waarden ook bij de naam van de lijst horen.
Test dit door de formule ‘=countA( Landen ) in een cel te typen.
Voeg nu een land toe aan de lijst en de functie zal nu 8 landen tellen ipv 7 zoals in bovenstaand voorbeeld.
Tot daar het basisvoorbeeld. Kijk morgen zeker ook naar de blog die je o.a. leert hoe je deze lijst als keuzelijst kan aanbieden in een cel.
Een voorbeeld dat niet op tabellen gebaseerd wordt, is al wat moeilijker te realiseren, maar het was wel de enige manier om dit op te lossen voor dat Excel de tabellen geïntroduceerd heeft. Hier heb je wel wat Excel ervaring voor nodig. Maar laat je niet te snel afschrikken. Give it a try !
Voor we van start gaan, zullen we ingaan op de werking van de functie OFFSET. Spreek tijdens een opleiding een van mijn collega’s aan voor enkele praktische voorbeelden van deze functie. Ik wil vandaag enkel aantonen dat de functie zich verplaatst naar een bepaalde cel of gebied. Een functionaliteit die we nodig hebben voor gebieden die telkens aangroeien.
Vul een aantal cellen met verschillende waarden (verticaal, al is dat niet noodzakelijk). Maak een nieuw lijstje aan maar gebruik zeker niet de ’table’ functionaliteit.
Met de Offset functie verwijzen we nu naar de oranje cel ‘E22’ vlak boven deze reeks. Dit is de referentie cel voor de Offset functie, m.a.w. het vertrekpunt. Dit wil zeggen dat Excel van hieruit zoekt naar de oplossing. Nadien geven we mee dat Excel 4 rijen (rows) naar beneden en 0 kolommen ‘cols’ naar rechts dient te kijken. De oplossing van deze functie zal “Germany” zijn.
De twee argumenten die we niet hebben gebruikt, kunnen de hoogte (height) en de breedte (width) van het gebied aangeven dat Excel in rekening moet brengen.
In onderstaand voorbeeld wordt dit even geïllustreerd. We verplaatsen ons van de oranje referentie-cel naar de gele cel (2 rijen omlaag en 1 cel naar rechts) en nemen dan een gebied dat 3 rijen hoog en 4 kolommen breed is. Daarvan willen we de som kennen. Zodoende krijgen we de som van blauwe cellen (en de gele natuurlijk).
Nu zijn we klaar om ons voorbeeld af te werken.
Maak een nieuwe lijst van landen. Alleen gaan we er deze keer geen tabel van maken. Doe dit op een nieuw leeg blad. Start op de eerste rij van een kolom, die je verder voor niets anders gebruikt. Je hoeft ook geen titel in te geven. Indien je dat toch zou willen, is de oplossing een beetje anders.
Kies om een nieuwe naam aan te maken en gebruik de Offset formule zoals in onderstaand voorbeeld.
De formule vertrekt in cel A1 en verplaatst zich 0 rijen en 0 kolommen. Nadien worden er zoveel cellen verticaal gebruikt als dat er cellen geteld worden in kolom A ( hiervoor kan je tijdens het maken van de formule gewoon op de kolomtitel ‘A’ klikken). Als laatste argument geven we aan dat het gebied 1 kolom breed dient te zijn.
Als je klaar bent geef je ergens de formule ‘= CountA ( AlleLanden )’ in. Voeg nu een land toe aan de lijst en de waarde van CountA zal stijgen. En dit zonder gebruik te maken van ’tabellen’!
Morgen gaan we deze naam toekennen aan een keuzelijst in Excel.
Morgen: Dag 15: Een keuzelijst met wisselende inhoud.
