Home-theater-designers
Zestawy danych w programie Excel często zawierają tę samą wartość wielokrotnie w kolumnie. Czasami warto wiedzieć, ile unikalnych wartości znajduje się w kolumnie. Na przykład, jeśli prowadzisz sklep i masz arkusz kalkulacyjny wszystkich swoich transakcji, możesz chcieć określić, ilu masz unikalnych klientów, zamiast liczyć każdą pojedynczą transakcję.
Można to zrobić, licząc unikatowe wartości w programie Excel, korzystając z metod, które omówimy poniżej.
Usuń zduplikowane dane z kolumny
Szybkim i brudnym sposobem na zliczenie unikalnych wartości w programie Excel jest usunięcie duplikatów i sprawdzenie, ile wpisów zostało. To dobra opcja, jeśli potrzebujesz szybkiej odpowiedzi i nie musisz śledzić wyniku.
Skopiuj dane do nowego arkusza (aby przypadkowo nie usunąć potrzebnych danych). Wybierz wartości lub kolumnę, z której chcesz usunąć zduplikowane wartości. w Narzędzia danych sekcja Dane wybierz zakładkę Usuń duplikaty . Spowoduje to usunięcie wszystkich zduplikowanych danych i pozostawienie tylko unikalnych wartości.
Ten sam proces działa, jeśli informacje są podzielone między dwie kolumny. Różnica polega na tym, że musisz wybrać obie kolumny. W naszym przykładzie mamy kolumnę na imię i drugą na nazwisko.
Jeśli chcesz śledzić liczbę unikalnych wartości, lepiej napisać formułę. Pokażemy Ci, jak to zrobić poniżej.
Powiązane: Jak filtrować w programie Excel, aby wyświetlić żądane dane?
Policz unikalne wartości za pomocą formuły Excel
Aby policzyć tylko unikalne wartości, musimy połączyć kilka funkcji Excela. Najpierw musimy sprawdzić, czy każda wartość jest duplikatem, a następnie policzyć pozostałe wpisy. Musimy również użyć funkcji tablicowej.
Jeśli szukasz tylko odpowiedzi, użyj tej formuły, zastępując każde wystąpienie A2:A13 komórkami, których chcesz użyć:
{=SUM(IF(FREQUENCY(MATCH(A2:A13, A2:A13, 0), MATCH(A2:A13, A2:A13, 0)) >0, 1))}Sposób, w jaki się tam dostaliśmy, jest trochę skomplikowany. Jeśli więc chcesz zrozumieć, dlaczego ta formuła działa, podzielimy ją na części poniżej.
Wyjaśnienie funkcji tablicy
Zacznijmy od wyjaśnienia, czym jest tablica. Tablica to pojedyncza zmienna, która przechowuje wiele wartości. To tak, jakby odwoływać się do kilku komórek Excela naraz, zamiast odwoływać się do każdej komórki osobno.
To dziwne rozróżnienie z naszego punktu widzenia. Jeśli powiemy formule, aby przeglądała komórki A2:A13 normalnie lub jako tablicę, dane wyglądają dla nas tak samo. Różnica polega na tym, jak program Excel traktuje dane zakulisowo. Jest to tak subtelna różnica, że najnowsze wersje programu Excel już ich nie rozróżniają, chociaż starsze wersje już tak.
Dla naszych celów ważniejsza jest wiedza, w jaki sposób możemy używać tablic. Jeśli masz najnowszą wersję programu Excel, automatycznie przechowuje dane jako tablicę, gdy jest to bardziej wydajne. Jeśli masz starszą wersję, po zakończeniu pisania formuły naciśnij Ctrl + Shift + Enter . Gdy to zrobisz, formuła zostanie otoczona nawiasami klamrowymi, aby pokazać, że jest w trybie tablicy.
Przedstawiamy funkcję CZĘSTOTLIWOŚCI
Funkcja CZĘSTOTLIWOŚĆ mówi nam, ile razy liczba pojawia się na liście. To jest świetne, jeśli pracujesz z liczbami, ale nasza lista jest tekstowa. Aby skorzystać z tej funkcji, musimy najpierw znaleźć sposób na przekonwertowanie naszego tekstu na liczby.
Jeśli próbujesz policzyć unikalne wartości na liście liczb, możesz pominąć następny krok.
Korzystanie z funkcji DOPASUJ
Funkcja PODAJ.POZYCJĘ zwraca pozycję pierwszego wystąpienia wartości. Możemy to wykorzystać do przekonwertowania naszej listy nazwisk na wartości liczbowe. Musi znać trzy informacje:
- Jakiej wartości szukasz?
- Jaki zbiór danych sprawdzasz?
- Szukasz wartości wyższych, niższych lub równych wartości docelowej?
W naszym przykładzie chcemy wyszukać każdą nazwę naszych klientów w naszym arkuszu kalkulacyjnym Exel, aby sprawdzić, czy ich dokładna nazwa pojawia się ponownie w innym miejscu.
jak sprawić, by komputer Ci czytał
W powyższym przykładzie przeszukujemy naszą listę (A2:A13) pod kątem Tiah Gallagher (A2) i chcemy uzyskać dokładne dopasowanie. 0 w ostatnim polu oznacza, że musi to być dokładne dopasowanie. Nasz wynik mówi nam, gdzie na liście imię pojawiło się jako pierwsze. W tym przypadku było to imię, więc wynik to 1.
Problem polega na tym, że interesują nas wszyscy nasi klienci, nie tylko Tiah. Ale jeśli spróbujemy wyszukać A2:A13 zamiast tylko A2, otrzymamy błąd. W tym miejscu przydatne są funkcje tablicowe. Pierwszy parametr może przyjmować tylko jedną zmienną, w przeciwnym razie zwraca błąd. Ale tablice są traktowane tak, jakby były pojedynczą zmienną.
Teraz nasza funkcja mówi Excelowi, aby sprawdził dopasowania dla całej naszej tablicy. Ale czekaj, nasz wynik się nie zmienił! Nadal jest napisane 1. Co tu się dzieje?
Nasza funkcja zwraca tablicę. Przechodzi przez każdy element w naszej tablicy i sprawdza dopasowanie. Wyniki wszystkich nazw są zapisywane w tablicy, która jest zwracana jako wynik. Ponieważ komórka pokazuje tylko jedną zmienną na raz, pokazuje pierwszą wartość w tablicy.
Możesz to sprawdzić sam. Jeśli zmienisz pierwszy zakres na A3:A13, wynik zmieni się na 2. Jest tak dlatego, że imię Eiliyah jest drugie na liście, a ta wartość jest teraz zapisana jako pierwsza w tablicy. Jeśli zmienisz pierwszy zakres na A7:A13, otrzymasz ponownie 1, ponieważ imię Tiah pojawia się na pierwszej pozycji sprawdzanego zestawu danych.
Związane z: Formuły Excela, które pomogą Ci rozwiązać problemy w prawdziwym życiu
Korzystanie z funkcji CZĘSTOTLIWOŚĆ
Teraz, gdy zmieniliśmy nazwy na wartości liczbowe, możemy użyć funkcji CZĘSTOTLIWOŚĆ. Podobnie jak MATCH, wymaga wyszukania celu i zestawu danych do sprawdzenia. Podobnie jak w przypadku MATCH, nie chcemy szukać tylko jednej wartości, chcemy, aby funkcja sprawdzała każdą pozycję na naszej liście.
Celem, który ma sprawdzać funkcja CZĘSTOTLIWOŚĆ, jest każdy element w tablicy, który zwróciła nasza funkcja PODAJ.POZYCJĘ. I chcemy sprawdzić zestaw danych zwrócony przez funkcję PODAJ.POZYCJĘ. W związku z tym wysyłamy funkcję MATCH, którą stworzyliśmy powyżej dla obu parametrów.
Jeśli szukasz unikalnych liczb i pominiesz poprzedni krok, wyślij zakres liczb jako oba parametry. Aby przeszukać wszystkie liczby na liście, musisz również użyć funkcji tablicowej, więc pamiętaj, aby nacisnąć Ctrl + Shift + Enter po wprowadzeniu formuły, jeśli używasz starszej wersji programu Excel.
Teraz nasz wynik to 2. Znowu nasza funkcja zwraca tablicę. Zwraca tablicę pokazującą, ile razy pojawiła się każda unikalna wartość. Komórka pokazuje pierwszą wartość w tablicy. W tym przypadku imię Tiah pojawia się dwukrotnie, więc zwracana częstotliwość to 2.
Korzystanie z funkcji JEŻELI
Teraz nasza tablica ma taką samą liczbę wartości, jak my mamy unikalne wartości. Ale jeszcze nie skończyliśmy. Potrzebujemy sposobu, aby to zsumować. Jeśli przekonwertujemy wszystkie wartości w tablicy na 1 i zsumujemy je, w końcu dowiemy się, ile mamy unikalnych wartości.
Możemy utworzyć funkcję JEŻELI, która zmienia wszystkie wartości powyżej zera na 1. Wtedy wszystkie wartości będą równe 1.
Aby to zrobić, chcemy, aby nasza funkcja IF sprawdzała, czy wartości w naszej tablicy CZĘSTOTLIWOŚCI są większe od zera. Jeśli prawda, powinna zwrócić wartość 1. Zauważysz, że teraz pierwsza wartość w tablicy zostanie zwrócona jako jeden.
Korzystanie z funkcji SUMA
Jesteśmy w końcowej fazie! Ostatnim krokiem jest SUM tablicę.
Zawijaj poprzednią funkcję w funkcję SUMA. Skończone! Więc nasza ostateczna formuła to:
{=SUM(IF(FREQUENCY(MATCH(A2:A13, A2:A13, 0), MATCH(A2:A13, A2:A13, 0)) >0, 1))}Zliczanie unikalnych wpisów w Excelu
To zaawansowana funkcja, która wymaga dużej wiedzy na temat Excela. Próba może być onieśmielająca. Ale po skonfigurowaniu może być bardzo pomocne, więc warto przeanalizować nasze wyjaśnienie, aby upewnić się, że je rozumiesz.
Jeśli nie musisz tak często liczyć unikalnych wpisów, szybka i brudna wskazówka dotycząca usuwania zduplikowanych wartości zadziała w mgnieniu oka!
Udział Udział Ćwierkać E-mail Jak kopiować formuły w programie Microsoft ExcelPoznanie wszystkich najlepszych metod kopiowania i wklejania formuł w arkuszu kalkulacyjnym Excel to świetny sposób na zaoszczędzenie czasu.
Czytaj dalej Powiązane tematy- Wydajność
- Arkusz
- Microsoft Excel
- Analiza danych
J. Seaton jest pisarzem naukowym, który specjalizuje się w rozwiązywaniu złożonych tematów. Posiada tytuł doktora na Uniwersytecie Saskatchewan; jej badania koncentrowały się na wykorzystaniu nauki opartej na grach w celu zwiększenia zaangażowania uczniów w Internecie. Kiedy nie pracuje, znajdziesz ją z jej czytaniem, graniem w gry wideo lub ogrodnictwem.
Więcej od Jennifer SeatonZapisz się do naszego newslettera
Dołącz do naszego newslettera, aby otrzymywać porady techniczne, recenzje, bezpłatne e-booki i ekskluzywne oferty!
Kliknij tutaj, aby zasubskrybować