Παρακολουθήστε βίντεο - Συμπληρώστε το Excel
Πολλές φορές, μπορεί να συναντήσετε ένα σύνολο δεδομένων όπου μόνο ένα κελί είναι γεμάτο με δεδομένα και τα κελιά κάτω από αυτό είναι κενά μέχρι την επόμενη τιμή.
Κάτι όπως φαίνεται παρακάτω:
Ενώ αυτή η μορφή λειτουργεί για ορισμένα άτομα, το πρόβλημα με αυτά τα είδη δεδομένων είναι ότι δεν μπορείτε να τα χρησιμοποιήσετε για να δημιουργήσετε Συγκεντρωτικούς Πίνακες ή να τα χρησιμοποιήσετε σε υπολογισμούς.
Και αυτό έχει μια εύκολη λύση!
Σε αυτό το σεμινάριο, θα σας δείξω πώς να το κάνετε γρήγορα συμπληρώστε κελιά στο Excel μέχρι την επόμενη συμπληρωμένη τιμή.
Μπορείτε εύκολα να το κάνετε αυτό χρησιμοποιώντας μια απλή τεχνική παράθυρο διαλόγου Go-To, VBA ή Power Query.
Ας ξεκινήσουμε λοιπόν!
Μέθοδος 1 - Συμπληρώστε χρησιμοποιώντας το Go To Special + Formula
Ας υποθέσουμε ότι έχετε ένα σύνολο δεδομένων όπως φαίνεται παρακάτω και θέλετε να συμπληρώσετε δεδομένα στη στήλη Α και τη στήλη Β.
Στη στήλη Β, ο στόχος είναι να συμπληρώσετε τον "Εκτυπωτή" μέχρι το τελευταίο κενό κελί κάτω από αυτό και, στη συνέχεια, όταν ξεκινήσει ο "Σαρωτής", στη συνέχεια γεμίστε τον "Σαρωτή" στα παρακάτω κελιά μέχρι να αδειάσουν τα κελιά.
Κάτω από τα βήματα που πρέπει να χρησιμοποιήσετε, μεταβείτε στο ειδικό για να επιλέξετε όλα τα κενά κελιά και συμπληρώστε χρησιμοποιώντας έναν απλό τύπο:
- Επιλέξτε τα δεδομένα στα οποία θέλετε να συμπληρώσετε (A1: D21 στο παράδειγμά μας)
- Μεταβείτε στην καρτέλα "Αρχική σελίδα"
- Στην ομάδα Επεξεργασία, κάντε κλικ στο εικονίδιο «Εύρεση & επιλογή» (αυτό θα εμφανίσει μερικές περισσότερες επιλογές στο αναπτυσσόμενο μενού)
- Κάντε κλικ στην επιλογή «Μετάβαση σε»
- Στο παράθυρο διαλόγου Μετάβαση σε που ανοίγει, κάντε κλικ στο κουμπί Ειδικά. Αυτό θα ανοίξει το παράθυρο διαλόγου «Μετάβαση στα ειδικά»
- Στο παράθυρο διαλόγου Μετάβαση στα ειδικά, κάντε κλικ στην επιλογή Κενά
- Κάντε κλικ στο OK
Τα παραπάνω βήματα θα επιλέξουν όλα τα κενά κελιά στο σύνολο δεδομένων (όπως φαίνεται παρακάτω).
Στα κενά κελιά που είναι επιλεγμένα, θα παρατηρήσετε ότι ένα κελί είναι ελαφρύτερο από το υπόλοιπο της επιλογής. Αυτό το κελί είναι το ενεργό κελί όπου θα εισαγάγουμε έναν τύπο.
Μην ανησυχείτε για το πού βρίσκεται το κελί στην επιλογή, καθώς η μέθοδος μας θα λειτουργούσε σε κάθε περίπτωση.
Τώρα, παρακάτω είναι τα βήματα για να συμπληρώσετε τα δεδομένα στα επιλεγμένα κενά κελιά:
- Πατήστε το πλήκτρο ίσο προς (=) στο πληκτρολόγιό σας. Αυτό θα εισαγάγει ένα σημάδι ίσου προς το ενεργό κελί
- Πατήστε το πλήκτρο επάνω βέλους. Αυτό θα εισαγάγει την αναφορά κελιού του κελιού πάνω από το ενεργό κελί. Για παράδειγμα, στην περίπτωσή μας, το Β3 είναι το ενεργό κελί και όταν κάνουμε αυτά τα δύο βήματα, εισάγει = Β2 στο κελί
- Κρατήστε πατημένο το πλήκτρο ελέγχου και πατήστε το πλήκτρο Enter
Τα παραπάνω βήματα θα εισάγουν αυτόματα όλα τα κενά κελιά με την τιμή πάνω από αυτά.
Παρόλο που αυτό μπορεί να μοιάζει με πάρα πολλά βήματα, μόλις τελειώσετε με αυτήν τη μέθοδο, θα μπορείτε να συμπληρώσετε γρήγορα δεδομένα στο Excel μέσα σε λίγα δευτερόλεπτα.
Τώρα, υπάρχουν δύο ακόμη πράγματα που πρέπει να προσέξετε όταν χρησιμοποιείτε αυτήν τη μέθοδο.
Μετατροπή τύπων σε τιμές
Το πρώτο είναι να βεβαιωθείτε ότι μετατρέπετε τύπους σε τιμές (έτσι ώστε να έχετε στατικές τιμές και τα πράγματα να μην μπερδεύονται σε περίπτωση που αλλάξετε δεδομένα στο μέλλον).
Παρακάτω είναι ένα βίντεο που θα σας δείξει πώς να μετατρέψετε γρήγορα τύπους σε τιμές στο Excel.
Αλλάξτε τη μορφή ημερομηνίας
Σε περίπτωση που χρησιμοποιείτε ημερομηνίες στα δεδομένα σας (όπως είμαι στα δεδομένα του παραδείγματος), θα παρατηρήσετε ότι οι συμπληρωμένες τιμές στη στήλη ημερομηνίας είναι αριθμοί και όχι ημερομηνίες.
Εάν η έξοδος στη στήλη ημερομηνίας είναι στην επιθυμητή μορφή ημερομηνίας, είστε καλοί και δεν χρειάζεται να κάνετε τίποτα άλλο.
Αλλά αν αυτά δεν είναι στη σωστή μορφή ημερομηνίας, πρέπει να κάνετε μια μικρή προσαρμογή. Ενώ έχετε τις σωστές τιμές, απλά πρέπει να αλλάξετε τη μορφή, έτσι ώστε να εμφανίζεται ως ημερομηνία στο κελί.
Παρακάτω είναι τα βήματα για να το κάνετε αυτό:
- Επιλέξτε τη στήλη που περιέχει τις ημερομηνίες
- Κάντε κλικ στην καρτέλα Αρχική σελίδα
- Στην ομάδα Αριθμός, κάντε κλικ στο αναπτυσσόμενο μενού Μορφή και, στη συνέχεια, επιλέξτε τη μορφή ημερομηνίας.
Εάν πρέπει να κάνετε αυτό το συμπλήρωμα μερικές φορές, σας συνιστώ να χρησιμοποιήσετε αυτήν την ειδική και ειδική τεχνική Go-To.
Παρόλο που έχει μερικά βήματα, είναι απλό και σας δίνει το αποτέλεσμα εκεί στο σύνολο δεδομένων σας.
Αλλά σε περίπτωση που πρέπει να το κάνετε αυτό αρκετά συχνά, τότε προτείνω να δείτε τις μεθόδους VBA και Power Query που καλύπτονται στη συνέχεια
Μέθοδος 2 - Συμπληρώστε χρησιμοποιώντας έναν απλό κώδικα VBA
Μπορείτε να χρησιμοποιήσετε έναν πραγματικά απλό κώδικα μακροεντολής VBA για να συμπληρώσετε γρήγορα κελιά στο Excel μέχρι την τελευταία κενή τιμή.
Χρειάζεται μόνο να προσθέσετε τον κωδικό VBA στο αρχείο μία φορά και, στη συνέχεια, μπορείτε εύκολα να επαναχρησιμοποιήσετε τον κώδικα πολλές φορές στο ίδιο βιβλίο εργασίας ή ακόμη και σε πολλά βιβλία εργασίας στο σύστημά σας.
Παρακάτω είναι ο κώδικας VBA που θα περάσει από κάθε κελί στην επιλογή και θα συμπληρώσει όλα τα κελιά που είναι κενά:
'Αυτός ο κώδικας VBA δημιουργήθηκε από το Sumit Bansal από το TrumpExcel.com Sub FillDown () Για κάθε κελί στην επιλογή If cell = "" Τότε cell.FillDown End If Next End Sub
Ο παραπάνω κώδικας χρησιμοποιεί έναν βρόχο For για να περάσει από κάθε κελί της επιλογής.
Μέσα στον βρόχο For, έχω χρησιμοποιήσει μια συνθήκη If-Then που ελέγχει εάν το κελί είναι άδειο ή όχι.
Σε περίπτωση που το κελί είναι κενό, συμπληρώνεται με την τιμή από το παραπάνω κελί και σε περίπτωση που δεν είναι κενό, τότε ο βρόχος for απλά αγνοεί το κελί και μετακινείται στο επόμενο.
Τώρα που έχετε τον κωδικό VBA, επιτρέψτε μου να σας δείξω πού να τοποθετήσετε αυτόν τον κώδικα στο Excel:
- Επιλέξτε τα δεδομένα όπου θέλετε να συμπληρώσετε
- Κάντε κλικ στην καρτέλα Προγραμματιστής
- Κάντε κλικ στο εικονίδιο της Visual Basic (ή μπορείτε να χρησιμοποιήσετε τη συντόμευση πληκτρολογίου ALT + F11). Αυτό θα ανοίξει το Visual Basic Editor στο Excel
- Στο Visual Basic Editor, στα αριστερά, θα έχετε το Project Explorer. Εάν δεν το βλέπετε, κάντε κλικ στην επιλογή «Προβολή» στο μενού και, στη συνέχεια, κάντε κλικ στο Project Explorer
- Σε περίπτωση που έχετε ανοίξει πολλά βιβλία εργασίας Excel, το Project Explorer θα σας δείξει όλα τα ονόματα του βιβλίου εργασίας. Εντοπίστε το όνομα του βιβλίου εργασίας όπου έχετε τα δεδομένα.
- Κάντε δεξί κλικ σε οποιοδήποτε από τα αντικείμενα του βιβλίου εργασίας, μεταβείτε στην ενότητα Εισαγωγή και, στη συνέχεια, κάντε κλικ στην ενότητα. Αυτό θα εισαγάγει μια νέα ενότητα για αυτό το βιβλίο εργασίας
- Κάντε διπλό κλικ στο ‘Module’ που μόλις εισαγάγατε στο παραπάνω βήμα. Θα ανοίξει το παράθυρο κώδικα για αυτήν την ενότητα
- Αντιγράψτε-επικολλήστε τον κώδικα VBA στο παράθυρο κώδικα
- Τοποθετήστε τον κέρσορα οπουδήποτε στον κώδικα και, στη συνέχεια, εκτελέστε τον κώδικα μακροεντολής κάνοντας κλικ στο πράσινο κουμπί στη γραμμή εργαλείων ή χρησιμοποιώντας τη συντόμευση πληκτρολογίου F5
Τα παραπάνω βήματα θα εκτελέσουν τον κωδικό VBA και τα δεδομένα σας θα συμπληρωθούν.
Εάν θέλετε να χρησιμοποιήσετε ξανά αυτόν τον κώδικα VBA στο μέλλον, πρέπει να αποθηκεύσετε αυτό το αρχείο ως βιβλίο εργασίας Excel με μακροεντολή (με επέκταση .XLSM)
Ένα άλλο πράγμα που μπορείτε να κάνετε είναι να προσθέσετε αυτήν τη μακροεντολή στη γραμμή εργαλείων γρήγορης πρόσβασης, η οποία είναι πάντα ορατή και θα έχετε πρόσβαση σε αυτήν τη μακροεντολή με ένα μόνο κλικ (στο βιβλίο εργασίας όπου έχετε τον κωδικό στο πίσω μέρος).
Έτσι, την επόμενη φορά που θα έχετε δεδομένα όπου πρέπει να συμπληρώσετε, το μόνο που χρειάζεται να κάνετε είναι να κάνετε την επιλογή και, στη συνέχεια, κάντε κλικ στο κουμπί μακροεντολής στη γραμμή εργαλείων γρήγορης πρόσβασης.
Μπορείτε επίσης να προσθέσετε αυτήν τη μακροεντολή στο Προσωπικό βιβλίο μακροεντολών εργασίας και στη συνέχεια να τη χρησιμοποιήσετε σε οποιοδήποτε βιβλίο εργασίας στο σύστημά σας.
Κάντε κλικ εδώ για να διαβάσετε περισσότερα σχετικά με τα προσωπικά βιβλία εργασίας μακροεντολών και πώς να προσθέσετε κώδικα σε αυτά.
Μέθοδος 3 - Συμπληρώστε χρησιμοποιώντας το Power Query
Το Power Query έχει μια ενσωματωμένη λειτουργικότητα που σας επιτρέπει να συμπληρώσετε με ένα μόνο κλικ.
Η χρήση του Power Query συνιστάται όταν το χρησιμοποιείτε ούτως ή άλλως για να μετατρέψετε τα δεδομένα σας ή να συνδυάσετε δεδομένα από πολλά φύλλα εργασίας ή πολλά βιβλία εργασίας.
Ως μέρος της υπάρχουσας ροής εργασίας, μπορείτε να χρησιμοποιήσετε την επιλογή συμπλήρωσης στο Power Query για να γεμίσετε γρήγορα τα κενά κελιά.
Για να χρησιμοποιήσετε το Power Query, συνιστάται τα δεδομένα σας να είναι σε μορφή πίνακα Excel. Εάν δεν μπορείτε να μετατρέψετε τα δεδομένα σας σε πίνακα Excel, θα πρέπει να δημιουργήσετε μια ονομαστική περιοχή για τα δεδομένα και, στη συνέχεια, να χρησιμοποιήσετε αυτήν την ονομαστική περιοχή στο Power Query.
Παρακάτω έχω το σύνολο δεδομένων που έχω μετατρέψει ήδη σε πίνακα Excel. Μπορείτε να το κάνετε αυτό επιλέγοντας το σύνολο δεδομένων, μεταβαίνοντας στην καρτέλα Εισαγωγή και, στη συνέχεια, κάνοντας κλικ στο εικονίδιο του πίνακα.
Κάτω από τα βήματα για να χρησιμοποιήσετε το Power Query για να συμπληρώσετε δεδομένα μέχρι την επόμενη τιμή:
- Επιλέξτε οποιοδήποτε κελί στο σύνολο δεδομένων
- Κάντε κλικ στην καρτέλα Δεδομένα
- Στην ομάδα Get & Transform Data, κάντε κλικ στο «From Sheet». Αυτό θα ανοίξει τον επεξεργαστή Power Query. Σημειώστε ότι τα κενά κελιά θα εμφανίζουν την τιμή «null» στο Power Query
- Στον επεξεργαστή Power Query, επιλέξτε τις στήλες για τις οποίες θέλετε να συμπληρώσετε. Για να το κάνετε αυτό, κρατήστε πατημένο το πλήκτρο ελέγχου και, στη συνέχεια, κάντε κλικ στην κεφαλίδα των στηλών που θέλετε να επιλέξετε. Στο παράδειγμά μας, θα ήταν οι στήλες Ημερομηνία και προϊόν.
- Κάντε δεξί κλικ σε οποιαδήποτε από τις επιλεγμένες κεφαλίδες
- Μεταβείτε στην επιλογή "Συμπλήρωση" και, στη συνέχεια, κάντε κλικ στο "Κάτω". Αυτό θα συμπληρώσει τα δεδομένα σε όλα τα κενά κελιά
- Κάντε κλικ στην καρτέλα Αρχείο και, στη συνέχεια, κάντε κλικ στο «Κλείσιμο & Φόρτωση». Αυτό θα εισαγάγει ένα νέο φύλλο εργασίας στο βιβλίο εργασίας με τον πίνακα που προκύπτει
Παρόλο που αυτή η μέθοδος μπορεί να ακούγεται υπερβολική, ένα μεγάλο πλεονέκτημα της χρήσης του Power Query είναι ότι σας επιτρέπει να ενημερώσετε γρήγορα τα δεδομένα που προκύπτουν σε περίπτωση που αλλάξουν τα αρχικά σας δεδομένα.
Για παράδειγμα, εάν προσθέσετε περισσότερες εγγραφές στα αρχικά δεδομένα, δεν χρειάζεται να κάνετε ξανά όλα τα παραπάνω. Μπορείτε απλά να κάνετε δεξί κλικ στον πίνακα που προκύπτει και στη συνέχεια να κάνετε κλικ στο Ανανέωση.
Ενώ η μέθοδος Power Query λειτουργεί καλά, είναι καλύτερο να τη χρησιμοποιήσετε όταν χρησιμοποιείτε ήδη το Power Query στη ροή εργασίας σας.
Εάν έχετε απλώς ένα σύνολο δεδομένων όπου θέλετε να συμπληρώσετε τα κενά κελιά, τότε η χρήση της ειδικής μεθόδου Μετάβαση ή η μέθοδος VBA (που αναφέρεται παραπάνω) θα ήταν πιο βολική.
Αυτοί είναι λοιπόν τρεις απλοί τρόποι που μπορείτε να χρησιμοποιήσετε συμπληρώστε τα κενά κελιά μέχρι την επόμενη τιμή στο Excel.
Ελπίζω να βρήκατε χρήσιμο αυτό το σεμινάριο.
