学了那么多公式，却依旧用不好Excel（实例讲解）

原创虾壳可乐可乐的数据分析之路

|本节内容为Excel输出日报（周报、月报）的自动化实例讲解，篇幅较长，文末可获取本节课讲解的数据案例。

涉及知识点：

1、Sum函数求累计

2、插入组合框控件

3、Vlookup函数

4、Match函数

5、Index函数

6、环比&同比

7、If函数嵌套

8、Text函数

9、Concatenate函数

10、offset函数

11、组合公式

12、Excel粘贴为图片

背景：

事情是这样的，小明每天都要进行日报通报，统计每天的全量用户数、消费用户数以及潜在用户数，算出价值用户数占比，且每个数都要与前一日对比，输出一大串文字并生成图片。每天的内容都是一样的，只是数字不一样，之前小明是每天都算出这些数字，然后再从日报中更改相应的部分，这样的工作重复个一两年也是会把人的精气神熬没了的呀。小明问我为什么他学了那么多公式，却依旧用不好Excel？

传统日报：

【5/16日报】

1、截止目前，全量用户数累计XX，环比昨日上升XX；

2、价值用户数占比为XX，环比昨日下降XX

（大段文字，单调枯燥，工作重复）

自动化后的日报：

因此我决定写一篇Excel实例应用的文章，用Excel进行日报自动化。当然本文应该算是半自动化，数据还是要手动录入，若要实现完全的自动化，还需仰仗VBA，这个本章暂不涉及，所以是半自动化。

注：本篇文章所用数据为虚构的非真实数据，并无实际意义。

一、原始数据区

原始数据是这样的：日期、全量用户数累计、消费用户数（累计）、潜在用户数（累计）

二、原始数据计算辅助列

首先我要算出消费用户数的累计占比，用累计消费用户数除以累计全量用户数得到。

潜在用户数的累积占比同理：

还要算出价值用户数累计占比，为消费用户数累计占比加上潜在用户数累计占比

好了，累计的数目全部算好了，下面来算每日的用户数，这个很容易理解，5月2日的全量用户数等于5月2日的累计全量用户数-5月1日的累计全量用户数。

同理，求出消费用户数每日和潜在用户数每日，以及消费用户数每日占比、潜在用户数每日占比和价值用户每日占比。

PS：这里插一句题外话，上文是已知累计求每日的数，是用当日减前日，若已知每日求累计，如何求？我们发现用当日的数加上前日的数好像不对了，因为当公式下拉的时候，得到的每一项的值都是当日加前日，就只有两个数据相加，实际上我需要的是当日以前所有的数据相加，这里就要用到sum函数。Sum($I$2:I2)，什么意思呢？$I$2是被绝对引用了，不能变，能变的是没有被引用的I2，第一个单元格不明显，那我们看第三个单元格，从I2加到I4，明白了吧。

收~

三、数据过渡区

数据源表太宽了，我们新建一张数据过度表，点击开发工具——插入——组合框。当鼠标变为十字的时候在Excel空白区画一个大小得宜的框，右键设置控件格式，在数据源区域选择原始数据中的日期一列，单元格链接随便选一个空白区域，下拉显示项数默认。这样我下拉框选择一个日期，如5月1日，在I1单元格就会出现日期对于的天数，1。