לכתוב נוסחאות בצורה מודרנית - תשכחו מגרירת נוסחאות

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

נתחיל בתיאור סיטואציה יומיומית שמתרחשת אצל כל מי שרגיל להשתמש ב-Excel: כולנו מכירים את זה, שאנחנו כותבים נוסחה לתא אחד, למשל B2*C2, ואז גוררים אותה ידנית למטה כדי ש-Excel יעתיק אותה לכל השורות. זו השיטה המסורתית שאנחנו מכירים כבר שנים, ולמרות שהיא מאוד קלה לביצוע, יש לה כמה חסרונות בולטים:

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

הפתרון לכל הבעיות האלה הוא בשימוש בתכונה שנקראת מערך זולג Spilled Array, והתכונה הזו, היא שינוי מהפכני (לא פחות!) בגרסאות אקסל החדשות.

הערה: במדריך הזה מוזכרות 3 תכונות שונות. התכונות זמינות מאקסל 2021, ולמנויי אופיס 365.

מה זה מערך זולג Spilled Array באקסל?

השינוי הוא כזה: אקסל מעכשיו “מבין” שכאשר אנחנו מבצעים פעולה על טווח נתונים, אנחנו כנראה רוצים שהתוצאה תחול על כל הטווח. זהו הרעיון שמאחורי מערך זולג Spilled Array .

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

במקום לכתוב B2*C2 ולגרור, תוכלו פשוט לכתוב בתא אחד: =B2:B15*C2:C15 (=כלומר כל תא בעמודה B כפול תא מתאים לו בעמודה C - בשורות הרלוונטיות). אקסל יבצע את החישוב עבור כל זוג תאים מקביל (B2*C2, B3*C3 וכן הלאה), והתוצאות יופיעו באופן אוטומטי לאורך העמודה, בלי צורך בגרירה.

נוסחאות מערך זולגות כתובות בתא אחד בלבד

לנוסחאות זולגות יש סוג של קסם.

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

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

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

דוגמה פשוטה לנוסחה זולגת

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

במקום זאת, כל מה שצריך לעשות הוא:

1. לבחור את התא הראשון בעמודה שבה רוצים להציג את התוצאות.
2. לכתוב את נוסחת ההכפלה, אך במקום להתייחס לתא בודד, מתייחסים לטווח שלם של תאים. לדוגמה, אם הנתונים שלך נמצאים בתאים A2 עד A5 ו-B2 עד B5, פשוט כתוב:
   =A2:A5 * B2:B5
3. לחץ על Enter.

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

מה עדיין לא מושלם בטווח זולג ?

הדוגמה שהצגנו היא נהדרת, אבל היא עדיין לא מושלמת. כמו שבוודאי שמתם לב, כתבנו את הנוסחה כך: =P8:P12*Q8:Q12. אז למרות שהנוסחה הזו גורמת לתוצאות לזלוג באופן אוטומטי, היא עדיין מקובעת לטווחים ספציפיים (שורות 8 עד 12).

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

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

הסימנים שמשנים את כללי המשחק: נקודה וסולמית

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

סימן הנקודה “dot operator”

האופרטור הזה מאפשר לנו, לקצץ אוטומטית טווח מיותר.

מה זה אומר?

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

הפתרון הוא השימוש בקיצור B:. - שימו לב לנקודה שהוספתי.

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

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

יש 2 אפשרויות לשימוש בסימן . נקודה באקסל (נקרא גם Trim Reference):

1. נקודה בסוף הטווח A1:C5. חותך תאים ריקים מהסוף.
2. נקודה בתחילת הטווח A1.:C5 חותך תאים ריקים מההתחלה.

וכמובן, אפשר להשתמש גם ב-2 נקודות, כלומר גם עבור חיתוך תאים רייקים בהתחלה, וגם עבור חיתוך תאים ריקים בסוף.

סימן סולמית (Hash operator)

סימן הסולמית (#) הוא מאפיין נוסף חשוב בעניין של נוסחאות זולגות/נשפכות.

מה מאפשרת סולמית ב- Excel?

היא מאפשרת להתייחס לטווח זולג שלם, בלי לדעת מראש עד איזה שורהעמודה הוא יגיע.

במילים אחרות, הסולמית אומרת לאקסל, הטווח יכול להתרחב כמה שצריך.

אבל!!! שימו לב, מדובר בטווח זולג! לא כל טווח.

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

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

מתי זה טוב?

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

מה זה #SPILL! ב- Excel?

יחד עם אפשרויות חדשות, מגיעות גם תקלות חדשות.

לפעמים ננסה בטעות לכתוב בתוך טווח זולג, ונקבל שגיאת #SPILL!.

זה אומר שיש משהו שניסינו להפריע לחישוב של אקסל לכתוב תוצאות בתוך טווח זולג. כלומר כתבנו משהו בתוך טווח, שכבר משמש לנוסחה זולגת, וזה - אסור. לכן אקסל נותן לנו תקלת “#SPILL!”.

כדי לפתור את תקלה #SPILL!, פשוט נמחק את התוכן הסורר מהתא, והנוסחה תתרענן ותציג את התוצאות כראוי.

מה היתרונות בכתיבת נוסחאות זולגות?

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

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

בנוסף, הטכנולוגיה הזו היא הבסיס לפונקציות מתקדמות רבות כמו FILTER, SORT, UNIQUE ו-XLOOKUP, שבלעדיהן לא היו יכולות לעבוד בצורה יעילה על טווחי נתונים. התחילו להשתמש בהן עוד היום, ותראו איך עבודתכם באקסל משתנה מקצה לקצה.