Seštevanje izbranih celic v Excelu. Excel. Štetje in seštevanje celic, ki izpolnjujejo pogojna merila za oblikovanje
Pogosto se zgodi, da morate v preglednici strniti vsako drugo, tretjo, četrto itd. Celico. Zdaj je po zaslugi naslednjega trika to mogoče storiti.
Excel ne ponuja standardne funkcije, ki bi lahko seštevala vsakega n-ta celica ali niz. Vendar lahko to nalogo opravite z več različne poti... Vsi ti pristopi temeljijo na funkcijah ROW in MOD.
Funkcija ROW vrne številko vrstice za dano referenco celice: ROW (sklic), v ruski različici Excela ROW (sklic).
Funkcija OSTAT (MOD) vrne preostanek delitve števila z delilcem: MOD (število; delilec) v ruski različici Excela OSTAT (število; delilec).
Vstavite funkcijo ROW v funkcijo MOD (da prenesete številski argument), delite z 2 (za seštevanje vseh drugih celic) in preverite, ali rezultat ni enak nič. V tem primeru se celica sešteje. Te funkcije je mogoče uporabljati na najrazličnejše načine - nekatere bodo prinesle boljše rezultate kot druge. Primer: matrična formula, ki sešteje vsako drugo celico v obsegu $ A $ 1: $ A $ 100, bi lahko izgledala takole: \u003d SUM (IF (MOD (ROW ($ A $ 1: $ A $ 500); 2) \u003d 0; $ A $ 1: $ A $ 500; 0)), v ruski različici Excela \u003d SUM (IF (OSTAT (LINE ($ A $ 1: $ A $ 500); 2) \u003d 0; $ A 1 USD: 500 USD; 0)).
Ker je to matrična formula, jo morate vnesti s pritiskom na Ctrl + Shift + Enter, Excel bo dodal zavite oklepaje, tako da bo videti takole: (\u003d SUM (IF (MOD (ROW ($ A $ 1: $ A $ 500) ), 2) \u003d 0; $ A $ 1: $ A $ 500; 0))), v ruski različici Excela: (\u003d SUM (IF (Preostalo (LINE ($ A $ 1: $ A $ 500); 2) \u003d 0; $ A $ 1: $ A $ 500; 0))) Za dodajanje kodrastih oklepajev potrebujete Excel; če jih dodate sami, formula ne bo delovala.
Čeprav je cilj dosežen, ta metoda negativno vpliva na oblikovanje. preglednico... To je nepotrebna uporaba matrične formule. Da je stvar še hujša, ta dolga formula vključuje funkcijo ROW, ki jo je mogoče ponovno izračunati, ki veliko formulo spremeni tudi v funkcijo, ki jo je mogoče ponovno izračunati. To pomeni, da se bo nenehno preračunaval, ne glede na to, kaj delate v delovnem zvezku. To je zelo slab način!
Tu je še ena formula, ki je rahlo najboljša izbira: \u003d SUMPRODUCT ((MOD (ROW ($ A $ 1: $ A $ 500); 2) \u003d 0) * ($ A $ 1: $ A $ 500)), v ruski različici Excela \u003d SUMPRODUCT ((REST (ROW ($ A $ 1: $ A $ 500); 2) \u003d 0) * ($ A $ 1: $ A $ 500)).
Ne pozabite pa, da bo ta formula vrnila vrednost #VARUE! (#VREDNOST!) Če katera koli celica v obsegu vsebuje besedilo namesto številk. Ta formula, čeprav pravzaprav ni matrična formula, se tudi upočasni excel deloče ga uporabljate prevečkrat ali če se vsakič nanaša na velik obseg.
Na srečo obstaja najboljši način, ki ni le bolj učinkovit, ampak tudi veliko bolj prilagodljiv. Zahteva uporabo funkcije DSUM. V tem primeru smo uporabili obseg A1: A500 kot obseg, v katerega je treba dodati vsako n-to celico.
V celico E1 vnesite besedo Merila. V celico E2 vnesite naslednjo formulo: \u003d MOD (ROW (A2) - $ C $ 2-1; $ C $ 2) \u003d 0, v ruski različici Excela \u003d OSTAT (ROW (A2) - $ C $ 2- 1; $ C $ 2) \u003d 0. Izberite celico C2 in izberite ukaz Podatki → Preverjanje.
V polju Dovoli izberite Seznam in v polje Vir vnesite 1, 2, 3, 4, 5, 6, 7, 8, 9, 10. Preverite, ali je potrditveno polje potrjeno. (V celici) in kliknite gumb V redu. V celico C1 vnesite besedilo SUM vsakih…. V katero koli celico, ki ni vrstica 1, vnesite naslednjo formulo: \u003d DSUM ($ A: $ A; 1; $ E $ 1: $ E $ 2), v ruski različici Excela \u003d BDSUMM ($ A: $ A; 1; $ E $ 1: $ E $ 2).
V celico neposredno nad celico, v katero ste vnesli funkcijo DSUM, vnesite besedilo \u003d "Summing Every" & $ C $ 2 & CHOOSE ($ C $ 2; "st"; "nd"; "rd"; "th" ; "th"; "th"; "th"; "th"; "th"; "th") & "Cell". Zdaj preostane le še, da izberemo želeno številko v celici C2, ostalo pa bo opravila funkcija DSUM.
S funkcijo BDSUM (DSUM) lahko seštejete celice v določenem intervalu. Funkcija DSUM je veliko bolj učinkovita kot matrična formula ali funkcija SUMPRODUCT. Čeprav traja nekaj dlje časa za postavitev, je to primer, ko se je težko naučiti in se zlahka boriti.
Prej sem opisal, kako najti a. Na žalost ta funkcija ne deluje, če so celice obarvane z pogojno oblikovanje... Obljubil sem, da bom "izpopolnil" funkcijo. Toda v dveh letih, ki sta minili od objave te opombe, nisem mogel sam napisati prebavljive kode niti z uporabo informacij iz interneta ... ( Posodobitev 29. marca 2017 Po nadaljnjih petih letih mi je še uspelo napisati kodo; glej zadnji del opombe). In prav pred kratkim sem naletel na idejo, ki jo vsebuje knjiga D. Hawleyja, R. Hawleyja "Excel 2007. Triki", ki vam omogoča brez kode.
Naj bo seznam številk od 1 do 100, ki se nahajajo v območju A1: A100 (slika 1; glej tudi list "SUMIF" v Excelovi datoteki). Obseg je bil pogojno oblikovan tako, da označuje celice, ki vsebujejo številke, večje od 10 in manjše ali enake 20.
Slika: 1. obseg številk; pogojno oblikovanje označenih celic, ki vsebujejo vrednosti od 10 do 20
Prenesite opombo v obliki, primere v obliki
Zdaj morate v celice dodati vrednosti, ki ustrezajo pravkar določenim merilom. Vseeno je, kakšno oblikovanje je uporabljeno za te celice, vendar morate poznati merila, po katerih so celice označene.
Če želite dodati vrsto celic, ki se ujemajo eno lahko uporabite funkcijo SUMIF (slika 2).
Slika: 2. Seštevanje celic, ki izpolnjujejo en pogoj
Če imate nekaj lahko uporabite funkcijo SUMIFS (slika 3).
Slika: 3. Seštevanje celic, ki izpolnjujejo več pogojev
S funkcijo COUNTIF lahko štejete število celic, ki izpolnjujejo en kriterij.
S funkcijo COUNTIF lahko štejete število celic, ki izpolnjujejo več meril.
Excel nudi še eno funkcijo, ki omogoča določanje več pogojev. Ta funkcija je vključena v nabor funkcij baz excel podatki in se imenuje BDSUMM. Če ga želite preveriti, uporabite isti niz številk v območju A2: A100 (slika 4; glejte tudi list "BDSUMM" v Excelovi datoteki).
Slika: 4. Uporaba funkcij baze podatkov
Izberite celice C1: D2 in poimenujte to merilo Kriterij, tako da ga vnesete v polje z imenom na levi strani vrstice s formulami. Zdaj izberite celico C1 in vnesite \u003d $ A $ 1, kar je sklic na prvo celico na listu, ki vsebuje ime baze podatkov. V celico D1 vnesite \u003d $ A $ 1 in dobili boste dve kopiji naslova stolpca A. Te kopije bodo uporabljene kot glave pogojev BDSUMM (C1: D2), ki ste jih poimenovali Merilo. V celico C2 vnesite\u003e 10. V celico D2 vnesite<=20. В ячейке, где должен быть результат, введите следующую формулу:
BDSUMM ($ A $ 1: $ A $ 101,1, merilo)
S funkcijo COUNT lahko štejete število celic, ki izpolnjujejo več meril.
Ob branju knjige Johna Walkenbacha sem ugotovil, da ima VBA z Excel 2010 novo lastnost DisplayFormat (glej na primer lastnost Range.DisplayFormat). To pomeni, da lahko VBA bere obliko, prikazano na zaslonu. Ni pomembno, kako je bil sprejet z neposrednimi uporabniškimi nastavitvami ali s pogojnim oblikovanjem. Na žalost je MS naredil tako, da lastnost DisplayFormat deluje samo v postopkih, ki jih prikliče VBA, in UDF-ji, ki temeljijo na tej lastnosti, vržejo #VREDNOST! Vendar lahko vsoto vrednosti v obsegu dobite po celicah določene barve s pomočjo postopka (makro, ne pa tudi funkcije). Odprto (vsebuje kodo VBA). Pojdite skozi meni Pogled -> Makro -> Makro; v oknu Makro, označite črto SumColorUslin pritisnite Izvedite... Zaženite makro, izberite obseg seštevanja in merilo. Odgovor se bo prikazal v oknu.
Koda postopka
Sub SumColorConv () Application.Volatile True Dim SumColor As Double Dim i As Range Dim UserRange As Range Dim CriterionRange As Range SumColor \u003d 0 "Range query Set UserRange \u003d Application.InputBox (_ Prompt: \u003d" Izberi obseg seštevanja ", _ Naslov: \u003d "Izbor obsega", _ Privzeto: \u003d ActiveCell.Address, _ Tip: \u003d 8) "Nabor zahtev za kriterij CriterionRange \u003d Application.InputBox (_ Prompt: \u003d" Izberi merilo seštevanja", _ Naslov: \u003d" Izbira merila ", _ Privzeto: \u003d ActiveCell.Address, _ Tip: \u003d 8)" Vsota "pravilnih" celic za vsako i v UserRange If i.DisplayFormat.Interior.Color \u003d _ CriterionRange.DisplayFormat Notranjost. Barva Potem SumColor \u003d SumColor + i Konec, če je naslednji MsgBox SumColor End Sub
Sub SumColorUl () Uporaba. Hlapljivo True Dim SumColor As Double Obseg Dim i As Zatemni obseg uporabnika kot obseg Dim CriterionRange As Range SumColor \u003d 0 "Poizvedba obsega Nastavite UserRange \u003d Application.InputBox (_ Poziv: \u003d "Izberi obseg seštevanja", _ Naslov: \u003d "Izbor obsega", _ Privzeto: \u003d ActiveCell.Address, _ Vrsta: \u003d 8) "Proscriptor Nastavite CriterionRange \u003d Application. InputBox (_ Poziv: \u003d "Izberite merilo seštevanja", _ Naslov: \u003d "Izbira merila", _ Privzeto: \u003d ActiveCell. Naslov, _ |
Recimo, da imate takšno poročilo o prodaji:
Iz njega morate ugotoviti, koliko svinčniki prodaja prodajni zastopnik Ivanov v januarja.
TEŽAVA: Kako povzeti podatke po več kriterijih ??
ODLOČBA: Metoda 1:
BDSUMM (A1: G16; F1; I1: K2)
V angleški različici:
DSUM (A1: G16, F1, I1: K2)
KAKO DELUJE:
Iz baze podatkov, ki smo jo navedli A1: G16 funkcijo BDSUMM pridobi in povzame podatke stolpcev znesek (prepir " Polje" = F1) glede na podane v celicah I1: K2 (Prodajalec \u003d Ivanov; Izdelki \u003d Svinčniki; Mesec \u003d januar) merila.
PROTI: Seznam meril mora biti na listu.
OPOMBE: Število meril za seštevanje je omejeno z RAM-om.
PODROČJE UPORABE: Katera koli različica Excela
2. metoda:
SUMPRODUCT ((B2: B16 \u003d I2) * (D2: D16 \u003d J2) * (A2: A16 \u003d K2) * F2: F16)
V angleški različici:
SUMPRODUCT ((B2: B16 \u003d I2) * (D2: D16 \u003d J2) * (A2: A16 \u003d K2) * F2: F16)
KAKO DELUJE:
Funkcija SUMPRODUCT v pomnilniku Excel tvori nize vrednosti TRUE in FALSE glede na izbrana merila.
Če bi bili izračuni izvedeni v celicah lista (zaradi jasnosti bom prikazal celotno delo formule, kot da izračuni potekajo na listu in ne v spominu), potem bi polja izgledala takole:
Očitno je, da če npr. D2 \u003d Svinčniki, potem bo vrednost TRUE in če D3 \u003d Mape, nato FALSE (ker je merilo za izbiro izdelka v našem primeru vrednost Svinčniki).
Ker vemo, da je TRUE vedno enako 1, FALSE pa vedno 0, še naprej delamo z nizi kot s številkama 0 in 1.
Če zaporedno pomnožimo dobljene vrednosti nizov, dobimo ENO niz ničel in enot. Kjer so bili izpolnjeni vsi trije izbirni kriteriji, ( IVANOV, Svinčniki, JANUAR) tj. vsi pogoji so dobili vrednosti TRUE, dobimo 1 (1 * 1 * 1 \u003d 1), če vsaj en pogoj ni izpolnjen, dobimo 0 (1 * 1 * 0 \u003d 0; 1 * 0 * 1 \u003d 0; 0 * 1 * 1 \u003d 0).
Zdaj je preostalo matriko le pomnožiti z matriko, ki vsebuje podatke, ki jih moramo kot rezultat povzeti (obseg F2: F16) in dejansko seštejemo tisto, kar se ne pomnoži z 0.
Zdaj primerjamo polja, dobljena s formulo, in med postopnim izračunom na listu (označeno z rdečo).
Mislim, da je vse jasno :)
MINUSI: SUMPRODUCT - "težka" formula matrike. Pri izračunu za velike obsege podatkov se čas preračunavanja opazno poveča.
OPOMBE
PODROČJE UPORABE: Katera koli različica Excela
3. metoda: Formula matrike
SUM (IF ((B2: B16 \u003d I2) * (D2: D16 \u003d J2) * (A2: A16 \u003d K2); F2: F16))
V angleški različici:
SUM (IF ((B2: B16 \u003d I2) * (D2: D16 \u003d J2) * (A2: A16 \u003d K2), F2: F16))
KAKO DELUJE: Na enak način kot 2. metoda. Razliki sta le dve - to formulo vnesemo s pritiskom na Ctrl + Shift + Enternamesto da samo pritisnete Enter in matrika 0 in 1-q se ne pomnoži z obsegom seštevanja, temveč se izbere s funkcijo IF.
MINUSI: Formule matrike pri izračunu obsega obsega podatkov opazno podaljšajo čas preračunavanja.
OPOMBE: Število obdelanih nizov je omejeno na 255.
PODROČJE UPORABE: Katera koli različica Excela
4. metoda:
SUMIFI (F2: F16; B2: B16; I2; D2: D16; J2; A2: A16; K2)