用 Excel 选项按钮动态控制数据表的升降序排列,原来根本不难

请在微信客户端打开

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

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

一提到动态表，很多同学就有放弃的打算，以为要编程。别介呀，有些动态数据表的效果虽然看起来酷酷的，其实每个分拆步骤都很简单。

不信咱走一波。

将下图 1 的数据表制作成动态的，选择“升序”或“降序”选项，整个表格就自动按要求排序。

效果如下图 2 所示。

1. 将 C 列设置为辅助列 –> 在 C2 单元格中输入以下公式 –> 下拉复制公式：

=B2*10000+ROW()

公式释义：

• 这个公式的作用是把 B 列的数值变成一个不会重复的唯一值

接下来设置控件。

2. 选择菜单栏的“开发工具”–>“插入”–> 选择“表单控件”处的“选项按钮”

3. 复制粘贴出一个同样的控件。

4. 选中上方的控件 –> 右键单击 –> 在弹出的菜单中选择“编辑文字”

5. 将文本修改为“升序”

6. 用同样的方式将下方控件的文本修改为“降序”

7. 选中任意一个控件 –> 右键单击 –> 在弹出的菜单中选择“设置控件格式”

8. 在弹出的对话框中选择 G1 来放置单元格链接 –> 单击“确定”

选择不同的选项，G2 单元格中的数值就会分别显示 1 和 2。

9. 用格式刷将原数据表的标题格式复制到 F 和 G 列，F1 也设置为“姓名”。

10. 选中 G1 单元格 –> 按 Ctrl+1 –> 在弹出的对话框中选择“数字”选项卡 –> 选择“自定义”–> 输入以下类型 –> 点击“确定”：

[=1]升序;[=2]降序

现在 G 列虽然仍然是数值，但是通过格式设置，会随着控件的选择分别显示为“升序”和“降序”。

11. 将 D 列作为第二个辅助列 –> 在 D2 单元格中输入以下公式 –> 下拉复制公式：

=IF($G$1=1,SMALL($C$2:$C$11,ROW(A1)),LARGE($C$2:$C$11,ROW(A1)))

公式释义：

• SMALL($C$2:$C$11,ROW(A1))：

• small 函数的作用是提取区域 $C$2:$C$11 中第 ROW(A1) 小的值；

• 随着公式下拉，row 函数的结果依次递增，small 函数就随之依次提取越来越大的值，从而起到升序排序的作用；

• LARGE($C$2:$C$11,ROW(A1))：large 函数的作用就正好与 small 相反，因此结果就是降序排序；

• IF($G$1=1,…,…)：如果 G1 的值为 1，则升序排序；否则就降序排序

* 上述公式中的参数，除了两个 row 函数的参数需要相对引用外，其他都要绝对引用。

选择控件选项，D 列的值就会随之排序。

12. 在 F2 单元格中输入以下公式：

=INDEX($A$2:$A$11,MATCH(D2,$C$2:$C$11,0))

公式释义：

• index+match 的公式组合已经写过太多案例了，作用是先用 match 函数在 $C$2:$C$11 中找到 D2 的排列位置数；

• 然后用 index 函数在 $A$2:$A$11 的上述对应位置提取出人名

13. 在 G2 单元格中输入以下公式：

=INDEX($B$2:$B$11,MATCH(D2,$C$2:$C$11,0))

这段公式跟前面作用一样，这次是提取出 B 列 的值。

14. 选中 F2:G2 –> 向下拖动复制公式

15. 将辅助列的字体设置为白色，隐藏起来。

至止，控件所控制的数据表排序已经设置完毕了。

接下来为了更加清晰展示数据差异，再设置一下数据条。

16.  选中 G2:G11 区域 –> 选择菜单栏的“开始”–>“条件格式”–>“数据条”–> 选择所需的数据条颜色

这就是最终效果。