Vzorec programu Excel: Obojsmerný súhrn so SUMIFS -

Zhrnutie

Ak chcete zostaviť obojsmernú súhrnnú tabuľku, ktorá sumarizuje číselné údaje podľa viacerých kritérií, môžete použiť funkciu SUMIFs. V zobrazenom príklade je vzorec v H5, kopírovaný cez rozsah H5: K7,:

=SUMIFS(value,name,$G5,stage,H$4)

kde hodnota (C5: C15), názov (B5: B15) a fáza (D5: D15) sú pomenované rozsahy. Výsledkom je tabuľka, ktorá zobrazuje súhrnné súčty pre každé meno podľa etapy.

Vysvetlenie

Funkcia SUMIFS je navrhnutá na sčítanie číselných hodnôt pomocou viacerých kritérií.

V zobrazenom príklade údaje v rozsahu B5: E15 zobrazujú predajný kanál, v ktorom je každý riadok príležitosťou vlastnenou predajcom v konkrétnej fáze. Vzorec v H5 je:

=SUMIFS(value,name,$G5,stage,H$4)

Prvá časť vzorca sumarizuje príležitosti podľa predajcu:

=SUMIFS(value,name,$G5 // sum by name

  • Rozsah súčtov je pomenované hodnoty rozsahu
  • Rozsah kritérií 1 je pomenovaný názov rozsahu
  • Kritérium 1 pochádza z bunky G5

Všimnite si, že $ G5 je zmiešaný odkaz so zamknutým stĺpcom a relatívnym riadkom. Toto umožňuje vzorec podľa potreby meniť, keď sa vzorec kopíruje do celej tabuľky.

Ďalšia dvojica rozsahov a kritérií v SUMIFS, súčty podľa etáp:

stage,H$4 // sum by stage

  • Rozsah kritérií 2 je pomenovaný stupeň rozsahu
  • Kritérium 2 je 4 USD

Opäť, 4 doláre sú zmiešané referencie, pričom stĺpec je relatívny a riadok je uzamknutý. Toto umožňuje kritériám zachytiť hodnoty fázy v riadku 4, keď sa vzorec kopíruje cez tabuľku a dole.

Pri obidvoch kritériách funkcia SUMIFS správne sčíta príležitosti podľa názvu a podľa fázy.

Bez rozsahov mien

Tento príklad používa pomenované rozsahy iba na uľahčenie. Bez pomenovaných rozsahov je ekvivalentný vzorec:

=SUMIFS($C$5:$C$15,$B$5:$B$15,$G5,$D$5:$D$15,H$4)

Odkazy na oznámenia pre názov, hodnotu a fázu sú teraz absolútnymi odkazmi, aby sa zabránilo zmenám pri kopírovaní vzorca v tabuľke a nadol.

Poznámka: kontingenčná tabuľka by bola tiež vynikajúcim spôsobom riešenia tohto problému.

Zaujímavé články...