Powiedz, że chcesz mieć możliwość zliczania unikatowych elementów z listy, ale z niespodzianką. I powiedz, że pracujesz z tym arkuszem:
Kolumna D zlicza wiersze w każdej sekcji z kolumny B, a kolumna C zlicza liczbę unikalnych sekcji na podstawie pierwszych pięciu znaków kolumny A dla tej sekcji. Komórki B2: B11 zawierają ARG i możesz policzyć osiem unikalnych elementów w pierwszych pięciu znakach A2: A11, ponieważ każdy A7: A9 zawiera 11158, więc dwa duplikaty nie są liczone. Podobnie, 5 w D12 mówi, że istnieje pięć wierszy dla BRD, ale w wierszach 12:16 są trzy unikalne elementy pierwszych pięciu znaków, ponieważ 11145 jest powtarzane, a 11173 jest powtarzane.
Ale jak powiedzieć programowi Excel, aby to zrobił? A jakiej formuły możesz użyć w C2, którą można skopiować do C12 i C17?
Prosta formuła liczenia w D2,, =COUNTIF(B:B,B2)liczy, ile razy B2 (ARG) istnieje w kolumnie B.
Używasz kolumny pomocniczej do wyodrębnienia pierwszych pięciu znaków kolumny A, jak na tym rysunku:
Następnie musisz w jakiś sposób wskazać, że w przypadku ARG interesują Cię tylko komórki F2: F11, aby znaleźć liczbę unikalnych przedmiotów. Ogólnie rzecz biorąc, wartość tę można znaleźć za pomocą formuły tablicowej pokazanej na tym rysunku:
Używasz komórki C3 tymczasowo tylko do pokazania formuły; widać, że nie ma go w C3 na poprzednich rysunkach. (Wkrótce dowiesz się, jak działa ta formuła).
Więc jaki jest wzór w C2, C12 i C17? Zaskakującą (i fajną) odpowiedź przedstawia ten rysunek:
Whoa! Jak to działa?
Spójrz na odpowiedź w nazwach zdefiniowanych na tym rysunku:
To ta sama formuła, co na wcześniejszej figurze, ale zamiast zakresu F2: F11 używa zakresu o nazwie Rg. Ponadto formuła była formułą tablicową, ale nazwane formuły są traktowane tak, jakby były formułami tablicowymi! Oznacza to, że =Answernie jest wprowadzane za pomocą Ctrl + Shift + Enter, ale jest po prostu wpisywane jak zwykle.
Jak więc definiuje się Rg? Jeśli wybrana jest komórka C1 (co jest ważnym krokiem do zrozumienia tej sztuczki), jest zdefiniowana tak, jak na tym rysunku:
To jest =OFFSET(Loan_Details!$F$1,MATCH(Loan_Details!$B1,Loan_Details!$B:$B,0)-1,0,COUNTIF(Loan_Details!$B:$B,Loan_Details!$B1),1).
Loan_Details to nazwa arkusza, ale możesz spojrzeć na tę formułę bez długiej nazwy arkusza. Łatwym sposobem na to jest tymczasowe nazwanie arkusza czymś prostym, na przykład x, a następnie ponowne spojrzenie na zdefiniowaną nazwę:
Ta formuła jest łatwiejsza do odczytania!
Możesz zobaczyć, że ta formuła dopasowuje $ B1 (zwróć uwagę na odniesienie względne do bieżącego wiersza) ze wszystkimi kolumnami B i odejmuje 1. Odejmujesz 1, ponieważ używasz OFFSET od F1. Teraz, gdy wiesz już o wzorze na C, spójrz na wzór na C2:
MATCH($B2,$B:$B,0)Część wzoru jest 2, a więc związek o wzorze (bez odniesienia do nazwy arkusza) stanowi:
=OFFSET($F$1,2-1,0,COUNTIF($B:$B,$B2),1)
lub:
=OFFSET($F$1,1,0,COUNTIF($B:$B,$B2),1)
lub:
=OFFSET($F$1,1,0,10,1)
Ponieważ COUNTIF($B:$B,$B2)jest 10, jest 10 ARG. To jest zakres F2: F11. W rzeczywistości, jeśli wybrana jest komórka C2 i naciśniesz F5, aby przejść do Rg, zobaczysz to:
Jeśli komórką początkową był C12, naciśnięcie klawisza F5, aby przejść do Rg, daje to:
Więc teraz, z odpowiedzią zdefiniowaną jako =SUM(1/COUNTIF(rg,rg)), wszystko gotowe!
Przyjrzyjmy się bliżej, jak działa ta formuła, używając znacznie prostszego przykładu. Zwykle składnia funkcji LICZ.JEŻELI jest =COUNTIF(range,criteria)taka, jak =COUNTIF(C1:C10, "b")na tym rysunku:
Dałoby to 2 jako liczbę b w zakresie. Jednak przekazanie samego zakresu jako kryterium powoduje użycie każdego elementu w zakresie jako kryterium. Jeśli zaznaczysz tę część formuły:
i naciśnij F9, zobaczysz:
Każda pozycja w zakresie jest oceniana, a ta seria liczb oznacza, że jest jedno a i są dwa b, trzy c i cztery d. Te liczby są podzielone na 1, co daje 1, ½, ½, ⅓, ⅓, ⅓, ¼, ¼, ¼, ¼, jak widać tutaj:
Masz więc 2 połówki, 3 trzecie, 4 czwarte i 1 całość, a zsumowanie ich daje 4. Gdyby element został powtórzony 7 razy, miałbyś 7 siódmych i tak dalej. Całkiem fajne! (Czapki z głów przed Davidem Hager'em za odkrycie / wynalezienie tej formuły.)
Ale poczekaj chwilę. W obecnej postaci musisz wprowadzić tę formułę tylko w C2, C12 i C17. Czy nie byłoby lepiej, gdybyś mógł wprowadzić go w C2, wypełnić i wyświetlić tylko we właściwych komórkach? W rzeczywistości możesz to zrobić. Możesz zmodyfikować formułę w C2, aby była =IF(B1B2,Answer,""), a kiedy ją wypełnisz, zrobi to:
Ale po co tu się zatrzymać? Dlaczego nie przekształcić formuły w nazwaną formułę, jak pokazano tutaj:
Aby to zadziałało, komórka C2 musi być komórką aktywną (w przeciwnym razie formuła musiałaby być inna). Teraz możesz zamienić formuły kolumny C na =Answer2:
Widać, że C3 ma =Answer2, podobnie jak wszystkie komórki w kolumnie C. Dlaczego nie kontynuować tego w kolumnie D? Wzór w D2, po zastosowaniu porównania do B1 i B2, pokazano tutaj:
Jeśli więc pozostawisz wybraną komórkę D2 i zdefiniujesz inną formułę, powiedz Odpowiedź3:
następnie możesz wejść =Answer3do komórki D2 i wypełnić:
Oto górna część arkusza roboczego z pokazanymi formułami, a następnie ten sam zrzut ekranu z pokazanymi wartościami:
Kiedy inni ludzie próbują to rozgryźć, na początku mogą podrapać się po głowie!
Ten artykuł gościnny pochodzi od MVP programu Excel, Boba Umlasa. Pochodzi z książki More Excel Outside the Box. Aby zobaczyć inne tematy w książce, kliknij tutaj.
