אקסל האשכול המרכזי לשאלות באקסל/גוגל שיטס

[ישראלי1]

פונקציה FILTER שמחזיר #REF! היות ויש 2 תוצאות והוא צריך 2 תאים לרשום את התוצאה
- והבעיה שבתא הסמוך יש בו טקסט (וא"א לפנות תא נוסף כי זה רץ על מאות תאים)

האם יש דרך לחבר את 2 התוצאות (כמובן שרק במקרה הנ"ל) לתא אחד- סוג של TEXTJOIN ולהגדיר שיתן כל פעם רק תוצאה אחת

 

[יעקב ויצמן]

שאלתך שאלה יפה,מעניינת ומאתגרת!

פונקציה FILTER שמחזיר #REF!

הארה: מדובר בגיליון שיטס.באקסל לעומת זאת השגיאה שתוחזר היא spill

היות ויש 2 תוצאות והוא צריך 2 תאים לרשום את התוצאה
- והבעיה שבתא הסמוך יש בו טקסט (וא"א לפנות תא נוסף כי זה רץ על מאות תאים)

להלן אציג את הבעיה בגיליון דוגמא המצורף ואציע מספר פתרונות.

האם יש דרך לחבר את 2 התוצאות (כמובן שרק במקרה הנ"ל) לתא אחד- סוג של TEXTJOIN ולהגדיר שיתן כל פעם רק תוצאה אחת

אקדים שני דברים:
א.פונקציית filter מתוכנתת כחלק מה-d.n.a שלה שתפרוס ("תשפוך") את כל טווח התא/ים הרלוונטי על פי התא/ים שהוזנו בפונקצייה.
ב.פונקצייה נבדלת מפונקציות כמו vlookup או xlookup וכדומה בכך שהיא מחזירה יותר מתוצאה אחת (אם יתר התוצאות רלוונטיות כמובן על פי התנאי/ים)
כך שניסיון לשנות "מהטבע שלה" ושתחזיר תוצאה אחת בלבד, הוא בגדר אותו משל שאמר ירמיהו הנביא(ירמיהו יג.כג) " הֲיַהֲפֹךְ כּוּשִׁי עוֹרוֹ וְנָמֵר חֲבַרְבֻּרֹתָיו...?"

כעת.אציג צילום מסך מתוך הגיליון המצורף ואסביר:

עמודות A וB
הן טבלת מקור.השם אברהם הוא השם היחיד שיש לו שני תוצאות של עיר מגורים ירושלים ובני ברק.

עמודה D
בתא d1 קיימת הפונקצייה:

=UNIQUE(A1:A)

ומתקיים כאן מיזוג נתונים.השם אברהם יופיע רק פעם אחת.

עמודה E
בתא e1 קיימת פונקציית filter .הפונקצייה נגררה כלפי מטה לתאים הסמוכים

=FILTER($B$1:$B,$A$1:$A=D1)

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

אוקיי.יש כאן בעיה?
אז איך מתקדמים לפיתרון בנושא?

עמודה F
ובכן.הפיתרון הכי פרקטי ואידיאלי (לפחות בעיניי...) הוא לעטוף את הפונקצייה בפונקציית textjoin
כך הפונקציות נראות בתא f1 למשל:

=TEXTJOIN(CHAR(10),,FILTER($B$1:$B,$A$1:$A=D1))

שים לב לפונקציית char עם קוד 10 (דיברתי על אפקט זה מספר פעמים בעבר).ובקצרה- קוד 10 מדמה גלישת טקסט ומעבר לשורה חדשה בתוך התא.
את זה אנו מזינים בארגומנט הראשון של textjoin
הרווחנו כאן שפונקציית filter משלימה את המשימה ומחזירה 2 תוצאות ומאידך הדבר לא פוגע בתאים סמוכים.

עמודה G
בתא g1 למשל מוזנות שלל הפונקציות האלו:

=INDEX(FILTER($B$1:$B,$A$1:$A=D1),RANDBETWEEN(1,COUNTA(FILTER($B$1:$B,$A$1:$A=D1))))

דיברתי לעיל על החזר תוצאה אחת בלבד...
אז ישנה פונקצייה מעניינת ושמה randbetween "מקבוצת וממשפחת" פונקציות הרנדומליות.
אם אחזור לסיום שאלתך.שאנו מבקשים רק תוצאה אחת.זה ניתן לביצוע אם יהיה כאן "משחק בינגו" תמידי.
כלומר שמבחינת פונקציית filter אין כאן "מעבר על הכללים".רק שגורם חיצוני מקבל את 2 התוצאות ומחזיר אחת מהן באופן אקראי לחלוטין.
יש לזכור כי בכל עת שיש שינוי בגיליון/פתיחת הגיליון מחדש יתבצע שוב "גורל מחדש"
אני יודע שבגיליון דוגמא שהצגתי.תוצאות משתנות בכל עת זה לא מה שפרקטי ורצוי למשתמש הגיליון..
אני הוספתי זאת רק כידיעה בעלמא שניתן לבצע זאת.
קומבינציית הפונקציות הנ"ל מאוד מאוד מעניינת .ובהזדמנות בל"נ ארחיב ואסביר על השימושים הרלוונטים שיכולים לבצע איתן יחד.וכן על הלוגיקה הנפלאה שטמונה כאן.

עמודה H
בתא h1 למשל מוזנות הפונקציות דלהלן:

=TRANSPOSE(FILTER($B$1:$B,$A$1:$A=D1))

פונקציית transpose תפקידה להפך תא/מערך ממאוזן למאונך או להיפך.
כאן בפיתרון זה,פונקצייה זו מחזירה פורסת את התוצאות לרוחב הגיליון.
מה אנו מרויחים כאן?
התשובה היא-שנכון שהדרך באורך העמודה חסומה על ידי תוכן של תא אחר שמפריע כאן בדרך..
אבל אם במקום זאת נשאיר עמודה ריקה כמו עמודה i בגיליון הנוכחי אז התוצאות יוחזרו במלואן על ידי פונקציית filter ולא תוחזר שגיאה.
כמובן שאם אנו חוזים מראש החזר של יותר מ2 תוצאות ,יש לפנות עמודות ריקות בסמיכות וכפי הצורך..


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


בהצלחה רבה!

 

[ישראלי1]

@יעקב ויצמן
ישר כח על התשובה שמוסבר בטוב טעם

לפי מה שכתבת לעיל - יש דרך בFILTER שנכניס מפריד בTEXTJOIN סוג של מקף -
ואז נחפש בעזרת FIND את מספר המיקום של המקף - הנ"ל
ובעזרת MID נשלוף רק את מה שצריך

 

[יעקב ויצמן]

@יעקב ויצמן
ישר כח על התשובה שמוסבר בטוב טעם

בשמחה רבה!

לפי מה שכתבת לעיל - יש דרך בFILTER שנכניס מפריד בTEXTJOIN סוג של מקף -
ואז נחפש בעזרת FIND את מספר המיקום של המקף - הנ"ל
ובעזרת MID נשלוף רק את מה שצריך

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

 

[y41]

שאלה במאקרו אקסל.
יש לי קובץ אקסל שאני מכניס בו נתונים ורוצה לשמור את הנתונים בקובץ CSV וששם הקובץ יהיה חתימת הזמן (תאריך+שעה).
יצרתי נוסחה בגיליון אחר שתתן לי את שם הקובץ (שמשתנה בכל פעם שיש רענון).
יצרתי פקודת מאקרו שאמורה לבצע את הפעולה של שמירת הקובץ בשם כקובץ CSV.
כאשר אני שולח את המאקרו לשדה בגיליון הפעיל הכל מצליח לי (אבל יש לי עם זה בעיה כי אני לא רוצה שבקובץ CSV יופיע השדה של שם הקובץ).
השאלה היא איך אני יכול לשלוח אותו לגליון עזר במקום לגיליון הנוכחי? להלן פקודת המאקרו:
Sub ùîéøä()
'

'

Dim strfolder As String
Dim strfilename As String

strfolder = "D:\11"
strfilename = Range("P7").Value
ActiveWorkbook.SaveAs Filename:=strfolder & "\" & strfilename, FileFormat:=22
End Sub

 

[מתן מאור]

שאלה במאקרו אקסל.
יש לי קובץ אקסל שאני מכניס בו נתונים ורוצה לשמור את הנתונים בקובץ CSV וששם הקובץ יהיה חתימת הזמן (תאריך+שעה).
יצרתי נוסחה בגיליון אחר שתתן לי את שם הקובץ (שמשתנה בכל פעם שיש רענון).
יצרתי פקודת מאקרו שאמורה לבצע את הפעולה של שמירת הקובץ בשם כקובץ CSV.
כאשר אני שולח את המאקרו לשדה בגיליון הפעיל הכל מצליח לי (אבל יש לי עם זה בעיה כי אני לא רוצה שבקובץ CSV יופיע השדה של שם הקובץ).
השאלה היא איך אני יכול לשלוח אותו לגליון עזר במקום לגיליון הנוכחי? להלן פקודת המאקרו:
Sub ùîéøä()
'

'

Dim strfolder As String
Dim strfilename As String

strfolder = "D:\11"
strfilename = Range("P7").Value
ActiveWorkbook.SaveAs Filename:=strfolder & "\" & strfilename, FileFormat:=22
End Sub

הצורה הזאת עובדת לי:

Sub שמירה()
'
'
    Dim WB As Workbook
    Set WB = Workbooks.Open("file path.xlsx")
   
    Dim strfolder As String
    Dim strfilename As String

    strfolder = "D:\11"
    strfilename = Range("P7").Value
    WB.SaveAs Filename:=strfolder & "\" & strfilename, FileFormat:=22

End Sub

ולהבא, עדיף לכתוב את הקוד באמצעות תיבת קוד, ככה הקוד קריא יותר ונעים לעין:

 

[I believe]

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

 

[יעקב ויצמן]

אשמח לעזרת הציבור: יש לי קובץ אקסל כבד וכשאני סוגר אותו חלונית השמירה מופיעה לי ללא השם של הקובץ אלא עם סימן שאלה, האם זה מורה על בעיה בקובץ, ואיך אפשר לסדר את זה?
תודה לכולם
צפה בקובץ המצורף 1647442

א.ראשית האם שלחצת על שמור, השינויים נשמרו?
ב.יתכן וקיים אופרטור "לא חוקי" בשם הקובץ.תנסה להיכנס למיקום הקובץ ולשנות את השם לטקסט פשוט כמו גיליון דוגמא .ואז לבצע שינוי ולנסות לסגור את הקובץ.האם הבעיה נמשכת?
ג.האם בקבצים אחרים באקסל מוזכר שם הקובץ בחלונית שציינת?
ובאגב.סיומת הקובץ היא xlsb או xlsx?

 

[I believe]

א.ראשית האם שלחצת על שמור, השינויים נשמרו?
ב.יתכן וקיים אופרטור "לא חוקי" בשם הקובץ.תנסה להיכנס למיקום הקובץ ולשנות את השם לטקסט פשוט כמו גיליון דוגמא .ואז לבצע שינוי ולנסות לסגור את הקובץ.האם הבעיה נמשכת?
ג.האם בקבצים אחרים באקסל מוזכר שם הקובץ בחלונית שציינת?
ובאגב.סיומת הקובץ היא xlsb או xlsx?

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

 

[יעקב ויצמן]

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

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

 

[I believe]

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

 

[יעקב ויצמן]

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

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

 

[רק שאלה!]

איך משנים באקסל את השעות מ - 13:00 ל 1:00 בלי לרשום 1:00 PM

 

[מתן מאור]

איך משנים באקסל את השעות מ - 13:00 ל 1:00 בלי לרשום 1:00 PM

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

1. שימוש בעמודת עזר והמרת התאריך לטקסט​

נניח שבעמודה A נכתבים השעות (אפשר לכתוב אותן בכל פורמט שהוא), בעמודה B נוסיף את הנוסחה הבאה:​

=SUBSTITUTE(SUBSTITUTE(TEXT(A1,"h:mmAM/PM"),"AM",""),"PM","")

הנוסחה ממירה לטקסט את השעה שמופיעה בעמודה A לפורמט שתיים עשרה שעות ואז מוחקת את התוספת AM/PM מהטקסט.​

החסרונות: ​

א. מצריך שימוש בעמודת עזר.​

ב. בעמודה שבה מופיעות השעות כתיקונן השעות מיוצגות כטקסט ועל כן אי אפשר לבצע עליהן מניפולציות חשבוניות.​

​

2. הסתרת הכיתוב AM/PM בשורה השנייה​

בעמודה שבה מיוצגים השעות בחר תבנית עיצוב של שתיים עשרה שעות.​

לאחר מכן עבור ללשונית "מותאם אישית" העמד את סמן העכבר כפי שמוצג בתמונה - הקש Ctrl + j ולחץ על אישור, על מנת להעביר את סימון ה- AN/PM לשורה השנייה בתא:​

לאחר מכן בחר באופציה "גלישת שורות" והשאר את התא בגודלו המקורי. כל המידע יישמר בתא וניתן יהיה לבצע פעולות חשבוניות על הנתונים שבתא, אבל סימון ה- AM/PM לא יוצג לעיני המשתמש מחוסר מקום:​

​

 

[רבישע נייעס]

קיים אקסל עם יומן הוצאות

 

[יעקב ויצמן]

קיים אקסל עם יומן הוצאות

טבלת אקסל הוצאות והכנסות להורדה: השוואה בין 8 קבצים (גם חינם)

ביום יום, בין העבודה, המשפחה והסידורים, הכסף פשוט זז – ואנחנו לא תמיד שמים לב לאן. פתאום יש חיוב שלא זכרנו, מינוס שקפץ, או תחושה כללית שהמשכורת נעלמת מהר מדי. זה קורה כמעט לכולם. האמת היא שלא צריך להיות רואה חשבון כדי לעשות סדר. קובץ אקסל, מסודר וברור,יכול לתת לכם שליטה אמיתית על מה שנכנס […]

financa.co.il

 

[יעקב ויצמן]

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

1. שימוש בעמודת עזר והמרת התאריך לטקסט​

נניח שבעמודה A נכתבים השעות (אפשר לכתוב אותן בכל פורמט שהוא), בעמודה B נוסיף את הנוסחה הבאה:​

=SUBSTITUTE(SUBSTITUTE(TEXT(A1,"h:mmAM/PM"),"AM",""),"PM","")

הנוסחה ממירה לטקסט את השעה שמופיעה בעמודה A לפורמט שתיים עשרה שעות ואז מוחקת את התוספת AM/PM מהטקסט.​

החסרונות: ​

א. מצריך שימוש בעמודת עזר.​

ב. בעמודה שבה מופיעות השעות כתיקונן השעות מיוצגות כטקסט ועל כן אי אפשר לבצע עליהן מניפולציות חשבוניות.​

​

2. הסתרת הכיתוב AM/PM בשורה השנייה​

בעמודה שבה מיוצגים השעות בחר תבנית עיצוב של שתיים עשרה שעות.​

צפה בקובץ המצורף 1648809​

לאחר מכן עבור ללשונית "מותאם אישית" העמד את סמן העכבר כפי שמוצג בתמונה - הקש Ctrl + j ולחץ על אישור, על מנת להעביר את סימון ה- AN/PM לשורה השנייה בתא:​

צפה בקובץ המצורף 1648810​

לאחר מכן בחר באופציה "גלישת שורות" והשאר את התא בגודלו המקורי. כל המידע יישמר בתא וניתן יהיה לבצע פעולות חשבוניות על הנתונים שבתא, אבל סימון ה- AM/PM לא יוצג לעיני המשתמש מחוסר מקום:​

צפה בקובץ המצורף 1648812​

​

ישר כח!
נהנתי לקרוא את הודעתך המפורטת ומנומקת היטב.
באמת נפלא.כל הכבוד!

 

[ש. ש.]

יש לי אקסל עם שני גליונות
אחד זה רשימת תורמים והשניה זה רשימת תרומות,
בגליון של רשימת תרומות אני רוצה לעשות בחירה מרשימה נפתחת של הרשימת תורמים אבל שיביא לי גם את השם וגם את המשפחה,
איך עושים א"ז ?

מצורף קובץ דוגמא

 

[יעקב ויצמן]

יש לי אקסל עם שני גליונות
אחד זה רשימת תורמים והשניה זה רשימת תרומות,
בגליון של רשימת תרומות אני רוצה לעשות בחירה מרשימה נפתחת של הרשימת תורמים אבל שיביא לי גם את השם וגם את המשפחה,
איך עושים א"ז ?

מצורף קובץ דוגמא

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

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

 

[יעקב ויצמן]

נהדר.ישר כח לך
רק אוסיף שניתן לבצע השוואה בדרך הפחות מוכרת..עם פונקציית vlookup
כפי שהארכתי וכתבתי בזמנו בלינק המצורף
https://www.prog.co.il/threads/רוצה-לעזור-לכם-באקסל.499341/post-13320802
ואז לבצע תנאי (כפי שכתבת עם פונקציית if) עם פונקציית ifna שעוטפת את vlookup.

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

ואנו רוצים שיוחזר לנו בטבלה צדדית אותן שמות שמופיעות בטבלה 1 וגם בטבלה 2.
ועוד טבלה צדדית שיוחזר לנו אותן שמות שמופיעות בטבלה 1 ואינן מופיעות בטבלה 2.
נשמע מסובך.נכון?
אז בלינק הנ"ל כתבתי פיתרון עם פונקציית vlookup.אבל כאן השתמשתי עם xlookup משתי סיבות:
1.כדי למנוע בעתיד שגיאות שיחולו אם עמודת החיפוש לא תהיה הכי ימנית וראשונה בטווח החיפוש.
2.היה כאן צורך להחזיר יותר מעמודה אחת.
אז נכון שהכל בר פיתרון לביצוע גם עם vlookup בעזרת קומבינציות כאלה ואחרות (וכתבתי על כך בעבר כאן בפורום באריכות רבה) אך הפעם לקצר באתי...כי גם כך מדובר בביצוע מעט מורכב...

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

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

=IF(LEN(G4)>0,"",VSTACK(A4:C4))

עכשיו תראו.אם נשאל את עצמינו,האם יכולתי להשתמש כאן בפונקציית counta(הסופרת תאים שמכילים גם טקסט שאינו ערך מספרי...זוכרים,נכון?) במקום פונקציית len?
התשובה היא:לא!!!
בואו נערוך ניסוי ואוכיח את זה...
(שתסיימו לקרוא עד הסוף.תראו שכיון שעמודה j מכילה פונקציות לכן פונקציית counta תחזיר תמיד 1.וזו לא התוצאה שאנו מבקשים בחשבון כאן...)
צירפתי כאן גיליון נפרד שמבודד מכל "רעשי הרקע" של הגיליון הנ"ל (של השוואות נתונים בעזרת xlookup) על מנת להקל על הבנת הדברים.

בעמודה B יש פונקציית if די פשוטה שמחזירה "כן" במידה והשם אברהם מופיע בתא שבעמודה A.
אם התנאי אינו מתקיים מוחזר תא ריק ככה "".
בעמודה D יש פונקציית counta שסופרת את התא שבעמודה B.אנו רואים שגם כלפי תא B2 וגם כלפי תא B3 התוצאה היא אחת.כלומר שתאים אלו מכילים ערך של טקסט כל שהוא ולכן מוחזר 1.
רגע רגע...אבל תא B3 הוא נראה ריק .אז למה הפונקצייה סופרת אותו?
לפני שנראה את התשובה לכך.נעצור כאן ותכף נשוב לנקודה זו.
בעמודה F יש פונקציית len שסופרת כמה תוים נכתבו בתא שמוזן בארגומנט.
אנו רואים שכלפי תא b3 התשובה היא 0 כלומר 0 תוים.
וכלפי תא b2 התשובה היא 2.(המילה כן שנכתבה שם מורכבת מ2 תוים...).

מה קורה פה בעצם עם תא b3 האם הוא מלא בערך כל שהוא או שהוא ריק?
התשובה היא שתא B3 אמנם מכיל ערך של טקסט אבל לא הכי "קונבנציונאלי".
כי מדובר בטקסט של פונקצייה ובמקרה כאן מדובר על פונקציית if.ומכאן השוני בתוצאות ההחזרים בין פונקציית counta לפונקציית len.

אנסה להסביר את "יסוד המחלוקת" (קצת לומדע'ס...) בין שתי הפונקציות הנ"ל עד כמה שניסיתי לחקור ולהעמיק בנושא זה.
אתחיל בפונקציית len-פונקצייה זו סופרת מספר תוים של טקסט בתנאי שמדובר בטקסט פשוט שכתוב "בפועל" ולא רק בכח.
פונקציית len מתייחסת בכל הקשור "למלל" של פונקציה רק "כהיכי תימצי" להחזר טקסט כל שהוא מהפונקצייה.
כיון שעדיין מדובר פה רק בטקסט בגדר "בכח בלבד" לכן מבחינת פונקציית len הפונקצייה כאילו איננה ומספר התוים שבתא נותר על 0.
רק כאשר מוחזר טקסט "ממשי" שיצא לפועל(חשוב להדגש!שימו לב טוב.הפונקצייה מחשבת גם רווחים למרות שאינם נראים אך הם "הוקלדו" עם מקש רווח במקלדת ולכן הם עדיין נחשבים כטקסט "ממשי" במובן מסוים) בעמודה B כמו בתא b2 אז,הפונקצייה סופרת את מספר התוים ומחזירה 2 (כלפי תא b2 הנל)

מנגד,פונקציית counta "שבודקת" את תוכן התא ומחזירה תוצאה של 1 או 0,-מתייחסת לתא שנכתב בו פונקצייה, כטקסט לכל דבר וענין.
כי נכון שפונקצייה היא "היכי תימצי" לערכים שונים שיוחזרו לתא שנכתבה בו הפונקצייה.אך מלל של פונקצייה- כבר עתה הוא טקסט כל שהוא (מי שמפקפק בכך שיסיר את סימון השווה בלבד בתחילת הפונקצייה...אז רק סימון השווה עושה פה את ההבדל בין טקסט "נסתר" לטקסט "גלוי"...תחשבו על זה...)
ואם כן יש לדון את "הטקסט הנסתר" (ובאגב.ק"ו שמדובר בטקסט של החזר שגיאה כמו n/a או value וכדומה) כתוכן כל שהוא שנכתב בתא.ומימלא אין לתא דין של "ריק מתוכן"
זו הסיבה שגם בתא D3 הפונקצייה מחזירה תוצאה 1.
מעתה יותר מובנת הסיבה שגם פונקצייה זו מתייחסת לרווחים כטקסט ותא שכזה נספר 1.(במובן מסוים הדברים הם ק"ו.לאור מה שהוספתי בסוגריים לעיל כלפי פונקציית len)
אם נרצה "לשפוט" על פי מה שעינינו רואות בלבד בתא...

זהו יהיה פלונטר, כי גם ההגדרה של "עינינו רואות" נתונה לשיפוט...

זהו עד כאן!
מקווה שההסבר מובן וברור ומקווה שנהנתם במסע הזה...
זהו עוד הצצה קטנה ומחקר "מולקולרי" (בהשאלה כמובן...)בפענוח הD.N.A של שתי הפונקציות הנפלאות שנדונו כאן.
אם יש שאלות/הארות/הערות,אשמח לשמוע.
בהצלחה!