עזרה באקסל אקסל - חישוב טווח שעות ופילוח שעות יום ולילה

[יעקב ויצמן]

בנוסף למה ששאלתי בהודעה 20 ובהודעה 21, ארצה לשאול שאלה נוספת:
האם יש אפשרות לעשות 'עיצוב מותנה' לשורה שלמה לפי 2 תנאים משני תאים שונים?

למשל באם בתא C כתוב ב או ג
ובתא D הערך הוא גובה מ-2,
אז שכל השורה תקבל עיצוב שונה.

התשובה היא שכן
תראה במדריך בלינק המצורף

עיצוב מותנה באקסל לפי שני תנאים

צפו כיצד לבנות עיצוב מותנה לשורה באקסל לפי שני תנאים שונים.www.clickenter.co.il

youtu.be

 

[צביזון]

1. מצ"ב
2. להכפיל ב24 ולשנות לפורמט מספר רגיל
3. רושמים את שעת היום הפותחת בתא מסוים ונותנים לו שם, בקובץ השם שנתתי לתא הוא _7 (קודם קו תחתון ואח"כ 7) כנ"ל לשעת היום הסוגרת, בקובץ השם _22 ואח"כ בנוסחה במקום לרשום time(7,0,0) ו time(22,0,0) משתמשים בשמות, _7 ו _22. הדרך הזאת מפשטת את התחביר, האורך והקריאה של הנוסחה.

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

בנוסף, מדוע הנוסחה הזו לא עובדת באם השעות כתובות יחד עם שניות, כך: 20:30:00

 

[יעקב ויצמן]

בנוסף, מדוע הנוסחה הזו לא עובדת באם השעות כתובות יחד עם שניות, כך: 20:30:00

תשנה בעמודות c.d פורמט hh:mm:ss

 

[צביזון]

אני מדבר על הנוסחה שמחשבת לפי תא עם שם, בקובץ של @xl3391
גם כאשר אני משנה את הפורמט כנ"ל, זה לא עוזר.

 

[xl3391]

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

נוחות. (כתיבה, קריאה, הבנה ואורך)

מדוע הנוסחה הזו לא עובדת באם השעות כתובות יחד עם שניות, כך: 20:30:00

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

 

[צביזון]

נוחות. (כתיבה, קריאה, הבנה ואורך)


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

אני מעלה כאן את הקובץ הבעייתי.

קודם כל לשים לב שהוא משמאל לימין.

אני אתן עליו קצת רקע
עמודות A-E זה העמודות בקובץ המקור.

כיון שכאן התאריך והשעון באותה עמודה, לכן דבר ראשון:
פיצלתי את השעה מתוך עמודה C לעמודה לעמודה I את שעת הכניסה,
ומתוך עמודה B לעמודה G את שעת היציאה.

בנוסף עשיתי עמודה לחישוב שעות בעמודה E
ובעמודה J שם שמתי את הנוסחה לחישוב שעות יום.

אבל זה לא מחשב לי אא"כ אסיר את השניות.

אמנם בכל השורות השעה היא ע"י נוסחה (אולי זה הגורם?)
אבל רק בשורה 2 הדבקתי את השעה כערך, וגם שם זה לא מחשב רק כשאני מסיר את השניות.

תודה רבה.

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

 

[xl3391]

אני מעלה כאן את הקובץ הבעייתי.

קודם כל לשים לב שהוא משמאל לימין.

אני אתן עליו קצת רקע
עמודות A-E זה העמודות בקובץ המקור.

כיון שכאן התאריך והשעון באותה עמודה, לכן דבר ראשון:
פיצלתי את השעה מתוך עמודה C לעמודה לעמודה I את שעת הכניסה,
ומתוך עמודה B לעמודה G את שעת היציאה.

בנוסף עשיתי עמודה לחישוב שעות בעמודה E
ובעמודה J שם שמתי את הנוסחה לחישוב שעות יום.

אבל זה לא מחשב לי אא"כ אסיר את השניות.

אמנם בכל השורות השעה היא ע"י נוסחה (אולי זה הגורם?)
אבל רק בשורה 2 הדבקתי את השעה כערך, וגם שם זה לא מחשב רק כשאני מסיר את השניות.

תודה רבה.

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

1. בקובץ שלכם הנוסחה נרשמה הפוך-במקום שצריך להפנות לשעת כניסה הפנתה לשעת יציאה ולהיפך.
2. במקום לשלוף את התאריכים והשעות כטקסט עדיף להשתמש בפונקציות datevalue ו timevalue וכך למנוע שגיאות לא צפויות.
3. ברגע שיש לכם עמודות כניסה ויציאה עם התאריך והשעות יחד, אין צורך בנוסחה מיוחדת לחשב את השעות, רק התא של היציאה פחות התא של הכניסה. (אצלכם B פחות C)

מצ"ב קובץ מתוקן

 

[צביזון]

1. בקובץ שלכם הנוסחה נרשמה הפוך-במקום שצריך להפנות לשעת כניסה הפנתה לשעת יציאה ולהיפך.
2. במקום לשלוף את התאריכים והשעות כטקסט עדיף להשתמש בפונקציות datevalue ו timevalue וכך למנוע שגיאות לא צפויות.
3. ברגע שיש לכם עמודות כניסה ויציאה עם התאריך והשעות יחד, אין צורך בנוסחה מיוחדת לחשב את השעות, רק התא של היציאה פחות התא של הכניסה. (אצלכם B פחות C)

מצ"ב קובץ מתוקן

לגבי 3 - ניסיתי לעשות B פחות C וזה מראה שגיאה.
אצלכם זה נתן תוצאה?

אגב, מדוע כשאני עומד על תא המחשב שעות יום, אני רואה שהנוסחה נמצאת בתוך סוגריים מסולסלות {} ?

 

[xl3391]

לגבי 3 - ניסיתי לעשות B פחות C וזה מראה שגיאה.
אצלכם זה נתן תוצאה?

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

אגב, מדוע כשאני עומד על תא המחשב שעות יום, אני רואה שהנוסחה נמצאת בתוך סוגריים מסולסלות {} ?

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

 

[צביזון]

מצורף צילום מסך:

 

[צביזון]

נ.ב. למה לחשב ולא להשתמש בנתון של כמות שעות שרשום בעמודה A?

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

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

 

[xl3391]

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

את השם _6 כדאי למחוק לגמרי

לאחר השינויים:

 

[צביזון]

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

לאחר השינויים:
צפה בקובץ המצורף 1431906

היכן נמצא הדיאלוג הנ"ל?
ואיזו בעיה הוא גורם כעת? את הסוגריים המסולסלות?

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

 

[xl3391]

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

 

[צביזון]

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

כן. באונידרייב.

 

[xl3391]

מצ"ב

היכן נמצא הדיאלוג הנ"ל?

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

ואיזו בעיה הוא גורם כעת? את הסוגריים המסולסלות?

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

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

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

 

[צביזון]

מצ"ב

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

יוצא מצוין!

אני רואה שהנוסחה שם היא =ROUND($E2-$A2,8)=0

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

 

[צביזון]

@xl3391 מדוע כשאני מעתיק מגיליון אקסל מהמחשב אל תוך הגיליון שב365 אונליין, הוא מעתיק אותו בצורה לא טובה, מה שגורמת לנוסחה TIMEVALUE לא לעבוד?

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

 

[xl3391]

זה אומר להוריד 8 עשרוניות?

להשאיר 8 ספרות

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

נראה שמספיק דיוק של 5 ספרות (שניה בעשרוני היא 0.000011574)
בחרתי 8 לדיוק בטוח של השניות, אבל אפשר בהחלט לשנות לפחות.

@xl3391 מדוע כשאני מעתיק מגיליון אקסל מהמחשב אל תוך הגיליון שב365 אונליין, הוא מעתיק אותו בצורה לא טובה, מה שגורמת לנוסחה TIMEVALUE לא לעבוד?

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

הניסיון שלי באונליין מועט. בכל אופן, אין לי אפשרות לפתוח קובץ rar . אם תשלחו בזיפ אנסה בע"ה לראות.

 

[צביזון]

להשאיר 8 ספרות


נראה שמספיק דיוק של 5 ספרות (שניה בעשרוני היא 0.000011574)
בחרתי 8 לדיוק בטוח של השניות, אבל אפשר בהחלט לשנות לפחות.


הניסיון שלי באונליין מועט. בכל אופן, אין לי אפשרות לפתוח קובץ rar . אם תשלחו בזיפ אנסה בע"ה לראות.

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