Så langt har vi lært hvordan vi summerer hele matchende kolonne i en tabell i Excel. Men hvordan summerer vi verdier når vi trenger å matche kolonne og rad. I denne artikkelen lærer vi hvordan du gjør summen av matchende rader og kolonner.
Det er to formler for å gjøre dette, men la oss først scenariet.
Her har jeg en tabell som registrerer salget gjort av ansatte i forskjellige måneder. Navnet på ansatte kan gjenta seg. Se figuren nedenfor:
Vi må få summen av mai måned der selgeren er Donald.
Metode 1: Oppsummering av matchende kolonneoverskrift og radoverskrift Ved hjelp av SUMPRODUCT -funksjonen.
De SUMPRODUCT Funksjon er den mest allsidige funksjonen når det gjelder å summere og telle verdier med vanskelige kriterier. Den generiske funksjonen som skal summeres ved å matche kolonne og rad er:
| = SUMPRODUCT ((kolonner)*(column_headers = column_heading)*(row_headers = row_heading) |
Kolonner:Det er det todimensjonale området for kolonnene du vil oppsummere. Den skal ikke inneholde overskrifter. I tabellen ovenfor er det C3: N7.
column_headers:Det er toppteksten påkolonner som du vil oppsummere. I dataene ovenfor er det C2: N2.
column_heading: Det er overskriften du vil matche. I eksemplet ovenfor er det i B13.
Uten ytterligere forsinkelse, la oss bruke formelen.
row_headers:Det er toppteksten pårader som du vil oppsummere. I dataene ovenfor er det B3: B10.
rad_overskrift: Det er overskriften du vil matche i rader. I eksemplet ovenfor er det i F13.
Uten ytterligere forsinkelse, la oss bruke formelen.
Skriv denne formelen i celle D13 og la excel gjøre magien (det er ikke noe som heter magi) …
| = SUMPRODUCT ((C3: N10)*(C2: N2 = B13)*(B3: B10 = E13)) |
Dette returnerer verdien:
Når du endrer måneden eller selgeren, vil summen endres i henhold til radoverskriften og kolonneoverskriften.
Hvordan virker det?
Denne enkle boolske logikken.
(C2: N2 = B13): Denne uttalelsen vil returnere en rekke SANN og FALSK. Alle matchende verdier i kolonnen har sanne og andre vil ha usanne. I dette tilfellet vil vi bare ha én True ettersom området C2: N2 bare inneholder én forekomst mai ved 5th Plassering.
(B3: B10 = E13): Dette fungerer på samme måte som ovenfor og returnerer en matrise SANN og FALSK. Alle matchende verdier vil ha SANN og andre vil ha FALSK. I dette tilfellet vil vi ha 2 TRUEs da området B3: B10 har to forekomster av "Donald".
(C2: N2 = B13)*(B3: B10 = E13): Nå multipliserer vi matrisene som returneres med setninger. Dette vil implementere og logikk, og vi får en rekke 1s og 0s. Nå vil vi ha et 2D -array som vil inneholde 2 1s og resten 0s.
(C3: N10)*(C2: N2 = B13)*(B3: B10 = E13)= Til slutt multipliserer vi 2D -matrisen med 2D -tabellen. Det vil returnere en rekke 0 -er og tallene som samsvarer med kriteriene.
Til slutt, SUMPRODUKT funksjon vil oppsummere matrisen som vil resultere i ønsket utgang.
Metode 2: Oppsummering av matchende kolonneoverskrift og radoverskrift Ved hjelp av SUM og IF -funksjonen
Den generiske formelen for å summere matchende rad og kolonne ved hjelp av SUM og IF Excel -funksjonen er:
| = SUM (IF (column_headers = column_heading, IF (row_headers = row_heading, columns))) |
Alle variablene er de samme som i den ovenfor forklarte metoden. Her må de bare brukes i en annen rekkefølge.
Skriv denne formelen i celle D13:
| = SUMMER (HVIS (C2: N2 = B13, HVIS (B3: B10 = E13, C3: N10))) |
Dette gir riktig svar. Se skjermbildet nedenfor:
Hvordan virker det?
Logikken er den samme som den første SUMPRODCUT -metoden, bare mekanismen er annerledes. Hvis jeg forklarer det kort, returnerer den indre IF -funksjonen en 2D -serie med samme dimensjon som tabellen. Denne matrisen inneholder antallet to matchede rader. Deretter samsvarer den indre IF-funksjonen med to-kolonneoverskriftene i denne matrisen og returnerer 2D-matrisen som bare inneholder tallene som samsvarer med både kolonne og overskrift. Alle andre elementer i matrisen vil være FALSE.
Til slutt oppsummerer SUM -funksjonen denne matrisen, og vi får summen vår.
Så ja gutta, slik kan du oppsummere matchende rader og kolonner fra en tabell i excel. Jeg håper det var forklarende og nyttig for deg. Hvis du er i tvil om dette emnet eller har annen excel/VBA -relatert tvil, spør i kommentarfeltet nedenfor.
Slik summerer du kolonne i en Excel ved å matche overskrift | Hvis du vil få summen av en kolonne ved å bare bruke kolonnenavnet, kan du gjøre dette på tre enkle måter i Excel. Syntaksen til SUMPRODUCT -metoden for å summere matchende kolonne 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.