Excel: 根据颜色数格子
一直想用excel做一个日程表记录每周的具体时间安排,举个栗子:
这里我使用颜色区分不同的类别,如果想统计每天/每周我学习英语/数学/经济分别用了多少时间的话,就得手动数非常麻烦!经过一番搜索以后,我们可以用VBA来实现根据颜色数格子的操作!
1. Excel另存为
首先我们要按F12
把我们的表格另存为能用宏的Excel表格:
2. 打开VBA,插入模组
按住Alt
+F11
打开VBA,插入Insert → 模组Module
3. 根据需要复制下列代码进模组(2选1)
代码转载自:https://stackoverflow.com/questions/52211098/counting-merged-cells-as-one-with-the-count-if-function-while-also-using-other-c
3.1 把合并单元格按行数来数
如果你一个合并单元格有5行,会count 5。
Function CountCcolor(range_data As Range, criteria As Range)
Application.Volatile
Dim datax As Range
Dim xcolor As Long
xcolor = criteria.Interior.ColorIndex
For Each datax In range_data
If datax.Interior.ColorIndex = xcolor Then
CountCcolor = CountCcolor + 1
End If
Next datax
End Function
3.2 把合并单元格当成1个整体
如果你一个合并单元格有5行,会count 1。
Function CountCcolor(range_data As Range, criteria As Range) As Long
Application.Volatile
Dim datax As Range
Dim xcolor As Long
Set D1 = CreateObject("scripting.dictionary")
xcolor = criteria.Interior.ColorIndex
For Each datax In range_data
If datax.Interior.ColorIndex = xcolor Then
If datax.MergeCells Then
D1(datax.MergeArea.Address) = datax.MergeArea.Address
Else
CountCcolor = CountCcolor + 1
End If
End If
Next datax
CountCcolor = CountCcolor + D1.Count
End Function
继续
因为我是需要按行数来算时间,所以使用了3.1:
按Ctrl
+s
保存,然后关闭VBA界面,回到Excel表格。
4. 使用方法
4.1 计算当天单个类别时长
可以看到我的日期下面空了3行给我统计数据,在B3单元格内输入=CountCcolor(B6:B53,A3)
就会返回在B6:B53
范围内有多少行与A3
颜色相同。因为我的表格里面一行是半小时,如果我想知道我今天学经济的时长(单位:小时),则还需÷2。
4.2 计算当天多个类别时长
按住单元格左下角下拉则可以计算英语/数学的行数
4.3 计算多天多个类别时长
锁定我们的经济
单元格:把B3
单元格内的A3
改到$A$3
,B4/B5单元格同理。
三格都改完以后,选中这三个单元格,按住右下角往右拉到需要的地方:
4.4 统计多天多类别总时长
只需要一个sum函数,写好这格以后往下拖到需要的地方就行了!
5. 存在的问题
数据无法在标记颜色之后自动更新,只能在有数据输入/更改后更新。简单的来说就是在表格上,任意单元格内输入/更改了文字/数字VBA计算的东西才会刷新。
Reference
- https://stackoverflow.com/questions/52211098/counting-merged-cells-as-one-with-the-count-if-function-while-also-using-other-c
- http://excel.dovov.com/excel-vba-1288.html