בעולם מונע נתונים זה, יש צורך בכלים שונים על מנת לנהל נתונים. נתונים בזמן אמת הם עצומים, ואחזור פרטים בנוגע לפיסת נתונים מסוימת בהחלט יהיה משימה מעייפת, אך עם VLOOKUP ב לְהִצטַיֵן , ניתן להשיג משימה זו בשורת פקודה אחת. במאמר זה תלמד על אחד החשובים פונקציות אקסל כלומר פונקציית VLOOKUP.
לפני שנמשיך, בואו נסתכל במהירות על כל הנושאים הנדונים כאן:
- מהו VLOOKUP ב- Excel?
- איך זה עובד?
- התאמה מדוייקת
- התאמה משוערת
- משחק ראשון
- רגישות למקרה
- טעויות
- בדיקת דו כיוונית
- שימוש בתווים כלליים
- טבלאות בדיקה מרובות
מהו VLOOKUP ב- Excel?
ב- Excel, VLOOKUP הוא פונקציה מובנית המשמש לחיפוש ואיסוף נתונים ספציפיים מגיליון Excel. V מייצג אנכי וכדי להשתמש בפונקציה VLOOKUP ב- Excel, הנתונים צריכים להיות מסודרים אנכית. פונקציה זו שימושית מאוד כאשר יש לך כמות עצומה של נתונים ויהיה כמעט בלתי אפשרי לחפש ידנית כמה נתונים ספציפיים.
איך זה עובד?
פונקציית VLOOKUP לוקחת ערך כלומר ערך החיפוש ומתחילה לחפש אותו בעמודה השמאלית ביותר. כאשר נמצא המופע הראשון של ערך החיפוש, הוא מתחיל לנוע ימינה באותה שורה ומחזיר ערך מהעמודה שציינת. ניתן להשתמש בפונקציה זו להחזרת התאמות מדויקות כמו גם התאמות משוערות (התאמת ברירת המחדל היא התאמה משוערת).
תחביר:
התחביר של פונקציה זו הוא כדלקמן:
VLOOKUP (lookup_value, table_array, col_index_num, [range_lookup])
איפה,
- ערך בדיקה הוא הערך שיש לחפש בו בעמודה הראשונה בטבלה הנתונה
- אינדקס שולחן היא הטבלה שממנה יש לאחזר את הנתונים
- col_index_num הוא העמודה שממנה יש לאחזר את הערך
- טווח_תצוגה הוא ערך לוגי הקובע אם ערך החיפוש צריך להיות התאמה מושלמת או התאמה משוערת ( נָכוֹן ימצא את ההתאמה הקרובה ביותר שֶׁקֶר בודק התאמה מדויקת)
התאמה מדוייקת:
כאשר תרצה שפונקציית VLOOKUP תחפש התאמה מדויקת לערך החיפוש, יהיה עליך להגדיר את ה- טווח_תצוגה ערך ל- FALSE. התבונן בדוגמה הבאה שהיא טבלה המורכבת מפרטי עובד:
אם ברצונך לחפש את ייעודם של מי מהעובדים הללו, תוכל לעשות את הדברים הבאים:
- בחר בתא שבו ברצונך להציג את הפלט ואז הקלד סימן '='
- השתמש בפונקציה VLOOKUP וספק את ה- ערך בדיקה (כאן, זה תעודת העובד)
- לאחר מכן העבירו את הפרמטרים האחרים כלומר את מערך שולחן , col_index_num וקבע את טווח_תצוגה ערך ל- FALSE
- לכן, הפונקציה והפרמטרים שלה יהיו: = VLOOKUP (104, A1: D8, 3, FALSE)
הפונקציה VLOOKUP מתחילה לחפש את מזהה העובד 104 ואז עוברת ימינה בשורה שבה נמצא הערך. זה ממשיך עד col_index_num ומחזיר את הערך הנוכחי במיקום זה.
התאמה משוערת:
תכונה זו של פונקציית VLOOKUP מאפשרת לך לאחזר ערכים גם כשאין לך התאמה מדויקת ל- loopup_value. כפי שצוין קודם לכן, על מנת לגרום ל- VLOOKUP לחפש התאמה משוערת, יהיה עליך להגדיר טווח_תצוגה ערך ל- TRUE. התבונן בדוגמה הבאה שבה ממופים הסימנים יחד עם ציוניהם והכיתה אליה הם משתייכים.
- בדיוק כמו שעשית בהתאמה מדויקת, בצע את אותם השלבים
- במקום ערך range_lookup, השתמש ב- TRUE במקום ב- FALSE
- לכן, הפונקציה יחד עם הפרמטרים שלה תהיה: = VLOOKUP (55, A12: C15, 3, TRUE)
בטבלה שממוינת בסדר עולה, VLOOKUP מתחיל לחפש התאמה משוערת ועוצר בערך הבא הגדול ביותר שקטן מערך החיפוש שהזנת. לאחר מכן הוא עובר ימינה בשורה זו ומחזיר את הערך מהעמודה שצוינה. בדוגמה שלעיל, ערך החיפוש הוא 55 וערך החיפוש הבא בגודלו בעמודה הראשונה הוא 40. לכן הפלט הוא מחלקה שנייה.
משחק ראשון:
במקרה שיש לך טבלה שמורכבת מערכי בדיקה מרובים, VLOOKUP עוצר בהתאמה הראשונה שלו ושולף ערך מאותה שורה בעמודה שצוינה.
התבונן בתמונה למטה:
המזהה 105 חוזר על עצמו וכאשר ערך החיפוש מוגדר כ- 105, VLOOKUP החזיר את הערך מהשורה שבה הופיע ערך החיפוש הראשון.
רגישות למקרה:
פונקציית VLOOKUP אינה רגישה לאותיות רישיות. במקרה שיש לך ערך בדיקה שבמקרה הגדול והערך הקיים בטבלה קטן, VLOOKUP עדיין יביא את הערך מהשורה שבה הערך קיים. התבונן בתמונה למטה:
כפי שאתה יכול לראות, הערך שציינתי כפרמטר הוא 'RAFA' ואילו הערך הקיים בטבלה הוא 'Rafa' אך VLOOKUP עדיין החזיר את הערך שצוין. אם יש לך התאמה מדויקת אפילו למקרה, VLOOKUP עדיין יחזיר את ההתאמה הראשונה של ערך החיפוש ללא קשר למקרה בו נעשה שימוש. התבונן בתמונה למטה:
כיצד להשתמש בריחוף ב css
שגיאות:
טבעי להיתקל בשגיאות בכל פעם שאנחנו משתמשים בפונקציות. באופן דומה, אתה יכול להיתקל בשגיאות גם בשימוש בפונקציית VLOOKUP וחלק מהשגיאות הנפוצות הן:
- #שֵׁם
- # N / A
- # REF
- #ערך
שגיאת #NAME:
שגיאה זו היא בעצם להודיע לך שעשית טעות כלשהי בתחביר. כדי להימנע משגיאות תחביריות, עדיף להשתמש באשף הפונקציות שמספק Excel עבור כל פונקציה. אשף הפונקציות עוזר לך במידע לגבי כל פרמטר וסוג הערכים שאתה צריך להזין. התבונן בתמונה למטה:
כפי שאתה יכול לראות, אשף הפונקציות מודיע לך להזין כל סוג של ערך במקום הפרמטר lookup_value וגם נותן תיאור קצר של אותו. באופן דומה, כשתבחר את הפרמטרים האחרים, תראה גם מידע בנוגע אליהם.
# N / A שגיאה:
שגיאה זו מוחזרת במקרה שלא תמצא התאמה לערך החיפוש הנתון. לדוגמא, אם אכניס 'AFA' במקום 'RAFA', אקבל שגיאה # N / A.
על מנת להגדיר הודעת שגיאה כלשהי עבור שתי השגיאות הנ'ל, באפשרותך להשתמש בפונקציה IFNA. לדוגמה:
שגיאה #REF:
שגיאה זו נתקלת כאשר אתה נותן התייחסות לעמודה שאינה זמינה בטבלה.
שגיאת #VALUE:
שגיאה זו נתקלת בעת הצבת ערכים שגויים לפרמטרים או פספוס של פרמטרים חובה.
בדיקה דו כיוונית:
חיפוש דו-כיווני מתייחס לאחזור ערך מטבלה דו-ממדית מכל תא בטבלה המפנית. על מנת לבצע בדיקה דו כיוונית באמצעות VLOOKUP, יהיה עליך להשתמש בפונקציה MATCH יחד איתה.
התחביר של MATCH הוא כדלקמן:
MATCH (lookup_value, lookup_array, match_type)
- ערך בדיקה הוא הערך שיש לחפש
- מערך חיפוש הוא טווח התאים המורכב מערכי החיפוש
- התאמת_סוג יכול להיות מספר כלומר 0, 1 או -1 המייצגים התאמה מדויקת, פחות או יותר מאשר בהתאמה
במקום להשתמש בערכים מקודדים עם VLOOKUP, אתה יכול להפוך אותו לעקוף דינמי בהפניות לתאים. שקול את הדוגמה הבאה:
כפי שניתן לראות בתמונה לעיל, פונקציית VLOOKUP מכניסה את הפניה לתא כ- F6 עבור ערך החיפוש וערך אינדקס העמודות נקבע על ידי פונקציית MATCH. כאשר אתה מבצע שינויים באחד מהערכים הללו, גם הפלט ישתנה בהתאם. התבונן בתמונה למטה בה שיניתי את הערך הנוכחי ב- F6 מכריס לליאו והפלט עודכן בהתאם:
במקרה שאשנה את הערך של G5, או גם F6 וגם G5, נוסחה זו תפעל בהתאם ולהציג את התוצאות המתאימות.
אתה יכול גם ליצור רשימות נפתחות כדי להפוך את משימת שינוי הערכים למאוד שימושית. בדוגמה שלעיל, יש לעשות זאת ל- F6 ו- G5. כך תוכל ליצור רשימות נפתחות:
כיצד לסרוק חרצית בג'אווה - -
- בחר נתונים מתוך כרטיסיית הסרט
- מהקבוצה כלי נתונים בחר בחר אימות נתונים
- פתח את חלונית ההגדרות ומתוך אפשר בחר רשימה
- ציין את מערך רשימת המקורות
כך זה נראה לאחר שיצרת רשימה נפתחת:
שימוש בתווים כלליים:
אם אינך יודע את ערך החיפוש המדויק אלא רק חלק ממנו, אתה יכול להשתמש בכרטיסי בר. ב- Excel, סמל '*' מייצג תו כללי. סמל זה מודיע ל- Excel כי יש לחפש את הרצף שמגיע לפני, אחריו או בין לבין, ויכול להיות מספר תווים כלשהו לפניהם או אחריהם. לדוגמא, בטבלה שיצרתי, אם הזן 'ארג' יחד עם קלפי בר משני הצדדים, VLOOKUP יחזיר את הפלט עבור 'סרג'יו' כמוצג להלן:
טבלאות בדיקה מרובות:
במקרה שיש לך מספר טבלאות בדיקה, אתה יכול להשתמש בפונקציה IF יחד איתה על מנת לבדוק את אחת הטבלאות בהתבסס על מצב מסוים. לדוגמה, אם יש טבלה המכילה נתונים של שני סופרמרקטים ואתה צריך לברר את הרווח שהפיק כל אחד מהם על סמך המכירות, אתה יכול לעשות את הדברים הבאים:
צור את הטבלה הראשית באופן הבא:
לאחר מכן צור את שתי הטבלאות שמהן יש להשיג את הרווח.
לאחר שתסיים זאת, צור טווח בשם עבור כל אחד מהטבלאות שנוצרו לאחרונה. כדי ליצור טווח בשם, בצע את השלבים הבאים:
- בחר בטבלה את כל הטבלה שאליה ברצונך להקצות שם
- בכרטיסיית הכלים, בחר נוסחאות ואז מקבוצת שמות מוגדרים בחר הגדר שם
- תופיע תיבת הדו-שיח הבאה
- תן כל שם לבחירתך
- לחץ על אישור
ברגע שזה נעשה עבור שתי הטבלאות, אתה יכול להשתמש בטווחים אלה בשם בפונקציה IF כדלקמן:
כפי שאתה יכול לראות, VLOOKUP החזיר את הערכים המתאימים למילוי העמודה רווח לפיה לאיזה סופרמרקט הם משתייכים. במקום לכתוב את הנוסחה בכל תא בעמודה רווח, יש לי רק העתיק את הנוסחה על מנת לחסוך זמן ואנרגיה.
זה מביא אותנו לסוף מאמר זה בנושא VLOOKUP ב- Excel. אני מקווה שברור לך עם כל מה ששותף איתך. דאג שתתאמן ככל האפשר ותחזיר את החוויה שלך.
יש לך שאלה עבורנו? אנא הזכיר זאת בקטע ההערות בבלוג 'VLOOKUP ב- Excel' ונחזור אליך בהקדם האפשרי.
כדי לקבל ידע מעמיק על כל טכנולוגיות מגמות יחד עם היישומים השונים שלה, אתה יכול להירשם לשידור חי עם תמיכה 24/7 וגישה לכל החיים.