Vzorec programu Excel: Zoznam najčastejšie sa vyskytujúcich čísel

Všeobecný vzorec

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

Zhrnutie

Ak chcete uviesť zoznam najčastejšie sa vyskytujúcich čísel v stĺpci (tj najbežnejšie, druhé najbežnejšie, tretie najbežnejšie atď.), Môžete vytvoriť vzorec poľa založený na štyroch funkciách programu Excel: IF, MODE, MATCH a ISNUMBER. V zobrazenom príklade je vzorec v D5:

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

kde „údaj“ je pomenovaný rozsah B5: B16. Vzorec sa potom skopíruje do riadkov pod D5, aby sa vytvoril požadovaný zoznam najčastejších čísel.

Poznámka: toto je vzorec poľa a musí sa zadávať pomocou klávesov Control + Shift + Enter.

Vysvetlenie

Jadrom tohto vzorca je funkcia MODE, ktorá vracia najčastejšie sa vyskytujúce číslo v rozsahu alebo poli. Zvyšok vzorca iba vytvorí filtrované pole, ktoré má MODE použiť v každom riadku. Rozširujúci sa rozsah $ D $ 4: D4 vylučuje čísla, ktoré už majú výstup v $ D $ 4: D4.

Práca zvnútra von:

  1. Používa sa funkcia MATCH, najskôr skontrolujte všetky čísla v pomenovanom rozsahu „data“ oproti existujúcim číslam v rozširujúcom sa rozsahu $ D $ 4: D4
  2. ISNUMBER prevádza priradené hodnoty na TRUE a nezhodné hodnoty na FALSE
  3. 1-ČÍSLO obráti pole a matematická operácia vygeneruje jednotky a nuly
  4. IF používa výstup poľa z # 3 vyššie na filtrovanie pôvodného zoznamu hodnôt, okrem čísel, ktoré sú už v $ D $ 4: D4
  5. Funkcia MODE vráti najčastejšie číslo na výstupe z poľa v kroku č. 4

V bunke D5 nedochádza k filtrovaniu a výstup z každého vyššie uvedeného kroku vyzerá 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 bunke D6 s 93 už v D5 vyzerá 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

Zaobchádzanie s chybami

Ak nie je k dispozícii žiadny režim, funkcia MODE vráti chybu # N / A. Pri kopírovaní vzorca do ďalších riadkov pravdepodobne narazíte na chybu # N / A. Ak chcete zachytiť túto chybu a namiesto nej vrátiť prázdny reťazec (""), môžete použiť príkaz IFERROR takto:

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

Zaujímavé články...