Niisiis, oleme juba õppinud, mis on Excelis 3D -viide. Naljakas fakt on see, et tavaline Excel 3D viitamine ei tööta tingimuslike funktsioonidega, nagu SUMIF -funktsioon. Selles artiklis õpime, kuidas 3D -viiteid SUMIF -funktsiooniga tööle panna.
SUMIF -i üldvalem koos 3D -viitega Excelis
Tundub keeruline, kuid pole seda (nii palju).
| = SUMPRODUCT (SUMIF (INDIRECT ("'" & name_range_of_sheet_names & "'!" & "Kriteeriumivahemik"), kriteeriumid, INDIRECT ("'" & name_range_of_sheet_names & "'!" & "Sum_range"))) |
"" "nime_vahemik_lehtede_nimed" "":See on nimega vahemik, mis sisaldab lehtede nimesid. See on väga oluline.
"kriteeriumivahemik":See on vahemikku sisaldavate kriteeriumide tekstiviide. (See peaks olema 3D-teatmeteose kõikidel lehtedel sama.)
kriteeriumid:See on lihtsalt tingimus, mille soovite summeerimiseks seada. See võib olla teksti- või lahtriviide.
"summa_vahemik":See on summa vahemiku tekstiviide. (See peaks olema 3D-teatmeteose kõikidel lehtedel sama.)
Piisab teooriast, las 3D -viitamine SUMIF -funktsiooniga töötab.
Näide: Summa piirkonna järgi mitmelt lehelt, kasutades Exceli 3D -viidet:
Võtame samu andmeid, mida võtsime lihtsas 3D -viitamise näites. Selles näites on mul viis erinevat lehte, mis sisaldavad sarnaseid andmeid. Iga leht sisaldab kuu andmeid. Põhilehel soovin kõikidelt lehtedelt ühikute summat ja kogumist regiooniti. Teeme seda kõigepealt üksuste jaoks. Ühikud on kõikidel lehtedel vahemikus D2: D14.
Kui kasutate tavalist 3D -viidet koos SUMIF -funktsiooniga,
= SUMIF (jaan: aprill! A2: A14, meister! B4, jaan: aprill! D2: D14)
See tagastab #VALUE! viga. Nii et me ei saa seda kasutada. Kasutame ülalmainitud üldist valemit.
Kasutades ülaltoodud Exceli üldist 3D -viite SUMIF -valemit, kirjutage see valem lahtrisse C3:
| = SUMPRODUCT (SUMIF (INDIRECT ("'" & Kuud & "'!" & "A2: A14"), Master! B3, INDIRECT ("'" & Kuud & "'!" & "D2: D14")) |
Siin kuud on nimega vahemik, mis sisaldab lehtede nimesid. See on ülioluline.
Kui vajutate sisestusklahvi, saate oma täpse väljundi.
Kuidas see töötab?
Valemi tuum on funktsioon INDIRECT ja nimega vahemik. Siin string"" "& Kuud &" "!" & "A2: A14"tõlgib massiivi vahemiku viiteid iga lehe lehel nimega vahemik.
| {"'Jaan! D2: D14"; "' veebruar '! D2: D14"; "' märts '! D2: D14"; "' aprill '! D2: D14"} |
See massiiv sisaldab tekstiviidevahemikest, mitte tegelikest vahemikest. Kuna see on tekstiviide, saab funktsioon INDIRECT seda kasutada tegelikeks vahemikeks teisendamiseks. See juhtub mõlema INDIRECT funktsiooni puhul. Pärast tekstide lahendamist kaudsete funktsioonide sees (hoidke kõvasti kinni) näeb valem välja järgmine:
| = SUMPRODUCT (SUMIF (INDIRECT (({{'' Jan '! A2: A14 ";"' Feb '! A2: A14 ";"' Märts '! A2: A14 ";"' Apr '! A2: A14 "}) , Meister! B3, KAUDSE ({"'jaan! D2: D14"; "' veebruar '! D2: D14"; "' märts '! D2: D14"; "' aprill '! D2: D14"})) |
Nüüd hakkab tööle funktsioon SUMIF (mitte kaudne, nagu võisite arvata). Tingimus sobitatakse esimesse vahemikku"" Jaan "! A2: A14". Funktsioon INDIRECT töötab siin dünaamiliselt ja teisendab selle teksti tegelikuks vahemikuks (Sellepärast ei saa te tulemust, kui proovite kõigepealt lahendada INDIRECT, kasutades klahvi F9). Järgmine võtab kokku sobitatud väärtused vahemikus"" Jaan "! D2: D14".See juhtub massiivi iga vahemiku puhul. Lõpuks saame funktsiooni SUMIF tagastatud massiivi.
| = SUMPRODUCT ({97; 82; 63; 73}) |
Nüüd teeb SUMPRODUCT seda, mida ta kõige paremini oskab. See võtab need väärtused kokku ja saame 3D SUMIF -funktsiooni tööle.
Nii et jah, poisid, nii saate saavutada 3D SUMIF -funktsiooni. See on natuke keeruline, olen sellega nõus. Selles 3D -valemis on palju vigu. Soovitan teil kasutada funktsiooni SUMIF igal lehel kindlas lahtris ja seejärel kasutada nende väärtuste summeerimiseks tavalist 3D -viidet.
Loodan, et olin piisavalt selgitav. Kui teil on kahtlusi seoses Exceli viitamisega mõnele muule Exceli/VBA -ga seotud päringule, küsige seda allpool kommentaaride osas.
Suhteline ja absoluutne viide Excelis | Viitamine Excelis on iga algaja jaoks oluline teema. Isegi kogenud exceli kasutajad teevad viitamisel vigu.
Dünaamilise töölehe viide | Andke võrdluslehed dünaamiliselt, kasutades Exceli funktsiooni INDIRECT. See on lihtne…
Viidete laiendamine Excelis | Laienev viide laieneb alla või paremale kopeerimisel. Selleks kasutame veeru ja rea numbri ees olevat märki $. Siin on üks näide…
Kõik absoluutse viite kohta | Vaikeviide Excelis on suhteline, kuid kui soovite, et lahtrite ja vahemike viide oleks absoluutne, kasutage märki $. Siin on kõik Exceli absoluutse viitamise aspektid.
Populaarsed artiklid:
50 Exceli otseteed tootlikkuse suurendamiseks | Saa oma ülesandega kiiremini hakkama. Need 50 otseteed aitavad teil Excelis veelgi kiiremini töötada.
Funktsioon VLOOKUP Excelis | See on üks enim kasutatud ja populaarsemaid Exceli funktsioone, mida kasutatakse erinevate vahemike ja lehtede väärtuse otsimiseks.
COUNTIF Excelis 2016 | Loendage väärtused tingimustega, kasutades seda hämmastavat funktsiooni. Te ei pea konkreetse väärtuse loendamiseks oma andmeid filtreerima. Countif funktsioon on armatuurlaua ettevalmistamisel hädavajalik.
Funktsiooni SUMIF kasutamine Excelis | See on veel üks armatuurlaua oluline funktsioon. See aitab teil teatud tingimustel väärtusi kokku võtta.