Iemand stelde de volgende vraag op de site:
“Ik heb de functie verticaal zoeken gebruikt, maar wil graag dat Excel rekening houdt met hoofdletters en kleine letters in tekst (mijn zoekwaarde is een tekst). Hoe kan ik dit doen?”
Hoofdlettergevoelig
Op het eerste gezicht lijkt dit een eenvoudig probleem. Hoe moeilijk kan het zijn om Excel bij het zoeken rekening te laten houden met hoofdletters? Daar zal toch wel een standaard functie voor zijn? Helaas, niets is minder waar. Er is maar één functie in Excel hoofdlettergevoelig en dat is GELIJK (Engels: EXACT). Helaas is dit geen zoekfunctie, maar een tekstfunctie. Het vergelijkt twee teksten en bepaald of ze wel of niet aan elkaar gelijk zijn.
Matrixformule
Uiteindelijk is het probleem wel met deze functie op te lossen, maar daarvoor moeten we wel gebruik maken van matrixformules. Daarom geef ik in dit artikel hier een korte uitleg over. In een volgend artikel leg ik uit hoe je met behulp van deze techniek hoofdlettergevoelig kunt zoeken.
In normale formules rekent Excel steeds met één cel (bijvoorbeeld A1*B1). Met een matrixformule is het mogelijk om met meerdere cellen tegelijkertijd te rekenen (bijvoorbeeld A1:A3*B1:B3). Ik zal dit met twee voorbeelden illustreren.
Gewogen gemiddelde
Stel we willen van een aantal getallen het gewogen gemiddelde berekenen. Het ligt voor de hand om het op de volgende manier op te lossen:
In het voorbeeld vermenigvuldig ik het gewicht met de score en deel ik de som daarvan door de som van de gewichten. De berekening verloopt echter wel in 2 stappen. Met behulp van een matrixformule kunnen we dit ook in één stap doen.
De kolom met gewicht x score hebben we nu niet meer nodig. De formule in cel B10 is:
{=SOM(B3:B7*C3:C7)/SOM(B3:B7)}
De accolades geven aan dat het om een matrixformule gaat. Je typt ze niet zelf in. Excel voegt ze toe omdat je de formule anders afsluit dan normaal, nl met [Ctrl] + [Shift] + [Enter]. Het matrix gedeelte zit in de eerste som. We geven Excel eerst de opdracht om de matrix B3:B7 te vermenigvuldigen met C3:C7. Het resultaat hiervan leidt (in het geheugen van Excel) tot een nieuwe matrix, namelijk {10;8;25;2;24}. Van deze matrix berekent Excel de som en deelt deze vervolgens door de som van B3 t/m B7.
Gemiddelde zonder nulwaarden
Het tweede voorbeeld gaat over het berekenen van een gemiddelde waarbij nulwaarden niet worden meegenomen.
De functie GEMIDDELDE (Engels: AVERAGE) neemt standaard de nulwaarden mee in de berekening van het gemiddelde. Soms wil je dit echter niet. Je kun dat oplossen met de volgende matrixformule:
{=GEMIDDELDE(ALS(B2:I2<>0;B2:I2;””))}
Ook hier geldt je typt de formule in zonder accolades en sluit af met [Ctrl] + [Shift] + [Enter]. Het matrix gedeelte zit nu in de ALS (Engels: IF) functie. Van iedere cel in het bereik B2:I2 controleert Excel of de waarde niet nul is. Is dit waar dan wordt de waarde overgenomen. Is de waarde 0 dan vervangt Excel deze door “”. Het resultaat van de ALS functie levert dan de volgende matrix op: {3;””;2;””;3;10;5;10}. Van deze matrix berekent Excel vervolgens het gemiddelde. De functie GEMIDDELDE negeert automatisch niet numerieke (“” in ons voorbeeld) waarden.
Met deze twee voorbeelden heb ik een idee gegeven wat je kunt doen met matrixformules. Wil je zelf met de voorbeelden stoeien, download dan het voorbeeld bestand onderaan deze pagina. In een volgend artikel zal ik uitleggen hoe we met deze techniek in Excel hoofdlettergevoelig kunnen zoeken.
- [drain file 8 url download het voorbeeldbestand]