הנושאים החמים
את השאלה הזו שמעתי / קראתי לא אחת, והיא: - איך / כיצד ליצור מספור רץ אוטומטי באקסל/ בשיטס באופן הכי מהיר והכי יעיל?
במהלך מאמר זה אציג ואסקור מספר פתרונות לשאלה זו, כמו כן, אסביר כיצד פועלת כל שיטה, כלומר מהי הלוגיקה שמסתתרת בטכניקה של השיטה.
כבר בפתיח המאמר אגלה לכם בסוד שפונקציית sequence הגאונית והנפלאה, היא השיטה המומלצת ביותר (לפחות בעיניי…) מכל ארסנל הפתרונות שאכתוב לקמן.
רוצים לדעת מהי הסיבה? תקראו עד הסוף…
קדימה התחלנו!
אז ראשית, אפשט ואציג את השאלה בצורה הכי פשוטה.
תראו בצילום מסך כאן:
כפי שאתם רואים, בעמודה C קיימת רשימת תאריכים של ימי העבודה. אם נרצה ליצור סדרה של מספרים עוקבים בעמודה B בצורה מקבילה לרשימת התאריכים,(מכל סיבה שהיא…ולא רק לדעת כמה ימי עבודה ישנם, מטרה שכזו ניתנת לחישוב גם בעזרת פונקציית counta למשל. לעיתים ישנה סיבה הכרחית למספור כמו בדוגמא שאציג להלן, כאשר ישנם תאים ריקים בתוך טווח התאים בעמודת התאריכים) נוכל לבצע זאת בכמה טכניקות.
פתרון 1 - גרירת שני תאים סמוכים:
זו השיטה הידועה ביותר, "הקלאסית" וזו שכל אקסליסט מתחיל מכירה ויודעה.
אופן הביצוע:
כותבים באופן ידני 2 תאים סמוכים (כמו התאים B2-B3 בצילום מסך הנ"ל) שהוזנו ומסמנים אותם עם עכבר המחשב. >
מזיזים את העכבר לפינה השמאלית והתחתונה. בשלב זה אנו נראה את ידית המילוי וכעין סימן פלוס +.(סימנתי באדום את מיקומה של ידית המילוי) > גוררים כלפי מטה עד לשורה בה הטבלה מסתיימת.
ההיגיון מאחורי הטכניקה:
שאנו כותבים 2 ערכי מספר בשני תאים סמוכים ומסמנים אותם, האקסל (והוא הדין שיטס) מספיק חכם "ומבין דבר מתוך דבר" כדי לחשב את ההפרש שקיים בין 2 המספרים.(הן בסיטואציה של הזנת 2 מספרים עוקבים כמו 1 ו2, והן בסיטואציה של הזנת 2 מספרים עם דילוג בניהם כמו 2 ו4 שההפרש בניהם הוא 2)
כאשר גוררים את ידית המילוי מטה, האקסל משמר ברציפות את אותו ההפרש שקיים בין שני התאים העליונים שהוזנו בהזנה ידנית.
רגע רגע…ומה קורה שבשני התאים העליונים הוזנו 2 תאריכים ולא מספרים?
התשובה היא- שהקסם עובד בדיוק אותו דבר, כל עוד הייתה הקפדה לכתוב את התאריך בפורמט הנכון וכן להגדיר נכונה את פורמט התא כפורמט תאריך.
למי מכם ששואל (ובצדק…) מה ההיגיון פה? אז אסביר במילה או שתיים את מה שמתחולל "מאחורי הקלעים" באקסל/שיטס.
ותחילה אשאל, האם נתקלתם באקסל/בשיטס אי פעם בתאריך "המוזר" 31/12/1899 כהחזר מפונקציה כזו ואחרת?
ובכן. ההסבר לכל זה הוא שכל תאריך "בעיניו" של האקסל/השיטס הוא למעשה מספר כן. כן…
(כאן אתייחס בכל הקשור לתאריך בלבד מבלי להיכנס לסוגיית השעה בתוך כל תאריך.)
המספר ב-subtext של התאריך מכונה "המספר הסידורי" של התאריך. תוכלו לחשוב עליו כעל "תעודת זהות" ייחודית…לכל יום יש מספר סידורי משלו.
אם נרצה לגלות מהו המספר הסידורי של תאריך מסוים שהוזן באחד מתאי האקסל/שיטס.
נוכל לעשות זאת על ידי 3 הדרכים העיקריות דלהלן:
1.לשנות ידנית (בסרגל כלים) את פורמט התא שנכתב בו התאריך -לפורמט מספר/טקסט.
2.על ידי פונקציית datevalue שמבצעת המרה "מאולצת" מפורמט תאריך למספר.
3.על ידי פונקציית value שגם היא מבצעת המרה "מאולצת" מפורמט תאריך למספר.
מסיבות כאלו ואחרות -התאריך הראשון שהוגדר לתחילת הספירה הוא 01/01/1990.
כלומר, בכרונולוגיה של הזמן, התאריך 31/12/1989 הוא תאריך "קצה" שממנו והלאה מתחילה ספירת התאריכים באקסל.
אין בתאריך זה (01/01/1990) איזה ענין /מאורע מיוחד, למרות שהדיפלומט היפני - Chiune Sugihara (שהציל אלפי יהודים בימי מלחמת עולם השנייה והבריחם דרך יפן ואף זכה להכרה בישראל "כחסיד אומות העולם") נולד בדיוק בתאריך זה.
הסיבה העיקרית לכך היא פשוט - שמדובר באיזה תאריך "הגיוני" לתחילת הספירה. וגם כזה שהיה ניתן להתאימו לתכנות של האקסל.
זו הסיבה שכאשר פונקציה "לא מוצאת" תאריך להחזר, היא פשוט מחזירה את התאריך "קצה" כברירת מחדל .כלומר התאריך 31/12/1899.
ולמי מכם שחפץ "בהרצת" ותכנון לו"ז לאלפי שנים הקרובות, אז רק אוסיף ואזכיר, שהמספר הסידורי של תאריכי האקסל מוגבל עד סיומה של שנת 9999…(וליתר דיוק עד 01/01/10000) אנא, לתשומת ליבכם …!
כעת ניתן להבין את המתרחש בעת גרירת שני תאריכים סמוכים עם ידית המילוי.
גם כאן האקסל (והוא הדין שיטס) מחשבים את ההפרש בין שני המספרים הסידוריים של התאריכים.(ככה זה! הפרש הוא מונח מעולם המתמטיקה, ושם השפה היא במספרים בלבד!)
כל הפעולה מתבצעת "מאחורי הקלעים", ולנו -בסופו של דבר מוצגים התאריכים בפורמט תאריך המוכר לנו.
תראו בצילום מסך הבא - מגיליון "נספח" המצורף בלינק להלן:
תוכלו לראות בשורות 2.4.5 את ההמרה מפורמט תאריך לפורמט מספר כפי שכתבתי לעיל.
ופונקציית datedif (זמינה הן באקסל 2010 ומעלה והן בשיטס) היא ההוכחה לכך.
פונקציה זו מחשבת את מספר הימים/החודשים/השנים ועוד (פרמטר זה מוגדר בארגומנט השלישי) בין שני תאריכים.
הערה חשובה- ישנם באגים ידועים (תוכלו למצוא מידע על כך בפורומים שונים באנגלית) סביב התאריכים בתחילתה של שנת 1990.
וכן מספר שינויים בין האקסל לשיטס (המספר הסידורי בתא C2 באקסל הוא 1 ולא 2)
זו הסיבה שהוספתי באופן ידני 2 + בסיומה של פונקציה זו בתא C7.
אז בקשה קטנה…בפעם הבאה שתרצו להשתמש בפונקציית datedif פשוט תמנעו מללכת "עד הקצה", קרי שנת 1990.זו באמת סוגיה סבוכה, וחבל לכם להסתכך בכל הטריטוריה הזו.
אפרופו טכניקה זו, כאשר מדובר בטבלה גדולה (עם מאות/אלפי שורות) ותרצו לבצע מספור רץ אוטומטי בעמודה מסוימת עד לסיום טווח הטבלה. אז הנה טריק קטן שאולי לא ידעתם…
הטריק הוא לכתוב 2 תאים סמוכים כנ"ל. ואז להניח את העכבר בפינה השמאלית והתחתונה של שני התאים, וללחוץ פעמיים(דאבל קליק) בעכבר שמאל. הופ…הקסם עובד! נסו ותראו! (כל זאת, ללא שום תלות אם מדובר בטווח או בטבלה-כלומר שבוצע "המר לטבלה")
פתרון 2-פונקציית row:
טכניקה זו היא גם מצוינת אך פחות מוכרת…
אופן הביצוע:
כותבים בתא העליון (שאנו מעוניינים בו להתחיל את המספור ) את פונקציית row עם הפנייה לתא בשורה 1 .ואז גוררים למטה עד לשורה שמסתיימת הטבלה.
בגיליון המצורף פונקציה זו (להלן קוד) הוזנה בתא F2 ונגררה למטה .
קוד:
=ROW(F1)ההיגיון מאחורי הטכניקה:
כזכור לכם מהמאמרים הקודמים, פונקציית row מחזירה את מספר השורה האמיתי (בתא שהוזן בארגומנט הפונקציה) שבגיליון אקסל/שיטס.
וכאשר לא הוזן כלום הפונקציה תחזיר את מספר השורה בו נכתבה הפונקציה.
זו הסיבה שמוחזר מספר 1 בתא F2 בדוגמא לעיל.
תא F1 שהוזן בארגומנט הפונקציה הוא ללא קיבוע.
ולכן, בעת גרירה מספר השורה תשתנה באופן "יחסי" ככלל "ההפניות היחסיות" .וכך יתקבל מספור רץ מ1 עד 9 (בדוגמא שהובאה בגיליון המצורף).
אז בפעם הבאה שישאלו מכם "חידת אקסל", כיצד יתכן לבצע מספור רץ אוטומטי בגרירת תא אחד בלבד (ולא שניים)???
התשובה היא כנ"ל בדיוק…זה נפלא ומדויק!
פתרון 3 -מילוי סדרה באמצעות סרגל הכלים באקסל:
טכניקה זו רלוונטית לאקסל בלבד .וגם היא (אולי) פחות מוכרת לכם.
אופן הביצוע:
כותבים באופן ידני את הספרה הראשונה שאנו מעוניינים בה להתחיל את סדרת הספרות.> בסרגל כלים לוחצים על מילוי>
בוחרים "סידרה" >יופיע לכם מסך כזה
בוחרים "בסדרה ב" בין עמודות (כלומר שהסדרה תרוץ מלמעלה למטה באותה עמודה) לבין שורות (כלומר שהסדרה תרוץ מימין לשמאל באותה שורה).> "בערך פסיעה" בוחרים וכותבים מהו הדילוג שנרצה בו, כלומר בכמה ספרות יהיה הדילוג בין תא לתא.(ברירת המחדל באקסל הוא דילוג של ספרה 1) > "בערך לסיום" כותבים מהו המספר האחרון בסדרה זו. > לוחצים אישור וזה הכול!
ההיגיון מאחורי הטכניקה:
עניין של אלגוריתם. כך תוכנת האקסל. סה טו!
פתרון 4 -פונקציית sequence:
טוב. אם קראתם עד כאן, תדעו שכל זה הקדמה לעיקרו של מאמר זה…כעת נלמד יחדיו את טכניקת הביצוע בעזרת פונקציית sequence הגאונית ורבת העוצמה.
אחרונה, אחרונה חביבה….אכן!
בגיליון המצורף קיימת סדרת תאריכים בעמודה K (וכן בעמודה J -עוד אשוב לעמודה זו בהמשך…)
אצרף כאן (בקוד להלן) את מבנה הפונקציה בתא K2,ואשלב הסבר אודות 4 הארגומנטים של הפונקציה.
קוד:
=SEQUENCE(10,1,DATE(2024,6,1),1)ארגומנט ראשון -rows: זהו ארגומנט חובה. בארגומנט זה כותבים בכמה שורות תהיה פריסת סדרת המספרים/התאריכים. בקוד לעיל הבחירה הייתה 10. אנו חפצים בסדרה בת 10 תאים.
ארגומנט שני -columns : כלומר בכמה עמודות תתבצע פריסת סדרת התאים. בקוד לעיל הבחירה הייתה 1.אנו חפצים בסדרה בת עמודה אחת.
ארגומנט שלישי - start: התחלה, כלומר מהו המספר /התאריך הראשון בו תתחיל הסדרה.
בקוד לעיל נעשה שימוש בפונקציית date .ההחזר מפונקציה זו שווה ערך לתאריך 01/06/2024.
ארגומנט רביעי -step : צעד, פסיעה. כלומר כמה צעדים יש לדלג בין תא לתא בסדרה. בקוד לעיל הבחירה הייתה 1.
במידה ומעוניינים בספירה לאחור (reverse), יש לכתוב בארגומנט הרביעי 1-.
לפני שנמשיך. הנה הארה קטנה: ארגומנט שני, שלישי ורביעי הם אופציונליים כלומר שדות רשות.
אם הארגומנטים הם ריקים מערכים, ברירת המחדל היא הספרה 1 בכולם.
נקודה חשובה- משפחת פונקציות התאריך באקסל/שיטס רבות הן ומגוונות. הזכרתי לעיל את פונקציית date.לא יכולתי להיכנס במסגרת מאמר זה לכל פירוט הווריאציות הקיימות בכל מיני דילוגים וחשבונות שונים. כי רבים המה…
אם נתקלתם בדילוג מסוים של תאריך שלא הצלחתם לפתור. נא שתפו בתגובות למטה.
וכאן הבן שואל: מהו היתרון הגדול בטכניקת ביצוע על ידי פונקציית sequence, ומהו "החידוש" בפונקציה זו? הרי את אותן תוצאות אנו יכולים לקבל בעזרת השיטות "הקלאסיות" מִקַּדְמַת דְּנָא(3 השיטות הראשונות שהוסברו לעיל )?
ובקיצור, מאי נפקא מינה…?
התשובה היא במילה אחת : דִּ-י-נָ-מִ-יּ-וּ-ת!
ובכמה מילים…הנה אסביר: תראו שוב את התמונה שצירפתי לעיל בראשית דבריי.
תוכלו לראות שבעמודות B וF בוצעו סדרות המספרים על ידי גרירה (כפתרון 1 ו2 שציינתי לעיל)
הגרירה בוצעה עד לשורה 10.מדוע רק עד לשורה זו בוצעה גרירה? אז התשובה -כי "אז" לא היה צורך ביותר מכך…
והנה מה קרה פתאום?...בעל הגיליון החליט להוסיף בעמודות התאריכים של ימי עבודה עוד שורה אחת נוספת של ערכים.
כאשר השימוש נעשה על ידי טכניקות של גרירה ידנית כזו ואחרת.(והוא הדין לטכניקה באקסל שהוזכרה לעיל בפתרון 3).המספור רץ כל עוד אצבעות בעל הגיליון אוחזות וגוררות בעכבר.
רגע אחרי = הכול נותר סטטי ואינו דינמי.
לשם היכר, צבעתי בגיליון המצורף את התאים האלו שנותרו ללא מספור במילוי צהוב.
כעת, בואו תראו כיצד שימוש נכון ונבון בפונקציית sequence, וכאשר משלבים אותה עם פונקציית counta כל המספור הפך לדינמי.
תראו את הפונקציות הנפלאות שהוזנו בתא J2.
קוד:
=SEQUENCE(COUNTA(K2:K))כאן אנו אומרים לפונקצייה במילים פשוטות,- נא ליצור מספור רץ של ספרות. (יתר הארגומנטים ריקים. הפונקציה "מבינה לבד" ששתיקה "כהודאה". וכי אנו חפצים בברירת מחדל של הפונקציה שהוא מספר 1 בכולם)
ואם תרצי לדעת עד איזו שורה להמשיך את המספור.כלומר כמה שורות לבצע מספור? אז הנה התשובה - (COUNTA(K2:K.
הנה זה פשוט נפלא! כי בכל פעם שתא חדש בעמודה K יתמלא בערך תאריך (לדוגמא) ,אז להחזר של פונקציית counta יתווסף מספר נוסף, וממילא בעמודה המקבילה J יתווסף באופן אוטומטי מספור רץ "חדש" על ידי ההוראה "החד פעמית" שניתנה לפונקציית sequence.
והנה עוד "ממתק אקסל" לסיום.
תראו את הצילום מסך דלהלן מתוך הגיליון המצורף.
בעמודה N מסיבה כזו ואחרת - ישנם תאים ריקים שלא מכילים טקסט תאריך כל שהוא.
כל השיטות שהוזכרו לעיל שהן לבדן לא יוכלו לתת מענה הולם. תחשבו על כך…!זו ממש בעיה!
אך, אל דאגה. גם לכך יש פתרון ברוך ה'.
תראו את הפונקציות שהוזנו בתא M2
קוד:
=IF(ISBLANK(N2),"",COUNTA($N$2:N2))כעת אסביר במילים פשוטות את מהלך פונקציית if הארוכה.
אנו "אומרים" לפונקציית if בתא M2.אם זה נכון שתא N2 ריק, כלומר שהתנאי מתקיים, אז יש להשאיר את תא M2 ריק דהיינו "".
ואם התנאי לא התקיים, דהיינו שתא M2 הוא מלא בערך כלשהו. אז- תחזירי בתא מספר ששווה לcounta מטווח N2 (עם קיבוע) עד N2(ללא קיבוע).
כמה זה ביחד? תא אחד, כלומר שווה -1.
יופי מצוין. כעת, את כל הקוד הנ"ל אנו גוררים מטה עד לקצה הטבלה הנראה לעין.
ומה אנו מרוויחים כאן?
בואו תראו. כאשר גוררים את הפונקציה, שימו לב שהתא ששייך ל - "מ" (כלומר מתחילת) של פונקציית counta מקובע. המשמעות לכך שלאורך כל גרירת התאים ערך N2 נשמר.
אך בחלק שמתייחס "עד לתא" התא אינו מקובע. ועם גרירת הנוסחה .הערך בפונקציה משתנה באופן יחסי.
אקח לדוגמא את תא M13 ואלו הן הפונקציות שבו.
קוד:
=IF(ISBLANK(N13),"",COUNTA($N$2:N13))שלא תתבלבלו. בדרך כלל הספירה של counta היא "עד ועד בכלל".
כאן התוצאה היא לא 12 אלא 11.כי counta מדלגת על תא N12 (הוא ריק…).ואם נפחית אחד מכלל התאים (12) יצא לנו בדיוק-11.
וואו. זה פשוט נפלא, וגאוני! באמת….
זהו. עד כאן!
מקווה שנהניתם מהלוגיקה של האקסל/שיטס…
מקווה שנהניתם מהגאונות, הקסם והיופי של כל הפונקציות שהוזכרו כאן, ובפרט פונקציית sequence המופלאה…
כל מאמר זה הוא בסה"כ מאמר "פתיחה" אודות נפלאותיה של הפונקציה ,בו הסברתי על היתרון שבה לביצוע מספור רץ אוטומטי.
במאמרים הבאים אי"ה תגלו עוד הפתעות מרתקות בכל הקשור לפונקציה זו.
ועד אז. תנסו פשוט "לאמץ" את הפונקציה ואת הטכניקות על ידה.
בהצלחה רבה!
לינק לגיליון שיטס הכולל הסבר אודות פונקציית sequence מצורף כאן.
- Next article in the series 'אקסלומדע'ס': אקסלומדע'ס פרק 5 ▪︎ סימולציה לדיני מעשר בהמה באקסל ▪︎ עוד טעימה מִנִּפְלְאוֹתֶיהָ של פונקציית SEQUENCE ▪︎ מיוחד ומרתק!
- Previous article in the series 'אקסלומדע'ס': אקסלומדע'ס פרק 3 ▪︎ כך תִּצְּרוּ את לוח המאה באקסל ▪︎ תופתעו כמה זה (כמעט) פשוט...
