הנושאים החמים
תמונת מקור - Leonardo AI
מאמר זה הִנּוֹ המשך ישיר למאמר שפורסם בלינק כאן, בו הוסבר אודות השיטות השונות להסרת רווחים בתאי האקסל/שיטס ובעיקר על פונקציית trim.
ומכאן, התחלתו של מאמר זה…
לעיתים אני נשאל, באלו מקרים/סיטואציות ביום יום- קיימת נחיצות הכרחית להשתמש בפונקציית trim?
והאם ניתן "לדלג" על התהליך ולוותר על עמודות עזר?
התשובה לכך היא בדוגמא קטנה עם פונקציית sumif כדלהלן.
תראו בצילום מסך מתוך הגיליון המצורף להלן בלינק.
בטווח תאים A2:A6 כתובים שמות- משה/אברהם.
השם אברהם נראה זהה בכל התאים…
אבל זהו, זה רק נראה כך…
בתא A5 וכן בתא A6 קיים רווח מיותר על פי "כללי" פונקציית trim.
על מנת לאמת זאת ולהבחין בכך ויזואלית, הוספתי בטווח C2:C6 בדיקה על ידי פונקציית len.
בטווח התאים B2:B6 קיימים סכומי חוב לשמות מעמודה A.
כעת אנו רוצים לדעת מהו סכום הכולל (חוב) של השם "אברהם"?
מה הבעיה? כולנו מכירים את פונקציית sumif…
נכון מאוד!
ולמען האמת ניתן להשתמש בעוד הרבה פתרונות שונים כמו פונקציות filter+sum או sumproduct למשל…
אבל גם אני ביצעתי זאת בעזרת פונקציית sumif המוכרת, הנפוצה והידועה.
בתא F2 הצבתי את פונקציית sumif כאשר הקריטריון הוא השם "אברהם".
קוד:
=SUMIF(A2:A6,"אברהם",B2:B6)התוצאה היא 500.
אבל רגע רגע…הרי אם נחשב זאת "בראש" לכאורה, התוצאה תהיה 1000 ולא 500?
(כלומר 300+200+100+400)
מה קרה פה בדיוק?
התשובה היא שפונקציות האקסל/השיטס הקשורות לתנאים (קריטריונים) מאוד "קפדניות" במהותן.
וכיון שבדוגמא כאן התנאי היה לחפש את השם "אברהם" שנכתב בארגומנט ללא רווחים, אז כך בדיוק התוצאות!
בתנאים לוגים ההחזר הוא true או false, אמת או שקר, כלומר האם התקיים התנאי או לא.
והואיל וכזכור לעיל - בתאים A5:A6 ישנם רווחים, החזר הפונקציה הוא false.
עכשיו תראו, במקרה כאן היה קל לאתר את מקור התקלה קרי הרווחים בתאי השיטס לאחר שכתבתי לכם את זאת מפורשות וגם כי מדובר בטבלה ממש קטנטונת…
אך כאשר מדובר בטבלאות המתפרסות על פני שורות רבות הקושי לאתר את הרווחים קשה שבעתיים…
ואני יודע שכעת אתם אומרים לעצמכם, הרי ניתן להשתמש בפונקציית trim בתוך עמודת עזר ובזה תם העניין…
אתם צודקים! נכון מאוד!
אבל במאמר זה אני רוצה להציג לפניכם עוד שתי שיטות נוספות כפתרונות אופציונליים. בהן ניתן לדלג ולוותר על עמודות עזר.
פתרון 1 - בעזרת כוכבית:
זו הכוכבית *
הכוכבית היא מאוד קטנה ופיצפונית, אבל יש בה טריק נפלא מאוד.
הכוכבית "אומרת" לפונקציות האקסל/שיטס. אל תהיו קפדניות "יותר מידי"!
את הכוכבית מציבים צמוד לקריטריון עם אופרטור & (אנד) לפני או אחרי התנאי או גם וגם…
למעשה, הכוכבית "אומרת" לפונקציות האקסל/שיטס אם הטקסט שמופיע בתנאי נמצא בתא/י האקסל/שיטס דיינו בכך…
נא לא להתייחס לכל מה שמעבר לכך…
וגם אם ישנם תווים נוספים כמו אותיות / רווחים לפני אותיות החיפוש או אחריה, אל תקפידו על כך!
כך זה נראה בפועל בפונקציית sumif בתא F4.
קוד:
=SUMIF(A2:A6,"*"&"אברהם"&"*",B2:B6)שימו לב שהכוכבית כאן נוספה הן לפני המילה "אברהם" והן אחרי המילה.
המשמעות היא כאמור, לא להתייחס לרווחים (אם ישנם) לפני/אחרי המילה אברהם.
בואו נראה דוגמא נוספת שיכולה להיות מאוד פרקטית ביום יום.
בצילום מסך הנ"ל ישנם שמות שונים בטווח התאים A11:A14.
ובטווח התאים B11:B14 ישנם סכומי חוב שונים.
וכעת. אם נרצה להשתמש בפונקציית vlookup לקבל החזר של עמודת סכום החוב לפי מילת חיפוש- ברור, זה אפשרי!
עכשיו תראו את מבנה הפונקציה בתא F11.
קוד:
=VLOOKUP("אברהם",$A$11:$B$14,2,0)הפונקציה מחזירה שגיאת n/a…
כי נכון שבתא A12 "מוזכרת" המילה אברהם. אך שם כתובים גם תווים נוספים (רווחים וכן המילה כהן)
ונשאלת השאלה. למה התכוון בעל הגיליון בחיפוש אחר תנאי שנכתב באופן לא כזה מדויק?
האם זה בכוונה תחילה?
אז זהו שכן…
נתאר לעצמנו עמודת שמות המתפרסת על פני (עשרות/מאות/אלפי) שורות רבות, בעל הגיליון זוכר באופן ברור שקיים השם "אברהם" אך הוא לא זוכר בבירור אם השם נכתב כאברהם או כאברהם כהן או ככהן אברהם.
מה הפתרון לכך? הרי מילת/מילות החיפוש בארגומנט הראשון של הפונקציה צריכות להיכתב באופן מדויק?
אבל הנה הפתרון, הרי "בכלל מאתיים מנה" ואם כן השם אברהם נכתב כך או כך…
ואם רק נוסיף כאן את הכוכבית, פונקציית vlookup תאתר את השם…
תראו את הפתרון באופן שמתורגם למעשה בתא F13.
קוד:
=VLOOKUP("*"&"אברהם"&"*",$A$11:$B$14,2,0)נפלא מאוד! נכון?
ועד כאן לפתרון/טריק הראשון.
פתרון 2 - בעזרת פונקציית regexmatch:
פונקציה זו זמינה בגוגל שיטס בלבד!
מהותה של פונקציה זו היא אחת - לבחון/לבדוק האם טקסט מסוים קיים בביטוי "רגולרי".
הפונקציה מחזירה בכן ולא כלומר- true או false.
הפונקציה מורכבת משני ארגומנטים בלבד.
הארגומנט הראשון - text: כלומר הטקסט שיש לבדוק מול הביטוי הרגולרי.
הארגומנט השני - regular_expression: כלומר הביטוי הרגולרי לבדיקת הטקסט מולו.
תראו דוגמא בתא D2 מתוך הגיליון המצורף בלינק להלן.
קוד:
=REGEXMATCH(A2,"אברהם")מה שמיוחד בפונקציה זו הוא העיקרון שציינתי לעיל- שכאשר טקסט מסוים אכן קיים בביטוי "רגולרי".
הפונקציה מתעלמת מכל היתר ומחזירה true.
הדוגמא לעיל נסובה על פונקציית sumif.
ואם נרצה לשלב את פונקציית regexmatch בתוך פונקציית sumif התוצאה תהא כפי המתואר בתא F6.
קוד:
=ARRAYFORMULA(SUMIF(REGEXMATCH(A2:A6,"אברהם"),TRUE,B2:B6))התוצאה היא 1000 כלומר שהתקיים התנאי (הוחזר true) בכל 4 התאים שהוזכר בהם הטקסט "אברהם".
נקודה חשובה:
מסיבות כאלו ואחרות, בגיליון גוגל שיטס יש לעטוף את הפונקציות הנ"ל בפונקציית arrayformula (הזמינה בגוגל שיטס בלבד)
עד כאן!
נהניתם? תאמצו את הטכניקות לשימושכם האישי בחיי היום יום!
לינק לגיליון שיטס הכולל דוגמאות לשימוש בכוכבית ופונקציית regexmatch - מצורף כאן.
- Next article in the series 'אקסלומדע'ס': אקסלומדע'ס פרק 22 ▪︎ כך תְּחַשְּׁבוּ בקלות את ס"ך המילים בתא אקסל/גוגל שיטס ▪︎ ואיך זה קשור לברכת כהנים?
- Previous article in the series 'אקסלומדע'ס': אקסלומדע'ס פרק 20 ▪︎ כל השיטות להסרת רְוָוחִים (SPACES) באקסל/גוגל שיטס ▪︎ תכירו, זו פונקציית TRIM הנפלאה!
