Teraz przyjmijmy, że tabela zawiera dane na temat wielkości produkcji różnych wyrobów cukierniczych, nie tylko pączków, produkowanych przez cukiernię, w której pracujesz w weekendy. Podzielone są na kwartały. Chcesz poddać dane analizie i utworzyć różne sumy wielkości produkcji wybranych produktów. Na pierwszy rzut oka właściwe wydaje się użycie funkcji SUMA.JEŻELI(), jednak funkcja ta ma poważne ograniczenie: możesz wprowadzić tylko jeden warunek/kryterium sumowania. Wykorzystaj więc często zapominaną funkcję BD.SUMA().
Plik z przykładowymi danymi do pobrania, znajduje się niżej.
W tym celu:
Przygotuj w pustym miejscu arkusza niewielką tabelę stanowiącą kryteria do obliczeń.
W wybranej komórce podaj tytuł kolumny z produktami, czyli Produkt.
Poniżej wypisz nazwy produktów, które chcesz uwzględnić w analizie.
W sąsiedniej komórce wprowadź odniesienie do kwartału, dla którego chcesz obliczać sumy. Przygotowana tabela może wyglądać jak na filmie.
Aby wyznaczyć sumy produkcji wybranych wyrobów w pierwszym kwartale, zastosuj w komórce H2 następującą formułę:
Linia 1. znak równości BD kropka SUMA otwórz nawias okrągły A1 dwukropek E8 średnik cudzysłów I kwartał cudzysłów średnik G1 dwukropek G5 zamknij nawias okrągły.
=BD.SUMA(A1:E8;"I kwartał";G1:G5)
lub jej wersję z odniesieniem do komórki z kryterium:
Wystarczy teraz zmienić wpis w komórce H1 na inny kwartał, a natychmiast otrzymasz sumę wielkości wybranych produktów dla określonego kwartału. Na filmie widać działanie formuły w praktyce.
RDmXM5yoS7td9
Nagranie filmowe dotyczące sumowania warunkowego z wieloma kryteriami.
Nagranie filmowe dotyczące sumowania warunkowego z wieloma kryteriami.
Źródło: Contentplus.pl Sp. z o.o., licencja: CC BY-SA 3.0.
Restauracja zatrudnia 7 studentów do lepienia pierogów. Każdy student pomaga w inny dzień tygodnia i każdy z nich ma obowiązek zapisywania pod koniec pracy, ile pierogów ulepił do uproszczonego 28‑dniowego kalendarza, gdzie dni tygodnia to wiersze, a kolumny to kolejne 4 tygodnie. Od następnego miesiąca kucharz chce płacić studentom pracującym w poniedziałki i piątki 62 grosze za każdego ulepionego pieroga, zamiast 50 groszy jak dotychczas. Średnio o ile więcej pieniędzy tygodniowo będzie musiał wydać kucharz na poniedziałkową i piątkową pracę, jeśli będzie szacować na podstawie swojego kalendarza? Przygotuj arkusz przedstawiający jeden miesiąc kalendarza i użyj znanych już tobie funkcji, by znaleźć odpowiedź.
Przykładowe rozwiązanie zadania:
R10zF2fRDQEWa
Na zrzucie ekranu widoczny jest fragment arkusza Excel. W kolumnach A, B, C, D i E wprowadzono dane dotyczące ilości ulepionych pierogów dziennie przez studentów w czterech kolejnych tygodniach. W arkuszu kolejno dodano opisy: w komórce A1 Dzień tygodnia, w komórce B1 Pierwszy tydzień, w komórce C1 Drugi tydzień, w komórce D1 Trzeci tydzień, w komórce E1 Czwarty tydzień. W kolumnie A, w komórkach od A2 do A8 wpisano nazwy dni tygodnia. W kolumnie B, w komórkach od B2 do B8 wpisano ilość pierogów w pierwszym tygodniu. W kolumnie C, w komórkach od C2 do C8 wpisano ilość pierogów w drugim tygodniu. W kolumnie D, w komórkach od D2 do D8 wpisano ilość pierogów w trzecim tygodniu. W kolumnie E, w komórkach od E2 do E8 wpisano ilość pierogów w czwartym tygodniu. Dodatkowo zaprezentowano sposób obliczania, o ile więcej pieniędzy tygodniowo będzie musiał wydać kucharz na poniedziałkową i piątkową pracę. Formułę obliczania należy wpisać w odpowiedniej komórce. W tym przypadku w komórce H5. Brzmi ona następująco: =BD.SUMA($A$1:$E$8;H4;$G$1:$G$3)*$G$5. Oprócz tego komórkę G1 zatytułowano dzień tygodnia. Komórkę G4 stara cena za sztukę. Komórkę G6 nowa cena za sztukę. W kolumnie G, w komórce G8 podano WYNIK - 101,31 złotych. W kolumnach H, I, J, K w komórkach H1, I1, J1, K1 wpisano kolejno pierwszy tydzień, drugi tydzień, trzeci tydzień, czwarty tydzień. Pod nimi znalazły się wyliczenia uwzględniające starą i nową cenę. Komórkę L4 zatytułowano Średnia. W komórkach L5 i L7 są wartości średniej.
Źródło: Contentplus.pl Sp. z o.o., licencja: CC BY-SA 3.0.
Polecenie 2
Używając tego samego arkusza, sprawdź, ile kosztuje łączna produkcja pierogów w pierwszym i ostatnim tygodniu we wtorki, środy i czwartki.
Przykładowe rozwiązanie zadania:
RGKqVhDEjR7sX
Na zrzucie ekranu widoczny jest fragment arkusza Excel. W kolumnach A, B, C, D i E wprowadzono dane dotyczące ilości ulepionych pierogów dziennie przez studentów w czterech kolejnych tygodniach. W arkuszu kolejno dodano opisy: w komórce A1 Dzień tygodnia, w komórce B1 Pierwszy tydzień, w komórce C1 Drugi tydzień, w komórce D1 Trzeci tydzień, w komórce E1 Czwarty tydzień. W kolumnie A, w komórkach od A2 do A8 wpisano nazwy dni tygodnia. W kolumnie B, w komórkach od B2 do B8 wpisano ilość pierogów w pierwszym tygodniu. W kolumnie C, w komórkach od C2 do C8 wpisano ilość pierogów w drugim tygodniu. W kolumnie D, w komórkach od D2 do D8 wpisano ilość pierogów w trzecim tygodniu. W kolumnie E, w komórkach od E2 do E8 wpisano ilość pierogów w czwartym tygodniu. Komórkę G1 zatytułowano dzień tygodnia. Komórkę G4 stara cena za sztukę. Komórkę G6 nowa cena za sztukę. W kolumnie G, w komórce G8 podano WYNIK - 101,31 złotych. W kolumnach H, I, J, K w komórkach H1, I1, J1, K1 wpisano kolejno pierwszy tydzień, drugi tydzień, trzeci tydzień, czwarty tydzień. Pod nimi znalazły się wyliczenia uwzględniające starą i nową cenę. Komórkę L4 zatytułowano Średnia. W komórkach L5 i L7 są wartości średniej. Dodatkowo zaprezentowano sposób obliczania kosztów produkcji pierogów w pierwszym i ostatnim tygodniu we wtorki, środy i czwartki. Formułę obliczania należy wpisać w odpowiedniej komórce. W tym przypadku w komórce G11. Brzmi ona następująco: =SUMA(BD.SUMA(A1:E8;B1;A10:13);BD.SUMA(A1:E8;E1;A10:A13))*G5.
Źródło: Contentplus.pl Sp. z o.o., licencja: CC BY-SA 3.0.
