数据分析精进

LEN，SUBTITUTE函数嵌套使用

LEN，SUBTITUTE函数嵌套使用

辅助行

辅助列

工作簿+工作表单元格 & 字段所在工作簿的单元格地址（涉及到公式下拉右拉，需注意相对引用）

在给定的几组“数组”中，将“数组”间对应的元素相乘，并返回乘积之和。

=SUMPRODUCT(数组1,数组2,数组3,...)

用逗号,分隔 【3个参数】

用乘号*分隔 【1个参数】

N函数返回一个数组，=N（Value）

SUMPRODUCT(N(要转换的值))

=SUMPRODUCT((条件1)*(条件2)*(条件3)*...)

=SUMPRODUCT((条件1)*(条件2)*(条件3)...,求和列)

如果用“,”分隔的参数必须利用N函数，将它转化为数值。
用“*”连接的参数可不必要将区域转化为数值，“*”乘后的结果即为数值。

统计1月份应收金额
=SUMPRODUCT(N($C$6:$C$16="应收")*(MONTH($A$6:$A$16)=1),$D$6:$D$16)

统计公司中包含“北京”的应付总额
=SUMPRODUCT(ISNUMBER(FIND("北京",B6:B16))*(C6:C16="应付"),D6:D16)

温馨提示：在编写函数公式时，为了防止输入错误，建议先输入
=SUMPRODUCT(()*(),()*()),然后进一步在()中补全参数。

通过数据透视表统计，或者利用SUMPRODUCT函数计算

除了多条件嵌套的方法，还可以利用数组的方法来实现。

利用数组做函数统计，需将数组转置为横向数组做乘积;
利用"*"与求和列做分隔，而不能用","

=SUMPRODUCT((公司中的北京分公司)*(订单金额>=当前金额))

共几家分公司
=SUMPRODUCT(1/COUNTIFS(B4:B13,B4:B13))

用"*"通配符来表示任意N个字符串；用"?"通配符表示任意1个字符串

用&""可以把VLOOKUP函数计算为0的结果，强制转换为文本，以空格的形式显示。

=LOOKUP(查找值,查找区域,结果区域)

1.起步线必须是一组升序序列

2.起步线代表：>=起步值

单击函数编辑区将其激活→（从火车车厢第2节开始）单击函数参数中的第二个参数，按Fn+F9键将其转化为数组；
单击函数参数中的第三个参数，按Fn+F9键将其转化为数组→单击√完成函数编辑。

在Excel中，最大的文本为“座”
=LOOKUP(“座”,查找区域)

在Excel中，最大的数值为“9E+307”
=LOOKUP(最大数值,查找区域)

=LOOKUP（1,0/（查找条件），结果区域）
=LOOKUP(1,0/(列区域<>""),列区域)

根据谁查找，就将按升序排序

=LOOKUP(1,0/((销售员姓名为表姐)*(产品类别为A)),业绩总额列)

先对查找条件（此处为时间）进行升序排列

利用排序，将查找最大时间的问题，转化为查找最后一个的问题，
就可以利用LOOKUP函数模式化公式进行查找。

法1

法2

利用定位空值的方法来处理合并单元格

利用辅助列来补充合并的单元格内容

=LOOKUP(9E+307,$D$4:D4)

=OFFSET(起始位置,偏移的行数,偏移的列数,[返回的行数],[返回的列数])

参数的正负分别代表不同的方向；偏移的行数/列数不包括自身，返回的行数/列数包括自身；
第4、5个参数为可选参数，无需求时可忽略，参数为正1或者负1时，依然会处在当前的行列中

避免#VALUE!错误：SUM+OFFSET嵌套

需求分析

数据提取及排序

公式→定义名称，在弹出的对话框中，新建一个名称，如：动态的做图数据源，
输入公式：=OFFSET(学生工作区!$H$3,0,0,学生工作区!$R$2,1)

公式→定义名称，在弹出的对话框中，新建一个名称，如：做图的客户名称，
在引用位置中输入公式：=OFFSET(学生工作区!$G$3,0,0,学生工作区!$R$2,1)

选中图表，界面上方会出现图表工具，点击设计下的选择数据，然后点击图例项（系列）下的编辑，
弹出的对话框中，定位在系列值下的输入框内，删除学生工作区！后面的$H$3:$H$17，Fn+F3调出
粘贴名称的对话框，选择定义好的名称：动态的做图数据源，确定即可。

同理，继续更改客户名称的数据源

选中图表，点击设计，在图表样式中一个样式

更改柱形的颜色，选中全部柱形，点击格式，接着点击形状填充，选择一个颜色

动态数据源设置条件格式，直观看出变化的趋势，同时加强数据的表达力

调整图表的字体和字号，更改图表标题及标题的字号

按住Alt键，拖动图表外边框对齐网格线，最后调整表格大小，并将图表拖动到适当的位置即可

利用IF函数构建虚拟数组

利用【Ctrl+Shift+Down】快速选中指定区域，【F4】切换引用方式

QUARTILE函数求四分位点

数组显示形式

法1：MAXIFS函数

法2:数组公式

法3：LOOKUP查找最大时间

法1:数据透视表

法2:数组公式

操作要点

人员基础信息

计时计件类基本工资

各类津贴补贴

考勤加扣

加班类核算

税前补发补扣

社保、公积金缴费基数

社保公积金核算表

汇总

个税核算表

其他免税收入

专项附加扣除

税后补发补扣

其他补发补扣

参数1：查找值
参数2：升序序列，匹配查找
参数3：返回列，按照升序序列中确定的值返回对应的值

一边绝对引用，一边相对引用
=SUM($D$5:D5)

累计预扣预缴应纳税所得额=累计应发薪资-累计减免

税率、速算扣除数（利用lookup函数匹配得出）

累计已预扣预缴税额

当月个税=累计预扣预缴应纳税所得额*税率-速算扣除数-累计已预扣预缴税额

=TEXT(MID(R2,7,8),"0000-00-00")

=DATEDIF(S2,TODAY(),"Y")

先框选要计算的单元格区域C3:N14，点击数据选项卡下的合并计算按钮。
对话框中的引用位置，依次添加各部门的工作表中的数据区域。最后，勾选创建指向源数据的链接。

给所有汇总行加上不同颜色的底纹，加以区分
点击筛选按钮→文本筛选→不包含，在自定义筛选方式对话框中，点击不包含科目后面，手动输入“部”字，即显示不包含“部”字的内容，
则明细数据全部被隐藏，只留下汇总数据。按键盘Alt+；即可只选中可见区域，然后再给它填充上喜欢的颜色即可。

=SUMPRODUCT(ISNUMBER(FIND("部",$B$3:$B$62))*C3:C62)

select 日期,物料编码,产品型号,包装编码,入库数量,出库数量,入库出库人姓名 from
(select 日期,物料编码,产品型号,包装编码,入库数量,0 as 出库数量,入库出库人姓名 from [入库$] union all
select 日期,物料编码,产品型号,包装编码,0 as 入库数量,出库数量,入库出库人姓名 from [出库$]) 
order by 日期

选择开始选项卡→单击选项按钮。在弹出的Excel选项对话框→单击快速访问工具栏按钮→
选择从下列位置选择命令中的所有命令选项→选择数据透视表和数据透视图向导选项→单击
添加按钮，这时右侧自定义快速访问工具栏中可以看到数据透视表和数据透视图向导→单击确定按钮。

除了通过快捷按钮外，还可以通过按<ALT+D+P>快捷键的方式来快速调用数据透视表和数据透视图向导

页字段数目：透视表筛选页数目

将筛选区的姓名字段拖拽到行区域，这样所有销售员的数据统计在一张表格中了

EXCEL选项对话框→单击高级按钮→在常规中→单击编辑自定义列表按钮。
选项对话框中，单击导入输入框将其激活，然后选择2018年工作表中C2:C13单元格区域，单击导入按钮

选中行标签中任意单元格，右击→选择排序选项→选择其他排序选项选项。
在排序对话框中选择升序排序选项→单击其他选项按钮。在弹出的其他排序选项对话框→
取消每次更新报表自动排序复选框→在主关键字排序顺序中选择男装-女装...顺序→单击确定按钮。

将筛选区的页字段，拖拽到行标签，放在行的上方。
修改标签名称。
修改透视表布局。

利用数据透视表多重合并计算完成两张表格的合并。

将页1字段拖拽至列标签，数据透视表中预算与执行情况即可并排放置。

选中数据透视表中任意单元格，选择设计选项卡→单击分类汇总按钮→选择不显示分类汇总选项

选择设计选项卡→单击总计按钮→选择对行和列禁用选项，这时，通过数据透视表就可以比对预算和执行两组数据的差异了。

在弹出的数据透视表和数据透视图向导--步骤2a对话框→选择创建单页字段选项→单击下一步按钮。

单击选定区域输入框将其激活→拖拽鼠标选中B1:F11单元格区域→单击添加按钮→单击下一步按钮。

在弹出的数据透视表和数据透视图向导--步骤3对话框→选择现有工作表选项→单击输入框建其激活，选中H1单元格→单击完成按钮。

在数据透视表中，将所有字段取消选中，然后将值字段拖拽到行标签，即可获取到所有销售员姓名的唯一值了。

在二维表工作表中，按<ALT+D+P>键→在弹出数据透视表和数据透视图向导中选择多重合并计算
数据区域→单击下一步按钮。在弹出的新对话框中选中自定义页字段选项→单击下一步按钮。
在弹出的数据透视表和数据透视图向导-第2b步对话框→单击选定区域输入框将其激活，选择二维表工作表中
全部数据区域，页字段数目选0→单击添加按钮→单击下一步按钮。在弹出的数据透视表和数据透视图向导-步
骤3对话框→选择新工作表选项→单击完成按钮，即可生成一张与二维表格结构相同的数据透视表。

将二维表格转化为数据透视表后，利用透视表“查看数据明细”功能，将表格数据转化为一维表格。
选中G12单元格（总计）→双击汇总值，如图所示，生成一张明细表，也就是我们需要的一维表格。

修改标签名称

字体设置为微软雅黑，居中对齐，调整字号。

将透视表转化为普通表：
选中透视表中任意单元格→选择设计选项→单击转化为区域按钮→单击是按钮。

筛选汇总行，Alt+；删除整行

选中单元格区域→选择开始选项卡→单击合并后居中按钮→选择取消合并单元格选项。
然后按<CTRL+G>键调用出定位对话框→单击定位条件按钮，选择空值选项→单击确定按钮。
连续输入四个按键，=+↑+Ctrl+Enter，将公式批量填充到空白单元格中。
Ctrl+Enter，即可批量填充

利用分隔符“|”将大类与小类连接起来。选中A3单元格，输入函数=B3&"|"&C3，按<Enter>键确认输入，
然后拖拽鼠标至A14单元格

同行字段处理方式，定位条件对话框中→选择空值→单击确定。

利用“|”将年份与月份连接

①按<ALT+D+P>键→在弹出数据透视表和数据透视图向导中选择多重合并计算数据区域→单击下一步按钮。
②在弹出的数据透视表和数据透视图向导--步骤2a话框→选中自定义页字段选项→单击下一步按钮。
③在弹出的数据透视表和数据透视图向导-第2b对话框中→单击选定区域输入框将其激活，然后选择多维工作表页签，
拖拽鼠标选中此工作表中全部数据区域→然后单击添加按钮。
④在弹出的数据透视表和数据透视图向导--步骤3对话框→选择新工作表选项→单击完成按钮。
⑤即可生成一张二维表格同样结构的数据透视表，双击透视表右下角汇总值，即可新建一张一维的数据透视表明细。

①将A列大类和小类按照“|”分隔开。
选中A列→选择数据选项卡→单击分列按钮→在弹出的文本分列向导对话框中选择分隔符号分列选项→单击下一步按钮。
在弹出的文本分列向导对话框→ 选中其他复选框→在输入框中输入|→单击下一步按钮。
单击目标区域输入框将其激活，选中D1单元格，单击完成按钮。修改字段名称。
②将B列年份月份分列处理。插入一列空白列，选中C列→右击选择插入选项。
在弹出的文本分列向导对话框→选择分隔符号分列选项→单击下一步按钮。
选中其他复选框→在输入框中前面已经输入了“|”符号，这里我们直接单击下一步按钮。
接着将字段名称修改为实际的字段名称。

设置字体字号，对齐方式等

调整列顺序：选中D:E两列，按住<SHIFT>键→拖拽这两列至A列位置

ADDRESS函数是获得单元格地址的函数。
=ADDRESS(行号,列号,引用类型,引用样式,"工作表名称")

INDIRECT函数是将文本字符串转化为区域，并引用它的值
=INDIRECT("文本字符串")

=INDIRECT(引用单元格)

=INDIRECT("'1101现金'!L1")，按<Enter>键确认。
不变的是'和'!L1，变化的是1101现金工作表

选择性粘贴、条件格式 + 利用MOD函数判断奇偶 —— 制作深浅条纹表格

实用函数VLOOKUP

多条件求和函数 — SUMIFS函数

AVERAGE函数构建辅助列

三个“；；；”隐藏数值

逆序辅助列的构建

SUM函数求和

另类会计单元格格式设置