Sažimanje određenih stanica u excelu. Selektivno zbrajanje po dva kriterija

Formulacija problema

Imamo tablicu prodaje, na primjer, sljedećeg oblika:

Zadatak : zbroj svih narudžbi koje je upravitelj Grigoriev implementirao za trgovinu "Kopeyka".

Metoda 1. SUMIF funkcija

Da je naš zadatak imao samo jedan uvjet (sve narudžbe od Grigorieva ili sve narudžbe do "Kopeyke"), tada bi se zadatak riješio vrlo jednostavno pomoću ugrađene funkcije ExcelSUMIF pregledavanje matematičke kategorije. Možete pročitati kako ga koristiti. Ali u našem slučaju postoje dva uvjeta, a ne jedan, tako da ova metoda nije prikladna ...

Metoda 2. Stupac-indikator

Dodajmo u našu tablicu još jedan stupac koji će poslužiti kao svojevrsni pokazatelj: ako je redoslijed bio u "Kopeyka" i od Grigorieva, tada će u ćeliji ovog stupca biti vrijednost 1 inače 0. Formula koja mora se unijeti u ovaj stupac vrlo je jednostavno:

\u003d (A2 \u003d "Kopeyka") * (B2 \u003d "Grigoriev")

Logičke jednakosti u zagradama daju TRUE ili FALSE, što je za Excel jednako 1 i 0. Dakle, budući da množimo ove izraze, na kraju ćemo dobiti jedan samo ako su oba uvjeta istinita. Sada se trošak prodaje treba pomnožiti s vrijednostima dobivenog stupca i zbrojiti dobivene iznose:

Metoda 3. Formula čarobnog niza

Ako prije niste naišli na tako divnu Excel značajku kao formule polja, savjetujem vam da prethodno pročitate puno dobrih stvari o njima.... Pa, u našem slučaju problem je riješen jednom formulom:

SUM ((A2: A26 \u003d "Penny") * (B2: B26 \u003d "Grigoriev") * D2: D26)

Nakon unosa ove formule, morate pritisnuti ne Enter, kao i obično, većCtrl + Shift + Enter - tada će ga Excel shvatiti kaoformula niza i sama ću dodati kovrčave zagrade. Ne morate unositi zagrade s tipkovnice. Lako je shvatiti da je ova metoda (poput prethodne) lako skalabilna za tri, četiri itd. uvjetima bez ikakvih ograničenja.

Metoda 4. Funkcija baze podataka BDSUMM

U kategoriji Baze podataka možete pronaći funkcijuBDSUM (DSUM) , koji nam također mogu pomoći u rješavanju našeg problema:

Nijansa je da za rad ove funkcije trebate stvoriti raspon ćelija na listu koji sadrže uvjete odabira, a zatim odrediti ovaj raspon funkcije kao argument:

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

Metoda 5. Čarobnjak za djelomičnu sumu

Ovo je naziv dodataka za Excel koji vam pomaže u stvaranju složene formule za višekriterijsko zbrajanje. Ovaj besplatni dodatak možete povezati putem izbornikaUsluga - dodaci - čarobnjak za zbrajanje(Alati - Dodaci - Čarobnjak za uvjetni zbroj)... Nakon toga u izbornikuServis trebala bi se pojaviti naredbaDjelomični iznos za pokretanje Summaranja čarobnjaka:

U prvom koraku Čarobnjaci za zbrajanje morate navesti raspon s podacima, tj. odaberite cijelu našu tablicu. U drugom koraku trebate odabrati stupac za zbrajanje i oblikovati uvjete za odabir vrijednosti, dodajući svaki uvjet na popis gumbomDodajte uvjet:

I na kraju, u 3. i 4. koraku naznačujemo ćeliju u kojoj želite prikazati rezultat. I na kraju imamo sljedeće:

Lako je vidjeti da smo koristili nešto slično ovoj formuli niza u Metodi 3. Samo ovdje uopće ne možete dodirivati \u200b\u200btipkovnicu - živjela lijenost - motor napretka!

Dali si znao, što je misaoni eksperiment, gedankenski eksperiment?
Ovo je nepostojeća praksa, onostrano iskustvo, mašta o onome što nije u stvarnosti. Misaoni eksperimenti su poput budnih snova. Rađaju čudovišta. Za razliku od fizičkog eksperimenta, koji je eksperimentalni test hipoteza, "misaoni eksperiment" lukavo zamjenjuje eksperimentalni test željenim, neprovjerenim u praksi zaključcima, manipulirajući logičkim konstrukcijama koje zapravo krše samu logiku koristeći nedokazane premise kao dokazane, tj. supstitucijom. Stoga je glavni zadatak podnositelja zahtjeva za "misaone pokuse" zavarati slušatelja ili čitatelja zamjenom stvarnog fizičkog eksperimenta njegovom "lutkom" - fiktivnim rezonovanjem na uvjetnom otpustu bez same fizičke provjere.
Ispunjavajući fiziku zamišljenim, "misaonim eksperimentima" doveli su do pojave apsurdne nadrealne, zbunjene i zbunjene slike svijeta. Pravi istraživač mora razlikovati takve "omote" od stvarnih vrijednosti.

Relativisti i pozitivisti tvrde da je "misaoni eksperiment" vrlo koristan alat za testiranje teorija (koje također nastaju u našem umu) na dosljednost. U tome obmanjuju ljude, jer bilo koju provjeru može provesti samo izvor neovisan o objektu provjere. Sam podnositelj zahtjeva za hipotezu ne može biti test vlastite izjave, jer je razlog same te izjave nepostojanje proturječnosti u izjavi vidljivoj podnositelju zahtjeva.

To vidimo na primjeru SRT-a i BRT-a, koji su se pretvorili u neku vrstu religije koja kontrolira znanost i javno mišljenje... Nijedna količina činjenica koja im proturječi ne može nadvladati Einsteinovu formulu: "Ako činjenica ne odgovara teoriji, promijenite je" (U drugoj verziji "- Činjenica ne odgovara teoriji? - Utoliko gore po činjenicu ").

Maksimum koji "misaoni eksperiment" može zahtijevati samo je unutarnja dosljednost hipoteze u okviru vlastite, često nipošto istinite, logike. Ovo ne provjerava prikladnost prakse. Pravi se test može provesti samo u valjanom fizičkom eksperimentu.

Eksperiment je također eksperiment, da on nije usavršavanje misli, već test misli. Misao koja je sama u sebi dosljedna ne može se provjeriti. To dokazuje Kurt Gödel.

VIJESTI FORUMA Vitezovi etera

30.10.2017 - 06:17: -\u003e - Karim_Khaidarov. 19.10.2017 - 04:24: -\u003e - Karim_Khaidarov. 11.10.2017 - 05:10: -\u003e - Karim_Khaidarov. 05.10.2017 - 11:03:

Funkcija SUMIF koristi se kada je potrebno zbrojiti vrijednosti raspona koje zadovoljavaju navedeni uvjet. Na primjer, pretpostavimo da u stupac s brojevima želite zbrajati samo vrijednosti veće od 5. Da biste to učinili, možete koristiti sljedeću formulu: \u003d SUMIF (B2: B25, "\u003e 5")

Ovaj videozapis je dio tečaj Dodajte brojeve u programu Excel 2013.

Savjet:

Sintaksa

SUMIF (raspon, stanje, [raspon_suma])

Argumenti funkcije SUMIF opisano u nastavku.

Domet ... Potreban argument. Raspon stanica za procjenu sukladnosti. Ćelije u svakom rasponu moraju sadržavati brojeve, imena, nizove ili brojevne reference. Prazne stanice i ćelije koje sadrže tekstne vrijednosti preskaču se. Odabrani raspon može sadržavati datume u standardnom Excel formatu (pogledajte primjere u nastavku).

Stanje ... Potreban argument. Uvjet u obliku broja, izraza, reference na ćeliju, teksta ili funkcije koji određuje koje ćelije treba zbrojiti. Na primjer, uvjet može biti predstavljen kao: 32, "\u003e 32", B5, "32", "apple" ili DANAS ().

Važno: Svi uvjeti i uvjeti teksta s logičkim i matematičkim znakovima moraju biti zatvoreni u dvostruke navodnike ( " ). Ako je uvjet broj, ne morate koristiti navodnike.

Zbroj_opsega ... Fakultativni argument. Stanice iz kojih se dodaju vrijednosti ako se razlikuju od ćelija navedenih kao domet ... Ako argument zbroj_opsega izostavljeno, Excel sumira ćelije navedene u argumentu domet (iste ćelije na koje se uvjet odnosi).

U raspravi stanje mogu se koristiti zamjenski znakovi: upitnik ( ? ) i zvjezdicu ( * ). Znak pitanja odgovara bilo kojem pojedinom znaku, a zvjezdica odgovara bilo kojem slijedu znakova. Ako želite izravno pronaći upitnik (ili zvjezdicu), morate ispred njega staviti tildu ( ~ ).

Bilješke

SUMIF vraća netočne rezultate kada se koristi za podudaranje nizova duljih od 255 znakova ili kada se primijeni na niz #Vrijednost!

Argument zbroj_opsega možda neće biti iste veličine kao argument domet ... Gornja lijeva ćelija argumenta koristi se kao početna ćelija pri određivanju stvarnih stanica koje se dodaju zbroj_opsega , a zatim se zbrajaju ćelije dijela raspona koji odgovara veličini argumenta domet ... Primjer:

Međutim, ako argumenti domet i zbroj_opsega Budući da funkcije SUMIF sadrže različit broj ćelija, ponovni izračun lista može potrajati dulje nego što se očekivalo.

Primjeri

Primjer 1

Kopirajte uzorke podataka iz sljedeće tablice i zalijepite ih u ćeliju A1 nove excel radni list... Da biste prikazali rezultate formula, odaberite ih i pritisnite F2, a zatim pritisnite Enter. Promijenite širinu stupaca prema potrebi da biste vidjeli sve podatke.

Primjer 2

Kopirajte uzorke podataka iz donje tablice i zalijepite ih u ćeliju A1 novog Excel lista. Da biste prikazali rezultate formula, odaberite ih i pritisnite F2, a zatim pritisnite Enter. Osim toga, možete prilagoditi širinu stupaca prema podacima koje sadrže.

Dobar dan!

Nastavit ću s nastojanjem da opišem raznolikost funkcija u Excelu i sljedeću za naše razmatranje. Ovo je još jedan predstavnik funkcija zbrajanja, ali sa svojim specifičnim uvjetima. BDSUMM funkcija u Excelu traži brojeve u vašoj tablici prema kriterijima koje ste definirali, ovo je njezino glavno svojstvo.

Iskreno govoreći, mogu reći da se puno proračuna i izračuna može izvršiti bez njega pomoću raznih ili SUMPRODUCT, ali ako trebate napraviti složeni odabir pomoću zamjenskih znakova, tada svakako trebate upotrijebiti junaka našeg članka.

Prvo, pogledajmo sintaksu koja koristi:

\u003d BDSUMM (raspon vaše baze podataka; polje za pretraživanje; pojam za pretraživanje)gdje

Pri radu s funkcija BDSUMM vrijedi istaknuti nekoliko uvjeta na koje biste trebali obratiti pažnju pri radu:

• Kada radite na cijelom stupcu ispunjenom podacima, obavezno umetnite prazan redak ispod naslova stupaca u navedenom rasponu kriterija;
• Raspon kriterija ne smije se preklapati s popisom;
• Iako podatke možete smjestiti tako da tvore raspon uvjeta bilo gdje na listu, ne biste ih trebali stavljati nakon popisa, jer će se podaci koje dodamo na popis umetnuti u prvi redak nakon popisa i ako postoji podatke u redu, a dodavanje novih neće raditi;
• Možete koristiti bilo koji raspon za uvjete koji sadrže barem jedan naslov i barem jednu ćeliju koja se nalazi ispod naslova i sadrži uvjet.

Dakle, teoretski dio, smatram dovršenim, prijeđimo sada na praktičnu primjenu bDSUMM funkcije u našem radu, za ovo ćemo razmotriti nekoliko primjera za izvršenje, primjere sam radio prema principu, ali umjesto prikupljanja vrijednosti prema kriteriju, slijedi zbrajanje:

BDSUMM funkcija sa jedan brojčani kriterij

Dakle, za početak, pogledajmo jednostavan primjer s jednim numeričkim kriterijem, za to odabiremo stupac "Berba" i naznačujemo da su nam potrebna stabla s prinosom "\u003e \u003d 10". Da bismo dobili rezultat, potrebna nam je formula ove vrste (savjetujem vam da koristite apsolutne reference):

\u003d BDSUMM ($ 6 $: $ 12 $; E6; E2: E3),

gdje je, $ B $ 6: $ G $ 12 raspon u kojem ćemo sažeti, E6 je stupac u kojem ćemo sažeti, a E2: E3 je raspon u koji smo unijeli kriterije za zbrajanje. Kao rezultat, formula je pronašla 3 mjesta za ukupno 34.

Da biste dobili sličan rezultat, možete koristiti i sljedeće formule:

\u003d SUMIF (E7: E12, "\u003e \u003d 10")

\u003d SUMIF (E7: E12, E3)

BDSUMM funkcija s jednim tekstualnim kriterijem

Sada pogledajmo kako se funkcija BDSUMM ponaša s tekstualnim kriterijima, općenito, u prethodnom primjeru sve ostaje isto, osim kako je naznačen tekstualni kriterij, a naznačen je samo u ovom obliku: \u003d "\u003d s. Serovo" i tada ćete dobiti rezultat, inače formula neće moći prepoznati vaš kriterij. Sada taj kriterij zamjenjujemo u formuli i dobivamo:

\u003d BDSUMM ($ 6 $: $ 12 $; E6; C2: C3), kao što vidimo, promijenio se samo raspon kriterija.

Da biste dobili sličan rezultat, trebat će vam:

\u003d SUMIF (C7: C12, "s.Serovo", E7: E12)

Zbir po dva kriterija u različitim stupcima

Primjer je kompliciran primjenom dva kriterija, ali nećemo primijeniti ništa suštinski novo, naznačit ćemo tekst kriterij "S. Serovo" i numerički kriterij "\u003e \u003d 10", ostavljajući polje zbrajanja "Žetva", dobit će promjenu formule samo za posljednji argument, kao rezultat ... Naša formula sada će izgledati ovako:

\u003d BDSUMM ($ 6 $: $ 12 $; E6; C2: E3), opet vidite promjene samo na adresi raspona kriterija.

Alternativna opcija može se dobiti pomoću i slično ovome:

\u003d SUMIF (E7: E12; C7: C12; C3; E7: E12; E3)

\u003d SUMIF (E7: E12; C7: C12; "s.Serovo"; E7: E12; "\u003e \u003d 10")

Zbir jednog od dva uvjeta u jednom stupcu

Razmotrimo drugu primjenu kako je primijenjena, ali sada ćemo upotrijebiti ne jedan kriterij, već dvostruki, ali za jedno polje. Dva kriterija moraju biti na različitim crtama. Bit formule svodi se na to da dva puta prolazi kroz isti raspon, računajući svaki od kriterija zasebno. Za ovaj primjer formula bi izgledala ovako:

\u003d BDSUMM ($ 6 $: $ 12 $; E6; C2: C4), ovdje opet mijenjamo raspon kriterija, ali ne u širinu, već u visinu.

Također kao zamjenu možete koristiti zbroj funkcije SUMIF:

\u003d SUMIF (C7: C12; C3; E7: E12) + SUMIF (C7: C12; C4; E7: E12).

Zbrajanje jednog od dva uvjeta u dva različita stupca

Peti primjer primjene bit će zbrajanje jednog od dva uvjeta u dva različita stupca, a argumenti se moraju smjestiti u nove retke, što će omogućiti njihovo sažimanje. U ove nam je svrhe potrebna formula:

\u003d BDSUMM ($ 6 $: $ 12 $; D6; C2: D4), načelo oblikovanja formule je očuvano, osim raspona kriterija, koji uključuje tri retka: naslov i dva kriterija.

Zbir po dvama kriterijima teksta u dva stupca

U ovom primjeru rada razmotrit ćemo gotovo cjelovit analog prethodno razmatranog primjera, kada su u dva stupca postojala dva kriterija, ali postojali su numerički i tekstualni kriterij, ali ovdje ćemo zbroj razmotriti po dva tekstualna kriterija po dva stupca. Koristimo kriterije "\u003d" \u003d s.Ivanovo "" i "\u003d" \u003d Cherry "", koje ćemo naznačiti u rasponu kriterija. Tako će naša formula izgledati ovako:

\u003d BDSUMM ($ 6 $: $ 12 $; D6; B2: C3).

Korištenje rezultata formule za dobivanje kriterija odabira i zbrajanja

U ovom primjeru funkcije BDSUMM koristim formulu za određivanje kriterija odabira i zbrajanja, u ovom ćemo slučaju pronaći koja stabla nam donose prihod i za utvrđivanje uvjetnog argumenta uzimamo prosječnu vrijednost prodaje plodova iz drveće i sve iznadprosječno nas zanima. Da bismo odredili prosječnu vrijednost koja će postati naš ISTINITI kriterij, kreiramo formulu:

\u003d G7\u003e PROSJEČNO ($ 7 $: $ 12 $), ne zaboravite popraviti raspon, tako da kada formula prolazi kroz vrijednosti, oni ne klize prema dolje, već bi vrijednost G7 trebala kliziti preko cijelog raspona kako bi se utvrdilo je li "FALSE" ili "TRUE".

Također je vrlo važno da se naslovi naslova ne dupliciraju, imaju razliku, pa ću polje s kriterijima nazvati "Prosječno". A onda će formula početi raditi, ponavljat će se u cijelom rasponu od 7 G $ 7: 12 G $ za prisutnost prosječne vrijednosti, a kada je pozitivan rezultat "TRUE", zbrojit će. Sljedeća će se formula nositi s ovim radom:

\u003d BDSUMM ($ 6 $: $ 12 $; G6; $ G $ 2: $ G $ 3)

A ako ste jako zainteresirani za alternativno rješenje pitanja, isprobajte opciju s funkcijom SUMIF u ovom obliku:

\u003d SUMIF ($ 7 $: $ 12 $; "\u003e" & PROSJEČNO ($ 7 $: $ 12 $))

BDSUMM funkcionira prema tri kriterija

U ovom primjeru izračunajmo prosječnu prodaju po voću uzgojenom u dva sela: „selo Ivanovo“ i „selo Uyutnoe“. Već sam opisao glavnu ideju odabira prema kriterijima, pa se neću ponavljati, jednostavno ću reći da će to biti kombinacija prethodno razmatranih kriterija. Da bismo dobili rezultat, potreban nam je u ovom obliku:

\u003d BDSUMM ($ 6 $: $ 12 $; G6; $ 2 $: $ G $ 4)

Zbir po kriteriju teksta, velika i mala slova

Kao što sam ranije spomenuo, može pretraživati \u200b\u200bne samo sa zamjenskim znakovima, već i s obzirom na velika i mala slova, sada je to upravo ta opcija. Za početak definiramo uvjet za odabir kriterija, ako se velikim slovima susreće naziv "s. IVANOVO", tada vršimo zbrajanje, da bismo odredili taj kriterij potrebna nam je formula:

\u003d VIJEĆE ("S. IVANOVO"; C7)

Ali sada možemo napisati funkciju koja će provjeriti raspon na prisutnost navedenog kriterija i, kad se primi vrijednost "TRUE", izvršit će zbrajanje. U primjeru sam jednom naznačio prema stanju, i kao što vidite, formula je uspješno odabrala sva naselja i pronašla željeno i dobila rezultat "9". Za to je korištena formula:

\u003d BDSUMM ($ 6 $: $ 12 $; D6; $ 2 $: $ 3 $)

Pa, mislim da sam detaljno opisao detalje, pa će biti malo pitanja, ali puno prednosti. Ako imate pitanja, pišite komentare, radujem se vašim lajkovima i recenzijama. O ostalim funkcijama možete saznati na mojoj web stranici.

Uspjeh u vašem poslu!

"Novac se, naravno, može kupiti preslatki pas, ali nikakav novac ne može ga natjerati da sretno maše repom.
"
D. Billings

Preporučujemo čitanje

Uredski programi

Kalendar značajnih i nezaboravnih datuma godina u Rusiji proglašena Godinom ekologije

Pametni telefoni

Kako riješiti primjere razlomcima

Tablete

Aforizmi i citati o religiji

Uredski programi

Što je unca i kako se unca pretvara u grame?

Uredski programi

Kratka tona Kratka tona do metrička tona

Tablete

Što žena treba jesti da bi joj povećala poprsje