实例 | Excel模板设计优化方案 / 四六文摘

业务背景：公式准备搞一次活动，活动数据需要按周统计，相关明细数据系统中可以导出，需要按周汇总，现在需要设计一个模板来统计相关数据！

活动周期：2020-5-24到2020-8-31

分析需求：核心在按周统计，本质也没什么，就要分清楚每周的开始和结束日期即可。

实际设计过程中一般会设计成这样：填写用户自己看看本周开始的日期和结束日期，然后填上。

问题：用户填写日期，不可控，最后可能无法保证准备的统计到数据！

建议设计：根据日期区间罗列好日期，设置好对应的周数，用户选择周即可！

日期和星期自动化设置

日期：=IF($D$2+ROW(A1)-1<=$E$2,$D$2+ROW(A1)-1,"")

可能你会直接开始日期下拉，但是这样的可扩展性没有了，所以一般我会考虑扩展性，这是是实际业务逼出来了，可能修改活动周期，这样设计自动扩展不用考虑修改问题，维护方便，自己修改开始和结束日期即可！

周的原则是从活动第一天开始，每7天算一周，那么我们也可以通过公式来处理，不用自己一个一个手动去算！

基础知识（点击跳转到文章）：学习函初 | 没有灰色地带-我是IF！

第几周：=TEXT(INT((ROW(A1)-1)/7)+1,"第[Dbnum1]d周")

以上我们就完成了基础配置的自动化，计算业务活动时间有调整，我们也不用大动干戈，设置只要终端用户自己调整配置即可！

下面的问题，就是如何让用户自行选择，减少输入，降低错误。

思路是设置下拉菜单，让用户自己选择周，然后根据周自动配置开始日期和结束日期来方便我们根据日期区间到明细表抽取数据！

要制作下来列表，我们就需要获取下拉选项，怎么获取，上面的周数据是有重复的，我们要去重后的数据

那就去个重复，去重的公式太多，这里不展开！

周去重，自动获得下拉选项

去重公式：=IFERROR(INDEX(B:B,MIN(IF(COUNTIF($G$1:G1,$B$2:$B$300)=0,ROW($2:$300),9^9)))&"","")

OK，这样我们下拉选项也有了，也是自动的，同步更新，下面就是写到下来菜单，我们也是设计成自动的

动态下来菜单的设置

我们通过公式来动态获取到有效的周数，定义成名称，方便下一步使用

公式：=OFFSET($G$2,,,LOOKUP(1,0/($G$2:$G$300<>""),ROW($G$2:$G$300)-1))

动态菜单，我已经分享过几次,我们今天用的基本都是我写过的基础知识！

基础知识（点击跳转到文章）：

> 自动化办公之区域大小动态获取！

定义名称完成，这样我们就可以按周选择了,比自己写日期容错率高了太多了

模板设计第一要考虑的就是准确性，然后是可用性、灵活性和可扩性！

下面我们就要考虑日期匹配的问题，根据选择的周，找到每周的开始日期和结束日期，用于根据日期范围获取明细数据！

开始日期自动获取匹配

公式：=INDEX(Sheet1!A:A,MATCH(Sheet2!B2,Sheet1!B:B,))

使用的公式也是我写过很多次的，万能查找组合

基础知识（点击跳转到文章）：Excel中最强查询组合-INDEX+MATCH！

结束日期自动匹配

公式：=LOOKUP(1,0/(Sheet1!B:B=B2),Sheet1!A:A)

也讲过，LOOKUP最常见的用法之一，基本已经成了模板

基础知识（点击跳转到文章）：函数 | 入门VLOOKUP，进阶用LOOKUP！

自动化模板，效果演示（动画演示）

认真看完，日期范围修改，全部自动同步调整，不需要任何干预

小结：今天给大家演示是一个为企业根据业务设计的实际模板(部分),其中用的都是我之前写过的知识，没有任何新知识。想要设计要模板.

如何的基本需求，要求设计者必须掌握以下知识：

首先，基础Excel知识的储备，基础功能的保证！比如常用的函数（INDEX、MATCH、LOOKUP、TEXT、ROW等案例中用到）和一些常用功能的熟悉（案例中的定义名称和数据有效性），掌握和灵活应用最基本的要求！
其次，一个美观和人性化的模板，可能还需要懂点设计方面的知识，比如配色和架构，这些可以慢慢学习
最后，模板可扩性、性能(速度）等方面，更要求设计者对Excel有全面的认知，比如什么函数属于快速、函数不行是否可以考虑VBA、VBA终端用户支持情况等都是要考虑的

没有完美的模板，只有不断优化的模板设计者！

实例 | Excel模板设计优化方案