Excel již má funkce jako SUMIF a SUMIFS pro sčítání dat podle skupin. Nicméně; nemohou fungovat, pokud máte kombinované hodnoty s datem a časem. Tento článek ukazuje Jak v Excelu sčítat podle data bez času pomocí funkcí SUMPRODUCT a INT.
Syntaxe
=SUMPRODUCT( rozsah hodnot * ( INT( rozsah datum-čas ) = buňka datum ) )
Kroky
- Začněte s =SUMPRODUCT( funkce
- Typ vyberte rozsah, který obsahuje hodnoty $C$3:$C$16
- Použijte hvězdičku pro součin hodnot rozsahu s rozsahem podmínek a otevřete závorku *(
- Pokračujte příkazem INT($B$3:$B$16) pro odstranění časové části
- Zadejte podmínku se znaménkem rovnosti a rozsah kritérií =E3
- Typ )) pro uzavření funkce a dokončení vzorce
Jak
Excel uchovává hodnoty data a času jako čísla. Excel předpokládá, že historie začíná od 1. ledna 1900, a akceptuje toto datum jako 1. Zatímco celá čísla představují dny, desetinná čísla představují čas. Například; 1. 1. 2018 se rovná 43101, 12:00 se rovná 0,5.
Pomocí funkce INT se můžeme zbavit časových hodnot nebo desetinné části čísel. Funkce INT jednoduše vrátí z číselné hodnoty celou číselnou část. V našem případě;
INT($B$3:$B$16) vrátí {43292;43292;43292;43292;43293;43293;43293;43293;43293;43293;43293;43293;43293;43293;43293;43294;43294}
Aby však funkce INT fungovala na poli, musíme použít funkci SUMPRODUCT. Schopnost funkce SUMPRODUCT pracovat s poli bez použití vzorců pro pole poskytuje výhodu oproti omezením funkcí SUMIF a SUMIFS. Funkce SUMPRODUCT dokáže vyhodnotit a vrátit pole z podmínek rozsah – hodnota. Tuto funkci využíváme k řešení dvojic rozsah – kritérium.
(INT($B$3:$B$16)=E3) vrací {TRUE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}
Funkce SUMPRODUCT také sčítá hodnoty v poli argumentů, pokud je argument pouze jeden. Podmíněné rovnice však vracejí logické hodnoty. Je tedy třeba je převést na čísla, která jsou pro funkci SUMPRODUCT smysluplná. Naštěstí to dělá operátor součinu (*).
Násobením pole hodnot s polem podmínek získáme hodnoty pole vygenerované podle našich potřeb.
$C$3:$C$16*(INT($B$3:$B$16)=E3) vrátí {1;2;3;4;0;0;0;0;0;0;0;0;0;0;0;0;0;0}
Nakonec funkce SUMPRODUCT vrátí výsledky jako součet podle data v aplikaci Excel, což je to, co hledáme.
=SUMPRODUCT($C$3:$C$16*(INT($B$3:$B$16)=E3)) vrátí 10 pro datum 7. 11. 2018.
Alternativní možnost
Alternativním způsobem je použití tabulky PivotTable, která vám může poskytnout více možností než součet hodnot. Podrobné informace najdete v článku Jak seskupit dny bez času pomocí PivotTabulky.
Podívejte se také na související články Jak vypočítat vážený průměr pomocí SUMPRODUCT, Jak sečíst 2d rozsahy pomocí SUMPRODUCT a Jak sečíst hodnoty s operátorem OR pomocí SUMPRODUCT s více kritérii.