第1-3章Excel数据分析基础

第1章：使用统计函数做数据分析

1-1常用统计函数应用

1、常用统计函数学习

要求	函数
总分	SUM
计数	COUNT
平均分	AVERAGE
最高分	MAX
最低分	MIN
第K个最小值	SMALL
第K个最大值	LARGE

1-2条件统计函数

统计	函数
条件求和	SUMIF
条件平均	AVERAGEIF
条件最大值	MAXIFS
条件最小值	MINIFS
条件计数	COUNTIF

1-3多条件统计函数

常用统计函数是：SUM、AVERAGE、MAX、MIN、COUNT，而多条件统计函数则是在这些函数的后面加上IFS即可，多条件统计函数同样支持通符条件。

例如

条件1：供应商为：黄乐安；商品总类为：显卡。

求和	=SUMIFS($E:$E,$B:$B,“黄乐安”,$C:$C,“显卡”)
平均	=AVERAGEIFS($E:$E,$B:$B,“黄乐安”,$C:$C,“显卡”)
最大	=MAXIFS($E:$E,$B:$B,“黄乐安”,$C:$C,“显卡”)
最小	=MINIFS($E:$E,$B:$B,“黄乐安”,$C:$C,“显卡”)
计数	=COUNTIFS($B:$B,“黄乐安”,$C:$C,“显卡”)

求和：

条件2：商品总类：显卡；采购日期为3月。

方法：
=COUNTIFS(C:C,“显卡”,A:A,“>=2022-3-1”,A:A,“<=2023-3-31”)

1-4条件统计函数中的通配符

在条件统计函数中，除了使用常规的等于、大于、大于或等于、小于、小于或等于、不等于这种比较运算之外，还可以使用通配符。

通配符有如下三个：

符号	注释
*	表示任意多个字符
？	表示单个字符
~	转义通配符*和~为普通字符

注意：通配符只能对字符串做匹配

例如

采购日期	供应商	商品总类	产品名称	入库数量
2022/2/15	黄乐安	电源	2500W全新	63
2022/2/9	黄乐安	平台	*847平台	1
2022/3/7	黄乐安	显卡	1070 算力33	2
2022/3/10	黄乐安	显卡	1080 算力36	1
2022/1/7	黄乐安	显卡	FHRX588 三星	11
2022/3/3	黄乐安	显卡	ydRTX1660S	1
2022/2/7	黄乐安	显卡	憾讯RX598	1
2022/1/3	黄乐安	主板	B85GW主板	2
2022/2/8	黄乐安	主板	B85GW主板	16
2022/2/27	黄乐安	转接线	6P*6单头	38
2022/2/9	刘雅致	电批	电批*	3
2022/3/4	刘雅致	电源线	nrm电源线	92
2022/2/18	刘雅致	风扇	暴力风扇	100
2022/2/25	刘雅致	风扇	暴力风扇	60
2022/1/31	刘雅致	平台	3865U多路平台	4
2022/2/7	刘雅致	平台	狼神A6	2
2022/1/7	刘雅致	显卡	1070 算力27	2
2022/3/26	刘雅致	显卡	1080 算力27	4
2022/1/22	刘雅致	显卡	1080 算力29	2
2022/3/3	刘雅致	显卡	1080 算力29	3
2022/1/27	刘雅致	显卡	1080 算力36	8
2022/3/18	刘雅致	显卡	1080 算力40	2
2022/3/2	刘雅致	显卡	FHRX588 镁光	12
2022/3/14	刘雅致	显卡	FHRX588 镁光	7
2022/2/26	刘雅致	显卡	ydRTX1660S	1
2022/1/15	刘雅致	显卡	憾讯RX588??	1
2022/3/2	刘雅致	主板	3855带U小插板	1
2022/3/27	刘雅致	主板	3855主板	1
2022/3/21	刘雅致	主板	847PRO主板	1
2022/2/28	路耘豪	电批头	?电批头	7
2022/3/5	路耘豪	电源	2000W全新	381
2022/2/8	路耘豪	电源	多路电源	5
2022/2/2	路耘豪	电源	二手电源	236
2022/3/10	路耘豪	拷盘转接板	拷盘转接板	5
2022/2/16	路耘豪	显卡	1080 算力27	3
2022/3/6	路耘豪	显卡	1080 算力30	4
2022/1/22	路耘豪	显卡	1080 算力33	1
2022/1/2	路耘豪	显卡	FHRX478D 三星	3
2022/3/3	路耘豪	显卡	FHRX478D 三星	7
2022/1/17	路耘豪	显卡	FHRX588 镁光	2
2022/3/15	路耘豪	显卡	jpRX588	3
2022/3/7	路耘豪	显卡	ydRTX1660S	1
2022/2/11	路耘豪	显卡	憾讯RX598	1
2022/2/14	路耘豪	显卡	憾讯RX598	1
2022/1/18	路耘豪	芯片	RX570	28
2022/2/22	路耘豪	硬盘	128G	47
2022/2/20	路耘豪	硬盘	64G	1622
2022/1/15	路耘豪	主板	B85GW主板	10
2022/3/1	路耘豪	主板	B85ZR主板	8
2022/1/1	路耘豪	转接线	暴力风扇转接线	39
2022/3/5	孙若英	风扇	暴力风扇	36
2022/3/14	孙若英	平台	847PRO平台	116
2022/3/11	孙若英	显存	尔必达显存	409
2022/3/7	孙若英	显卡	1070 算力29	6
2022/3/5	孙若英	显卡	1080 算力27	1
2022/2/23	孙若英	显卡	1080 算力30	16
2022/1/21	孙若英	显卡	4改8 三星	1
2022/1/2	孙若英	显卡	FHRX478D 三星	13
2022/1/24	孙若英	显卡	FHRX588 三星	13
2022/3/8	孙若英	显卡	FHRX588 三星	6
2022/1/11	孙若英	显卡	jpRX588	2
2022/3/27	孙若英	主板	847带U小插板	1
2022/2/5	孙若英	主板	K37主板	1
2022/2/2	孙若英	转接线	6P*6双头	43
2022/1/12	孙若英	转接线	6P*8双头	378
2022/2/8	扬飞英	电源	二手电源	43
2022/2/20	扬飞英	电源线	1.5*1.8m电源线	1302
2022/2/24	扬飞英	平台	B85ZR平台	326
2022/2/6	扬飞英	显存	三星	547
2022/3/28	扬飞英	显卡	1070 算力29	2
2022/2/3	扬飞英	显卡	1080 算力29	8
2022/2/13	扬飞英	显卡	FHRX588 镁光	26
2022/3/29	扬飞英	显卡	FHRX588 镁光	14
2022/1/18	扬飞英	显卡	FHRX588 三星	2
2022/3/25	扬飞英	显卡	FHRX588 三星	16
2022/1/5	扬飞英	显卡	jpRX598	2
2022/3/28	扬飞英	显卡	磐镭RX588	1
2022/1/30	扬飞英	硬盘	64G	2
2022/3/15	扬飞英	主板	3865U单路主板	8
2022/1/2	扬飞英	主板	847主板	2
2022/1/7	扬飞英	主板	B85GW主板	20
2022/1/27	扬飞英	主板	B85ZR主板	12
2022/2/3	于安然	显存	海力士	264
2022/2/9	于安然	显卡	1080 算力28	15
2022/2/18	于安然	显卡	FHRX478D 镁光	1
2022/1/1	于安然	显卡	FHRX478D 三星	3
2022/2/5	于安然	显卡	FHRX588 三星	15
2022/2/25	于安然	显卡	憾讯RX588	1
2022/3/16	于安然	显卡	精影RX588	7
2022/3/15	于安然	主板	B85ZR主板	1

要求	方法	函数	结果
商品总类为三个字	???	=COUNTIF(C:C,“???”)	7
产品名称以“三星”结束	*三星	=COUNTIF(D:D,“*三星”)	12
产品名称以“1080”开头	1080*	=COUNTIF(D:D,“1080*”)	13
产品名称包含“588”	588	=COUNTIF(D:D,“588”)	17
产品名称包含“*”	~*	=COUNTIF(D:D,“*~**”)	6
产品名称包含“?”	~?	=COUNTIF(D:D,“~?”)	2

应用：产品名称：FHRX588；采购日期为3月。

方法：=COUNTIFS(D:D,“FHRX588”,A:A,“/3/”)

1-5将条件统计函数中的条件数组化

1、一维数组条件

=SUMIF(采购表!C:C,A2:A14,采购表!E:E)

2、二维数组条件

=SUMIFS(采购表!E:E,采购表!C:C,A2:A14,采购表!B:B,B1:G1)

3、通配符条件组成的条件

型号	占比	方法
FHRX478D	18%	=SUMIF(采购表!D:D,““&A2:A3&””,采购表!E:E)/SUM(SUMIF(采购表!D:D,““&A2:A3&””,采购表!E:E))
FHRX588	82%	=SUMIF(采购表!D:D,““&A2:A3&””,采购表!E:E)/SUM(SUMIF(采购表!D:D,““&A2:A4&””,采购表!E:E))

1-6单条件文本合并-新增函数

文本的合并可以使用函数TEXTJOIN来完成。如果需要有条件的合并数据，则可以使用F或者FILTER函数来执行筛选。

部门	姓名
财务部	张三
财务部	风清扬
销售部	李四
销售部	小七
销售部	祝重八
培训部	曾贤志
研发部	黄大笑
研发部	老六
研发部	欧阳风
研发部	辛飞扬

部门	人数	函数	名单	函数
财务部	2	=COUNTIF(A:A,D2:D5)	张三、风清扬	=TEXTJOIN(“、”,IF(A$2:A$11=D2,B$2:B$11,“”))
研发部	4	=COUNTIF(A:A,D2:D5)	黄大笑、老六、欧阳风、辛飞扬	=TEXTJOIN(“、”,IF(A$2:A$11=D3,B$2:B$11,“”))
培训部	1	=COUNTIF(A:A,D2:D5)	曾贤志	=TEXTJOIN(“、”,IF(A$2:A$11=D4,B$2:B$11,“”))
销售部	3	=COUNTIF(A:A,D2:D5)	李四、小七、祝重八	=TEXTJOIN(“、”,IF(A$2:A$11=D5,B$2:B$11,“”))

1-7多条件与模仿通配符的文本合并

1.多条件合并字符串的写法

2.模仿通配符-指定长度
3.模仿通配符-指定开头关键字

4.模仿通配符-指定结尾关键字

5.模仿通配符-指定包含关键字

第2章：数据分析之合并计算

2-1合并计算-初级应用

1、合并计算的使用计算

2、合并计算注意事项

合并汇总的依据 一定是 汇总区域的第一列

2-2合并计算-通配符应用

1、指定列条件

2、指定行条件

3、行条件为通配符

4、列条件为通配符

5、行列条件为通配符

符号	注释
*	表示任意多个字符
？	表示单个字符
~	转义通配符*和~为普通字符

2-3合并计算-多表合并

1、多表合并

2、多表对比

3、批量分组分级

2-4合并计算-动态合并多表

新建模块

Option Explicit
Sub test2()
Dim Sh As Worksheet, Arr(), intNum As Integer

ReDim Arr(1 To Sheets.Count - 1)

For Each Sh In Sheets
    If Sh.Name <> ActiveSheet.Name Then
        intNum = intNum + 1
        Arr(intNum) = Sh.Name & "!" & Sh.UsedRange.Address(, , xlR1C1)
        
    End If
Next Sh
Range("a2:b99").ClearContents
Range("A1:B1").Consolidate Arr, xlSum, True, True

End Sub

第3章：数据分析之分类汇总

3-1分类汇总-初级应用

1、分类的列必须排序

2、分类汇总的创建于删除

3、分类后的层级关系

例如：接单年月日汇总数量和金额

删除

3-2分类汇总-中级应用

1、对同意列的不同汇总方式

已经汇总过求和，又想汇总计数或者平均值的话，在“分类汇总”那里把下面的“替换当前分类汇总©”选项去掉。

2、不同列的不同汇总方式

例如：数量汇总成平均，金额汇总成求和

先数量跟金额都汇总求和，再把数量求和的公式该为平均值

=SUBTOTAL(1,H2:H6)

需要什么值就找到公式的相应的数字替换

如就和为 =SUBTOTAL(9,I2:I6) 将“(9”替换成"（1"就为平均值。

3、分类汇总中的分页设置

例如：省市进行打页

每页打印有标题

3-3分类汇总-精彩妙用

1、动态给单元格汇总行着色(条件格式)

=ISFORMULA(I2)

=ISFORMULA($I1)

2、用分类汇总做批量分级

3、用分类汇总做批量单元格合并