Stiahnite si ukážkový zošit
Tento tutoriál predvedie, ako sčítať údaje zodpovedajúce konkrétnym číslam týždňov v Exceli a Tabuľkách Google.
Suma Ak podľa týždňa Číslo
Na „súčet ak“ podľa čísla týždňa použijeme funkciu SUMIFS. Najprv však musíme pridať pomocný stĺpec obsahujúci funkciu WEEKNUM.
The Číslo týždňa pomocný stĺpec sa počíta pomocou funkcie WEEKNUM:
| 1 | = WEEKNUM (B3,1) |
Ďalej použijeme funkciu SUMIFS na zhrnutie všetkých Predaj ktoré sa odohrávajú v konkrétnom Číslo týždňa.
| 1 | = SUMIFS (D3: D9, C3: C9, F3) |
Suma podľa počtu týždňov - bez stĺpca pomocníka
Metódu pomocného stĺpca je možné ľahko sledovať, ale môžete tiež zopakovať výpočet v jednom vzorci pomocou funkcie SUMPRODUCT v kombinácii s funkciou WEEKNUM, aby ste súčet Celkový počet predajov od Číslo týždňa.
| 1 | = SUMPRODUCT (-(TÝŽDEŇ (B3: B9+0,1) = E3), C3: C9) |
V tomto prípade môžeme použiť funkciu SUMPRODUCT na vykonávanie komplikovaných výpočtov „sum if“. Pozrime sa na vyššie uvedený príklad.
Toto je náš konečný vzorec:
| 1 | = SUMPRODUCT (-(TÝŽDEŇ (B3: B9+0,1) = E3), C3: C9) |
Najprv funkcia SUMPRODUCT uvádza pole hodnôt z rozsahov buniek:
| 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}) |
Potom funkcia WEEKNUM vypočíta Číslo týždňa každého z Termíny predaja.
Funkcia WEEKNUM nie je navrhnutá tak, aby pracovala s hodnotami poľa, preto musíme pre WEEKNUM pridať nulu („+0“), aby boli hodnoty správne spracované.
| 1 | = SUMPRODUCT (-({1; 2; 2; 3; 3; 3; 4} = 1), {4; 9; 1; 7; 6; 2; 5}) |
Číslo týždňa hodnoty rovnajúce sa 1 sa zmenia na TRUE hodnoty.
| 1 | = SUMPRODUCT (-({TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE}), {4; 9; 1; 7; 6; 2; 5}) |
Ďalej dvojité pomlčky (-) konvertujú hodnoty TRUE a FALSE na 1 s a 0 s:
| 1 | = SUMPRODUCT ({1; 0; 0; 0; 0; 0; 0}, {4; 9; 1; 7; 6; 2; 5}) |
Funkcia SUMPRODUCT potom znásobí každú dvojicu záznamov v poliach a vytvorí pole Počet predajov ktoré majú a Číslo týždňa z 1:
| 1 | = SUMPRODUCT ({4; 0; 0; 0; 0; 0; 0}) |
Nakoniec sa čísla v poli sčítajú:
| 1 | =4 |
Tento vzorec sa potom zopakuje pre ďalšie možné hodnoty Číslo týždňa.
Viac podrobností o použití booleovských príkazov a príkazu „-“ vo funkcii SUMPRODUCT nájdete tu.
Zamknutie odkazov na bunky
Aby boli naše vzorce ľahšie čitateľné, ukázali sme vzorce bez uzamknutých odkazov na bunky:
| 1 | = SUMPRODUCT (-(TÝŽDEŇ (B3: B9+0,1) = E3), C3: C9) |
Tieto vzorce však nebudú fungovať správne, keď ich skopírujete a vložíte na iné miesto v súbore. Namiesto toho by ste mali použiť uzamknuté odkazy na bunky takto:
| 1 | = SUMPRODUCT (-(TÝŽDEŇ ($ B $ 3: $ B $ 9+0,1) = E3), $ C $ 3: $ C $ 9) |
Ak sa chcete dozvedieť viac, prečítajte si náš článok o uzamknutí odkazov na bunky.
Suma podľa počtu týždňov v Tabuľkách Google
Tieto vzorce fungujú v Tabuľkách Google úplne rovnako ako v programe Excel.
Funkcia WEEKNUM je však v Tabuľkách Google flexibilnejšia než v Exceli a akceptuje vstupy a výstupy poľa. Operácia {Array} +0 vo vzorci WEEKNUM (B3: B9+0,1) sa preto nevyžaduje.
Úplný vzorec SUMPRODUCT je možné zapísať do Tabuliek Google ako:
| 1 | =SUMPRODUCT(--(WEEKNUM($ B $ 3: $ B $ 9+0,1)=E3),$ C $ 3: $ C $ 9) |
