איחוד נתונים לשורה רציפה מתוך רשימת ערכים

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

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

אנחנו יכולים לראות את רשימת התלמידים ועבור כל תלמיד את הקורסים שלמד.
יעל למשל למדה בקורס טבלאות ציר (שורה 2), וגם קורס Power Query (שורה 4).
הדרישה היא להעביר את הטבלה לפורמט הבא:

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

אז איך עושים זאת?

לאט ובזהירות…
ראשית, נוודא שהטבלה היא טבלה דינמית (חכמה) ונטען אותה אל העורך.
בשלב השני נסמן את העמודה שמכילה את שמות התלמידים ונלחץ על 'קיבוץ לפי' בכרטיסיית 'בית'.
ניתן שם לעמודה החדשה (Tmp) ונבחר לבצע אותה על כל השורות:

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

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

לצורך המשימה שלנו, אנחנו לא נפתח את הטבלה, אלא נהפוך אותה לרשימה, שכוללת את כל הפריטים המקובצים מתוך הטבלה.
אבל לפני זה, בואו נעשה סדר:
שם העמודה המקורי הוא Course
לאחר הקיבוץ יצרנו עמודה חדשה בשם Tmp, שהחליפה את העמודה המקורית Course והיא מכילה אובייקט בשם Table.

ולחלק המעניין…

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

אז ראשית, נעבור לכרטיסיית 'הוסף עמודה' ושם נבחר ב'עמודה מותאמת אישית'

החלונית הבאה תיפתח, ובה ניתן את השם Course List לעמודה החדשה שתכיל את כל הקורסים של התלמיד.
לאחר מכן נשתמש בפונקציה Table.Column ליצירת הרשימה

אז מה יש לנו בחלונית 'עמודה מותאמת אישית'?
ראשית, נתנו את השם המשמעותי Course List, משום שהרשימה שתיווצר תכיל את רשימת כל הקורסים של התלמיד,
לאחר מכן השתמשנו בפונקציה Table.Column כשהפרמטר הראשון שלה הוא השם שנתנו לרשימה המקובצת,
והוא נלקח מתוך רשימת העמודות הזמינות (מצד שמאל של המסך),
והפרמטר השני שלה הוא השם המקורי של עמודת הקורסים.
שימו לב שבשפת M יש להקפיד על אותיות גדולות וקטנות!
לאחר לחיצה על אישור קיבלנו את הטבלה הבאה:

עכשיו נלחץ על שני החצים ונבחר ב'חלץ ערכים'

מתוך הרשימה נבחר את המפריד הרצוי. במקרה שלנו – בחרתי בפסיק:

כעת נוכל למחוק את עמודת Tmp ולטעון את הטבלה אל הגיליון:

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

קסם!

והערה קטנה לסיום –

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

רוצים לדעת איך עושים את אותה הפעולה באמצעות פונקציות המערך הדינאמיות החדשות של 365?

לחצו על הקישור, ותעברו למאמר