加载中…
Excel中如何统计条件格式下带颜色表格的数量？
(2010-08-24 16:41:52)
标签：
杂谈 |
分类： MSN搬家 |
Function SumColor(col As Range, sumrange As Range) As Integer
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
=sumcolor(D$1,$A$1:$A$10)
D$1为带指定颜色的格子
$A$1:$A$10为混了颜色的区域
但是，如果你可以使用条件格式显示不同的颜色，那就可以使用条件求和计算满足条件的格子
Excel中自定义函数实例剖析
2004-09-06 14:08作者：罗庆丰 原创出处：天极网责任编辑：Shiny
三、自定义函数实例剖析
下面我们通过两个典型实例，学习自定义函数使用的全过程。这里实际上假设读者朋友都有一定的VBA基础。
假如你完全没有VBA基础也不要紧，当学习完实例后，若觉得自定义函数在自己以后的工作中可能用到，那么再去补充相应的VBA基础也不迟。
(一) 计算个人调节税的自定义函数
任务
假设个人调节税的收缴标准是：工资小于等于800元的免征调节税，工资800元以上至1500元的超过部分按5％的税率征收，1500元以上至2000元的超过部分按8％的税率征收，高于2000元的超过部分按20％的税率征收。
分析
假设Sheet1工作表的A、B、C、D列中分别存放“姓名”、“总工资”、“调节税”、“税后工资”字段数据，如图1所示。
http://www.yesky.com/imagesnew/software/0409/06/yl040906-oa1-1.gif
图 1
平时使用较多的方法是借助嵌套使用IF函数计算，比如在C2单元格输入公式“=IF(B2<=800,0,IF(B2& lt;=1500,(B2-800)*0.05,IF(B2<=2000,700*0.05+ (B2-1500)*0.08,700*0.05+500*0.08+(B2-2000)*0.2)))”，然后通过填充柄复制公式到C列的其余单元格。
既然公式能够解决问题，为什么还要使用自定义函数的方法呢？
正如前面提到的两个方面的原因：一是公式看起来太繁琐，不便于理解和管理；二是公式的处理能力在面对稍微复杂一些的问题时便失去效用，比如假设调节税的税率标准会根据年龄的不同而改变，那么公式可能就无能为力了。
使用自定义函数
下面就通过此例介绍使用自定义函数的全过程，即使是初学Excel的朋友，也会感觉其操作实际上是非常简单的。
1. 为了便于测试自定义函数的计算效果，可以先把上面采用公式计算的结果删去。然后选择菜单“工具→宏→Visual Basic编辑器”命令(或按下键盘Alt+F11组合键)，打开Visual Basic窗口，我们将在这里自定义函数。
2. 进入Visual Basic窗口后，选择菜单“插入→模块”命令，于是得到“模块1”，在其中输入如下自定义函数的代码(图2)：
Function TAX(salary)
Const r1 As Double = 0.05
Const r2 As Double = 0.08
Const r3 As Double = 0.2
Select Case salary
Case Is <= 800
TAX = 0
Case Is <= 1500
TAX = (salary - 800) * r1
Case Is <= 2000
TAX = (1500 - 800) * r1 + (salary - 1500) * r2
Case Is > 2000
TAX = (1500 - 800) * r1 + (2000 - 1500) * r2 + (salary - 2000) * r3
End Select
End Function
http://www.yesky.com/imagesnew/software/0409/06/yl040906-oa1-2.jpg
图 2
3. 函数自定义完成后，选择菜单“文件→关闭并返回到Microsoft Excel”命令，返回到Excel工作表窗口，在C2单元格中输入公式“=TAX(B2)”回车后就计算出了第一个员工应付的个人调节税，然后用公式填充柄复制公式到其它后面的单元格，这样就利用自定义函数完成了个人调节税的计算(图3)。
http://www.yesky.com/imagesnew/software/0409/06/yl040906-oa1-3.gif
图 3
4. 从自定义函数的代码中可以看出，用这种方式，自定义函数的功能非常易于理解，同时如果税率改变，相应地变化r1、r2、r3的值即可。
通常，自定义的函数只能在当前工作薄使用，如果该函数需要在其它工作薄中使用，则选择菜单“文件→另存为”命令，打开“另存为”对话框，选择保存类型为“Mircosoft Excel加载宏”，然后输入一个文件名，如“TAX”单击“确定”后文件就被保存为加载宏(图4)。然后选择菜单“工具→加载宏”命令，打开“加载宏” 对话框，勾选“可用加载宏”列表框中的“Tax”复选框即可，单击“确定”按钮后(图5)，就可以在本机上的所有工作薄中使用该自定义函数了。
http://www.yesky.com/imagesnew/software/0409/06/yl040906-oa1-4.jpg
图 4
http://www.yesky.com/imagesnew/software/0409/06/yl040906-oa1-5.jpg
图 5
如果想要在其它机器上使用该自定义函数，只要把上面的加载宏文件复制到其它电脑上加载宏的默认保存位置即可。
说明：Windows XP系统下加载宏文件的默认保存位置为：C:\Documents and Settings\zunyue(用户帐户)\Application Data\Microsoft\AddIns文件夹。
EXCEL中单元格的颜色统计
其实，这个问题要用到一个特别的函数：get.cell
解决这个问题的思路是，你首先得让系统知道你每种颜色的代码是多少。得到这个代码就用到这个函数。设你要统计的单元格在A列，B列为空列，操作如下：
1，点插入，名称，定义，弹出的窗口第一行名称名内写入一个自定义的名称名字，可以是中文也可以是英文，比如XX，最下边一行的引用位置一行内填入：
=get.cell(63.A1) 确定。
2，在B1中输入=XX，光标指向B1单元格右下角的小黑点，光标变成小黑实心十字时，双击左键。完成填充。
这样操作以后，在B列中会出现各个单元格中底色的代码。
3,用countif()函数对你要统计的单元进行统计，如：
=countif($B$1:$B$100,"5")
这个统计公式意思是统计B1至B100中颜色代码是5的单元格有多少个。
用辅助列可以做到
假设列A为原数据列，选择列B为第一辅助列
1.在插入-名称-定义里定义一个名称，X=GET.CELL(24,SHEET1!A1)
2.在B1中输入=X
这样你就会看到列B中有对应的列A颜色返回的数值，假定黑色格子的颜色数值为1
再利用一辅助列C，在列C输入公式=IF(OR(B:B=1,B:B=57,B:B=0),A:A+3,A:A)，即可得所需变更后的全部数值列
最后便是利用选择性拷贝的方法，将C列的值复制到A列，就可以完成全部操作了
注意：
1、GET.CELL(24,SHEET1!A1)
https://l98nyg.blu.livefilestore.com/y1mFP2nNdziVNJX1GC_3fO8bFWGYxLqYJPERIRpWRgxlCr8dSe2Ef9dU-Lv3HwXQWoARZTSAkrek7XGOWqxWBeGsJPcxdRuRChODS-hMD5YB0_0KzIyWSzG8t_3fdqzWEWpk61mIEm-XqQq2fIr5xy7dg/clip_image001_thumb.gif?download&psid=1大 | 中 | 小https://l98nyg.blu.livefilestore.com/y1m0kd9l2li5zD5bsnuYUhjvOoqxpA6n7ZY1BWp553Cp4e3CF24LVsZ5WcZ2XzFqY89Hmff-vfOH3ho9KL1cYj8QTM4AOyLLBATbO0mGKdLM-d0GLc3WEgz_0JI2YBfxtDeH1zklX77YA5nEZ_HO2Regg/clip_image003[3]%207D89BF36.gif?download&psid=1
![https://l98nyg.blu.livefilestore.com/y1m0kd9l2li5zD5bsnuYUhjvOoqxpA6n7ZY1BWp553Cp4e3CF24LVsZ5WcZ2XzFqY89Hmff-vfOH3ho9KL1cYj8QTM4AOyLLBATbO0mGKdLM-d0GLc3WEgz_0JI2YBfxtDeH1zklX77YA5nEZ_HO2Regg/clip_image003[3]%207D89BF36.gif?download&psid=1](https://l98nyg.blu.livefilestore.com/y1m0kd9l2li5zD5bsnuYUhjvOoqxpA6n7ZY1BWp553Cp4e3CF24LVsZ5WcZ2XzFqY89Hmff-vfOH3ho9KL1cYj8QTM4AOyLLBATbO0mGKdLM-d0GLc3WEgz_0JI2YBfxtDeH1zklX77YA5nEZ_HO2Regg/clip_image003[3]%207D89BF36.gif?download&psid=1){kind=link}
STEP 1 ：打开你的excel；
STEP 2 ：菜单栏：工具－宏－Visual Basic 编辑器；
STEP 3 ：Visual Basic 编辑器菜单栏： 插入－模块
STEP 4 ：贴入下面这段函数
Function Countcolor(col As Range, countrange As Range)
Dim icell As Range
Application.Volatile
For Each icell In countrange
Next icell
End Function
Function Sumcolor(col As Range, sumrange As Range)
End Function
STEP 5 ：保存并关闭Visual Basic 编辑器
STEP 6 ：使用函数 countcolor（所要统计的颜色所在单元格，统计的区域）
///若是字体颜色
Function Sumfontcolor(col As Range, sumrange As Range)
End Function
1、 建立Excel对象