תהליכים עסקיים רבים (ואפילו עסקים שלמים) בחיים אלה כרוכים במילוי הזמנות על ידי מספר מצומצם של מבצעים עד תאריך יעד נתון. תכנון במקרים כאלה מתרחש, כמו שאומרים, "מהלוח שנה" ולעתים קרובות יש צורך להעביר את האירועים המתוכננים בו (הזמנות, פגישות, משלוחים) ל-Microsoft Excel - להמשך ניתוח לפי נוסחאות, טבלאות ציר, תרשימים, וכו '
כמובן שהייתי רוצה ליישם העברה כזו לא על ידי העתקה מטופשת (שזה פשוט לא קשה), אלא עם עדכון אוטומטי של הנתונים כך שבעתיד כל השינויים שיבוצעו בלוח השנה וההזמנות החדשות יוצגו ב- לְהִצטַיֵן. ניתן ליישם ייבוא כזה תוך דקות ספורות באמצעות תוסף Power Query המובנה ב-Microsoft Excel, החל מגרסת 2016 (עבור Excel 2010-2013, ניתן להוריד אותה מאתר Microsoft ולהתקין אותה בנפרד מהקישור) .
נניח שנשתמש ב-Google Calendar החינמי לתכנון, בו יצרתי, מטעמי נוחות, לוח שנה נפרד (הכפתור עם סימן פלוס בפינה הימנית התחתונה ליד לוחות שנה אחרים) עם הכותרת תיק עבודות. כאן אנו מכניסים את כל ההזמנות שיש להשלים ולספק ללקוחות בכתובות שלהם:
על ידי לחיצה כפולה על כל הזמנה, תוכל להציג או לערוך את פרטיה:
שים לב ש:
- שם האירוע הוא מנהלמי ממלא את הצו הזה (אלנה) ו מספר הזמנה
- מצוין כתובת מסירה
- ההערה מכילה (בשורות נפרדות, אך בכל סדר) את פרמטרי ההזמנה: סוג תשלום, סכום, שם לקוח וכו' בפורמט פרמטר=ערך.
לשם הבהירות, ההזמנות של כל מנהל מודגשות בצבע משלו, אם כי אין צורך בכך.
שלב 1. קבל קישור ליומן Google
ראשית עלינו לקבל קישור אינטרנט ליומן ההזמנות שלנו. לשם כך, לחץ על הכפתור עם שלוש נקודות אפשרויות לוח שנה עבודה ליד שם היומן ובחר את הפקודה הגדרות ושיתוף:
בחלון שנפתח תוכלו, אם תרצו, להפוך את היומן לציבורי או לפתוח אליו גישה למשתמשים בודדים. אנחנו צריכים גם קישור לגישה פרטית ליומן בפורמט iCal:
שלב 2. טען נתונים מהלוח ל- Power Query
כעת פתח את Excel ובכרטיסייה נתונים (אם יש לך Excel 2010-2013, אז בכרטיסייה שאילתת כוח) בחר פקודה מהאינטרנט (נתונים - מהאינטרנט). לאחר מכן הדבק את הנתיב שהועתק ללוח השנה ולחץ על אישור.
ה-iCal Power Query לא מזהה את הפורמט, אבל קל לעזור. בעיקרו של דבר, iCal הוא קובץ טקסט רגיל עם נקודתיים כמפריד, ובתוכו הוא נראה בערך כך:
אז אתה יכול פשוט ללחוץ לחיצה ימנית על הסמל של הקובץ שהורד ולבחור את הפורמט הכי קרוב במשמעותו CSV – והנתונים שלנו לגבי כל ההזמנות ייטענו לתוך עורך השאילתות של Power Query ויחולקו לשתי עמודות לפי נקודתיים:
אם אתה מסתכל היטב, אתה יכול לראות בבירור כי:
- מידע על כל אירוע (סדר) מקובץ לבלוק שמתחיל במילה BEGIN ומסתיים ב-END.
- תאריכי ההתחלה והסיום מאוחסנים במחרוזות המסומנות DTSTART ו-DTEND.
- כתובת המשלוח היא LOCATION.
- הערת הזמנה – שדה DESCRIPTION.
- שם האירוע (שם מנהל ומספר הזמנה) — שדה סיכום.
נותר לחלץ את המידע השימושי הזה ולהפוך אותו לטבלה נוחה.
שלב 3. המר לתצוגה רגילה
לשם כך, בצע את שרשרת הפעולות הבאה:
- בואו נמחק את 7 השורות העליונות שאיננו צריכים לפני הפקודה הראשונה של BEGIN דף הבית — מחק שורות — מחק שורות עליונות (בית - הסר שורות - הסר שורות עליונות).
- סנן לפי עמודה Column1 שורות המכילות את השדות שאנו צריכים: DTSTART, DTEND, DESCRIPTION, LOCATION ו- SUMMARY.
- בכרטיסיה מתקדם הוספת עמודה לבחור עמודת אינדקס (הוסף עמודה - עמודת אינדקס)כדי להוסיף עמודת מספר שורה לנתונים שלנו.
- ממש שם בכרטיסייה. הוספת עמודה לבחור צוות עמודה מותנית (הוסף עמודה - עמודה מותנית) ובתחילת כל בלוק (סדר) אנו מציגים את הערך של האינדקס:
- מלא את התאים הריקים בעמודה שהתקבלה לחסוםעל ידי לחיצה ימנית על הכותרת שלו ובחירת הפקודה למלא למטה (למלא למטה).
- הסר עמודה מיותרת מדד.
- בחר עמודה Column1 ולבצע קונבולוציה של הנתונים מהעמודה Column2 באמצעות הפקודה טרנספורמציה - עמודת Pivot (טרנספורמציה - עמודת ציר). הקפד לבחור באפשרויות לא לצבור (לא לצבור)כך ששום פונקציה מתמטית לא תוחל על הנתונים:
- בטבלה הדו-ממדית (הצלבה) שהתקבלה, נקה את ההלוכסים האחוריים בעמודת הכתובת (לחץ לחיצה ימנית על כותרת העמודה - החלפת ערכים) והסר את העמודה המיותרת לחסום.
- כדי להפוך את תוכן העמודות DTSTART и DTEND בתאריך-שעה מלא, הדגשת אותם, בחר בכרטיסייה טרנספורמציה - תאריך - הפעל ניתוח (טרנספורמציה - תאריך - ניתוח). לאחר מכן נתקן את הקוד בשורת הנוסחאות על ידי החלפת הפונקציה תאריך מ on DateTime.Fromכדי לא לאבד ערכי זמן:
- לאחר מכן, על ידי לחיצה ימנית על הכותרת, אנו מפצלים את העמודה תיאור עם פרמטרי סדר לפי מפריד – סמל n, אך במקביל, בפרמטרים, נבחר את החלוקה לשורות, ולא לעמודות:
- שוב, אנו מחלקים את העמודה המתקבלת לשניים נפרדים - הפרמטר והערך, אך לפי סימן השוויון.
- בחירת עמודה תיאור.1 בצע את הפיתול, כפי שעשינו קודם לכן, עם הפקודה טרנספורמציה - עמודת Pivot (טרנספורמציה - עמודת ציר). עמודת הערך במקרה זה תהיה העמודה עם ערכי פרמטר - תיאור.2 הקפד לבחור פונקציה בפרמטרים לא לצבור (לא לצבור):
- נותר להגדיר את הפורמטים עבור כל העמודות ולשנות את שמם כרצונך. ואתה יכול להעלות את התוצאות בחזרה לאקסל עם הפקודה בית - סגור וטען - סגור וטען פנימה... (בית — סגור&טען — סגור&טען אל...)
והנה רשימת ההזמנות שלנו שנטענו לאקסל מלוח השנה של גוגל:
בעתיד, בעת שינוי או הוספת הזמנות חדשות ללוח השנה, יספיק רק לעדכן את הבקשה שלנו בפקודה נתונים - רענן הכל (נתונים - רענן הכל).
- לוח השנה של היצרן ב-Excel עודכן מהאינטרנט באמצעות Power Query
- הפיכת עמודה לטבלה
- צור מסד נתונים באקסל