Πίνακες ελέγχου και αναφορές του Excel: Παράδειγμα πλαισίου λίστας

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

Ευτυχώς, όλα αυτά γίνονται χωρίς κώδικα VBA, μόνο μια χούφτα τύπους και ένα πλαίσιο λίστας.

Για να δημιουργήσετε αυτό το παράδειγμα, ξεκινήστε με τρία ακατέργαστα σύνολα δεδομένων — όπως φαίνεται στο παρακάτω σχήμα — που περιέχουν τρεις κατηγορίες δεδομένων: Έσοδα, Καθαρό Εισόδημα % και Μικτό Περιθώριο. Κάθε σύνολο δεδομένων περιέχει μια ξεχωριστή γραμμή για κάθε περιοχή, συμπεριλαμβανομένης μιας για όλες τις περιοχές.

Στη συνέχεια, προσθέτετε ένα πλαίσιο λίστας που εξάγει τον αριθμό ευρετηρίου του επιλεγμένου στοιχείου στο κελί P2, όπως φαίνεται στην παρακάτω εικόνα.

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

Στο Excel, η συνάρτηση CHOOSE επιστρέφει μια τιμή από μια καθορισμένη λίστα τιμών με βάση έναν καθορισμένο αριθμό θέσης. Για παράδειγμα, ο τύπος CHOOSE(3,”Red”, “Yellow”, “Green”, “Blue”) επιστρέφει πράσινο επειδή το πράσινο είναι το τρίτο στοιχείο στη λίστα τιμών. Ο τύπος CHOOSE(1, "Red", "Yellow", "Green", "Blue") επιστρέφει κόκκινο.

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

Στο παράδειγμα που φαίνεται στο παραπάνω σχήμα, τα δεδομένα που θα επιστραφούν με αυτόν τον τύπο CHOOSE είναι 41767. Γιατί; Επειδή το κελί P2 περιέχει τον αριθμό 3 και η τρίτη αναφορά κελιού στον τύπο CHOOSE είναι το κελί B9.

Εισαγάγετε τον ίδιο τύπο τύπου CHOOSE στη στήλη Jan και, στη συνέχεια, τον αντιγράψετε κατά μήκος, όπως φαίνεται στο παρακάτω σχήμα.

Για να ελέγξετε ότι οι τύποι σας λειτουργούν, αλλάξτε την τιμή του κελιού P2 με μη αυτόματο τρόπο, πληκτρολογώντας 1, 2, 3, 4 ή 5. Όταν οι τύποι λειτουργούν, το μόνο που μένει να κάνετε είναι να δημιουργήσετε τα γραφήματα χρησιμοποιώντας τον πίνακα σταδιοποίησης.

Εάν οι λειτουργίες του Excel όπως το CHOOSE ή το INDEX είναι λίγο τρομακτικές για εσάς, μην ανησυχείτε. Υπάρχουν κυριολεκτικά εκατοντάδες τρόποι για να χρησιμοποιήσετε διάφορους συνδυασμούς στοιχείων ελέγχου Φόρμας και συναρτήσεων Excel για την επίτευξη διαδραστικών αναφορών. Δεν υπάρχουν καθορισμένοι κανόνες για τα στοιχεία ελέγχου φόρμας ή τις συναρτήσεις του Excel που πρέπει να χρησιμοποιήσετε στο μοντέλο σας.

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