Obecný vzorec
(=MODE(IF(1-ISNUMBER(MATCH(data,exp_rng,0)),data)))
souhrn
Chcete-li zobrazit seznam nejčastěji se vyskytujících čísel ve sloupci (tj. Nejběžnější, druhý nejběžnější, třetí nejběžnější atd.), Můžete vytvořit maticový vzorec založený na čtyřech funkcích aplikace Excel: IF, MODE, MATCH a ISNUMBER. V zobrazeném příkladu je vzorec v D5:
(=MODE(IF(1-ISNUMBER(MATCH(data,$D$4:D4,0)),data)))
kde „data“ je pojmenovaný rozsah B5: B16. Vzorec se poté zkopíruje do řádků pod D5, aby se vytvořil požadovaný seznam nejčastějších čísel.
Poznámka: toto je maticový vzorec a musí být zadán pomocí kláves Control + Shift + Enter.
Vysvětlení
Jádrem tohoto vzorce je funkce MODE, která vrací nejčastěji se vyskytující číslo v rozsahu nebo poli. Zbytek vzorce pouze vytvoří filtrované pole, které MODE použije v každém řádku. Rozšiřující se rozsah $ D $ 4: D4 funguje tak, že vylučuje čísla, která již mají výstup v $ D $ 4: D4.
Práce zevnitř ven:
- Použije se funkce MATCH, nejprve zkontrolujte všechna čísla v pojmenovaném rozsahu „data“ proti existujícím číslům v rozšiřujícím se rozsahu $ D $ 4: D4
- ISNUMBER převádí spárované hodnoty na TRUE a neodpovídající hodnoty na FALSE
- 1-ČÍSLO převrátí pole a matematické operace vygeneruje jednotky a nuly
- IF používá výstup pole # 3 výše k filtrování původního seznamu hodnot, s výjimkou čísel již v $ D $ 4: D4
- Funkce MODE vrací v kroku 4 nejčastější číslo na výstupu pole
V buňce D5 nedochází k žádnému filtrování a výstup každého kroku výše vypadá takto:
(#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
V buňce D6 s 93 již v D5 vypadá výstup takto:
(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
Zpracování chyb
Funkce MODE vrátí chybu # N / A, pokud není k dispozici žádný režim. Při kopírování vzorce dolů do následujících řádků pravděpodobně narazíte na chybu # N / A. Chcete-li tuto chybu zachytit a místo toho vrátit prázdný řetězec (""), můžete použít IFERROR takto:
=IFERROR(MODE(IF(1-ISNUMBER(MATCH(data,$D$4:D4,0)),data)),"")
