Formuła Excel: wypisz najczęściej występujące liczby -

Formuła ogólna

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

Podsumowanie

Aby wymienić najczęściej występujące liczby w kolumnie (tj. Najczęściej, na drugim miejscu, na trzecim miejscu itd.), Możesz utworzyć formułę tablicową opartą na czterech funkcjach programu Excel: JEŻELI, TRYB, PODAJ.POZYCJĘ i NUMER. W przedstawionym przykładzie formuła w D5 to:

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

gdzie „dane” to nazwany zakres B5: B16. Formuła jest następnie kopiowana do wierszy poniżej D5 w celu wyświetlenia żądanej listy najczęściej występujących liczb.

Uwaga: to jest formuła tablicowa i należy ją wprowadzić za pomocą Ctrl + Shift + Enter.

Wyjaśnienie

Rdzeniem tej formuły jest funkcja TRYB, która zwraca najczęściej występującą liczbę w zakresie lub tablicy. Reszta formuły po prostu konstruuje przefiltrowaną tablicę do użycia w trybie MODE w każdym wierszu. Rozszerzający się zakres $ D $ 4: D4 służy do wykluczania liczb już wyświetlanych w $ D $ 4: D4.

Praca od wewnątrz:

  1. Funkcja MATCH jest używana najpierw sprawdź wszystkie liczby w nazwanym zakresie "dane" z istniejącymi liczbami w rozszerzającym się zakresie $ D 4 $: D4
  2. ISNUMBER konwertuje dopasowane wartości na TRUE, a niedopasowane wartości na FALSE
  3. 1-NUMBER odwraca tablicę, a operacja matematyczna zwraca jedynki i zera
  4. JEŻELI używa wyjścia tablicowego nr 3 powyżej do filtrowania oryginalnej listy wartości, z wyłączeniem liczb znajdujących się już w $ D $ 4: D4
  5. Funkcja TRYB zwraca najczęściej występującą liczbę w danych wyjściowych tablicy w kroku 4

W komórce D5 nie występuje żadne filtrowanie, a dane wyjściowe każdego powyższego kroku wyglądają następująco:

(#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

W komórce D6, gdzie 93 jest już w D5, dane wyjściowe wyglądają następująco:

(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

Obsługa błędów

Funkcja TRYB zwróci błąd # N / A, gdy nie ma trybu. Podczas kopiowania formuły do ​​kolejnych wierszy prawdopodobnie napotkasz błąd # N / D. Aby przechwycić ten błąd i zamiast tego zwrócić pusty ciąg („”), możesz użyć funkcji IFERROR w następujący sposób:

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

Interesujące artykuły...