مطالب برتر
حضرت علی (ع): «زَکوةُ العِلمِ نَشرُهُ؛ زکات علم نشر آن است».
وبلاگ "ترفندها" (سایت پی‌سی‌ترفند) برای عمل به این حدیث شریف و با هدف ارتقاء دانش و مهارت‌های مرتبط با رایانه در ۳۰ مهرماه ۱۳۹۱ ایجاد شده است. به امید آنکه مفید واقع شود.
🔵 کانال پی‌سی‌ترفند در تلگرام:
telegram.me/pctarfandir
🔵 ارتباط با ادمین:
telegram.me/pctarfand_admin
تبليغات
۲۰
شهریور ۹۴
نظرات
۱۸
شمارش و جمع زدن سلول‌ها براساس رنگ زمینه و فونت آن‌ها در اکسل + ماکرو
مرادی
اگر بخواهید تعداد سلول‌هایی را که رنگی کرده‌اید یا سلول‌هایی را که با فرمت شرطی رنگ شده‌اند را بشمارید و یا اینکه مجموع اعداد سلولهای رنگی را محاسبه کنید، چه کار می‌کنید؟ بسته به تعداد سلولهای رنگی، شمارش و جمع زدن دستی آن‌ها ممکن است بسیار زمان‌بر و همراه با خطا باشد. در ادامه مطلب با پی‌سی‌ترفند همراه باشید تا با روش‌های خودکار شمارش و جمع زدن سلولهای رنگی با استفاده از امکانات اکسل آشنا شوید.
روش ۱) با استفاده از فرمول - ساخت تابع جدید بوسیله ماکرونویسی
اکسل فرمول‌های مختلفی برای کاربردهای متفاوت دارد اما متاسفانه هیچ فرمولی وجود ندارد که امکان جمع زدن و یا شمارش برحسب رنگ را به ما بدهد. بنابراین باید با استفاده از ماکرو نویسی یک تابع جدید برای اکسل تعریف کرد.
فرض کنید در جدولی مشابه شکل زیر لیست سفارش‌های یک شرکت را دارید که در آن سلول‌ها در ستون Delivery برمبنای مقدار خود رنگ شده‌اند، سلولهای Due in X Days نارنجی، Delivered سبز و Past Due قرمز هستند.
آنچه اکنون می‌خواهیم شمارش خودکار سلول‌ها برحسب رنگ است یعنی محاسبه تعداد سلولهای قرمز، سبز و نارنجی.
🔵 ماکرونویسی
پس از باز کردن فایل اکسل، اطلاعات خود را در فایل باز شده کپی کنید.
۴ تابع CountCellsByColor ،SumCellsByColor ،CountCellsByFontColor و SumCellsByFontColor در این فایل قابل اجرا است.
حال اگر فرض کنید که محدوده مورد نظرتان F2:F14 باشد و بخواهید تعداد سلول‌هایی که دارای رنگ پس زمینه قرمز باشند که در اینجا سلول A17 دارای رنگ قرمز است را بشمارید از فرمول زیر استفاده کنید:
=CountCellsByColor(F2:F14,A17)
با روش مشابهی میتوانید فرمولی برای رنگ‌های دیگر ایجاد کنید.
اگر داده‌های عددی درون سلولهای رنگ شده داشته باشید (مانند ستون Qty در شکل) میتوانید به راحتی جمع را نیز برحسب رنگ محاسبه کنید که در این شکل نشان داده شده است:
همان طور که می‌بینید از فرمول زیر استفاده کرده‌ایم که در آن D2:D14 محدوده و A17 سلول با الگوی رنگ مورد نظر است.
=SumCellsByColor(D2:D14,A17)
با روش مشابهی میتوانید سلول‌هایی را جمع بزنید که دارای یک رنگ قلم (Font Color) خاص هستند که برای این کار به ترتیب از فرمول‌های CountCellsByFontColor و SumCellsByFontColor استفاده کنید.
منبع: techreaders.ir (ترجمه شده از سایت ablebits.com)
البته از کدهای دیگری نیز میتوانید استفاده کنید که در منابع زیر ارائه شده است:
روش ۲) با استفاده از فیلتر کردن بر اساس رنگ
نحوه فیلتر کردن در این مطلب آموزش داده شده است.
اما در مورد این حالت خاص یعنی نحوه فیلتر کردن بر اساس رنگ مثلا در شکل زیر، ابتدا ستونی که می‌خواهید براساس رنگ فیلتر شود انتخاب کنید.
سپس از تب Data، روی دکمه Filter کلیک کنید تا دکمه 
در کنار عنوان ستون ظاهر شود.
روی دکمه کلیک کرده و از لیست باز شده روی گزینه Filter by Color کلیک نموده و رنگ مورد نظرتان برای فیلتر کردن را انتخاب کنید.
پس از اینکار لیست مورد نظر براساس رنگی که انتخاب کردید فیلتر می‌شود.
پس از فیلتر کردن با انتخاب سلولهای فیلتر شده میتوانید تعداد و مجموع آن‌ها را در نوار وضعیت پائین اکسل مشاهده کنید و یا اینکه با استفاده از توابع AGGREGATE یا SUBTOTAL تعداد و یا مجموع آن‌ها را محاسبه کنید. نحوه بکارگیری این دو تابع در این سایت و این سایت آموزش داده شده است.
روش ۳) با استفاده از جستجو بر اساس رنگ
ابتدا محدوده‌‌ی مورد نظرتان را انتخاب کنید. سپس کلید Ctrl + F را بفشارید تا پنجره Find and Replace باز شود.روی تب Find کلیک کنید. روی دکمه Format کلیک کرده و گزینه Choose Format From Cell را انتخاب کنید تا یک رنگ گیر (Color picker) ظاهر شود. سپس روی سلولی که دارای رنگ زمینه مورد نظرتان است کلیک کنید تا رنگ آن به عنوان رنگ جستجو انتخاب شود.
* اگر دکمه Format وجود ندارد روی دکمه Options کلیک کنید تا ظاهر شود.
سپس روی دکمه Find All کلیک کنید تا نتیجه جستجو در قسمت پائین پنجره جستجو نمایش داده شود. اکنون میتوانید همه نتایج جستجو را انتخاب کنید (Ctrl + A) و تعداد آن را در پائین پنجره مشاهده کنید.
روش ۴) با استفاده از افزونه
- افزونه Kutools for Excel: راهنمای استفاده
- افزونه ablebits: راهنمای استفاده
منبع: extendoffice.com
برای مطلع شدن از جدیدترین مطالب سایت، کانال پی‌سی‌ترفند در تلگرام را با آی‌دی pctarfandir@ دنبال کنید:
|
سوالات خود در زمینه ورد و اکسل و ... را میتوانید از طریق ایمیل و تلگرام با ما مطرح کنید. آدرس ایمیل: tarfandha.blog@gmail.com آی‌دی تلگرام: pctarfand_admin@ |
نظرات (۱۸)
-
یوسف حاجی احمدی
سلام
در درگاه مبلغ واریز شد ولی به سایت برگشت نکرد وفایل را نتوانستم دانلود کنم ولی مبلغ پرداخت شد-
پاسخ:
سلامبا تشکر از شمافایل برایتان ایمیل شد.موفق باشید.
-
-
ali
سلام وقت بخیر
من ی جدول دارم که بهصورت سالیانه هر فرد کارکرد ساعتی روزانه داخلش ثبت شده موضوعی وجود داره و روزهای تعطیل رنگ سلول متفاوت هست چطور میشه تمام روزهای تعطیل بایک عدد خاص ضرب کرد؟-
پاسخ:
سلاممی خواهید از فرمول استفاده کنید؟ ملاک شناسایی روز تعطیل فقط رنگ سلول است؟برای حالت بدون فرمول روش گفته شده در سایت زیر احتمالا برایتان مفید باشد.موفق باشید.
-
-
سهیل
سلام فایل رو خریداری کردم و یک سوال اینکه از طریق conditinoal formatting شرط گذاری رنگ بشه اعمال نمیکنه...ولی به صورت دستی رنگ گذاری رو اعمال میکنه...چیکار باید کرد اون شکلی و با شرط رنگ گذاری رنگ هارو بشناسه-
پاسخ:
سلاماین کد فقط برای رنگ معمولی جوابگو هست برای سوال شما از کد گفته شده در سایت زیر استفاده کنید.موفق باشید
-
-
vahid
با سلام بنده این فایل رو خریداری و اجرا کردم ولی کار نکرد لطفا کمکم کنید احتیاج دارم به فرمول دارم ممنونم-
پاسخ:
سلاملطفا مشکل خود را کاملتر توضیح دهید. کار نکرد یعنی عکس العملی نشان نداد؟
-
-
شبنم
ممنون از توضیحات خوبتان -
سید مجید
در درگاه مبلغ واریز شد ولی به سایت برگشت نکرد وفایل را نتوانستم دانلود کنم ولی مبلغ پرداخت شد در صورت درخواست شماره پیگیری ارسال می شود-
پاسخ:
سلاملینک برای شما ایمیل شد.موفق باشید.
-
-
افشین
درود
خیلی به کارم اومد
دستت درد نکنه واقعا عالی بود -
حسین
دوستون داریم یک ستون اعداد و ستون دکناری ان شامل حروف تکراری،ایا تابعی وجود داره که مثلاازستون کناری برای حرف م از ستون اول اعداد روبروی حرف م باهم جمع ببنده-
پاسخ:
سلاممتوجه سوال شما نشدم. لطفا واضح تر توضیح دهید.
-
-
کمال فضلعلی
باسلام و احترام
ضمن تشکر و عرض ارادت، کپی کردم و نتیجه مورد نظرم محاسبه شد. -
saeed
عالی بود کارم راه افتاد -
مجمدرضا
سلام من تو یه فایل اکسل یک سری اعداد دارم که در سلولهای فرد مثل N5,N7,N9 تا N2023 قرار دارند یک فرمول میخواهم که بتونه از سلول N5 تا N2023 همه سلولهای فرد رو با هم جمع بزنه یعنی یک سطر در میان. ممنون میشم اگه مشکلم رو زود حل کنید.-
پاسخ:
-
-
سعید
با سلام.
بسیار عالی بود. کارم راه افتاد استاد. -
ابوالفضل
با سلام
من یه اکسل دارم با مثلا 100 سلول، می خوام جمع 20 تا سلول که رنگ قرمز داره و در کل اکسل پراکنده است را بدست آورم (جمع اعداد داخل سلول ها).لطفا راهنمایی بفرمائید.-
پاسخ:
سلام
آموزش مطلب فوق را انجام دهید.
-
-
رضا
سلام؛با سپاس از اطلاعات مفیدی که در اختیار عموم قرار می دهید.تمامی مراحلی که در این مقاله قید شد را انجام دادم ولی بعد از خارج شدن از برنامه و باز نمودن دوباره آن، فرمول برای شمارش خانه ها رنگی از کار میفته و خطا میده.با اینکه این ماژول همچنان در ا« صفحه موجود است ولی اجرا نمیشه. گویا باید قسمتی برای Run شدن اتوماتیک آن وجود داشته باشه که بی اطلاع هستم. لطفا راهنمایی بفرمایید.من از اکسل 2007 استفاده می کنم.-
پاسخ:
-
-
م.
سلام. عالی بود. ممنون از آموزش خوبتون. -
android
سلامعذر میخوام اگه سوال واضح نبودمنظورم اینه من روشی پیدا کردم که داده های تکراری رو پیدا میکنه با استفادعه از conditional formattingمیخوام ببینم میشه هر داده تکراری یک رنگ خاص بشه یا نه؟؟مثلا اگر چند داده تکراری x داریم رنگ قرمز بشه ، داده های تکراری y رنگ سبز و...آیا امکان اینکار وجود داره ؟؟؟؟ممنون-
پاسخ:
ظاهرا با استفاده از ابزارهای اکسل چنین امکانی وجود ندارد مگر اینکه با ماکرونویسی بتوان انجام داده که من اطلاعی ندارم.
-
-
android
سلامخیلی ممنون از کمک و راهنمائیتون.مشکلم حل شد.برای داده های تکراری اینکه بخواهیم یک رنگ بشن و همچنین تعداد این داده ها هم چطوری مشه؟؟باید ماکرو بنویسم؟؟ممنون-
پاسخ:
سلام
سوالتان را دقیق‌تر مطرح کنید. یعنی همه داده‌های تکراری همرنگ بشوند؟ یک تکرار هر عدد با تکرار عدد دیگر رنگ متفاوتی داشته باشد؟
-
-
امجد
سلام
استاد گرامی انصافاً آموزش هاتون بسیار عالی هستند . سپاسگزارم
