توابع ریاضی در اکسل – قسمت سوم

به علت طولانی بودن سرفصل “توابع ریاضی در اکسل”، این سرفصل در سه مقاله ارایه میشود، برای مشاهده ی مقالات دیگر به قسمت پایگاه دانش مراجعه نمایید و یا اینکه تابع اکسل مورد نظر خود را از طریق Search Box پایگاه دانش جستجو نمایید.

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

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

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

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

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

توابع هندسه و ریاضی در اکسل یا Math & Trig Functions توابعی هستند که در ریاضیات و سایر علوم به صورت گسترده مورد استفاده قرار میگیرند، کارهایی ساده مانند جمع کردن، تفریق کردن، به توان رساند و ضرب کردن و کارهایی پیچیده تر مانند حساب کردن سینوس زوایا و یا لگاریتم اعداد همگی در این دسته بندی جای میگیرند. در این مقاله تمام توابع ریاضی در اکسل معرفی شده و کاربرد هریک مورد بررسی قرار میگیرد.

توابع جمع در اکسل

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

تابع SUM در اکسل

ساده ترین تابع جمع در اکسل، تابع SUM میباشد که محتوای سلول ها را با هم جمع میکند،  مثلاً عبارت SUM(A1:A10) مقدار سلول های A1 تا A10 را با هم جمع میکند. برای تابع SUM مخفی بودن یا فیلتر شدن سطر و ستون ها محدودیتی ایجاد نمیکند، یعنی اگر مثلاً سطر دوم در مثال بالا Hide شده باشد (یا فیلتر شده باشد) حاصل این تابع توفیری با قبل ندارد، در هر صورت محتوای سطر مخفی شده یا فیلتر شده در محاسبه حاصل جمع لحاظ میگردد.

تابع SUMSQ در اکسل

این تابع مانند تابع SUM عمل میکند با این تفاوت که تابع SUMSQ مجموع مربعات اعداد را بدست می آرود، مثلاً

SUMSQ (5;3) برابر است با عدد ۳۴٫

تابع SUMIF در اکسل

برای جمع کردن محتوای سلول ها همانند تابع SUM عمل میکند، در تابع SUMIF میتوان برای سلول هایی که میخواهیم حاصل جمع آنها را داشته باشیم محدودیت تعیین کنیم، مثال زیر را در نظر بگیرید:

در جدول بالا لیست اقلام موجود در یک کارگاه به همراه ظرفیت، وزن، وضعیت و ارزش هریک از آنها نشان داده شده است. از شما میخواهند که در یک سلول مجموع ارزش اقلام با ظرفیت باربری برابر و بیش از سه تن را گزارش کنید، در اینجا شما از تابع SUMIF میتوانید استفاده نمایید. فرمول زیر را در یک سلول بنویسید:

SUMIF(E3:E15;”>=3”;F3:F15)

تابع SUMIF سه آرگومان دارد، آرگومان اول محدوده ای است که میخواهید برای آن شرط تعیین کنید و آرگومان دوم شرط مورد نظر است، آرگومان سوم محدوده ای است که در صورت درست بودن شرط در حاصل جمع قرار میگیرد، آرگومان سوم یک آرگومان اختیاری است و اگر آن را خالی بگذارید همان محدوده ای که برای آن شرط تعریف کرده اید در حاصل جمع قرار میگیرد. فایل تمرین را دانلود کرده و این تابع را بیشتر تمرین کنید.

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

تابع SUMIFS در اکسل

همانند تابع SUMIF است با این تفاوت که در تابع SUMIFS میتوان چندین شرط تعیین کرد، مثلاً میخواهید در یک سلول مجموع ارزش اقلام با ظرفیت باربری برابر و بیش از سه تن که وضعیت آنها سالم میباشد را گزارش کنید، تابع زیر را بنویسید:

SUMIFS(F3: F15; E3:E15;”>=3″;G3: G15;”سالم”)

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

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

تابع SUBTOTAL دراکسل

تابع SUBTOTAL برای بدست آوردن SUBTOTAL یک لیست (یک محدوده از اکسل) استفاده میشود، این تابع کاربرد گسترده ای دارد، Syntax تابع SUBTOTAL به صورت زیر است:

SUBTOTAL(function_num, ref1, [ref2], …)

آرگومان اول عملیاتی است که قرار است بر روی لیست مورد نظر (یک یا چند محدوده از سلول ها، یا یک ستون جدول که توسط آرگومان های بعدی به تابع معرفی شده اند) صورت گیرد، آرگومان دوم به بعد محدوده هایی هستند که قرار است بر روی آنها عملیات صورت بگیرد، در این بین آرگومان دوم اجباری و آرگومان های دیگر اختیاری هستند. ارگومان های دوم به بعد تحت عنوان ref1 تا ref254 به تابع معرفی شده اند.

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

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

هریک از اعداد ۱ تا ۱۱ و ۱۰۱ تا ۱۱۱ میتوانند به عنوان آرگومان اول قرار گیرند، اعداد ۱ و ۱۰۱ هر دو به این معنی هستند که تابع SUBTOTAL مقدار میانگین اعداد معرفی شده در آرگومان های بعدی (ref1 تا ref254) را به عنوان خروجی به کاربر ارئه میدهد، تفاوت آنها در این است که اگر در لیست مورد نظر، سطر یا ستونی مخفی شده باشد با نوشتن عدد ۱۰۱ اعداد سطر یا ستون مخفی شده در محاسبه ی میانگین در نظر گرفته نمیشوند ولی اگر عدد ۱ به عنوان آرگومان اول باشد این اعداد در محاسبه ی میانگین در نظر گرفته میشوند.

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

در استفاده از تابع SUBTOTAL به چند نکته توجه کنید:

• اگر در هریک از سلول های ref1 تا ref254 تابع SUBTOTAL قرار داشته باشد، محتوای این سلول در محاسبات لحاظ نمیشود.
• تابع SUBTOTAL هر سطر یا ستونی که توسط فیلتر کردن جدول ها دیده نشود را در محاسبات در نظر نمیگیرد، توجه داشته باشید مخفی کردن سطر و ستون ها با فیلتر کردن سطر و ستون ها متفاوت است.
• تابع SUNTOTAL برای یک ستون از داده ها معتبر است، یعنی اگر برای داده هایی که در یک سطر تعریف شده اند از تابع SUBTOTAL استفاده کنید در برخی موارد دچار مشکل میشوید، بهتر است داده ها در ستون های متفاوت را بجای داده ها در چندین سطر برای این تابع تعریف کنید.