Αφαίρεση κενών σειρών και στηλών στα δεδομένα

Οι κενές γραμμές και στήλες μπορεί να είναι ενοχλητικοί στους πίνακες σε πολλές περιπτώσεις. Οι τυπικές συναρτήσεις για ταξινόμηση, φιλτράρισμα, σύνοψη, δημιουργία συγκεντρωτικών πινάκων κ.λπ. αντιλαμβάνονται τις κενές γραμμές και στήλες ως αλλαγή πίνακα, χωρίς να συλλέγουν τα δεδομένα που βρίσκονται πιο πίσω τους. Εάν υπάρχουν πολλά τέτοια κενά, τότε η χειροκίνητη αφαίρεσή τους μπορεί να είναι πολύ δαπανηρή και δεν θα λειτουργήσει να αφαιρεθούν ταυτόχρονα "χύμα" χρησιμοποιώντας φιλτράρισμα, επειδή το φίλτρο θα "σκοντάψει" επίσης στα διαλείμματα.

Ας δούμε διάφορους τρόπους επίλυσης αυτού του προβλήματος.

Μέθοδος 1. Αναζήτηση κενών κελιών

Αυτός μπορεί να μην είναι ο πιο βολικός, αλλά σίγουρα ο ευκολότερος τρόπος αξίζει να αναφερθεί.

Ας υποθέσουμε ότι έχουμε να κάνουμε με έναν τέτοιο πίνακα που περιέχει πολλές κενές γραμμές και στήλες μέσα (επισημαίνεται για λόγους σαφήνειας):

Ας υποθέσουμε ότι είμαστε σίγουροι ότι η πρώτη στήλη του πίνακα μας (στήλη Β) περιέχει πάντα το όνομα μιας πόλης. Στη συνέχεια, τα άδεια κελιά σε αυτήν τη στήλη θα είναι σημάδι περιττών κενών σειρών. Για να τα αφαιρέσετε γρήγορα όλα, κάντε τα εξής:

1. Επιλέξτε περιοχή με πόλεις (B2:B26)
2. Πατήστε το πλήκτρο F5 και στη συνέχεια πατήστε Επισημάνετε (Μετάβαση στο Ειδικό) ή επιλέξτε στην καρτέλα Αρχική σελίδα — Εύρεση και επιλογή — Επιλέξτε μια ομάδα κελιών (Αρχική — Εύρεση&Επιλογή — Μετάβαση στην ειδική).
3. Στο παράθυρο που ανοίγει, επιλέξτε την επιλογή Άδεια κελιά (Κενά) και πατήστε το πλήκτρο OK – θα πρέπει να επιλεγούν όλα τα άδεια κελιά στην πρώτη στήλη του πίνακα μας.
4. Τώρα επιλέξτε στην καρτέλα Αρχική εντολή Διαγραφή – Διαγραφή σειρών από το φύλλο (Διαγραφή — Διαγραφή σειρών) ή πατήστε τη συντόμευση πληκτρολογίου Ctrl+πλην – και το έργο μας λύθηκε.

Φυσικά, μπορείτε να απαλλαγείτε από κενές στήλες με τον ίδιο ακριβώς τρόπο, χρησιμοποιώντας την κεφαλίδα του πίνακα ως βάση.

Μέθοδος 2: Αναζήτηση για κενές σειρές

Όπως ίσως έχετε ήδη καταλάβει, η προηγούμενη μέθοδος θα λειτουργήσει μόνο εάν τα δεδομένα μας περιέχουν απαραιτήτως πλήρως γεμάτες σειρές και στήλες, στις οποίες μπορείτε να κολλήσετε κατά την αναζήτηση κενών κελιών. Τι γίνεται όμως αν δεν υπάρχει τέτοια εμπιστοσύνη και τα δεδομένα ενδέχεται να περιέχουν και άδεια κελιά;

Ρίξτε μια ματιά στον παρακάτω πίνακα, για παράδειγμα, για μια τέτοια περίπτωση:

Εδώ η προσέγγιση θα είναι λίγο πιο δύσκολη:

1. Εισαγάγετε στο κελί A2 τη συνάρτηση COUNT (COUNTA), το οποίο θα υπολογίσει τον αριθμό των γεμισμένων κελιών στη σειρά στα δεξιά και θα αντιγράψει αυτόν τον τύπο σε ολόκληρο τον πίνακα:
2. Επιλέξτε το κελί A2 και ενεργοποιήστε το φίλτρο με την εντολή Δεδομένα – Φίλτρο (Δεδομένα — Φίλτρο) ή συντόμευση πληκτρολογίου Ctrl+αλλαγή+L.
3. Ας φιλτράρουμε τα μηδενικά με βάση την υπολογιζόμενη στήλη, δηλαδή όλες τις σειρές όπου δεν υπάρχουν δεδομένα.
4. Απομένει να επιλέξετε τις φιλτραρισμένες γραμμές και να τις διαγράψετε με την εντολή Αρχική — Διαγραφή -' Διαγραφή σειρών από το φύλλο (Αρχική — Διαγραφή — Διαγραφή σειρών) ή συντόμευση πληκτρολογίου Ctrl+πλην.
5. Απενεργοποιούμε το φίλτρο και παίρνουμε τα δεδομένα μας χωρίς κενές γραμμές.

Δυστυχώς, αυτό το τέχνασμα δεν μπορεί πλέον να γίνει με στήλες – το Excel δεν έχει μάθει ακόμη πώς να φιλτράρει κατά στήλες.

Μέθοδος 3. Μακροεντολή για να αφαιρέσετε όλες τις κενές γραμμές και στήλες σε ένα φύλλο

Μπορείτε επίσης να χρησιμοποιήσετε μια απλή μακροεντολή για να αυτοματοποιήσετε αυτήν την εργασία. Πατήστε τη συντόμευση πληκτρολογίου άλλος+F11 ή επιλέξτε από την καρτέλα προγραμματιστή — Visual Basic (Προγραμματιστής — Visual Basic Editor). Εάν καρτέλες προγραμματιστή δεν είναι ορατό, μπορείτε να το ενεργοποιήσετε μέσω Αρχείο – Επιλογές – Ρύθμιση κορδέλας (Αρχείο — Επιλογές — Προσαρμογή κορδέλας).

Στο παράθυρο του προγράμματος επεξεργασίας της Visual Basic που ανοίγει, επιλέξτε την εντολή μενού Εισαγωγή – Ενότητα και στην κενή ενότητα που εμφανίζεται, αντιγράψτε και επικολλήστε τις ακόλουθες γραμμές:

   Sub DeleteEmpty() Dim r As Long, rng As Range 'удаляем пустые строки Για r = 1 To ActiveSheet.UsedRange.Row - 1 + ActiveSheet.UsedRange.Rows.Count If Application.CountA(Rows(r)) = 0 Στη συνέχεια, εάν rng Δεν είναι τίποτα Τότε Ορίστε rng = Σειρές(r) Άλλο Ορίστε rng = Ένωση(rng, Σειρές(r)) Τέλος Αν Επόμενο r Εάν Όχι rng Δεν Είναι Τίποτα Τότε rng.Διαγραφή 'удаляем пустые столбцы Ορισμός rng = Τίποτα Για r = 1 Προς ActiveSheet.UsedRange.Column - 1 + ActiveSheet.UsedRange.Columns.Count If Application.CountA(Columns(r)) = 0 Τότε εάν το rng δεν είναι τίποτα, τότε ορίστε rng = Columns(r) Else Set rng = Union(rng, Columns( r)) Τέλος Αν Επόμενο r Αν Όχι Το rng Δεν Είναι Τίποτα Τότε rng.Διαγραφή Τελ. Υπο  

Κλείστε το πρόγραμμα επεξεργασίας και επιστρέψτε στο Excel. 

Τώρα χτυπήστε συνδυασμό άλλος+F8 ή κουμπί Μακροεντολές αυτί προγραμματιστή. Το παράθυρο που ανοίγει θα εμφανίσει όλες τις μακροεντολές που είναι διαθέσιμες αυτήν τη στιγμή για εκτέλεση, συμπεριλαμβανομένης της μακροεντολής που μόλις δημιουργήσατε. DeleteEmpty. Επιλέξτε το και κάντε κλικ στο κουμπί τρέξιμο (τρέξιμο) – όλες οι κενές γραμμές και στήλες στο φύλλο θα διαγραφούν αμέσως.

Μέθοδος 4: Power Query

Ένας άλλος τρόπος για να λύσουμε το πρόβλημά μας και ένα πολύ συνηθισμένο σενάριο είναι να αφαιρέσουμε κενές γραμμές και στήλες στο Power Query.

Αρχικά, ας φορτώσουμε τον πίνακα μας στο Power Query Query Editor. Μπορείτε να το μετατρέψετε σε δυναμικό «έξυπνο» με τη συντόμευση πληκτρολογίου Ctrl+T ή απλώς να επιλέξετε το εύρος δεδομένων μας και να του δώσετε ένα όνομα (για παράδειγμα ημερομηνία) στη γραμμή τύπων, μετατροπή σε όνομα:

Τώρα χρησιμοποιούμε την εντολή Δεδομένα – Λήψη δεδομένων – Από πίνακα / περιοχή (Δεδομένα – Λήψη δεδομένων – Από πίνακα / περιοχή) και φορτώνουμε τα πάντα στο Power Query:

Τότε όλα είναι απλά:

1. Διαγράφουμε κενές γραμμές με την εντολή Αρχική – Μείωση γραμμών – Διαγραφή γραμμών – Διαγραφή κενών γραμμών (Αρχική – Αφαίρεση Γραμμών – Αφαίρεση κενών γραμμών).
2. Κάντε δεξί κλικ στην επικεφαλίδα της πρώτης στήλης Πόλη και επιλέξτε την εντολή Unpivot Other Columns από το μενού περιβάλλοντος. Το τραπέζι μας θα είναι, όπως τεχνικά σωστά ονομάζεται, κανονικοποιήθηκε – μετατρέπεται σε τρεις στήλες: πόλη, μήνας και τιμή από τη διασταύρωση της πόλης και μήνας από τον αρχικό πίνακα. Η ιδιαιτερότητα αυτής της λειτουργίας στο Power Query είναι ότι παρακάμπτει τα κενά κελιά στα δεδομένα προέλευσης, αυτό που χρειαζόμαστε:
3. Τώρα εκτελούμε την αντίστροφη λειτουργία – επαναφέρουμε τον πίνακα που προκύπτει σε δισδιάστατο για να τον επαναφέρουμε στην αρχική του μορφή. Επιλέξτε τη στήλη με μήνες και στην καρτέλα Μεταμόρφωση επιλέξτε μια ομάδα Συγκεντρωτική στήλη (Μετατροπή — Συγκεντρωτική στήλη). Στο παράθυρο που ανοίγει, ως στήλη τιμών, επιλέξτε την τελευταία (Τιμή) και στις σύνθετες επιλογές - τη λειτουργία Μην συγκεντρώνετε (Μην συγκεντρώνετε):
4. Απομένει να ανεβάσουμε το αποτέλεσμα πίσω στο Excel με την εντολή Αρχική σελίδα — Κλείσιμο και φόρτωση — Κλείσιμο και φόρτωση σε… (Αρχική — Κλείσιμο&Φόρτωση — Κλείσιμο&Φόρτωση σε…)

• Τι είναι μια μακροεντολή, πώς λειτουργεί, πού να αντιγράψετε το κείμενο μιας μακροεντολής, πώς να εκτελέσετε μια μακροεντολή;
• Συμπλήρωση όλων των κενών κελιών στη λίστα με τις τιμές των γονικών κελιών
• Αφαίρεση όλων των κενών κελιών από μια δεδομένη περιοχή
• Κατάργηση όλων των κενών σειρών σε ένα φύλλο εργασίας με το πρόσθετο PLEX