א' שואל:
אנו עובדים כמה אנשים ביחד על אקסל drive יש אלו שמכניסים נתונים ויש מי שצריך לטפל בדברים,
יש לי רעיון - כל מי שמכניס נתון חדש שצריך טיפול יצבע את התא באדום,
ואז המטפל ידע שיש נתון חדש לטיפול אחרי הטיפול המטפל יבצע את התא בשחור ואז אדע שמטופל.
איך אני יכול לעשות נוסחה שתראה לי בראש השורה אם יש אדום באמצע השורה,
ובנוסף בתחילת השורות יוצג סיכום - כמה שורות יש בהם אדום (דהיינו כמה טיפולים יש עדיין)
תודה רבה
תשובה:
ניתן לבצע זאת על ידי הגדרת נוסחה מותאמת ב-VBA ושימוש בה בגיליון הנתונים של אקסל.
יש לשמור את קובץ האקסל עם סיומת XLSM על מנת שקוד ה-VBA יישמר (ניתן לביצוע בתפריט קובץ -> שמור בשם ובחירת סוג הקובץ XLSM).
שלב 1 - הגדרת פונקציות ב-VBA
יש להגדיר במודול חדש בעורך VBA את הפונקציה CountCellsByFillColor שמקבלת כקלט טווח תאים שבו יש לספור את מספר התאים הצבועים ובנוסף תא אחד שצבוע בצבע אותו יש לספור.
הפונקציה עוברת על התאים בטווח הנבחר ומחזירה את מספר התאים הצבועים בצבע שהוגדר.
בנוסף יש להגדיר את הפונקציה CalculateWorkbook שתשמש לעדכון החישובים ע"י לחיצה על לחצן בגיליון הנתונים.
Public Function CountCellsByFillColor(rngData As Range, rngColor As Range) As Long
Dim lngCounter As Long, lngCheckColor As Long, cell As Range
Application.Volatile
lngCounter = 0
lngCheckColor = rngColor.Interior.Color
For Each cell In rngData
If cell.Interior.Color = lngCheckColor Then
lngCounter = lngCounter + 1
End If
Next cell
CountCellsByFillColor = lngCounter
End Function
Public Sub CalculateWorkbook()
Application.Calculate
End Sub
שלב 2 - הגדרת טבלת הנתונים
נגדיר לדוגמה את הטבלה הבאה בגיליון אקסל.
הטבלה מפרטת את שלבי הביצוע של פרויקטים.
בעמודת "משימות לביצוע" יוצג דגל "X" במקרים בהם יש תא אדום צבוע בשורה.
את תא A1 נצבע באדום ולידו נוסיף צורת מלבן עם הכיתוב "עדכן".
תא A1 יציג את מספר השורות בהן נמצא תא צבוע באדום.
שלב 3 - הגדרת נוסחאות
בתא A4 נגדיר את הנוסחה הבאה: IF(CountCellsByFillColor(B4:G4,$A$1)>0,"X","")
הנוסחה מבצעת שימוש בפונקציה המותאמת שהגדרנו ב-VBA המונה את מספר התאים הצבועים באדום (צבע תא A1) ואם הוא גדול מאפס מחזירה את הדגל "X". אחרת לא מחזירה דבר.
נעתיק את הנוסחה מתא A4 לתאים A5 עד A16.
בתא A1 נגדיר את הנוסחה: COUNTIF(A3:A16,"X") שעושה שימוש בפונקציה COUNTIF ומונה את מספר השורות בהן מופיע תא בצבע אדום (ע"ב הדגל "X" שמחושב בתחילת כל שורה).
שלב 4 - קישור קוד מקרו ללחצן
נסמן את לחצן "עדכן", נלחץ לחיצה ימנית בעכבר ואז "הקצה מקרו" ונבחר במקרו שהגדרנו CalculateWorkbook.
שלב 5 - הזנת נתונים ובדיקה
נסמן מספר תאים באדום ונלחץ על לחצן "עדכן".
בשורות עם תאים אדומים יופיע הדגל "X" וראש השורות יופיע מספר השורות בהן יש תאים אדומים.