Zliczanie niepowtarzalnych wartości w Excelu

Autor: Piotr Dynia
Wiele razy potrzebujemy zliczyć, ile jest niepowtarzalnych wartości w danym zestawieniu. Poniżej pokażemy, jaką formułę zastosować w takim wypadku na postawie przykładu opartego o zestawienie zgłoszeń awarii i usterek.
Rys. 1. Arkusz zawierający zestawienie zgłoszeń i awarii
W drugim arkuszu znajduje się statystyka zgłoszeń przedstawiona na rysunku 2.
 
Rys. 2. Arkusz zawierający statystykę dotyczącą zgłoszeń
W tym arkuszu w kolumnie B chcemy wpisać odpowiednie formuły, które będą wyliczały wartości określone w kolumnie A. W tym celu:
1. W komórce B2 wpisujemy formułę zliczającą ilość zgłoszeń:
=ILE.NIEPUSTYCH(Zgłoszenia!A:A)-1
Ilość niepustych komórek w kolumnie A jest pomniejszona o jeden, ponieważ w pierwszym wierszu znajduje się tekst nagłówka kolumny.
Do zliczania ilości zgłoszeń musimy w argumencie funkcji ILE.NIEPUSTYCH wpisać zakres komórek w kolumnie, która zawsze musi być wypełniona dla każdego zgłoszenia. W opisywanym przykładzie jest to kolumna A.
2. W komórce B3 wpisujemy formułę wyliczającą ilość niepowtarzalnych osób w kolumnie Zgłaszający:
=SUMA.ILOCZYNÓW((Zgłoszenia!$D$2:$D$1000<>"")/(LICZ.JEŻELI(Zgłoszenia!$D$2:$D$1000;Zgłoszenia!$D$2:$D$1000&"")))
Formuły tej możemy zawsze użyć do zliczania niepowtarzalnych wartości w danym zakresie danych bez różnicy czy mamy do czynienia z tekstami, wartościami, czy datami. Funkcja LICZ.JEŻELI dla każdej komórki w zakresie D2:D1000 wylicza ilość takich samych wartości w tym zakresie komórek. W drugim argumencie tej funkcji dodatkowo do wartości komórki dodawany jest pusty ciąg znaków, aby przekształcić wszystkie wartości w zakresie na wartości tekstowe. Przy użyciu funkcji SUMA.ILOCZYNÓW, dla każdej komórki w zakresie D2:D1000, która jest niepusta, wartość 1 jest dzielona na wyliczoną wcześniej ilość takich samych wartości w tym zakresie, a następnie wszystkie te wyniki dzielenia są sumowane. Wynik jest ilością niepowtarzalnych wartości w zakresie D2:D1000 i nie uwzględnia on pustych wartości we wskazanym zakresie komórek.
 
W powyższej formule możemy zmniejszyć lub zwiększyć zakres komórek, gdy przewidujemy, że zestawienie maksymalnie będzie zawierać inną ilość wpisów. Należy jednak pamiętać, że we wszystkich trzech miejscach występowania tego zakresu komórek powinien on być dokładnie taki sam.
3. W komórce B4 wpisujemy podobną formułę, wyliczającą ilość niepowtarzalnych osób w kolumnie Data, zmieniając jedynie trzykrotnie zakres komórek:
=SUMA.ILOCZYNÓW((Zgłoszenia!$B$2:$B$1000<>"")/(LICZ.JEŻELI(Zgłoszenia!$B$2:$B$1000;Zgłoszenia!$B$2:$B$1000&"")))
4. W ten sam sposób w komórce B4 wpisujemy podobną formułę wyliczającą ilość niepowtarzalnych osób w kolumnie Rodzaj zgłoszenia:
=SUMA.ILOCZYNÓW((Zgłoszenia!$F$2:$F$1000<>"")/(LICZ.JEŻELI(Zgłoszenia!$F$2:$F$1000;Zgłoszenia!$F$2:$F$1000&"")))
Po wpisaniu formuł, arkusz zawierający statystyki zgłoszeń przedstawia się tak, jak to pokazano na rysunku 3.
Rys. 3. Arkusz zawierający statystykę dotyczącą zgłoszeń po wpisaniu odpowiednich formuł


Piotr Dynia


Tagi: excel

Zaloguj się, aby dodać komentarz

Nie masz konta? Zarejestruj się »

Zobacz także

Skuteczne narzędzia do wykrywania uszkodzonych podzespołów

pobierz

Wykrywanie i usuwanie niechcianych programów

pobierz

Polecane artykuły

Array ( [docId] => 50065 )

Array ( [docId] => 50065 )
Array ( [docId] => 50065 )