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. Lad os undersøge disse måder.
I modsætning til andre artikler, lad os først se scenariet.
Her har jeg en tabel med salg udført af forskellige sælgere i forskellige måneder.
Nu er opgaven at få summen af den givne måneds salg i Cell C10. Hvis vi ændrer måneden i B10, bør summen ændre sig og returnere denne måneds sum uden at ændre noget i formlen.
Metode 1: Sum hele kolonnen i tabel ved hjælp af SUMPRODUCT -funktionen.
Syntaksen for SUMPRODUCT -metoden til at summere matchende kolonne er:
| = SUMPRODUCT ((kolonner)*(overskrifter = overskrift)) |
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.
Overskrifter:Det er header -området af kolonner som du vil opsummere. I ovenstående data er det C2: N2.
Overskrift: Det er den overskrift, du vil matche. I eksemplet ovenfor er det i B10.
Uden yderligere forsinkelse lad os bruge formlen.
| = SUMPRODUCT ((C3: N7)*(C2: N2 = B10)) |
og dette vender tilbage:
Hvordan virker det?
Det er enkelt. I formlen, sætningenC2: N2 = B10 returnerer en matrix, der indeholder alle FALSE værdier undtagen en, der matcher B10. Nu er formlen
| =SUMPRODUCT ((C3: N7)*{FALSK, FALSK, FALSK, FALSK,SAND, FALSK, FALSK, FALSK, FALSK, FALSK, FALSK, FALSK}) |
Nu multipliceres C3: N7 til hver værdi i dette array. Hver kolonne bliver nul undtagen den kolonne, der ganges med SAND. Nu bliver formlen:
| = 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,0}) |
Nu er dette array opsummeret, og vi får summen af den kolonne, der matcher kolonnen i cellen B10.
Metode 2: Sum hele kolonnen i tabel ved hjælp af INDEX-MATCH-funktionen.
Syntaksen for metoden til at opsummere den matchende kolonneoverskrift i excel er:
| = SUM (INDEX (kolonner ,, MATCH (overskrift, overskrifter, 0))) |
Alle variablerne i denne metode er de samme som SUMPRODUCT -metoden. Lad os bare implementere det for at løse problemet. Skriv denne formel i C10.
| = SUM (INDEX (C3: N7,, MATCH (B10, C2: N2,0))) |
Dette returnerer:
Hvordan virker det?
Formlen løses indefra og ud. For det første returnerer MATCH -funktionen indekset for den matchende måned fra intervallet C2: N2. Da vi har maj i B1o, får vi 5. Nu bliver formlen
| = SUM (INDEX (C3: N7,, 5)) |
Dernæst returnerer INDEX -funktionen værdier fra 5. kolonne i C3: N7. Nu bliver formlen:
| = SUM ({6; 12; 15; 15; 8}) |
Og endelig får vi summen af disse værdier.
Metode 3: Sum hele kolonnen i tabel ved hjælp af navngivet område og INDIRECT -funktion
Alt bliver enkelt, hvis du navngiver dine områder som kolonneoverskrifter. I denne metode skal vi først navngive kolonnerne som deres overskriftsnavne.
Vælg tabellen inklusive overskrifterne, og tryk på CTRL+SKIFT+F3. Det åbner en dialog for at oprette et navn fra områderne. Kontroller den øverste række, og tryk på knappen OK.
Det vil navngive alle datakolonner som deres overskrifter.
Nu vil den generiske formel for at summere den matchende kolonne være:
| = SUM (INDIREKT (overskrift)) |
Overskrift: Det er navnet på den kolonne, du vil opsummere. I dette eksempel er det B10, der indeholder maj fra nu af.
For at implementere denne generiske formel skal du skrive denne formel i celle C10.
| = SUM (INDIREKT (B10)) |
Dette returnerer summen af maj måned:
En anden metode ligner denne. I denne metode bruger vi excel -tabeller og dens strukturerede navngivning. Lad os sige, hvis du har navngivet ovenstående tabel som tabel1. Derefter fungerer denne formel på samme måde som ovenstående formel.
| = SUM (INDIRECT ("Tabel1 [" & B10 & "]")) |
Hvordan virker det?
I denne formel tager INDIREKTE -funktionen referencen til navnet og konverterer det til en egentlig navnerefference. Fremgangsmåden fremover er enkel. SUM -funktionen opsummerer det navngivne område.
Så ja fyre, sådan kan du opsummere den matchende kolonne i excel. Jeg håber, det er nyttigt og forklarende for dig. Hvis du er i tvivl om denne artikel eller et andet Excel/VBA -relateret emne, så spørg i kommentarfeltet herunder.
Sådan summeres ved at matche række og kolonne i Excel |SUMPRODUCT 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 …
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.