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

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

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





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



פונקציית התאים

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





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

זה מתייחס לכל תא בודד בגיליון כולו. זוהי הדוגמה היחידה שבה הפונקציה תאים אינה מתייחסת לתא בודד:



Worksheets('Sheet1').Cells

זה מתייחס לתא השלישי משמאל, בשורה העליונה. תא C1:

Worksheets('Sheet1').Cells(3)

הקוד הבא מפנה לתא D15:



Worksheets('Sheet1').Cells(15,4)

אם אתה רוצה, אתה יכול גם להתייחס לתא D15 עם 'תאים (15,' D ')' --- אתה רשאי להשתמש באות העמודה.

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



פונקציית הטווח

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

הפורמט של פונקציה זו הוא טווח (תא מספר 1, תא מספר 2) . כל תא יכול להיות מסומן במספר אותיות.

בואו נסתכל על כמה דוגמאות.

כיצד לחסום את המספר שלך בעת קריאה ל- Android

כאן, פונקציית הטווח מתייחסת לתא A5:

Worksheets('Sheet1').Range('A5')

כאן, פונקציית הטווח מתייחסת לכל התאים בין A1 עד E20:

Worksheets('Sheet1').Range('A1:E20')

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


With Worksheets('Sheet1')
.Range(.Cells(1, 1), _
.Cells(20, 5))
End With

הקוד לעיל מתייחס לאותו טווח כמו הפונקציה טווח ('A1: E20'). הערך בשימוש בו הוא שהוא יאפשר לך לכתוב קוד שעובד באופן דינמי עם טווחים באמצעות לולאות.

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

עיבוד נתונים עם פונקציית תאים

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

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

אולי יש לך גיליון 1 שעוקב אחר מספר המכירות שלהם, והיקף המכירות שלהם.

עַל גיליון 2 הוא המקום שבו אתה עוקב אחר דירוג המשוב שלהם במשך 30 הימים האחרונים מלקוחות החברה שלך.

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

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

אז למה לא פשוט להפעיל את החישובים של טור הבונוס באותו סקריפט באותו זמן?

פונקציית התאים בפעולה

אם מעולם לא כתבת VBA ב- Excel לפני כן, יהיה עליך להפעיל את פריט התפריט מפתח. לשם כך, עבור אל קוֹבֶץ > אפשרויות . לחץ על התאמה אישית של סרט . לבסוף, בחר מפתח מהחלונית השמאלית, לְהוֹסִיף בחלונית הימנית וודא כי תיבת הסימון מסומנת.

עכשיו, כאשר אתה לוחץ בסדר וחזור לדף הראשי, תראה את אפשרות התפריט מפתחים.

אתה יכול להשתמש ב לְהַכנִיס בתפריט כדי להכניס לחצן פקודה, או פשוט לחץ הצג קוד כדי להתחיל לקודד.

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

Private Sub Workbook_Open()
End Sub

חלון הקוד שלך ייראה בערך כך.

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

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

החישוב הסופי משתמש באחוזים הבאים כדי להוסיף עד 100 אחוזים מציון הבונוס הכולל. הוא מבוסס על ספירת מכירות אידיאלית על 50, היקף מכירות של 50,000 $ וציון משוב של 10.

  • (ספירת מכירות/50) x 0.4
  • (נפח מכירות/50,000) x 0.5
  • (ציון משוב/10) x 0.1

גישה פשוטה זו מעניקה לעובדי מכירות בונוס משוקלל. לספירה של 50, נפח של 50,000 $ וציון של 10 --- הם מקבלים את כל הבונוס המקסימלי לחודש. עם זאת כל דבר תחת מושלם על כל גורם מקטין את הבונוס. כל דבר טוב יותר מאידיאלי מגביר את הבונוס.

עכשיו בואו נראה איך אפשר להוציא את כל ההיגיון הזה בתסריט VBA פשוט וקצר מאוד:

Private Sub Workbook_Open()
For x = 2 To 12
Worksheets('Sheet1').Cells(x, 4) = (Worksheets('Sheet1').Cells(x, 2).Value / 50) * 0.4 _
+ (Worksheets('Sheet1').Cells(x, 3).Value / 50000) * 0.5 _
+ (Worksheets('Sheet2').Cells(x, 2).Value / 10) * 0.1 _
Next x
End Sub

כך ייראה הפלט של סקריפט זה.

מה אתה יכול לעשות עם פטל פטל

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

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

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

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

עיצוב תאים עם פונקציית טווח

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

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

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

בואו נסתכל כיצד תוכלו לעשות זאת באמצעותו הצהרת IF .

Private Sub Workbook_Open()
If Worksheets('Sheet1').Cells(13, 3).Value > 400000 Then
ActiveSheet.Range('D2:D12').Interior.ColorIndex = 4
End If
End Sub

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

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

  • החלת מתאר סביב הקבוצה
  • בדוק את כתיב הטקסט בתוך טווח תאים
  • נקה, העתק או גזור תאים
  • חפש באמצעות טווח בשיטת 'מצא'
  • הרבה יותר

הקפד לקרוא את דף MSDN של מיקרוסופט כדי שאובייקט טווח יראה את כל האפשרויות.

קח את Excel לשלב הבא

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

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

לַחֲלוֹק לַחֲלוֹק צִיוּץ אימייל Canon לעומת ניקון: איזה מותג מצלמות עדיף?

קנון וניקון הם שני השמות הגדולים ביותר בתעשיית המצלמות. אבל איזה מותג מציע את המערך הטוב ביותר של מצלמות ועדשות?

קרא הבא נושאים קשורים
  • פִּריוֹן
  • תִכנוּת
  • תכנות Visual Basic
  • Microsoft Excel
על הסופר ריאן דוב(פורסמו 942 מאמרים)

לריאן תואר ראשון בהנדסת חשמל. הוא עבד 13 שנים בהנדסת אוטומציה, 5 שנים בתחום ה- IT, וכיום הוא מהנדס אפליקציות. עורך מנהל לשעבר של MakeUseOf, דיבר בכנסים לאומיים בנושא ויזואליזציית נתונים והוצג בטלוויזיה ורדיו ארצי.

עוד מאת ריאן דוב

הירשם לניוזלטר שלנו

הצטרף לניוזלטר שלנו לקבלת טיפים, סקירות, ספרים אלקטרוניים בחינם ומבצעים בלעדיים!

לחצו כאן להרשמה