Stáhněte si ukázkový sešit
Tento tutoriál předvede, jak sečíst data odpovídající konkrétním číslům týdnů v Excelu a Tabulkách Google.
Součet Pokud podle týdne
Abychom „sečetli“ podle čísla týdne, použijeme funkci SUMIFS. Nejprve však musíme přidat pomocný sloupec obsahující funkci WEEKNUM.
The Číslo týdne pomocný sloupec se vypočítá pomocí funkce WEEKNUM:
| 1 | = TÝDEN (B3,1) |
Dále použijeme funkci SUMIFS k sečtení všech Odbyt které se konají v konkrétním Číslo týdne.
| 1 | = SUMIFS (D3: D9, C3: C9, F3) |
Součet podle počtu týdnů - bez sloupce pomocníka
Metodu pomocného sloupce lze snadno sledovat, ale výpočet můžete také replikovat v jediném vzorci pomocí funkce SUMPRODUCT v kombinaci s funkcí WEEKNUM k sečtení Celkový počet prodejů podle Číslo týdne.
| 1 | = SUMPRODUCT (-(TÝDEN (B3: B9+0,1) = E3), C3: C9) |
V tomto příkladu můžeme použít funkci SUMPRODUCT k provádění komplikovaných výpočtů „sum if“. Pojďme si projít výše uvedený příklad.
Toto je náš konečný vzorec:
| 1 | = SUMPRODUCT (-(TÝDEN (B3: B9+0,1) = E3), C3: C9) |
Za prvé, funkce SUMPRODUCT uvádí pole hodnot z rozsahů buněk:
| 1 | =(--(({"1/3/2020"; "1/6/2020"; "1/9/2020"; "1/12/2020"; "1/15/2020"; "1/18/2020"; "1/21/2020"}+0,1)=1), {4; 9; 1; 7; 6; 2; 5}) |
Poté funkce WEEKNUM vypočítá Číslo týdne každého z Termíny prodejů.
Funkce WEEKNUM není navržena pro práci s hodnotami pole, takže pro WEEKNUM musíme hodnoty nula („+0“) správně zpracovat.
| 1 | = SUMPRODUCT (-({1; 2; 2; 3; 3; 3; 4} = 1), {4; 9; 1; 7; 6; 2; 5}) |
Číslo týdne hodnoty rovné 1 se změní na SKUTEČNÉ hodnoty.
| 1 | = SUMPRODUCT (-({TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE}), {4; 9; 1; 7; 6; 2; 5}) |
Dále dvojité pomlčky (-) převádějí hodnoty PRAVDA a NEPRAVDA na 1 s a 0 s:
| 1 | = SUMPRODUCT ({1; 0; 0; 0; 0; 0; 0}, {4; 9; 1; 7; 6; 2; 5}) |
Funkce SUMPRODUCT pak vynásobí každou dvojici záznamů v polích a vytvoří pole Počet prodejů které mají a Číslo týdne z 1:
| 1 | = SUMPRODUCT ({4; 0; 0; 0; 0; 0; 0}) |
Nakonec jsou čísla v poli sečtena:
| 1 | =4 |
Tento vzorec se pak opakuje pro další možné hodnoty Číslo týdne.
Další podrobnosti o použití booleovských příkazů a příkazu „-“ ve funkci SUMPRODUCT najdete zde.
Zamykání odkazů na buňky
Aby byly naše vzorce čitelnější, ukázali jsme vzorce bez zamčených odkazů na buňky:
| 1 | = SUMPRODUCT (-(TÝDEN (B3: B9+0,1) = E3), C3: C9) |
Tyto vzorce ale nebudou správně fungovat, když je zkopírujete a vložíte jinam do souboru. Místo toho byste měli použít uzamčené odkazy na buňky takto:
| 1 | = SUMPRODUCT (-(TÝDEN ($ B $ 3: $ B $ 9+0,1) = E3), $ C $ 3: $ C $ 9) |
Další informace najdete v našem článku o zamykání odkazů na buňky.
Součet podle počtu týdnů v Tabulkách Google
Tyto vzorce fungují v Tabulkách Google úplně stejně jako v Excelu.
Funkce WEEKNUM je však v Tabulkách Google flexibilnější než v Excelu a přijímá vstupy a výstupy pole. Operace {Array} +0 ve vzorci WEEKNUM (B3: B9+0,1) proto není vyžadována.
Celý vzorec SUMPRODUCT lze v Tabulkách Google zapsat jako:
| 1 | =SUMPRODUKT(--(WEEKNUM($ B $ 3: $ B $ 9+0,1)=E3),$ C $ 3: $ C $ 9) |
