Excel数据分析 day04

摘要：

判断身份证长度是否正确

• LEN函数
• 如果是或逻辑,使用OR()函数
• 能用数组的地方，可以使用其他方法，只不过使用数组更高效，但是数组的方法不好想出来
• 判断长度为15或者18
  • 数组常量的方法，OR(LEN()={15,18}),选择一个单元格，然后快速填充，不能选中全部的单元格
  • 一般方法，OR(LEN=15,LEN()=18)
  • 数组的方法,IF((LEN(全部单元格)=15)+(LEN(全部单元格))=18)，TRUE,FALSE),如果是或逻辑，就是加，与逻辑就是乘

多条件求和

• 方法1：SUMIFS的数值替代方法，范围与条件之间不是等号，是逗号
• 方法2：数组方法，使用sum+逻辑乘（条件1条件2求和项）
• sum((条件1=x)*(条件2=y)$\ast$(求和项))，Ctrl+shift+enter

判断男女退休否

• 数组的方法，就是使用*+代替and or
• =IF((()=男)$\ast$(()>=60)+(()=女)*(()>=55),“退休”,“未退休”)
• 如果忘记使用Ctrl+shift+enter，搞成了enter，那么使用快速填充吧

多个同类型结果一排

很多个裁判的表决结果，都通过才是通过

• 方法1，AND(1=“通过”,……）
• 方法2，AND+数组的方法，AND(选中一行=“通过”),这儿只能使用Ctrl+shift+enter,所以说，必须牢记Ctrl+shift+enter
• 方法3，*+数组，IF((()=“通过”$\ast$()=“通过”$\ast$……),“不通过”,“不通过”）
• Excel输完数据以后，记住了，你有两个选择的:enter或者Ctrl+shift+enter

数组与sumproduct

• 数组就想着是几维数组乘以几维数组,一维*三维+相对引用的知识

=SUM((A$2:A$12=G2)*B$2:D$12)

• sumproduct("()=()"*"()=()")
  差不多，也要锁定单元格的

Vlookup函数

• lookup_value，查找值，查找到以后作为横坐标
• table_array,表格范围
• col_index_num，正整数，位于第几列，1开始，纵坐标
• range_lookup，True or False，精确查找还是模糊查找
• 横坐标必须位于表格内，横坐标必须位于表格第一列

出生日期提取

• 首先，提取出来数字，
  1.1 快速填充
  1.2 mid函数
• 其次，数字转化为日期
  2.1 数据分列，不要画分割线，直接点默认，最后选择日期就行了
  2.2 --TEXT(old format,"####-##-##"),TEXT是将值以某种文本格式输出，重要的是以某种格式输出，转换为文本数据类型只不过是他的小功能
  推荐方法2，更快

快速填充与mid left right

距离比较远的，用，mid，left ，right，而不是快速填充

OFFSET复制函数

1. 复制一个区域
2. offset（基点，行偏，列偏，行高，行宽），无论基点选择多少个，始终跟左上角的基点有关
3. Ctrl+shift+enter

offset函数可以拿来选择数据范围

OFFSET+SUM,选择范围，并求和

• sum（offset($B$1,1,0,DAY(A2）,1))
• SUM($B$2:B2)
• OFFSET可以依托旁边的列
• 也可以是列表，ROW
• 也可以是数值的个数,COUNT

VLOOKUP与offset，如果方便给一个数值进行查找，就是VLOOKUP，否者就是offset，offset方便定位

Match函数是给值返回坐标

match在小于和大于模式的时候，对于数据的排列是有要求的
小于是查找小于给定值的最大值，正数，必须升序排列
大于是查找大于给定值的最小值，负数，必须降序排列

• match函数只能单行单列查询

match函数应用

判断数组a与数组b有多少数据是相同的，不考虑位置，不考虑大小写，match查找的时候是不考虑大小写的

COUNT+MATCH的方法

• 采用数组的方法
• COUNT(match（范围1，范围2,0）)
• match函数一定要写最后一个参数，也就是查找的数据类型，是精确查找，否者就会出问题

index函数

• index函数是根据索引查找值
• 也只能单行单列
• row_num 就是第几个，跟row没关系，列的话，也是填写这个参数

	match	index
功能	根据数值，查找坐标	根据坐标查找数值
查找范围	单行单列	单行单列

比较奇怪的要求，如果有条件就是VLOOKUP，没有条件就是offset，不会做，就写几个例子，找规律

INDIRECT()函数

INDIRECT()根据地址查找数值
主要的分歧表现在，括号内选择另外一个单元格的时候

• 直接选择另外一个单元格的话，单元格里面的是地址，需要读取单元格的内容，再去寻找
• 加引号写的话，引号里面的直接是地址

二级组合框

1. 公式——名称管理器——新建——名称——一级列表——引用位置
2. 公式——二级列表——根据所选内容创建——首行
3. 数据——数据验证——序列——来源——省直辖市自治区
4. 数据——数据验证——序列——来源——indirect（前面的那个选择框）
   选择内容，根据首行创建的时候，要求精确选择，按住Ctrl选择长度不同的列

Excel图表

• 柱状图横着，就叫条形图了
• 折线图主要分析趋势
• 饼图分析占比，分类太多就不适合使用饼图了
• 散点图适合两种数据之间的关系
• 雷达图，两组数据需要在5-8个是维度进行比较
• 气泡图

大部分点点点就可以了

如何替换x轴数据

选中——右键——数据选择

将数值低的曲线搞到上面去

右键——数据选择

簇状条形图
堆积条形图
百分比堆积条形图

对比两家公司

插入组合图
两个都选择簇状条形图
修改显示范围-7000-7000，修改格式，负数显示成正数
数据标签选为低
如何让他们往两个方向生长？点击刻度那，选择逆序就行了

散点图

系列名称没什么用

方法1（用这个）

选择两列，最后添加数据标签，选择单元

方法2

数据选择，选择系列就是标签，选择x，选择y

气泡图

其余跟散点图一样，就是气泡大小，右击——选择数据——点击编辑，气泡大小，绝对不是新建

各种对比图

子弹图

对比关系，这种图，时间上是两个柱状图，把其中一个的间距拉大（选择次坐标轴），无填充，选择边框就可以了

瀑布图

就是堆积柱状图，其中一个填充选择无填充，涉及到逆序，数据选择

漏斗图——流程流量分析

要看一下是谁+谁等于100，其实也是堆积条形图，部分无填充导致的
Excel2019已经有漏斗图了

增维分析

这个图其实是 扇形图和折线图的组合，把扇形图缩小了放在 折线图上面，选择纯黑原因是为了防止有色差

开发工具

文件——选项——自定义功能区——开发工具

动态图表（核心：offset函数）

主要涉及到的要点是

1. 选择插件：
   1. 数据验证
   2. 开发工具——插入——多级列表，右击设置控件格式
2. offset函数
3. 插入图表
4. 注：多级列表的控件的选择范围，只能是列，不能是行

数据透视表

• 数据透视表，Pivot Table，是一种交互式的表格，可以自由选择多个字段的不同组合，用于快速汇总、分析大量数据中字段与字段之间的关联关系