נניח שאתה מפעיל מספר פרויקטים עם תקציבים שונים וברצונך לדמיין את העלויות שלך עבור כל אחד מהם. כלומר, מטבלת מקור זו:
.. קבל משהו כזה:
במילים אחרות, עליכם לפזר את התקציב על פני ימי כל פרויקט ולקבל גרסה פשוטה של טבלת גנט של הפרויקט. לעשות את זה עם הידיים זה ארוך ומשעמם, פקודות מאקרו קשות, אבל Power Query עבור אקסל במצב כזה מראה את כוחו במלוא הדרו.
שאילתת כוח הוא תוסף מבית מיקרוסופט שיכול לייבא נתונים לאקסל כמעט מכל מקור ולאחר מכן להפוך אותם בכמה דרכים שונות. ב-Excel 2016, התוסף הזה כבר מובנה כברירת מחדל, ועבור Excel 2010-2013 ניתן להוריד אותו מאתר Microsoft ולאחר מכן להתקין אותו במחשב האישי שלך.
ראשית, בואו נהפוך את הטבלה המקורית שלנו לטבלה "חכמה" על ידי בחירת הפקודה עיצוב כטבלה כרטיסייה עמוד הבית (בית - עיצוב כטבלה) או על ידי לחיצה על קיצור המקלדת Ctrl+T :
לאחר מכן עבור לכרטיסייה נתונים (אם יש לך Excel 2016) או בכרטיסייה שאילתת כוח (אם יש לך Excel 2010-2013 והתקנת את Power Query כתוספת נפרדת) ולחץ על הלחצן מתוך טבלה/טווח. :
הטבלה החכמה שלנו נטענת לתוך עורך השאילתות Power Query, כאשר השלב הראשון הוא להגדיר את פורמטי המספרים עבור כל עמודה באמצעות התפריטים הנפתחים בכותרת הטבלה:
כדי לחשב את התקציב ליום, צריך לחשב את משך הזמן של כל פרויקט. כדי לעשות זאת, בחר (החזק את המקש Ctrl) עמודה ראשונה סיום, ולאחר מכן הַתחָלָה ולבחור צוות הוסף עמודה - תאריך - חיסור ימים (הוסף עמודה - תאריך - החסר ימים):
המספרים המתקבלים הם 1 פחות מהנדרש, כי אנחנו אמורים להתחיל כל פרויקט ביום הראשון בבוקר ולסיים ביום האחרון בערב. לכן, בחר את העמודה המתקבלת והוסף לה יחידה באמצעות הפקודה טרנספורמציה - סטנדרטית - הוסף (טרנספורמציה - רגילה - הוסף):
כעת נוסיף עמודה שבה אנו מחשבים את התקציב ליום. כדי לעשות זאת, בכרטיסייה הוסף עמודה אני לא משחק עמודה מותאמת אישית (עמודה מותאמת אישית) ובחלון שיופיע הזינו את שם השדה החדש ואת נוסחת החישוב, תוך שימוש בשמות העמודות מהרשימה:
עכשיו הרגע הכי עדין - אנחנו יוצרים עוד עמודה מחושבת עם רשימה של תאריכים מתחילתו ועד סופו עם שלב של יום אחד. כדי לעשות זאת, לחץ שוב על הכפתור עמודה מותאמת אישית (עמודה מותאמת אישית) ולהשתמש בשפת Power Query המובנית M, שנקראת רשימה.תאריכים:
לפונקציה זו יש שלושה ארגומנטים:
- תאריך התחלה - במקרה שלנו, הוא נלקח מהעמודה הַתחָלָה
- מספר התאריכים שייווצרו - במקרה שלנו, זה מספר הימים לכל פרויקט, שספרנו קודם לכן בעמודה חִסוּר
- שלב זמן - נקבע לפי עיצוב #duration(1,0,0,0), כלומר בשפת מ' – יום אחד, אפס שעות, אפס דקות, אפס שניות.
לאחר לחיצה על OK אנו מקבלים רשימה (רשימה) של תאריכים, אותה ניתן להרחיב לשורות חדשות באמצעות הכפתור בכותרת הטבלה:
… ואנחנו מקבלים:
כעת כל שנותר הוא לכווץ את הטבלה, תוך שימוש בתאריכים שנוצרו בתור השמות לעמודות החדשות. הצוות אחראי לכך. עמודת פרטים (עמודת ציר) כרטיסייה המרת (שינוי צורה):
לאחר לחיצה על OK אנו מקבלים תוצאה קרובה מאוד לתוצאה הרצויה:
Null הוא, במקרה זה, אנלוגי של תא ריק באקסל.
נותר להסיר עמודות מיותרות ולפרוק את הטבלה המתקבלת ליד הנתונים המקוריים עם הפקודה סגור וטען - סגור וטען פנימה... (סגור וטען - סגור וטען ל...) כרטיסייה עמוד הבית (בית):
אנו מקבלים כתוצאה מכך:
ליופי רב יותר, אתה יכול להתאים אישית את המראה של הטבלאות החכמות המתקבלות בכרטיסייה בנאי (לְעַצֵב): הגדר סגנון צבע יחיד, השבת לחצני סינון, הפעלת סכומים וכו'. בנוסף, אתה יכול לבחור טבלה עם תאריכים ולאפשר הדגשת מספרים עבורה באמצעות עיצוב מותנה בכרטיסייה בית - עיצוב מותנה - סולמות צבע (בית - עיצוב מותנה - סולמות צבע):
והחלק הטוב ביותר הוא שבעתיד תוכל לערוך בבטחה ישנים או להוסיף פרויקטים חדשים לטבלה המקורית, ולאחר מכן לעדכן את הטבלה הנכונה בתאריכים באמצעות לחצן העכבר הימני - ו- Power Query יחזור על כל הפעולות שעשינו באופן אוטומטי .
וואלה!
- תרשים גנט באקסל באמצעות עיצוב מותנה
- לוח שנה אבני דרך של הפרויקט
- יצירת שורות כפולות עם Power Query