LAMBDA לחישוב שכר יומי

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

ועכשיו נעבור על השלבים לביצוע המשימה:

חישוב השעות הנוספות

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

אילו עמודות אנחנו צריכים?

כל החישובים מתבססים על מניפולציות שמובססות על 4 תאים בלבד:

• שעת ההתחלה
• שעת הסיום
• השעות היומיות
• התעריף לשעה.

שלב ראשון – החלפת כל הנוסחאות לכאלה שמכילות רק את עמודות הבסיס

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

שעות רגילות

הנוסחה שבתא

=MIN(C2-B2,$N$1)

כוללת רק את התאים שמשתתפים בחישוב (שעת התחלה, שעת הסיום והשעות היומיות), ולכן אין מה להחליף כאן

שעות נוספות 125%

הנוסחה שבתא:

=MIN((C2-B2)-E2,"2:00")

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

=MIN((C2-B2)-MIN(C2-B2,$N$1),"2:00")

שעות נוספות 150%

גם כאן, נחליף את כל ההפניות לנוסחאות שכתובות בתאים הרלוונטים

=D2–E2–F2

ולאחר ההחלפה נקבל את הנוסחה הבאה:

=C2-B2-MIN(C2-B2,$N$1)–MIN((C2-B2)–MIN(C2-B2,$N$1),"2:00")

שלב שני – חישוב השכר

עמודות חישוב השכר הן H:J.
עבור כל אחת מהן נכפיל בהתאמה את מספר השעות שקיבלנו בעמודות החישוב E:G ב-24 (כדי לקבל את מספר השעות בפורמט עשרוני)
ולאחר מכן נכפיל באחוזים הרלוונטיים.

שכר השעות הרגילות:

מספר השעות
כפול 24 (כדי להפוך למספר עשרוני)
כפול השכר לשעה

=MIN(C2-B2,$N$1)*24*$N$2

שכר השעות הנוספות (125%)

מספר השעות
כפול 24
כפול השכר לשעה
כפול 125%

=MIN((C2-B2)-MIN(C2-B2,$N$1),"2:00")*24*$N$2*1.25

שער השעות הנוספות (150%)

מספר השעות
כפול 24
כפול השכר לשעה
כפול 150%

=(C2-B2-MIN(C2-B2,$N$1)-MIN((C2-B2)-MIN(C2-B2,$N$1),"2:00"))*24*$N$2*1.5

שלב שלישי – איחוד כל התאים הרלוונטיים בעמודות השכר לנוסחה אחת

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

=H2+I2+J2

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

=MIN(C2-B2,$N$1)*24*$N$2+
MIN((C2-B2)-MIN(C2-B2,$N$1),"2:00")*24*$N$2*1.25+
(C2-B2-MIN(C2-B2,$N$1)-MIN((C2-B2)-MIN(C2-B2,$N$1),"2:00"))*24*$N$2*1.5

אפשר לפשט את הנוסחה על ידי הוצאת המכפלה ב-24 (תא Q1) ואת התעריף לשעה (Q2) אל מחוץ לסוגריים:

=(MIN(C2-B2,$N$1)+
MIN((C2-B2)-MIN(C2-B2,$N$1),"2:00")*1.25+
(C2-B2-MIN(C2-B2,$N$1)-MIN((C2-B2)-MIN(C2-B2,$N$1),"2:00"))*1.5)
*24*$N$2

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

ועכשיו – כתיבת ה LAMBDA

אנחנו יכולים לראות שיש לנו ארבעה פרמטרים:
שעת התחלה, שעת סיום, התעריף לשעה והשכר השעתי,
כלומר – אנחנו צריכים לכתוב פונקציית LAMBDA עם 4 פרמטרים:

• Start – לציון תחילת העבודה. הערך נמצא בתא B2
• End – לציון שעת הסיום. הערך נמצא בתא C2
• Hrs – לציון שעות העבודה היומיות. הערך נמצא בתא N1
• Rate – לציון התעריף לשעה. הערך נמצא בתא N2

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

וזו הנוסחה שהתקבלה:

=(MIN(End-B2,Hrs)+MIN((End-B2)-
MIN(End-B2,Hrs),"2:00")*1.25+
(End-B2-MIN(End-B2,Hrs)-MIN((End-B2)-MIN(End-B2,Hrs),"2:00"))*1.5)
*24*Rate

נעטוף אותה ב-LAMBDA:

=LAMBDA(Start,End,Hrs,Rate,
(MIN(End-Start,Hrs)+
MIN((End-Start)-MIN(End-Start,Hrs),"2:00")*1.25+
(End-Start-MIN(End-Start,Hrs)-MIN((End-Start)-MIN(End-Start,Hrs),"2:00"))*1.5)
*24*Rate)

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

=LAMBDA(Start,End,Hrs,Rate,
(MIN(End-Start,Hrs)
+MIN((End-Start)-MIN(End-Start,Hrs),"2:00")*1.25+
(End-Start-MIN(End-Start,Hrs)-MIN((End-Start)-MIN(End-Start,Hrs),"2:00"))*1.5)
*24*Rate)
(B2,C2,$O$1,$O$2)

שמירה במנהל השמות

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

ועכשיו – רגע האמת: כתיבת הפונקציה בגיליון

=Salary(B2,C2,$N$1,$N$2)

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

להורדת הקובץ לחצו כאן