职业必备技能——Excel数据分析（二）

最新推荐文章于 2025-05-19 21:59:44 发布

害羞的白菜

最新推荐文章于 2025-05-19 21:59:44 发布

阅读量760

点赞数 37

分类专栏：数据分析文章标签： excel 数据分析数据挖掘 microsoft windows 产品经理项目管理

本文链接：https://blog.csdn.net/2301_78053665/article/details/148046044

版权

数据分析专栏收录该内容

3 篇文章

订阅专栏

Excel常用函数的使用

sum求和函数

=SUM('源数据0701-0816'!J2:J144)使用函数跨表拿到连续的数据的解释

哪些情况需要添加双引号，哪些情况下不需要添加双引号

月环比的计算方法

Excel常用函数的使用

sum求和函数

sum函数的本质就是对选定的区域进行求和（区域可以是整行或者整列或者任意区域进行求和）

使用方法

=sum（参数1 符号参数2）

上述的符号可以是逗号或者冒号

例如，SUM(A1:A10,B1:B10)表示对 A1 到 A10 单元格区域和 B1 到 B10 单元格区域中的所有数值进行求和。

如果要表示连续的单元格区域，在参数中使用冒号 “:”，如SUM(A1:A10)表示对 A1 到 A10 这 10 个单元格进行求和，这里的 A1 和 A10 之间用冒号分隔，表示一个连续的区域。

Excel是可以跨工作表进行函数计算的，如下图（源数据…是另外一个表格的数据）

如果是跨表进行函数的调用那么在对应的函数单元格中会出现“=SUM('源数据0701-0816'!J2:J144)”这样的格式（如下图）

=SUM('源数据0701-0816'!J2:J144)使用函数跨表拿到连续的数据的解释

=SUM（）表示我们调用的是一个求和的函数，内部的‘源数据0701-0816’！这个部分表示的是我们从哪里拿到数据表，J2:J144表示我们进行sum求和的单元格区域

但是我们如果有大量的数据但是我们不想来回数据表进行切换的话，优化方式如下

函数依赖的表的数据调用优化方式

第一步：在源数据中的“视图”选项卡单击选择“新建窗口”功能，这样我们就复制出了一个一模一样的Excel表格，在复制的新的表格中

对非连续的区域进行sum求和的方式

如果我们已经有了连续的一段时间的数据，例如我们知道了1月到8个整个区间内的数据但是我们只需要求7月和8月这单独两个月进行求和，如何进行操作呢？

可以使用逗号分隔不同的区域，J2到J107为7月份的数据，J108-J144为8月份的数据，二者使用逗号隔开之后再进行求和即可

冻结窗格的实现

当我们使用函数的时候不知道函数的结束行号或者我们不确定哪一行是我们想要查找的列，可以使用冻结窗格进行实现

冻结窗格就是当我们滚动的时候会自动将设置冻结窗格的单元格进行悬停，方便我们查阅

设置冻结窗格的方法

在视图选项卡中可以设置冻结窗格，冻结窗格有5种形式分别为“冻结至第X行Y列”、“冻结至第X行”、“冻结至第Y列”、“冻结首行”、“冻结首列”

冻结至第X行Y列表示Excel自动将单元格以上的行和以左的列进行冻结这样我们在滑动的时候行前列前就是固定的

冻结至第X行，表示我们仅冻结行，下滑的时候超过超过冻结的行，冻结的行会悬停

冻结至第Y列的原理和冻结至第X行同理

冻结首航，冻结首列表示仅冻结第一行或者仅冻结第一列

一般我们会选中B2这个单元格进行“冻结至1行A列”这样我们的表头和列属性都是被冻结的

Sumif函数

能用sum做的sumif同样能够做到，sumif就是sum的升级版，即为添加上条件的求和。例如我们想要求七月一号的GMV就可以使用SUMIF函数进行求和，先进性日期的筛选之后进行求和

SUMIF的参数

第一个参数为区域：表示需要去进行条件判断的区域，简言之就是需要对哪一列进行条件求和，如下图我们对日期进行条件判断就需要在n格列中找到日期所在的列，即为A列

所以我们鼠标单击A列的属性列函数就可以被选上了，结果如下图

第二个参数为条件：我们找到需要进行求和的列之后，还需要找到该列的条件，在A列的所有数据中数据为多少才进行计算。如果想要求7月1号条件就是日期为7月1号才进行计算，选中日期为7月1号的单元格即可（可以跨表进行选择也可以在分析表格中（原表格进行选择），只要单元格的内容和原表格中的内容格式一样且内容为7月1号即可）

在原表格中进行选择

跨表进行选择，只要内容是一样的即可

第三个参数为求和区域，对谁的什么属性进行求和：我们已经知道了求哪些区域的哪些列，但是对该列的什么内容进行求和呢？

例如我们想求7月1号的GMV，我们的第一个参数表示的是7月1号日期所在的列，第二个参数表示的是求列中条件的值为7月1号的列，第三个参数就是求GMV了

上图就是求日期（区域）为7月1 日（条件）的GMV（求和区域）

单元格的锁定

当我们进行自动填充功能的时候左右拖拽单元格会发生变化，我们想要控制单元格的变化可以使用单元格的锁定功能，就是‘$’符号，被'$'锁定的行不会进行变化，被$锁定的列不会进行变化，被$锁定的行和列不会发生变化

一个单元格的坐标的表示为‘列号行号’的方式，例如A2表示1列2行，B19表示2列19行，当我门添加了$符号的时候距离符号最近的坐标不会发生变化

$列号行号：表示锁住了单元格的列，自动填充的时候会变行不变列，可以进行上下的自动填充

列号$行号:表示锁定了单元格的行，自动填充的时候变列不变行，可以进行左右的自动填充

$列号$行号：表示锁定单元格无论怎么进行填充该单元格都不会随着自动填充的改变而改变

单元格的锁定对于函数部分的帮助非常大，必须理解掌握，选中函数中的可变内容按F4快速进行锁定

多条件求和SUMIFS函数

SUMIFS的参数项

求和区域表示我们需要计算的值

区域1表示我们进行条件判断所在的区域

条件1表示我们需要在区域1中设置什么样的条件

…表示我们的多条件求和，可以是‘求和区域，区域1，条件1，条件2，条件3……’

与sumif形式上的不同点就在于SUMIFS的求和区域在前二SUMIF的求和区域在后，SUMIFS可以是多个条件而SUMIF只能是单条件

SUMIFS的例子

如果我们想知道美团在7月1号的GMV数据，这里就会有两个条件，一个是日期条件7月1号，另一个是平台条件“美团”，这样我们就能够使用SUMIFS函数进行多条件的求和

=SUMIFS('源数据0701-0816'!J:J,'源数据0701-0816'!A:A,A19,'源数据0701-0816'!H:H,'源数据0701-0816'!H4)的解释
1.SUMIFS（）表示多条件求和
2.'源数据0701-0816'!J:J,'表示需要的值，对应的J列表示为GMV
3.'源数据0701-0816'!A:A,A19,'表示我们第一个条件所在的区域为'源数据0701-0816'!A:A,'且我们该区域（日期区域）的条件为A19，A19是我们本表的单元格，值为2020/8/1
4.'源数据0701-0816'!H:H,'源数据0701-0816'!H4表示我们的第二个条件所在的区域为'源数据0701-0816'!H:H,且我们第二个条件区域（平台区域）的条件为'源数据0701-0816'!H4，'源数据0701-0816'!H4是我们跨表的单元格值为“美团”

当然，参数不光光是具体单元格内的条件才能生效，参数还可以是具体的字符串，例如我们上述例子中筛选“美团”平台7月1日的营收，这里的美团也可以是我们直接输入字符串“美团”而不需要我们手动的调用单元格内的内容=SUMIFS('源数据0701-0816'!J:J,'源数据0701-0816'!A:A,A19,'源数据0701-0816'!H:H,“美团”)

需要注意的是直接输入字符串需要添加双引号！

剩余的数据我们直接进行自动填充即可，如果不放心我们可以对内容进行检查，例如查看8.7号的数据是否正确，如下图。

环比和同比

一、环比和同比的解释

环比和同比是两种常用的数据对比方法，用于分析不同时间段内数据的变化趋势，常见于经济、商业、统计等领域。

环比（Month-on-Month, MoM 或 Chain-based Comparison）

定义：环比是指相邻两个统计周期（如日、周、月）之间的数据对比，反映短期内的变化趋势。
公式：

$

\text{环比增长率} = \frac{\text{本期数据} - \text{上期数据}}{\text{上期数据}} \times 100%

$
特点：消除季节性因素干扰，适合分析短期波动（如月度销量变化）。

同比（Year-on-Year, YoY）

定义：同比是指同一统计周期在不同年份之间的对比，反映长期趋势或季节性影响。
公式：

$

\text{同比增长率} = \frac{\text{本期数据} - \text{去年同期数据}}{\text{去年同期数据}} \times 100%

$
特点：消除短期波动影响，适合分析年度趋势（如节假日销售对比）。

二、环比和同比的分类

根据对比周期的不同，环比和同比可细分为以下类型：

1. 环比分类

|类型|定义|应用场景| |-|-|-| |日环比|今日数据与昨日数据的对比|监控日销售额、用户活跃度波动| |周环比|本周数据与上周数据的对比|分析周内业务波动（如电商促销）| |月环比|本月数据与上月数据的对比|评估月度经营业绩变化| |年环比|本年度某周期与上年度相邻周期的对比|较少使用，通常用同比替代|

2. 同比分类

|类型|定义|应用场景| |-|-|-| |日同比|今日数据与去年同日的对比|分析节假日或特殊日期的影响（如双十一）| |周同比|本周数据与去年同周的对比|比较季节性活动（如寒暑假消费）| |月同比|本月数据与去年同月的对比|评估年度增长趋势（如季度财报）| |年同比|本年度数据与上年度数据的对比|分析长期经济或行业发展|

三、核心区别与选择建议

环比 vs 同比

环比：反映短期波动（如月度业务增长是否停滞）；
同比：反映长期趋势（如年度增长是否符合预期）。

如何选择

需要消除季节性影响 → 使用同比（如比较12月圣诞季销售额）；
需要观察短期变化 → 使用环比（如分析促销活动的即时效果）。

四、示例说明

月环比应用：某电商平台3月销售额为1000万，2月为800万，则环比增长25%。
月同比应用：某品牌2023年5月销量为5000台，2022年5月为4000台，则同比增长25%。
通过合理使用环比和同比，可以更精准地捕捉数据背后的业务逻辑。

同比环比结合到Excel中进行计算的例子

环比的例子

例如我们需要计算美团平台的8.1-8.7的GMV同比和环比，如何进行计算呢？

首先我们要明确上述例子本质的模型是一个SUMIFS多条件的求和模型。，其次我们要知道8.1-8.7的GMV同比就是依照着7.1-7.7的GMV进行除法运算-1

而8.1-8.7的环比就是与7.31-8.6的GMV做除法运算-1即可。

具体的公式如下：

GMV日环比=当天GMV/前一天GMV-1

环比的前一天如何进行计算呢？不要忘记我们日期的本质仍然是“数字”，直接在调用日期的条件中-1即可

B列是我们的GMV列，A列日期列，H是平台列，有美团和饿了摸两个平台

=B19/SUMIFS('源数据0701-0816'!J:J,'源数据0701-0816'!A:A,A19-1,'源数据0701-0816'!H:H,"美团")-1

剩余内容我们自动填充即可

最后的环比一般设置为百分比的格式，我们需要在“数字格式”选项卡中设置“百分比”即可完成格式的转换

同比的例子

同比又可以分为两种类型的同比，一种类型的同比是日相对于周的同比，另一种类型的同比是日相对于“月”的同比

周同比的公式：当天的GMV/前一周的对应的那天的GMV-1，例如今天是周1，如果想求今天的周同比可以用今天/上周一日期的GMV-1

月同比的公式：当天的GMV/前一个月的对应的那天的GMV-1，例如今天是1号，如果想求今天的月同比可以用今天的日期的GMV/上月对应的日期的GMV-1

月同比的易错情况

月同比的特殊情况，例如我们今天是3月31号，如何计算今天的月同比？2月可没有31号！

解决方法是配合日期函数快速完成计算

Excel中支持的日期函数有YEAR（），MONTH（），WEEK（）， DAY（）

例如我们的A19是日期类型的单元格，日期为2020/8/1，我们对不同的函数分别调用不同的日期函数

需要注意的是WEEK（）函数需要-1才是日期对应的正确的星期，因为外国的星期日就是星期1，多算了一天要减回来

且如果显示的不是下图的格式可能是我们的数据显示格式错误了，将数据的显示格式改为“日期格式”即可

上述的操作都完成了之后如果我们想要组合日期可以使用DATE函数完成日期格式的组合

DATE函数的格式为DATE（YEAR（），MONTH（），DAY（）），如果有某些列已经被YEAR、MONTH、DAY函数计算出来的话存放在单元格中的话，可以直接调用单元格即可代表对应的日期函数（如下图）

如果没有已经计算完成的日期单元格我们也可以在DATE中一步完成日期的筛选和组合

由格式可以看出来DATE函数是一个复合函数

下面我们展示月同比的错误求法（没有考虑到月尾的最后一天在上个月的月尾是否存在的情况

月同比的错误求法

根据上述我们月的易错点分析，我们在计算月同比的时候可以使用组合日期，但是组合日期时的月需要-1

知道了上一个月的日期，所以本日的月同比GMV求法可以根据公式：本日的GMV/上一个月该日的GMV

上个月的对应日期的GMV转换成Excel格式为（例如本日为2020/8/1）

=SUMIFS('源数据0701-0816'!J:J,'源数据0701-0816'!A:A,DATE(YEAR(A19),MONTH(A19)-1,DAY(A19)),'源数据0701-0816'!H:H,"美团") 解释： =SUMIFS（）表示根据条件求和源数据0701-0816'!J:J,'表示查询的GMV所在的列号 '源数据0701-0816'!A:A,表示查询日期这个条件的列号 DATE(YEAR(A19),MONTH(A19)-1,DAY(A19)),表示查询日期的条件，通过MONTH（日期单元格中的月）-1来精确找到A19的上个月同一个日期 源数据0701-0816'!H:H,表示第二个条件所在的列号

"美团"表示在第二个条件的H列区域中查找该区域中名称为“美团”的条件 TIP：A19单元格的内容是2020-8-1

通过自动填充快速的对单元格进行填充

这时我们当天的GMV和上一个月对应日期的GMV都拿到了之后利用公式计算对应日期的月同比GMV即可

或者直接调用函数进行求解，复制我们上个月那一天的GMV（下方蓝色区域）和当天的GMV（下方黄色）相除之后再减1即可

=SUMIFS('源数据0701-0816'!J:J,'源数据0701-0816'!A:A,A19,'源数据0701-0816'!H:H,"美团")/SUMIFS('源数据0701-0816'!J:J,'源数据0701-0816'!A:A,DATE(YEAR(A19),MONTH(A19)-1,DAY(A19)),'源数据0701-0816'!H:H,"美团")-1

月同比的正确求法

目前来说较为简单的方式就是用上一个月的最后一天的日期进行代替，而判断上一个月的最后日期可以使用IF函数

例如我们要求3月31日的月环比，用IFBLANK判断日期是否存在

=IF(ISBLANK(B2), A2/C2, A2/B2)
A2: 3 月 31 日 GMV（X元）
B2: 2 月 31 日 GMV（空白）
C2: 2 月 28 日 GMV（Y元）

上述的代码仍然会存在问题，就是2025年的2月是几天我门不确定，或者说我门确定程序不知道，就算是程序知道了但是我门把代码写死了如果后面的年份存在闰年呢？这些问题可以先保留，当我们后期学会了INDEX函数之后可以使用该函数检查最近的可用日期

计算月份的累计GMV，整个月的GMV求和

我们的模板Excel中的日期的格式为YYYY-MM-DD，并没有给我明显的月份的GMV，如何进行月份的GMV计算呢？

Excel本质上不支持YYYY-MM的显示，当我们强行输入了YYYY-MM格式的日期默认会自动变成YYYY-MM-01这样的类型，更改方式如下

更改日期类型

第一步：在数字格式下滑菜单中找到“其他数据格式”

第二步：在打开的窗口中找到“自定义”选项卡中的“YYYY年m月”单击确定即可

之后我们就能看到日期的格式发生了相应的变化了

计算一个月的GMV的累加和需要知道月的区间，我们可以使用≥和≤来完成月的区间的筛选，但是每个月的第一天好计算，每个月的最后一天如何进行计算呢？

具体方法如下：

知道了日期格式之后我们每个月的GMV还需要知道每个月的第一天和最后一天，每个月的第一天可以结合DATE组合函数实现

每月的第一天

=DATE(YEAR(A19),MONTH(A19),1)

每个月的最后一天是最容易出错的地方，正常思路即使我们设置成30为最后一天或者31为最后一天都是不对的，如果换一种思路，每个月的最后一天就是下个月的第一天-1就是本月的最后一天了

每月的最后一天

=DATE(YEAR(A19),MONTH(A19)+1,1)-1

之后我们就可以计算月累计的GMV了

七月的计算公式如下

=SUMIFS('源数据0701-0816'!J:J,'源数据0701-0816'!H:H,"美团",'源数据0701-0816'!A:A,">="&DATE(YEAR(A18),MONTH(A18),1),'源数据0701-0816'!A:A,"<="&DATE(YEAR(A18),MONTH(A18)+1,1)-1)

注意：A18单元格的内容为2020/7/1，如果要计算时间区间需要使用≤和≥期间需要使用""将大于等于和小于等于引起来，之后还需要使用&符号进行连接

八月的计算公式如下

=SUMIFS('源数据0701-0816'!J:J,'源数据0701-0816'!H:H,"美团",'源数据0701-0816'!A:A,">="&DATE(YEAR(A19),MONTH(A19),1),'源数据0701-0816'!A:A,"<="&DATE(YEAR(A19),MONTH(A19)+1,1)-1)

注意：A19单元格的内容为2020/8/1，如果要计算时间区间需要使用≤和≥期间需要使用""将大于等于和小于等于引起来，之后还需要使用&符号进行连接