Excel: חישוב שבועות - כך זה עובד עם נוסחה

מצא את הפונקציה הנכונה

ב- Microsoft Excel, הפונקציה 'לוח השנה' זמינה באמצעותה תוכל להציג את שבוע הקלנדרים לתאריך ספציפי. לדוגמה, תוכל להציג באופן אוטומטי באיזה שבוע מתקיימת פגישה. לפונקציית הטבלה CALENDAR WEEK ב- Excel יש גם את המכשולים שלה. הסיבה לכך היא כי שבוע קלנדרי מוגדר אחרת בגרמניה, באוסטריה ובשוויץ מאשר במדינות אירופה השכנות או ארה"ב. מאמר זה מראה כיצד אתה עדיין יכול לחשב את השבוע הקלנדרי הנכון ואת ההבדלים בין השבוע הקלנדרי באזור DACH ומדינות אחרות.

הפונקציה CALENDAR WEEK ב- Excel

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

התחביר לחישוב השבוע הקלנדרי הוא:

= שבוע יומן (תאריך)

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

קבע נכון את שבועות הלוח השנה ב- Excel - כך זה עובד

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

= TRIM ((A4-DATE (YEAR (A4-REMAIN (A4-2,7) +3); 1; REMAIN (A4-2,7) -9)) / 7)

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

• הפונקציה REST (A4-2; 7) מופיעה מספר פעמים בנוסחה. פונקציה זו מחלקת את התאריך ב- 7 ומחזירה את שאר התוצאה. פונקציה זו מחזירה את הערך 0 לימי שני, הערך 1 לימי שלישי עד לערך 6 לימי ראשון.
• בנוסחה, המספר 2 מופחת מערך התאריך. הסיבה לכך היא שתאריך הבסיס 1/1/1900 היה יום ראשון. מחולק ב -7 תוצאות בשארית 1. מסיבה זו, יום שני ייפתר למספר 2. כדי לתקן את ספירת הימים ל -0 ליום ראשון, 1 ליום שני וכו ', המספר 2 מופחת מהתאריך.
• בהתבסס על חישוב יום חול זה, החלק (YEAR (A4-REST (A4-2; 7) +3) מספק ערך שנתי מופחת ב -1 לשלושת הימים הראשונים בשנה, בתנאי שימים אלה הם ימי שישי, שבת או ראשון מעשים.
• ערך שנתי זה נכלל בתאריך בתוך הפונקציה DATE. תאריך זה נוצר בדרך כלל עם הטענה השנייה 1 לחודש ינואר.
• כערך היומי, העבר את הפונקציה REST שתוארה לעיל לפונקציית DATE בניכוי הערך 9. זה בדרך כלל גורם לערך יומי שלילי. הנוסחה מראה שהתאריך המחושב עד DATE הוא בדרך כלל בדצמבר.
• עם זאת, בשלושת הימים הראשונים בשנה, מדובר בדצמבר בשנה שלפני הלפני אחרון, בתנאי ששלושת הימים הללו הם ימי שישי, שבת או ראשון.
• אתה מפחית תאריך זה המחושב על ידי הפונקציה DATE מהתאריך בעמודה A, מחלק את ההפרש ב -7 (במשך 7 הימים בשבוע) אתה משתמש בכל החישוב הזה בתוך פונקציית SHORT על מנת להשיג רק ערכים שלמים.

לחלופין: השתמש בארגומנט הפונקציה WOCHENSTART

אפשרות נוספת לעבוד עם הפונקציה CALENDAR WEEK ב- Microsoft Excel ולהשיג תוצאות נכונות היא להוסיף את נוסחת טיעון שבוע הפונקציה לפורמולה. בדומה לנוסחה המוצגת לעיל, נדרש מאמץ לבנות את הנוסחה.

ארגומנט הפונקציה WOCHENSTART יכול להבדיל בין שתי המערכות השונות לחישוב השבוע הקלנדרי:

1. מערכת 1 (מערכת אמריקאית) - מדווח על שבוע 1 כשבוע הכולל את ינואר הראשון.
2. מערכת 2 (תקן אירופאי על פי ISO 8601) - KW 1 הוא השבוע הכולל את יום חמישי הראשון בשנה.

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

יוֹם שֵׁנִי	2 או 11
יוֹם שְׁלִישִׁי	12
יום רביעי	13
יוֹם חֲמִישִׁי	14
יוֹם שִׁישִׁי	15
יום שבת	16
יוֹם רִאשׁוֹן	1 או 17

קשר את הפונקציה CALENDAR WEEK עם ארגומנט הפונקציה WOCHENSTART ב -6 שלבים

כדי לקשר בין הפונקציה שבוע קלנדרי לבין תחילת שבוע הטיעון בפונקציה, בצע את הפעולות הבאות:

1. הזן תאריך בגיליון האלקטרוני של Excel.

2. לחץ באמצעות לחצן העכבר השמאלי בתא שבו ברצונך לשלב את השבוע הקלנדרי.

3. עבור ללשונית FORMULAS ולחץ באמצעות לחצן העכבר השמאלי על "הוסף פונקציה".

   

4. הזן שבוע יומן בשדה החיפוש.

   

5. לאחר שמצאת את הפונקציה CALENDAR WEEK, אשר את בחירתך באישור.

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

   • Serial_number = תא המכיל את התאריך, למשל A1.
   • סוג מספר = ערך מספרי ליום השבוע על פי הטבלה לעיל.
   

השבוע הקלנדרי פלט בצורה נכונה על פי ISO 8601.

כיצד לחשב את ההתחלה והסוף של כל שבוע קלנדרי ב- Excel

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

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

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

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

= טקסט (DATE (מספר שנה; 1; 7 * calweek-3-
WEEKDAY (DATE (מספר שנה ;;); 3)); "DD.MM.YY")
& "-" & טקסט (DATE (מספר שנה; 1; 7 * שבוע קלנדרי + 3-
WEEKDAY (DATE (מספר שנה ;;); 3)); "DD.MM.YY")

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

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

בחוברת העבודה המוצגת, הנוסחה הבאה בתא D4 מספקת את התקופה "30/09/13 - 10/6/13" כתוצאה מכך:

= טקסט (תאריך (C4; 1; 7 * B4-3-WEEKDAY
(DATE (C4 ;;); 3)); "DD.MM.YY") & "-" & TEXT
(DATE (C4; 1; 7 * B4 + 3-WEEKDAY (DATE (C4 ;;); 3));
"DD.MM.YY")

תקופה זו היא שבוע קלנדרי 40 מתא B4 בשנת 2013 מתא C4.

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

= טקסט (תאריך (C4; 1; 7 * B4-3-WEEKDAY
(DATE (C4 ;;); 3)); "DDD DD.MM.
YY “) &“ - “& TEXT (DATE (C4; 1; 7 * B4 + 3-
WEEKDAY (DATE (C4 ;;); 3)); "DDDDD.MM.YY")

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

כיצד לחשב שבועות ב- Excel ולמצוא את התאריך הנכון

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

האיור הבא מציג רשימה של ערכי תאריך בעמודה A. בעמודה B יש ערך המציין כמה שבועות יש לדחות ערך תאריך זה:

בעמודה C אתה רוצה להשתמש בנוסחה שמחשבת את התאריך החדש על סמך התאריך בעמודה A ומספר השבועות בעמודה B. אם מספר השבועות הוא ערך גדול מאפס, התאריך מועבר לעתיד, אם הערך שלילי, הוא מועבר לעבר.

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

= A1 + 7 * B1

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

מסקנה: שבועות לוח שנה הם חיוניים לניהול פגישות מקצועי

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

Microsoft Excel מסוגלת לציין את השבוע הקלנדרי המתאים לכל תאריך. הגורם המכריע כאן הוא שמשתמשים באירופה מתבוננים במערכת התאריכים האירופית על פי ISO 8601 בעת בניית נוסחאות האקסל שלהם. כברירת מחדל, השבוע הקלנדרי מחושב על פי המערכת האמריקאית, מה שמוביל לסטיות. בארצות הברית, היום הראשון בשנה מוקצה בדרך כלל לשבוע הקלנדרי הראשון. בגרמניה, אוסטריה ושוויץ, לעומת זאת, השבוע הקלנדרי הראשון מוגדר בדרך כלל כשבוע שבו, כשבוע הראשון בשנה, מורכב מארבעה ימים לפחות. בפועל, המשמעות היא ששבוע 1 במערכת האירופית הוא השבוע הכולל את יום חמישי הראשון בשנה.

לחישוב הנכון של השבוע הקלנדרי, תוכל להשתמש הן בפונקציה CALENDAR WEEK והן בארגומנט פונקציית WEEKSTART ב- Excel. אפשר גם להגדיר נוסחאות בודדות ולהתאים אותן באופן ספציפי לגיליון האלקטרוני שלך.