Generička formula
(=MODE(IF(1-ISNUMBER(MATCH(data,exp_rng,0)),data)))
Sažetak
Da biste popisali brojeve koji se najčešće pojavljuju u stupcu (tj. Najčešće, drugo najčešće, treće najčešće itd.), Možete formulu niza na temelju četiri Excel funkcije: IF, MODE, MATCH i BROJ. U prikazanom primjeru formula u D5 je:
(=MODE(IF(1-ISNUMBER(MATCH(data,$D$4:D4,0)),data)))
gdje je "podatak" imenovani raspon B5: B16. Zatim se formula kopira u retke ispod D5 da bi se dobio željeni popis najčešćih brojeva.
Napomena: ovo je formula niza i mora se unijeti sa control + shift + enter.
Obrazloženje
Jezgra ove formule je funkcija MODE, koja vraća broj koji se najčešće javlja u rasponu ili nizu. Ostatak formule samo gradi filtrirani niz za MODE koji će se koristiti u svakom retku. Raspon $ D $ 4: D4 koji se širi djeluje na izuzeće brojeva koji su već objavljeni u $ D $ 4: D4.
Rad iznutra prema van:
- Koristi se funkcija MATCH najprije provjeri sve brojeve u imenovanom rasponu "podaci" prema postojećim brojevima u opsegu koji se širi $ D $ 4: D4
- ISNUMBER pretvara usklađene vrijednosti u TRUE, a nepodudarane vrijednosti u FALSE
- 1-NUMBER preokreće niz, a matematička operacija daje jedinice i nule
- IF koristi gornji izlaz # 3 za filtriranje izvornog popisa vrijednosti, izuzimajući brojeve koji su već u $ D $ 4: D4
- Funkcija MODE vraća najčešći broj u izlazu niza u koraku # 4
U ćeliji D5 ne dolazi do filtriranja i izlaz svakog gornjeg koraka izgleda ovako:
(#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
U ćeliji D6, s 93 već u D5, izlaz izgleda ovako:
(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
Pogreške u rukovanju
Funkcija MODE vratit će pogrešku # N / A kada nema načina. Dok kopirate formulu u naredne retke, vjerojatno ćete naletjeti na pogrešku # N / A. Da biste zarobili ovu pogrešku i umjesto toga vratili prazan niz (""), možete koristiti IFERROR ovako:
=IFERROR(MODE(IF(1-ISNUMBER(MATCH(data,$D$4:D4,0)),data)),"")