如何根据Excel中的条件添加组？ [英] How to add groups based on conditions in Excel?

问题描述

我有一大堆数据，我需要计算表中特定列的组合数。以下是表格示例：

我需要对每个日期组（A列）总结列H，以便它将在组的最后一行保持显示。换句话说，在第一组我们可以看到这里3/21/13，我们将总结38 + 2 + 21，并显示61在单元格H4930的右边。这将在整个数据中重复，以便显示组中最低项目上每个组的H列条目的总和。

我假设在这里需要一些VBA，但我不知道如何攻击这个。这样做如何？

解决方案

我在您的Sum列之前添加了2个工作栏。

添加： = NOT（ISNA（MATCH（C2，$ Q $ 2：$ Q $ 3,0）））到列N，其中Q2： Q3是您要检查的数字。可以隐藏。

添加： = IF（N2，COUNTIFS（A3：$ A $ 7416，A2，N3：$ N $ 7416，TRUE） = 0，）到列O.可以隐藏。

添加： = IF ，O2），SUMIFS（H2：$ H $ 2，A2：$ A $ 2，A2，N2：$ N $ 2，TRUE），）到列P.这是你的总和。

如果您实际上不需要显示所有的数据，那么数据透视表将会更加清晰。您可以轻松地根据数字进行过滤。

编辑：编辑以检查C列中的特定代码。
edit2：每个评论完全改变。要求与最后一个突出显示的行对齐是更复杂的。

I have a large set of data where I need to calculate the sum of groups for specific columns in a table. Here is an example of the table:

I need to sum up column H for each date group (column A) such that it will keep display this sum on the last row of the group. In other words, on the first group that we can visibly see here for 3/21/13, we would sum up 38 + 2 + 21 and display 61 to the right of cell H4930. This would be repeated throughout the data so as to display the sum of each group's H column entries at the lowest item in the group as aforenoted.

I am assuming that some VBA will be required here, but I am not sure how to attack this. How would this be done?

解决方案

I added 2 working columns before your Sum column.

Add: =NOT(ISNA(MATCH(C2,$Q$2:$Q$3,0))) to column N, where the Q2:Q3 are the numbers you wanted to check. Can be hidden.

Add: =IF(N2,COUNTIFS(A3:$A$7416,A2,N3:$N$7416,TRUE)=0,"") to column O. Can be hidden.

Add: =IF(AND(N2,O2),SUMIFS(H2:$H$2,A2:$A$2,A2,N2:$N$2,TRUE),"") to column P. This is your sum.

If you don't actually need to display all the data like this then a pivot table would be a lot clearer. You can filter based on numbers and so on easily.

edit: edited to check for specific codes in column C, per comment. edit2: completely changed per comment. Requiring it to be inline with the last highlighted row is more complex.

这篇关于如何根据Excel中的条件添加组？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！