Λειτουργία AGGREGATE στο Excel
Η συνάρτηση AGGREGATE στο excel επιστρέφει το σύνολο ενός δεδομένου πίνακα δεδομένων ή λίστες δεδομένων, αυτή η συνάρτηση έχει επίσης το πρώτο όρισμα ως αριθμό συνάρτησης και περαιτέρω ορίσματα είναι για μια σειρά από τα σύνολα δεδομένων, ο αριθμός λειτουργίας πρέπει να θυμόμαστε για να γνωρίζουμε ποια συνάρτηση θα χρησιμοποιήσουμε .
Σύνταξη
Υπάρχουν δύο σύνταξη για τον τύπο AGGREGATE:
- Σύνταξη αναφοράς
= AGGREGATE (function_num, options, ref1, ref2, ref (3),…)
- Σύνταξη Array
= AGGREGATE (function_num, options, array, (k))
Το Function_num είναι ένας αριθμός που δηλώνει μια συγκεκριμένη συνάρτηση που θέλουμε να χρησιμοποιήσουμε. είναι ένας αριθμός από 1-19
Επιλογή: είναι επίσης μια αριθμητική τιμή που κυμαίνεται από 0 έως 7 και καθορίζει ποιες τιμές πρέπει να αγνοούνται κατά τους υπολογισμούς
Ref1, ref2, ref (3): είναι το όρισμα κατά τη χρήση της σύνταξης αναφοράς. είναι αριθμητική τιμή ή τιμές στις οποίες θέλουμε να εκτελέσουμε τον υπολογισμό, απαιτούνται τουλάχιστον δύο ορίσματα. Τα υπόλοιπα επιχειρήματα είναι προαιρετικά.
Array: είναι ένας πίνακας τιμών στις οποίες θέλουμε να λειτουργήσουμε. χρησιμοποιείται στη σύνταξη πίνακα της συνάρτησης AGGREGATE στο excel.
Κ: είναι ένα προαιρετικό όρισμα και αριθμητική τιμή. χρησιμοποιείται όταν χρησιμοποιείται η συνάρτηση LARGE, SMALL, PERCENTILE.EXC, QUARTILE.INC, PERCENTILE.INC ή QUARTILE.EXC στο Excel.
Παραδείγματα
Παράδειγμα - # 1
Ας υποθέσουμε ότι έχουμε μια λίστα αριθμών και θα υπολογίσουμε τον μέσο όρο, τον αριθμό που είναι ο αριθμός των κελιών που περιέχουν μια τιμή, τον αριθμό των κελιών που δεν είναι κενές, το μέγιστο, το ελάχιστο, το προϊόν και το άθροισμα των δεδομένων αριθμητικών τιμών . Οι τιμές δίνονται παρακάτω στον πίνακα:
Ας υπολογίσουμε πρώτα τον μέσο όρο στη σειρά 9, για όλες τις δεδομένες τιμές. Κατά μέσο όρο, η συνάρτηση_ αριθμός είναι 1
Στη στήλη Γ, δίνονται όλες οι τιμές και δεν θα χρειαστεί να αγνοήσουμε καμία τιμή, ώστε να επιλέξουμε την επιλογή 4
Και επιλέγοντας το εύρος τιμών C1: C8 ως μια σειρά αριθμητικών τιμών
Δεδομένου ότι το « k» είναι ένα προαιρετικό όρισμα και χρησιμοποιείται όταν χρησιμοποιείται μια συνάρτηση όπως LARGE, SMALL in Excel, PERCENTILE.EXC, QUARTILE.INC, PERCENTILE.INC ή QUARTILE.EXC, αλλά σε αυτήν την περίπτωση, υπολογίζουμε το Μέσο έως παραλείψτε την τιμή του k.
Έτσι, η μέση τιμή είναι
Ομοίως, για το εύρος D1: D8, πάλι, θα επιλέξουμε την επιλογή 4.
Για το εύρος E1: E8, ένα κελί E6 περιέχει μια τιμή σφάλματος. Εάν χρησιμοποιήσουμε τον ίδιο τύπο AGGREGATE, θα λάβουμε ένα σφάλμα. Ωστόσο, όταν χρησιμοποιείται μια κατάλληλη επιλογή, το AGGREGATE στο Excel δίνει το μέσο όρο των υπόλοιπων τιμών παραβλέποντας την τιμή σφάλματος στο E6.
Για να αγνοήσουμε τις τιμές σφάλματος, έχουμε την επιλογή 6.
Ομοίως, για το εύρος G1: G8, θα χρησιμοποιήσουμε την επιλογή 6 (αγνοήστε τις τιμές σφάλματος)
Τώρα, για το εύρος H3, αν βάλουμε μια τιμή 64, και κρύψουμε την τρίτη σειρά και χρησιμοποιήσουμε την επιλογή 5, για να αγνοήσουμε την κρυφή σειρά, το AGGREGATE στο Excel, θα δώσουμε τη μέση τιμή μόνο για ορατές αριθμητικές τιμές.
Έξοδος χωρίς απόκρυψη σειράς 3
Έξοδος μετά την απόκρυψη σειράς 3
Εφαρμόζοντας τον τύπο AGGREGATE για άλλες λειτουργίες, έχουμε
Παράδειγμα - # 2
Ας υποθέσουμε ότι έχουμε έναν πίνακα για τα έσοδα που δημιουργούνται σε διαφορετικές ημερομηνίες από τα διαφορετικά κανάλια όπως δίνεται παρακάτω
Τώρα, θέλουμε να ελέγξουμε τα έσοδα που δημιουργούνται για διαφορετικά κανάλια. Έτσι, όταν εφαρμόζουμε τη συνάρτηση αθροίσματος, λαμβάνουμε τα συνολικά έσοδα που δημιουργούνται, αλλά σε περίπτωση που θέλουμε να ελέγξουμε τα έσοδα που δημιουργούνται για οργανικό κανάλι ή απευθείας κανάλι ή οποιοδήποτε άλλο, όταν εφαρμόζουμε φίλτρα στο excel για το ίδιο, η συνάρτηση αθροίσματος θα δίνει πάντα το συνολικό ποσό
Θέλουμε ότι όταν φιλτράρουμε το κανάλι, λαμβάνουμε το άθροισμα των ορατών τιμών, οπότε αντί να χρησιμοποιήσουμε τη συνάρτηση SUM, θα χρησιμοποιήσουμε τη συνάρτηση AGGREGATE για να πάρουμε το άθροισμα των τιμών που είναι ορατές όταν εφαρμόζεται ένα φίλτρο.
Έτσι, αντικαθιστώντας τον τύπο SUM με μια συνάρτηση AGGREGATE με τον κωδικό επιλογής 5 (αγνοώντας τις κρυφές σειρές και τιμές), έχουμε,
Τώρα, όταν εφαρμόζουμε το φίλτρο για διαφορετικά κανάλια, θα εμφανίζει τα έσοδα για αυτό το κανάλι μόνο καθώς οι υπόλοιπες σειρές αποκρύπτονται.
Συνολικά έσοδα που δημιουργούνται για απευθείας κανάλι:
Συνολικά έσοδα που δημιουργούνται για οργανικό κανάλι:
Συνολικά έσοδα που δημιουργούνται για επί πληρωμή κανάλι:
Μπορούμε να δούμε ότι η συνάρτηση AGGREGATE υπολογίζει τις διαφορετικές τιμές αθροίσματος για τα έσοδα που δημιουργούνται για διαφορετικά κανάλια μόλις φιλτραριστούν. Έτσι, η συνάρτηση AGGREGATE μπορεί να χρησιμοποιηθεί δυναμικά για την αντικατάσταση διαφορετικών λειτουργιών για διαφορετικές συνθήκες χωρίς τη χρήση του τύπου υπό όρους.
Ας υποθέσουμε ότι για τον ίδιο πίνακα, ορισμένες από τις τιμές εσόδων μας περιέχουν ένα σφάλμα, τώρα πρέπει να αγνοήσουμε τα σφάλματα και ταυτόχρονα, εάν θέλουμε να εφαρμόσουμε ένα φίλτρο, η συνάρτηση AGGREGATE θα πρέπει να αγνοήσει επίσης τις τιμές κρυφών σειρών.
Όταν χρησιμοποιούμε την επιλογή 5, λαμβάνουμε το σφάλμα για το άθροισμα των συνολικών εσόδων. Για να αγνοήσουμε τα σφάλματα, πρέπει να χρησιμοποιήσουμε την επιλογή 6
Χρησιμοποιώντας την επιλογή 6, λαμβάνουμε το άθροισμα αγνοώντας τις τιμές σφάλματος. Ακόμα, όταν εφαρμόζουμε το φίλτρο, για παράδειγμα, φίλτρο κατά τιμή καναλιού Direct, παίρνουμε το ίδιο άθροισμα αγνοώντας τα σφάλματα, αλλά ταυτόχρονα, πρέπει να αγνοήσουμε τις κρυφές τιμές επίσης.
Έτσι, σε αυτήν την περίπτωση, θα χρησιμοποιήσουμε την επιλογή 7 που αγνοεί τις τιμές σφάλματος και, ταυτόχρονα, τις κρυφές σειρές.
Πράγματα που πρέπει να θυμάστε
- Η συνάρτηση AGGREGATE δεν αναγνωρίζει τη συνάρτηση _num τιμή μεγαλύτερη από 19 ή μικρότερη από 1. Ομοίως, για τον αριθμό επιλογής, δεν προσδιορίζει τις τιμές μεγαλύτερες από 7 και λιγότερες από 1. εάν παρέχουμε άλλες τιμές, δίνει # ΑΞΙΑ! Λάθος
- Αποδέχεται πάντα την αριθμητική τιμή και πάντα επιστρέφει μια αριθμητική τιμή ως έξοδο
- Το AGGREGATE στο Excel έχει περιορισμό. αγνοεί μόνο τις κρυφές σειρές αλλά δεν αγνοεί τις κρυφές στήλες.
