Excel trucjes
Ik heb me nooit echt verdiept in de mogelijkheden van Excel, maar nu ik een vervelend herhalend klusje in een excel moet uitvoeren ben ik me gaan verdiepen in een aantal handige mogelijkheden.
Voor mijn eigen referentie de volgende functies. De eerste functie is address waarmee je een referentie tekstueel kan samenstellen, bijvoorbeeld
ADDRESS(14;30;1;TRUE;CONCATENATE("Wk "; TEXT($A11;$A11)))
maakt een verwijzing naar AD14 (of eigenlijk $AD$14 vanwege de opvolgende parameter 1). Dit had overigens ook via ROW($AD$14) en COLUMN($AD$14) gedaan kunnen worden. De laatste parameter is een verwijzing naar het blad, in dit geval ‘Wk 10′ waarbij in $A11 het getal 10 staat. Ofwel de formule hierboven maakt een tekst aan ‘Wk 10′!$AD$14.
Met de tekst kan je niet veel, maar via de functie indirect kan je de waarde van die verwijzing ophalen:
=INDIRECT(ADDRESS(14;30;1;TRUE;CONCATENATE("Wk "; TEXT($A11;$A11)));TRUE)
Dit is dus hetzelfde als =’Wk 10′$AD$14
Maar je kan nu via copy-and-paste de formule eenvoudig laten varieren over de weeknummers. Immers zal via copy-and-past naar de volgende regel $A11 veranderen in $A12 waarin in dit geval het volgende weeknummer staat en dat levert ‘Wk 11′$AD$14 op.
Een ander trucje is het opzoeken van de laatst ingevulde waarde in een kolom. Heel handig voor kolommen die je regelmatig aanvult.
MAX(IF(LEN(B3:B39)>0;ROW(B3:B39);0))
Dit levert in deze vorm een foutmelding op. Maar als je de formule invoert en met Ctrl-Shift-Enter opslaat, zal Excel de formule per element van B3:B39 uitvoeren, waardoor het MAX van een array van rijnummers wordt bepaald. In het array zitten alleen rijnummers van cellen die gevuld zijn (LEN > 0).
Vervolgens kan je dit rijnummer in een formule gebruiken, bijvoorbeeld om het verschil van de laatst ingevulde waarde in B3:B39 met een startwaarde in A3 te berekenen:
=INDIRECT(ADDRESS(MAX(IF(LEN(B3:B39)>0;ROW(B3:B39);0));COLUMN(B3)))-A3
Deze trucjes zoek je eenmalig uit en vergeet je daarna weer, vandaar deze post 