SUMIF दृश्यमान सेल - एक्सेल टिप्स

विषय - सूची

वीडियो देखेंा

एक मापदंड का मिलान करने वाली * दृश्यमान * कोशिकाओं को योग करने के लिए एक SUMIF का उपयोग कैसे करें। उदाहरण के लिए, उन सभी अभिलेखों का योग करें जहाँ क्षेत्र = "पूर्व", लेकिन उन पंक्तियों को अनदेखा करें जो पहले से छिपी हुई हैं। आप उस समारोह में आश्चर्यचकित होंगे जो दिन बचाता है।

वीडियो देखेंा

जॉन से प्रश्न: एक ऐसा SUMIFS करें जो केवल दृश्य कोशिकाओं को जोड़ता है
बिल की पहली कोशिश: AGGREGATE फ़ंक्शन में एक सरणी पास करें - लेकिन यह विफल रहता है
माइक का भयानक समाधान:
SUBTOTAL या AGGREGATE किसी सरणी को स्वीकार नहीं कर सकता है
लेकिन आप सरणी को संसाधित करने और परिणाम को भेजने के लिए OFFSET का उपयोग कर सकते हैं।
यह पता लगाने के लिए कि पंक्ति YES है या नहीं, SUMPRODUCT का उपयोग करें।
बिल की दूसरी कोशिश यह देखने के लिए की तरह है कि क्या दरवाजा बंद होने पर रेफ्रिजरेटर की रोशनी चली जाएगी:
प्रत्येक पंक्ति में एक एकल सेल के AGGREGATE के साथ एक सहायक स्तंभ जोड़ें
उस कॉलम पर SUMIFS इंगित करें

ऑटो-जनरेट ट्रांसक्रिप्ट

अरे वापस स्वागत है यह दूसरे के लिए समय है
एक्सेल पॉडकास्ट द्वंद्वयुद्ध मैं बिल जेलन हूँ
माइक इरविन द्वारा शामिल होने से
एक्सेल से मजेदार है यह हमारा एपिसोड है
187 अगर कुछ ठीक दिखाई दे रहा है
वापस स्वागत है मैं एक संगोष्ठी में कर रहा था
फोर्ट मायर्स फ्लोरिडा कल और जॉन
दर्शकों में था और वह एक था
प्रश्न उन्होंने कहा कि यदि आप एक योग कर सकते हैं
यह केवल दृश्यमान पंक्तियों को ठीक करता है
यहीं हमारे डेटाबेस और कुछ अगर
यह काफी सरल है
देखो ३ look५ से 3 to४ देखें कि क्या जवाब हाँ है
अगर यह इसी कोशिकाओं को जोड़ रहा है
बी 5 से 24 तक सभी काम करते हैं लेकिन तब
जॉन एक फिल्टर लगा रहा था इसलिए हमने ए
यहाँ पर श्रेणी और डेटा बस चलो
जल्दी से लागू करने के लिए ऑटो फ़िल्टर का उपयोग करें
फ़िल्टर करें तो हमारे सभी बी सेल हैं
और हम केवल हां कोशिकाओं को जोड़ना चाहते हैं
बी कोशिकाएं हैं तो यह होनी चाहिए
100 के लिए कुछ है, लेकिन यह सूत्र नहीं है
काम तो यह है कि हम कैसे कर सकते हैं
एक राशि यदि वह केवल दृश्यमान दिखती है
पंक्तियाँ और मैंने ठीक ही कहा है
केवल एक चीज जो मुझे पता है कि वह जा रही है
दृश्यमान पंक्तियों को अनदेखा करना समग्र है
सभी समुच्चय को कार्य करते हैं
फ़ंक्शन तो हम कुछ संख्या 9 वाले हैं
अल्पविराम और फिर यहाँ महत्वपूर्ण हिस्सा है
छिपी पंक्तियों को अनदेखा करें सब ठीक है और
तो मैं बस पाने के लिए कुछ रास्ता खोजने की जरूरत है
वहाँ एक सरणी और मैं उच्च पता है
संस्करण सब कुछ की तरह एक सरणी का उपयोग करते हैं
उस दूसरे तर्क के लिए 11 से ऊपर एक का उपयोग करें
सरणी लेकिन आप जानते हैं कि एक सरणी काम करेगी
यहाँ सब ठीक है तो मैं क्या कहना चाहता था
मैं करने जा रहा था मैं सब कहने वाला था
सही है कि हम इन सभी नंबरों को लेने वाले हैं
यहाँ और उन्हें एक बूलियन गुणा करें
इसलिए सभी शब्दों को एक पर देखें
एक 5 सभी हाँ और क्या है के बराबर है
होने वाला अच्छी तरह से इन जा रहे हैं
सही संख्या में हो और ये जा रहे हैं
trues और झूठा की एक श्रृंखला में मूल्यांकन
और फिर जब हम एक्सेल को गुणा करने के लिए मजबूर करते हैं
trues और falses एक संख्या में कई बार होते हैं
1 के माध्यम से हो जाते हैं
सब कुछ ठीक है तो यह हाँ नहीं कहता है
कुछ भी है कि 0 का मूल्यांकन करने जा रहा है
करता है कि हाँ का मूल्यांकन करने जा रहा है
बिक्री ठीक है और फिर हम बस
एक ctrl शिफ्ट एंटर करना होगा
तो मेरे भगवान यह काम नहीं करता है मैं शायद नहीं
सिर्फ एक प्रविष्टि आपको बताती है कि मैं माइक क्या हूं
मैं इस पर काम कर रहा हूँ मैं इसे फेंक देते हैं
तेरे लिए
let's see what you have thanks
well you know what aggregate yes that's
one of two functions that can ignore
hidden or filtered rows now aggregate
was invented in Excel 2010 before
aggregate we had the subtotal function
and the subtotal function can also
ignore hidden rows or filtered rows now
the same problem occurs with subtotal as
with aggregate if I choose function 9
that we'll just avoid counting filtered
rows if I choose 109 that will ignore
hidden and filtered rows so 109 I would
like to do that but guess what this
subtotal function runs into the same
problem as the aggregate reference
argument means you cannot have an array
operation there so what do we do we're
gonna use the offset function to
simultaneously get each individual row
which subtotal will in essence create
individual sub tools and this trick I
learned years ago froms very
Oh message board now offset it
needs a starting point so I'm going to
click in the first cell comma and then
offset goes down or up a certain number
of rows to get a particular value I'm
going to say just as an example to see
how this function works go down six rows
comma columns that's how many rows you
want to subtract our ad we don't want to
subtract or add any so I'm going to
leave it blank
now offset notice I already have some
rows hid in there three rows so offset
if I tell it to go down six out of a one
two three four
five and six so offset should be going
and getting that 30 in Selby 16 if I
highlight it and hit the f9 key I can
see sure enough that it's doing exactly
that
control-z but what I really want it to
do is simultaneously get every single
value so in rows amuse the row function
highlight all the way from ten to twenty
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
बंद करने के लिए मुझे प्रत्येक को उसके कॉलम की मदद करनी थी
पंक्ति जो सेल का एक समुच्चय कहती है
इस पंक्ति में किसी भी छुपी हुई पंक्ति को अनदेखा करें
ताकि चट्टान पर 0 में बदल जाए
छिपा हुआ है और उसके बाद योग को इंगित करें
स्तंभ अच्छी तरह से आप इसके लिए धन्यवाद है
द्वारा रोकना हम अगली बार देखेंगे
श्री से एक और द्वंद्वयुद्ध एक्सेल पॉडकास्ट।
एक्सेल और एक्सेल मजेदार है

फ़ाइल डाउनलोड करें

यहाँ नमूना फ़ाइल डाउनलोड करें: Duel187.xlsx

दिलचस्प लेख...