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.