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

[יעקב ויצמן]

איך עושים בפונקציית lookup/Xlookup שימצא את מה שהיה ב- 01/01/2023 ויחזיר את זה ל -01/01/2024
ניסיתי לעשות בעיצוב תאים dd/mm אך ללא הועיל
תודה ענקית!!!!

צריך להכניס את פונקציית text בפורמט dd/mm בארגומנט ראשון ושני של הxlookup
ראה כאן דוגמא

 

[יעקב ויצמן]

זו עוד שיטה.מעט יותר ארוכה

 

[במיטב היגיון]

מומחים יקרים שלום וברכה
האם יש דרך לנסח תנאי לוגי מול מערך?
דוגמה:
פונקציית SUMIFS סוכמת טווח A:A אם בטווח B:B יש התאמה ל"יעקב ויצמן))"?
כי בעיקרון הטווח אמור עמודה ולא כמה עמודות, ואילו אני רוצה לבדוק אם ישנה התאמה גם בעמודה B וגם בעמודה C וD.

 

[יעקב ויצמן]

מומחים יקרים שלום וברכה
האם יש דרך לנסח תנאי לוגי מול מערך?
דוגמה:
פונקציית SUMIFS סוכמת טווח A:A אם בטווח B:B יש התאמה ל"יעקב ויצמן))"?
כי בעיקרון הטווח אמור עמודה ולא כמה עמודות, ואילו אני רוצה לבדוק אם ישנה התאמה גם בעמודה B וגם בעמודה C וD.

התאמה דווקא ליעקב ויצמן?חחח
לא כך שנו רבותינו "שכשם שפרצופיהם שונים כך דעותיהם שונות..."?
לשאלתך:
1.ראשית, אין נפקא מינה בין sumif לבין sumifs כי מדובר פה בקריטריון אחד בלבד.
2.שנית.פונקציות sumif/sumifs מקבלות טווחים אך הן לא נוסחאות מערך בD.N.A שלהן.וחשוב לדעת זאת.זו עובדה שמוטמעת באלגוריתם של הפונקציות!
3.מזה נובע כי פונקציות sumif /sumifs מוכרחות להיות עם טווח זהה בגודל ובתבנית של ארגומנט range כמו של range_sum.
ולכן לא ניתן באופן רגיל לבצע כשאלתך.
4.כהמשך לסעיף 2 .פונקציות אלו מאופינות "ויוצאות דופן" יחד עם עוד 6 מחברותיהן שאלו שמותיהן:-

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

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

הפיתרון הצבוע בתכלת.הוא למעשה "הפשוט" שבהם.הרעיון להקצות לכל עמודת תנאי פונקצייה שלמה .ולחבר את תוצאות הפונקציות בפעולת חיבור.
ארוך קצת.נכון?
אז בפיתרון הצבוע בורוד קיימת פונקציית filter עם "עטיפה" של פונקציית sum.
בטכניקה של מיסגור כל תנאי בסוגריים עם פעולת חיבור.הרעיון בזה ליצור אפקט פונקציית or כאשר הסברתי פעמים רבות בפורום זה.ראה כאן דוגמא בלינק המצורף
https://www.prog.co.il/threads/הפונקציה-filter-בגוגל-שיטס.795652/post-13151412
ואחרון חביב...זהו הפיתרון הצבוע בצהוב .הפיתרון כאן קצר וקולע בעזרת פונקציית sumproduct הגאונית והנפלאה.כתבתי והרחבתי לאחרונה על פונקצייה זו כאן בפורום זה..
הרעיון הוא שכאשר התנאי מתקיים בכל אחד מ3 תאים שבכל שורה מוחזר true "השווה" בערכו ל1.
ולכן המכפלה בכל שורה (שמתקיים בה התנאי)היא של הסכום ב1.
כל סכומי המכפלות מחוברים יחד באופן אוטומט כחלק מעצם מהותה של הפונקצייה.
וכפי שהסברתי לעיל בלינק הבא
https://www.prog.co.il/threads/רוצה-לעזור-לכם-באקסל.499341/post-13216216
לסיכום: הנה לפניך 3 פתרונות שונים .כל פיתרון בדרך שונה ומיוחדת.
ניתן להוסיף עוד פיתרונות יותר ארוכים ומפותלים.אך דומני שזה מספיק די והותר....

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

 

[2u2]

תראה בגיליון המצורף בטווח הצבוע בצהוב

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

 

[יעקב ויצמן]

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

לא מובן לי לגמרי מה המטרה פה.סורי.
בסופו של ענין-הכל פה משחק של קיבועים ואנדים & .סה טו!
אני אשמח שתתקשר ותסביר לי טלפונית יותר בהרחבה.ואטפל בזה בשמחה ובל"נ עד תום.
0528174820

 

[קול תורה]

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

נראה לי שהעניין הוא הפניות יחסיות או נעולות (לא התעמקתי, ולא הבנתי מה צריך לצאת בסופו של דבר)

 

[במיטב היגיון]

התאמה דווקא ליעקב ויצמן?חחח
לא כך שנו רבותינו "שכשם שפרצופיהם שונים כך דעותיהם שונות..."?
לשאלתך:
1.ראשית, אין נפקא מינה בין sumif לבין sumifs כי מדובר פה בקריטריון אחד בלבד.
2.שנית.פונקציות sumif/sumifs מקבלות טווחים אך הן לא נוסחאות מערך בD.N.A שלהן.וחשוב לדעת זאת.זו עובדה שמוטמעת באלגוריתם של הפונקציות!
3.מזה נובע כי פונקציות sumif /sumifs מוכרחות להיות עם טווח זהה בגודל ובתבנית של ארגומנט range כמו של range_sum.
ולכן לא ניתן באופן רגיל לבצע כשאלתך.
4.כהמשך לסעיף 2 .פונקציות אלו מאופינות "ויוצאות דופן" יחד עם עוד 6 מחברותיהן שאלו שמותיהן:-
צפה בקובץ המצורף 1574469
השוני העיקרי המשותף להן שארגומנט טווח הקריטריון וארגומנט הקריטריון עצמו מופרדים זה מזה ואינם נכללים בארגומנט אחד.
(ובאגב.יש לכך השפעה על צורת כתיבת אופירטורים כמו גדול/קטן עם מרכאות או בלי)
שינויים אלו הן חלק מאפיון של הפונקציות שאינן נוסחאות מערך.
זהו נושא עם לוגיקה רבה ועומק,ואולי בהזדמנות ארחיב בל"נ יותר בזה.

וכעת לפיתרונות שניתן להציע:
אז הנה צילום מסך מגיליון דוגמא שערכתי
צפה בקובץ המצורף 1574478
הפיתרון הצבוע בתכלת.הוא למעשה "הפשוט" שבהם.הרעיון להקצות לכל עמודת תנאי פונקצייה שלמה .ולחבר את תוצאות הפונקציות בפעולת חיבור.
ארוך קצת.נכון?
אז בפיתרון הצבוע בורוד קיימת פונקציית filter עם "עטיפה" של פונקציית sum.
בטכניקה של מיסגור כל תנאי בסוגריים עם פעולת חיבור.הרעיון בזה ליצור אפקט פונקציית or כאשר הסברתי פעמים רבות בפורום זה.ראה כאן דוגמא בלינק המצורף
https://www.prog.co.il/threads/הפונקציה-filter-בגוגל-שיטס.795652/post-13151412
ואחרון חביב...זהו הפיתרון הצבוע בצהוב .הפיתרון כאן קצר וקולע בעזרת פונקציית sumproduct הגאונית והנפלאה.כתבתי והרחבתי לאחרונה על פונקצייה זו כאן בפורום זה..
הרעיון הוא שכאשר התנאי מתקיים בכל אחד מ3 תאים שבכל שורה מוחזר true "השווה" בערכו ל1.
ולכן המכפלה בכל שורה (שמתקיים בה התנאי)היא של הסכום ב1.
כל סכומי המכפלות מחוברים יחד באופן אוטומט כחלק מעצם מהותה של הפונקצייה.
וכפי שהסברתי לעיל בלינק הבא
https://www.prog.co.il/threads/רוצה-לעזור-לכם-באקסל.499341/post-13216216
לסיכום: הנה לפניך 3 פתרונות שונים .כל פיתרון בדרך שונה ומיוחדת.
ניתן להוסיף עוד פיתרונות יותר ארוכים ומפותלים.אך דומני שזה מספיק די והותר....

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

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

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

 

[יעקב ויצמן]

דבר ראשון תודה רבה.
עזרת לי מאוד.
אתה פשוט אלוף!!

בשמחה רבה ידידי! ותודה על הפידבק!

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

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

תשתמש בsumproduct
וזה לך לאות שזה עובד (בצילום מסך המצורף)

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

 

[2u2]

דבר ראשון תודה רבה.
עזרת לי מאוד.
אתה פשוט אלוף!!

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

 

[יעקב ויצמן]

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

תודה רבה על ההערכה.תבורך מפי עליון!
שמחתי לעזור וברוך ה' שבסוף הכל הסתדר לשביעות רצונך.

 

[רק שאלה!]

אני מנמסה ליצור רשימה של שבתות השנה-
ניסיתי עם הפונקצה הזאת
=FILTER(SEQUENCE(30,13,15/10/2023,1),WEEKDAY(SEQUENCE(30,13,15/10/2023,1)=7))
והחזיר לי שגיאה VALUE
איפה טעיתי??

 

[במיטב היגיון]

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

אכן אכן!
מסירות נפש לזולת
ירבו כמותו בישראל.
תודה רבה רבה !

 

[יעקב ויצמן]

אני מנמסה ליצור רשימה של שבתות השנה-
ניסיתי עם הפונקצה הזאת
=FILTER(SEQUENCE(30,13,15/10/2023,1),WEEKDAY(SEQUENCE(30,13,15/10/2023,1)=7))
והחזיר לי שגיאה VALUE
איפה טעיתי??

זו הרשימה שרצית לקבל?

 

[רק שאלה!]

זו הרשימה שרצית לקבל?

כן אבל שיתחיל מראש השנה
בעיקרון עם תיבת עזר שיהיה אפשר לשנות כל שנה

 

[יעקב ויצמן]

כן אבל שיתחיל מראש השנה
בעיקרון עם תיבת עזר שיהיה אפשר לשנות כל שנה

תראה .אם היה לך רשימה בעמודה נפרדת של כל ימות השנה וההית רוצה "לשלוף" משם רק את שבתות השנה .היה צריך להשתמש כאן בטכניקה של שילוב sequence +mod +rows בתוך filter כפי שהסברתי והדגמתי לגבי דין "מעשר בהמה" בלינק כאן
https://www.prog.co.il/threads/רוצה-לעזור-לכם-באקסל.499341/post-13167983

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

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

בהצלחה רבה!

 

[במיטב היגיון]

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

 

[רק שאלה!]

תראה .אם היה לך רשימה בעמודה נפרדת של כל ימות השנה וההית רוצה "לשלוף" משם רק את שבתות השנה .היה צריך להשתמש כאן בטכניקה של שילוב sequence +mod +rows בתוך filter כפי שהסברתי והדגמתי לגבי דין "מעשר בהמה" בלינק כאן
https://www.prog.co.il/threads/רוצה-לעזור-לכם-באקסל.499341/post-13167983
צפה בקובץ המצורף 1575306
אך במקרה שלך אתה מעונין ליצור מראש רשימה עם דילוגים .ולכן כל שנדרש כאן הוא להוסיף בארגומנט הרביעי בפונקצייה שמשמעותו בכמה לדלג קדימה /אחורה.(דילוג אחורה יתבטא בסימון 1- וכדומה)
במקרה הזה הדילוג הוא 7 (7ימים)

לגבי קביעת תאריך התחלה ניתן לבצע זאת בכתיבה ישירה בפונקצייה או בכתיבה בתאי עזר ותוך כדי שימוש בפונקציית date
מצורף לך גיליון עם 3 הווריאציות .והבוחר יבחר...
צפה בקובץ המצורף 1575307צפה בקובץ המצורף 1575308
בהצלחה רבה!

תודה רבה!!
בהתחלה עשיתי לוח נוסף - ואחרי זה ניסיתי לצמצם,,

 

[רק שאלה!]

תראה .אם היה לך רשימה בעמודה נפרדת של כל ימות השנה וההית רוצה "לשלוף" משם רק את שבתות השנה .היה צריך להשתמש כאן בטכניקה של שילוב sequence +mod +rows בתוך filter כפי שהסברתי והדגמתי לגבי דין "מעשר בהמה" בלינק כאן
https://www.prog.co.il/threads/רוצה-לעזור-לכם-באקסל.499341/post-13167983
צפה בקובץ המצורף 1575306
אך במקרה שלך אתה מעונין ליצור מראש רשימה עם דילוגים .ולכן כל שנדרש כאן הוא להוסיף בארגומנט הרביעי בפונקצייה שמשמעותו בכמה לדלג קדימה /אחורה.(דילוג אחורה יתבטא בסימון 1- וכדומה)
במקרה הזה הדילוג הוא 7 (7ימים)

לגבי קביעת תאריך התחלה ניתן לבצע זאת בכתיבה ישירה בפונקצייה או בכתיבה בתאי עזר ותוך כדי שימוש בפונקציית date
מצורף לך גיליון עם 3 הווריאציות .והבוחר יבחר...
צפה בקובץ המצורף 1575307צפה בקובץ המצורף 1575308
בהצלחה רבה!

יישר כוח
מכיוון שזה עובד בכפולות של 7 מספיק לעשות 55 שבתות ולא 365 ----

 

[יעקב ויצמן]

יישר כוח
מכיוון שזה עובד בכפולות של 7 מספיק לעשות 55 שבתות ולא 365 ----

צודק לחלוטין.
אני התבלבלתי כאן לרגע בהקלדה.סורי...