נהדר.ישר כח לך
רק אוסיף שניתן לבצע השוואה בדרך הפחות מוכרת..עם פונקציית 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 של שתי הפונקציות הנפלאות שנדונו כאן.
אם יש שאלות/הארות/הערות,אשמח לשמוע.
בהצלחה!
אקסל האשכול המרכזי לשאלות באקסל/גוגל שיטס
financa.co.il
