I denne artikkelen lærer vi hvordan du bruker IF -funksjonen i stedet for SUMPRODUCT- og SUMIFS -funksjonen i Excel.
Scenario:
I enkle ord, når vi arbeider med et langt spredt datasett, må vi noen ganger finne summen av tall med noen kriterier over den. For eksempel å finne summen av lønninger i en bestemt avdeling eller ha flere kriterier over dato, navn, avdeling eller kan til og med nummerere data som lønn under verdi eller mengde over verdi. For dette bruker du vanligvis funksjonen SUMPRODUCT eller SUMIFS. Men du ville ikke tro, du utfører den samme funksjonen med Excel grunnfunksjon IF -funksjon.
Hvordan løse problemet?
Du må tenke på hvordan dette er mulig, for å utføre logiske operasjoner over bordarrayer ved hjelp av IF -funksjon. HVIS funksjonen i Excel er veldig nyttig, vil den komme deg gjennom noen vanskelige oppgaver i Excel eller andre kodingsspråk. IF -funksjon tester betingelser på array som tilsvarer nødvendige verdier og returnerer resultatet som array som samsvarer med True conditions som 1 og False som 0.
For dette problemet bruker vi følgende funksjoner:
- SUM -funksjon
- IF -funksjon
Vi vil kreve disse funksjonene ovenfor og en grunnleggende følelse av datadrift. logiske forhold på matriser kan brukes ved hjelp av logiske operatorer. Disse logikkoperatørene jobber både med tekst og tall. Nedenfor er den generiske formelen. { } curly braces er det magiske verktøyet for å utføre matriseformler med IF -funksjon.
Generisk formel:
| { = SUMMER (HVIS ((logisk_1) * (logisk_2) * … * (logisk_n), sum_array)) } |
Merk: For krøllete seler ( { } ) Bruk Ctrl + Shift + Enter når du arbeider med matriser eller områder i Excel. Dette vil generere Curly Braces på formelen som standard. IKKE prøv å hardt kode krøllete klammeparenteser.
Logisk 1: tester tilstand 1 på matrise 1
Logisk 2: tester tilstand 2 på matrise 2 og så videre
sum_array: array, operasjonssummen utføres
Eksempel:
Alt dette kan være forvirrende å forstå. Så la oss teste denne formelen ved å kjøre den på eksemplet nedenfor. Her har vi data om leverte produkter til forskjellige byer sammen med tilsvarende kategorifelt og mengder. Her har vi dataene, og vi må finne mengden informasjonskapsler sendt til Boston hvor mengden er større enn 40.
Datatabell og kriterietabell er vist i bildet ovenfor. For å forstå formål brukte vi navngitte områder for de brukte matrisene. Navngitte områder er oppført nedenfor.
Her:
By definert for matrise A2: A17.
Kategori definert for matrise B2: A17.
Mengde definert for matrise C2: C17.
Nå er du klar til å få ønsket resultat ved å bruke formelen nedenfor.
Bruk formelen:
| { = SUMME (HVIS ((By = "Boston") * (Kategori = "Informasjonskapsler") * (Antall> 40), Antall)) } |
Forklaring:
- City = "Boston": sjekker at verdiene i byområdet samsvarer med "Boston".
- Category = "Cookies": sjekker at verdiene i kategoriområdet samsvarer med "Cookies".
- Antall> 40: sjekker verdiene i Mengdeområde til ma
- Antall være matrise der sum kreves.
- IF -funksjonen sjekker alle kriteriene og stjernen (*) multipliserer alle matriseresultatene.
= SUMME (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}))
- Nå returnerer IF -funksjonen bare mengdene som tilsvarer 1 -tallet, og resten ignoreres.
- SUM -funksjonen returnerer SUMMEN.
Nå summerer mengden som tilsvarer 1’er bare for å få resultatet.
Som du kan se blir kvantitet 43 returnert, men det er tre informasjonskapselordrer levert til "Boston" med mengde 38, 36 og 43. Vi trengte en sum mengde der mengden var over 40. Så formelen returnerer bare 43. Bruk nå andre kriterier for å få SUM Mengde for by: "Los Angeles" og kategori: "Barer" og Antall være mindre enn 50.
Bruk formelen
| { = SUMME (HVIS ((By = "Los Angeles") * (Kategori = "Barer") * (Antall <50), Antall)) } |
Som du kan se, returnerer formelen verdiene 86 som resultat. Som er summen av 2 bestillinger som tilfredsstiller betingelsene med mengde 44 og 42. Denne artikkelen illustrerer hvordan du erstatter en nestet IF -formel med en enkelt IF i en matriseformel. Dette kan brukes til å redusere kompleksiteten i komplekse formler. Imidlertid kan dette spesielle problemet enkelt løses med SUMIFS eller SUMPRODUCT -funksjonen.
Bruk av SUMPRODUCT -funksjonen:
SUMPRODUCT -funksjonen returnerer summen av tilsvarende verdier i matrisen. Så vi vil få matrisene til å returnere 1s a True -setningsverdiene og 0s til False -setningsverdiene. Så siste sum vil være korresponderende der alle utsagn står Sann.
Bruk formelen:
| = SUMPRODUCT ( - (City = "Boston"), - (Category = "Cookies"), - (Mengde> 40), Mengde) |
-: operasjon som brukes til å konvertere alle TRUEs til 1s og False til 0.
SUMPRODUCT -funksjonen sjekker SUM av mengden som returneres av SUM- og IF -funksjonen forklart ovenfor.
Tilsvarende for det andre eksemplet står resultatet det samme.
Som du kan se kan SUMPRODUCT -funksjonen utføre den samme oppgaven.
Her er alle observasjonsnotatene om bruk av formelen.
Merknader:
- Sum_arrayen i formelen fungerer bare med tall.
- Hvis formelen returnerer #VALUE -feil, må du kontrollere om de krøllete bukseselene er tilstede i formelen som vist i eksemplene i artikkelen.
- Negasjon (-) char endrer verdier, TRUEs eller 1s til FALSEs eller 0s og FALSEs eller 0s til TRUEs eller 1s.
- Operasjoner som er lik ( = ), mindre enn lik ( <= ), større enn ( > ) eller ikke er lik () kan utføres innenfor en anvendt formel, bare med tall.
Håper denne artikkelen om Hvordan bruke IF -funksjonen i stedet for SUMPRODUCT og SUMIFS -funksjonen i Excel er forklarende. Finn flere artikler om Summing -formler her. Hvis du likte bloggene våre, del den med fristarts på Facebook. Og du kan også følge oss på Twitter og Facebook. Vi vil gjerne høre fra deg, gi oss beskjed om hvordan vi kan forbedre, utfylle eller innovere arbeidet vårt og gjøre det bedre for deg. Skriv til oss på e -post
Slik bruker du SUMPRODUCT -funksjonen i Excel: Returnerer SUMMEN etter multiplikasjon av verdier i flere matriser i excel.
SUM hvis datoen er mellom : Returnerer SUMMEN av verdier mellom gitte datoer eller periode i excel.
Sum hvis datoen er større enn gitt dato: Returnerer SUMMEN av verdier etter gitt dato eller periode i excel.
2 måter å summere etter måned i Excel: Returnerer SUMMEN av verdier i en gitt spesifikk måned i excel.
Slik summerer du flere kolonner med betingelse: Returnerer SUM av verdier på tvers av flere kolonner som har betingelse i excel.
Populære artikler:
50 Excel -snarvei for å øke produktiviteten : Bli raskere på oppgaven din. Disse 50 snarveiene får deg til å jobbe enda raskere med Excel.
Slik bruker du tVLOOKUP -funksjonen i Excel : Dette er en av de mest brukte og populære funksjonene i excel som brukes til å slå opp verdi fra forskjellige områder og ark.
Slik bruker du COUNTIF -funksjonen i Excel : Tell verdier med betingelser ved hjelp av denne fantastiske funksjonen. Du trenger ikke å filtrere dataene dine for å telle spesifikke verdier. Countif -funksjonen er avgjørende for å forberede dashbordet.
Slik bruker du SUMIF -funksjonen i Excel : Dette er en annen viktig funksjon i instrumentbordet. Dette hjelper deg med å oppsummere verdier på spesifikke forhold.