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

Το σημείωμα του ιστολογίου “Φιλτραρισμένη επικύρωση” περιγράφει μία τεχνική , με την οποία, η επιλογή από ένα πτυσσόμενο πλαίσιο μίας επικύρωσης μπορεί να γίνει πιο φιλική. Αν το πτυσσόμενο πλαίσιο περιλαμβάνει πάρα πολλά ονόματα, πράγμα που το καθιστά δύσχρηστο, με την τεχνική αυτή, ο χρήστης μπορεί να περιορίσει κατά πολύ τις επιλογές του πλαισίου, αν προηγουμένως γράψει στο κελί το πρώτο γράμμα του ονόματος που αναζητά, και μετά ανοίξει το πλαίσιο της επικύρωσης. Στο παράδειγμα της πρώτης εικόνας, ο χρήστης θέλει να εισάγει στο κελί Α1 (που περιέχει την επικύρωση) ένα όνομα που αρχίζει από Δ. Αντί να ανοίξει το πλαίσιο και να ψάχνει μέσα σε όλο τον κατάλογο των, ενδεχομένως, χιλιάδων ονομάτων, πληκτρολογεί στο κελί το πρώτο γράμμα Δ και μετά ανοίγει το πλαίσιο της επικύρωσης, το οποίο θα εμφανίσει μόνο τα ονόματα που αρχίσουν από Δ, και θα κάνει πιο εύκολα και γρήγορα την επιλογή του.
new_filtered_validation_01Υπάρχουν περιπτώσεις όμως, όπου και μετά από αυτό το γενικό αλφαβητικό φιλτράρισμα, το πλαίσιο εξακολουθεί να περιέχει πολλές εγγραφές και να είναι δύσχρηστο. Αυτό το πρόβλημα έθεσε πριν λίγες ημέρες ένας φίλος του ιστολογίου, και έτσι ξαναδούλεψα και βελτίωσα την τεχνική που περιγράφω σε εκείνο το σημείωμα, ώστε ο χρήστης να μπορεί να πληκτρολογεί, αν θέλει, και περισσότερα από ένα αρχικά γράμματα του ονόματος που ψάχνει, πριν ανοίξει το πλαίσιο της επικύρωσης, και έτσι να περιορίζει ακόμα περισσότερο το πλήθος των εγγραφών στο πλαίσιο.
Στις επόμενες εικόνες ο χρήστης ψάχνει για ονόματα που αρχίζουν από ΔΙΟ , ΔΙΟΝ, ΧΡΥΣΑ αντίστοιχα, και ανάλογα έχουν περιοριστεί και οι εγγραφές στο πλαίσιο.
new_filtered_validation_02
new_filtered_validation_03
new_filtered_validation_04
Παρακολουθήστε παρακάτω,, αναλυτική περιγραφή όλων των βημάτων που πρέπει να κάνετε, αν θέλετε να έχετε μια τέτοια επικύρωση. Μάλιστα, όλη η εργασία γίνεται χωρίς μακροεντολές, μόνο με συναρτήσεις φύλλου συμβατές με όλες τις εκδόσεις excel. Και επειδή είμαστε “ένα ιστολόγιο για τον Έλληνα χρήστη” έχει προβλεφθεί να δουλεύει με πεζά ή κεφαλαία, τονισμένα ή άτονα γράμματα.
Α) Σε ένα βιβλίο ονομάστε ένα φύλλο Help και ένα φύλο sheet. Στο φύλλο Help θα γίνεται όλη η δουλειά παρασκηνίου και στο φύλλο sheet θα είναι η επικύρωση. Όταν τελειώσετε, το φύλλο Help που θα κάνει όλη τη “βρώμικη” δουλειά, μπορείτε να το κρύψετε “κάτω από το χαλί”. Αν θέλετε να μην είναι ορατό στον χρήστη, χαρακτηρίστε το κρυφό ή πολύ κρυφό.
Β) Στο φύλλο Help, κάντε τα παρακάτω:
Στη στήλη Α, ξεκινώντας από το κελί Α1, θα περάσετε τη μεγάλη λίστα με τις πολλές εγγραφές σας, από την οποία θα προκύπτουν οι φιλτραρισμένες επικυρώσεις. Στη λίστα-στήλη αυτή, δώστε ένα δυναμικό όνομα:

stili = OFFSET(Help!$A$1;0;0;COUNTA(Help!$A:$A);1)

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

=LEN(INDIRECT("sheet!"&ADDRESS(CELL("ROW");CELL("col"))))

Ο τύπος αυτός παρακολουθεί τα κελιά στα οποία θα εισάγετε αργότερα την επικύρωση στο φύλλο sheet, και επιστρέφει το πλήθος των γραμμάτων που πληκτρολογούνται πριν ανοίξει το πλαίσιο της επικύρωσης. Στο παράδειγμα της πιο πάνω εικόνας, όπου ο χρήστης πληκτρολόγησε ΧΡΥΣΑ, ο τύπος επιστρέφει 5.
Β2 ) Στο κελί Β2 γράψτε τον τύπο:

=SUMPRODUCT(1*(UPPER(LOWER(LEFT(stili;$B$1)))=UPPER(LOWER($D$1))))

Ο τύπος αυτός διατρέχει όλη τη στήλη Α και μετρά το πλήθος των εγγραφών που αρχίζουν από τα γράμματα που πληκτρολογήθηκαν στο κελί της επικύρωσης. Αν ο χρήστης έχει πληκτρολογήσει ΔΙΟ, η συνάρτηση θα μετρήσει και θα επιστρέφει το πλήθος όλων των εγγραφών της στήλης Α που αρχίζουν από ΔΙΟ, διο, Διο, Δίο, Διό και όλους τους άλλους συνδυασμούς πεζών, κεφαλαίων, τονισμένων και άτονων γραμμάτων του μονοτονικού.
C1) Στο κελί C1 γράψτε 0 (μηδέν) και στο κελί C2 γράψτε τον τύπο:

=IF(C1<$B$2;C1+1;"")

και αντιγράψτε τον αρκετά κάτω στη στήλη C. Η συνάρτηση αυτή δημιουργεί μία συνεχή αρίθμηση από το 1 έως τον αριθμό που επιστρέφει ο τύπος του κελιού Β2
D1) Στο κελί D1 γράψτε τον τύπο:

=INDIRECT("sheet!"&ADDRESS(CELL("ROW");CELL("col")))

Ο τύπος αυτός (όπως και ο τύπος Β1) παρακολουθεί τα κελιά στα οποία θα εισάγετε αργότερα την επικύρωση και επιστρέφει ότι ακριβώς πληκτρολόγησε ο χρήστης στο κελί της επικύρωσης
D2) Στο κελί D2 γράψτε τον τύπο:

=IF(ISNUMBER(C2);INDEX(stili;SMALL(IF(UPPER(LOWER(LEFT(stili;$B$1)))=UPPER(LOWER($D$1));ROW(stili);"");C2));"")

Μόλις τον γράψετε, επιλέξτε το κελί D2, πατήστε στο πληκτρολόγιο F2 και αμέσως μετά πατήστε μαζί τα πλήκτρα Ctrl, Shift και Enter. Θα δείτε τον τύπο να περικλείεται μέσα σα αγκύλες :

{=IF(ISNUMBER(C2);INDEX(stili;SMALL(IF(UPPER(LOWER(LEFT(stili;$B$1)))=UPPER(LOWER($D$1));ROW(stili);"");C2));"")}

Είναι μία συνάρτηση-πίνακας και πρέπει να εισαχθεί με Ctrl+ Shift+ Enter για να δουλέψει σωστά. Για τις συναρτήσεις-πίνακες και πως εισάγονται διαβάστε πιο αναλυτικά εδώ . Την συνάρτηση αυτή αντιγράψτε αρκετά κάτω στη στήλη D. Στη στήλη αυτή επιστρέφουν φιλτραρισμένα όλα τα ονόματα που αρχίζουν από τα γράμματα που πληκτρολόγησε ο χρήστης στο κελί της επικύρωσης και αυτά τα ονόματα θα εμφανιστούν στο πλαίσιο της επικύρωσης.
Τώρα θα εισάγετε ένα δεύτερο δυναμικό όνομα το οποίο θα χρησιμοποιήσετε στην επικύρωση:

myValidation =OFFSET(Help!$D$1;0;0;1+Help!$B$2;1)

Η λίστα (πίνακας) myValidation επιστρέφει τα περιεχόμενο όλων των εγγραφών που θα βρει στη στήλη D από το κελί D1 και κάτω.
Μένει τώρα να ορίσετε την επικύρωση στο φύλλο sheet. Επιλέξτε όσα και όποια κελιά θέλετε στο φύλλο αυτό και ορίστε επικύρωση με Επιτρεπόμενη καταχώρηση: Λίστα και Προέλευση: =myValidation
new_filtered_validation_05
Κατεβάστε το excel βιβλίο: new_filtered_validation για να δείτε όλα τα παραπάνω στην πράξη. Στο βιβλίο θα διαβάσετε μερικές ακόμα λεπτομέρειες που θα σας βοηθήσουν να στήσετε τη δικιά σας φιλτραρισμένη επικύρωση.

This entry was posted in excel, Επικύρωση, Συναρτήσεις φύλλου, Φίλτρα and tagged , , , . Bookmark the permalink.

14 Responses to Φιλτραρισμένη επικύρωση, ξανά!

  1. Ο/Η KONSTADINOS.V λέει:

    Είσαι άπαιχτος. Δουλεύει τέλεια και μάλιστα όχι μόνο για 3 ή 4 γράμματα που είχα πει, αλλα όλα όσα βάζεις…
    Πιστεύω σε μια βάση 10.000 εγγραφών είναι σωτήριο κάτι τέτοιο.
    Μπράβο και χίλια ευχαριστώ..

  2. Ο/Η Γιώργος λέει:

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

  3. Ο/Η vioannis λέει:

    Γιώργο, με έχει ήδη απασχολήσει το θέμα και έχω αφιερώσει χρόνο για αυτό. Δεν έχω να προτείνω λύση, ούτε με vba. Πρόσεξε, δεν λέω ότι δεν υπάρχει λύση, λέω ότι εγώ δεν μπορώ να βρω λύση. Άλλωστε το ίδιο το excel στη λειτουργία συμπλήρωσης μια στήλης με αλφαριθμητικά (ονόματα) ελέγχει και προτείνει κατά το χρόνο της πληκτρολόγησης.

  4. Ο/Η Aris λέει:

    Καταπληκτική δουλειά! Έψαχνα καιρό στο internet για κάτι παρόμοιο, αλλά το έψαχνα σε ξένα site. Δεν φανταζόμουν ότι η λύση είναι εδώ δίπλα μου και δωρεάν!. Σας ευχαριστώ πολύ!
    Να πω στον Γιώργο που διαμαρτύρεται ότι είναι σπαστικό να αφήνει το πληκτρολόγιο για να πιάσει το ποντίκι, ότι δεν χρειάζεται καθόλου το ποντίκι. Μόλις ολοκληρώσει την πληκτρολόγηση των γραμμάτων στο κελί, πατάει Alt και πάνω ή κάτω βέλος και το πλαίσιο θα ανοίξει και κάνει μετά επιλογή με τα βέλη και Enter. Επίσης με Alt και πάνω ή κάτω βέλος κλείνει το πλαίσιο.
    Επίσης βρήκα πολύ πρωτότυπο και χρήσιμο το άρθρο σας με τις συναρτήσεις που ψάχνουν για ελληνικούς και αγγλικούς χαρακτήρες! Και ακόμα δεν έχω δει όλα τα θέματα.

  5. Ο/Η vioannis λέει:

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

  6. Ο/Η ΓΕΩΡΓΙΟΣ ΑΝΑΣΤΑΣΟΠΟΥΛΟΣ λέει:

    ΕΑΝ ΚΛΕΙΔΩΣΟΥΜΕ ΤΟ ΦΥΛΛΟ «sheet» ΣΤΟ ΜΕΝΟΥ «ΠΡΟΣΤΑΣΙΑ ΦΥΛΛΟΥ» ΜΕ ΚΩΔΙΚΟ ΠΡΟΣΒΑΣΗΣ, ΓΙΑ ΝΑ ΠΡΟΣΤΑΤΕΥΨΟΥΜΕ ΑΛΛΑΓΕΣ ΣΕ ΑΛΛΑ ΚΕΛΙΑ ΣΤΟ ΙΔΙΟ ΦΥΛΛΟ, ,
    Η ΕΠΙΚΥΡΩΣΗ ΔΥΣΤΥΧΩΣ ,ΔΕΝ ΛΕΙΤΟΥΡΓΕΙ ΣΤΟ EXCEL 2007.
    YΠΑΡΧΕΙ ΚΑΜΙΑ ΛΥΣΗ ΣΕ ΑΥΤΟ ???

  7. Ο/Η vioannis λέει:

    Γιώργο, είσαι σίγουρος ότι πριν εισάγεις προστασία στο φύλλο, έχεις ορίσει τα κελιά με επικύρωση «όχι κλειδωμένα»; Σε 2010 που δουλεύω αυτή τη στιγμή δεν έχω πρόβλημα. Αύριο θα το δω και σε 2007.

  8. Ο/Η ΓΕΩΡΓΙΟΣ ΑΝΑΣΤΑΣΟΠΟΥΛΟΣ λέει:

    AKOMH KAI ΣΤΟ ΑΡΧΕΙΟ ΠΟΥ ΚΑΤΕΒΑΣΑ ΑΠΟ ΤΟ SITE ΣΟΥ, ΤΟ ΔΟΚΙΜΑΣΑ ΕΠΕΞΕΡΓΑΣΜΕΝΟ ΣΕ ΛΕΙΤΟΥΡΓΕΙΑ ΣΥΜΒΑΤΟΤΗΤΑΣ *.XLS, ΠΑΛΙ ΔΕΝ ΛΕΙΤΟΥΡΓΕΙ. ΔΟΥΛΕΥΩ ΤΑ OFFICE 2007 ΗΟΜΕ EDITION X32.

  9. Ο/Η apostolos orfanidis λέει:

    κυριε Γιαννη καλησπερα σας , καλο φθινοπωρο . σχετικα με την ερωτηση του γ.αναστασοπουλου κοιταξα με το excel 2007 και δεν ειδα καποιο προβλημα με το κλειδωμενο φυλλο προστασιας . Αυτο που εκανα ομως και ισως τον βοηθησει ειναι να κανει πριν προστατεψει το φυλλο του ,σε καθε κελι που εχει δημιουργησει επικυρωση τα εξης : δεξι κλικ πανω στο κελι που εχει δημιουργησει επικυρωση – FORMAT CELLS – και στο PROTECTION ( ΠΡΟΣΤΑΣΙΑ ) να μην ειναι τσεκαρισμενο τιποτα απο τις 2 επιλογες που εχει ( locked-hidden). ετσι οταν θα κλειδωσει το φυλλο του excel τα παραπανω κελια θα ειναι ενεργα . σε ευχαριστω πολυ για ολη την βοηθεια που μας παρεχεις .

  10. Ο/Η vioannis λέει:

    Γιώργο, το δοκίμασα και εγώ σε διάφορες εκδόσεις και δουλεύει. Δες αυτά που γράφει παραπάνω ο Απόστολος και επιπλέον: στην καρτέλα με την Προστασία του φύλλου φρόντισε να είναι τσεκαρισμένα (δηλαδή οι χρήστες να έχουν δικαίωμα να κάνουν)τα:
    – Επιλογή μη κλειδωμένων κελιών
    – Επεξεργασία αντικειμένων
    Δοκίμασέ το έτσι, και πες μας.

  11. Ο/Η ΓΕΩΡΓΙΟΣ ΑΝΑΣΤΑΣΟΠΟΥΛΟΣ λέει:

    Γιάννη σε ευχαριστώ πολύ για την άμεση απάντηση σου, καθώς και το φίλο μας Απόστολο για την προσπάθεια και τις συμβουλές του. Η λύση δόθηκε με την πρόσθετη ενεργοποίηση της επιλογής «Επεξεργασία αντικειμένων» που ήταν ίσως η μόνη που δεν δοκίμασα, πρίν το κλείδωμα του φύλλου.
    Σας ευχαριστώ πολύ.

  12. Ο/Η Diomidis λέει:

    Προφανώς το θέμα και η δουλειά σου αγαπητέ Γιάννη είναι εξαιρετικά χρίσημη και ωραία αλλά για μας που δεν έχουμε τις ιδιαίτερες και τις καλύτερες γνώσεις στο πρόγραμμα είναι λίγο δύσκολο να καταλάβουμε το τρόπο που θα γίνει όλο αυτό (προσπαθώ ξαναπροσπαθώ αλλά τίποτα) π.χ. στην αρχή λες : «Στη λίστα-στήλη αυτή, δώστε ένα δυναμικό όνομα: stili = OFFSET(Help!$A$1;0;0;COUNTA(Help!$A:$A);1) » πως, που θα μπει ο παραπάνω τύπος για να ονομάσουμε την στήλη; θα βάλω όλα μου τα στοιχεία-ονόματα Α1 όσπου πάει και μετά θα βάλω τον πρώτο αυτό τύπο και σε ποιο κελί; αν θες και γίνεται πες μας σε παρακαλώ κάποιες λεπτομέριες παραπάνω ως προς εγκατάσταση των τύπων και το «στήσιμο» της φόρμας!
    Υ.Γ

  13. Ο/Η vioannis λέει:

    Αγαπητέ Διομήδη, προσπαθώ να απαντώ σε όσες περισσότερες περιπτώσεις μπορώ, αλλά δεν θέλω (και δεν μπορώ) με το ιστολόγιο να υποκαταστήσω ένα καλό βιβλίο για excel ή το σύστημα υποστήριξης της Microsoft. Απλά, δίνω στους επισκέπτες και φίλους του ιστολογίου, από την μικρή εμπειρία που έχω, ιδέες για ένα βήμα παραπέρα. Τώρα, για το θέμα των καθορισμένων ονομάτων (περιοχών και συναρτήσεων) θα βρεις όλες τις λεπτομέρειες στην υποστήριξη της Microsoft εδώ

  14. Ο/Η Diomidis λέει:

    Έχεις δίκιο Γιάννη (…….) σε ευχαριστώ για την υπόδειξη, μέσα από την Microsoft θα βρω την λύση.

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