Φιλτραρισμένη επικύρωση (δεδομένα σε ομάδες)

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

Οι τιμές που θα εμφανίζονται στο πτυσσόμενο πλαίσιο της επικύρωσης έχουν χωριστεί σε 4 ομάδες στο φύλλο “βοηθητικό”.
Στο κελί Α1 ενός άλλου φύλλου (φύλλο1) ο χρήστης επιλέγει την ομάδα και το πτυσσόμενο πλαίσιο της επικύρωσης (στήλη C ), εμφανίζει μόνο τις τιμές της ομάδας. Δέστε τις δύο επόμενες εικόνες:

Στην πράξη:
Στο βοηθητικό φύλλο χωρίζουμε τα δεδομένα σε ομάδες (στήλες), κάθε ομάδα έχει ένα τίτλο (1η γραμμή) και έναν αύξοντα αριθμό (2η γραμμή).
Στη συνέχεια για τα δεδομένα κάθε ομάδας δίνουμε ένα όνομα.
Πχ για την Α στήλη: Αstili =βοηθητικό!$A$3:$A$13
Επειδή τα δεδομένα μπορεί να μεταβάλλονται καλύτερα να ορίσετε δυναμικά ονόματα :
Astili =OFFSET(βοηθητικό!$A$3;0;0;COUNTA(βοηθητικό!$A:$A)-2;1)
Bstili =OFFSET(βοηθητικό!$B$3;0;0;COUNTA(βοηθητικό!$B:$B)-2;1)
Cstili =OFFSET(βοηθητικό!$C$3;0;0;COUNTA(βοηθητικό!$C:$C)-2;1)
Dstili =OFFSET(βοηθητικό!$D$3;0;0;COUNTA(βοηθητικό!$D:$D)-2;1)
Δίνουμε επίσης ένα όνομα για τους τίτλους των ομάδων:
OmadesTitloi =βοηθητικό!$A$1:$D$1
Στη συνέχεια εισάγουμε ένα ακόμα όνομα :
OmadesLookup=HLOOKUP(Φύλλο1!$A$1;βοηθητικό!$A$1:$D$2;2;FALSE)
Είναι μία συνάρτηση που με τη βοήθεια της οριζόντιας LOOKUP επιστρέφει τον αύξοντα αριθμό της ομάδας που έχει επιλεγεί από το χρήστη στο κελί Α1.
Τον αριθμό αυτό χρησιμοποιεί στο πρώτο όρισμά της η πιο κάτω συνάρτηση CHOOSE η οποία εισάγεται και αυτή σαν όνομα:
epikirosi=CHOOSE(OmadesLookup;Astili;Bstili;Cstili;Dstili)
Αφού έχουμε εισάγει τα 7 αυτά ονόματα, ορίζομε τις παρακάτω επικυρώσεις:
Στο κελί Α1 στο φύλλο1:
Επιτρεπόμενη καταχώριση: Λίστα,
Προέλευση: =OmadesTitloι
Στη στήλη C στο φύλλο1:
Επιτρεπόμενη καταχώριση: Λίστα,
Προέλευση: =epikirosi
Όταν ο χρήστης επιλέγει μία ομάδα στο κελί Α1, ας πούμε «Νησιά», η συνάρτηση HLOOKUP επιστρέφει 3 και αποδίδει την τιμή αυτή στο όνομα OmadesLookup.  Στη συνέχεια η συνάρτηση CHOOSE αφού το πρώτο όρισμά της είναι 3, επιστρέφει την τρίτη τιμή Cstili και αποδίδει αυτή την τιμή στο όνομα epikirisi.  Εμείς έχουμε χρησιμοποιήσει αυτό το όνομα σαν λίστα της επικύρωσης μας και έτσι όταν ανοίξουμε το πτυσσόμενο πλαίσιο βλέπουμε την Cstili, δηλαδή τα νησιά.

Δέστε το παραπάνω παράδειγμα στο excel βιβλίο: group_filtered_validation

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

βλέπετε μια διαφορετική δόμηση του φύλλου1. Εδώ επιλέγουμε στην στήλη Α την ομάδα και τότε στην στήλη Β, το πλαίσιο της επικύρωσης εμφανίζει σε κάθε κελί την ομάδα που περιγράφει το κελί αριστερά του.

Δέστε το παράδειγμα αυτό στο excel βιβλίο: group_filtered_validation_2

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

18 Responses to Φιλτραρισμένη επικύρωση (δεδομένα σε ομάδες)

  1. Ο/Η apapa0000 λέει:

    Πολύ ωραία προσπάθεια η προτασή σας μού έλυσε ένα θέμα που έχω από καιρό. Ευχαριστώ

    apapa0000

  2. Ο/Η george λέει:

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

  3. Ο/Η george λέει:

    ΑΚΥΡΟ ΦΙΛΕ ΜΟΥ ΞΑΝΑΔΙΑΒΑΣΑ ΠΡΟΣΕΚΤΙΚΑ ΤΟ ΠΑΡΑΔΕΙΓΜΑ ΣΟΥ ΚΑΙ ΤΟ ΒΡΗΚΑ!.ΘΑ ΜΠΟΡΟΥΣΕ ΝΑ ΜΑΣ ΔΩΣΕΙΣ ΚΑΝΕΝΑ ΠΑΡΑΔΕΙΓΜΑ ΜΕ ΦΟΡΜΕΣ ΕXCEL KAI VBA ΚΩΔΙΚΑ!
    ΚΑΙ ΚΑΤΙ ΤΕΛΕΥΤΑΙΟ ΥΠΑΡΧΕΙ ΚΑΝΕΝΑ ΒΙΒΛΙΟ ΠΟΥ ΠΡΟΤΕΙΝΕΙΣ ΓΙΑ ΝΑ ΔΙΑΒΑΣΩ ΣΧΕΤΙΚΑ ΜΕ EXCEL σε προχωρημένο στάδιο.Σε ευχαριστώ εκ των προτέρων

  4. Ο/Η vioannis λέει:

    Φίλε george, Ευχαριστώ για τα θετικά σχόλια σου.
    Ένα βιβλίο που συνιστώ ανεπιφύλακτα για ένα μέσο και προχωρημένο χρήστη του excel είναι : «Τύποι και συναρτήσεις του Ελληνικού Excel 2010» του John Walkenbach, εκδόσεις Μ.Γκιούρδας. Στις περίπου 800 σελίδες του διδάσκει μεθοδικά πολλά ειδικά θέματα και κυρίως προχωρημένες τεχνικές με τύπους. Και ένα μέρος να διαβάσεις του βιβλίου αυτού, μετά θα βλέπεις το excel με άλλα μάτια. Η έκδοση 2010 καλύπτει απόλυτα και την έκδοση 2007. Το βιβλίο υπάρχει και για την έκδοση 2003 με τον ίδιο τίτλο και από τον ίδιο εκδότη.

    Για VBA:
    Για αυτούς που αρχίζουν τώρα, για τις εκδόσεις 2000, 2002, 2003 προτείνω:
    «EXCEL 2002 VISUAL BASIC FOR APPLICATION ΒΗΜΑ ΒΗΜΑ» του Reed Jacobson, εκδόσεις ΚΛΕΙΔΑΡΙΘΜΟΣ
    Και για τις εκδόσεις 2007, 2010 «VBA και Μακροεντολές για το Excel 2007» των Bill Jelen και Tracy Surstad, εκδόσεις Μ.Γκιούρδας.
    Και τα δύο σε ξεκινούν από το μηδέν και διδάσκουν συστηματικά.
    Για πιο προχωρημένη μελέτη το βιβλίο του John Walkenbach, «Εγχειρίδιο Προγραμματισμού Excel 2010 με VBA» εκδόσεις Μ.Γκιούρδας. Το βιβλίο υπάρχει και για προηγούμενες εκδόσεις του Excel 2002, 2003. Εγχειρίδιο γραμμένο από ένα κορυφαίο συγγραφέα, με περισσότερες από 1000 σελίδες και 100αδες παραδείγματα.

    Για το παράδειγμα με φόρμα πρέπει να γίνεις πιο συγκεκριμένος, (αλλά θα σου απαντήσω μετά την Τρίτη)

  5. Ο/Η ΠΑΝΑΓΙΩΤΗΣ λέει:

    K. ΓΙΑΝΝΗ ΚΑΛΗΜΕΡΑ, ΘΑ ΗΘΕΛΑ ΤΗΝ ΒΟΗΘΕΙΑ ΣΑΣ, ΠΡΟΣΠΑΘΩ ΜΕ ΤΟ ΦΥΛΛΟ »GROUP FILTERED» ΝΑ ΤΟ ΠΡΟΣΑΜΩΣΩ ΣΕ ΈΝΑ ΔΙΚΟ ΜΟΥ ΦΥΛΛΟ ΑΛΛΑ ΔΕΝ ΜΟΥ ΒΓΑΙΝΕΙ. ΘΕΛΩ ΌΤΑΝ ΕΠΙΛΕΓΩ ΝΗΣΙ ΑΠΌ ΤΗΝ Α’ ΛΙΣΤΑ(32 ΝΗΣΙΑ ) ΝΑ ΜΟΥ ΕΝΦΑΝΙΖΕΙ ΣΤΗΝ Β’ ΛΙΣΤΑ (50 ΠΕΛΑΤΕΣ ΣΥΝΟΛΟ ΑΝΑ ΝΗΣΙ) ΜΟΝΟ ΤΟΥΣ ΠΕΛΑΤΕΣ ΠΟΥ ΕΧΩ ΣΤΟ ΣΥΓΚΕΚΡΙΜΕΝΟ ΝΗΣΙ. ΣΑΣ ΕΥΧΑΡΙΣΤΩ ΠΟΛΥ ΕΚ ΤΩΝ ΠΡΟΤΕΡΩΝ

  6. Ο/Η vioannis λέει:

    Παναγιώτη,
    Για να φτιάξουμε τις επικυρώσεις, χρησιμοποιούμε τη συνάρτηση φύλλου CHOOSE στην όποια το πρώτο όρισμα (index_num) είναι ένας αριθμός από το 1 έως το 29 (για τις εκδόσεις του excel έως 2003) ή ένας αριθμός από το 1 έως 254 (για τις εκδόσεις του excel 2007 και πάνω). Αν, λοιπόν, δουλεύεις σε excel έως 2003, οι ομάδες που μπορείς να έχεις είναι έως 29 και όχι 32. Αν δουλεύεις σε excel 2007 και πάνω, τότε κάτι άλλο δεν πάει καλά και θα χρειαστούν περισσότερες λεπτομέρειες.

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

    καλησπερα κυριε γιαννη να ειστε παντα καλά . Μια απλή ερώτηση υπάρχει η δυνατότητα 3 πλης επικύρωσης ? στο παραδειγμα που εχετε κανει εαν καποιος επιλεξει νησια εμφανιζεται δωδεκανησα .θα μπορουσε μετα να υπαρχει επικυρωση για ροδο. τηλο κοκ που περιεχονται στο συμπλεγμα των δωδεκανησιων ?

    ευχαριστω πολυ.αποστολος ..

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

    κυριε γιαννη καλη εβδομαδα . νομιζω οτι εφτιαξα την 3 πλη επικυρωση να σας την στειλω να μου πειτε την γνωμη σας και σε ποιο email ?
    ευχαριστω για ολα

  9. Ο/Η vioannis λέει:

    Απόστολε, όπως είδες και μόνος σου, φυσικά και γίνεται επικύρωση σε τρία και σε τέσσερα επίπεδα, με την ίδια λογική και τεχνική που γίνεται στα δύο επίπεδα. Εν το μεταξύ είχα ετοιμάσει ένα παράδειγμα. Αν θες να το δεις κατέβασε το βιβλίο TreeD_group_filtered_validation για να δεις τις τεχνικές λεπτομέρειες. Είναι το τελευταίο πιο πάνω παράδειγμα (group_filtered_validation_2) όπου πρόσθεσα για μερικούς νομούς, ένα ακόμα επίπεδο. Επίσης με χαρά θα ανεβάσω και το δικό σου παράδειγμα, το περιμένω, σου έστειλα ένα email.

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

    Καλήμερα.
    Περιττό να πω πόσο με έχει βοηθήσει το ιστολόγιό σου, με τις συμβουλές που δίνεις.
    Θα ήθελα και γω να ζητήσω κάτι να μου πεις κατά πόσο είναι εφικτό.
    Λοιπον, εχω μία μεγάλη λίστα με π.χ. ΟΝΟΜΑΤΕΠΩΝΥΜΑ σε ένα φύλλο εργασίας. και θέλω σε ένα άλλο φύλλο να φτιάξω τον κώδικα που θα κάνει τέτοιο φιλτράρισμα ΚΑΤΑ ΤΗ ΣΤΙΓΜΗ της πληκτρολόγησης ώστε μόλις γράφω στο κελι πχ ΠΑΠΑΔΟΠ.. να μου εμφανίζει από κάτω τα επώνυμα που ξεκινούν από ΠΑΠΑΔΟΠ. …

    Οπως κατάλαβες δεν θέλω να τελειώνω την εισαγωγή του ΕΠΩΝΥΜΟΥ και να πατάω ΕΝΤΕR (που μπορεί να γίνει με μια Vlookup), αλλά «Live» κατά την πληκτρολόγηση να μου περιορίζει τις επιλογές μου….

    Ευχαριστώ εκ των προτέρων….

  11. Ο/Η vioannis λέει:

    Κωνσταντίνε, το θέμα έχει απασχολήσει στο παρελθόν το ιστολόγιο, και έχει προτείνει μια μερική λύση σε αυτό που ζητάς, περιορίζοντας όμως το φιλτράρισμα της λίστας μόνο κατά το αρχικό γράμμα. Και μάλιστα η δουλειά γίνεται χωρίς μακροεντολές, μόνο με συναρτήσεις φύλλου. Δες το θέμα του ιστολογίου: “Φιλτραρισμένη επικύρωση”. Ίσως σε καλύπτει.

  12. Ο/Η vioannis λέει:

    Αν δεν σε καλύπτει, πες μου, ίσως μπορεί να βελτιωθεί με περισσότερα, από το αρχικό, γράμματα.

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

    Σε ευχαριστώ πάρα πολύ. Το μελέτησα και είδα πως γίνεται. Πραγματικά με βοηθάει. Αλλά μιας και αναφέρεις ότι υπάρχει βελτίωση πέρα από το αρχικό γράμμα, θα ήθελα να το δω, αν δε σε κουράζει. Ο λόγος που θα ήθελα να το βελτιώσουμε όσο γίνεται πέρα από το αρχικό γράμμα, είναι ότι η λίστα μου μπορεί να περιέχει έως και 10.000 ονόματα … οπότε το αρχικό γράμμα μόνο, δεν φτάνει, για να βρεις το όνομα που ψάχνεις… ίσως χρειαστεί να πάμε τουλάχιστον στο 3ο ή και στο 4ο…
    Δεν ξέρω βέβαια αν είναι εφικτό να διαχειριστεί το excel, τόσο μεγάλη πληροφορία με Επικύρωση…
    Όπως και να έχει χίλια ευχαριστώ.

  14. Ο/Η vioannis λέει:

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

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

    Καλησπέρα κύριε Γιαννη . σας ευχαριστώ για την απάντηση στην 3 πλη επικύρωση που σας είχα ζητήσει . ετοιμάζω ένα ωραίο αρχείο με 3 επικύρωση για να σας το στείλω.
    Είδα και την καινούργια θεματολογία και παρατήρησα το εξής : εαν γράψω Αποστ ( τα 5 πρώτα γράμματα του ονόματος μου ) υπάρχει αποδοχή εάν πατήσω enter . είναι σωστό αυτό ? υπάρχει τρόπος να μην επιτρέπεται ?

  16. Ο/Η vioannis λέει:

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

  17. Ο/Η Αλέξανδρος λέει:

    κ. Γιάννη καλημέρα σας,
    Αφού ευχαριστήσω για μια ακόμα φορά για τις δεκάδες λύσεις που έχω βρει με τη βοήθειά σας, θέλω να ρωτήσσω κάτι:
    Έχω φτιάξει ένα αρκετά μεγάλο φύλλο με δενδρική δομή επικύρωσης δεδομένων (20 αρχικά επίπεδα με 5 υποεπίπεδα το κάθε ένα και άλλα 5 υπό-υποεπίπεδα για κάθε υποεπίπεδο. Επειδή είναι αρκετά χρονοβόρος ο ορισμός ονομάτων, υπάρχει κάποια μέθοδος για μαζική εισαγωγή;
    Ακόμα, υπάρχει τρόπος να προσθέτουμε ή να αφαιρούμε δυναμικά στήλες με το δεύτερο και το τρίτο επίπεδο;
    Τέλος, υπάρχει στην επικύρωση δεδομένων η δυνατότητα ορισμού μήκους κειμένου που θα δεχθεί το κελί. Γίνεται να υπάρχει συνδυασμός του τύπου της επικύρωσης (λίστα και μήκος κειμένου) ώστε να περιορίζεται το κείμενο σε ν χαρακτήρες;
    Ευχαριστώ εκ των προτέρων

  18. Ο/Η vioannis λέει:

    Αλέξανδρε:
    Α) Επειδή είναι αρκετά χρονοβόρος ο ορισμός ονομάτων, υπάρχει κάποια μέθοδος για μαζική εισαγωγή;
    Στο μενού Τύποι, Καθορισμένα Ονόματα, υπάρχει η Δημιουργία από επιλογή. Με αυτή, πολλές στήλες μαζί μπορούν να γίνουν ονόματα με όνομα την πρώτη γραμμή, η γραμμές με όνομα το πρώτο αριστερό κελί κλπ. Δοκίμασέ το , αρκετές φορές διευκολύνει, αλλά για ονόματα περιοχών, όχι για ονόματα που ορίζονται με συναρτήσεις (offset, index, choose κλπ)
    Β) Υπάρχει τρόπος να προσθέτουμε ή να αφαιρούμε δυναμικά στήλες με το δεύτερο και το τρίτο επίπεδο;
    Γενικά όχι, ίσως σε κάποια ειδικά προβλήματα να μπορεί να γραφτεί μακροεντολή που να το κάνει , ίσως.
    Γ) Όπως είναι γνωστό, υπάρχει στην επικύρωση δεδομένων η δυνατότητα ορισμού μήκους κειμένου που θα δεχθεί το κελί. Γίνεται να υπάρχει συνδυασμός του τύπου της επικύρωσης (λίστα και μήκος κειμένου) ώστε να περιορίζεται το κείμενο σε ν χαρακτήρες;
    Αυτό είναι πολύ ωραίο και πολύ ενδιαφέρον ερώτημα. Και μεγάλη πρόκληση να λυθεί αυτό χωρίς vba. Σίγουρα κάθε μέθοδος από τις 8 που θα εισαχτεί από την καρτέλα της επικύρωσης, αποκλείει την προηγούμενη. Αλλά ίσως να υπάρχει ο μαγικός τύπος για την περίπτωση της ‘προσαρμογής’ που να το κάνει!
    Μέχρι να βρεθεί αυτή λύση, αν υπάρχει, μπορείς να εισάγεις την επικύρωση σου και να εισάγεις στη λειτουργική μονάδα του αντίστοιχου φύλλου (και όχι σε module) μια μακροεντολή τύπου Worksheet_Change που να παρακολουθεί τα κελιά που περιέχουν την επικύρωση και να προσαρμόζει το μήκος των τιμών που εισάγονται. Η παρακάτω μακροεντολή θεωρεί ότι η επικύρωση είναι στα κελιά Α1:Α20 και κάθε φορά που εισάγεται μία νέα τιμή στα κελιά αυτά, επεμβαίνει και κρατά μόνο τους 4 πρώτους χαρακτήρες. Ίσως σε εξυπηρετήσει.

    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim kelia As Range
    Dim keli As Range
    Dim mytext As Variant
    Set kelia = Range("a1:a20")
    Set keli = Intersect(Target, kelia)
    If keli Is Nothing Then Exit Sub
    If Not keli Is Nothing And keli.Count = 1 Then
    mytext = keli.Value
    Application.EnableEvents = False
    keli = VBA.Left(mytext, 4)
    Application.EnableEvents = True
    End If
    End Sub
    

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