另类玩法:选Excel下拉菜单,计算不同指标,且智能切换数字格式

请在微信客户端打开

公众号平台最新的推送规则对技术类文章不太友善，如果不想错过干货，请务必“设为星标”哦！！！

点击任意文章上方的“☆星标”即可。

试过下拉菜单这种玩法吗？

工作汇报中经常需要展示各种各样的 KPI，同一批源数据要从各种维度进行计算，通常做法都是每个计算结果放一列。

其实完全可以做成一个下拉菜单，通过选择菜单项，计算出所需的指标，并且还要显示成合适的数据格式。

案例：

下图 1 是某公司销售人员 2019、2020 年一季度的实际业绩，以及 2020 年一季度指标。请根据下拉菜单选项分别计算出同比增长数、同比增长率和目标达成率。

效果如下图 2 所示。

解决方案：

先制作下拉菜单。

1. 选中 F1 单元格 --> 选择菜单栏的“数据”-->“数据验证”

2. 在弹出的对话框中选择“设置”选项卡 --> 按以下方式设置 --> 点击确定：

• 允许：选择“序列”

• 来源：输入“同比增长数,同比增长率,目标达成率”

下拉菜单已设置完成。

3. 在 F3 单元格输入公式：=F1

接下来根据下拉菜单设置不同计算公式及数据格式。

4. 在 F4 单元格输入以下公式，下拉复制公式：

=IF($F$3="同比增长数",E4-C4,IF($F$3="同比增长率",E4/C4-1,E4/D4))

5. 选中需要设置格式的区域 --> 选择菜单栏的“开始”-->“条件格式”-->“新建规则”

6. 在弹出的对话框种选择“使用公式确定要设置格式的单元格”--> 输入以下公式 --> 点击“格式”：

=$F$3="同比增长数"

7. 在弹出的对话框中选择“数字”选项卡 --> 选择“数值”--> 在右侧区域进行以下设置 --> 点击“确定”：

• 小数位数：设置为“0”

• 勾选“使用千分位分隔符”

• 选择红色带括号的负数类型

8. 点击“确定”

现在选择下拉菜单中的“同比增长数”，就会计算出 2020 Q1 实际减去 2019 Q1 的数值，且格式符合刚才的设置要求。

接下来设置百分比格式。

9. 选中需要设置格式的区域 --> 选择菜单栏的“开始”-->“条件格式”-->“新建规则”

10. 在弹出的对话框中选择“使用公式确定要设置格式的单元格”--> 输入以下公式 --> 点击“格式”：

=COUNTIF($F$3,"*率")>0

公式释义：

• 只要 F3 单元格的内容是以“率”结尾，就符合规则

11. 在弹出的对话框中选择“数字”选项卡 --> 选择“百分比”，将右侧的“小数位数”设置为 0 --> 点击“确定”

12. 点击“确定”

现在所有设置都已经完成了，选择菜单选项，F 列就会计算出不同 KPI，且自动转换成对应的数据格式。