Wiemy, jak uzyskać w Excelu najczęściej pojawiający się numer. Korzystamy z funkcji TRYB. Ale jeśli chcemy wypisać najczęściej pojawiające się liczby, ta formuła nie zadziała. Do wykonania tego zadania będziemy potrzebować kombinacji funkcji programu Excel.
Oto ogólna formuła zawierająca najczęściej występujące liczby:
=TRYB(JEŻELI(1-CZY.LICZBA(PODZIEL(liczby;zakres rozszerzający;0));liczby)) |
liczby:Jest to zakres lub tablica, z której musimy pobrać najczęściej pojawiające się liczby.
zakres_rozszerzenia:Jest to rozszerzający się zakres, który zaczyna się od jednej komórki powyżej miejsca, w którym piszesz tę formułę. Na przykład, jeśli poprawna formuła programu Excel w komórce B4, zakres rozszerzający będzie wynosił $ B $ 3: B3.
Zobaczmy teraz tę formułę w akcji.
Przykład: Uzyskaj 3 często pojawiające się oceny.
Więc scenariusz jest taki. Posiadamy dane dotyczące ankiety, w której użytkownicy ocenili nasze usługi. Musimy uzyskać 3 najczęściej pojawiające się oceny.
Napisz tę formułę w G3:
= TRYB (JEŻELI (1 - JEST LICZBA (DOPASOWANIE ($ D $ 3: $ D $ 11, $ G $ 2: G 2,0)), $ D $ 3: $ D $ 11)) |
To jest formuła tablicowa. Użyj klawiszy CTRL+SHIFT+ENTER. Przeciągnij tę formułę w trzech komórkach. A teraz masz 3 najczęściej występujące liczby.
Teraz, kiedy to zrobiliśmy, zobaczmy, jak to działa.
Jak to działa?
Tutaj chodzi o wykluczenie już wyodrębnionych najczęściej pojawiających się liczb w następnym uruchomieniu funkcji TRYBU. w tym celu korzystamy z dodatkowej pomocy innych funkcji logicznych programu Excel.
Aby to zrozumieć, spójrzmy na formułę w komórce G4. Który jest:
= TRYB (JEŻELI (1 - JEST LICZBA (DOPASOWANIE ($ D $ 3: $ D $ 11, $ G $ 2: G3,0)), $ D $ 3: $ D $ 11)) |
Formuła zaczyna się od najbardziej wewnętrznej funkcji PODAJ.POZYCJĘ.
Funkcja PODAJ.POZYCJĘ ma zakres wyszukiwania D3:D11 (liczby). Wartość wyszukiwania to również zakres G2:G3 (rozszerzony po skopiowaniu). Teraz oczywiście G2 zawiera tekst, którego nie ma w zakresie D3:D11. Następnie G3 zawiera 6. PODAJ.POZYCJĘ szuka 6 w zakresie D3:D11 i zwraca tablicę błędów #N/A i pozycji 6 w zakresie, co jest {#nie dotyczy;2;#nie dotyczy;2;2;#nie dotyczy;#nie dotyczy;#nie dotyczy;#nie dotyczy}.
Teraz ta tablica jest przekazywana do funkcji CZY.LICZBA. CZY.LICZBA zwraca TRUE dla liczb w tablicy. Stąd otrzymujemy kolejną tablicę jako {FAŁSZ;PRAWDA;FAŁSZ;PRAWDA;PRAWDA;FAŁSZ;FAŁSZ;FAŁSZ;FAŁSZ}.
Następnie ta tablica jest odejmowana od 1. Jak wiemy FAŁSZ jest traktowany jako 0, a PRAWDA jako 1. Gdy odejmiemy tę tablicę od 1, otrzymamy odwróconą tablicę jako{1;0;1;0;0;1;1;1;1}. Jest to dokładne odbicie lustrzane tablicy zwróconej przez funkcję CZY.LICZBA.
Teraz dla każdego 1 (PRAWDA) TRYB działa funkcja. Funkcja MODE otrzymuje tablicę{2;FAŁSZ;2;FAŁSZ;FAŁSZ;5;4;4;5}.I na koniec otrzymujemy najczęściej występującą liczbę w tablicy jako 2. (Zauważ, że zarówno 5, jak i 4 występują 2 razy. Ale ponieważ 2 pojawiło się jako pierwsze, otrzymujemy 2.
W G3 funkcja CZY.LICZBA zwraca tylko tablicę FAŁSZ. Stąd cała gama zasilana jest funkcją MODE. Wtedy funkcja mode zwraca najczęściej występującą liczbę z zakresu jako 6.
Inna formuła
Innym sposobem jest użycie funkcji NOT, aby odwrócić zwracaną tablicę przez funkcję ISNUMBER.
{=TRYB(JEŻELI(NIE(CZY.LICZBA(DOPASOWANIE($D$3:$D$11;$G$2:G3,0))),$D$3:$D$11))} |
Więc tak, w ten sposób możesz pobrać najczęściej pojawiające się liczby w programie Excel. Mam nadzieję, że wystarczająco wyjaśniłem. Jeśli masz jakiekolwiek wątpliwości dotyczące którejkolwiek funkcji użytej w tej formule, po prostu na nie kliknij. Zaprowadzi cię do wyjaśnienia tej funkcji. Jeśli masz jakiekolwiek pytania dotyczące tego artykułu lub jakiejkolwiek innej funkcji programu Excel lub VBA, zapytaj mnie w sekcji komentarzy poniżej.
Jak znaleźć często występujące liczby | Najczęściej pojawiające się liczby można obliczyć za pomocą funkcji TRYB programu Excel.
Pobierz najczęściej pojawiający się tekst w Excelu | Dowiedz się, jak uzyskać często pojawiający się tekst w programie Excel.
Wyszukaj często pojawiający się tekst z kryteriami w programie Excel | Aby uzyskać najczęściej pojawiający się tekst w programie Excel z kryteriami, wystarczy umieścić warunek if przed funkcją dopasowania.
Pobieranie pierwszej wartości z listy, która jest większa/mniejsza niż określona wartość | Dowiedz się, jak wyszukać pierwszą wartość, która jest większa od określonej wartości. Ogólna formuła do wyszukania to…
Jak pobrać cały wiersz o dopasowanej wartości| Łatwo jest wyszukać i pobrać cały wiersz z tabeli. Zobaczmy, jak możemy wykonać to wyszukiwanie.
Jak pobrać najnowszą cenę w programie Excel | Aby sprawdzić najnowszą cenę z zakresu, musisz mieć posortowane dane. Po posortowaniu użyj tej formuły…
Popularne artykuły:
50 skrótów Excela zwiększających produktywność | Przyspiesz swoje zadanie. Te 50 skrótów przyspieszy pracę w programie Excel.
Funkcja WYSZUKAJ.PIONOWO w programie Excel | Jest to jedna z najczęściej używanych i popularnych funkcji programu Excel, która służy do wyszukiwania wartości z różnych zakresów i arkuszy.
LICZ.JEŻELI w programie Excel 2016 | Policz wartości z warunkami za pomocą tej niesamowitej funkcji. Nie musisz filtrować danych, aby policzyć konkretną wartość. Funkcja Countif jest niezbędna do przygotowania deski rozdzielczej.
Jak korzystać z funkcji SUMA.JEŻELI w programie Excel | To kolejna ważna funkcja deski rozdzielczej. Pomaga to zsumować wartości w określonych warunkach.