2022-11-11 22:16来源:m.sf1369.com作者:宇宇
excel超实用的小方法~
在EXCEL中实现按单元格背景颜色求和或计数
我们在EXCEL工作表中,如果要对多个单元格的数值进行求和,一般都会用到SUM函数,SUM函数的计算结果可以根据各个对象单元格数值的变化而发生变化,满足了大多数EXCLE用户的需求。但是,在SUM函数中,当被求和单元格的位置发生变化时,SUM函数是无法进行自动求和的,这就需要用到SUMCOLOR函数,它能根据单元格的背景颜色进行求和,无论是单元格的位置发生了变化还是单元格数值发生变化,只要被求和单元格的背景颜色一致,SUMCOLOR函数都可以实现对她们的求和。如果要对相同颜色单元格进行计数,也可以运用COUNTCOLOR函数实现。
步骤:
1.打开WPS ,选择要求和的单行数据。在框选的时候,记得在最后面留下一个空格。
2. 选择好后,找到工具栏中的“自动求和”按钮。
3.点击后,即可在选中行当中的最后一个空格中自动出现单行的和;
4.如果要一次性多求几行的和,那么可以同时框选多行,后面同样留下一个空白格;
5. .然后再次单击工具栏的“自动求和”,即可把每一行按颜色求和。
直接用函数对excel表中数据按颜色求和是肯定不行的,有两种方法解决:
1,用VBA自设一函数进行求和;
2,加辅助列用不同数字区分不同颜色行。
一、查找法
首先我们要选择想要统计的数据区域,然后按Ctrl+F调出查找的窗口,然后点击选项,点击格式选择从单元格选取格式,鼠标就会变成一个小吸管的样子,我们直接吸取想要统计颜色的单元格,然后点击全部查找,紧接着按Ctrl+a选择查找到的全部数据,在工作表右下角的状态栏就会显示该颜色的个数及求和
二、宏表函数法
有可能会问到宏表函数是什么,宏表函数可以说是vba的前身,但是随着vba的出现,宏表函数已经被逐渐的取代,但是现在仍然可以使用,只不过只能通过定义名称来使用
在这里我们需要用到的宏表函数是GET.CELL,他的参数一共有两个
第一参数:type_num,单元格中的信息,会根据输入的代码返回不同的单元的信息
第二参数: Reference,单元格范围
我们想要获取单元格的单色信息,就需要将GET.CELL函数的第一参数设置为63.因为代码63返回的是单元色的颜色信息
宏表函数的使用必须先对其定义名称,首选我们点击公式,点击定义名称,将名称设置为颜色,然后输入公式=GET.CELL(63,Sheet1!B2),在这里Sheet1是表格的名称,B2是统计区域的第一个单元格,设置完后点击确定
然后我们直接输入等于颜色,然后向下填充数据,就会根据不同的颜色返回不同的结果,黄色的结果为6.绿色的结果为10
最后我们使用sumif函数和countif函数进行求与计数即可
求和公式:=SUMIF(C2:C18,6,B2:B18)
计数公式:=COUNTIF(C2:C18,6)
三、使用vba自定义函数
1.根据颜色求和代码
Function SumColor(col As Range, sumrange As Range) As Long
Dim icell As Range
Application.Volatile
For Each icell In sumrange
If icell.Interior.ColorIndex = col.Interior.ColorIndex Then
SumColor = Application.Sum(icell) + SumColor
End If
Next icell
End Function
2.根据颜色计数代码
Function CountColor(ary1 As Range, ary2 As Range)
Application.Volatile
For Each i In ary2
If i.Interior.ColorIndex = ary1.Interior.ColorIndex Then
CountColor = CountColor + 1
End If
Next
End Function
想要使用这两个代码,我们就必须先将其粘贴进vba中,首先我们点击开发工具,选择visual basic,进入vba的编辑界面,然后点击插入,选择模块,将代码粘贴进去即可
在这里我们自定了了两个函数,根据颜色求和的函数名称是SumColor,根据颜色计数的函数名称是CountColor
这两个函数的参数都是一样的
第一参数:统计颜色的单元格
第二参数:要统计的数据区域
使用方法如下动图
首先,我们可以单击工具栏中的插入函数,也可以直接在单元格中输入“=SUB”,选择下拉菜单中的SUBTOTAL。
然后在下拉菜单中选择109-SUM。
然后单击逗号,选择我们需要计算的所有数据,包含所有黄色区域的数据。
我们看到合计里就显示出总额了,然后单击右键,选择筛选。
我们看到月份和销售金额出现了两个倒三角的下拉菜单,单击下拉菜单,选择颜色筛选,单击黄色方格。
我们看到单元格里就剩所有的黄色的单元格了,下面的合计也是这几个月的销售金额总和。
这样,我们在WPS表格中按照颜色区域来分类求和的方法就介绍完了,是不是很简单呢,赶快试一下吧~
方法步骤:
一、双击打开需要求和的word表格。
二、把光标放在需要得出求和结果的单元格里。
三、依次单击“表格工具”菜单,单击选择“公式”命令。
四、选择公式命令后,会弹出一个公式对话框。
五、在公式框输入要计算的单元格区域,例如需要计算的数据在(C2:E2)里,所在输入“=SUM(C2:E2)”,单击确定。
六、单击确定后, 可看到光标所在的单元格里得出了计算结果。
七、同样方法,计算其它单元格只需要输入相应的计算公式,单击确定即可。
1、单击首个要输入公式的单元格,点击“公式-定义名称”。
2、新建名称中,输入名称“color”,引用位置输入公式“=GetCell(63,C5)+Now()^0-1”。
3、D5单元格输入公式“=color”,并向下复制,公式作用是得到C列单元格的背景颜色值。
4、G5单元格输入公式“=SUMIF(D:D,color,C:C)”,其中的color取得的是F5单元格背景色的数值,然后根据此数值对C列的销售量进行相应的求和。
5、回到表格,就能看到G5单元格统计出了结果数据。
利用宏进行操作;例如 Sub test() Dim R As Integer Dim I As Integer Dim B As Integer B = 0 R = 0 For I = 1 To 9 If Cells(I, 1).Font.Color = vbRed Then R = R + 1 If Cells(I, 1).Font.Color = vbBlack Then B = B + 1 Next I MsgBox "red=" & R & " Black=" & B End Sub
1、现在要求黄色背景中数据的总和。
2、首先,按快捷键Ctrl+F,弹出【查找与替换】对话框,下拉【格式】菜单,【从单元格选择格式】
3、随便点选一个黄色背景的单元格,然后点击【查找全部】,查找到所有黄色背景的数据。
4、框选所有数据,然后点击【关闭】。
5、点击【公式】--【定义名称】,给选择的数据定义一个名称;
6、在弹出的【新建名称】对话框中,把名称改成【黄色背景】
7、点击【公式】--【自动求和】--【求和】;
8、在公式的编辑栏中输入【=SUM(黄色背景)】,就可以得到黄色背景中的求和。