Κάντε λήψη του παραδείγματος βιβλίου εργασίας
Αυτό το σεμινάριο θα δείξει τον τρόπο υπολογισμού των υποσυνολικών ανά ομάδα χρησιμοποιώντας τη συνάρτηση SUMIFS στο Excel και στα Υπολογιστικά φύλλα Google.
Υποσύνολο Πίνακας ανά Κατηγορία ή Ομάδα
Αρχικά, θα δείξουμε πώς να δημιουργήσετε έναν δυναμικό υποσύνολο συνοπτικό πίνακα από μια περιοχή δεδομένων είτε στο Excel 365 και μετά είτε στα Υπολογιστικά φύλλα Google.
Χρησιμοποιούμε τη ΜΟΝΑΔΙΚΗ συνάρτηση και τη συνάρτηση SUMIFS για αυτόματο υποσύνολο του Αριθμός προϊόντων με Ομάδα προϊόντων:
| 1 | = SUMIFS (C3: C11, B3: B11, E3) |
Για να δημιουργήσουμε αυτόν τον υποσύνολο πίνακα, χρησιμοποιούμε την τυπική εφαρμογή της συνάρτησης SUMIFS για να αθροίσουμε το Αριθμός προϊόντων που ταιριάζουν το καθένα Ομάδα προϊόντων. Ωστόσο, πριν αυτό είναι δυνατό, πρέπει να δημιουργήσουμε μια λίστα μοναδικών Ομάδες προϊόντων. Οι χρήστες του Microsoft Excel 365 και των Υπολογιστικών φύλλων Google έχουν πρόσβαση στη ΜΟΝΑΔΙΚΗ συνάρτηση για τη δημιουργία μιας δυναμικής λίστας μοναδικών τιμών από μια περιοχή κελιών. Σε αυτό το παράδειγμα, προσθέτουμε τον ακόλουθο τύπο στο κελί Ε3:
| 1 | = ΜΟΝΑΔΙΚΟ (Β3: Β11) |
Όταν εισαχθεί αυτός ο τύπος, μια λίστα δημιουργείται αυτόματα κάτω από το κελί για να εμφανίσει όλες τις μοναδικές τιμές που βρίσκονται στο Ομάδα προϊόντων φασμα ΔΕΔΟΜΕΝΩΝ. Σε αυτό το παράδειγμα, η λίστα επεκτάθηκε για να καλύψει το E3: E5 για να δείξει και τα 3 μοναδικά Ομάδα προϊόντων αξίες.
Αυτή είναι μια συνάρτηση δυναμικού πίνακα όπου το μέγεθος της λίστας αποτελεσμάτων δεν χρειάζεται να καθοριστεί και αυτόματα θα συρρικνωθεί και θα μεγαλώσει καθώς αλλάζουν οι τιμές των δεδομένων εισόδου.
Λάβετε υπόψη ότι στο Excel 365, η ΜΟΝΑΔΙΚΗ συνάρτηση δεν έχει διάκριση πεζών -κεφαλαίων, αλλά στα Υπολογιστικά φύλλα Google είναι. Εξετάστε τη λίστα {"A". "ένα"; "ΣΙ"; "ντο"}. Η έξοδος UNIQUE Function εξαρτάται από το πρόγραμμα:
- {"ΕΝΑ"; "ΣΙ"; "C"} στο Excel 365
- {"ΕΝΑ"; "ένα"; "ΣΙ"; "C"} στα Υπολογιστικά φύλλα Google
Εάν χρησιμοποιείτε έκδοση Excel πριν από το Excel 365, θα πρέπει να ακολουθήσετε διαφορετική προσέγγιση. Αυτό συζητείται στην επόμενη ενότητα.
Υποσύνολο πίνακα ανά κατηγορία ή ομάδα - Pre Excel 365
Εάν χρησιμοποιείτε μια έκδοση του Excel πριν από το Excel 365, η ΜΟΝΑΔΙΚΗ συνάρτηση δεν είναι διαθέσιμη για χρήση. Για να επαναλάβετε την ίδια συμπεριφορά, μπορείτε να συνδυάσετε τη συνάρτηση INDEX και τη συνάρτηση MATCH με μια συνάρτηση COUNTIF για να δημιουργήσετε έναν τύπο πίνακα για να δημιουργήσετε μια λίστα μοναδικών τιμών από μια περιοχή κελιών:
| 1 | {= INDEX ($ B $ 3: $ B $ 11, MATCH (0, COUNTIF ($ E $ 2: E2, $ B $ 3: $ B $ 11), 0))} |
Για να λειτουργήσει αυτός ο τύπος, οι σταθερές αναφορές κελιών πρέπει να γραφτούν προσεκτικά, με τη συνάρτηση COUNTIF να αναφέρεται στο εύρος $ E $ 2: E2, το οποίο είναι το εύρος που ξεκινά από το E2 μέχρι το κελί πάνω από το κελί που περιέχει τον τύπο.
Ο τύπος πρέπει επίσης να εισαχθεί ως τύπος πίνακα πατώντας CTRL + SHIFT + ENTER αφού γραφτεί. Αυτός ο τύπος είναι α Τύπος πίνακα 1 κυψέλης, το οποίο στη συνέχεια μπορεί να επικολληθεί με αντιγραφή στα κελιά Ε4, Ε5 κ.λπ. Μην το εισαγάγετε ως τύπο πίνακα για ολόκληρο το εύρος Ε3: Ε5 σε μία ενέργεια.
Με τον ίδιο τρόπο όπως στο προηγούμενο παράδειγμα, μια συνάρτηση SUMIFS χρησιμοποιείται στη συνέχεια για να υποσύνολο το Αριθμός προϊόντων με Ομάδα προϊόντων:
| 1 | = SUMIFS (C3: C11, B3: B11, E3) |
Άθροισμα ανά κατηγορία ή ομάδα - Υποσύνολα στους πίνακες δεδομένων
Ως εναλλακτική λύση στη μέθοδο του συνοπτικού πίνακα που φαίνεται παραπάνω, μπορούμε να προσθέσουμε υποσύνολα απευθείας σε έναν πίνακα δεδομένων. Θα το δείξουμε χρησιμοποιώντας τις συναρτήσεις IF μαζί με τη συνάρτηση SUMIFS για να προσθέσουμε ένα Υποσύνολο ανά ομάδα στον αρχικό πίνακα δεδομένων.
| 1 | = IF (B3 = B2, "", SUMIFS (C3: C11, B3: B11, B3)) |
Αυτό το παράδειγμα χρησιμοποιεί μια συνάρτηση SUMIFS τοποθετημένη μέσα σε μια συνάρτηση IF. Ας χωρίσουμε το παράδειγμα σε βήματα:
Για να προσθέσουμε συνοπτικά στατιστικά στοιχεία απευθείας σε έναν πίνακα δεδομένων, μπορούμε να χρησιμοποιήσουμε τη συνάρτηση SUMIFS. Ξεκινάμε με το σύνολο Αριθμός προϊόντων που ταιριάζουν με τα σχετικά Ομάδα προϊόντων:
| 1 | = SUMIFS (C3: C11, B3: B11, B3) |
Αυτός ο τύπος παράγει μια υποσυνολική τιμή για κάθε σειρά δεδομένων. Για να εμφανίσετε υποσύνολα μόνο στην πρώτη σειρά δεδομένων του καθενός Ομάδα προϊόντων, χρησιμοποιούμε τη συνάρτηση IF. Σημειώστε ότι τα δεδομένα πρέπει ήδη να ταξινομηθούν κατά Ομάδα προϊόντων για να διασφαλιστεί ότι τα υποσύνολα εμφανίζονται σωστά.
| 1 | = IF (B3 = B2, "", SUMIFS (C3: C11, B3: B11, B3)) |
Η συνάρτηση IF συγκρίνει κάθε σειρά δεδομένων Ομάδα προϊόντων τιμή με τη γραμμή δεδομένων πάνω από αυτήν, και αν έχουν την ίδια τιμή εξάγει ένα κενό κελί ("").
Αν το Ομάδα προϊόντων οι τιμές είναι διαφορετικές, εμφανίζεται το άθροισμα. Με αυτόν τον τρόπο, το καθένα Ομάδα προϊόντων Το άθροισμα εμφανίζεται μόνο μία φορά (στη σειρά του πρώτου δείγματος).
Ταξινόμηση συνόλων δεδομένων κατά ομάδα
Εάν τα δεδομένα δεν είναι ήδη ταξινομημένα, μπορούμε ακόμα να χρησιμοποιήσουμε τον ίδιο τύπο για το υποσύνολο.
Το παραπάνω σύνολο δεδομένων δεν ταξινομείται κατά Ομάδα προϊόντων, έτσι το Υποσύνολο κατά ομάδα στη στήλη εμφανίζεται κάθε υποσύνολο περισσότερες από μία φορές. Για να πάρουμε τα δεδομένα στη μορφή που θέλουμε, μπορούμε να επιλέξουμε τον πίνακα δεδομένων και να κάνουμε κλικ στο "Ταξινόμηση Α έως Ζ".
Κλείδωμα αναφορών κυττάρων
Για να διευκολύνουμε την ανάγνωση των τύπων μας, δείξαμε μερικούς από τους τύπους χωρίς κλειδωμένες αναφορές κελιών:
| 1 | = IF (B3 = B2, "", SUMIFS (C3: C11, B3: B11, B3)) |
Αλλά αυτοί οι τύποι δεν θα λειτουργήσουν σωστά όταν αντιγράφονται και επικολλούνται αλλού στο αρχείο σας. Αντ 'αυτού, θα πρέπει να χρησιμοποιήσετε αναφορές κλειδωμένων κελιών όπως αυτό:
| 1 | = IF (B3 = B2, "", SUMIFS ($ C $ 3: $ C $ 11, $ B $ 3: $ B $ 11, B3)) |
Διαβάστε το άρθρο μας σχετικά με το κλείδωμα αναφορών κυττάρων για να μάθετε περισσότερα.
Χρήση περιστροφικών πινάκων για την εμφάνιση υποσυνολικών
Προκειμένου να καταργηθεί η απαίτηση για προ-ταξινόμηση των δεδομένων κατά Ομάδα προϊόντων, μπορούμε να χρησιμοποιήσουμε τη δύναμη των Συγκεντρωτικών πινάκων για να συνοψίσουμε τα δεδομένα. Οι Συγκεντρωτικοί Πίνακες υπολογίζουν αυτόματα τα υποσύνολα και εμφανίζουν σύνολα και υποσύνολα σε διάφορες διαφορετικές μορφές.
Άθροισμα ανά κατηγορία ή ομάδα στα Υπολογιστικά φύλλα Google
Αυτοί οι τύποι λειτουργούν το ίδιο στα Υπολογιστικά φύλλα Google όπως και στο Excel. Ωστόσο, η ΜΟΝΑΔΙΚΗ συνάρτηση έχει διάκριση πεζών -κεφαλαίων στα Υπολογιστικά φύλλα Google.
