Η λειτουργία του SUMMESLE, καθώς και τα SUMMESLES με δύο κριτήρια

1. Αναζήτηση βάσει ετικετών

Φανταστείτε έναν πίνακα στον οποίο τα ονόματα των τμημάτων (ή λογαριασμών, ή κάτι άλλο) παρατίθενται σε σειρές στη σειρά.

Συγκεντρώστε τα κελιά με κριτήριο
Είναι απαραίτητο να υπολογίσετε το συνολικό ποσό για κάθε τμήμα. Πολλοί το κάνουν με ένα φίλτρο και γράφοντας με πένες στα κελιά.
Αν και μπορεί να γίνει εύκολα και απλά με μία μόνο λειτουργία - SUMMESLI .
SUMMESLES (SUMIF) -Μειώνει τα κελιά που ικανοποιούν μια δεδομένη κατάσταση (μπορεί να οριστεί μόνο μία προϋπόθεση). Αυτή η λειτουργία μπορεί επίσης να χρησιμοποιηθεί αν ο πίνακας χωρίζεται σε στήλες κατά περιόδους (μηνιαίως, σε κάθε μήνα, τρεις στήλες - Έσοδα | Έξοδα | Διαφορά) και πρέπει να υπολογίσετε το συνολικό ποσό για όλες τις περιόδους μόνο με Έσοδα, Έξοδα και Διαφορά.

Υπάρχουν συνολικά τρία επιχειρήματα για το SUMMESLI: Range , Criterion , Range_Summing .
= SUMMESLE (A1: A20000, A1, B1: B20000)
= SUMIF (Α1: Α20000, Α1, Β1: Β20000)

• Εύρος (A1: A20000) - υποδεικνύει την εμβέλεια με τα κριτήρια. Δηλαδή Η στήλη στην οποία πρέπει να αναζητήσετε την τιμή που υποδεικνύεται από το επιχείρημα Criterion .
• Το κριτήριο (A1) είναι η τιμή (κείμενο ή αριθμητική, καθώς και η ημερομηνία) που πρέπει να βρεθεί στην περιοχή . Μπορεί να περιέχει τους χαρακτήρες μπαλαντέρ "*" και "?". Δηλαδή προσδιορίζοντας το κριτήριο "* μάζα *" για να συνοψίσουμε τις τιμές στις οποίες εμφανίζεται η λέξη "μάζα". Ταυτόχρονα, η λέξη "μάζα" μπορεί να συμβεί οπουδήποτε στο κείμενο ή μπορεί να υπάρχει μόνο μία λέξη σε μια κυψέλη. Και προσδιορίζοντας τη "μάζα", όλες οι αξίες που αρχίζουν με "μάζα" θα συνοψιστούν. ";" - αντικαθιστά μόνο έναν χαρακτήρα, δηλ. με την ένδειξη "mas a a" μπορείτε να συνοψίσετε τις γραμμές με την τιμή "mass" και την τιμή "mask" κ.λπ.
  Αν το κριτήριο είναι γραμμένο σε ένα κελί και εξακολουθείτε να χρειαστεί να χρησιμοποιήσετε χαρακτήρες μπαλαντέρ, τότε μπορείτε να δημιουργήσετε μια σύνδεση με αυτό το κελί προσθέτοντας το απαραίτητο. Ας υποθέσουμε ότι πρέπει να συνοψίσουμε τις τιμές που περιέχουν τη λέξη "σύνολο". Η λέξη "σύνολο" γράφεται στο κελί A1, ενώ στη στήλη Α μπορεί να υπάρχουν διάφορες τιμές ορθογραφίας που περιέχουν τη λέξη "σύνολο": "σύνολα για τον Ιούνιο", "σύνολα για τον Ιούλιο", "σύνολα για τον Μάρτιο". Ο τύπος τότε θα πρέπει να μοιάζει με αυτό:
  = ΚΑΛΟΚΑΙΡΙΝΑ (A1: A20000, "*" & A1 & "*", B1: B20000)
  "*" & A1 & "*" - το σύμβολο & amp; semper (ampersand) συνδυάζει διάφορες τιμές σε μία. Δηλαδή το αποτέλεσμα θα είναι "* αποτέλεσμα *".
  Για να κατανοήσετε καλύτερα την αρχή του τρόπου με τον οποίο λειτουργούν οι τύποι, είναι προτιμότερο να χρησιμοποιήσετε το εργαλείο Υπολογισμός φόρμουλας : Πώς μπορείτε να δείτε τα βήματα για τον υπολογισμό των τύπων
  Όλα τα κειμενικά κριτήρια και τα κριτήρια με λογικά και μαθηματικά σημεία πρέπει να περικλείονται σε διπλά εισαγωγικά (= SUMMESLI (A1: A20000, "total", B1: B20000)). Εάν το κριτήριο είναι ένας αριθμός, τα εισαγωγικά δεν απαιτούνται. Αν θέλετε να βρείτε ερωτηματικό ή αστερίσκο άμεσα, πρέπει να βάλετε ένα tilda (~) μπροστά του.
  Σχετικά με το tilde και τα χαρακτηριστικά του μπορούν να βρεθούν σε αυτό το άρθρο: Πώς να αντικαταστήσει / αφαιρέσει / βρει αστερίσκο;
• Sum_Range (B1: B20000) (προαιρετικό όρισμα) - καθορίζει το εύρος των αθροισμάτων ή αριθμητικών τιμών.

Πώς λειτουργεί: η λειτουργία αναζητά το Range για την τιμή που καθορίζεται από το όρισμα Criterion και όταν βρεθεί μια αντιστοίχιση, αθροίζει τα δεδομένα που υποδεικνύονται από το πεδίο Range_Amount. Δηλαδή εάν έχουμε ένα όνομα τμήματος στη στήλη Α και ένα ποσό στη στήλη Β, τότε προσδιορίζοντας το τμήμα ανάπτυξης ως το κριτήριο θα έχει ως αποτέλεσμα το άθροισμα όλων των τιμών της στήλης Β, απέναντι από τις οποίες βρίσκεται το τμήμα ανάπτυξης στην στήλη Α. Στην πραγματικότητα, το SumArrangement μπορεί να μην είναι το ίδιο μέγεθος με το εύρος Range και αυτό δεν θα προκαλέσει σφάλμα της ίδιας της λειτουργίας. Ωστόσο, κατά τον ορισμό των αθροισμάτων των κελιών, το αριστερό κυψέλης του αντικειμένου Range_Amount θα χρησιμοποιηθεί ως αφετηρία για την άθροιση και στη συνέχεια τα αθροίσματα των κυψελών που αντιστοιχούν στο μέγεθος και το σχήμα προς το εύρος της περιοχής.

Ορισμένες λειτουργίες
Το τελευταίο όρισμα της συνάρτησης (Sum_And_Band: B1: B20000) είναι προαιρετικό. Αυτό σημαίνει ότι δεν μπορεί να καθοριστεί. Αν δεν το καθορίσετε, η συνάρτηση θα προσθέσει τις τιμές που καθορίζονται από το όριο εύρους . Τι είναι αυτό. Για παράδειγμα, πρέπει να πάρετε το άθροισμα μόνο των αριθμών που είναι μεγαλύτεροι από το μηδέν. Στη στήλη Α του ποσού. Στη συνέχεια, η λειτουργία θα έχει την εξής μορφή:
= ΚΑΛΟΚΑΙΡΙ (A1: A20000; "> 0")

Τι πρέπει να ληφθεί υπόψη: το εύρος τιμών και η περιοχή πρέπει να είναι ίσες στον αριθμό των γραμμών. Διαφορετικά, μπορείτε να πάρετε λάθος αποτέλεσμα. Τέλος, αν μοιάζει με τους τύπους που έχω δώσει: το εύρος και το εύρος των αθροισμάτων ξεκινάει από τη μία γραμμή και έχει τον ίδιο αριθμό γραμμών: A1: A20000; Β1: Β20000

Σύνοψη σε δύο ή περισσότερα κριτήρια
Αλλά τι να κάνετε όταν τα κριτήρια για την αθροιστική 2 και περισσότερο; Ας υποθέσουμε ότι πρέπει να συγκεντρώσετε μόνο τα ποσά που ανήκουν σε ένα τμήμα και μόνο για μια συγκεκριμένη ημερομηνία. Οι ευχαριστημένοι ιδιοκτήτες των εκδόσεων γραφείου 2007 και άνω μπορούν να χρησιμοποιήσουν τη λειτουργία SUMMESLIMN:
= SUMMESLIMN ($ C $ 2: $ C $ 50 $ A $ 2 $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $
$ C $ 2: $ C $ 50 - range_summing. Το πρώτο επιχείρημα καθορίζει το φάσμα των κυττάρων που περιέχουν τα ποσά που θα συλλεχθούν σε ένα.
$ A $ 2: $ A $ 50, $ B $ 2: $ B $ 50 - Κριτήρια_εξόδου. Καθορίζει την περιοχή των κελιών στα οποία θέλετε να αναζητήσετε ένα κριτήριο αντιστοίχισης.
$ I $ 3, $ H8 - κριτήριο. Εδώ, όπως και στο SUMMESLI, οι χαρακτήρες μπαλαντέρ * και ? Επιτρέπονται . και λειτουργούν με τον ίδιο τρόπο.

Ειδικά χαρακτηριστικά προσδιορισμού των επιχειρημάτων: Κατ 'αρχάς, καθορίζεται το εύρος κριτηρίων (αριθμημένα), τότε η τιμή (κριτήριο) υποδεικνύεται απευθείας στο ερωτηματικό, το οποίο πρέπει να βρεθεί σε αυτό το εύρος - $ A $ 2: $ A $ 50; $ I $ 3. Και τίποτε άλλο. Δεν πρέπει να προσπαθήσετε να καθορίσετε πρώτα όλα τα εύρη και, στη συνέχεια, τα κριτήρια για αυτά - η λειτουργία θα δώσει είτε ένα σφάλμα, είτε δεν θα συνοψίσει τι είναι απαραίτητο.

Όλες οι συνθήκες συγκρίνονται σύμφωνα με την αρχή Ι. Αυτό σημαίνει ότι εάν πληρούνται όλες οι αναφερόμενες προϋποθέσεις. Εάν δεν πληρούται τουλάχιστον μία προϋπόθεση, η λειτουργία παραλείπει τη γραμμή και δεν προσθέτει τίποτα.
Όσο για τα SUMMERS, οι σειρές άθροισης και κριτηρίων πρέπει να είναι ίσες στον αριθμό των σειρών.

Επειδή Το SUMMESLIMN εμφανίστηκε μόνο σε εκδόσεις του Excel, αρχής γενομένης από το 2007, τότε πώς μπορεί να είναι δυστυχισμένοι χρήστες προηγούμενων εκδόσεων σε τέτοιες περιπτώσεις; Πολύ απλή: χρησιμοποιήστε μια άλλη λειτουργία - SUMPRODUCT. Δεν θα ζωγραφίσω τα επιχειρήματα, γιατί Υπάρχουν πολλοί από αυτούς και είναι όλοι συστοιχίες αξιών. Αυτή η λειτουργία πολλαπλασιάζει τις συστοιχίες που υποδεικνύονται από τα επιχειρήματα. Θα προσπαθήσω να περιγράψω τη γενική αρχή της χρήσης αυτής της συνάρτησης για να συνοψίσω τα δεδομένα σε διάφορες συνθήκες.
Για να λυθεί το πρόβλημα αθροίσεως με διάφορα κριτήρια, η λειτουργία θα έχει την εξής μορφή:
= SUMPRODUCT (($ A $ 2: $ A $ 50 = $ I $ 3) * ($ B $ 2: $ B $ 50 = H5) $ $ $ $ $ $ $ $ 50
$ A $ 2: $ A $ 50 - εύρος ημερομηνιών. $ I $ 3 είναι η ημερομηνία του κριτηρίου για την οποία είναι απαραίτητο να συνοψίσουμε τα δεδομένα.
$ B $ 2: $ B $ 50 - τα ονόματα των τμημάτων. H5 - το όνομα του τμήματος, τα στοιχεία που πρέπει να συνοψίζονται.
$ C $ 2: $ C $ 50 - εύρος με ποσά.

Αναλύουμε τη λογική, γιατί για πολλούς, θα είναι εντελώς ασαφές μόνο με την εξέταση αυτής της λειτουργίας. Εάν μόνο επειδή στη βοήθεια αυτή η εφαρμογή δεν περιγράφεται. Για μεγαλύτερη αναγνωσιμότητα, μειώστε το μέγεθος των περιοχών:
= SUMPRODUCT (($ A $ 2: $ A $ 5 = $ I $ 3) * ($ B $ 2: $ B $ 5 = H5) $ $ $ $ $ $ $ 5
Έτσι, οι εκφράσεις ($ A $ 2: $ A $ 5 = $ I $ 3) και ($ B $ 2: $ B $ 5 = H5) είναι λογικές και επιστρέφουν συστοιχίες λογικών FALSE και TRUE. TRUE εάν το κελί του εύρους $ A $ 2: $ A $ 5 είναι ίσο με την τιμή του κελί $ I $ 3 και το κελί του εύρους $ B $ 2: $ B $ 5 είναι ίσο με την τιμή του κελιού H5. Δηλαδή έχουμε τα εξής:
= ΑΝΤΙΜΕΤΩΠΙΣΗ ({FALSE, TRUE, TRUE, FALSE} * {FALSE, FALSE, TRUE, FALSE}, $ C $ 2: $ C $ 50)
Όπως μπορείτε να δείτε, στην πρώτη σειρά υπάρχουν δύο αγώνες για την κατάσταση, και στη δεύτερη. Επιπλέον, αυτές οι δύο συστοιχίες πολλαπλασιάζονται (το σήμα πολλαπλασιασμού (*) είναι υπεύθυνο για αυτό). Όταν εμφανιστεί πολλαπλασιασμός, εμφανίζεται η σιωπηρή μετατροπή των συστοιχιών FALSE και TRUE στις αριθμητικές σταθερές 0 και 1 αντίστοιχα ({0, 1; 1; 0} * {0; 0; 1; 0}). Όπως γνωρίζετε, όταν πολλαπλασιάζεται με το μηδέν, έχουμε μηδέν. Και το αποτέλεσμα είναι μια ενιαία σειρά:
= SUMPRODUCT ({0; 0; 1; 0}; $ C $ 2: $ C $ 50)
Στη συνέχεια, ο πίνακας {0; 0; 1; 0} πολλαπλασιάζεται με μια σειρά αριθμών στην περιοχή $ C $ 2: $ C $ 50:
= SUMPRODUCT ({0; 0; 1; 0}; {10; 20; 30; 40})
Και ως αποτέλεσμα, παίρνουμε 30. Τι χρειαζόμασταν - παίρνουμε μόνο το ποσό που ικανοποιεί το κριτήριο. Εάν υπάρχουν περισσότερα από ένα ποσά που ικανοποιούν το κριτήριο, τότε θα συνοψιστούν.

Πλεονέκτημα του SUMMYROIZV
Αν τα επιχειρήματα έχουν το σύμβολο συν αντί για το σύμβολο πολλαπλασιασμού:
($ A $ 2: $ A $ 5 = $ I $ 3) + ($ B $ 2: $ B $ 5 = H5)
τότε οι συνθήκες θα συγκριθούν σύμφωνα με την αρχή OR: δηλ. τα συνολικά ποσά θα συνοψιστούν εάν πληρούται τουλάχιστον μία προϋπόθεση: είτε $ A $ 2: $ A $ 5 ισούται με την κυτταρική τιμή $ I $ 3 ή κυψέλη εύρους $ B $ 2: $ B $ 5 ισούται με την τιμή κυψέλης H5.
Αυτό είναι το πλεονέκτημα του SUMMPRODUCT πάνω από το SUMMESLIMN. Το SUMMESLIMN δεν μπορεί να συγκεντρώσει τιμές σύμφωνα με την αρχή της ΑΑ, μόνο σύμφωνα με την αρχή ΑΝ (πρέπει να πληρούνται όλες οι προϋποθέσεις).

Μειονεκτήματα
Το SUMPRODUCT δεν μπορεί να χρησιμοποιήσει χαρακτήρες wildcards * και?. Είναι δυνατόν να χρησιμοποιηθούν με μεγαλύτερη ακρίβεια, αλλά δεν θα θεωρηθούν ως ειδικοί χαρακτήρες, αλλά ως αστερίσκος και ερωτηματικό. Νομίζω ότι αυτό είναι ένα σημαντικό μειονέκτημα. Και παρόλο που αυτό μπορεί να παρακαμφθεί, χρησιμοποιώ άλλες λειτουργίες μέσα στο SUMPRODUCT - θα ήταν ακόμα ωραίο αν η λειτουργία θα μπορούσε κάπως να χρησιμοποιήσει μπαλαντέρ.

Στο παράδειγμα θα βρείτε μερικά παραδείγματα λειτουργιών για την καλύτερη κατανόηση αυτού που γράφτηκε παραπάνω.

Κατεβάστε ένα παράδειγμα

Ποσό με διάφορα κριτήρια (41,5 KiB, 10,477 λήψεις)

Δείτε επίσης:
Σύνοψη των κελιών με χρώμα πλήρωσης
Σύνοψη των κελιών με χρώμα γραμματοσειράς
Σύνοψη των κυττάρων με κυψέλη
Υπολογίστε την ποσότητα των κυττάρων με χρώμα πλήρωσης
Υπολογίστε την ποσότητα των κελιών κατά χρώμα γραμματοσειράς
Πώς να συνοψίσω τα δεδομένα από πολλά φύλλα, συμπεριλαμβανομένης της κατάστασης

{"Bottom bar": {"textstyle": "static", "textpositionstatic": "bottom", "textautohide": true, "textpositionmarginstatic" "texteffectasing": "easeOutCubic", "texteffectduration": 600, "texteffectslidedirection": "αριστερά", "texteffectslidedistance": 24, "textpositionmargintop" : 30, "texteffectdelay": 500, "texteffectseparate": false, "texteffect1": "slide", "texteffectslidedirection1": "δεξιά", "texteffectslidedistance1": 120, "texteffectasing1": "easeOutCubic", "texteffectduration1" , "texteffectdelay1": 1000, "texteffect2": "slide", "texteffectslidedirection2": "right", "texteffectslidedistance2" κείμενοcss ":" εμφάνιση: μπλοκ · θέση: απόλυτη · κορυφή: 0px · αριστερά: 0px · πλάτος: 100% · ύψος: 100% · χρώμα φόντου: # 333333 · αδιαφάνεια: 0,6 · φίλτρο: α lpha (αδιαφάνεια = 60) "," titlecss ":" εμφάνιση: μπλοκ? θέση: σχετική. γραμματοσειρά: έντονη 14px \ "Lucida Sans Unicode \", \ "Lucida Grande \", sans-serif, Arial; χρώμα: #fff; "," descriptioncss ":" εμφάνιση: μπλοκ? θέση: σχετική. γραμματοσειρά: 12px \ "Lucida Sans Unicode \", \ "Lucida Grande \", sans-serif, Arial; χρώμα: #fff; margin-top: 8px; "," buttoncss ":" εμφάνιση: μπλοκ? θέση: σχετική. περιθώριο κέρδους: 8px; "," texteffectresponsive ": true," texteffectresponsivesize ": 640," titlecssresponsive ":" font-size: 12px "," descriptioncssresponsive ":" display: none: important "," buttoncssresponsive " "", "addgooglefonts": false, "googlefonts": "", "textleftrightpercentforstatic": 40}}

Αναζήτηση βάσει ετικετών