Jednym z typowych pytań na tablicy dyskusyjnej jest sposób użycia funkcji SumIf z dwoma różnymi warunkami. Niestety odpowiedź brzmi, że SumIf nie radzi sobie z dwoma różnymi warunkami.
Aby spełnić dwa warunki, musisz użyć dość skomplikowanej formuły tablicowej. Dodatek do kreatora sum warunkowych umożliwia łatwe wprowadzanie tych skomplikowanych formuł.
Oto arkusz programu Excel z kolumnami dotyczącymi produktu, przedstawiciela handlowego i sprzedaży. Dane znajdują się w komórkach A2: C29.
Jeśli chcesz zsumować sprzedaż, zadziała prosta funkcja SUMA (). =SUM(C2:C29).
Wielu Excellerów odkrywa funkcję SumIf. Korzystając z tej funkcji, można dość łatwo obliczyć całkowitą sprzedaż produktu ABC.=SUMIF(A2:A29,E2,C2:C29)
Łatwo jest również obliczyć całkowitą sprzedaż dokonaną przez przedstawiciela handlowego Joe =SUMIF(B2:B29,E2,C2:C29).
Można by wtedy założyć, że można obliczyć całkowitą sprzedaż produktu ABC wykonanego przez Joe. Jednak nie ma sposobu, aby to zrobić za pomocą funkcji SumIf. Okazuje się, że musisz użyć dość złożonej tablicy lub formuły CSE.
Spójrzmy prawdzie w oczy - formuła Sum to Excel 101. Formuła SumIf nie jest daleko w tyle pod względem złożoności. Jednak formuła CSE do obliczenia całkowitej sprzedaży ABC dokonanej przez Joe wystarczy, aby przyprawić o zawrót głowy.
Dobra wiadomość - Microsoft oferuje Kreatora sum warunkowych, który pozwala nawet nowicjuszowi wprowadzać złożone formuły warunkowe na podstawie 1, 2 lub więcej warunków. Kreator sum warunkowych jest dodatkiem. Aby dodać tę funkcjonalność do programu Excel, przejdź do menu Narzędzia i wybierz Dodatki. W oknie dialogowym Dodatki zaznacz pole wyboru obok opcji Kreator sum warunkowych i wybierz OK. Możliwe, że w tym momencie będziesz potrzebować instalacyjnego dysku CD, ponieważ firma Microsoft nie dołącza kreatora do domyślnej instalacji.
Po pomyślnym włączeniu dodatku pojawi się opcja Suma warunkowa… w dolnej części menu Narzędzia.
Wybierz jedną komórkę w zbiorze danych i wybierz Narzędzia - Suma warunkowa. Zakładając, że dane są ładnie sformatowane z jednym wierszem nagłówków, program Excel poprawnie zgadnie zakres danych. Wybierz Dalej.
W kroku 2 wybierz kolumnę do zsumowania. W tym przypadku kreator już odgadł, że chcesz zsumować pierwszą (i jedyną) kolumnę liczbową - Sales. Na środku okna dialogowego znajdują się trzy rozwijane elementy sterujące. Tak się składa, że są one poprawne dla pierwszego warunku - Produkt równa się ABC, więc wybierz przycisk Dodaj warunek.
Następnie możesz dodać drugi warunek. W takim przypadku chcesz określić, że przedstawicielem handlowym jest Joe. Wybierz strzałkę pierwszego menu rozwijanego. Excel oferuje alfabetyczną listę dostępnych nazw kolumn. Wybierz przedstawiciela handlowego.
Centrum rozwijane jest poprawne, ale aby uzyskać kompletność, możesz zobaczyć, że mogłeś wybrać równy, mniejszy niż, większy niż, mniejszy lub równy, większy lub równy lub nie równy.
Z trzeciego menu wybierz Joe.
Wybierz przycisk Dodaj warunek.
Teraz możesz przejść do kroku 3. Naciśnij przycisk Dalej.
W kroku 3 masz dwie możliwości. Przy pierwszym wyborze Kreator wprowadzi pojedynczą formułę z wartościami „ABC” i „Joe” wpisanymi na stałe w formule. Da ci odpowiedź, ale nie będzie możliwości łatwej zmiany formuły. W przypadku drugiego wyboru program Excel ustawi nową komórkę z wartością „ABC” i nową komórką z wartością „Joe”. Trzecia komórka będzie zawierać formułę wykonującą sumę warunkową na podstawie tych dwóch wartości. Dzięki tej opcji możesz wpisać nowe wartości do komórek, aby zobaczyć łączną liczbę XYZ sprzedanych przez Adama.
Kreator zapyta następnie, gdzie chcesz uzyskać wartość ABC. Wybierz komórkę i wybierz Dalej. Powtórz, gdy kreator poprosi Cię o wybranie komórki dla Joe i formuły.
Po wybraniu opcji Zakończ w ostatnim kroku program Excel utworzy nieco inną (ale prawidłową) wersję formuły CSE.
Ta formuła oblicza, że Joe sprzedał 33 338 dolarów ABC.
Jeśli zmienisz komórkę wejściową produktu z ABC na DEF, formuła zostanie ponownie obliczona, aby pokazać, że Joe sprzedał 24 478 USD DEF.
Kreator sum warunkowych sprawia, że złożone formuły są łatwo dostępne dla wszystkich właścicieli programu Excel.
Dodatkowe informacje:Jeśli chcesz zbudować tabelę, która pokaże sprzedaż każdego produktu przez każdego przedstawiciela handlowego, musisz wiedzieć o tych formułach jakaś specjalna „opieka i karmienie”. Wpisz każdego przedstawiciela handlowego w górnej części zakresu. Wpisz każdy produkt w lewej kolumnie zakresu. Edytuj formułę dostarczoną przez kreatora. Na poniższym obrazku formuła wskazuje produkt w komórce E6. To odniesienie naprawdę musi mieć wartość $ E6. Jeśli pozostawisz odwołanie jako E6 i skopiujesz formułę do kolumny G, formuła będzie wyglądać na F6 zamiast E6 i to będzie błędne. Dodanie znaku dolara przed literą E w E6 zapewni, że formuła zawsze będzie patrzeć na produkt w kolumnie E. Formuła wskazuje również przedstawiciela handlowego w komórce F5. Ta referencja naprawdę musi wynosić 5 F $. Jeśli zostawisz odniesienie jako F5 i skopiujesz do wiersza 7,odniesienie F5 zmieni się na F6 i to nie jest poprawne. Dodanie znaku dolara przed numerem wiersza zablokuje numer wiersza, a odniesienie będzie zawsze wskazywało wiersz 5.
W trybie edycji (zaznacz komórkę i naciśnij klawisz F2, aby edytować), wpisz $ przed E. Wpisz znak dolara przed 5 w F5. Nie naciskaj jeszcze Enter!
Ta formuła jest specjalnym typem formuły. Jeśli naciśniesz Enter, otrzymasz 0, co nie jest poprawne.
Zamiast wpisywać Enter, przytrzymaj klawisze Ctrl i Shift podczas naciskania Enter. Ta magiczna kombinacja C trl + S hift + E nter jest powodem, dla którego nazywam te formuły CSE.
Przed skopiowaniem formuły do pozostałej części tabeli należy wziąć pod uwagę jeszcze jedną kwestię. Twoja skłonność może polegać na skopiowaniu F6 i wklejeniu do F6: G8. Jeśli spróbujesz tego, program Excel wyświetli zagadkowy komunikat „Nie możesz zmienić części tablicy”. Program Excel skarży się, że nie można wkleić formuły CSE do zakresu zawierającego oryginalną formułę CSE.
Łatwo to obejść. Skopiuj F6. Wklej do F7: F8.
Skopiuj F6: F8. Wklej do G6: G8. Otrzymasz tabelę formuł CSE pokazującą sumy na podstawie dwóch warunków.
