התקשרו אלינו: 052-2928949

התקשרו אלינו: 052-2928949

תיקון תאריכים שמגיעים בקובץ אקסל בפורמט אמריקאי

Power Query

תיקון תאריכים שמגיעים בקובץ אקסל בפורמט אמריקאי

Power Query

מודה – תאריכים באקסל הם בלבול מוח לא קטן,  תיקון תאריכים – בלבול מוח גדול…
בארץ אנו כותבים תאריכים בפורמט dd/mm/yy.
בארה"ב הפורמט המקובל הוא mm/dd/yy

איך נקבע פורמט התאריך?

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

כאשר אנו כותבים את התאריך 3/4/2000 אנו מתכוונים לשלושה באפריל, ואילו האמריקאים מתכוונים לארבעה במרץ.
כאשר מדובר ב 31/12/2000, אנו מתכוונים ל 31 בדצמבר, ואילו עבור האמריקאים – אין תאריך כזה, משום שאין יום 12 בחודש ה-31.

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

כאשר אנו מקבלים קובץ שמקורו בארה"ב, ובו הנתונים הבאים:

במחשב שההגדרות האיזוריות הן אמריקאיות התאריך הראשון חל בחודש מרץ, השני חל באפריל והשלישי בדצמבר.

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

תיקון תאריכים שמגיעים מקובץ CSV

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

והיום? Power Query הוא הפתרון!

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

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

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

תיפתח החלונית הבאה:

נקליד תו רווח ונאשר

וזו התוצאה שתתקבל:

כעת נפצל את הנתונים על פי מפריד:

יפתח החלון הבא:

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

בשלב האחרון עלינו לסמן את העמודות על פי הסדר הנכון: ראשית את עמודת היום (העמודה השנייה), לאחר מכן עמודת החודש (העמודה הראשונה) ולסיום עמודת השנה (העמודה השלישית)

בכרטיסיית 'המר' נלחץ על 'מיזוג עמודות', נבחר במפריד לוכסן וניתן שם לעמודה:

תתקבל עמודה שמכילה את התאריך המתוקן:

זהו, כל שנותר הוא לטעון את הטבלה המתוקנת אל תוך האקסל שלנו.

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

אז אם אתם מקבלים באופן קבוע קבצים מארה"ב, כל שעליכם לעשות הוא רק לשנות את מקור הנתונים בשלב הראשון תחת 'שלבים שהוחלו', ותקבלו מייד טבלת תאריכים מתוקנת!

מדהים, לא?

ולמי שתוהה למה אני לא משתמשת באפשרות המובנית לקבוע את האיזור:

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

בחלון שיפתח נשנה את סוג הנתונים לתאריך ואת האיזור לארה"ב:

נאשר ונטען.
ואפשר לקבוע את ההגדרה גם לכל חוברת העבודה:
נבחר ב'קבל נתונים' >'אפשרויות שאילתה' ותחת חוברת העבודה הנוכחית נבחר בהגדרות איזוריות ותחת האיזור נבחר את השפה:

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

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

רוצים ללמוד Power Query?

תוך כמה דקות הספר הזה, יכול להיות אצלכם:

מעוניינים? לחצו כאן

דילוג לתוכן