פורום שאלות ותשובות

ברוכים הבאים לפורום שאלות ותשובות באופיס

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

שאלה חדשה

א' שואל:

אנו עובדים כמה אנשים ביחד על אקסל 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" וראש השורות יופיע מספר השורות בהן יש תאים אדומים.
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
בברכה,
צוות אניפיט