Αυτό το άρθρο θα μιλήσει για τον τρόπο αντιστοίχισης όλων των τιμών από έναν πίνακα και ανάκτησής τους σε διαφορετικά κελιά. Αυτό είναι παρόμοιο με την αναζήτηση πολλαπλών τιμών.
Γενικός τύπος
{= INDEX (ονόματα, ΜΙΚΡΑ (ΑΝ (ομάδες = όνομα_ομάδας, ΣΕΙΡΑ (ονόματα) -ΜΙΝ (ΣΕΙΡΑ (ονόματα))+1), ΣΤΗΛΕΣ (επέκταση εύρους))), "--List Ends--")}
Πάρα πολλές συναρτήσεις και μεταβλητές !!!. Ας δούμε ποιες είναι αυτές οι μεταβλητές.
Ονόματα: Αυτή είναι η λίστα με τα ονόματα.
Ομάδες: Ο κατάλογος της ομάδας στην οποία ανήκουν επίσης αυτά τα ονόματα.
Ονομα ομάδας: η αναφορά του ονόματος της ομάδας.
Επέκταση εύρους: αυτό είναι ένα διευρυνόμενο εύρος που χρησιμοποιείται για να αυξάνεται ο αριθμός όταν αντιγράφεται στα δεξιά.
Παράδειγμα: Εξαγάγετε τα ονόματα των εργαζομένων σε διαφορετικές στήλες ανάλογα με την εταιρεία τους.
Ας υποθέσουμε ότι έχετε έναν πίνακα εργαζομένων που ομαδοποιείται ανάλογα με την εταιρεία τους. Η πρώτη στήλη περιέχει ονόματα εργαζομένων και η δεύτερη στήλη το όνομα της εταιρείας.
Τώρα πρέπει να λάβουμε το όνομα κάθε εργαζομένου σε διαφορετικές στήλες ανάλογα με την εταιρεία τους. Με άλλα λόγια, πρέπει να τα αποσυγκροτήσουμε.
Εδώ, ονόμασα το A2: A10 ως εργαζόμενο και το B2: B10 ως εταιρεία, έτσι ώστε ο τύπος να είναι ευανάγνωστος.
Γράψτε αυτόν τον τύπο πίνακα στο F2. Χρησιμοποιήστε CTRL+SHIFT+ENTER για να εισαγάγετε αυτόν τον τύπο.
{= INDEX (Υπάλληλος, ΜΙΚΡΟΣ (ΕΑΝ (Εταιρεία = $ E2, ROW (Υπάλληλος)) -MIN (ROW (Υπάλληλος))+1), ΣΤΗΛΕΣ ($ E $ 1: E1))), "--List Ends--" )}
Αντιγράψτε αυτόν τον τύπο σε όλα τα κελιά. Θα εξαγάγει κάθε μεμονωμένο όνομα στις διαφορετικές στήλες ανάλογα με την ομάδα τους.
Όπως μπορείτε να δείτε στην παραπάνω εικόνα, κάθε υπάλληλος διαχωρίζεται σε διαφορετικά κελιά.
Λοιπόν, πώς λειτουργεί αυτός ο τύπος;
Για να κατανοήσετε τον τύπο, ας δούμε τον τύπο στο G2
Ποιο είναι = IFERROR (INDEX (Υπάλληλος, ΜΙΚΡΟ (ΑΝ (Εταιρεία = $ E3,ΣΕΙΡΑ(Υπάλληλος) -MIN (ΣΕΙΡΑ (Υπάλληλος))+1), ΣΤΗΛΕΣ ($ E $ 1: F2))), "-Η λίστα τελειώνει--")
Οι μηχανισμοί είναι απλοί και σχεδόν ίδιοι με τον πολλαπλό τύπο VLOOKUP. Το κόλπο είναι να πάρετε τον αριθμό ευρετηρίου κάθε υπαλλήλου από διαφορετικές ομάδες και να τον περάσετε στον τύπο INDEX. Αυτό γίνεται με αυτό το μέρος του τύπου.
ΑΝ(Εταιρία=Ε3 $,ΣΕΙΡΑ(Υπάλληλος) -MIN (ΣΕΙΡΑ (Υπάλληλος))+1):
Αυτό το μέρος επιστρέφει μια σειρά ευρετηρίων και ψευδών για το όνομα της εταιρείας σε $ E3, η οποία περιέχει "Rankwatch".
{FALSE; 2; FALSE; 4; FALSE; FALSE; 7; FALSE; 9}.
Πως? Ας το γκρεμίσουμε από μέσα.
Εδώ αντιστοιχίζουμε το όνομα της εταιρείας σε $ E3 με κάθε τιμή in Εύρος εταιρείας (Εταιρεία = $ E3).
Αυτό επιστρέφει μια σειρά αληθινών και ψευδών. {FALSE;ΑΛΗΘΗΣ;ΨΕΥΔΗΣ;ΑΛΗΘΗΣΛΑΘΟΣ; ΛΑΘΟΣΑΛΗΘΗΣ;ΨΕΥΔΗΣ;ΑΛΗΘΗΣ}.
Τώρα η συνάρτηση IF εκτελεί τις TRUE δηλώσεις της για TRUE, η οποία είναι ROW (Employee) -MIN (ROW (Υπάλληλος))+1. Αυτό το μέρος επιστρέφει αυτό το μέρος επιστρέφει μια σειρά ευρετηρίων που ξεκινούν από το 1 έως τον αριθμό υπαλλήλους {1; 2; 3; 4; 5; 6; 7; 8; 9}. Η συνάρτηση if παίρνει τιμές μόνο για TRUE, η οποία με τη σειρά της μας δίνει {FALSE; 2; FALSE; 4; FALSE; FALSE; 7; FALSE; 9}.
Ο τρέχων τύπος απλοποιείται σε
= IFERROR (INDEX (Εργαζόμενος, ΜΙΚΡΟΣ ({FALSE; 2; FALSE; 4; FALSE) FALSE; 7; FALSE; 9},ΣΤΗΛΕΣ ($ E $ 1: F2))), "-Η λίστα τελειώνει--"). Όπως γνωρίζουμε, η μικρή συνάρτηση επιστρέφει την ένατη μικρότερη τιμή από έναν πίνακα. ΣΤΗΛΕΣ ($ E $ 1: F2) αυτό επιστρέφει 2. Η συνάρτηση SMALL επιστρέφει τη δεύτερη μικρότερη τιμή από τον παραπάνω πίνακα, η οποία είναι 4.
Τώρα ο τύπος απλοποιήθηκε = IFERROR (INDEX (Υπάλληλος, 4), "-Η λίστα τελειώνει--"). Τώρα, η συνάρτηση INDEX επιστρέφει απλώς το τέταρτο όνομα από υπάλληλος πίνακας που μας δίνει "ο Σαμ”.
Ναι, έτσι εξάγετε ονόματα από ομάδες σε διαφορετικές στήλες χρησιμοποιώντας τη λειτουργία INDEX, SMALL, ROW, COLUMNS και IF. Εάν έχετε οποιαδήποτε αμφιβολία σχετικά με αυτήν τη λειτουργία ή εάν δεν λειτουργεί για εσάς, ενημερώστε με την παρακάτω ενότητα σχολίων.
Λήψη αρχείου:
Σχετικά Άρθρα:
VLOOKUP Πολλαπλές τιμές
Χρησιμοποιήστε INDEX και MATCH για αναζήτηση τιμής
Αξία αναζήτησης με πολλαπλά κριτήρια
Δημοφιλή άρθρα:
Η συνάρτηση VLOOKUP στο Excel
COUNTIF στο Excel 2016
Πώς να χρησιμοποιήσετε τη συνάρτηση SUMIF στο Excel
