Excel Table (Ctrl+T) و Structured References: پایه‌ی هر فایل حرفه‌ای

محدوده‌ی معمولی را به جدول تبدیل کنید

اگر فقط یک عادت از این دوره بردارید، این باشد: هر داده‌ی فهرستی (فروش، کالا، مشتری، پرسنل) را با Ctrl+T به Excel Table تبدیل کنید. جدول یک شیء هوشمند است که بزرگ و کوچک شدنش را خودش مدیریت می‌کند؛ فرمول‌ها، Pivot، نمودار، Data Validation و Power Query همگی با اضافه شدن سطر جدید خودکار به‌روز می‌شوند.

بلافاصله بعد از ساختن جدول

1. در تب Table Design، کادر Table Name را از Table1 به نامی معنادار مثل Sales یا Products تغییر دهید.
2. سرستون‌ها را کوتاه، یکتا و بدون فاصله‌ی اضافه بنویسید؛ سرستون در فرمول‌ها ظاهر می‌شود.
3. اگر جمع لازم است، Total Row (Ctrl+Shift+T) را روشن کنید و برای هر ستون از فهرست کشویی تابع جمع، میانگین یا شمارش را انتخاب کنید.

مزیت‌ها

Structured References

ستون محاسباتی داخل جدول Sales (همین سطر):
=[@Area] * [@UnitPrice]

بیرون از جدول:
=SUM(Sales[Amount])
=SUMIFS(Sales[Amount], Sales[Rep], G2)
=COUNTA(Sales[Invoice])
=XLOOKUP(G2, Products[Code], Products[Price])

ارجاع‌های ویژه:
Sales[#Headers]        ← ردیف سرستون
Sales[#Totals]         ← ردیف جمع
Sales[#All]            ← کل جدول با سرستون و جمع
Sales[[Date]:[Rep]]    ← چند ستون پشت‌سرهم
Sales[@Amount]         ← مقدار همین سطر، از بیرون جدول و در همان ردیف

ثابت نگه‌داشتن ستون هنگام کشیدن فرمول به راست:
=SUMIFS(Sales[[Amount]:[Amount]], Sales[[Rep]:[Rep]], $G2, Sales[[Month]:[Month]], H$1)

ارجاع جدولی هنگام کپی به پایین ثابت است (مثل $)، اما هنگام کشیدن به راست، به ستون بعدی جدول جابه‌جا می‌شود. شکل دوتایی [[Amount]:[Amount]] این جابه‌جایی را متوقف می‌کند.

نکته‌هایی که کمتر کسی می‌داند

• اگر نمی‌خواهید فرمول به کل ستون اعمال شود، بلافاصله پس از تایپ یک بار Ctrl+Z بزنید؛ فقط گسترش خودکار لغو می‌شود و فرمول در همان سلول می‌ماند.
• در منبع Data Validation و Conditional Formatting نمی‌توان مستقیم Sales[Rep] نوشت؛ از =INDIRECT("Sales[Rep]") یا یک نام که به ستون جدول اشاره می‌کند استفاده کنید.
• اگر سرستون کاراکترهای ویژه مثل [ ] # یا ' داشته باشد، در فرمول باید با آپاستروف خنثی شود؛ سرستون‌های ساده دردسر را حذف می‌کنند.
• داخل جدول، Ctrl+Space فقط داده‌های ستون را انتخاب می‌کند؛ بار دوم سرستون را هم؛ بار سوم کل ستون شیت را.
• فرمول‌های آرایه‌ی پویا (FILTER، UNIQUE) داخل جدول #SPILL! می‌دهند؛ گزارش‌های پویا را کنار جدول بسازید، نه داخل آن.