کوکی ها به ما کمک خدمات ما ارائه کرده است. با استفاده از خدمات ما، شما را به استفاده ما از کوکی ها است.
نکته: زبانهای دیگر گوگل ترجمه شده اند. شما می توانید از English نسخه این لینک
ورود
x
or
x
x
ثبت نام
x

or

چگونه تعداد سلول ها را براساس رنگ پس زمینه در اکسل جمع کنیم؟

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

تعداد کل و مجموع سلول بر اساس رنگ پر رنگ توسط فیلتر و SUBTOTAL

تعداد و مجموع سلول ها بر اساس رنگ پر رنگ با عملکرد GET.CELL

تعداد و مجموع سلول بر اساس رنگ پر رنگ با کاربر تعریف شده عملکرد

تعداد کل و مجموع سلول بر اساس رنگ پر رنگ خاص (یا رنگ قالب بندی شرطی) با Kutools برای اکسل

یک کلیک برای شمارش، جمع و متوسط ​​سلول های رنگی در اکسل

با عالی شمارش توسط رنگ ویژگی Kutools برای اکسل، شما می توانید به سرعت تعداد، جمع و متوسط ​​سلول ها را با رنگ پر رنگ مشخص شده یا رنگ فونت با تنها یک کلیک در اکسل. علاوه بر این، این ویژگی همچنین مقادیر حداکثر و حداقل سلول ها را با رنگ پر رنگ یا رنگ فونت می یابد. ویژگی رایگان رایگان 60 روز دادگاه!
شمارش تعداد تبلیغ توسط رنگ 2


سلول های رنگی و شمارش شده توسط فیلتر و SUBTOTAL

فرض کنید ما یک جدول فروش میوه داریم که در تصویر زیر نشان داده شده است، و ما سلول های رنگی را در ستون Amount شمارش کنیم یا جمع کنیم. در این وضعیت، می توانیم ستون Amount را با رنگ فیلتر اندازه گیری کنیم و سپس به راحتی در Excel اعداد را به سلول های رنگی فیلتر شده با عملکرد SUBTOTAL بسپاریم.

1. سلول های خالی را برای ورود به عملکرد SUBTOTAL انتخاب کنید.

  1. برای شمارش تمام سلول ها با همان رنگ پس زمینه، لطفا فرمول را وارد کنید = SUBTOTAL (102، E2: E20);
  2. برای جمع کردن تمام سلول ها با همان رنگ پس زمینه، لطفا فرمول را وارد کنید = SUBTOTAL (109، E2: E20);


توجه داشته باشید: در هر دو فرمول، E2: E20 ستون محدوده حاوی سلول های رنگی است و شما می توانید آنها را به همان صورت که نیاز دارید تغییر دهید.

2. هدر جدول را انتخاب کنید و روی کلیک کنید داده ها > فیلتر. تصویر را ببینید:

3. روی آیکون Filter کلیک کنید در سلول سرصفحه ستون مقدار و کلیک کنید فیلتر بر اساس رنگ و رنگ مشخص شده شما به صورت متوالی به حساب می آید. تصویر را مشاهده کنید:

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

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


تعداد سلول های رنگی با استفاده از عملکرد GET.CELL

در این روش، ما یک محدوده نامی با عملکرد GET.CELL ایجاد می کنیم، کد رنگی سلول ها را می گیریم و سپس به راحتی در Excel اعداد را با استفاده از کد رنگ به دست می آوریم. لطفا به شرح زیر عمل کنید:

1است. کلیک کنید فرمول > تعریف نام. تصویر را ببینید:

2. در گفتگوی نام جدید، لطفا به صورت زیر تصویر زیر را نشان دهید:
(1) یک نام را در جعبه Name تایپ کنید
(2) فرمول را وارد کنید = GET.CELL (38، Sheet4! $ E2) در اشاره به جعبه (توجه داشته باشید: در فرمول 38 به معنی بازگشت کد سلولی، و Sheet4! $ E2 اولین سلول ستون Amount به جز هدر ستون است که شما باید براساس اطلاعات جدول خود تغییر دهید.)
(3) کلیک کنید OK را فشار دهید.

3. حالا یک ستون Color جدید درست به جدول اصلی اضافه کنید. بعد فرمول را تایپ کنید = NumColor ، و دسته "خودکار تکمیل" را بکشید تا فرمول را به سلول های دیگر ستون رنگ اعمال کنید. تصویر را مشاهده کنید:
توجه داشته باشید: در فرمول NumColor محدوده نامی است که در اولین مراحل 2 مشخص شده است. شما باید آن را به نام مشخص شده تنظیم کنید.

حالا کد رنگی هر سلول در ستون Amount در Column Column باز می شود. تصویر را مشاهده کنید:

4. کپی و رنگ پر رنگ را در یک محدوده خالی در صفحه فعال فعال کنید و فرمول هایی را که در کنار آنها قرار دارد را تایپ کنید همانطور که در تصویر زیر نشان داده شده است:
A. برای شمارش سلول ها با رنگ، لطفا فرمول را وارد کنید = COUNTIF ($ F $ 2: $ F $ 20، NumColor);
B. برای جمع کردن سلول ها با رنگ، لطفا فرمول را وارد کنید = SUMIF ($ F $ 2: $ F $ 20، NumColor، $ E $ 2: $ E $ 20).

توجه داشته باشید: در هر دو فرمول $ F $ 2: $ F $ 20 ستون رنگ است NumColor دامنه مشخص شده نامیده می شود $ E $ 2: $ E $ 20 ستون مقدار است و شما می توانید آنها را به همان صورت که نیاز دارید تغییر دهید.

حالا سلول های ستون Sum را می بینید و با رنگ های پر رنگ آنها جمع می شوند.


تعداد و مجموع سلول بر اساس رنگ پر رنگ با کاربر تعریف شده عملکرد


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

1. پایین نگه دارید ALT + F11 کلید، و آن باز می شود مایکروسافت ویژوال بیسیک برای برنامه پنجره.

2است. کلیک کنید قرار دادن > ماژول ها، و کد زیر را در پنجره ماژول قرار دهید.

VBA: شمار و مجموع سلول ها بر اساس رنگ پس زمینه:

Function ColorFunction(rColor As Range, rRange As Range, Optional SUM As Boolean)
Dim rCell As Range
Dim lCol As Long
Dim vResult
lCol = rColor.Interior.ColorIndex
If SUM = True Then
For Each rCell In rRange
If rCell.Interior.ColorIndex = lCol Then
vResult = WorksheetFunction.SUM(rCell, vResult)
End If
Next rCell
Else
For Each rCell In rRange
If rCell.Interior.ColorIndex = lCol Then
vResult = 1 + vResult
End If
Next rCell
End If
ColorFunction = vResult
End Function

3. سپس کد را ذخیره کنید و فرمول زیر را اعمال کنید:
A. تعداد سلول های رنگی: = عملکرد رنگ (A، B: C، FALSE)
B. مجموع سلول های رنگی: = عملکرد رنگ (A، B: C، TRUE)

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

4. به عنوان مثال، تصویر زیر را وارد کنید فرمول را وارد کنید= عملکرد رنگ (A1، A1: D11، FALSE) برای شمارش سلول های زرد و از فرمول استفاده کنید = عملکرد رنگ (A1، A1: D11، TRUE) برای جمع کردن سلول های زرد تصویر را ببینید:

5. اگر میخواهید دیگر سلولهای رنگی را شمارش کنید و آنرا بپردازید، لطفا مرحله 4 را تکرار کنید. سپس نتایج زیر را دریافت خواهید کرد:


تعداد کل و مجموع سلول ها بر اساس رنگ پر رنگ با Kutools برای اکسل

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

1. دامنه ای را که می خواهید استفاده کنید را انتخاب کنید و روی کلیک کنید Kutools Plus > شمارش توسط رنگ، تصویر را ببینید

2. و در شمارش توسط رنگ کادر محاوره ای، لطفا به صورت زیر تصویر زیر را نشان دهید:
(1) را انتخاب کنید قالب بندی استاندارد از روش رنگ لیست کشویی
(2) را انتخاب کنید زمینه از نوع حساب لیست کشویی
(3) روی دکمه گزارش تولید کلیک کنید.

Kutools برای اکسل - شامل بیش از 300 ابزار مفید اکسل. نسخه کامل آزمایشی رایگان 60 روز، بدون کارت اعتباری مورد نیاز است! اکنون آن را بگیرید!

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

حالا شما یک کتاب جدید با آمار دریافت خواهید کرد. تصویر را مشاهده کنید:


مقاله مرتبط:

چگونه می توان تعداد / مجموع سلول را بر اساس رنگ فونت در اکسل؟


نسخه ی نمایشی: تعداد و مجموع سلول ها بر اساس پس زمینه، رنگ قالب بندی شرطی:

در این ویدئو، کوتولس برگه و Kutools Plus برگه اضافه شده توسط Kutools برای اکسل. اگر به آن نیاز دارید، لطفا کلیک کنید اینجا را انتخاب کنید. برای یک محاکمه رایگان 60 روز بدون محدودیت!



ابزارهای توصیه شده برای بهره وری

دفتر تب

gold star1 زبانه های مفید برای اکسل و دیگر نرم افزار Office، درست مانند Chrome، Firefox و اینترنت اکسپلورر جدید.

Kutools برای اکسل

gold star1 حیرت آور! بهره وری خود را در دقیقه 5 افزایش دهید. به مهارت های ویژه نیاز نداشته باشید، صرفه جویی در هر ساعت دو ساعت!

gold star1 ویژگی های جدید 300 برای اکسل، اکسل بسیار آسان و قدرتمند:

  • ادغام سلول / ردیف / ستون بدون از دست دادن داده ها.
  • ترکیب و ادغام چندین ورق و کارنامه.
  • مقایسه مقادیر، روندهای چندگانه کپی، تبدیل متن به تاریخ، تبدیل واحد و ارز.
  • شمارش توسط رنگ ها، کلیدهای پیمایش، مرتب سازی پیشرفته و سوپر فیلتر،
  • بیشتر انتخاب / قرار دادن / حذف / متن / فرمت / لینک / نظر / کتاب کار / ابزار برگه ها ...

شات Screen Kutools برای اکسل

Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    Mori · 3 days ago
    I copied and paste but calculation result is "0" why?? I am using Office 2016.
  • To post as a guest, your comment is unpublished.
    Troy Havard · 1 months ago
    I've copied and pasted as stated and have used this formula for over a year but recently saved the workbook to a new name and now the function doesn't work! I can't figure out what the deal is and I'm losing hair and sleep over it! Loading the original workbook, the formula works like a charm but going back to the new one, it doesn't! I've loaded VBA and tried re-creating the function but it doesn't work. Using Office 2019 - any help appreciated.
    • To post as a guest, your comment is unpublished.
      Jku · 14 days ago
      #NAME clearly indicates some keyword is not used in 2019, say, Interior.ColorIndex. Try to figure out changing the code from minimum lines to the full by adding one by one or search for the keywords in Excel/VBA in 2019
    • To post as a guest, your comment is unpublished.
      Troy Havard · 1 months ago
      I should probably have also noted that I get a #NAME? error in the cell in which I try to use the function.
  • To post as a guest, your comment is unpublished.
    Karolin · 2 months ago
    works fine, except it does not update the value in the totals when i change a cell to a diffrent colour. i have to click in the totals cell again to trigger a recount. Any Ideas how to fix this?
    • To post as a guest, your comment is unpublished.
      Arnier Hamz · 1 months ago
      Same here, i tried Ctrl+Alt+F9 or Ctrl+Shift+Alt+F9 to recalculate all formula and it works
  • To post as a guest, your comment is unpublished.
    Aaron · 3 months ago
    This doesn't work at all. It's total BS.
  • To post as a guest, your comment is unpublished.
    Schmingy · 5 months ago
    I was experiencing issues when attempting to use this function as-is to count/sum the lighter (pastel?) colors, at least in Excel 2016. Some debugging led me to realize that the range of "ColorIndex" values is quite limited, and the function above fails to distinguish between light yellow and light orange, reading both as ColorIndex = 19.

    Using the "Color" attribute, instead (as in "lCol = rColor.Interior.Color" and the two instances of "If rCell.Interior.Color = lCol Then", per the sample code above), resolved the issue.