SUMIF med 3D Reference i Excel

Indholdsfortegnelse:

Anonim

Så vi har allerede lært, hvad 3D -reference er i Excel. Den sjove kendsgerning er, at den normale Excel 3D -henvisning ikke fungerer med betingede funktioner, som SUMIF -funktion. I denne artikel lærer vi, hvordan du får 3D -referencer til at arbejde med SUMIF -funktion.

Den generiske formel for SUMIF med 3D -reference i Excel

Det ser kompliceret ud, men det er ikke (så meget).

= SUMPRODUCT (SUMIF (INDIRECT ("'" & name_range_of_sheet_names & "'!" & "Criteria_range"), criteria, INDIRECT ("'" & name_range_of_sheet_names & "'!" & "Sum_range")))

"'" name_range_of_sheet_names "'":Det er et navngivet område, der indeholder arknavnene. Dette er meget vigtigt.

"criteria_range":Det er tekstreferencen til kriterier, der indeholder rækkevidde. (Det skal være det samme i alle ark til 3-D opslagsværk.)

kriterier:Det er simpelthen den betingelse, du vil sætte for summering. Det kan være en tekst- eller cellereference.

"sum_range":Det er tekstreferencen for sumintervallet. (Det skal være det samme i alle ark til 3-D opslagsværk.)

Nok med teorien, lad os 3D referere med SUMIF -funktion, der fungerer.

Eksempel: Sum efter område fra flere ark ved hjælp af 3D -henvisning til Excel:

Vi tager de samme data, som vi tog i det simple 3D -referenceeksempel. I dette eksempel har jeg fem forskellige ark, der indeholder lignende data. Hvert ark indeholder en måneds data. I Master -arket vil jeg have summen af ​​enheder og samling efter område fra alle arkene. Lad os først gøre det for enheder. Enhederne er i området D2: D14 i alle arkene.

Hvis du nu bruger den normale 3D -henvisning med SUMIF -funktion,

= SUMIF (Jan: Apr! A2: A14, Master! B4, Jan: Apr! D2: D14)

Det returnerer #VÆRDI! fejl. Så vi kan ikke bruge det. Vi vil bruge den generiske formel, der er nævnt ovenfor.

Brug den ovenstående generiske 3D -refererende SUMIF -formel for excel, og skriv denne formel i celle C3:

= SUMPRODUCT (SUMIF (INDIRECT ("'" & Months & "'!" & "A2: A14"), Master! B3, INDIRECT ("'" & Months & "'!" & "D2: D14"))))

Her måneder er et navngivet område, der indeholder navnene på ark. Dette er afgørende.

Når du trykker på enter, får du dit nøjagtige output.

Hvordan virker det?

Kernen i formlen er INDIRECT -funktionen og navngivet område. Her snoren"'" & Måneder & "'!" & "A2: A14"oversætter til en række rækkehenvisninger for hvert ark i navngivet område.

{"'Jan'! D2: D14"; "'Feb'! D2: D14"; "'Mar'! D2: D14"; "'Apr'! D2: D14"}

Denne matrix indeholder tekstreferenceaf intervaller, ikke de faktiske intervaller. Da det nu er en tekstreference, kan det bruges af INDIRECT -funktionen til at konvertere dem til faktiske områder. Dette sker for begge de INDIREKTE funktioner. Efter at have løst teksterne inde i INDIRECT -funktionerne (hold fast), ser formlen sådan ud:

= SUMPRODUCT (SUMIF (INDIRECT (({"'Jan'! A2: A14"; "'Feb'! A2: A14"; "'Mar'! A2: A14"; "'Apr'! A2: A14"})) ,
Master! B3, INDIRECT ({"'Jan'! D2: D14"; "'Feb'! D2: D14"; "'Mar'! D2: D14"; "'Apr'! D2: D14"}))))

Nu kommer SUMIF -funktionen til handling (ikke INDIREKT, som du måske har gættet). Tilstanden matches i det første område"'Jan'! A2: A14". Her fungerer INDIRECT -funktionen dynamisk og konverterer denne tekst til det faktiske område (det er derfor, hvis du prøver at løse INDIRECT først ved hjælp af F9 -nøglen, får du ikke resultatet). Næste opsummerer de matchede værdier i intervallet"'Jan'! D2: D14".Dette sker for hvert område i arrayet. Endelig får vi et array returneret af SUMIF -funktionen.

= SUMPRODUCT ({97; 82; 63; 73})

Nu SUMPRODUCT gør, hvad det gør bedst. Det opsummerer denne værdi, og vi får vores 3D SUMIF -funktion til at fungere.

Så ja fyre, sådan kan du opnå en 3D SUMIF -funktion. Det er lidt komplekst, det er jeg enig i. Der er masser af muligheder for fejl i denne 3D -formel. Jeg vil foreslå, at du bruger SUMIF -funktion på hvert ark i en bestemt celle og derefter bruger den normale 3D -henvisning til at opsummere disse værdier.

Jeg håber, jeg var forklarende nok. Hvis du er i tvivl om excel, der henviser til andre Excel/VBA -relaterede forespørgsler, skal du spørge i kommentarfeltet herunder.

Relativ og absolut reference i Excel | Henvisning i excel er et vigtigt emne for enhver nybegynder. Selv erfarne excel -brugere laver fejl ved henvisning.

Dynamisk regnearksreference | Giv referenceark dynamisk ved hjælp af INDIRECT -funktionen i excel. Dette er enkelt…

Udvidelse af referencer i Excel | Den ekspanderende reference udvides, når den kopieres nedad eller mod højre. Vi bruger $ -tegnet før kolonne- og rækkenummer til at gøre det. Her er et eksempel …

Alt om absolut reference | Standardreferencetypen i excel er relativ, men hvis du vil have reference til celler og områder til at være absolut, skal du bruge $ -tegnet. Her er alle aspekter ved absolut henvisning i Excel.

Populære artikler:

50 Excel -genveje til at øge din produktivitet | Få hurtigere til din opgave. Disse 50 genveje får dig til at arbejde endnu hurtigere i Excel.

VLOOKUP -funktionen i Excel | Dette er en af ​​de mest anvendte og populære funktioner i excel, der bruges til at slå værdi op fra forskellige områder og ark.

COUNTIF i Excel 2016 | Tæl værdier med betingelser ved hjælp af denne fantastiske funktion. Du behøver ikke filtrere dine data for at tælle specifik værdi. Countif -funktion er afgørende for at forberede dit dashboard.

Sådan bruges SUMIF -funktionen i Excel | Dette er endnu en vigtig instrumentbrætfunktion. Dette hjælper dig med at opsummere værdier på bestemte betingelser.