עזרה באקסל הפונקציה FILTER בגוגל שיטס

[xl3391]

או 'להכין' אותה באקסל ע"י LAMBDA (פונקציה שמגדירה פונקציות)
הוספת שם REGEXMATCH ולשייך אליו את הפונקציה:

=LAMBDA(rangeToSearch,valToFind,IF(IFERROR(FIND(valToFind,rangeToSearch),0)>0,TRUE,FALSE))

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

 

[אברהם1111]

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

זה הקטגוריה
=arrayformula(FILTER('CC1'!A:AP,('CC1'!N:N="הרר יושע הערש פליישמאן היו")))
והקטגוריה שזה לא קולט זה הר"ר יושע הערש פליישמאן הי"ו יש לנו 2 גרשיים וזה לא קולט

תודה מראש

 

[יעקב ויצמן]

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

זה הקטגוריה
=arrayformula(FILTER('CC1'!A:AP,('CC1'!N:N="הרר יושע הערש פליישמאן היו")))
והקטגוריה שזה לא קולט זה הר"ר יושע הערש פליישמאן הי"ו יש לנו 2 גרשיים וזה לא קולט

תודה מראש

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

 

[צביזון]

או 'להכין' אותה באקסל ע"י LAMBDA (פונקציה שמגדירה פונקציות)
הוספת שם REGEXMATCH ולשייך אליו את הפונקציה:

=LAMBDA(rangeToSearch,valToFind,IF(IFERROR(FIND(valToFind,rangeToSearch),0)>0,TRUE,FALSE))

צפה בקובץ המצורף 1441932
אחרי ההגדרה הזאת אמור להתאפשר להשתמש בה כמו פונקציה רגילה.
הבעיה שאפשר לעשות זאת רק בתוך הקובץ ולא כהגדרת פונקציה כללית לאקסל.

1) איך אני מגיע לחלונית הזו ששם אני מכניס את זה?

2) ואח"כ תהיה לי בעיה 2 לפתור, שכאשר זה יורד מגוגל שיטס, כבר ברגע פתיחת הקובץ זה דופק את הנוסחה, ואחרי שהנוסחה דפוקה, לא בטוח שיעזור להכניס את זה בשביל שהקובץ יחזור לתיקנו.
אבל את זה אנסה, אחרי שאדע התשובה על נקודה 1.

תודה רבה רבה.

 

[xl3391]

1) איך אני מגיע לחלונית הזו ששם אני מכניס את זה?

בכרטיסיה נוסחאות> הגדר שם
בצהוב את שם הנוסחה
בירוק את הנוסחה LAMBDA הנ"ל ואישור

2) ואח"כ תהיה לי בעיה 2 לפתור, שכאשר זה יורד מגוגל שיטס, כבר ברגע פתיחת הקובץ זה דופק את הנוסחה, ואחרי שהנוסחה דפוקה, לא בטוח שיעזור להכניס את זה בשביל שהקובץ יחזור לתיקנו.
אבל את זה אנסה, אחרי שאדע התשובה על נקודה 1.

נכון. לכן רשמתי

הבעיה שאפשר לעשות זאת רק בתוך הקובץ ולא כהגדרת פונקציה כללית לאקסל

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

 

[צביזון]

בכרטיסיה נוסחאות> הגדר שם
בצהוב את שם הנוסחה
בירוק את הנוסחה LAMBDA הנ"ל ואישור
צפה בקובץ המצורף 1442277


נכון. לכן רשמתי

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

במנהל השמות זה נראה כעת כמו התמונה להלן, אבל זה לא עוזר.

ואת הנוסחה, תיקנתי על ידי שהעתקתי אותה מגוגל שיטס והדבקתי אותה שוב בתא שבו היא הייתה.

 

[צביזון]

אגב, הנוסחה שהעתקתי מגוגל שיטס נראית כך:

=FILTER('גיליון לחיפוש'!A2:Q30000,(LEFT('גיליון לחיפוש'!C2:C30000,LEN(C2))=C2)*(REGEXMATCH('גיליון לחיפוש'!D2:D30000,D2))*(REGEXMATCH('גיליון לחיפוש'!E2:E30000,E2))*(REGEXMATCH('גיליון לחיפוש'!F2:F30000,F2))*(if(and(G2="",H2=""),1,('גיליון לחיפוש'!G2:G30000=G2)+('גיליון לחיפוש'!H2:H30000=G2)))*(REGEXMATCH('גיליון לחיפוש'!I2:I30000,I2))*(REGEXMATCH('גיליון לחיפוש'!J2:J30000,J2))*(REGEXMATCH('גיליון לחיפוש'!K2:K30000,K2)))

אולי יש כאן עוד נוסחאות שאקסל לא מכיר?

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

 

[צביזון]

אבל אם אני שם את הנוסחה בגליון חדש, ששם הוא כן מדביק אותו כנוסחה, עדיין הוא יראה שגיאה.

כך:

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

 

[xl3391]

במנהל השמות זה נראה כעת כמו התמונה להלן, אבל זה לא עוזר.

ואת הנוסחה, תיקנתי על ידי שהעתקתי אותה מגוגל שיטס והדבקתי אותה שוב בתא שבו היא הייתה.

צפה בקובץ המצורף 1442341

1.צריך למחוק את המרכאות ואת השווה המיותר.
2.לבדוק אם יש לכם את פונקציית LAMBDA (אפשר ע"י הקלדה בתא באקסל ולראות אם האקסל מביא אותה ברשימה)

 

[xl3391]

אולי עדיף:

לא מכירה, אבל אפשר לנסות עם FIND
למשל את

REGEXMATCH('גיליון1'!E2:E11989,E2)

להחליף ב

IFERROR(FIND(E2,'גיליון1'!E2:E11989),0)>0

חשבתי לפשט עם הLAMBDA אבל אם זה מסבך אולי עדיף ישירות עם FIND

 

[צביזון]

1.צריך למחוק את המרכאות ואת השווה המיותר.
2.לבדוק אם יש לכם את פונקציית LAMBDA (אפשר ע"י הקלדה בתא באקסל ולראות אם האקסל מביא אותה ברשימה)

עשיתי את 1, וכעת זה עובד.
תודה רבה.

לגבי 2, יש לי את הפונקציה.


לא הבנתי מה הכוונה בהודעה הזו:
(כעת זה עובד, כאמור, אבל בשביל הידע).

אולי עדיף:

חשבתי לפשט עם הLAMBDA אבל אם זה מסבך אולי עדיף ישירות עם FIND

 

[אברהם1111]

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

בסוף בחרתי את התא כפי שהוא, לפי מס' תא ועמודה, אבל קודם בחרתי את הבעייתי, ואח"כ את הרגיל וזה עובד...
תודה רבה

 

[צביזון]

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

@יעקב ויצמן היכן ההסבר לכך?

כי בתחילה חשבתי שאעתיק מגוגל שיטס, אבל אני רואה שהוא מילא לי את הקובץ בנוסחאות משובשות

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

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

 

[יעקב ויצמן]

@יעקב ויצמן היכן ההסבר לכך?

כי בתחילה חשבתי שאעתיק מגוגל שיטס, אבל אני רואה שהוא מילא לי את הקובץ בנוסחאות משובשות

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

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

אני אתייחס לשאלתך נטו.בלי להיכנס לכל הדיון לעיל ולפתרונות שנידונו עם @xl3391
אז הסוגיא הזו אכן מורכבת.
על דבר אחד אין ויכוח ובו אפתח.שגוגל ומייקרוסופט שתי מעצמות ענק של הטכנולוגיה.
שפת התיכנות של השיטס vs אקסל שונות כידוע.
פונקציות רבות קיימות באקסל ולא בשיטס.וכן להיפך....
כאשר מעתיקים גיליון שיטס ומדביקים באקסל.מתקבלות הרבה שגיאות של name (בדרך כלל) מהסיבה שהוזכרה לעיל והיא-אותן תוספות(בבחינת כל המוסיף גורע...) טקסט /פונקציות הגורמות לשיבוש.
שים לב שלעיתים .האקסל ידביק את הפונקציות.אבל הן מוצגות כהדבקת ערכים בלבד.(כפי שהזכרת בשאלתך..)"כאילו הטקסט קפא על מקומו" .הפונקציות מושמטות או שאינן מתפקדות.
(כמו שקורה ביצוא לpdf מהשיטס/אקסל)
זה יקרה בדרך כלל שקיימות הפניות לגיליון אחר או חוברת עבודה עם url של הדרייב.
האקסל הוא נמצא במובן מסוים "ביקום מקביל" ולכן כל url שכזה מהשיטס לא נפתח אוטומט או שמושמט.
יש כאן עוד הרבה מה להתפלסף עם המהלך הלוגי.
מה שיותר חשוב זה פתרונות.
א.סוף מעשה במחשבה תחילה...ואני באמת חושב שבעבודה משרדית יש לקבוע מראש על איזו מערכת ענן נשענים.
ואם בוחרים בעבודה עם הדרייב והשיטס .אז שזה יהיה עיקבי ותמידי.
ומלכתחילה לייצר את הגיליונות בשיטס ולהמשיך לעבוד איתן דרך השיטס בלבד.
ב.אם בכל אופן הגיליון הורד מהשיטס ונפתח באקסל.אז פשוט לעבוד עבודת נמלים ולעבור על שגיאות הref או name ולתקן אותן.
זה קשה ומעצבן אני יודע..אבל זה נצרך ונדרש...
ג.אני אוכל להציע לך בע"מ לבצע יבוא נתונים מסונכרן וישיר של גיליון השיטס אל האקסל.
דרך ה-power query
ראה כאן את ההדרכה של שלומי פוסטלניק האלוף.
אומר לך בכנות.כי לא בדקתי וערכתי ניסוי אם זה מעלים אוטומט את השגיאות הספציפיות מעין אלו.
אבל אני מקווה שזה יעבוד לך.ויפתור לך את הבעיה וגם יחסוך לך את כל עבודת נמלים הזו.
בהצלחה במשימה

 

[shlomor]

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

 

[יעקב ויצמן]

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

במקרה כזה תוחזר שגיאת CALC או N/A

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

במקרה כזה יוחזר false

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

 

[shlomor]

במקרה כזה תוחזר שגיאת CALC או N/A

במקרה כזה יוחזר false

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

ראשית, אני מדבר על SHEETS ולא על EXCEL.
בנוסף, אני אסביר יותר טוב עם דוגמה:
יש טווח בגליון 1:

זאת נוסחת ה FILTER בגליון 2

=FILTER('גיליון1'!A21000,'גיליון1'!B2:B1000=G2,'גיליון1'!C2:C1000=G3,'גיליון1'!D21000>G4)​

שמסננת טווח מתוך גליון 1, לפי השוואה לכמה תנאים שאני בוחר מתוך רשימה. כל תנאי בנוסחה מתייחס לפרמטר שונה: תנאי ראשון התאמה לשם פרטי (עמודה B בגליון 1 לתא G2), תנאי שני להתאמה לעיר (עמודה C בגליון אחד לתא G3) וכו'.

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

 

[shlomor]

כתבתי בטעות F3 F4 F5 במקום G2 G3 G4

 

[יעקב ויצמן]

ראשית, אני מדבר על SHEETS ולא על EXCEL.
בנוסף, אני אסביר יותר טוב עם דוגמה:
יש טווח בגליון 1:
צפה בקובץ המצורף 1548468

זאת נוסחת ה FILTER בגליון 2


שמסננת טווח מתוך גליון 1, לפי השוואה לכמה תנאים שאני בוחר מתוך רשימה. כל תנאי בנוסחה מתייחס לפרמטר שונה: תנאי ראשון התאמה לשם פרטי (עמודה B בגליון 1 לתא G2), תנאי שני להתאמה לעיר (עמודה C בגליון אחד לתא G3) וכו'.
צפה בקובץ המצורף 1548470
כרגע, רק אם אני ממלא את כל התאים F2,F3,F4 אני אקבל נתונים. אני מחפש כיצד לכתוב את הנוסחה באופן שאם התא ריק (לדוג' תא F2 שמתייחס לשם פרטי) הנוסחה תתעלם מהתנאי הספציפי הזה, ותביא את כל שאר ההתאמות (בדוג' זו את כל השמות הפרטיים שעומדים בשאר התנאים.

פונקציית filter מתנהגת "בטבעה" הרגיל כמו אפקט פונקציית and דהיינו שכל התנאים צריכים להתקיים מבלי להחסיר אפילו אחד מהם.
אם רוצים שהתנאים יהיו כמו אפקט פונקציית or דהיינו שכל תנאי בפני עצמו ויכול להתקיים או תנאי x או תנאי y וכו.יש להוסיף כל תנאי בסוגריים ולהציב פעולת חיבור.
תראה דוגמא לכך במה שכתבתי בזמנו בלינק הבא (בשלב 3)
הודעה בנושא 'רוצה לעזור לכם באקסל' https://www.prog.co.il/threads/רוצה-לעזור-לכם-באקסל.499341/post-13069957

 

[xl3391]

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

לפי ההסבר לא צריך בIF במקרה שהתנאי לא מתקיים להתעלם, אלא להתייחס לתוצאת התנאי כTRUE, לכן לשים בארגומט של הFALSE
TRUE או 1 (שמשמעותו TRUE).
במקרה שלכם התחביר הזה אמור לעבוד: