|
excelperfect 标签：Excel实战技巧 在《数据有效性+条件格式，创建有想象力的输入界面》中，我们构建了如下图1所示的Excel工作表界面，使输入数据在用户面前能够清晰地展示。 图1 下面，让我们进一步扩展其输入表现方式，使效果更完美，如下图2所示。 图2 当用户在列B中选择了支付方式后，如果相应的输入单元格输入了内容，则正常显示，否则添加背景色高亮显示；如果相应的输入单元格全都输入了内容，则列A中显示√，如果只输入了部分单元格，则显示!，如果都没有输入，则显示×。 仍然是使用条件格式来实现，但公式可能会稍微复杂一些。 步骤1：构建辅助区域 为方便实现功能，我们构建一个辅助区域，如下图3所示。 图3 其中，列J中是各种支付方式，列K中是支付方式对应的输入单元格数量。 步骤2：编写公式 在单元格A3中，输入公式： =IFERROR(CHOOSE(MATCH(B3,$J$3:$J$5,0),COUNTA(C3:D3),COUNTA(E3:F3),COUNTA(G3))/VLOOKUP(B3,$J$3:$K$5,2,FALSE),0) 公式中，MATCH函数根据单元格B3中的值，获得相应的输入区域位置，CHOOSE函数根据位置值选择并得到相应区域中非空单元格数；VLOOKUP函数根据单元格B3中的值得到应该输入内容的单元格数。将上述两个结果相除，如果结果是1，表明应该输入内容的单元格已全部输入数据；如果结果大于0小于1，则表明只有部分单元格输入了数据；如果结果为0，则表明要输入内容的单元格仍为空。 拖动单元格A3，复制公式至单元格A16。 步骤3：设置单元格区域A3:A16的条件格式 选择单元格区域A3:A16，单击功能区“开始”选项卡“样式”组中的“条件格式——新建规则…”。在弹出的“新建格式规则”对话框中，选择规则类型“基于各自值设置所有单元格的格式”，在“格式样式”中，选择“图标集”，在“图标样式”中选择“三个符号（无圆圈）”，勾选“仅显示图标”前的复选框，设置图标显示值，如下图4所示。 图4 设置完毕后，单击“确定”按钮。 步骤4：分别设置单元格区域C3:D16、E3:F16、G3:G16的条件格式 先选择单元格区域C3:D16，单击功能区“开始”选项卡“样式”组中的“条件格式——新建规则…”，在弹出的“新建格式规则”中，选择规则类型为“使用公式确定要设置格式的单元格”，在“为符合此公式的值设置格式”中输入公式： =($B3='信用卡')*((C3='')+(C3=0)) 单击“格式”按钮，设置单元格背景色。结果如下图5所示。 图5 同样，依次选择单元格区域E3:F16和G3:G16，设置条件格式规则，只是要将公式修改为： =($B3='信用卡')*((E3='')+(E3=0)) 和 =($B3='信用卡')*((G3='')+(G3=0)) 大功告成！ 注：如果有兴趣，你可以到知识星球完美Excel社群下载本文配套示例工作簿，对照文章研习。 |
|
|
来自： hercules028 > 《excel》
