Lejupielādējiet darbgrāmatas piemēru
Šī apmācība parādīs, kā izmantot funkcijas SUMPRODUCT un SUMIFS, lai apkopotu datus, kas atbilst noteiktiem kritērijiem vairākās Excel un Google izklājlapu lapās.
Regulāra summa vairākās lapās
Dažreiz jūsu dati var ietvert vairākas darblapas Excel failā. Tas ir raksturīgi periodiski apkopotiem datiem. Katrā darbgrāmatas lapā var būt dati par noteiktu laika periodu. Mēs vēlamies formulu, kas apkopo datus, kas ietverti divās vai vairākās lapās.
Funkcija SUM ļauj ērti apkopot datus vairākās lapās, izmantojot 3D atsauce:
| 1 | = SUM (1. lapa: 2. lapa! A1) |
Tomēr tas nav iespējams, izmantojot funkciju SUMIFS. Tā vietā mums jāizmanto sarežģītāka formula.
Summa, ja vairākas lapas
Šis piemērs apkopos Plānotās piegādes katram Klients vairākās darblapās, katra satur datus par citu mēnesi, izmantojot SUMIFS, SUMPRODUCT un NETIEŠĀS funkcijas:
| 1 | = SUMPRODUCT (SUMIFS (NETIEŠS ("'" & F3: F6 & "'!" & "D3: D7"), NETIEŠS ("" "& F3: F6 &" '! "&" C3: C7 "), H3)) |
Apskatīsim šo formulu.
1. darbība: izveidojiet SUMIFS formulu tikai 1 ievades lapai:
Lai summētu, mēs izmantojam funkciju SUMIFS Plānotās piegādes pēc Klients vienai ievades datu lapai:
| 1 | = SUMIFS (D3: D7, C3: C7, H3) |
2. darbība: pievienojiet formulai lapas atsauci
Mēs saglabājam formulas rezultātu nemainīgu, bet mēs norādām, ka ievades dati atrodas izsauktajā lapā “2. darbība”
| 1 | = SUMIFS ('2. darbība'! D3: D7, '2. darbība'! C3: C7, H3) |
3. darbība: ievietojiet funkciju SUMPRODUCT
Lai sagatavotu formulu, lai veiktu SUMIFS aprēķinus vairākās lapās un pēc tam summētu rezultātus, mēs ap formulu pievienojam SUMPRODUCT funkciju
| 1 | = SUMPRODUCT (SUMIFS ('3. darbība'! D3: D7, '3. darbība'! C3: C7, H3)) |
Izmantojot funkciju SUMIFS vienā lapā, tiek iegūta viena vērtība. Vairākās lapās funkcija SUMIFS izvada vērtību masīvu (pa vienai katrai darblapai). Mēs izmantojam funkciju SUMPRODUCT, lai apkopotu vērtības šajā masīvā.
4. solis: aizstājiet lapas atsauci ar lapu nosaukumu sarakstu
Mēs vēlamies nomainīt Lapas nosaukums formulas daļa ar datu sarakstu, kurā ir vērtības: Jan, Febr, Marts, un Apr. Šis saraksts tiek saglabāts šūnās F3: F6.
Funkcija INDIRECT nodrošina teksta saraksta parādīšanu Lapu nosaukumi tiek uzskatīta par daļu no derīgas šūnu atsauces funkcijā SUMIFS.
| 1 | = SUMPRODUCT (SUMIFS (NETIEŠS ("'" & F3: F6 & "'!" & "D3: D7"), NETIEŠS ("" "& F3: F6 &" '! "&" C3: C7 "), H3)) |
Šajā formulā iepriekš uzrakstītā diapazona atsauce:
| 1 | “3. darbība”! D3: D7 |
Tiek aizstāts ar:
| 1 | NETIEŠI ("" "& F3: F6 &" '! "&" D3: D7 ") |
Pēdiņas apgrūtina formulas lasīšanu, tāpēc šeit tā ir parādīta ar pievienotām atstarpēm:
| 1 | NETIEŠI ("" "& F3: F6 &" '! "&" D3: D7 ") |
Izmantojot šo veidu atsauci uz šūnu sarakstu, mēs varam arī apkopot datus no vairākām lapām, kas neatbilst skaitliskajam saraksta stilam. Standarta 3D atsaucei būtu nepieciešams, lai lapu nosaukumiem būtu šāds stils: Input1, Input2, Input3 utt., Bet iepriekš minētais piemērs ļauj izmantot visu Lapu nosaukumi un atsaukties uz tiem atsevišķā šūnā.
Šū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 (SUMIFS (NETIEŠS ("'" & F3: F6 & "'!" & "D3: D7"), NETIEŠS ("" "& F3: F6 &" '! "&" C3: C7 "), H3)) |
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 (SUMIFS (NETIEŠI ("" "& $ F $ 3: $ F $ 6 &" '! "&" D3: D7 "), NETIEŠI (" "" & $ F $ 3: $ F $ 6 & "'!" & "C3: C7"), H3)) |
Lai uzzinātu vairāk, izlasiet mūsu rakstu par šūnu atsauču bloķēšanu.
Summa, ja Google izklājlapās ir vairākas lapas
Funkcijas INDIRECT izmantošana, lai izmantotu atsauci uz lapu SUMPRODUCT un SUMIFS funkciju sarakstu, Google izklājlapās pašlaik nav iespējama.
Tā vietā katrai ievades lapai var veikt atsevišķus SUMIFS aprēķinus un rezultātus saskaitīt kopā:
| 1234 | = SUMIFS (Jan! D3: D7, Jan! C3: C7, H3)+SUMIFS (februāris! D3: D7, februāris! C3: C7, H3)+SUMIFS (marts! D3: D7, marts! C3: C7, H3)+SUMIFS (Apr! D3: D7, Apr! C3: C7, H3) |
