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

[יעקב ויצמן]

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

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

הרבה הצלחה.

תודה רבה ידידי היקר על הברכות.
תבורך מפי עליון!

 

[שיקול דעת]

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

נראה לי שמי שמצמצה באקסל זה עבודה של כמה דקות בלבד.

ייש"כ

 

[יעקב ויצמן]

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

נראה לי שמי שמצמצה באקסל זה עבודה של כמה דקות בלבד.

ייש"כ

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

 

[שיקול דעת]

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

 

[יעקב ויצמן]

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

 

[המורה המנהלת]

איך אני ממירה טווח נתונים לטבלת נתונים

 

[א1122]

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

נראה לי שמי שמצמצה באקסל זה עבודה של כמה דקות בלבד.

ייש"כ

שעה צריך לכתוב 100 ושעתיים 200 וכן הלאה

 

[שיקול דעת]

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

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

 

[ה. שלמה]

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

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

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

לדוגמה:

מתא B1 עד תא F7 מופיעים סכומים, וב A1 עד A7 מופיעים שמות, ואני מעוניין שהוא יסכם עבורי רק את מה שמתאים ל @יעקב ויצמן או @במיטב היגיון שבעמודה A.

 

[ה. שלמה]

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

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

 

[יעקב ויצמן]

איך אני ממירה טווח נתונים לטבלת נתונים

לבדוק שיש כותרות בשורה עליונה של הטווח>
לסמן בעכבר את טווח הטבלה>בסרגל הכלים -בית ללחוץ על עצב כטבלה>להשאיר סימון "לטבלה שלי יש כותרות">ללחוץ אישור
בהצלחה

 

[יעקב ויצמן]

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

לדוגמה:

מתא B1 עד תא F7 מופיעים סכומים, וב A1 עד A7 מופיעים שמות, ואני מעוניין שהוא יסכם עבורי רק את מה שמתאים ל @יעקב ויצמן או @במיטב היגיון שבעמודה A.

מצורף פיתרון דוגמא

בהצלחה

 

[ה. שלמה]

מצורף פיתרון דוגמא
צפה בקובץ המצורף 1706352
בהצלחה

שכוייח!!

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

 

[יעקב ויצמן]

שכוייח!!

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

להכניס את התאריך בפונקציית filter בתוך פונקציית date

 

[יעקב ויצמן]

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

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

וואו. שאלה קשה ומאתגרת...
חשבתי רבות על פתרון יצירתי ובס"ד מצאתי

מה שניתן לעשות הוא כך
תראה בצילום מסך המצורף

אז קודם כל צריך לכתוב את רצף החודשים בפורמט תאריך תקני
בתא b1 יש להזין את הפונקציות להלן ולגרור למטה

=MONTH(A1)&"/"&YEAR(A1)

אתה רצית בעצם שהלינק יפנה לעמודה ליד החודש הנוכחי
החודש למשל 7/2024 זה יוצא תא c7
איך ניתן לבצע זאת?

בתא j1 יש לינק לתא בלי מספר השורה של התא

https://docs.google.com/spreadsheets/d/1BoX_QCbI1rA2LW_6Yd7qbGXUYlxPFaF8/edit?pli=1&gid=1521789984#gid=1521789984&range=C

את השורה 7 שיש לצרף לc אנחנו נחשבן בנפרד בתא i1

=MATCH(MONTH(TODAY())&"/"&YEAR(TODAY()),B1:B24,0)

ואז נחבר את הכל עם פונקציית concat בתוך פונקציית hyperlink בתא g1

=HYPERLINK(CONCAT(J1,I1),"קישור לתא של החודש הנוכחי")

מצורף לך גיליון שיטס
תעלה אותו לדרייב ותעתיק את הלינק כלומר את ה url
תדביק את הלינק בתא j1 ואחכ תמחק ידנית את השורה של התא
תראה שהלינק עובד מצוין

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

 

[ה. שלמה]

וואו. שאלה קשה ומאתגרת...
חשבתי רבות על פתרון יצירתי ובס"ד מצאתי

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

=MONTH(A1)&"/"&YEAR(A1)

אתה רצית בעצם שהלינק יפנה לעמודה ליד החודש הנוכחי
החודש למשל 7/2024 זה יוצא תא c7
איך ניתן לבצע זאת?

בתא j1 יש לינק לתא בלי מספר השורה של התא

https://docs.google.com/spreadsheets/d/1BoX_QCbI1rA2LW_6Yd7qbGXUYlxPFaF8/edit?pli=1&gid=1521789984#gid=1521789984&range=C

את השורה 7 שיש לצרף לc אנחנו נחשבן בנפרד בתא i1

=MATCH(MONTH(TODAY())&"/"&YEAR(TODAY()),B1:B24,0)

ואז נחבר את הכל עם פונקציית concat בתוך פונקציית hyperlink בתא g1

=HYPERLINK(CONCAT(J1,I1),"קישור לתא של החודש הנוכחי")

מצורף לך גיליון שיטס
תעלה אותו לדרייב ותעתיק את הלינק כלומר את ה url
תדביק את הלינק בתא j1 ואחכ תמחק ידנית את השורה של התא
תראה שהלינק עובד מצוין

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

מיוחד!!!

והשתא דאתית להכא.
מכיון שהייתי עסוק ביצירות עותק, שמתי לב שהקישור בHYPERLINK המפנה ללינק של תא בגיליון, לא משתנה ללינק של קובץ חדש בעת יצירת עותק.

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

 

[יעקב ויצמן]

מיוחד!!!

והשתא דאתית להכא.
מכיון שהייתי עסוק ביצירות עותק, שמתי לב שהקישור בHYPERLINK המפנה ללינק של תא בגיליון, לא משתנה ללינק של קובץ חדש בעת יצירת עותק.

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

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

לסיכום: צריך להכניס את הurl העדכני לתא j1

 

[ה. שלמה]

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

לסיכום: צריך להכניס את הurl העדכני לתא j1

אני חושב על פיתרון פשוט.

בכל לינק למסמך גוגל שיטס יש את החלק הראשון

https://docs.google.com/spreadsheets/d/17-ZHx_qix7Z_Pqdu8YonumKlTsiG7Bx330pmCTzO1Ew/edit

ויש את החלק השני שמציין את המיקום במסמך לצורך העניין

#gid=674300628&range=F4

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

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

עריכה: עובד פיקס.

 

[יעקב ויצמן]

אני חושב על פיתרון פשוט.

בכל לינק למסמך גוגל שיטס יש את החלק הראשון

https://docs.google.com/spreadsheets/d/17-ZHx_qix7Z_Pqdu8YonumKlTsiG7Bx330pmCTzO1Ew/edit

ויש את החלק השני שמציין את המיקום במסמך לצורך העניין

#gid=674300628&range=F4

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

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

עריכה: עובד פיקס.

מצוין וברוך ה'
שמח לשמוע!

 

[דו פעמי]

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