【辅助列解决实际问题_系列8】按颜色求和

cleverzhzhf · 发表于 2014-9-2 22:13

本帖最后由 cleverzhzhf 于 2014-9-2 22:54 编辑

颜色的处理，是Excel的一大弱势，在2003版及之前，基础操作几乎无法处理相关于颜色的事情
在2007版本出来后，总算出来了两个相关的功能：按颜色筛选、按颜色排序
可是这两项都是手工处理，没法像函数那样实现自动化，那么这里就来用函数来解决这个问题：

三国公司8月份上旬各员工的销售业绩，总经理汉献帝审核的时候，随后标注了几个颜色，然后要求对这些标注颜色的数据求和
这也难不住罗贯中的解答呀~~
宏表函数，对于普通用户来讲，就是一个神一样存在的东东，这里就要为我们所用，来玩一玩GET.CELL
选中D5单元格，然后定义名称color：

=GET.CELL(63,Sheet1!C5)+NOW()^0-1

复制代码

公式：

D5：=color
G5：=SUMIF(D:D,color,C:C)

复制代码

按颜色求和.rar (8.87 KB, 下载次数: 388)

（文件使用了宏表函数，需要启用宏方可正确计算。宏表函数基础知识的参考学习：http://club.excelhome.net/thread-1043482-1-1.html）

cleverzhzhf · 发表于 2014-9-2 22:14

本帖最后由 cleverzhzhf 于 2014-9-3 09:02 编辑

第一步：=GET.CELL(63,Sheet1!C5)+NOW()^0-1
1、GET.CELL(63,Sheet1!C5)
务必是选择D5单元格时候定义。这是这里面最关键的内核，GET.CELL函数，是取得单元格相关格式、内容的，第一个参数是从1到66的数字，每个数字代表取得不同的内容，63代表相应单元格的背景颜色，参考：
[分享] 宏表函数Get.Cell那些事儿
http://club.excelhome.net/thread-1028374-1-1.html
GET.CELL只能在定义名称中使用，所以务必要注意单元格位置的相对位置
(GET.CELL不能得到条件格式设置的颜色，如果要是有那种情况，那就根据“条件格式”的条件来求和即可)
2、GET.CELL(63,Sheet1!C5)+NOW()^0-1
  1）由于GET.CELL不能根据表格的变化，进行自动重算，必须双击相应的单元格，才可能引起相应的重算，以达到最新的结果。
  2）函数技巧：NOW()函数，是每时每刻都在变化的，由它的变化，强行引起GET.CELL同时进行重算，以达到及时更新的目标。以后只需按一下F9，所有格就都是最新结果了。
  3）任何非0数字的0次方，结果都是1，所以NOW()^0-1始终结果为0，并不影响计算结果。其他常用的还有&T(NOW())，不过这样就把数字变成了文本格式。虽不影响计算，但还是根据个人喜好选择相应的方法。

第二步：
D5：=color
就是得到相应格的背景颜色值，务必注意是写在D5格。
G5：=SUMIF(D:D,color,C:C)
其中的color取得的是F5格的背景色的数值，然后根据此数值对C列的销售量进行相应的求和。回归到Sumif的基础使用方式。

（附：如果想在H列得到平均值，此时需要再定义一个名称，使目标单元格和当前单元格相差“两格”，以达到相应格的计算目标。）

衣袂飘飘 · 发表于 2014-9-2 23:00

哈哈，学习下哦。

sw=andy · 发表于 2014-9-3 08:04

Mark 按一下

cleverzhzhf · 发表于 2014-9-5 13:45

本帖最后由 cleverzhzhf 于 2014-9-5 16:48 编辑

补充：
对于Get.Cell返回的单元格背景颜色数值，只能有56种，即color的结果只能是数字1-56，可以参考2003版的相关颜色选项卡：
56颜色1.png

对于RGB颜色，一些相近的颜色，使用Get.Cell返回的是相同结果，可以参考以下附件：
getcell的颜色值测试.zip (938.15 KB, 下载次数: 123)

2014-9-5 13:44 上传
点击文件名下载附件

相关测试代码：

Sub yanse()
Cells.Clear
Dim arr(1 To 32768, 3)
For i = 0 To 255 Step 8
For j = 0 To 255 Step 8
For k = 0 To 255 Step 8
m = m + 1
Cells(m, 1).Interior.Color = RGB(i, j, k)
arr(m, 1) = i
arr(m, 2) = j
arr(m, 3) = k
Next k
Next j
Next i
''''''''''''color是定义名称，color：=GET.CELL(63,Sheet1!A1)
For n = 1 To 32768
arr(n, 0) = "=color"
Next
Range("B1:E32768") = arr
End Sub

复制代码

qwedef · 发表于 2014-9-26 14:45

cleverzhzhf 发表于 2014-9-5 13:45
补充：
对于Get.Cell返回的单元格背景颜色数值，只能有56种，即color的结果只能是数字1-56，可以参考2003版 ...

非常感谢大师仔细解答，谢谢！

zgjxxx · 发表于 2017-5-16 12:30

大师，能不能不用辅助列解决这个问题？

cleverzhzhf · 发表于 2017-5-18 15:33

zgjxxx 发表于 2017-5-16 12:30
大师，能不能不用辅助列解决这个问题？

借用这种方案，必须要加辅助列，因为后面的sumifs需要使用单元格区域

思念…… · 发表于 2017-8-21 20:47

如果是这样的表，请问要怎么按照颜色加总呢？

蓝星880428 · 发表于 2020-7-1 17:16

如果更改颜色了，要怎么才能让公式自动重算提取颜色代码呢，现在得要点击一下公式用来重新提取代码

		自动登录	找回密码
密码			免费注册

[原创] 【辅助列解决实际问题_系列8】按颜色求和

评分