אקסל לעזרת המורה - חלק א: נוסחת מערך

            אקסל לעזרת המורה - חלק א: נוסחת מערך

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

לפעמים יש צורך לנהל ב"אקסל" גם נתונים אחרים. למשל, "אקסל" עוזרת לי לנהל אירועים בכתה: חיסורים, איחורים והפרעות בשעורים.

בפרק זה אסביר כיצד אני מנהל אירועים בכתה וכיצד (בעזרת נוסחאות מערך - Array Formulae) אני יכול מיד לדעת כמה איחורים, חיסורים והפרעות יש לכל תלמיד במהלך תקופה: חודש/סמסטר/שנה וכו'.

כמו-כן, בעזרת עצוב מותנה - Conditional Formatting - אני יכול  לקבוע באופן דינמי מהו הסף (מספר האירועים המינימלי) אשר מצדיק נקיטת אמצעים כלפי התלמיד: הודעה למחנכת, מכתב להורים וכו' (ועל כך בפרק הבא: "אקסל לעזרת המורה - חלק ב: עִצוּב מותנה").

הגליון המצורף מדגים את השיטה.

ראשית, הגדרתי מהם הארועים החריגים:

1. חסור לא מוצדק (מסומן באות "ח")

2. הפרעה בשעור (מסומנת באות "ה")

3. חסור מוצדק (מסומן באותיות "חר")

4. אחור לשעור (מסומן באות "א")

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

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

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

לשם כך אני משתמש בנוסחא מסוג מיוחד הנקראת נוסחת מערך (Array Formula). בנגוד לנוסחה רגילה ב"אקסל" אשר בדרך-כלל  פועלת על תא בודד, נוסחת המערך פועלת על תחום של תאים.

למשל: נוסחת המערך בתא K4 - סוכמת את מספר המופעים של אִחוּרים ("א") בתאים: C4:J4 (ארועים עבור התלמידה: ליאור)

{=COUNT(FIND("א",C4:J4,1))}

הסבר הנוסחא:

הנוסחא מורכבת משתי פונקציות: FIND ו- COUNT

1) הפונקציה: FIND מחפשת תו (או תת-מחרוזת = substring) בתוך מחרוזת טכסט (text string).

הארגומנטים לפונקציה:

1. תו/תת-מחרוזת לחִפּוּש ["א"]

2. היכן (באיזה תא) אנו מחפשים [C4:J4]. בנוסחא רגילה, נקיש כתובת של תא בודד (למשל: C4). כאן, אנו מקישים תחום של תאים (range): C4:J4

3. באיזה תו יש להתחיל את החפוש [1]. אם ארגומנט זה מושמט, הפונקציה תחפש את התו המבוקש/תת-המחרוזת המבוקשת החל מהתו הראשון בתא. בדוגמא שלנו, יכולתי להשמיט את הארגומנט הזה (כי אני מבקש חפוש מהתו הראשון).

אנו מחפשים, אם כן, את התו "א" (שכזכור מציין "איחורים לשעור") בתחום התאים (C4:J4) החל מהתו הראשון בכל תא בתחום.

תוצאת החפוש: הפונקציה מחזירה: 1 אם התו נמצא ו #VALUE!(שגיאה בערך) אם התו לא נמצא.

2) הפונקציה: COUNT סופרת את מספר הפעמים שבהם נמצא התו המבוקש בתחום התאים. לכן, אם התו לא נמצא באחד מהתאים בסכום - הערך יהיה שונה מ- 1 ולכן לא ייספר. לכן, מוטב להשתמש בפונקציה COUNT לסכימת תוצאת החפוש בתחום התאים ולא בפונקציה SUM: כאשר הפונקציה SUMתתקל בערך: #VALUE! (שאיננו ערך נומרי) - היא תחזיר שגיאה: #VALUE!

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

FIND("א", C4, 1)

FIND("א", D4, 1)

FIND("א", E4, 1)

….

FIND("א", J4, 1)

ואז להוסיף את הפונקציה COUNT כדי לסכום את תוצאות כל החפושים הבודדים.

בודאי שאלת את עצמך: מאין נולדו הסוגריים המסולסלים { } המקיפים את הנוסחא?

ובכן, כאשר מקלידים נוסחת מערך ב"אקסל" לא מסיימים אותה בהקשת Enterכמו בנוסחא רגילה, אלא משתמשים בצרוף המקשים: Ctrl+Shift+Enter (ולכן נוסחאות כאלו נקראות גם נוסחאות CSE). כאשר "אקסל" מזהה שזוהי נוסחת מערך, הוא מוסיף לה באופן אוטומטי את הסוגריים המסולסלים - אין להוסיף את הסוגריים האלה בכתיבת הנוסחא!

לאחר שהקלדנו את הנוסחא בתא: K4 וקבלנו את התוצאה (3): התלמידה ליאור אחרה שלוש פעמים במהלך שיעורי חודש ינואר 2013. כעת נוכל להעתיק את הנוסחא כלפי מטה באמצעות ידית המלוי (fill handle) - הרבוע הקטן בפינה השמאלית התחתונה של התא (מסומן בצִלוּם המסך):

נעמיד את העכבר על ידית המלוי ונגרור כלפי מטה, עד לתא K12 (בשורת הנתונים האחרונה):

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

נוסחא דומה נקליד בתא L4 על מנת לקבל את סה"כ ההפרעות של התלמידה ליאור במהלך החודש. הפעם אנו מחפשים את האות "ה" (אשר כאמור לעיל, מציינת הפרעה בשעור).

גם כאן תהיה זו נוסחת מערך שתסכם את כל מופעי התו "ה" בתחום התאים: C4:J4. לא נשכח לסיים את הקשת הנוסחא ב: Ctrl+Shift+Enter במקום ב: Enter. "אקסל" כבר "ידאג" להקיף את הנוסחא בסוגריים מסולסלים...  

{ =COUNT(FIND("ה",C4:J4,1))}

כעת, נעתיק את הנוסחא כלפי מטה באמצעות ידית המלוי. כעת, כאשר כל התאים בעמודה הסמוכה (K) מלאים עבור כל התלמידים (K4:K15) אפשר להקליק קליק כפול (double click) על ידית המלוי והנוסח תועתק כלפי מטה עבור כל התאים. אין צורך לגרור את העכבר לכל תחום התאים.

כך יֵרָאֶה הגליון לאחר הוספת נוסחת המערך השניה עבור כל התלמידים:

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

לכן, הנוסחא שנקליד בתא M4 תהיה:

{=COUNT(FIND("ח",C4:J4,1))-COUNT(FIND("חר",C4:J4,1))}

(שוב, עלינו לזכור לסיים את הקלדת הנוסחא ב: Ctrl+Shift+Enter. הסוגרַיִם המסולסלים נוספים באֹפן אוטומטי על-ידי "אקסל").

ושוב, נגרֹר באמצעות העכבר את "ידית המִלוּי" כלפי מטה. גם כאן, אפשר לגרור את העכבר כלפי מטה או פשוט להקליק קליק כפול על ידית המלוי בתא M4.

אנו רואים, למשל, ששון היה חסר פעמיים במהלך החודש, פעם אחת חסור מוצדק (ב-13.1) ופעם אחת חסור לא מוצדק (ב-21.1). לכן, לפי הנוסחא נרשם לו רק חסור אחד.

כעת, נרצה לדעת כמה סה"כ ארועים חריגים היו לכל תלמיד ותלמיד במהלך חודש ינואר 2013.

לשם כך, נפעיל את פונקצית הסִכּוּם   (SUM) ונסכם בכל שורה את התאים המתאימים בעמודות: K - אִחוּרִים, L  - הפרעות, M - חִסוּרִים.

בתא: N4נכתֹב את הנוסחא: =SUM(K4:M4) ונעתיק כלפי מטה עד תא N15:

כעת, לפנינו כל המידע על הארועים החריגים לכל תלמיד ותלמיד במהלך החודש: כמה פעמים אֵחֵר לשעור (בעמודה K), כמה פעמים הפריע במהלך השעור (בעמודה L) וכמה פעמים החסיר שעורים (ללא סבה מוצדקת) (בעמודה M). כמו-כן, ברשותנו סה"כ הארועים החריגים לכל תלמיד (בעמודה N).

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

ppp

qqq