Så vi har allerede lært hva 3D -referanse er i Excel. Det morsomme faktum er at den normale Excel 3D -referansen ikke fungerer med betingede funksjoner, som SUMIF -funksjonen. I denne artikkelen vil vi lære hvordan du får 3D -referanser til å arbeide med SUMIF -funksjonen.
Den generiske formelen for SUMIF med 3D -referanse i Excel
Det ser komplisert ut, men det er ikke (så mye).
| = 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 navngitt område som inneholder arknavnene. Dette er veldig viktig.
"criteria_range":Det er tekstreferansen til kriterier som inneholder rekkevidde. (Det bør være det samme i alle ark med 3D-referanser.)
kriterier:Det er ganske enkelt betingelsen du vil sette for summering. Det kan være en tekst- eller cellehenvisning.
"sum_range":Det er tekstreferansen til sumområdet. (Det bør være det samme i alle ark med 3D-referanser.)
Nok om teorien, la oss 3D referere med SUMIF -funksjon som fungerer.
Eksempel: Sum etter region fra flere ark ved bruk av 3D -referanse i Excel:
Vi tar de samme dataene som vi tok i det enkle 3D -referanseeksemplet. I dette eksemplet har jeg fem forskjellige ark som inneholder lignende data. Hvert ark inneholder en måneds data. I hovedarket vil jeg ha summen av enheter og samling etter region fra alle arkene. La oss gjøre det for enheter først. Enhetene er i området D2: D14 i alle arkene.
Hvis du nå bruker den normale 3D -referansen med SUMIF -funksjonen,
= SUMIF (jan: apr! A2: A14, Master! B4, jan: apr! D2: D14)
Det vil returnere #VERDI! feil. Så vi kan ikke bruke den. Vi vil bruke den generiske formelen nevnt ovenfor.
Ved å bruke den ovennevnte generiske 3D -refererende SUMIF -formelen for excel, skriver du denne formelen i celle C3:
| = SUMPRODUCT (SUMIF (INDIRECT ("'" & Months & "'!" & "A2: A14"), Master! B3, INDIRECT ("'" & Months & "'!" & "D2: D14")))) |
Her måneder er et navngitt område som inneholder navnene på ark. Dette er avgjørende.
Når du trykker enter, får du den eksakte utgangen.
Hvordan virker det?
Kjernen i formelen er INDIRECT -funksjonen og navngitt område. Her er strengen"'"&Måneder&"'!" & "A2: A14"oversetter til en rekke rekkehenvisninger for hvert ark i navngitt område.
| {"'Jan'! D2: D14"; "'Feb'! D2: D14"; "'Mar'! D2: D14"; "'Apr'! D2: D14"} |
Denne matrisen inneholder tekstreferanseav områder, ikke de faktiske områdene. Siden det nå er en tekstreferanse, kan den brukes av INDIRECT -funksjonen til å konvertere dem til faktiske områder. Dette skjer for begge INDIRECT -funksjonene. Etter å ha løst tekstene inne i INDIRECT -funksjonene (hold tett), ser formelen slik ut:
| = 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"})))) |
Nå kommer SUMIF -funksjonen til handling (ikke INDIRECT, som du kanskje har gjettet). Tilstanden samsvarer med det første området"'Jan'! A2: A14". Her fungerer INDIRECT -funksjonen dynamisk og konverterer denne teksten til det faktiske området (det er derfor hvis du prøver å løse INDIRECT først ved å bruke F9 -tasten, får du ikke resultatet). Neste oppsummerer de samsvarende verdiene i området"'Jan'! D2: D14".Dette skjer for hvert område i matrisen. Til slutt vil vi få en matrise returnert av SUMIF -funksjonen.
| = SUMPRODUCT ({97; 82; 63; 73}) |
Nå gjør SUMPRODUCT det den gjør best. Den oppsummerer denne verdien, og vi får vår 3D SUMIF -funksjon til å fungere.
Så ja gutta, slik kan du oppnå en 3D SUMIF -funksjon. Dette er litt komplisert, det er jeg enig i. Det er mye rom for feil i denne 3D -formelen. Jeg vil foreslå at du bruker SUMIF -funksjonen på hvert ark i en bestemt celle og deretter bruker den normale 3D -referansen for å oppsummere disse verdiene.
Jeg håper jeg var forklarende nok. Hvis du er i tvil om excel som refererer til andre Excel/VBA -relaterte spørsmål, kan du spørre i kommentarfeltet nedenfor.
Relativ og absolutt referanse i Excel | Å referere i excel er et viktig tema for hver nybegynner. Selv erfarne excel -brukere gjør feil i referansen.
Dynamisk regnearkreferanse | Gi referanseark dynamisk ved hjelp av INDIRECT -funksjonen til excel. Dette er enkelt…
Utvide referanser i Excel | Den ekspanderende referansen utvides når den kopieres ned eller til høyre. Vi bruker $ -tegnet før kolonnen og radnummeret for å gjøre det. Her er et eksempel …
Alt om absolutt referanse | Standard referansetype i excel er relativ, men hvis du vil at referansen til celler og områder skal være absolutt, bruker du $ -tegnet. Her er alle aspektene ved absolutt referanse i Excel.
Populære artikler:
50 Excel -snarveier for å øke produktiviteten | Bli raskere på oppgaven din. Disse 50 snarveiene får deg til å jobbe enda raskere med Excel.
VLOOKUP -funksjonen i Excel | Dette er en av de mest brukte og populære funksjonene til excel som brukes til å slå opp verdi fra forskjellige områder og ark.
COUNTIF i Excel 2016 | Tell verdier med betingelser ved hjelp av denne fantastiske funksjonen. Du trenger ikke filtrere dataene dine for å telle spesifikk verdi. Countif -funksjonen er avgjørende for å forberede dashbordet.
Slik bruker du SUMIF -funksjonen i Excel | Dette er en annen viktig funksjon på dashbordet. Dette hjelper deg med å oppsummere verdier på spesifikke forhold.