Τα VLOOKUP, HLOOKUP και INDEX-MATCH είναι διάσημοι και συνηθισμένοι τρόποι αναζήτησης τιμών στον πίνακα Excel. Αλλά αυτοί δεν είναι οι μόνοι τρόποι αναζήτησης τιμών στο Excel. Σε αυτό το άρθρο, θα μάθουμε πώς να χρησιμοποιούμε τη συνάρτηση OFFSET μαζί με τη συνάρτηση MATCH στο Excel. Ναι, το διαβάσατε σωστά, θα χρησιμοποιήσουμε τη διαβόητη συνάρτηση OFFSET του Excel για να αναζητήσουμε μια συγκεκριμένη τιμή σε έναν πίνακα Excel.
Γενικός τύπος,
| = OFFSET (StartCell, MATCH (RowLookupValue, RowLookupRange, 0), MATCH (ColLookupValue, ColLookupRange, 0)) |
Διαβάστε αυτό προσεκτικά:
StartCell:Αυτό είναι το αρχικό κελί του πίνακα αναζήτησης. Ας πούμε αν θέλετε να αναζητήσετε στην περιοχή A2: A10, τότε το StartCell θα είναι A1.
RowLookupValue: Αυτή είναι η τιμή αναζήτησης που θέλετε να βρείτε στις γραμμές κάτω από τοStartCell.
RowLookupRange:Αυτό είναι το εύρος στο οποίο θέλετε να αναζητήσετε το RowLookupValue. Είναι το εύρος παρακάτω StartCell (A2: A10).
ColLookupValue: Αυτή είναι η τιμή αναζήτησης που θέλετε να βρείτε σε στήλες (κεφαλίδες).
ColLookupRange:Αυτό είναι το εύρος στο οποίο θέλετε να αναζητήσετε το ColLookupValue. Είναι το εύρος στη δεξιά πλευρά του StartCell (όπως το B1: D1).
Λοιπόν, αρκετά από τη θεωρία. Ας ξεκινήσουμε με ένα παράδειγμα.
Παράδειγμα: Αναζήτηση πωλήσεων χρησιμοποιώντας τη λειτουργία OFFSET και MATCH Από τον πίνακα Excel
Εδώ έχουμε ένα δείγμα πίνακα πωλήσεων που πραγματοποιήθηκαν από διαφορετικούς υπαλλήλους σε διαφορετικούς μήνες.
Τώρα το αφεντικό μας μας ζητά να δώσουμε τις πωλήσεις που πραγματοποίησε η Id 1004 τον Ιούνιο. Υπάρχουν πολλοί τρόποι για να το κάνουμε, αλλά επειδή μαθαίνουμε για τον τύπο OFFSET-MATCH, θα τους χρησιμοποιήσουμε για να αναζητήσουμε την επιθυμητή τιμή.
Έχουμε ήδη τον γενικό τύπο παραπάνω. Απλώς πρέπει να προσδιορίσουμε αυτές τις μεταβλητές σε αυτόν τον πίνακα.
StartCell είναι Β1 εδώ. RowLookupValue είναι Μ1. RowLookupRange είναι B2: B1o (εύρος κάτω από το startcell).
ColLookupValue βρίσκεται στο κελί Μ2. ColLookupRange είναι C1: I1 (Εύρος κεφαλίδων στα δεξιά του StartCell).
Έχουμε εντοπίσει όλες τις μεταβλητές. Ας τα βάλουμε σε έναν τύπο Excel.
Γράψτε αυτόν τον τύπο στο κελί M3 και πατήστε το κουμπί εισαγωγής.
| = OFFSET (B1, MATCH (M1, B2: B10,0), MATCH (M2, C1: I1,0)) |
Καθώς πατάτε το κουμπί εισαγωγής, λαμβάνετε το αποτέλεσμα αμέσως. Η πώληση που έγινε με το ID 1004 τον Ιούνιο είναι 177.
Πώς λειτουργεί;
Το έργο της συνάρτησης OFFSET είναι να απομακρυνθεί από το δεδομένο κελί εκκίνησης σε δεδομένη γραμμή και στήλες και στη συνέχεια να επιστρέψει τιμή από αυτό το κελί. Για παράδειγμα, αν γράψω OFFSET (B1,1,1), η συνάρτηση OFFSET θα μεταβεί στο κελί C2 (1 κελί προς τα κάτω, 1 κελί προς τα δεξιά) και επιστρέφει την τιμή του.
Εδώ, έχουμε τον τύποOFFSET (B1, MATCH (M1, B2: B10,0), MATCH (M2, C1: I1,0)).
Η πρώτη συνάρτηση MATCH επιστρέφει τον δείκτη M1 (1004) στο εύρος B2: B10, που είναι 4. Τώρα ο τύπος είναι,OFFSET (B1,4, MATCH (M2, C1: I1,0)).
Η επόμενη συνάρτηση MATCH επιστρέφει τον δείκτη M2 ('Jun') στο εύρος C1: I1, το οποίο i 7. Τώρα ο τύπος είναιOFFSET (Β1,4,7).
Τώρα η συνάρτηση OFFSET μετακινεί απλώς 4 κελιά προς τα κάτω στο κελί Β1 που το μεταφέρει στο Β5. Στη συνέχεια, η συνάρτηση OFFSET μετακινείται στο 7 αριστερά στο Β5, το οποίο το μεταφέρει στο Ι5. Αυτό είναι. Η συνάρτηση OFFSET επιστρέφει την τιμή από το κελί I5 το οποίο 177.
Πλεονεκτήματα αυτής της τεχνικής αναζήτησης;
Αυτό είναι γρήγορο. Το μόνο πλεονέκτημα αυτής της μεθόδου είναι ότι είναι ταχύτερη από τις άλλες μεθόδους. Το ίδιο πράγμα μπορεί να γίνει χρησιμοποιώντας τη λειτουργία INDEX-MATCH ή τη λειτουργία VLOOKUP. Αλλά είναι καλό να γνωρίζουμε μερικές εναλλακτικές λύσεις. Μπορεί να είναι βολικό κάποια μέρα.
Λοιπόν, ναι, με αυτόν τον τρόπο μπορείτε να χρησιμοποιήσετε τη συνάρτηση OFFSET και MATCH για ΑΝΑΚΟΙΝΩΣΗ τιμών στους πίνακες Excel. Ελπίζω να ήταν αρκετά επεξηγηματικό. Εάν έχετε αμφιβολίες σχετικά με αυτό το άρθρο ή οποιοδήποτε άλλο θέμα που σχετίζεται με το excel/VBA, ρωτήστε με στην παρακάτω ενότητα σχολίων.
Χρησιμοποιήστε INDEX και MATCH για αναζήτηση τιμής:Ο τύπος INDEX-MATCH χρησιμοποιείται για να αναζητήσει δυναμικά και με ακρίβεια μια τιμή στον δεδομένο πίνακα. Αυτή είναι μια εναλλακτική λύση στη λειτουργία VLOOKUP και ξεπερνά τις αδυναμίες της συνάρτησης VLOOKUP.
Άθροισμα κατά ομάδες OFFSET σε γραμμές και στήλες: Η συνάρτηση OFFSET μπορεί να χρησιμοποιηθεί για να αθροίσει δυναμικά την ομάδα κελιών. Αυτές οι ομάδες μπορούν να βρίσκονται οπουδήποτε στο φύλλο.
Πώς να ανακτήσετε κάθε Nth τιμή σε μια περιοχή στο Excel: Χρησιμοποιώντας τη συνάρτηση OFFSET του Excel μπορούμε να ανακτήσουμε τιμές από εναλλασσόμενες γραμμές ή στήλες. Αυτός ο τύπος απαιτεί τη βοήθεια της συνάρτησης ROW για εναλλαγή γραμμών και συνάρτηση ΣΤΗΛΗ για εναλλαγή σε στήλες.
Πώς να χρησιμοποιήσετε τη λειτουργία OFFSET στο Excel: Η συνάρτηση OFFSET είναι μια ισχυρή συνάρτηση Excel που υποτιμάται από πολλούς χρήστες. Αλλά οι ειδικοί γνωρίζουν τη δύναμη της συνάρτησης OFFSET και πώς μπορούμε να χρησιμοποιήσουμε αυτήν τη λειτουργία για να κάνουμε μερικές μαγικές εργασίες σε τύπο Excel. Ακολουθούν τα βασικά της λειτουργίας OFFSET.
Δημοφιλή άρθρα:
50 συντομεύσεις Excel για να αυξήσετε την παραγωγικότητά σας | Γίνετε πιο γρήγοροι στην εργασία σας. Αυτές οι 50 συντομεύσεις θα σας κάνουν να εργάζεστε ακόμη πιο γρήγορα στο Excel.
Πώς να χρησιμοποιήσετε τη συνάρτηση Excel VLOOKUP| Αυτή είναι μια από τις πιο δημοφιλείς και δημοφιλείς λειτουργίες του excel που χρησιμοποιείται για την αναζήτηση τιμών από διαφορετικά εύρη και φύλλα.
Πώς να χρησιμοποιήσετε το Excel Λειτουργία COUNTIF| Μετρήστε τιμές με συνθήκες χρησιμοποιώντας αυτήν την εκπληκτική συνάρτηση. Δεν χρειάζεται να φιλτράρετε τα δεδομένα σας για να μετρήσετε συγκεκριμένες τιμές. Η λειτουργία COUNTIF είναι απαραίτητη για την προετοιμασία του πίνακα ελέγχου σας.
Πώς να χρησιμοποιήσετε τη συνάρτηση SUMIF στο Excel | Αυτή είναι μια άλλη βασική λειτουργία του ταμπλό. Αυτό σας βοηθά να συνοψίσετε τιμές υπό συγκεκριμένες συνθήκες.