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

יעקב ויצמן

מנהל פורום מחשבים
מנהל
מנוי פרימיום
מצורף גיליון עבור אקסל
1720634245931.png
 

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

  • דוגמא מתוקן.xlsx
    KB 10.4 · צפיות: 18

במיטב היגיון

משתמש רשום
קוד:
=IF(C11<>"",TRANSPOSE(WRAPROWS(C20,7,C11)),"")

[ניתן גם להשתמש עם הנוסחא הזאת גם אם תיבת סימון
קודם כותבים את הנוסחא בצורה הזאת ומוסיפים תיבת סימון ב C11 ומסמנים וי ואח"כ מוסיפים תיבת סימון להכל ואז ניתן להוריד את הוי
קוד:
=IF(C11=FALSE,"",TRANSPOSE(WRAPROWS(C20,7,C11)))
אבל אז אם כבר סימנת על אחד מהתאים וי לא יהיה ניתן לסמן אח"כ על הכל
אלא רק אם עדיין לא סימנת כלום יהיה ניתן לבחור או הכל או חלק]
יפה מאוד, בהחלט רעיון
הוא ממש גאוני בהיותו הפוך שמשתמש עם הN/A בעיקר.

תודה רבה @א1122 @יעקב ויצמן

ממש מהנה פה סוף סוף אנשים לעניין, שחיים את זה ברמה ממש גבוהה...:D
 

יעקב ויצמן

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

מאמר זה הוא השני מתוך סדרת המאמרים סביב פונקציית sequence הנפלאה.

תהנו מהלוגיקה, החכמה והיופי של האקסל...
נהנתם? -נא שתפו אחרים.
אשמח לשמוע מכם תגובות/ שאלות נוספות בנושא המאמר.


תודה רבה לכם!

תודה מיוחדת וגדולה לידידינו היקר הרב @הכלבויניק על העזרה והייעוץ המקצועי/הטכני במאמר זה.ישר כח!
 

אבי2021

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

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

הרבה הצלחה.
 

יעקב ויצמן

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

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

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

שיקול דעת

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

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

ייש"כ
 

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

  • חוברת1.xlsx
    KB 9.7 · צפיות: 18

יעקב ויצמן

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

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

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

שיקול דעת

משתמש סופר מקצוען
מנוי פרימיום
בוגר/תלמיד פרוג
עיצוב גרפי
עריכה תורנית
עימוד ספרים

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

  • ביצוע מול יעד.xlsx
    KB 10.4 · צפיות: 17

יעקב ויצמן

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

א1122

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

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

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

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

  • ביצוע מול יעד.xlsx
    KB 11.9 · צפיות: 17

שיקול דעת

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

בהצלחה
 

ה. שלמה

משתמש סופר מקצוען
מנוי פרימיום

יעקב ויצמן

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

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

יעקב ויצמן

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

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

מה שניתן לעשות הוא כך
תראה בצילום מסך המצורף
צילום מסך 2024-07-14 001722.png

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

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

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

  • לינק לתא לפי חודש -דינמי.xlsx
    KB 52 · צפיות: 14

ה. שלמה

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

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

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

אולי מעניין אותך גם...

הפרק היומי

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


תהילים פרק קכו

א שִׁיר הַמַּעֲלוֹת בְּשׁוּב יי אֶת שִׁיבַת צִיּוֹן הָיִינוּ כְּחֹלְמִים:ב אָז יִמָּלֵא שְׂחוֹק פִּינוּ וּלְשׁוֹנֵנוּ רִנָּה אָז יֹאמְרוּ בַגּוֹיִם הִגְדִּיל יי לַעֲשׂוֹת עִם אֵלֶּה:ג הִגְדִּיל יי לַעֲשׂוֹת עִמָּנוּ הָיִינוּ שְׂמֵחִים:ד שׁוּבָה יי אֶת (שבותנו) שְׁבִיתֵנוּ כַּאֲפִיקִים בַּנֶּגֶב:ה הַזֹּרְעִים בְּדִמְעָה בְּרִנָּה יִקְצֹרוּ:ו הָלוֹךְ יֵלֵךְ וּבָכֹה נֹשֵׂא מֶשֶׁךְ הַזָּרַע בֹּא יָבוֹא בְרִנָּה נֹשֵׂא אֲלֻמֹּתָיו:
נקרא  100  פעמים

אתגר AI

קוביסט • אתגר 144

לוח מודעות

למעלה