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

יעקב ויצמן · 12/2/24

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

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

קול תורה · 12/2/24

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

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

במיטב היגיון · 12/2/24

נכתב ע"י יעקב ויצמן:
התאמה דווקא ליעקב ויצמן?חחח
לא כך שנו רבותינו "שכשם שפרצופיהם שונים כך דעותיהם שונות..."?
לשאלתך:
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 פתרונות שונים .כל פיתרון בדרך שונה ומיוחדת.
ניתן להוסיף עוד פיתרונות יותר ארוכים ומפותלים.אך דומני שזה מספיק די והותר....

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

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

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

יעקב ויצמן · 12/2/24

נכתב ע"י במיטב היגיון:
דבר ראשון תודה רבה.
עזרת לי מאוד.
אתה פשוט אלוף!!

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

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

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

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

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

2u2 · 12/2/24

נכתב ע"י במיטב היגיון:
דבר ראשון תודה רבה.
עזרת לי מאוד.
אתה פשוט אלוף!!

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

יעקב ויצמן · 12/2/24

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

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

רק שאלה! · 12/2/24

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

במיטב היגיון · 12/2/24

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

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

יעקב ויצמן · 12/2/24

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

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

רק שאלה! · 12/2/24

נכתב ע"י יעקב ויצמן:
זו הרשימה שרצית לקבל?

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

יעקב ויצמן · 12/2/24

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

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

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

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

בהצלחה רבה!

במיטב היגיון · 12/2/24

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

רק שאלה! · 12/2/24

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

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

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

רק שאלה! · 12/2/24

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

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

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

יעקב ויצמן · 12/2/24

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

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

אחת שאוהבת להבין · 13/2/24

נכתב ע"י xl3391:

אפשר עם FILTER או עם SUMPRODUCT בשניהם על אותו רעיון של הכפלה ב1 כאשר אין ערך.

קוד:

=SUM(FILTER(טבלה1[שכר],IF([@מתאריך]="",1,טבלה1[תאריך]>=[@מתאריך])*IF([@[עד תאריך]]="",1,טבלה1[תאריך]<[@[עד תאריך]])*IF([@עבודה]="",1,טבלה1[עבודה]=[@עבודה]),0))

קוד:

=SUMPRODUCT(טבלה1[שכר]*IF([@מתאריך]="",1,טבלה1[תאריך]>=[@מתאריך])*IF([@[עד תאריך]]="",1,טבלה1[תאריך]<[@[עד תאריך]])*IF([@עבודה]="",1,טבלה1[עבודה]=[@עבודה]))

כהמשך לפתרונך, אשמח לדעת אם תוכלי את או @יעקב ויצמן או מישהו אחר לעזור לי בנוגע לשלב נוסף שאני מעוניינת שהוא יחשב סיכום של 2 עמודות בתור טווח הקריטריוני, כלומר הוא יחשב 2-עמודות מקבילות בתור-Criteria_range, ורק שורה שהתאים שלה מ 2 העמודות עולים על סכום מסויים המוגדר ב-Criteria תכנס לחישוב כחלק מה-Sum_range.
צרפתי קובץ דוגמה שם חישבתי באמצעות נוסחת FILTER כעצתך, אבל לא אכפת לי גם פתרון אחר כמו על ידי ה-SUMIFS שהציע @קול תורה כאן.
הנה צילום מסך מהקובץ-

תודה מראש לכולם

אחת שאוהבת להבין · 13/2/24

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

יעקב ויצמן · 13/2/24

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

בעבר,כתבתי על שאלה מעין זו תשובה מפורטת בלינק כאן
https://www.prog.co.il/threads/הפונקציה-filter-בגוגל-שיטס.795652/post-12614516
בקיצור,אתה מוריד קובץ שיטס כxlsx ופותח באקסל.
א.תעבור על העמודות באקסל בזהירות ובסבלנות רבה אט אט.תראה שכל החישובים האוטומטים עובדים ונמצאים במקומם (כפונקציות דינאמיות ולא ערכים בלבד שמודבקים)
ב.אם קיימת שגיאה(בדרך כלל name) לא להיבהל.!!!תיכנס לשיטס ותראה את התבנית שם.ותסיר את כל מה שמיותר בשורת הנוסחאות באקסל
הסברתי פעמים רבות כאן בפורום על אותם "עצמים בלתי מזוהים" שנוספים באופן אוטומט
הנה דוגמא למשל
https://www.prog.co.il/threads/רוצה-לעזור-לכם-באקסל.499341/post-12501905
ג.בכל הקשור לפקודות מאקרו דרך גוגל סקריפט שקיימות בגיליון שיטס .אז זה פשוט לא רלוונטי באקסל.יש לזכור כי השפה והאלגוריתם שונים בין אקסל לשיטס.
ועל כן- יש לקודד מחדש פקודות דרך הvba.
ד.ישנן פונקציות שקיימות רק בשיטס ולא באקסל(וכן להיפך...).בדרך כלל ניתן למצוא תחליף לפונקציות אלו.אך אם נתקלת בקושי (ולכל שאלה נוספת)אתה מוזמן תמיד לשתף ולשאול כאן בפורום.
בהצלחה רבה במשימה!

במיטב היגיון · 13/2/24

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

דסוש · 13/2/24

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

פרוגבוט

פרסומת

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

מנהל פורום מחשבים

משתמש פעיל

משתמש רשום

מנהל פורום מחשבים

משתמש מקצוען

מנהל פורום מחשבים

משתמש רשום

משתמש רשום

מנהל פורום מחשבים

משתמש רשום

מנהל פורום מחשבים

קבצים מצורפים

משתמש רשום

משתמש רשום

משתמש רשום

מנהל פורום מחשבים

משתמש צעיר

קבצים מצורפים

משתמש צעיר

מנהל פורום מחשבים

משתמש רשום

משתמש צעיר

פרוגבוט

תוכן שיווקי

איזה כיף שהצטרפתם לניוזלטר שלנו!