טבלאות חכמות באקסל

וִידֵאוֹ

ניסוח הבעיה

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

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

פתרון

בחר תא כלשהו בטבלה ובכרטיסייה עמוד הבית (בית) להרחיב את הרשימה עיצוב כטבלה (פורמט כטבלה):

ברשימת הסגנונות הנפתחת בחרו כל אפשרות מילוי לטעמנו ולצבענו, ובחלון האישור לטווח הנבחר לחצו OK ונקבל את הפלט הבא:

כתוצאה מכך, לאחר שינוי כזה של הטווח ל"חכם" טבלתי (עם אות גדולה!) יש לנו את ההנאות הבאות (חוץ מעיצוב יפה):

1. נוצר טבלתי מקבל שם לוח 1,2,3 וכו' שניתן לשנות לאחד מתאים יותר בכרטיסייה בנאי (לְעַצֵב). ניתן להשתמש בשם זה בכל נוסחאות, רשימות נפתחות ופונקציות, כגון מקור נתונים לטבלת ציר או מערך חיפוש עבור פונקציית VLOOKUP.
2. נוצר פעם אחת טבלתי מתאים אוטומטית לגודל בעת הוספה או מחיקה של נתונים אליו. אם תוסיף על כאלה טבלתי קווים חדשים - הוא יימתח נמוך יותר, אם תוסיף עמודות חדשות - הוא יתרחב לרוחבה. בפינה הימנית התחתונה לוחות אתה יכול לראות את סמן הגבול שזז אוטומטית ובמידת הצורך להתאים את מיקומו עם העכבר:

    

3. בכובע לוחות באופן אוטומטי סינון אוטומטי מופעל (ניתן לאלץ להשבית בכרטיסייה נתונים (תַאֲרִיך)).
4. כאשר מוסיפים להם שורות חדשות באופן אוטומטי כל הנוסחאות מועתקות.
5. בעת יצירת עמודה חדשה עם נוסחה - היא תועתק אוטומטית לכל העמודה - אין צורך לגרור נוסחה עם צלב השלמה אוטומטית שחורה.
6. בעת גלילה לוחות מטה כותרות העמודות (A, B, C...) משתנות לשמות שדות, כלומר אתה כבר לא יכול לתקן את כותרת הטווח כמו קודם (ב-Excel 2010 יש גם מסנן אוטומטי):
7. על ידי הפעלת תיבת הסימון הצג שורה כוללת (סה"כ שורה) כרטיסייה בנאי (לְעַצֵב) נקבל שורת סיכומים אוטומטית בסוף לוחות עם היכולת לבחור פונקציה (סכום, ממוצע, ספירה וכו') עבור כל עמודה:
8. לנתונים ב טבלתי ניתן להתייחס באמצעות שמות האלמנטים הבודדים שלו. לדוגמה, כדי לסכם את כל המספרים בעמודת המע"מ, ניתן להשתמש בנוסחה =SUM(טבלה1[מע"מ]) במקום =SUM(F2:F200)‎ ולא לחשוב על גודל הטבלה, מספר השורות ונכונות טווחי הבחירה. אפשר גם להשתמש בהצהרות הבאות (בהנחה שלטבלה יש את השם הסטנדרטי לוח 1):

• =טבלה1[#הכל] - קישור לטבלה כולה, כולל כותרות העמודות, הנתונים והשורה הכוללת
• =טבלה1[#נתונים] - קישור לנתונים בלבד (ללא שורת כותרת)
• =טבלה1[#Headers] – קישור רק לשורה הראשונה של הטבלה עם כותרות העמודות
• =טבלה1[#סיכומים] - קישור לשורה הכוללת (אם היא כלולה)
• =טבלה1[#שורה זו] — התייחסות לשורה הנוכחית, לדוגמה, הנוסחה =טבלה1[[#שורה זו];[מע"מ]] תתייחס לערך המע"מ משורת הטבלה הנוכחית.

  (בגרסה האנגלית, אופרטורים אלה יישמעו, בהתאמה, בתור #All, #Data, #Headers, #Totals ו-#This row).

PS

באקסל 2003 היה משהו דומה מרחוק לטבלאות "חכמות" כאלה - זה נקרא הרשימה והוא נוצר דרך התפריט נתונים - רשימה - צור רשימה (נתונים - רשימה - צור רשימה). אבל אפילו מחצית מהפונקציונליות הנוכחית לא הייתה שם בכלל. גם בגרסאות ישנות יותר של Excel לא היה את זה.