Πολλές φορές, μπορεί να συναντήσετε ένα σύνολο δεδομένων Excel όπου μόνο ένα κελί είναι γεμάτο με δεδομένα και τα κελιά κάτω από αυτό είναι κενά μέχρι την επόμενη τιμή.
Οπως φαίνεται παρακάτω:
Αν και αυτή η μορφή λειτουργεί για μερικούς ανθρώπους, το πρόβλημα με αυτού του είδους τα δεδομένα είναι ότι δεν μπορείτε να τα χρησιμοποιήσετε για τη δημιουργία συγκεντρωτικών πινάκων ή τη χρήση τους σε υπολογισμούς.
Είναι εύκολο να διορθωθεί!
Σε αυτό το σεμινάριο θα σας δείξω πώςΓεμίστε γρήγορα κελιά μέχρι την επόμενη τιμή πλήρωσης στο Excel.
Μπορείτε να το κάνετε εύκολα χρησιμοποιώντας απλές ειδικές τεχνικές διαλόγου Go-To, VBA ή Power Query.
Ας ξεκινήσουμε λοιπόν!
Περιεχόμενα
Μέθοδος 1 – Συμπληρώστε χρησιμοποιώντας τον τύπο Μετάβαση στο Ειδικό +
Ας υποθέσουμε ότι έχετε ένα σύνολο δεδομένων όπως φαίνεται παρακάτω και θέλετε να συμπληρώσετε τα δεδομένα στις στήλες Α και Β.
Στη στήλη Β, ο στόχος είναι να γεμίσετε τον "εκτυπωτή" μέχρι το τελευταίο κενό κελί κάτω από αυτόν, στη συνέχεια, όταν ξεκινήσει ο "σαρωτής", στη συνέχεια να συμπληρώσετε τον "σαρωτή" στο παρακάτω κελί μέχρι να αδειάσει το κελί.
Τα παρακάτω βήματα χρησιμοποιήστε μεταβείτε στο ειδικό για να επιλέξετε όλα τα κενά κελιά και να τα συμπληρώσετε με έναν απλό τύπο:
- Επιλέξτε τα δεδομένα που θέλετε να συμπληρώσετε (A1:D21 στο παράδειγμά μας)
- Μεταβείτε στην καρτέλα "Αρχική σελίδα".
- Στην ομάδα επεξεργασίας, κάντε κλικ στο εικονίδιο Εύρεση και επιλογή (αυτό θα αποκαλύψει περισσότερες επιλογές στο αναπτυσσόμενο μενού)
- Κάντε κλικ στην επιλογή "Μετάβαση".
- Στο παράθυρο διαλόγου Μετάβαση σε που ανοίγει, κάντε κλικ στο κουμπί Ειδικό.Αυτό θα ανοίξει το παράθυρο διαλόγου "Μετάβαση στο Ειδικό".
- Στο παράθυρο διαλόγου Μετάβαση σε Ειδικό, κάντε κλικ στο Κενό
- Κάντε κλικ στο OK
Τα παραπάνω βήματα θα επιλέξουν όλα τα κενά κελιά στο σύνολο δεδομένων (φαίνεται παρακάτω).
Ανάμεσα στα επιλεγμένα κενά κελιά, θα παρατηρήσετε ότι ένα κελί είναι πιο φωτεινό από την υπόλοιπη επιλογή.Αυτό το κελί είναι το ενεργό κελί όπου θέλουμε να εισαγάγουμε τον τύπο.
Μην ανησυχείτε για τη θέση του κελιού στην επιλογή, καθώς η μέθοδός μας θα λειτουργήσει σε κάθε περίπτωση.
Τώρα, εδώ είναι τα βήματα για να συμπληρώσετε τα δεδομένα στα επιλεγμένα κενά κελιά:
- Κάντε κλικ στο πλήκτρο του ίσου (=) στο πληκτρολόγιό σας.Αυτό θα εισαγάγει ένα σύμβολο ίσου στο ενεργό κελί
- Πατήστε το πλήκτρο επάνω βέλους.Αυτό θα εισαγάγει την αναφορά κελιού του κελιού πάνω από το ενεργό κελί.Για παράδειγμα, στην περίπτωσή μας, το B3 είναι το ενεργό κελί και όταν κάνουμε αυτά τα δύο βήματα, εισάγει =B2 στο κελί
- Κρατήστε πατημένο το πλήκτρο Control και πατήστε Enter
Τα παραπάνω βήματα θα εισάγουν αυτόματα όλα τα κενά κελιά με τις παραπάνω τιμές.
Αν και αυτό μπορεί να φαίνεται σαν πάρα πολλά βήματα, μόλις το καταφέρετε, θα μπορείτε να συμπληρώσετε γρήγορα δεδομένα στο Excel μέσα σε δευτερόλεπτα.
Τώρα, υπάρχουν δύο ακόμη πράγματα που πρέπει να γνωρίζετε όταν χρησιμοποιείτε αυτήν τη μέθοδο.
Μετατροπή τύπου σε τιμή
Το πρώτο είναι να βεβαιωθείτε ότι μετατρέπετε τους τύπους σε τιμές (έτσι έχετε στατικές τιμές και να μην χάνετε όταν αλλάζετε τα δεδομένα στο μέλλον).
Αλλαγή μορφής ημερομηνίας
Εάν χρησιμοποιείτε ημερομηνίες στα δεδομένα σας (όπως έκανα στο παράδειγμα δεδομένων μου), θα παρατηρήσετε ότι οι συμπληρωμένες τιμές στη στήλη ημερομηνία είναι αριθμοί αντί για ημερομηνίες.
Εάν η έξοδος στη στήλη ημερομηνία είναι στην επιθυμητή μορφή ημερομηνίας, τότε είστε καλοί και δεν χρειάζεται να κάνετε τίποτα άλλο.
Αλλά εάν αυτές οι ημερομηνίες δεν είναι στη σωστή μορφή, απαιτείται λεπτομέρεια.Ενώ έχετε τη σωστή τιμή, πρέπει απλώς να αλλάξετε τη μορφή ώστε να εμφανίζεται ως ημερομηνία στο κελί.
Εδώ είναι τα βήματα για να το κάνετε αυτό:
- Επιλέξτε στήλες που περιέχουν ημερομηνίες
- Κάντε κλικ στην καρτέλα Αρχική σελίδα
- Στην ομάδα Αριθμοί, κάντε κλικ στο αναπτυσσόμενο μενού Μορφή και επιλέξτε μια μορφή ημερομηνίας.
Εάν χρειάζεται να κάνετε αυτό το γέμισμα από καιρό σε καιρό, προτείνω αυτήν την ειδική τεχνική και τεχνική φόρμουλας Go-To.
Αν και έχει μερικά βήματα, είναι απλό και σας δίνει αποτελέσματα στο σύνολο δεδομένων.
Αλλά αν πρέπει να το κάνετε συχνά, τότε σας προτείνω να δείτε το VBA και τη μέθοδο Power Query που περιγράφεται στη συνέχεια
Μέθοδος 2 - Συμπληρώστε με απλό κώδικα VBA
Μπορείτε να χρησιμοποιήσετε έναν πολύ απλό κώδικα μακροεντολής VBA για να γεμίσετε γρήγορα κελιά στο Excel μέχρι την τελευταία κενή τιμή.
Χρειάζεται να προσθέσετε κώδικα VBA σε ένα αρχείο μόνο μία φορά και, στη συνέχεια, μπορείτε εύκολα να επαναχρησιμοποιήσετε αυτόν τον κώδικα πολλές φορές στο ίδιο βιβλίο εργασίας ή ακόμα και σε πολλά βιβλία εργασίας στο σύστημά σας.
Εδώ είναι ο κώδικας VBA που θα επαναληφθεί σε κάθε κελί της επιλογής και θα συμπληρώσει τυχόν κενά κελιά:
Sub FillDown() Για Κάθε κελί στην Επιλογή Εάν κελί = "" Τότε κελί.FillDown Τέλος Εάν Επόμενο Τέλος Υπο
Ο παραπάνω κώδικας χρησιμοποιεί έναν βρόχο For για επανάληψη σε κάθε κελί της επιλογής.
Στον βρόχο For, χρησιμοποιώ μια συνθήκη If-Then για να ελέγξω εάν το κελί είναι κενό.
Εάν το κελί είναι κενό, συμπληρώνεται με την τιμή από το παραπάνω κελί, εάν όχι, ο βρόχος for αγνοεί το κελί και μετακινείται στο επόμενο κελί.
Τώρα που έχετε τον κωδικό VBA, επιτρέψτε μου να σας δείξω πού να τοποθετήσετε αυτόν τον κώδικα στο Excel:
- Επιλέξτε τα δεδομένα που θέλετε να συμπληρώσετε
- Κάντε κλικ στην καρτέλα προγραμματιστή
- Κάντε κλικ στο εικονίδιο της Visual Basic (ή μπορείτε να χρησιμοποιήσετε τη συντόμευση πληκτρολογίου ALT+F11).Αυτό θα ανοίξει το πρόγραμμα επεξεργασίας της Visual Basic στο Excel
- Στο πρόγραμμα επεξεργασίας της Visual Basic στα αριστερά, θα έχετε το Project Explorer.Εάν δεν το βλέπετε, κάντε κλικ στην επιλογή Προβολή στο μενού και, στη συνέχεια, κάντε κλικ στην Εξερεύνηση έργου
- Εάν έχετε ανοιχτά πολλά βιβλία εργασίας του Excel, το Project Explorer εμφανίζει όλα τα ονόματα των βιβλίων εργασίας.Βρείτε το όνομα του βιβλίου εργασίας όπου έχετε τα δεδομένα.
- Κάντε δεξί κλικ σε οποιοδήποτε αντικείμενο στο βιβλίο εργασίας, μεταβείτε στην επιλογή Εισαγωγή και κάντε κλικ στην ενότητα Μονάδα.Αυτό θα εισαγάγει μια νέα ενότητα για αυτό το βιβλίο εργασίας
- Κάντε διπλό κλικ στο "Module" που μόλις εισαγάγατε στα παραπάνω βήματα.Θα ανοίξει το παράθυρο κώδικα για αυτήν την ενότητα
- Αντιγράψτε και επικολλήστε τον κώδικα VBA στο παράθυρο κώδικα
- Τοποθετήστε τον κέρσορα οπουδήποτε στον κώδικα και εκτελέστε τον κωδικό μακροεντολής κάνοντας κλικ στο πράσινο κουμπί στη γραμμή εργαλείων ή χρησιμοποιώντας τη συντόμευση πληκτρολογίου F5
Τα παραπάνω βήματα θα εκτελέσουν τον κωδικό VBA και τα δεδομένα σας θα συμπληρωθούν.
Εάν θέλετε να χρησιμοποιήσετε ξανά αυτόν τον κώδικα VBA στο μέλλον, θα πρέπει να αποθηκεύσετε αυτό το αρχείο ως βιβλίο εργασίας του Excel με δυνατότητα μακροεντολής (με επέκταση .XLSM)
Ένα άλλο πράγμα που μπορείτε να κάνετε είναι να προσθέσετε αυτήν τη μακροεντολή στη γραμμή εργαλείων γρήγορης πρόσβασης, η οποία είναι πάντα ορατή και μπορείτε να αποκτήσετε πρόσβαση σε αυτήν τη μακροεντολή με ένα μόνο κλικ (σε ένα βιβλίο εργασίας με κώδικα στο backend).
Έτσι, την επόμενη φορά που θα έχετε δεδομένα που πρέπει να συμπληρώσετε, μπορείτε απλώς να κάνετε μια επιλογή και να κάνετε κλικ στο κουμπί μακροεντολής στη γραμμή εργαλείων γρήγορης πρόσβασης.
Μπορείτε επίσης να προσθέσετε αυτήν τη μακροεντολή στο προσωπικό σας βιβλίο εργασίας μακροεντολών και στη συνέχεια να τη χρησιμοποιήσετε σε οποιοδήποτε βιβλίο εργασίας στο σύστημά σας.
Κάντε κλικ εδώ για να διαβάσετε περισσότερα σχετικά με το προσωπικό σας βιβλίο εργασίας μακροεντολών και τον τρόπο προσθήκης κώδικα σε αυτό.
Μέθοδος 3 - Συμπληρώστε χρησιμοποιώντας το Power Query
Ερώτημα ισχύοςΔιαθέτει ενσωματωμένη λειτουργικότητα που σας επιτρέπει να τη συμπληρώνετε με ένα μόνο κλικ.
Συνιστάται όταν εξακολουθείτε να χρησιμοποιείτε το Power Query για να μετατρέψετε δεδομένα ή να συνδυάσετε δεδομένα από πολλά φύλλα εργασίας ή πολλά βιβλία εργασίας.
Ως μέρος μιας υπάρχουσας ροής εργασίας, μπορείτε να γεμίσετε γρήγορα κενά κελιά χρησιμοποιώντας την επιλογή Συμπλήρωση στο Power Query.
Για να χρησιμοποιήσετε το Power Query, συνιστάται τα δεδομένα σας να είναι σε μορφή πίνακα Excel.Εάν δεν μπορείτε να μετατρέψετε τα δεδομένα σε πίνακα Excel, πρέπει να δημιουργήσετε μια περιοχή με όνομα για τα δεδομένα και στη συνέχεια να χρησιμοποιήσετε αυτήν την περιοχή με όνομα στο Power Query.
Παρακάτω έχω ένα σύνολο δεδομένων που έχει μετατραπεί σε πίνακα Excel.Μπορείτε να το κάνετε επιλέγοντας το σύνολο δεδομένων, μεταβαίνοντας στην καρτέλα Εισαγωγή και κάνοντας κλικ στο εικονίδιο Πίνακας.
Ακολουθούν τα βήματα για να χρησιμοποιήσετε το Power Query για να συμπληρώσετε τα δεδομένα μέχρι την επόμενη τιμή:
- Επιλέξτε οποιοδήποτε κελί στο σύνολο δεδομένων
- Κάντε κλικ στην καρτέλα Δεδομένα
- Στην ομάδα Λήψη και μετατροπή δεδομένων, κάντε κλικ στην επιλογή Από φύλλο εργασίας.Αυτό θα ανοίξει το πρόγραμμα επεξεργασίας Power Query.Σημειώστε ότι τα κενά κελιά θα εμφανίζουν την τιμή "null" στο Power Query
- Στο Power Query Editor, επιλέξτε τη στήλη που θέλετε να συμπληρώσετε.Για να το κάνετε αυτό, κρατήστε πατημένο το πλήκτρο Control και κάντε κλικ στην κεφαλίδα της στήλης που θέλετε να επιλέξετε.Στο παράδειγμά μας θα είναι οι στήλες ημερομηνίας και προϊόντος.
- Κάντε δεξί κλικ σε οποιονδήποτε επιλεγμένο τίτλο
- Μεταβείτε στην επιλογή Συμπλήρωση και κάντε κλικ στο Κάτω.Αυτό θα συμπληρώσει όλα τα κενά κελιά με δεδομένα
- Κάντε κλικ στην καρτέλα Αρχείο και, στη συνέχεια, κάντε κλικ στην επιλογή Κλείσιμο και φόρτωση.Αυτό θα εισαγάγει ένα νέο φύλλο εργασίας με τον πίνακα αποτελεσμάτων στο βιβλίο εργασίας
Αν και αυτή η προσέγγιση μπορεί να ακούγεται υπερβολική, ένα από τα μεγάλα πλεονεκτήματα της χρήσης του Power Query είναι ότι σας επιτρέπει να ενημερώνετε γρήγορα τα δεδομένα που προκύπτουν όταν αλλάζουν τα αρχικά δεδομένα.
Για παράδειγμα, εάν προσθέσετε περισσότερες εγγραφές στα αρχικά δεδομένα, δεν χρειάζεται να κάνετε ξανά όλα τα παραπάνω.Μπορείτε απλώς να κάνετε δεξί κλικ στον πίνακα που προκύπτει και να κάνετε κλικ στην ανανέωση.
Ενώ η μέθοδος Power Query λειτουργεί καλά, είναι καλύτερο να τη χρησιμοποιείτε όταν χρησιμοποιείτε ήδη το Power Query στη ροή εργασίας σας.
Εάν έχετε μόνο ένα σύνολο δεδομένων με κενά κελιά για συμπλήρωση, είναι πιο βολικό να χρησιμοποιήσετε την ειδική μέθοδο Μετάβαση σε ή τη μέθοδο VBA (που περιγράφεται παραπάνω).
Έτσι, μπορείτε να χρησιμοποιήσετε αυτές τις τρεις απλές μεθόδους για ναΣυμπληρώστε τα κενά κελιά μέχρι την επόμενη τιμή στο Excel.
Ελπίζω να σας φάνηκε χρήσιμο αυτό το σεμινάριο.
