קורס אקסל מתקדם פתרונות תרגילים בנושא Vlookup & Match
תוכן הדף
• פונקציית Match - הסבר כללי
- פונקציית Match דוגמה מס' 1 (התאמה מדוייקת)
- פונקציית Match דוגמה מס' 2 (התאמה מדוייקת)
- פונקציית Match דוגמה מס' 3 (טווח לפי 1-)
- פונקציית Match דוגמה מס' 4 (טווח לפי 1)
• סרטון פתרון תרגיל - אפשרות פתרון מס' 1 (הפנייה לטווח רגיל)
• סרטון פתרון תרגיל - אפשרות פתרון מס' 2 (הפנייה לשם)
• סרטון פתרון תרגיל - אפשרות פתרון מס' 2 (הפנייה לטבלה)
פתרונות תרגילים בנושאים נוספים | קורס אקסל מתקדם
- פונקציית IF
- פונקציית Vlookup
- טבלה חכמה/דינמית (Format as Table)
- טבלאות ציר (Pivot Tables)
- עיבוד וטיפול בנתונים (Text to Columns, FlashFill, Text Functions)
פונקציית Match
פונקציית Match מאפשרת לחפש ערך מסויים (מספר או טקסט) בטווח נתון (עמודה או שורה) ולאחר שאותר (במידה ואוצר) הפונקציה תחזיר את מיקומו (האינדקס שלו) בטווח שצויין.
עם פונקציית Match ניתן לעבוד ב-3 מצבי חיפוש:
• (1-) עבור חיפוש בטווח: התאמה עבור הערך הגבוה הקרוב ביותר (יש למיין את הערכים בסדר יורד).
• (0) חיפש בהתאמה מדוייקת: כמו עם Vlookup, נקבל NA במידה הערך לא נמצא בהתאמה מלאה (מתאים לעבוד עם מספרים וטקסט).
• (1) חיפוש בטווח: התאמה עבור הערך הנמוך הקרב ביותר (יש למיין את הערכים בסדר עולה).
תחביר פונקציית Match:
Match(lookup_value, lookup_array, [match_type])
lookup_value - הערך אותו נחפש בטווח
lookup_array - הטווח בו אנו מבצעים את החיפוש
[match_type] - סוג החיפוש: 1-,0 או 1
» לקריאה נוספת על פונקציית Match בדך התמיכה של Microsoft
דוגמה מס' 1 (התאמה מדוייקת)
לפננו הערכים (20,25,40,60,95) - בגיליון אקסל הנתונים מוקלדים לפי אחת מהאפשרויות בתמונה: (שימו לב שכיוון הגיליון חשוב, ראו הערה מס' 1) :
תמונה מס' 1:
הערך המחופש בהתאמה מדוייקת הוא 25, התוצאה תהיה 2: מפני שזהו מיקמו של הערך 25 בטווח.
הערך המחופש בהתאמה מדוייקת הוא 95, התוצאה תהיה 5: מפני שזהו מיקמו של הערך 95 בטווח.
** הערה מס' 1: יש לשים לב לכך שבגיליון האקסל המיקום של הערכים תלוי כמובן בכיוון הגיליון, אם כיוון הגיליון הוא ימין לשמאל המיקום יקבע מימין לשמאל ובמקרה זה התוצאה עבור הערך 25 תהיה 4.
במידה וכיוון הגילוין הוא שמאל לימין המיקום יקבע משמאל לימין כמו בדוגמה זו.
lookup_value: 95
array: (20,25,40,60,95)
match_type: 0
Formula: Match(95,{20,25,40,60,95},0) = 5
דוגמה מס' 2 (התאמה מדוייקת)
לפננו רשימת ערכים (Laptop, PC, Speakers, Headphones), נניח שרשימת הערכים מוקלדת בגליון משמאל לימין (או בעמודה בסדר יורד) לכן "Laptop" הוא הערך הראשון ברשימה ו "Headphones" הערך הרביעי.
נניח ואנו מבצעים חיפוש עבור הערך "Speakers", התוצאה תהיה 3: מפני שזהו מיקמו של הערך "Speaker" בטווח.
lookup_value:"Speakers"
array: (Laptop, PC, Speakers, Headphones)
match_type: 0
Formula: Match("Speakers",{Laptop, PC, Speakers, Headphones},0) = 3
נניח ואנו מבצעים חיפוש עבור הערך "Speaker", התוצאה תהיה NA: מפני שערך זה אינו קיים בטווח שלנו בהתאמה מדוייקת, Speaker ≠ Speakers.
דוגמה מס' 3 (חיפוש בטווח 1- , התאמה לפי הערך הגבוה הקרוב ביותר)
** בחיפוש אינדקס 1- יש למיין את הערכים בסדר יורד.
לפננו הערכים (20,25,40,60,95)
הערך המחופש (לפי אינדקס חיפוש 1-) הוא 37, התוצאה תהיה NA: מפני שכאשר אנו מבצעים חיפוש לפי אינדקס 1- הרשימה צריכה להיות ממויינת מהגדול לקטן (ראו תמונה מס' 1)
לכן הרשימה במיקרה זה צריכה להיות (95,60,40,25,20) ** כל זה כמובן בהנחה שכיוון הגיליון הוא שמאל לימין (ראו תמונה מס' 2).
תמונה מס' 2
עכשיו כשהערך המחופש (לפי אינדקס חיפוש 1-) הוא 37, התוצאה תהיה 3: מפני שהערך הגבוה הקורב ביותר ל-37 הוא 40 ומיקומו בטווח הוא 3
lookup_value: 37
array: (95,60,40,25,20)
match_type: 0
Formula: Match(37,{95,60,40,25,20},0) = 3
דוגמה מס' 4 (חיפוש בטווח 1 , התאמה לפי הערך הנמוך הקרוב ביותר)
** בחיפוש אינדקס 1 יש למיין את הערכים בסדר עולה.
לפננו הערכים (20,25,40,60,95)
הערך המחופש (לפי אינדקס חיפוש 1-) הוא 37, התוצאה תהיה 2: מפני שהערך הנמוך הקורב ביותר ל-37 הוא 25 ומיקומו בטווח הוא 2.
lookup_value: 37
array:(20,25,40,60,95)
match_type: 0
Formula: Match(95,{20,25,40,60,95},0) = 2
פתרונות תרגילים לדוגמה מתוך קורס אקסל מתקדם, קורס אקסל למתקדמים ומומחים:
סרטון מס' 1 פתרון לדוגמה תרגיל מקובץ Vlookup and Match | אפשרות מס' 1 הפנייה לטווח רגיל
סרטון מס' 2 פתרון לדוגמה תרגיל מקובץ Vlookup and Match | אפשרות מס' 2 הפנייה לשם
סרטון מס' 3 פתרון לדוגמה תרגיל מקובץ Vlookup and Match | אפשרות מס' 3 הפנייה לטבלה חכמה