Generisk formel
=INDEX(rng1,MODE(IF(rng2=criteria,MATCH(rng1,rng1,0))))
Resumé
For at finde den hyppigst forekommende tekst i et interval baseret på kriterier, du angiver, kan du bruge en matrixformel baseret på flere Excel-funktioner INDEX, MATCH, MODE og IF. I det viste eksempel er formlen i G5:
=INDEX(supplier,MODE(IF(client=F5,MATCH(supplier,supplier,0))))
hvor "leverandør" er det navngivne område C5: C15, og "klient" er det navngivne interval B5: B15.
Bemærk: dette er en matrixformel og skal indtastes med kontrol + skift + enter.
Forklaring
Når vi arbejder indefra og ud, bruger vi MATCH-funktionen til at matche tekstområdet med sig selv ved at give MATCH det samme område for opslagsværdi og opslagsarray med nul for matchtype:
MATCH(supplier,supplier,0)
Da opslagsværdien er en matrix med 10 værdier, returnerer MATCH en matrix med 10 resultater:
(1;1;3;3;5;1;7;3;1;5;5)
Hver vare i denne matrix repræsenterer den første position, hvor et leverandørnavn vises i dataene. Denne matrix indføres i IF-funktionen, som kun bruges til at filtrere resultaterne for klient A:
IF(client=F5,(1;1;3;3;5;1;7;3;1;5;5))
IF returnerer det filtrerede array til MODE-funktionen:
(1;FALSE;3;FALSE;5;1;FALSE;FALSE;1;5;FALSE)
Bemærk kun positioner tilknyttet klient A forbliver i arrayet. MODE ignorerer FALSE-værdier og returnerer det hyppigst forekommende tal til INDEX-funktionen som række nummer:
=INDEX(supplier,1)
Endelig returnerer INDEX med det navngivne område "leverandør" som array, "Brown", den hyppigst forekommende leverandør for klient A.
