I den här artikeln kommer vi att lära oss hur man använder IF -funktionen istället för funktionen SUMPRODUCT och SUMIFS i Excel.
Scenario:
I enkla ord, när vi arbetar med en lång spridd datauppsättning, måste vi ibland hitta summan av tal med några kriterier över den. Till exempel att hitta summan av löner i en viss avdelning eller ha flera kriterier över datum, namn, avdelning eller kan till och med siffror data som löner under värde eller kvantitet över värde. För detta brukar du använda funktionen SUMPRODUCT eller SUMIFS. Men du skulle inte tro, du utför samma funktion med Excel grundläggande funktion IF -funktion.
Hur löser man problemet?
Du måste tänka på hur detta är möjligt för att utföra logiska operationer över tabellarrayer med IF -funktion. Om funktionen i Excel är mycket användbar, kommer den att ta dig igenom några svåra uppgifter i Excel eller andra kodningsspråk. IF -funktion testar villkor på array som motsvarar erforderliga värden och returnerar resultatet som array som motsvarar True -villkor som 1 och Falskt som 0.
För detta problem kommer vi att använda följande funktioner:
- SUM -funktion
- IF -funktion
Vi kommer att kräva dessa funktioner ovan och en grundläggande känsla av datadrift. logiska villkor för matriser kan tillämpas med logiska operatorer. Dessa logikoperatorer arbetar både med text och siffror. Nedan är den generiska formeln. { } lockiga hängslen är det magiska verktyget för att utföra matrisformler med IF -funktion.
Generisk formel:
| { = SUMMA (OM ((logical_1) * (logical_2) *… * (logical_n), sum_array)) } |
Obs: För lockiga hängslen ( { } ) Använda sig av Ctrl + Skift + Enter när du arbetar med matriser eller intervall i Excel. Detta genererar som standard Curly Braces på formeln. Försök INTE att koda lockiga tandställningstecken.
Logisk 1: testar villkor 1 på array 1
Logisk 2: testar tillstånd 2 på array 2 och så vidare
sum_array: array, operationssumma utförs
Exempel:
Allt detta kan vara förvirrande att förstå. Så, låt oss testa denna formel genom att köra den på exemplet som visas nedan. Här har vi uppgifter om levererade produkter till olika städer tillsammans med motsvarande kategorifält och kvantiteter. Här har vi data och vi måste hitta mängden kakor som skickas till Boston där mängden är större än 40.
Datatabell och kriterietabell visas i bilden ovan. För att förstå ändamålet använde vi namngivna intervall för de använda matriserna. Namngivna intervall listas nedan.
Här:
Stad definierad för array A2: A17.
Kategori definierad för array B2: A17.
Kvantitet definierad för array C2: C17.
Nu är du redo att få önskat resultat med hjälp av formeln nedan.
Använd formeln:
| { = SUMMA (OM ((Stad = "Boston") * (Kategori = "Cookies") * (Kvantitet> 40), Kvantitet)) } |
Förklaring :
- City = "Boston": kontrollerar värdena i stadsintervallet för att matcha med "Boston".
- Category = "Cookies": kontrollerar värdena i kategoriintervallet för att matcha med "Cookies".
- Kvantitet> 40: kontrollerar värdena i kvantitetsintervall till ma
- Kvantitet vara array där summan krävs.
- IF -funktionen kontrollerar alla kriterier och asterisk char (*) multiplicerar alla arrayresultat.
= SUMMA (OM ({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 returnerar IF -funktionen endast de kvantiteter som motsvarar 1: orna och resten ignoreras.
- SUM -funktionen returnerar SUMMEN.
Nu summeras mängden som motsvarar 1: or för att få resultatet.
Som du kan se returneras kvantitet 43 men det finns tre kakorder som levereras till "Boston" med kvantitet 38, 36 och 43. Vi behövde en summa kvantitet där kvantiteten var över 40. Så formeln returnerar endast 43. Använd nu andra kriterier för att få SUMMängd för stad: "Los Angeles" och kategori: "Bars" och antal vara mindre än 50.
Använd formeln
| { = SUMMA (OM ((stad = "Los Angeles") * (kategori = "barer") * (kvantitet <50), kvantitet)) } |
Som du kan se returnerar formeln värdena 86 som resultat. Vilket är summan av 2 order som uppfyller villkoren med kvantitet 44 & 42. Den här artikeln illustrerar hur du ersätter en kapslad IF -formel med en enda IF i en matrisformel. Detta kan användas för att minska komplexiteten i komplexa formler. Detta problem kan dock enkelt lösas med SUMIFS- eller SUMPRODUCT -funktionen.
Användning av SUMPRODUCT -funktionen:
SUMPRODUCT -funktionen returnerar summan av motsvarande värden i arrayen. Så vi får matriserna för att returnera 1s a True -satsvärdena och 0s till False -satsvärdena. Så sista summan kommer att motsvara där alla påståenden står Sanna.
Använd formeln:
| = SUMPRODUCT ( - (City = "Boston"), - (Category = "Cookies"), - (Quantity> 40), Quantity) |
-: operation som används för att konvertera alla TRUE till 1s och False till 0.
SUMPRODUCT -funktionen kontrollerar SUMMAN för kvantitet som returneras av SUM- och IF -funktionen som förklaras ovan.
På samma sätt för det andra exemplet är resultatet detsamma.
Som du kan se kan SUMPRODUCT -funktionen utföra samma uppgift.
Här är alla observationsanteckningar om användning av formeln.
Anmärkningar:
- Sum_array i formeln fungerar bara med siffror.
- Om formeln returnerar #VÄRDE -fel, kontrollera om de lockiga hängslen måste finnas i formeln som visas i exemplen i artikeln.
- Negation (-) char ändrar värden, TRUE eller 1s till FALSE eller 0s och FALSE eller 0s till TRUE eller 1s.
- Operationer som är lika med ( = ), mindre än lika med ( <= ), större än ( > ) eller inte är lika med () kan utföras inom en tillämpad formel, endast med siffror.
Hoppas den här artikeln om hur man använder IF -funktion istället för SUMPRODUCT och SUMIFS -funktionen i Excel är förklarande. Hitta fler artiklar om Summing formler här. Om du gillade våra bloggar, dela den med dina fristarts 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
Så här använder du SUMPRODUCT -funktionen i Excel: Returnerar SUMMEN efter multiplikation av värden i flera matriser i excel.
SUMMA om datumet är mellan : Returnerar summan av värden mellan givna datum eller period i excel.
Summa om datum är större än angivet datum: Returnerar summan av värden efter det angivna datumet eller perioden i excel.
2 sätt att summera per månad i Excel: Returnerar summan av värden inom en viss specifik månad i excel.
Hur man summerar flera kolumner med villkor: Returnerar SUMMAN av värden över flera kolumner med villkor i excel.
Populära artiklar:
50 Excel -genväg för att öka din produktivitet : Bli snabbare på din uppgift. Dessa 50 genvägar får dig att arbeta ännu snabbare med Excel.
Hur man använder than VLOOKUP -funktion 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.
Så här använder du funktionen COUNTIF 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.
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.