Lejupielādējiet darbgrāmatas piemēru
Šī apmācība parādīs, kā Excel un Google izklājlapās apkopot datus, kas atbilst konkrētiem nedēļas numuriem.
Summa Ja pēc nedēļas numura
Lai “summētu” pēc nedēļas numura, mēs izmantosim funkciju SUMIFS. Bet vispirms mums jāpievieno palīga kolonna, kas satur funkciju WEEKNUM.
The Nedēļas numurs palīga kolonna tiek aprēķināta, izmantojot funkciju WEEKNUM:
| 1 | = WEEKNUM (B3,1) |
Tālāk visu summēšanai izmantosim funkciju SUMIFS Pārdošana kas notiek noteiktā Nedēļas numurs.
| 1 | = SUMIFS (D3: D9, C3: C9, F3) |
Summa Ja pēc nedēļas numura - bez palīga kolonnas
Palīgkolonnas metodi ir viegli ievērot, taču aprēķinu var atkārtot arī vienā formulā, izmantojot funkciju SUMPRODUCT kombinācijā ar funkciju WEEKNUM, lai summētu Kopējais pārdošanas apjoms pēc Nedēļas numurs.
| 1 | = SUMPRODUCT (-(WEEKNUM (B3: B9+0,1) = E3), C3: C9) |
Šajā piemērā mēs varam izmantot funkciju SUMPRODUCT, lai veiktu sarežģītus summas aprēķinus. Apskatīsim iepriekš minēto piemēru.
Šī ir mūsu galīgā formula:
| 1 | = SUMPRODUCT (-(WEEKNUM (B3: B9+0,1) = E3), C3: C9) |
Pirmkārt, funkcija SUMPRODUCT uzskaita šūnu masīvu vērtību masīvu:
| 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}) |
Pēc tam funkcija WEEKNUM aprēķina Nedēļas numurs no katra Pārdošanas datumi.
Funkcija WEEKNUM nav paredzēta darbam ar masīva vērtībām, tāpēc, lai WEEKNUM pareizi apstrādātu vērtības, mums jāpievieno nulle (“+0”).
| 1 | = SUMPRODUCT (-({1; 2; 2; 3; 3; 3; 4} = 1), {4; 9; 1; 7; 6; 2; 5}) |
Nedēļas numurs vērtības, kas vienādas ar 1, tiek mainītas uz TRUE vērtībām.
| 1 | = SUMPRODUCT (-({TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE}), {4; 9; 1; 7; 6; 2; 5}) |
Tālāk dubultās svītras (-) pārvērš TRUE un FALSE vērtības par 1s un 0s:
| 1 | = SUMPRODUCT ({1; 0; 0; 0; 0; 0; 0}, {4; 9; 1; 7; 6; 2; 5}) |
Funkcija SUMPRODUCT reizina katru masīvu ierakstu pāri, lai izveidotu masīvu Pārdošanas gadījumu skaits kuriem ir a Nedēļas numurs no 1:
| 1 | = SUMPRODUCT ({4; 0; 0; 0; 0; 0; 0}) |
Visbeidzot, masīvā esošie skaitļi tiek summēti kopā:
| 1 | =4 |
Pēc tam šo formulu atkārto citām iespējamām vērtībām Nedēļas numurs.
Sīkāku informāciju par Būla paziņojumu izmantošanu un komandu “-” SUMPRODUCT funkcijā var atrast šeit.
Šūnu atsauču bloķēšana
Lai mūsu formulas būtu vieglāk lasāmas, mēs esam parādījuši formulas bez bloķētām šūnu atsaucēm:
| 1 | = SUMPRODUCT (-(WEEKNUM (B3: B9+0,1) = E3), C3: C9) |
Bet šīs formulas nedarbosies pareizi, kopējot un ielīmējot citur jūsu failā. Tā vietā jums vajadzētu izmantot šādas bloķētas šūnu atsauces:
| 1 | = SUMPRODUCT (-(WEEKNUM ($ B $ 3: $ B $ 9+0,1) = E3), $ C $ 3: $ C $ 9) |
Lai uzzinātu vairāk, izlasiet mūsu rakstu par šūnu atsauču bloķēšanu.
Summa Ja pēc nedēļas Google izklājlapās
Šīs formulas Google izklājlapās darbojas tieši tāpat kā programmā Excel.
Tomēr funkcija WEEKNUM Google izklājlapās ir elastīgāka nekā programmā Excel, un tā pieņem masīva ievades un izvades. Tāpēc {Masīvs} +0 darbība WEEKNUM (B3: B9+0,1) formulā nav nepieciešama.
Pilnu SUMPRODUCT formulu var ierakstīt Google izklājlapās šādi:
| 1 | =SUMPRODUCT(--(WEEKNUM($ B $ 3: $ B $ 9+0,1)=E3),3 ASV dolāri: 9 ASV dolāri) |
