Kombinert bruk av sumif (vlookup)
Sumif med VLOOKUP er en kombinasjon av to forskjellige betingede funksjoner, SUMIF brukes til å summere cellene basert på noen tilstand som tar argumenter av rekkevidde som har dataene og deretter kriteriene eller tilstanden og cellene til å summere, i stedet for kriteriene vi bruker VIS UT som kriteriene når det er stor mengde data tilgjengelig i flere kolonner.
SUMIF er en funksjon presentert i Excel fra 2007-versjonen for å summere de forskjellige verdiene som samsvarer med kriteriene. VLOOKUP er en av de beste formlene for å samle inn data fra andre tabeller. Når flere betingelser og kolonner er der, brukes sumif (vlookup) til å utføre flere beregninger på excel-arket. Ulempen med at SUMIF-funksjonen returnerer det eneste tallet, overvinnes ved å bruke VLOOKUP. VLOOKUP hjelper deg med å returnere alle slags data fra en tabell basert på de samsvarende kriteriene.
Forklaring
SUMIF-funksjon: Det er en trigonometri og matematikkfunksjon for å summere verdiene når den etablerte tilstanden er sann. Sumverdien oppnås basert på bare ett kriterium.
Når vi håndterer SUMIF-funksjonen i excel, brukes følgende formel
- Område: Det er rekkevidden til cellene som brukes til å vurdere de etablerte kriteriene
- Kriterier: Det er betingelsen å oppsummere verdiene. Det kan være en cellereferanse, et tall og en annen excel-funksjon. Når vi ønsker å kombinere SUMIF og VLOOKUP, vil vlookup-funksjonen legges inn i stedet for kriteriene
- Sumområde: Det er området av celler som er spesifisert for å summere de numeriske verdiene.
Nå er formelen endret til
Formel = SUM (Område, Vlookup (oppslagsverdi, tabellmatrise, kolonneindeksnummer, [rekkeviddeoppslag]), [sumområde])
- Oppslagsverdi: Den spesifiserer verdien som skal søkes i en tabell. Det kan være en referanse eller verdi.
- Tabellmatrise: Det er rekkevidden til tabellen som inneholder to eller flere enn to kolonner.
- Kolonneindeksnummer: Det er den relative indeksen til en kolonne som skal spesifiseres for å returnere nødvendige data fra en bestemt kolonne.
- [Områdeoppslag]: Det er enten 0 eller 1 for å spesifisere om den nøyaktige verdien skal returneres eller den omtrentlige verdien skal returneres. Men det er valgfritt for en bruker. 0 indikerer en nøyaktig samsvar og 1 indikerer en omtrentlig kamp.
Hvordan bruke SUMIF med VLOOKUP-funksjon?
Kombinert bruk av sumif (vlookup) er nyttig for å søke etter data basert på de enkelte kriteriene. Disse brukes på mange måter i Excel for å utføre beregningene ved å søke etter data. Disse brukes samlet i forretningsmiljøet for å utføre forskjellige oppgaver for å ta gode beslutninger. For å bruke disse funksjonene effektivt sammen,
Først skal SUMIF-funksjonen legges inn ved hjelp av de to metodene.
Første metode: Formelen må skrive fra tastaturet som vist på bildet nedenfor.
Andre metode: SUMIF-funksjonen kan settes inn fra 'Formel' -fanen som vist på bildet.
Etter at du har kommet inn i SUMIF-funksjonen, blir formelen for VLOOKUP skrevet inn i SUMIF-funksjonen ved å erstatte elementet 'Kriterier'. Alle parametrene til VLOOKUP inkludert oppslagsverdi, tabelloppsett, indeksnummer for kolonnen og rekkeviddeoppslag. Disse skal inngå i parentes for å unngå feil med formelen. Verdiområdet som skal summeres, er inkludert i sumområdet for SUMIF-funksjonen. Til slutt trykkte CTRL-, SHIFT- og ENTER-tastene sammen for å lette verdiene som en matrise.
Eksempler
Du kan laste ned denne SUMIF With VLOOKUP Excel Mal her - SUMIF With VLOOKUP Excel MalEksempel 1 - Bruk av sumif (vlookup) sammen for å bestemme noen verdi
Dette eksemplet viser hvordan du bruker sumif (vlookup) sammen for å finne summen av salget i samme måned i forskjellige år. Følgende data vurderes for dette eksemplet som vist på skjermbildet.
Oppslagstabellen som er vurdert er vist som nevnt nedenfor. Den inkluderte referanseverdier for månedene januar til desember.
Etter å ha lagt inn data i hovedtabellen og oppslagstabellen, brukes SUMIF-funksjonen til å bestemme det totale salget som genereres i forskjellige måneder i året. Her er Lookup-verdien som er vurdert måneden. Formelen som kombinerer sumif (vlookup) vises som,
Det totale salget i januar måned bestemmes som 17263.3 . Da vi endret utseende til en annen måned, genereres det respektive totale salget.
Eksempel 2 - Bestemme sum basert på samsvarende kriterier i forskjellige arbeidsark
I dette eksemplet er oppslagstabellen og hovedtabellen tatt i forskjellige ark i stedet for et enkelt ark. Dataene for oppslagstabellen vises i skjermbildet nedenfor.
Hovedtabelldataene vises i skjermbildet nedenfor.
For å bestemme det totale salget, blir navnet på selgeren tatt som oppslagsverdi, og ansattes ID-er brukes til referanseformål. Formelen legges inn som presentert nedenfor, og få endringer observeres i formelen sammenlignet med det første eksemplet.
I dette eksemplet nevnes bare Lookup_table i stedet for å velge en oppslagsmatrise. Ved å trykke på de tre tastene, inkludert CTRL, SHIFT og ENTER, produseres nøyaktige resultater.
Når navnet på selgeren endres til navnene som er nevnt i oppslaget, varieres salgssummen og gir et nytt resultat.
fordeler
Følgende er fordelene ved å bruke disse funksjonene.
- Vi kan enkelt trekke ut verdiene fra en annen tabell for å utføre beregninger.
- Bestemme summen av verdier som presenteres i et område som oppfyller kriteriene nevnt i forskjellige aspekter av en virksomhet.
Ting å huske
- Mens du bruker VLOOKUP-funksjonen, bør ikke kolonneindeksnummeret være lavere enn 1 for å unngå feil.
- Indekser skal gis til oppslagstabellkolonnene ved å indikere med tallene 1, 2, 3 og så videre.
- CTRL + SKIFT + ENTER i excel skal brukes i stedet for å angi nøkkel siden VLOOKUP er angitt som en matriseformel.
- Å definere to tabeller inkludert hoved- og oppslag er nødvendig for å trekke ut verdier og bestemme summen av matriseverdiene.
- SUMIF gir nøyaktige resultater for bare numeriske data, det fungerer ikke for en annen type data.