תוכן
אחד הסרטונים הנצפים ביותר בערוץ היוטיוב שלי הוא סרטון על Flash Fill ב-Microsoft Excel. המהות של הכלי הזה היא שאם אתה צריך איכשהו לשנות את נתוני המקור שלך, אתה רק צריך להתחיל להקליד את התוצאה שאתה רוצה לקבל בעמודה הסמוכה. לאחר מספר תאים שהוקלדו ידנית (בדרך כלל 2-3 מספיקים), Excel "יבין" את ההיגיון של הטרנספורמציות שאתה צריך וימשיך אוטומטית את מה שהקלדת, וישלים עבורך את כל העבודה המונוטונית:
תמצית היעילות. כפתור הקסם "עשה זאת נכון" שכולנו אוהבים כל כך, נכון?
למעשה, יש אנלוגי של כלי כזה ב-Power Query - שם זה נקרא טור מתוך דוגמאות (עמודה מתוך דוגמאות). למעשה, זוהי בינה מלאכותית קטנה המובנית ב-Power Query שיכולה ללמוד במהירות מהנתונים שלך ואז לשנות אותם. בואו נסתכל מקרוב על היכולות שלו במספר תרחישים מעשיים על מנת להבין היכן הוא יכול להועיל לנו במשימות אמיתיות.
דוגמה 1. הדבקה/חיתוך טקסט
נניח שיש לנו טבלה "חכמה" כזו באקסל עם נתונים על עובדים:
טען אותו ל-Power Query בדרך הרגילה - עם הכפתור מתוך טבלה/טווח כרטיסייה נתונים (נתונים - מטבלה/טווח).
נניח שצריך להוסיף עמודה עם שמות משפחה וראשי תיבות לכל עובד (Ivanov SV לעובד הראשון וכו'). כדי לפתור בעיה זו, אתה יכול להשתמש באחת משתי שיטות:
- לחץ לחיצה ימנית על כותרת העמודה עם נתוני המקור ובחר את הפקודה הוסף עמודה מתוך דוגמאות (הוסף עמודה מתוך דוגמאות);
- בחר עמודה אחת או יותר עם נתונים ובכרטיסייה הוספת עמודה לבחור צוות טור מתוך דוגמאות. כאן, ברשימה הנפתחת, תוכל לציין אם יש לנתח את כל העמודות שנבחרו או רק.
ואז הכל פשוט – בעמודה שמופיעה מימין, אנחנו מתחילים להזין דוגמאות לתוצאות הרצויות, והבינה המלאכותית המובנית ב-Power Query מנסה להבין את היגיון הטרנספורמציה שלנו ולהמשיך הלאה בעצמה:
אגב, אתה יכול להזין את האפשרויות הנכונות בכל תאים של העמודה הזו, כלומר לא בהכרח מלמעלה למטה ובשורה. כמו כן, אתה יכול בקלות להוסיף או להסיר עמודות מהניתוח מאוחר יותר באמצעות תיבות הסימון בשורת הכותרת.
שימו לב לנוסחה בחלק העליון של החלון - זה מה ש- Power Query יוצר כדי להשיג את התוצאות שאנחנו צריכים. זה, אגב, ההבדל המהותי בין הכלי הזה לבין מילוי מיידי באקסל. מילוי מיידי עובד כמו "קופסה שחורה" – הם לא מראים לנו את ההיגיון של התמורות, אלא פשוט נותנים תוצאות מוכנות ואנחנו לוקחים אותן כמובן מאליו. כאן הכל שקוף ותמיד אפשר להבין בבירור מה בדיוק קורה עם הנתונים.
אם אתה רואה ש-Power Query "תפס את הרעיון", אז אתה יכול ללחוץ בבטחה על הכפתור OK או קיצור מקלדת Ctrl+זן – תיווצר עמודה מותאמת אישית עם נוסחה שהומצאה על ידי Power Query. אגב, ניתן לערוך אותו מאוחר יותר בקלות כעמודה רגילה שנוצרה באופן ידני (עם הפקודה הוספת עמודה - עמודה מותאמת אישית) על ידי לחיצה על סמל גלגל השיניים מימין לשם הצעד:
דוגמה 2: מקרה כמו במשפטים
אם תלחץ לחיצה ימנית על כותרת העמודה עם טקסט ובחר בפקודה טרנספורמציה (שינוי צורה), אז אתה יכול לראות שלוש פקודות שאחראיות לשינוי האוגר:
נוח ומגניב, אבל ברשימה הזו, למשל, לי אישית תמיד הייתה חסרה עוד אופציה אחת – רישיות כמו במשפטים, כאשר רישיות (אות רישיות) הופכת לא לאות הראשונה בכל מילה, אלא רק לאות הראשונה בתא, וכן שאר הטקסט כאשר זה מוצג באותיות קטנות (קטנות).
קל ליישם את התכונה החסרה הזו עם בינה מלאכותית עמודות מתוך דוגמאות - פשוט הזן כמה אפשרויות כדי ש-Power Query תמשיך באותה רוח:
כנוסחה כאן, Power Query משתמש בחבורה של פונקציות טקסט.עליון и טקסט.תחתון, המרת טקסט לאותיות גדולות וקטנות, בהתאמה, ופונקציות טקסט.התחל и טקסט.אמצע - אנלוגים של פונקציות Excel LEFT ו-PSTR, מסוגלים לחלץ מחרוזת משנה מהטקסט משמאל ומהאמצע.
דוגמה 3. שינוי מילים
לפעמים, בעת עיבוד הנתונים שהתקבלו, יש צורך לסדר מחדש את המילים בתאים ברצף נתון. כמובן שניתן לחלק את העמודה לעמודות מילים נפרדות על ידי המפריד ואז להדביק אותה בחזרה בסדר המצוין (לא לשכוח להוסיף רווחים), אבל בעזרת הכלי טור מתוך דוגמאות הכל יהיה הרבה יותר קל:
דוגמה 4: רק מספרים
משימה חיונית נוספת היא להוציא רק מספרים (מספרים) מתוכן התא. כמו קודם, לאחר טעינת נתונים לתוך Power Query, עבור לכרטיסייה הוספת עמודה - עמודה מתוך דוגמאות ומלא כמה תאים באופן ידני כדי שהתוכנית תבין מה בדיוק אנחנו רוצים להשיג:
בינגו!
שוב, כדאי להסתכל בחלק העליון של החלון כדי לוודא שאילתה יצרה את הנוסחה בצורה נכונה - במקרה זה היא מכילה פונקציה טֶקסט. בחר, אשר, כפי שניתן לנחש, מחלץ את התווים הנתונים מטקסט המקור בהתאם לרשימה. לאחר מכן, רשימה זו, כמובן, ניתנת לעריכה בקלות בשורת הנוסחאות במידת הצורך.
דוגמה 5: טקסט בלבד
בדומה לדוגמא הקודמת, ניתן לשלוף ולהיפך - רק את הטקסט, מחיקת כל המספרים, סימני פיסוק וכו'.
במקרה זה, נעשה שימוש בפונקציה שכבר הפוכה במשמעותה – Text.Remove, אשר מסירה תווים מהמחרוזת המקורית לפי רשימה נתונה.
דוגמה 6: חילוץ נתונים מדייסה אלפאנומרית
Power Query יכול לעזור גם במקרים קשים יותר, כאשר אתה צריך לחלץ מידע שימושי מהדייסה האלפאנומרית בתא, למשל, לקבל את מספר החשבון מתיאור מטרת התשלום בדף חשבון הבנק:
שים לב שנוסחת ההמרה שנוצרה באמצעות Power Query יכולה להיות מורכבת למדי:
כדי להקל על הקריאה וההבנה, ניתן להמיר אותו לצורה הרבה יותר שפויה באמצעות שירות מקוון בחינם. Power Query Formatter:
דבר שימושי מאוד - כבוד ליוצרים!
דוגמה 7: המרת תאריכים
כלי טור מתוך דוגמאות ניתן להחיל גם על עמודות תאריך או תאריך שעה. כאשר תזין את הספרות הראשונות של תאריך, Power Query יציג באופן מועיל רשימה של כל אפשרויות ההמרה האפשריות:
אז אתה יכול בקלות להמיר את התאריך המקורי לכל פורמט אקזוטי, כגון "שנה-חודש-יום":
דוגמה 8: סיווג
אם נשתמש בכלי טור מתוך דוגמאות לעמודה עם נתונים מספריים, זה עובד אחרת. נניח שיש לנו תוצאות בדיקות עובדים שנטענו ב-Power Query (ציונים מותנים בטווח 0-100) ואנחנו משתמשים בהדרגה המותנית הבאה:
- מאסטרס - אלו שקיבלו יותר מ-90
- מומחים - קיבלו ציון בין 70 ל-90
- משתמשים - מ-30 עד 70
- מתחילים - אלו שקיבלו פחות מ-30
אם נוסיף לרשימה עמודה מהדוגמאות ונתחיל לסדר את ההדרגות הללו באופן ידני, אז בקרוב מאוד Power Query תקלוט את הרעיון שלנו ותוסיף עמודה עם נוסחה, שבה אופרטורים מקננים זה בזה if הלוגיקה תיושם, דומה מאוד למה שאנחנו צריכים:
שוב, אתה לא יכול ללחוץ על המצב עד הסוף, אלא ללחוץ על OK ולאחר מכן תקן את ערכי הסף כבר בנוסחה - זה מהיר יותר כך:
מסקנות
בהחלט כלי טור מתוך דוגמאות אינו "גלולת קסם" ובמוקדם או במאוחר, יהיו מצבים לא סטנדרטיים או מקרים מוזנחים במיוחד של "חווה קולקטיבית" בנתונים, כאשר Power Query ייכשל ולא תוכל להחליט מה אנחנו רוצים נכון עבורנו. עם זאת, ככלי עזר, הוא טוב מאוד. בנוסף, על ידי לימוד הנוסחאות שהוא יצר, אתה יכול להרחיב את הידע שלך על הפונקציות של שפת M, מה שתמיד יהיה שימושי בעתיד.
- ניתוח טקסט עם ביטויים רגולריים (RegExp) ב-Power Query
- חיפוש טקסט מעורפל ב-Power Query
- מילוי פלאש ב-Microsoft Excel