Lataa esimerkkityökirja
Tämä opetusohjelma osoittaa, kuinka SUMPRODUCT- ja SUMIFS -toimintojen avulla voidaan laskea yhteen tietyt kriteerit täyttävät tiedot useilla Excel- ja Google Sheets -arkkeilla.
Säännöllinen summa useilla arkeilla
Joskus tietosi voivat ulottua useisiin Excel -tiedoston laskentataulukoihin. Tämä on yleistä säännöllisesti kerättäville tiedoille. Työkirjan jokainen arkki saattaa sisältää tietoja tietyltä ajanjaksolta. Haluamme kaavan, joka laskee yhteen kahden tai useamman taulukon tiedot.
SUM -toiminnon avulla voit helposti laskea yhteen useiden arkkien tiedot käyttämällä 3D -viite:
| 1 | = SUMMA (arkki1: arkki2! A1) |
Tämä ei kuitenkaan ole mahdollista SUMIFS -toiminnolla. Sen sijaan meidän on käytettävä monimutkaisempaa kaavaa.
Summa, jos useilla arkeilla
Tämä esimerkki summaa Suunniteltujen toimitusten määrä jokaiselle Asiakas useilla laskentataulukoilla, joista kukin sisältää eri kuukauteen liittyviä tietoja SUMIFS-, SUMPRODUCT- ja Epäsuorat -funktioiden avulla:
| 1 | = SUMPRODUCT (SUMIFS (Epäsuora ("'" & F3: F6 & "'!" & "D3: D7"), Epäsuora ("" "& F3: F6 &" '! "&" C3: C7 "), H3)) |
Käydään tämä kaava läpi.
Vaihe 1: Luo SUMIFS -kaava vain 1 syöttöarkille:
Käytämme SUMIFS -funktiota summan laskemiseen Suunniteltujen toimitusten määrä käyttäjältä Asiakas yksittäiselle syöttötietolomakkeelle:
| 1 | = SUMIFS (D3: D7, C3: C7, H3) |
Vaihe 2: Lisää taulukkoon viittaus kaavaan
Pidämme kaavan tuloksen samana, mutta määritämme, että syöttötiedot ovat taulukossa nimeltä "Vaihe 2"
| 1 | = SUMIFS ('Vaihe 2'! D3: D7, 'Vaihe 2'! C3: C7, H3) |
Vaihe 3: Pesä SUMPRODUCT -toiminnon sisällä
Valmistaaksemme kaavan suorittamaan SUMIFS -laskutoimituksia useilla arkeilla ja laskemaan sitten tulokset yhteen, lisäämme SUMPRODUCT -funktion kaavan ympärille
| 1 | = SUMPRODUCT (SUMIFS ('Vaihe 3'! D3: D7, 'Vaihe 3'! C3: C7, H3)) |
SUMIFS -funktion käyttäminen yhdellä arkilla tuottaa yhden arvon. SUMIFS -toiminto tuottaa useiden arkkien joukon arvoja (yksi kullekin laskentataulukolle). Käytämme SUMPRODUCT -funktiota tämän taulukon arvojen laskemiseen.
Vaihe 4: Korvaa arkin viite taulukon nimiluettelolla
Haluamme korvata Arkin nimi osa kaavaa, jossa on tietoluettelo, joka sisältää arvot: Tammi, Helmikuu, Maalisja Huhti. Tämä luettelo on tallennettu soluihin F3: F6.
INDIRECT -toiminto varmistaa, että tekstiluettelo näkyy Arkkien nimet käsitellään osana kelvollista soluviittausta SUMIFS -funktiossa.
| 1 | = SUMPRODUCT (SUMIFS (Epäsuora ("'" & F3: F6 & "'!" & "D3: D7"), Epäsuora ("" "& F3: F6 &" '! "&" C3: C7 "), H3)) |
Tässä kaavassa aiemmin kirjoitettu alueviite:
| 1 | 'Vaihe 3'! D3: D7 |
Korvataan seuraavilla:
| 1 | Epäsuora ("" "& F3: F6 &" '! "&" D3: D7 ") |
Lainausmerkit vaikeuttavat kaavan lukemista, joten tässä se esitetään lisättyjen välilyönnien kanssa:
| 1 | Epäsuora ("" "& F3: F6 &" '! "&" D3: D7 ") |
Käyttämällä tätä tapaa viitata soluluetteloon voimme myös tehdä yhteenvedon useista arkeista, jotka eivät noudata numeerista luettelotyyliä. Normaali 3D -viite edellyttäisi, että arkkien nimet ovat tyylillä: Input1, Input2, Input3 jne., Mutta yllä olevassa esimerkissä voit käyttää luetteloa kaikista Arkkien nimet ja että niihin viitataan erillisessä solussa.
Soluviitteiden lukitseminen
Kaavojemme lukemisen helpottamiseksi olemme näyttäneet kaavat ilman lukittuja soluviittauksia:
| 1 | = SUMPRODUCT (SUMIFS (Epäsuora ("'" & F3: F6 & "'!" & "D3: D7"), Epäsuora ("" "& F3: F6 &" '! "&" C3: C7 "), H3)) |
Mutta nämä kaavat eivät toimi kunnolla, kun kopioit ja liität muualle tiedostoosi. Käytä sen sijaan lukittuja soluviittauksia seuraavasti:
| 1 | = TUMMA "C3: C7"), H3)) |
Lue artikkelimme soluviitteiden lukitsemisesta saadaksesi lisätietoja.
Summa, jos useilla Google Sheetsin arkeilla
INDIRECT -funktion käyttäminen SUMPRODUCT- ja SUMIFS -toimintojen taulukkolaskentaan ei ole tällä hetkellä mahdollista Google Sheetsissä.
Sen sijaan jokaiselle syöttölomakkeelle voidaan tehdä erilliset SUMIFS -laskelmat ja tulokset laskea yhteen:
| 1234 | = SUMIFS (tammikuu! D3: D7, tammikuu! C3: C7, H3)+SUMIFS (helmikuu! D3: D7, helmikuu! C3: C7, H3)+SUMIFS (maaliskuu! D3: D7, maalis! C3: C7, H3)+SUMIFS (huhti! D3: D7, huhtikuu! C3: C7, H3) |
