souhrn
Chcete-li vytvořit obousměrnou souhrnnou tabulku, která shrnuje číselná data s více než jedním kritériem, můžete použít funkci SUMIFs. V zobrazeném příkladu je vzorec v H5, kopírovaný přes rozsah H5: K7,:
=SUMIFS(value,name,$G5,stage,H$4)
kde hodnota (C5: C15), název (B5: B15) a fáze (D5: D15) jsou pojmenované rozsahy. Výsledkem je tabulka, která zobrazuje souhrnné součty pro každé jméno podle fáze.
Vysvětlení
Funkce SUMIFS je navržena k součtu číselných hodnot pomocí více kritérií.
V zobrazeném příkladu data v rozsahu B5: E15 ukazují prodejní kanál, kde každý řádek je příležitost vlastněná prodejcem v konkrétní fázi. Vzorec v H5 je:
=SUMIFS(value,name,$G5,stage,H$4)
První část vzorce shrnuje příležitosti od prodejce:
=SUMIFS(value,name,$G5 // sum by name
- Rozsah rozsahu je pojmenované hodnoty rozsahu
- Rozsah kritérií 1 je pojmenovaný název rozsahu
- Kritérium 1 pochází z buňky G5
Všimněte si, že $ G5 je smíšený odkaz se zamčeným sloupcem a relativním řádkem. To umožňuje vzorec podle potřeby měnit, když je vzorec zkopírován do celé tabulky.
Další pár rozsahů / kritérií v SUMIFS, součty po etapách:
stage,H$4 // sum by stage
- Rozsah kritérií 2 je pojmenovaná fáze rozsahu
- Kritérium 2 je H $ 4
Opět platí, že H $ 4 je smíšený odkaz, přičemž relativní sloupec a řádek jsou uzamčeny. To umožňuje kritériím vyzvednout hodnoty fáze v řádku 4 při kopírování vzorce napříč a dolů v tabulce.
S oběma kritérii dohromady funkce SUMIFS správně shrnuje příležitosti podle názvu a podle fáze.
Bez rozsahů jmen
Tento příklad používá pojmenované rozsahy pouze pro pohodlí. Bez pojmenovaných rozsahů je ekvivalentní vzorec:
=SUMIFS($C$5:$C$15,$B$5:$B$15,$G5,$D$5:$D$15,H$4)
Odkaz na oznámení pro název, hodnotu a fázi jsou nyní absolutní odkazy, aby se zabránilo změnám při kopírování vzorce napříč a dolů v tabulce.
Poznámka: kontingenční tabulka by byla také vynikajícím způsobem, jak tento problém vyřešit.
