在Excel中按其颜色对单元格进行计数 [英] Count cells by their color in Excel

问题描述

这个问题很简单:如何根据给定列中有多少个红色X来更新在Excel中减少的总数.

This question is simple: How can I update a total number count in Excel that decreases based on how many red X's there are in a given column.

这对我来说太复杂了:我制作了宏来根据数字或颜色对单元格进行排序.有五种不同的颜色，每种颜色代表我所在地区的不同县.我每个县都有总数.(例如，黄色= 41，蓝色= 15，红色...)，并且要分别更新每种颜色的总和(通过为每个X减去1)，如果已完成的列中有X.

Where this becomes too complicated for me: I have made macros to sort cells based on numbers or color. There are five different colors, each representing a different county in my area. I have a total for each county. (ex. yellow = 41, blue = 15, red...) and want to update each colors total respectively (by subtracting 1 for each X) IF there is an X in the completed column.

所以问题:

• 如何跟踪一排单元格，尽管它们可能会被排序为三个在给定的时间有不同的方式(是否存在永久性的小区ID?).
• 如何仅针对特定范围的单元格更新数字计数相同的颜色.

随附的是电子表格顶部的图片，以更好地了解其外观:

Attached is a picture of the top of the spreadsheet to better understand how this looks:

非常感谢您的帮助，我不熟悉excel函数可能是导致此问题的根本原因.

any help is greatly appreciated, my unfamiliarity with excel functions is probably the root cause of this problem.

推荐答案

解决方案:如果可以为每个条目添加县名，则可以在不使用VBA 的情况下完成.假设您将它们放入J列(请参见下图中的黄色单元格).

Solution: This can be done without VBA if you can add the county name for each entry. Let's say, you are putting them into column J (see yellow cells in below picture).

然后，要计算总数，只需使用 COUNTIF 函数获取一个县的总行数，然后使用 COUNTIFS 减去那些标记为"X"的行功能.在单元格N2中，我输入:

Then, to count totals, simply use the COUNTIF function to get the total number of rows for a county and substract those who are marked "X" using the COUNTIFS function. In cell N2, I entered:

= COUNTIF(J:J，L2)-COUNTIFS(J:J，L2，H:H，"X")

在L列中现有县名下方添加其他县名，然后将公式从N2复制到下面的单元格中.

Add the other county names below the existing ones in column L and copy the formula from N2 to the cells below.

说明:: COUNTIF 计算符合一个条件的行数.在这里，我们设置县名称(第一个县为L2)，并在J列中查找.接下来，我们需要与县和"X"的完成状态都匹配的行数. COUNTIFS 可以解决这个问题，因为它可以计算符合两个或更多条件的行数.在这种情况下，我们想要给定县(J列)的行数，并且希望它们是L列中值的值(N2为"Bronx"，N3为"Manhattan"等).第二个条件是它们的完成状态(H列)，您希望该状态为X(在公式中指定为"X").然后，从第一个数字中减去第二个.

Explanation: COUNTIF counts the number of rows that match one criterion. Here, we are setting the county name (L2 for the first county) and we are looking for it in column J. Next, we need the number of rows that match both the county and the completion state of "X". COUNTIFS will do the trick as it counts the number of rows that match two or more criteria. In this case, we want the number of rows of a given county (column J) and we want them to be the value of a value in column L ("Bronx" for N2, "Manhattan" for N3 etc.). The second criterion is their completion state (column H) which you want to be X (specified in the formula as "X"). You then substract the second from the first number.

顺便说一句，排序不会影响这些公式，它们将继续起作用.

By the way, sorting does not affect these formulas, they will continue to work.

这篇关于在Excel中按其颜色对单元格进行计数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！