|
|
发表于 2023-9-18 11:13:42
|
显示全部楼层
花了一个月时间整理了3万字的Excel数据分析技巧，全程干货无废话！Excel有强大的数据处理和数据分析能力，Excel数据分析技巧也有很多，如Excel公式和函数、Excel数据处理、数据透视表、统计分析功能、可视化等等...
从整体来看，Excel知识框架大致如下。
而作为一名数据分析师，则至少需要掌握以下几个方面的技巧，下面我将从以下六个方面详细为大家展开。内容有点多，建议大家点赞+收藏+喜欢，希望对大家会有所帮助！
一、Excel公式和函数
二、Excel数据处理
三、数据透视表
四、统计分析功能
五、可视化
六、Excel数据分析案例解析
知乎已经陆续更新了七大专题，分别是：分析工具-Excel篇、大数据行业应用篇、分析工具-SQL篇
分析工具-Python篇、数据分析方法篇、机器学习篇、Pandas篇，想学习的朋友可以关注下！
一、Excel公式和函数
Excel函数学会50%，就很强大！Excel函数，不是数学概念里的方程式，它是Excel表格里的超级计算器，机械重复的人工计算，函数一条公式就能解决。
vlookup大家应该知道，而Excel 有哪些和 vlookup 一样重要的函数或功能呢？
总结了数据分析常用的43个函数，为大家系统的进行讲解，什么是函数、什么是公式、函数与分类、常用函数有哪些，掌握了这些技能会让你的数据处理能力翻倍。
01什么是函数？
它是由Excel内部预先定义并按照特定的顺序、结构来执行计算、分析等数据处理任务的功能模块。因此，函数被人们称为“特殊公式”，与公式一样，Excel函数的最终返回结果为值。函数只有唯一的名称且不区分大小写，它决定了函数的功能和用途。
举个简单的例子——处理表格时，如何把名字首个字母全部变成大写？
不懂函数的你，是不是一个个手动修改？如果你知道Proper这个函数，就不会一个个修改了。输入函数公式，3秒搞定！
02什么是公式？
公式就是由用户自行设计并结合常量数据、单元格引用、运算符等元素进行数据处理和计算的算式。用户使用公式是为了有目的地计算结果，因此Excel的公式必须(且只能)返回值。
公式的结构：=(C2+D2)*5从公式结构来看，构成公式的元素通常包括等号、常量、引用和运算符等元素。其中，=号是不可或缺的。但在实际应用中，公式还可以使用数组、Excel函数或名称(命名公式)来进行运算。
| 公式中的运算符 | | 符号 | 说明 | 实例 | | - | 算数运算符：负号 | =8*-5=-40 | | % | 算数运算符：百分号 | =60*50%=30 | | ^ | 算数运算符：乘幂 | =3^2=9 | | *和/ | 算数运算符：乘和除 | =3*2/4=1.5 | | +和- | 算数运算符：加和减 | =3+2-5=0 | | =,<>,>,<,>=,<= | 比较运算符：等于、不等于、大于、小于、大于等于、小于等于 | =(A1=A2)判断A1与A2相等=(B1<>“ABC”) 判断B1不等于“ABC” | | ＆ | 文本运算符：连接文本 | =“Excel”＆“Home”=“ExcelHome” | | : | 区域运算符：冒号 | =SUM(A1:A10) | | 空格 | 交叉运算符：单个空格 | =SUM(A1:B5 A4:D9)相当于=SUM(A4:B5) | | , | 联合运算符：逗号 | =RANK(A1(A1:A10,C1:C10)) |
通常情况下，Excel按照从左向右的顺序进行公式运算，当公式中使用多个运算符时，Excel将根据各运算符的优先级进行运算，对于同一级次的运算符，则按自左而右的顺序运算。具体的优先顺序如下表：
| 运算符优先级 | | 序号 | 符号 | | 1 | : 空格 ， | | 2 | - | | 3 | % | | 4 | ^ | | 5 | *和/ | | 6 | +和- | | 7 | ＆ | | 8 | =,<>,>,<,>=,<= |
在使用Excel公式进行计算时，可能会因为某种原因无法得到正确结果，而返回一个错误值。常见的错误值及其含义如下表所示。
| 错误值类型 | 含义 | | ##### | 当使用了负的日期或负的时间时，出现错误 | | #VALUE! | 当使用的参数或操作数类型错误时，出现错误 | | #DIV/O! | 当数字被零(0)除时，出现错误 | | #NAME? | 当Excel未识别公式中的文本时，出现错误 | | #N/A | 当数值对函数或公式不可用时，出现错误 | | #REF! | 当单元格引用无效时，出现错误 | | #NUM! | 公式或函数中使用无效数字值时，出现错误 | | #NULL! | 当指定并不相交的两个区域的交点时，出现错误 |
当公式的结果返回错误值时，应该及时地查找错误原因，并修改公式来解决问题。
03函数与分类
Excel函数通常是由函数名称、左括号、参数、半角逗号和右括号构成。
函数公式结构：=IF(A1>0,”正数”，IF(A1<0,负数,””))对于函数的参数来说，可以由数值、日期和文本等元素组成，也可以使用常量、数组、单元格引用或其他函数。
当函数的参数也是函数时，Excel称之为函数的嵌套。函数一共有11类，分别是数据库函数、日期与时间函数、工程函数、财务函数、信息函数、逻辑函数、查询和引用函数、数学和三角函数、统计函数、文本函数以及用户自定义函数。
04常用函数
本文内容为目录式的，介绍每个函数是做什么的、遇到某个问题可以用哪个函数解决等，具体使用方法各位可以自行百度学习。
对于函数，不用死记硬背，只需要知道应该选取什么类别的函数，以及需要哪些参数怎么用就行了！比如选取字段，用LEFT／RIGHT／MID函数......其他细节交给万能的百度吧！
下面根据不同的运用场景，对这些常用的必备函数进行分类介绍。
（1）关联匹配类
需要的数据不在同一个Excel表或同一个Excel表不同sheet中，数据太多copy起来麻烦还容易出错，如何整合呢？下面这些函数就是用于多表关联或者行列比对时的场景，而且表格越复杂，用起来越爽！
01.VLOOKUP
功能：用于查找首列满足条件的元素。
语法：=VLOOKUP (lookup_value,table_array, col_index_num, [range_lookup])
*备注：[ ]内为可选参数，其余为必需参数，下文同理。
=VLOOKUP (要查找的项、要查找位置、区域中包含要返回的值的列号、返回近似匹配或精确匹配 - 指示为 1/TRUE 或 0/FALSE) 。
例：查询姓名是F5单元格中的员工是什么职务。
02.HLOOKUP
功能：在表格的首行或数值数组中搜索值，然后返回表格或数组中指定行的所在列中的值。HLOOKUP中的H代表“行”。
语法：=HLOOKUP(lookup_value,table_array, row_index_num, [range_lookup])
例：=HLOOKUP(&#34;车轴&#34;,A1:C4, 2, TRUE) 在首行查找车轴，并返回同列（列A）中第2行的值。
LOOKUP和HLOOKUP区别：当比较值位于数据表格的首行时，如果要向下查看指定的行数，则可使用HLOOKUP。当比较值位于所需查找的数据的左边一列时，则可使用VLOOKUP。
03.INDEX
功能：返回表格或区域中的值或值的引用。
语法：=INDEX(array,row_num, [column_num])
例：= INDEX(B2:D11,3,3) 位于区域A2:B3中第三行和第三列交叉处的数值。
04.MATCH
功能：用于返回指定内容在指定区域（某行或者某列）的位置。
语法：=MATCH(lookup_value,lookup_array, [match_type])
例：=MATCH(41,B2:B5,0) 单元格区域B2:B5中值41的位置。
match_type：
- 1或省略：MATCH 查找小于或等于lookup_value的最大值。
- 0：MATCH查找完全等于lookup_value的第一个值。
- -1：MATCH查找大于或等于lookup_value的最小值。
05.RANK
功能：求某一个数值在某一区域内一组数值中的排名。
语法：=RANK(number,ref,[order])
例：=RANK(A3,A2:A6,1) A3在上表中A2:A6的排位排名方式：0是降，1是升序，默认为0
06.ROW
功能：返回引用的行号。
语法：= ROW([reference])
例：= ROW() 公式所在行的行号
07.COLUMN
功能：返回单元格所在的列。
语法=COLUMN（reference)
例：=COLUMN (D10) 返回4，因为D列是第四列。
08.OFFSET
功能：返回对单元格或单元格区域中指定行数和列数的区域的引用。返回的引用可以是单个单元格或单元格区域。可以指定要返回的行数和列数。
语法：=OFFSET(reference, rows, cols,[height], [width])
例：=OFFSET(D3,3,-2,1,1)显示单元格 B6中的值，其中3为下方三行，-2为左方两行，1为行高和列宽。
（2）清洗处理类
数据处理之前，需要对提取的数据进行初步清洗，如清除字符串空格，合并单元格、替换、截取字符串、查找字符串出现的位置等。
- 截取字符串：使用MID /LEFT/ RIGHT
- 替换单元格中内容：SUBSTITUTE /REPLACE
- 合并单元格：使用CONCATENATE
- 清除字符串空格：使用TRIM/LTRIM/RTRIM
- 查找文本在单元格中的位置：FIND/ SEARCH
09.MID
功能：从中间截取字符串
语法：=MID(text,start_num, num_chars)
例：=MID(A2,1,5) 从A2内字符串中第1个字符开始，返回5个字符。
根据身份证号码提取年月。
10.LEFT
功能：从左截取字符串。
语法：=LEFT(text,[num_chars])
例：=LEFT(A2,4) 第一个字符串中的前四个字符。
11.RIGHT
功能：从右截取字符串。
语法：=RIGHT(text,[num_chars])
例：=RIGHT(A2,5)第一个字符串的最后5个字符
12. SUBSTITUTE
功能：在文本字符串中用new_text替换old_text。
语法：=SUBSTITUTE(text,old_text, new_text, [instance_num])
例：=SUBSTITUTE(A2, &#34;销售&#34;, &#34;成本&#34;)将“销售”替换为“成本”（成本数据）替换部分电话号码。
13.REPLACE
功能：替换掉单元格的字符串。
语法：=REPLACE(old_text,start_num, num_chars, new_text)
例：=REPLACE(A2,6,5,&#34;*&#34;) 在A2中，从第六个字符(f)开始使用单个字符*替换五个字符。
REPLACE和SUBSTITUTE区别：两个函数很接近，不同在于REPLACE根据位置实现替换，需要提供从第几位开始替换，替换几位，替换后的新的文本；而SUBSTITUTE根据文本内容替换，需要提供替换的旧文本和新文本，以及替换第几个旧文本等。因此REPLACE实现固定位置的文本替换，SUBSTITUTE实现固定文本替换。
14.CONCATENATE
功能：将两个或多个文本字符串联接为一个字符串。
语法：=CONCATENATE(text1,[text2], ...)
合并单元格中的内容，还有另一种合并方式是&，需要合并的内容过多时，CONCATENATE 效率更快。
例：=CONCATENATE(B2, &#34; &#34;, C2) 联接三部分内容：单元格B2中的字符串、空格字符以及单元格C2中的值。
15.TRIM
功能：除了单词之间的单个空格之外，移除文本中的所有空格。
语法：=TRIM(text)
Text为要去掉空格的文本。
例：=TRIM(&#34;First Quarter Earnings &#34;) 从公式的文本中移除前导空格和尾随空格。
16.LTRIM
功能：从字符串左侧删除空格或其他预定义字符。
语法：=LTRIM (string, [charlist])
17.RTRIM
功能：从字符串右侧删除空格或其他预定义字符。
语法：= LTRIM(string, [charlist])
18.FIND
功能：查找文本位置
语法：=FIND(find_text,within_text, [start_num])
例：=FIND(&#34;M&#34;,A2) 单元格A2中第一个“M”的位置
19.SEARCH
功能：返回一个指定字符或文本字符串在字符串中第一次出现的位置,从左到右查找。
语法：=SEARCH(find_text,within_text,[start_num])
例：=SEARCH(&#34;e&#34;,A2,6) 单元格A2中的字符串中，从第6个位置起，第一个“e”的位置。
FIND和SEARCH区别：这两个函数功能几乎相同，实现查找字符所在的位置，区别在于FIND函数精确查找，区分大小写；SEARCH函数模糊查找，不区分大小写。
20.LEN
功能：返回文本字符串中的字符个数。
语法：=LEN(text)
例：=LEN(A1) A1单元格字符串的长度
21. LENB
功能：返回文本字符串中用于代表字符的字节数。
语法：=LENB(text)
例：=LEN(A1)A1单元格字符串的字节数。
（3）逻辑运算类
逻辑，顾名思义，不赘述，直接上函数。
22.IF
功能：使用逻辑函数IF 函数时，如果条件为真，该函数将返回一个值；如果条件为假，函数将返回另一个值。
语法：=IF(Logical,Value_if_true,Value_if_false)
如果指定条件的计算结果为true，IF函数将返回某个值；如果该条件的计算结果为false，则返回另一个值。
23.COUNTIF
功能：用于统计满足某个条件的单元格的数量；例如，统计特定城市在客户列表中出现的次数。
语法：=COUNTIF(单元格1: 单元格2 ,条件)
统计特定店铺在列表中出现的次数。
24.AND
功能：逻辑判断，相当于“并”。
语法：全部参数为True，则返回True，经常用于多条件判断。
例：=AND(A2>1,A2<100) 如果A2大于1并且小于100，则显示TRUE；否则显示FALSE。
25.OR
功能：逻辑判断，相当于“或”。
语法：只要参数有一个True，则返回Ture，经常用于多条件判断。
例：=OR(A2>1,A2<100) 如果A2大于1或者小于100，则显示TRUE；否则显示FALSE。
（4）计算统计类
在利用Excel表格统计数据时，常常需要使用各种Excel自带的公式，也是最常使用的一类。（对于这些，Excel自带快捷功能）
26.MIN
功能：找到某区域中的最小值。
语法：=MIN(number1, [number2], ...)
例：=MIN(D2:D11) 区域D2:D11中的最小数。
27.MAX
功能：找到某区域中的最大值。
语法：=MAX(number1, [number2], ...)
例：=MAX(A2:A6) 区域A2:A6中的最大值。
28.AVERAGE
功能：计算某区域中的平均值。
语法：=AVERAGE(number1, [number2], ...)
例：=AVERAGE(D2:D11) 单元格区域D2到D11中数字的平均值。
29.COUNT
功能：计算含有数字的单元格的个数。
语法：=COUNT(value1, [value2], ...)
例：=COUNT(A2:A7) 计算单元格区域A2到A7中包含数字的单元格的个数。
30.COUNTIFS
功能：统计一组给定条件所指定的单元格数。
语法：COUNTIFS(criteria_range1,criteria1, [criteria_range2, criteria2],…)
例：=COUNTIFS(A2:A7,&#34;<6&#34;,A2:A7,&#34;>1&#34;)计算1和6之间（不包括1和6）有几个数包含在单元格A2到A7中。
31.SUM
功能：计算单元格区域中所有数值的和。
语法：=SUM(单元格1:单元格2)
例：=SUM(A2:A10) 将单元格A2:10中的值加在一起。
32.SUMIF
功能：求满足条件的单元格和。
语法：=SUMIF(range,criteria, [sum_range])
例：=SUMIF(A2:A7,&#34;水果&#34;,C2:C7) “水果”类别下所有食物的销售额之和。
32.SUMIFS
功能：对一组满足条件指定的单元格求和。
语法：=SUMIFS(sum_range,criteria_range1, criteria1, [criteria_range2, criteria2], ...)
例：=SUMIFS(A2:A9, B2:B9, &#34;=香*&#34;, C2:C9, &#34;卢宁&#34;) 计算以“香”开头并由“卢宁”售出的产品的总量。
33.SUMPRODUCT
功能：返回相应的数组或区域乘积的和。
语法：=SUMPRODUCT (array1， [array2]， [array3]， ...)
例：=SUMPRODUCT(Table1!A1:Table1!A100,Table2!B1:Table2!B100) 计算表格1的A1到A100与表格2的B1到B100的乘积和，即A1*B1+A2*B2+A3*B3+…
34.STDEV
功能：基于样本估算标准偏差。
语法：STDEV(number1,[number2],...)
例：=STDEV(D2:D17) 列的标准偏差
35.SUBSTOTAL
功能：返回列表或数据库中的分类汇总。
语法：=SUBTOTAL(function_num,ref1,[ref2],...)
例：=SUBTOTAL(9,A2:A5)使用9作为第一个参数,算出的单元格A2:A5中分类汇总的值之和。
http://36.INT／ROUND
功能：ROUND 函数将数字四舍五入到指定的位数。
语法：=ROUND(A1, 2)
例：=ROUND(2.15, 1)将2.15四舍五入到一个小数位
功能：INT将数字向下舍入到最接近的整数。
语法:=INT(8.9) 将 8.9 向下舍入到最接近的整数。
（5）时间序列类
专门用于处理时间格式以及转换。
37.TODAY
功能：返回当前日期的序列号。
语法：=TODAY()
li&#39;z=TODAY()+5返回当前日期加5天。例如，如果当前日期为1/1/2012，此公式会返回1/6/2012。
38.NOW
功能：返回当前日期和时间的序列号。
语法：=Now()
=NOW()+7 返回7天后的日期和时间。
39.YEAR
功能：返回对应于某个日期的年份。
语法：=YEAR(serial_number)
=YEAR(A3) 单元格A3中日期的年份
40.MONTH
功能：返回日期中的月份。
语法：=MONTH(serial_number)
=MONTH(A2) 单元格A2中日期的月份
41.DAY
功能：返回以序列数表示的某日期的天数。
语法：=DAY(serial_number)
=DAY(A2) 单元格A2中日期的天数
42.WEEKDAY
功能：返回对应于某个日期的一周中的第几天。默认情况下，天数是1（星期日）到7（星期六）范围内的整数。
语法：=WEEKDAY(serial_number,[return_type])
=WEEKDAY(A2) 1（星期日）到7（星期六）一周中的第几天
=WEEKDAY(A2, 2) 1（星期一）到7（星期日）一周中的第几天。
43.DATEDIF
功能：计算两个日期之间相隔的天数、月数或年数。
语法：=DATEDIF(start_date,end_date,unit)
=DATEDIF(Start_date,End_date,&#34;Y&#34;)一段时期内的年数
=DATEDIF(Start_date,End_date,&#34;D&#34;)一段时期内的天数
=DATEDIF(Start_date,End_date,&#34;YD&#34;)忽略日期中的年份，一段时期内的天数
附：
| 函数参数或其他常用单词 | | Sheet(Worksheet) | 工作表 | | Workbook | 工作簿 | | Cell | 单元格 | | TRUE | 真 | | FALSE | 假 | | Logical_test | 逻辑判断式 | | Value | 值 | | Value if true | 如果为真 | | Value if false | 如果为假 | | Logical | 逻辑 | | Value if error | 如果错误 | | Function num | 函数编号 | | Number | 数字 | | Ref (reference) | 涉及的内容 | | Range | 范围 | | Criteria | 标准 | | Sum range | 求和的范围 | | Digits | 数字 | | Divisor | 除数 | | Lookup | 查找 | | Lookup value | 查找的值 | | Array | 数组、区域、阵列 | | Col index num (Col=Column) | 索引的列号 | | Num (Number) | 数字的缩写 | | Type | 类型 | | Text | 文本 | | Num chars | 字符数量 |
<hr/>二、如何用Excel做数据透视表？
数据透视表的强大之处在于其仅靠鼠标移动字段位置，即可变换出各种类型的报表。它是解决函数公式速度瓶颈的手段之一，被称为最常用、功能最全的Excel数据分析工具之一。
01关于数据透视表
数据透视表是用来从Excel数据列表、关系数据库文件或OLAP多维数据集中的特殊字段中总结信息的分析工具。
它是一种交互式报表，可以快速分类汇总和比较大量的数据，并可以随时选择其中页、行和列中的不同元素，以快速查看源数据的不同统计结果，同时还可以随意显示和打印出你所感兴趣区域的明细数据。
数据透视表有机地综合了数据排序、筛选、分类汇总等数据分析的优点，可方便地调整分类汇总的方式，灵活地以多种不同方式展示数据的特征。
02数据透视表的用途
Excel 数据透视表是职场中高频使用的表格工具之一，但是很多人对于数据透视表的主要功能和定位很模糊，导致很多明明可以用数据透视表解决的问题，却只能用其他费力费时的方式解决。
那么数据透视表是一个什么样的工具？数据透视表能帮助用户分析，组织数据，例如，计算平均数、标准差，建立列联表、计算百分比、建立新的数据子集等。
建好数据透视表后，可以对数据透视表重新安排，以便从不同的角度查看数据。
数据透视表的名字来源于它具有“透视”表格的能力，从大量看似无关的数据中寻找背后的联系，从而将纷繁的数据转化为有价值的信息，以供研究和决策所用。
总之，合理运用数据透视表进行计算与分析，能使许多复杂的问题简单化并极大地提高工作效率。
03创建数据透视表
使用数据透视表向导可以创建数据透视表，启用此向导的方法是单击Excel菜单栏中的插入，选择数据透视表。在该向导的指导下，用户只要按部就班地一步一步进行操作，就可以轻松地完成数据透视表的创建。
它的操作步骤共分为3步。
步骤1：插入透视表、选择数据源类型。
步骤2：选择数据源区域、指定数据透视表位置。
步骤3：选择数据透视表字段。
示例：这里有一份2011年某超市五家门店的全年销售数据，那么如何根据这一份销售明细，快速汇总每家门店的销售额呢？想跟着练习的朋友可以下载下原始数据。
下载地址：https://pan.baidu.com/s/18mW2Cgd3Nqfen8FoJmcZCA
提取码：n9f1
样例数据如下：
在学习了Excel函数后，常规的做法是使用SUMIF函数，如果对于多个条件，则使用SUMIFS函数进行数据汇总求和。
使用函数求解，效率较慢，这里使用数据透视表功能三步就能搞定。
第一步、选择数据源的任意单元格位置，在插入选项卡下选择数据透视表。
第二步、选择生成数据透视表的区域，这里选择生成到一个新的工作表。
第三步、在数据透视表字段中，将门店拖入到行，销售额拖入到值进行汇总，即可得到汇总结果。
小tips：更改源数据，记得在数据透视表里点击刷新。
04十大数据透视表功能及技巧
学会创建数据透视表基础操作后，跟随我完成进阶学习，掌握这十大技巧，能让你的效率翻倍！
（1）数值排序
右键需要排序的变量，在排序里面选择降序或者升序排列。
（2）值汇总依据
右键需要汇总的变量，在值汇总依据选择需要汇总的方式，默认对于数值型的字段做求和汇总，对于文本型的字段做计数汇总。
（3）值显示方式
默认对于数据字段是无计算的，如果要计算每一个门店销售额占总销售额百分比情况，可以右键，在值显示方式中选择总计的百分比，即可得到汇总的结果。
（4）计算字段
选择数据透视表任意位置，在数据透视表分析选项卡下选择字段、项目和集，插入一个计算字段。
插入计算字段，自定义一个名称为销售单价，如：=销售额/订单量，其中销售额和订单量需要插入字段，点击添加完成计算字段，点击确定即可生成。
（5）数据分析表设计
如果我们想看各个门店的产品销售额情况，并且产品后面需要匹配各个类别，那么在行里面同时拖入门店产品类别和类别编号。
生成明细后我们发现，由于在行里面添加了类别，数据明细是按行呈现，这与我们常规的表格按列分布不符，需要对数据透视表进行调整。
点击数据透视表，在设计里面选择报表布局选项卡，选择以表格形式显示。
然后在设计里面选择分类汇总选项卡，选择不显示分类汇总。
即可得到数据表格更改结果，从左边的表变为右边的表。
（6）手动分组
当我们将字段拖入到行时，Excel数据透视表会默认自动分组，比如这里要数据透视2011年每个门店的每月的订单量和销售额，将订单日期拖入到行会自动按照月/季度/年分组。
如下是所有门店每个月的订单量和销售额数据透视结果。
如果我们要将按月排列改为按季度，可以选择要手动分组的区域，右键点击组合。
在组合里面选择季度/年，可以手动分组。
分组结果如下所示。
（7）报表筛选页功能
上面得到所有门店手动分组后每一个季度的订单量和销售额，若需要得到每一个门店的具体明细，我们选择数据透视表，在数据透视表分析选项卡下，点击显示报表筛选页。
选定要显示的报表筛选页字段，比如这里选择的门店名称字段是之前创建数据透视表时添加到筛选区域的字段。
如下得到了每一个门店每一个季度的订单量和销售额情况，具体明细分发到了每一个单独的Sheet表中。
（8）数据透视表图表制作
我们需要对门店的订单数量和销售额进行可视化，选取数据区域，在数据透视表分析选项卡下插入一个数据透视图。
插入一个组合图，销售额这里选择折线图，并选择次坐标轴。
创建图形后，发现图表有很多字段按钮，这里可以右键点击隐藏图表上的值字段按钮、隐藏图表上的所有字段按钮。
图表格式调整，关于图表格式调整会专门在Excel数据可视化内容里讲解，经过图表格式调整后如下。
（9）插入切片器
如果要看门店每个月的订单量和销售额情况，可以在数据透视表分析选项卡下插入切片器。
插入切片器，这里选择销售日期，点击确定。
在切片器中筛选时，也会有不需要的项目以灰色状态显示。如何把不需要的项目隐藏呢？
隐藏多余项目的方法：选中切片器，点击切片器设置，勾选隐藏没有数据的项即可将多余的项目隐藏。
问题来了，你发现切片器中月份排序无论是升序还是降序顺序都是乱的，如何解决这种问题呢？
跟着下面的操作步骤，设置好后，再次进行排序时，就可以按我们习惯的要求来进行排序了。点击文件-选项-高级-编辑自定义列表-导入-确定来解决。
最后，选中切片器，点击切片器工具选项，列数选择6，可以将切片器切为2行6列的横排切片器。
如下就是数据切片后的结果。
（10）数据透视表合成仪表看板
如果掌握了以上的内容，就不难将下方原始数据表格合成仪表看板。之前的文章已经详细讲解过制作步骤，大家感兴趣可以去看下。
10分钟教会你用数据透视表合成仪表看板【含视频教程】
<hr/>三、数据处理
拿到的数据通常不是完美无瑕的，而是经常出现缺失值、错误值等，这时摆在数据分析师面前的两座大山，便是“数据清洗”和“数据加工”，翻过这两座大山之后才可以顺畅地进行接下来的具体的数据分析、数据可视化等操作。
导入数据是数据处理的先行军，而Excel中支持导入的数据类型可以分为三类：
文本类数据、网站类数据、数据库类数据。
大家简单浏览一下，本次的重点是数据处理。
数据处理的方法通常包含数据清洗、数据加工等，本篇文章将为大家介绍一下Excel中的数据处理技巧。
数据清洗通常包括：筛选并剔除多余的重复值；补充缺失值；修正或删除错误值。
数据加工通常包括：对清洗后的数据进行合并、提取、拆分、转换等。
01数据清洗
数据清洗处理的数据包括：重复值、缺失值、错误值。
01处理重复值
1 函数法02
函数：COUNTIF(range,criteria)，对区域内满足单个指定条件的单元格进行计数。range：计数范围；criteria：计数条件，可以指定为数字、文本或表达式。计数1：在D2单元格输入=COUNTIF(C:C,C2)，计算每个订单编号总共出现的次数。计数2：在E2单元格输入=COUNTIF(C$2:C2,C2)，计算当前单元格对应的订单编号是第几次出现。
针对“计数2”列，以E6单元格对应的C6单元格中的订单号为例，3表示在C2~C8中该订单号是第三次出现。
此时能够很清晰的通过D列和E列看出重复值的具体情况：重复值共计出现多少次以及对应重复值是第几次出现。通过函数法完成对重复值的计数之后，接下来就要进行删除步骤了。
（1） 排序删除
针对函数法筛选出来的重复数据，可以对E列数据进行升序排序，选择并删除大于1的数据即可。
（2） 筛选删除
同样是针对函数法筛选出的数据，对E列数据进行筛选，取消显示数值为1的数据后将剩余的数据删除即可。
还可使用“数字筛选”功能，将“大于1”或“不等于1”的数据筛选出来后删除。
点击“大于”或“不等于”后，在弹出的窗口中，输入相应的数值进行筛选删除。
2 高级筛选法
选中准备筛选的数据，使用高级筛选功能并选中“选择不重复的记录”。
点击确定之后显示的就是不带重复值的数据。
高级筛选法的优点是操作简便，可以迅速得到去除重复值后的展现效果。
3 条件格式法
选中准备筛选的数据，点击“开始-条件格式-突出显示单元格规则-重复值”，即可对重复值进行标记。
此处还可以选择重复值被标记的颜色。
条件格式法相对其他方法就没有那么便捷了，它的缺点是只能标记出重复的数据，而当数据量较大的时候不能清晰的看出重复数据和重复次数。
4 数据工具法
使用“数据工具”中的“删除重复项”功能，可以直接删除数据中的重复值。
值得注意的是在点击“删除重复项”后弹出来的窗口中，需要先“取消全选”，然后选择想要删除重复项的列，再点确定。
因为弹出的窗口有时候会默认全选，此时不能直接点击确定，否则会将自己需要保留的数据也一并删除了。
这个方法的优点也是操作简单，十分便捷。接下来介绍如何处理数据中的缺失值。
处理缺失值
处理缺失值通常有两个方法：定位条件和查找替换。
1 定位条件
定位条件功能的入口：1）快捷键“CTRL+G”。2）“开始”-“查找和选择”-“定位条件”。选择需要处理的数据后，点击“定位条件”。
此时选择“空值”并确定即可标记缺失值。
图中的缺失值对应的内容是“Express Air”，正常输入需要填充的内容后，使用快捷键“CTRL+Enter”填充剩余的缺失值。
2 查找替换
查找替换功能的入口：1）快捷键：“CTRL+H”。2）“开始”-“查找和选择”-“替换”。
点击替换之后，输入需要查找的内容以及替换的内容，再点击全部替换即可。由于此次查找的为缺失值，所以查找内容保持空白即可。
补充几点通常用于处理缺失值的思路：
用样本统计量的值替换缺失值，比如样本均值；
用统计模型计算出来的值替换缺失值。比如回归模型、判别模型等；
删除包含缺失值的数据记录；
保留包含缺失值的数据记录，分析时按需排除。
以上就是关于Excel数据分析中处理缺失值的内容啦~接下来介绍常用的处理错误值的方法。
02处理错误值
Excel中的常见错误值有8种，分别是：#DIV/0!、#REF!、#VALUE!、#NULL!、#NAME?、####、#NUM!、#N/A、。每一种错误值都有各自的特点，因此对应着不同的处理方法，接下来就介绍一下以上8种错误值都可以通过什么办法解决~
错误类型1 #DIV/0!
出现#DIV/0!的原因是数据相除运算时，除数出现了0、空格或缺失值的情况。因为除数不能为0，所以当公式运算不符合要求时，会出现#DIV/0!的错误值形式。
如图所示，可以通过“公式-错误检查”功能查看错误原因和计算步骤。图中显示为“被零除”错误，此时修改除数内容即可。
错误类型2 #REF!
出现#REF!的原因是通过公式引用某一单元格或单元格区域时，当引用的区域被删除了，公式无法找到引用的区域，此时会出现#REF!错误值。
如图所示，删除“3月销售额列后”，“3月总销售额”数据显示#REF!错误值，错误检查功能显示原因为移动或删除单元格导致了无效的单元格引用。针对此类问题我们需要重新梳理公式计算逻辑，更新公式的计算区域。
错误类型3 #VALUE!
出现#VALUE!的原因是输入公式时将不同数据类型的值进行计算，比如将“小组1”与“15”相加时出现#VALUE!错误值。
图中为文本类型和数值类型的相加，导致出现了#VALUE!错误值，此时我们需要检查公式中计算区域的值是否为相同类型。
错误类型4 #NULL!
出现#NULL!错误值的原因是在公式中使用了不正确的区域运算符，或者在区域引用之间使用了交叉运算符（空格字符）来指定不相交的两个区域的交集。
如图所示，由于错误使用了区间运算符，将“，”替换成了“空格”，出现了#NULL!错误值。此时需要重新检查公式以及公式引用的区间。
错误类型5 #NAME?
#NAME?出现的原因可能是公式中的名称拼写错误、公式的语法使用错误、公式引用了未定义的名称中的一种。
如图所示，将函数“SUMPRODUCT”输入成“SUNPRODUCT”时，出现了#NAME?错误值。此时需要重新检查错误值单元格中的公式拼写。
错误类型6 ####
出现####错误值的原因是列不够宽而无法显示单元格的所有内容。
如图所示，当列不够宽无法显示完整日期时会出现####错误值，此时调整列宽即可。
错误类型7 #NUM!
出现#NUM!错误值的原因是公式中包含无效数值。
如图所示，由于公式中“3的6785次方“计算的数值超出了Excel限定的数值计算范围导致出现了#NUM!错误值，此时只能将数据进行拆分或重新整理计算。
错误类型8 #N/A
出现#N/A错误值的原因是公式找不到要求查找的内容。
如图所示，因为表格中不存在公式中的F7-“小组5”的内容，所以显示#N/A错误值。此时需要重新检查查找范围，以及查找内容是否有误。
处理错误值的补充：
小易在这里再补充一个常用于处理错误值的函数：IFERROR(value, value_if_error)value：检查是否存在错误的参数；value_if_error：公式计算结果为错误时要返回的值。以错误值#N/A为例，当由于公式查询的数据不存在时出现#N/A错误值，可以使用该函数返回设定的备注。
以上就是处理错误值的内容啦~接下来小易带大家进入数据加工的板块。
02数据加工
数据加工通常包括数据合并、数据拆分和数据转换。下面先从数据合并开始。
03数据合并
数据合并通常包括字段合并和字段匹配。
1 字段合并
字段合并常用到函数：CONCAT(text1,[text2],……)和&连接符。text1（所需的）：要连接的文本项。字符串或字符串数组，如单元格区域。text2（可选）：要连接的其他文本项。文本项最多可以有253个文本参数，每个文本参数可以是一个字符串或字符串数组，如单元格区域。&：适用于需要拼接的字段较少的情况。下图是对两个方法的使用示例。
2 字段匹配
跨表格的字段匹配通常需要使用函数：VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])lookup_value（必需参数）：要查找的值。要查找的值必须列于在参数参数中指定的单元格table_array列中；table_array（必需参数）：VLOOKUP 在其中搜索 lookup_value 和返回值的单元格区域。单元格区域的第一列必须包含lookup_value。单元格区域还需要包含要查找的返回值；col_index_num（必需参数）：对于包含 (的列，列号table_array) 从 1 开始；[range_lookup]（可选参数）：一个逻辑值，该值指定希望 VLOOKUP 查找近似匹配还是精确匹配。
表格1
表格2如上图所示，若要根据“订单号”将表格2中的“运送日期”数据匹配到表格1中，可根据下图的公式进行字段匹配。
上述两个方法都是用于数据合并的，接下来介绍数据拆分的方法。
04数据拆分
数据拆分通常指字段分列，小易总结了两个方法来实现字段分列。
1 菜单法
功能入口：“数据-数据工具-分列”（如图所示）
确认下一步之后，还需要在弹出的窗口中确认分隔符号，案例中使用的分隔符号是“/”。
此处点击下一步后，可以在弹出的窗口中选择拆分的数据对应的格式，对于案例中的数值可直接默认选择，点击完成。
案例中由于被拆分的数据相邻的列已有内容，所以最后呈现的是将C列和D列数据替换后的效果。
2 函数法
函数法对应的有两个函数：
LEFT(text,[num_chars])；RIGHT(text,[num_chars])text（必需）：包含要提取的字符的文本字符串；num_chars（可选）：指定要由LEFT/RIGHT提取的字符数量。下图以LEFT函数为例，对C列数据进行拆分。
以上是数据拆分的内容，接下来轮到数据转换啦~
05数据转换
数据转换通常包括行列转换和数据类型转换。
1 行列转换
行列转换比较简单，可直接选择需要转换的数据范围，进行“复制”、“粘贴”，唯一需要注意的是在粘贴时使用“粘贴选项-转置”即可。
2 数据类型转换
数据类型转换涉及到文本转数值、数值转文本、数值日期转日期等。可以通过函数VALUE()、TEXT()来实现。
VALUE(text)：将表示数字的文本数据转为数字。text：用引号括起来的文本或包含要转换文本的单元格的引用。TEXT(value, format_text)：数值数据转为文本数据。value：要转换为文本的数值；format_text：一个文本字符串，定义要应用于所提供值的格式。下面以TEXT()函数为例，对F列数据进行转换。将F列数字转换为文本类型数据。
下图示例中，我们还可以用VALUE()函数将H列的文本数据转换为数字。
以上就是本次分享的全部内容~这些内容仅仅是数据分析知识的冰山一角，想要更深入的学习数据分析相关技巧，可以关注戎易大数据~我们接下来还会继续分享更多和数据分析相关的内容。
<hr/>四、统计分析功能
使用Excel的“统计分析功能”需要建立在完成了数据处理操作的前提下，因为数据不“干净”，经过“统计分析”得出的数据结论就没什么价值了。
Excel的“统计分析功能”位于它的分析工具库中，一般需要我们手动加载这一功能。当然，Excel的分析工具库的功能十分强大，它包含的远不止一个“统计分析功能”，还有“方差分析”和“预测分析”等等，但是小易这次给大家介绍的主要是“统计分析功能”~
加载指南：
单击工具栏的“文件”。
再单击弹出页面左下方的“选项”。
在“Excel选项”页面中找到左侧的“加载项”，并在“加载项”中找到“分析工具库”，此时需要选中下方“管理”中的“Excel加载项”再点击“转到”。
在接下来的窗口中勾选“分析工具库”后，点击“确定”就可以开始使用“统计分析功能”啦~
具体使用入口见下图。
接下来估计会有好奇的小伙伴问“那么什么是统计分析呢？”小易这就告诉大家~统计分析是以概率论为理论基础，根据试验或观察得到的数据来研究随机现象，对研究对象的客观规律做出种种合理的估计和判断。
总的来说就是我们需要运用统计分析的知识来处理搜集到的海量数据，将其转化为便于我们得出结论的数据。前菜上完了，下面进入正题~本篇文章主要介绍的是如何应用“统计分析功能”中的“描述统计”和“直方图”工具来描述和观察数据的重要特征，以及“假设检验”中的应用“t-检验”、“F-检验”和“z-检验”工具根据样本数据来判断总体数据的分布特征。
01描述统计
描述统计的任务是描述随机变量的统计规律性。要完整地描述随机变量的统计特性需要分布函数。但在实际问题中，求随机变量的分布函数是比较困难的。很多时候也不需要去全面考察随机变量的变化规律，而只需知道随机变量的某些特征。
例如，在研究某一地区居民的消费水平时，在许多场合只需知道该地区的平均消费水平；又如在分析某个年龄段儿童的生长发育情况时，常常关心的是该年龄段儿童的平均身高、平均体重；再如检查一批灯泡的质量时，既需要注意灯泡的平均寿命，又需要注意灯泡寿命与平均寿命的偏离程度，平均寿命较长、偏离程度较小，质量就较好。
尽管这些数值不能完整地描述随机变量，但能描述随机变量在某些方面的重要特征。随机变量的常用统计量有平均值、标准误差、标准偏差、方差、最大值、最小值、中值、峰值、众数、偏斜度等。
其中，平均值描述了随机变量的集中程度，而方差描述了随机变量相对于平均值的离散程度，是最常用的两个统计量。当需要计算一组数据的一些常用统计量时，可使用Excel提供的统计函数来实现，但更便捷的方法是使用Excel提供的描述统计工具，它能同时给出一组数据的许多常用统计量。示例：利用某个班级3门课程的考试成绩数据来运用描述统计功能。
选中数据后，点击“数据分析”，在弹出的窗口中选中“描述统计”。
按照上述操作点击确定后，弹出“描述统计“窗口。此时确认输入区域为”$B$1:$D$16“，输出区域为”$F$1“。并选中”标志位于第一行“、”汇总统计“、”平均数置信度-95%“、”第K大值-3”、”第K小值-3”。
分组方式：通常情况Excel会根据指定的输入区域自动选择；标志位于第一行复选框：若输入区域包含标志行，则必须勾选此复选框。
否则Excel会自动以列1、列2、列3……作为数据的列标志；汇总统计：若勾选，则显示描述统计结果，否则不显示；平均数置信度：勾选此框，并输入要使用的置信度，则输出包含均值的置信度，本例输入95%；第K大值：根据需要指定要输出数据中的第几个最大值，本例输入3；第K小值；根据需要指定要输出数据的第几个最小值，本例输入3。
输入完有关参数后点击确定，即得到描述统计结果。
直方图可以更直观地看出成绩的分布情况。虽然可以使用Excel提供的函数和图表向导完成制作，但分析工具库的直方图更加方便。
制作直方图首先需要定义组距，即一组按升序排列的边界值。Excel将统计在当前边界点和相邻的高值边界点之间的数据个数，并据此绘制直方图。本例中根据成绩的优、良、中、及格和不及格分类，在表中的“B19:B24”单元格区域设置的组距如下图。
“直方图”的入口和“描述统计”的入口一致。都位于“数据-分析-数据分析“。
在弹出的“直方图“窗口中，选中输入区域为”$B$2:$B$16“（因为本例使用线性代数成绩绘制直方图），接收区域为”$B$20:$B$24“，输出区域为”$F$1“，并选择输出方式为”图标方式“，再点击确定即可得到直方图。
标志：本例指定的数据未包含标志，故不勾选；输出方式：根据需要确定是否选择柏拉图、累积百分率、图表输出复选框。若选定柏拉图，则统计结果按频率从大到小的顺序排序；若选定累积百分率，则统计结果中增加一列频率累积百分比数值，并同时在直方图中添加累积百分比折线；若选定图表输出，则根据统计结果画出直方图。本例只勾选图表输出复选框。
02假设检验
假设检验的基本思想可以应用小概率原理来解释，即小概率事件在一次试验中是几乎不可能发生的。如果对于总体的某个假设是真实的，那么不支持这一假设的小概率事件A在一次试验中竟然发生了，人们就有理由怀疑该假设的真实性，从而拒绝假设。假设检验问题常见于根据样本观测值来判断总体假设是否成立的问题中。
处理假设检验问题的一般步骤：
- 根据实际问题的要求，提出原假设H0及备择假设H1；
- 给定显著性水平α和样本容量n（α的值视具体情况而定，通常取0.1、0.05、0.01及0.005等值）；
- 确定检验统计量和拒绝域的形式；
- 按P{拒绝H0/H1为真}=α求出拒绝域；
- 根据样本观测值所求出的统计量确定是接受还是拒绝原假设H0。
本次讲述的假设检验共分为三类：基于成对数据的“t-检验 成对二样本分析”、双样本假设问题中的“F-检验 双样本方差“和”t-检验 双样本等方差假设“。
03t-检验 成对二样本分析
基于成对数据的t-检验常用于逐对比较法中，即有时为了比较两种产品、两种仪器、两种方法的差异，常在相同的条件下进行对比试验，得到一批成对的观察值，然后分析观察数据做出推断。示例：运用t-检验分析某体育疗法对减肥的作用。现随机抽取了12位病人进行试验，除参加该体育疗法外，其余一切条件都尽可能地做到相同，先需根据试验前后测得体重数据判断这种新体育疗法对减肥是否具有显著作用。样本数据如下。
“t-检验 平均值的成对二样本分析”的入口同上，位于“数据-分析-数据分析”。下面就可以对样本数据使用分析方法啦~
根据上述操作后，需要在弹出的“t-检验 平均值的成对二样本分析“窗口中输入相应参数。
变量1的区域：指定试验前的数据所在单元格区域B1:B13；变量2的区域：指定试验后的数据所在单元格区域C1:C13；假设平均差：根据实际问题输入假设成对观测样本d的均值μd。
本例中假设样本x与样本y的平均值相等，所以输入0；标志：指定的数据区域包含标志行则勾选；α值：根据需要指定显著性水平，本例为0.05；输出区域：选中将结果输入到输出区域。输入完参数后点击确定，即得到下列分析结果。
根据结果可看出，样本的t统计量为4.745664，大于t双尾临界值2.200985，所以拒绝原假设H0，即在置信度α=0.05的情况下，实施新体育疗法前后的数据有显著差异。由此得出结论：这种新体育疗法对于95%以上的人具有减肥作用，疗效显著。上述示例针对的还只是来自同一总体的样本数据，下面介绍的两个方法则是针对来自两个总体的双样本数据进行分析。
04F-检验双样本方差
示例：在机床上进行一项试验，以确定改进的操作方法是否会增加产品的优质率，以及是否会改变产品优质率的波动性。试验在同一机床上进行，每生产一批零件时除了操作方法外，其他条件都尽可能相同。先后用标准方法和改进方法进行生产，交替进行，各生产了10批零件，得到有关产品优质率的两组样本。样本数据如下。
”F-检验 双样本方差“的入口同上，位于”数据-分析-数据分析“。下面就使用该工具来检验两总体样本的方差。
在弹出的窗口中输入相应参数，即可获得检验结果。
变量1的区域：指定试验前的数据所在单元格区域B1:B13；变量2的区域：指定试验后的数据所在单元格区域C1:C13；标志：指定的数据区域包含标志行则勾选；α值：根据需要指定显著性水平，本例为0.05；输出区域：选中将结果输入到输出区域。检验结果如下图。
由检验结果可看出两个总体方差相等，即改进的操作方法与标准方法相比，两者的产品优质率的波动性没有显著变化。
05t-检验 双样本等方差假设
由“F-检验 双样本方差“的检验结果可假设两总体的方差相等，因此要检验改进的方法能否提高产品的优质率，可以使用”t-检验 双样本等方差假设“工具来进行检验。”t-检验 双样本等方差假设“的入口同上，位于”数据-分析-数据分析“。
同样地在弹出的窗口中输入相应参数，得出检验结果。
变量1的区域：指定试验前的数据所在单元格区域B1:B13；变量2的区域：指定试验后的数据所在单元格区域C1:C13；假设平均差：根据假设，示例输入0；标志：指定的数据区域包含标志行则勾选；α值：根据需要指定显著性水平，本例为0.05；输出区域：选中将结果输入到输出区域。检验结果如下图。
从检验结果可看出，t=-4.29<-t单位临界值=-1.73，所以可认为改进的操作方法较原来的方法更好，能明显提高产品的优质率。
<hr/>五、Excel可视化
相同的数据，使用不同的图表进行体现，效果也会千差万别，那么我们应该如何正确选择，才能让图表的作用发挥到极致呢？Excel中图表类型有很多，重点为大家讲解Excel中15种图表类型和应用，关于图表的详细制作方法先不做过多讲解。
01折线图
折线图用于显示数据在一个连续的时间间隔或者时间跨度上的变化，它的特点是反映事物随时间或有序类别而变化的趋势。在折线图中，数据是递增还是递减、增减的速率、增减的规律（周期性、螺旋性等）、峰值等特征都可以清晰地反映出来。
所以，折线图常用来分析数据随时间的变化趋势，也可用来分析多组数据随时间变化的相互作用和相互影响。例如可用来分析某类商品或是某几类相关的商品随时间变化的销售情况，从而进一步预测未来的销售情况。
适合的场景：不同月份之间的温度变化。
不适合的场景：当水平轴的数据类型为无序的分类或者垂直轴的数据类型为连续时间时，不适合使用折线图。我们以一个不同游戏类型的销量对比的场景为例，对于表示分类对比的数据时，我们更应该使用柱状图，而不是折线图。
02柱状图和条形图
柱状图，使用垂直或水平的柱子显示类别之间的数值比较。其中一个轴表示需要对比的分类维度，另一个轴代表相应的数值。柱状图有别于直方图，柱状图无法显示数据在一个区间内的连续变化趋势。柱状图描述的是分类数据，回答的是每一个分类中“有多少”这个问题。
适合的场景：游戏销量的图表，展示不同游戏类型的销量对比。
不适合的场景：某股票在 2015 年 9 月份整个月的每日的价格走势。随着有序的时间变化的数值趋势，更适合使用折线图或者面积图。
03饼图
饼图广泛地应用在各个领域，用于表示不同分类的占比情况，通过弧度大小来对比各种分类。饼图通过将一个圆饼按照分类的占比划分成多个区块，整个圆饼代表数据的总量，每个区块（圆弧）表示该分类占总体的比例大小，所有区块（圆弧）的加和等于 100%。饼图可以很好地帮助用户快速了解数据的占比分配。
它的主要缺点是：饼图不适用于多分类的数据，原则上一张饼图不可多于 9 个分类，因为随着分类的增多，每个切片就会变小，最后导致大小区分不明显，每个切片看上去都差不多大小，这样对于数据的对比是没有什么意义的。
所以饼图不适合用于数据量大且分类很多的场景。相比于具备同样功能的其他图表（比如百分比柱状图、环图），饼图需要占据更大的画布空间。很难进行多个饼图之间的数值比较。尽管如此，在一张饼图上比较一个数据系列上各个分类的大小占比还是很方便高效的。
适合的场景：某班级的男女性别比例。
不适合的场景：各省份人口占比，因为这张图上包含的分类过多，就出现了简介中提到的问题，很难清晰对比各个省份的人口数据占比情况，所以这种情况下，我们推荐使用横向柱状图。
04面积图
面积图又叫区域图。它是在折线图的基础之上形成的, 它将折线图中折线与自变量坐标轴之间的区域使用颜色或者纹理填充，这样一个填充区域我们叫做面积，颜色的填充可以更好的突出趋势信息，需要注意的是颜色要带有一定的透明度，透明度可以很好的帮助使用者观察不同序列之间的重叠关系，没有透明度的面积会导致不同序列之间相互遮盖减少可以被观察到的信息。
和折线图一样，面积图也用于强调数量随时间而变化的程度，也可用于引起人们对总值趋势的注意。他们最常用于表现趋势和关系,而不是传达特定的值。
适合的场景：某公司在 Florida、Texas、Nevada三个城市 1996 年 至 2015 年的收益情况，通过垂直坐标轴的正负方向很形象地表现了公司的盈利亏损情况。
不适合的场景：不同分类之间的数值比较，下图是不同游戏类型的销售情况。
05XY散点图
XY散点图可用来说明一组或多组变量间的相互关系，其每一个数据点都由两个分别对应于坐标轴的变量构成。每一组数据构成一个数据系列。
XY散点图的数据点一般呈簇状不规则分布，可用线段将数据点连接在一起，也可仅用数据点来说明数据的变化趋势、离散程度以及不同系列数间的相关性(正相关、负相关或不相关)。
散点图通常用于显示和比较数值，当在不考虑时间的情况下，比较大量数据点时，可以使用散点图，散点图中包含的数据越多，比较的效果就越好。
适合的场景：“身体脂肪百分比与 BMI”的散点图，以评估两个变量之间的关系。BMI 和身体脂肪数据的散点图显示了两个变量之间的强度很大的正线性关系。
06地图
地图，作为表现跨区域数据的最佳方式，一直是政府和企业高级管理人员钟爱的数据表现方式。地图可制作气泡地图、统计地图、热力地图等等。
以气泡地图为例，气泡地图其实就是气泡图和地图的结合，我们以地图为背景，在上面绘制气泡。我们将圆（这里我们叫它气泡）展示在一个指定的地理区域内，气泡的面积代表了这个数据的大小。比分级统计图更适用于比较带地理信息的数据的大小。
它的主要缺点是当地图上的气泡过多过大时，气泡间会相互遮盖而影响数据展示，所以在绘制时需要考虑这点。
适合的场景：各个国家遭受的恐怖袭击次数展示。如图，可以看出伊拉克遭受的恐怖袭击次数最多，并且恐怖袭击主要集中在中东地区。
不适合的场景：当数值字段表达的不是一个区域的总值，而仅仅是个取样值(气温、降水等）时不适合使用带气泡的地图，下图是中国气温的一个分布图。此时更适合热力图。
07股价图
常用于展示股票交易数据。将各种股票每日、每周、每月的开盘价、收盘价、最高价、最低价等涨跌变化状况，用图形的方式表现出来。如图所示:
1. 最上方的一条细线称为上影线，中间的一条粗线为实体，下面的一条细线为下影线。
2. 当收盘价高于开盘价，也就是股价走势呈上升趋势时，我们称这种情况下的 K 线为阳线，中部的实体以空白或红色表示。反之称为阴线用黑色实体或绿色表示。
3. 上影线的长度表示最高价和收盘价之间的价差，实体的长短代表收盘价与开盘价之间的价差，下影线的长度则代表开盘价和最低价之间的差距。
适合场景：下图展示了“湖南天雁”股票 2015 年 1 月 5 日至 2015 年 11 月 19 日的日 K 线图。
08曲面图
曲面图实际上是折线图和面积图的另一种形式，其有3个轴，分别代表分类、系列和数值由面图通过跨两维(分类和系列)的曲面图形来表示数据的变化趋势，曲面图形的颜色与图等表其取值范围。
适合场景：在地图上，用颜色和图来表示某个海拔高度范围。通过拖放曲面图的坐标可以方便地变换观察图表的角度。
09雷达图
雷达又叫戴布拉图、蜘蛛网图。传统的雷达图被认为是一种表现多维（4维以上）数据的图表。它将多个维度的数据量映射到坐标轴上，这些坐标轴起始于同一个圆心点，通常结束于圆周边缘，将同一组的点使用线连接起来就称为了雷达图。
虽然雷达图每个轴线都表示不同维度，但使用上为了容易理解和统一比较。使用雷达图经常会人为的将多个坐标轴都统一成一个度量，比如：统一成分数、百分比等。雷达图还可以展示出数据集中各个变量的权重高低情况，非常适用于展示性能数据。
雷达图的主要缺点是：
(1) 如果雷达图上多边形过多会使可读性下降，使整体图形过于混乱。特别是有颜色填充的多边形的情况，上层会遮挡覆盖下层多边形。
(2) 如果变量过多，也会造成可读性下降，因为一个变量对应一个坐标轴，这样会使坐标轴过于密集，使图表给人感觉很复杂。所以最佳实践就是尽可能控制变量的数量使雷达图保持简单清晰。
10树状图
树状图全称为矩形式树状结构图，可以实现层次结构可视化的图表结构，以便用户轻松地发现不同系列之间、不同数据之间的大小关系。
矩形树图由马里兰大学教授Ben Shneiderman于上个世纪90年代提出，起初是为了找到一种有效了解磁盘空间使用情况的方法。
矩形树图适合展现具有层级关系的数据，能够直观体现同级之间的比较。矩形树图的好处在于，相比起传统的树形结构图，矩形树图能更有效地利用空间，并且拥有展示占比的功能。矩形树图的缺点在于，当分类占比太小的时候文本会变得很难排布。相比起分叉树图，矩形树图的树形数据结构表达得不够直观、明确。
适合场景：下图是2015年手机品牌及其下属手机型号的销量信息。
不适合场景：没有权重关系，且需要明显展示层级关系，用分叉树图更合适。
11旭日图
旭日图是一种现代饼图，它超越传统的饼图和环图，能表达清晰的层级和归属关系，以父子层次结构来显示数据构成情况。旭日图中，离远点越近表示级别越高，相邻两层中，是内层包含外层的关系。
适合场景：如下图运用旭日图展现各国的获奖数据，旭日图能便于细分溯源分析数据，通过分层占比情况真正了解数据的具体构成。
12直方图
直方图，形状类似柱状图却有着与柱状图完全不同的含义。直方图牵涉统计学的概念，首先要对数据进行分组，然后统计每个分组内数据元的数量。在平面直角坐标系中，横轴标出每个组的端点，纵轴表示频数，每个矩形的高代表对应的频数，称这样的统计图为频数分布直方图。
频数分布直方图需要经过频数乘以组距的计算过程才能得出每个分组的数量，同一个直方图的组距是一个固定不变的值，所以如果直接用纵轴表示数量，每个矩形的高代表对应的数据元数量，既能保持分布状态不变，又能直观的看出每个分组的数量。
适合场景：下图绘制了钻石的全深比数据的统计直方图，从图中可以看出在66附近有两个孤立值。
不适合的场景：抽取的样本数量过小，将会产生较大误差，可信度低，也就失去了统计的意义。因此，样本数不应少于 50 个。
13箱形图
箱形图又称盒须图、盒式图或箱线图，是一种用作显示一组数据分布情况的统计图。如果一个数据集中包含了一个分类变量和一个或者多个连续变量，那么你可能会想知道连续变量会如何随着分类变量水平的变化而变化，而箱形图就可以提供这种方法，它只用了5个数字对分布进行概括，即一组数据的最大值、最小值、中位数、下四分位数及上四分位数。
对于数据集中的异常值，通常会以单独的点的形式绘制。箱形图可以水平或者垂直绘制。箱形图多用于数值统计，虽然相比于直方图和密度曲线较原始简单，但是它不需要占据过多的画布空间，空间利用率高，非常适用于比较多组数据的分布情况。
适合的场景：我们用箱形图将不同种类的鸢尾花的花萼和花瓣的长度、宽度数据展示出来，同时我们还可以比较不同品种间花瓣和萼片数据是如何变化的。
14瀑布
瀑布图有助于理解依次引入正值或负值的累积效应。瀑布图也被称为飞行砖图或马里奥图，因为看起来像悬挂在空中的砖头。瀑布图通常用于了解初始值如何受到一系列中间正值或负值的影响。
适合的场景：瀑布图具有自上而下的流畅效果，在企业经营分析、财务分析中使用较多，用以表示企业成本的构成、变化等情况。
15漏斗图
漏斗图适用于业务流程比较规范、周期长、环节多的单流程单向分析，通过漏斗各环节业务数据的比较能够直观地发现和说明问题所在的环节，进而做出决策。漏斗图用梯形面积表示某个环节业务量与上一个环节之间的差异。漏斗图从上到下，有逻辑上的顺序关系，表现了随着业务流程的推进业务目标完成的情况。
漏斗图总是开始于一个100%的数量，结束于一个较小的数量。在开始和结束之间由N个流程环节组成。每个环节用一个梯形来表示，梯形的上底宽度表示当前环节的输入情况，梯形的下底宽度表示当前环节的输出情况，上底与下底之间的差值形象的表现了在当前环节业务量的减小量，当前梯形边的斜率表现了当前环节的减小率。
通过给不同的环节标以不同的颜色，可以帮助用户更好地区分各个环节之间的差异。漏斗图的所有环节的流量都应该使用同一个度量。
适合场景：适用于流程流量分析。随着流程的推进，每个环节所要达成的成功数量在减少。最终的成交量是企业想要达成的交易数量。通过将各个流程中数量的信息画入漏斗图可以清晰的分析到哪个环节是当前业务流程中的薄弱环节，哪个环节是流量转化的瓶颈，进而帮助人们更加专注于薄弱环节提高整个流程的产出。
不适合的场景：漏斗图不适合表示无逻辑顺序的分类对比，如果要表示无逻辑顺序的分类对比情况，请使用柱状图。漏斗图也不适合表示占比情况，如果要表示占比情况，请使用饼图。下图是一个游戏销量的图表，展示不同游戏类型的销量对比，用柱状图合适，用漏斗图不合适。
六、Excel数据分析案例解析
之前已经分享过Excel数据分析的相关知识，现在来找些有意思的数据来练练手。
在kaggle上找到了一个美国2015-2018年牛油果的销售情况数据，大家可以把数据下载下来跟着进行一起来练习。
数据来源：https://www.kaggle.com/neuromusic/avocado-prices
数据下载：私信我，回复关键字【牛油果】获取。
01背景
近年来，健康饮食的理念被越来越多的人们所接受，牛油果作为一种健康的脂肪来源，逐渐变成高端、健康、营养的代名词，受到人们的追捧。
通过分析2015-2018年美国各地区牛油果的销售数据，我们可以看出近三年牛油果的销量和价格变化，分析出不同地区消费者的购买偏好随时间的变化趋势，对于制定相应的销售决策有着一定的指导意义。
02理解数据
拿到数据先不要着急去做分析，首先要做的是观察表内都有哪些数据，各字段的含义分别是什么，然后将不好理解的列名修改一下，不需要的列隐藏起来，让表看上去更加整洁明了。
2015-2018牛油果销售情况原始表
这个表共有18250条数据，14列，第一行是表头，我们看上图表头处2个红框，第一列的列名为空，经过观察发现第一列下面的数字表示一年的第几周。
第5-7列的数字不是很理解，经查询4046、 4225、 4770是贴在牛油果身上的代码，叫“价格查找代码”，英文为“Pricelook-upcodes”，简称PLU码，是为了方便超市跟踪查询产品的种类、大小以及价格等信息。
在这里我们把它们当做三种不同的牛油果就可以。为方便理解，把表格的英文字段进行了翻译，表格中的数据段分别为：
搞清楚这些后，就可以进行下一步操作，数据处理。
03数据处理
一、缺失值：第一列缺乏列名，手动填写Week进行补全。
二、列名重命名：将5-7列数据列名转换为易于理解的形式——4046/4225/4770前加上PLU-。
三、重复值和异常值：该表格的数据非常干净整齐，没有重复值和缺失值，也没有异常值，数据类型也很整齐，不用做数据整理和清洗的工作。
四、一致化处理：通过检查数据，发现region一列既有TotalUS（全美），又有Southwest（西南地区）等区域，还有California（加利福尼亚）及LosAngeles（洛杉矶）等城市，处理极不方便，应该分层次处理。
方法如下：(1)在region一列右边新建country列，然后将region列中的TotalUS数据填充到新建的名为“country”的列中，公式为：=IF($N2=&#34;TotalUS&#34;,$N2,&#34;&#34;)并双击表格右下角完成批量填充。
(2)同理，在country列右边新建region列，将原region列改为city，输入公式=IF(OR($N2=&#34;Midsouth&#34;,$N2=&#34;Northeast&#34;,$N2=&#34;SouthCentral&#34;,$N2=&#34;Southeast&#34;,$N2=&#34;West&#34;),$N2,&#34;&#34;)进行填充。
(3)将现在的region列和country列全选/右键复制/选择性粘贴/值和数字形式——避免原region列的数值变化后这两列也跟着变化。(4)选中city一列，ctrl+F定位出所有的&#34;TotalUS&#34;、&#34;Midsouth&#34;、&#34;Northeast&#34;、&#34;SouthCentral&#34;、&#34;Southeast&#34;、&#34;West&#34;，然后替换为&#34;&#34;。
最终得到下表，其中country列仅有Total US，region列仅有Southwest等地区，city列为除了Total US和Southwest以外的城市。
该案例省去了部分数据处理的步骤，关于数据处理之前有篇文章专门为大家详细介绍过，感兴趣可以去了解下。【知识专栏丨Excel】数据分析师是如何用Excel做数据处理的？
04分析数据及可视化
处理完数据，就到了分析数据这一步了，分析数据时，我们一般会将各组数据间的关系用合适的图表表现出来，图表易于展示且方便理解，能将我们的分析结果更好的展现出来。
Excel的数据透视表功能强大且简单好用，通过选取不同的内容到行和列，可以快速的找出各元素之间的联系，还可以按照不同时间段进行分组数据，可以将数据分组进行计数、求和、求平均值等，非常方便。下面我们练习用数据透视表分析数据关系。
一、新建一个数据透视表，选中某处数据，在插入选项中选择数据透视表，选择新工作表。
二、红框内是表格的所有字段，将红框里的字段拖到蓝框的行、列、值的位置，就可以将多种数据组合进行分析，选中数据组合并制作图表，就可以观察图形规律进行分析。
为了让我们的文件保持整洁，我们将图表都集中到新的工作表中。这样我们就有三种Sheet：原始数据，数据透视表，和各种分析图表。关于数据透视表之前有篇文章专门为大家详细介绍过，感兴趣的可以去了解下。
【知识专栏丨Excel】一篇文告诉你如何制作数据透视表！
下面来看几个简单的例子。
1）各大区域牛油果均价走势
首先我们来观察2015-2018年间牛油果平均价格的变化趋势，选择几个主要地区和全美总体的平均价格，时间按照季度划分。
各地区牛油果均价走势数据透视表
各区域牛油果均价走势
整体来看，从2015年到2018年之间，各大地区的牛油果均价均显现出不同程度的增长趋势，在2017年第三季度前后出现了峰值，其中West地区的增长幅度最大。
整体价格来看，Northeast地区均价几乎一直高于其他地区，而SouthCentral相较其他地区均价最低。同时，年内价格出现季节性波动，每年的第一二季度价格较低，第三四季度价格偏高，这种周期性的变化可能与牛油果的成熟季节有关。
2）牛油果分地区年度销量走势
依旧选择各大地区，选取牛油果的总销量，时间间隔选择季度，我们来观察各地区2015-2018年牛油果总销量的走势。
各地区销量走势数据透视表
各地区牛油果总销量走势
牛油果在2015年的销量明显低于后三年，从2015年到2018年各地区销量有轻微上升趋势，总体来看没有太明显的增长，但2018年一季度似乎呈现出强于往年的增长势头。
其中Midsouth地区销量一直低于其他区，之前我们看到Midsouth的牛油果价格也偏高，也许是因为这个地区本身牛油果产量少，需要从周边地区引进，导致价格较高，销量较小。
West和SouthCentral地区的销量最大，而在上一个图表中我们也看到SouthCentral和West地区的牛油果均价是最低的，这有多种可能，也许这两个地区本身的牛油果产量非常大，人口较多，人们也很喜欢食用牛油果。
同时可以看到销量在季度间也有周期性的变化，一二季度销量较大，三四季度销量开始下滑，这与上一图的价格趋势刚好相反，也比较符合我们的预期。
3）各城市牛油果年销量排行
我们再来看一下各个城市的牛油果销量情况。在数据分析前，我们已经做过城市的分层次处理，所以将city放入行中即可。
各城市牛油果销售数据透视表
各城市牛油果销售情况
由于城市数目太多，图表的内容过于拥挤，我们只选出销量最多的前10个城市来具体分析。
10大牛油果消费城市
从图表上可以看到California销量遥遥领先，几乎是排名第二的GreatLakes区域的二倍之多，第五名开始的6个城市销量相差不大。从2015年到2018年，各个城市的年销量几乎都有所增加。
如果再结合人口数据，我们可以进一步判断销量的增加，更多受居民的人数增加影响还是受居民的购买力增强影响；我们还可以计算人均牛油果消费量来观察各地区居民对牛油果的喜好程度等。但这个表内并没有包含城市的人口数量，所以我们无法获得更多信息。
4）有机牛油果销量和价格的变化
近些年来，有机食品愈加受到人们的喜爱，人们越来越重视更健康的饮食方式。我们来看一下有机牛油果的价格和销量有没有显示出相应的趋势，依旧选取季度数据。
不同类型牛油果均价数据透视表
不同类型牛油果价格走势
有机牛油果价格始终高于普通牛油果，差值保持在0.6美元左右，两种牛油果价格的变化趋势大致相同。从2016年第二季度，牛油果价格开始上涨，到2017年三季度时到达峰值，接着便呈现出持续的下降趋势。
不同类型牛油果销量数据透视表
普通牛油果和有机牛油果销量走势
从上图我们能够很明显地看出，顾客的购买选择还是以普通牛油果为主。有机牛油果的销量只占非常小的一部分，但是数值呈现出非常明显的增长趋势，以2018年一季度的数据与2015年一季度相比，有机牛油果的销量翻了一番还多。
结合上图中2017年三季度后牛油果价格呈现的下降趋势，这似乎可以说明人们在能接受价格的条件下，越来越重视饮食的健康，开始更多的选择有机牛油果。
5）牛油果袋装和单卖的比例变化
TotalBags表示以袋出售的牛油果销量，Total Volume表示单个卖和按袋卖的牛油果销量之和。
单买牛油果的顾客可能是独居，或是食用牛油果的频率较低；买袋装牛油果的顾客可能是家庭采购，或是食用频率较高，所以一次会购买多个。为了观察袋装牛油果的销售情况，我新增了一列来表示以袋装出售的牛油果销量占总销量的百分比，记为Percentage-Bags。
袋装与单卖牛油果销售比例数据透视表
袋装与单卖牛油果销售比例变化趋势
2015年袋装牛油果的销量很少，只占总销量的四分之一，2015年四季度开始，袋装牛油果的销量占比开始稳步上升，到2018年一季度，袋装牛油果销量占比为35.5%，且仍表现出良好的上升趋势。越来越多的顾客会一次性购买多个牛油果，这或许与近几年的牛油果热潮有关，人们开始关注健身和饮食，牛油果作为一种健康的脂肪来源，逐渐变成高端、健康、营养的代名词，受到人们的追捧。
05商业分析报告
分析完数据后，我们通常需要将分析结果做成PPT进行汇报演示。报告常使用总-分-总的结构，先表明分析的背景与目的，再分别列出各个图表和分析，最后总结，得出结论。现在我将上面做出的图表和分析整理，适当排版，做成一个PPT文件。
整理不易，记得点赞+收藏+喜欢，感谢！ |
本帖子中包含更多资源
您需要 登录 才可以下载或查看，没有账号？立即注册
×
|
变色卡