I denne artikel lærer vi, hvordan man bruger IF -funktion i stedet for funktionen SUMPRODUCT og SUMIFS i Excel.
Scenarie:
Kort sagt, når vi arbejder med et langt spredt datasæt, skal vi nogle gange finde summen af tal med nogle kriterier over det. For eksempel at finde summen af lønninger i en bestemt afdeling eller have flere kriterier over dato, navne, afdeling eller kan endda nummerere data som lønninger under værdi eller mængde over værdi. Til dette bruger du normalt funktionen SUMPRODUCT eller SUMIFS. Men du ville ikke tro, du udfører den samme funktion med Excel grundlæggende funktion IF -funktion.
Hvordan løser man problemet?
Du skal tænke, hvordan er dette muligt, for at udføre logiske operationer over bordarrays ved hjælp af IF -funktion. HVIS funktion i Excel er meget nyttig, vil den få dig igennem nogle vanskelige opgaver i Excel eller andre kodningssprog. IF funktion tester betingelser på array svarende til nødvendige værdier og returnerer resultatet som array svarende til True betingelser som 1 og Falsk som 0.
Til dette problem bruger vi følgende funktioner:
- SUM -funktion
- IF -funktion
Vi vil kræve disse ovenstående funktioner og en vis grundlæggende følelse af datadrift. logiske betingelser for arrays kan anvendes ved hjælp af logiske operatorer. Disse logiske operatorer arbejder på både tekst og tal. Nedenfor er den generiske formel. { } krøllede seler er det magiske værktøj til at udføre matrixformler med IF -funktion.
Generisk formel:
| { = SUM (HVIS ((logisk_1) * (logisk_2) * … * (logisk_n), sum_array)) } |
Bemærk: Til krøllede seler ( { } ) Brug Ctrl + Shift + Enter når du arbejder med arrays eller intervaller i Excel. Dette genererer som standard Curly Braces på formlen. Forsøg IKKE at hardt kode krøllede seler tegn.
Logisk 1: tester betingelse 1 på array 1
Logisk 2: tester tilstand 2 på array 2 og så videre
sum_array: array, operations sum udføres
Eksempel:
Alt dette kan være forvirrende at forstå. Så lad os teste denne formel ved at køre den på eksemplet nedenfor. Her har vi data om leverede produkter til forskellige byer sammen med tilhørende kategorifelter og mængder. Her har vi dataene, og vi skal finde mængden af cookies sendt til Boston, hvor mængden er større end 40.
Datatabel og kriterietabel er vist i billedet ovenfor. For at forstå det formål brugte vi navngivne intervaller til de brugte arrays. Navngivne områder er angivet nedenfor.
Her :
By defineret for array A2: A17.
Kategori defineret for array B2: A17.
Mængde defineret for array C2: C17.
Nu er du klar til at få det ønskede resultat ved hjælp af nedenstående formel.
Brug formlen:
| { = SUM (HVIS ((By = "Boston") * (Kategori = "Cookies") * (Mængde> 40), Mængde)) } |
Forklaring:
- City = "Boston": kontrollerer værdierne i byområder for at matche med "Boston".
- Category = "Cookies": kontrollerer værdierne i kategoriområdet for at matche med "Cookies".
- Mængde> 40: kontrollerer værdierne i mængdeinterval til ma
- Mængde være array, hvor sum kræves.
- IF funktion kontrollerer alle kriterier og stjerne (*) multiplicerer alle array -resultater.
= SUM (HVIS ({0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 1; 0; 0; 0}, {33; 87; 58; 38; 54; 51; 28; 36; 28; 44; 23; 27; 43; 42; 33; 30}))
- Nu returnerer IF -funktionen kun de mængder, der svarer til 1'erne, og resten ignoreres.
- SUM -funktionen returnerer SUM.
Nu tilføjer mængden, der svarer til 1’er, kun for at få resultatet.
Som du kan se, returneres mængde 43, men der leveres tre cookieordrer til "Boston" med mængde 38, 36 og 43. Vi havde brug for en sum mængde, hvor mængden var over 40. Så formlen returnerer kun 43. Brug nu andre kriterier for at få SUM -mængden for by: "Los Angeles" og kategori: "barer" og mængde være mindre end 50.
Brug formlen
| { = SUM (HVIS ((By = "Los Angeles") * (Kategori = "Barer") * (Mængde <50), Mængde)) } |
Som du kan se, returnerer formlen værdierne 86 som resultat. Hvilken er summen af 2 ordrer, der opfylder betingelserne med mængde 44 & 42. Denne artikel illustrerer, hvordan man udskifter en indlejret IF -formel med en enkelt IF i en matrixformel. Dette kan bruges til at reducere kompleksiteten i komplekse formler. Dette særlige problem kan dog let løses med SUMIFS- eller SUMPRODUCT -funktionen.
Brug af SUMPRODUCT -funktionen:
SUMPRODUCT -funktionen returnerer summen af de tilsvarende værdier i arrayet. Så vi får arrays til at returnere 1s a de True -sætningsværdier og 0s til de falske sætningsværdier. Så sidste sum vil svare, hvor alle udsagn står Sandt.
Brug formlen:
| = SUMPRODUCT ( - (By = "Boston"), - (Kategori = "Cookies"), - (Mængde> 40), Mængde) |
-: operation, der bruges til at konvertere alle TRUE'er til 1'er og False til 0.
SUMPRODUCT -funktionen kontrollerer SUM af mængden, der returneres af SUM- og IF -funktionen forklaret ovenfor.
Tilsvarende for det andet eksempel står resultatet det samme.
Som du kan se, kan SUMPRODUCT -funktionen udføre den samme opgave.
Her er alle observationsnotater vedrørende brug af formlen.
Bemærkninger:
- Sum_arrayet i formlen fungerer kun med tal.
- Hvis formlen returnerer #VÆRDI -fejl, skal du kontrollere, om de krøllede seler skal være til stede i formlen som vist i eksemplerne i artiklen.
- Negation (-) char ændrer værdier, TRUEs eller 1s til FALSEs eller 0s og FALSEs eller 0s til TRUEs eller 1s.
- Handlinger som er lig med ( = ), mindre end lig med ( <= ), bedre end ( > ) eller ikke er lig med () kan udføres inden for en anvendt formel, kun med tal.
Håber denne artikel om Sådan bruges IF -funktion i stedet for SUMPRODUCT- og SUMIFS -funktion i Excel er forklarende. Find flere artikler om Summing formler her. Hvis du kunne lide vores blogs, kan du dele den med dine fristarts 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 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 : Bliv hurtigere til din opgave. Disse 50 genveje får dig til at arbejde endnu hurtigere i Excel.
Sådan bruges than VLOOKUP -funktion 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 funktionen COUNTIF 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.
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.