در هنگام کار با داده‌ها در مایکروسافت اکسل، استفاده از مراجع ساختاری می‌تواند به شما کمک کند تا فرمول‌ها را ساده‌تر، قابل فهم‌تر و پویاتر کنید. این مراجع به ویژه زمانی که در جداول فرمت شده استفاده می‌شوند، قدرت خود را نشان می‌دهند و می‌توانند به شما این امکان را بدهند که به‌طور خودکار و دقیق‌تر داده‌ها را محاسبه کنید. برخلاف مراجع سلولی معمولی مانند "B7"، که ممکن است باعث پیچیدگی در فرمول‌ها شود، مراجع ساختاری با استفاده از نام‌های معنادار برای جداول و هدرها، فرآیند کار با داده‌ها را بسیار ساده‌تر و قابل فهم‌تر می‌کنند. در این مقاله از سلام دنیا، به بررسی برخی از رایج‌ترین روش‌های استفاده از مراجع ساختاری برای ارجاع دهی در اکسل خواهیم پرداخت که می‌تواند به شما در مدیریت و تحلیل داده‌ها کمک کند.
نحوه استفاده از مراجع ساختاری / رفرنس ها در مایکروسافت اکسل
حتما بخوانید: رفع مشکل حذف نشدن لینک در اکسل
1. استفاده از رفرنس ها در مایکروسافت اکسل از طریق محاسبه داخل جداول
از آنجایی که رفرنس فقط بر روی جداول کار می کنند، بهترین راه برای استفاده از آنها در همان جداول است. به عنوان مثال، ما یک جدول ساده از B2 تا F8 با اطلاعات فروش برای یک فروشگاه ایجاد می کنیم. توجه داشته باشید که ما نام جدول را "فروش" گذاشتیم (به "نام جدول" در بالا سمت چپ مراجعه کنید).
بیایید مجموع هر فروش را محاسبه کنیم:
- مرحله 1: روی F2 کلیک کنید (اما نه روی نماد کشویی). به «صفحه اصلی» سپس به «insert» بروید و «درج ستون‌های جدول در سمت راست» را انتخاب کنید. این به طور خودکار یک ستون جدید به جدول اضافه می کند.
- مرحله 2: عنوان ستون G را "Total" نامگذاری کنید.
- مرحله 3: در G3، فرمول زیر را درج کرده و Enter را بزنید. در صورت نیاز خروجی سلول را فرمت کنید.
[Quantity@] * [PricePerUnit@]=
“[PricePerUnit@]” و “[Quantity@]” ارجاعاتی به فیلدهای مربوطه در آن ستون‌ها هستند. آرگومان "@" قبل از نام ستون ها به این معنی است که هر سلول نتیجه از منابع همان ردیف جدول استفاده می کند.
برای ترجمه، فرمول [Quantity@] * [PricePerUnit@]= در G3 اساساً مانند نوشتن فرمول زیر می باشد.
=$C3*$D3
2. استفاده از رفرنس ها در مایکروسافت اکسل از طریق واکشی یک محدوده خارج از جدول
هنگامی که می خواهید از یک مرجع ساختاریافته در سلولی خارج از جدول استفاده کنید، باید پیشگفتار مرجع را با TableName وارد کنید. در مثال قبلی ما، استفاده از «Sales[Total]» کل محدوده را در زیر عنوان «Total» جدول «Sales» دریافت می‌کند.
حتما بخوانید: آموزش کشیدن و ساخت جدول در اکسل ویندوز و مک
این بدان معنی است که چندین مقدار در یک آرایه دریافت خواهید کرد که می توانید آنها را دستکاری کنید. در اینجا این است که چگونه این در داخل اکسل در سلول I3 به نظر می رسد، مشروط بر اینکه فضای کافی برای ریختن محدوده باقی بماند.
3. استفاده از رفرنس ها در مایکروسافت اکسل از طریق جمع بندی جزئی یک ستون
برای جمع‌بندی سریع کل یک ستون، می‌توانید از علامت «ردیف کل» در گزینه‌های «طراحی جدول» (در زیر «گزینه‌های سبک جدول») استفاده کنید. در اینجا مثالی از دریافت مجموع ستون‌های "تعداد" و "کل" آورده شده است.
در حالی که ردیف "Total" به خودی خود قابل جابجایی نیست و در انتهای جدول قرار می گیرد (با امکان درج)، می توانید نتیجه آن را در جای دیگری کپی کنید:
- برای بدست آوردن مجموع تمام سطرهای ستون "Total"، از استفاده می کنیم.
SUM (Sales [Total] )=
- اگر می‌خواهید فقط مجموع ستون‌های قابل مشاهده را دریافت کنید، مثلاً پس از فیلتر کردن جدول، از زیر استفاده کنید.این فرمول همان کاری است که گزینه "Total Row" در قالب جدول در واقع در ردیف خود انجام می دهد.
SUBTOTAL (109, Sales [Total] )=
شما همچنین می توانید یک مجموع جزئی بر اساس متغیر خاصی که در داخل جدول یافت می شود بدون قالب بندی آن را دریافت کنید. به عنوان مثال:
حتما بخوانید: رفع مشکل باز نشدن فایل در اکسل
- برای دریافت مجموع کل فروش های مایک می توانید از دستور زیر استفاده کنید. در فرمول، "Mike" رشته ای است که به صورت دستی وارد شده است.
SUMIF (Sales[Seller], ”Mike”, Sales[Total])=
- برای دریافت مجموع محصولات با شناسه 41230 از کد زیر استفاده کنید . توجه داشته باشید که از آنجایی که ستون ProductID دارای قالب "عمومی" است، می توانید شماره را مستقیماً وارد کنید.
SUMIF (Sales [ProductID], 41230, Sales[Total] )=
4. استفاده از رفرنس ها در مایکروسافت اکسل از طریق اعتبارسنجی داده ها از یک جدول از طریق INDIRECT
فرض کنید جدول فروش قبلاً استفاده شده را دارید. می‌توانید گزینه‌های اعتبارسنجی داده‌های سفارشی ایجاد کنید تا جستجو در جدول را آسان‌تر کنید. بیایید یک جدول کوچکتر بسازیم که به شما امکان می‌دهد بین شناسه‌های محصول، تاریخ‌ها یا فروشنده‌ها انتخاب کنید و سپس هر مورد را از آن زیر مجموعه‌ها انتخاب کنید تا کل فرعی نمایش داده شود.
- مرحله 1: در سلول B13، باید Data Validation را ایجاد کنید (برگه داده > ابزارهای داده > اعتبارسنجی داده).
- مرحله 2: در پنجره بازشو، از میان گزینه های «Allow»، «List» را انتخاب کنید، سپس مقادیر ستون ها را به صورت دستی در کادر «Source» که با کاما از هم جدا شده اند وارد کنید. در این مورد، «ID محصول، فروشنده، تاریخ» را درج کردیم.
- مرحله 3: در سلول C13، یک Data Validation دیگر ایجاد کنید. دوباره «فهرست» را انتخاب کنید. برای «منبع»، فرمول زیر را وارد کنید.
INDIRECT ( "Sales ["&B13&"]" )=
- مرحله 4: در سلول D13 از فرمول زیر استفاده کنید
=SUMIF (INDIRECT ("Sales ["&B13&"]" ) , B14, Sales[Total] )
اکنون می‌توانید گزینه‌ها را از بین دو لیست اعتبارسنجی داده‌ها انتخاب کنید و مجموع فرعی در D13 نمایش داده می‌شود.
سخن آخر
استفاده از مراجع ساختاری در اکسل نه تنها کار با داده‌ها را ساده‌تر می‌کند، بلکه فرمول‌ها را نیز خواناتر و دقیق‌تر می‌سازد. با استفاده از این مراجع می‌توانید به راحتی داده‌ها را مدیریت کنید، از فرمول‌های پیچیده پرهیز کنید و تحلیل‌های سریع و دقیق‌تری انجام دهید. این تکنیک‌ها ابزار قدرتمندی برای کسانی هستند که می‌خواهند بهره‌وری خود را در اکسل افزایش دهند و در عین حال دقت و انعطاف‌پذیری بیشتری در محاسبات خود داشته باشند.
مطالب مرتبط:
رفع مشکل هنگ، سنگین و کند کار کردن اکسل
دیدگاه ها