למד לבצע אוטומציה של Vlookups שלך עם Excel VBA

למד לבצע אוטומציה של Vlookups שלך עם Excel VBA
קוראים כמוך עוזרים לתמוך ב-MUO. כאשר אתה מבצע רכישה באמצעות קישורים באתר שלנו, אנו עשויים להרוויח עמלת שותף. קרא עוד.

Vlookup היא פונקציה חיונית בתוך Excel והפכה לחלק חיוני בעיבוד נתונים. הוא מספק חלק מהפונקציונליות שאתה עשוי לשייך בדרך כלל למסד נתונים מלא.





אבל מה אם אתה רוצה להפוך את הפונקציה הזו לאוטומטית? כן, זה אפשרי, במיוחד אם אתה יודע איך להשתמש בשפת האוטומציה המקורית של Excel, VBA. כך תוכל להפוך את פונקציית vlookup לאוטומטית ב- Excel VBA.



איפור של סרטון היום

התחביר של Vlookup

התחביר של פונקציית vlookup הוא די פשוט, אבל יש לו ארבעה היבטים שאתה חייב להתמקד בהם, אפילו תוך כדי אוטומציה של זה ב- Excel VBA.





שתף קבצים בין Mac ו- Windows 10 
 =vlookup(lookup_value, lookup_array, column_index, exact_match/partial_match)

לארגומנטים של הפונקציה יש את המשמעות הבאה:

  • lookup_value: זהו הערך העיקרי שברצונך לחפש ממערך בדיקת המידע.
  • מערך_חיפוש: זהו נתוני המקור, שה-lookup_value ישתמש בהם כהפניה.
  • column_index: עמודה שממנה ניתן להחזיר את הערך.
  • exact_match/partial_match: השתמש ב-0 או 1 כדי לקבוע את סוג ההתאמה.

הכנת נתונים

למערך נתונים זה שני חלקים: טבלת חיפוש ונקודות נתוני היעד.



טבלת החיפוש מורכבת משלוש עמודות, כאשר הנתונים ממלאים את השדות תת-קטגוריה ושדות מוצר:

טבלת נתונים ב-MS Excel עם שורות מאוכלסות מראש

לטבלת היעד יש עמודות תואמות, ללא נתוני קטגוריות משנה או שם מוצר. שים לב שהערכים בעמודת מזהה ההזמנה שלו נמצאים גם באותה עמודה בטבלת החיפוש:



חפש ערכים בגיליון אלקטרוני של אקסל

שימוש ישירות ב-Vlookup בגיליון אלקטרוני של Excel

כדי למלא את עמודות היעד, B ו-C, אתה יכול השתמש בפונקציית vlookup של Excel .

בתא B2 , הזן את הנוסחה הבאה:



 =VLOOKUP($A2, $F:$H, 2, 0)

באופן דומה, בתא C2 , הזן את הנוסחה הזו:

 =VLOOKUP($A2, $F:$H, 3, 0)

אתה יכול לגרור את הנוסחה למטה מתא B2 עד סוף העמודה, B17. חזור על תהליך זה גם עבור עמודה C. הערכים עבור כל ערך עוקב יתעדכנו אוטומטית בשתי העמודות.

הערכים שאתה מעביר לפונקציית vlookup הם:

  • A2 : ערך הבדיקה נמצא בתא זה.
  • F3:H17 : זה מורכב מטווח החיפוש.
  • 23 : עמודות ההפניה שמהן יש להביא את הערך.
  • 0 : מציין התאמה מדויקת.

פונקציית Vlookup של Excel VBA

לפונקציית vlookup יש קווי דמיון רבים בין אם אתה משתמש בה ישירות ב- Excel או באמצעות VBA. בין אם אתה מנתח נתונים העוסק בשורות נתונים, או מנהל מלאי שעוסק במוצרים חדשים באופן קבוע, אתה יכול להפיק תועלת באמצעות vlookup.

כאשר עובדים עם טווח חיפוש דינמי, עדיף תמיד להפוך את הנוסחאות שלך לאוטומטיות, כדי להימנע מיישום נוסחאות חוזרות ונשנות בתוך Excel. על ידי אוטומציה של פונקציית Vlookup שלך ב-VBA, אתה יכול לבצע חישובים מרובי עמודות בלחיצה אחת.

1. בצע את ההגדרה הנדרשת

התחל בפתיחת עורך הקידוד (לחץ Alt + F11 או נווט אל מפתח Tab) בחוברת עבודה חדשה של Excel והוסף מודול כדי להתחיל בכתיבת הקוד שלך. בתוך עורך הקוד, הוסף את השורות הבאות בראש חלון הקידוד כדי ליצור תת שגרה:

היישום הטוב ביותר לקיחת פתקים לאנדרואיד 
  Sub vlookup_fn1()End Sub

תת-השגרה היא מיכל לקוד ה-VBA שלך והיא קריטית להפעלתו בהצלחה. חלופה היא ל צור טופס משתמש VBA כדי להפוך את ממשק המשתמש שלך לאינטראקטיבי יותר.

2. הצהר על המשתנים שלך וצור את טווחי ההתייחסות שלך

ראשית, עליך להצהיר על סוגי הנתונים המשתנים באמצעות ה עָמוּם הַצהָרָה:

  Dim i as integer, lastrow as long

לאחר מכן, צור א שורה אחרונה משתנה, כדי לאחסן את הערך של השורה האחרונה המאוכלסת בטווח הבדיקה שלך. הפונקציה lastrow משתמשת ב- end(xldown) פונקציה לחישוב הפניה לשורה הסופה בטווח שצוין.

במקרה זה, המשתנה lastrow מאחסן את ערך השורה המאוכלסת האחרון של טווח החיפוש, 17. 

  lastrow = Sheets("Sheet2").Range("F3").End(xlDown).Row

בדוגמה לעיל, טבלת היעד משתרעת מ- A2:C17 , בעוד טבלת המקור משתרעת מ- F2:H17 . נוסחת vlookup תעבור בלולאה דרך כל ערך המאוחסן בעמודה A, תחפש אותו בטבלת המקור ותדביק את הערכים התואמים בעמודות B ו-C.

עם זאת, לפני שקפיצה ללולאה, עליך לאחסן את ערכי הטווח בתוך משתנה חדש ( שלי_טווח ), כדלהלן:

  my_range = Sheets("Sheet2").Range("F3:H" & lastrow)

עליך להגדיר במפורש את הפניה לתא ההתחלתי ( F3 ) והפניית עמודת הסיום ( ח ). VBA מוסיף אוטומטית את ערך השורה כדי להשלים את מערך הבדיקה.

קוד VBA בחלון עורך VBA

3. כתוב לולאה כדי לעבור דרך כל ערך בדיקה

לפני כתיבת הלולאה, הגדר את ערך השורה ההתחלתית, 2 . הגדרת ערך התחלתי עבור הלולאה שלך היא חיונית מכיוון שאתה מתמודד עם אלמנטים דינמיים. בטבלת היעד, שורה 2 היא השורה הראשונה של הנתונים. שנה ערך זה אם הנתונים שלך מתחילים בשורה אחרת.

  i = 2

אתה יכול להשתמש ב- a עושה תוך כדי לולאה לעיבוד כל שורה, מתחילה בשורה 2 ומסתיימת בשורה 17. כמו שאר הקוד, היבט זה הוא דינמי; הלולאה תפעל עד שהתנאי יהיה שקרי. השתמש בתנאי כדי לבדוק אם יש ערך לא ריק בתא הראשון של השורה הנוכחית.

  Do While len(Sheets("Sheet2").Cells(i, 1).value) <> 0

בתוך הלולאה, אתה יכול לקרוא לפונקציה VLookup כדי לאכלס את התאים:

  Sheets("Sheet2").Cells(i, 2).Value = _ Application.WorksheetFunction.VLookup(Sheets("Sheet2") _ .Cells(i, 1).Value, my_range, 2, False)Sheets("Sheet2").Cells(i, 3).Value = _ Application.WorksheetFunction.VLookup(Sheets("Sheet2") _ .Cells(i, 1).Value, my_range, 3, False)

הצהרות אלו קובעות את ערך התאים בשורה הנוכחית, בעמודות 2 ו-3 בהתאמה. הם משתמשים ב- פונקציית גליון עבודה מתנגד להתקשר ל V Lookup פונקציה, העברת הערך המתאים מעמודה 1 לחיפוש. הם גם מעבירים את הטווח שהגדרת קודם ואת האינדקס של העמודה הרלוונטית כדי להביא ממנו את הערך הסופי.

קוד vlookup מוכן, אבל אתה עדיין צריך להגדיל את משתנה השורה בכל פעם סביב הלולאה:

  i = i + 1

בכל פעם שהלולאה פועלת, היא מעלה את הערך של אני ב-1. זכור, ערך השורה ההתחלתית הוא 2, והעלייה מתרחשת בכל פעם שהלולאה פועלת. להשתמש ב לוּלָאָה מילת מפתח כדי להגדיר את סוף גוש הקוד של הלולאה.

כאשר אתה מפעיל את הקוד כולו, הוא יאכלס את התוצאות בשתי העמודות, בהתבסס על הערכים בטבלת המקור.

פלט טבלאי בגיליון אלקטרוני של Excel

להלן כל הקוד לעיון:

  Sub vlookup_fn1() Dim i As Integer lastrow = Sheets("Sheet2").Range("F3").End(xlDown).Row my_range = Sheets("Sheet2").Range("F3:H" & lastrow) i = 2 Do While Len(Sheets("Sheet2").Cells(i, 1).Value) <> 0 On Error Resume Next Sheets("Sheet2").Cells(i, 2).Value = _ Application.WorksheetFunction.VLookup(Sheets("Sheet2") _ .Cells(i, 1).Value, my_range, 2, False) Sheets("Sheet2").Cells(i, 3).Value = _ Application.WorksheetFunction.VLookup(Sheets("Sheet2") _ .Cells(i, 1).Value, my_range, 3, False) i = i + 1 LoopEnd Sub

4. צור כפתור להפעלת הקוד

עם הקוד מחוץ לדרך, אתה יכול ליצור לחצן בחוברת העבודה של Excel כדי להפעיל אותו בלחיצה אחת. הכנס את הצורה המועדפת עליך בגיליון Excel ריק, לחץ עליה באמצעות לחצן העכבר הימני ולחץ על הקצה מאקרו אוֹפְּצִיָה.

אומר שאני מחובר אבל אין אינטרנט
לחצן מלבן בגיליון אלקטרוני של Excel עם רשימה של אפשרויות

בתוך תיבת הדו-שיח, בחר את השם של תת-השגרה שלך ולחץ על בסדר .

תיבת מאקרו פתוחה בגיליון אלקטרוני של אקסל

כאשר אתה רוצה להפעיל את הקוד שלך, לחץ על הלחצן כדי לראות כיצד הנתונים מתאכלסים באופן מיידי.

שימוש ב-Excel VBA לאוטומציה של פונקציות חיפוש

Excel VBA היא שפה גמישה, המכווננת היטב כדי להפוך את האוטומציה לקלה יותר בתוך ההיבטים השונים של MS Excel. אפשרויות רבות קיימות ב-MS Excel VBA, החל מאוטומציה של פונקציות Excel ליצירת טבלאות ציר משוכללות באופן אוטומטי.

כשאתה עובד עם מקטעי Excel שונים, אתה יכול להתנסות באפשרויות שונות כדי להפוך את חייך לקלים ויעילים יותר.