הפוקציה NETWORKDAYS בעברית

משתמשי גרסאות 97 - 2003 של אקסל שהתקינו את התוספת Analysis ToolPak, או משתמשי XL2007, יכולים להנות מהפונקציה NETWORKDAYS. איתה אפשר לחשב את ימי העבודה בין שני תאריכים, כולל התחשבות בחגים. השימוש בפונקציה פשוט: מגדירים תאריכי התחלה וסוף וטווח בגליון שכמחזיק את התאריכים המוגדרים כחגים. הפונצקיה תחשב את מספר המימים בין התאריכים, לא כולל סופי שבוע וחגים. הבעיה עם הפונקציה הזאת היא שההגדרה של "סוף שבוע" לא ניתנת לשינוי: שבת וראשון. המשתמש הישראלי ירצה בד"כ להגדיר את ששי ושבת כסוף שבוע, אבל אקסל לא מאפשר זאת.

לדוגמה, בספטמבר 2009 ישנם שני חגים, ב-20 וב-28. אם נחשב את מספר ימי עבודה עם NETWORKDAYS נקבל 21. אם נספר את ימי העבודה, כאשר ששי ושבת הם ימי מנוחה ויום ראשון הינו יום עבודה, נקבל 20.

בתא A2 הכנסנו את תאריך ההתחלה ובתא B2 סוף החודש; בטווח A5:A6 רשמנו את תאריכי החגם של החודש. בתא C2 הכנסנו את הנוסחה

=NETWORKDAYS(A2,B2,A5:A6)

אפשר לפתור את הבעיה עם נוסחת המערך (array formula) שפיתח Chip Pearson:

=IF(OR(A2<=0,B2<=0,A2>B2,ISNUMBER(A2)=FALSE,ISNUMBER(B2)=FALSE),NA(),SUM(IF(ISERROR(MATCH(WEEKDAY(ROW(INDIRECT(A2&":"&B2))),מנוחה,0)),IF(ISERROR(MATCH(ROW(INDIRECT(A2&":"&B2)),חגים,0)),1,0)),0))

על מנת להשתמש בנוסחה הזאת הגדרנו את טווח החגים תחת השם (Name) "חגים" ובנוסף הגדרנו טווח נוסף "מנוחה" הכולל את המספר של היום או ימים של השבוע שהם ימי מנוחה (במקרה שלנו ששי = 6 ושבת = 7)

פונקציות מערך יש להכניס לתא בלחיצה בוזמנית על Ctrl+Shift+Enter

הפונקציה הזאת הרבה יותר גמישה מה-NETWORKDAYS של אקסל.

פונקציות צבע באקסל

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

עכשיו צריך רק לסכם את ימי החופש, מילוים ומחלה. המשימה נראית קלה שהרי סימנתם כל יום בצבע המתאים. הבעיה היחידה היא שלאקסל אין פונקציות שמאפשרות לבצע פעולות חישוב לפי צבע! כלומר, אין פונקציות מובנות. אבל קיימת האפשרות לבנות פונקציות משתמש (UDF: user defined function). במאמר הזה נטפל בפונקציות UDF מבוססות צבעים (ומאמר הבא נראה פתרון אחר מבוסס פונקציות של שפת המקרו הקדום של אקסל, ה-XLM).

על מנת להשתמש בפונקציות UDF צריך:

1 - לכתוב את הקוד

2 - להעתיק/להדביק אותו במודול רגיל של העורך VB

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

הפונקציה הראשונה היא num_color שמחזירה מספר שמזהה את צבע התבנית של התא

Function num_color(myCell As Range)
    Select Case myCell.Interior.ColorIndex
        Case xlNone
            num_color = 0
        Case Else
            num_color = myCell.Interior.ColorIndex
    End Select
End Function
Function count_by_color(RangeColor As Range, CellColor As Range)

את הקוד הזה מעתיקים למודול רגיל של העורך של VB

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

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

Function count_by_color(RangeColor As Range, CellColor As Range)
    Dim rngCelda As Range
        
        For Each rngCelda In RangeColor
            If rngCelda.Interior.ColorIndex = CellColor.Interior.ColorIndex Then
                count_by_color = count_by_color + 1
            End If
        Next
End Function

הפונקציה האחרונה היא sum_by_color שמאפשרת לסכם מספרים בטווח מסוים לפי צבעים מקבילים בטווח אחר (או באותם תאים):

Function sum_by_color(RangeColor As Range, CellColor As Range, RangeSum As Range)
    Dim rngCelda As Range
    Dim colOffset As Long
    
    colOffset = RangeSum.Column - RangeColor.Column
    
    For Each rngCelda In RangeColor
        If rngCelda.Interior.ColorIndex = CellColor.Interior.ColorIndex Then
            sum_by_color = sum_by_color + rngCelda.Offset(0, colOffset).Value
        End If
    Next
End Function

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