استفاده از فرمول در جدول

در این بخش از آموزش اکسل 2016 به تشریخ چگونگی قرار گرفتن فرمول ها در جدول‌ها پرداخته می شود و با استفاده از فرمول در جدول آشنا می شویم.

جمع کردن مقادیر عددی سلول‌های جدول با یکدیگر

تصویر زیر جدول ساده‌ای متشکل از سه ستون را نشام می دهد که نام جدول به صورت پیش فرض Table1 تعیین شده است.

استفاده از فرمول در جدول در اکسل

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

یکی از سلول های جدول را اانتخاب کنید.

دستور زیر را اجرا کنید تا ردیف مجموع به جدول اضافه شود و حاصل جمع هر ستون به نمایش در آید:

Table Tools→ Design →Table Style Options →Total Row

سلولی را در Total Row فعال کنید و با استفاده از فهرست بازشو، نوع فرمول مورد نظرتان را بر گزینید. برای مثال برای محاسبه معدل ستونActual، تابع Average را از فهرست باز شو برای سلول D15انتخاب کنید. اکسل به صورت خودکار فرمول زیر را درج می کند:

=SUBTOTAL(101 ͵ [Actuall1])

استفاده از فرمول در جدول در اکسل

 

برای تابع SUBTOTAL، عدد 101 آرگومان شمرده شده‌ای است که پاسخ تابع AVERAGE را نشان می دهد. آرگومان دوم برای تابع SUBTOTAL، نام ستونی است که در کروشه قرار گرفته است.با استفاده از نام ستون که در بین کروشه قرار دارد، ارجاع‌های ساختار یافته در جدول ایجاد می شوند. برای خواموش کردن ردیف مجموع می توانید دستور زیر رت دوباره اجرا کنید:

Table Tools→ Design →Table Style Options →Total Row

 

استفاده از فرمول ها در جدول

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

1- سلول E2 را فعال کرده وعنوان ستون جدید را Difference قرار دهید. اکسل به صورت خودکار جدول را بسط می دهد تا ستون جدید به آن اضافه شود.

2- سلول E3  را فعال و با تایپ کارکتر"=" مشخص کنید که قصد درج یک فرمول را دارید.

3- کلید مکان نمای سمت چپ را فشار دهید تا اکسل عبارت [@Actual] که عنوان ستون است را در نوار Formula نمایش دهد.

4- کارکتر"-"را تایپ کنید و دوبار کلید مکان نمای چپ را فشار دهید تا اکل عبارت[@Projected] را در نوار Formula نمایش دهد.

5- کلید Enter را فشار دهید تا عملیات درج فرمول پایان یابد. اکسل فرمول را به تمام ردیف‌های جدول کپی می کند.

استفاده از فرمول در جدول در اکسل

اگر جدول را نگاه کنید متوجه می ‌شوید که برای تمام سلول‌ها در ستونDifference، فرمول زیر درج شده است:

=[@Actual]- [@Projected]

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

می توانید فرمول‌ها را به صورت دستی نیز در جدول درج کنید و در این حالت به جای عناوین ستون‌ها از ارجاع‌های سلولی استاندارد استفاده می شود. برای مثال می توانید فرمول زیر را در سلول E3 درج کنید:

=D3-C3

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

اگر تمایل ندارید فرمول‌ها  به صورت خودکار در سلول‌های ستون‌های جدول کپی شوند از طریق کادر محاوره‌ای Excel Options وارد زبانه‌ی Proofing شده و روی دکمه‌یOptions AutoCorrect کلیک کنید و سپس زبانه‌ی Auto Format As You Type کلیک کرده و تیک گزینه‌ی fill formulas in tables to creat calculated columms را بردارید.

ارجاع به داده‌های موجود در جدول

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

=SUM(Table1)

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

=SUM(AnnualData)

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

=SUM(Table1[Actual])

دقت کنید که نام ستون در بین دو کروشه قرار گرفته است و اگر عنوان ستون را تغییر دهید فرمول هم به صورت خودکار تغییر خواهد کرد. اکسل امکانات جالب تری برای فرمول‌های جدول‌ها در اختیار کابر قرار می دهد تا راحت‌تر بتواند فرمولی بسازد که به داده‌های جدول استناد کند. ویژگی Help خصوصیت AutoComplete فهرستی از عناصر موجود در جدول را مشخص می‌کند که می توان در ساخت فرمول از آنها استفاده کرد. دقت کنید که علاوه بر عناوین ستون‌ها، دیگر عناصر جدول مانند#All،#Data،  #Headersو  #Totalsکه می‌توان به انها ارجاع کرد را نیز فهرست می‌کند.

تصحیح  خطاهای متداول در فرمول‌ها

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

=A1*(B1+C2

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

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

مقدار خطا

شرح

#DIV/0!

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

#NAME?

در فرمول از نامی استفاده شده که اکسل آن را نمی‌شناسد.

#N/A

در فرمول به سلولی ارجاع شده که تابع NA استفاده می‌کند تا اعلان کند که داده‌ها موجود نیستند. بعضی توابع نیز ممکن است مقدار #N/A را برگرداند.

#NULL!

در فرمول از تقاطع دو دامنه که هیچ تقاطعی ندارند استفاده می‌شود.

#NUM!

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

#REF!

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

#VALUE!

فرمول شامل آرگومان یا عملوندی است که نوع نادرست می باشد. همچنین عملوند مقدار یا ارجاع سلولی است که فرمول برای محاسبه یک نتیجه به کار می‌گیرد.

مشخص کردن زمان محاسبه ی فرمول

به طور حتم متوجه شده‌اید که اکسل، عملیات ریاضی را بلافاصله اجرا و نتیجه را درج می کند. اگر مقادیر سلول‌هایی که در فرمول‌ها استفاده می شوند را تغییر دهید، اکسل بدون هیچ وقفه ای، محاسبات را دوباره انجام می‌دهد و نتایج جدید را در سلول‌ها درج می‌کند. البته این امر تا زمانی انجام می شود که حالت Calculation در وضعیت Automatic تنظیم شده باشد. در حالتCalculation Automatic که حالت پیش فرض محاسبات است، اکسل در هنگام انجام محاسبات برای کاربرگ از قوانین زیر تبعیت می کند:

1- وقتی تغییراتی انجام می شوند. برای مثال داده‌ها یا فرمول‌ها را ویرایش می کنید، اکسل بلافاصله آن فرمول‌هایی که وابسته به داده‌های جدید یا ویرایش شده هستند را محاسبه می کند.

2- اگر اکسل در حال انجام یک محاسبه‌ی طولانی باشد و لازم باشد که کارهای دیگری روی کاربرگ انجام شود، به صورت موقت محاسبه را متوقف می‌کند و وقتی آن کارها روی کاربرگ انجام شدند دوباره محاسبه را از سر می گیرد.

3- فرمول‌ها با ترتیب طبیعی مورد ارزیابی قرار می گیرند. به عنوان مثال اگر فرمولی در سلول D12  وابسته به نتیجه ی فرمولی در سلول D24 باشد، اکسل پیش از محاسبه فرمول سلول D12، فرمول سلولD24 را محاسبه می کند.

با وجود این گاهی اوقات ممکن است بخواهید زمان محاسبه‌ی فرمول‌ها به وسیله‌ی اکسل را کنترل کنید. برای مثال اگر کارپوشه‌ای دارای هزاران فرمول پیچیده ایجاد کنید، سرعت اناجم کارها بسیار کند خواهد شد، چون اکسل بیشتر ظرفیت خود را روی انجام این محاسبات متمرکز می‌کند. در چنین حالتی میتوانید حالت محاسبه‌ی اکسل را از طریق دستور زیر به صورت دستی تنظیم کنید.

Formulas→Calculation→Calculation Options→Manual

استفاده از فرمول در جدول در اکسل

وقتی اکسل در حالت Manual Calculation قرار دارد، عبارت Calculate در زمانی که فرمول‌‌های محاسبه نشده‌ای وجود دارند در نوار وضعیت نمایش داده می شود. همچنین برای محاسبه‌ی دوباره‌ی فرمول‌ها می توانید از کلید های میانبر زیر استفاده کنید:

1- F9: با فشردن این کلید فرمول های تمام کارپوشه‌های باز دوباره محاسبه می شوند.

2- Shift+F9: تنها فرمول های کاربرگ فعال را دباره محاسبه می کند و دیگر کاربرگ‌هایی که حتی در کارپوشه ی فعلی را دسا نخورده باقی می گذارد.

3- Ctrl+Alt+F9: تمام فرمول ها را به صورت کامل دوباره محاسبه می کند.

4- Ctrl+Alt+ Shift+F9: ساختار ارجاعات محاسبات را بازسازی می کند و تمام محاسبات را دوباره انجام می دهد.


نویسنده : --

تگ ها : آموزش اکسل
ثبت نظر