Torej smo že izvedeli, kaj je 3D referenca v Excelu. Zabavno dejstvo je, da običajno sklicevanje v Excelu 3D ne deluje s pogojnimi funkcijami, kot je funkcija SUMIF. V tem članku se bomo naučili, kako 3D referenciranje deluje s funkcijo SUMIF.
Splošna formula za SUMIF s 3D referenco v Excelu
Izgleda zapleteno, vendar ni (toliko).
| = SUMPRODUCT (SUMIF (INDIRECT ("'" & name_range_of_sheet_names & "'!" & "Criteria_range"), kriteriji, INDIRECT ("'" & name_range_of_sheet_names & "'!" & "Sum_range"))) |
"'" name_range_of_sheet_names "'":To je poimenovani obseg, ki vsebuje imena listov. To je zelo pomembno.
"obseg_kriterijev":To je besedilna referenca meril, ki vsebujejo obseg. (V vseh listih za 3-D referenčno delo bi moralo biti enako.)
merila:To je preprosto pogoj, ki ga želite postaviti za seštevanje. Lahko je besedilo ali sklic na celico.
"sum_range":To je besedilna referenca obsega vsote. (V vseh listih za 3-D referenčno delo bi moralo biti enako.)
Dovolj teorije, recimo 3D sklicevanje s funkcijo SUMIF.
Primer: vsota po regijah iz več listov z uporabo 3D sklicevanja v Excelu:
Vzamemo iste podatke, ki smo jih vzeli v preprostem primeru sklicevanja na 3D. V tem primeru imam pet različnih listov, ki vsebujejo podobne podatke. Vsak list vsebuje mesečne podatke. Na glavnem listu želim vsoto enot in zbirko po regijah iz vseh listov. Najprej naredimo za enote. Enote so v razponu D2: D14 na vseh listih.
Zdaj, če uporabljate običajno 3D sklicevanje s funkcijo SUMIF,
= SUMIF (Jan: Apr! A2: A14, Master! B4, Jan: Apr! D2: D14)
Vrnil bo #VALUE! napaka. Zato ga ne moremo uporabiti. Uporabili bomo zgoraj omenjeno generično formulo.
Z uporabo zgornje splošne 3D sklicne formule SUMIF v Excelu zapišite to formulo v celico C3:
| = SUMPRODUCT (SUMIF (INDIRECT ("'" & Meseci & "'!" & "A2: A14"), Master! B3, INDIRECT ("'" & Meseci & "'!" & "D2: D14"))) |
Tukaj mesecih je poimenovani obseg, ki vsebuje imena listov. To je ključnega pomena.
Ko pritisnete enter, dobite natančen izhod.
Kako deluje?
Jedro formule sta funkcija INDIRECT in imenovani obseg. Tukaj je niz"'" & Meseci & "'!" & "A2: A14"prevede v niz sklicev na obseg vsakega lista v imenovani obseg.
| {"'Jan'! D2: D14"; "'Feb'! D2: D14"; "'Mar'! D2: D14"; "'Apr'! D2: D14"} |
Ta matrika vsebuje sklic na besediloobsegov, ne dejanskih razponov. Ker gre za besedilno referenco, jo lahko funkcija INDIRECT uporabi za pretvorbo v dejanske obsege. To se zgodi pri obeh funkcijah INDIRECT. Po razrešitvi besedil znotraj funkcij INDIRECT (držite se) formula izgleda tako:
| = SUMPRODUCT (SUMIF (INDIRECT (({"'Jan'! A2: A14"; "'Feb'! A2: A14"; "'Mar'! A2: A14"; "'Apr'! A2: A14"}) , Mojster! B3, INDIRECT ({"'Jan'! D2: D14"; "'Feb'! D2: D14"; "'Mar'! D2: D14"; "'Apr'! D2: D14"}))) |
Zdaj začne delovati funkcija SUMIF (ne INDIRECT, kot ste morda uganili). Pogoj je prilagojen prvemu razponu"'Jan'! A2: A14". Tu funkcija INDIRECT deluje dinamično in to besedilo pretvori v dejansko območje (zato, če najprej poskusite rešiti NIREKTNO s tipko F9, ne boste dobili rezultata). Nato povzame ujemajoče se vrednosti v obsegu"'Jan'! D2: D14".To se zgodi za vsako območje v matriki. Končno bomo imeli niz, ki ga vrne funkcija SUMIF.
| = PODROČJE ({97; 82; 63; 73}) |
Zdaj SUMPRODUCT počne tisto, kar mu je najboljše. Povzame te vrednosti in naša funkcija 3D SUMIF deluje.
Torej, fantje, tako lahko dosežete funkcijo 3D SUMIF. To je nekoliko zapleteno, s tem se strinjam. V tej 3D formuli je veliko možnosti za napake. Predlagam, da uporabite funkcijo SUMIF na vsakem listu v določeni celici in nato za seštevanje teh vrednosti uporabite običajno sklicevanje v 3D.
Upam, da sem bil dovolj razložljiv. Če imate kakršne koli dvome glede sklicevanja programa Excel na katero koli drugo poizvedbo, povezano z Excelom/VBA, vprašajte v spodnjem razdelku za komentarje.
Relativna in absolutna referenca v Excelu | Sklicevanje v excelu je pomembna tema za vsakega začetnika. Tudi izkušeni uporabniki excela delajo napake pri sklicevanju.
Sklic dinamičnega delovnega lista | Referenčne liste podajte dinamično s funkcijo INDIRECT v Excelu. To je preprosto…
Razširitev referenc v Excelu | Naraščajoča referenca se pri kopiranju navzdol ali desno razširi. Za to uporabljamo znak $ pred številko stolpca in vrstice. Tukaj je en primer…
Vse o absolutnih referencah | Privzeta referenčna vrsta v Excelu je relativna, če pa želite, da so sklice na celice in obsege absolutne, uporabite znak $. Tu so vsi vidiki absolutnega sklicevanja v Excelu.
Priljubljeni članki:
50 bližnjic v Excelu za večjo produktivnost | Poskrbite, da bo vaša naloga hitrejša. S temi 50 bližnjicami boste še hitreje delali v Excelu.
Funkcija VLOOKUP v Excelu | To je ena najpogosteje uporabljenih in priljubljenih funkcij programa Excel, ki se uporablja za iskanje vrednosti iz različnih obsegov in listov.
COUNTIF v Excelu 2016 | Štejte vrednosti s pogoji s to neverjetno funkcijo. Za štetje določene vrednosti vam ni treba filtrirati podatkov. Funkcija Countif je bistvena za pripravo vaše armaturne plošče.
Kako uporabljati funkcijo SUMIF v Excelu | To je še ena bistvena funkcija armaturne plošče. To vam pomaga povzeti vrednosti za posebne pogoje.