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

תגרין · 22/8/23

נכתב ע"י יעקב ויצמן:
@עבד ד'
זה בקשר לשאלת @תגרין ?
נא לכתוב שוב מה השאלה בדיוק

איך משנים מספור אוטומטי שימספר רק רשימה מסוננת?

יעקב ויצמן · 22/8/23

נכתב ע"י תגרין:
איך משנים מספור אוטומטי שימספר רק רשימה מסוננת?

האם התכוונת לנושא בלינק המצורף?

How to Auto Number or Renumber after Filter in Excel (7 Easy Ways)

In this article, we have demonstrated how to auto number or renumber after filter in Excel. We have used 7 easy ways to do so!!

www.exceldemy.com

למה? · 22/8/23

נכתב ע"י יעקב ויצמן:
צפה בקובץ המצורף 1445266 צפה בקובץ המצורף 1445267
בבקשה
2 דרכים לפיתרון.
ואידך זיל גמור...
בהצלחה

בפתרון 2
אני לא בקי בעניין של המסולסלות
אשמח אם תוכל להסביר מה עושה כאן ה { }
וגם למה זה לא יעבוד בלי SUM בהתחלה?

יעקב ויצמן · 22/8/23

נכתב ע"י למה?:
בפתרון 2
אני לא בקי בעניין של המסולסלות
אשמח אם תוכל להסביר מה עושה כאן ה { }
וגם למה זה לא יעבוד בלי SUM בהתחלה?

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

במיטב היגיון · 22/8/23

נכתב ע"י יעקב ויצמן:
צפה בקובץ המצורף 1445266 צפה בקובץ המצורף 1445267
בבקשה
2 דרכים לפיתרון.
ואידך זיל גמור...
בהצלחה

@יעקב ויצמן
הפיתרון השני לא עובד לי בשיטס, אין פיתרון אחר מלבד הפיתרון הראשון שהוא לגמרי מסורבל וכבד "ובעיקר פשוט"(באידיש: א באלעבטישער תירוץ).

והאם מסולסלות אף פעם לא עובדים בשיטס?

צביזון · 22/8/23

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

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

קוד:

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

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

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

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

יעקב ויצמן · 22/8/23

נכתב ע"י במיטב היגיון:
@יעקב ויצמן
הפיתרון השני לא עובד לי בשיטס,

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

נכתב ע"י במיטב היגיון:
אין פיתרון אחר מלבד הפיתרון הראשון שהוא לגמרי מסורבל וכבד "ובעיקר פשוט"(באידיש: א באלעבטישער תירוץ).

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

נכתב ע"י במיטב היגיון:
והאם מסולסלות אף פעם לא עובדים בשיטס?

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

קול תורה · 23/8/23

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

הסימון ; עד כמה שאני יודע לא עובד באקסל ונראה לי שצריך להחליף אותו בפסיק
הסוגריים המסולסלים כן אמורים לעבוד בעיקרון
רק אוסיף שהנוסחה sort לא קיימת בגרסאות הישנות של אקסל

צביזון · 23/8/23

ניסיתי גם עם פסיק, ולא עובד.
באקסל שלי sort עובד.

למה? · 23/8/23

נכתב ע"י קול תורה:
הסימון ; עד כמה שאני יודע לא עובד באקסל ונראה לי שצריך להחליף אותו בפסיק
הסוגריים המסולסלים כן אמורים לעבוד בעיקרון
רק אוסיף שהנוסחה sort לא קיימת בגרסאות הישנות של אקסל

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

יעקב ויצמן · 23/8/23

נכתב ע"י למה?:
מה תפקידו של סימון ;
?

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

xl3391 · 23/8/23

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

הנוסחה שלהלן שנכתבה כאן בעבר ע"י @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)

צביזון · 23/8/23

נכתב ע"י xl3391:
מההודעה של @נעליים כאן למדתי ש 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),"אין תוצאות")

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

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

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

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

יעקב ויצמן · 23/8/23

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

קוד:

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

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

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

צביזון · 23/8/23

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

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

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

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

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

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

יעקב ויצמן · 23/8/23

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

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

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

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

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

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

יעקב ויצמן · 23/8/23

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

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

הנה ככה

יעקב ויצמן · 23/8/23

נכתב ע"י צביזון:
עובד מצוין!

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

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

קוד:

=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
(הטרו והפולס הפוכים פה במיקומם)