فرمول نویسی با استفاده از Table

فرمول نویسی در Table

ما در اکسل داده ها رو ثبت، ذخیره و آماده میکنیم که بتونیم از این داده ها گزارش گیری کنیم و تحلیل هایی بدست بیاریم. داده ها در اکسل یا در قالب Range معمولی و یا در قالب Table ذخیره میشن. Range معمولی یعنی همون سلول های موجود در اکسل که بصورت A1, B10 و … شناخته میشن، اما Table متفاوت هست. وقتی یک Range در اکسل رو به Table تبدیل میکنیم، یک سری ویژگی ها پیدا میکنه که یکی از مهم ترین ویژگی هاش Structured Reference هست. در این آموزش میخواهیم راجع به فرمول نویسی در Table (که بخشی از فرمول نویسی در اکسل است) و اهمیت و کاربرد آن صحبت کنیم.

تعریف Structure Reference برای فرمول نویسی در Table

یک structured reference یا table reference یک راه ویژه برای آدرس دهی اجزای یک Table هست که بجای آدرس سلول ها، از ترکیب اسم Table و نام ستون ها استفاده میکنه.

همین موضوع Table رو در برابر Range معمولی خیلی قوی تر و منعطف تر میکنه. Range معمولی اکسل نمیتونه در برابر اضافه شدن داده، بصورت پویا، اپدیت بشه. در حالیکه یکی از ویژگی های خیلی مهم ابزارTable  همین آپدیت شدن محدوده ها بصورت خودکار هست. مثلا برای محاسبه جمع هزینه ها در D2:D9 اگر داده ها Range معمولی باشه از فرمول زیر استفاده میکنیم:

=Sum(D2:D9)

شکل ۱ – آدرس دهی در حالت Range معمولی

اما اگر این داده ها از جنس Table باشه، برای همین محاسبه، فرمول زیر رو مینویسیم:

=SUM (Table1[مبلغ نهایی])

شکل ۲ – آدرس دهی در حالت Table و Structured Reference

ویژگی های اصلی Structured Reference

در مقایسه با Range معمولی، Table ها یک سری ویژگی هایی دارند که در ادامه اونها رو شرح میدهیم:

براحتی ایجاد میشوند

وقتی در فرمول نویسی میخواهیم به محدوده های خاصی از یک تیبل اشاره کنیم، فقط کافیه اون محدوده ها رو انتخاب کنیم. خودش تبدیل به ساختار مختص به Table میشه و نیازی به دانش خاصی برای این نوع فرمول نویسی نداریم.

انعطاف پذیر هستند و بصورت خودکار آپدیت میشوند

وقتی اسم یک ستون رو تغییر بدیم، فرمول هایی که از اون ستون استفاده کردن، آپدیت میشن و فرمول خراب نمیشه. اگر هم داده هایی به جدول اضافه بشه، فرمول های نوشته شده، بصورت خودکار گسترش پیدا میکنن و ردیف های جدید رو هم شامل میشن.

پس هر تغییری در Table داده ها ایجاد کنیم، در فرمول ها آپدیت میشه و مشکلی پیش نمیاد.

بصورت خودکار اتوفیل میشه

برای اینکه یک فرمول رو برای همه ردیف ها در یک تیبل داشته باشیم، کافیه فقط برای یک سلول این فرمول رو بنویسیم. فرمول بصورت خودکار در کل اون ستون اعمال میشه.

چطور Structured Reference رو ایجاد و از آنها استفاده کنیم؟

کار کردن با Structured Reference خیلی ساده هست. کافیه در ابتدا Range معمولی اکسل رو به Table تبدیل کنیم که برای این کار کافیه محدوده رو انتخاب کرده و با زدن کلید ترکیبی Ctrl+T محدوده رو تبدیل به Table کنیم. توضیحات بیشتر راجع به این موضوع رو در مقاله Table چیست ببینید.

برای ایجاد Structured Reference مراحل زیر رو باید انجام بدیم:

1. فرض کنید میخواید فرمول معمولی بنویسید. پس با زدن = در یک سلول شروع میکنیم.
2. بعد از نوشتن تابع مورد نظر، محدوده دلخواه رو از جدول مورد نظر انتخاب کنید. اکسل بصورت خودکار نام ستون رو فراخوانی میکنه و با توجه به نوع انتخاب محدوده ها، ساختار مناسب رو ایجاد میکنه.
3. پرانتز پایانی تابع رو وارد کرده و Enter میزنیم. اگر فرمول داخل Table ایجاد شده باشه، بصورت خودکار، تا انتهای تیبل، فرمول اعمال میشه.

شکل ۳ – فرمول نویسی در Table

کل ردیف های Table با فرمول زیر پر میشن. همونطور که مشخصه، هیچ اسمی از آدرس سلول ها وجود نداره و فقط فیلد مقدار در نرخ ضرب شده.

=[@نرخ]*[@مقدار]

اگر فرمول رو خارج از Table وارد کنیم و در فرمول نیاز به انتخاب یک range خاص داشته باشیم بهتره طبق مراحل زیر عمل کنیم:

1. بعد از باز شدن پرانتز تابع مورد نظر، شروع کنیم به تایپ نام Table مربوطه. به محض شروع به تایپ اولین حروف، نامهایی که با این عبارات شروع میشن نمایش داده میشن و ما میتونیم از لیست مورد نظر نام Table رو انتخاب کنیم.
2. با استفاده از کلیدهای چهار جهت (Arrow Key) Table مورد نظر رو انتخاب میکنیم.
3. با زدن دبل کلیک یا کلید Tab جدول مورد نظر انتخاب میشه و در فرمول نمایش داده میشه.
4. پرانتز فرمول رو بسته و Enter رو میزنیم.

مثلا در مثال زیر، نام جدول مورد نظر ما Sale هست (اصول نامگذاری محدوده های نامگذاری رو مطالعه کنید). حالا میخواهیم بیشترین میزان فروش بین همه محصولات و در همه ماه ها رو محاسبه کنیم. برای این کار طبق توضیحات بالا از فرمول زیر استفاده میکنیم:

=MAX(sale)

تشریح اجزای Structured Reference برای فرمول نویسی در Table

همونطور که در بالا توضیح دادیم، نیازی به شناخت اجزای این ساختار نیست. اما درک اجزای این گونه ساختارها به فهم چگونگی عملکرد فرمول های نوشته شده کمک میکنه.

عموما یک structured reference از یک رشته ای تشکیل میشه که با نام Table مورد نظر شروع میشه و با نام ستون مورد نظر تمام میشه. به عنوان مثال، فرمول زیر رو که جمع داده های موجود در ستون شمال تا جنوب رو در ردیف جمع کل (Total) در جدولی به نام Region حساب میکنه رو ببینیم:

=SUM (region[[#Totals],[جنوب]:[شمال]])

این فرمول سه قسمت داره:

Region: نام Table مورد نظر هست.

[#Totals]: مشخص کننده آیتم مورد نظر  برای انجام عملیات.

[جنوب]:[شمال]: ستون های مورد نظر برای انجام عملیات.

شکل ۴- تشریح اجزای Structured Reference

قسمت اول، نام Table

نام table به کل داده های جدول اشاره میکنه، بدون سرستون ها و بدون ردیف جمع کل (Total Row). این نام میتونه نام پیشفرض جدول ها در اکسل باشه، مثل Table1 و … یا اینکه اسم خاصی که ما براش تعیین میکنیم مثل Region . تغییر نام Table از تب Design و در قسمت Properties امکان پذیر هست.

اگر فرمول داخل خود Table نوشته بشه، بصورت پیشفرض، نام جدول از ساختار فرمول حذف میشه.

مشخص کننده ستون – Column Specifier

این قسمت، به داده های موجود در ستون مورد نظر اشاره میکنه. بدون سر ستون و بدون داده موجود در ردیف کل. یک Column Specifier با نام ستون که داخل براکت قرار گرفته نمایش داده میشه.

برای ارجاع دادن به بیش از یک ستون (پشت سر هم) از ساختار Range استفاده میکنیم مثلا: ][جنوب]:[شمال]]

مشخص کننده آیتم  – Item Specifier

برای ارجاع دادن به قسمت خاصی از جدول، باید از یکی از گزینه های زیر استفاده کنیم:

 توج داشته باشید که علامت # برای همه این موارد استفاده میشه به جز ردیف جاری. در آیتم ردیف جاری، از @ استفاده میشه برای ارجاع دادن به ردیف متناظر در سلولی که داریم فرمول رو ثبت میکنیم.

مثلا اگر بخواهیم فروش شمال و جنوب رو برای هر محصول حساب کنیم، از فرمول زیر استفاده میکنیم:

=SUM([@جنوب],[@شمال])

عملگرها لازم برای فرمول نویسی در Table

با عملگرهای زیر میتونیم ساختارهای منعطف تری رو بسازیم:

Range Operator یا :

همونطور که Range پیوسته در اکسل با : تعیین میشه مثل A1:B10 . در Table هم با همین : Range رو تعیین میکنیم. مثلا ][جنوب]:[شمال]] که به معنی ستون شمال تا جنوب هست.

Union Operator یا ,

برای ارجاع به محدوده های ناییوسته در Table از کاما استفاده میکنیم.

=SUM (region[شمال] , region[جنوب] )

Intersection Operator یا (فاصله-Space)

از این عملگر برای ارجاع دادن به یک سلول خاص در تقاطع یک ستون و یک ردیف استفاده میشه.

مثلا میخواهیم مقدار کل فروش در غرب رو از ردیف Total فراخوانی کنیم:

=Region[#Totals] Region[[#All],[غرب]]

دقت کنید در اینجا All لازمه چون نشخص کننده ستون، شامل سطر کل نمیشه. اگر اشتراکی بین دو جزء فرمول پیدا نشه با خطای NULL مواجه میشیم.

برای این کار از یک ساختار دیگه هم میشه استفاده کرد (بدون Space):

=region[[#Totals],[غرب]]

قوانین ساختار Structured Reference

برای ویرایش فرمول های نوشته شده یا ثبت فرمول ها بصورت دستی قوانین زیر رو در نظر داشته باشید:

1. مشخص کننده های آیتم و ستون رو در براکت قرار بدید

همه مشخص کننده ها Item Specifier و Column Specifier باید داخل ]براکت[ قرار داده بشن. هر مشخص کننده ای که خود شامل یک مشخص کننده دیگه هست هم باید داخل براکت قرار بگیره.

region[[شرق] : [غرب]]

2. مشخص کننده های تو در تو باید با , از هم تفکیک بشن

اگر هر مشخص کننده ای شامل یک یا چند مشخص کننده دیگه هست، باید با , از هم جدا بشن. مثلا:

region[[#Headers] , [شرق]]

3. نیازی به استفاده از کوتیشن در این نوع ساختار نیست
4. از ‘ برای کاراکترهای خاص در سرستون ها استفاده کنید

در structured reference بعضی کاراکترها معنی خاصی دارند مثل #، [ ]، ‘ . حالا اگر این کارکترها در سرستونها استفاده شده بود، برای آدرس دادن اون ستون از ‘ استفاده میکنیم. مثلا برای سرستونی که Item# هست، در مشخص کننده ستون از این ساختار استفاده میکنیم: [Item ‘#]

5. از Space برای افزایش خوانایی فرمول استفاده کنید

برای افزایش خوانایی فرمول بصورت structured reference بین مشخص کننده ها میتونیم از Space استفاده کنیم.

=AVERAGE(region[جنوب], region[شمال], region[شرق])

آدرس دهی مطلق و نسبی در Referenced Structure

بصورت پیشفرض وقتی structured reference رو درگ میکنیم با دو حالت زیر روبرو هستیم:

• فرمول هایی با ستون های چندتایی، مطلق هستن و زمانیکه فرمول کپی میشه، فیکس میمونن.
• فرمول هایی با ستون های تکی، نسبی هستن و زمانیکه درگ میشن، تغییر میکنن. اما زمانیکه Copy/Paste بشن یا از کلید ترکیبی Ctrl+C یا Ctrl+V استفاده بشه، تغییر نمیکنن و فیکس باقی میمونن.

زمان هایی که بخواهیم ترکیبی از این دو حالت رو در Table داشته باشیم، راهی نیست جز کپی کردن فرمول و تغییر دستی فرمول. درگ کردن فرمو، برای ستون های تکی، موجب تغییر میشه و کپی پیست باعث فیکس موندن محدوده ها میشه. اما یکی دو تا ترفند رو معرفی میکنیم که راحت تر بتونیم با این نوع ساختار، کار کنیم.

مطلق کردن ستون تکی در Structured Reference

برای مطلق کردن یک ستون تکی، در هنگام درگ کردن فرمول، اسم ستون مورد نظر رو مشابه یک Range و با : تکرار میکنیم:

ستون تکی نسبی (متغیر هنگام درگ کردن)

Table

ستون تکی مطلق (ثابت در هنگام درگ کردن)

Table[

برا یاینکه کاربرد این موضوع رو متوجه بشیم یک مثال حل میکنیم:

در داده های زیر میخواهیم جمع فروش هر محصول رو در ماه های مختلف محاسبه کنیم. اگر فرمول رو به شکل زیر بنویسیم و به سمت راست درگ کنیم، فرمول بصورت نمایش داده شده در شکل ۵ در خواهد آمد.

شکل ۵ – آدرس دهی نسبی ستون تکی برای فرمول نویسی در Table

همونطور که در شکل ۵ مشخص هست این فرمول غلطه و محاسباتی که می میخواهیم رو انجام نمیده. ما میخواهیم ستون Criteria range (یعنی ستون نام محصول) فیکس باشه ولی ستون Sum range (یعنی فروش هر ماه) فیکس نباشه و با درگ کردن تغییر کنه. برای اینکه بتونیم همچنان از این ساختار استفاده کنیم و در عین حال آدرس دهی نسبی و مطلق رو بصورت همزمان داشته باشیم فرمول رو بصورت زیر تغییر میدهیم.

=SUMIF(sale[[محصول]:[محصول]],$F$4,sale[ماه ۱])

همونطور که در بالا توضیح دادیم ستون های چندگانه همیشه فیکس هستن. پس از این طریق، آرگومان اول رو که میخوایم فیکس بمونه، بصورت چندگانه مینویسیم و ستون بعدی که مربوط به ماه ها است و میخواهیم نسبی باشه و تغییر کنه، بصورت تک ستون. حالا درگ میکنیم.

شکل ۶ – آدرس دهی مطلق و نسبی ستون تکی برای فرمول نویسی در Table

نسبی کردن ستون چندتایی در Structured Reference

همونطور که گفتیم، در هنگام درگ کردن، نسبی بودن ستون تکی و مطلق بودن ستون چندتایی، بصورت پیشفرض اتفاق می افتد. نحوه فیکس کردن ستون تکی رو که شرح دادیم. حالا ببینیم چطور میتونیم ستون های چندتایی رو نسبی کنیم.

اگر بخوایم یک Range رو در هنگام درگ کردن نسبی کنیم. باید نام جدول مورد نظر رو قبل از هر دو ستون بیاریم و براکت کلی رو هم برداریم. یعنی:

ستون چندتایی مطلق (فیکس در هنگام درگ کردن)

table[[column1]:[column2]]

ستون چندتایی نسبی (متغیر در هنگام درگ کردن)

table[column1]:table[column2]

ارجاع به ردیف جاری بصورت متغیر

[@column1]:[@column2]

 مثلا فرض کنیم میخواهیم میزان فروش هر ماه رو با ماه قبلش جمع بزنیم. برای این کار اگر فرمول رو به صورت زیر بنویسیم، فرمول کاملا فیکسه و تغییری در درگ کردن نمیکنه.

=SUM(sale[@[ماه ۱]:[ماه ۲]])

اما اگه همین فرمول رو بصورت زیر بنویسیم و درگ کنیم، محدوده بصورت نسبی تغییر خواهد کرد:

=SUM(sale[@[ماه ۱]]:sale[@[ماه ۲]])

شکل ۷ – آدرس دهی نسبی ستون چندتایی در Structured Reference

در این مقاله نحوه استفاده از اجزای Table در فرمول نویسی و نحوه آدرس دهی رو تشریح کردیم. با مطالعه این مقاله خیلی راحت تر میتونیم با داده ها در ساختار table فرمول نویسی انجام بدیم.