Policz kolorowe komórki w programie Excel (spis treści)
- Wprowadzenie do zliczania kolorowych komórek w programie Excel
- Jak liczyć kolorowe komórki w programie Excel?
Wprowadzenie do zliczania kolorowych komórek w programie Excel
Wszyscy doskonale zdajemy sobie sprawę z tego, że funkcje LICZNIK i LICZ.JEŻELI są tymi, których można użyć do zliczenia komórek za pomocą liczb i komórek spełniających określone kryteria. Czy to nie jest fantazyjne mieć funkcję, która liczy kolorowe komórki? Mam na myśli coś, co liczy różne komórki na podstawie ich koloru? Niestety nie ma takiej funkcji bezpośrednio dostępnej w programie Excel. Możemy jednak użyć kombinacji różnych funkcji, aby osiągnąć to samo. Dzięki wszechstronności Microsoft Excel. Dlatego oto metody, za pomocą których możemy policzyć liczbę kolorowych komórek w programie Excel.
- Korzystanie z formuły SUBTOTAL i filtrów kolorów.
- Używanie COUNTIF i GET.CELL do zliczania kolorowych komórek.
Jak liczyć kolorowe komórki w programie Excel?
Zobaczmy, jak liczyć kolorowe komórki w programie Excel z kilkoma przykładami.
Możesz pobrać ten szablon programu Excel Liczba komórek w tym miejscu - Szablon programu Excel Kolorowe komórkiPrzykład # 1 - Korzystanie z SUBTOTAL formuły i filtrów kolorów
Załóżmy, że mamy dane 1000 kolorowych wierszy, jak pokazano poniżej, a my chcieliśmy tylko znaleźć liczbę każdego koloru w całych danych. Zobacz częściowy układ danych podany poniżej:
Krok 1: Przejdź do komórki F1002, w której możemy użyć formuły SUBTOTAL do przechwycenia sumy częściowej.
Krok 2: Zacznij pisać formułę SUBTOTAL w komórce F1002. Ta formuła zawiera kilka opcji funkcji. Możesz zobaczyć listę, gdy tylko formuła będzie aktywna.
Krok 3: Z listy należy wybrać funkcję numer 102, która jest powiązana z funkcją LICZBA. Przeciągnij myszką w kierunku funkcji 102 i kliknij ją dwukrotnie, aby ją wybrać.
Krok 4: Użyj komórek F1 do F10001 jako tablicy odniesienia, w której funkcja ZLICZANIE zostanie użyta do zliczenia komórek. Napraw odwołanie do komórki za pomocą znaku dolara, jak pokazano na zrzucie ekranu poniżej.
Krok 5: Zamknij nawiasy, aby ukończyć formułę i naciśnij klawisz Enter, aby zobaczyć wynik. Możesz zobaczyć 1000 jako liczbę w komórce F1002.
Krok 6: Teraz zastosuj filtr do wszystkich danych i użyj opcji Filtruj według koloru komórki, aby filtrować dane za pomocą kolorów komórek. Gdy tylko filtrujesz określony kolor, wartość komórki F1002 zmieni się na liczbę komórek, które mają ten konkretny kolor. Zobacz zrzuty ekranu poniżej.
Widać, że jest 419 komórek, które mają ten sam kolor co filtrowany, tj. Jasnopomarańczowy. Podobnie możesz sprawdzić liczbę innych kolorów.
Działa to dobrze, ponieważ jako argument użyto funkcji COUNT (102) w SUBTOTAL. Funkcja COUNT zlicza wszystkie niepuste komórki w danym zakresie, a suma częściowa daje sumę zliczeń. Jeśli zastosujemy filtr według określonych kryteriów, ze względu na naturę SUBTOTAL otrzymamy tylko całkowitą liczbę związaną z tymi kryteriami (w tym przypadku kolor).
Przykład # 2 - Korzystanie z COUNTIF i GET.CELL
Możemy również użyć funkcji COUNTIF i GET.CELL w połączeniu, aby uzyskać liczbę kolorowych komórek. GET.CELL to stara funkcja makra, która nie działa z większością funkcji. Ma jednak zgodność z nazwanymi zakresami. Ta funkcja może wyodrębnić dodatkową informację, której normalna funkcja CELL nie mogła wyodrębnić. Korzystamy z tej funkcji, aby uzyskać informacje o kolorze komórki. Postępuj zgodnie z instrukcjami, aby dowiedzieć się, jak możemy policzyć komórki z kolorem przy użyciu tych dwóch funkcji.
Krok 1: Utwórz nowy nazwany zakres ColorCode za pomocą opcji nazwanych zakresów na karcie Formuły.
Przejdź do zakładki Formuły > w sekcji Zdefiniowane nazwy, kliknij Zdefiniuj nazwę, aby móc utworzyć nazwany zakres.
Krok 2: Pojawi się wyskakujące okienko z „ Nowa nazwa ”. Wspomnij nazwę jako ColorCode, aw sekcji Odnosi się do: użyj formuły as = GET.CELL (38, „Przykład nr 2”! $ A2) . Naciśnij OK po poprawkach. Twój nazwany zakres zostanie uzupełniony i będzie gotowy do użycia.
38 tutaj jest numer operacji powiązany z GET.CELL. Jest to związane z operacją, która przechwytuje unikalne liczby kolorów w programie Excel.
Zmień nazwę kolumny G na ColorCode, w której przechwycimy kody numeryczne powiązane z każdym kolorem. Te kody są unikalne dla każdego koloru.
Krok 3: W komórce G2 zacznij pisać = ColorCode . Jest to nazwany zakres, który właśnie utworzyliśmy w poprzednim kroku. Naciśnij klawisz Enter, aby uzyskać unikalny kod numeryczny powiązany z kolorem komórki A2 (pamiętaj, definiując nazwany zakres, użyliśmy A2 jako odniesienia z arkusza).
40 to kod związany z kolorem różowym w programie Excel.
Krok 4: Przeciągnij i wklej wzór do komórki G3: G1001, a zobaczysz różne liczby, które są niczym innym jak kodami kolorów powiązanymi z każdym kolorem. Zobacz częściowy zrzut ekranu poniżej.
Krok 5: Teraz musimy obliczyć, jaka jest liczba każdego koloru w arkuszu. W tym celu użyjemy funkcji LICZ.JEŻELI. Zacznij pisać = COUNTIF w komórce B1003 danego arkusza programu Excel.
Pamiętaj, że pokolorowaliśmy komórkę 1003: 1006 kolorami, których użyliśmy odpowiednio w naszym przykładzie.
Krok 6: Użyj kolumny G jako argumentu zakresu dla tej funkcji i ColorCode nazwanego zakresu jako kryterium. Uzupełnij formułę, zamykając nawiasy i wyświetl wynik w komórce B1003.
Po użyciu formuły dane wyjściowe pokazano poniżej.
Przeciągnij i wklej tę formułę do pozostałych komórek i zobacz liczbę pozostałych kolorów.
W tym artykule widzieliśmy różne metody, które można zastosować do ustalenia liczby kolorowych komórek. Zapakujmy rzeczy z pewnymi punktami do zapamiętania.
Rzeczy do zapamiętania na temat liczenia kolorowych komórek w programie Excel
- Nie mamy żadnej wbudowanej funkcji, która może zliczyć kolorowe komórki w arkuszu programu Excel. Możemy jednak użyć innej kombinacji formuł, aby uchwycić to samo.
- Funkcja CELL, której używamy w drugim przykładzie, daje więcej informacji związanych z komórką, których nie może dostarczyć ogólna funkcja CELL.
- Kod numeryczny 102, którego używamy w funkcji SUBTOTAL, to pod nim kod numeryczny funkcji COUNT.
Polecane artykuły
Jest to przewodnik na temat liczenia kolorowych komórek w programie Excel. Tutaj omawiamy, jak korzystać z liczenia kolorowych komórek w programie Excel, wraz z praktycznymi przykładami i szablonem programu Excel do pobrania. Możesz także przejrzeć nasze inne sugerowane artykuły -
- Model danych w programie Excel
- Chroń arkusz w programie Excel
- Policz komórki z tekstem w Excelu
- Excel Pokaż formułę