توابع در فرمول ها

در این بخش از آموزش اکسل 2016 به سراغ توابع در فرمول ها و مباحث مرتبط با آن می رویم و با مثال توضیح خواهیم داد.

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

چند مثال از فرمول هایی که از توابع استفاده می کنند

تابع می تواند فرمول را تا حد زیادی ساده کند. به مثال زیر دقت کنید:

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

=(A1+A2+A2+A3+A4+A5+A6+A7+A8+A9+A10)/10

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

=AVERAGE(A1:A10)

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

=MAX(A1:D100)

گاهی اوقات توابع می توانند ویرای هایی که دستی انجام می شوند را حذف کنند. فرض کنید کاربرگی داریئ که حاوی هزار نام در سلول های A1:A1000 است و تمام حروف این نام ها، بزرگ تایپ شده اند. رئیستان این فهرست را می بیند و به شما اطلاع می دهد که نام ها با یک فرم ادغام خواهد شد که در آن تمام حروف نام نباید بزرگ باشد و تنها حرف نخست آنها باید بزرگ تایپ شود. در اینجا دو راه پیش روی شماست، یا ساعت ها وقت صرف کرده و نام ها را تصحیح کنید یا فرمولی مانند عبارت زیر که از تابع PROPER برای تبدیل عبارت متنی سلول A1 به حالت دلخواه شما استفاده می کند را به کار بگیرید:

=PROPER(A1)

این فرمول را یک بار در سلول B1 درج و سپس آن را در 909 ردیف دیگر بعدی نیزز کپی کنید سپس دامنه ی BA:A1000 را انتخاب کنید و دستور:

Home→ Clipboard →Coppy

را اجرا کنید تا دامنه مذکور را کپی کند. سپس در حالی مه هنوز دامنه ی B1:B1000 در حالت انتخاب شده قرار دارد، دستور:

Home→ Clipboard →Paste Values

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

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

=IF(A1<100000͵A1*5%͵A1*7.5%)

در واقع این فرمول، نوعی تصمیم گیری را نیز انجام می دهد؛ یعنی ابتدا مقدار سلول A1 را کنترل کرده و گر این مقدار کمتر از 100000 بود، مقدار سلول A1 را در 5 درصد ضرب می کند و نیجه را بر می گرداند. در غیر این صورت، مقدار سلول A1 را در 5/7 درصد ضرب می کند. در این مثال از سه آرگومان استفاده می شود که در بخش بعدی به بررسی آنها خواهیم پرداخت.

آرگومان های تابع

در این بخش از آموزش اکسل 2016 به سراغ آرگومان های تابع می رویم.

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

بدون آرگومان

یک آرگومان

تعداد ثابتی آرگومان

تعداد نامعینی آرگومان

آرگومان های اختیاری

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

=NOW()

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

 

ارجاع سلولی

=SUM(A1:A24)

مقدار عددی

=SQRT(121)

رشته متنی

=PROPER(“john f.smith”)

عبارت ریاضی

= SQRT (183+12)

توابع دیگر

=SQRT(SUM(A1:A24))

 

درج فرمول در کاربرگ

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

توابع در فرمول ها در اکسل

استفاده از خصوصیت Formula AutoComplete

با کمک خصوصیت Formula AutoComplete، عملیات درج فرمول‌ها بسیار ساده تر از قبل شده است. با ذکر یک مثال، به تشریح چگونگی عملکرد این خصوصیت پرداخته می شود. هدف این مثال، ساخت فرمولی است از تابع AGGREGATE برای محاسبه‌ی مقدار میانگین در دامنه‌ای به نام TestScores استفاده می کند. توجه داشته باشید که چون یکی از مقادیر این دامنه، یک مقدار خطاست نمی توانید از تابع AVERAGE استفاده کنید. روال استفاده از خصوصیت Formula AutoComplete  به شرح زیر است:

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

حرف a را تایپ کنید تا فهرستی از توابع و دامنه‌هایی که نام آنها با حرف A شروع می شوند را مشاهده کنید. این خصوصیت نسبت به بزرگ و کوچک بدن حساس نیست پس می توانید از کارکتر‌های بزرگ یا کوچک استفاده کنید.

حرف دیگری را تایپ کنید تا گزینه‌های فهرست، کوتاه‌تر شود.

 

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

توابع در فرمول ها در اکسل

گزینه ی 1-AVERAGE را انتخاب کنید و سپس کلید Tab را فشار دهید تا اکسل عدد 1 را درج کند که در واقع کد مربوط به عملیات محاسبه‌ی میانگین است.

توابع در فرمول ها در اکسل

 

یک کارکتر ویرگول تایپ کنید تا آرگومان دوم را از آرگومان اول جدا کند.

وقتی اکسل، فهرست گزینه‌های آرگومان دوم تابع AGGREGATE را نشان داد، گزینه‌ی 2-Ignore Error Value را انتخاب کنید و سپس کلید tab را فشار دهید.

د.باره یک کارکتر و ویرگول تایپ کنید تا آرگومان دوم از آرگومان سوم ( که دامنه‌ی اعداد است که قرار است معدل گیری شوند) جدا شود.

حرف T را تایپ کنید تا فهرستی از توابع و نام‌هایی که با حرف T شروع می‌شوند نمایش داده شود. با توجه به اینکه می خواهید دامنه‌ای با نام Test Scores را پیدا کنید، اگر حرف e را نیز تایپ کنید، فهرست به صورت کوتاه‌تر در می‌آید.

Test Scores را انتخاب کنید و سپس کلید tab را فشار دهید.

پرانتز را ببندید و کلید Enter را فشار دهید.

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

= AGGREGATE(1͵2͵Test Scores)

خصوصیت Formula AutoComplete شامل موارد زیر است که هر کدام از آنها با آیکونی متفاوت و مجزا شناسایی می ‌شوند:

توابع داخلی اکسل؛

توابع تعریف شده‌ی کاربر( توابعی که کاربر از طریق VBA تعریف کرده است)؛

نام های تعریف شده؛

آرگومان‌های شماره‌گذاری شده که از یک مقدار عددی برای نمایش یک گزینه استفاده می کنند. تنها معدودی از توابع از چنین آرگومان ایی استفاده می کنند؛

ارجاع به ساختار جدول؛


نویسنده : --

تگ ها : آموزش اکسل
تاپ سایت 98
عضویت طلایی
محصولات آموزشی
ثبت نظر