Περιεχόμενα
Τι είναι οι δυναμικοί πίνακες
Τον Σεπτέμβριο του 2018, η Microsoft κυκλοφόρησε μια ενημέρωση που προσθέτει ένα εντελώς νέο εργαλείο στο Microsoft Excel: Dynamic Arrays και 7 νέες λειτουργίες για εργασία μαζί τους. Αυτά τα πράγματα, χωρίς υπερβολές, αλλάζουν ριζικά όλη τη συνηθισμένη τεχνική εργασίας με τύπους και συναρτήσεις και αφορούν κυριολεκτικά κάθε χρήστη.
Εξετάστε ένα απλό παράδειγμα για να εξηγήσετε την ουσία.
Ας υποθέσουμε ότι έχουμε έναν απλό πίνακα με δεδομένα για πόλεις-μήνες. Τι θα συμβεί αν επιλέξουμε οποιοδήποτε κενό κελί στα δεξιά του φύλλου και εισάγουμε σε αυτό έναν τύπο που δεν συνδέεται με ένα κελί, αλλά αμέσως με μια περιοχή;
Σε όλες τις προηγούμενες εκδόσεις του Excel, αφού κάνετε κλικ στο εισάγετε θα παίρναμε τα περιεχόμενα ενός μόνο πρώτου κελιού Β2. Πως αλλιώς?
Λοιπόν, διαφορετικά θα ήταν δυνατό να τυλίξετε αυτό το εύρος σε κάποιο είδος συνάρτησης αθροίσματος όπως =SUM(B2:C4) και να λάβετε ένα μεγάλο σύνολο για αυτό.
Εάν χρειαζόμασταν πιο σύνθετες λειτουργίες από ένα πρωτόγονο άθροισμα, όπως η εξαγωγή μοναδικών τιμών ή το Top 3, τότε θα έπρεπε να εισάγουμε τον τύπο μας ως τύπο πίνακα χρησιμοποιώντας μια συντόμευση πληκτρολογίου Ctrl+αλλαγή+εισάγετε.
Τώρα όλα είναι διαφορετικά.
Τώρα, αφού εισάγουμε έναν τέτοιο τύπο, μπορούμε απλά να κάνουμε κλικ εισάγετε – και λάβετε ως αποτέλεσμα αμέσως όλες τις τιμές uXNUMXbuXNUMXb στις οποίες αναφερθήκαμε:
Αυτό δεν είναι μαγικό, αλλά οι νέοι δυναμικοί πίνακες που διαθέτει τώρα το Microsoft Excel. Καλώς ήρθατε στον νέο κόσμο 🙂
Χαρακτηριστικά εργασίας με δυναμικούς πίνακες
Τεχνικά, ολόκληρος ο δυναμικός πίνακας μας αποθηκεύεται στο πρώτο κελί G4, συμπληρώνοντας τον απαιτούμενο αριθμό κελιών προς τα δεξιά και προς τα κάτω με τα δεδομένα του. Εάν επιλέξετε οποιοδήποτε άλλο κελί στον πίνακα, τότε ο σύνδεσμος στη γραμμή τύπων θα είναι ανενεργός, δείχνοντας ότι βρισκόμαστε σε ένα από τα "θυγατρικά" κελιά:
Μια προσπάθεια διαγραφής ενός ή περισσότερων "θυγατρικών" κελιών δεν θα οδηγήσει σε τίποτα - το Excel θα τα υπολογίσει ξανά και θα τα γεμίσει αμέσως.
Ταυτόχρονα, μπορούμε με ασφάλεια να αναφερθούμε σε αυτά τα «παιδικά» κύτταρα σε άλλους τύπους:
Εάν αντιγράψετε το πρώτο κελί ενός πίνακα (για παράδειγμα, από το G4 στο F8), τότε ολόκληρος ο πίνακας (οι αναφορές του) θα κινηθεί προς την ίδια κατεύθυνση όπως στους κανονικούς τύπους:
Αν χρειαστεί να μετακινήσουμε τον πίνακα, τότε θα αρκεί να μετακινήσουμε (με το ποντίκι ή με συνδυασμό Ctrl+X, Ctrl+V), πάλι, μόνο το πρώτο κύριο κελί G4 – μετά από αυτό, θα μεταφερθεί σε νέο μέρος και ολόκληρη η συστοιχία μας θα επεκταθεί ξανά.
Εάν πρέπει να ανατρέξετε κάπου αλλού στο φύλλο στον δυναμικό πίνακα που δημιουργήθηκε, τότε μπορείτε να χρησιμοποιήσετε τον ειδικό χαρακτήρα # ("λίβρα") μετά τη διεύθυνση του κύριου κελιού του:
Για παράδειγμα, τώρα μπορείτε εύκολα να δημιουργήσετε μια αναπτυσσόμενη λίστα σε ένα κελί που αναφέρεται στον δυναμικό πίνακα που δημιουργήθηκε:
Σφάλματα δυναμικής διάταξης
Τι συμβαίνει όμως εάν δεν υπάρχει αρκετός χώρος για την επέκταση του πίνακα ή εάν υπάρχουν ήδη κελιά που καταλαμβάνονται από άλλα δεδομένα στη διαδρομή του; Γνωρίστε έναν ριζικά νέο τύπο σφαλμάτων στο Excel – #ΜΕΤΑΦΟΡΑ! (#ΔΙΑΡΡΟΗ!):
Όπως πάντα, αν κάνουμε κλικ στο εικονίδιο με ένα κίτρινο διαμάντι και ένα θαυμαστικό, θα λάβουμε μια πιο λεπτομερή εξήγηση της πηγής του προβλήματος και θα βρούμε γρήγορα παρεμβαλλόμενα κελιά:
Παρόμοια σφάλματα θα προκύψουν εάν ο πίνακας φύγει από το φύλλο ή χτυπήσει ένα συγχωνευμένο κελί. Εάν αφαιρέσετε το εμπόδιο, τότε όλα θα διορθωθούν αμέσως αμέσως.
Δυναμικοί πίνακες και έξυπνοι πίνακες
Εάν ο δυναμικός πίνακας δείχνει σε έναν "έξυπνο" πίνακα που δημιουργήθηκε από μια συντόμευση πληκτρολογίου Ctrl+T ή από Αρχική σελίδα – Μορφοποίηση ως πίνακα (Αρχική σελίδα — Μορφοποίηση ως πίνακα), τότε θα κληρονομήσει και την κύρια ιδιότητά του – την αυτόματη ρύθμιση μεγέθους.
Όταν προσθέτετε νέα δεδομένα στο κάτω μέρος ή στα δεξιά, ο έξυπνος πίνακας και το δυναμικό εύρος θα τεντωθούν επίσης αυτόματα:
Ωστόσο, υπάρχει ένας περιορισμός: δεν μπορούμε να χρησιμοποιήσουμε μια αναφορά δυναμικού εύρους σε φόρουμ μέσα σε έναν έξυπνο πίνακα:
Δυναμικοί πίνακες και άλλες δυνατότητες του Excel
Εντάξει, λες. Όλα αυτά είναι ενδιαφέροντα και αστεία. Δεν χρειάζεται, όπως πριν, να τεντώσετε με μη αυτόματο τρόπο τον τύπο με αναφορά στο πρώτο κελί της αρχικής περιοχής προς τα κάτω και προς τα δεξιά και όλα αυτά. Και αυτό είναι όλο?
ΟΧΙ ακριβως.
Οι δυναμικοί πίνακες δεν είναι απλώς ένα άλλο εργαλείο στο Excel. Τώρα είναι ενσωματωμένα στην ίδια την καρδιά (ή τον εγκέφαλο) του Microsoft Excel – τη μηχανή υπολογισμού του. Αυτό σημαίνει ότι και άλλοι τύποι και συναρτήσεις του Excel που είναι γνωστές σε εμάς υποστηρίζουν πλέον την εργασία με δυναμικούς πίνακες. Ας ρίξουμε μια ματιά σε μερικά παραδείγματα για να σας δώσουμε μια ιδέα για το βάθος των αλλαγών που έχουν συμβεί.
Μεταθέτω
Για να μεταφέρετε μια περιοχή (εναλλαγή σειρών και στηλών) το Microsoft Excel είχε πάντα μια ενσωματωμένη λειτουργία ΜΕΤΑΦΟΡΑ (ΜΕΤΑΘΕΤΩ). Ωστόσο, για να το χρησιμοποιήσετε, πρέπει πρώτα να επιλέξετε σωστά το εύρος για τα αποτελέσματα (για παράδειγμα, εάν η είσοδος ήταν εύρος 5×3, τότε πρέπει να έχετε επιλέξει 3×5), στη συνέχεια εισάγετε τη συνάρτηση και πατήστε το συνδυασμός Ctrl+αλλαγή+εισάγετε, επειδή μπορούσε να λειτουργήσει μόνο σε λειτουργία τύπου πίνακα.
Τώρα μπορείτε απλώς να επιλέξετε ένα κελί, να εισάγετε τον ίδιο τύπο σε αυτό και να κάνετε κλικ στο κανονικό εισάγετε – Ο δυναμικός πίνακας θα κάνει τα πάντα μόνος του:
Προπαιδεία
Αυτό είναι το παράδειγμα που έδινα όταν μου ζητήθηκε να απεικονίσω τα οφέλη των τύπων πίνακα στο Excel. Τώρα, για να υπολογίσετε ολόκληρο τον Πυθαγόρειο πίνακα, αρκεί να σταθείτε στο πρώτο κελί Β2, να εισαγάγετε εκεί έναν τύπο που πολλαπλασιάζει δύο πίνακες (κάθετο και οριζόντιο σύνολο αριθμών 1..10) και απλά κάντε κλικ στο εισάγετε:
Κόλληση και μετατροπή θήκης
Οι πίνακες μπορούν όχι μόνο να πολλαπλασιαστούν, αλλά και να κολληθούν μεταξύ τους με τον τυπικό τελεστή & (συμφωνητικό). Ας υποθέσουμε ότι πρέπει να εξαγάγουμε το όνομα και το επώνυμο από δύο στήλες και να διορθώσουμε την περίπτωση άλματος στα αρχικά δεδομένα. Το κάνουμε αυτό με έναν σύντομο τύπο που σχηματίζει ολόκληρο τον πίνακα και, στη συνέχεια, εφαρμόζουμε τη συνάρτηση σε αυτόν ΠΡΟΠΝΑΧ (ΚΑΤΑΛΛΗΛΟΣ)για να τακτοποιήσετε το μητρώο:
Συμπέρασμα Top 3
Ας υποθέσουμε ότι έχουμε ένα σωρό αριθμούς από τους οποίους θέλουμε να βγάλουμε τα τρία πρώτα αποτελέσματα, ταξινομώντας τα με φθίνουσα σειρά. Τώρα αυτό γίνεται με έναν τύπο και, πάλι, χωρίς καμία Ctrl+αλλαγή+εισάγετε σαν πριν:
Εάν θέλετε τα αποτελέσματα να μην τοποθετούνται σε στήλη, αλλά σε μια σειρά, τότε αρκεί να αντικαταστήσετε τα άνω και κάτω τελεία (διαχωριστικό γραμμής) σε αυτόν τον τύπο με ένα ερωτηματικό (διαχωριστής στοιχείων σε μία γραμμή). Στην αγγλική έκδοση του Excel, αυτά τα διαχωριστικά είναι ερωτηματικά και κόμματα, αντίστοιχα.
VLOOKUP εξαγωγή πολλαπλών στηλών ταυτόχρονα
Συναρτήσεις VPR (VLOOKUP) τώρα μπορείτε να τραβήξετε τιμές όχι από μία, αλλά από πολλές στήλες ταυτόχρονα - απλώς καθορίστε τους αριθμούς τους (με οποιαδήποτε επιθυμητή σειρά) ως πίνακα στο τρίτο όρισμα της συνάρτησης:
Η συνάρτηση OFFSET επιστρέφει έναν δυναμικό πίνακα
Μία από τις πιο ενδιαφέρουσες και χρήσιμες (μετά το VLOOKUP) συναρτήσεις για την ανάλυση δεδομένων είναι η συνάρτηση ΔΙΑΘΕΣΗ (ΑΝΤΙΣΤΑΘΜΙΖΕΤΑΙ), στο οποίο αφιέρωσα κάποτε ένα ολόκληρο κεφάλαιο στο βιβλίο μου και ένα άρθρο εδώ. Η δυσκολία στην κατανόηση και τον έλεγχο αυτής της συνάρτησης ήταν πάντα ότι ως αποτέλεσμα επέστρεφε έναν πίνακα (εύρος) δεδομένων, αλλά δεν μπορούσαμε να το δούμε, επειδή το Excel ακόμα δεν ήξερε πώς να δουλεύει με πίνακες εκτός πλαισίου.
Τώρα αυτό το πρόβλημα ανήκει στο παρελθόν. Δείτε πώς τώρα, χρησιμοποιώντας έναν μόνο τύπο και έναν δυναμικό πίνακα που επιστρέφεται από το OFFSET, μπορείτε να εξαγάγετε όλες τις σειρές για ένα δεδομένο προϊόν από οποιονδήποτε ταξινομημένο πίνακα:
Ας ρίξουμε μια ματιά στα επιχειρήματά της:
- A1 – κελί εκκίνησης (σημείο αναφοράς)
- ПОИСКПОЗ(F2;A2:A30;0) – υπολογισμός της μετατόπισης από το αρχικό κελί προς τα κάτω – στο πρώτο λάχανο που βρέθηκε.
- 0 – μετατόπιση του «παραθύρου» προς τα δεξιά σε σχέση με το αρχικό κελί
- СЧЁТЕСЛИ(A2:A30;F2) – υπολογισμός του ύψους του επιστρεφόμενου «παραθύρου» – ο αριθμός των γραμμών όπου υπάρχει λάχανο.
- 4 — μέγεθος του «παραθύρου» οριζόντια, δηλαδή έξοδος 4 στηλών
Νέες συναρτήσεις για δυναμικούς πίνακες
Εκτός από την υποστήριξη του μηχανισμού δυναμικής διάταξης σε παλιές συναρτήσεις, αρκετές εντελώς νέες λειτουργίες έχουν προστεθεί στο Microsoft Excel, οι οποίες έχουν ενισχυθεί ειδικά για εργασία με δυναμικούς πίνακες. Ειδικότερα, πρόκειται για:
- ΒΑΘΜΟΣ (ΕΙΔΟΣ) – ταξινομεί το εύρος εισόδου και παράγει έναν δυναμικό πίνακα στην έξοδο
- SORTPO (ΤΑΞΙΝΟΜΗΣΗ ΚΑΤΑ) – μπορεί να ταξινομήσει ένα εύρος κατά τιμές από το άλλο
- FILTER (ΦΙΛΤΡΟ) – ανακτά σειρές από την περιοχή προέλευσης που πληρούν τις καθορισμένες συνθήκες
- UNIK (ΜΟΝΑΔΙΚΟΣ) – εξάγει μοναδικές τιμές από μια περιοχή ή αφαιρεί διπλότυπα
- SLMASSIVE (RANDARRAY) – δημιουργεί έναν πίνακα τυχαίων αριθμών δεδομένου μεγέθους
- ΥΣΤΕΡΟ (ΑΛΛΗΛΟΥΧΙΑ) — σχηματίζει έναν πίνακα από μια ακολουθία αριθμών με ένα δεδομένο βήμα
Περισσότερα για αυτούς - λίγο αργότερα. Αξίζουν ένα ξεχωριστό άρθρο (και όχι ένα) για στοχαστική μελέτη 🙂
συμπεράσματα
Αν έχετε διαβάσει όλα όσα γράφτηκαν παραπάνω, τότε νομίζω ότι έχετε ήδη συνειδητοποιήσει το μέγεθος των αλλαγών που έχουν συμβεί. Τόσα πολλά πράγματα στο Excel μπορούν πλέον να γίνουν ευκολότερα, ευκολότερα και πιο λογικά. Πρέπει να ομολογήσω ότι είμαι λίγο σοκαρισμένος με το πόσα άρθρα θα πρέπει τώρα να διορθωθούν εδώ, σε αυτόν τον ιστότοπο και στα βιβλία μου, αλλά είμαι έτοιμος να το κάνω με ανάλαφρη καρδιά.
Συνοψίζοντας τα αποτελέσματα, pluses δυναμικούς πίνακες, μπορείτε να γράψετε τα εξής:
- Μπορείτε να ξεχάσετε τον συνδυασμό Ctrl+αλλαγή+εισάγετε. Το Excel τώρα δεν βλέπει καμία διαφορά μεταξύ "κανονικών τύπων" και "τύπων πίνακα" και τους αντιμετωπίζει με τον ίδιο τρόπο.
- Σχετικά με τη λειτουργία ΑΝΤΙΠΡΟΣΩΠΟΣ (SUMPRODUCT), που χρησιμοποιήθηκε προηγουμένως για την εισαγωγή τύπων πίνακα χωρίς Ctrl+αλλαγή+εισάγετε μπορείτε επίσης να ξεχάσετε – τώρα είναι αρκετά εύκολο ΑΘΡΟΙΣΜΑ и εισάγετε.
- Έξυπνοι πίνακες και οικείες συναρτήσεις (SUM, IF, VLOOKUP, SUMIFS κ.λπ.) υποστηρίζουν τώρα πλήρως ή εν μέρει δυναμικούς πίνακες.
- Υπάρχει συμβατότητα προς τα πίσω: εάν ανοίξετε ένα βιβλίο εργασίας με δυναμικούς πίνακες σε μια παλιά έκδοση του Excel, θα μετατραπούν σε τύπους πίνακα (σε σγουρές αγκύλες) και θα συνεχίσουν να εργάζονται με το "παλιό στυλ".
Βρέθηκε κάποιος αριθμός μείον:
- Δεν μπορείτε να διαγράψετε μεμονωμένες γραμμές, στήλες ή κελιά από έναν δυναμικό πίνακα, δηλαδή ζει ως μία οντότητα.
- Δεν μπορείτε να ταξινομήσετε έναν δυναμικό πίνακα με τον συνηθισμένο τρόπο Δεδομένα – Ταξινόμηση (Δεδομένα — Ταξινόμηση). Υπάρχει τώρα μια ειδική λειτουργία για αυτό. ΒΑΘΜΟΣ (ΕΙΔΟΣ).
- Ένα δυναμικό εύρος δεν μπορεί να μετατραπεί σε έξυπνο τραπέζι (αλλά μπορείτε να δημιουργήσετε ένα δυναμικό εύρος με βάση έναν έξυπνο πίνακα).
Φυσικά, αυτό δεν είναι το τέλος και είμαι σίγουρος ότι η Microsoft θα συνεχίσει να βελτιώνει αυτόν τον μηχανισμό στο μέλλον.
Πού μπορώ να κατεβάσω;
Και τέλος, η κύρια ερώτηση 🙂
Η Microsoft ανακοίνωσε για πρώτη φορά και έδειξε μια προεπισκόπηση δυναμικών πινάκων στο Excel τον Σεπτέμβριο του 2018 σε μια διάσκεψη Ignite. Τους επόμενους μήνες, πραγματοποιήθηκε μια ενδελεχής δοκιμή και εκτέλεση νέων λειτουργιών, αρχικά γάτες υπαλλήλους της ίδιας της Microsoft και στη συνέχεια σε εθελοντές δοκιμαστές από τον κύκλο των Office Insiders. Φέτος, η ενημέρωση που προσθέτει δυναμικούς πίνακες άρχισε να διατίθεται σταδιακά στους κανονικούς συνδρομητές του Office 365. Για παράδειγμα, το έλαβα μόνο τον Αύγουστο με τη συνδρομή μου στο Office 365 Pro Plus (Μηνιαία στοχευμένη).
Εάν το Excel σας δεν διαθέτει ακόμη δυναμικούς πίνακες, αλλά θέλετε πραγματικά να εργαστείτε με αυτούς, τότε υπάρχουν οι ακόλουθες επιλογές:
- Εάν διαθέτετε συνδρομή στο Office 365, μπορείτε απλώς να περιμένετε μέχρι να φτάσει αυτή η ενημέρωση σε εσάς. Το πόσο γρήγορα θα συμβεί αυτό εξαρτάται από το πόσο συχνά παραδίδονται ενημερώσεις στο Office σας (μία φορά το χρόνο, μία φορά κάθε έξι μήνες, μία φορά το μήνα). Εάν διαθέτετε εταιρικό υπολογιστή, μπορείτε να ζητήσετε από τον διαχειριστή σας να ρυθμίσει τις ενημερώσεις που θα λαμβάνετε πιο συχνά.
- Μπορείτε να συμμετάσχετε στις τάξεις αυτών των εθελοντών δοκιμής του Office Insiders – τότε θα είστε οι πρώτοι που θα λάβετε όλες τις νέες δυνατότητες και λειτουργίες (αλλά υπάρχει πιθανότητα αυξημένου buggy στο Excel, φυσικά).
- Εάν δεν έχετε συνδρομή, αλλά μια αυτόνομη έκδοση του Excel σε κουτί, τότε θα πρέπει να περιμένετε μέχρι την κυκλοφορία της επόμενης έκδοσης του Office και του Excel το 2022, τουλάχιστον. Οι χρήστες τέτοιων εκδόσεων λαμβάνουν μόνο ενημερώσεις ασφαλείας και διορθώσεις σφαλμάτων και όλα τα νέα «καλά» πηγαίνουν πλέον μόνο στους συνδρομητές του Office 365. Θλιβερό αλλά αληθινό 🙂
Σε κάθε περίπτωση, όταν εμφανίζονται δυναμικοί πίνακες στο Excel - μετά από αυτό το άρθρο, θα είστε έτοιμοι για αυτό 🙂
- Τι είναι οι τύποι πίνακα και πώς να τους χρησιμοποιήσετε στο Excel
- Άθροιση παραθύρου (εύρος) χρησιμοποιώντας τη συνάρτηση OFFSET
- 3 τρόποι για να μεταφέρετε έναν πίνακα στο Excel