Prenesite primer delovnega zvezka
Ta vadnica bo pokazala, kako uporabiti funkcije SUMPRODUCT in SUMIFS za seštevanje podatkov, ki izpolnjujejo določena merila, na več listih v Excelu in Google Preglednicah.
Redna vsota na več listih
Včasih lahko vaši podatki obsegajo več delovnih listov v Excelovi datoteki. To je običajno za podatke, ki se občasno zbirajo. Vsak list v delovnem zvezku lahko vsebuje podatke za nastavljeno časovno obdobje. Želimo formulo, ki sešteje podatke v dveh ali več listih.
Funkcija SUM vam omogoča enostavno seštevanje podatkov na več listih z uporabo Referenca 3D:
| 1 | = SUM (List1: List2! A1) |
To pa s funkcijo SUMIFS ni mogoče. Namesto tega moramo uporabiti bolj zapleteno formulo.
Vsota, če na več listih
Ta primer bo povzemal Število načrtovanih dobav za vsakogar Stranka na več delovnih listih, od katerih vsak vsebuje podatke, ki se nanašajo na drug mesec, s funkcijami SUMIFS, SUMPRODUCT in INDIRECT:
| 1 | = SUMPRODUCT (SUMIFS (INDIRECT ("'" & F3: F6 & "'!" & "D3: D7"), INDIRECT ("'" & F3: F6 & "'!" & "C3: C7"), H3)) |
Pojdimo skozi to formulo.
1. korak: Ustvarite formulo SUMIFS samo za 1 vhodni list:
Za seštevanje uporabimo funkcijo SUMIFS Število načrtovanih dobav avtor: Stranka za en sam vnosni podatkovni list:
| 1 | = SUMIFS (D3: D7, C3: C7, H3) |
2. korak: Formuli dodajte sklic na list
Rezultat formule ohranimo enak, vendar določimo, da so vhodni podatki v klicanem listu "2. korak"
| 1 | = SUMIFS ('2. korak'! D3: D7, '2. korak'! C3: C7, H3) |
3. korak: Nest Inside SUMPRODUCT funkcija
Za pripravo formule za izvajanje izračunov SUMIFS na več listih in nato za seštevanje rezultatov dodamo funkcijo SUMPRODUCT okoli formule
| 1 | = PODROČJE (SUMIFS ('Korak 3'! D3: D7, 'Korak 3'! C3: C7, H3)) |
Uporaba funkcije SUMIFS na enem listu prinese eno samo vrednost. Na več listih funkcija SUMIFS prikaže niz vrednosti (po eno za vsak delovni list). Za seštevanje vrednosti v tem nizu uporabljamo funkcijo SUMPRODUCT.
4. korak: Referenco lista zamenjajte s seznamom imen listov
Želimo zamenjati Ime lista del formule s seznamom podatkov, ki vsebuje vrednosti: Jan, Februar, Mar, in Apr. Ta seznam je shranjen v celicah F3: F6.
INDIRECT funkcija zagotavlja, da se prikaže seznam besedil Imena listov se obravnava kot del veljavne sklice na celico v funkciji SUMIFS.
| 1 | = SUMPRODUCT (SUMIFS (INDIRECT ("'" & F3: F6 & "'!" & "D3: D7"), INDIRECT ("'" & F3: F6 & "'!" & "C3: C7"), H3)) |
V tej formuli je predhodno zapisana referenca obsega:
| 1 | "Korak 3"! D3: D7 |
Zamenja se z:
| 1 | INDIRECT ("'" & F3: F6 & "'!" & "D3: D7") |
Navedniki otežujejo branje formule, zato je tukaj prikazana z dodatnimi presledki:
| 1 | INDIRECT ("'" & F3: F6 & "'!" & "D3: D7") |
Uporaba tega načina sklicevanja na seznam celic nam omogoča tudi povzemanje podatkov z več listov, ki ne sledijo številčnemu slogu seznama. Standardna referenca 3D bi zahtevala, da so imena listov v slogu: Input1, Input2, Input3 itd., Vendar zgornji primer omogoča uporabo seznama vseh Imena listov in jih sklicevati v ločeni celici.
Zaklepanje referenc celic
Za lažje branje formul smo prikazali formule brez zaklenjenih sklicev na celice:
| 1 | = SUMPRODUCT (SUMIFS (INDIRECT ("'" & F3: F6 & "'!" & "D3: D7"), INDIRECT ("'" & F3: F6 & "'!" & "C3: C7"), H3)) |
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 (SUMIFS (INDIRECT ("'" & $ F $ 3: $ F $ 6 & "'!" & "D3: D7"), INDIRECT ("'" & $ F $ 3: $ F $ 6 & "'!" & "C3: C7"), H3)) |
Če želite izvedeti več, preberite naš članek o zaklepanju referenc celic.
Vsota, če na več listih v Google Preglednicah
Uporaba funkcije INDIRECT za sklicevanje na seznam listov v funkciji SUMPRODUCT in SUMIFS trenutno ni mogoča v Google Preglednicah.
Namesto tega je mogoče za vsak vnosni list narediti ločene izračune SUMIFS in rezultate sešteti skupaj:
| 1234 | = SUMIFS (Jan! D3: D7, Jan! C3: C7, H3)+SUMIFS (februar! D3: D7, februar! C3: C7, H3)+SUMIFS (Mar! D3: D7, Mar! C3: C7, H3)+SUMIFS (apr! D3: D7, apr! C3: C7, H3) |
