Ορατά κελιά SUMIF - Συμβουλές για το Excel

Πώς να χρησιμοποιήσετε ένα SUMIF για να αθροίσετε τα * ορατά * κελιά που ταιριάζουν με κριτήρια. Για παράδειγμα, αθροίστε όλες τις εγγραφές όπου η Περιοχή = "Ανατολή", αλλά αγνοήστε τις σειρές που είναι ήδη κρυμμένες. Θα εκπλαγείτε με τη λειτουργία που σώζει την ημέρα.

Δες το βίντεο

• Ερώτηση από τον Jon: Κάντε ένα SUMIFS που προσθέτει μόνο τα ορατά κελιά
• Πρώτη δοκιμή του Bill: Περάστε έναν πίνακα στη συνάρτηση AGGREGATE - αλλά αυτό αποτυγχάνει
• Η φοβερή λύση του Mike:
• Το SUBTOTAL ή το AGGREGATE δεν μπορεί να δεχτεί έναν πίνακα
• Αλλά μπορείτε να χρησιμοποιήσετε το OFFSET για να επεξεργαστείτε έναν πίνακα και να στείλετε τα αποτελέσματα στο SUBTOTAL.
• Χρησιμοποιήστε το SUMPRODUCT για να μάθετε αν η σειρά είναι ΝΑΙ ή όχι.
• Η δεύτερη δοκιμή του Μπιλ είναι σαν να ελέγχεις για να δεις αν σβήνει το φως του ψυγείου όταν κλείνει η πόρτα:
• Προσθέστε μια βοηθητική στήλη με AGGREGATE ενός μεμονωμένου κελιού σε κάθε σειρά
• Στρέψτε το SUMIFS σε αυτήν τη στήλη

Αυτόματη δημιουργία αντιγράφου

• γεια καλωσορίσατε πίσω, ήρθε η ώρα για άλλο
• dueling Excel podcast Είμαι ο Bill Jelen
• να ενταχθούν από τον Mike Irvin
• από το Excel είναι διασκεδαστικό αυτό είναι το επεισόδιο μας
• 187 κάποια αν το ορατό τριαντάφυλλο εντάξει
• Καλώς ήλθατε πίσω έκανα ένα σεμινάριο στο
• Το Fort Myers Florida χθες και ο John
• ήταν στο κοινό και είχε ένα
• ερώτηση είπε πώς μπορείτε να κάνετε ένα ποσό εάν
• αθροίζει μόνο τις ορατές σειρές
• εδώ είναι η βάση δεδομένων μας και μερικά εάν
• είναι αρκετά απλό
• Κοιτάξτε 385 έως 824 δείτε αν η απάντηση είναι ναι
• αν προσθέσει τα αντίστοιχα κελιά
• από το b5 έως το 24 ότι όλα λειτουργούν εκτός από τότε
• Ο Τζον εφάρμοζε ένα φίλτρο, έτσι είχαμε ένα
• κατηγορία εδώ και τα δεδομένα ας δούμε
• χρησιμοποιήστε το αυτόματο φίλτρο για να εφαρμόσετε γρήγορα το
• φιλτράρετε έτσι υπάρχουν όλα τα κελιά Β
• και θέλουμε να προσθέσουμε μόνο τα κελιά ναι
• που είναι Β κύτταρα τότε αυτό πρέπει να είναι
• κάτι στο 100 αλλά αυτός ο τύπος δεν είναι
• δουλεύοντας έτσι το ερώτημα είναι πώς μπορούμε να κάνουμε
• ένα άθροισμα αν αυτό φαίνεται μόνο στο ορατό
• σειρές και είπα καλά καλά
• το μόνο πράγμα που ξέρω ότι πρόκειται να
• αγνοήστε τις ορατές σειρές είναι το σύνολο
• λειτουργούν εντάξει το σύνολο
• Λοιπόν, θα έχουμε έναν αριθμό 9
• κόμμα και στη συνέχεια εδώ είναι το σημαντικό μέρος
• αγνοήστε τις κρυφές σειρές εντάξει και
• τότε πρέπει απλώς να βρω κάποιο τρόπο για να πάρω
• ένας πίνακας εκεί και ξέρω το υψηλότερο
• Οι εκδόσεις χρησιμοποιούν έναν πίνακα όπως τα πάντα
• παραπάνω 11 για αυτό το δεύτερο όρισμα χρησιμοποιήστε ένα
• πίνακας αλλά γνωρίζετε ότι θα λειτουργήσει ένας πίνακας
• εδώ εντάξει, έτσι αυτό που ήθελα να πω
• Επρόκειτο να κάνω, θα έλεγα όλα
• σωστά θα πάρουμε όλους αυτούς τους αριθμούς
• εδώ και πολλαπλασιάστε τους φορές boolean
• οπότε κοιτάξτε όλες τις λέξεις πάνω από ένα
• ένα 5 ίσο με ναι εντάξει και τι είναι
• θα συμβεί είναι καλά αυτά που πρόκειται να
• να είναι αριθμοί σωστοί και αυτοί πρόκειται
• αξιολόγηση σε μια σειρά από αλήθειες και ψεύδη
• και μετά όταν αναγκάζουμε το Excel να πολλαπλασιαστεί
• οι αλήθειες και τα ψέματα φορές έναν αριθμό
• τα περάσματα γίνονται 1
• εντάξει οτιδήποτε δεν λέει ναι
• πρόκειται να αξιολογήσει σε 0 οτιδήποτε άλλο
• το ναι θα το αξιολογήσει
• οι πωλήσεις εντάξει και μετά απλά
• πρέπει να κάνετε μια αλλαγή ctrl enter
• ο Θεός μου δεν λειτουργεί, ίσως όχι
• απλώς ένα enter, πες τι είμαι ο Mike
• θα συνεχίσω να δουλεύω σε αυτό
• για σενα
• ας δούμε τι έχετε ευχαριστώ
• καλά ξέρετε τι συνολικά ναι είναι αυτό
• μία από τις δύο λειτουργίες που μπορούν να αγνοηθούν
• οι κρυφές ή φιλτραρισμένες σειρές συγκεντρώνονται τώρα
• εφευρέθηκε στο Excel 2010 πριν
• συγκεντρωτικά είχαμε τη συνάρτηση υποσυνόλου
• και η συνάθροιση συνάρτησης μπορεί επίσης
• αγνοήστε τις κρυφές σειρές ή τις φιλτραρισμένες σειρές τώρα
• το ίδιο πρόβλημα παρουσιάζεται με το σύνολο
• με αθροιστικό εάν επιλέξω τη συνάρτηση 9
• ότι απλώς θα αποφύγουμε τη μέτρηση φιλτραρισμένη
• σειρές αν επιλέξω 109 που θα αγνοήσουν
• κρυφές και φιλτραρισμένες σειρές, έτσι 109 θα το έκανα
• μου αρέσει να το κάνω αυτό, αλλά μαντέψτε τι είναι αυτό
• η συνάρτηση υποσύνολων εκτελείται στην ίδια
• πρόβλημα ως η συνολική αναφορά
• όρισμα σημαίνει ότι δεν μπορείτε να έχετε πίνακα
• λειτουργία εκεί, λοιπόν, τι κάνουμε είμαστε
• θα χρησιμοποιήσω τη λειτουργία μετατόπισης για
• ταυτόχρονα λάβετε κάθε μεμονωμένη σειρά
• το οποίο θα δημιουργήσει ουσιαστικά ένα σύνολο
• επιμέρους δευτερεύοντα εργαλεία και αυτό το τέχνασμα I
• έμαθε πριν από χρόνια από πολύ
• 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 στο βράχο είναι
• κρυμμένο και, στη συνέχεια, δείξτε το άθροισμα εάν αυτό
• στήλη καλά εκεί το έχετε ευχαριστώ για
• σταματώντας θα δούμε την επόμενη φορά για
• ένα άλλο dueling excel podcast από τον κ.
• Το Excel και το Excel είναι διασκεδαστικό

Λήψη αρχείου

Κατεβάστε το δείγμα αρχείου εδώ: Duel187.xlsx