שימוש בפונקציה VLOOKUP כדי להחליף ערכים

מי עצלן מדי או שאין לו זמן לקרוא - צפה בסרטון. פרטים וניואנסים נמצאים בטקסט למטה.

ניסוח הבעיה

אז יש לנו שני שולחנות - שולחן להזמין и רשימת מחירים:

המשימה היא להחליף מחירים מהמחירון לטבלת ההזמנות באופן אוטומטי תוך התמקדות בשם המוצר כדי שבהמשך תוכלו לחשב את העלות.

פתרון

בערכת הפונקציות של Excel, תחת הקטגוריה הפניות ומערכים (חיפוש והתייחסות) יש פונקציה VPR (VLOOKUP).פונקציה זו מחפשת ערך נתון (בדוגמה שלנו, זו המילה "תפוחים") בעמודה השמאלית ביותר של הטבלה שצוינה (מחירון) הנעה מלמעלה למטה, ולאחר שמצאה אותו, מציגה את התוכן של התא הסמוך (23 רובל) .סכמטית, ניתן לייצג את פעולת הפונקציה הזו אז:

כדי להקל על שימוש נוסף בפונקציה, עשה דבר אחד בבת אחת - תן לטווח התאים במחירון שמך משלך. כדי לעשות זאת, בחר את כל התאים של המחירון מלבד ה"כותרת" (G3: H19), בחר מהתפריט הוסף - שם - הקצה (הוסף - שם - הגדר) או לחץ על CTRL + F3 והזן כל שם (ללא רווחים) כמו מחיר… כעת, בעתיד, תוכל להשתמש בשם זה כדי לקשר למחירון.

כעת אנו משתמשים בפונקציה VPR... בחר את התא שבו הוא יוזן (D3) ופתח את הכרטיסייה נוסחאות - הכנסת פונקציות (נוסחאות - הכנס פונקציה)… בקטגוריה הפניות ומערכים (חיפוש והפניה) למצוא את הפונקציה VPR (VLOOKUP) ולחץ OK... יופיע חלון להזנת ארגומנטים עבור הפונקציה:

שימוש בפונקציה VLOOKUP כדי להחליף ערכים

אנו ממלאים אותם בתורם:

  • ערך רצוי (ערך חיפוש) – שם המוצר שהפונקציה צריכה למצוא בעמודה השמאלית ביותר של המחירון. במקרה שלנו, המילה "תפוחים" מתא B3.
  • טבלתי (מערך טבלאות) – טבלה שממנה נלקחו הערכים הרצויים uXNUMXbuXNUMXbare, כלומר המחירון שלנו. לעיון, אנו משתמשים בשם שלנו "מחיר" שניתן קודם לכן. אם לא נתת שם, אתה יכול פשוט לבחור את הטבלה, אבל אל תשכח ללחוץ על הכפתור F4להצמיד את הקישור עם סימני דולר, כי אחרת, הוא יחליק מטה בעת העתקת הנוסחה שלנו למטה לשאר התאים בעמודה D3:D30.
  • מספר_עמודה (מספר אינדקס עמודה) – מספר סידורי (לא אות!) של העמודה במחירון ממנה ניקח ערכי מחירים. העמודה הראשונה של המחירון עם השמות ממוספרת 1, לכן אנחנו צריכים את המחיר מהעמודה שמספרה 2.
  • interval_lookup (חיפוש טווח) - ניתן להזין רק שני ערכים בשדה זה: FALSE או TRUE:
      • אם הוזן ערך 0 or שֶׁקֶר (שֶׁקֶר), אז למעשה זה אומר שמותר רק חיפוש התאמה מדויקת, כלומר אם הפונקציה לא מוצאת את הפריט הלא סטנדרטי שצוין בטבלת ההזמנות במחירון (אם הוזן "קוקוס", למשל), היא תיצור את השגיאה #N/A (ללא נתונים).
      • אם הוזן ערך 1 or נכון (נָכוֹן), אז זה אומר שאתה מאפשר את החיפוש לא עבור המדויק, אבל התאמה משוערת, כלומר במקרה של "קוקוס", הפונקציה תנסה למצוא מוצר עם שם קרוב ככל האפשר ל"קוקוס" ולהחזיר את המחיר לשם זה. ברוב המקרים, החלפה משוערת כזו יכולה להשפיע על המשתמש על ידי החלפת הערך של המוצר הלא נכון שהיה שם בפועל! אז עבור רוב הבעיות העסקיות האמיתיות, חיפוש משוער עדיף לא לאפשר. היוצא מן הכלל הוא כאשר אנו מחפשים מספרים ולא טקסט - למשל, בעת חישוב הנחות צעד.

הכל! נשאר ללחוץ OK והעתק את הפונקציה שהוזנה לכל העמודה.

# N / A שגיאות ודיכוי שלהן

פונקציה VPR (VLOOKUP) מחזיר שגיאה #N/A (#N/A) אם:

  • חיפוש מדויק מופעל (טיעון תצוגת מרווחים = 0) והשם הרצוי אינו מופיע טבלתי.
  • חיפוש גס כולל (תצוגת מרווחים = 1), אבל טבלתי, שבו מתבצע החיפוש אינו ממוין בסדר עולה של שמות.
  • הפורמט של התא שממנו מגיע הערך הנדרש של השם (לדוגמה, B3 במקרה שלנו) והפורמט של התאים של העמודה הראשונה (F3: F19) של הטבלה שונים (לדוגמה, מספרי וטקסט ). מקרה זה אופייני במיוחד כאשר משתמשים בקודים מספריים (מספרי חשבונות, מזהים, תאריכים וכו') במקום שמות טקסט. במקרה זה, אתה יכול להשתמש בפונקציות Ч и טקסט להמרת פורמטים של נתונים. זה ייראה בערך כך:

    =VLOOKUP(TEXT(B3),מחיר,0)

    אתה יכול לקרוא עוד על זה כאן.

  • הפונקציה לא יכולה למצוא את הערך הנדרש מכיוון שהקוד מכיל רווחים או תווים בלתי נראים שאינם ניתנים להדפסה (מעברי שורות וכו'). במקרה זה, אתה יכול להשתמש בפונקציות טקסט TRIM (מְטוּפָּח) и הדפס(לְנַקוֹת) כדי להסיר אותם:

    =VLOOKUP(TRIMSPACES(CLEAN(B3)),price,0)

    =VLOOKUP(TRIM(CLEAN(B3));price;0)

כדי לדכא את הודעת השגיאה # N / A (#N/A) במקרים שבהם הפונקציה לא יכולה למצוא התאמה מדויקת, אתה יכול להשתמש בפונקציה טעות (IFERROR)... אז, למשל, הבנייה הזו מיירטת כל שגיאה שנוצרת על ידי VLOOKUP ומחליפה אותן באפסים:

= IFERROR (VLOOKUP (B3, מחיר, 2, 0), 0)

= IFERROR (VLOOKUP (B3; מחיר; 2; 0); 0)

PS

אם אתה צריך לחלץ לא ערך אחד, אלא את כל הסט בבת אחת (אם יש כמה שונים), אז תצטרך לעשות שמאניזציה עם נוסחת המערך. או השתמש בתכונת XLOOKUP החדשה מ-Office 365.

 

  • גרסה משופרת של פונקציית VLOOKUP (VLOOKUP 2).
  • חישוב מהיר של הנחות צעד (טווח) באמצעות פונקציית VLOOKUP.
  • כיצד ליצור "VLOOKUP שמאלה" באמצעות הפונקציות INDEX ו-MATCH
  • כיצד להשתמש בפונקציית VLOOKUP כדי למלא את הטפסים עם נתונים מהרשימה
  • איך להוציא לא את הראשון, אלא את כל הערכים מהטבלה בבת אחת
  • פונקציות VLOOKUP2 ו- VLOOKUP3 מהתוסף PLEX

 

השאירו תגובה