Predpostavimo, da imate naslednje poročilo o prodaji prodajnih zastopnikov:
Iz njega morate ugotoviti, koliko svinčniki prodaja prodajalec Ivanov v januar.
TEŽAVA: Kako povzeti podatke po več kriterijih??
REŠITEV: 1. metoda:
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 sešteje podatke stolpcev Količina(prepir " Polje" = F1) glede na podano v celicah I1:K2 (Prodajalec = Ivanov; Izdelki = Svinčniki;Mesec = januar) merila.
PROTI: Seznam kriterijev naj bo na listu.
OPOMBE: Število kriterijev seštevanja je omejeno z RAM-om.
PODROČJE UPORABE: katera koli različica Excela
2. način:
SUMPRODUCT((B2:B16=I2)*(D2:D16=J2)*(A2:A16=K2)*F2:F16)
V angleški različici:
SUMPRODUCT((B2:B16=I2)*(D2:D16=J2)*(A2:A16=K2)*F2:F16)
KAKO DELUJE:
Funkcija SUMPRODUCT generira polja vrednosti TRUE in FALSE glede na izbrana merila v Excelovem pomnilniku.
Če bi bili izračuni izvedeni v celicah lista (zaradi jasnosti bom prikazal celotno delovanje formule, kot da bi izračuni potekali na listu in ne v pomnilniku), bi nizi izgledali takole:
Očitno je, da če npr. D2=Svinčniki, potem bo vrednost TRUE in če D3=Mape, nato FALSE (ker je merilo za izbiro izdelka v našem primeru vrednost Svinčniki).
Ker vemo, da je TRUE vedno enako 1 in FALSE vedno enako 0, nadaljujemo z delom z nizi kot s števili 0 in 1.
Če zaporedno pomnožimo dobljene vrednosti nizov med seboj, dobimo EN niz ničel in enic. Če so bila izpolnjena vsa tri merila za izbor, ( IVANOV, SVINČNIKI, JANUAR) tj. vsi pogoji so TRUE, dobimo 1 (1*1*1 = 1), če pa vsaj en pogoj ni bil izpolnjen, dobimo 0 (1*1*0 = 0 ; 1*0*1 = 0 ; 0* 1* 1 = 0).
Zdaj ostane samo še pomnožiti nastalo matriko z matriko, ki vsebuje podatke, ki jih moramo sešteti kot rezultat (obseg F2:F16) in pravzaprav seštejejo tisto, kar ni pomnoženo z 0.
Zdaj primerjajte nize, dobljene s pomočjo formule in z izračunom korak za korakom na listu (označeno z rdečo).
Mislim, da je vse jasno :)
MINUSI: SUMPRODUCT - "težka" matrična formula. Pri računanju na velikih obsegih podatkov se čas ponovnega izračuna opazno poveča.
OPOMBE
PODROČJE UPORABE: katera koli različica Excela
3. način: matrična formula
SUM(IF((B2:B16=I2)*(D2:D16=J2)*(A2:A16=K2),F2:F16))
V angleški različici:
SUM(IF((B2:B16=I2)*(D2:D16=J2)*(A2:A16=K2),F2:F16))
KAKO DELUJE: Tako kot metoda št. 2. Obstajata samo dve razliki - to formulo vnesemo s pritiskom na Ctrl+Shift+Enter namesto da samo pritisnete Vnesite in niz 0. in 1. ni pomnožen z obseg seštevanja, in je izbran s funkcijo IF.
MINUSI: Matrične formule pri računanju na velikih obsegih podatkov opazno podaljšajo čas ponovnega izračuna.
OPOMBE: Število obdelanih nizov je omejeno na 255.
PODROČJE UPORABE: katera koli različica Excela
4. način:
SUMIFS(F2:F16;B2:B16;I2;D2:D16;J2;A2:A16;K2)
Prej sem opisal, kako uporabiti funkcijo po meri za iskanje . Na žalost ta funkcija ne deluje, če so celice obarvane z pogojno oblikovanje. Obljubil sem, da bom funkcijo "dodelal do konca". Toda v dveh letih, ki sta minili od objave tistega zapisa, mi ni uspelo napisati prebavljive kode niti sam niti s pomočjo informacij iz interneta ... ( Posodobitev 29. marec 2017 Po nadaljnjih petih letih mi je vseeno uspelo napisati kodo; glej zadnji del opombe). In ravno pred kratkim sem naletel na idejo iz knjige D. Holy, R. Holy "Excel 2007. Triki", ki vam omogoča, da sploh ne delate brez kode.
Naj obstaja seznam števil od 1 do 100, postavljenih v obseg A1: A100 (slika 1; glejte tudi list "SUMIS" datoteke Excel). Obseg ima pogojno oblikovanje, ki označuje celice, ki vsebujejo števila, večja od 10 in manjša ali enaka 20.
riž. 1. Razpon števil; pogojno oblikovanje poudari celice, ki vsebujejo vrednosti od 10 do 20
Prenesite opombo v formatu , primere v formatu
Zdaj morate v celice dodati vrednosti, ki izpolnjujejo merila, ki ste jih pravkar določili. Ni pomembno, kakšno oblikovanje je uporabljeno za te celice, vendar morate poznati merila, po katerih so celice označene.
Če želite dodati obseg celic, ki ustreza sam kriterij, lahko uporabite funkcijo SUMIF (slika 2).
riž. 2. Seštevanje celic, ki izpolnjujejo enak pogoj
Če imate več pogoje, lahko uporabite Funkcija SUMIFS(slika 3).
riž. 3. Seštevanje celic, ki izpolnjujejo več pogojev
S funkcijo COUNTIF lahko preštejete število celic, ki izpolnjujejo iste kriterije.
S funkcijo COUNTIFS lahko preštejete število celic, ki izpolnjujejo več kriterijev.
Excel ponuja še eno funkcijo, ki vam omogoča, da določite več pogojev. Ta funkcija je del osnovnega nabora funkcij. excel podatki in se imenuje BDSUMM. Za preverjanje uporabite isti nabor številk v obsegu A2:A100 (slika 4; glejte tudi list "BDSUMM" datoteke Excel).
riž. 4. Uporaba funkcij baze podatkov
Izberite celice C1:D2 in ta obseg poimenujte Criteria, tako da ga vnesete v polje za ime na levi strani vrstice s formulami. Zdaj izberite celico C1 in vnesite =$A$1, ki je povezava do prve celice na delovnem listu, ki vsebuje ime baze podatkov. Vnesite =$A$1 v celico D1 in dobili boste dve kopiji naslova stolpca A. Ti kopiji bosta uporabljeni kot naslova za pogoje BDSUMM (C1:D2), ki ste jih poimenovali Kriteriji. V celico C2 vnesite >10. V celico D2 vnesite<=20. В ячейке, где должен быть результат, введите следующую формулу:
BDSUMM($A$1:$A$101,1, Merila)
S funkcijo COUNT lahko preštejete število celic, ki ustrezajo več kriterijem.
Med branjem knjige Johna Walkenbacha sem izvedel, da je VBA od Excela 2010 uvedel novo lastnost DisplayFormat (glejte na primer Lastnost Range.DisplayFormat). To pomeni, da lahko VBA prebere format, prikazan na zaslonu. Ni pomembno, kako je bilo pridobljeno, z neposrednimi uporabniškimi nastavitvami ali s pogojnim oblikovanjem. Na žalost so razvijalci MS naredili tako, da lastnost DisplayFormat deluje samo v postopkih, klicanih iz VBA, in uporabniško definirane funkcije, ki temeljijo na tej lastnosti, ustvarijo #VALUE! Vendar pa lahko dobite vsoto vrednosti v obsegu v celicah določene barve z uporabo postopka (makra, ne funkcije). Odpri (vsebuje kodo VBA). Pojdite skozi meni Pogled -> Makri -> Makri; v oknu Makro, označite vrstico SumColorCond. in pritisnite Teči. Zaženite makro, izberite obseg seštevanja in kriterij. Odgovor se prikaže v oknu.
Šifra postopka
Sub SumColorCond() Application.Volatile True Dim SumColor As Double Dim i As Range Dim UserRange As Range Dim CriterionRange As Range SumColor = 0 " Range Request Set UserRange = Application.InputBox(_ Prompt:="Select Sum Range", _ Title: ="Range Selection", _ Privzeto:=ActiveCell.Address, _ Type:=8) " Criterion Query Set CriterionRange = Application.InputBox(_ Prompt:="Select kriterij seštevanja", _ Title:="Criteria selection", _ Default:=ActiveCell.Address, _ Type:=8) " Seštejte "pravilne" celice za vsak i In UserRange If i.DisplayFormat.Interior.Color = _ CriterionRange.DisplayFormat Interior.Color Then SumColor = SumColor + i End If Next MsgBox SumColor End Sub
Sub SumColorCond() aplikacija. Hlapno Res Dim SumColor kot dvojna Dim i As Range Zatemni obseg uporabnika kot obseg Dim CriterionRange kot obseg SumColor = 0 "Zahteva za obseg Nastavi UserRange = Application.InputBox(_ Poziv:="Izberi obseg seštevka", _ Naslov:="Izbira obsega", _ Privzeto:=ActiveCell.Address, _ Tip:=8) "Merila za zahtevo Nastavite CriterionRange = Aplikacija. InputBox(_ Poziv := "Izberi merilo seštevka", _ Naslov : = "Izbira meril" , _ Privzeto:= ActiveCell. Naslov, _ |
Pogosto se zgodi, da morate sešteti vsako drugo, tretjo, četrto itd. celico v preglednici. Zdaj je to mogoče, zahvaljujoč naslednjemu triku.
Excel ne ponuja standardne funkcije, ki bi lahko seštela vsako n-to celico ali niz. Vendar pa lahko to nalogo opravite z več različne poti. Vsi ti pristopi temeljijo na funkcijah ROW (ROW) in MOD (MOD).
funkcija ROW vrne številko vrstice za dano referenco celice: ROW(reference), v ruski različici Excela ROW(reference).
Funkcija MOD (MOD) vrne ostanek po deljenju števila z deliteljem: MOD(število;delilec), v ruski različici Excela MOD(število;delitelj).
Vstavite funkcijo ROW v funkcijo MOD (za prenos številskega argumenta), delite z 2 (za seštevek vsake druge celice) in preverite, ali rezultat ni nič. Če je tako, se celica sešteje. Te funkcije je mogoče uporabiti na veliko različnih načinov – nekatere bodo zagotovile boljše rezultate kot druge. Na primer, matrična formula za seštevek vseh drugih celic v obsegu $A$1:$A$100 je lahko videti takole: =SUM(IF(MOD(ROW($A$1:$A$500);2)=0;$ 1 A$: 500 A$;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: (=SUM(IF(MOD(ROW($A$1:$A$500), 2)= 0;$A$1:$A$500;0))) $500;0))) Za dodajanje zavitih oklepajev potrebujemo 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 bi bile stvari še hujše, ima ta dolga formula v njej ugnezdeno funkcijo ROW, ki jo je mogoče ponovno izračunati, zaradi česar je večjo formulo prav tako mogoče ponovno izračunati. To pomeni, da se bo nenehno preračunaval, ne glede na to, kaj počnete v delovnem zvezku. To je zelo slab način!
Tukaj je še ena formula, ki je malo najboljša izbira: =SUMPRODUCT((MOD(ROW($A$1:$A$500);2)=0)*($A$1:$A$500)) :$A$500);2)=0)*($A$1 :$500 A$)).
Vendar se zavedajte, da bo ta formula vrnila #VALUE! (#VREDNOST!), če katera koli celica v obsegu vsebuje besedilo namesto številk. Tudi ta formula se upočasni, čeprav dejansko ni matrična formula Delo v Exceluče je uporabljen 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 katerem se sešteje vsaka n-ta celica.
V celico E1 vnesite besedo Kriteriji. V celico E2 vnesite to formulo: =MOD(ROW(A2)-$C$2-1,$C$2)=0 =0 . Izberite celico C2 in izberite Podatki → Preverjanje.
V polju Data Type (Allow) izberite Seznam (List) in v polje Source (Vir) vnesite 1, 2, 3, 4, 5, 6, 7, 8, 9, 10. Prepričajte se, da je seznam polje je izbrano dovoljene vrednosti(V celici) in kliknite gumb V redu. V celico C1 vnesite besedilo SUM vsakih…. V katero koli celico razen v vrstico 1 vnesite to formulo: =DSUM($A:$A;1;$E$1:$E$2) :$E$2) .
V celico tik nad tisto, v katero ste vnesli funkcijo DSUM, vnesite besedilo ="Summing Every" & $C$2 & CHOOSE($C$2;"st";"nd";"rd";"th";" th";"th";"th";"th";"th";"th") & "Celica" . Zdaj ostane samo še izbrati želeno število v celici C2, ostalo pa bo opravila funkcija DSUM.
S funkcijo DSUM lahko seštejete celice v intervalih, ki jih določite. Funkcija DSUM je veliko učinkovitejša od matrične formule ali funkcije SUMPRODUCT. Čeprav nastavitev traja nekoliko dlje, je to primer, ko se je težko naučiti, enostavno boriti.
