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

[יעקב ויצמן]

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

יצרתי עמודה של חודשים {DD/YYYY} (A:A) + עמודה נוספת של סכום לחודש זה (B:B) + עמודה נוספת סך התשלום למנוי ספציפי (C:C)

איזה נוסחה תעזור לי ?

א. [הרעיון שאמור להיות - אם תאריך X קטן מסוף חודש של תא (A1) & אם תאריך Y גדול מתחילת חודש של תא (A1)]
ב. [ספירת כמות הימים - מתארך X עד תאריך Y - בחודש זה]

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

השאלות כאן הן מאוד מאתגרות
להשגת פיתרון מתאים עמלתי זמן רב,וברוך ה' הפיתרון הושג.
לכל מי שמעונין בצלילה הרפתקנית בים האקסל והפונקציות.אני אסביר בקצרה על "כל הטוב" שיש לפנינו.ובכן:
בעמודה c ישנה רשימת חודשים בפורמט תאריך (כמובן)
בעמודה e ישנם תעריפים משתנים (זהו מקור האתגר פה).כל חודש עם תעריף אחר.
עמודה a-נוספה והיא מציגה את היום הראשון באותו חודש.על ידי פונקציית eomonth

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

בעמודה d מחושב ההפרש בין עמודה a לעמודה b .זהו למעשה מספר הימים באותו חודש.על ידי פונקציית datedif

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

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

עמודות f.g.h.i.נוספו לצורך חישוב הימים בין תחילת/סוף חודש המנוי
אסביר את הפונקציות בתא f2 ,אך הרעיון המרכזי בכל עמודות אלו זהה.

פונקציית הxlookup "רואה" את הערך בעמודה c (החודש לחישוב-בעזרת פונקציית monts ששואבת רק את נתון "חודש" מהתאריך).ומחפשת אותו בעמודה p .
אם הוא נמצא מחושב הפרש הימים בין תחילת המנוי לבין תחילת אותו חודש(בעזרת פונקציית days).זה אומר לנו כמה ימים עברו מתחילת אותו חודש של המנוי.עד כניסת תאריך המנוי לתוקף.ואידך זיל גמור...

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

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

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

וזהו כבר צילום מסך של כל הגיליון על כל חישוביו

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

בהצלחה רבה לך!

 

[תגרין]

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

 

[יעקב ויצמן]

איך מקפיאים עמודה B באקסל בשיטס?

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

, כשמקפיאים הוא מקפיא רק עמודה ראשונה.

תראי כאן מדריך עבור שיטס עם 2 שיטות

How to Freeze a Row in Google Sheets, Columns Too! [Easy]

Learn how to freeze a row in Google Sheets which can help be a lot more efficient when working with large datasets. This works for columns too.

spreadsheetpoint.com

מדריך לאקסל ראי בלינק כאן

How to freeze rows and columns in Excel

See how to freeze cells in Excel by using the Freeze Panes command and other features. Learn how to freeze one or more rows, lock columns, or freeze multiple columns and rows at the same time.

www.ablebits.com

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

 

[תגרין]

תודה רבה עבד מצוין

 

[יעקב ויצמן]

תודה רבה עבד מצוין

ברוך ה'
שמח לשמוע

 

[ישראלי1]

השאלות כאן הן מאוד מאתגרות
להשגת פיתרון מתאים עמלתי זמן רב,וברוך ה' הפיתרון הושג.
לכל מי שמעונין בצלילה הרפתקנית בים האקסל והפונקציות.אני אסביר בקצרה על "כל הטוב" שיש לפנינו.ובכן:
בעמודה c ישנה רשימת חודשים בפורמט תאריך (כמובן)
בעמודה e ישנם תעריפים משתנים (זהו מקור האתגר פה).כל חודש עם תעריף אחר.
עמודה a-נוספה והיא מציגה את היום הראשון באותו חודש.על ידי פונקציית eomonth
צפה בקובץ המצורף 1457598הפונקצייה בודקת את היום האחרון של חודש שעבר(1-).ואז מוסיפה עוד יום אחד קדימה.זהו בדיוק היום הראשון של החודש.
עמודה b-נוספה ובה יוצג היום האחרון של אותו החודשצפה בקובץ המצורף 1457602
בעמודה d מחושב ההפרש בין עמודה a לעמודה b .זהו למעשה מספר הימים באותו חודש.על ידי פונקציית datedif
צפה בקובץ המצורף 1457605
היה צורך בהוספת +1 בגלל אופן החישוב בפונקצייה של יום ההתחלה /הסיום..עיין ערך סוגיית "עד ועד בכלל או עד ולא עד בכלל וכו...(אולי בהזדמנות ארחיב על כל סוגיא זו בהקשר לאקסל כמובן...)

עמודות p.q סודרו מחדש עם גרירת ערך תא העליון (עם קיבוע שורה)
לצורך פונקציית xlookup שתכף נראה..
צפה בקובץ המצורף 1457653

עמודות f.g.h.i.נוספו לצורך חישוב הימים בין תחילת/סוף חודש המנוי
אסביר את הפונקציות בתא f2 ,אך הרעיון המרכזי בכל עמודות אלו זהה.
צפה בקובץ המצורף 1457659
פונקציית הxlookup "רואה" את הערך בעמודה c (החודש לחישוב-בעזרת פונקציית monts ששואבת רק את נתון "חודש" מהתאריך).ומחפשת אותו בעמודה p .
אם הוא נמצא מחושב הפרש הימים בין תחילת המנוי לבין תחילת אותו חודש(בעזרת פונקציית days).זה אומר לנו כמה ימים עברו מתחילת אותו חודש של המנוי.עד כניסת תאריך המנוי לתוקף.ואידך זיל גמור...

אם נרצה לומר לאקסל לשלוף את הפרש הימים בחודש מסוים של תחילת/סוף המנוי.זו בעיה מורכבת..
השאלה הגדולה היא האם לשלוף את מספר הימים לפני תחילת המנוי או אחרי?
לאחר הרבה מחשבה וניסויים זו הפונקצייה שעשיתי.יש בה הרבה לוגיקה וענין..תהנו...
צפה בקובץ המצורף 1457751
זה פשוט עובד...ברוך ה'.
מה שנותר הוא לחלק את תעריף החודש במספר ימי החודש.התוצאה היא תעריף יומי.ואז להכפיל במספר הימים לחיוב.
וזו אכן הנוסחא בעמודה l
צפה בקובץ המצורף 1457752
בעמודה l הסכום שמתקבל הוא על פי חודש.
הסיכום הסופי לתשלום בשורה תחתונה.הוא בעמודה m עם פונקציית sum (על סכומי עמודה l) קטנטנה אך מסכמת את כל המסע עד כה...
עד כאן.ובקצרה...

וזהו כבר צילום מסך של כל הגיליון על כל חישוביו
צפה בקובץ המצורף 1457753

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

בהצלחה רבה לך!

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

 

[יעקב ויצמן]

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

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

 

[n&s]

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

 

[GTV]

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

תודה רבה!

 

[ה. שלמה]

השאלות כאן הן מאוד מאתגרות
להשגת פיתרון מתאים עמלתי זמן רב,וברוך ה' הפיתרון הושג.
לכל מי שמעונין בצלילה הרפתקנית בים האקסל והפונקציות.אני אסביר בקצרה על "כל הטוב" שיש לפנינו.ובכן:
בעמודה c ישנה רשימת חודשים בפורמט תאריך (כמובן)
בעמודה e ישנם תעריפים משתנים (זהו מקור האתגר פה).כל חודש עם תעריף אחר.
עמודה a-נוספה והיא מציגה את היום הראשון באותו חודש.על ידי פונקציית eomonth
צפה בקובץ המצורף 1457598הפונקצייה בודקת את היום האחרון של חודש שעבר(1-).ואז מוסיפה עוד יום אחד קדימה.זהו בדיוק היום הראשון של החודש.
עמודה b-נוספה ובה יוצג היום האחרון של אותו החודשצפה בקובץ המצורף 1457602
בעמודה d מחושב ההפרש בין עמודה a לעמודה b .זהו למעשה מספר הימים באותו חודש.על ידי פונקציית datedif
צפה בקובץ המצורף 1457605
היה צורך בהוספת +1 בגלל אופן החישוב בפונקצייה של יום ההתחלה /הסיום..עיין ערך סוגיית "עד ועד בכלל או עד ולא עד בכלל וכו...(אולי בהזדמנות ארחיב על כל סוגיא זו בהקשר לאקסל כמובן...)

עמודות p.q סודרו מחדש עם גרירת ערך תא העליון (עם קיבוע שורה)
לצורך פונקציית xlookup שתכף נראה..
צפה בקובץ המצורף 1457653

עמודות f.g.h.i.נוספו לצורך חישוב הימים בין תחילת/סוף חודש המנוי
אסביר את הפונקציות בתא f2 ,אך הרעיון המרכזי בכל עמודות אלו זהה.
צפה בקובץ המצורף 1457659
פונקציית הxlookup "רואה" את הערך בעמודה c (החודש לחישוב-בעזרת פונקציית monts ששואבת רק את נתון "חודש" מהתאריך).ומחפשת אותו בעמודה p .
אם הוא נמצא מחושב הפרש הימים בין תחילת המנוי לבין תחילת אותו חודש(בעזרת פונקציית days).זה אומר לנו כמה ימים עברו מתחילת אותו חודש של המנוי.עד כניסת תאריך המנוי לתוקף.ואידך זיל גמור...

אם נרצה לומר לאקסל לשלוף את הפרש הימים בחודש מסוים של תחילת/סוף המנוי.זו בעיה מורכבת..
השאלה הגדולה היא האם לשלוף את מספר הימים לפני תחילת המנוי או אחרי?
לאחר הרבה מחשבה וניסויים זו הפונקצייה שעשיתי.יש בה הרבה לוגיקה וענין..תהנו...
צפה בקובץ המצורף 1457751
זה פשוט עובד...ברוך ה'.
מה שנותר הוא לחלק את תעריף החודש במספר ימי החודש.התוצאה היא תעריף יומי.ואז להכפיל במספר הימים לחיוב.
וזו אכן הנוסחא בעמודה l
צפה בקובץ המצורף 1457752
בעמודה l הסכום שמתקבל הוא על פי חודש.
הסיכום הסופי לתשלום בשורה תחתונה.הוא בעמודה m עם פונקציית sum (על סכומי עמודה l) קטנטנה אך מסכמת את כל המסע עד כה...
עד כאן.ובקצרה...

וזהו כבר צילום מסך של כל הגיליון על כל חישוביו
צפה בקובץ המצורף 1457753

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

בהצלחה רבה לך!

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

 

[יעקב ויצמן]

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

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

 

[ה. שלמה]

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

והסיבה היא פשוטה מכיון שפונקציית XLOOKUP לא יכולה למצוא בעמודה p וq את החודש האמצעי שלא מופיע בו, בכדי לעקוף את הבעיה ניתן להוסיף לעמודה j התניה אם מופיע בעמודה k הטקסט "פעיל" הרי שיוחזר הערך המופיע בעמודה d ואם לא שיופיע החשבון שקיים שם.

 

[ה. שלמה]

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

ברוך שכוונתי וכוונת.

 

[יעקב ויצמן]

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

באקסל או בשיטס?

 

[יעקב ויצמן]

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

תודה רבה

תשלחי בבקשה גיליון דוגמא תוך שימת דגש על:
1.שם העמודה של תאריך עברי
2.פורמט התאריך עברי במדויק
(לדוגמא.
התשפג או תשפג?
תשפג או תשפ"ג?)
ועוד שאלה.האם מדובר באקסל או בשיטס?
תודה

 

[לעזור לכולם]

עזרה באקסל - בקשת נוסחה באקסל

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

www.prog.co.il

 

[Yanky Itskowitz]

יש אפשרות לבנות באקסל סוג של אגרון/זהותון?

 

[יעקב ויצמן]

תשלחי בבקשה גיליון דוגמא תוך שימת דגש על:
1.שם העמודה של תאריך עברי
2.פורמט התאריך עברי במדויק
(לדוגמא.
התשפג או תשפג?
תשפג או תשפ"ג?)
ועוד שאלה.האם מדובר באקסל או בשיטס?
תודה

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

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

 

[יעקב ויצמן]

באקסל או בשיטס?

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

Random sort

To sort a list or table in a random order, you can use the SORTBY function with the RANDARRAY function. In the example shown, the formula in D5 is: =SORTBY(data,RANDARRAY(ROWS(data))) where data is the named range B5:B16. The result is a new random sort order whenever the worksheet is...

exceljet.net

עבור גוגל שיטס ראה כאן

How To Randomize a List in Google Sheets (3 Easy Ways)

In this tutorial you will see how to randomize a list in Google Sheets using the new Randomize range feature, along with other traditional methods.

spreadsheetpoint.com

בהצלחה

 

[ישר]

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