Vzorec aplikace Excel: Seznam nejčastěji se vyskytujících čísel -

Obsah

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:

  1. 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
  2. ISNUMBER převádí spárované hodnoty na TRUE a neodpovídající hodnoty na FALSE
  3. 1-ČÍSLO převrátí pole a matematické operace vygeneruje jednotky a nuly
  4. 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
  5. 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)),"")

Zajímavé články...