انجام پروژه اکسل و اکسس

#مدرس_اکسل : آموزش فارسی و روان جمع و شمارش شرطی در اکسل به همراه ویدیو

در این آموزش می خواهم به طور مفصل، جمعِ شرطی و شمارشِ شرطی در اکسل را برایتان توضیح دهم. در حقیقت شرح چهار تابع؛ COUNTIF، SUMIF، COUNTIFS و SUMIFS . این چهار تابع، از جمله توابع پرکاربرد اکسل است و تقریبا در هر کاربردی اعم از اداری، مالی و حسابداری، مهندسی، منابع انسانی و ... کاربرد فراوان دارد و دانستن آن برای هر کاربر اکسلی، ضروری است. این آموزش کاملا تصویری و با مثالی فارسی و ملموس توضیح داده می شود. اگر تمایل داشتید، می توانید آموزش تابع VLOOKUP را هم در اینجا ببینید.

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

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

09392218434-09211895199

چرا می گوییم جمع شرطی یا شمارش شرطی؟

اجازه بدهید با یک مثال توضیح دهم. تصویر زیر را در نظر بگیرید:

صورت مسئله این است: مجموعه ای از رکوردهای فروش محصولات لبنی داریم (ستون های A تا E در تصویر بالا). در هر رکورد (سطر)، اطلاعات فروش ما اعم از نامِ برند، نامِ کالا، تعداد، قیمت واحد و مبلغ کل آن (حاصلضربِ تعداد در قیمت واحد) آمده است. مثلا در اولین رکورد، تعداد 17 کره ی کاله با قیمت واحد 1305 و مبلغ کل 22185 فروخته شده است.

حال می خواهیم اطلاعات جدول سمت راست را به دست بیاوریم. مثلا می خواهیم بدانیم چند بار دوغ هراز فروخته ایم؟ چه تعداد فروش بیشتر از بیست هزارتومان داشته ایم؟ مجموع کل فروش خامه چوپان چقدر بوده است؟ و الی آخر. ممکن است بگویید خب ابزار Pivot Table هم که این کار را برای ما انجام می دهد! چه نیازی است که این همه دردِ سر داشته باشیم و خود را درگیر توابع کنیم؟ در جواب این سوال، چند نکته را لازم است یادآوری کنم:

اول اینکه همیشه و همه جا نیازی است و لزومی ندارد Pivot Table تهیه کنیم، مثلا آیا منطقی است که به ازای هر کدام از خواسته های این مسئله (مثلا مجموع کل فروش خامه ی چوپان) من یک Pivot Table تهیه کنم؟ چندین Pivot Table باید تهیه کنید و مقدار را از آن بخوانید و کپی کنید که قطعا منطقی نیست.

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

حال به سوالی که در ابتدای این بخش مطرح کردم، پاسخ دهم؛ چرا میگوییم جمع شرطی یا شمارش شرطی؟ به عنوان مثال، اولین مورد از جدول آبی رنگ در صورت مسئله را در نظر بگیرید؛ می خواهیم بدانیم چندبار مشتری دوغ هراز خریده است (تعدادش در این مورد مهم نیست، تنها دفعات رخداد فروش از برند هراز مهم است). اینجا با یک عملیات شمارش روبه رو هستیم، به عبارت چندبار؟ دقت کنید؛ حالا چرا می گوییم شرطی؟ چون به شرطی می شمارم که هر رکورد از برند هراز باشد؛ پس به همین دلیل می گوییم شمارش شرطی!

راجع به جمع شرطی هم همینطور، مثلا این سوال را در نظر بگیرید:

مجموع فروش دوغ چقدر بوده است؟ اینجا هم یک عملیات جمع کردن روی ستون مبلغ کل داریم، اما یک شرط هم داریم که نوع کالا، دوغ باشد! حالا برویم سراغ توضیح چهار تابع که برای ما جمع شرطی و شمارش شرطی را انجام می دهند.

شمارش شرطی تنها با یک شرط (تابع COUNTIF)

اولین و ساده ترین حالت همین است. این سوال که کلا چندبار از برند هراز فروش داشته ایم، با استفاده از این تابع محاسبه می شود. چون که می گوییم چندبار، پس باید از خانواده توابع COUNT استفاده نموده و چون شرط داریم (نام برند حتما هراز باشد) و تعداد شرط ها تنها یک مورد است، پس باید از این تابع استفاده کنیم. سینتکس تابع COUNTIF به صورت زیر است:

COUNTIF( range, criteria )

این تابع دو ورودی دارد، محدوده (range) و شرط (criteria). لطفا دقت بفرمائید که اصلا لازم نیست شما سینتکس تابع را حفظ کنید. مهم این است که تنها نوع ورودی ها را بدانید. مثلا بدانید محدوده چیست؟ شرط چیست؟ چگونه می توان برای مقادیر متنی یا عددی یا تاریخی شرط گذاشت؟ که در ادامه توضیح می دهم. حال برویم و همین ورودی های همین تابع COUNTIF را بررسی کنیم:

ورودی اول: range یا محدوده

محدوده یا range جایی است که شما می خواهید در آن شرطتان را چک کنید. الان شرط ما چیست؟ بله اینکه نام برند هراز باشد. این شرط باید در کدام محدوده چک شود؟ بله ستون نام برند. پس محدوده را می توانم بصورت A:A به تابع COUNTIF بدهم. البته به دلیل آنکه کلا به تعداد 455 رکورد داریم در این شیت، محدوده را می توانستم بصورت A1:A455 هم وارد کنم.

نکته: در اکسل منظور از محدوده یا رِنج، ناحیه ای از سلول هاست که از نام دو سلول که در بین آنها یک علامت ":" قرار دارد، مشخص می شود. علامت ":"به معنی "تا" است. مثلا وقتی می نویسیم A1:C17 یعنی از سلول A1 تا سلول C17.

ورودی دوم: criteria یا شرط

با این ورودی، شرطی که شمارش بر مبنای آن می بایست انجام شود را مشخص می کنیم. شرط ما اینجا چیست؟ بله، هراز بودن شرط ما است که باید در محدوده ی وارد شده در قسمت range چک شود. پس شرط را به صورت "هراز" وارد می کنیم. البته می توانستیم به عنوان ورودی criteria، تنها روی یک سلول که در آن کلمه ی هراز درج شده هم کلیک کنیم. تصویر زیر هر دو حالت را نشان می دهد:

شمارش شرطی با بیش از یک شرط (تابع COUNTIFS)

این سوال را در نظر بگیرید: کلا چند بار دوغ فروخته ایم که مبلغ کل آن بیش از 20 هزار تومان بوده است؟ در واقع می خواهیم تعداد رکوردهایی را بشماریم که نوع کالا در آنها، دوغ بوده و مبلغ کل هم بیش از 20 هزارتومان بوده است. چون در این جا مسئله ی شمارش مطرح است، از یکی از توابع خانواده ی COUNT باید استفاده کنیم و ضمنا چون تعداد شرط ها بیش از یکی است (دو شرط داریم؛ یکی روی نام کالا و یکی هم روی مبلغ) پس می بایست از تابع COUNTIFS استفاده کنیم. سینکتس این تابع به صورت زیر است:

COUNTIFS( criteria_range1, criteria1, [criteria_range2, criteria2, ... criteria_range_n, criteria_n] )

در این تابع، زوج هایی به صورت criteria_range و criteria داریم که در انتهای هر کدام یک عدد آمده است، هر شرطی، مثلا criteria1 در محدوده ی مربوط به همان شرط یعنی criteria_range1 چک می شود. ما در این مثال دو شرط داریم که باید برای هر کدام محدوده ی مرتبط با آن را هم مشخص کنیم. دقت بفرمائید تقدم و تاخر شرط ها و محدوده هایشان مهم نیست.

تنها نکته ی مهم این است که دقیقا برای هر شرطی، محدوده ی مرتبط با همان شرط (و نه شرط های دیگر را) به تابع بدهیم. محدوده ی شرط اول را B:B یعنی ستون نام کالا قرار می دهیم، شرط اول ما "دوغ" است که هم می توانیم به صورت ثابت متنی وارد کنیم (یعنی خود کلمه ی دوغ را بین دبل کوتیشن قرار دهیم) یا اینکه از سلول دیگری (اینجا سلول B9) که در آن کلمه ی دوغ نوشته شده است درج کنیم. محدوده ی شرط دوم ستون مبلغ کل یعنی E:E است و شرط آن یعنی بزرگتر از 20 هزار بودن را هم بصورت "20000<" وارد می کنیم.

دقت بفرمائید که شرط های تابع COUNTIF و COUNTIFS (و همچنین SUMIF و SUMIFS) حتما باید بصورت متنی وارد شوند، پس حتما شرط روی ستون مبلغ کل را داخل دبل کوتیشن قرار دهید.

جمع شرطی تنها با یک شرط (تابع SUMIF)

به عنوان مثالی از جمع شرطی با یک شرط، این سوال را در نظر بگیرید: کلا چه میزان (مبلغ) دوغ فروخته ایم. در این حالت با یک جمع شرطی مواجه ایم. سینتکس تابع SUMIF به صورت زیر است:

SUMIF( range, criteria, [sum_range] )

ورودی های این تابع 3 تاست. محدوده (range)، شرط (criteria) و محدوده ای که روی آن می خواهیم عملیات جمع ([sum_range]) انجام شود. دقت بفرمائید که ورودی آخر یعنی [sum_range] داخل براکت قرار گرفته است. در اکسل هر ورودی تابع که داخل براکت باشد، اختیاری است؛ یعنی بسته به شرایط و کاربرد، می توان آن ورودی را وارد کرد و یا از ورود آن صرف نظر کرد.

در مورد تابع SUMIF اگر ورودی آخر وارد نشود، محدوده ی [sum_range] همان محدوده ی range در نظر گرفته می شود؛ این مورد برای مواقعی استفاده می شود که ستونی که می خواهیم روی آن جمع انجام شود، همان ستونی است که می خواهیم در آن شرط چک شود، مثلا وقتی بخواهیم مجموع کل فروش های بیش از 20 هزار تومان را حساب کنیم، در این حالت، هم محدوده ای که روی آن می خواهیم جمع انجام شود و هم محدوده ای که میخواهیم در آن شرط ما چک شود، یکسان و ستون مبلغ کل است).

حال ببنیم این ورودی ها هرکدام به چه معنی است. ورودی range محدوده ای است که شما می خواهید در آن شرطی را چک کنید. در مثالی که داریم، شرط ما دوغ بودن است و محدوده ای که می خواهیم در آن این شرط چک شود محدوده ی نام کالا یعنی B:B است. خود شرط را هم می توانیم به صورت "دوغ" وارد کنیم و هم اینکه از سلولی بخوانیم (مشابه توضیح قسمت قبلی یعنی تابع COUNTIFS). ورودی [sum_range] هم محدوده ای است که می خواهیم جمع روی آن انجام شود.

لطفا دقت کنید! ما در یک محدوده شرط چک می کنیم (اینجا محدوده ی B:B)  که به آن range می گوییم و در یک محدوده ی دیگر جمع انجام می دهیم (ستون مبلغ کل یعنی E:E) که این دو محدوده لزوما یکسان نیستند و باید به این نکته توجه کنیم و محدوده ها را جابه جا وارد نکنیم. مجددا تاکید میکنم که اگر محدوده های range و [sum_range] یکسان بود، نیازی به وارد کردن [sum_range] نیست و تابع SUMIF خودش [sum_range] را مشابه range در نظر می گیرد. به عنوان نمونه ای از این حالت، شما خودتان مجموع فروش های بیش از 20 هزارتومان را بدست آورید! در این حالت هر دو محدوده یکسان است. پس با توجه به این توضیحات، نهایتا داریم:

یک نکته ی حیاتی دیگر را هم بگویم، دقت بفرمائید که محدوده ها در همه ی این چهار تابع باید متناسب با هم باشند، یعنی مثلا اگر در تابع SUMIF شما محدوده ی range را به صورت B:B (به معنی کل ستون B) وارد می کنید، دیگر نباید محدوده ی [sum_range] را به صورت محدود یعنی به صورت E1:E451 وارد کنید. هر دو باید متناسب با یکدیگر باشند، یعنی یا هر دو را به فرم B:B و E:E وارد کنید یا اینکه هر دو را به فرم B1:B451 و E1:E451.

جمع شرطی با بیش از یک شرط (تابع SUMIFS)

آخرین تابعی که در این آموزش بحث می کنیم، تابع SUMIFS است. زمانی که می خواهیم جمع شرطی انجام دهیم و ضمنا شرط های ما بیش از یکی است، باید از این تابع استفاده کنیم. این سوال را در نظر بگیرید: کلا چه میزانی (مبلغی) خامه ی چوپان فروخته ایم؟ ماهیت مسئله، یک جمع است؛ پس بنابراین باید از یکی از توابع خانواده ی SUM استفاده کنیم؛ همچنین به دلیل اینکه بیش از یک شرط داریم، می بایست از تابع SUMIFSاستفاده شود. برویم سراغ این تابع:

سینتکس تابع SUMIFS به فرم زیر است:

SUMIF( sum_range, criteria_range1, criteria1, [criteria_range2, criteria2, ... criteria_range_n, criteria_n] )

ورودی sum_range یا محدوده ی جمع:

با این ورودی، محدوده ای که می خواهیم جمع در آن انجام شود را مشخص می کنیم؛ در مثال ما، محدوده ای که می خواهیم جمع در آن انجام شود، ستون مبلغ کل است، پس این ورودی را به صورت E:E یا بصورت E1:E451 وارد می کنیم.

ورودی criteria_range1 یا محدوده ی شرط اول:

با این ورودی، محدوده ای که می خواهیم شرط اول ما در آن چک شود را وارد می کنیم که باید متناسب با محدوده ی sum_range باشد. در مثال ما شرط اول این است که نام کالا خامه باشد، پس محدوده ی شرط اول، ستون نام کالا یعنی  B:B است.

ورودی criteria1 یا خود شرط اول:

با این ورودی، خود شرط اول را به تابع SUMIFS اعلام می کنیم که در مثال ما شرطِ اول، خامه است که به صورت "خامه" وارد می کنیم، یا اینکه مقدار آن را از سلول B3 می خوانیم.

ورودی criteria_range2 یا محدوده ی شرط دوم:

با این ورودی، محدوده ای که می خواهیم شرط دوم ما در آن چک شود را وارد می کنیم که باز هم باید متناسب با محدوده ی sum_range باشد. در مثال ما شرط دوم این است که نام برند چوپان باشد، پس محدوده ی شرط دوم، ستون نام برند یعنی  A:A است.

ورودی criteria2 یا خود شرط دوم:

با این ورودی خود شرط دوم را به تابع SUMIFS اعلام می کنیم که در مثال ما شرطِ دوم، چوپان است که به صورت "چوپان" وارد می کنیم یا اینکه مقدار آن را از یک سلولی که حاوی کلمه چوپان است (مثلا A3 ) می خوانیم.

نهایتا تمام ورودی ها را مطابق تصویر زیر وارد می کنیم:

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

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

با تشکر