Ο αριθμός των διαθέσιμων στοιχείων για φιλτράρισμα είναι περιορισμένος. Το Excel δεν μπορεί να φιλτράρει στήλες στις οποίες ο αριθμός των στοιχείων υπερβαίνει τα 999 (όχι ο αριθμός των γραμμών).
Για φιλτράρισμα όταν υπάρχουν περισσότερα από 999 στοιχεία, χρησιμοποιήστε προηγμένο φίλτρο.
Για να δημιουργήσουμε ένα προηγμένο φίλτρο, θα χρησιμοποιήσουμε τις λειτουργίες "OFFSET" και "COUNTA" στο Microsoft Excel.
COUNTA: Επιστρέφει τον αριθμό του αριθμού των κελιών που περιέχουν τιμές.
Σύνταξη της λειτουργίας «COUNTA»: = COUNTA (τιμή1, τιμή2, τιμή3….)
Παράδειγμα: Στο εύρος A1: A5, τα κελιά A2, A3 και A5 περιέχουν τις τιμές και τα κελιά A1 και A4 είναι κενά. Επιλέξτε το κελί A6 και γράψτε τον τύπο-
= COUNTA (A1: A5) η λειτουργία θα επιστρέψει 3
ΑΝΤΙΣΤΑΘΜΙΖΕΤΑΙ: Επιστρέφει μια αναφορά σε ένα εύρος που αντισταθμίζει έναν αριθμό γραμμών και στηλών από άλλο εύρος ή κελί.
Σύνταξη της συνάρτησης OFFSET: = OFFSET (αναφορά, γραμμές, στήλες, ύψος, πλάτος)
Αναφορά:- Αυτό είναι το κελί ή το εύρος από το οποίο θέλετε να αντισταθμίσετε.
Σειρές και στήλες για μετακίνηση: - Ο αριθμός των σειρών που θέλετε να μετακινήσετε από το σημείο εκκίνησης και οι δύο αυτές μπορεί να είναι θετικές, αρνητικές ή μηδενικές.
Heψος και Πλάτος: - Αυτό είναι το μέγεθος του εύρους που θέλετε να επιστρέψετε. Αυτό είναι ένα προαιρετικό πεδίο.
Ας πάρουμε ένα παράδειγμα για να κατανοήσουμε τη συνάρτηση Offset στο Excel.
Έχουμε δεδομένα στο εύρος A1: D10. Η στήλη Α περιέχει κωδικό προϊόντος, η στήλη Β περιέχει ποσότητα, η στήλη Γ περιέχει κόστος ανά προϊόν και η στήλη Δ περιέχει συνολικό κόστος. Πρέπει να επιστρέψουμε την τιμή του κελιού C5 στο κελί Ε2.
Για να έχουμε το επιθυμητό αποτέλεσμα, πρέπει να ακολουθήσουμε τα παρακάτω βήματα.
- Επιλέξτε το κελί Ε2 και γράψτε τον τύπο.
- = OFFSET (A1,4,2,1,1)και πατήστε Enter στο πληκτρολόγιο.
- Η συνάρτηση θα επιστρέψει την τιμή του κελιού C5.
Σε αυτό το παράδειγμα, πρέπει να λάβουμε την τιμή από το κελί C5 έως E2. Το κελί αναφοράς μας είναι το πρώτο κελί στην περιοχή που είναι A1 και το C5 είναι 4 σειρές παρακάτω και 2 στήλες στα δεξιά από το A1. Ως εκ τούτου, ο τύπος είναι = OFFSET (A1,4,2,1,1) ή = OFFSET (A1,4,2) (αφού το 1,1 είναι προαιρετικό).
Τώρα, ας πάρουμε ένα παράδειγμα για να ανακτήσουμε την τελευταία τιμή σε μια δυναμική λίστα.
Έχουμε ονόματα χωρών σε μια περιοχή. Τώρα, αν προσθέσουμε περισσότερες χώρες σε αυτήν τη λίστα, θα πρέπει να είναι διαθέσιμη αυτόματα στην αναπτυσσόμενη λίστα.
Για να προετοιμάσετε προηγμένο φίλτρο, ακολουθήστε τα παρακάτω βήματα:-
- Επιλέξτε το κελί Β2.
- Μεταβείτε στην καρτέλα Δεδομένα, επιλέξτε Επικύρωση δεδομένων από την ομάδα εργαλείων δεδομένων.
- Θα εμφανιστεί το παράθυρο διαλόγου "Επικύρωση δεδομένων". Στην καρτέλα "Ρυθμίσεις", επιλέξτε "Προσαρμοσμένο" από την αναπτυσσόμενη λίστα "Να επιτρέπεται".
- Το πλαίσιο τύπου θα ενεργοποιηθεί.
- Γράψτε τον τύπο σε αυτό το πλαίσιο.
- = OFFSET (A: A, 1,0, COUNTA (A: A) -1,1).
- Κάντε κλικ στο OK.
- Σε αυτό το στάδιο, το τελευταίο ενημερωμένο κελί είναι το A11.
- Για να ελέγξετε αν η επικύρωση δεδομένων λειτουργεί σωστά, προσθέστε ένα όνομα πόλης στο κελί A12.
Μόλις προσθέσετε μια καταχώριση στο A12, θα προστεθεί στην αναπτυσσόμενη λίστα.
Αυτός είναι ο τρόπος με τον οποίο μπορούμε να προσθέσουμε περισσότερες καταχωρήσεις από 999 στοιχεία στο Microsoft Excel.
