Taigi, mes jau sužinojome, kas yra „3D“ nuoroda „Excel“. Įdomus faktas yra tas, kad įprasta „Excel“ 3D nuoroda neveikia su sąlyginėmis funkcijomis, tokiomis kaip SUMIF funkcija. Šiame straipsnyje mes išmoksime, kaip gauti 3D nuorodą naudojant SUMIF funkciją.
Bendra SUMIF formulė su 3D nuoroda „Excel“
Atrodo sudėtinga, bet nėra (tiek daug).
| = SUMPRODUKTAS (SUMIF (INDIRECT ("'" & name_range_of_sheet_names & "'!" & "Kriterijų_reguliaras"), kriterijai, INDIRECT ("'" & name_range_of_sheet_names & "'!" & "Sum_range")) |
"" "name_range_of_sheet_names" "":Tai yra pavadintas diapazonas, kuriame yra lapų pavadinimai. Tai labai svarbu.
"kriterijų_reguliaras":Tai yra kriterijų, kuriuose yra diapazonas, teksto nuoroda. (Jis turėtų būti vienodas visuose 3-D informacinio darbo lapuose.)
kriterijai:Tai tiesiog sąlyga, kurią norite pateikti apibendrinimui. Tai gali būti teksto arba langelio nuoroda.
"sum_range":Tai yra sumos diapazono teksto nuoroda. (Jis turėtų būti vienodas visuose 3-D informacinio darbo lapuose.)
Užteks teorijos, tegul veikia 3D nuoroda su SUMIF funkcija.
Pavyzdys: suma pagal regioną iš kelių lapų, naudojant „Excel“ 3D nuorodą:
Mes paimame tuos pačius duomenis, kuriuos gavome paprastame 3D nuorodų pavyzdyje. Šiame pavyzdyje turiu penkis skirtingus lapus, kuriuose yra panašių duomenų. Kiekviename lape yra mėnesio duomenys. Pagrindiniame lape noriu iš visų lapų vienetų ir surinkimo pagal regioną sumos. Pirmiausia padarykime tai vienetams. Vienetai visuose lapuose yra diapazone D2: D14.
Dabar, jei naudojate įprastą 3D nuorodą su funkcija SUMIF,
= SUMIF (sausis: balandis! A2: A14, meistras! B4, sausis: balandis! D2: D14)
Tai grąžins #VALUE! klaida. Taigi mes negalime juo naudotis. Mes naudosime pirmiau minėtą bendrąją formulę.
Naudodami aukščiau pateiktą „Excel“ bendrąją 3D nuorodų SUMIF formulę, C3 langelyje parašykite šią formulę:
| = SUMPRODUCT (SUMIF (INDIRECT ("'" & Mėnesiai & "'!" & "A2: A14"), meistras! B3, NETIESIOGINIS ("" "& Mėnesiai &" '! "&" D2: D14 ")) |
Čia mėnesių yra pavadintas diapazonas, kuriame yra lapų pavadinimai. Tai labai svarbu.
Kai paspausite Enter, gausite tikslią išvestį.
Kaip tai veikia?
Formulės esmė yra INDIRECT funkcija ir pavadintas diapazonas. Čia eilutė"'"&Mėnesių&"'!" & "A2: A14"verčiamas į kiekvieno lapo diapazono nuorodų masyvą pavadintas diapazonas.
| {"'Sausis'! D2: D14"; "'vasaris'! D2: D14"; "'kovas'! D2: D14"; "'balandis'! D2: D14"} |
Šiame masyve yra teksto nuorodadiapazonų, o ne faktinių diapazonų. Kadangi tai yra teksto nuoroda, ją gali naudoti funkcija INDIRECT, kad juos konvertuotų į faktinius diapazonus. Tai atsitinka abiem INDIRECT funkcijoms. Išsprendus tekstus INDIRECT funkcijose (laikykite tvirtai), formulė atrodo taip:
| = SUMPRODUCT (SUMIF (NETIESIOGINIS (({{'Jan'! A2: A14 ";" 'Vasaris'! A2: A14 ";" 'Mar'! A2: A14 ";" 'Balandis'! A2: A14 "}) , Mokytojas! B3, NETIESIOGINIS ({"'Jan'! D2: D14"; "'Vasaris'! D2: D14"; "'Mar'! D2: D14"; "'Balandis'! D2: D14"}))) |
Dabar pradeda veikti SUMIF funkcija (ne netiesioginė, kaip galbūt atspėjote). Sąlyga atitinka pirmąjį diapazoną"" Sausis "! A2: A14". Čia INDIRECT funkcija veikia dinamiškai ir konvertuoja šį tekstą į tikrąjį diapazoną (todėl jei pirmą kartą bandysite išspręsti INDIRECT naudodami klavišą F9, rezultato negausite). Toliau apibendrinamos suderintos diapazono vertės"Sausis"! D2: D14 ".Tai atsitinka kiekvienam masyvo diapazonui. Galiausiai turėsime masyvą, kurį grąžins funkcija SUMIF.
| = SUMPRODUCT ({97; 82; 63; 73}) |
Dabar SUMPRODUCT daro tai, ką moka geriausiai. Tai apibendrina šias vertes ir mes naudojame 3D SUMIF funkciją.
Taigi taip, vaikinai, taip galite pasiekti 3D SUMIF funkciją. Tai šiek tiek sudėtinga, aš sutinku. Šioje 3D formulėje yra daug klaidų. Aš siūlau naudoti SUMIF funkciją kiekviename lape tam tikrame langelyje ir tada naudoti įprastą 3D nuorodą, kad susumuotumėte šias vertes.
Tikiuosi, kad buvau pakankamai aiškinantis. Jei turite abejonių dėl „Excel“ nuorodos į bet kurią kitą su „Excel“/VBA susijusią užklausą, paklauskite žemiau esančiame komentarų skyriuje.
Santykinė ir absoliuti nuoroda „Excel“ | Nuorodos „Excel“ yra svarbi tema kiekvienam pradedančiajam. Net patyrę „Excel“ vartotojai daro nuorodų klaidų.
Dinaminio darbalapio nuoroda | Dinamiškai pateikite informacinius lapus naudodami „Excel“ netiesioginę funkciją. Tai paprasta…
„Excel“ nuorodų išplėtimas | Išplečiama nuoroda išplečiama, kai nukopijuojama žemyn arba į dešinę. Tam naudojame $ ženklą prieš stulpelį ir eilutės numerį. Štai vienas pavyzdys…
Viskas apie absoliučią nuorodą | Numatytasis „Excel“ nuorodos tipas yra santykinis, tačiau jei norite, kad ląstelių ir diapazonų nuoroda būtų absoliuti, naudokite $ ženklą. Štai visi absoliučios nuorodos į „Excel“ aspektai.
Populiarūs straipsniai:
50 „Excel“ nuorodų, skirtų produktyvumui padidinti | Greičiau atlikite savo užduotį. Šie 50 sparčiųjų klavišų leis dar greičiau dirbti naudojant „Excel“.
„VLOOKUP“ funkcija „Excel“ | Tai yra viena iš dažniausiai naudojamų ir populiariausių „Excel“ funkcijų, naudojama norint ieškoti vertės iš įvairių diapazonų ir lapų.
COUNTIF „Excel 2016“ | Naudodami šią nuostabią funkciją, suskaičiuokite reikšmes su sąlygomis. Norint skaičiuoti konkrečią vertę, nereikia filtruoti duomenų. Skaitiklio funkcija yra būtina norint paruošti prietaisų skydelį.
Kaip naudoti „SUMIF“ funkciją „Excel“ | Tai dar viena esminė prietaisų skydelio funkcija. Tai padeda apibendrinti vertes konkrečiomis sąlygomis.