چهار روش برای استفاده از ارجاعات ساختاری در اکسل
به گزارش اپ خونه، کار با اکسل معمولاً حول پیدا کردن ارتباط بین داده‌های مختلف می‌چرخد. با این حال، وقتی فرمول‌های پیچیده وارد می‌کنید، استفاده مکرر از ارجاعات صریح سلول (مانند “B7” یا موارد مشابه آن) می‌تواند به‌سرعت باعث شود که نوار فرمول به یک مجموعه غیرقابل‌خواندن تبدیل شود.
ارجاعات ساختاری (Structured references) در اکسل به شما این امکان را می‌دهد که این فرآیند را ساده‌تر کنید. این کار با اختصاص نام به جداول و هدرهای (headers) آن‌ها انجام می‌شود. سپس این نام‌ها به‌عنوان ارجاعات سلول ضمنی استفاده می‌شوند تا اکسل به‌طور خودکار داده‌های ساختاریافته را بازیابی و محاسبه کند.
در ادامه برخی از رایج‌ترین روش‌ها برای استفاده از ارجاعات ساختاری در اکسل آمده است:
۱. محاسبه در داخل جداول
از آنجایی که ارجاعات ساختاری فقط روی جداول (tables) کار می‌کنند، بهترین راه برای استفاده از آن‌ها، داخل همان جداول است.
مثلاً ما یک جدول ساده از محدوده B2 تا F8 ایجاد می‌کنیم که شامل داده‌های فروش یک فروشگاه است. توجه کنید که نام این جدول را “Sales” گذاشته‌ایم (در بخش “Table Name” در گوشه بالا سمت چپ مشخص است).
مراحل محاسبه در داخل جداول:
۱. روی F2 کلیک کنید (نه روی آیکون کشویی). سپس به مسیر Home > Insert بروید و Insert Table Columns to the Right را انتخاب کنید. این کار به‌طور خودکار یک ستون جدید به جدول اضافه می‌کند.
۲. نام هدر ستون G را “Total” بگذارید.
۳. در سلول G3 فرمول زیر را وارد کنید و Enter بزنید:
سپس خروجی سلول را به‌صورت دلخواه قالب‌بندی کنید.
در اینجا، “[@PricePerUnit]” و “[@Quantity]” به ستون‌های مربوطه در جدول اشاره می‌کنند. علامت “@” قبل از نام ستون‌ها نشان می‌دهد که هر سلول نتیجه، از داده‌های همان ردیف جدول استفاده خواهد کرد.
به عبارت دیگر، فرمول [@PricePerUnit]*[@Quantity] در G3 مشابه نوشتن این فرمول است: =$C3*$D3
۲. بازیابی یک محدوده خارج از جدول
اگر بخواهید از یک ارجاع ساختاری در سلولی خارج از جدول استفاده کنید، باید قبل از ارجاع، نام جدول را ذکر کنید.
برای مثال، در جدول قبلی، استفاده از “Sales[Total]” کل محدوده زیر هدر “Total” در جدول “Sales” را بازیابی می‌کند. این بدان معناست که یک محدوده داده از چندین مقدار دریافت می‌کنید که می‌توانید آن را دستکاری کنید.
مثال: این مورد در سلول I3 به‌صورت زیر نشان داده می‌شود (به شرطی که فضای کافی برای ریختن مقادیر محدوده داده به پایین در نظر بگیرید).
۳. جمع‌کردن کامل و جزئی یک ستون
برای جمع سریع کل یک ستون، می‌توانید از گزینه Total Row در تنظیمات Table Design (زیر Table Style Options) استفاده کنید.
مراحل:
• برای جمع کردن تمام ردیف‌های ستون “Total”، فرمول زیر را استفاده کنید:
• اگر بخواهید فقط جمع ستون‌های قابل مشاهده (مثلاً بعد از فیلتر کردن جدول) را دریافت کنید، از فرمول زیر استفاده کنید:
این همان فرمولی است که گزینه “Total Row” در قالب جدول استفاده می‌کند.
همچنین می‌توانید جمع جزئی بر اساس یک متغیر خاص را بدون نیاز به قالب‌بندی جدول دریافت کنید.
• برای جمع تمام فروش‌هایی که توسط “Mike” انجام شده است:
در این فرمول، “Mike” یک رشته متنی است که به‌صورت دستی وارد شده است.
• برای جمع تمام محصولاتی با شناسه “۴۱۲۳۰”:
توجه کنید که چون ستون ProductID فرمت “General” دارد، می‌توانید عدد را مستقیماً وارد کنید.
۴. اعتبارسنجی داده‌ها از یک جدول با استفاده از INDIRECT
فرض کنید جدول Sales را دارید. می‌توانید گزینه‌های اعتبارسنجی داده سفارشی ایجاد کنید تا جستجو در جدول راحت‌تر شود. حالا یک جدول کوچک‌تر ایجاد می‌کنیم که به شما امکان انتخاب بین ProductIDs، Dates یا Sellers را بدهد و سپس هر آیتم از این زیرمجموعه‌ها را برای نمایش زیرجمع انتخاب کنید.
مرحله۱. در سلول B13، اعتبارسنجی داده ایجاد کنید (Data > Data Tools > Data Validation).
مرحله۲. در پنجره باز شده، از گزینه‌های “Allow”، گزینه List را انتخاب کنید و سپس مقادیر ستون‌ها را به‌صورت دستی در بخش “Source” با کاما جدا کنید (مثلاً: ProductID, Seller, Date).
مرحله۳. در سلول C13، یک اعتبارسنجی داده دیگر ایجاد کنید. باز هم List را انتخاب کنید. در بخش “Source”، فرمول زیر را وارد کنید:
مرحله۴. در سلول D13، از فرمول زیر استفاده کنید:
اکنون می‌توانید از دو لیست کشویی داده برای انتخاب گزینه‌ها استفاده کنید و زیرجمع در D13 نمایش داده می‌شود.
ارجاعات ساختاری در اکسل یکی از ابزارهای بسیار قدرتمند برای کار با داده‌های ساختاریافته است. این ویژگی نه تنها فرمول‌ها را خواناتر و قابل مدیریت‌تر می‌کند، بلکه امکان به‌روزرسانی خودکار فرمول‌ها را با تغییر نام ستون‌ها فراهم می‌آورد. از محاسبات ساده در جداول گرفته تا جمع‌های پیچیده و اعتبارسنجی داده‌ها، استفاده از ارجاعات ساختاری می‌تواند بهره‌وری شما را در کار با اکسل به طرز چشمگیری افزایش دهد.
اگر تا به حال از این قابلیت استفاده نکرده‌اید، پیشنهاد می‌کنیم همین حالا آن را امتحان کنید. با این روش، نه تنها زمان خود را در مدیریت داده‌ها کاهش می‌دهید، بلکه فرمول‌های شما شفاف‌تر و خطایابی آن‌ها آسان‌تر خواهد بود.
آیا این مطلب برایتان مفید بود؟ اگر سوالی داشتید، خوشحال می‌شویم پاسخ دهیم. 😊
