Tātad, mēs jau esam iemācījušies, kāda ir 3D atsauce programmā Excel. Jautri ir tas, ka parastā Excel 3D atsauce nedarbojas ar nosacījuma funkcijām, piemēram, SUMIF funkciju. Šajā rakstā mēs uzzināsim, kā panākt, lai 3D atsauces darbotos ar SUMIF funkciju.
SUMIF vispārīgā formula ar 3D atsauci programmā Excel
Tas izskatās sarežģīti, bet nav (tik daudz).
| = SUMPRODUCT (SUMIF (INDIRECT ("'" & name_range_of_sheet_names & "'!" & "Kritēriju diapazons"), kritēriji, NETIEŠI ("" "& name_range_of_sheet_names &" '! "&" Sum_range ")) |
"" "name_range_of_sheet_names" "":Tas ir nosaukts diapazons, kurā ir lapu nosaukumi. Tas ir ļoti svarīgi.
"kritēriju_diapazons":Tā ir teksta atsauce uz kritērijiem, kas satur diapazonu. (Tam jābūt vienādam visās trīsdimensiju atsauces darbu lapās.)
kritēriji:Tas ir vienkārši nosacījums, kuru vēlaties izvirzīt summēšanai. Tā var būt teksta vai šūnu atsauce.
"summas diapazons":Tā ir summu diapazona teksta atsauce. (Tam vajadzētu būt vienādam visās trīsdimensiju atsauces darbu lapās.)
Pietiek ar teoriju, pieņemsim, ka 3D atsauce ar SUMIF funkciju darbojas.
Piemērs: Summa pēc reģiona no vairākām lapām, izmantojot Excel 3D atsauci:
Mēs ņemam tos pašus datus, kas tika ņemti vienkāršā 3D atsauces piemērā. Šajā piemērā man ir piecas dažādas lapas, kurās ir līdzīgi dati. Katrā lapā ir mēneša dati. Galvenajā lapā es vēlos, lai no visām lapām tiktu iegūta vienību un kolekciju summa pēc reģiona. Vispirms darīsim to vienībām. Vienības ir diapazonā D2: D14 visās lapās.
Tagad, ja izmantojat parasto 3D atsauci ar funkciju SUMIF,
= SUMIF (Jan: Apr! A2: A14, Master! B4, Jan: Apr! D2: D14)
Atgriezīsies #VĒRTĪBA! kļūda. Tāpēc mēs to nevaram izmantot. Mēs izmantosim iepriekš minēto vispārīgo formulu.
Izmantojot iepriekš minēto vispārīgo 3D atsauces SUMIF formulu Excel, ierakstiet šo formulu šūnā C3:
| = SUMPRODUCT (SUMIF (INDIRECT ("'" & Mēneši & "'!" & "A2: A14"), Master! B3, INDIRECT ("" "& Mēneši &" '! "&" D2: D14 ")) |
Šeit mēnešus ir nosaukts diapazons, kurā ir lapu nosaukumi. Tas ir izšķiroši.
Nospiežot taustiņu Enter, jūs iegūsit precīzu rezultātu.
Kā tas darbojas?
Formulas kodols ir funkcija NETIEŠĀ un nosaukts diapazons. Šeit virkne"" "& Mēneši &" "!" & "A2: A14"tulko uz katras lapas lapas diapazona atsauču masīvu nosaukts diapazons.
| {"'Jan'! D2: D14"; "'Feb'! D2: D14"; "'Mar'! D2: D14"; "'Apr'! D2: D14"} |
Šajā masīvā ir teksta atsaucediapazoniem, nevis faktiskajiem diapazoniem. Tā kā tā ir teksta atsauce, funkcija INDIRECT to var izmantot, lai tos pārvērstu faktiskajos diapazonos. Tas notiek abām INDIRECT funkcijām. Pēc tekstu atrisināšanas INDIRECT funkcijās (turiet cieši), formula izskatās šādi:
| = SUMPRODUCT (SUMIF (INDIRECT (({{'Jan'! A2: A14 ";" 'Feb'! A2: A14 ";" 'Mar'! A2: A14 ";" 'Apr'! A2: A14 "}) , Meistars! B3, NETIESA |
Tagad darbojas SUMIF funkcija (nevis netieša, kā jūs, iespējams, uzminējāt). Nosacījums ir iekļauts pirmajā diapazonā"" Jan "! A2: A14". Šeit funkcija INDIRECT darbojas dinamiski un pārvērš šo tekstu faktiskajā diapazonā (tāpēc, ja jūs vispirms mēģināt atrisināt INDIRECT, izmantojot taustiņu F9, rezultāts netiks iegūts). Tālāk tiek apkopotas atbilstošās diapazona vērtības"'Jan'! D2: D14".Tas notiek katram masīva diapazonam. Visbeidzot, mums būs masīvs, ko atgriež funkcija SUMIF.
| = SUMPRODUCT ({97; 82; 63; 73}) |
Tagad SUMPRODUCT dara to, ko prot vislabāk. Tas apkopo šīs vērtības, un mēs iegūstam savu 3D SUMIF funkciju.
Tātad, jā, puiši, šādi jūs varat sasniegt 3D SUMIF funkciju. Tas ir nedaudz sarežģīti, es tam piekrītu. Šajā 3D formulā ir daudz kļūdu. Es ieteiktu jums izmantot SUMIF funkciju katrā lapā noteiktā šūnā un pēc tam izmantot parasto 3D atsauci, lai apkopotu šīs vērtības.
Ceru, ka biju pietiekami izskaidrojošs. Ja jums ir šaubas par Excel atsauci uz jebkuru citu ar Excel/VBA saistītu vaicājumu, jautājiet tālāk komentāru sadaļā.
Relatīvā un absolūtā atsauce programmā Excel | Atsauces programmā Excel ir svarīga tēma ikvienam iesācējam. Pat pieredzējuši Excel lietotāji atsaucoties pieļauj kļūdas.
Atsauce uz dinamisko darblapu | Dodiet atsauces lapas dinamiski, izmantojot Excel INDIRECT funkciju. Tas ir vienkārši…
Atsauču paplašināšana programmā Excel | Izvēršamā atsauce izvēršas, ja to nokopē uz leju vai pa labi. Mēs izmantojam $ zīmi pirms kolonnas un rindas numura. Šeit ir viens piemērs…
Viss par absolūtu atsauci | Noklusējuma atsauces veids programmā Excel ir relatīvs, bet, ja vēlaties, lai šūnu un diapazonu atsauce būtu absolūta, izmantojiet $ zīmi. Šeit ir visi absolūtās atsauces aspekti programmā Excel.
Populāri raksti:
50 Excel saīsnes, lai palielinātu produktivitāti | Ātrāk izpildiet savu uzdevumu. Šie 50 īsceļi ļaus jums strādāt vēl ātrāk programmā Excel.
Funkcija VLOOKUP programmā Excel | Šī ir viena no visbiežāk izmantotajām un populārākajām Excel funkcijām, kas tiek izmantota, lai meklētu vērtību no dažādiem diapazoniem un lapām.
COUNTIF programmā Excel 2016 | Saskaitiet vērtības ar nosacījumiem, izmantojot šo apbrīnojamo funkciju. Jums nav jāfiltrē dati, lai uzskaitītu konkrētu vērtību. Skaitītāja funkcija ir būtiska, lai sagatavotu informācijas paneli.
Kā lietot SUMIF funkciju programmā Excel | Šī ir vēl viena būtiska informācijas paneļa funkcija. Tas palīdz apkopot vērtības noteiktos apstākļos.
