Hittills har vi lärt oss hur man summerar hela matchande kolumn i en tabell i Excel. Men hur summerar vi värden när vi behöver matcha kolumn och rad. I den här artikeln lär vi oss hur man gör summan av matchande rader och kolumner.
Det finns två formler för att göra detta, men låt oss först scenariot.
Här har jag en tabell som registrerar försäljningen av anställda under olika månader. Namnet på de anställda kan upprepas. Se figuren nedan:
Vi måste få summan av maj månad där säljaren är Donald.
Metod 1: Sammanfattning av matchande kolumnrubrik och radrubrik Med funktionen SUMPRODUCT.
De SUMPRODUCT Funktion ä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:
| = SUMPRODUCT ((kolumner)*(column_headers = column_heading)*(row_headers = row_heading) |
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.
column_headers:Det är rubrikområdet förkolumner som du vill summera. I ovanstående data är det C2: N2.
column_heading: Det är rubriken som du vill matcha. I exemplet ovan finns det i B13.
Utan ytterligare dröjsmål, låt oss använda formeln.
row_headers:Det är rubrikområdet förrader som du vill summera. I ovanstående data är det B3: B10.
rad_rubrik: Det är rubriken som du vill matcha i rader. I exemplet ovan är det i F13.
Utan ytterligare dröjsmål, låt oss använda formeln.
Skriv denna formel i cell D13 och låt excel göra magi (det finns inget som heter magi) …
| = SUMPRODUCT ((C3: N10)*(C2: N2 = B13)*(B3: B10 = E13)) |
Detta returnerar värdet:
Nu när du ändrar månaden eller säljaren kommer summan att ändras enligt radrubriken och kolumnrubriken.
Hur fungerar det?
Denna enkla booleska logik.
(C2: N2 = B13): Detta uttalande returnerar en matris med SANT och FALSKT. Alla matchande värden i kolumnen har sanna och andra har falska. I det här fallet har vi bara en True eftersom intervallet C2: N2 bara innehåller en instans maj vid 5th Plats.
(B3: B10 = E13): Detta fungerar på samma sätt som ovan och returnerar en matris SANT och FALSKT. Alla matchande värden har SANT och andra har FALSKA. I det här fallet kommer vi att ha 2 TRUE eftersom intervallet B3: B10 har två instanser av "Donald".
(C2: N2 = B13)*(B3: B10 = E13): Nu multiplicerar vi matriserna som returneras med uttalanden. Detta kommer att implementera och logik och vi kommer att få en array med 1: or och 0: or. Nu kommer vi att ha en 2D -array som kommer att innehålla 2 1: or och vila 0: or.
(C3: N10)*(C2: N2 = B13)*(B3: B10 = E13)= Slutligen multiplicerar vi 2D -matrisen med 2D -tabellen. Det kommer att återge en array med 0s och siffrorna som matchar kriterierna.
Slutligen, SUMPRODUKT funktion summerar matrisen som resulterar i önskad utmatning.
Metod 2: Sammanfattning av matchande kolumnrubrik och radrubrik Med funktionen SUMMA och OM
Den generiska formeln för att summera matchande rad och kolumn med funktionen SUMMA och OM Excel är:
| = SUMMA (IF (column_headers = column_heading, IF (row_headers = row_heading, columns))) |
Alla variabler är desamma som i den ovan förklarade metoden. Här behöver de bara användas i en annan ordning.
Skriv denna formel i cell D13:
| = SUMMA (IF (C2: N2 = B13, IF (B3: B10 = E13, C3: N10))) |
Detta returnerar rätt svar. Se skärmdumpen nedan:
Hur fungerar det?
Logiken är densamma som den första SUMPRODCUT -metoden, bara mekanismen är annorlunda. Om jag förklarar det kort, returnerar den inre IF -funktionen en 2D -uppsättning med samma dimension som tabellen. Den här matrisen innehåller antalet två matchade rader. Sedan matchar den inre IF-funktionen tvåkolumnrubrikerna i den här matrisen och returnerar 2D-matrisen som bara innehåller de siffror som matchar både, kolumn och rubrik. Alla andra element i matrisen är FALSE.
Slutligen summerar SUMM -funktionen denna array och vi får vår summa.
Så ja killar, så här kan du sammanfatta matchande rader och kolumner från en tabell i excel. Jag hoppas att det var förklarande och användbart för dig. Om du har några tvivel angående detta ämne eller har något annat Excel/VBA -relaterat tvivel, fråga i kommentarfältet nedan.
Hur man summerar kolumn i en Excel genom att matcha rubrik | 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. Syntaxen för SUMPRODUCT -metoden för att summera matchande kolumn ä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.