W poprzednim artykule opisywałam funkcję, która potrafi podsumować wartości komórek na podstawie koloru ich wypełnienia. Funkcja ta została stworzona w języku VBA, czyli krótko mówiąc: za pomocą makra.
W tym artykule pokażę Ci dwa inne sposób – oba bez użycia makr.
Sytuacja jest zatem taka sama: mamy prostą tabelę z kolumną z wartościami, które chcemy podsumować na podstawie ich koloru:
Formatka
Metoda 1: autofiltr i funkcja SUMY.CZĘŚCIOWE
Pierwsza metoda wykorzystuje, jak już wspomniałam: autofiltr i funkcję SUMY.CZĘŚCIOWE. Jest trochę kombinowana, ale każdy da sobie z nią radę.
Po pierwsze: należy założyć filtr na dane i przefiltrować komórki wg koloru, który chcesz sumować:
Suma po kolorach w autofilrze
Jak już masz przefiltrowaną tabelę, to wstawiasz autosumę (Alt + =), która wygeneruje funkcję SUMY.CZĘŚCIOWE i otrzymujesz to:
Autosuma na przefiltrowanej tabeli
Bardzo prosta metoda, tylko ma sporą wadę: nie zrobimy z niej automatycznego podsumowania, że kolor zielony to wartość X, kolor czerwony to Y itd. Takie podsumowanie moglibyśmy stworzyć, gdyby do naszej tabeli dorobić kolumnę, w które opisalibyśmy, że zielony kolor to X, a czerwony to Y…
Metoda 2: kolumna pomocnicza i SUMA.JEŻELI
Tutaj posłużymy się właśnie kolumną pomocniczą, np. Opis. Będziemy w niej wpisywać konkretne opisy, mówiące o znaczeniu koloru. Np. kolor zielony mógłby oznaczać, akceptację, pomarańczowy – sprawę w toku, a czerwony – porażkę. Czy cokolwiek innego 😊.
Ja dla uproszczenia zadania po prostu będę wpisywała nazwy tych kolorów, czyli u nas: zielony i różowy.
Aby ułatwić sobie przyporządkowywanie opisów do kolorów, warto posortować nasze dane. W tym celu:
- Ustaw się w zakresie danych, który chcesz sortować
- Dane → Sortuj
- Wybierz sortowanie według kolorów, jak na obrazku:
Sortowanie po kolorach
Efekt będzie następujący:
Wynik sortowania po kolorach
To teraz stworzymy dodatkową kolumnę w tabeli. Ja nazwę ją Opis i wpiszę do niej odpowiednie kolory. O tak:
Uzupełnione opisy kolorów
Kiedy już to mamy, śmiało możemy dokonywać podsumowania.
Mamy na to co najmniej kilka metod. Przykładowo możemy użyć tutaj funkcji SUMA.JEŻELI czy też SUMA.WARUNKÓW, albo choćby tabeli przestawnej. Teraz to już do wyboru, do koloru 😉.
Sumować będziemy na podstawie opisów w kolumnie Opis, a nie na podstawie koloru komórki. Efekt będzie oczywiście ten sam i o to nam chodzi.
Do obu wyżej wymienionych funkcji potrzebujemy wpisać treść użytych przez nas opisów w miejsce, gdzie chcemy dokonać podsumowania. U mnie to będą komórki H5 i H7. O tak:
Tworzenie podsumowania
I teraz, najlepiej w komórkach obok tych opisów, wpiszmy formuły:
=SUMA.JEŻELI(F:F;H5;E:E)
albo:
=SUMA.WARUNKÓW(E:E;F:F;H5)
Obie funkcje dadzą taki sam wynik:
Wynik
To rozwiązanie jest o tyle ciekawe, że daje nam możliwość zrobienia jeszcze kolejnej automatyzacji: na podstawie opisu w kolumnie Opis, możemy automatycznie nadać odpowiedni kolor komórce z wartością PLN. Możemy to zrobić za pomocą formatowania warunkowego. Wtedy oczywiście ręczne kolorowanie komórek już nie będzie potrzebne. Metodę tę pokazuję na filmie poniżej:
Plik do pobrania:
https://malinowyexcel.pl/wp-content/uploads/2024/09/MalinowyExcel-SumaKolor-bez-makr.xlsx
0 Comments