יום ראשון, 9 בספטמבר 2012

עיצוב מותנה ב"אקסל" - 5 שיטות לסימון המספר הקטן ביותר




"אקסל" - עִצוּב מוּתְנֶה -
 5 שיטות לצביעת המספר הקטן ביותר ברשימה

הקדמה

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

כעת, היא רוצה לגלות מיהו התלמיד שקבל את הציון הנמוך ביותר.

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

אנו נדגים שִמּוּש בחמש(!) שיטות של עִצוּב מוּתְנֶה (Conditional Formatting) ל"צביעת" הציון הנמוך ביותר.

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

מחמש השיטות שאציג לפניכם, השיטה הראשונה קלה יחסית.
השיטות הבאות (שניה עד החמישית) - דורשות שמוש בנוסחאות ובפונקציות, מהקל אל הכבד.
כל השיטות הן, כמובן, מאד שמושיות. אפשר לישם בכל תחום (range) שבו יש רשימת מספרים, סכומים וכו' ויש צורך למצוא את המספר הקטן ביותר.
ההסבר שלי מתיַחֵס ל"אקסל" בגרסאות 2007, 2010 - אך ניתן לישום גם ב"אקסל" 2003 - בשינויים קלים.

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



שיטה א': שימוש בכללים מובנים של "אקסל" 



בתחום התאים (A3:B22) בנינו רשימת ציונים לפי תלמידים. אנו רוצים למצוא את הציון הנמוך ביותר ברשימה זו.

ראשית, נסמן את כל התאים בתחום (A3:B22), כלומר בלי שורת הכותרת.  

נבחר בתא: A3. נלחץ על מקשי: Shift+Ctrl ואז על מקש "חץ שמאלה": כדי לבחור גם את התא הסמוך משמאל: B3. כעת נלחץ על צרוף המקשים: Ctrl+Shift ועל מקש "חץ כלפי מטה":    כדי לבחור את כל השורות ברשימה כלפי מטה, עד לשורה 22.

נראה ש"אקסל" בחר בכל תחום התאים שרצינו.

כעת נשתמש בכלי הנקרא "עצוב מותנה" (Conditional Formatting).
בלשונית: "בית" בקבוצה "סגנונות", נקליק ב: "עיצוב מותנה".



נבחר ב"כללי עליונים/תחתונים" ובתוכם ב "10 פריטים תחתונים"

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

בתבת הרשימה (drop-down list) משמאל למספר שהקשנו, נבחר ב: "תבנית מותאמת אישית..."


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




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



כעת, נבחן מה יקרה אם יהיו שני תלמידים שלהם הציון הנמוך ביותר?
למשל, כאשר נשנה את הציון של מאי מ-68 ל-39, גם הציון של מאי יצָבַע באותו צבע רקע -  צהוב.



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

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


שיטה ב': הגדרת כלל עיצוב באמצעות הפונקציה MIN


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

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

הסמון מתבצע כפי שהסברתי בשיטה הקודמת:
נבחר בתא: A3. נלחץ על מקשי: Shift+Ctrl ואז על מקש "חץ שמאלה": כדי לבחור גם את התא הסמוך משמאל: B3. כעת נלחץ על צרוף המקשים: Ctrl+Shift ועל מקש "חץ כלפי מטה":    כדי לבחור את כל השורות ברשימה כלפי מטה, עד לשורה 22.
בעִצוּב מותנה, התא הפעיל (active cell) הוא התא הראשון שבו מקליקים כאשר רוצים לסמן תחום תאים (range).

כעת נבחר ב"עיצוב מותנה" ובחלונית שתפָּתַח נבחר ב: "כלל חדש"




בחלונית "כלל עיצוב חדש" שתִפָּתַח, נבחר בסוג הכלל האחרון: "השתמש בנוסחה כדי לקבוע אילו תאים לעצב"




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



ואז החלון יֵרָאֶה כך:


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



שים לב לסטטוס שמציג "אקסל" בפנה הימנית התחתונה של  שורת הסטטוס. אנו נמצאים בסטטוס "נקודה" (באנגלית: Point), כי בחרנו תא או תחום תאים. הבחירה (התא: B3) מוקפת במסגרת מְקֻוְקֶוֶת.



"אקסל" תִּרגם את כתובת התא לכתובת מוחלטת ($B$3) - שבה גם השורה וגם העמודה נעולות.
אך מכיון שאנו רוצים שהנוסחא תתיחס לכל הציונים בעמודה B (B3:B22), נשנה את הכתובת לכתובת מעורבת: העמודה תשאר נעולה, ונשחרר את הנעילה מהשורות.
כיצד משנים מכתובת מוחלטת לכתובת מעורבת?
נעמיד את הסמן על הכתובת ונלחץ פעמיים על F4, עד שכתובת התא תשתנה  מ-$B$3ל-  $B3: עמודה Bנעולה, אך השורות אינה נעולות - ולכן הנוסחא תפעל על כל השורות הרלבנטיות בעמודה B, כלומר התאים: B3:B22.
[את ההבדלים בין כתובת מוחלטת, כתובת יחסית וכתובת מעורבת: אסביר בפוסט נפרד]
כעת, נוסיף את סימן השויון: = ונקליד את המלה min(.
הפונקציה MIN- מקבלת ארגומנט אחד: תחום תאים ומחזירה את המספר הקטן ביותר בתחום.
לכן, לאחר MINנבחר בתחום התאים B3:B22. תחום זה הוא הארגומנט לפונקציה.
כבר למדנו כיצד לבחור תחום תאים רציף:
נבחר בתא הציונים הראשון: B3 ואז נלחץ על צרוף המקשים: Ctrl+Shift ועל מקש "חץ כלפי מטה": - כדי לבחור את כל הרשימה, עד לשורת הנתונים האחרונה (שורה 22).
שים לב, אנו שוב נמצאים בסטטוס "נקודה" לאחר בחירת תחום התאים (B3:B22).




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


כאן עלינו להִזָּהֵר ולא להקיש במקשי הנִווּט (למשל: חצים, Page Up, Page Down) - כי אז, מכיון שאנו מצביעים על התא הפעיל (A3) - "אקסל" ישנה את התא הפעיל לפי מקש הנווט.
לכן רצוי לעבור למצב "עריכה" (Edit) על-ידי לחיצה על מקש F2. כעת "אקסל" יתעלם ממקשי הנווט, ונוכל להקליד את הנוסחא מבלי לחשוש שהיא תשתנה ללא רצוננו.
אנו, למעשה, סיימנו את הקלדת הנוסחא.
כך תֵרָאֵה הנוסחא הסופית:
=$B3=MIN($B$3:$B$22)



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

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



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

מה שבעצם הגדרנו בנוסחא:  
=$B3=MIN($B$3:$B$22)
אמרנו ל"אקסל": עֲבוֹר על כל התאים של התחום בעמודה Bומצא תא שבו מתקיים התנאי: =MIN($B$3:$B$22), כלומר שבו נמצא הערך הנמוך ביותר בכל התחום. אם נמצא תא כזה, צבע אותו לפי מה שהוגדר בחלונית עצוב תאים: צבע הרקע שנבחר, אפקטי מילוי שנבחרו וכו'.
כפי שראינו בשיטה הראשונה, יתכן וימָצְאוּ מספר תאים העונים על התנאי.

שיטה ג': הגדרת כלל עיצוב באמצעות הפונקציה SMALL


גם כאן נסמן את כל התאים בתחום (A3:B22), מכיון שאנו רוצים לצבוע את כל השורה: שם התלמיד והציון - נסמן את כל התחום (A3:B22).

כמו בשיטה ב', נבחר ב"עיצוב מותנה" ובחלונית שתפָּתַח נבחר ב: "כלל חדש"


בחלונית "כלל עיצוב חדש"


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

בתבה "עצב ערכים כאשר נוסחה זו נכונה" נקליד =
ונקליק על התא הראשון של תחום הציונים (B3). "אקסל" יתרגם את כתובת התא לכתובת מוחלטת ($B$3) - שבה גם השורה וגם העמודה נעולות, כפי שראינו בשיטה ב' וכפי שנראה גם בשיטות הבאות.

גם כאן, נשנה את הכתובת לכתובת מעורבת: נעמיד את הסמן על הכתובת ונלחץ פעמיים על F4, עד שכתובת התא תשתנה  מ-$B$3ל-  $B3: עמודה B נעולה, אך השורות אינה נעולות - ולכן הנוסחא תפעל על כל השורות בעמודה Bשמופיעות בתחום: B3:B22.
[כפי שכבר אמרתי, את ההבדלים בין כתובת מוחלטת, כתובת יחסית וכתובת מעורבת: אסביר בפוסט נפרד]
כעת, נקליד שוב את סימן השויון: = ואחריו SMALL(
במקום הפונקציה MIN שבה השתמשנו בשיטה הקודמת, נשתמש כעת בפונקציה: SMALL. פונקציה זו מקבלת שני ארגומנטים:
הראשון-  תחום תאים
השני - מיקום מבוקש של המספר מבחינת גודל. לדוגמא: 1 - המספר הקטן ביותר, 2 - המספר השני הכי קטן וכו'.
למשל:
 SMALL($B$3:$B$22,1 -  תן לי את המספר הכי קטן (מס. 1  מהסוף) בתחום המופיע בארגומנט מס. 1
- SMALL($B$3:$B$22,2הבא לי את המספר השני הכי קטן (מס. 2 מהסוף), וכן הלאה.
מכיון שאנו רוצים את המספר הקטן ביותר (מספר "1") - נקיש 1 בארגומנט השני.
לכן, לאחר הקשת הפונקציה SMALL נבחר בתחום התאים $B$3:$B$22, כפי שכבר למדנו בשיטות הקודמות (באמצעות מקשי: CTRL+SHIFT ומקשי החצים) ["אקסל" יהפוך את הכתובות לכתובות מוחלטות].
כעת נוסיף פסיק (מפריד בין ארגומנטים), נוסיף את הארגומנט השני: 1,  ונוסיף את הסוגר (.
כך תֵרָאֵה הנוסחא הסופית:
=$B3=SMALL($B$3:$B$22,1)


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

           
לאחר בחירת הצבע נלחץ על כפתור "אישור" ובחלון "עריכת כלל עיצוב" נוכל לראות את הנוסחא החדשה ואת צבע המילוי שבחרנו.
נלחץ שוב על כפתור"אישור" וכעת נראה את תוצאת הכלל שהגדרנו: התא שערכו המספרי הוא הנמוך ביותר והתא הסמוך אליו (תאים: A8:B8) -צבועים בצבע שבחרנו


שיטה ד': הגדרת כלל עיצוב באמצעות הפונקציה LARGE



כמו בשיטות ב' ו-ג', נסמן את התאים B3:B22 ונבחר ב"עיצוב מותנה"*"כלל חדש"*"השתמש בנוסחה כדי לקבוע אילו תאים לעצב"
בתֵבָה: "עצב ערכים כאשר נוסחה זו נכונה" נקליד את הנוסחא:
=$B3=LARGE($B$3:$B$22,COUNT($B$3:$B$22))
הסבר: הפונקציה LARGE - היא הפונקציה ההפוכה ל- SMALL שבה השתמשנו בשיטה הקודמת. במקום להחזיר את המספר הקטן ביותר, היא מחזירה את המספר הגדול ביותר.
למשל:
=LARGE($B$3:$B$22,1) מחזירה את המספר הגדול ביותר (מס.1) בתחום.
=LARGE($B$3:$B$22,2) מחזירה את המספר השני בגדלו (מס.2) בתחום, וכן הלאה.

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

כך, אם כן, תֵרָאה הנוסחא הסופית:




ובחלונית ה"עיצוב" נבחר בצבע המתאים.


נלחץ שוב על כפתור"אישור" (לסגירת חלונית "עיצוב תאים") וכעת נראה את תוצאת הכלל שהגדרנו: התא שערכו המספרי הוא הנמוך ביותר והתא הסמוך אליו (תאים: A8:B8) -צבועים בצבע שבחרנו ושוב "אישור" (לסגירת חלונית "עריכת כלל עיצוב") ושוב אישור (לסגירת חלונית "מנהל כללי עיצוב מותנה") ונקבל את השורה/ות העונה/ות לתנאי שהגדרנו.
           

שיטה ה': הגדרת כלל עיצוב באמצעות הפונקציה RANK



שיטה זו דומה לקודמותיה (שמוש בנוסחא) אך הנוסחא מורכבת ומסובכת מעט יותר מקודמותיה.
בשתי הפונקציות האחרונות שבהן השתמשנו (SMALL ו-LARGE) , אמרנו ל"אקסל" למצוא את הערך במקום ה-X ברשימה (ראשון=הכי גדול, ראשון מהסוף=הכי קטן וכו').

הפונקציה RANKשבה נשתמש היא בעצם הפונקציה ההפוכה ל-LARGE או ל- SMALL

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

הפונקציה מקבלת שלשה ארגומנטים:
1. הערך שמחפשים את דרוגו
2. הטבלה/רשימה שהערך הוא חלק ממנה
3. מספר המצין את סדר הדרוג: האם הדרוג הוא בסדר יורד (מהגדול לקטן- ואז ערך הארגומנט = 0) או מסדר עולה (מהקטן לגדול - ואז ערך הארגומנט = 1).

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

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

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

שוב, נסמן את התאים B3:B22 ונבחר ב"עיצוב מותנה"*"כלל חדש"*"השתמש בנוסחה כדי 
לקבוע אילו תאים לעצב"

בתֵבָה: "עצב ערכים כאשר נוסחה זו נכונה" נקליד את הנוסחא:
=RANK($B3,$B$3:$B$23)=RANK($B3,$B$3:$B$22,0)+RANK($B3,$B$3:$B$22,1)-1




ובחלונית ה"עיצוב" נבחר בצבע המתאים.



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



גם כאן (כמו בשיטה הראשונה) נבחן מה יקרה אם לשלשה תלמידים יהיה הציון הנמוך ביותר?      

נשנה את הציון של אופיר ושל מאי מ-68 ל-39, ואז נראה ששלוש שורות מסומנות בצבע הרקע הסגול שבחרנו: של ליה, של מאי ושל אופיר.









ואם נשפר את הציון של ליה ל-77 - "אקסל" באופן אוטומטי יצבע את התא שמכיל עכשיו את הציון הנמוך ביותר  (55): התלמידה מעין.






 


הערה לסיום


הערה חשובה לסיום

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

א. לבחור תא כלשהו בתחום שעליו הגדרנו את הכלל (בדוגמאות שלנו מדובר בתחום-A3:A22)

ב. לבחור תא כלשהו מחוץ לתחום, לבחור ב"עיצוב מותנה*ניהול כללים" ולשנות את בחלונית "מנהל כללי עיצוב מותנה" את תחום התֹקֶף של הכלל,
מ: "הקטע הנבחר הנוכחי"

ל: "גיליון עבודה זה"

ואז הכלל שהגדרנו - יופיע בטבלת הכללים ("מנהל כללי עיצוב מותנה"), גם אם נבחר תא כלשהו בגליון, שאיננו בתחום התאים שעליהם חל הכלל. 
בתמונה להלן, בחרנו בתא: D18- שאיננו בתחום A3:B22 ואז בחרנו ב"עיצוב מותנה*ניהול כללים". כעת, נוכל לראות את כל כללי העיצוב המותנה שהוגדרו לגליון כולו.

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




µµµ

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

בשיטה הראשונה השתמשנו בכללים מובנים בתוך "עיצוב מותנה" ובשיטות שלאחריה השתמשנו בכללים שאנו יצרנו באמצעות 4 נוסחאות המשתמשות בפונקציות של "אקסל": MIN, SMALL, LARGE, RANK

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

µµµ

לסִכּוּם, אני מקוה שהשִעור היה מועיל וגם מעניין, אם כי מעט ארוך....

מי שמעוניין בקובץ ה"אקסל" ובו הדוגמאות שהוצגו בשעור - מוזמן לפנות אלי באי-מייל:
-PaxMundi@gmail.com



תגובה 1:

  1. שלום,
    ברצוני לדעת, איך אני יכול להוסיף לסרגל כלים באקסל 2003; לחצן "נקה עיצוב" או "מחק עיצוב". היה לי פעם, אך נעלצתי להתקין מחדש ואנני זוכר כיצד אז הוספתי לחצן זה.
    בתודה מראש

    השבמחק