Jak obliczyć sumę sprzedaży w okresie 1 miesiąca zapisanego jako tekst?
Sumowanie po zakresie miesiąca gdy miesiąc jest tekstem — 2 rozwiązania — porada #94
W tym filmie czeka nas trudne zadanie ponieważ chcemy sumować wartości sprzedaży po zakresie miesiąca, który jest podany w formie tekstu, czyli czeka nas najpierw zadanie przekształcenia miesiąca w formie tekstu na zrozumiałą dla Excela datę.
Pokarzemy tu 2 możliwe rozwiązania.
Pierwsze wykorzystuje funkcję SUMA.WARUNKÓW, a najtrudniejsze w nim jest zamiana miesiąca tekstowego na datę. Przede wszystkim potrzebujemy 1 danego miesiąca konkretnego roku. Ponieważ są to wartości niezmienne możemy je wpisać na stałe i połączyć ampersandami z miesiącem. Otrzymamy po oszacowaniu formę daty, która wygląda tak:
"1cze2013"
Nie jest to może najelegantszy format daty, ale Excel go rozumie i to nam wystarczy.
Ostatni dzień miesiąca uzyskamy dzięki wstawieniu tego połączenia do odpowiedniej funkcji:
NR.SER.OST.DN.MIES(1&G$10&2013;0)
Łączymy wszystkie warunki w funkcji SUMA.WARUNKÓW i otrzymujemy następującą formułę:
Drugie rozwiązanie wykorzysta funkcję SUMA.ILOCZYNÓW. Jako jeden z argumentów wstawimy cały zakres sprzedaży ($D$2:$D$325), kolejnym będzie sprawdzenie miesiąca, ale podejdziemy do tego inaczej niż w pierwszym rozwiązaniu.
Ponieważ mamy tylko jeden rok możemy sprawdzić, czy zgadza się miesiąc i to wystarczy. Żeby to zrobić wykorzystamy funkcję TEKST i zapis formatowania niestandardowego dla skróconej nazwy miesiąca "mmm".
Musimy to zrobić na całym obszarze i przyrównać do wartości miesiąca z nagłówka.
TEKST($A$2:$A$325;"mmm")=G$1
taka formuła zwróci nam tablicę PRAWDA i FAŁSZ , których niestety funkcja SUMA.ILOCZYNÓW nie rozumie dlatego potrzebujemy zamienić je na 1 i 0. Najszybciej uzyskamy to dzięki podwójnemu znakowi minus (–). Musimy pamiętać o jednym, że zmiana znaków jako operacja matematyczna odbywa się przed porównaniem, więc porównanie musimy wziąć w nawiasy i dopiero przed nimi postawić znaki minus:
–(TEKST($A$2:$A$325;"mmm")=G$1)
Analogicznie robimy dla warunku sprawdzania sprzedawcy:
Jeśli chcesz dowiedzieć się więcej na temat Excela lub nie wiesz jak coś zrobić napisz do mnie o tym w komentarzu pod spodem albo bezpośrednio. W miarę możliwości odpowiem na Twoje pytanie.
Jak obliczyć sumę sprzedaży w okresie 1 miesiąca mając tylko 1 datę w Excelu?
Sumowanie po zakresie miesiąca na podstawie 1 daty SUMA.WARUNKÓW — porada #93
Co w sytuacji kiedy chcemy dokonać sumowania po zakresie jednego miesiąca i mamy tylko 1 komórkę, która jak zdaje się zawiera tylko nazwę miesiąca?
W tym filmie omówimy prosty przypadek, gdy wyświetlająca się nazwa miesiąca wynika z formatowania niestandardowego "mmm", a w komórce jest w rzeczywistości data.
W takiej sytuacji sytuacja jest prosta bo potrzeba tylko na podstawie tej daty (pierwszego dnia miesiąca) stworzyć warunek większe lub równe tej wartości oraz drugi warunek mówiący, że mniejsze lub równe wartości ostatniego dnia miesiąca. Do tego drugiego warunku wykorzystamy funkcję NR.SER.OST.DN.MIES
Dodatkowo jeszcze dokładamy jeszcze 1 warunek sprawdzający do naszego przykładu — kryterium sprzedawcy. Wszystkie warunki sprowadzają się do jednej formuły:
Jeśli chcesz dowiedzieć się więcej na temat Excela lub nie wiesz jak coś zrobić napisz do mnie o tym w komentarzu pod spodem albo bezpośrednio. W miarę możliwości odpowiem na Twoje pytanie.
Jak dokonać sumy sprzedaży w określonym zakresie dat w Excelu?
Sumowanie sprzedaży w wybranym zakresie dat — porada #92
Zakładamy, że chcemy z sumować wszystkie wartości sprzedaży, które miały miejsce w określonym przedziale czasowym, czyli od daty początkowej do daty końcowej. Wynika z tego, że potrzebujemy 2 warunków i sumowania, czyli wykorzystamy funkcję SUMA.WARUNKÓW.
Na początek sprawdzimy sobie jaka jest najstarsza data (minimalna) w naszym zakresie i najmłodsza (maksymalna), dzięki odpowiednio funkcji MIN i MAX po całym zakresie dat.
Dzięki temu mamy datę od której chcemy sumować. Dokładnie chcemy sumować po kolejnych miesiącach, więc potrzebujemy stworzyć tabelę początków i końców naszych miesięcy.
Zaczynamy od daty wyznaczonej przez funkcję MIN i na jej podstawie wyznaczamy ostatni dzień miesiąca dzięki funkcji:
=NR.SER.OST.DN.MIES(G5;0)
parametr zero informuje nas, że chcemy mieć ostatni dzień z tego miesiąca. Gdybyśmy wstawili np: ‑1 (minus jeden) to uzyskalibyśmy ostatni dzień poprzedniego miesiąca. Jeśli wstawimy tam 1 (jedynkę) to otrzymamy ostatni dzień z następnego miesiąca.
Kolejnym krokiem jest stworzenie 1 dnia kolejnego miesiąca. Najprościej zrobić to dodają 1 do ostatniego dnia pierwszego miesiąca.
Analogicznie wypełniamy dane do końca dat w naszym zakresie. Końcową datę możemy zostawić taką jaka wynika z naszych obliczeń albo przypisać wartości z funkcji MAX.
Teraz możemy już wstawiać funkcję SUMA.WARUNKÓW z odpowiednimi warunkami mniejsze i równe oraz większe i równe w nawiązaniu do dat.
Uzyskaliśmy sumę sprzedaży w zakresie 2 dat.
Nasze daty możemy dowolnie modyfikować jeśliby zaszła taka potrzeba.
P.S.
Jeśli chcesz dowiedzieć się więcej na temat Excela lub nie wiesz jak coś zrobić napisz do mnie o tym w komentarzu pod spodem albo bezpośrednio. W miarę możliwości odpowiem na Twoje pytanie.
Jak stworzyć wykres wodospadowy zysków i strat w Excelu?
Wykres wodospadowy waterfall zysków i strat — porada #91
W tym wideo zajmiemy się stworzeniem wykresu wodospadowego (waterfall) zysków i strat. Na przykładzie oszczędności rodziny. Początkowe dane zawierają kwotę, od której zaczynamy oraz bilans poszczególnych miesięcy — kiedy wydaliśmy więcej niż przewidywał budżet, a kiedy zaoszczędziliśmy.
Jednak ten zbiór danych nie pozwoli stworzyć wykresu wodospadowego. Potrzebujemy stworzyć 4 kolumny, żeby uzyskać pełny zakres danych:
1) to kolumna Krawędzi, która będzie zawierała wartości brzegowe, czyli kwotę oszczędności z jaką zaczęliśmy (3000zł) oraz kwotę końcową, którą musimy obliczyć.
2) to kolumna W górę, w której będziemy szukać pozytywnych bilansów, czyli miesięcy, w których zaoszczędziliśmy. Uzyskamy ją dzięki funkcji:
=MAX(0;F4)
3) to kolumna W dół, w której będziemy szukać negatywnych bilansów, czyli miesięcy kiedy wydaliśmy więcej niż mieliśmy (przewidywał budżet). Tak naprawdę potrzebujemy odwrotności tej wartości ze znakiem plus, więc przed funkcję postawimy znak minus:
=-MIN(0;F4)
4) to kolumna Podstawy. Czyli wartości na której będą opierać się wartości W górę i W dół, by symbolizować, że od tej wartości wartość wzrosła lub do tej wartości spadła.
Na jej obliczenie składa się pierwsza wartość krawędzi, suma z wcześniejszej podstawy oraz wartości W górę oraz odjęcie wartości W dół.
=SUMA(B3;D3:E3)-C4
Przeciągamy tą formułę o 1 niżej niż Grudzień (koniec naszego bilansu), by uzyskać końcową wartość brzegową/krawędzi (musimy ją przeciągnąć do odpowiedniej kolumny.
Teraz trzeba zaznaczyć dane — wszystkie 4 kolumny + miesiące oraz wartości krawędzi przed i za miesiącami. Czyli wartości oszczędności z jakimi rodzina zaczęła rok i z jakimi oszczędnościami skończyła rok.
Wstawiamy wykres kolumnowy skumulowany. Usuwamy zbędne dane np: Legendę, a następnie zaznaczamy serię podstawy by ustawić jej Wypełnienie na Brak wypełnienia, i kolor linii na Brak linii.
Teraz możemy dopasować kolory pozostałych serii malując je np: narzędziem Kolor wypełnienia z karty Narzędzia główne, co przy pełnym kolorze jest szybsze od wchodzenia w okno formatowania serii i opcje wypełnienia serii.
Zostało jeszcze dopracowanie nazw osi poziomej (kategorii). Bo przy naszym zaznaczeniu Excel pomyślał, że nazwy miesiąca to Seria, dlatego trzeba je usunąć z Serii a dołożyć do Kategorii. Pamiętaj zaznaczyć kategorię o 1 komórkę przed i po nazwach miesiąca, żeby ilość punktów kategorii była równa ilościom punktów w seriach.
Właśnie stworzyliśmy funkcjonalny i ładnie się prezentujący wykres wodospadowy.
P.S.
Jeśli chcesz dowiedzieć się więcej na temat Excela lub nie wiesz jak coś zrobić napisz do mnie o tym w komentarzu pod spodem albo bezpośrednio. W miarę możliwości odpowiem na Twoje pytanie.
Jak stworzyć wykres termometru z ujemnymi i dodatnimi temperaturami w Excelu?
Wykres termometru dla dodatniej i ujemnej temperatury — porada #90
W poradzie 89 stworzyliśmy wykres termometru dla wartości dodatnich, ale co z wartościami ujemnymi? Niestety zaproponowany tam wykres się nie sprawdza. Potrzebujemy innego podejścia. Dodatkowo chcemy, aby wartości dodatnie były oznaczone innym kolorem niż ujemne.
Przede wszystkim zamiast 1 punktu będziemy potrzebować 3 punktów — wszystkie na podstawie 1 wartości temperatury będą wyliczane. Musimy sobie też ustalić minimalną temperaturę jaka ma być wyświetlana załóżmy minus 50 stopni.
Obliczenia dla temperatury, komórka A2, dla 3 kolejnych punktów to:
Pierwsza wartość pokazuje się tylko gdy temperatura jest dodatnia.
Druga wartość gdy tylko, gdy są wartości ujemny temperatury.
Trzecia to nasze minimum ‑50 stopni, gdy są wartość dodatnie temperatury i dopełnienie do minus 50 stopni dla ujemnych wartości temperatury.
Dodajemy sobie pusty wykres kolumnowy skumulowany, a następnie dodajemy po kolei serie pojedyncze punkty w kolejności w jakiej zostały wymienione.
Mamy już wykres, który przypomina termometr jaki chcemy, ale potrzebujemy wyrzucić trochę śmieci legenda, poziome linie oraz zwęzić wykres i ustawić parametr Szerokość przerwy na Brak przerwy.
Teraz potrzebujemy serii dodatniej nadać ciepłe kolory np: gradient od żółtego do czerwonego, a dla naszego minimum, 3 wartości, kolory zimne np: gradient od jasnego niebieskiego do ciemnego niebieskiego. Najprościej to zrobić dla temperatury dodatniej.
Została jeszcze jedna seria, której nie widać przy dodatniej temperaturze, więc zmieniamy ją na ujemną i wtedy zaczyna być widoczna, tylko my nie chcemy w ogóle jej widzieć, dlatego dla niej ustawiamy Brak wypełnienia i Brak Linii.
I to już są najważniejsze rzeczy, reszta to kosmetyka. Niestety jeśli chodzi o kolorystykę ciepłą i zimna dla temp dodatnich i ujemnych nie da się dokonać podziału osi y na 2 obszary o różnych kolorach, więc zostawiłem ją bez zmian.
Na koniec chcemy jeszcze ukryć kolumnę z danymi, żeby widz widział tylko temperaturę, ale jak to zrobimy znikają wartości na wykresie. Excel jest tak domyślnie ustawiony, że wartości ukrytych nie pokazuje na wykresie, więc trzeba mu to zmienić.
Wchodzimy w opcję Zaznacz dane
następnie naciskamy guzik Ukryte i puste komórki
by wreszcie zaznaczyć opcję Pokaż dane w ukrytych wierszach i kolumnach.
P.S.
Jeśli chcesz dowiedzieć się więcej na temat Excela lub nie wiesz jak coś zrobić napisz do mnie o tym w komentarzu pod spodem albo bezpośrednio. W miarę możliwości odpowiem na Twoje pytanie.
