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 scenariot först.
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 den 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.
| = SUMPRODUCT ((C3: N7)*(C2: N2 = B10)) |
och detta kommer tillbaka:
Hur fungerar det?
Det är enkelt. I formeln, uttalandetC2: 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:
| = SUMMA (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.
| = SUMMA (INDEX (C3: N7,, MATCH (B10, C2: N2,0))) |
Detta returnerar:
Hur fungerar det?
Formeln löses ut och in. Först returnerar MATCH -funktionen indexet för matchande månad från intervallet C2: N2. Eftersom vi har maj i B1o får vi 5. Nu blir formeln
| = SUMMA (INDEX (C3: N7,, 5)) |
Därefter returnerar INDEX -funktionen värden från den femte kolumnen i C3: N7. Nu blir formeln:
| = SUMMA ({6; 12; 15; 15; 8}) |
Och slutligen får vi summan av dessa värden.
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:
| = SUMMA (INDIRECT (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.
| = SUMMA (INDIRECT (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.
| = SUMMA (INDIRECT ("Tabell1 [" & B10 & "]")) |
Hur fungerar det?
I den här formeln tar INDIRECT -funktionen referensen till namnet och konverterar det till den faktiska namnreferensen. Proceduren och framåt är enkel. SUMMEFunktionen summerar det namngivna intervallet.
Så ja killar, så här kan du summera matchande kolumn i excel. Jag hoppas att det är till hjälp och förklarande för dig. Om du har några tvivel angående denna artikel eller något annat Excel/VBA -relaterat ämne, fråga i kommentarfältet nedan.
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 kalkylbladshänvisning | 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:
50 Excel -genvägar för att öka din produktivitet | Bli snabbare på din uppgift. Dessa 50 genvägar gör ditt arbete ännu snabbare i Excel.
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.
COUNTIF i Excel 2016 | Räkna värden med förhållanden med denna fantastiska funktion. Du behöver inte filtrera dina data för att räkna specifikt värde. Countif -funktionen är avgörande för att förbereda din instrumentpanel.
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.