כיצד לבצע חיפוש מתקדם (VLOOKUP) במספר גליונות עבודה?
הפונקציה VLOOKUP היא אחת הפונקציות הנפוצות ביותר ב-Excel לחיפוש והפקת נתונים. עם זאת, כאשר מתמודדים עם מספר גליונות עבודה, פונקציית VLOOKUP ישירה לא תספיק מכיוון שהיא עובדת רק בתוך טווח יחיד. נניח שיש לי שלושה גליונות עבודה עם טווח נתונים, ואני רוצה לקבל חלק מהערכים המתאימים על סמך קריטריונים מתוך שלושת הגליונות הללו. המדריך הזה מסביר כיצד ליישם חיפוש VLOOKUP רב-גליונות ב-Excel באמצעות שיטות שונות.
 |  |  |  |  |
חיפוש ערכים ממספר גליונות עבודה באמצעות נוסחת מערך
חיפוש ערכים ממספר גליונות עבודה באמצעות Kutools for Excel
חיפוש ערכים ממספר גליונות עבודה באמצעות נוסחה רגילה
חיפוש ערכים ממספר גליונות עבודה באמצעות נוסחת מערך
כדי להשתמש בנוסחת מערך זו, עליכם לתת לשם שלושת הגליונות טווח שם, אנא ציינו את שמות גליונות העבודה שלכם בגיליון חדש, כפי שמוצג בסcreenshot הבא:
1. תנו לגליונות העבודה שם טווח, בחרו את שמות הגליונות, וכתבו שם בתיבת השם אשר ליד שורת הנוסחאות, במקרה זה, אני אכתוב Sheetlist כשם הטווח, ולאחר מכן לחצו על מקש Enter.
2. ואז תוכלו להכניס את הנוסחה הארוכה הבאה לתא מסוים:
=VLOOKUP(A2,INDIRECT("'"&INDEX(Sheetlist,MATCH(1,--(COUNTIF(INDIRECT("'"&Sheetlist&"'!$A$2:$B$6"),A2)>0),0))&"'!$A$2:$B$6"),2,FALSE)3. ואז, לחצו יחדיו על מקשי Ctrl + Shift + Enter כדי לקבל את הערך המתאים הראשון, לאחר מכן גררו את ידית המילוי למטה לתאים שברצונכם להחיל עליהם את הנוסחה, כל הערכים הקשורים של כל שורה יוחזרו כדלקמן:
הערות:
1. בנוסחה לעיל:
- A2: הוא הייחוס לתא שברצונכם להחזיר את הערך הקשור שלו;
- Sheetlist: הוא שם הטווח של שמות גליונות העבודה שיצרתי בשלב 1;
- A2:B6: הוא טווח הנתונים של גליונות העבודה שאתם צריכים לחפש בהם;
- 2: מצביע על מספר העמודה שהערך התואם מוחזר ממנה.
2. אם הערך הספציפי שאתם מחפשים לא קיים, יוצג ערך #N/A.
חיפוש ערכים ממספר גליונות עבודה באמצעות Kutools for Excel
Kutools for Excel מספק לכם תכונה יעילה ופשוטה - "חיפוש מתקדם (LOOKUP) במספר גליונות", המסייע לכם לבצע בקלות שאילתות VLOOKUP על פני מספר גליונות עבודה. ללא צורך בנוסחאות מורכבות, תוכלו לחפש במהירות ולהוציא את הנתונים הנדרשים ממספר גליונות. בעזרת כמה קליקים בלבד, תוכלו להשלים את ההתאמה והשילוב של נתונים ממספר טבלאות, מה שמשפר באופן משמעותי את יעילות העבודה. בנוסף, Kutools תומך בשמירת סכמות שהיו בשימוש בעבר, כך שיהיה נוח להשתמש בהן ישירות במשימות עתידיות.
לאחר התקנת Kutools for Excel, אנא עשו כך:
1. לחצו על "Kutools" > "חיפוש מתקדם" > "LOOKUP Across Multiple Sheets", ראו צילום מסך:
2. בחלון הדו-שיח "LOOKUP Across Multiple Sheets", אנא בצעו את הפעולות הבאות:
- בחרו את תאי הפלט ואת תאי ערכי החיפוש מהחלק "טווח פלט" ו"ערכי חיפוש" בהתאמה;
- לאחר מכן, לחצו על כפתור "הוסף" כדי לבחור ולהוסיף את טווח הנתונים מגליונות אחרים אחד אחרי השני לתיבת הרשימה "טווח נתונים".
- לבסוף, לחצו על כפתור אישור.
תוצאה: כל הרשומות התואמות חזרו, ראו צילומי מסך:
| | | |  |
לחצו כדי להוריד את Kutools for Excel ולנסות בחינם עכשיו!
חיפוש ערכים ממספר גליונות עבודה באמצעות נוסחה רגילה
אם אינכם רוצים ליצור שם טווח ואינכם מכירים את נוסחת המערך, כאן יש גם נוסחה רגילה שתעזור לכם.
1. אנא הקלידו את הנוסחה הבאה לתוך תא שתרצו:
=IFERROR(VLOOKUP($A2,Sheet1!$A$2:$B$6,2,FALSE),IFERROR(VLOOKUP($A2,Sheet2!$A$2:$B$6,2,FALSE),VLOOKUP($A2,Sheet3!$A$2:$B$6,2,FALSE)))2. ואז גררו את ידית המילוי למטה לטווח התאים שתרצו לכלול את הנוסחה הזו, ראו צילום מסך:
הערות:
1. בנוסחה לעיל:
- A2: הוא הייחוס לתא שברצונכם להחזיר את הערך הקשור שלו;
- Sheet1, Sheet2, Sheet3: הם שמות הגליונות הכוללים את הנתונים שתרצו להשתמש בהם;
- A2:B6: הוא טווח הנתונים של גליונות העבודה שאתם צריכים לחפש בהם;
- 2: מצביע על מספר העמודה שהערך התואם מוחזר ממנה.
2. כדי להבין טוב יותר את הנוסחה, למעשה, הנוסחה הארוכה מורכבת ממספר פונקציות VLOOKUP המחוברות באמצעות הפונקציה IFERROR. אם יש לכם יותר גליונות עבודה, פשוט צריך להוסיף את פונקציית ה-VLOOKUP יחד עם IFERROR אחרי הנוסחה.
3. אם הערך הספציפי שאתם מחפשים לא קיים, יוצג ערך #N/A.
מסקנה:
המדריך הזה סיפק שלוש שיטות שונות לביצוע VLOOKUP על פני מספר גליונות עבודה, המשרתות צרכים שונים של משתמשים ורמות מיומנות:
- נוסחת מערך: שיטה חזקה עבור משתמשים מתקדמים, נוסחאות מערך מאפשרות חיפושים דינמיים וгибוליים על פני מספר גליונות ללא צורך בכלי נוסף. למרות שדורשת קצת מיומנות בנוסחאות, היא יעילה ומועילה עבור מערכות נתונים מורכבות.
- Kutools for Excel: בחירה מצוינת עבור משתמשים המחפשים פתרון ידידותי למשתמש וממוחשב. עם הכלים המובנים שלו, Kutools מפשט את תהליך החיפוש הרב-גליון, חוסך זמן ומאמץ, במיוחד עבור אלה שאינם מכירים היטב את הפונקציות המתקדמות של Excel.
- נוסחה רגילה: שימוש בנוסחאות IFERROR או IF מקוננות הוא דרך מעשית ופשוטה להשיג חיפוש רב-גליון. הגישה הזו מתאימה עבור מערכות נתונים קטנות או כאשר אתם זקוקים לפתרון מהיר ללא כלי נוספים או טכניקות מתקדמות.
בחירת השיטה הנכונה תלויה בהיכרות שלכם עם Excel, מורכבות המשימה שלכם והכלים שבידכם. אם אתם מעוניינים לחקור עוד טיפים וטריקים של Excel, האתר שלנו מציע אלפי מדריכים שיעזרו לכם לשלוט ב-Excel.
עוד מאמרים קשורים:
- חיפוש Vlookup ערך התאמה מלמטה למעלה ב-Excel
- באופן רגיל, הפונקציה Vlookup יכולה לעזור לכם למצוא את הנתונים מלמעלה למטה כדי לקבל את הערך התואם הראשון מהרשימה. אבל, לפעמים, אתם צריכים לחפש Vlookup מלמטה למעלה כדי לחלץ את הערך התואם האחרון. האם יש לכם רעיונות טובים להתמודד עם משימה זו ב-Excel?
- חיפוש Vlookup וחזרה של שורה שלמה/מלאה של ערך התאמה ב-Excel
- באופן רגיל, אתם יכולים לחפש Vlookup ולהחזיר ערך התאמה מטווח נתונים באמצעות הפונקציה Vlookup, אבל, האם ניסיתם למצוא ולהחזיר את כל השורה של הנתונים על סמך קריטריונים מסוימים כפי שמוצג בסcreenshot הבא.
- חיפוש Vlookup וחיבור מספר ערכים מתאימים ב-Excel
- כמו שאנו יודעים, הפונקציה Vlookup ב-Excel יכולה לעזור לנו לחפש ערך ולהחזיר את הנתונים המתאימים בעמודה אחרת, אך בדרך כלל, היא יכולה לקבל רק את הערך הראשון אם יש מספר נתונים מתאימים. במאמר זה, אני אדבר על איך לחפש Vlookup לחבר מספר ערכים מתאימים בתא אחד בלבד או ברשימה אנכית.
- חיפוש Vlookup על פני מספר גליונות וסיכום תוצאות ב-Excel
- נניח שיש לי ארבעה גליונות עבודה עם אותו עיצוב, ואני רוצה למצוא את 'טלוויזיה' בעמודה מוצר בכל גיליון, ולקבל את סכום כל הזמנות על פני אותם גליונות כפי שמוצג בסcreenshot הבא. כיצד יכולתי לפתור את הבעיה הזאת בצורה קלה ומהירה ב-Excel?
- חיפוש Vlookup וחזרה של ערך התאמה ברשימה מסוננת
- הפונקציה VLOOKUP יכולה לעזור לכם למצוא ולהחזיר את הערך התואם הראשון כברירת מחדל, בין אם מדובר בטווח רגיל או ברשימה מסוננת. לפעמים, אתם רוצים לחפש Vlookup ולהחזיר רק את הערך הנראה אם יש רשימה מסוננת. כיצד תוכלו להתמודד עם משימה זו ב-Excel?
כלי הפרודוקטיביות הטובים ביותר ל-Office
שדרג את כישורי ה-Excel שלך עם Kutools for Excel ותחווה יעילות שלא הכרת. Kutools for Excel מציע מעל300 פיצ'רים מתקדמים לשיפור הפרודוקטיביות ולחסוך זמן. לחץ כאן כדי לקבל את הפיצ'ר שהכי נחוץ לך...
Office Tab מביא ממשק טאב ל-Office והופך את העבודה שלך להרבה יותר קלה
- אפשר עריכה וקריאה בטאבים ב-Word, Excel, PowerPoint
- פתח וצור מסמכים מרובים בטאבים חדשים באותו חלון, במקום בחלונות חדשים.
- הגדל את הפרודוקטיביות שלך ב-50% וצמצם מאות קליקים של עכבר בכל יום!