Nadszedł czas na rozbudowanie tematu i przyjrzenie się tematowi sumowania wartości na podstawie koloru dwóch komórek w wierszu. Takie pytanie zadał mi jeden z widzów mojego kanału na YouTube pod filmem o sumowanie komórek według ich koloru (bez użycia makr). Brzmiało ono konkretnie tak: 

Pytanie widza

Czyli, z tego co rozumiem, sytuacja mogłaby wyglądać następująco: mamy tabelę z pracownikami, ich wynagrodzeniem i godzinami pracy. Wynagrodzenie jest zaznaczone kolorem, w zależności od działu, w którym pracują, np.: Magazyn = niebieski, Produkcja = pomarańczowy, Logistyka = zielony. 

Godziny pracy też są zaznaczone kolorem w zależności od zmiany, np.: Pierwsza zmiana = brązowy, Druga zmiana = żółty. 

Formatka

Docelowo chodzi o stworzenie raportu wynagrodzeń pracowników w zależności od działu i zmiany, w jakich pracują. 

Powiem tak: z tak zaprezentowanymi danymi będzie to BARDZO CIĘŻKIE. Dlatego przerobię nieco te dane, a potem zastosuję klasyczne narzędzia, które szybko poradzą sobie z naszą potrzebą. 

Do dzieła! 

1. Przerabianie danych 

Tutaj, tak jak w poprzednim artykule o sumowanie komórek na podstawie ich koloru (bez użycia makr), dodamy nowe kolumny: Zmiana i Dział. 

Następnie do każdego koloru działu i zmiany, przypiszemy ręcznie wartości: 

1. Magazyn = niebieski, Produkcja = pomarańczowy, Logistyka = zielony 
2. 6:00 – 14:00 = 1, 14:00 – 22:00 = 2 

Oczywiście, żeby ułatwić sobie zadanie, polecam posortować dane przed przypisywaniem wartości 😉. 

Efekt jest taki: 

Wartości w nowych kolumnach

Teraz, z tak utworzonymi danymi, stworzenie żądanego raportu to już będzie pikuś 😊. Pokażę Ci 2 metody. 

Metoda 1: SUMA.WARUNKÓW 

Jeśli chcesz, aby raport wynagrodzeń pracowników w zależności od działu i zmiany, w jakich pracują, odświeżał się natychmiastowo po ewentualnych zmianach danych, polecam Ci skorzystać z funkcji do jego stworzenia. 

Jako pierwszy etap tworzenia raportu, przygotowałam ręcznie formatkę, zawierającą dane działów i zmian, które mnie interesują: 

Formatka dla SUMY.WARUNKÓW

W komórkę J5 (i pozostałe komórki zakresu J5:K7) wpiszę teraz następującą formułę: 

=SUMA.WARUNKÓW($E$4:$E$28;  $D$4:$D$28;  $I5;  $F$4:$F$28;  J$4) 

Funkcja SUMA.WARUNKÓW podsumuje dane w zakresie $E$4:$E$28 na postawie danych w odpowiednich wierszach kolumn Dział ($D$4:$D$28) i Zmiana ($F$4:$F$28). 

Efekt jest następujący: 

Wynik dla SUMY.WARUNKÓW

Zauważ, że pisząc powyższą formułę, użyłam wielu kombinacji znaków $. Wszystko po to, abym mogła napisać jedną formułę dla każdej komórki mojego raportu, a nie dwie osobne formuły. Więcej o znakach dolarów ($) i metodach określania jak je wstawiać, znajdziesz w moim kursie podstawowym “Excel w codziennej pracy cz. 1.”. Napisałam też artykuł o adresowaniu komórek tutaj. 

A więcej o funkcji SUMA.WARUNKÓW i jej braciach i siostrach (np. LICZ.WARUNKI, ŚREDNIA.WARUNKÓW, LICZ.JEŻELI) dowiesz się z kursu średniozaawansowanego “Excel w codziennej pracy cz. 2.”.

Metoda 2: Tabela przestawna 

Drugą metodą na stworzenie raportu wynagrodzeń pracowników w zależności od działu i zmiany, w jakich pracują, jest oczywiście tabela przestawna. I to bardzo prosta. Aby ją stworzyć: 

1. Ustaw się w dowolnej komórce zakresu z danymi 
2. Wstawianie → Tabela przestawna 
3. W kreatorze tabeli przestawnej wybierz źródło danych (zakres, w którym stałe(a)ś) i lokalizację. Jeśli chcesz umieścić tabelę przestawną w jakimś konkretnym miejscu, wybierz drugą opcję Istniejący arkusz: 

Kreator tabeli przestawnej

Po zatwierdzeniu otrzymamy na razie pustą tabelę przestawną, czy to:

Pusta tabela przestawna

Teraz należy ją tylko uzupełnić odpowiednimi danymi. W tym celu korzystamy z okienka Pola tabeli przestawnej, metodą drag-and-drop umieszczamy: 

1. pole Dział na polu wierszy, 
2. pole Zmiana na polu kolumn i 
3. pole Wynagrodzenie na polu wartości. 

O tak:

Umieszczanie pól w tabeli przestawnej

Po dodatkowym sformatowaniu wartości, efekt jest następujący: 

Gotowy raport tabeli przestawnej

Tabela przestawna jest o tyle super, że robisz kilka kliknięć myszką i gotowe! A jeśli Ci się nie podoba zaproponowany przeze mnie układ, to możesz go w dowolnej chwili zmienić wedle swojego uznania.  

Świetna, prawda? 

Pamiętaj tylko, że jeśli zmienić dane źródłowe, to tabela przestawna się nie odświeży z automatu. Należy to zrobić ręcznie, czyli kliknąć prawym przyciskiem myszy na tabelę przestawną i wybrać opcję Odśwież: 

Odświeżanie tabeli przestawnej

O tabelach przestawnych opowiadam więcej i to w bardzo ustrukturyzowany sposób w kursie średniozaawansowanym “Excel w codziennej pracy cz. 2.”. Polecam gorąco 😊 

Plik do pobrania:

https://malinowyexcel.pl/wp-content/uploads/2024/09/MalinowyExcel-SumaKolor-po-kilku-kolumnach-DW.xlsx

Film na Youtubie, gdzie pokazuję powyższe rozwiązanie: