יום רביעי, 26 ביוני 2013

אקסל לעזרת המורה - חלק ד: ממוצע משוקלל

אקסל לעזרת המורה - חלק ד: ממוצע משוקלל

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

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



לדוגמא, נניח שהיו לי במהלך הסמסטר רק בחינה אחת ותרגיל אחד. בגלל שהבחינה חשובה יותר, נתתי לה משקל של 80% (=0.8) ולתרגיל נתתי משקל של 0.2. סך כל המשקלים (0.8+0.2) שווה ל-1.
כעת, נניח שתלמיד מסוים קבל בבחינה ציון: 80 ובעבודה ציון 100.
בשיטת הממוצע הרגיל - הציון של התלמיד יהיה: 90 (=(100+80)/2=90)
בשיטת הממוצע המשוקלל - הציון של התלמיד יהיה:
80*0.8+100*0.2=64+20=84

"אקסל" מקל עלינו מאד בחשוב הממוצע המשוקלל באמצעות הפונקציה: SUMPRODUCT

להלן דוגמא:

                        תמונה 1: טבלת ציוני התלמידים והציון המשוקלל (עמודה S)

בתמונה אנו רואים את הציונים של התלמידים בכִּתָה בתאריכים שונים (F4:R4) למשימות שונות (F5:R5)
לכל אחת מהמשימות נקבע משקל (F3:R3), כאשר סה"כ המשקלים חייב להיות שווה ל-1 (S3)

תמונה 2: הפונקציה SUMPRODUCT מחשבת את הממוצע המשוקלל

ניקח לדוגמא את ציוני התלמידה ליאור: הציון המשוקלל שלה הוא 71.50. הנוסחא בתא S6  היא:

תמונה 3: הפונקציה SUMPRODUCT מכפילה מערך אחד בשני

הפונקציה: SUMPRODUCT משמשת להכפלת מערך אחד במערך אחר (או במערכים אחרים).
המערך הראשון בנוסחא הוא מערך הציונים של ליאור  (F6:R6)
המערך השני הוא המערך (הקבוע) של משקלות הציונים ($F$3:$R$3).
הכפלת שני המערכים זה בזה נותנת את התוצאה הרצויה.
כך תראה נוסחת חשוב הממוצע המשוקלל אילולא השתמשנו בפונקציה SUMPRODUCT

תמונה 4: הדרך הקשה והארוכה לחשוב ממוצע משוקלל


את הנוסחא בתא S6 - נעתיק כלפי מטה לכל התאים בעמודה עד לתא S17, בעזרת גרירת ידית המלוי (באמצעות העכבר) .



תמונה 5: לפני העתקת הנוסחא


תמונה 6: אחרי העתקת הנוסחא



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

אני מקוה שפוסט זה יביא לכם תועלת בעבודתכם {{{.





                                            qqqq                          

יום שלישי, 25 ביוני 2013

אקסל לעזרת המורה - חלק ג: עִצּוּב מותנה דינמי

אקסל לעזרת המורה - חלק ג: עִצּוּב מותנה דינמי

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

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

                        תמונה מס. 1: טבלת הפרמטרים

בשורה הראשונה בטבלה [G2:I2], קבעתי 3 דרגות למדידת הצלחת התלמיד:
0.1  (10%), 0.4 (40%) ו-0.7 (70%)
מה פרוש הפרמטרים?
בעזרת עצוב מותנה, אני רוצה ש"אקסל"
א.    יצבע לי בירוק את כל התלמידים שציוניהם השתפרו במהלך השנה ב-10% או פחות (10%>= שפור בציון > 0%)
ב.     יצבע לי בתכלת את כל התלמידים שציוניהם השתפרו בשעור שבין 10% ובין 40% (40%>= שפור בציון > 10%)
ג.      יצבע לי באפור את כל התלמידים שציוניהם השתפרו בשעור שבין 40% ובין 70% (70%>= שפור בציון > 40%)
באופן דומה, ארצה לראות את הנסיגה בציוני התלמידים.
בשורה השניה בטבלה [G3:I3], קבעתי 3 דרגות למדידת כשלון התלמיד:
0.1 (10%), 0.3 (40%) ו-0.7 (70%)

אני רוצה ש"אקסל"
א.    יצבע לי בצהוב את כל התלמידים שציוניהם הורעו במהלך השנה ב-10% או פחות (10%>= הרעה בציון > 0%)
ב.     יצבע לי בכתום את כל התלמידים שציוניהם הורעו בשעור שבין 10% ובין 30% (30%>= הרעה בציון > 10%)
ג.      יצבע לי באדום את כל התלמידים שציוניהם הורעו בשעור שבין 40% ובין 70% (70%>= הרעה בציון > 40%)


                        תמונה מס. 2: טבלת הציונים

אפשר לראות בברור שהתלמידים: אילן, גילי, רינת, קובי ואורי - השתפרו בשעור שבין 0% ובין 10%, התלמידים: אופיר, אור, רוני וסיגלית - השתפרו בשעור שבין 10% ובין 40% והתלמידה ורד השתפרה ב-59% !!!
כמו-כן, אפשר לראות את התלמידים שחלה נסיגה בהישגיהם:
ציוני ליאל, שון וניל נסוגו בשעור שבין 0% ובין 10%, ציוניהם של: שיר, רקפת, נתנאל, עדי, דניאל, רויטל וליה - נסוגו בשעור שבין 10% ובין 30% וציוני התלמיד דין - הורעו ב- 44% (!)
היתרון הגדול של טבלת הפרמטרים היא היותה דינמית.
נניח, שאני רוצה לשנות את תחומי הקטגוריות. התוצאה תשתקף מיד בטבלת הציונים
למשל: הפרמטרים של 3 קטגוריות הגידול (G2:I2) הם כעת: 5%, 70% ו-100%.
לצורך המחשה, נשנה את הציון של ליאל בתחילת השנה. אפשר לראות שהציון של ליאל השתפר ב-73% (כלומר, בקטגוריה 3) מ-30 ל-52


            תמונה מס. 3: שנוי בפרמטרים - משפיע על עִצּוּב שורות בטבלת הציונים

ומה אם הציון שלה ישתפר ביותר מ-100% (למשל, מ-23 ל-52)?
אז שורת נתוני הציונים של ליאל לא תהיה צבועה כלל


                        תמונה מס. 4: ערכים מחוץ לתחום - לא מקבלים עִצּוּב

אך אם נרצה, נוכל להגדיר גם קטגורית גידול הגדולה מ-100%, ואז שורת הציונים של ליאל תהיה צבועה בצבע של הקטגוריה השלישית, כי הגידול בציונים שלה (126%) גדול מ- 70% (סף עליון של קטגוריה 2) וקטן מ-130% (סף עליון של קטגוריה 3)


            תמונה מס. 5: אחוזי הגידול בפרמטר יכולים להיות גדולים מ- 100%


עד כאן, הכל טוב ויפה.
אבל איך "אומרים" ל"אקסל" להפעיל את הקטגוריות, כך ששורות הנתונים תִצָבַענָה בצבעים המתאימים?

לשם כך, נשתמש בכלי מובנה ב"אקסל" הנקרא: עִצּוּב מותנה (Conditional Formatting)
נסמן את תאי טבלת התלמידים (A2:C22)  ובלשונית Home, בקבוצה Styles נבחר ב- Conditional Formatting וב- New Rule


                        תמונה מס. 6: הגדרת כלל עִצּוּב חדש - שלב א

בחלון שיפָּתַח (New Formatting Rule) נבחר באפשרות:
Use a formula to determine which cells to format
בחלון עריכת הנוסחא נקיש את הנוסחא:
=AND(($C2-$B2)/$B2>0,($C2-$B2)/$B2<=$G$2)
ונלחץ על כפתור Format כדי לבחור בעצוב הרצוי בהתקיים התנאי של הנוסחא.


                                    תמונה מס. 7: הגדרת כלל עִצּוּב חדש - שלב ב

בחלון: Format Cells - מתוך טבלת הצבעים, נבחר בצבע הירוק ונאשר ב-OK


                        תמונה מס. 8: הגדרת כלל עִצּוּב חדש - שלב ג

חזרנו לחלון: New Formatting Rule - שוב נאשר ב-OK.


תמונה מס. 9: הגדרת כלל עִצּוּב חדש - שלב ד

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


הסבר על הנוסחא:


          תמונה מס. 10: הנוסחא בכלל העִצּוּב עבור קטגוריַת גידול 1

הנוסחא בעצם דורשת קיום של שני תנאים (ביחס AND - כלומר, שניהם חייבים להתקיים כדי שהעצוב המוגדר לחוק זה ייושם על הנתונים המתאימים):
התנאי הראשון: % הגידול בציון > 0
הביטוי:  ($C2-$B2)/$B2מבטא את אחוז הגידול בציון, כלומר: ההפרש בין הציון הסופי (בתא C2) ובין הציון ההתחלתי (בתא B2) - מחולק בציון המקורי.
התנאי השני: % הגידול בציון >= $G$2  (כלומר, לא קטן מהערך שמוגדר בקטגוריה 1 של הגידול).

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


            תמונה מס. 11: שש הנוסחאות בששת כללי העִצּוּב


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

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

אני מקוה שפוסט זה יסיע לכם בנִתוח מצב תלמידי הכתה/ות שאתם מלמדים.





                                                ppppp
qqqq