I den här artikeln lär vi oss hur man skapar beräknade kolumner i Excel.
Scenario
Om du vill få summan av en kolumn genom att bara använda kolumnnamnet kan du göra detta på tre enkla sätt i Excel. Låt oss utforska dessa sätt.
Till skillnad från andra artiklar, låt oss se med olika scenarier först.
Så här skapar du en beräknad kolumn i en tabell:
- Markera en cell i en av kolumnerna i en tabell eller en tom cell precis till den sista kolumnen, i exemplet nedan är den markerade cellen G2.
- Sätt in formeln som beräknar försäljning per enhet, skriv lika (=) symbolen, välj cell F2, skriv divider (/) symbolen och välj sedan cell E2 och tryck på Retur.
Formeln kopieras automatiskt till alla celler i kolumnen.
Exempel:
Här har jag en tabell med försäljning gjord av olika säljare under olika månader.
Nu är uppgiften att få summan av given månads försäljning i Cell C10. Om vi ändrar månaden i B10, bör summan ändras och returnera månadens summa, utan att ändra något i formeln.
Metod 1: Summa hela kolumnen i tabellen med funktionen SUMPRODUCT.
Syntaxen för SUMPRODUCT -metoden för att summera matchande kolumn är:
| = SUMPRODUCT ((kolumner)*(rubriker = rubrik)) |
Kolumner: Det är det 2-dimensionella intervallet för kolumnerna som du vill summera. Den ska inte innehålla rubriker. I tabellen ovan är det C3: N7.
Rubriker: Det är rubrikområdet för kolumner som du vill summera. I ovanstående data är det C2: N2.
Rubrik: Det är rubriken som du vill matcha. I exemplet ovan finns det i B10.
Utan ytterligare dröjsmål, låt oss använda formeln.
| =SUMPRODUKT((C3: N7)*(C2: N2 = B10)) |
och detta kommer tillbaka:
Hur fungerar det?
Det är enkelt. I formeln, uttalandet C2: N2 = B10 returnerar en array som innehåller alla FALSE -värden utom en som matchar B10. Nu är formeln
| = SUMPRODUCT ((C3: N7)*{FALSE, FALSE, FALSE, FALSE,SANN, FALSKT, FALSKT, FALSKT, FALSKT, FALSKT, FALSKT, FALSKT}) |
Nu multipliceras C3: N7 till varje värde i denna array. Varje kolumn blir noll utom kolumnen som multipliceras med TRUE. Nu blir formeln:
| = 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}) |
Nu summeras denna array, och vi får summan av kolumnen som matchar kolumnen i cellen B10.
Metod 2: Summa hela kolumnen i tabellen med hjälp av INDEX-MATCH-funktionen.
Syntaxen för metoden för att summera matchande kolumnrubrik i excel är:
| =BELOPP(INDEX(kolumner,,MATCH(rubrik, rubriker, 0))) |
Alla variabler i denna metod är desamma som SUMPRODUCT -metoden. Låt oss bara implementera det för att lösa problemet. Skriv denna formel i C10.
| =BELOPP(INDEX(C3: N7 ,,MATCH(B10, C2: N2,0))) |
Detta returnerar:
Metod 3: Summa hela kolumnen i tabellen med namngivet intervall och INDIRECT -funktion
Allt blir enkelt om du heter dina intervall som kolumnrubriker. I den här metoden måste vi först namnge kolumnerna som deras rubriknamn.
Välj tabellen inklusive rubrikerna och tryck på CTRL+SKIFT+F3. Det öppnar en dialogruta för att skapa ett namn från intervallerna. Kontrollera den översta raden och tryck på OK -knappen.
Det kommer att namnge alla datakolumner som deras rubriker.
Nu kommer den generiska formeln för att summera matchande kolumn att vara:
| =BELOPP(INDIREKT(rubrik)) |
Rubrik: Det är namnet på kolumnen som du vill summera. I det här exemplet är det B10 som innehåller maj från och med nu.
För att implementera denna generiska formel, skriv den här formeln i cell C10.
| =BELOPP(INDIREKT(B10)) |
Detta returnerar summan av maj månad:
En annan metod liknar denna. I denna metod använder vi excel -tabeller och dess strukturerade namngivning. Låt oss säga om du har namngett tabellen ovan som tabell1. Då fungerar denna formel på samma sätt som ovanstående formel.
| =BELOPP(INDIRECT ("Tabell1 [" & B10 & "]")) |
Hoppas att den här artikeln om hur man skapar beräknade kolumner i Excel är förklarande. Hitta fler artiklar om beräkning av värden och relaterade Excel -formler här. Om du gillade våra bloggar, dela den med dina vänner på Facebook. Och du kan också följa oss på Twitter och Facebook. Vi vill gärna höra från dig, låt oss veta hur vi kan förbättra, komplettera eller förnya vårt arbete och göra det bättre för dig. Skriv till oss på e -postwebbplatsen.
Hur man summerar genom att matcha rad och kolumn i Excel : SUMPRODUCT är den mest mångsidiga funktionen när det gäller att summera och räkna värden med knepiga kriterier. Den generiska funktionen att summera genom att matcha kolumn och rad är …
SUMIF med 3D -referens i Excel : Det roliga faktum är att den normala Excel 3D -referensen inte fungerar med villkorade funktioner, som SUMIF -funktionen. I den här artikeln lär vi oss hur man får 3D -referenser att arbeta med SUMIF -funktionen.
Relativ och absolut referens i Excel: Att referera i excel är ett viktigt ämne för varje nybörjare. Även erfarna excelanvändare gör misstag när de refererar.
Dynamisk kalkylbladreferens: Ge referensblad dynamiskt med hjälp av Excel -indirektfunktionen. Det här är enkelt …
Utöka referenser i Excel: Den expanderande referensen expanderar när den kopieras nedåt eller åt höger. Vi använder $ -tecknet före kolumn- och radnumret för att göra det. Här är ett exempel …
Allt om absolut referens: Standardreferenstypen i excel är relativ, men om du vill att referensen för celler och intervall ska vara absolut använder du $ -tecknet. Här är alla aspekter av absolut referens i Excel.
Populära artiklar:
Hur man använder IF -funktionen i Excel : IF -satsen i Excel kontrollerar villkoret och returnerar ett specifikt värde om villkoret är SANT eller returnerar ett annat specifikt värde om det är FALSKT.
Så här använder du VLOOKUP -funktionen i Excel : Detta är en av de mest använda och populära Excel -funktionerna som används för att leta upp värde från olika intervall och ark.
Hur man använder SUMIF -funktionen i Excel : Detta är en annan viktig instrumentpanel. Detta hjälper dig att summera värden på specifika förhållanden.
Hur man använder funktionen RÄNKNING i Excel : Räkna värden med villkor med denna fantastiska funktion. Du behöver inte filtrera dina data för att räkna specifika värden. Countif -funktionen är avgörande för att förbereda din instrumentpanel.