הנושאים החמיםתמונת מקור - Leonardo AI
חג החנוכה הגיע ובא…
וכולנו מכירים/זוכרים את שיר הילדים על אותו סביבון שהלך לו והסתתר…
היכן הוא, היכן?
לאן הוא נעלם?
אולי הוא פה?
אולי הוא שם?
כעת, בואו תראו איך זה קשור לאקסל ולעולמן של הפונקציות…
יומן מסע - חלק א:
לפניכם, מצורף צילום מסך מתוך הגיליון המצורף להלן בלינק.
בשורות 2 עד 6 ישנה טבלת מקור עם ערכים אנונימיים (כמובן…) המתפרסת על עמודות A:C.
זו באמת טבלה קטנה ופצפונת ולהמחשה בלבד…
בשטח / למעשה - טבלה כזו יכולה להכיל מאות/אלפי שורות וממספר רב של עמודות…
ואז שאלתי את עצמי. רגע רגע...בגיליון אמת המכיל כמות עצומה של עמודות ושורות.-הרי אם מישהו יחליט לחפש מידע כלשהו ,המלאכה תהא רבה ומייגעת..
אז נכון שתאמרו אבל יש פונקציות לכך כמו filter או xlookup ועוד...ומה הבעיה כאן?
אבל העניין הוא, שכאשר מדובר בבעל עסק (למשל) וכעת הוא מעוניין לחפש "שם לקוח" (עבור תא החיפוש לפונקציה כזו ואחרת) מסויים ולא זכור לו שם משפחתו (בכלל או באופן מדויק)...
והרי לכולנו ידוע כי פונקציות החיפוש באקסל/שיטס "קפדניות ודייקניות" מאוד.
וכאשר לא נמצא ערך התואם במדויק לתא החיפוש- תוחזר שגיאת n/a(בדרך כלל) או value(בחלק מהפונקציות).
בסיטואציה כזו משתמש הגיליון שואל בייאוש:
מהו שם הלקוח?(המדויק)
היכן היא השורה שאני מחפש?...
היכן היא מסתתרת?...
(זוכרים את הטקסט הזה משיר הסביבון בפתיחה?)
צריך למצוא כאן פתרון לחיפוש בפורמט "מכיל טקסט x".
אציין, כי בעבר כתבתי במאמר כאן 2 פתרונות נפלאים לבעיה זו באמצעות טריק של הוספת כוכבית לפני ואחרי הערך לחיפוש, וכן בעזרת פונקציית regexmatch הנפלאה.
אך כעת נלמד שיטה נוספת וגאונית לכך.
זהו מבנה הפונקציות בתא A13:
קוד:
=SORT(FILTER(A2:C6,ISNUMBER(SEARCH(A11,A2:A6))))אני אסביר את כל הטוב שיש כאן לפנינו.
בעבר כתבתי לא אחת, שהחוקיות באקסל / שיטס שכאשר יש סוגריים בתוך סוגריים, יש "לפתור" (כלומר כך זה עובד בפועל…) את הסוגריים הפנימיים ביותר, ואז "לצאת החוצה" וכו'.
ממש כמו בחוקי המתמטיקה!
אז בואו נדבר ונכיר את פונקציית search:
באופן כללי, תפקידה של פונקציה זו היא - לאתר מחרוזת טקסט אחת בתוך מחרוזת טקסט שניה ולהחזיר את המספר של המיקום ההתחלתי של מחרוזת הטקסט הראשונה החל מהתו הראשון של מחרוזת הטקסט השנייה.
הפונקציה בנויה מ 3 ארגומנטים.
הארגומנט הראשון find_text: הוא חובה, הטקסט שברצונך לאתר.
הארגומנט השני within_text: גם הוא חובה, הטקסט שבתוכו אתה מחפש את הערך של הארגומנט find_text.
הארגומנט השלישי start_num: זהו ארגומנט אופציונלי. מספר התו בארגומנט within_text שבו ברצונך להתחיל בחיפוש.
בצילום מסך הנ"ל תוכלו להבחין כי פונקציית search מחפשת את הטקסט "אברהם" (הכתוב בתא A11) במחרוזות שכתובות בטווח A2:A6.
התוצאות במקרה שלנו הן:
1 (כלפי תא A2)
1 (כלפי תא A3)
1(כלפי תא A4)
כי הטקסט "אברהם" מופיע ב 3 תאים בטווח זה, ובהם הטקסט "אברהם" מופיע מהתו הראשון במחרוזת.
את פונקציית search "עוטפת" פונקציית isnumber.
פונקציה זו בודקת דבר אחד בלבד, האם הערך שהוזן (או הוחזר) בארגומנט שבה- הוא מספר?
כן או לא? true או false?
נו, מה אתם אומרים?
פונקציית search מחזירה 3 פעמים את הספרה 1.
הספרה 1 היא מספר?
כן! ברור שכן…
מצוין, נתקדם עוד שלב.
מי שעוטפת את 2 הפונקציות האלו היא פונקציית filter.
ומה אנו מבקשים מפונקציית filter?
להחזיר ממערך A2:C6 את אותן שורות שהתקיים בהם התנאי.
ומהו התנאי?
התשובה היא- שהתא בעמודה A הוא אכן מספר.
אה רגע…פונקציית isnumber "ספרה" 3 תאים כאלו. נכון?
אלו בדיוק השורות שמוחזרות כעת על ידי פונקציית filter.
ומה עם מיון מהקטן לגדול (על פי עמודת השם)?
אין בעיה…את כל הטוב הנ"ל אנו עוטפים בפונקציית sort הנפלאה שתפקידה לבצע מיון.(בהזדמנות אכתוב בהרחבה אודותיה…ועוד חזון למועד)
וכעת, רוצים לדעת מהו המנגנון שגורם לכך שההחזר יתבצע גם בסגנון חיפוש של "מכיל x"???
התשובה היא: כי כפי שכתבתי לעיל התנאי של filter מתקיים על כל מה שמוחזר true על ידי isnumber.
ופונקציית search תמיד תחזיר מספר (למעשה הכוונה "למיקום" אותו תו/מחרוזת מתחילת הטקסט)גם שמדובר בטקסט חלקי…
זה באמת נפלא וגאוני!
וואו!
יומן מסע - חלק ב:
תנו לי להראות לכם "דבר פלא"...
בגיליון המצורף ישנה הפתעה נוספת.
אם מישהו מכם ינסה לבצע ניסוי בגיליון "ראשי"
מהו הניסוי? תשאלו…
אז הניסוי הוא להשאיר תא ריק בתא החיפוש - כלומר תא A11.
התוצאה שתתקבל היא יבוא כל נתוני גיליון המקור ככתבם וכלשונם.
וכאן הבן שואל ומקשה:
מה קורה כאן?
אם ביקשנו כתנאי לפונקציית filter שתחפש ערך שכתוב בתא A11.והתא הזה הוא ריק היה לפונקציה להחזיר שגיאת n/a או value
ומדוע ולמה כל התוצאות של גיליון המקור מוחזרות?
(למי שתוהה אין כאן שייכות לרווחים "מקובלים" או "מיותרים" כך שפונקציית trim לא תשנה מאומה מהתוצאה..נסו ותראו)
מי שינסה לבצע עבודת מחקר מקיפה יגלה בסוף (ואני כבר יגלה לכם בסוד את המסקנות...) שמי שאחראית כאן על "המחדל" הזה היא פונקציית search.
אם נעשה רגע "ZOOM IN" לפונקציית search שהיא לבדה (מבודדת "מכל רעשי הרקע הנ"ל)נגלה שכאשר תא החיפוש ריק .הפונקציה מחזירה את המספר 1.
(וזו הסיבה שפונקציית filter מתזירה תוצאות..כי התנאי הוא שתוצאת search תהיה מספר.כדי שפונקציית isnumber תחזיר true .והנה התקיים כאן התנאי...)
סיכמתי זאת בצילום מסך שלפניכם (מתוך הגיליון המצורף להלן בלינק).
ניתן לראות בשטח הירוק שכאשר נכתב בתא חיפוש ערך שלא קיים במחרוזת- מוחזרת שגיאה של value.
אבל בשטח הצבוע בתכלת בתא C16 מוחזרת הספרה 1.למרות שתא A16 ריק לחלוטין!
השאלה הגדולה כאן היא - מדוע?
הרי התו הראשון בתא B16 הוא "א" (של אברהם) .למה הפונקציה מתייחסת לתו הריק כראשון?
מה ההיגיון כאן???
פלא פלאים!!!
יומן מסע - חלק ג:
טוב תראו, השאלה כאן העסיקה אותי רבות...המשכתי לבצע עוד כמה עבודות מחקר וניסויים.
וברשותכם.אשתף אתכם "במהלך" להבנת כל הסוגיא פה.
וראשית, תראו שוב את הצילום מסך (הכולל תוספת על הנ"ל).
ובכן. שאנו דנים על תא ריק מה מהותו ומה דינו? אז תלוי את מי שואלים…
ולא…
ממש לא התכוונתי כאן לפילוסופיה של פָּרְמֶנִידֶס היווני ובטענה הלוגית "שהיש ישנו והאין איננו..." למרות הדיו הרב שנשפך על הטיעון הזה…
אבל אם נשאל את פונקציית len כמו בתא B27 .אז מבחינתה התוצאה 0.
כי ריק הוא ריק "כפשוטו" והרי אין שום תו/טקסט בתא A27.
וכאשר נשאל את פונקציית isblank כמו בתא B28 אודות התא ריק.
התשובה תהיה true .כי תא A27 הוא אכן ריק וכנ"ל.
אך כל תא ריק הוא בפוטנציאל (לפחות) לכתיבת טקסט ותווים.
עם זה כולכם מסכימים…
נכון?
כמה תווים יכולים "להידחס" /להכיל תא בודד?
לא ניסיתי זאת…
אך לפחות על פי הנאמר בלינק כאן .תא אחד יכול במקסימום להכיל 32.767 תווים!
תראו משהו מעניין בשורה 19, פונקציית search מחזירה תוצאה 7 לאחר חיפוש של האות י.
זאת למרות הרווח הקיים שם.
המסקנה שהסקתי מכך היא שפונקציית search עם D.N.A מאוד מיוחד.
ואני מתכוון לכך, שהפונקציה מתייחסת "למרחב" ריק בתא כתו.
ומבחינת הפונקציה כל תא כזה מכיל 32.767 "מרחבים" פוטנציאלים לטקסט.
אבל...דרכה של הפונקציה (ללא הארגומנט השלישי) היא להחזיר את התוצאה הראשונה שהיא "פוגשת".
זו בדיוק הסיבה שבשורה 16 יוחזר המספר 1.כי זה שנכתב תו ראשון לכאורא האות א של אברהם אינו סותר את הפוטנציאל של תא החיפוש.
"המרחב" בתא שתופס את מקום התו של הריק הוא אותו מרחב על פי הפונקציה.
ומה קורה כשמוסיפים ארגומנט שלישי???
אנו למעשה מגבילים את הפונקציה מהיכן היא תתחיל לחפש את תכולת תא החיפוש בתוך המחרוזת.
בשורה 22 למשל.ביקשנו מהפונקציה לבצע חיפוש מתו 2 ואילך.
מה כתוב בתו 2 ?
גם כאן, תלוי את מי שואלים…
אם נשאל "תינוק מבית הספר", אז התשובה תהא האות ב של המילה אברהם.
אבל מבחינת הפונקציה זה אינו סותר את זה שבתא החיפוש היה תא ריק.
כי האות ב היא בסך הכול "הוצאה לפועל" של אותו "מרחב" של התו הריק. ולכן התוצאה תהיה 2.
שימו לב שכל הלומדע'ס הזה יכול להתקיים רק כאשר התא ריק לחלוטין.
במידה מסוימת ניתן לומר כאנלוגיה שתא זה נחשב "כחומר חסר צורה" .כי כל עוד הוא ריק הרי שנחשב כפוטנציאל לכל תו שרק נחליט.
אך כאשר נכתב בו טקסט הרי שכבר התא איבד "מהאופי הסגולי" שבו לקבל כל תו.
ואין לנו אלא הנכתב במפורש בתא.
ולכן בשורה 12 למשל.הפונקציה מחזירה שגיאת value .כי הפונקציה רצה על כל המחרוזת מתחילתה ועד סופה .ולא מצאה את רצף הטקסט הזה שנקרא "משה".
ההסבר הנ"ל הניח את דעתי(לפחות)...
ואכן זו הייתה הזדמנות עבורי לצאת למסע "הרפתקני" ומהנה בעולמה של פונקציית search המופלאה והגאונית.
ועד כאן להיום…
מקווה מאוד שגם אתם הקוראים נהניתם מכל הלוגיקה הנפלאה במאמר זה סביב פונקציית search הגאונית.
תלמדו את האקסל!
תחקרו את האקסל!
זה באמת מהנה ומשתלם…
אני מאחל לכם ולכל בית ישראל-
חג חנוכה שמח!
לינק לגיליון שיטס הכולל הסבר ודוגמאות לפונקציית search הנפלאה - מצורף כאן.
- Next article in the series 'אקסלומדע'ס': אקסלומדע'ס פרק 30 ▪︎ סוגיית "שניים אוחזין בטלית" על קצה המזלג ▪︎ ואיך זה קשור למתמטיקה ולאקסל/גוגל שיטס?
- Previous article in the series 'אקסלומדע'ס': אקסלומדע'ס פרק 28 ▪︎ תכירו, זו טכניקת ה- "Double Negative" בנוסחאות האקסל ▪︎ פשוט נפלא וגאוני!
