W tym poście omówimy podsumowanie sprzedaży według dni tygodnia.
Zadanie to wykonamy na podstawie przykładowych danych z rys. nr 1.
Mamy niezbędne dla nas dane, czyli datę i wartość sprzedaży. Na tej podstawie chcemy uzyskać podsumowanie, ile transakcji odbyło się w każdym dniu tygodnia i jaka to była wartość sprzedaży. Na konkretny dzień tygodnia możemy patrzeć po jego nazwie lub po numerze (rys. nr 2).
Możemy tutaj użyć funkcji DZIEŃ.TYG, która na podstawie daty podaje dzień tygodnia. Pierwszym argumentem funkcji jest liczba_kolejna, czyli data, z której chcemy wyciągnąć dzień tygodnia. Drugi argument funkcji to [zwracany_typ], gdzie musimy zwrócić uwagę, jak są numerowane dni tygodnia. Standardowa numeracja jest tutaj określana liczbą 2, co widać na rys. nr 3.
Zapis funkcji powinien wyglądać następująco:
=DZIEŃ.TYG(A2;2)
Powyższą formułę zatwierdzamy i kopiujemy na komórki poniżej. Otrzymamy numery dni tygodnia dla poszczególnych dat przedstawione na rys. nr 4.
Jeśli będziemy mieli numerację dni tygodnia, to możemy wykorzystać dane z kolumny G. Możemy również wykorzystać funkcję TEKST i odpowiednio sformatować datę. Pierwszym argumentem funkcji TEKST jest wartość, czyli nasza data. Drugi argument to format_tekst, czyli oznaczenie formatu "dddd", które powoduje wyświetlanie pełnej nazwy tekstowej dnia tygodnia. Zapis formuły powinien wyglądać następująco:
=TEKST(A2;"dddd")
Powyższą formułę zatwierdzamy i kopiujemy na komórki poniżej. Podsumowując, jeśli datę z kolumny A, sformatujemy jako "dddd", to otrzymamy pełną nazwę dnia tygodnia (rys. nr 5).
Jak widać w danych powyżej wartości z kolumn C i D ze sobą korespondują, tzn. jeśli mamy np. wartość 3 w kolumnie C, to w kolumnie D mamy dzień o nazwie Środa itd.
Jeśli możemy sobie pozwolić na zbudowanie w danych dodatkowych kolumn (pomocniczych), to możemy użyć funkcji SUMA.WARUNKÓW lub SUMA.JEŻELI. My jednak założymy, że nie mamy możliwości zbudowania takich kolumn pomocniczych, dlatego takie obliczenia musimy wykonać wewnątrz jednej formuły. A funkcja SUMA.WARUNKÓW nie pozwala na obliczenia w jej wnętrzu.
W pierwszej kolejności musimy sprawdzić dzień tygodnia, ale dla całej kolumny A. Użyjemy do tego funkcji DZIEŃ.TYG. Zaznaczamy komórkę A2 i za pomocą skrótu klawiszowego Ctrl+Shift+Strzałka w dół zaznaczamy całą kolumnę. Podany zakres blokujemy bezwzględnie za pomocą klawisza F4. W argumencie zwracany_typ wybieramy wartość 2 (dla numeracji poniedziałek — 1, niedziela – 7). Zapis formuły powinien wyglądać następująco:
=DZIEŃ.TYG($A$2:$A$1998;2)
Ze względu na to, że pracujemy na Excelu tablicowym, po zatwierdzeniu formuły, wyniki rozleją się na odpowiednią ilość komórek (rys. nr 6).
Interesuje nas, kiedy wartości z kolumny H są równe wartością z kolumny G. Zapis formuły powinien wyglądać następująco:
=DZIEŃ.TYG($A$2:$A$1998;2)=G2
Po zatwierdzeniu formuły otrzymamy wartości logiczne PRAWDA i FAŁSZ przedstawione na rys. nr 7.
Nie ma znaczenia, jeśli nie posiadasz Excela tablicowego, to tylko ułatwia pokazywania rozlewających się formuł. Teraz musimy zamienić nasze wartości logiczne na wartości 1 i 0. Możemy to zrobić za pomocą podwójnej negacji, czyli wstawiamy dwa znaki minus przed funkcję. Zapis powinien wyglądać następująco:
=–(DZIEŃ.TYG($A$2:$A$1998;2)=G2)
Po zatwierdzeniu formuły otrzymamy wartości 0 i 1. Wartość logiczna PRAWDA zostanie zamieniona na 1, a FAŁSZ na 0 (rys. nr 8).
Teraz możemy łatwo zsumować nasze wyniki. Żebyśmy nie musieli tej formuły zatwierdzać w klasycznym Excelu kombinacją klawiszy Ctrl+Shift+Enter, korzystamy z funkcji SUMA.ILOCZYNÓW. Argumentem funkcji jest tablica1, czyli tablica uzyskana z poprzedniej formuły. Zapis powinien wyglądać następująco:
=SUMA.ILOCZYNÓW(–(DZIEŃ.TYG($A$2:$A$1998;2)=G2))
Po zatwierdzeniu formuły skrótem klawiszowym Ctrl+Enter otrzymamy ilość wszystkich transakcji przypadających na poniedziałek. Po skopiowaniu formuły w dół, otrzymamy dane dla pozostałych dni tygodnia (rys. nr 9).
Żeby obliczyć wartość sprzedaży postępujemy bardzo podobnie. Możemy skopiować poprzednią formułę. Zamiast podwójnej negacji (dwa minusy) zrobimy operację mnożenia przez kolumnę Sprzedaż, czyli zakres $B$2:$B$1998. Na chwilę usuniemy też funkcję SUMA.ILOCZYNÓW. Zapis formuły powinien wyglądać następująco:
=DZIEŃ.TYG($A$2:$A$1998;2)=G2)*$B$2:$B$1998
Po zatwierdzeniu formuły otrzymamy rozlane wartości sprzedaży z podaną wartością dla poniedziałku przedstawione na rys. nr 10.
Aby otrzymać wyniki dla wszystkich dni tygodnia musimy otoczyć poprzednią formułę funkcją SUMA.ILOCZYNÓW. Zapis formuły powinien wyglądać następująco:
=SUMA.ILOCZYNÓW((DZIEŃ.TYG($A$2:$A$1998;2)=G2)*$B$2:$B$1998)
Po zatwierdzeniu formuły skrótem klawiszowym Ctrl+Enter i skopiowaniu w dół otrzymamy wartości sprzedaży dla poszczególnych dni tygodnia przedstawione na rys. nr 11.
