Töltse le a példa munkafüzetet
Ez az oktatóanyag bemutatja, hogyan kell használni a SUMPRODUCT és a SUMIFS függvényeket bizonyos feltételeknek megfelelő adatok összegzésére az Excel és a Google Táblázatok több lapján.
Rendszeres összeg több lapon
Néha az adatok több munkalapra is kiterjedhetnek egy Excel fájlban. Ez gyakori a rendszeresen gyűjtött adatok esetében. A munkafüzet minden lapja tartalmazhat egy meghatározott időtartamra vonatkozó adatokat. Olyan képletet szeretnénk, amely összegezi a két vagy több lapon található adatokat.
A SUM funkció lehetővé teszi az adatok egyszerű összegzését több lapon keresztül a 3D referencia:
| 1 | = SUM (1. lap: 2. lap! A1) |
Ez azonban nem lehetséges a SUMIFS funkcióval. Ehelyett bonyolultabb képletet kell használnunk.
Összeg, ha több lapon keresztül
Ez a példa összegzi a Tervezett szállítások száma az egyes Vevő több munkalapon, amelyek mindegyike más hónapra vonatkozó adatokat tartalmaz a SUMIFS, SUMPRODUCT és INDIRECT függvények használatával:
| 1 | = SUMPRODUCT (SUMIFS (KÖZVETLEN ("'" & F3: F6 & "'!" & "D3: D7"), KÖZVETLEN ("" "& F3: F6 &" '! "&" C3: C7 "), H3)) |
Nézzük végig ezt a képletet.
1. lépés: SUMIFS képlet létrehozása csak 1 bemeneti lapon:
A SUMIFS függvényt használjuk az összeg összegzésére Tervezett szállítások száma által Vevő egyetlen bemeneti adatlap esetén:
| 1 | = SUMIFS (D3: D7, C3: C7, H3) |
2. lépés: Adjon hozzá egy referencialapot a képlethez
A képlet eredményét változatlanul tartjuk, de megadjuk, hogy a bemeneti adatok az ún '2. lépés'
| 1 | = SUMIFS ('2. lépés'! D3: D7, '2. lépés'! C3: C7, H3) |
3. lépés: Fészkeljen be egy SUMPRODUCT funkcióba
A képlet előkészítéséhez SUMIFS számítások elvégzésére több lapon, majd az eredmények összesítéséhez össze kell adnunk egy SUMPRODUCT függvényt a képlet körül
| 1 | = SUMPRODUCT (SUMIFS ('3. lépés'! D3: D7, '3. lépés'! C3: C7, H3)) |
A SUMIFS függvény egy lapon történő használata egyetlen értéket eredményez. Több lapon a SUMIFS függvény egy tömb értéket ad ki (egyet minden munkalaphoz). A SUMPRODUCT függvényt használjuk a tömb értékeinek összesítésére.
4. lépés: Cserélje ki a lapreferenciát a lapnevek listájával
Szeretnénk lecserélni a Lap neve a képlet része az értékeket tartalmazó adatlistával: Jan, Február, Márc, és Április. Ez a lista az F3: F6 cellákban található.
Az INDIRECT funkció biztosítja, hogy a szöveglista megjelenjen Lapnevek a SUMIFS függvényben érvényes cellahivatkozás részeként kezelik.
| 1 | = SUMPRODUCT (SUMIFS (KÖZVETLEN ("'" & F3: F6 & "'!" & "D3: D7"), KÖZVETLEN ("" "& F3: F6 &" '! "&" C3: C7 "), H3)) |
Ebben a képletben a korábban írt tartományhivatkozás:
| 1 | „3. lépés”! D3: D7 |
Helyébe a következő lép:
| 1 | KÖZVETLEN ("" "& F3: F6 &" '! "&" D3: D7 ") |
Az idézőjelek megnehezítik a képlet olvasását, ezért itt szóközökkel látható:
| 1 | KÖZVETLEN ("" "& F3: F6 &" '! "&" D3: D7 ") |
A cellalista ilyen hivatkozási módjának használata lehetővé teszi számunkra, hogy összefoglaljuk az adatokat több olyan lapról, amelyek nem követik a numerikus listastílust. Egy szabványos 3D -s referencia megköveteli, hogy a lapok nevei stílusban legyenek: Input1, Input2, Input3, stb., De a fenti példa lehetővé teszi, hogy Lapnevek és hogy külön cellában hivatkozzanak rájuk.
Cellareferenciák zárolása
A képleteink könnyebb olvashatósága érdekében megmutattuk a képleteket zárolt cellahivatkozások nélkül:
| 1 | = SUMPRODUCT (SUMIFS (KÖZVETLEN ("'" & F3: F6 & "'!" & "D3: D7"), KÖZVETLEN ("" "& F3: F6 &" '! "&" C3: C7 "), H3)) |
De ezek a képletek nem fognak megfelelően működni, ha másolja és beilleszti a fájl máshová. Ehelyett a következőképpen kell zárolt cellahivatkozásokat használni:
| 1 | = SUMPRODUCT (SUMIFS (KÖZVETLEN ("" "& $ F $ 3: $ F $ 6 &" '! "&" D3: D7 "), KÖZVETLEN (" "" & $ F $ 3: $ F $ 6 & "'!" & "C3: C7"), H3)) |
További információért olvassa el a Cellareferenciák zárolása című cikkünket.
Összeg, ha több munkalapon található a Google Táblázatokban
Az INDIRECT függvény használata a SUMPRODUCT és SUMIFS függvény laplistájára való hivatkozáshoz jelenleg nem lehetséges a Google Táblázatokban.
Ehelyett minden SUMIFS számítást el lehet végezni minden bemeneti lapon, és az eredményeket össze kell adni:
| 1234 | = SUMIFS (Jan! D3: D7, Jan! C3: C7, H3)+SUMIFS (február! D3: D7, február! C3: C7, H3)+SUMIFS (március! D3: D7, márc. C3: C7, H3)+SUMIFS (ápr. D3: D7, ápr. C3: C7, H3) |
