LET לחישוב שעות עבודה

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

מה יש לנו

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

קיבלנו נוסחה ארוכה, קשה להבנה, ובעיקר –  קשה לתיקון:

=(MIN(C7-B7,$N$6)+MIN((C7-B7)-MIN(C7-B7,$N$6),"2:00")*1.25+(C7-B7-MIN(C7-B7,$N$6)-MIN((C7-B7)-MIN(C2-B2,$N$6),"2:00"))*1.5)*24*$N$7

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

ואיך עושים את זה?

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

• מספר השעות היומיות, שבקובץ המצורף נמצא בתא N6, קיבל את השם Hrs, כדי לציין שמדובר בשעות
• התעריף לשעה, שנמצא בתא N7, קיבל את השם Rate
• פעולת חיסור שעת הכניסה משעת היציאה, שמוגדרת על ידי הנוסחה C7-B7, קיבלה את השם Mn (כקיצור של Min)
• וחישוב המינימום בין מספר השעות היומיות, למספר שעות התקן היומיות, שמיוצג על ידי חישוב נוסחת MIN בין מערך שהתקבל מחיסור שעת הכניסה והיציאה, למספר שעות התקן, שקיבל את הקיצור MinH (כקיצור של מינימום השעות עבור כל תעריף של שעות נוספות)

ועכשיו, למלאכת ההגדרה

בשלב הראשון, נגדיר את המשתנים, ונפנה אל התאים הרלוונטיים:

=LET(Hrs,$N$6,Rate,$N$7,Mn,C7-B7,MnH,MIN(Mn,Hrs)

כלומר:

• הצהרנו על המשתנה Hrs והצמדנו אליו את תא N6. חשוב לקבע אותו, משום שזוהי הפניה קבועה
• הצהרנו על המשתנה Rate, והצמדנו אליו את תא N7
• הצהרנו על המשתנה Mn שאמור לייצג את המינימום בין שעת הכניסה לשעת היציאה,
  ולכן הצמדנו אליו את הנוסחה C7-B7, שזוהי שורת החישוב הראשונה שלנו.
  מכיוון שנרצה לגרור את הנוסחה, חשוב לא לקבע את ההפניות;
• ולסיום הצהרנו על MinH, שבו חישבנו את המינימום שהתקבל מהמשתנה Mn, ושעות התקן שמיוצגות על ידי המשתנה Hrs
  שימו לב שהשתמשתי במשתנים שהגדרתי קודם, בתוך משתנים אחרים!

המשך – כתיבת הפונקציה

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

=LET(Hrs,$N$6,Rate,$N$7,Mn,C7-B7,MnH,MIN(Mn,Hrs),(MnH+MIN((Mn)-MnH,"2:00")*1.25+(Mn-MnH-MIN((Mn)-MnH,"2:00"))*1.5)*24*Rate)

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

רגע, אבל הנוסחה הזאת לא יותר קצרה מהקודמת

נכון ולא נכון…
הנוסחה ארוכה יותר, כי הגדרנו משתנים בתחילתה, אבל החלק של החישוב קצר יותר, ואפילו מובן יותר, מכיוון שנתנו שמות משמעותיים.
כאשר כתוב בנוסחה $N$6, אנחנו צריכים לחזור לגיליון, ולבדוק באיזה תא מדובר, אבל כשאנחנו רואים את המשתנה Hrs, ברור לנו שמדובר במספר השעות (ואם זה לא ברור לכם, תנו שמות שיהיו משמעותיים עבורכם)
ויתרון נוסף – אם נזיז את מיקום תאי העזר, או נוסחאות החישוב, לא נצטרך לשנות אותם בכל מופע שלהם בנוסחה שכתבנו, אלא פעם אחת בלבד, בחלק הראשון של הגדרת המשתנים!
להורדת הקובץ המצורף, לחצו כאן

,