I denne artikel lærer vi, hvordan du opretter beregnede kolonner i Excel.
Scenarie
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 se med forskellige scenarier først.
Sådan opretter du en beregnet kolonne i en tabel:
- Vælg en celle i en af kolonnerne i en tabel eller en tom celle lige til den sidste kolonne, i eksemplet nedenfor er den valgte celle G2.
- Indsæt den formel, der beregner salg pr. Enhed, indtast symbolet på lige (=), vælg celle F2, indtast divideringssymbolet (/), og vælg derefter celle E2, og tryk på Enter.
Formlen kopieres automatisk til alle cellerne i kolonnen.
Eksempel:
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.
| =SUMPRODUKT((C3: N7)*(C2: N2 = B10)) |
og dette vender tilbage:
Hvordan virker det?
Det er enkelt. I formlen, sætningen C2: 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(INDEKS(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(INDEKS(C3: N7 ,,MATCH(B10, C2: N2,0))) |
Dette returnerer:
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 & "]")) |
Håber denne artikel om Sådan oprettes beregnede kolonner i Excel er forklarende. Find flere artikler om beregning af værdier og relaterede Excel -formler her. Hvis du kunne lide vores blogs, kan du dele den med dine venner på Facebook. Og også du kan følge os på Twitter og Facebook. Vi vil meget gerne høre fra dig, lad os vide, hvordan vi kan forbedre, supplere eller innovere vores arbejde og gøre det bedre for dig. Skriv til os på e -mail -stedet.
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 begår 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:
Sådan bruges IF -funktionen i Excel : IF -sætningen i Excel kontrollerer betingelsen og returnerer en bestemt værdi, hvis betingelsen er SAND, eller returnerer en anden specifik værdi, hvis FALSK.
Sådan bruges 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.
Sådan bruges SUMIF -funktionen i Excel : Dette er en anden vigtig instrumentbrætfunktion. Dette hjælper dig med at opsummere værdier på bestemte betingelser.
Sådan bruges COUNTIF -funktionen i Excel : Tæl værdier med betingelser ved hjælp af denne fantastiske funktion. Du behøver ikke at filtrere dine data for at tælle bestemte værdier. Countif -funktion er afgørende for at forberede dit dashboard.