Joten olemme jo oppineet, mikä 3D -viite on Excelissä. Hauska tosiasia on, että normaali Excel 3D -viittaus ei toimi ehdollisten toimintojen, kuten SUMIF -funktion, kanssa. Tässä artikkelissa opimme saamaan 3D -viittauksen toimimaan SUMIF -toiminnon kanssa.
Yleinen kaava SUMIF: lle, jossa on 3D -viite Excelissä
Se näyttää monimutkaiselta, mutta ei ole (niin paljon).
| = SUMPRODUCT (SUMIF (INDIRECT ("'" & name_range_of_sheet_names & "'!" & "Kriteeriväli"), kriteerit, INDIRECT ("'" & name_range_of_sheet_names & "'!" & "Sum_range")) |
"" "name_range_of_sheet_names" "":Se on nimetty alue, joka sisältää arkkien nimet. Tämä on hyvin tärkeää.
"ehto_alue":Se on alueen sisältävien ehtojen tekstiviite. (Sen pitäisi olla sama kaikissa kolmiulotteisen viitetyön arkeissa.)
kriteeri:Se on yksinkertaisesti ehto, jonka haluat asettaa yhteenvetoon. Se voi olla teksti- tai soluviittaus.
"summa_alue":Se on summa -alueen tekstiviite. (Sen pitäisi olla sama kaikissa kolmiulotteisen viitetyön arkeissa.)
Teoria riittää, 3D -viittaus SUMIF -toiminnon kanssa.
Esimerkki: Summa alueittain useista arkeista Excelin 3D -viittauksen avulla:
Otamme samat tiedot kuin yksinkertaisessa 3D -viittausesimerkissä. Tässä esimerkissä minulla on viisi erilaista taulukkoa, jotka sisältävät samanlaisia tietoja. Jokainen taulukko sisältää kuukauden tiedot. Pääarkissa haluan yksiköiden ja kokoelman summan alueittain kaikista arkeista. Tehdään se ensin yksiköille. Yksiköt ovat kaikilla arkeilla alueella D2: D14.
Jos nyt käytät normaalia 3D -viittausta SUMIF -toiminnolla,
= SUMIF (tammikuu: huhtikuu! A2: A14, mestari! B4, tammikuu: huhtikuu! D2: D14)
Se palauttaa #ARVO! virhe. Joten emme voi käyttää sitä. Käytämme yllä mainittua yleistä kaavaa.
Kirjoita tämä kaava soluun C3 käyttämällä yllä olevaa yleistä 3D -viittaavaa SUMIF -kaavaa Excelissä:
| = SUMPRODUCT (SUMIF (epäsuora ("'" & kuukaudet & "'!" & "A2: A14"), päällikkö! B3, epäsuora ("" "& kuukaudet &" '! "&" D2: D14 ")) |
Tässä kuukaudet on nimetty alue, joka sisältää arkkien nimet. Tämä on ratkaisevan tärkeää.
Kun painat Enter -näppäintä, saat tarkan tuloksen.
Kuinka se toimii?
Kaavan ydin on Epäsuora funktio ja nimetty alue. Tässä merkkijono"'"&Kuukaudet&"'!" & "A2: A14"kääntää joukon alueviittauksia jokaisesta taulukosta nimetty alue.
| {"'Tammikuu'! D2: D14"; "'helmikuu! D2: D14"; "' maaliskuu! D2: D14"; "'huhtikuu'! D2: D14"} |
Tämä taulukko sisältää tekstiviitealueista, ei varsinaisista alueista. Koska se on tekstiviite, INDIRECT -toiminto voi käyttää sitä muuntaakseen ne todellisiksi alueiksi. Tämä tapahtuu molemmissa epäsuorassa toiminnoissa. Kun olet ratkaissut tekstit INDIRECT -toimintojen sisällä (pidä tiukasti kiinni), kaava näyttää tältä:
| = SUMPRODUCT (SUMIF (INUM. , Mestari! B3, Epäsuora ({"'tammikuu'! D2: D14"; "'helmikuu'! D2: D14"; "" maaliskuu "! D2: D14"; "'huhtikuu! D2: D14"}))) |
Nyt SUMIF -toiminto alkaa toimia (ei epäsuora, kuten olet ehkä arvannut). Ehto sovitetaan ensimmäiselle alueelle"'Jan'! A2: A14". Tässä INDIRECT -toiminto toimii dynaamisesti ja muuntaa tämän tekstin todelliseksi alueeksi (Siksi jos yrität ratkaista ensin epäsuorasti F9 -näppäimellä, et saa tulosta). Seuraava summaa alueen sovitetut arvot"'Jan'! D2: D14".Tämä tapahtuu jokaisen taulukon alueen osalta. Lopuksi SUMIF -funktio palauttaa taulukon.
| = SUMPRODUCT ({97; 82; 63; 73}) |
Nyt SUMPRODUCT tekee mitä parhaiten osaa. Se summaa nämä arvot ja saamme 3D SUMIF -toiminnon toimimaan.
Joten kyllä kaverit, tällä tavalla voit saavuttaa 3D SUMIF -toiminnon. Tämä on hieman monimutkainen, olen samaa mieltä. Tässä 3D -kaavassa on paljon tilaa virheille. Ehdotan, että käytät SUMIF -funktiota jokaisessa taulukossa tietyssä solussa ja käytä sitten normaalia 3D -viittausta näiden arvojen yhteenlaskemiseen.
Toivottavasti olin tarpeeksi selittävä. Jos sinulla on epäilyksiä muista Excel/VBA -kyselyihin viittaamisesta Excelissä, kysy alla olevasta kommenttiosasta.
Suhteellinen ja ehdoton viite Excelissä | Viittaaminen Excelissä on tärkeä aihe jokaiselle aloittelijalle. Jopa kokeneet Excel -käyttäjät tekevät virheitä viittauksessa.
Dynaamisen laskentataulukon viite | Anna viitearkit dynaamisesti käyttämällä Excelin INDIRECT -toimintoa. Tämä on yksinkertaista…
Viittausten laajentaminen Excelissä | Laajeneva viite laajenee, kun sitä kopioidaan alas tai oikealle. Käytämme $ -merkkiä ennen saraketta ja rivinumeroa. Tässä yksi esimerkki…
Kaikki absoluuttisesta viittauksesta | Excelin oletusviitetyyppi on suhteellinen, mutta jos haluat, että solujen ja alueiden viittaus on ehdoton, käytä $ -merkkiä. Tässä on kaikki ehdoton viittaus Excelissä.
Suosittuja artikkeleita:
50 Excel -pikanäppäintä tuottavuuden lisäämiseksi | Nopeuta tehtävääsi. Nämä 50 pikanäppäintä tekevät työskentelystäsi entistä nopeampaa Excelissä.
VLOOKUP -toiminto Excelissä | Tämä on yksi eniten käytetyistä ja suosituimmista Excel -toiminnoista, jota käytetään arvon etsimiseen eri alueilta ja arkeilta.
COUNTIF Excel 2016: ssa | Laske arvot olosuhteilla käyttämällä tätä hämmästyttävää toimintoa. Sinun ei tarvitse suodattaa tietojasi tietyn arvon laskemiseksi. Laskutoiminto on välttämätön kojelaudan valmistelemiseksi.
SUMIF -toiminnon käyttäminen Excelissä | Tämä on toinen kojelaudan olennainen toiminto. Tämä auttaa sinua laskemaan yhteen arvot tietyissä olosuhteissa.