Indtil videre har vi lært, hvordan man summerer hele matchende kolonne i en tabel i Excel. Men hvordan summerer vi værdier, når vi skal matche kolonne og række. I denne artikel lærer vi, hvordan man gør summen af matchende rækker og kolonner.
Der er to formler til at gøre dette, men lad os først scenariet.
Her har jeg en tabel, der registrerer salget foretaget af medarbejdere i forskellige måneder. Medarbejdernes navn kan gentages. Se nedenstående figur:
Vi skal få summen af maj måned, hvor sælgeren er Donald.
Metode 1: Opsummering af den matchende kolonneoverskrift og rækkeoverskrift Ved hjælp af funktionen SUMPRODUCT.
Det SUMPRODUCT Funktion er den mest alsidige funktion, når det kommer til at summere og tælle værdier med vanskelige kriterier. Den generiske funktion, der skal summeres ved at matche kolonne og række, er:
| = SUMPRODUCT ((kolonner)*(column_headers = column_heading)*(row_headers = row_heading) |
Kolonner:Det er det todimensionale område af kolonnerne, du vil opsummere. Det bør ikke indeholde overskrifter. I tabellen ovenfor er det C3: N7.
column_headers:Det er header -området afkolonner som du vil opsummere. I ovenstående data er det C2: N2.
column_heading: Det er den overskrift, du vil matche. I eksemplet ovenfor er det i B13.
Uden yderligere forsinkelse lad os bruge formlen.
row_headers:Det er header -området afrækker som du vil opsummere. I ovenstående data er det B3: B10.
række_overskrift: Det er den overskrift, du vil matche i rækker. I eksemplet ovenfor er det i F13.
Uden yderligere forsinkelse lad os bruge formlen.
Skriv denne formel i celle D13, og lad excel gøre magien (der er ikke noget, der hedder magi) …
| = SUMPRODUCT ((C3: N10)*(C2: N2 = B13)*(B3: B10 = E13)) |
Dette returnerer værdien:
Når du nu ændrer måneden eller sælgeren, ændres summen i henhold til rækkeoverskriften og kolonneoverskriften.
Hvordan virker det?
Denne simple boolske logik.
(C2: N2 = B13): Denne erklæring returnerer en vifte af SAND og FALSK. Alle matchende værdier i kolonnen har sand, og andre vil have falsk. I dette tilfælde vil vi kun have en sand, da intervallet C2: N2 kun indeholder én forekomst maj kl. 5th Beliggenhed.
(B3: B10 = E13): Dette fungerer på samme måde som ovenfor og returnerer et array SANDT og FALSKT. Alle matchende værdier vil have SAND, og andre vil have FALSK. I dette tilfælde vil vi have 2 TRUE'er, da området B3: B10 har to forekomster af "Donald".
(C2: N2 = B13)*(B3: B10 = E13): Nu multiplicerer vi de arrays, der returneres med udsagn. Dette vil implementere og logik, og vi får en række 1'er og 0'er. Nu får vi et 2D -array, der indeholder 2 1'er og hviler 0'er.
(C3: N10)*(C2: N2 = B13)*(B3: B10 = E13)= Endelig multiplicerer vi 2D -arrayet med 2D -tabellen. Det returnerer igen en matrix med 0'er og de tal, der matcher kriterierne.
Endelig er SUMPRODUKT funktion opsummerer arrayet, hvilket vil resultere i det ønskede output.
Metode 2: Opsummering af den matchende kolonneoverskrift og rækkeoverskrift Brug funktionen SUM og IF
Den generiske formel for summering af matchende række og kolonne ved hjælp af SUM og IF Excel -funktion er:
| = SUM (IF (column_headers = column_heading, IF (row_headers = row_heading, kolonner))) |
Alle variablerne er de samme som i den ovenfor forklarede metode. Her skal de bare bruges i en anden rækkefølge.
Skriv denne formel i celle D13:
| = SUM (HVIS (C2: N2 = B13, HVIS (B3: B10 = E13, C3: N10))) |
Dette giver det korrekte svar. Se skærmbilledet herunder:
Hvordan virker det?
Logikken er den samme som den første SUMPRODCUT -metode, kun mekanismen er en anden. Hvis jeg forklarer det kort, returnerer den indre IF -funktion et 2D -array med samme dimension som tabellen. Denne matrix indeholder antallet af to matchede rækker. Derefter matcher den indre IF-funktion to-kolonneoverskrifterne i dette array og returnerer 2D-arrayet, der kun indeholder de tal, der matcher både, kolonne og overskrift. Alle andre elementer i arrayet vil være FALSKE.
Endelig summerer SUM -funktionen dette array, og vi får vores sum.
Så ja fyre, sådan kan du opsummere de matchende rækker og kolonner fra en Excel -tabel. Jeg håber, det var forklarende og nyttigt for dig. Hvis du er i tvivl om dette emne eller har anden excel/VBA -relateret tvivl, så spørg i kommentarfeltet herunder.
Sådan summeres kolonne i en Excel ved at matche overskrift | Hvis du vil få summen af en kolonne ved blot at bruge kolonnens navn, kan du gøre dette på 3 nemme måder i Excel. Syntaksen for SUMPRODUCT -metoden til at summere matchende kolonne er:
SUMIF med 3D -reference 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.
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 gør dit 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.