جمع کردن و شمارش داده ها بر اساس رنگ سلول ها

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

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

برای جمع بستن داده ها در اکسل توابع کاربردی با عنوان  Sum  داریم . تابع Sum  در واقع شامل یک خانواده از توابع هستند که حالت های مختلفی را برای جمع کردن اعداد برای ما ممکن می سازند .

از طرف دیگر برای شمارش مقادیر و داده های مربوط به سلول های یک محدوده اکسل تابع Count  را در اختیار ما گذاشته است . تابع Count  هم به نوبه خود دارای چند حالت مختلف است که می توانیم از انها استفاده کنیم .

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

و اما داستان این آموزش از کچا شروع شد .

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

و این تبدیل به کابوسی برای وی شده بود . چون در انتهای هر هفته اکسل را باز می کرد و بین ۲۵۰ قلم کالایی که در اکسل ثبت شده بود یکی یکی به دنبال سلول هایی می گشت که رنگ آنها به رنگ قرمز درآمده بود. 

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

من برای این کار دو راه حل به ذهنم رسید که با انجام یکی از آنها این مشکل برای وی حل شد .

راه اول استفاده از تابع Countif  بود که به کمک ان و با یک شرط ساده از برنامه می خواهیم که فقط کالاهایی را که موجودی ۱۰ و یا کمتر از ۱۰ دارند را برای ما بشمارد .

راه دوم هم این است که با استفاده یک کدنویسی ساده در محیط برنامه نویسی VBA  کاری کنیم که برنامه برای ما سلول هایی که به رنگ خاص هستند – مثلا اینجا رنگ قرمز مد نظر بود – را برای ما بشمرد .

و این شد بهانه ای برای آموزش این مطلب که چطور به جمع کردن و شمارش داده ها بر اساس رنگ سلول ها در اکسل بپردازیم .

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

– نحوه شمارش سلول ها با رنگ پس زمسنه خاص

– نحوه جمع بستن مقادیر سلول ها بر اساس رنگ پس زمینه آنها .

پس اگر شما هم علاقمند به فراگیری این روش هستید  تا انتهای این مطلب با من همراه باشید.

جمع کردن و شمارش داده ها

شمارش داده ها بر اساس رنگ پس زمینه سلول ها 

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

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

جمع کردن و شمارش داده ها

همانطور که در این تصویر هم مشخص است ما لیستی از کالاها را داریم . در این لیست کالاهایی با تعداد موجودی کمتر از ۱۰ در سلول هایی  به رنگ قرمز کالاهایی با تعداد ۱۰ تا ۲۰ در سلول هایی به رنگ زرد هستند . 

سایر کالا هم در سلول های با پس زمینه سفید قرار دارند .

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

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

۱- روش اول استفاده از امکانات خود نرم افزار اکسل است .

۲- روش دوم هم شامل استفاده از قابلیت کد نویسی و برنامه نویسی VBA  در اکسل می باشد .

در اینجا هر دو تا روش را با هم مرور می کنیم .

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

 کاری که اینجا باید بکنیم چندان سخت نیست . اول سلول های خود را بر مبنای رنگی که مد نظرمان است فیلتر می کنیم و بعد با کمک و استفاده از تابع Subtotal  سلول های فیلتر شده را شمارش می کنیم .

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

۱- در جدول بالا ما ۳ نوع سلول داریم . یک دسته از  سلول ها سلول هایی هستند  به رنگ سفید .یک دسته به رنگ قرمز و دسته دیگر هم به رنگ زرد . 

اول  به سراغ تابع subtotal  می رویم و از آن استفاده می کنیم اما چطوری ؟

در یک سلول که خارج از محدوده سلول های حاوی اطلاعات است یک علامت مساوی بزنید و در داخل آن تابع Subtotal  را  بصورت زیر درج کنید .

اما این تابع چه می کند ؟

در این تابع عدد ۱۰۲  ماره مربوط به تابع Count  است . بخش دوم تابع هم که محدوده مورد نظر را برای تابع معرفی می کند .

این از اولین مرحله کار در مرحله دوم باید به سراغ استفاده از فیلتر ها برویم . اینجا می خواهیم سلول های خود را بر اساس رنگ مورد نظر خود فیلتر کنیم . رنگ مورد نظر ما در این مرحله رنگ قرمز است .

جمع کردن و شمارش داده ها

با استفاده از قابلیت فیلتر کردن سلول های با پس زمینه قرمز را برای خودمان فیلتر می کنیم . 

برای فیلتر کردن سلول ها بر اساس رنگ آنها مراحل زیر را دنبال کنید .

۱- اول بر روی اولین سلول از ستون C  یعنی همان سلول C1  کلیک کنید تا انتخاب شود .

۲- از سربرگ Home  به سراغ داشبورد Editing  بروید .

۳ – در این داشبورد گزینه ای داریم به اسم Sort & Filter  بر روی ان کلیک کنید تا باز شود .

۴- از بین گزینه هایی که برای شما نمایش داده می شود بر روی گزینه Filter  کلیک کنید .

جمع کردن و شمارش داده ها

 

۵- Filter by color  گزینه ای است که در این مرحله باید بر روی آن کلیک کنید . با این کار رنگهایی را که در حال حاضر در سلول ها وجود دارند را برای ما نمایش می دهد .

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

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

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

 ترفند اصلی این روش در قابلیت تابع Count  نهفته است . این تابع فقط سلول هایی را شمارش می کند که اولا حاوی اطلاعات باشند و در ثانی فقط سلول هایی را قابل مشاهده هستند را برای شمارش در نظر می گیرد .

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

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

برای این کار روش دیگری نیز از سوی اکسل وجود دارد که در زیر به آن می پردازیم .

روش دوم: شمارش داده ها با استفاده از دستور Get.cell

این مطلب به تدریج تکمیل می شود . 

 

آموزش اکسل بازدید : 194 views 27 اکتبر, 2020 ۰