כיצד ניצור לוח שנה באקסל 365
אחת השאלות הנפוצות שאני נתקלת בהן, היא איך ליצור לוח שנה באקסל, שיציג את ימי החודש הנבחר.
במאמר הזה אציג את הטכניקה הבסיסית ליצירת לוח השנה, וכן טכניקות עיצוביות להצגת הנתונים.
מה יש לנו?
כדי לאפשר למשתמש לבחור את החודש והשנה ליצירת לוח השנה, אשתמש בשני תאי עזר, שבהם המשתמש יקליד את הנתונים הרצויים:
מה אנחנו רוצים להשיג?
לוח שנה שיציג את ימי אותו החודש, באופן הבא:
במה נשתמש?
- בפונקציה SEQUENCE ליצירת רשימת הימים
- בפונקציות תאריך ליצירת תאריך מתאי העזר, וכן למיקום היום הראשון בחודש ביום הנכון בשבוע
אז בואו נתחיל…
שלב ראשון – יצירת התבנית של לוח השנה
לצורך כך ניצור באמצעות הפונקציה SEQUENCE מטריצה של 6X7
עבור ימי השבוע והשבועות בחודש:
=SEQUENCE(6,7)
נקבל את הטבלה הבאה:
השלב השני – החלפת המספרים בתאריכים
לצורך כך ניתן לאקסל את תאריך ההתחלה, בארגומנט השלישי של הפונקציה Sequence
את התאריך נבנה באמצעות תאי העזר, והפונקציה Date:
DATE(B1,B2,1)
ונכניס אותה כארגומנט השלישי של הפונקציה Sequence:
=SEQUENCE(6,7, DATE(B1,B2,1))
וזו התוצאה שהתקבלה:
זה המקום להזכיר שבאקסל תאריכים הם בסך הכל עיצוב של מספר, כשה 1/1/1900 מיוצג על ידי המספר 1,
ושאר התאריכים מקבלים מספר עוקב.
כדי שנדע באיזה יום מדובר, נוסיף את שמות ימות השבוע בשורת הכותרת.
כדי להציג את היום בחודש, אשנה את העיצוב מ'מספר' ל'מותאם אישית', ואבחר ב DD, שיציג לי את היום בחודש.
הדרך המהירה ביותר להיכנס לעיצוב תאים היא על ידי שילוב המקשים Ctrl+1, אבל אתם יכולים לשנות את העיצוב בדרך שנוחה לכם:
וזו התוצאה שהתקבלה:
אבל רגע, יש לנו בעיה…
ה 1/7/2023 לא יוצא ביום ראשון, אלא ביום שבת, ולכן צריך להוסיף לנוסחת חישוב היום תיקון, שיציב את הראשון לחודש תחת היום הנכון.
את זה נעשה באמצעות הפחתת היום בשבוע של התאריך, פלוס 1:
WEEKDAY(DATE(B1,B2,1))+1
ואם נשלב אותו בנוסחה, היא תראה כך:
=SEQUENCE(6,7,DATE(B1,B2,1)-WEEKDAY(DATE(B1,B2,1))+1)
בואו נראה שהכל עובד
נשנה את החודש לאוגוסט, ואנחנו יכולים לראות שהראשון לחודש אכן מתחיל ביום ג'
ועכשיו, כמה תיקונים עיצוביים להצגת הימים הרלוונטים
אנחנו יכולים לראות שלוח השנה מציג לנו את הימים האחרונים של חודש יולי, וגם את תחילת חודש ספטמבר.
בדוגמה שלנו, אפשר לראות שיש שורה שלמה מיותרת (שורה 11) שאין בה אף יום אחד ששייך לחודש אוגוסט.
אז איך מציגים רק את הימים הרלוונטיים?
נוכל לעשות זאת באמצעות עיצוב מותנה
את העיצוב המותנה נעשה בשלושה חלקים שונים:
סימון הימים שלא שייכים לחודש הנוכחי בצבע אפור
כדי להבחין בין ימי החודש הזה לימי החודש הקודם, נבקש מאקסל לסמן את כל הימים שאינם שייכים לו בצבע אפור, וזו הנוסחה:
=MONTH(C6)<>$B$2
כלומר, אנחנו בודקים עבור כל תא, בטבלה שמתחילה בתא C6, האם החודש של התאריך שקיים בו, שונה מהחודש שקבענו בתא B2
הסיבה שאנחנו יכולים לחשב את החודש, למרות שמה שמוצג בתא הוא היום, היא שבפועל קיים בו תאריך מלא, ואנחנו בחרנו להציג מתוכו רק את החודש.
התצוגה היא ויזואלית בלבד, ולא משפיעה על הערך האמיתי.
בתא C6 הסרתי את הקיבועים, כדי שיעבוד על כל טווח הטבלה (גם על העמודות וגם על השורות),
ואילו את תא B2 השארתי עם קיבוע מלא, משום שאליו ארצה להשוות תמיד.
וזו התוצאה שהתקבלה:
הסתרת שורה שלמה, אם יום א' שייך לחודש הבא
אמנם קיבלנו את הימים החורגים מהחודש בצבע אפור, אולם השורה האחרונה לא צריכה להופיע כלל, כי יום א' חל כבר בחודש הבא.
לכן, נוסיף כלל שאומר שאם התאריך ביום א' בשורה הרלוונטית גדול מהחודש שבחרנו, כל השורה תיצבע בלבן
זו הנוסחה:
=MONTH($C6)>$B$2
שימו לב שקיבעתי את עמודה C בנוסחה, כדי שתמיד אשווה ליום ראשון באותו השבוע, אבל הסרתי את הקיבוע מהשורה,
משום שאני רוצה שההשוואה תחול על כל שורה בהתאמה.
וזו התוצאה:
שימו לב למסגרת הכחולה שמקיפה את הטווח, בה אפשר לראות בפירוש שקיימים נתונים גם בשורה האחרונה,
אולם משום שצבענו את הפונט בצבע לבן, אנחנו לא רואים אותם
הוספת קווי גבול
עכשיו נרצה להוסיף קווי גבול ללוח השנה שלנו, ואת זה נעשה על ידי בדיקה של יום א' בכל שורה. אם הוא קטן או שווה לחודש הנוכחי, הקף אותו בגבול.
שימו לב לקיבוע שמחייב את האקסל לבדוק את העמודה הראשונה בטווח, עבור כל נתון בשורה הרלוונטית
=MONTH($C6)<=$B$2
וזו התוצאה שהתקבלה:
אם נבחר חודש שהימים שבו מתפרשים על 6 שורות, לדוגמה חודש יולי בשנת 2023,
קווי הגבול יופיעו גם בשורה שכעת נראית ריקה.
רוצים לדעת איך להדגיש את ימי הפעילות? לחצו כאן
כאן תוכלו ללמוד עוד על הפונקציה UNIQUE
מעוניינים ללמוד בקורס על פונקציות המערך החדשות?
צרו קשר כאן ואחזור אליכם בהקדם