V tem članku se bomo naučili ZBIRATI ČE z uporabo iskalnih vrednosti v Excelu.
Preprosto povedano, pri delu z različnimi podatkovnimi tabelami moramo včasih izračunati vrednosti v vrednostih, ki temeljijo na prvi tabeli, v drugi tabeli. Pogoj je, da dobite vsoto vrednosti v prvi tabeli z uporabo vrednosti druge tabele
Za ta članek bomo potrebovali uporabo naslednjih funkcij:
- SUMPRODUCT funkcija
- Funkcija SUMIF
Funkcija SUMPRODUCT je matematična funkcija v Excelu. Deluje na več območjih. Ustrezne matrike pomnoži in jih nato sešteje.
Skupaj = (A1 * B1 * C1 * … + A2 * B2 * C2 * … + …)
Sintaksa:
= SUMPRODUCT (polje1, [polje2],…)
array: To je obseg ali seznam vrednosti.
Funkcija SUMIF v Excelu vrne SUM številk, ki so izpolnile določena merila. Obseg SUM in obseg meril sta lahko enaka ali različna.
Splošna formula Excel SUMIF:
= SUMIF (merilo_razpon, merila, obseg_vsote)
obseg_kriterijev: obseg, kjer se uporabi pogoj.
merila: pogoj za uporabo na merilu_razpon.
sum_range: obseg vsote na podlagi meril.
Zdaj bomo iz teh funkcij naredili formulo. Tukaj bomo dali podatke in potrebovali smo vsote rezultatov, kjer se vrednost ujema z vrednostjo v iskalni tabeli.
Splošna formula:
= SUMPRODUCT (SUMIF (rezultat, zapisi, vsote_številk))
rezultat : vrednosti, ki se ujemajo v iskalni tabeli
zapis : vrednosti v prvi tabeli
sum_nums : vrednosti za seštevanje glede na ujemajoče se vrednosti.
Preizkusimo to formulo tako, da jo zaženemo na primeru
Tukaj izračunavamo skupne točke nogometne ekipe z uporabo tabele točk ali tabele za iskanje.
Tu je rezultat (B8: B10) podan kot imenovano območje, točke (C8: C10) pa kot imenovano območje.
Zdaj bomo uporabili spodnjo formulo, da dobimo SUM točk za mesec 1 v celici F3
Uporabite formulo:
= PODROČJE (SUMIF (rezultat, B3: E3, točke))
Pojasnilo:
- Funkcija SUMIF ujema rezultat z vrednostmi v B3: E3.
- Nato se točke, ki ustrezajo rezultatu, vrnejo v funkcijo SUMPRODUCT.
- Funkcija SUMPRODUCT vrne funkciji SUM vrednosti, podanih v nizu.
Kot lahko vidite tukaj, je skupno število točk, ki jih je ekipa dosegla v prvem mesecu, 7.
3 (W) + 0 (L) + 1 (D) + 3 (W) = 7 točk
Kopirajte formulo v druge celice, izberite celice v prvi celici, kjer je formula že uporabljena, uporabite bližnjično tipko Ctrl + D ali uporabite možnost povleci celico navzdol v Excelu.
Kot lahko vidite na zgornjem posnetku, so točke, dosežene v štirih različnih mesecih.
Pridobite SUM vseh točk za 4 mesece.
To pomeni, da je ekipa v štirih mesecih skupaj dosegla 18 točk.
Alternativna metoda
Namesto funkcije SUMPRODUCT uporabite metodo SUM in matriko.
Uporabite formulo:
{= SUM (SUMIF (rezultat, B3: E3, točke))}
Zaviti oklepaji so matrična metoda, ki predlaga, da funkcija izračuna vse vrnjene vrednosti s funkcijo SUMIF.
Opomba: Ne simbole kodrastih naramnic uporabite ročno. Uporabi Ctrl + Shift + Enter za uporabo matrične metode v Excelu.
Kot lahko vidite iz zgornje formule, lahko z uporabo pogojne iskalne tabele pridobite skupno število točk.
Opombe:
- Formula deluje samo s številkami.
- Formula deluje le, če v iskalni tabeli ni dvojnikov
- Funkcija SUMPRODUCT šteje neštevilčne vrednosti kot 0.
- Funkcija SUMPRODUCT upošteva logično vrednost TRUE kot 1 in False kot 0.
- Polje argumentov mora biti enake dolžine, sicer je funkcija
Upam, da je ta članek o tem, kako vrniti SUM samo iz formul v Excelu, razložljiv. Več člankov o funkcijah SUMPRODUCT najdete tukaj. Prosimo, delite svoje vprašanje spodaj v polju za komentar. Mi vam bomo pomagali.
Kako uporabljati funkcijo SUMPRODUCT v Excelu: Vrne SUM po množenju vrednosti v več matrikah v Excelu.
SUM, če je datum med : Vrne SUM vrednosti med danimi datumi ali obdobjem v Excelu.
Vsota, če je datum večji od navedenega datuma: Vrne SUM vrednosti po danem datumu ali obdobju v Excelu.
2 načina seštevanja po mesecih v Excelu: Vrne SUM vrednosti v določenem mesecu v Excelu.
Kako sešteti več stolpcev s pogojem: Vrne SUM vrednosti v več stolpcih s pogojem v excelu
Priljubljeni članki
50 Bližnjica v Excelu za večjo produktivnost
Uredite spustni seznam
Absolutna referenca v Excelu
Če s pogojnim oblikovanjem
Če z nadomestnimi znaki
Vpogled po datumu
Pretvorite palce v stopala in palce v Excelu 2016
Pridružite se imenu in priimku v Excelu
Preštejte celice, ki ustrezajo bodisi A ali B