Arkusze kalkulacyjne są jednym z najbardziej wydajnych i łatwych w użyciu sposobów organizowania, przeglądania i manipulowania wszelkiego rodzaju danymi. Jednym z najczęstszych zadań wykonywanych przez ludzi za pomocą arkuszy kalkulacyjnych takich jak Microsoft Excel jest analiza zestawów danych. Często arkusz kalkulacyjny może zawierać zduplikowane dane, to znaczy wiersz lub komórkę, która duplikuje inny wiersz lub komórkę. Czasami chcemy usunąć te duplikaty i stworzyliśmy artykuł na temat usuwania duplikatów w programie Excel, aby nauczyć się, jak to zrobić. Czasami jednak nie chcemy zmieniać danych, ale chcemy na przykład wiedzieć, ile razy dana wartość jest duplikowana w naszym zestawie danych. W tym artykule poznasz kilka różnych sposobów liczenia duplikatów w arkuszach kalkulacyjnych Excel.
Funkcja COUNTIF
COUNTIF jest niewątpliwie jedną z bardziej zaawansowanych i wygodnych funkcji statystycznych Excela. Funkcja LICZ.JEŻELI polega na znalezieniu całkowitej liczby komórek w wybranym zakresie, które spełniają określone kryteria. Na przykład możesz poprosić COUNTIF o informację, ile komórek w kolumnie D zawiera frazę „Excel jest groovy”. Składnia tej funkcji programu Excel to: = LICZ.JEŻELI (zakres, kryteria) . Zakres to komórki, w których chcesz wyszukać kryteria, kryteria są tym, co funkcja ma liczyć. Jak więc używamy COUNTIF do liczenia zduplikowanych wartości?
Najpierw wprowadź dane zastępcze do pustego arkusza kalkulacyjnego Excel. Wprowadź wartości „45”, „252”, „52”, „45”, „252” i „45” w komórkach A2: A7. Następnie arkusz kalkulacyjny powinien pasować do pokazanego bezpośrednio poniżej.
Załóżmy teraz, że musisz dowiedzieć się, ile komórek zawiera zduplikowaną wartość 45. Funkcja COUNTIF powie ci to w mgnieniu oka! Wybierz komórkę A9, a następnie naciśnij przycisk FX . Wybierz COUNTIF i naciśnij OK, aby otworzyć okno pokazane bezpośrednio poniżej. (Jeśli nie masz doświadczenia z formułami programu Excel, możesz po prostu wpisać formułę bezpośrednio w komórce bez użycia okien dialogowych).
Kliknij przycisk Zakres i wybierz zakres komórek A2: A9. Następnie wpisz „45” w polu tekstowym Kryteria. Naciśnij OK, aby zamknąć okno. A9 zwróci teraz wynik formuły 3. Zatem w wybranym zakresie są trzy komórki, które zawierają wartość 45.
Funkcja działa tak samo dla tekstu. Jako przykład wpisz „gruszka”, „jabłko”, „jabłko” i „pomarańcza” w komórkach A11: 14. Następnie arkusz kalkulacyjny powinien zawierać krótką listę owoców, jak pokazano bezpośrednio poniżej.
Wybierz, aby dodać funkcję COUNTIF do komórki A16. Naciśnij przycisk FX , wybierz CountIF i kliknij OK . Tym razem wybierz zakres A11: 14 jako zakres. Wpisz „jabłko” w polu tekstowym Kryteria, jak pokazano bezpośrednio poniżej.
Teraz, gdy naciśniesz OK , A16 powinien zwrócić wartość 2. Zatem istnieją dwie komórki, które zawierają duplikaty jabłek. Pamiętaj, że komórki w wybranym zakresie nie mogą zawierać spacji. Jeśli tak, program Excel nie liczy ich jako duplikatów (chyba że wprowadzone kryteria obejmują również dokładnie takie same puste miejsca). Ten przewodnik Tech Junkie zawiera informacje na temat usuwania pustych spacji z komórek arkusza kalkulacyjnego Excel.
Policz wiele zduplikowanych wartości
Ale co, jeśli trzeba znaleźć całkowitą liczbę duplikatów dla dwóch, trzech lub więcej wartości? Na przykład może być konieczne ustalenie, ile razy trzy zestawy wartości są duplikowane w zakresie komórek. W takim przypadku możesz rozwinąć funkcję LICZ.JEŻELI, aby obejmowała wiele kryteriów.
Wybierz A9 w arkuszu kalkulacyjnym Excel. Następnie kliknij pasek efektów, aby edytować oryginalną funkcję. Dodaj „+ COUNTIF (A2: A7, 252)” do funkcji i naciśnij klawisz Enter.
Pełna funkcja stanie się wtedy efektywnie = LICZ.JEŻELI (A2: A7, 45) + LICZ.JEŻELI (A2: A7, 252), jak pokazano na zdjęciu bezpośrednio poniżej. A9 zwróci wówczas wartość 5. Jako taka funkcja zsumowała zarówno 45, jak i 252 duplikatów w naszym zakresie komórek, co wynosi 5.
Ta funkcja może również zliczać wartości w zakresach komórek w wielu arkuszach kalkulacyjnych Excel. Wymagałoby to modyfikacji wymaganych zakresów komórek, aby zawierały odwołanie do arkusza, takie jak Arkusz2! Lub Arkusz3 !, w odwołaniu do komórki. Na przykład, aby uwzględnić zakres komórek w Arkuszu 3, funkcja mogłaby wyglądać następująco: = COUNTIF (A2: A7, 45) + COUNTIF (Arkusz3! C3: C8, 252).
Policz wszystkie zduplikowane wartości w kolumnie lub wierszu
Niektórzy użytkownicy programu Excel mogą potrzebować policzyć wszystkie zduplikowane wartości lub elementy w kolumnie arkusza kalkulacyjnego. Możesz to również zrobić za pomocą funkcji LICZ.JEŻELI. Jednak funkcja wymaga bezwzględnego odwołania do komórki dla całej kolumny, w której należy policzyć wszystkie duplikaty.
Kliknij komórkę B2 we własnym arkuszu kalkulacyjnym Excel. Kliknij przycisk fx i wybierz, aby otworzyć okno Argumenty funkcji COUNTIF. Wpisz „$ A $ 2: $ A $ 7” w polu Zakres. Wpisz „$ A2” w polu Kryteria i naciśnij przycisk OK , aby dodać funkcję do arkusza kalkulacyjnego. Komórka B2 zwróci wartość 3, jak pokazano poniżej.
Teraz musisz skopiować funkcję do wszystkich komórek poniżej, aż do A7. Wybierz B2, kliknij lewym przyciskiem myszy prawy dolny róg komórki i przeciągnij go w dół do A7. To kopiuje funkcję do wszystkich pozostałych komórek, jak pokazano bezpośrednio poniżej.
Kolumna B w powyższym ujęciu skutecznie liczy teraz wszystkie wartości z zakresu A2: A7. Podkreśla, że 45 razy powiela się trzy razy i że 252 powiela się dwukrotnie w wybranym zakresie. Teraz możesz szybko znaleźć wszystkie powtarzane wartości w kolumnach lub wierszach arkusza kalkulacyjnego, włączając bezwzględne odwołania do komórek w funkcji COUNTIF.
Teraz możesz policzyć dowolną liczbę zduplikowanych wartości lub elementów w arkuszach kalkulacyjnych Excel za pomocą COUNTIF. Otwórz tę stronę YouTube, aby zobaczyć, jak działa funkcja LICZ.JEŻELI.
Znasz inne fajne wskazówki i techniki Excela? Udostępnij je nam poniżej!
