Prenesite primer delovnega zvezka
Ta vadnica bo pokazala, kako sešteti podatke, ki ustrezajo številkam določenih tednov v Excelu in Google Preglednicah.
Seštevek Če po številki tedna
Za "seštevanje če" po številki tedna bomo uporabili funkcijo SUMIFS. Najprej pa moramo dodati pomožni stolpec, ki vsebuje funkcijo WEEKNUM.
The Številka tedna pomožni stolpec se izračuna s funkcijo WEEKNUM:
| 1 | = WEEKNUM (B3,1) |
Nato bomo za seštevanje vseh uporabili funkcijo SUMIFS Prodaja ki potekajo v določenem Številka tedna.
| 1 | = SUMIFS (D3: D9, C3: C9, F3) |
Vsota po številki tedna - brez stolpca pomočnika
Metodi pomožnega stolpca je enostavno slediti, lahko pa tudi izračun ponovite v eni formuli z uporabo funkcije SUMPRODUCT v kombinaciji s funkcijo WEEKNUM, da seštejete Skupno število prodaj avtor: Številka tedna.
| 1 | = PODROČJE (-(TEDEN (B3: B9+0,1) = E3), C3: C9) |
V tem primeru lahko uporabimo funkcijo SUMPRODUCT za izvedbo zapletenih izračunov "vsote, če". Pojdimo skozi zgornji primer.
To je naša zadnja formula:
| 1 | = PODROČJE (-(TEDEN (B3: B9+0,1) = E3), C3: C9) |
Prvič, funkcija SUMPRODUCT navaja niz vrednosti iz obsegov celic:
| 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}) |
Nato funkcija WEEKNUM izračuna Številka tedna vsakega od Datumi prodaje.
Funkcija WEEKNUM ni zasnovana za delo z vrednostmi matrike, zato moramo dodati WEEKNUM za nujno pravilno obdelavo vrednosti ("+0").
| 1 | = PODROČJE (-({1; 2; 2; 3; 3; 3; 4} = 1), {4; 9; 1; 7; 6; 2; 5}) |
Številka tedna vrednosti enake 1 se spremenijo v vrednosti TRUE.
| 1 | = SUMPRODUCT (-({TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE}), {4; 9; 1; 7; 6; 2; 5}) |
Nato dvojne črtice (-) pretvorijo vrednosti TRUE in FALSE v 1s in 0s:
| 1 | = PODROČJE ({1; 0; 0; 0; 0; 0; 0}, {4; 9; 1; 7; 6; 2; 5}) |
Funkcija SUMPRODUCT nato pomnoži vsak par vnosov v matrikah, da ustvari matriko Število prodaj ki imajo a Številka tedna od 1:
| 1 | = PODROČJE ({4; 0; 0; 0; 0; 0; 0}) |
Končno se številke v matriki seštejejo skupaj:
| 1 | =4 |
Ta formula se nato ponovi za druge možne vrednosti Številka tedna.
Več podrobnosti o uporabi logičnih stavkov in ukaza "-" v funkciji SUMPRODUCT najdete tukaj.
Zaklepanje referenc celic
Za lažje branje formul smo prikazali formule brez zaklenjenih sklicev na celice:
| 1 | = PODROČJE (-(TEDEN (B3: B9+0,1) = E3), C3: C9) |
Toda te formule ne bodo delovale pravilno, če jih kopirate in prilepite drugam v datoteko. Namesto tega uporabite zaklenjene sklice na celice, kot je ta:
| 1 | = SUMPRODUCT (-(WEEKNUM ($ B $ 3: $ B $ 9+0,1) = E3), $ C $ 3: $ C $ 9) |
Če želite izvedeti več, preberite naš članek o zaklepanju referenc celic.
Seštevek Če po številki tedna v Google Preglednicah
Te formule delujejo popolnoma enako v Google Preglednicah kot v Excelu.
Vendar je funkcija WEEKNUM bolj prilagodljiva v Google Preglednicah kot v Excelu in sprejema vnose in izhode matrike. Zato operacija {Array} +0 v formuli WEEKNUM (B3: B9+0,1) ni potrebna.
Celotna formula SUMPRODUCT je lahko v Google Preglednicah zapisana kot:
| 1 | =PODROČJE(--(WEEKNUM($ B $ 3: $ B $ 9+0,1)=E3),$ C $ 3: $ C $ 9) |
