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

[צביזון]

כאשר משתמשים בפונקציה, הוא נותן רק את מה שמתאים לפילטר, ואין אין משהו בטבלה שמתאים לפילטר הוא לא נותן כלום.

למשל, אם אני עושה פילטר לפי תא K2, הוא יתן רק תוצאות שמתאימות ל"K2", וכאשר התא יהיה ריק, הוא לא יתן כלום.

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

 

[xl3391]

להוסיף תנאי אם K2 ריק אז index לכל הטווח.
(אם מחזיר NA אפשר במקום IF לעטוף את הFILTER ב IFNA).

 

[צביזון]

להוסיף תנאי אם K2 ריק אז index לכל הטווח.
(אם מחזיר NA אפשר במקום IF לעטוף את הFILTER ב IFNA).

תודה על התשובה. אבל איני יודע איך לעשות זאת.
כעת כתוב אצלי כך:

=FILTER('גיליון1'!A1:J1500,'גיליון1'!A1:A1500=K2)

מה אני צריך לעשות בשביל שיהיה כפי שכתבת לי?

אציין שהתא K2 מלא באותיות ולא במספרים.

 

[xl3391]

=IF(K2="",INDEX('גיליון1'!A1:J1500),FILTER('גיליון1'!A1:J1500,'גיליון1'!A1:A1500=K2))

 

[צביזון]

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

 

[xl3391]

=IF(K2="",INDEX('גיליון1'!A1:J1500),FILTER('גיליון1'!A1:J1500,REGEXMATCH('גיליון1'!A1:A1500,K2)))

 

[xl3391]

=IF(K2="",INDEX('גיליון1'!A1:J1500),FILTER('גיליון1'!A1:J1500,REGEXMATCH('גיליון1'!A1:A1500,K2)))

במחשבה שניה, במקרה הזה לא צריך את התנאי והאינדקס.
אפשר רק:

=FILTER('גיליון1'!A1:J1500,REGEXMATCH('גיליון1'!A1:A1500,K2))

 

[צביזון]

במחשבה שניה, במקרה הזה לא צריך את התנאי והאינדקס.
אפשר רק:

=FILTER('גיליון1'!A1:J1500,REGEXMATCH('גיליון1'!A1:A1500,K2))

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

=FILTER('גיליון1'!A1:J1500,('גיליון1'!I2:I11576=E1)+REGEXMATCH('גיליון1'!A1:A1500,K2))

 

[xl3391]

במקום + לרשום פסיק
במקום I2:I11576 לרשום I1:I1500 (מספר השורות בטווח שנבדק צריך להיות זהה למספר השורות בטווח שמוחזר)

 

[צביזון]

במקום + לרשום פסיק

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

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

 

[צביזון]

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

 

[xl3391]

אפשר לנסות להחליף אותה ב:

LEFT('גיליון1'!A1:A1500,LEN(K2))=K2

 

[xl3391]

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

מוזר. אצלי עבד עם פסיק.

 

[צביזון]

LEFT('גיליון1'!A1:A1500,LEN(K2))=K2

לא הצלחתי שהנוסחה הנ"ל תעבוד לי. או שלא ידעתי בדיוק איך לשלב אותה. אולי היא צריכה להחליף רק חלק מהנוסחה?

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

=FILTER('גיליון1'!A1:J1500,REGEXMATCH('גיליון1'!A1:A1500,J2)*REGEXMATCH('גיליון1'!B1:B1500,K2))

 

[xl3391]

במקום הREGEXMATCH

=FILTER('גיליון1'!A1:J1500,(LEFT('גיליון1'!A1:A1500,LEN(J2))=J2)*(LEFT('גיליון1'!B1:B1500,LEN(K2))=K2))

 

[צביזון]

במקום הREGEXMATCH

=FILTER('גיליון1'!A1:J1500,(LEFT('גיליון1'!A1:A1500,LEN(J2))=J2)*(LEFT('גיליון1'!B1:B1500,LEN(K2))=K2))

תודה רבה! עובד מצוין.

יש לי עוד 2 שאלות טכניות.

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

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

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

ושוב, תודה רבה.

 

[צביזון]

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

לגבי מספר 2, האם יש פתרון?

 

[xl3391]

בקוד

 

[צביזון]

בקוד

מה כוונתך? האם רצית לתת כאן קוד ונשמט, או שכוונתך שמסובך להסביר זאת?

 

[xl3391]

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