Εξετάστε αυτό το σχήμα:
Ας υποθέσουμε ότι θέλετε να δημιουργήσετε μια αναφορά από αυτό σαν να φιλτράρετε στην περιοχή. Δηλαδή, αν φιλτράρετε στο Βορρά, θα δείτε:
Τι γίνεται όμως αν θέλετε μια έκδοση με βάση τον τύπο του ίδιου πράγμα;
Εδώ είναι το αποτέλεσμα που ψάχνετε στις στήλες I: K:
Είναι σαφές ότι είναι η ίδια αναφορά, αλλά δεν υπάρχουν φιλτραρισμένα στοιχεία εδώ. Αν θέλετε μια νέα αναφορά για την Ανατολή, θα ήταν καλό να αλλάξετε απλώς την τιμή στο G1 σε Ανατολή:
Δείτε πώς γίνεται. Πρώτα απ 'όλα, δεν γίνεται με χρήση του VLOOKUP. Έτσι είπα ψέματα για τον τίτλο αυτής της τεχνικής!
Η στήλη F δεν εμφανίστηκε στο παρελθόν και μπορεί να κρυφτεί (ή να μετακινηθεί κάπου αλλού, ώστε να μην παρεμβαίνει στην αναφορά).
Αυτό που φαίνεται στη στήλη F είναι οι αριθμοί σειράς όπου το G1 βρίσκεται στη στήλη Α. δηλαδή, ποιες σειρές περιέχουν την τιμή "Βόρεια"; Αυτή η τεχνική περιλαμβάνει τη χρήση πάνω από το κελί, γι 'αυτό πρέπει να ξεκινήσει τουλάχιστον σειρά 2. συμφωνεί με την τιμή «Βόρεια» κατά της στήλης Α, αλλά αντί για το σύνολο της στήλης, χρησιμοποιήστε μια συνάρτηση OFFSET: OFFSET($A$1,F1,0,1000,1).
Εφόσον το F1 είναι 0, αυτό OFFSET(A1,0,0,1000,1)είναι το A1: A1000. (Το 1000 είναι αυθαίρετο, αλλά αρκετά μεγάλο για να κάνει τη δουλειά - μπορείτε να το κάνετε οποιονδήποτε άλλο αριθμό).
Η τιμή 2 στο F2 είναι εκεί που βρίσκεται το πρώτο "North". Θέλετε επίσης να προσθέσετε ξανά την τιμή του F1 στο τέλος, αλλά μέχρι στιγμής είναι μηδέν.
Η «μαγεία» ζωντανεύει στο κελί F3. Γνωρίζετε ήδη ότι ο πρώτος Βορράς βρίσκεται στη Σειρά 2. Έτσι, θέλετε να ξεκινήσετε την αναζήτηση δύο σειρών κάτω από το Α1. Μπορείτε να το κάνετε ορίζοντας το 2 ως το δεύτερο όρισμα της συνάρτησης OFFSET.
Ο τύπος στην F3 θα επισημάνει αυτόματα στο 2 που υπολογίστηκε στο κελί F2: Όταν αντιγράφετε τον τύπο προς τα κάτω, θα δείτε =OFFSET($A$1,F2,0,1000,1)ποια είναι OFFSET($A$1,2,0,1000,1)ποια είναι Α3: A1000. Έτσι αντιστοιχίζετε το North με αυτό το νέο εύρος και βρίσκει το North στο τρίτο κελί αυτής της νέας σειράς, οπότε το MATCH δίνει 3.
Προσθέτοντας πίσω την τιμή από το παραπάνω κελί, F2, θα δείτε το 3 συν το 2 ή το 5, που είναι η σειρά που περιέχει το δεύτερο Βορρά.
Αυτός ο τύπος συμπληρώνεται αρκετά μακριά για να πάρει όλες τις τιμές.
Αυτό θα σας δώσει τους αριθμούς σειράς όπου βρίσκονται όλες οι εγγραφές του Βορρά.
Πώς μεταφράζετε αυτούς τους αριθμούς σειράς στα αποτελέσματα στις στήλες I έως K; Όλα γίνονται με έναν μόνο τύπο. Εισάγετε αυτόν τον τύπο σε Ι2: =IFERROR(INDEX(A:A,$F2),””). Αντιγράψτε δεξιά και, στη συνέχεια, αντιγράψτε προς τα κάτω.
Γιατί να χρησιμοποιήσετε το IFERROR; Πού είναι το σφάλμα; Παρατηρήστε το κελί F6 - περιέχει # N / A (γι 'αυτό θα θέλατε να αποκρύψετε τη στήλη F) επειδή δεν υπάρχουν πια στη Βόρεια μετά τη σειρά 15. Επομένως, εάν η στήλη F είναι σφάλμα, επιστρέψτε ένα κενό. Διαφορετικά, πάρτε την τιμή από τη στήλη A (και όταν συμπληρωθεί δεξιά, B & C).
Το $ F2 είναι μια απόλυτη αναφορά στη στήλη F, οπότε το δικαίωμα πλήρωσης αναφέρεται ακόμα στη στήλη F.
Αυτό το άρθρο επισκεπτών προέρχεται από το Excel MVP Bob Umlas. Είναι μια από τις αγαπημένες του τεχνικές από το βιβλίο του, το Excel Out the Box.
Excel εκτός κουτιού »
