本文总共1896个字,阅读需7分钟,全文加载时间:2.371s,本站办公入门专栏收录该内容! 字体大小:

文章导读:今天的案例厉害了,包罗了条件格式、公式、控件、VBA,知识面非常广,也很有趣。 目录内容 隐藏 ……各位看官请向下阅读:

今天的案例厉害了,包罗了条件格式、公式、控件、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 高手。

想要从零系统学习Excel,查看本站上方“视频教程”获取最新秋叶Office三合一办公应用课程!

以上内容由优质教程资源合作伙伴 “鲸鱼办公” 整理编辑,如果对您有帮助欢迎转发分享!

你可能对这些文章感兴趣:

发表评论

您的电子邮箱地址不会被公开。 必填项已用*标注