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

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

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

בעזרת שלוש נוסחאות המערך חִשַּבנו את שלושת סוגי הארועים עבור חודש ינואר 2013:

א. סה"כ חסורים לתלמיד (בעמודה K- תאים: K4:K15)

ב. סה"כ הפרעות לתלמיד (בעמודה L- תאים: L4:L15)

ג. סה"כ חסורים (לא מוצדקים) לתלמיד (בעמודה M- תאים: M4:M15)

כמו-כן, מצאנו את המספר הכולל של ארועים חריגים לכל תלמיד (בעמודה N - תאים: N4:N15)

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

אנו רואים בתמונה ש:

1) בעמודה K מסומנים (בכתום) כל התאים שערכם >=4

2) בעמודה L מסומנים (בצהוב) כל התאים שערכם >=2

3) בעמודה N מסומנים (באדום) כל התאים שערכם >=5

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

סה"כ אִחורים (עמודה K),סה"כ הפרעות (עמודה L) וסה"כ אֵרועים לחודש (עמודה N).

1. הגדרת עיצוב מותנה לעמודת האִחוּרים (עמודה K, תאים K4:K15)

ראשית, קבעתי מהו הסף שאותו אני רוצה להציג. הערך שבחרתי הוא: 4 והוא מוגדר בתא: P1. הנוסחא של העצוב המותנה תתנה את עצוב התאים בערך התא. אם ערכו נמוך מהסף שבחרתי (למשל: 1, 2 או 3) - התא לא יסומן. אך אם ערך התא אינו קטן מערך הסף - התא יסומן.

בעמודה K,אפשר לראות בברור כי התאים המסומנים בצבע (כתום) הם התאים שערכם הוא 4 (או יותר), הווה אומר: תאים K9 (שערכו: 4) ותא K12 (שערכו: 5).

כעת נגדיר את העצוב המותנה.

א. נסמן את תחום התאים שעליו אנו רוצים להפעיל את העִצוּב (K4:K15), נלחץ בפס הנוסחאות (ribbon) על כפתור "עיצוב מותנה" ונבחר ב"כלל חדש"

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

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

=K4>=$P$1

הנוסחא בעצם אומרת ל"אקסל" - בכל אחד מהתאים שנבחרו (K4:K15) עצב כל תא שבו מתקיים התנאי: המספר בתא >= (גדול או שווה) לערך שנקבע כפרמטר (בתא P1). יש להדגיש כי כתובת תא P1חייבת להיות כתובת מוחלטת (absolute reference), כלומר: $P$1 - אחרת, עבור כל אחד מהתאים בתחום (K4:K15) הנוסחא תתייחס לתא אחר כתא קובע הפרמטר: P2, P3, P4 וכו'.

כעת נלחץ על כפתור "עיצוב" על מנת לבחור את העיצוב המיוחד לתאים שיעמדו בתנאי הנוסחא.

ד. בחלון "עיצוב תאים" נבחר בלשונית "מילוי" ובצבע הכתום. נלחץ "אישור"

ה. נאשר את העיצוב שבחרנו

ו. כך יֵרָאֵהחלון "כלל עיצוב חדש" לאחר הקשת הנוסחא ובחירת העיצוב הרצוי (הצבע הכתום). נלחץ על "אישור".

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

נאשר בלחיצה על "אישור". 

ח. והתוצאה: בעמודה K, בתאים הרלבנטיים שנבחרו אכן מסומנים בצבע כתום התאים שערכם גדול (או שווה) לערך הפרמטר בתא: P1- כלומר:>=4.

ט. אם נשנה את ערך התא ב- P1 (ל- 3)- נראה מיד את התוצאה בעמודה K: גם תא K4(שערכו = 3) - צבוע בעיצוב הנבחר, מכיון שכעת גם תא זה עונה על תנאי הסף (P1 )

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

2. הגדרת עיצוב מותנה לעמודת ההפרעות (עמודה L, תאים L4:L15)

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

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

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

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

ד. בתא השמאלי ביותר, נקיש: =2  כי אנו רוצים "לצבוע" את כל התאים בעמודה זו שערכם גדול מ 2.

ה. ונלחץ על "עיצוב"

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

ז. בחלון "כלל עיצוב חדש" - נאשר

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

שוב נאשר בלחיצה על כפתור "אישור".

ט. כך יֵרָאה העיצוב המותנה על עמודה L.

כאן לא נוכל לשלוט באופן דינמי בערכים המוצגים (כלומר: לשנות את תנאי הסף לסימון תאים בעמודה) אלא אם נשנה את ההגדרה ב"עריכת כלל עיצוב" או שנהפך את העיצוב המותנה בעמודה זו לעיצוב מותנה דינמי (כלומר, באמצעות פרמטר - כפי שעשינו עבור עמודה K)

מה שנותר לנו הוא להגדיר עיצוב מותנה עבור העמודה השלישית - עמודה N (סה"כ ארועים לחודש).

בתא R1קבענו את הסף: 5.

כעת נגדיר את העיצוב המותנה כפי שעשינו עבור עמודה K והתוצאה הסופית:

כל התאים בעמודה N שערכם גדול או שווה מערך הסף (5) - צבועים באדום.

ואם נשנה את ערך הפרמטר (בתא R1) מ-5 ל-4: יצָבעוּ גם התאים שערכם = 4 בעמודה N, כפי שאפשר לראות בתמונה הבאה:

ttt

uuu                        

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

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

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

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

בפרק זה אסביר כיצד אני מנהל אירועים בכתה וכיצד (בעזרת נוסחאות מערך - 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

פתרון אלגנטי יותר....

ג'והן ווקנבאך הוא אחד המומחים הגדולים בעולם ל"אקסל" (לדעת רבים, הגדול מכולם).
הוא ידוע כ"מר גליון אלקטרוני" (Mr. Spreadsheet) וכתב יותר מ-50 (!) ספרי "אקסל". לדוגמא (מהאחרונים שבהם, עבור "אקסל" 2010):

Excel Dashboards and Reports

Excel 2010 Power Programming With VBA

Excel 2010 Formulas

Excel VBA Programming For Dummies

Excel 2010 Bible

חפוש שמו (John Walkenbach)  ב"גוגל" העלה למעלה מ- 700,000 תוצאות (!).

באחד מספריו האחרונים:

John Walkenbach’s Favorite Excel® 2010 Tips & Tricks

מצאתי את הטיפ הבא (מס.72) אשר מסביר כיצד להפוך מידע טבלאי (המאוחסן בעמודה אחת) לטבלה (בשורה אחת).

בעמודה A יש לנו מידע המכיל שם ופרטי כתובת (רחוב, עיר, מדינה ומיקוד). בכל "רשומה" 5 פריטי מידע ב-5 שורות: המידע על John Smith  מופיע בתאים A1:A5, המידע על Sally Jones מופיע בתאים A6:A10, המידע על Karen Richardson מופיע בתאים A11:A15 וכו'.

אנו רוצים שהנתונים המופיעים במאונך ב-5 תאים (A1:A5) - יופיעו במאוזן, בשורה אחת, ב-5 תאים: C1:G1.

הפתרון שמציע ווקנבאך בספר הוא להשתמש ב-5 נוסחאות, אחת עבור כל אחד מחמשת פרטי המידע. כדי להציג את המידע על John Smith  (תאים A1:A5) - יש להשתמש ב-5 הנוסחאות הבאות בתאים C1:G1:

C1: =INDIRECT("A" & ROW()*5-4)       ->  "Joe Smith"

D1: =INDIRECT("A" & ROW()*5-3)       ->  "505 Main St."

E1: =INDIRECT("A" & ROW()*5-2)       ->  "St. Louis"

F1: =INDIRECT("A" & ROW()*5-1)        ->  "MO"

G1: =INDIRECT("A" & ROW()*5-0)       ->  "64333"

                                                                      

                       

הסבר:

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

למשל, אם תא A1 מכיל את הערך: "C3" - אזי,

=INDIRECT(A1)

תחזיר את הערך שנמצא בתא C3 (ולא את הערך C3!)

הפונקציה ROW() - מחזירה את מספר השורה הנוכחית [הסוגריים הריקים מציינים שהפונקציה לא מקבלת שום ארגומנט]. למשל, אם הנוסחא הוקשה בתא: C1 - הפונקציה מחזירה: 1.

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

=INDIRECT("A"&[1*5-4]) => "A"&1 => "A1"

אנו משרשרים (באמצעות התו: &) את המחרוזת "A" ביחד עם המספר (1*5-4) = 1 ומקבלים את כתובת התא: A1

אנו יודעים שבתא A1 מופיע: "John Smith" ולכן, בתא C1 - שבו מופיעה הנוסחא נקבל את תוכן התא A1, כלומר: "John Smith".

באופן דומה, בתא D1מופיעה הנוסחא: 

=INDIRECT("A"&[1*5-3]) => "A"&2 => "A2"

ובתא A2מופיע המידע: "505 Main St.". לכן, בתא D1נקבל מידע זה.

עד כאן, הכל בסדר.

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

=INDIRECT("A" & COLUMN()-2+ (ROW()-1)*5)

הסבר:

התא שעליו מצביעה הפונקציה INDIRECT מזוהה בעצם על-ידי שני ארגומנטים: העמודה והשורה.

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

למשל, אם אנו נמצאים בעמודה C, הפונקציה מחזירה: 3. אם אנו נמצאים בעמודה D, הפונקציה מחזירה: 4 וכו'.

קטע הנוסחא: COLUMN()-2 מחזיר את מס. העמודה של תא המקור. אם אנו נמצאים בעמודה C (העמודה השלישית), נקבל: 1. ואם אנו נמצאים בעמודה D (העמודה הרביעית) - נקבל: 2.

השילוב: COLUMN()-2+ (ROW()-1)*5 - נותן בעצם את מס. השורה של תא המקור. למשל,

אם אנו רוצים לקבל תוכן התא: A1בתא C1- אזי מספר השורה יהיה: 1

COLUMN()-2+ (ROW()-1)*5 = (3-2) + (1-1)*5 = 1 + 0 = 1

אם אנו רוצים לקבל תוכן התא: A2בתא D1- אזי מספר השורה יהיה: 2

COLUMN()-2+ (ROW()-1)*5 = (4-2) + (1-1)*5 = 2 + 0 = 2

והשלוב של המחרוזת "A" עם תוצאה זו נותן את כתובת התא הרצוי (A1, A2…)

על פתרון זה, כתב לי Mr. Spreadsheet בכבודו ובעצמו:

AAA