درباره نویسنده
فرمول‌نویسی و بکارگیری توابع
فرمول‌ها و فرمول‌نویسی از مهم‌ترین قابلیت‌های اکسل هستند. فرمول‌ها، عبارت‌های محاسباتی یا مقایسه‌ای هستند که روی انواع داده‌ها به کمک عملگر‌ها، عملیاتی را انجام می‌دهند. تابع یکی از امکانات مهم اکسل می‌باشد که انجام عملیات به روی فرمول‌ها را آسان می‌کند.
فرمول‌ها و فرمول‌نویسی
در اکسل فرمول‌ها با علامت مساوی = شروع شده و هر فرمول می‌تواند یک یا چند قسمت داشته باشد. ثابت‌ها، آدرس سلول‌ها، عملگر‌ها و انواع توابع اجزای فرمول را تشکیل می‌دهند. هر فرمول در یک سلول نوشته شده و هدف از فرمول‌نویسی در سلول تعیین محتوای همان سلول است. همچنین خروجی همه فرمول‌هایی که در یک سلول نوشته می‌شوند فقط یک مقدار می‌باشد. مثلا نمی‌توانیم در سلول A1 فرمولی بنویسیم که محتوای سلول A2 را تعیین کند یا خروجی آن مثلا چند عدد باشد.
علاوه بر علامت مساوی، می‌توانیم از علامت‌های مثبت + و منفی − نیز برای شروع فرمول‌نویسی استفاده کنیم. اما بهتر است از همان علامت مساوی استفاده شود.
ثابت‌ها مقادیری هستند که تغییر نمی‌کنند. مثلا عدد 120 یا عبارت سود خالص. اگر بخواهیم از عدد 200 به جای 120 استفاده کنیم، باید عدد را در فرمول بازنویسی کنیم.
بکارگیری توابع
تابع، فرمول از پیش نوشته شده‌ای است که هیچ، یک یا چند مقدار را به عنوان ورودی گرفته، عملیاتی را انجام داده و هیچ، یک یا چند مقدار را به عنوان نتیجه برمی‌گرداند. با استفاده از توابع فرمول‌ها ساده‌تر می‌شوند، به خصوص که توابع از پیش آماده زیادی در اکسل وجود دارد.
ساختار تابع
تابع دارای 2 قسمت اصلی می‌باشد، نام تابع و آرگومان‌های تابع.
نام تابع کلید واژه‌ای است که نوع عملیات را نشان می‌دهد و در مواردی می‌تواند مخفف کلماتی باشد. آرگومان تابع نیز ورودی یا ورودی‌های تابع است. آرگومان‌ها با یک جداکننده که عموما نقطه ویرگول ; یا ویرگول , می‌باشد از یکدیگر جدا می‌شوند، که در هر سیستم می‌تواند متفاوت ظاهر شود. برای تغییر این جداکننده می‌توانیم به مسیر زیر برویم.
- Control Panel
-
- Region
-
- Formats
-
- Additional Settings
-
- Numbers
-
- List Separator
توابع از نظر آرگومان‌هایشان به 3 دسته تقسیم می‌شوند.
- فاقد آرگومان
- دارای تعدادی آرگومان مشخص
- دارای حداکثر 255 آرگومان
تابع فاقد آرگومان
تابعی است که هیچ ورودی ندارد، اما باید حتما پرانتز باز و بسته پس از نام تابع آورده شود. فرمول زیر یک تابع فاقد آرگومان می‌باشد که یک عدد تصادفی بین ۱ و صفر را به عنوان نتیجه برمی‌گرداند.
RAND()تابع دارای آرگومان مشخص
این نوع توابع، تعدادی ثابت ورودی می‌گیرند و کم و زیاد کردن تعداد ورودی‌ها می‌تواند سبب بروز خطا شود. تابع زیر دارای 2 آرگومان است و باقیمانده تقسیم عدد اول بر عدد دوم را به عنوان نتیجه برمی‌گرداند.
MOD(n,m)تابع دارای حداکثر 255 آرگومان
تابعی مثل Sum می‌تواند 255 ورودی داشته باشد. به عنوان مثال، برای جمع محدوده A1:A7 می‌توان حالت‌های زیر را در نظر گرفت.
=SUM(A1:A7)
=SUM(A1:A3,A4:A7)
=SUM(A1:A2,A3:A5,A6:A7)آرگومان می‌تواند دارای یک سلول یا محدوده‌ای از سلول‌ها باشد، بنابراین 255 آرگومان به معنی 255 سلول نیست.
بعضی آرگومان‌ها اجباری هستند و باید در تابع تخصیص داده شوند، در غیر اینصورت تابع با خطا مواجه می‌شود. اما بعضی آرگومان‌ها در بعضی توابع اختیاری هستند و می‌توانیم از اختصاص آنها صرف‌نظر کنیم.
بعضی آرگومان‌ها، درون کروشه [] نشان داده می‌شوند، که نشان‌دهنده این است که این آرگومان اختیاری است. بقیه آرگومان‌ها که در درون کروشه [] نیستند، اجباری بوده و باید در تابع تخصیص داده شوند.
بیشتر بخوانید: روش‌های محاسبه تاریخ
تعریف تابع
تابع را می‌توان به 2 روش تعریف کرد. تایپ تابع یا استفاده از Insert Function.
تایپ تابع
تایپ تابع مستلزم دانستن نام تابع و آرگومان‌های مورد نیاز آن می‌باشد. بعد از وارد کردن علامت مساوی، تایپ نام تابع مورد نظر و باز کردن پرانتز، آرگومان‌های مربوط به آن نمایش داده می‌شود. با کمک ابزار توضیح، تعداد و آرگومان‌های تابع را می‌توانیم تشخیص دهیم.
تعریف تابع بوسیله تایپ نام تابع
استفاده از Insert Function
می‌توانیم بعد از وارد کردن علامت مساوی، تایپ نام تابع مورد نظر و باز کردن پرانتز، به روی دکمه fx در نوار فرمول کلیک کنیم. آرگومان‌های تابع در پنجره‌ای به نام Insert Function نمایان می‌شوند و می‌توانیم در این پنجره آرگومان‌ها را اختصاص دهیم.
تعریف تابع توسط Insert Function
به طور پیش‌فرض در سلول‌هایی که دارای فرمول هستند، نتیجه فرمول نشان داده می‌شود و محتوای آن با انتخاب سلول در نوار فرمول قابل مشاهده می‌باشد. اما اگر بخواهیم به جای نتیجه محاسبه، محتوای فرمول را در سلول مشاهده کنیم، باید به مسیر زیر برویم. همچنین می‌توانیم از کلیدهای ترکیبی Ctrl+` استفاده کنیم.
- Formulas
-
- Formula Auditing
-
- Show Formulas
ترکیب توابع
گاهی لازم است که از نتیجه سایر توابع، در یک تابع به عنوان ورودی استفاده کنیم. به این ترتیب، لازم نیست که حتما توابع مورد نیاز جداگانه تعریف شوند، بلکه می‌توانیم از آنها به عنوان آرگومان در تابع استفاده کنیم. در حالت‌های خاص، آرگومان‌های یک تابع می‌توانند نتیجه یک تابع دیگر باشند. مثلا در فرمول زیر، تابع Average درون تابع If تعریف شده است که محاسبه تابع Sum درون تابع If را کنترل می‌کند.
=IF(AVERAGE(F2:F5)>50,SUM(G2:G5),0)تابع Average و Sum هر دو در یک سطح (سطح دوم) قرار دارند، یعنی هر دو آرگومان‌های تابع If هستند. اگر تابعی را به جای آرگومان تابع Average یا Sum قرار دهیم، تابع سطح سوم خواهد شد.
در فرمول‌نویسی، به ازای هر تابع، یک پرانتز باز شده و یک پرانتز بسته می‌شود. در ترکیب توابع، تعداد و محل قرارگیری پرانتزها از اهمیت بالایی برخوردار است. مثلا در تصویر 3 پرانتز مشکی مربوط به تابع Index و پرانتز قرمز مربوط به تابع Match می‌باشد. با دقت به این رنگ‌ها می‌توانیم تشخیص دهیم که پرانتز کدام تابع به درستی بسته نشده یا در جای مناسبی قرار ندارد.
اهمیت پرانتزها در فرمول‌نویسی
در ترکیب توابع، اگر این پرانتزها به درستی بسته نشده باشند با پیغام تصویر 4 روبه‌رو می‌شویم.
پیغام نمایش داده شده در صورت جابه‌جایی یا کمبود پرانتزها در فرمول‌نویسی
بیشتر بخوانید: روش‌های محاسبه زمان
عیب‌یابی فرمول‌ها (Debug)
عیب‌یابی فرمول (Debug) اصطلاحا به معنی مشاهده نحوه محاسبات اجزای یک فرمول و برطرف کردن مشکلات آن می‌باشد. اگر بخواهیم نحوه عملکرد یک فرمول را بررسی کرده و ببینیم این فرمول چطور محاسبات را انجام داده و یا مطمئن شویم که آیا این فرمول درست نوشته شده است یا خیر، باید آن را مرحله به مرحله بررسی کنیم.
روش اول: ابزار Evaluate Formula
یکی از روش‌های Debug کردن فرمول استفاده از ابزار Evaluate Formula می‌باشد. فرض کنید می‌خواهیم نحوه عملکرد فرمول زیر را بررسی کنیم.
=IF(A2>=100,A2*5%,IF(A2<100,A2*2%,0))سلول دارای فرمول را انتخاب کرده و به مسیر زیر می‌رویم. در قسمت Evaluation فرمول مورد نظر نشان داده می‌شود که در زیر اولین عبارت آن خط کشیده شده است. با هر بار کلیک به روی دکمه Evaluate نتیجه عبارتی که زیر آن خط کشیده شده است نشان داده می‌شود.
- Formulas
-
- Formula Auditing
-
- Evaluate Formula
Debug کردن فرمول توسط Evaluate Formula
روش دوم: استفاده از کلید F9
روشی دیگر برای Debug کردن فرمول، استفاده از کلید F9 می‌باشد. مزیت این روش نسبت به Evaluate Formula در این است که می‌توانیم مثلا در یک فرمول پیچیده فقط قسمتی خاص از آن را Debug کنیم. اما در روش قبل، این عملیات از داخلی‌ترین عبارت شروع شده تا کل فرمول محاسبه شود. در این روش باید عبارتی که می‌خواهیم نحوه محاسبات آن را مشاهده کنیم را انتخاب کرده و کلید F9 را فشار دهیم. اگر بخواهیم نحوه انجام محسابات مربوط به یک تابع را مشاهده کنیم، باید این تابع را به طور کامل به همراه پرانتزها و اجزای آن انتخاب کنیم.
Debug کردن فرمول توسط کلید F9
پس از Debug کردن فرمول، برای خارج شدن از سلول باید از کلید Esc استفاده کنیم. زیرا با زدن کلید Enter عبارت‌هایی که محاسبه شده‌اند جایگزین فرمول شده و دیگر با تغییر سلول‌های استفاده شده، نتیجه فرمول تغییر پیدا نمی‌کند.
Debug کردن فرمول در پیدا کردن علت خطا و خروجی‌های نادرست به ما کمک می‌کند. همچنین با استفاده از این ویژگی می‌توانیم نحوه عملکرد توابع را بهتر درک کنیم.
سلول‌های تاثیرگذار و تاثیرپذیر
Trace Precedents
فرض کنید می‌خواهیم فرمولی را حذف کرده یا تغییر دهیم. قبل از این‌کار باید ببینیم که این فرمول به روی چه سلول‌هایی تاثیر می‌گذارد. سلولی که دارای فرمول می‌باشد را انتخاب کرده و به مسیر زیر می‌رویم. با انجام این‌کار همه سلول‌هایی که در این فرمول اثرگذار هستند نشان داده می‌شوند. همه ارتباطات با فلش آبی نشان داده شده و اگر شیت‌های دیگر نیز در این فرمول اثرگذار باشند با خط‌چین مشکی نشان داده می‌شوند.
- Formulas
-
- Formula Auditing
-
- Trace Precedents
نمایش سلول‌های تاثیرگذار در فرمول
اگر سلول انتخاب شده، به روی سلول‌ها یا شیت‌های دیگر اثر نداشته باشد با پیغام تصویر 8 مواجه می‌شویم.
پیغام نمایش داده شده در صورت عدم تاثیرگذاری بین سلول انتخاب شده و سایر سلول‌ها
Trace Dependents
اگر بخواهیم سلولی را حذف کرده یا تغییر دهیم، باید ببینیم که این سلول به روی چه فرمول‌هایی تاثیرگذار است. اگر یک سلول را انتخاب کرده و به مسیر زیر برویم، از این سلول به سمت همه سلول‌هایی که از این سلول تغذیه می‌شوند فلش آبی نشان داده شده و همه سلول‌هایی که این سلول به روی آنها اثر گذاشته مشخص می‌شوند. اگر این سلول با شیت‌های دیگر در ارتباط باشد با خط‌چین مشکی نشان داده می‌شود.
- Formulas
-
- Formula Auditing
-
- Trace Dependents
نمایش سلول‌های تاثیرپذیر از یک سلول
اگر سلول انتخاب شده، از هیچ سلول یا شیت دیگری تاثیر نگیرد با پیغام تصویر 10 مواجه می‌شویم.
پیغام نمایش داده شده در صورت عدم تاثیرپذیری بین سلول انتخاب شده و سایر سلول‌ها
اگر بخواهیم همه این نشانه‌گذاری‌ها را از شیت حذف کنیم، باید به مسیر زیر برویم. همچنین گزینه Remove Precedent Arrows همه نشانه‌گذاری‌های مربوط به سلول‌های تاثیرگذار و گزینه Remove Dependent Arrows همه نشانه‌گذاری‌های مربوط به سلول‌های تاثیرپذیر را حذف می‌کند.
- Formulas
-
- Formula Auditing
-
- Remove Arrows
بیشتر بخوانید: نام‌گذاری محدوده‌ها
حالت‌های محاسباتی فرمول
در اکسل انجام محاسبات به طور پیش‌فرض به صورت خودکار (Automatic) انجام می‌شود. وقتی یک فرمول را می‌نویسیم با زدن کلید Enter نتیجه آن قابل مشاهده می‌باشد و با تغییر اجزای فرمول، نتیجه آن بروزرسانی می‌شود. اگر بخواهیم با انجام تغییرات، نتیجه فرمول‌ها تغییر پیدا نکند باید محاسبات را به روی حالت دستی (Manual) تنظیم کنیم. برای انجام این‌کار به مسیر زیر می‌رویم. در این حالت، با انتخاب گزینه Calculation Now یا زدن کلید F9 محاسبات بروزرسانی می‌شود.
- Formulas
-
- Calculation
-
- Calculation Options
-
- Manual
اگر فایل مورد نظر دارای فرمول‌ها و داده‌های زیادی باشد، در حالت عادی، با هر بار تغییر در شیت کل فایل محاسبه شده، که این‌کار زمان‌بر خواهد بود. اما اگر محاسبات را به‌روی حالت (Manual) تنظیم کنیم، با هر بار تغییر در فایل، محاسبه انجام نمی‌شود. پس برای بهینه‌سازی فایل‌ها بکارگیری این روش توصیه می‌شود.
