SUMIF Z VLOOKUP | Združite SUMIF s funkcijo VLOOKUP Excel

Kombinirana uporaba sumifa (vlookup)

Sumif z VLOOKUP je kombinacija dveh različnih pogojnih funkcij, SUMIF se uporablja za seštevanje celic na podlagi nekega pogoja, ki upošteva argumente obsega, v katerem so podatki, in nato merila ali pogoj in celice, ki jih seštejemo, namesto meril, ki jih uporabljamo VLOOKUP kot merilo, kadar je v več stolpcih na voljo veliko podatkov.

SUMIF je funkcija, predstavljena v Excelu iz različice 2007 za seštevanje različnih vrednosti, ki ustrezajo merilom. VLOOKUP je ena najboljših formul za zbiranje podatkov iz drugih tabel. Ko je na voljo več pogojev in stolpcev, se za izvedbo več izračunov na Excelovem listu uporabi sumif (vlookup). Pomanjkljivost funkcije SUMIF, ki vrne edino številko, je odpravljena z uporabo VLOOKUP. VLOOKUP pomaga vrniti kakršne koli podatke iz tabele na podlagi ujemajočih se meril.

Pojasnilo

Funkcija SUMIF: Funkcija trigonometrije in matematike sešteva vrednosti, če je vzpostavljeni pogoj resničen. Vsota vrednosti se dobi na podlagi samo enega merila.

Ko obravnavamo funkcijo SUMIF v Excelu, se uporablja naslednja formula

  • Območje: To je obseg celic, ki se uporabljajo za oceno določenih meril
  • Merila: pogoj je seštevanje vrednosti. To je lahko referenca celice, številka in druga excel funkcija. Ko želimo združiti SUMIF in VLOOKUP, bo namesto meril vpisana funkcija vlookup
  • Obseg vsote: To je obseg celic, določenih za seštevanje številskih vrednosti.

Zdaj je formula spremenjena v

Formula = SUMIF (Obseg, Vlookup (vrednost iskanja, matrika tabele, številka indeksa stolpca, [iskanje obsega]), [obseg vsote])

  • Iskalna vrednost: Določa vrednost, ki jo želite iskati v tabeli. To je lahko referenca ali vrednost.
  • Polje tabele: To je obseg tabele, ki vsebuje dva ali več kot dva stolpca.
  • Številka indeksa stolpca: Je relativni indeks stolpca, ki ga določite za vrnitev zahtevanih podatkov iz določenega stolpca.
  • [Iskanje obsega]: bodisi 0 bodisi 1 določite, ali naj se vrne natančna vrednost ali približno vrednost. Uporabniku pa ni obvezen. 0 označuje natančno ujemanje, 1 pa približno ujemanje.

Kako uporabiti SUMIF s funkcijo VLOOKUP?

Kombinirana uporaba sumifa (vlookup) je koristna pri iskanju podatkov na podlagi posameznih meril. V Excelu se na več načinov uporabljajo za izračun z iskanjem podatkov. Ti se v poslovnem okolju skupno uporabljajo za izvajanje različnih nalog za dobro odločanje. Za učinkovito skupno uporabo teh funkcij

Najprej vnesite funkcijo SUMIF z uporabo obeh metod.

Prva metoda: Formulo morate tipkati s tipkovnice, kot je prikazano na spodnji sliki.

Drugi način: funkcijo SUMIF lahko vstavite z zavihka "Formula", kot je prikazano na sliki.

Po vnosu funkcije SUMIF se v funkcijo SUMIF vnese formula za VLOOKUP z zamenjavo elementa »Merila«. Vsi parametri VLOOKUP-a, vključno z vrednostjo iskanja, matriko tabele, indeksno številko stolpca in iskanjem obsega. Te je treba priložiti v oklepaju, da se izognemo napakam s formulo. Obseg vrednosti, ki jih je treba sešteti, so vključeni v elemente obsega vsote funkcije SUMIF. Končno so tipke CTRL, SHIFT in ENTER stisnjene skupaj, da olajšajo vrednosti kot matriko.

Primeri

To predlogo SUMIF With VLOOKUP Excel lahko prenesete tukaj - SUMIF With VLOOKUP Excel Predloga

Primer # 1 - Uporaba sumifa (vlookup) skupaj za določitev neke vrednosti

Ta primer prikazuje, kako z uporabo sumifa (vlookup) skupaj najti vsoto prodaje v istem mesecu v različnih letih. Za ta primer so upoštevani naslednji podatki, kot je prikazano na posnetku zaslona.

Upoštevana pregledna tabela je prikazana, kot je navedeno spodaj. Vključeval je referenčne vrednosti za mesece od januarja do decembra.

Po vnosu podatkov v glavno tabelo in iskalno tabelo se s funkcijo SUMIF določi skupna prodaja, ustvarjena v različnih mesecih leta. Tu je upoštevana vrednost iskanja mesec. Formula, ki združuje sumif (vlookup), je prikazana kot,

Skupna prodaja v januarskem mesecu je določena kot 17263,3 . Ko smo vrednost videza spremenili v drug mesec, se ustvari ustrezna skupna prodaja.

2. primer - Določanje vsote na podlagi meril ujemanja v različnih delovnih listih

V tem primeru sta iskalna tabela in glavna tabela vzeti na različnih listih in ne na enem listu. Podatki iskalne tabele so prikazani na spodnjem posnetku zaslona.

Podatki glavne tabele so prikazani na spodnjem posnetku zaslona.

Za določitev skupne prodaje se kot prodajna vrednost vzame ime prodajalca, za referenčne namene pa se uporabijo ID-ji zaposlenih. Formula se vnese, kot je predstavljeno spodaj, in v primerjavi s prvim primerom je v formuli opaziti nekaj sprememb.

V tem primeru je namesto izbire iskalnega polja omenjena samo Lookup_table. S pritiskom na tri tipke, vključno s CTRL, SHIFT in ENTER, dobimo natančne rezultate.

Ko se ime prodajalca spremeni v imena, omenjena v iskanju, se vsota prodaje spreminja in daje nov rezultat.

Prednosti

Sledijo prednosti uporabe teh funkcij.

  • Vrednosti lahko enostavno izvlečemo iz druge tabele za izvajanje izračunov.
  • Določanje vsote vrednosti, predstavljene v obsegu, ki ustreza merilom, navedenim v različnih vidikih podjetja.

Stvari, ki si jih je treba zapomniti

  • Med uporabo funkcije VLOOKUP številka stolpca ne sme biti nižja od 1, da se izognemo napakam.
  • Indekse je treba podati stolpcem iskalne tabele, tako da jih označite s številkami 1, 2, 3 itd.
  • Namesto vnosa ključa je treba uporabiti CTRL + SHIFT + ENTER v Excelu, saj je VLOOKUP vnesen kot matrična formula.
  • Za izvlečenje vrednosti in določitev vsote vrednosti matrike je potrebno določiti dve tabeli, vključno z Main in Lookup.
  • SUMIF zagotavlja natančne rezultate samo za numerične podatke, za druge vrste podatkov ne bo deloval.