Excel-formel: Liste over hyppigst forekommende numre -

Generisk formel

(=MODE(IF(1-ISNUMBER(MATCH(data,exp_rng,0)),data)))

Resumé

For at liste de hyppigst forekommende numre i en kolonne (dvs. mest almindelige, næst mest almindelige, tredje mest almindelige osv.) Kan du en matrixformel baseret på fire Excel-funktioner: HVIS, MODE, MATCH og ISNUMBER. I det viste eksempel er formlen i D5:

(=MODE(IF(1-ISNUMBER(MATCH(data,$D$4:D4,0)),data)))

hvor "data" er det navngivne område B5: B16. Formlen kopieres derefter til rækker under D5 for at sende den ønskede liste over hyppigste numre.

Bemærk: dette er en matrixformel og skal indtastes med kontrol + skift + enter.

Forklaring

Kernen i denne formel er MODE-funktionen, som returnerer det hyppigst forekommende tal i et interval eller array. Resten af ​​formlen konstruerer bare et filtreret array til MODE til brug i hver række. Det ekspanderende område $ D $ 4: D4 udelukker numre, der allerede er output i $ D $ 4: D4.

Arbejder indefra og ud:

  1. MATCH-funktionen bruges først kontrollere alle numre i det navngivne interval "data" mod eksisterende numre i det ekspanderende område $ D $ 4: D4
  2. ISNUMBER konverterer matchede værdier til SAND og ikke-matchede værdier til FALSK
  3. 1-NUMMER vender arrayet, og matematikoperationen udlæser en og nuller
  4. IF bruger array-output på # 3 ovenfor til at filtrere den oprindelige liste over værdier, eksklusive tal, der allerede er i $ D $ 4: D4
  5. MODE-funktionen returnerer det hyppigste nummer i array-output i trin # 4

I celle D5 forekommer der ingen filtrering, og output fra hvert trin ovenfor ser sådan ud:

(#N/A;#N/A;#N/A;#N/A;#N/A;#N/A;#N/A;#N/A;#N/A;#N/A;#N/A;#N/A) (FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE) (1;1;1;1;1;1;1;1;1;1;1;1) (93;92;93;94;95;96;97;98;99;93;97;95) 93

I celle D6, med 93 allerede i D5, ser output sådan ud:

(2;#N/A;2;#N/A;#N/A;#N/A;#N/A;#N/A;#N/A;2;#N/A;#N/A) (TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE) (0;1;0;1;1;1;1;1;1;0;1;1) (FALSE;92;FALSE;94;95;96;97;98;99;FALSE;97;95) 95

Håndteringsfejl

MODE-funktionen returnerer fejlen # N / A, når der ikke er nogen tilstand. Når du kopierer formlen ned i efterfølgende rækker, vil du sandsynligvis løbe ind i fejlen # N / A. For at fælde denne fejl og returnere en tom streng ("") i stedet kan du bruge IFERROR sådan:

=IFERROR(MODE(IF(1-ISNUMBER(MATCH(data,$D$4:D4,0)),data)),"")

Interessante artikler...