הנושאים החמים
תמונת מקור - Leonardo AI
שלמה המלך ע"ה החכם מכל האדם כתב בספר משלי (כ"ז.י"ט):
"כַּמַּיִם הַפָּנִים לַפָּנִים כֵּן לֵב הָאָדָם לָאָדָם".
ופשוטו של מקרא זה הוא כמו שפירש רש"י ז"ל:
"כמים - הללו הפנים שאתה מראה לתוכן הן מראות לך.
כן לב האדם לאדם - חברו. לפי מה שאדם יודע שחבירו אוהבו, כן הוא מראה לו פנים".
ובספר "דברי אליהו" (והוא לקט אמרות, חידושים וביאורים בשם רבינו אליהו מווילנא - הגר"א זצ"ל) הביא בפסוק זה מה שכתב בספר "בית יצחק" וזה לשונו:
"שמעתי בשם מאורן של ישראל הגר״א מווילנא, לכאורה יש לדקדק למה השמיענו שלמה המלך דבר זה, ואיזה דבר מוסר יצא לנו מזה?
ואמר הגר״א ז״ל, דכוונת הכתוב בזה היא כך, דהנה כאשר נעיין באותיות האל״ף בי״ת, נראה דרובן אין אותיותיהן הנסתרות והנעלמות דומות ושוות לאותיות הנגלות והנראות, דהיינו אות אל״ף, האות הנגלה היא א' שהוא אחד, והאותיות הנסתרות הן - לף שמניינן עולה למאה ועשר.
וכן אות בי״ת, האות הנגלה והנראה היא ב' שהיא שווה שנים, ואותיותיו הנסתרות הנה - ית שהן ארבע מאות ועשר.
כמו כן יש להיפך, שהאות הנגלה הוא יתר על אותיותיו הנסתרות, כגון האות ה״א, שהאות הנגלה הוא ה' שמנינה חמש, והאות הנסתר היא א' שהיא רק אחד. וכן האותיות עי״ן, פ״א, צד״י, קו״ף שכולן אותיותיהן הנגלות יתירות על האותיות הנסתרות.
אמנם כל האותיות של תיבת - מים הנגלות והנסתרות שוות בהן, דהיינו האות מ״ם, הגלויה היא מ' שמנינה ארבעים, והנסתרת היא גם מ'. וכן היו׳׳ד, האות הגלויה היא י' שמונה עשר, והנסתרות - וד, שהן ג״כ עשר. וכן המ״ם השני.
זהו שאמר שלמה המלך כמים רוצה לומר, כמו תיבת מים - הפנים לפנים אותיותיה שוות ודומות פנים הנסתר לפנים הנגלה כנ״ל, כן לב האדם שהוא הפנים הנסתר, צריך להיות שווה לאדם לפנים הנגלה, שהוא הפה, שיהא פיו ולבו שווים, ולא ידבר אחד בפה ואחד בלב, אלא יהיה תוכו כברו.
בזה ביאר הגר״א ז״ל מדרש פליאה ״כמים הפנים לפנים - מדוע לא אמר כיין הפנים לפנים? מפני ו' יתירה". ע״כ. והוא פלא.
אכן לפי הנ״ל הוא מבואר יפה, דהוקשה למדרש למה לא אמר כיין הפנים לפנים כיון דגם אותיות יין שוות בהן הנגלות והנסתרות, דהיודי"ן הם שווים כנ״ל, והנ׳ ג״כ שווה, דאות הנגלה הוא נ' והנסתר ג״כ נ'? ותירץ מפני ו׳ יתירה, דאות נ׳ נכתב במלואו עם ו' – נו"ן. וה-ו' אין שווה בו הנגלה והנסתר". עכ"ל "הדברי אליהו"
נפלא מאוד!
דִּבְרֵי פִי חָכָם חֵן…
ומתוך ביאורו של רבינו זצ"ל (שביאר דמניינם של האות מ והאות ם שווים הם לארבעים) מילתא אגב אורחא קא משמע לן דאותיות מנצפ"ך הם שווים במנין הגימטריה לאותיות הרגילות. (וכך היא ברירת המחדל בפונקציות האקסל / שיטס. זו הסיבה שלא היה צורך להוסיף את אותיות מנצפ"ך בטבלת המקור בגיליון המצורף לקמן בלינק).
ודע, דשיטה זו שונה וחלוקה משיטת החישוב של מנין הגימטריאות על פי צופן אי"ק בכ"ר, עיין במה שכתבתי באריכות במאמר כאן.
כעת, בואו תראו איך הנ"ל קשור לאקסל ולעולמן של הפונקציות…
ובכן, להבדיל בין קודש לחול…הנה כמה מילים על המונח - גרירת נוסחאות באקסל / שיטס, על הטכניקות הקלאסיות ועל עוד טכניקה נפלאה שאולי לא הכרתם.
סקרנים לשמוע? בואו נתחיל.
לפני שנלמד על הטכניקות השונות חשוב להבין קודם את העיקרון המנחה שנקרא "הפניות יחסיות" (Relative References).
כברירת מחדל, כאשר אתם כותבים נוסחה (למשל, =A1*B1) בתא C1, תוכנת האקסל / שיטס מפרשת את ההפניה כ"הכפל את התא שנמצא שתי עמודות משמאל בתא שנמצא עמודה אחת משמאל".
כאשר תגררו נוסחה זו מטה לתא C2, הלוגיקה נשמרת, וההפניות מתעדכנות באופן יחסי למיקום החדש. הנוסחה הופכת אוטומטית ל=A2*B2 ("הכפל את התא ששתי עמודות משמאל בשורה הנוכחית בתא שבעמודה אחת משמאל בשורה הנוכחית").
בצילום מסך שלפניכם (מתוך הגיליון המצורף בלינק להלן) תוכלו להבחין כי בתא F2 הוזנה פונקציית xlookup "הכי קלאסית שיש" עם הפניה "מעורבת" (קיבוע מלא בארגומנט השני והשלישי והפניה יחסית בארגומנט הראשון).
הפונקציה בתא F2 היא:
קוד:
=XLOOKUP(E2,$B$2:$B$4,$C$2:$C$4)לאחר גרירת הפונקציה כלפי מטה - לתאים F3:F4 הלוגיקה נשמרה. והפונקציות התעדכנו באופן מותאם.
אבל רגע רגע…כיצד מבצעים את אותה "גרירה" המדוברת?
שאלה חשובה! והנה כל התשובות לכך:
שיטה 1 - שיטת הגרירה הידנית:
תחילה, יש ללחוץ על התא (המיועד לגרירה לתאים סמוכים) לחיצה בודדת ולסמן אותו.
לאחר מכן, יש למצוא את "ידית המילוי" (שנראית כמו סימן פלוס דק ושחור כזה +) בפינה השמאלית (בגיליון מימין לשמאל. בגיליון משמאל לימין - ידית המילוי נמצאת בפינה הימנית התחתונה) התחתונה של התא המסומן.
ואז, לגרור עם העכבר את ידית המילוי למטה / למעלה / ימינה / שמאלה.
הטכניקה זמינה הן באקסל והן בגוגל שיטס.
שיטה 2 - שיטת הלחיצה הכפולה:
שיטת הגרירה הידנית יכולה די לעייף שמדובר בגרירה כלפי מטה שמדובר בעמודה המכילה מאות / אלפי שורות…
בשיטה זו, התוכנה חוסכת מכם (בעזרת אוטומציה מובנית) את הגרירה האינסופית ומזהה אוטומטית גבולות נתונים בעמודה הסמוכה כדי לדעת היכן לעצור.
תחילה, יש לוודא כי קיימים נתונים בעמודה הצמודה.
לאחר מכן, יש ללחוץ לחיצה בודדת על התא ולמצוא את "ידית המילוי" וכנ"ל.
ולבסוף, יש ללחוץ לחיצה כפולה (Double-Click) על ידית המילוי. וזהו זה!
הטכניקה זמינה הן באקסל והן בגוגל שיטס.
שיטה 3 - קיצורי מקלדת:
במחשב, קיים קיצור דרך בינארי לפקודת "מלא מטה" או "מלא ימינה". זהו למעשה מימוש פקודתי של הלוגיקה היחסית מבלי לגעת בעכבר.
תחילה, יש לבחור את תא המקור ואת כל תאי היעד שמתחתיו.
לאחר מכן, יש להקיש Ctrl + D לגרירה כלפי מטה.
למילוי וגרירה ימינה (בגיליון משמאל לימין. עבור גיליון מימין לשמאל הגרירה היא שמאלה)יש להקיש Ctrl + R.
הטכניקה זמינה הן באקסל והן בגוגל שיטס.
שיטה 4 - העתקה והדבקה רגילה:
שיטה זו נחשבת מאוד גמישה. בעת ההדבקה, התוכנה מפעילה את אותה לוגיקה של הפניות יחסיות, אך מאפשרת למשתמש להדביק את הנוסחה לטווחים מפוזרים או לא רציפים בגיליו
תחילה, יש להעתיק תא בודד עם הנוסחה (Ctrl + C).
(וכאגב, הנה עוד טיפ קטן…תוכלו לבחור תאים מרובים שאינם סמוכים זה לזה באמצעות החזקת מקש Ctrl בעת הבחירה. נסו ותראו…)
לאחר מכן יש לבצע הדבק בתא/י היעד (Ctrl + V).
הטכניקה זמינה הן באקסל והן בגוגל שיטס.
שיטה 5 - מילוי חכם בגוגל שיטס:
כלי זה (Smart Fill) משתמש בבינה מלאכותית כדי להציע הצעות למילוי אוטומטי של דפוסים או נוסחאות תוך כדי הקלדה.
התהליך מאוד פשוט וברור. תחילה, יש להקליד את הנוסחה בתא אחד, והגוגל שיטס יציג "באדיבות" הצעה אפורה להשלמת הטור.
לחיצה על מקש Tab תקבל את ההצעה.
כאמור, טכניקה זו זמינה בגוגל שיטס בלבד.
וואו, 5 שיטות!!! נשמע מושלם ופנטסטי, נכון?
האמת, שכן…
ובכל זאת, אני רוצה להציג בפניכם עוד שיטה נוספת שאולי לא הכרתם…
לשיטה זו קוראים - "נוסחאות מערך" וניתן לומר כי זו השיטה "האולטימטיבית" ביותר…(בהמשך תבינו למה ומדוע…)
אז ראשית, איך זה נראה "בשטח"?
ובכן, אם אחזור לפונקציית xlookup שהודגמה לעיל. הנה עוד צילום מסך (מתוך הגיליון המצורף בלינק להלן):
וזהו כבר מבנה הפונקציות בתא F2:
קוד:
=ARRAYFORMULA(XLOOKUP(E2:E,B2:B4,C2:C4,""))בארגומנט הראשון הוזן E2:E. והרעיון המרכזי כאן הוא - שנוסחה בודדת "שולטת" בטווח שלם ומבטלת את הצורך בגרירה לחלוטין. כלומר, שבמקום "נוסחה לכל תא", יש רק נוסחה אחת בתא הראשי, והתוצאות "נשפכות" מטה בהתאמה. (כלומר, כהפניות יחסיות המתעדכנות אוטומטית. תאים / טווחים שהם מקובעים בנוסחה - בעת הגרירה "הוירטואלית" הקיבוע ישמר ולא יתבצע בתאים / טווחים אלו עדכון אוטומטי).
ולא זו בלבד…
שאנו מדברים על "חסכון" בגרירת תאים, המטרה היא לא רק עבור טווח הזמן המיידי (כלומר, בעת הזנת הנוסחה) אלא גם ובעיקר עבור טווח הזמן העתידי…
ואני מתכוון ל 3 יתרונות מרכזיים וחשובים וכדלהלן:
יתרון ראשון - שלמות ואמינות:
קרה לכם פעם שפתאום הבחנתם "במשהו מוזר" בתוצאת תא בודד / חישוב על טווח ואז שמתם לב שבטעות (כן ממש בטעות…) שכחתם לגרור את הנוסחה בתא / טווח מסוים או שמחקתם את הנוסחה (לאחר גרירה) מתא מסוים, ואז הבנתם מה גרם לחשבון "הלא נכון" והתקלה שקרתה פה?
נשמע לכם מוכר?
אז זהו, שבשיטת "נוסחאות מערך" לא ניתן למחוק בטעות תא אחד באמצע הטווח. ישנה נוסחה אחת ששולטת בכל הנעשה, מה שמבטיח עקביות ומקל על איתור שגיאות.
אם תנסו למחוק תא באמצע הטווח, תקבלו שגיאה!
כמובן שניתן עדיין למחוק את הנוסחה בתא המקור…אבל ההסתברות שתעשו זאת קטנה בעשרות מונים…ואם אכן זה יקרה השיבושים יהיו כל כך בולטים שתבחינו בזה מיידית.
יתרון שני - תחזוקה יעילה:
בשיטות הגרירה הקלאסיות, אם תרצו לשנות משהו בנוסחה (לתקן טווח / להוסיף קיבוע / לעטוף בנוסחה נוספת וכדומה) תצטרכו לבצע שינוי בתא העליון ביותר ושוב לבצע גרירה "חדשה". נכון?
אך בשיטת "נוסחאות מערך" אם צריך לשנות את הלוגיקה, משנים רק את התא הבודד שבראש הטור וזהו זה!
קלי קלות…אמרנו כבר?
יתרון שלישי - דינמיות מלאה:
יתרון זה הוא אספקט נוסף של היתרון השני כאשר הרעיון המרכזי הוא שנוסחאות אלו (מסוג נוסחאות "מערך") יכולות לגדול ולהתכווץ אוטומטית ככל שמוסיפים נתונים למקור, ללא צורך בגרירה ידנית חוזרת.
ציינתי לעיל שתוכלו להבחין כי הטווח שהוזן בארגומנט הראשון של פונקציית xlookup הוא E2:E.
המשמעות בציון E בסוף הטווח היא - עד סוף עמודה E וככל שעמודה זו תגדל ותגדל בעוד שורות נוספות. זה חוסך המון "כאבי ראש" וצורך לזכור בעתיד את גרירת הנוסחה בעת הוספת שורות לטווח הטבלה.
הערה חשובה: הפטנט הנ"ל יעיל וישים בגוגל שיטס. באקסל, מתקבלת שגיאה כאשר סוף הטווח "אינו מוגדר".
הפתרון לכך הוא לכתוב מספר שורה "מופקע" שכמעט אין סיכוי סביר שבעתיד הרחוק מימדי הטבלה יגיעו לשורה זו. למשל - E1000 או E4000 וכדומה…
והנה עוד נקודה חשובה שצריך לזכור והיא - שבהרבה נוסחאות ופונקציות יוחזרו (בעמודת נוסחת המערך באותן שורות שאין לצידן בעמודות הסמוכות ערך בתא החיפוש / ערכים שונים) שגיאות מגוונות שהנפוצה שבהן היא שגיאת N/A
אם זה קורה לכם, אין מקום לדאגה. פשוט תעטפו את הפונקציות (בפונקציית xlookup למשל תוספת זו ניתנת להזנה בארגומנט הרביעי שבה) בפונקציית ifna (או בפונקציית iferror הכוללת את כל סוגי השגיאות) בתוספת "" (בארגומנט השני) דהיינו להשאיר את התא ריק.
נוסחאות מערך שכאלו זמינות בגוגל שיטס או באקסל - גרסת 365.
ובגוגל שיטס (בלבד) יש צורך הכרחי בכל יישום שכזה לעטוף את הנוסחה / הפונקציה / הפונקציות בפונקציית arrayformula.
ולאחר הקדמה זו נתקדם לעיקרו של מאמר זה…
ראינו לעיל בפירושו של הגר"א זצ"ל בלשונו הטהור "אמנם כל האותיות של תיבת - מים הנגלות והנסתרות שוות בהן, דהיינו האות מ״ם, הגלויה היא מ' שמנינה ארבעים, והנסתרת היא גם מ'. וכן היו׳׳ד, האות הגלויה היא י' שמונה עשר, והנסתרות - וד , שהן ג״כ עשר. וכן המ״ם השני"
בצילום מסך שלפניכם (מתוך הגיליון המצורף בלינק להלן) תוכלו לראות כדוגמה את החשבון לאות "יוד" בעזרת פונקציות האקסל / שיטס שלב אחר שלב תוך כדי שימוש בטכניקת "נוסחאות מערך" שנתבארה לעיל באריכות.
שלב 1:
בתא D2 יש להזין את האות במילויה לבדיקה.
כאמור, בדוגמה כאן נכתבה האות י' (מתוך המילה מים) במילויה כלומר - יוד.
שלב 2:
זהו מבנה הפונקציה בתא E2:
קוד:
=(MID(D2,1,1))כולכם מכירים כבר את פונקציית mid הנפלאה. נכון? (אודותיה כתבתי במאמר כאן.)
במקרה שלנו, "אנו אומרים" לפונקציה (בארגומנט הראשון) תסתכלי במחרוזת הטקסט בתא D2 ותחלצי (בארגומנט השני) החל מהתו הראשון (בארגומנט השלישי) תו אחד בלבד.
התוצאה היא = י
שהרי התו "י" הוא התו הראשון במחרוזת הטקסט "יוד".
שלב 3:
זהו מבנה הפונקציה בתא F2:
קוד:
=XLOOKUP(MID(D2,1,1),A1:A,B1:B)טוב, אין כאן חידוש מיוחד. מדובר בפונקציית xlookup קלאסית לגמרי.
הפונקציה מחפשת את התו "י" בעמודה A ומחזירה בהתאמה את ערך הגימטריה (הרגילה) שמופיע בעמודה B.
התוצאה היא = 10 שהרי הגימטריה של האות י היא 10.
שלב 4:
זהו מבנה הפונקציה בתא G2:
קוד:
=ARRAYFORMULA(MID(D2,SEQUENCE(LEN(D2),1,2,1),1))במאמר כאן הסברתי באריכות על הלוגיקה הנפלאה בשילובן יחד של 3 הפונקציות mid & sequence & len.
במקרה שלנו פונקציית sequence יוצרת מערך מספרים שנראה כך:
2
3
ואז... פונקציית mid מחלצת בכל פעם תו בודד (כזכור, בארגומנט השלישי שבה הוזן 1) המתחיל במיקום (מתחילת המחרוזת) שהוחזר במערך שהוחזר על ידי פונקציית sequence.
כנגד המספר 2 - פונקציית mid מחזירה את התו "ו" מהמחרוזת "יוד".
כנגד המספר 3 - פונקציית mid מחזירה את התו "ד" מהמחרוזת "יוד".
שילוב נפלא וגאוני. כמו תמיד…
שלב 5:
זהו מבנה הפונקציה בתא H2:
קוד:
=ARRAYFORMULA(XLOOKUP(G2:G,A1:A,B1:B,""))המטרה בשלב זה היא להחזיר את הגימטרייה של האותיות / התווים שהוחזרו בשלב 4 באמצעות פונקציית xlookup.
וכאן מחכה לנו "ההפתעה" שנקראת "נוסחת מערך" החוסכת את הצורך בגרירת נוסחאות בהווה (דהיינו בעת הזנת הפונקציה) ובעתיד.
שימו לב שבארגומנט הראשון של פונקציית xlookup הוזן מערך תאים של G2:G (ולא תא בודד).
הפונקציה מחפשת כלפי כל תא במערך את מופעו בעמודה A ואז… הפונקציה מחזירה את ערך הגימטרייה בהתאמה מעמודה B.
והתוצאות:
כלפי התא לחיפוש G2 - הפונקציה מחזירה את הערך 6.
כלפי התא לחיפוש G3 - הפונקציה מחזירה את הערך 4.
ומה בקשר לכל התאים מתא G4 ומטה (עד לסוף העמודה)? הרי לא קיים בתאים אלו כל ערך לחיפוש?
נכון, הפונקציה היתה צריכה להחזיר (בעמודה H מתא H4 ומטה) במקרה שכזה את שגיאת N/A. ובצדק…
אך שכמו הזכור לעיל, ניתן לפתור את "הבעיה" הזו בתוספת "" בארגומנט הרביעי של הפונקציה המיועד "לבעיות" מסוג זה.
כזכור, בגוגל שיטס יש "לעטוף" את נוסת המערך בפונקציית arrayformula. זו בדיוק הסיבה שפונקציה זו הוזנה בשלב זה.
באקסל 365, אין צורך בפונקציה זו ויש להשמיטה ולמוחקה בשורת הנוסחאות.
שלב 6:
בשלב זה אנו נסכם את התוצאות של ערכי הגימטרייה שהוחזרו בשלב 5 באמצעות פונקציית sum פשוטה.
זו מבנה הפונקציה בתא I2:
קוד:
=ARRAYFORMULA(SUM(XLOOKUP(G2:G,A1:A,B1:B,"")))התוצאה היא - 10. שהרי 10 = 4 + 6
שלב 7:
כזכור בפירושו של הגר"א זצ"ל בכל אותיות "מים" (במילוי שלהן) האותיות "הנגלות" שוות לאותיות "הנסתרות" בערכן המספרי.
בשלב זה, אנו בודקים (בדוגמה שלנו עם האות יוד) האם ההחזר משלב 6 (של האותיות ו-ד) שווה להחזר משלב 3 (של האות י) באמצעות סימן השווה =.
זהו מבנה הנוסחה בתא J2:
קוד:
=F2=I2התוצאה היא = true
נכון מאוד, הערך 10 שווה לערך 10.
לסיום, הנה "הדובדבן שבקצפת"...
בצילום מסך שלפניכם (מתוך הגיליון המצורף בלינק להלן) תוכלו לראות את דברי רבינו הגר"א זצ"ל וכן את כל השלבים הנ"ל "מקופלים" בנוסחה אחת ארוכה כפי שאתם קוראי המאמר אוהבים לראות…
זהו מבנה הפונקציות בתא E2:
קוד:
=XLOOKUP(MID(D2,1,1),$A$1:$A,$B$1:$B)=ARRAYFORMULA(SUM(XLOOKUP(MID(D2,SEQUENCE(LEN(D2),1,2,1),1),$A$1:$A,$B$1:$B,"")))וזהו מבנה הפונקציות בתא E3:
קוד:
=XLOOKUP(MID(D3,1,1),$A$1:$A,$B$1:$B)=ARRAYFORMULA(SUM(XLOOKUP(MID(D3,SEQUENCE(LEN(D3),1,2,1),1),$A$1:$A,$B$1:$B,"")))התוצאה היא אחת = true !!!
נפלא מאוד!
ועד כאן לאקסל ולעולמן של הפונקציות…
מקווה שנהניתם מהטכניקה ומהלוגיקה של נוסחאות המערך שהוסברו והודגמו לאורכו של מאמר זה.
והנה טיפ קטן עבורכם קוראי המאמר - תְּתַרְגְּלוּ ותאמצו את הטכניקה בגיליונות האקסל / שיטס אצלכם…
זה נפלא וגאוני. ויותר מכך - זה יחסוך לכם הרבה זמן, "התעסקות מיותרת" וכאבי ראש נִלְוִוים…
לינק לגיליון שיטס הכולל סימולציה לביאורו של הגר"א זצ"ל תוך שימוש בטכניקת "נוסחאות מערך" - מצורף כאן.
- Next article in the series 'אקסלומדע'ס': אקסלומדע'ס פרק 78 ▪︎ כך תִּסְפְּרוּ תאים ריקים באקסל ובגוגל שיטס ▪︎ ואיך זה קשור לדברי רב סעדיה גאון בספרו "אמונות ודעות"?
- Previous article in the series 'אקסלומדע'ס': אקסלומדע'ס פרק 76 ▪︎ כמה מילים אודות פונקציית ADDRESS הנפלאה באקסל / גוגל שיטס ▪︎ "תלמיד חכם צריך שיהא בו אחד משמונה בשמינית"
