Suma według koloru w programie Excel

Jak sumować według koloru w programie Excel? (2 przydatne metody)

Najlepsze metody 2 do sumowania według kolorów w programie Excel są następujące -

  1. Użycie formuły SUMA CZĘŚCIOWA w programie Excel i funkcji filtrowania według koloru.
  2. Stosowanie formuły GET.CELL poprzez zdefiniowanie nazwy na karcie formuły i zastosowanie formuły SUMA.JEŻELI w programie Excel w celu podsumowania wartości według kodów kolorów.

Omówmy szczegółowo każdy z nich -

Możesz pobrać tę sumę według szablonu programu Excel w kolorze tutaj - Suma według szablonu programu Excel w kolorze

# 1 - Suma według koloru za pomocą funkcji sumy częściowej

Aby zrozumieć podejście do obliczania sumy wartości według kolorów tła, rozważmy poniższą tabelę danych, która zawiera szczegółowe informacje o kwotach w dolarach amerykańskich według regionu i miesiąca.

  • Krok 1:  Załóżmy, że chcielibyśmy podświetlić te komórki, które mają ujemne wartości w celu wskazania, można to osiągnąć, stosując formatowanie warunkowe lub ręcznie podświetlając komórki, jak pokazano poniżej.

  • Krok 2: Teraz, aby uzyskać sumę komórek pokolorowanych w programie Excel, wprowadź formułę SUMA CZĘŚCIOWA poniżej tabeli danych. Poniżej przedstawiono składnię formuły SUMA CZĘŚCIOWA .

Formuła wprowadzana do obliczenia sumowania to

= SUMA CZĘŚCIOWA (9; D2: D13)

W tym przypadku liczba „9” w argumencie nr_funkcji odnosi się do sumarycznej funkcjonalności, a argument referencyjny jest podany jako zakres komórek do obliczenia. Poniżej znajduje się zrzut ekranu tego samego.

  • Krok 3: Jak widać na powyższym zrzucie ekranu, obliczono sumę kwoty w USD. W celu obliczenia kwot zaznaczono jasnoczerwonym kolorem tła. Zastosuj filtr do tabeli danych, przechodząc do opcji Dane, a następnie wybierając filtr.

  • Krok 4: Następnie wybierz filtr według koloru i wybierz jasnoczerwony kolor komórki w sekcji „Filtruj według koloru komórki”. Poniżej znajduje się zrzut ekranu, aby lepiej opisać filtr.

  • Krok 5: Po zastosowaniu filtru programu Excel tabela danych zostanie przefiltrowana tylko pod kątem jasnoczerwonych komórek tła, a wzór sumy częściowej zastosowany na dole tabeli danych wyświetli sumę kolorowych komórek, które są filtrowane, jak pokazano poniżej.

Jak pokazano na powyższym zrzucie ekranu, obliczenia kolorowej komórki są wykonywane w komórce E17, formuła sumy częściowej.

# 2 - Suma według koloru za pomocą funkcji Get.Cell

Wyjaśniono, że drugie podejście prowadzi do sumy komórek koloru w programie Excel i jest omówione w poniższym przykładzie. Rozważ tabelę danych, aby lepiej zrozumieć metodologię.

  • Krok 1: Teraz zaznaczmy listę komórek w kolumnie Kwota w USD, które jesteśmy skłonni uzyskać żądaną sumę kolorowych komórek, jak pokazano poniżej.

  • Krok 2: Jak widać na powyższym zrzucie ekranu, w przeciwieństwie do pierwszego przykładu tutaj mamy wiele kolorów. W związku z tym będziemy używać formuły = GET.CELL , definiując ją w polu nazwy, a nie bezpośrednio używając jej w programie Excel.
  • Krok 3: Teraz, gdy pojawi się okno dialogowe „Zdefiniuj nazwę”, wprowadź nazwę i formułę dla GET.CELL w „Odnieś się do”, jak pokazano na poniższym zrzucie ekranu.

Jak widać na powyższym zrzucie ekranu, nazwa wprowadzona dla funkcji to „CellColor”, a formuła = GET.CELL (38, „Przykład 2! $ D2) należy wprowadzić w polu„ Odnosi się do ”. W formule liczba 38 odnosi się do informacji o kodzie komórki, a drugi argument to numer komórki D2 odnosi się do komórki odniesienia. Teraz kliknij OK.

  • Krok 4: Teraz wprowadź funkcję Nazwa `` CellColor '' w komórce obok komórki oznaczonej kolorem, która została zdefiniowana w oknie dialogowym, jak wyjaśniono w kroku 3.

Jak widać na powyższym zrzucie ekranu, wprowadzana jest funkcja „CellColor”, która zwraca kod koloru dla koloru komórki tła.

Podobnie formuła jest przeciągana do całej kolumny.

  • Krok 5:  Teraz, aby uzyskać sumę wartości według kolorów w programie Excel, wprowadzimy formułę SUMA.JEŻELI. Składnia formuły SUMA.JEŻELI jest następująca: -

Jak widać na powyższym zrzucie ekranu, do formuły SUMA.JEŻELI wpisujemy następujące argumenty: -

  • Argument zakresu jest wprowadzany dla zakresu komórek E2: E13
  • Kryteria są wprowadzane jako G2, którego zsumowane wartości są potrzebne do pobrania.
  • Wprowadzono zakres komórek jako D2: D13

Formuła SUMA.JEŻELI jest przeciągnięta w dół dla wszystkich numerów kodów kolorów, dla których mają zostać dodane wartości.

Rzeczy do zapamiętania

  • Korzystając z podejścia opartego na formule SUBTOTAL, ta funkcjonalność umożliwia użytkownikom filtrowanie tylko dla jednego koloru filtru na raz. Ponadto ta funkcja umożliwia dodanie tylko jednej kolumny wartości według kolorów filtrów. Jeśli istnieje więcej niż jedna kolumna z różnymi kolorami według wierszy w różnych kolumnach, SUMA CZĘŚCIOWA może pokazywać poprawny wynik tylko dla jednego filtru według koloru tylko w określonej kolumnie.
  • Formuła GET.CELL w połączeniu z podejściem SUMIF eliminuje to ograniczenie, ponieważ ta funkcja może być używana do podsumowania według kolorów dla wielu kodów kolorów w tle komórki.