Φιλτραρισμένη επικύρωση

Έχω εισάγει επικύρωση και κάνω χρήση  μιας πτυσσόμενης λίστας, στη βάση που παρακολουθώ τις κινήσεις του πελατολογίου μου. Η χρήση της επικύρωσης με προστατεύει από τα λάθη και ότι αυτά συνεπάγονται, αλλά ήδη οι πελάτες μου είναι περισσότεροι από 500 και είναι μεγάλος μπελάς η αναζήτηση του ονόματος του πελάτη σε μία τόσο μεγάλη λίστα. Τι ωραία θα ήταν, τώρα που θέλω να εισάγω τον πελάτη μου κύριο Επικυρωμένο Τάκη, η  πτυσσόμενη λίστα να εμφάνιζε μόνο το ονόματα από Ε και να διαλέξω εύκολα και γρήγορα από αυτά το κύριο Τάκη!
Μια φιλτραρισμένη λίστα λοιπόν. Η βάση μου είναι στο φύλλο sheet του βιβλίου και η επικύρωση όπου εισάγω τα ονόματα είναι στη στήλη Α. Βρίσκομαι στο κελί Α2011, πληκτρολογώ Ε,  ανοίγω δεξιά στο κελί, το πλήκτρο της πτυσσόμενης και ναι!  θαύμα!  εμφανίζονται μόνο οι πελάτες από Ε και διαλέγω τον κύριο Επικυρωμένο Τάκη.
Πως έφτασα εδώ;   Αν σας ενδιαφέρει παρακολουθήστε:

Θα χρειαστώ ένα βοηθητικό φύλλο. Ας το ονομάσω table.
Στο φύλλο αυτό:
1) Στη στήλη Α εισάγω τα δεδομένα από τα οποία αναζητά η πτυσσομένη λίστα της επικύρωσης. Ας πούμε ότι η λίστα μας περιλαμβάνει ονόματα κρατών στην Ελληνική Γλώσσα. Πριν το πρώτο όνομα και στις θέσεις Α1 έως Α24 γράφω όλα και τα 24 κεφαλαία γράμματα της Ελληνικής αλφαβήτου και από το κελί Α25 και κάτω ακολουθούν τα κράτη.
2) Θα ορίσω για τη λίστα αυτή της στήλης Α ένα όνομα. Και μάλιστα επειδή η λίστα πιθανά να ανανεώνετε και να συμπληρώνεται με νέα ονόματα, θα την ονομάσω stili όχι στατικά, αλλά δυναμικά. Εισάγω λοιπόν το global define name:  stili
=OFFSET(table!$A$1;0;0;COUNTA(table!$A:$A);1)
Αν, πχ,  έχω εισάγει 200 κράτη, η stili περιλαμβάνει αυτή τη στιγμή τα κελιά  από το Α1 έως και το Α224.  Αν προσθέσω και νέα κράτη η stili θα επεκταθεί αυτόματα μόνη της και θα συμπεριλάβει και τα νέα κελιά. Πρέπει όμως στη στήλη Α1:A224 να μην υπάρχουν κενά κελιά, διότι αν υπάρχουν, η  COUNTA θα δώσει λάθος πληροφορίες στην  OFFSET  για το πόσα κελιά να συμπεριλάβει.
3) Στο κελί  Β1 εισάγω τη συνάρτηση:
=INDIRECT("sheet!"&ADDRESS(CELL("ROW");1))
Η συνάρτηση αυτή ανιχνεύει ποιο πρώτο γράμμα μόλις έχει πληκτρολογήσει ο χρήστης που εισάγει στοιχειά στην περιοχή της επικύρωσης. Η περιοχή που έχει εισαχθεί η επικύρωση είναι η στήλη Α του φύλλου sheet. Ο χρήστης θέλει να εισάγει σε κάποιο κελί αυτής της στήλης  το κράτος: Αντίγκουα και Μπαρμπούντα. Από το πληκτρολόγιο εισάγει Α και πατά το κουμπί της πτυσσομένης λίστας, οπού και θα δει φιλτραρισμένη τη λίστα μόνο με τα ονόματα που αρχίζουν από Α, για να επιλέξει την Αντίγκουα και Μπαρμπούντα. Αυτό ακριβώς το Α επιστρέφει η  παραπάνω συνάρτηση το οποίο θα αποτελέσει το κριτήριο για το φιλτράρισμα.
(Θυμίζω ότι η CELL δίνει πληροφορίες για το ενεργό κελί.)
4) Στο κελί  Β2 εισάγω τη συνάρτηση:
=SUMPRODUCT(1*(UPPER(LOWER(LEFT(stili)))=$B$1))
Η συνάρτηση επιστρέφει το πλήθος των εγγραφών που υπάρχουν στη stili και των οποίων το όνομα αρχίζει από το γράμμα που ανιχνεύσαμε στο κελί Β1. (στο παράδειγμά μας το πλήθος των κρατών που αρχίζουν από Α)
5) Στο κελί  Β3 εισάγω τη συνάρτηση:
=SUMPRODUCT(1*(CODE(UPPER(LOWER(LEFT(stili))))=MODE(CODE(UPPER(LOWER(LEFT(stili;1)))))))
Η συνάρτηση αυτή δεν είναι απαραίτητη για την λειτουργία της επικύρωσης, επιστρέφει τον μεγαλύτερο από τους αριθμούς που είναι πιθανό να εμφανιστούν στο κελί Β2. Στο παράδειγμά μας αν τα περισσότερα κράτη αρχίζουν ας πούμε από M και τα κράτη αυτά είναι 30, η συνάρτηση θα επιστρέψει 30. Ο αριθμός αυτός δεν αλλάζει εκτός  αν προσθαφαιρέσω ή αλλάξω στοιχειά στην stili. Θα δούμε αμέσως παρακάτω που βοηθά ο αριθμός αυτός.
6) Στο κελί  C1  εισάγω τη συνάρτηση:
=IF(ROWS($A$1:A1)<=$B$2;ROWS($A$1:A1);"")
Αντιγράφω (σέρνω) τη συνάρτηση αυτή έως το κελί Α30 τουλάχιστον. (Να που βοηθά η προηγούμενη συνάρτηση του Β3, μου λέει πόσο κάτω πρέπει να αντιγράψω τη συνάρτηση). Μπορώ να αντιγράψω και όσο πιο κάτω  από το Α30 θέλω, τίποτε δεν βλάπτει, αλλά όχι λιγότερο από όσο μας λέει η Β3.
Η συνάρτηση αυτή δημιουργεί μία συνεχή αρίθμηση από το 1 έως τον αριθμό Β2.
7) Η αρίθμηση θα βοηθήσει στο φιλτράρισμα που θα κάνουμε αμέσως τώρα στη στήλη D όχι με το φίλτρο του excel, αλλά:
στο κελί  D1  εισάγω τη συνάρτηση:
=IF(ISNUMBER(C1);INDEX(stili;SMALL(IF(UPPER(LOWER(LEFT(stili;1)))=$B$1;ROW(stili);"");C1));"")
Η συνάρτηση αυτή είναι συνάρτηση-πίνακας και εισάγεται με Ctrl+Shift+Enter.
(Πρακτικά τη γράφω ή την  αντιγράφω στο κελί D1, πατάω F2  και κατόπιν Ctrl+Shift+Enter). Στη γραμμή τύπων βλέπω την συνάρτηση μέσα σε άγκιστρα {}
Αντιγράφω (σέρνω) τη συνάρτηση αυτή έως το κελί D30 τουλάχιστον, ακριβώς όσο κάτω αντέγραψα και της συνάρτηση του κελιού C1
8) Τέλος εισάγω σαν όνομα την λίστα της  επικύρωσης. Φυσικά είναι η περιοχή D1:D30 που περιέχει τα φιλτραρισμένα κράτη. Επειδή όμως έχει μεταβλητό μήκος θα την ορίσω δυναμικά. Εισάγω το global define name:  myValidation
 =INDIRECT("table!"&ADDRESS(1;4)&":"&ADDRESS(table!$B$2;4))
Τελειώσα με το φύλλο table.
Αν θέλω μπορώ να το ορίσω «κρυφό» ή «εντελώς κρυφό» και θα δουλεύει πολύ καλά.
Όπως είπα παραπάνω το φύλλο στο οποίο θα χρησιμοποιήσω την επικύρωση είναι το φύλλο sheet. Στην στήλη Α εισάγω την επικύρωση κατά τα γνωστά. ( Στην καρτέλα «Ρυθμίσεις» της επικύρωσης δεδομένων επιλέγω: Επιτρεπόμενη καταχώριση: Λίστα και Προέλευση: =myValidation.
Αν κατά την δημιουργία της επικύρωσης εμφανιστεί το μήνυμα: «Το αρχείο προέλευσης καταλήγει σε εσφαλμένο αποτέλεσμα αυτή τη στιγμή. Θέλετε να συνεχίσετε;» απαντώ ΝΑΙ)
ΣΥΜΠΛΗΡΩΜΑΤΙΚΑ
Οι συναρτήσεις είναι φτιαγμένες να δουλεύουν με Ελληνικό αλφάβητο. Θα δουλέψουν άριστα και με Αγγλικό (Λατινικό). Αν η stili περιλαμβάνει λέξεις αποκλειστικά με λατινικούς χαρακτήρες τότε στα κελιά Α1 έως Α26 της stili δεν θα εισαχθεί η ελληνική αλφαβήτα αλλά η Αγγλική (κεφαλαία). Για ονόματα και στα 2 αλφάβητα θα εισαχθούν στη stili και τα 50 κεφαλαία γράμματα των 2 αλφάβητων. Με αριθμούς μπορούν να δουλέψουν αν οι αριθμοί έχουν εισαχθεί σαν κείμενο. Για λέξεις που αρχίζουν από ειδικούς χαρακτήρες (Ελληνικούς πολυτονικούς, Γαλλικούς με τόνους, Ισπανικούς ή Σκανδιναβικούς με ειδικά σύμβολα πάνω τους κλπ), οι συναρτήσεις χρειάζονται προσαρμογή κατά περίπτωση.
Αν η stili έχει αποκλειστικά λέξεις της Αγγλικής στις παραπάνω συναρτήσεις μπορεί να απαλειφθεί η LOWER (προσοχή στις παρενθέσεις)
Συνοπτικά:
Φύλλο table κελί Β1 συνάρτηση:=INDIRECT("sheet!"&ADDRESS(CELL("ROW");1))
Φύλλο table κελί Β2 συνάρτηση: =SUMPRODUCT(1*(UPPER(LOWER(LEFT(stili)))=$B$1))
Φύλλο table κελί Β3 συνάρτηση: =SUMPRODUCT(1*(CODE(UPPER(LOWER(LEFT(stili))))=MODE(CODE(UPPER(LOWER(LEFT(stili;1)))))))
Φύλλο table κελιά C1:Ct συνάρτηση: =IF(ROWS($A$1:A1)<=$B$2;ROWS($A$1:A1);"")
Φύλλο table κελιά D1:Dt συνάρτηση: {=IF(ISNUMBER(C1);INDEX(stili;SMALL(IF(UPPER(LOWER(LEFT(stili;1)))=$B$1;ROW(stili);"");C1));"")}
ΟΝΟΜΑΤΑ (global define names):
MyValidation =INDIRECT("table!"&ADDRESS(1;4)&":"&ADDRESS(table!$B$2;4))
Stili =OFFSET(table!$A$1;0;0;COUNTA(table!$A:$A);1) 
ΕΠΙΚΥΡΩΣH:
Φύλλο sheet στήλη Α: Επιτρεπόμενη καταχώριση: Λίστα , Προέλευση: =myValidation
Κατεβάστε αν θέλετε ένα excel βιβλίο 
με το παραπάνω παράδειγμα: filtered_validation
This entry was posted in excel, Επικύρωση, Συναρτήσεις φύλλου and tagged . Bookmark the permalink.

3 Responses to Φιλτραρισμένη επικύρωση

  1. Ο/Η Θάνος Θρ. λέει:

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

  2. Ο/Η vioannis λέει:

    Σας ευχαριστώ για την επικοινωνία και για την σαφήνεια με την οποία διατυπώσατε την ερώτησή σας. Ναι, υπάρχει πρόταση, η οποία αξίζει νομίζω να γίνει ξεχωριστή ανάρτηση. Δώστε μου λίγο χρόνο να την επεξεργαστώ και ξαναεπισκεφτείτε το ιστολόγιο.

  3. Ο/Η Θάνος Θρ. λέει:

    Χίλια ευχαριστώ για τον κόπο σας! Το δεύτερο παράδειγμα group_filtered_validation_2 , ήταν ακριβώς αυτό που ζητούσα.

Τα σχόλια είναι απενεργοποιημένα.