I denne artikel lærer vi at SUMMERE HVIS ved hjælp af opslagsværdier i Excel.
Med enkle ord, mens vi arbejder med forskellige datatabeller, er vi nogle gange nødt til at beregne værdier i første tabelbaserede værdier i en anden tabel. Betingelse er at få summen af værdier i den første tabel ved hjælp af den anden tabelværdi
Til denne artikel skal vi bruge følgende funktioner:
- SUMPRODUCT funktion
- SUMIF -funktion
SUMPRODUCT -funktionen er en matematisk funktion i Excel. Det fungerer på flere områder. Det multiplicerer de tilsvarende arrays og tilføjer dem derefter.
I alt = (A1 * B1 * C1 *… + A2 * B2 * C2 *… +…)
Syntaks:
= SUMPRODUCT (array1, [array2],…)
array: Det er området eller en liste med værdier.
SUMIF -funktionen i Excel returnerer SUMMEN for de tal, der har opfyldt de angivne kriterier. SUM -område og kriterier kan være ens eller forskellige.
Generisk Excel SUMIF -formel:
= SUMIF (kriterierækkevidde, kriterier, summarange)
criteria_range: område, hvor betingelsen anvendes.
kriterier: betingelse for at anvende på criteria_range.
sum_range: sumsområde på grundlag af kriterier.
Nu vil vi lave en formel ud af disse funktioner. Her vil vi give dataene, og vi havde brug for sumresultater, hvor værdien matcher værdien i opslagstabellen.
Generisk formel:
= SUMPRODUCT (SUMIF (resultat, poster, sum_nums))
resultat : værdier, der skal matches i opslagstabellen
optage : værdier i den første tabel
sum_nums : værdier, der skal summeres i henhold til matchende værdier.
Lad os teste denne formel ved at køre den på et eksempel
Her beregner vi de samlede point for et fodboldspillende hold ved hjælp af pointtabellen eller opslagstabellen.
Her er resultatet (B8: B10) givet som navngivet område og punkter (C8: C10) givet som navngivet område.
Nu vil vi bruge nedenstående formel til at få SUM af point for Month1 i F3 -cellen
Brug formlen:
= SUMPRODUCT (SUMIF (resultat, B3: E3, point))
Forklaring:
- SUMIF -funktion matcher resultatet med værdier i B3: E3.
- Derefter returneres punkter, der svarer til resultatet, til SUMPRODUCT -funktionen.
- SUMPRODUCT -funktionen returnerer SUM af værdier, der er givet som array til funktionen.
Som du kan se her, er det samlede point scoret af holdet i første måned 7.
3 (W) + 0 (L) + 1 (D) + 3 (W) = 7 point
Kopier formlen i andre celler, vælg cellerne, der tager den første celle, hvor formlen allerede er anvendt, brug genvejstasten Ctrl + D eller brug træk -ned -celleindstillingen i excel.
Som du kan se i ovenstående snapshot scorede pointene i de 4 forskellige måneder.
Få SUMMEN af alle point i 4 måneder.
Det betyder, at holdet scorede i alt 18 point på 4 måneder.
Alternativ metode
Brug metoden SUM og array i stedet for at bruge SUMPRODUCT -funktionen.
Brug formlen:
{= SUM (SUMIF (resultat, B3: E3, point))}
Krøllede seler er arraymetoden til at foreslå funktionen beregne al den returnerede værdi ved SUMIF -funktion.
Bemærk: Må ikke brug symbolerne til krøllet seler manuelt. Brug Ctrl + Shift + Enter at bruge matrixmetoden i Excel.
Som du kan se fra ovenstående formel, kan du få samlede point ved hjælp af betinget opslagstabel.
Bemærkninger:
- Formlen fungerer kun med tal.
- Formlen fungerer kun, når der ikke er nogen dubletter i opslagstabellen
- SUMPRODUCT -funktionen betragter ikke -numeriske værdier som 0s.
- SUMPRODUCT -funktionen betragter logisk værdi TRUE som 1 og Falsk som 0.
- Argumentmatrixen skal have samme længde, ellers skal funktionen
Håber denne artikel om, hvordan du kun returnerer SUM fra formler i Excel, er forklarende. Find flere artikler om SUMPRODUCT -funktioner her. Del venligst din forespørgsel nedenfor i kommentarfeltet. Vi hjælper dig.
Sådan bruges funktionen SUMPRODUCT i Excel: Returnerer SUMMEN efter multiplikation af værdier i flere arrays i excel.
SUM, hvis datoen er mellem : Returnerer SUM af værdier mellem givne datoer eller periode i excel.
Sum, hvis datoen er større end den givne dato: Returnerer SUM af værdier efter den givne dato eller periode i excel.
2 måder at summe efter måned i Excel: Returnerer SUM af værdier inden for en given specifik måned i excel.
Sådan summeres flere kolonner med betingelse: Returnerer SUM af værdier på tværs af flere kolonner med betingelse i excel
Populære artikler
50 Excel -genvej til at øge din produktivitet
Rediger en rulleliste
Absolut reference i Excel
Hvis med betinget formatering
Hvis det er med jokertegn
Vlookup efter dato
Konverter tommer til fod og tommer i Excel 2016
Deltag i for- og efternavn i excel
Tæl celler, der matcher enten A eller B