جستجو

ترفندهای کامپیوتری

حضرت علی (ع): «زَکوةُ العِلمِ نَشرُهُ؛ زکات علم نشر آن است».
وبلاگ "ترفندها" (سایت پی‌سی‌ترفند) برای عمل به این حدیث شریف و با هدف ارتقاء دانش و مهارت‌های مرتبط با رایانه در ۳۰ مهرماه ۱۳۹۱ ایجاد شده است. به امید آنکه مفید واقع شود.
🔵 کانال‌های پی‌سی‌ترفند در تلگرام و سروش:
telegram.me/pctarfand_ir
sapp.ir/pctarfand_ir

خلاصه آمار

تبليغات

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

اکسل ماکرونویسی

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

روش ۱) با استفاده از فرمول - ساخت تابع جدید بوسیله ماکرونویسی

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

فرض کنید در جدولی مشابه شکل زیر لیست سفارش‌های یک شرکت را دارید که در آن سلول‌ها در ستون 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) و تعداد آن را در پائین پنجره مشاهده کنید.


روش ۴) با استفاده از افزونه

منبع: extendoffice.com

 

عضویت در خبرنامه:

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

          

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

آدرس ایمیل: tarfandha.blog@gmail.com

آی‌دی سروش: pctarfand_info@

گروه سروش:

نظرات (۱۱)

  1. دوستون داریم یک ستون اعداد و ستون دکناری ان شامل حروف تکراری،ایا تابعی وجود داره که مثلاازستون کناری برای حرف م از ستون اول اعداد روبروی حرف م باهم جمع ببنده
    • پاسخ:

      سلام
      متوجه سوال شما نشدم. لطفا واضح تر توضیح دهید.
  2. باسلام و احترام
    ضمن تشکر و عرض ارادت، کپی کردم و نتیجه مورد نظرم محاسبه شد.
  3. سلام من تو یه فایل اکسل یک سری اعداد دارم که در سلولهای فرد مثل N5,N7,N9 تا N2023 قرار دارند یک فرمول میخواهم که بتونه از سلول N5 تا N2023 همه سلولهای فرد رو با هم جمع بزنه یعنی یک سطر در میان. ممنون میشم اگه مشکلم رو زود حل کنید.
    • پاسخ:

      سلام
      از فرمول گفته شده در این سایت استفاده کنید.
      موفق باشید.
  4. با سلام
    من یه اکسل دارم با مثلا 100 سلول، می خوام جمع 20 تا سلول که رنگ قرمز داره و در کل اکسل پراکنده است را بدست آورم (جمع اعداد داخل سلول ها).لطفا راهنمایی بفرمائید.
    • پاسخ:

      سلام
      آموزش مطلب فوق را انجام دهید.
  5. سلام؛
    با سپاس از اطلاعات مفیدی که در اختیار عموم قرار می دهید.
    تمامی مراحلی که در این مقاله قید شد را انجام دادم ولی بعد از خارج شدن از برنامه و باز نمودن دوباره آن، فرمول برای شمارش خانه ها رنگی از کار میفته و خطا میده.
    با اینکه این ماژول همچنان در ا« صفحه موجود است ولی اجرا نمیشه. گویا باید قسمتی برای Run شدن اتوماتیک آن وجود داشته باشه که بی اطلاع هستم. لطفا راهنمایی بفرمایید.
    من از اکسل 2007 استفاده می کنم.
    • پاسخ:

      سلام
      اگر منظورتان قابل استفاده بودن در همه فایل‌ها است به این مطلب مراجعه کنید.
  6. سلام
    عذر میخوام اگه سوال واضح نبود
    منظورم اینه من روشی پیدا کردم که داده های تکراری رو پیدا میکنه با استفادعه از conditional formatting
    میخوام ببینم میشه هر داده تکراری یک رنگ خاص بشه یا نه؟؟
    مثلا اگر چند داده تکراری x  داریم رنگ قرمز بشه ، داده های تکراری  y رنگ سبز و...
    آیا امکان اینکار وجود داره ؟؟؟؟
    ممنون
    • پاسخ:

      ظاهرا با استفاده از ابزارهای اکسل چنین امکانی وجود ندارد مگر اینکه با ماکرونویسی بتوان انجام داده که من اطلاعی ندارم.
  7. سلام
    خیلی ممنون از کمک و راهنمائیتون.مشکلم حل شد.
    برای داده های تکراری اینکه بخواهیم یک رنگ بشن و همچنین تعداد این داده ها هم چطوری مشه؟؟
    باید ماکرو بنویسم؟؟
    ممنون
    • پاسخ:

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

ارسال نظر

قبل از ارسال نظر به نکات زیر توجه کنید:

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

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

۳- تنها به سوالاتی پاسخ داده می‌شود که از نحوه حل آن‌ها اطلاع داشته باشم.

۴- نظراتی با مضمون زیر، صرفا تایید و نمایش داده می‌شود و به آن‌ها پاسخی داده نمی‌شود.

الف) در مورد پاسخ سوال شما اطلاعی نداشته باشم،

ب) پاسخ سوال شما در مطلب فوق وجود داشته باشد.

ارسال نظر آزاد است، اما اگر قبلا در بیان ثبت نام کرده اید می توانید ابتدا وارد شوید.
شما میتوانید از این تگهای html استفاده کنید:
<b> یا <strong>، <em> یا <i>، <u>، <strike> یا <s>، <sup>، <sub>، <blockquote>، <code>، <pre>، <hr>، <br>، <p>، <a href="" title="">، <span style="">، <div align="">
تجدید کد امنیتی