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

[יעקב ויצמן]

מה תפקידו של סימון ;
?

זה נקרא נקודה פסיק
בשפת המחשב-תפקידו ליצור הפרדה בין שני ערכים.
אך יש בו גם אלמנט של חיבור בין שני הערכים ליחידה אחת.
סוג של כילאיים.
בתוך סוגריים מסולסלים לפעמים השימוש בפסיק רגיל ולפעמים בנקודה פסיק.
תראה הלינק המצורף
https://www.prog.co.il/threads/מתי-משתמשים-בנקודה-ופסיק-מתחת.123089/post-1781243

 

[xl3391]

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

הנוסחה שלהלן שנכתבה כאן בעבר ע"י @xl3391 עובדת לי טוב בגוגל שיטס,

=SORT({'גיליון עזר1'!A1:T1000;'גיליון עזר2'!A1:T1000},20,true)

והיא עושה 2 פעולות:

1) מאחדת 2 טבלאות מ2 גיליונות שונים,
2) ממיינת אותם לפי עמודה 20.

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

מההודעה של @נעליים כאן למדתי ש VSTACK מאחד טווחים. אפשר כך:

=SORT(VSTACK('גיליון עזר1'!A1:T1000,'גיליון עזר2'!A1:T1000),20,true)

 

[צביזון]

מההודעה של @נעליים כאן למדתי ש VSTACK מאחד טווחים. אפשר כך:

=SORT(VSTACK('גיליון עזר1'!A1:T1000,'גיליון עזר2'!A1:T1000),20,true)

עובד מצוין!

האם יש אפשרות לעשות איזה קיבוץ טווחים גם לפונקצית פילטר?

שבמקום לכתוב כך:

=FILTER(B15:C20,(C15:C20=E15)+(C15:C20=E16)+(C15:C20=E17)+(C15:C20=E18)+(C15:C20=E19)+(C15:C20=E20),"אין תוצאות")

יהיה אפשר לצמצם את כל מה שאני מזריח בצהוב:

ולעשות משהו כעין זה:

התמונה של הקיצור לעיל, לא מצליחה להחליף את הנוסחה הארוכה.

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

 

[יעקב ויצמן]

מההודעה של @נעליים כאן למדתי ש VSTACK מאחד טווחים. אפשר כך:

=SORT(VSTACK('גיליון עזר1'!A1:T1000,'גיליון עזר2'!A1:T1000),20,true)

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

ברשותכם.אוסיף כאן הארה ונקודה חשובה:
כמובן שמיידית פתחתי את האקסל לנסות את הפונקצייה .
הכל עבד מצוין.
הבעיה היחידה שישנה כאן היא.בגלל שהטווח בsort מוגדר עד t1000
אם אין ערכים בגיליון המקור.יוחזר בפונקציית sort ערך 0 בתאים.
אז אמרתי לעצמי.אוקיי.אז בוא ונעטוף את את כל הטוב הזה עם substitute ונגיד לאקסל להחליף 0 בתא ריק ""
ובכן גם זה לא פתרון מושלם.כי בשיטה הזו כל האפסים יושמטו כולל אפסים שיתכן וישנם בטבלאות המקור.
אז כדרכי .חיפשתי פיתרונות ברשת.
והפיתרון הוא לכתוב בארגומנט השני של substitute ככה "".
אנו אומרים לפונקציה להחליף תא ריק בתא ריק.
מה אנו עושים ומרויחים כאן?
אפסים שנוספו רק "מכח" פונקציית vstack הם לא נחשבים "ערכים (אמיתיים)שממלאים תא". ולכן דינם כתא ריק לחלוטין.
הם יוחלפו בתא ריק "כפשוטו" .התא יראה ממש ריק חסר ערכים.
ואפסים שהיו בטבלת המקור ישארו כפי שהם.והכל יבוא אל מקומו בשלום.
כל זאת ועוד בלינק המצורף כאן
בהצלחה

 

[צביזון]

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

ברשותכם.אוסיף כאן הארה ונקודה חשובה:
כמובן שמיידית פתחתי את האקסל לנסות את הפונקצייה .
הכל עבד מצוין.
הבעיה היחידה שישנה כאן היא.בגלל שהטווח בsort מוגדר עד t1000
אם אין ערכים בגיליון המקור.יוחזר בפונקציית sort ערך 0 בתאים.
אז אמרתי לעצמי.אוקיי.אז בוא ונעטוף את את כל הטוב הזה עם substitute ונגיד לאקסל להחליף 0 בתא ריק ""
ובכן גם זה לא פתרון מושלם.כי בשיטה הזו כל האפסים יושמטו כולל אפסים שיתכן וישנם בטבלאות המקור.
אז כדרכי .חיפשתי פיתרונות ברשת.
והפיתרון הוא לכתוב בארגומנט השני של substitute ככה "".
אנו אומרים לפונקציה להחליף תא ריק בתא ריק.
מה אנו עושים ומרויחים כאן?
אפסים שנוספו רק "מכח" פונקציית vstack הם לא נחשבים "ערכים (אמיתיים)שממלאים תא". ולכן דינם כתא ריק לחלוטין.
הם יוחלפו בתא ריק "כפשוטו" .התא יראה ממש ריק חסר ערכים.
ואפסים שהיו בטבלת המקור ישארו כפי שהם.והכל יבוא אל מקומו בשלום.
כל זאת ועוד בלינק המצורף כאן
בהצלחה

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

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

עריכה: אכן אתה צודק. אם נשתמש במה שאני כתבתי, זה יוריד גם 0 שהוא בטבלת המקור.

אבל לא הבנתי איך לעטוף את הנוסחה דלעיל עם הנוסחה שלך.

 

[יעקב ויצמן]

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

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

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

עריכה: אכן אתה צודק. אם נשתמש במה שאני כתבתי, זה יוריד גם 0 שהוא בטבלת המקור.

אבל לא הבנתי איך לעטוף את הנוסחה דלעיל עם הנוסחה שלך.

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

 

[יעקב ויצמן]

עריכה: אכן אתה צודק. אם נשתמש במה שאני כתבתי, זה יוריד גם 0 שהוא בטבלת המקור.

אבל לא הבנתי איך לעטוף את הנוסחה דלעיל עם הנוסחה שלך.

הנה ככה

 

[יעקב ויצמן]

עובד מצוין!

האם יש אפשרות לעשות איזה קיבוץ טווחים גם לפונקצית פילטר?

שבמקום לכתוב כך:

=FILTER(B15:C20,(C15:C20=E15)+(C15:C20=E16)+(C15:C20=E17)+(C15:C20=E18)+(C15:C20=E19)+(C15:C20=E20),"אין תוצאות")

יהיה אפשר לצמצם את כל מה שאני מזריח בצהוב:

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

ולעשות משהו כעין זה:
צפה בקובץ המצורף 1446234

התמונה של הקיצור לעיל, לא מצליחה להחליף את הנוסחה הארוכה.

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

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

 

[פ. הלר]

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

 

[יעקב ויצמן]

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

xlsm ראי כאן

שמירת מאקרו - תמיכה של Microsoft

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

support.microsoft.com

 

[צביזון]

בא נחשוב ביחד ונשאל.
ממאי נפשך.
אם הערכים מתא e15 עד e20 הם אותם ערכים.אז מספיק לכתוב אחד מהם.ולמה לי כולי האי?
ואם הם לא אותם ערכים .אז שהאקסל ירצה לבחון על פי קריטריון.הוא יסתכל באוסף ערכים בששת התאים האלו.ואז הוא ישאל את עצמו לאיזה קריטריון התכוון המשורר.
צריך להבין את הלוגיקה פה.קריטריון לא יכול להתפרס על פני הרבה תאים.!!!
מה שהאקסל יפעל במקרה כזה.הוא יראה את הקריטריון הכי עליון(את העיקרון הזה ניתן למצוא בהרבה מקומות באקסל כמו בvlookup למשל.)
במקרה הנל זה הקרטריון בתא e15 ועל פיו בלבד הוא יפעל.
תהיה היתעלמות מוחלטת מכל יתר הקריטריונים.
האקסל בדרך כךל "בטוב ליבו" יבליג (ולא יחזיר שגיאה)בשתיקה על בילבולו של משתמש הגיליון.וימשיך לפעול כפי שהוא "רואה לנכון" וכפי שהוא רגיל לנהוג.
לכן בפונקציית פילטר הנל.האקסל אומר למשתמש הגיליון.אני אסקור ואסרוק איזה טווח שתרצה ושרק תמיין.
.מc5 עד c15.?אוקיי...
אבדוק את כולם אחד אחד.
אבל אמור לי בבקשה אדון נכבד -קריטריון אחד בלבד שאוכל לבדוק האם קיימת התאמה או לא.
הנה לך דוגמא קטנה מפונקציית if
(הטרו והפולס הפוכים פה במיקומם)
צפה בקובץ המצורף 1446391

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

דבר נוסף, בבסיס, אקסל יודע לעבוד עם פילטר לפי כמה תנאים, והראיה זו הנוסחה הארוכה לעיל.

השאלה איך לתת לו להבין בצורה יותר מקוצרת, משהו שיהיה יותר קל לעבוד איתו על הרבה תאים.
אולי לא בצורה שאני הבאתי, אבל עדיין יותר מקוצרת מהנוסחה המקורית.
אולי יהיה ל @xl3391 איזה רעיון חדש.

 

[עוגת בוקר]

איך יורדים שורה בתוך תא?
אנטר מעביר אותי לתא הבא.

 

[יעקב ויצמן]

איך יורדים שורה בתוך תא?
אנטר מעביר אותי לתא הבא.

 

[צביזון]

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

את המידע לנוסחה לקחתי מכאן.

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

נוסחת הפילטר כתובה בתא G4 בקובץ שהעליתי.
והוא שואב מטבלת המקור שמימין, לפי הטווח שבעמודה E
והוספתי SORT למיין לפי עמודה C
אבל איני מצליח שאח"כ ימשיך הלאה למיין לפי עמודה B

 

[יעקב ויצמן]

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

את המידע לנוסחה לקחתי מכאן.

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

רצית להסיר כפילויות שבעמודת המקור כמו אפקט של unique?
בגלל זה השתמשת ב countifs?

נוסחת הפילטר כתובה בתא G4 בקובץ שהעליתי.
והוא שואב מטבלת המקור שמימין, לפי הטווח שבעמודה E
והוספתי SORT למיין לפי עמודה C
אבל איני מצליח שאח"כ ימשיך הלאה למיין לפי עמודה B

מיון לפי עמודה 3 ולפי עמודה 2 זה תרתי דסתרי.
אי אפשר ששניהן יתקיימו כאחד

 

[צביזון]

רצית להסיר כפילויות שבעמודת המקור כמו אפקט של unique?
בגלל זה השתמשת ב countifs?

מיון לפי עמודה 3 ולפי עמודה 2 זה תרתי דסתרי.
אי אפשר ששניהן יתקיימו כאחד

ראית את הטבלה? לפי השאלה, זה נראה שאתה לא תופס את העוצמה.

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

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

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

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


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

כמובן שאפשר לעטוף את הSORT בעוד SORT ואז זה מתבצע מצוין.
אבל לא הצלחתי לעשות את זה ע"י SORT אחד.

 

[יעקב ויצמן]

ראית את הטבלה? לפי השאלה, זה נראה שאתה לא תופס את העוצמה.

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

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

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

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


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

כמובן שאפשר לעטוף את הSORT בעוד SORT ואז זה מתבצע מצוין.
אבל לא הצלחתי לעשות את זה ע"י SORT אחד.

תוכל לשלוח צילום מסך איך ביצעת sort בתוך sort ואיך נראית הטבלה?

 

[יעקב ויצמן]

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

 

[צביזון]

תוכל לשלוח צילום מסך איך ביצעת sort בתוך sort ואיך נראית הטבלה?

מצורפת הטבלה עם SORT בתוך SORT

בינתיים תראה את שני הטבלאות הירוקות.

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

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

 

[יעקב ויצמן]

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

אם "נתלוש" לרגע את פונקציית countifs ונבודד אותה.כמו שעשיתי פה בעמודה d.
אנו אומרים לאקסל תסתכל על רשימת הערכים בעמודה e ותספור כאן בעמודה d בתנאי שאחד מהערכים (מעמודה e) מופיע בעמודה c.
אנו רואים שבשורה 7.8 התוצאה היא 0.
כי חיפה וקרית גת לא מופיעים בעמודה e.
כעת נחזור לארגומנט השני של פילטר בטבלה הירוקה .אנו אומרים לאקסל שהתנאי לשורות שיסוננו הוא שהתוצאה לכל הcountifs (שחישבנו לעיל.וכמו שמופיע בשטח הכתום) הוא 0< דהיינו גדול מ0.
ולכן תושבי חיפה וקרית גת .נותרו בחוץ .כי בעמודה d התוצאה שהיתה לצידם היא 0.