如何用Excel制作周报表?自动生成超方便!(上篇) | 您所在的位置:网站首页 › 月报表公式怎么快速生成 › 如何用Excel制作周报表?自动生成超方便!(上篇) |
制作后的模板效果如下图: 最终效果展示图(GIF动图) 周报表模板设计思路 针对上面提出的周报表共性问题,我们需要做以下工作。 1.解决报表标题中的周数和日期 思路: (1)可以填充数据生成一年的周数,并根据报表需要的日期格式将周数与起止日期组合成如下值。 (2)利用数据验证建立下拉选择菜单,序列值就是上面组合的周日期列。 (3)运用连字符号&将选择值与表名如“工程结算申请表”进行组合。 2.根据周数自动汇总和自动呈现明细 思路: (1)处理原始数据,将周报表需要的值(含日期)提取到一张整理表(小青这里取名为“引用表”)中。 这一步的难易程度根据每个公司的实际情况不同而不同。 如果原始数据中恰好有周报表中的各个值项,则直接用等号“=”进行跨表引用即可; 如果原始数据中的数据需要清理、计算等才能得到周报表需要的值项,则比较困难。有需要的读者可以参考《由打卡记录一键生成考勤汇总表,加班迟到早退全统计好!(上)》《由打卡记录一键生成考勤汇总表,加班迟到早退全统计好!(下篇)》 (2)在整理表中根据日期,如2022-11-30,用WEEKNUM函数生成对应周数。 (3)让整理表中的周数与报表标题中的周数进行比较,相等的话,则将数据引用到明细表中。这里可能需要运用到万金油组合公式INDEX+SMALL+IF+ROW。 (4)用公式汇总明细表数据到汇总栏。 表格的绘制 新建一个Excel文档,保存为“周报.xlsx”。 Step1:在文档中新建3张工作表:周报表、周次表、引用表。 周报表:就是最终要打印给领导看的报表,包括了汇总和对应明细。 周次表:就是上面提到的一年的周数以及日期表。 引用表:就是上面提到的整理表,用来引用并存放整理后的周报需要的数据。 Step2:绘制周报表 每个读者可以根据实际需要绘制周报表的具体格式,只有两个地方注意: 1.将第一行和第二行预留起来。 2.能够固定的内容,尽量直接录入。 譬如小青公司,建筑工程周期长,往往一个项目就是几年;参与项目的分公司往往固定;因此,合同编号、工程编码、分公司名称等看起来是需要填写的内容都直接录入了,而不需要做成变动项,利用公式来汇总统计。 如果读者当前不便创建自己的周报表,也可以直接用本文素材中的周报表来练习。 Step3:绘制周次表 切换到周次表中。先将第一行空着。为何空着?先卖一个关子不说。 1.计算周次 在该表的A2单元格输入“2022/1/1”,A3单元格输入“2022/1/3”,A4单元格输入“=A3+7”(表示7天为一周),然后选中A4单元格下拉填充。 在B2单元格输入公式=WEEKNUM(A2,2),下拉填充公式后完成周次数据。 公式解析: 用WEEKNUM函数计算对应单元格日期在一年中属于第几周,参数“2”表示从星期一开始的7天计为1周。 2.生成周次&起始日期组合值 定位到C2单元格输入公式="第"&B2&"周("&TEXT(A2,"mm-dd")&"~"&TEXT(A3-1,"mm-dd")&")",并下拉填充。 公式解析: 用TEXT函数将A列日期设置为“月-日”的形式,并且使用“&”符号将相应的字符连接起来,组成类似“第2周(01-03~01-09)”的字样(方便根据日期查看对应的周次)。 要制作其他年份的周次表时,只要更改A2、A3单元格的数据,如改为“2023/1/1”和“2023/1/2”(每个年度第1周、第2周的起始日期都要手动填写),剩余的内容即可同步更新了。 Step4:绘制引用表 读者可以根据周报的需要来设计引用表。 此处小青的引用表非常简单。 引用表 引用表公式设置 前面说过引用表是用来存放周报需要的数据,数据将引自原始记录。 因为小青的原始记录文档“协作数据.xlsx”中的数据结构与引用表数据结构一致,所以可以直接用等号进行引用。 Step1:输入引用公式 定位到引用表A1单元格,在地址栏输入“=”。 Step2:更改引用公式 返回“周报.xlsx”的“引用表”,将公式更改为=[协作数据.xlsx]数据表!A1,然后向右、向下填充至10000行(不是固定,只要能满足一年的数据引用即可)完成数据的引用。 关闭“协作数据.xlsx”文档。不用担心,关闭后引用仍然有效,唯一要注意的是“协作数据.xlsx”文档的保存位置不变,否则可能提示找不到引用文档。 Step3:生成周次标记 定位到E2单元格输入公式“=WEEKNUM(B2,2)”,下拉填充公式完成B列日期对应周次的标记。后续将根据这个标记实现自动按周数引用数据。 至此,完成了“周次表”和“引用表”的设计。在下期我们继续介绍如何设置“周报表”,以实现打开文档就能打印的目标。 需要课件的同学,可以微信扫码。进群领取,有什么疑问,咱们评论区见。 另外,Excel基础不扎实的同学,欢迎学习下面这套工作用得上的Excel训练营返回搜狐,查看更多 |
CopyRight 2018-2019 实验室设备网 版权所有 |