Οι βασικές συναρτήσεις του Excel λειτουργούν καλά για απλούς υπολογισμούς, αλλά γίνονται γρήγορα περίπλοκες όταν ασχολείστε με πολύπλοκη ανάλυση δεδομένων. Καταλήγετε με ένθετους τύπους που είναι δύσκολο να διαβαστούν, πολλαπλές βοηθητικές στήλες που γεμίζουν το υπολογιστικό φύλλο σας και τύπους που μπορεί να μην λειτουργούν όταν αλλάζουν τα δεδομένα σας. Εδώ μπαίνουν στο παιχνίδι οι τύποι πινάκων στο Excel.
Οι τύποι πινάκων σάς επιτρέπουν να εκτελείτε υπολογισμούς σε ολόκληρα εύρη δεδομένων σε έναν μόνο τύπο. Επομένως, μπορείτε Εκτελέστε αναζητήσεις με αστραπιαία ταχύτητα, φιλτράρετε και ταξινομήστε με μία ισχυρή παράσταση, αντί να γράφετε ξεχωριστούς τύπους για κάθε γραμμή ή στήλη. Δεν είναι κάτι καινούργιο στο Excel, αλλά μερικοί άνθρωποι επιμένουν στους παλιούς τρόπους να κάνουν τα πράγματα, ενώ αυτές οι συναρτήσεις μπορούν να κάνουν την εργασία τους απλούστερη και πιο αποτελεσματική.
Γρήγοροι σύνδεσμοι
5. XLOOKUP
Έχει καλύτερη απόδοση από το VLOOKUP κάθε φορά.
Η συνάρτηση XLOOKUP είναι η συνάρτηση αναζήτησης που θα έπρεπε να υπάρχει εξαρχής. Σε αντίθεση με τη συνάρτηση VLOOKUP, η οποία σας αναγκάζει να μετράτε στήλες και αναζητά μόνο προς τα δεξιά, η συνάρτηση XLOOKUP λειτουργεί προς οποιαδήποτε κατεύθυνση και χρησιμοποιεί πραγματικές αναφορές στηλών. Έχει την ακόλουθη σύνταξη:
=XLOOKUP(τιμή_αναζήτησης, πίνακας_αναζήτησης, πίνακας_επιστροφής, [αν_δεν_βρέθηκε], [λειτουργία_αντιστοίχισης], [λειτουργία_αναζήτησης])
Δείτε τι σημαίνει κάθε παράμετρος:
- lookup_value: Η συγκεκριμένη τιμή που αναζητάτε. Αυτή θα μπορούσε να είναι ένας αριθμός εξαρτήματος, ένας κωδικός προϊόντος ή οποιοδήποτε αναγνωριστικό στο σύνολο δεδομένων σας.
- lookup_array: Το εύρος στο οποίο αναζητά το Excel lookup_value Δικό σας. Αυτή είναι συνήθως μία στήλη ή γραμμή που περιέχει τα κριτήρια αναζήτησής σας.
- return_array: Το εύρος που περιέχει τις τιμές που θέλετε να ανακτήσετε. Αυτό μπορεί να είναι μία μόνο στήλη, πολλές στήλες ή ακόμα και μια ολόκληρη ενότητα πίνακα.
- αν_δεν_βρέθηκε (προαιρετικό): Προσαρμοσμένο κείμενο ή τιμή που θα εμφανίζεται όταν δεν βρεθεί αντιστοιχία. Εξαλείφει τα ενοχλητικά σφάλματα #Δ/Υ και σας επιτρέπει να εμφανίζετε αντ' αυτού τις λέξεις "Δεν βρέθηκε" ή "Έλεγχος Αριθμού Μέρους".
- λειτουργία_ταιριάσματος (προαιρετικό): Ελέγχει τον τύπο αντιστοίχισης. Χρησιμοποιήστε 0 για ακριβή αντιστοίχιση (προεπιλογή), -1 για την επόμενη ακριβή ή μικρότερη αντιστοίχιση, 1 για την επόμενη ακριβή ή μεγαλύτερη αντιστοίχιση και 2 για αντιστοίχιση με wildcard.
- λειτουργία_αναζήτησης (προαιρετικό): Καθορίζει την κατεύθυνση αναζήτησης. Χρησιμοποιήστε 1 για αναζήτηση από το τέλος (προεπιλογή), -1 για αναζήτηση από το τέλος και 2 για δυαδική αναζήτηση σε ταξινομημένα δεδομένα.
Ας πάρουμε ένα παράδειγμα ενός υπολογιστικού φύλλου μηχανολογικού αποθέματος. Ο ακόλουθος τύπος αναζητά τον αριθμό εξαρτήματος "BRG-002" μέσα σε ένα εύρος αναγνωριστικών εξαρτημάτων και επιστρέφει τα αντίστοιχα δεδομένα. Εάν το εξάρτημα δεν υπάρχει, εμφανίζει την ένδειξη "Δεν βρέθηκε εξάρτημα" αντί για σφάλμα.
=XLOOKUP("BRG-002", A:A, A:H, "Δεν βρέθηκε το μέρος")
Το XLOOKUP σάς επιτρέπει να εξάγετε δεδομένα από διαφορετικές στήλες χωρίς τους περίπλοκους υπολογισμούς στηλών που υπάρχουν στο VLOOKUP, καθιστώντας το ένα από τα πιο σημαντικά. Συναρτήσεις Excel για γρήγορη εύρεση δεδομένων.
4. ΑΝΤΙΠΡΟΣΩΠΟΣ
Σταθμός παραγωγής ενέργειας για υπολογισμούς υπό όρους
Η συνάρτηση SUMPRODUCT όχι μόνο προσθέτει αριθμούς, αλλά πολλαπλασιάζει επίσης πίνακες και αθροίζει τα αποτελέσματα. Αυτό την καθιστά χρήσιμη για σύνθετους υπολογισμούς υπό όρους που απαιτούν πολλαπλές βοηθητικές στήλες.
Έχει τον ακόλουθο τύπο:
=SUMPRODUCT(πίνακας1, [πίνακας2], [πίνακας3], ...)
εδώ, πίνακας1 Είναι το πρώτο εύρος τιμών που πολλαπλασιάζεται – συνήθως η κύρια στήλη δεδομένων σας, όπως ποσότητες ή κόστη. πίνακας2 Είναι ένα προαιρετικό δεύτερο εύρος για πολλαπλασιασμό, το οποίο συχνά περιέχει κριτήρια ή λογική υπό όρους χρησιμοποιώντας τελεστές σύγκρισης.
Γίνονται πιο χρήσιμα όταν χρησιμοποιούμε λογικούς τελεστές μέσα σε πίνακες. Για παράδειγμα, όταν πληκτρολογούμε συνθήκες όπως (supplier="Siemens"), το Excel μετατρέπει τα αποτελέσματα TRUE/FALSE σε 1/0, επιτρέποντας τους υπολογισμούς.
Για παράδειγμα, ο ακόλουθος τύπος υπολογίζει τη συνολική αξία αποθέματος για ανταλλακτικά που παρέχονται μόνο από τη Siemens. Ο τύπος πολλαπλασιάζει τις ποσότητες με το κόστος μονάδας, αλλά μόνο για γραμμές όπου ο προμηθευτής πληροί τα κριτήρια.
=SUMPRODUCT(D2:D100*H2:H100*(G2:G100="Siemens"))
Ομοίως, ο ακόλουθος τύπος βρίσκει το συνολικό κόστος ενός αποθέματος ρουλεμάν σε καλή κατάσταση:
=SUMPRODUCT((C2:C100="Bearings")*(D2:D100>=15)*H2:H100)
Ισχύουν ταυτόχρονα δύο προϋποθέσεις – η κατηγορία πρέπει να είναι «Ρουλεμάν» και τα επίπεδα αποθέματος πρέπει να είναι 15 μονάδες ή υψηλότερα, κάτι που μας βοηθά να εντοπίσουμε κατηγορίες ρουλεμάν που έχουν επαρκή κάλυψη αποθέματος.
Σε αντίθεση με τις παραδοσιακές συναρτήσεις SUM με πολλαπλά κριτήρια, η συνάρτηση SUMPRODUCT δεν απαιτεί πολύπλοκες ένθετες δομές επειδή χειρίζεται πολλαπλές συνθήκες σε έναν μόνο, ευανάγνωστο τύπο. Συναρτήσεις SUM στο Excel, Όπως οι συναρτήσεις SUMIF και SUMIFS, είναι εξαιρετικές για απλή υπό όρους άθροιση, αλλά η συνάρτηση SUMPRODUCT υπερέχει όταν χρειάζεται να πολλαπλασιάσετε τιμές πριν από την άθροιση ή να χειριστείτε πιο σύνθετες λογικές λειτουργίες.
3. ΦΙΛΤΡΟ
Κάνει την δυναμική εξαγωγή δεδομένων απλή
Το FILTER εξάγει γραμμές από το σύνολο δεδομένων σας με βάση τις συνθήκες που καθορίζετε. Σε αντίθεση με το χειροκίνητο φιλτράρισμα, αυτή η συνάρτηση δημιουργεί δυναμικά αποτελέσματα που ενημερώνονται αυτόματα όταν αλλάζουν τα δεδομένα προέλευσης. Η σύνταξη του FILTER έχει ως εξής:
=FILTER(πίνακας, περιλαμβάνει, [if_empty])
Δείτε τι ελέγχει κάθε είσοδος:
- πίνακας (εύρος): Το πλήρες εύρος των δεδομένων που θέλετε να φιλτράρετε. Αυτό περιλαμβάνει όλες τις στήλες που θέλετε στα αποτελέσματά σας, όχι μόνο τη στήλη κριτηρίων.
- συμπεριλαμβάνω: Λογική συνθήκη που καθορίζει ποιες γραμμές θα επιστραφούν – χρησιμοποιεί τελεστές σύγκρισης για να δημιουργήσει πίνακες TRUE/FALSE για κάθε γραμμή.
- if_empty (προαιρετικό): Εμφανίζει ένα προσαρμοσμένο μήνυμα όταν καμία γραμμή δεν πληροί τα κριτήριά σας. Αποτρέπει τα σφάλματα #CALC! και εμφανίζει ουσιαστικό κείμενο, όπως "Δεν βρέθηκαν αποτελέσματα που να ταιριάζουν".
Η συνάρτηση λειτουργεί αξιολογώντας την συνθήκη σας σε σχέση με κάθε γραμμή στο εύρος. Όταν η συνθήκη επιστρέψει την τιμή TRUE, ολόκληρη η γραμμή εμφανίζεται στα φιλτραρισμένα αποτελέσματα. Ακολουθεί ένα παράδειγμα από ένα υπολογιστικό φύλλο μηχανικού αποθέματος:
=FILTER(A2:H101, (C2:C101="Bearings")*(G2:G101="Timken"))
Αυτός ο τύπος εξάγει όλες τις γραμμές όπου ο πόρος είναι "Timken" και η κατηγορία είναι "Ρουλεμάν". Ο αστερίσκος (*) δημιουργεί μια συνθήκη AND πολλαπλασιάζοντας τους λογικούς πίνακες μεταξύ τους.
Όταν προσθέτετε νέα δεδομένα στο εύρος προέλευσης, Χρήση της συνάρτησης FILTER στο Excel Έχει περισσότερο νόημα από τη μη αυτόματη ταξινόμηση και τους προσωρινούς πίνακες, επειδή τα φιλτραρισμένα αποτελέσματα ενημερώνονται αυτόματα. Αυτό το καθιστά χρήσιμο για τη δημιουργία ζωντανών πινάκων ελέγχου και αναφορών.
2. ΜΟΝΑΔΙΚΌΣ
Εξαγωγή μοναδικών τιμών χωρίς διπλότυπα
Η συνάρτηση UNIQUE εξάγει μοναδικές τιμές από το εύρος δεδομένων σας και αποφεύγει αυτόματα τα διπλότυπα. Αυτή η συνάρτηση είναι σημαντική εάν θέλετε να δημιουργήσετε αναπτυσσόμενες λίστες, να αναλύσετε κατηγορίες δεδομένων και να δημιουργήσετε συνοπτικές αναφορές. Ο τύπος είναι:
=UNIQUE(πίνακας, [ανά_στήλη], [ακριβώς_μία φορά])
Δείτε πώς λειτουργεί κάθε είσοδος:
- πίνακας (εύρος): Το εύρος που περιέχει τα δεδομένα από τα οποία θέλετε να καταργήσετε διπλότυπα—μπορεί να είναι μία μόνο στήλη, πολλές στήλες ή ολόκληρη ενότητα του πίνακα.
- by_col (προαιρετικό): Η τιμή FALSE συγκρίνει τις γραμμές για να προσδιορίσει τη μοναδικότητα (προεπιλογή), ενώ η τιμή TRUE συγκρίνει τις στήλες. Ωστόσο, τα περισσότερα σενάρια χρησιμοποιούν την προεπιλεγμένη σύγκριση γραμμών.
- ακριβώς_μία φορά (προαιρετικό): Η συνάρτηση FALSE επιστρέφει όλες τις μοναδικές τιμές, συμπεριλαμβανομένων εκείνων που εμφανίζονται πολλές φορές (προεπιλογή), και η συνάρτηση TRUE επιστρέφει μόνο τιμές που εμφανίζονται ακριβώς μία φορά στο σύνολο δεδομένων.
Η συνάρτηση UNIQUE αξιολογεί κάθε γραμμή ή τιμή στον πίνακα σας και επιστρέφει μόνο την πρώτη εμφάνιση κάθε μοναδικού στοιχείου. Η σειρά ταιριάζει με την αρχική ακολουθία δεδομένων. Ακολουθεί ένα παράδειγμα:
=ΜΟΝΑΔΙΚΟ(G2:G22)
Αυτός ο τύπος εξάγει όλα τα μοναδικά ονόματα προμηθευτών από τη στήλη Προμηθευτής G και δημιουργεί μια καθαρή, διπλότυπη λίστα. Τον χρησιμοποιώ για να δημιουργήσω αναπτυσσόμενες λίστες προμηθευτών ή συνοπτικές αναφορές.
Μπορείτε επίσης να το χρησιμοποιήσετε σε ολόκληρο τον πίνακα, όπως φαίνεται παρακάτω:
=UNIQUE(A2:F100)
Επιστρέφει μοναδικούς συνδυασμούς σε όλες τις στήλες (A έως F), εμφανίζοντας ξεχωριστά αρχεία αποθέματος. Εάν δύο μέρη έχουν πανομοιότυπες τιμές σε κάθε στήλη, μόνο ένα θα εμφανίζεται στα αποτελέσματα.
Όταν εργάζεστε με μεγάλα σύνολα δεδομένων, το UNIQUE εξαλείφει την κουραστική διαδικασία της χειροκίνητης αφαίρεσης διπλότυπων δεδομένων. Τα δυναμικά αποτελέσματα ενημερώνονται καθώς φτάνουν νέα δεδομένα και, επειδή το UNIQUE δημιουργεί πίνακες spillover, αυτή η προσέγγιση εξαλείφει την ταλαιπωρία της αλλαγής μεγέθους πινάκων, προσαρμόζοντας αυτόματα την κλίμακα για να φιλοξενήσει όλες τις μοναδικές τιμές. Το χρησιμοποιώ για να διατηρώ καθαρές λίστες αναφοράς και να δημιουργώ αξιόπιστα εύρη επικύρωσης δεδομένων.
1. ΤΑΞΙΝΟΜΗΣΗ και ΤΑΞΙΝΟΜΗΣΗ ΜΕ
Οργανώστε τα δεδομένα σας χωρίς να διακυβεύσετε το αρχικό τους μέγεθος
Οι συναρτήσεις SORT και SORTBY οργανώνουν τα δεδομένα δυναμικά διατηρώντας παράλληλα την πηγή ανέπαφη. Η SORT χειρίζεται τη βασική ταξινόμηση κατά θέση στήλης, ενώ η SORTBY ταξινομεί με βάση τιμές σε διαφορετικές στήλες, δίνοντάς σας μεγαλύτερη ευελιξία για σύνθετη ταξινόμηση.
Η SORT χρησιμοποιεί αυτήν τη δομή:
=SORT(πίνακας, [sort_index], [sort_order], [by_col])
Δείτε τι ελέγχει κάθε παράμετρος:
- πίνακας: Το εύρος δεδομένων που θέλετε να ταξινομήσετε—περιλαμβάνει όλες τις στήλες που θα πρέπει να εμφανίζονται στα ταξινομημένα αποτελέσματα.
- sort_index (προαιρετικό): Ο αριθμός στήλης μέσα στον πίνακα με βάση τον οποίο θα γίνει η ταξινόμηση. Χρησιμοποιήστε 1 για την πρώτη στήλη, 2 για τη δεύτερη στήλη και ούτω καθεξής (η προεπιλογή είναι 1).
- sort_order (προαιρετικό): Χρησιμοποιήστε 1 για αύξουσα σειρά (προεπιλογή) και -1 για φθίνουσα σειρά.
- by_col (προαιρετικό): FALSE για ταξινόμηση κατά γραμμές (προεπιλογή), TRUE για ταξινόμηση κατά στήλες—τα περισσότερα σενάρια χρησιμοποιούν ταξινόμηση σε γραμμές.
Η συνάρτηση SORTBY έχει την ακόλουθη μορφή:
=SORTBY(πίνακας, κατά_πίνακα1, [ταξινόμηση_παραγγελίας1], [κατά_σειρά2], [ταξινόμηση_παραγγελίας2], ...)
Οι συναλλαγές της περιλαμβάνουν:
- πίνακας: Το εύρος των δεδομένων προς ταξινόμηση—παρόμοια με τη συνάρτηση SORT, περιέχει όλες τις στήλες που θέλετε στα αποτελέσματα.
- από_πίνακα1: Το εύρος που περιέχει τις τιμές που καθορίζουν τη σειρά ταξινόμησης μπορεί να είναι οποιαδήποτε στήλη, ακόμη και εκτός του εύρους του κύριου πίνακα.
- ταξινόμηση_σειράς1 (προαιρετικό): 1 για αύξουσα σειρά (προεπιλογή), -1 για φθίνουσα σειρά.
- από_πίνακα_2, σειρά_ταξινόμησης2 (προαιρετικά): Πρόσθετα κριτήρια ταξινόμησης για ταξινόμηση σε πολλαπλά επίπεδα.
Εξετάζοντας ένα παράδειγμα από ένα υπολογιστικό φύλλο μηχανικού αποθέματος, αυτές οι συναρτήσεις χειρίζονται σενάρια ταξινόμησης πραγματικού κόσμου:
=SORT(A2:H22, 4, -1)
Αυτό ταξινομεί ολόκληρο το απόθεμα κατά επίπεδα αποθεμάτων σε φθίνουσα σειρά, με τα είδη με το υψηλότερο απόθεμα να εμφανίζονται πρώτα. Ο τύπος ταξινομεί κατά στήλη 4 (επίπεδα αποθεμάτων) διατηρώντας παράλληλα όλες τις σχέσεις μεταξύ των γραμμών.
Χρησιμοποιώ τη συνάρτηση SORTBY. Αντί για την ταξινόμηση, μπορείτε να τη χρησιμοποιήσετε για να αποκτήσετε καλύτερο έλεγχο στα κριτήρια ταξινόμησης και σε πολλαπλά επίπεδα ταξινόμησης. Για παράδειγμα, ο ακόλουθος τύπος ταξινομεί πρώτα αλφαβητικά ανά κατηγορία και στη συνέχεια ανά επίπεδα αποθέματος από το υψηλότερο στο χαμηλότερο εντός κάθε κατηγορίας.
=SORTBY(A2:H22, C2:C22, 1, D2:D22, -1)
Οργανωμένα υπολογιστικά φύλλα, πιο έξυπνα αποτελέσματα
Οι τύποι πίνακα εξαλείφουν την ακαταστασία των βοηθητικών στηλών και των ένθετων συναρτήσεων που δυσχεραίνουν τη συντήρηση των υπολογιστικών φύλλων. Λαμβάνετε μεμονωμένους τύπους που χειρίζονται πολλαπλές λειτουργίες, καθιστώντας τα βιβλία εργασίας πιο καθαρά και πιο επαγγελματικά.
Ένα αξιοσημείωτο πλεονέκτημα είναι οι δυναμικές συναρτήσεις, όπου τα αποτελέσματα ενημερώνονται αυτόματα όταν αλλάζουν τα δεδομένα προέλευσης. Αυτό εξαλείφει τις μη αυτόματες ενημερώσεις ή τις κατεστραμμένες συμβολοσειρές τύπων, καθιστώντας τα υπολογιστικά σας φύλλα πιο αξιόπιστα για συνεχή ανάλυση.
Η βιβλιοθήκη συναρτήσεων πίνακα του Excel συνεχίζει να επεκτείνεται πέρα από αυτά τα βασικά εργαλεία. Όταν χρειάζεται να συνδυάσω δεδομένα από πολλαπλές πηγές, χρησιμοποιώ τις συναρτήσεις VSTACK και HSTACK για να συνδυάσω εύρη. Μαζί, αυτές οι συναρτήσεις δημιουργούν ισχυρές ροές εργασίας επεξεργασίας δεδομένων που θα ήταν αδύνατες χρησιμοποιώντας παραδοσιακούς τύπους.
