Kako sešteti vrednosti v excelu. Selektivno seštevanje po dveh kriterijih

Oblikovanje problema

Imamo na primer takole prodajno tabelo:

Naloga : povzetek vseh naročil, ki jih je upravitelj Grigoriev izvedel za trgovino Kopeika.

Metoda 1. Funkcija SUMIF

Če bi naš problem imel samo en pogoj (vsa naročila od Grigoriev ali vsa naročila od Kopeyka), potem bi težavo rešili precej enostavno z uporabo vgrajene funkcije Excel SUMIF iz kategorije matematika. Lahko preberete, kako ga uporabljati. Toda v našem primeru obstajata dva pogoja, ne eden, zato ta metoda ni primerna ...

Metoda 2. Indikatorski stolpec

V našo tabelo dodamo še en stolpec, ki bo služil kot nekakšen indikator: če je bilo naročilo od Kopeyke in od Grigorieva, bo celica v tem stolpcu imela vrednost 1, sicer 0. Formula, ki jo je treba vnesti v tem stolpcu je zelo preprosto:

=(A2="Kopejka")*(B2="Grigorjev")

Logične enakosti v oklepajih ustvarijo vrednosti TRUE ali FALSE, ki so v Excelu enake kot 1 in 0. Torej, ker množimo te izraze, bo ena na koncu le ena, če sta oba pogoja resnična. Zdaj je vse, kar ostane, pomnožiti prodajne vrednosti z vrednostmi nastalega stolpca in sešteti dobljene zneske:

Metoda 3. Magična matrična formula

Če še niste naleteli na tako čudovito funkcijo Excela, kot so matrične formule, vam svetujem, da najprej preberete veliko dobrih stvari o njih. No, v našem primeru je problem rešen z eno formulo:

SUM((A2:A26="Kopejka")*(B2:B26="Grigorjev")*D2:D26)

Ko vnesete to formulo, morate pritisniti ne Enter, kot običajno, ampak Ctrl + Shift + Enter - potem bo Excel to interpretiral kotformula poljain bo sam dodal zavite oklepaje. Ni vam treba vnašati oklepajev s tipkovnice. Preprosto je ugotoviti, da je to metodo (tako kot prejšnjo) mogoče enostavno povečati s tri, štiri itd. pogoji brez kakršnih koli omejitev.

Metoda 4. Funkcija baze podatkov BDSUMM

V kategoriji Baze podatkov lahko najdete funkcijo DSUM , ki nam lahko pomaga tudi pri reševanju naše težave:

Niansa je v tem, da morate za delovanje te funkcije ustvariti obseg celic na listu, ki vsebuje izbirne pogoje, in nato določiti ta obseg funkcije kot argument:

BDSUMM(A1:D26;D1;F1:G2)

Metoda 5. Čarovnik za delno vsoto

To je ime dodatka Excel, ki vam pomaga ustvarjati kompleksne formule za večkriterijsko seštevanje. Ta brezplačen dodatek lahko povežete prek menijaOrodja – Dodatki – Čarovnik za vsoto(Orodja – Dodatki – Čarovnik za pogojno vsoto). Po tem v meniju Storitev mora se pojaviti ukaz Delni znesek , ki zažene čarovnika za povzemanje:

Na prvi stopnici Čarovniki vsotes podatki je treba določiti obseg, tj. izberite našo celotno mizo. V drugem koraku morate izbrati stolpec za seštevanje in ustvariti pogoje za izbiro vrednosti, pri čemer vsak pogoj dodate na seznam z gumbomDodajte pogoj:

In končno, v 3. in 4. koraku označimo celico, v kateri naj bo prikazan rezultat. In na koncu dobimo naslednje:

Preprosto je videti, da smo v 3. metodi uporabili nekaj podobnega tej matrični formuli. Samo tukaj se vam sploh ni treba dotikati tipkovnice - naj živi lenoba - motor napredka!

Ali si vedel, Kaj je miselni eksperiment, gedanken eksperiment?
To je neobstoječa praksa, nezemeljska izkušnja, domišljija nečesa, kar dejansko ne obstaja. Miselni eksperimenti so kot budne sanje. Rojevajo pošasti. Za razliko od fizikalni poskus, ki je eksperimentalni preizkus hipotez, »miselni eksperiment« čudežno nadomešča eksperimentalno testiranje z želenimi zaključki, ki niso bili preizkušeni v praksi, manipulira z logičnimi konstrukcijami, ki dejansko kršijo samo logiko z uporabo nedokazanih premis kot dokazanih, to je s substitucijo . Tako je glavni cilj prijaviteljev "miselnih eksperimentov" prevarati poslušalca ali bralca z zamenjavo resničnega fizičnega eksperimenta z njegovo "lutko" - fiktivnim sklepanjem pod iskreno brez samega fizičnega testa.
Polnjenje fizike z imaginarnimi, »miselnimi eksperimenti« je pripeljalo do nastanka absurdne, nadrealistične, zmedene slike sveta. Pravi raziskovalec mora takšne »zavitke bonbonov« ločiti od resničnih vrednosti.

Relativisti in pozitivisti trdijo, da so »miselni eksperimenti« zelo uporabno orodje za preverjanje doslednosti teorij (ki nastajajo tudi v naših glavah). Pri tem zavajajo ljudi, saj morebitno preverjanje lahko izvede samo vir, neodvisen od predmeta preverjanja. Prijavitelj hipoteze sam ne more biti preizkus lastne izjave, saj je razlog za to trditev sama odsotnost protislovij v izjavi, ki jih vidi prijavitelj.

To vidimo na primeru SRT in GTR, ki sta se spremenila v edinstven tip religije, ki vlada znanosti in javno mnenje. Nobeno dejstvo, ki jim nasprotuje, ne more premagati Einsteinove formule: »Če dejstvo ne ustreza teoriji, spremeni dejstvo« (V drugi različici »Ali dejstvo ne ustreza teoriji? - Toliko slabše za dejstvo «).

Največ, kar lahko zahteva "miselni eksperiment", je le notranja konsistentnost hipoteze v okviru prijaviteljeve lastne, pogosto nikakor neresnične logike. To ne preverja skladnosti s prakso. Resnično preverjanje lahko poteka samo v dejanskem fizikalnem poskusu.

Eksperiment je eksperiment, ker ni izpopolnjevanje misli, ampak preizkus misli. Misel, ki je samokonsistentna, se ne more preveriti. To je dokazal Kurt Gödel.

NOVICE FORUMA Vitezi teorije etra

30.10.2017 - 06:17: -> - Karim_Khaidarov. 19.10.2017 - 04:24: -> - Karim_Khaidarov. 10/11/2017 - 05:10: -> - Karim_Khaidarov. 05.10.2017 - 11:03:

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 z uporabo dveh kriterijev, vendar ne bomo uporabili ničesar bistveno novega, navedli bomo besedilni kriterij "s". številčno merilo»>=10«, zapustimo polje za seštevek »Donos«, bomo kot rezultat dobili spremembo formule samo za zadnji argument. Zdaj bo naša formula videti takole:

=BDSUMM($B$6:$G$12,E6,C2:E3), ponovno vidite samo 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 ohranjen princip oblikovanja formule, 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 prisrčen pes, vendar ga noben denar ne more pripraviti do tega, da bi veselo mahal z repom.
"
D. Billings

funkcija SUMIF uporablja se, ko je treba sešteti vrednosti obsega, ki izpolnjujejo določen pogoj. Na primer, predpostavimo, da želite v stolpcu s številkami sešteti samo vrednosti, večje od 5. Če želite to narediti, lahko uporabite naslednjo formulo: =SUMIF(B2:B25,">5")

Ta video je del vadba Seštevanje števil v Excelu 2013.

Nasvet:

Sintaksa

SUMIF(obseg, pogoj, [vsota_obseg])

Argumenti funkcije SUMIF so opisani spodaj.

Razpon. Potreben argument. Obseg celic, za katere se oceni ustreznost. Celice v vsakem obsegu morajo vsebovati številke, imena, nize ali sklice na številke. Prazne celice in celice, ki vsebujejo besedilne vrednosti, so preskočene. Izbrani obseg lahko vsebuje standardne datume format Excel(glej primere spodaj).

Pogoj. Potreben argument. Pogoj v obliki števila, izraza, sklica na celico, besedila ali funkcije, ki določa, katere celice je treba sešteti. Pogoj je lahko na primer predstavljen kot: 32, ">32", B5, "32", "jabolka" ali DANES().

Pomembno: Priloženi morajo biti vsi besedilni pogoji in pogoji z logičnimi in matematičnimi predznaki dvojni narekovaji (" ). Če je pogoj število, vam ni treba uporabiti narekovajev.

obseg_vsote. Izbirni argument. Celice, katerih vrednosti se seštejejo, če se razlikujejo od celic, navedenih kot obseg . Če argument obseg_vsote izpuščeno, Excel sešteje celice, navedene v argumentu obseg (iste celice, za katere velja pogoj).

V argumentu stanje Uporabite lahko nadomestne znake: vprašaj ( ? ) in zvezdica ( * ). Vprašaj ustreza kateremu koli posameznemu znaku, zvezdica pa kateremu koli zaporedju znakov. Če želite neposredno najti vprašaj (ali zvezdico), morate pred njim postaviti znak tilde ( ~ ).

Opombe

Funkcija SUMIF vrne napačne rezultate, če je uporabljena za ujemanje nizov, daljših od 255 znakov, ali ko je uporabljena za niz #VALUE!.

Prepir obseg_vsote morda ni enake velikosti kot argument obseg . Pri določanju dejanskih celic, ki jih je treba sešteti, se kot začetna celica uporabi zgornja leva celica argumenta obseg_vsote , nato pa se celice dela obsega, ki ustreza velikosti argumenta, seštejejo obseg . primer:

Vendar, če argumenti obseg in obseg_vsote Funkcije SUMIF vsebujejo drugačna številka celic, lahko ponovni izračun delovnega lista traja dlje, kot je bilo pričakovano.

Primeri

Primer 1

Kopirajte vzorčne podatke iz naslednje tabele in jih prilepite v celico A1 nove Excel list. Če želite prikazati rezultate formul, jih izberite in pritisnite F2, nato pritisnite Enter. Po potrebi spremenite širino stolpcev, da vidite vse podatke.

Primer 2

Kopirajte vzorčne podatke iz spodnje tabele in jih prilepite v celico A1 novega Excelovega delovnega lista. Če želite prikazati rezultate formul, jih izberite in pritisnite F2, nato pritisnite Enter. Poleg tega lahko prilagodite širino stolpcev glede na podatke, ki jih vsebujejo.