Żeby zsumować w Excelu liczby należące do jednej kategorii, użyj SUMA.WARUNKÓW. Zamiast wskazywać osobno każdą komórkę z godzinami projektu Atlas, podaj kolumnę godzin, kolumnę nazw i szukaną nazwę. Wynik będzie zależał od danych, a nie od tego, czy zauważysz wszystkie pasujące wiersze.
Poniżej policzymy godziny trzech fikcyjnych projektów. To przykład dla polskiego Excela z typowymi ustawieniami regionalnymi. W angielskiej wersji funkcja nazywa się SUMIFS; separator argumentów zależy od konfiguracji programu.
Wpisz formułę obok listy projektów
W kolumnie A znajdują się nazwy projektów, w B czynności, a w C liczby godzin. Dane zajmują wiersze 2–7. W E2 wpisz Atlas, a w F2 wprowadź poniższą formułę i naciśnij Enter.
=SUMA.WARUNKÓW($C$2:$C$7;$A$2:$A$7;E2)$C$2:$C$7 — liczby do dodania. $A$2:$A$7 — nazwy sprawdzane przy każdym zapisie. E2 — komórka z wybranym projektem.
Dla Atlasu wynik wynosi 6 godzin: 2 godziny planu, 1,5 godziny rozmowy i 2,5 godziny analizy. Orion ma 4 godziny, Vega też 4. Kolumna czynności nie wpływa na tę pierwszą formułę — sumujesz cały projekt, niezależnie od rodzaju pracy.
Nie wpisuj nazwy komórki w cudzysłowie. E2 oznacza jej zawartość, a „E2” byłoby dosłownym tekstem do znalezienia. Jeśli wpisujesz nazwę projektu bezpośrednio w formule zamiast odwołania, użyj zwykłych cudzysłowów, np. "Atlas".
Skopiuj obliczenie bez przesuwania danych
W E3 wpisz Orion, a w E4 Vega. Skopiuj F2, zaznacz F3:F4 i wklej. W nowych formułach ostatnie odwołanie powinno zmienić się na E3 i E4, ale zakresy źródłowe mają nadal zaczynać się w wierszu 2 i kończyć w 7.
Znaki dolara blokują adresy podczas kopiowania. Dzięki $C$2:$C$7 druga formuła nie zacznie pomijać pierwszego zapisu i dodawać pustej komórki pod listą. E2 zostaje odwołaniem względnym, bo każdy wiersz podsumowania ma czytać inną nazwę. Znak dolara nie blokuje edycji samej komórki.
To także powód, żeby nie przepisywać wyniku ręcznie. Jeżeli później zmienisz godzinę, formuła może zareagować. Gdy potrzebujesz osobnej, niezmiennej kopii do przekazania, zastosuj wklejanie samych wartości, zachowując oryginał z obliczeniami.
Sprawdź wynik przez zmianę jednej liczby
W pobranym pliku formuły są już wpisane. Zmień C2 z 2 na 5. Suma Atlasu w F2 powinna wzrosnąć z 6 do 9 godzin. Orion i Vega powinny nadal mieć po 4. Przywróć potem C2 do 2, żeby wrócić do punktu wyjścia.
Ten test sprawdza dwie rzeczy naraz: czy obliczenie czyta właściwą liczbę oraz czy nie dolicza jej do innych projektów. Sam widok poprawnego „6” nie pokazałby, czy w komórce znajduje się formuła, czy tylko ręcznie wpisany wynik.
W drugim teście zastąp nazwę w E2 nazwą, której nie ma w danych. Wynik wyniesie 0. Zero nie dowodzi, że projekt został sprawdzony i nie ma godzin — może po prostu oznaczać literówkę albo brak wpisów w objętym zakresie. Sprawdź źródło, zanim potraktujesz taki wynik jako informację o wykonanej pracy.
Plik do własnej próby
Godziny projektów z działającymi formułami
Jeden arkusz „Godziny”. Jasnożółte pola to dane i nazwy projektów, szare pola F2:F4 zawierają obliczenia. Wszystkie wpisy są fikcyjne. Plik nie zawiera makr ani połączeń zewnętrznych.
Dodaj warunek albo rozszerz listę świadomie
Gdy chcesz policzyć tylko rozmowy w wybranym projekcie, dopisz parę obejmującą kolumnę czynności i słowo „Rozmowa”. Dla Atlasu z początkowych danych dostaniesz 1,5 godziny.
=SUMA.WARUNKÓW($C$2:$C$7;$A$2:$A$7;E2;$B$2:$B$7;"Rozmowa")Pierwszy warunek wybiera projekt. Drugi ogranicza go do rozmów. Każdy dodawany zapis musi spełniać oba.
Podany zakres ma koniec. Jeśli dopiszesz nowy rekord w A8:C8, nie zakładaj, że formuła obejmująca wiersze 2–7 sama go policzy. Rozszerz odpowiednie zakresy do wiersza 8, także w pozostałych komórkach podsumowania. Dla dodatkowego wpisu Atlas — Plan — 2 suma całego Atlasu powinna wynieść 8 godzin. Nie dopisuj podsumowań do kolumny źródłowej, bo można wtedy policzyć te same godziny drugi raz.
Jeśli potrzebujesz obejrzeć konkretne pasujące wiersze, użyj filtrów w nagłówkach. Tutaj celem jest liczba w podsumowaniu, a nie ukrywanie reszty listy. SUMA.WARUNKÓW nie jest sumą wyłącznie widocznych wierszy.
Gdy suma wygląda podejrzanie
Porównaj ręcznie kilka wpisów jednej kategorii. Sprawdź, czy wszystkie zakresy obejmują te same wiersze i czy nazwy nie mają dodatkowych spacji. Godziny wpisuj jako liczby, np. 1,5, a nie tekst „1,5 godz.”. W tym przykładzie 1,5 oznacza półtorej godziny, nie godzinę i pięć minut.
SUMA.WARUNKÓW dodaje wielkości, a nie liczy rekordy. Trzy zapisy Atlasu dają 6 godzin, nie 3. Gdy zadaniem jest liczba odpowiedzi i ich udział procentowy, potrzebujesz innego podsumowania — takiego jak w poradniku o wykresie wyników ankiety. Dobierz działanie do tego, co ma oznaczać wynik.
Jeżeli zakresy i kryteria są poprawne, ale końcówka sumy różni się od liczb widocznych na ekranie, sprawdź różnicę między zaokrąglaniem a formatem komórek. Dwa wyświetlane miejsca nie oznaczają, że formuła korzysta tylko z nich.
Gdy z sum przechodzisz do średnich, zachowaj też liczbę ważnych pomiarów w każdej kategorii. Zobacz, dlaczego średnia ze średnich grup może dać błędny wynik i jak odzyskać średnią całego zbioru.
Gdy zamiast jednej formuły potrzebujesz swobodnie zmieniać układ podsumowania, zobacz jak zbudować tabelę przestawną i skontrolować sumę. Uważaj na kwoty zapisane tekstem — zmiana Licznika na Sumę nie naprawia źródła.
Źródła i sposób sprawdzenia przykładu
- Microsoft — SUMA.WARUNKÓW. Składnia, kolejność argumentów i zgodne rozmiary zakresów.
- Microsoft — odwołania względne i bezwzględne. Zachowanie adresów podczas kopiowania.
- Microsoft — sumowanie na podstawie wielu warunków. Łączenie kryteriów w formule.
Plik przeliczono narzędziem arkuszowym Artifact Tool, sprawdzając zmianę liczby, brak dopasowania, zero, puste pole i rozszerzenie zakresu. Nie sterowano natywnym Excelem. Podgląd przedstawia render rzeczywistego pliku, a ilustracja otwierająca została wygenerowana.
