פונקציית XLOOKUP ב-Excel היא אחת הפונקציות החזקות והגמישות ביותר לחיפוש נתונים. היא נועדה להחליף את VLOOKUP, HLOOKUP, וגם את INDEX+MATCH, תוך שהיא מתגברת על המגבלות שלהן.
להלן האפשרויות והשימושים המרכזיים של XLOOKUP:
XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
הסבר פרמטרים:
- lookup_value
הערך אותו רוצים לחפש. - lookup_array
טווח התאים שבו מתבצע החיפוש (עמודה/שורה). - return_array
טווח התאים שממנו רוצים להחזיר את הערך. - [if_not_found] (אופציונלי)
הערך שיוחזר אם לא נמצא התאמה. ברירת המחדל היא שגיאת #N/A. - ) [match_mode] אופציונלי)
שיטה לקביעת סוג ההתאמה:
- 0 התאמה מדויקת (ברירת המחדל).
- 1- התאמה מדויקת או הערך הקטן ביותר הקרוב.
- 1 התאמה מדויקת או הערך הגדול ביותר הקרוב.
- 2 שימוש בתווים כלליים (*, ?, ~) לחיפוש.
- [search_mode] (אופציונלי)
מצב החיפוש:
- 1 חיפוש מההתחלה לסוף (ברירת מחדל).
- 1 – חיפוש מהסוף להתחלה.
- 2 חיפוש בינארי בסדר עולה (לטבלאות ממוינות).
יתרונות מרכזיים:
- תמיכה בחיפוש שמאלה
בניגוד ל-VLOOKUP, ניתן לחפש בעמודה כלשהי ולהחזיר ערך מעמודה שממוקמת שמאלה ממנה. - התמודדות עם ערכים חסרים
אפשרות להגדיר מה יוחזר אם לא נמצא ערך (למשל, "לא נמצא"). - גמישות במציאת התאמות
פונקציה זו תומכת בחיפוש ערכים מדויקים וגם בערכים הקרובים ביותר. - חיפוש דינמי לפי כיוון
ניתן לבצע חיפוש מהסוף להתחלה בקלות (אידיאלי למקרים בהם יש נתונים כפולים). - חיפוש בתווים כלליים
מאפשר חיפוש מתקדם המבוסס על דפוסי תווים, למשל:- * מייצג מספר כלשהו של תווים.
- ? מייצג תו יחיד.
דוגמאות:
- חיפוש פשוט:
=XLOOKUP(1001, A2:A10, B2:B10)
מחפש את הערך 1001 בעמודה A ומחזיר את הערך המקביל מעמודה B.
- חיפוש עם ערך חלופי:
=XLOOKUP(500, A2:A10, B2:B10, "לא נמצא")
אם הערך 500 לא נמצא, יוחזר "לא נמצא".
- חיפוש עם התאמה קרובה:
=XLOOKUP(50, A2:A10, B2:B10, "לא נמצא", 1)
יחפש את הערך הקרוב הגדול ביותר אם ערך מדויק לא נמצא.
- חיפוש מהסוף להתחלה:
=XLOOKUP(1001, A2:A10, B2:B10, לא נמצא", 0, -1")
מחפש את ההתאמה האחרונה של 1001 בעמודה A
- חיפוש באמצעות תווים כלליים:
=XLOOKUP("תפ*", A2:A10, B2:B10, לא נמצא", 2")
מחפש ערכים שמתחילים ב"תפ".
- פונקציית XLOOKUP ב-Excel מאפשרת חיפוש והחזרת ערכים בהתבסס על קריטריונים מוגדרים. בגרסאות הבטא האחרונות, נוספה לפונקציה זו תמיכה בביטויים רגולריים Regular Expressions או RegEx המאפשרים חיפושים מורכבים וגמישים יותר.
מהם ביטויים רגולריים (RegEx)?
ביטויים רגולריים הם שפה לתיאור תבניות טקסטואליות, המשמשת לזיהוי, התאמה ומניפולציה של מחרוזות טקסט. באמצעות RegEx, ניתן לבצע חיפושים מתקדמים כמו:
- התאמה למילים שמתחילות או מסתיימות בתווים מסוימים.
- זיהוי מחרוזות המכילות תבניות ספציפיות, כמו מספרי טלפון, כתובות דוא"ל או קודי דואר.
- התאמה למילים עם מספר משתנה של תווים או תווים מיוחדים.
כיצד להשתמש ב-RegEx ב-XLOOKUP?
בגרסת הבטא, ניתן להשתמש ב-RegEx באמצעות הפרמטר [match_mode] של הפונקציה XLOOKUP. כאשר מגדירים את match_mode לערך המתאים, הפונקציה תבצע חיפוש בהתבסס על הביטוי הרגולרי שסופק.
דוגמה:
נניח שיש לנו טבלה עם רשימת שמות בעמודה A, ואנו רוצים למצוא את השם הראשון שמתחיל באות "א" ומסתיים באות "ן".
הנוסחה תהיה:
=XLOOKUP("^א.*ן$", A2:A100, A2:A100, לא נמצא", 2")
הסבר:
- "^א.*ן$" הוא הביטוי הרגולרי:
- ^ מציין תחילת מחרוזת.
- א היא האות הראשונה.
- .* מציין כל מספר של תווים בין ההתחלה לסוף.
- ן היא האות האחרונה.
- $ מציין סוף מחרוזת.
- A2:A100 הוא הטווח שבו מתבצע החיפוש.
- "לא נמצא" הוא הערך שיוחזר אם לא נמצאה התאמה.
- 2 ב- match_mode מציין שימוש בתווים כלליים או ביטויים רגולריים לחיפוש.
הערות:
- תמיכה ב- RegEx ב -XLOOKUP זמינה כרגע בגרסאות הבטא של Excel.
- יש לוודא שהביטויים הרגולריים נכתבים בצורה נכונה כדי לקבל את התוצאות הרצויות.
- השימוש ב-RegEx מאפשר חיפושים מורכבים וגמישים יותר, אך דורש הבנה בסיסית של התחביר שלהם.
ביטויים רגולריים (RegEx) הם כלי עוצמתי לעיבוד טקסט, המאפשרים התאמה, חיפוש ומניפולציה של מחרוזות בהתבסס על תבניות מוגדרות. להלן טבלה המרכזת את הסימנים הנפוצים ב-RegEx ומשמעותם:
| סימן | משמעות | דוגמה | תיאור |
| . | כל תו בודד (למעט תו ירידת שורה) | a.b | מתאים ל-"a" ואחריו כל תו בודד ואז "b" (כגון "acb", "a1b") |
| \d | ספרה (0-9) | \d\d | מתאים לשתי ספרות רצופות (כגון "42", "99") |
| \D | כל תו שאינו ספרה | \D\D | מתאים לשני תווים שאינם ספרות (כגון "ab", "!!") |
| \w | תו מילה: אותיות, ספרות וקו תחתון (באנגלית) | \w\w\w | מתאים לשלושה תווי מילה רצופים (כגון "abc", "A1_") |
| \W | כל תו שאינו תו מילה | \W\W | מתאים לשני תווים שאינם תווי מילה (כגון "@#", " !") |
| \s | תו רווח: רווח, טאבים ותווי שורה חדשה | \s\s | מתאים לשני תווי רווח רצופים (כגון " ", "\t\n") |
| \S | כל תו שאינו תו רווח | \S\S | מתאים לשני תווים שאינם רווח (כגון "ab", "A1") |
| ^ | תחילת מחרוזת | ^Hello | מתאים למחרוזת שמתחילה ב-"Hello" |
| $ | סוף מחרוזת | world$ | מתאים למחרוזת שמסתיימת ב-"world" |
| * | אפס או יותר חזרות של התו/התבנית הקודמת | a* | מתאים לרצף של אפס או יותר תווי "a" (כגון "", "a", "aaaa") |
| + | אחת או יותר חזרות של התו/התבנית הקודמת | a+ | מתאים לרצף של אחת או יותר תווי "a" (כגון "a", "aa", "aaa") |
| ? | אפס או אחת חזרות של התו/התבנית הקודמת | a? | מתאים לאפס או תו "a" אחד (כגון "", "a") |
| {n} | בדיוק n חזרות של התו/התבנית הקודמת | a{3} | מתאים בדיוק לשלושה תווי "a" רצופים (כגון "aaa") |
| {n,m} | בין n ל-m חזרות של התו/התבנית הקודמת | a{2,4} | מתאים בין שניים לארבעה תווי "a" רצופים (כגון "aa", "aaa", "aaaa") |
| ` | ` | או (OR) בין תבניות | `cat |
| [] | מחלקת תווים: כל תו מתוך הרשימה | [abc] | מתאים ל-"a", "b" או "c" |
| [^] | מחלקת תווים שלילית: כל תו שאינו ברשימה | [^abc] | מתאים לכל תו שאינו "a", "b" או "c" |
| () | סוגריים לקיבוץ תבניות ולהתייחסות אליהן כיחידה אחת; משמשים גם ללכידת קבוצות | (abc) | מתאים ל-"abc" ומאפשר התייחסות לקבוצה זו בנפרד |
| \b | גבול מילה | \bword\b | מתאים למילה "word" כולה, לא כחלק ממילה אחרת (כגון "sword") |
| \B | לא גבול מילה | \Bword\B | מתאים ל-"word" כחלק ממילה אחרת |
| \ | תו מילוט (escape) לתווים מיוחדים, כדי להתייחס אליהם כתווים רגילים | \. | מתאים לנקודה "." תרתי משמע |
| (?=…) | מבט קדימה חיובי: התאמה אם התבנית בתוך הסוגריים מופיעה לאחר התו/התבנית הנוכחית | a(?=b) | מתאים ל-"a" שרק אם אחריו מופיע "b" (כגון "ab") |
| (?!…) | מבט קדימה שלילי: התאמה אם התבנית בתוך הסוגריים לא מופיעה לאחר התו/התבנית הנוכחית | a(?!b) | מתאים ל-"a" שרק אם לא אחריו מופיע "b" (כגון "ac", "a") |
| (?<=…) | מבט אחורה חיובי: התאמה אם התבנית בתוך הסוגריים מופיעה לפני התו/התבנית הנוכחית | (?<=a)b | מתאים ל-"b" שרק אם לפניו מופיע "a" (כגון "ab") |
| (?<!…) | מבט אחורה שלילי: התאמה אם התבנית בתוך הסוגריים לא מופיעה לפני התו/התבנית הנוכחית | (?<!a)b | מתאים ל-"b" שרק אם לא לפניו מופיע "a" (כגון "cb", "b") |
הערות:
- ישנם תווים מיוחדים נוספים ותכונות מתקדמות ב-RegEx, אך הטבלה לעיל מכסה את הסימנים הנפוצים והשימושיים ביותר.
- השימוש ב-RegEx עשוי להשתנות מעט בין שפות תכנות וכלי תוכנה שונים, ולכן מומלץ לעיין בתיעוד הספציפי של הסביבה בה אתם עובדים.
השילוב של ביטויים רגולריים בפונקציית XLOOKUP מרחיב את היכולות של Excel בביצוע חיפושים מתקדמים ומורכבים, ומאפשר למשתמשים לבצע התאמות מדויקות וגמישות יותר בנתונים שלהם.
סיכום:
פונקציית XLOOKUP מחליפה בצורה חכמה את הפונקציות המסורתיות, תוך מתן שליטה וגמישות רבה יותר למשתמשים. היא אידיאלית לעבודות חיפוש מורכבות וגם פשוטות, ומומלצת לכל מי שעובד עם נתונים ב-Excel.
