Laadige alla töövihiku näide
See õpetus näitab, kuidas kasutada funktsioone SUMPRODUCT ja SUMIFS, et liita andmed, mis vastavad teatud kriteeriumidele Exceli ja Google'i arvutustabelite mitmele lehele.
Tavaline summa mitmel lehel
Mõnikord võivad teie andmed hõlmata mitmeid Exceli faili töölehti. See on tavaline perioodiliselt kogutavate andmete puhul. Iga töövihiku leht võib sisaldada andmeid teatud ajavahemiku kohta. Me tahame valemit, mis liidaks kokku andmed, mis sisalduvad kahes või enamas lehes.
Funktsioon SUM võimaldab hõlpsalt liita andmeid mitme lehe vahel, kasutades a 3D viide:
| 1 | = SUM (leht 1: leht 2! A1) |
Funktsiooniga SUMIFS pole see aga võimalik. Selle asemel peame kasutama keerulisemat valemit.
Summa mitme lehe vahel
See näide võtab kokku Planeeritud tarnete arv igaühele Klient mitmele töölehele, millest igaüks sisaldab andmeid erineva kuu kohta, kasutades SUMIFS, SUMPRODUCT ja FUNDECT funktsioone:
| 1 | = SUMPRODUCT (SUMIFS (KAUDSED ("'" & F3: F6 & "'!" & "D3: D7"), KAUDSED ("" "& F3: F6 &" '! "&" C3: C7 "), H3)) |
Lähme läbi selle valemi.
Samm: looge SUMIFS valem ainult 1 sisendlehele:
Summa summeerimiseks kasutame funktsiooni SUMIFS Planeeritud tarnete arv kõrval Klient ühe sisendandmelehe jaoks:
| 1 | = SUMIFS (D3: D7, C3: C7, H3) |
Samm: lisage valemile leheviide
Valemi tulemus jääb samaks, kuid täpsustame, et sisendandmed on lehel nimega „2. samm”
| 1 | = SUMIFS ('2. samm'! D3: D7, '2. etapp'! C3: C7, H3) |
3. toiming: pesake funktsiooni SUMPRODUCT sees
Valemi ettevalmistamiseks mitmele lehele SUMIFS -arvutuste tegemiseks ja seejärel tulemuste kokkuvõtmiseks lisame valemi ümber funktsiooni SUMPRODUCT
| 1 | = SUMPRODUCT (SUMIFS ('samm 3'! D3: D7, 'samm 3'! C3: C7, H3)) |
Funktsiooni SUMIFS kasutamine ühel lehel annab ühe väärtuse. Funktsioon SUMIFS väljastab mitme lehe kaudu hulga väärtusi (üks iga töölehe jaoks). Selle massiivi väärtuste summeerimiseks kasutame funktsiooni SUMPRODUCT.
Samm: asendage lehe viide lehtede nimede loendiga
Soovime asendada Lehe nimi valemi osa koos andmete loendiga, mis sisaldab väärtusi: Jaan, Veebr, Märtsja Apr. See loend on salvestatud lahtritesse F3: F6.
Funktsioon INDIRECT tagab tekstiloendi kuvamise Lehtede nimed Funktsioonis SUMIFS käsitletakse kehtiva lahtri viite osana.
| 1 | = SUMPRODUCT (SUMIFS (KAUDSED ("'" & F3: F6 & "'!" & "D3: D7"), KAUDSED ("" "& F3: F6 &" '! "&" C3: C7 "), H3)) |
Selles valemis on eelnevalt kirjutatud vahemiku viide:
| 1 | "3. samm"! D3: D7 |
Asendatakse järgmisega:
| 1 | KAUDSED ("" "& F3: F6 &" '! "&" D3: D7 ") |
Jutumärgid muudavad valemi raskesti loetavaks, nii et siin näidatakse seda tühikutega:
| 1 | KAUDSED ("" "& F3: F6 &" '! "&" D3: D7 ") |
Sellise lahtrite loendile viitamise viisi kasutamine võimaldab meil võtta kokku ka mitme lehe andmed, mis ei järgi numbrilist loendistiili. Tavaline 3D -viide nõuaks, et lehtede nimed oleksid stiilis: Sisend1, Sisend2, Sisend3 jne, kuid ülaltoodud näide võimaldab kasutada kõigi Lehtede nimed ja neile eraldi lahtrisse viidata.
Lahtrite viidete lukustamine
Meie valemite lugemise hõlbustamiseks oleme näidanud valemeid ilma lahtriviideta:
| 1 | = SUMPRODUCT (SUMIFS (KAUDSED ("'" & F3: F6 & "'!" & "D3: D7"), KAUDSED ("" "& F3: F6 &" '! "&" C3: C7 "), H3)) |
Kuid need valemid ei tööta korralikult, kui kopeerite ja kleebite mujale oma faili. Selle asemel peaksite kasutama selliseid lukustatud lahtriviiteid:
| 1 | = SUMPRODUCT (SUMIFS (KAUDSED ("'" & $ F $ 3: $ F $ 6 & "'!" & "D3: D7"), EHTSED ("" "& $ F $ 3: $ F $ 6 &" '! "& "C3: C7"), H3)) |
Lisateabe saamiseks lugege meie artiklit lahtrite viidete lukustamise kohta.
Summa, kui Google'i arvutustabelites on mitu lehte
Funktsiooni INDIRECT kasutamine lehtede loendile viitamiseks funktsioonis SUMPRODUCT ja SUMIFS ei ole praegu Google'i arvutustabelites võimalik.
Selle asemel saab iga sisendlehe jaoks teha eraldi SUMIFS -i arvutused ja tulemused liita:
| 1234 | = SUMIFS (jaan! D3: D7, jaan! C3: C7, H3)+SUMIFS (veebruar! D3: D7, veebruar! C3: C7, H3)+SUMIFS (märts! D3: D7, märts! C3: C7, H3)+SUMIFS (aprill! D3: D7, aprill! C3: C7, H3) |
