Podsumowanie
Aby zbudować dwukierunkową tabelę podsumowań, która sumuje dane liczbowe z więcej niż jednym kryterium, można użyć funkcji SUMA.JEŻELI. W przedstawionym przykładzie formuła w H5 skopiowana w zakresie H5: K7 to:
=SUMIFS(value,name,$G5,stage,H$4)
gdzie wartość (C5: C15), nazwa (B5: B15) i etap (D5: D15) to nazwane zakresy. Wynikiem jest tabela zawierająca podsumowanie sumarycznych dla każdej nazwy według etapu.
Wyjaśnienie
Funkcja SUMA.WARUNKÓW służy do sumowania wartości liczbowych przy użyciu wielu kryteriów.
W przedstawionym przykładzie dane z zakresu B5: E15 przedstawiają potok sprzedaży, w którym każdy wiersz jest szansą sprzedaży należącą do sprzedawcy na określonym etapie. Wzór w H5 to:
=SUMIFS(value,name,$G5,stage,H$4)
Pierwsza część formuły podsumowuje możliwości według sprzedawcy:
=SUMIFS(value,name,$G5 // sum by name
- Zakres sumy to nazwane wartości zakresu
- Zakres kryteriów 1 to nazwana nazwa zakresu
- Kryterium 1 pochodzi z komórki G5
Zauważ, że $ G5 to odniesienie mieszane, z zablokowaną kolumną i względnym wierszem. Dzięki temu formuła może się zmieniać w razie potrzeby, gdy jest kopiowana w całej tabeli.
Następna para zakres / kryteria w SUMA.WARUNKÓW, sumy według etapu:
stage,H$4 // sum by stage
- Zakres kryteriów 2 to nazwany etap zakresu
- Kryterium 2 to 4 H $
Ponownie, H $ 4 jest odniesieniem mieszanym, ze względną kolumną i zablokowanym wierszem. Dzięki temu kryteria mogą pobierać wartości etapów w wierszu 4, gdy formuła jest kopiowana w poprzek i w dół tabeli.
Gdy oba kryteria są razem, funkcja SUMA.WARUNKÓW poprawnie sumuje możliwości według nazwy i etapu.
Bez zakresów nazw
Ten przykład używa nazwanych zakresów tylko dla wygody. Bez nazwanych zakresów równoważna formuła to:
=SUMIFS($C$5:$C$15,$B$5:$B$15,$G5,$D$5:$D$15,H$4)
Zauważ, że odniesienia do nazwy, wartości i etapu są teraz odniesieniami bezwzględnymi, aby zapobiec zmianom podczas kopiowania formuły w poprzek i w dół tabeli.
Uwaga: tabela przestawna byłaby również doskonałym sposobem rozwiązania tego problemu.
