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

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

تابع COUNT

تابع «COUNT» تعداد سلول‌هایی که در یک محدوده عدد در خود دارند را شمارش می‌کند. گاهی اوقات ممکن است فرمولی داشته باشید که محاسبات زیادی را انجام داده باشد و بخواهید بدانید که چندبار کار خود را تکرار کرده است. در چنین شرایطی تابع COUNT به کارتان خواهد آمد. اگر یک فرمول ارور می‌دهد، با استفاده از این تابع می‌توانید به سادگی ببینید این ارور چند بار رخ داده است.

فیلم آموزش استفاده از توابع و فرمول نویسی در اکسل Excel در فرادرس

کلیک کنید

این تابع تنها زمانی یک سلول را حساب می‌کند که در آن عدد، تاریخ، مقدار منطقی یا متنی وجود داشته باشد که شامل عدد می‌شود. اگر می‌خواهید سلول‌هایی که شامل متن، خطا یا متن خالی می‌شوند را نیز محاسبه کنید، باید از تابع «COUNTA» استفاده نمایید. تابع «COUNTBLANK» تعداد سلول‌های خالی را شمارش می‌کند.

دستور COUNT به شکل زیر نوشته می‌شود:

1=COUNT([range 1], [range 2], ...)

آرگومان [range] محدوده‌ی سلول‌هایی که قصد شمارششان را دارید مشخص می‌کند. در صفحه گسترده‌ی زیر ما تعدادی ستون داریم که شامل نام، نام خانوادگی، شهر، ایالت و امتیاز آن‌ها در آزمون «SAT» می‌شود. برخی از افراد امتیازی دریافت نکرده‌اند و ما می‌خواهیم تعداد این دسته از افراد را شمارش کنیم. ما می‌دانیم که 100 نفر در این آزمون شرکت کرده‌اند، پس با استفاده از تابع COUNT ستون SAT را شمارش می‌کنیم. این کار با استفاده از دستور زیر انجام می‌شود:

1=Count(E:E)

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

همانطور که مشاهده می‌کنید، مقدار امتیاز SAT‏ 85 دانشجو وارد شده است، در نتیجه 15 دانشجو بدون نمره مانده‌اند.

تابع COUNTIF

این تابع بسیار شبیه به تابع «COUNT» عمل می‌کند، ولی به شما این امکان را می‌دهد که با ایجاد شرط، سلول‌های شمارش شده را محدود کنید. این دستور به شکل زیر نوشته می‌شود:

1=COUNTIF([range], [value])

درست همانند دستور «COUNT»، مقدار [range] مشخص کننده‌ی محدوده‌ی شمارش ما است. مقدار [value] مشخص کننده‌ی شرط شما است. برای مثال اگر بخواهیم تمام دانش آموزانی که در تهران تحصیل کرده‌اند را شمارش کنیم، می‌توانیم دستور خود را به شرح زیر بنویسیم:

1=COUNTIF(C:C, "تهران")

فیلم آموزش اکسل – محاسبات ریاضی و ماتریسی با Microsoft Excel 2016 در فرادرس

کلیک کنید

همچنین می‌توانیم با استفاده از این دستور، تعداد دانش آموزانی که نمره‌ای بیشتر از یک نمره‌ی مشخص را دریافت کرده‌اند را بشماریم. برای مثال می‌توانید از مقدار «1500<» در پارامتر «Value» استفاده نمایید. حتی می‌توانید از مقدار یک سلول برای شمارش خود استفاده کنید. برای مثال می‌خواهیم تعداد دانشجویانی که در ایالت «Texas» زندگی می‌کنند را شمارش کنیم. برای این کار فرمول زیر را وارد می‌نماییم:

1=COUNTIF(D:D, D2)

سلول D2 شامل مقدار «Texas» می‌شود که در اینجا به عنوان یک منبع برای ما به حساب می‌آید و ما دیگر نیازی به نوشتن دستی نام شهر نخواهیم داشت.

تابع COUNTIFS

در تابع «COUNTIF» تنها می‌توانستید از یک شرط استفاده کنید، ولی تابع «COUNTIFS» به شما این امکان را می‌دهد تا هر تعداد دستوری که مد نظرتان است را وارد نمایید.

فیلم آموزش اکسل Microsoft Excel 2019 – مقدماتی در فرادرس

کلیک کنید

همچنین می‌توانید بر اساس چندین شرط در چندین محدوده به دنبال محتوای مورد نظر خود بگردید. این دستور به شکل زیر نوشته می‌شود:

1=COUNITFS([criteria_range1], [criteria1], [criteria_range2], [criteria2]…)

برای مثال ما می‌خواهیم از این دستور استفاده کرده و تعداد دانش آموزانی که از ایالت «Hawaii» آمده و نمره‌ی بالای 1500 گرفته‌اند را به دست بیاوریم. این فرمول به شکل زیر نوشته می‌شود:

1=COUNTIFS(D:D, “Hawaii”, E:E, >1500)

با اجرای این دستور، نتیجه‌ی زیر را دریافت خواهیم کرد:

تابع SUMIF

احتمالا می‌دانید که می‌توانید با استفاده از تابع «SUM» تعدادی عدد را با یکدیگر جمع ببندید، ولی اگر تنها بخواهید مجموعه‌ای خاص از اعداد را در محاسبات خود قرار دهید چه کار می‌کنید؟ تابع «SUMIF» به شما این مکان را می‌دهد که بتوانید تنها سطرهایی را شمارش کنید که شرط خاصی در آن‌ها رعایت شده باشد.

فیلم آموزش اکسل – ابزارهای کاربردی Excel در فرادرس

کلیک کنید

این دستور به شکل زیر نوشته می‌شود:

1=SUMIF([range], [criteria], [sum_range])

پارامتر [range] محدوده‌ی شرط ما را مشخص می‌کند. پارامتر [criterion] هم می‌تواند یک عبارت مشخص باشد و هم یک اپراتور منطقی که شرط ما را تعیین می‌کند. برای مثال می‌توانید تمام سلول‌های داخل یک لیست که مقدار آن‌ها بزرگتر از 500 است را با یکدیگر جمع کنید. پارامتر [sum_range] بسیار کاربردی است. این پارامتر مشخص می‌کند که کدام محدوده را می‌خواهیم جمع ببندیم.

بهتر است این مورد را در یک مثال ببینید. برای مثال در صفحه گسترده‌ی زیر یک لیست از افراد و کمک‌هایی که جمع‌آوری کرده‌اند را وارد کرده‌ایم. حال می‌خواهیم تمام مقادیر کمکی «David» را محاسبه نماییم. برای این کار از دستور زیر استفاده می‌کنیم:

1=SUMIF(A:A, "David", B:B)

نتیجه‌ی این دستور به شکل زیر خواهد بود:

در هر کجا که اکسل مقدار «David» را در ستون A پیدا کند، مقدار موجود در ستون B را جمع می‌بندد.

تابع SUMIFS

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

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

کلیک کنید

نحوه نوشتن این تابع کمی از تابع «SUMIF» متفاوت است:

1=SUMIFS([sum_range], [criteria_range1], [criteria1], [criteria_range2], [criteria2]…)

همانطور که مشاهده می‌کنید، پارامتر [sum_range] در ابتدای دستور قرار گرفته و دیگر در خانه‌ی سوم نیست.

به صفحه گسترده‌ی خود یک ستون اضافه کرده‌ایم که شامل ایالتی می‌شود که شخص مبلغ را در آنجا جمع‌آوری کرده است. با استفاده از تابع «SUMIFS» می‌خواهیم ببینیم که «John» چه مبلغی را در ایالت «Michigan» جمع‌آوری کرده است. این کار را با دستور زیر انجام می‌دهیم:

1=SUMIFS(B:B, A:A, “John”, C:C, “Michigan”)

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

بسیاری از کاربران اکسل با تابع «SUM» آشنا هستند، ولی تعداد افرادی که از توابع دیگر نظیر COUNT، ‏COUNTIF، ‏COUNTIFS، ‏SUMIF و SUMIFS استفاده می‌کنند به مراتب کمتر است. با این حال استفاده از این توابع می‌تواند اطلاعات بسیاری خوبی را راجع به جداول‌تان به شما بدهد.

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

• گنجینه آموزش های اکسل (Microsoft Excel)
• آموزش‌های مجموعه نرم‌افزاری آفیس
• مجموعه آموزش‌های مهارت‌های اساسی کار با کامپیوتر
• ۱۶ فرمول اکسل که به حل مشکلات روزمره کمک می‌کنند
• برنامه نویسی VBA در اکسل — آموزش مقدماتی
• توابع INDEX و MATCH در اکسل؛ جست‌وجوی سریعتر در صفحات گسترده

^^