Zbroji odabrane stanice u Excelu. Excel. Broji i zbrajaj stanice koje udovoljavaju uvjetima uvjetnog oblikovanja
Često se dogodi da u proračunskoj tablici trebate zbrojiti svaku drugu, treću, četvrtu i tako dalje. Sada, zahvaljujući sljedećem triku, to se može učiniti.
Excel ne nudi standardnu \u200b\u200bfunkciju koja može sažeti svaku n-ta ćelija ili niz. Međutim, ovaj zadatak možete ostvariti s nekoliko različiti putevi... Svi se ovi pristupi temelje na funkcijama ROW i MOD.
RED funkcija vraća broj retka za zadanu referencu ćelije: ROW (referenca), u ruskoj verziji Excela ROW (referenca).
OSTAT funkcija (MOD) vraća ostatak dijeljenja broja dijeliteljem: MOD (broj; djelitelj), u ruskoj verziji programa Excel OSTAT (broj; djelitelj).
Stavite funkciju ROW u funkciju MOD (da biste proslijedili numerički argument), podijelite s 2 (da zbrojite svaku drugu ćeliju) i provjerite nije li rezultat nula. Ako je tako, ćelija se zbraja. Te se funkcije mogu koristiti na razne načine - neke će pružiti bolje rezultate od drugih. Na primjer, formula niza za zbrajanje svake druge ćelije u rasponu $ A $ 1: $ A $ 100 može izgledati ovako: \u003d SUM (IF (MOD (ROW ($ A $ 1: $ A $ 500); 2) \u003d 0; $ A $ 1: $ A $ 500; 0)), u ruskoj verziji programa Excel \u003d SUM (AKO (OSTAT (LINE ($ A $ 1: $ A $ 500); 2) \u003d 0; $ A $ 1: $ 500 $; 0)).
Budući da je ovo formula niza, morate je unijeti pritiskom na Ctrl + Shift + Enter, Excel će dodati kovrčave zagrade tako da izgleda ovako: (\u003d SUM (IF (MOD (ROW ($ A $ 1: $ A $ 500) ), 2) \u003d 0; $ A $ 1: $ A $ 500; 0))), u ruskoj verziji programa Excel: (\u003d SUM (AKO (Preostalo (ROW ($ A $ 1: $ A $ 500); 2) \u003d 0; $ A $ 1: $ A $ 500; 0))) Trebate Excel da biste sami dodali kovrčave zagrade; ako ih dodate sami, formula neće raditi.
Iako je cilj postignut, ova metoda negativno utječe na dizajn. proračunska tablica... Ovo je nepotrebna primjena formule niza. Da stvar bude još gora, ova dugačka formula uključuje funkciju ROW koja se može ponovno izračunati, koja veliku formulu također pretvara u funkciju koja se može ponovno izračunati. To znači da će se stalno preračunavati, bez obzira na to što radite u radnoj knjižici. Ovo je vrlo loš način!
Evo još jedne formule koja je blago najbolji izbor: \u003d SUMPRODUCT ((MOD (RED ($ A $ 1: $ A $ 500); 2) \u003d 0) * ($ A $ 1: $ A $ 500)), u ruskoj verziji Excela \u003d SUMPRODUCT ((OSTALO (LINIJA ($ A $ 1: $ A $ 500); 2) \u003d 0) * ($ A $ 1: $ A $ 500)).
Međutim, imajte na umu da će ova formula vratiti #Vrijednost! (#Vrijednost!) Ako bilo koja ćelija u rasponu sadrži tekst umjesto brojeva. Ova formula, iako zapravo nije formula niza, također usporava excel radako ga koristite previše puta ili se svaki put odnosi na velik raspon.
Srećom postoji najbolji način, što je ne samo učinkovitije, već i puno fleksibilnije. Zahtijeva upotrebu funkcije DSUM. U ovom smo primjeru koristili raspon A1: A500 kao raspon u koji treba dodati svaku n-tu ćeliju.
Unesite riječ Kriteriji u ćeliju E1. U ćeliju E2 unesite sljedeću formulu: \u003d MOD (RED (A2) - $ C $ 2-1; $ C $ 2) \u003d 0, u ruskoj verziji Excela \u003d OSTAT (RED (A2) - $ C $ 2- 1; $ C $ 2) \u003d 0. Odaberite ćeliju C2 i odaberite naredbu Podaci → Provjera.
U polje Dopusti odaberite Popis, a u polje Izvor unesite 1, 2, 3, 4, 5, 6, 7, 8, 9, 10. Provjerite je li potvrđen okvir (U ćeliji) i kliknite gumb U redu. U ćeliju C1 unesite tekst SUM svakih .... U bilo koju ćeliju osim retka 1 unesite sljedeću formulu: \u003d DSUM ($ A: $ A; 1; $ E $ 1: $ E $ 2), u ruskoj verziji programa Excel \u003d BDSUMM ($ A: $ A; 1; $ E $ 1: $ E $ 2).
U ćeliju neposredno iznad ćelije u koju ste unijeli funkciju DSUM, unesite text \u003d "Summing Every" & $ C $ 2 & CHOOSE ($ C $ 2; "st"; "nd"; "rd"; "th" ; "th"; "th"; "th"; "th"; "th"; "th") & "Cell". Sada ostaje samo odabrati željeni broj u ćeliji C2, a ostalo će učiniti funkcija DSUM.
Pomoću funkcije BDSUM (DSUM) možete zbrojiti stanice u intervalu koji navedete. Funkcija DSUM mnogo je učinkovitija od formule niza ili funkcije SUMPRODUCT. Iako je potrebno malo više vremena za postavljanje, ovo je slučaj kada je teško naučiti i lako se boriti.
Ranije sam opisao kako pronaći. Nažalost, ova funkcija ne radi ako su stanice obojene uvjetno oblikovanje... Obećao sam da ću "doraditi" funkciju. Ali u dvije godine koje su prošle od objavljivanja te bilješke, nisam uspio napisati probavljivi kod niti samostalno niti koristeći podatke s Interneta ... ( Ažuriranje od 29. ožujka 2017 Nakon još pet godina, ipak sam uspio napisati kod; vidi završni dio bilješke). I nedavno sam naišao na ideju sadržanu u knjizi D. Hawleyja, R. Hawleyja "Excel 2007. Trikovi", koja vam omogućuje da radite bez koda.
Neka postoji popis brojeva od 1 do 100, koji se nalaze u rasponu A1: A100 (slika 1; vidi također list "SUMIF" datoteke Excel). Raspon je uvjetno formatiran da označi ćelije koje sadrže brojeve veće od 10 i manje ili jednako 20.
Lik: 1. Raspon brojeva; uvjetnim oblikovanjem istaknute ćelije koje sadrže vrijednosti od 10 do 20
Preuzmite bilješku u formatu, primjere u formatu
Sada morate dodati vrijednosti u ćelije koje zadovoljavaju upravo postavljene kriterije. Nije važno kakvo se oblikovanje primjenjuje na ove stanice, ali morate znati kriterije po kojima su stanice istaknute.
Da biste zbrojili niz ćelija koje se podudaraju jedan kriterij, možete koristiti funkciju SUMIF (slika 2).
Lik: 2. Zbrajanje stanica koje ispunjavaju jedan uvjet
Ako imate nekoliko U uvjetima možete koristiti funkciju SUMIFS (slika 3).
Lik: 3. Sumirajući stanice koje ispunjavaju nekoliko uvjeta
Možete koristiti funkciju COUNTIF za brojanje broja stanica koje zadovoljavaju jedan kriterij.
Možete koristiti funkciju COUNTIF za brojanje broja ćelija koje ispunjavaju više kriterija.
Excel pruža još jednu funkciju koja vam omogućuje da odredite više uvjeta. Ova je funkcija uključena u skup funkcija baza excel podaci a naziva se BDSUMM. Da biste je provjerili, upotrijebite isti skup brojeva u rasponu A2: A100 (slika 4; vidi također list „BDSUMM“ u datoteci Excel).
Lik: 4. Korištenje funkcija baze podataka
Odaberite ćelije C1: D2 i imenujte ovaj raspon Kriterij tako da ga upišete u okvir za naziv lijevo od trake formule. Sada odaberite ćeliju C1 i unesite \u003d $ A $ 1, što je referenca na prvu ćeliju na listu koja sadrži ime baze podataka. Unesite \u003d $ A $ 1 u ćeliju D1 i dobit ćete dvije kopije naslova stupca A. Te će se kopije koristiti kao zaglavlja za uvjete BDSUMM (C1: D2), koje ste imenovali Kriterij. U ćeliju C2 unesite\u003e 10. U ćeliju D2 unesite<=20. В ячейке, где должен быть результат, введите следующую формулу:
BDSUMM ($ 1 $: 101,1 $, kriterij)
Pomoću funkcije COUNT možete računati broj ćelija koje ispunjavaju više kriterija.
Čitajući knjigu Johna Walkenbacha, saznao sam da, počevši od programa Excel 2010, VBA ima novo svojstvo DisplayFormat (vidi, na primjer, svojstvo Range.DisplayFormat). Odnosno, VBA može čitati format prikazan na zaslonu. Nije važno kako je primljen izravnim korisničkim postavkama ili korištenjem uvjetnog oblikovanja. Nažalost, MS je napravio tako da svojstvo DisplayFormat radi samo u procedurama pozvanim iz VBA, a UDF-ovi koji se temelje na ovom svojstvu daju #Vrijednost! Međutim, zbroj vrijednosti u rasponu možete dobiti ćelijama određene boje pomoću postupka (makronaredba, ali ne i funkcija). Otvoreno (sadrži VBA kôd). Prođite kroz izbornik Pogled -> Makronaredbe -> Makronaredbe; u prozoru Makro, istaknite crtu SumColorUsli pritisnite Izvršiti... Pokrenite makronaredbu, odaberite raspon zbroja i kriterij. Odgovor će se pojaviti u prozoru.
Kod postupka
Sub SumColorConv () Application.Volatile True Dim SumColor kao Double Dim i As Range Dim UserRange As Range Dim CriterionRange As Range SumColor \u003d 0 "Upit o rasponu Set UserRange \u003d Application.InputBox (_ Prompt: \u003d" Odabir raspona zbrajanja ", _ Naslov: \u003d "Odabir raspona", _ Zadano: \u003d ActiveCell.Address, _ Tip: \u003d 8) "Postavljanje zahtjeva za kriterij CriterionRange \u003d Application.InputBox (_ Prompt: \u003d" Select kriterij zbrajanja", _ Naslov: \u003d" Odabir kriterija ", _ Zadano: \u003d ActiveCell.Address, _ Tip: \u003d 8)" Zbrajanje "ispravnih" ćelija za svaki i u UserRange If i.DisplayFormat.Interior.Color \u003d _ CriterionRange.DisplayFormat Interijer.Color Tada SumColor \u003d SumColor + i End If Next MsgBox SumColor End Sub
Sub SumColorUl () Primjena. Hlapljivo Istina Zatamni sumboju kao dvostruku Dim i As Range Zatamni opseg korisnika kao raspon Dim CriterionRange As Range SumColor \u003d 0 "Upit za raspon Postavi UserRange \u003d Application.InputBox (_ Upit: \u003d "Odaberi raspon zbrajanja", _ Naslov: \u003d "Odabir raspona", _ Zadano: \u003d ActiveCell.Address, _ Vrsta: \u003d 8) "Proscriptor Postavite CriterionRange \u003d Primjena. InputBox (_ Upit: \u003d "Odaberite kriterij zbrajanja", _ Naslov: \u003d "Odabir kriterija", _ Zadano: \u003d ActiveCell. Adresa, _ |
Pretpostavimo da imate ovakvo izvješće o prodaji:
Iz nje trebate saznati koliko olovke prodaje prodajni predstavnik Ivanov u siječnja.
PROBLEM: Kako sažeti podatke po nekoliko kriterija ??
ODLUKA: Metoda 1:
BDSUMM (A1: G16; F1; I1: K2)
U engleskoj verziji:
DSUM (A1: G16, F1, I1: K2)
KAKO RADI:
Iz baze podataka koju smo naveli A1: G16 funkcija BDSUMM dohvaća i sažima podatke stupca broj (argument " Polje" = F1) prema danoj u stanicama I1: K2 (Prodavač \u003d Ivanov; Proizvodi \u003d Olovke; Mjesec \u003d siječanj) kriteriji.
PROTIV: Popis kriterija trebao bi biti na listu.
BILJEŠKE: Broj kriterija zbrajanja ograničen je RAM-om.
PODRUČJE PRIJAVE: Bilo koja verzija programa Excel
Metoda 2:
SUMPRODUKT ((B2: B16 \u003d I2) * (D2: D16 \u003d J2) * (A2: A16 \u003d K2) * F2: F16)
U engleskoj verziji:
SUMPRODUKT ((B2: B16 \u003d I2) * (D2: D16 \u003d J2) * (A2: A16 \u003d K2) * F2: F16)
KAKO RADI:
Funkcija SUMPRODUCT oblikuje nizove vrijednosti TRUE i FALSE, prema odabranim kriterijima, u Excel memoriji.
Ako su izračuni izvedeni u stanicama lista (radi preglednosti, prikazat ću cjelokupni rad formule kao da se izračuni odvijaju na listu, a ne u memoriji), tada bi nizovi izgledali ovako:
Očito je da ako npr. D2 \u003d Olovke, tada će vrijednost biti TRUE, a ako D3 \u003d Mape, zatim FALSE (budući da je kriterij za odabir proizvoda u našem primjeru vrijednost Olovke).
Znajući da je TRUE uvijek jednako 1, a FALSE uvijek jednako 0, nastavljamo raditi s nizovima kao s brojevima 0 i 1.
Množeći uzastopno dobivene vrijednosti nizova između sebe, dobivamo JEDAN niz nula i jedinica. Gdje su zadovoljena sva tri kriterija za odabir, ( IVANOV, Olovke, siječanj) tj. svi su uvjeti dobili vrijednosti TRUE, dobivamo 1 (1 * 1 * 1 \u003d 1), ako barem jedan uvjet nije zadovoljen, dobivamo 0 (1 * 1 * 0 \u003d 0; 1 * 0 * 1 \u003d 0; 0 * 1 * 1 \u003d 0).
Sada ostaje samo pomnožiti dobiveni niz s nizom koji sadrži podatke koje trebamo zbrojiti kao rezultat (raspon F2: F16) i zapravo zbrojimo ono što se ne pomnoži s 0.
Sada usporedite nizove dobivene pomoću formule i tijekom detaljnog izračuna na listu (označeno crvenom bojom).
Mislim da je sve jasno :)
MINUSI: SUMPRODUCT - "teška" formula niza. Prilikom izračunavanja na velikim rasponima podataka, vrijeme ponovnog izračunavanja zamjetno se povećava.
BILJEŠKE
PODRUČJE PRIJAVE: Bilo koja verzija programa Excel
3. metoda: Formula niza
ZBIR (IF ((B2: B16 \u003d I2) * (D2: D16 \u003d J2) * (A2: A16 \u003d K2); F2: F16))
U engleskoj verziji:
ZBIR (IF ((B2: B16 \u003d I2) * (D2: D16 \u003d J2) * (A2: A16 \u003d K2), F2: F16))
KAKO RADI: Na isti način kao i metoda # 2. Postoje samo dvije razlike - u ovu se formulu unosi pritiskom Ctrl + Shift + Enternego samo pritiskom Unesi a niz 0-ti i 1-q ne množi se rasponom zbrajanja, već se odabire pomoću funkcije IF.
MINUSI: Formule niza pri izračunavanju velikih raspona podataka osjetno povećavaju vrijeme ponovnog izračuna.
BILJEŠKE: Broj obrađenih nizova ograničen je na 255.
PODRUČJE PRIJAVE: Bilo koja verzija programa Excel
Metoda 4:
SUMIF (F2: F16; B2: B16; I2; D2: D16; J2; A2: A16; K2)