Jak liczyć komórki za pomocą koloru w programie Excel?
Istnieje wiele sposobów zliczania komórek na podstawie koloru komórki w programie Excel.
- Metoda nr 1 - Policz komórki z kolorem przy użyciu metody filtrowania z funkcją sumy częściowej
- Metoda nr 2 - Policz komórki z kolorem, tworząc funkcję za pomocą kodu VBA
Omówmy teraz szczegółowo każdą z metod wraz z przykładem -
# 1 Korzystanie z metody filtrowania z funkcją sumy częściowej
Jak powiedziałem, mamy wiele metod liczenia komórek na podstawie koloru komórki w programie Excel. Ta metoda wykorzystuje funkcję Filter & Subtotal.
- Krok 1: Najpierw musimy zastosować funkcję sumy częściowej, więc na końcu danych otwórz funkcję SUMA CZĘŚCIOWA w programie Excel.
- Krok 2: Po otwarciu funkcji SUMA CZĘŚCIOWA otrzymujemy pod tym wykaz funkcji; ponieważ liczymy komórki, wybieramy funkcję „ 2 - COUNT ”.
- Krok 3: Po wybraniu następnie metody sumy częściowej musimy wybrać zakres komórek do zliczenia.
- Krok 4: OK, teraz liczba tych komórek wyniesie 7.
- Krok 5: W przypadku powyższych danych struktura stosuje opcję filtra.
- Krok 6: Kliknij listę rozwijaną filtrów, aby przejść do „ Filtruj według koloru”. Wyświetli wszystkie dostępne kolory do filtrowania.
- Krok 7: Wybierz dowolny kolor, a my będziemy mieć te kolorowe rzędy.
- Krok 8: Jak widać powyżej, nasza funkcja SUMA CZĘŚCIOWA pokazuje liczbę tylko przefiltrowanych wierszy. Ponieważ są tylko dwa wiersze oznaczone kolorem pomarańczowym, funkcja SUMA CZĘŚCIOWA wyświetla liczbę tylko jako trzy; podobnie wybierz inny kolor i zobacz, czy działa idealnie, czy nie.
Liczba ta wynosi 2, więc mamy zaktualizowany wynik.
# 2 Utwórz funkcję za pomocą kodowania VBA
Jak już powiedziałem, niestety nie mamy żadnej wbudowanej funkcji, która może pomóc nam zliczać komórki na podstawie koloru w programie Excel. Powiedzieliśmy, że nie ma wbudowanej funkcji, ale piękno polega na tym, że możemy stworzyć własną funkcję za pomocą kodowania VBA.
Poniżej znajduje się kod, który już napisałem, aby utworzyć funkcję zliczania komórek na podstawie koloru.
Kod:
Funkcja ColorCount (ColorCell As Range, DataRange As Range) Dim Data_Range As Range Dim Cell_Color As Long Cell_Color = ColorCell.Interior.ColorIndex For Each Data_Range In DataRange If Data_Range.Interior.ColorIndex = Cell_Color Then ColorCount = ColorCount + 1 End If Next Data_Range End Funkcjonować
Wszystko, co musisz zrobić, to skopiować powyższy kod i wkleić go do modułu VBA, jak pokazano poniżej.
Teraz wróć do arkusza roboczego, w którym mamy dane i utwórz trzy komórki z kolorem.
Umieść znak równości i otwórz funkcję, którą stworzyliśmy za pomocą powyższego kodu.
Pierwszym argumentem tej funkcji jest „musimy wybrać kolorową komórkę, którą musimy policzyć”, więc w tym przypadku wybiorę komórkę E2.
Drugi argument dotyczy tego, w jakim zakresie komórek musimy policzyć wybrany kolor komórki.
OK, to wszystko, czego potrzebujemy do tej funkcji. Zamknij nawias i naciśnij klawisz Enter, aby uzyskać liczbę wybranego koloru komórki.
Podobnie zrób dla innych kolorowych komórek. Otrzymujemy następujący wynik.
Odbywa się to za pomocą „ funkcji zdefiniowanej przez użytkownika” języka VBA. Zapętla się ona przez podany zakres komórek i zlicza liczbę komórek, które mają dokładnie ten sam kolor, co podany kolor komórki.
Następnie zsumuje wszystkie pasujące komórki kolorów i zwróci wynik jako liczbę komórek w tym kolorze.
W ten sposób możemy liczyć komórki z kolorami w programie Excel. Istnieją również inne metody, ale są one nieco skomplikowane, więc na początku nauki wybierz prostszą.
O czym należy pamiętać
- Funkcja SUMA CZĘŚCIOWA zlicza tylko komórki, które są widoczne, więc zastosowany filtr zlicza tylko te przefiltrowane kolorowe komórki.
- Kod VBA jest przeznaczony dla funkcji UDF, ponieważ nie ma wbudowanych funkcji, więc musimy stworzyć własne.
