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

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

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

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

                        תמונה מס. 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

מיון ללא מיון ב"אקסל" - פרק ב'

מיון ללא מיון ב"אקסל" - פרק ב'

כזכור לכם, בפרק הקודם הדגמנו כיצד ניתן למַיֵּן תחום מספרים ב"אקסל"

כפי שאפשר לראות בתמונה 1, בתאים: A2:A19 מופיעה רשימת מספרים לא ממוינת.

לאחר הקשת הנוסחא:

=LARGE($A$2:$A$19,ROW()-1)

בתא: G2 וגרירתה כלפי מטה באמצעות "ידית המלוי" - עד לתא G19, נקבל בעמודה G (תאים: G2:G19) את הנתונים הממוינים מהגדול לקטן (מיון יורד)

תמונה 1: עמודה G מציגה במיון יורד את המספרים הלא-ממוינים בעמודה A

ואם נקיש את הנוסחא:

=SMALL($A$2:$A$19,ROW()-1)

בתא: G2 ונגרר עד G19 - נקבל את הנתונים ממוינים מהקטן לגדול  (מיון עולה)

 תמונה 2: הפונקציהSMALL  בעמודה G מציגה במיון עולה את המספרים בעמודה A  

כעת, יש לי מצב מעט יותר מורכב.

יש לי רשימת סוכנים ומכירות.

לכל סוכן, סכום המכירות שלו.

תמונה 3: טבלת סוכנים ומכירות

אם נרצה למַיֵּן את הטבלה לפי סכומי המכירות (מהגדול לקטן- כדי לדעת מי הסוכן שמכר הכי הרבה) - כתוצאה מהמיון, סכומי המכירות ישנו מקום בעמודה הממוינת. אם למשל, הסכום הגבוה ביותר (1000.00) היה בתא הרביעי של הטבלה (B5 - מכיון שהנתונים מתחילים בתא B2)- לאחר המיון, הוא יהיה התא הראשון בטבלה החדשה.

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

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

הפתרון: בשלשה שלבים

שלב א: נגדיר את תחום המספרים (B2:B19)  בשם: Sales. נסמן את התחום וב- Name Box (תיבת השם) נכתֹב: Sales. כך יקבל התחום את השם: Sales.

השִמּוּש בשם יהפך את כל הנוסחאות לפשוטות יותר ולקריאות יותר...

תמונה 4: הגדרת השם Sales  לתחום הסכומים: B2:B19

שלב ב:

שלב זה זהה לפתרון בפרק א: אנו ממַיְנִים את המספרים בעמודה חדשה (נניח, עמודה I)

נקליד (בתא: I2)  את הנוסחא

=LARGE(Sales, ROW()-1)

 ונגרֹר כלפי מטה:

והתוצאה:

תמונה 5: שלב ב - מיון סכומי המכירות (B2:B19) בעמודה I (I2:I19)

שלב ג:

נקליד את הנוסחא הבאה בתא H2:

=INDIRECT("A"&1+MATCH($I,Sales,0))

ונגרר כלפי מטה (עד H19):

et voilà!

תמונה 6: עמודות H,I  מציגות את הטבלה הממוינת לפי סכום מכירות לסוכן (בסדר יורד)

אנו רואים שבאמת בטבלה החדשה (H2:I19) נשמרו הקשרים המקוריים בין הסוכן ובין סכום מכירה שלו.

למשל: הסוכן James (A5, H2) מכר ב- 1000.00 (B5, I2), הסוכן Ben(A12, H7) מכר ב- 540.00 (B12, I7) וכו'.

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

=INDIRECT("A"&1+MATCH($I,Sales,0))

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

1)  MATCH

2) INDIRECT

1) הפונקציה: MATCH

=INDIRECT("A"&1+ MATCH($I2,Sales,0))

א. כללי:

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

ב. ארגומנטים:

1. מה לחפש (lookup_value)- אנו מחפשים את הערך ב: $I2 (כלומר, את הערך: 1000.00)

2. היכן לחפש (lookup_array)- המערך/טבלה שבה אמור ערך זה להִמָּצֵא (בדוגמא שלנו: Sales)

3. כיצד לחפש (match_type) - 3 אפשרויות לחפוש

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

ב. 1 - יש למצוא את הערך הגבוה ביותר שאיננו גדול מערך החפוש (כלומר, קטן או שווה לערך החפוש)

ג. 1- - יש למצוא הערך הקטן ביותר שאינו קטן מערך החפוש  (כלומר, גדול או שווה לערך החפוש)

ג. תוצאה: בדוגמא שלנו, כאשר הפרמטרים הם:

            1. ערך החפוש = $I2 (העמודה - מקובעת והשורה - יחסית)

            2. הטבלה לחפוש = Sales

            3. כיצד לחפש = 0 [מצא ערך זהה בדיוק לערך החפוש]

התוצאה היא: 4 - הערך 1000.00 נמצא בשורה הרביעית של הטבלה (כלומר, ב-B5)

2) הפונקציה: INDIRECT

א. כללי:

הפונקציה INDIRECT  מחזירה את הערךשנמצא בכתובת תא.

למשל, אם  תא A1  ערכו: 7, אזי הפונקציה:

=INDIRECT(“A1”)

תחזיר את הערך 7

ב. ארגומנטים:

1. כתובת התא (ref_text)- בדוגמא שלנו, כתובת התא היא תוצאה של שרשור המחרוזת

“A”&1עם תוצאת נוסחת ה- MATCH:

“A”&1+MATCH($I2,Sales,0) -> “A”&1+4 -> “A5”

כלומר, תוצאת הבטוי כולו תהיה “A5”

2. שיטת הכתובות בגליון [a1] - האם שיטת הכתובות היא A1 (השיטה הרגילה) או שיטת R1C1. אנו משתמשים בשיטת A1  ולכן פרמטר זה מיותר.

ג. תוצאה: בדוגמא שלנו, כאשר הפרמטרים הם:

            1. כתובת התא =A5

            2. שיטת הכתובות = A1

התוצאה היא: הערך שנמצא בתא A5,

=INDIRECT(“A”&1+4) -> =INDIRECT(“A5”) -> James

                                                ╬╬╬╬╬╬╬╬╬╬╬╬╬╬╬╬╬╬