Φιλτράρισμα οριζόντιων στηλών στο Excel

Εάν δεν είστε αρκετά αρχάριος χρήστης, τότε πρέπει να έχετε ήδη παρατηρήσει ότι το 99% όλων των στοιχείων στο Excel έχει σχεδιαστεί για να λειτουργεί με κάθετους πίνακες, όπου οι παράμετροι ή τα χαρακτηριστικά (πεδία) περνούν από τις στήλες και εντοπίζονται πληροφορίες για αντικείμενα ή συμβάντα στις γραμμές. Συγκεντρωτικοί πίνακες, υποσύνολα, αντιγραφή τύπων με διπλό κλικ – όλα είναι προσαρμοσμένα ειδικά για αυτήν τη μορφή δεδομένων.

Ωστόσο, δεν υπάρχουν κανόνες χωρίς εξαιρέσεις και με μια αρκετά κανονική συχνότητα, με ρωτούν τι πρέπει να κάνω εάν συναντήσει στην εργασία ένας πίνακας με οριζόντιο σημασιολογικό προσανατολισμό ή ένας πίνακας όπου οι γραμμές και οι στήλες έχουν το ίδιο βάρος στη σημασία:

Και αν το Excel εξακολουθεί να ξέρει πώς να ταξινομεί οριζόντια (με την εντολή Δεδομένα – Ταξινόμηση – Επιλογές – Ταξινόμηση στηλών), τότε η κατάσταση με το φιλτράρισμα είναι χειρότερη - απλά δεν υπάρχουν ενσωματωμένα εργαλεία για το φιλτράρισμα στηλών, όχι σειρές στο Excel. Έτσι, εάν αντιμετωπίζετε μια τέτοια εργασία, θα πρέπει να βρείτε λύσεις διαφορετικού βαθμού πολυπλοκότητας.

Μέθοδος 1. Νέα λειτουργία FILTER

Εάν χρησιμοποιείτε τη νέα έκδοση του Excel 2021 ή μια συνδρομή στο Excel 365, μπορείτε να επωφεληθείτε από τη λειτουργία που παρουσιάστηκε πρόσφατα FILTER (ΦΙΛΤΡΟ), το οποίο μπορεί να φιλτράρει τα δεδομένα προέλευσης όχι μόνο κατά σειρές, αλλά και κατά στήλες. Για να λειτουργήσει, αυτή η συνάρτηση απαιτεί μια βοηθητική οριζόντια μονοδιάστατη σειρά-σειρά, όπου κάθε τιμή (TRUE ή FALSE) καθορίζει εάν θα εμφανίσουμε ή, αντίθετα, θα αποκρύψουμε την επόμενη στήλη στον πίνακα.

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

• Ας υποθέσουμε ότι θέλουμε πάντα να εμφανίζουμε την πρώτη και την τελευταία στήλη (κεφαλίδες και σύνολα), οπότε για αυτές στο πρώτο και το τελευταίο κελί του πίνακα ορίζουμε την τιμή = TRUE.
• Για τις υπόλοιπες στήλες, τα περιεχόμενα των αντίστοιχων κελιών θα είναι ένας τύπος που ελέγχει τη συνθήκη που χρειαζόμαστε χρησιμοποιώντας συναρτήσεις И (ΚΑΙ) or OR (Ή). Για παράδειγμα, ότι το σύνολο είναι στην περιοχή από 300 έως 500.

Μετά από αυτό, μένει μόνο να χρησιμοποιήσετε τη λειτουργία FILTER για να επιλέξετε στήλες πάνω από τις οποίες ο βοηθητικός πίνακας μας έχει TRUE τιμή:

Ομοίως, μπορείτε να φιλτράρετε στήλες με βάση μια δεδομένη λίστα. Σε αυτή την περίπτωση, η λειτουργία θα βοηθήσει COUNTIF (COUNTIF), το οποίο ελέγχει τον αριθμό των εμφανίσεων του ονόματος της επόμενης στήλης από την κεφαλίδα του πίνακα στην επιτρεπόμενη λίστα:

Μέθοδος 2. Συγκεντρωτικός πίνακας αντί για το συνηθισμένο

Προς το παρόν, το Excel έχει ενσωματωμένο οριζόντιο φιλτράρισμα κατά στήλες μόνο σε συγκεντρωτικούς πίνακες, οπότε αν καταφέρουμε να μετατρέψουμε τον αρχικό μας πίνακα σε συγκεντρωτικό πίνακα, μπορούμε να χρησιμοποιήσουμε αυτήν την ενσωματωμένη λειτουργία. Για να γίνει αυτό, ο πίνακας πηγών μας πρέπει να πληροί τις ακόλουθες προϋποθέσεις:

• να έχετε μια «σωστή» γραμμή κεφαλίδας μιας γραμμής χωρίς άδεια και συγχωνευμένα κελιά – διαφορετικά δεν θα λειτουργήσει η δημιουργία συγκεντρωτικού πίνακα.
• Μην περιέχουν διπλότυπα στις ετικέτες των γραμμών και στηλών – θα «συμπέσει» στη σύνοψη σε μια λίστα με μοναδικές μόνο τιμές.
• περιέχουν μόνο αριθμούς στο εύρος τιμών (στη διασταύρωση σειρών και στηλών), επειδή ο συγκεντρωτικός πίνακας θα εφαρμόσει σίγουρα κάποιο είδος συνάρτησης συγκέντρωσης σε αυτούς (άθροισμα, μέσος όρος, κ.λπ.) και αυτό δεν θα λειτουργήσει με το κείμενο

Εάν πληρούνται όλες αυτές οι προϋποθέσεις, τότε για να δημιουργηθεί ένας συγκεντρωτικός πίνακας που μοιάζει με τον αρχικό μας πίνακα, αυτός (ο αρχικός) θα πρέπει να επεκταθεί από την εγκάρσια καρτέλα σε ένα επίπεδο (κανονικοποιημένο). Και ο ευκολότερος τρόπος για να το κάνετε αυτό είναι με το πρόσθετο Power Query, ένα ισχυρό εργαλείο μετασχηματισμού δεδομένων ενσωματωμένο στο Excel από το 2016. 

Αυτά είναι:

1. Ας μετατρέψουμε τον πίνακα σε μια «έξυπνη» δυναμική εντολή Αρχική σελίδα – Μορφοποίηση ως πίνακα (Αρχική σελίδα — Μορφοποίηση ως πίνακα).
2. Φόρτωση στο Power Query με την εντολή Δεδομένα – Από πίνακα / Εύρος (Δεδομένα – Από πίνακα / Εύρος).
3. Φιλτράρουμε τη γραμμή με τα σύνολα (η περίληψη θα έχει τα δικά της σύνολα).
4. Κάντε δεξί κλικ στην επικεφαλίδα της πρώτης στήλης και επιλέξτε Καταργήστε τη σύμπτυξη άλλων στηλών (Κατάργηση περιστροφής άλλων στηλών). Όλες οι μη επιλεγμένες στήλες μετατρέπονται σε δύο – το όνομα του υπαλλήλου και την τιμή του δείκτη του.
5. Φιλτράρισμα της στήλης με τα σύνολα που μπήκαν στη στήλη Χαρακτηριστικό.
6. Κατασκευάζουμε έναν πίνακα περιστροφής σύμφωνα με τον επίπεδο (κανονικοποιημένο) πίνακα που προκύπτει με την εντολή Αρχική σελίδα — Κλείσιμο και φόρτωση — Κλείσιμο και φόρτωση σε… (Αρχική — Κλείσιμο & Φόρτωση — Κλείσιμο & Φόρτωση σε…).

Τώρα μπορείτε να χρησιμοποιήσετε τη δυνατότητα φιλτραρίσματος στηλών που είναι διαθέσιμες σε συγκεντρωτικούς πίνακες – τα συνήθη σημάδια επιλογής μπροστά από τα ονόματα και τα στοιχεία Φίλτρα υπογραφής (Φίλτρα ετικέτας) or Φιλτράρει κατά τιμή (Φίλτρα τιμών):

Και φυσικά, κατά την αλλαγή των δεδομένων, θα χρειαστεί να ενημερώσετε το ερώτημά μας και τη σύνοψη με μια συντόμευση πληκτρολογίου Ctrl+άλλος+F5 ή ομάδα Δεδομένα – Ανανέωση όλων (Δεδομένα — Ανανέωση όλων).

Μέθοδος 3. Μακροεντολή σε VBA

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

Ας υποθέσουμε ότι θέλουμε να φιλτράρουμε στήλες εν κινήσει όπου το όνομα του διαχειριστή στην κεφαλίδα του πίνακα ικανοποιεί τη μάσκα που καθορίζεται στο κίτρινο κελί A4, για παράδειγμα, ξεκινά με το γράμμα "A" (δηλαδή, λάβετε "Anna" και "Arthur " σαν άποτέλεσμα). 

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

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

Ιδιωτικό δευτερεύον φύλλο εργασίας_Change(ByVal Target As Range) If Target.Address = "$A$4" Τότε για κάθε κελί στο εύρος ("D2:O2") Εάν κελί = True Τότε cell.EntireColumn.Hidden = False Else cell.EntireColumn.Hidden = True End If Next cell End If End Sub  

Η λογική του είναι η εξής:

• Σε γενικές γραμμές, αυτός είναι ένας χειριστής συμβάντων Φύλλο εργασίας_Αλλαγή, δηλαδή αυτή η μακροεντολή θα εκτελείται αυτόματα σε οποιαδήποτε αλλαγή σε οποιοδήποτε κελί στο τρέχον φύλλο.
• Η αναφορά στο αλλαγμένο κελί θα βρίσκεται πάντα στη μεταβλητή στόχος.
• Αρχικά, ελέγχουμε ότι ο χρήστης έχει αλλάξει ακριβώς το κελί με το κριτήριο (A4) – αυτό γίνεται από τον χειριστή if.
• Μετά ξεκινά ο κύκλος Για κάθε… για επανάληψη σε γκρι κελιά (D2:O2) με τιμές ένδειξης TRUE / FALSE για κάθε στήλη.
• Εάν η τιμή του επόμενου γκρι κελιού είναι TRUE (true), τότε η στήλη δεν είναι κρυφή, διαφορετικά την αποκρύπτουμε (ιδιότητα κρυμμένο).

•  Λειτουργίες δυναμικού πίνακα από το Office 365: FILTER, SORT και UNIC
• Συγκεντρωτικός πίνακας με κεφαλίδα πολλών γραμμών χρησιμοποιώντας το Power Query
• Τι είναι οι μακροεντολές, πώς να τις δημιουργήσετε και να τις χρησιμοποιήσετε