Hvis du vil få summen av en kolonne ved å bare bruke kolonnenavnet, kan du gjøre dette på tre enkle måter i Excel. La oss utforske disse måtene.
I motsetning til andre artikler, la oss se scenariet først.
Her har jeg en tabell med salg gjort av forskjellige selgere i forskjellige måneder.
Nå er oppgaven å få summen av gitt måneds salg i Cell C10. Hvis vi endrer måneden i B10, bør summen endres og returnere den månedens sum, uten å endre noe i formelen.
Metode 1: Sum hele kolonnen i tabellen ved hjelp av SUMPRODUCT -funksjonen.
Syntaksen til SUMPRODUCT -metoden for å summere matchende kolonne er:
| = SUMPRODUCT ((kolonner)*(overskrifter = overskrift)) |
Kolonner:Det er det todimensjonale området for kolonnene du vil oppsummere. Den skal ikke inneholde overskrifter. I tabellen ovenfor er det C3: N7.
Overskrifter:Det er toppteksten på kolonner som du vil oppsummere. I dataene ovenfor er det C2: N2.
Overskrift: Det er overskriften du vil matche. I eksemplet ovenfor er det i B10.
Uten ytterligere forsinkelse, la oss bruke formelen.
| = SUMPRODUCT ((C3: N7)*(C2: N2 = B10)) |
og dette kommer tilbake:
Hvordan virker det?
Det er enkelt. I formelen, setningenC2: N2 = B10 returnerer en matrise som inneholder alle FALSE -verdier bortsett fra en som samsvarer med B10. Nå er formelen
| =SUMPRODUCT ((C3: N7)*{FALSK, FALSK, FALSK, FALSK,EKTE, FALSK, FALSK, FALSK, FALSK, FALSK, FALSK, FALSK}) |
Nå multipliseres C3: N7 til hver verdi i denne matrisen. Hver kolonne blir null bortsett fra kolonnen som multipliseres med TRUE. Nå blir formelen:
| = SUMPRODUCT ({0,0,0,0,6,0,0,0,0,0,0,0; 0,0,0,0,12,0,0,0,0,0,0, 0; 0,0,0,0,15,0,0,0,0,0,0,0; 0,0,0,0,15,0,0,0,0,0,0,0; 0,0,0,0,8,0,0,0,0,0,0,0}) |
Nå er denne matrisen oppsummert, og vi får summen av kolonnen som samsvarer med kolonnen i cellen B10.
Metode 2: Sum hele kolonnen i tabellen ved hjelp av INDEX-MATCH-funksjonen.
Syntaksen til metoden for å summere den matchende kolonneoverskriften i excel er:
| = SUM (INDEKS (kolonner ,, MATCH (overskrift, overskrifter, 0))) |
Alle variablene i denne metoden er de samme som SUMPRODUCT -metoden. La oss bare implementere det for å løse problemet. Skriv denne formelen i C10.
| = SUM (INDEKS (C3: N7,, MATCH (B10, C2: N2,0))) |
Dette returnerer:
Hvordan virker det?
Formelen er løst ut og inn. Først returnerer MATCH -funksjonen indeksen for den matchende måneden fra området C2: N2. Siden vi har mai i B1o, får vi 5. Nå blir formelen
| = SUMME (INDEKS (C3: N7,, 5)) |
Deretter returnerer INDEX -funksjonen verdier fra den femte kolonnen i C3: N7. Nå blir formelen:
| = SUM ({6; 12; 15; 15; 8}) |
Og til slutt får vi summen av disse verdiene.
Metode 3: Sum hele kolonnen i tabellen ved hjelp av navngitt område og INDIRECT -funksjon
Alt blir enkelt hvis du navngir områdene som kolonneoverskrifter. I denne metoden må vi først navngi kolonnene som overskriftsnavn.
Velg tabellen inkludert overskriftene og trykk CTRL+SKIFT+F3. Det åpner en dialogboks for å lage et navn fra områdene. Kontroller den øverste raden og trykk OK.
Det vil navngi alle datakolonnene som deres overskrifter.
Nå vil den generiske formelen for å summere den matchende kolonnen være:
| = SUM (INDIREKT (overskrift)) |
Overskrift: Det er navnet på kolonnen du vil oppsummere. I dette eksemplet er det B10 som inneholder mai per nå.
For å implementere denne generiske formelen, skriver du denne formelen i celle C10.
| = SUMME (INDIREKT (B10)) |
Dette returnerer summen av mai måned:
En annen metode ligner denne. I denne metoden bruker vi excel -tabeller og dens strukturerte navn. La oss si hvis du har kalt tabellen ovenfor som tabell1. Da vil denne formelen fungere på samme måte som formelen ovenfor.
| = SUMME (INDIRECT ("Tabell1 [" & B10 & "]")) |
Hvordan virker det?
I denne formelen tar INDIRECT -funksjonen referansen til navnet og konverterer det til den faktiske navnreferansen. Fremgangsmåten fremover er enkel. SUMM -funksjonen oppsummerer det navngitte området.
Så ja gutta, slik kan du oppsummere den matchende kolonnen i excel. Jeg håper det er nyttig og forklarende for deg. Hvis du er i tvil om denne artikkelen eller et annet Excel/VBA -relatert emne, kan du spørre i kommentarfeltet nedenfor.
Slik summerer du ved å matche rad og kolonne i Excel |SUMPRODUCT er den mest allsidige funksjonen når det gjelder summering og telling av verdier med vanskelige kriterier. Den generiske funksjonen som skal summeres ved å matche kolonne og rad er …
SUMIF med 3D -referanse 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.
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 vil gjøre arbeidet ditt enda raskere i 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.