טעינת טבלה שמיקומה משתנה

לעתים אנחנו מקבלים קבצים שבהם טבלאות, שלא מתחילות בשורה הראשונה, לדוגמה:

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

שימו לב שהעורך מוחק באופן אוטומטי שורות ריקות שנמצאות בראש הטבלה, ולכן, בדוגמה לעיל, עלינו להסיר את 3 השורות העליונות, למרות שבקובץ המקור ישנן 4 שורות.

אבל מה קורה כשמיקום הכותרות אינו קבוע?

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

ולכן, אם מדובר בייבוא טבלאות מתוך קובץ, והחלפתו כאשר מגיע מידע חדש,
האוטומציה שבנינו לא תיתן לנו מענה טוב, מכיוון שהפעם צריך למחוק ארבע שורות ולא שלוש.
במאמר זה נלמד כיצד ליצור פתרון דינמי, שיאתר את השורה הראשונה בטבלה, ללא קשר למיקומה, וימחק את כל השורות עד אליה.
לו היינו עובדים ישירות באקסל, היינו יכולים להשתמש בפונקציה MATCH,
אבל לצערנו אין פונקציה מקבילה ב Power Query, וצריך לחפש דרך אחרת לאתר את מספר השורה.
אז איך עושים זאת?

שלב ראשון – טעינת הנתונים

לאחר שנטען את הנתונים אל העורך, יווצרו שלושת השלבים הבאים באופן אוטומטי:

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

שלב שני – הוספת אינדקס

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

למה לא למחוק את שלב שינוי הסוג:

כאשר אנחנו עובדים עם הממשק, לכל פעולה שאנחנו עושים נוצר שלב, שפונה לשלב הקודם, בדרך כלל על פי שמו.
הפניה לשלב ה Navigation שונה, וההסבר לדרך הפניה אליו חורג מתחולתו של מאמר זה.
כדי להקל עלינו, השארתי את שלב שינוי הסוג, שבו הפניה לשלב הקודם נעשתה על ידי העורך, כך שבהמשך נוכל לפנות לשלב הזה, במקום לשלב הניווט.
ועכשיו, אפשר סוף סוף להוסיף את האינדקס:
בכרטיסיית 'הוסף עמודה', נבחר ב'עמודת אינדקס', ונבחר באפשרות 'מ-0':

זו הטבלה שהתקבלה:

שלב שלישי – סינון הטבלה

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

נלחץ על הערך בלחיצה ימנית > 'בצע הסתעפות':

נקבל את הערך שמציין את מספר השורה:

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

שלב רביעי – החזרת הטבלה למצבה הקודם

לאחר שמצאנו את מספר השורה, נרצה להחזיר את הטבלה אל המצב הקודם,
ואת זה נעשה על ידי הוספת שלב ידנית בלחיצה על fx בשורת הנוסחאות, והפניה לשלב הרצוי.
אני מזכירה לכם ששמרנו את השלב אליו נרצה לחזור בשם Tbl, ולכן הפקודה תהיה

=Tbl

הטבלה חזרה לפורמט שלפני השימוש ב INDEX.

שלב חמישי – הסרת השורות העליונות

בכרטיסיית בית נבחר ב'הסר שורות' > 'הסר שורות עליונות' ונקליד את מספר השורות הרצוי, במקרה שלנו – 3
וזו הנוסחה שהתקבלה:

מכיוון שבחרנו להסיר 3 שורות, המספר 3 קודד בתוך הנוסחה, ועלינו להחליף אותו בערך שהתקבל בשלב Indx, כך:

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

רוצים לצפות בסרטון הסבר?