Μία από τις κοινές ερωτήσεις στον πίνακα μηνυμάτων είναι ο τρόπος χρήσης της συνάρτησης SumIf με δύο διαφορετικές συνθήκες. Δυστυχώς, η απάντηση είναι ότι το SumIf δεν μπορεί να χειριστεί δύο διαφορετικές συνθήκες.
Για να κάνετε δύο προϋποθέσεις, πρέπει να χρησιμοποιήσετε έναν μάλλον περίπλοκο τύπο πίνακα. Το πρόσθετο του wizard αθροίσματος υπό όρους σάς επιτρέπει να εισαγάγετε αυτούς τους περίπλοκους τύπους με ευκολία.
Ακολουθεί ένα φύλλο εργασίας του Excel με στήλες για προϊόντα, αντιπροσώπους πωλήσεων και πωλήσεις. Τα δεδομένα βρίσκονται στα κελιά A2: C29.
Εάν θέλετε να ολοκληρώσετε τις πωλήσεις, θα λειτουργήσει μια απλή συνάρτηση SUM (). =SUM(C2:C29).
Πολλοί Excellers ανακαλύπτουν τη λειτουργία SumIf. Χρησιμοποιώντας αυτήν τη λειτουργία, είναι αρκετά εύκολο να υπολογίσετε το σύνολο των πωλήσεων για το προϊόν ABC.=SUMIF(A2:A29,E2,C2:C29)
Είναι επίσης εύκολο να υπολογίσετε το σύνολο των πωλήσεων που πραγματοποίησε ο αντιπρόσωπος πωλήσεων Joe =SUMIF(B2:B29,E2,C2:C29).
Στη συνέχεια, θα υποθέσετε ότι είναι δυνατόν να υπολογίσετε τις συνολικές πωλήσεις του προϊόντος ABC που πραγματοποίησε ο Joe. Ωστόσο, δεν υπάρχει τρόπος να το κάνετε αυτό με τη συνάρτηση SumIf. Αποδεικνύεται ότι πρέπει να χρησιμοποιήσετε έναν αρκετά περίπλοκο πίνακα ή τύπο CSE.
Ας το παραδεχτούμε - ο τύπος Sum είναι το Excel 101. Ο τύπος SumIf δεν είναι πολύ πίσω στην πολυπλοκότητα. Ωστόσο, ο τύπος του ΧΑΚ για τον υπολογισμό του συνόλου των πωλήσεων ABC που έκανε ο Joe είναι αρκετός για να κάνει ακόμη και το κεφάλι μου να γυρίσει.
Τα καλά νέα - Η Microsoft προσφέρει τον Οδηγό Άθροισης υπό όρους που επιτρέπει σε έναν αρχάριο να εισέλθει σε σύνθετους τύπους υπό όρους που βασίζονται σε 1, 2 ή περισσότερες συνθήκες. Ο υπό όρους συνοπτικός οδηγός είναι ένα πρόσθετο. Για να προσθέσετε αυτήν τη λειτουργικότητα στο Excel, μεταβείτε στο μενού Εργαλεία και επιλέξτε Πρόσθετα. Στο παράθυρο διαλόγου Πρόσθετα, επιλέξτε το πλαίσιο ελέγχου δίπλα στον Οδηγό αθροίσματος υπό όρους και επιλέξτε ΟΚ. Είναι πιθανό να χρειαστείτε το CD εγκατάστασης σε αυτό το σημείο, επειδή η Microsoft δεν περιλαμβάνει τον οδηγό στην προεπιλεγμένη εγκατάσταση.
Μόλις το πρόσθετο ενεργοποιηθεί επιτυχώς, θα υπάρχει μια επιλογή Conditonal Sum… κοντά στο κάτω μέρος του μενού Tools.
Επιλέξτε ένα μόνο κελί στο σύνολο δεδομένων σας και επιλέξτε Εργαλεία - Άθροισμα υπό όρους. Υποθέτοντας ότι τα δεδομένα σας έχουν καλή μορφοποίηση με μία μόνο γραμμή επικεφαλίδων, το Excel θα μαντέψει σωστά το εύρος των δεδομένων σας. Επιλέξτε Επόμενο.
Στο βήμα 2, επιλέξτε τη στήλη για άθροισμα. Σε αυτήν την περίπτωση, ο οδηγός μαντέψει ήδη ότι θέλετε να συνοψίσετε την πρώτη (και μόνο) αριθμητική στήλη - Πωλήσεις. Στη μέση του παραθύρου διαλόγου υπάρχουν τρία αναπτυσσόμενα στοιχεία ελέγχου. Αυτά συμβαίνουν να είναι σωστά για την πρώτη συνθήκη - Το προϊόν ισούται με ABC, οπότε επιλέξτε το κουμπί Προσθήκη συνθήκης.
Στη συνέχεια, μπορείτε να προσθέσετε τη δεύτερη σας κατάσταση. Σε αυτήν την περίπτωση, θέλετε να καθορίσετε ότι ο πωλητής πωλήσεων είναι Joe. Επιλέξτε το βέλος για το πρώτο αναπτυσσόμενο μενού. Το Excel προσφέρει μια αλφαβητική λίστα των διαθέσιμων ονομάτων στηλών. Επιλέξτε Αντιπρόσωπος πωλήσεων.
Το κεντρικό αναπτυσσόμενο μενού είναι σωστό, αλλά για πληρότητα εδώ, μπορείτε να δείτε ότι θα μπορούσατε να έχετε επιλέξει ίσο, μικρότερο από, μεγαλύτερο από, μικρότερο ή ίσο, μεγαλύτερο από ή ίσο, ή όχι ίσο.
Από το τρίτο αναπτυσσόμενο μενού, επιλέξτε Joe.
Επιλέξτε το κουμπί Προσθήκη συνθήκης.
Τώρα είστε έτοιμοι να μεταβείτε στο Βήμα 3. Πατήστε το κουμπί Επόμενο.
Στο βήμα 3, έχετε δύο επιλογές. Στην πρώτη επιλογή, ο Οδηγός θα εισαγάγει έναν μόνο τύπο με τις τιμές "ABC" και "Joe" στον κώδικα. Θα σας δώσει την απάντηση, αλλά δεν θα υπάρχει ευκαιρία να αλλάξετε εύκολα τον τύπο. Με τη δεύτερη επιλογή, το Excel θα δημιουργήσει ένα νέο κελί με την τιμή "ABC" και ένα νέο κελί με την τιμή "Joe". Ένα τρίτο κελί θα περιέχει τον τύπο που κάνει ένα άθροισμα υπό όρους με βάση αυτές τις δύο τιμές. Με αυτήν την επιλογή, θα μπορούσατε να πληκτρολογήσετε νέες τιμές στα κελιά για να δείτε τις συνολικές πωλήσεις XYZ από τον Adam.
Στη συνέχεια, ο οδηγός θα ρωτήσει πού θέλετε την τιμή για το ABC. Επιλέξτε ένα κελί και επιλέξτε Επόμενο. Επαναλάβετε καθώς ο Οδηγός σας ζητά να επιλέξετε ένα κελί για τον Joe και τον τύπο.
Όταν επιλέξετε Finish στο τελευταίο βήμα, το Excel θα δημιουργήσει μια ελαφρώς διαφορετική (αλλά έγκυρη) έκδοση του τύπου CSE.
Αυτός ο τύπος υπολογίζει ότι ο Joe πούλησε 33.338 $ ABC.
Εάν αλλάξετε το κελί εισαγωγής προϊόντος από ABC σε DEF, ο τύπος θα υπολογίσει εκ νέου για να δείξει ότι ο Joe πούλησε 24.478 $ DEF.
Ο Οδηγός αθροιστικών προϋποθέσεων θέτει πολύπλοκους τύπους σε όλους τους κατόχους Excel.
Επιπλέον πληροφορίες:Εάν θέλετε να δημιουργήσετε έναν πίνακα που θα δείχνει τις πωλήσεις κάθε προϊόντος από κάθε αντιπρόσωπο πωλήσεων, υπάρχει κάποια ειδική "φροντίδα και σίτιση" που θα πρέπει να γνωρίζετε για αυτούς τους τύπους. Πληκτρολογήστε κάθε αντιπρόσωπο πωλήσεων στο επάνω μέρος του εύρους. Πληκτρολογήστε κάθε προϊόν στην αριστερή στήλη του εύρους. Επεξεργαστείτε τον τύπο που παρέχεται από τον οδηγό. Στην παρακάτω εικόνα, ο τύπος δείχνει ένα προϊόν στο κελί E6. Αυτή η αναφορά πρέπει πραγματικά να είναι E6 $. Εάν αφήσετε την αναφορά ως E6 και αντιγράψετε τον τύπο στη στήλη G, ο τύπος θα κοιτάξει το F6 αντί για το E6 και αυτό θα ήταν λάθος. Η προσθήκη ενός δολαρίου πριν από το E στο E6 θα διασφαλίσει ότι ο τύπος κοιτάζει πάντα το προϊόν στη στήλη E. Ο τύπος δείχνει επίσης έναν αντιπρόσωπο πωλήσεων στο κελί F5. Αυτή η αναφορά πρέπει πραγματικά να είναι 5 $. Εάν αφήσατε την αναφορά ως F5 και αντιγράψετε στη γραμμή 7,η αναφορά F5 θα αλλάξει σε F6 και αυτό δεν είναι σωστό. Η προσθήκη ενός δολαρίου πριν από τον αριθμό σειράς θα κλειδώσει τον αριθμό σειράς και η αναφορά θα δείχνει πάντα στη σειρά 5.
Στη λειτουργία Επεξεργασία (επιλέξτε το κελί και πατήστε F2 για επεξεργασία), πληκτρολογήστε ένα $ πριν από το E. Πληκτρολογήστε το σύμβολο δολαρίου πριν από το 5 στο F5 Μην πατήσετε Enter ακόμα!
Αυτός ο τύπος είναι ένας ειδικός τύπος τύπου. Εάν πατήσετε Enter, θα λάβετε ένα 0, το οποίο δεν είναι σωστό.
Αντί να πληκτρολογήσετε Enter, κρατήστε πατημένα τα πλήκτρα Ctrl και Shift ενώ πατάτε Enter. Αυτός ο μαγικός συνδυασμός C trl + S hift + E nter είναι ο λόγος που ονομάζω αυτούς τους τύπους ΧΑΚ.
Υπάρχει μια τελευταία σκέψη πριν από την αντιγραφή του τύπου στον υπόλοιπο πίνακα. Η τάση σας μπορεί να είναι να αντιγράψετε το F6 και να το επικολλήσετε στο F6: G8. Εάν το δοκιμάσετε, το Excel θα σας δώσει το αινιγματικό μήνυμα "Δεν μπορείτε να αλλάξετε μέρος ενός πίνακα". Το Excel παραπονιέται ότι δεν μπορείτε να επικολλήσετε έναν τύπο CSE σε ένα εύρος που περιέχει τον αρχικό τύπο CSE.
Είναι εύκολο να το αντιμετωπίσουμε. Αντιγραφή F6. Επικόλληση στο F7: F8.
Αντιγραφή F6: F8. Επικόλληση στο G6: G8. Θα έχετε έναν πίνακα τύπων ΧΑΚ που θα εμφανίζει σύνολα βάσει δύο συνθηκών.
