今天的案例厉害了,包罗了条件格式、公式、控件、VBA,知识面非常广,也很有趣。
下图 1 本是个普通的销售业绩表,现有如下要求:
分别设置两种筛选条件,用单选钮来选择;
其一是将单人获客总次数符合指定数值的行标绿;
其二是将获客总数超过某个数的所有人标红
效果如下图 2 所示。
1. 在 E 列分别创建两个条件项
2. 选中 E2 单元格 --> 选择菜单栏的“数据”-->“数据验证”-->“数据验证”
3. 在弹出的对话框中选择“设置”选项卡,按以下方式设置 --> 点击“确定”:
允许:选择“序列”
来源:输入“1,2,3,4,>=5”
4. 选择菜单栏的“开发工具”-->“插入”-->“数值调节钮”
5. 将控件放置到下图所示的位置
6. 选中控件 --> 右键单击 --> 在弹出的菜单中选择“设置控件格式”
7. 在弹出的对话框中选择“控制”选项卡,按以下方式设置 --> 点击“确定”:
最小值:30
最大值:120
步长:30
单元格链接:选择 E5 单元格
8. 将 E5 单元格的字体设置为白色、居中,将 E2 单元格的字体也设置为居中。
现在点击控件的上下箭头, E5 单元格内的数值就会以规定的步长增加或减小。
9. 选择菜单栏的“开发工具”-->“插入”-->“选项按钮”
10. 将控件放置到 D2 单元格的位置
11. 删除控件的文本
12. 将选项按钮复制粘贴到 D5 单元格
13. 选择菜单栏的“开发工具”-->“插入”-->“分组框”
14. 用分组框包住刚才创建的两个选项按钮
15. 删除分组框的文本
16. 选中上面那个选项按钮 --> 右键单击 --> 在弹出的菜单中选择“设置控件格式”
17. 在弹出的对话框中选择“控制”选项卡 -->“单元格链接”选择 H1 单元格 --> 点击“确定”
现在 H1 单元格就和选项按钮联动了,选中上面的单选钮,H1 显示 1,选中下面的,则显示 2。
接下来设置条件格式。
18. 选中 A2:B28 区域 --> 选择菜单栏的“开始”-->“条件格式”-->“新建规则”
19. 在弹出的对话框中选择“使用公式确定要设置格式的单元格”--> 输入以下公式 --> 点击“格式”按钮:
=AND(COUNTIF($A$2:$A$28,$A2)=$E$2,$H$1=1)
公式释义:
COUNTIF($A$2:$A$28,$A2)=$E$2:等号左边计算 A2 单元格在整个区域内出现的总次数,判断其与等号右边的值是否一致;
$H$1=1:表示选中了第一个选项按钮;
AND(...):如果两个条件同时符合,则触发条件格式
* 请注意公式中的绝对和相对引用。
20. 在弹出的对话框中选择“填充”选项卡 --> 选择所需的填充色 --> 点击“确定”
21. 点击“确定”
第一个条件就设置好了:选中上方的选项按钮,选择 E2 单元格中的菜单选项,数据表中符合条件的人就会整行高亮显示为绿色。
接下来继续设置第二个条件格式。
22. 再次选中 A2:B28 区域 --> 选择菜单栏的“开始”-->“条件格式”-->“新建规则”
23. 在弹出的对话框中选择“使用公式确定要设置格式的单元格”--> 输入以下公式 --> 点击“格式”按钮:
=AND(SUMIF($A$2:$A$28,$A2,$B$2:$B$28)>=$E$5,$H$1=2)
公式释义:
SUMIF($A$2:$A$28,$A2,$B$2:$B$28):计算 A 列中姓名为 A2 的人的获客数之和;
>=$E$5:判断上述结果是否大于等于 E5 单元格的值;
AND(...,$H$1=2):当符合上述判断条件,且 H1 单元格的值为 2 时,触发条件
24. 在弹出的对话框中选择“填充”选项卡 --> 选择所需的填充色 --> 点击“确定”
第二个条件格式也设置好了。选中下方的按钮,用控件调整 E5 单元格的数值,数据表中符合条件的行都会变成红色。
25. 将 H1 单元格的文字设置为白色,隐藏起来。
最后再把组合框的边框隐藏掉。
26. 按 Alt+F11 打开 VBE --> 按 Ctrl+G 打开立即窗口 --> 输入以下代码 --> 回车 --> 关闭窗口:
ActiveSheet.GroupBoxes.Visible = False
妥了,难看的边框没有了,最终设置完毕。
很多同学会觉得 Excel 单个案例讲解有些碎片化,初学者未必能完全理解和掌握。不少同学都希望有一套完整的图文教学,从最基础的概念开始,一步步由简入繁、从入门到精通,系统化地讲解 Excel 的各个知识点。
现在终于有了,以下专栏,从最基础的操作和概念讲起,用生动、有趣的案例带大家逐一掌握 Excel 的操作技巧、快捷键大全、函数公式、数据透视表、图表、打印技巧等……学完全本,你也能成为 Excel 高手。
联系客服