Kako koristiti SUMIF za zbrajanje * vidljivih * stanica koje odgovaraju kriterijima. Na primjer, zbrojite sve zapise gdje je Regija = "Istok", ali zanemarite retke koji su već skriveni. Iznenadit ćete se funkcijom koja štedi dan.
Gledaj video
- Pitanje od Jona: Napravite SUMIFS koji dodaje samo vidljive stanice
- Billov prvi pokušaj: Proslijedite niz u funkciju AGREGATE - ali to ne uspije
- Mikeovo izvrsno rješenje:
- SUBTOTAL ili AGREGATE ne mogu prihvatiti niz
- Ali možete koristiti OFFSET za obradu niza i slanje rezultata na SUBTOTAL.
- Upotrijebite SUMPRODUCT da biste utvrdili je li reda DA ili ne.
- Billov drugi pokušaj je poput provjere gasi li se svjetlo hladnjaka kad se vrata zatvore:
- Dodajte pomoćni stupac s AGREGATE jedne ćelije u svaki redak
- Usmjerite SUMIFS na taj stupac
Automatski generirani prijepis
- hej dobrodošli natrag vrijeme je za još jedan
- dvoboj Excel podcasta Ja sam Bill Jelen
- od pridružio se Mike Irvin
- iz Excela je zabavno ovo je naša epizoda
- 187 neki ako je vidljivo ruža u redu
- dobrodošao natrag, održavao sam seminar u
- Fort Myers Florida jučer i John
- bio u publici i imao je
- pitanje rekao je kako možeš napraviti iznos ako
- samo zbraja vidljive redove u redu pa
- ovdje je naša baza podataka i neke ako
- dovoljno je jednostavno
- pogledajte 385 do 824 i pogledajte je li odgovor potvrdan
- ako je zbroji odgovarajuće stanice
- od b5 do 24 da sve radi ali onda
- John je primjenjivao filter pa smo imali
- kategorija ovdje i podaci samo
- upotrijebite automatski filtar za brzu primjenu
- filtra tako da postoje sve naše B stanice
- i želimo zbrojiti samo da stanice
- to su B stanice koje bi to trebale biti
- nešto na 100, ali ova formula nije
- pa je pitanje kako to možemo učiniti
- zbroj ako to gleda samo na vidljivo
- redove i dobro sam rekao dobro
- jedino što znam da će doći
- zanemari vidljive retke je agregat
- funkcionirati u redu agregat
- funkciju pa ćemo dobiti neki broj 9
- zarez i onda je ovdje važan dio
- zanemari skrivene retke u redu i
- onda samo moram pronaći neki način da dođem
- niz tamo i znam viši
- verzije koriste niz kao i sve
- iznad 11 za taj drugi argument upotrijebite
- polje, ali znate da li će niz uspjeti
- ovdje dobro, pa ono što sam želio reći
- Htio sam učiniti, htio sam reći sve
- u redu, uzet ćemo sve ove brojeve
- ovdje i pomnožite ih puta logički
- pa pregledajte sve riječi preko
- 5 jednako da, u redu i što
- to će se dogoditi
- biti brojevi u pravu i to će biti
- procijeniti u nizu istinitih i lažnih podataka
- i onda kad prisilimo Excel da se množi
- istiniti i lažni puta puta broj the
- prolaze postaju 1
- u redu, tako da sve što ne kaže da
- procijenit će na 0 sve što
- čini li to da će procijeniti na
- prodaja u redu i onda samo
- morate napraviti ctrl shift enter
- pa moj bože to ne uspijeva možda i ne
- samo Enter će vam reći što sam Mike
- nastavit ću raditi na ovome, bacit ću ga
- za tebe
- da vidimo što imaš hvala
- dobro znate što je agregat da
- jedna od dvije funkcije koje mogu ignorirati
- skriveni ili filtrirani redovi sada su agregirani
- je izumljen u programu Excel 2010 prije
- agregat smo imali subtotalnu funkciju
- a subtotalna funkcija također može
- zanemari skrivene retke ili filtrirane retke sada
- isti se problem javlja s međuzbrojem kao
- s agregatom ako odaberem funkciju 9
- da ćemo samo izbjegavati brojati filtrirano
- redaka ako odaberem 109 koji će ignorirati
- skriveni i filtrirani redovi pa bih 109
- volio to raditi, ali pogodi što je ovo
- subtotal funkcija nailazi na isto
- problem kao zbirna referenca
- argument znači da ne možete imati niz
- pa tamo što radimo mi smo
- koristit ću funkciju offset za
- istodobno dobiti svaki pojedini redak
- koji će u osnovi stvoriti podzbroj
- pojedinačni pomoćni alati i ovaj trik I
- učio prije godina iz vrlo
- Oh, oglasna ploča sada je to nadoknadila
- treba početnu točku pa idem
- kliknite prvu zareznu ćeliju, a zatim
- pomak ide prema dolje ili prema gore za određeni broj
- redaka kako bih dobio određenu vrijednost I'm
- reći samo kao primjer za vidjeti
- kako ova funkcija radi siđite šest redova
- stupci sa zarezom, to je koliko redaka imate
- želimo oduzeti naš oglas koji ne želimo
- oduzmi ili dodaj bilo koji pa ću ići
- ostavite prazno
- sad primjetiti da već imam neke
- redovi su se sakrili tamo tri reda tako pomaknuto
- ako mu kažem da padne šest od jednog
- dva tri četiri
- pet i šest pa bi trebao ići pomak
- i dobivanje tih 30 u Selbyju 16 ako bih
- istaknite ga i pritisnite tipku f9 što mogu
- uvjeri se da to čini točno
- da
- control-z, ali ono što stvarno želim
- učiniti je istodobno dobiti svaki pojedinačni
- vrijednost tako da u redovima zabavlja funkciju retka
- istaknite sve od deset do dvadeset
- nine close parenthesis if I highlight
- this row which is now doing a function
- argument array operation will just
- deliver an array of the numbers ten to
- twenty nine so f9 there we go ah but we
- really want to tell offset to go down
- zero one two so it can get offset zero
- offset one and so on if I can subtract
- 10 from all of these I'll have exactly
- what I want
- zero one two three and so on control-z
- well now I simply minus the row of the
- very first cell in that range close
- parenthesis now in the rows argument if
- I f9 there's an array of all the
- positions offset needs to offset to get
- each item individually
- control-z that is an array in row so it
- will force offset to deliver exactly one
- cell for each item in that range and
- that'll be different than just the
- complete range which subtotal cannot
- handle comma columns we don't need any
- so close parenthesis that hole offset if
- I f9 look at that right now it's
- delivering every single cell including
- right there is three items that are
- hidden I can see them 30 40 30 but
- that's because I haven't dumped this
- entire resultant array into subtotal
- control Z I come to the end closed
- parenthesis and now when I am nine watch
- this there we are 0 0 z
- because subtotal is ignoring the hidden
- or later we'll see that it will ignore
- filtered rows also now I would like to
- further eliminate some of these numbers
- for example the second number 50 we
- can't have that because it says no right
- there so I'm going to put this result in
- array inside of some product and then
- multiply this array times another array
- of zeros and ones that will represent
- yes that'll be the one no that'll be the
- zero
- control-z now I put it inside of some
- product that's array one I need to come
- to be in comma and highlight the column
- with the yeses equals and I have the
- criteria up here now if I click on array
- 2 + f9 trues and falses but some product
- can see those trues and falses so we
- need to convert them to ones and zeros
- ctrl z so I'm going to use any math
- operation to convert trues and falses to
- ones and zeros I'm gonna use double
- negative because in general that tends
- to be the fastest and most efficient now
- I could have taken that array and
- directly multiplied it by the first
- array then we would just have that
- multiplication in array 1 but I've
- chosen to have array number 1 there then
- some product will multiply times these
- ones and zeros f9 and we'll get exactly
- what we want notice right now 1 1 1
- means yes yes yes but those will be
- matched up with the zero zero zero from
- the subtotal for the hidden rows
- control-z now I can come to the end
- close parentheses control enter now I'm
- hiding over here if I come over and hide
- right-click hi I should get exactly a
- total of 50 and 10 control-z now if I
- turn on the filters with ctrl shift L
- now I can come up and filter just to
- show B and there we go
- 30 times 3 is 90 plus 10 is 100 so there
- after a great trick I learned years ago
- from the message board
- subtotal with offset to get our some ifs
- which is really some product with a
- criteria here to add only the filtered
- rows and one last thing about offset
- remember we were talking about aggregate
- and subtotal couldn't handle an array
- operation right there but we do have
- some array operations inside of offset
- offset delivers something that Excel can
- interpret as a reference it works to
- disguise the array operation as a
- reference that in our case subtotal can
- understand all right I'm gonna throw it
- back over to Oh Mike that is
- one awesome formula I wonder I couldn't
- come up with it there alive at the
- seminar I was headed in completely the
- wrong direction but well you've been
- working on that I'm still absolutely
- convinced that aggregate there's some
- way that I can use aggregate to solve
- this but if I can't use an array here
- because this is five and not one of the
- ones higher than twelve then I still
- feel like there should be some way to do
- this and check this out here's what I'm
- gonna do is I'm gonna use a helper
- column and you know as I started think
- about this one this is this is a lot
- like finding out if the refrigerator
- light turns off when you close the door
- all right you're not going to know but
- what I'm gonna do is I'm gonna add it
- and help her column with an aggregate
- function and that aggregate function is
- going to sum ignore the hidden rows and
- what am I gonna sum I'm gonna sum just
- this row just this one row and we will
- copy that down all right now the big
- question is what is the answer to this
- when I filter to just the B's what is
- the answer
- are those hidden rows returning zero or
- not well I don't know how we can find
- out well actually I do know how we can
- find out because we have a secret way to
- look inside that sound all right so
- here's what I'm doing I'm a press
- control ditto to bring that exact
- formula down and then instead of
- pointing to column B I want to point at
- column C like that
- right and we'll press ENTER and then
- apply a filter all right and let's see
- 30 plus 30 plus 30 plus 10 100 it's
- working so apparently in those hidden
- rows the rows we can't see like for
- example row 7 here we could do down here
- equals C 7 aggregate is returning 0
- because it that hidden row that row
- itself is hidden all right there you go
- kind of cheating with that helper column
- but at least it's something that I can
- understand and probably even knock out
- again without having to look at the
- formula all right wrap up from today
- question from John do a summits that
- only has the visible cells my first pass
- I was trying to pass an array into the
- aggregate function but that fails and
- Mike came up with an awesome solution
- use the offset function to point each
- row use the subtotal to see that rows
- hidden or not and then some product to
- multiply you know is it yes times the
- result from the subtotal my second try
- it's like checking to see if the
- refrigerator like goes out once the door
- zatvara Morao sam pomoći svakoj njezinoj kolumni
- redak koji kaže agregat stanice
- u ovom retku kaže "zanemari bilo koji skriveni redak"
- pa će se to promijeniti na 0 na stijeni
- skriveni, a zatim usmjerite zbroj ako je to
- stupac pa eto vam hvala
- navrativši ćemo vidjeti sljedeći put za
- još jedan duel excel podcast gosp.
- Excel i Excel su zabavni
Preuzmi datoteku
Preuzmite datoteku uzorka ovdje: Duel187.xlsx
