הדרישה – קיבוץ נתונים לתא אחד
פעמים רבות אני מתבקשת על ידי לקוחות ליצור להם קובץ שידע לקחת נתוני טבלה, ולקבץ אותם לתא אחד, מופרד בסימן כלשהו (פסיק, נקודה פסיק, או כל תו אחר)
הדרישה הזו באה בעיקר כשהם צריכים להעלות קובץ בפורמט הזה למערכות שהם עובדים איתן.
אז מה הבעיה?
המידע מאוחסן אצלם באקסל בצורה הזאת:
ואילו הם צריכים להעלות את הנתונים בצורה הזאת:
בעבר לימדתי אתכם איך ניתן לבצע זאת באמצעות Power Query,
ועכשיו אני רוצה להראות לכם איך עושים את זה עם פונקציות המערך הדינאמיות של אקסל 365.
אז מה יש לנו
טבלה חכמה שנתתי לה את השם Data שמכילה בעמודה אחת שמות ובעמודה השנייה קורסים.
מכיוון שכל תלמיד יכול לקחת כמה קורסים, שמות התלמידים ושמות הקורסים, יכולים לחזור על עצמם, כפי שאכן קורה בטבלה.
יאללה, לעבודה…
שלב ראשון – יצירת רשימה ייחודית
באמצעות הפונקציה Unique אצור רשימה ייחודית (ללא כפילויות) של שמות התלמידים:
=UNIQUE(Data[שם])
וזו התוצאה שהתקבלה:
שימו לב למסגרת הכחולה שמצביעה על כך שמדובר בפונקציית מערך דינאמית שזלגה כלפי מטה,
כשהפונקציה עצמה כתובה בתא H2 בלבד.
שלב שני – איתור כל הקורסים עבור כל תלמיד
בשלב השני צריך לייצר רשימת קורסים עבור כל אחד מהתלמידים.
הפונקציה להחזרת ערכים מרובים היא הפונקציה Filter:
=FILTER(Data[קורס],Data[שם]=H2)
ביקשתי לסנן את המידע בעמודת הקורסים בטבלה הראשית, ולכלול רק את אותן הרשומות שבהן שם הקורס זהה למה שכתוב בתא H2, ובמקרה שלנו – יעל.
זו התוצאה שהתקבלה:
שימו לב, בתא I2 כתבנו את הפונקציה, אך מכיוון שהפונקציה Filter יודעת להחזיר תוצאות מרובות עבור קריטריון אחד, קיבלנו טווח תוצאות ולא תוצאה אחת בלבד
חשוב שתשימו לב גם לכך שהתוצאה כאן רלוונטית רק עבור יעל, משום שהנוסחה מתייחסת לתא H2 בלבד.
שלב שלישי – איחוד הנתונים
כעת אני רוצה לאחד את כל התוצאות שמופיעות בטבלה לתא אחד מופרד בפסיקים, ואת זה נעשה עם הפונקציה TEXTJOIN:
=TEXTJOIN(", ",,FILTER(Data[קורס],Data[שם]=H2))
וזו התוצאה שהתקבלה:
כעת כל מה שנותר לעשות הוא לגרור את הנוסחה, כך שנקבל את התוצאות עבור כל אחד מהשמות:
הצעד הבא…
ומה יקרה אם לרשימת המקור יתווספו תלמידים?
הוספתי את מעין בשורה 14, ואכן, היא הופיעה בעמודה H תחת הפונקציה Unique:
אבל כדי להציג את רשימת הקורסים, יהיה עלינו לגרור את הנוסחה שבעמודה I.
כדי להימנע ממצב שבו עבור כל הוספת שם אצטרך לגרור את הנוסחה, אגרור אותה מראש למספר גדול יותר של תאים:
אז פתרנו בעיה אחת, אבל יצרנו אחרת…
מכיוון שיש לנו תאים בעמודה I שמפנים לתאים ריקים בעמודה H, קיבלנו את הודעת השגיאה CALC
אז מה הפתרון?
נעטוף את הפונקציה ב Iferror, באופן הבא:
=IFERROR(TEXTJOIN(", ",,FILTER(Data[קורס],Data[שם]=H2)),"")
כעת נוכל לגרור את הנוסחה כלפי מטה עם טווח ביטחון של תאים בלי חשש!
מעוניינים ללמוד בקורס על פונקציות המערך החדשות?
צרו קשר כאן ואחזור אליכם בהקדם.