Už sme sa teda dozvedeli, čo je to 3D referencia v Exceli. Zábavným faktom je, že bežné 3D referencie v Exceli nefungujú s podmienenými funkciami, ako je napríklad funkcia SUMIF. V tomto článku sa naučíme, ako získať 3D odkazovanie pri práci s funkciou SUMIF.
Obecný vzorec pre SUMIF s 3D odkazom v programe Excel
Vyzerá to komplikovane, ale nie je to (až tak veľa).
| + |
"'" názov_rozsahu_názov_listov "" ":Jedná sa o pomenovaný rozsah, ktorý obsahuje názvy hárkov. Toto je veľmi dôležité.
"rozsah_kritérií":Jedná sa o textový odkaz na kritériá obsahujúce rozsah. (Malo by byť rovnaké vo všetkých hárkoch s referenčným dielom 3-D.)
kritériá:Je to jednoducho podmienka, ktorú chcete dať na sčítanie. Môže to byť odkaz na text alebo bunku.
"sum_range":Je to textový odkaz na rozsah súčtov. (Malo by byť rovnaké vo všetkých hárkoch s referenčným dielom 3-D.)
Dosť bolo teórie, poďme pracovať 3D odkazovanie s funkciou SUMIF.
Príklad: Suma podľa regiónov z viacerých hárkov pomocou 3D odkazu na Excel:
Berieme rovnaké údaje, aké sme použili v jednoduchom príklade 3D odkazovania. V tomto prípade mám päť rôznych hárkov, ktoré obsahujú podobné údaje. Každý list obsahuje údaje o mesiaci. V hlavnom hárku chcem súčet jednotiek a zber podľa regiónov zo všetkých hárkov. Najprv to urobme pre jednotky. Jednotky sú vo všetkých listoch v rozsahu D2: D14.
Ak teraz použijete normálne 3D odkazovanie s funkciou SUMIF,
= SUMIF (január: apríl! A2: A14, majster! B4, január: apríl! D2: D14)
Vráti #HODNOTU! chyba. Nemôžeme to teda použiť. Použijeme vyššie uvedený generický vzorec.
Pomocou vyššie uvedeného generického 3D referenčného vzorca SUMIF v Exceli napíšte tento vzorec do bunky C3:
| = SUMPRODUCT (SUMIF (NEPRIAMY ("'" & Mesiace & "'!" & "A2: A14"), Master! B3, NEPRIAMY ("'" & Mesiace & "'!" & "D2: D14"))) |
Tu mesiacov je pomenovaný rozsah, ktorý obsahuje názvy hárkov. To je zásadné.
Po stlačení klávesu Enter získate presný výstup.
Ako to funguje?
Jadrom vzorca je funkcia NEPRIAMY a pomenovaný rozsah. Tu je reťazec"'" & Mesiace & "'!" & "A2: A14"prekladá na pole odkazov na rozsah každého listu v súbore pomenovaný rozsah.
| {"'Jan'! D2: D14"; "'Feb'! D2: D14"; "'Mar'! D2: D14"; "'Apr'! D2: D14"} |
Toto pole obsahuje súbor textový odkazrozsahov, nie skutočných rozsahov. Keďže teraz ide o textový odkaz, môže ho použiť funkcia INDIRECT na ich prevod do skutočných rozsahov. Stáva sa to pre obe NEPRIAME funkcie. Po vyriešení textov vo vnútri NEPRIAMYCH funkcií (držte pevne) vzorec vyzerá takto:
| = SUMPRODUCT (SUMIF (NEPRIAMY (({"'Jan'! A2: A14"; "'Feb'! A2: A14"; "'Mar'! A2: A14"; "'Apr'! A2: A14"}) , Majster! B3, NEPRIAMY ({"'Jan'! D2: D14"; "'Feb'! D2: D14"; "'Mar'! D2: D14"; "'Apr'! D2: D14"}))) |
Teraz vstupuje do činnosti funkcia SUMIF (nie NEPRIAMY, ako ste asi uhádli). Podmienka je zaradená do prvého rozsahu„„ Jano! A2: A14 “. Tu funkcia INDIRECT funguje dynamicky a prevádza tento text do skutočného rozsahu (preto ak sa pokúsite najskôr vyriešiť INDIRECT pomocou klávesu F9, nedosiahnete výsledok). Ďalej sumarizuje zodpovedajúce hodnoty v rozsahu„„ Jano! D2: D14 “.Stáva sa to pre každý rozsah v poli. Nakoniec budeme mať pole vrátené funkciou SUMIF.
| = SUMPRODUCT ({97; 82; 63; 73}) |
Teraz SUMPRODUCT robí to, čo vie najlepšie. Tieto hodnoty sú zhrnuté a naša funkcia 3D SUMIF funguje.
Takže áno, chlapci, týmto spôsobom môžete dosiahnuť funkciu 3D SUMIF. Je to trochu zložité, v tomto súhlasím. V tomto 3D vzorci je veľký priestor pre chyby. Navrhoval by som, aby ste použili funkciu SUMIF na každom hárku v určitej bunke a potom na zhrnutie týchto hodnôt použili normálne 3D referencie.
Dúfam, že som bol dostatočne vysvetľujúci. Ak máte nejaké pochybnosti týkajúce sa Excelu odkazujúceho na akýkoľvek iný dotaz týkajúci sa programu Excel/VBA, opýtajte sa ho v sekcii komentárov nižšie.
Relatívna a absolútna referencia v programe Excel | Odporúčanie v Exceli je dôležitou témou pre každého začiatočníka. Dokonca aj skúsení používatelia programu Excel robia chyby pri odkazovaní.
Referencia dynamického pracovného hárka | Poskytujte referenčné hárky dynamicky pomocou funkcie INDIRECT v programe Excel. Toto je jednoduché…
Rozšírenie referencií v programe Excel | Rozbaľujúca sa referencia sa rozbalí pri kopírovaní nadol alebo doprava. Na to použijeme znak $ pred číslom stĺpca a riadka. Tu je jeden príklad…
Všetko o absolútnej referencii | Predvolený typ referencie v programe Excel je relatívny, ale ak chcete, aby referencia buniek a rozsahov bola absolútna, použite znak $. Tu sú všetky aspekty absolútneho odkazovania v programe Excel.
Populárne články:
50 skratiek programu Excel na zvýšenie produktivity | Vykonajte svoju úlohu rýchlejšie. Týchto 50 skratiek vám umožní pracovať ešte rýchlejšie v Exceli.
Funkcia VLOOKUP v programe Excel | Toto je jedna z najpoužívanejších a najobľúbenejších funkcií programu Excel, ktorá sa používa na vyhľadávanie hodnôt z rôznych rozsahov a hárkov.
COUNTIF v Exceli 2016 | Počítajte hodnoty s podmienkami pomocou tejto úžasnej funkcie. Na počítanie konkrétnej hodnoty nepotrebujete filtrovať svoje údaje. Funkcia Countif je potrebná na prípravu palubnej dosky.
Ako používať funkciu SUMIF v programe Excel | Toto je ďalšia zásadná funkcia palubnej dosky. To vám pomôže zhrnúť hodnoty za konkrétnych podmienok.