טבלה תקנית עם SEQUENCE

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

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

זיהוי מספר העמודות והשורות הרצויים

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

COUNTA(B1:B12)/3

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

יצירת המטריצה

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

=SEQUENCE(4,3)

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

SEQUENCE(COUNTA(B1:B12)/3,3)

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

החלפת המיקומים בערכים

קיבלנו טבלה, שמכילה את כל המיקומים מ-1 ועד 12,
ואנחנו צריכים להחליף את המיקום בערך שלו. את זה אפשר לעשות באמצעות הפונקציה הוותיקה INDEX.
אני מזכירה לכם שהתחביר של INDEX הוא:

=INDEX (Array, Row Num, Column Num)

אז את המערך יש לנו – זהו טווח השמות בעמודה B;
את מספר השורה קיבלנו באמצעות הפונקציה SEQUENCE
ומספר העמודה הוא תמיד 1, משום שזו העמודה יחידה בטווח
ועכשיו כל מה שנשאר לנו זה להכניס את הערכים המתאימים במקומות המתאימים, וכך תיראה הפונקציה:

=INDEX(B1:B12,SEQUENCE(COUNTA(B1:B12)/3,3),1)

ולמה לא לעבוד עם טבלה חכמה (דינמית)?

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

=INDEX(B:B,SEQUENCE(COUNTA(B:B)/3,3),1)

והטבלה התקנית תגדל כשטבלת המקור תגדל.