Πώς να χρησιμοποιήσετε τη συνάρτηση SUM & IF αντ 'αυτού για συνάρτηση SUMPRODUCT ή SUMIFS στο Excel

Anonim

Σε αυτό το άρθρο, θα μάθουμε πώς να χρησιμοποιούμε τη συνάρτηση IF αντί της συνάρτησης SUMPRODUCT και SUMIFS στο Excel.

Σενάριο:

Με απλά λόγια, όταν δουλεύουμε με ένα πολύ διάσπαρτο σύνολο δεδομένων, μερικές φορές πρέπει να βρούμε το άθροισμα των αριθμών με ορισμένα κριτήρια. Για παράδειγμα, η εύρεση του αθροίσματος των μισθών σε ένα συγκεκριμένο τμήμα ή η ύπαρξη πολλαπλών κριτηρίων κατά την ημερομηνία, τα ονόματα, το τμήμα ή μπορεί ακόμη και να αριθμήσει δεδομένα, όπως μισθούς κάτω από την αξία ή ποσότητα πάνω από την αξία. Για αυτό συνήθως χρησιμοποιείτε τη συνάρτηση SUMPRODUCT ή SUMIFS. Αλλά δεν θα το πιστεύατε, εκτελείτε την ίδια λειτουργία με τη συνάρτηση IF της βασικής συνάρτησης Excel.

Πώς να λύσετε το πρόβλημα;

Πρέπει να σκέφτεστε πώς είναι δυνατόν να εκτελέσετε λογικές πράξεις σε πίνακες πίνακα χρησιμοποιώντας τη συνάρτηση IF. Η συνάρτηση IF στο excel είναι πολύ χρήσιμη. Θα σας οδηγήσει σε μερικές δύσκολες εργασίες στο Excel ή σε οποιαδήποτε άλλη γλώσσα κωδικοποίησης. Η συνάρτηση IF δοκιμάζει συνθήκες στον πίνακα που αντιστοιχεί στις απαιτούμενες τιμές και επιστρέφει το αποτέλεσμα ως πίνακα που αντιστοιχεί σε συνθήκες True ως 1 και False ως 0.

Για αυτό το πρόβλημα, θα χρησιμοποιήσουμε τις ακόλουθες συναρτήσεις:

  1. Συνάρτηση SUM
  2. Συνάρτηση IF

Θα απαιτήσουμε αυτές τις παραπάνω λειτουργίες και κάποια βασική αίσθηση λειτουργίας δεδομένων. Οι λογικές συνθήκες στους πίνακες μπορούν να εφαρμοστούν χρησιμοποιώντας λογικούς τελεστές. Αυτοί οι τελεστές λογικής λειτουργούν τόσο στο κείμενο όσο και στους αριθμούς. Παρακάτω είναι ο γενικός τύπος. { } Τα σγουρά σιδεράκια είναι το μαγικό εργαλείο για την εκτέλεση τύπων συστοιχιών με λειτουργία IF.

Γενικός τύπος:

{ = SUM (IF ((logic_1) * (logic_2) *… * (logic_n), sum_array)) }

Σημείωση: Για σγουρά σιδεράκια ( { } ) Χρήση Ctrl + Shift + Enter όταν εργάζεστε με πίνακες ή εύρη στο Excel. Αυτό θα δημιουργήσει Curly Braces στον τύπο από προεπιλογή. ΜΗΝ προσπαθείτε να κωδικοποιήσετε χαρακτήρες με σγουρά σιδεράκια.

Λογικό 1: δοκιμάζει την κατάσταση 1 στον πίνακα 1

Λογικό 2: δοκιμάζει τη συνθήκη 2 στον πίνακα 2 και ούτω καθεξής

sum_array: πίνακας, εκτελείται το άθροισμα λειτουργίας

Παράδειγμα:

Όλα αυτά μπορεί να προκαλούν σύγχυση στην κατανόηση. Έτσι, ας δοκιμάσουμε αυτόν τον τύπο εκτελώντας τον στο παράδειγμα που φαίνεται παρακάτω. Εδώ έχουμε δεδομένα για τα προϊόντα που παραδόθηκαν σε διαφορετικές πόλεις μαζί με αντίστοιχα πεδία κατηγορίας και ποσότητες. Εδώ έχουμε τα δεδομένα και πρέπει να βρούμε την ποσότητα των cookies που αποστέλλονται στη Βοστώνη όπου η ποσότητα είναι μεγαλύτερη από 40.

Ο πίνακας δεδομένων και ο πίνακας κριτηρίων εμφανίζονται στην παραπάνω εικόνα. Για σκοπούς κατανόησης χρησιμοποιήσαμε ονόματα εύρους για τις χρησιμοποιούμενες συστοιχίες. Τα ονομαζόμενα εύρη παρατίθενται παρακάτω.

Εδώ :

Πόλη που ορίζεται για τον πίνακα A2: A17.

Κατηγορία που ορίζεται για τον πίνακα B2: A17.

Ποσότητα που ορίζεται για τον πίνακα C2: C17.

Τώρα είστε έτοιμοι να λάβετε το επιθυμητό αποτέλεσμα χρησιμοποιώντας τον παρακάτω τύπο.

Χρησιμοποιήστε τον τύπο:

{ = SUM (IF ((City = "Boston") * (Category = "Cookies") * (Ποσότητα> 40), Ποσότητα)) }

Επεξήγηση:

  1. Πόλη = "Boston": ελέγχει τις τιμές στο εύρος της πόλης για να ταιριάζει με το "Boston".
  2. Κατηγορία = "Cookies": ελέγχει τις τιμές στο εύρος κατηγορίας που ταιριάζουν με τα "Cookies".
  3. Ποσότητα> 40: ελέγχει τις τιμές στην περιοχή Ποσότητα στο ma
  4. Ποσότητα είναι πίνακας όπου απαιτείται άθροισμα.
  5. Η συνάρτηση IF ελέγχει όλα τα κριτήρια και ο αστερίσκος char (*) πολλαπλασιάζει όλα τα αποτελέσματα του πίνακα.

= SUM (IF ({0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 1; 0; 0; 0}, {33; 87; 58; 38; 54; 51; 28; 36; 28; 44; 23; 27; 43; 42; 33; 30}))

  1. Τώρα η συνάρτηση IF επιστρέφει μόνο τις ποσότητες που αντιστοιχούν στο 1 και τα υπόλοιπα αγνοούνται.
  2. Η συνάρτηση SUM επιστρέφει το SUM.

Τώρα η ποσότητα που αντιστοιχεί στο 1 αθροίζεται μόνο για να πάρει το αποτέλεσμα.


Όπως μπορείτε να δείτε, η ποσότητα 43 επιστρέφεται αλλά υπάρχουν τρεις παραγγελίες μπισκότων που παραδίδονται στη "Βοστώνη" με ποσότητες 38, 36 και 43. Χρειαζόμασταν ένα άθροισμα ποσότητας όπου η ποσότητα ήταν πάνω από 40. Έτσι ο τύπος επιστρέφει 43 μόνο. Τώρα χρησιμοποιήστε άλλα κριτήρια για να λάβετε την SUM Ποσότητα για Πόλη: "Λος Άντζελες" & Κατηγορία: "Μπαρ" & Η ποσότητα να είναι μικρότερη από 50.

Χρησιμοποιήστε τον τύπο

{ = SUM (IF ((Πόλη = "Λος Άντζελες") * (Κατηγορία = "Μπαρ") * (Ποσότητα <50), Ποσότητα)) }

Όπως μπορείτε να δείτε, ο τύπος επιστρέφει τις τιμές 86 ως αποτέλεσμα. Ποιο είναι το άθροισμα 2 παραγγελιών που πληρούν τις προϋποθέσεις που έχουν ποσότητα 44 & 42. Αυτό το άρθρο, επεξηγεί τον τρόπο αντικατάστασης ενός ένθετου τύπου IF με ένα μόνο IF σε έναν τύπο πίνακα. Αυτό μπορεί να χρησιμοποιηθεί για τη μείωση της πολυπλοκότητας σε σύνθετους τύπους. Ωστόσο, το συγκεκριμένο πρόβλημα θα μπορούσε εύκολα να λυθεί με τη συνάρτηση SUMIFS ή SUMPRODUCT.

Χρήση της συνάρτησης SUMPRODUCT:

Η συνάρτηση SUMPRODUCT επιστρέφει το άθροισμα των αντίστοιχων τιμών στον πίνακα. Έτσι θα πάρουμε τους πίνακες να επιστρέφουν τις τιμές 1s a the true εντολή και 0s τις τιμές ψευδούς δήλωσης. Έτσι, το τελευταίο άθροισμα θα είναι αντίστοιχο όπου όλες οι δηλώσεις ισχύουν True.

Χρησιμοποιήστε τον τύπο:

= SUMPRODUCT ( - (Πόλη = "Βοστώνη"), - (Κατηγορία = "Μπισκότα"), - (Ποσότητα> 40), Ποσότητα)

-: λειτουργία που χρησιμοποιείται για τη μετατροπή όλων των TRUE σε 1s και False σε 0.

Η συνάρτηση SUMPRODUCT επανελέγχει το SUM της ποσότητας που επιστρέφεται από τη συνάρτηση SUM και IF που εξηγήθηκε παραπάνω.

Ομοίως για το δεύτερο παράδειγμα το αποτέλεσμα παραμένει το ίδιο.

Όπως μπορείτε να δείτε, η συνάρτηση SUMPRODUCT μπορεί να εκτελέσει την ίδια εργασία.

Εδώ είναι όλες οι σημειώσεις παρατήρησης σχετικά με τη χρήση του τύπου.

Σημειώσεις:

  1. Το sum_array στον τύπο λειτουργεί μόνο με αριθμούς.
  2. Εάν ο τύπος επιστρέψει σφάλμα #VALUE, ελέγξτε για τα σγουρά στηρίγματα πρέπει να υπάρχουν στον τύπο όπως φαίνεται στα παραδείγματα του άρθρου.
  3. Το char αρνητικό (-) char αλλάζει τιμές, TRUEs ή 1s σε FALSEs ή 0s και FALSEs ή 0s σε TRUEs ή 1s.
  4. Λειτουργίες ίσες με ( = ), λιγότερο από ίσο με ( <= ), μεγαλύτερος από ( > ) ή δεν ισούται με () μπορεί να εκτελεστεί μέσα σε έναν τύπο που εφαρμόζεται, με αριθμούς μόνο.

Ελπίζουμε ότι αυτό το άρθρο σχετικά με τον τρόπο χρήσης της συνάρτησης IF αντί της συνάρτησης SUMPRODUCT και SUMIFS στο Excel είναι επεξηγηματικό. Βρείτε περισσότερα άρθρα σχετικά με τους τύπους αθροίσεων εδώ. Αν σας άρεσαν τα ιστολόγια μας, μοιραστείτε το με τους φίλους σας στο Facebook. Επίσης, μπορείτε να μας ακολουθήσετε στο Twitter και το Facebook. Θα θέλαμε πολύ να σας ακούσουμε, ενημερώστε μας πώς μπορούμε να βελτιώσουμε, να συμπληρώσουμε ή να καινοτομήσουμε το έργο μας και να το κάνουμε καλύτερο για εσάς. Γράψτε μας στον ιστότοπο email

Πώς να χρησιμοποιήσετε τη συνάρτηση SUMPRODUCT στο Excel: Επιστρέφει το SUM μετά τον πολλαπλασιασμό τιμών σε πολλούς πίνακες στο excel.

SUM εάν η ημερομηνία είναι μεταξύ : Επιστρέφει το άθροισμα των τιμών μεταξύ συγκεκριμένων ημερομηνιών ή περιόδου στο excel.

Άθροισμα εάν η ημερομηνία είναι μεγαλύτερη από τη δεδομένη ημερομηνία: Επιστρέφει το άθροισμα αξιών μετά τη δεδομένη ημερομηνία ή περίοδο στο excel.

2 τρόποι για άθροισμα ανά μήνα στο Excel: Επιστρέφει το άθροισμα αξιών μέσα σε ένα συγκεκριμένο μήνα στο excel.

Πώς να αθροίσετε πολλαπλές στήλες με συνθήκη: Επιστρέφει το άθροισμα των τιμών σε πολλές στήλες με συνθήκη στο Excel.

Δημοφιλή άρθρα:

50 Συντόμευση Excel για να αυξήσετε την παραγωγικότητά σας : Προχωρήστε γρηγορότερα στην εργασία σας. Αυτές οι 50 συντομεύσεις θα σας κάνουν να εργάζεστε ακόμη πιο γρήγορα στο Excel.

Πώς να χρησιμοποιήσετε το tσυνάρτηση VLOOKUP στο Excel : Αυτή είναι μια από τις πιο χρησιμοποιούμενες και δημοφιλείς λειτουργίες του excel που χρησιμοποιείται για την αναζήτηση τιμών από διαφορετικά εύρη και φύλλα.

Πώς να χρησιμοποιήσετε τη συνάρτηση COUNTIF στο Excel : Μετρήστε τιμές με συνθήκες χρησιμοποιώντας αυτήν την εκπληκτική συνάρτηση. Δεν χρειάζεται να φιλτράρετε τα δεδομένα σας για να μετρήσετε συγκεκριμένες τιμές. Η λειτουργία Countif είναι απαραίτητη για την προετοιμασία του ταμπλό σας.

Πώς να χρησιμοποιήσετε τη συνάρτηση SUMIF στο Excel : Αυτή είναι μια άλλη βασική λειτουργία του πίνακα ελέγχου. Αυτό σας βοηθά να συνοψίσετε τιμές υπό συγκεκριμένες συνθήκες.