Seštevanje celic v Excelu po pogoju. Funkcija BDsum z enim numeričnim kriterijem

Dober večer

Nadaljeval bom z željo po opisu pestrosti funkcij v Excelu in naslednja je pri nas v obravnavi. To je še en predstavnik funkcij seštevanja, vendar s svojimi posebnimi pogoji. Funkcija BDSUMM v Excelu išče tudi številke v vaši tabeli glede na kriterije, ki jih določite, to je njegova glavna lastnost.

Roko na srce, lahko rečem, da je veliko izračunov in izračunov mogoče narediti brez njega z uporabo različnih ali SUMPRODUCT, če pa morate narediti zapleten izbor z znaki "nadomestni znak", potem morate zagotovo uporabiti junaka našega članka.

Najprej si poglejmo sintakso, ki uporablja:

=BDSUMM(vaš obseg baze podatkov; iskalno polje; iskalni pogoj), Kje

Pri delu z funkcija BDSUMM Upoštevati je treba več pogojev, na katere morate biti pozorni pri delu:

• Ko delate na celotnem stolpcu, napolnjenem s podatki, morate vsekakor vstaviti prazno vrstico pod naslove stolpcev v podanem obsegu meril;
• Obseg meril se ne sme prekrivati ​​s seznamom;
• Čeprav lahko podatke za oblikovanje obsega pogojev postavite kamor koli na list, jih kljub temu ne smete postaviti za seznamom, saj bodo podatki, ki jih dodamo na seznam, vstavljeni v prvo vrstico za seznamom in če tam so podatki v vrstici, potem dodajanje novih ne bo delovalo;
• Za pogoje lahko uporabite kateri koli obseg, ki vsebuje vsaj en naslov in vsaj eno od celic, ki se nahaja pod naslovom in vsebuje pogoj.

Torej, menim, da je teoretični del zaključen, zdaj nadaljujemo praktična uporaba Funkcije BDSUMM pri našem delu bomo za to upoštevali več primerov za izvedbo, naredil sem primere po načelu, vendar namesto zbiranja vrednosti po kriteriju bo seštevek:

Funkcija BDSUMM zen numerični kriterij

Torej, najprej razmislimo o preprostem primeru z enim numeričnim kriterijem, izberite stolpec »Donos« in navedite, da potrebujemo drevesa z donosom »>=10«. Da bi dobili rezultat, potrebujemo formulo te vrste (priporočam uporabo absolutnih referenc):

=BDSUMM($B$6:$G$12,E6,E2:E3),

kjer je $B$6:$G$12 obseg, v katerem bomo seštevali, E6 je stolpec, v katerem bomo seštevali, E2:E3 pa je obseg, v katerega smo vnesli kriterije za seštevanje. Kot rezultat je formula našla 3 položaje za skupno 34.

Če želite dobiti podoben rezultat, lahko uporabite tudi naslednje formule:

=SUMIF(E7:E12,">=10")

=SUMIF(E7:E12;E3)

Funkcija BDSUMM z enim besedilnim kriterijem

Zdaj pa poglejmo, kako se funkcija BDSUMM obnaša z besedilnimi kriteriji. Na splošno ostaja vse enako kot v prejšnjem primeru, razen tega, kako je naveden besedilni kriterij, ki je naveden samo v tej obliki: ="=s. Serovo. " in potem boste dobili rezultat, sicer formula ne bo mogla prepoznati vašega kriterija. Zdaj nadomestimo ta kriterij v formulo in dobimo:

=BDSUMM($B$6:$G$12,E6,C2:C3), kot vidimo, je prišlo le do sprememb v obsegu merila.

Za podoben rezultat boste potrebovali:

=SUMIF(C7:C12;"s.Serovo";E7:E12)

Seštevanje po dveh kriterijih v različnih stolpcih

Primer je zapleten zaradi uporabe dveh kriterijev, vendar ne bomo uporabili nič bistveno novega, navedli bomo besedilni kriterij »vas Serovo« in numerični kriterij »>=10«, pri čemer bomo zapustili seštevalno polje »Žetev«, dobite spremembo formule samo za zadnji argument, kot rezultat . Sedaj bo naša formula videti takole:

=BDSUMM($B$6:$G$12,E6,C2:E3), spet vidite le spremembe naslova obsega kriterijev.

Alternativno možnost lahko dobite z uporabo in tako:

=SUMIFS(E7:E12;C7:C12;C3;E7:E12;E3)

=SUMIFS(E7:E12;C7:C12;"Vas Serovo";E7:E12;">=10")

Seštevek enega od dveh pogojev v enem stolpcu

Razmislimo o drugi možnosti, kako se uporablja, zdaj pa ne bomo uporabili enega samega merila, temveč dvojnega, vendar za eno polje. Obe merili morata biti v različnih vrsticah. Bistvo formule je v tem, da gre dvakrat skozi isti obseg, pri čemer šteje vsako merilo posebej. Za ta primer bi formula izgledala takole:

=BDSUMM($B$6:$G$12,E6,C2:C4), tukaj ponovno spremenimo obseg merila, vendar ne v širino, ampak v višino.

Kot nadomestek lahko uporabite tudi vsoto funkcije SUMIF:

=SUMIF(C7:C12,C3,E7:E12)+SUMIF(C7:C12,C4,E7:E12).

Seštevek enega od dveh pogojev v dveh različnih stolpcih

Peti primer uporabe bo seštevek enega od dveh pogojev v dveh različnih stolpcih in ne pozabite postaviti vseh argumentov v nove vrstice, kar bo omogočilo njihovo seštevanje po vrsti. Za te namene potrebujemo formulo:

=BDSUMM($B$6:$G$12,D6,C2:D4), je princip oblikovanja formule ohranjen, razen obsega kriterijev, ki vključuje tri vrstice: naslov in dva kriterija.

Seštevanje po dveh besedilnih kriterijih v dveh stolpcih

IN v tem primeru dela, bomo obravnavali skoraj popolno analogijo prej obravnavanega primera, ko sta bila v dveh stolpcih dva kriterija, obstajala pa sta numerični in besedilni kriterij, tukaj pa bomo upoštevali seštevek po dveh besedilnih kriterijih in po dveh stolpcih. Uporabljamo kriterije "="=vas Ivanovo"" in "="=Češnja"", ki jih bomo navedli v obsegu kriterijev. Torej bo naša formula videti takole:

=BDSUMM($B$6:$G$12,D6,B2:C3).

Uporaba rezultata formule za pridobitev kriterijev izbire in seštevanja

V tem primeru funkcije BDSUMM uporabljam formulo za določitev merila za izbiro in seštevek, v tem primeru bomo ugotovili, katera drevesa nam prinašajo dohodek, za določitev pogojnega argumenta pa bomo vzeli povprečno vrednost za prodajo sadja z dreves in vse, kar je nad povprečjem, nas zanima. Za določitev povprečne vrednosti, ki bo postala naš kriterij »TRUE«, ustvarimo formulo:

=G7>POVPREČNO($G$7:$G$12), ne pozabite na določitev obsega, tako da, ko gre formula skozi vrednosti, te ne zdrsnejo navzdol, ampak mora vrednost G7 zdrseti čez celoten obseg, da ugotovi, ali je »FALSE« ali »TRUE«.

Prav tako je zelo pomembno, da se imena naslovov ne podvajajo in se razlikujejo, zato bom polje kriterijev imenoval »Povprečje«. In potem bo formula začela delovati, šla bo skozi celoten obseg $G$7:$G$12 za prisotnost povprečne vrednosti in ko bo prejela pozitiven rezultat »TRUE«, bo seštela. Delo bo opravila naslednja formula:

=BDSUMM($B$6:$G$12,G6,$G$2:$G$3)

In če vas zelo zanima alternativna rešitev težave, poskusite možnost s funkcijo SUMIF v tej obliki:

=SUMIF($G$7:$G$12,">"&AVERAGE($G$7:$G$12))

Funkcija BDSUMM temelji na treh kriterijih

V tem primeru bomo izračunali povprečje prodaje pridelanega sadja v dveh vaseh: »vas Ivanovo« in »vas Uyutnoye«. Glavno idejo izbire na podlagi meril sem že opisal, zato je ne bom ponavljal, rekel bom le, da bo šlo za kombinacijo prej obravnavanih meril. Da bi dobili rezultat, ga potrebujemo v tej obliki:

=BDSUMM($B$6:$G$12,G6,$C$2:$G$4)

Seštevanje glede na velike in male črke po besedilnem kriteriju

Kot sem že omenil, lahko išče ne samo z nadomestnimi znaki, ampak tudi z upoštevanjem velikih in malih črk, in zdaj je ravno to možnost. Najprej določimo pogoj za izbiro kriterija, če se ime vasi IVANOVO pojavi z velikimi črkami, potem izvedemo seštevek za določitev tega kriterija potrebujemo formulo:

=COINCIDENCE("vas IVANOVO";C7)

Zdaj pa lahko napišemo funkcijo, ki bo preverila obseg prisotnosti podanega kriterija in ob prejemu vrednosti »TRUE« izvedla seštevek. V primeru sem posebej navedel enkrat glede na pogoj in kot vidite je formula uspešno izbrala vsa naselja in našla pravega ter dobila rezultat “9”. V ta namen je bila uporabljena formula:

=BDSUMM($B$6:$G$12,D6,$C$2:$C$3)

No, mislim, da sem podrobno opisal podrobnosti, tako da bo malo vprašanj, bo pa veliko koristi. Če imate kakršna koli vprašanja, napišite komentarje, veselim se vaših všečkov in ocen. O drugih funkcijah lahko izveste na moji spletni strani.

Vso srečo vam pri vaših prizadevanjih!

»Za denar se seveda da kupiti ljubek pes, vendar ga noben denar ne more pripraviti do tega, da bi veselo mahal z repom.
"
D. Billings