今天分享的案例来自一个朋友,数据源是某小区每个月的水电费抄表数据,其中一个需求就是点击任一住户房号,自动生成该住户的【房租、水、电费(专用)收据】,并且能快速复制收据以方便通过微信把收据发送给住户。
附件-房租水电费收款收据.xlsm
效果如下图:
图1 点击任一住户房号,自动生成该住户的【房租、水、电费(专用)收据】及快速复制效果展示
图2 数据源-某小区每月水电费抄表数据
这个案例中,关键点有两个:一是如何通过鼠标点击房号按钮得到该房号值,二是如何根据房号生成该住户的【房租、水、电费(专用)收据】,三是如何不通过选择和复制,就能实现复制
一 利用选项按钮,通过鼠标点击房号按钮得到该房号值
通过鼠标点选房号得到该房号的方法我只会两种,简单易操作的是就是通过excel表单控件中的选项按钮来控制房号。
在图1中看到的效果, 就是已经添加选项按钮的excel表。具体操作步骤如下:
1、如果excel选项卡里没有【开发工具】选项卡,先把【开发工具】选项卡调出来
图3 调用【开发工具】选项卡
在自定义功能区,右键单击,选择【自定义功能区】对话框。
在弹出的对话框中选中【自定义功能区】列表框的【开发工具】复选框,单击【确定】按钮。
2、单击【开发工具】选项卡,可以看到在该选项卡的【控件】选项组的【插入】-【表单控件】中包含了所有表单控件,里面有一个圆圈里有个小点的图标,就是【选项按钮】,当鼠标放到上面时会显示说明。
图5 选项按钮
3、点击【选项按钮】后,excel光标变成十字的形状,这时我们在excel框选出一个区域后发现,选项按钮已经出现了,右键选择【编辑文字】,改成房号,改完后鼠标放到框外任一单元格,退出选项按钮编辑状态。
图6 插入选项按钮,编辑按钮文字显示
4、然后选择刚刚插入的选项按钮,单击右键,选择【设置控件格式】,鼠标放到对话框的【单元格链接】框中,然后选择链接的单元格,本案例我们选择S1单元格。点【确定】后退出对话框。
图7 选项按钮的【设置控件格式】
然后按同样的步骤,再插入39个选项按钮。
选项按钮的对齐方式,见下图
图8 选项按钮对齐
后续背景色可自行设置
5、控件格式设置完毕后,当我们选择插入的第一个选项按钮,S1单元格会显示1,选择第二个插入的选项按钮,S1单元格会显示2…,也就是选择第几个插入的选项按钮,S1单元格就会显示几。那么,我们可以根据S1单元格显示的数字,匹配对应的房号。
图9选择第几个插入的选项按钮,S1单元格就会显示几
6、在Z列和AA列作为辅助列,输入【选项按钮序号】和【房号】
7、在C5单元格,使用vlookup函数,从Z列和AA列中匹配到房号。这样,当点击不同的选项按钮时,S1单元格显示该选项按钮的序号,C5单元格显示SI对应的房号。实现了点击哪个住户房号按钮,C5单元格就显示哪个房号的目标。
图10使用vlookup函数,从Z列和AA列中匹配到房号
二 根据房号生成该住户的【房租、水、电费(专用)收据】
根据房号生成收据模板,这个很简单,可以使用vlookup和match函数组合,或者index和match函数组合,都可以很简单查询出该住户的水电表读数及各项费用,鉴于篇幅要求,在此不再赘述,大家可以根据我已经设好公式或者参考10月4日的文章(根据身份证号生成退休审批表)自行学习。当然,如果大家需要我再讲讲的话,请留言,我下期讲。
模板里面还有【合计人民币(大写)】需要填写,公式我也已经设置好,同样考虑文章篇幅,今天不再讲。
三 使用【录制宏】,实现【房租、水、电费(专用)收据】的快速复制
40个住户,每个住户都要发送收据,那么就需要选择、复制40次,简单重复劳动,这时我们应该想到使用【录制宏】,将选择收据、复制收据的过程录制下来,然后将录制的宏指定给按钮或者文本框都可以。
图11录制一个复制收据的宏
将宏指定给按钮与文本框的不同在于,按钮只能进行简单的格式设置,而文本框则可以设置其填充色、字体颜色、阴影效果等多种格式。再次,我们选择的是将宏指定给文本框。
图12将宏指定给文本框
好了,今天就分享这些了,喜欢的朋友请点赞、转发!不明白的地方,欢迎留言。
领取专属 10元无门槛券
私享最新 技术干货