excel第一天-数据分析与Excel

数据分析与Excel

1. 数据分析

随着互联网+的不断深入，网络中的数据量飞速膨胀。身处信息的海洋，在这个数据高速爆发的时代，企业想要快速发展，不能只简单地靠历史的经验，想要快速成功，就要认清数据、企业、社会三者之间的联系，因此就出现了数据分析这个职业。尤其是在以数据驱动为首的百度、美团、以及京东等企业中，数据分析都扮演了重要的角色。现在的传统企业也慢慢意识到了数据驱动的重要性，大部分企业也都在学着用数据分析解决问题，或者提升业绩，这类公司在进行数字化转型。比如：银行企业的中国银联，交通银行的东方航空，通信行业的移动、联通、电信等都在通过数据进行探索。

1.1 什么是数据分析

数据分析是通过技术手段，对业务进行流程梳理、指标监控、问题诊断以及效果评估，它的目的是对过去发生的现象进行评估和分析，并在这个基础上对未来事物的发生和发展做出预期分析处理，以此指导未来的一些关键性决策。

随着数据量的不断增长，数据处理以及信息挖掘技术也在迅速发展，人们对于数据的处理也不仅仅是数据存储以及信息的简单探索，而是结合一些模型的应用进一步分析。虽然现在出现了大量数据分析技术，例如：Python、R等编程语言以及MySQL、Hadoop等数据存储技术，但是Excel凭借其操作简单、灵活以及宽广的覆盖面，在数据分析中占据着一席之地。

1.2 数据分析流程

数据分析流程可以概括为：数据理解、提取数据、数据清洗、数据分析、数据可视化、撰写报告。

2. Excel用法

2.1什么是Excel

Excel是Office办公软件中的组件之一。Excel专长于对表格中的数据进行计算和统计管理，通常用于财务或其他数据管理的表格制作。同时Excel还有很好的可视化能力，可用于制作各种行业报告。

2.2创建与保护

❀创建工作簿、工作表

• 工作簿：表格文件。

• 工作表：下方切换的N个活动页，创建一个工作簿的时候会自动的创建一个工作表。

• 单元格：能够看到的每一个方格。

❀保护功能

• 保护工作簿： 防止其他用户对工作簿的结构进行更改，如移动、删除或者添加工作表。
• 保护工作表：通过限制其他用户的编辑能力来防止他们进行不需要的更改。
• 保护单元格： 防止数据被篡改。

❀操作方法

• 保护工作簿：审阅 --> 保护工作簿

  

• 保护工作表：在指定工作表右键 --> 保护工作表

  

• 保护单元格：选中单元格单元格 --> 开始 --> 格式 --> 锁定单元格、保护工作表

  选中单元格之后：

  

2.3快速输入

❥填充柄

选中单元格，右下角出现黑色十字时，鼠标放置到单元格右下角后右键向下拖拽，选择对应选项。

• 复制单元格：同时将单元格的样式和内容复制给其他单元格。
• 填充序列：复制单元格样式，将单元格内容按照一定规则变化。
• 仅填充格式：只复制单元格样式。
• 不带格式填充：只将单元格内容按照一定规则变化，样式不改变。
• 以年填充：按照年份变化。
• 以月填充：按照月份变化。
• 以天数填充：按照天数变化。
• 填充工作日：与以天数填充同理，仅仅去除每周周末。

❥快速填充

• 方法：从数据列后方相邻单元格输入内容，选中单元格，鼠标放置到单元格右下角后右键向下拖拽，选择对应选项。

• wps方法：开始->填充->智能填充

❥自定义列表填充

• Excel自定义列表位置：文件 --> 选项 --> 高级 --> 编辑自定义列表（自定义列表中的逗号必须用英文的逗号）
• WPS自定义列表位置：文件-表格偏好设置-自定义序列
  
  

❥多个不连续单元格同数据

• Ctrl+鼠标左键选中单元格，填入数据，Ctrl+Enter补全

• Shift+F8，鼠标点击单元格，输入数据，Ctrl+Enter补全

  

❥多个不连续单元格不同数据

• 选中下方数据，Ctrl+ G，条件引用选中空值，对空值进行单元格引用，=按一下需要的数据，Ctrl + Enter

• 选中下方数据，Ctrl + G->定位条件->空值->输入"="->点击山东省对应的单元格->Ctrl + Enter

  

❥文本记忆输入

注意：必须连续单元格输入才会生效

• Alt + 向下箭头
• 直接输入内容，根据提示选择回车。

2.4数据验证

❀数据有效性的作用：不仅能够对单元格的输入数据进行条件限制，还可以在单元格中创建下拉列表菜单方便用户选择输入。

❀数据有效性

• Excel：.选中单元格 --> 数据 --> 数据验证 --> 数据验证 --> 设置 --> 允许：序列 --> 来源（来源是表格中的序列）

  

• WPS：选中单元格->数据->有效性

❀圈释无效数据

• 1.选中单元格设置数据验证1-10的范围；

• 2.圈释无效数据。

  

2.5定位条件

❀操作

• 开始 --> 查找和选择 --> 定位条件（Ctrl + G或者F5）

❀要求

• 使用定位条件选择区域中空单元格，填入100，按组合键Ctrl+Enter填充

❀效果图：

2.6单元格查找与替换

开始 --> 查找和选择 --> 替换

2.7快速选择数据

❥操作：Ctrl + Shift + 方向键

❥优点：可以快速选择规模较大的区域数据

❥缺点：如果起始行或者起始列出现空值，会出现选择不全的问题，需要稍加注意。再按下同方向的方向键即可。

2.8选择性粘贴

❥方法步骤：

• 输入需要增加的比例。
• 将需要变化的数据提前复制一份。
• 复制比例，选中数据，选择正确的选项即可。

2.9冻结单元格

❥操作

• 视图 --> 冻结窗格
• 注意：如果要冻结前N行，要选到第N+1行再冻结。冻结列同理。

2.10导入外部数据

❥Excel支持从外部导入数据，导入途径包含：本地各类存储数据的文件、数据库、网站等。

❥操作方式

• 数据 --> 获取数据–> 选择对应功能选项

  

2.11Excel数据类型

❥1.文本

• 姓名、性别、住址以及一些不需要计算的数值，比如：手机号、银行卡号等。

❥2.数值

• 整数、小数、科学计数等需要计算的数字。

❥3.日期和时间

• TRUE、FALSE。

❥4.逻辑值

• 2022/12/12

❥5.错误值

• 错误值通常是在使用公式时，由于种种原因没能返回需要的结果，而是返回了一串不同字符组成的错误值代码。

• 常见错误值：

  #VALUE!	键入公式的方式错误。或者引用的单元格错误。
  #DIV/0!	这个错误值是说数字被0除了，因为0不能当被除数嘛。
  #NAME?	在公式中是因为公式名称中出现拼写错误
  #N/A!	公式找不到引用的值
  #REF!	公式引用无效单元格时将显示 #REF! 错误。 当公式所引用的单元格被删除或被粘贴覆盖时最常发生这种情况。
  #NUM!	公式或函数中包含无效数值时
  ######	如果单元格所含的数字、日期或时间比单元格宽，或者单元格的日期时间公式产生了一个负值，就会产生#####
  #NULL!	如果在公式中使用了不正确的区域运算符，或者在区域引用之间使用了交叉运算符（空格字符）来指定不相交的两个区域的交集，将显示此错误。

❥查看Excel单元格数据类型

• TYPE()

  类型	type值
  数字，日期	1
  文本	2
  逻辑值	4
  误差值	16
  数组	64
  复合数据	128

2.12单元格格式

❀对整个单元格以及单元格中的数据应用格式 ：

• 方式1：更改单元格中文本格式，可以将单元格中文本格式改为数值、货币、时间等Excel定义格式，也可以改为按照一定规则设定的自定义格式。
• 方式2：将单元格想象成图片框，框中的图片就是数据。 设置单元格的格式可能包括添加边框、添加颜色或阴影，或更改单元格的大小和样式。

❀操作

• 开始–>数字右下角

  

❀更改单元格文本格式

原始数据	格式	效果
23	#	23
23.2677	#.##	23.27
2.3345555	0.000	2.335
2.738383	0.00元	2.74元
34	总共#个	总共34个
张三	“姓名:”@	姓名:张三
abc	@*a	abcaaaaaaa
12345678	#,##	12,345,678
23	[红色][<0];[绿色][>0]	8

❀自定义格式语法

格式	说明
#	数字占位符；四舍五入；不保留无意义的0
'0	数字占位符；四舍五入；会保留无意义的0
?	数字占位符；四舍五入；会用空格代替数据中无意义的0
,	使用数字占位符的时候，设置逗号分割的位数
@	文本占位符；在格式中代表文本数据本身
*	重复后面一个符号，直到把单元格填满为止
文本颜色	[颜色1][条件1]格式1;[颜色2][条件2]格式2;[颜色3][条件3]格式3

2.13条件格式

❀条件格式设置

• 借助“数据条”直观分析数据。
• 使用“色阶”创建热图。
• 借助“图标集”为数据分类。
• 条件格式的新建、删除、编辑。

2.14添加辅助列

❀操作方式

• 在最前面插入一列，并且输入每行数据对应的编号，对应的编号是1、3、5…，然后在第一列输入编号的最后面继续添加2、4、6、8…，最后按照序号的值对整个表格排序。

❀操作结果

2.15拆分

3.Excel常用公式

❀运算符

• +: 加

• -:减

• *:乘

• /: 除

  

• ^: 幂运算

  

❀给单元格提供内容的方式

• 直接输入数据：输入的是什么，单元格内容就是什么。

• “=单元格地址”：直接引用单元格内容。

  a.单元格地址引用

  注意：引用单元格内容时，如果引用地址没有锁定，往下拉单元格行号不断加1，往右拉单元格列号不断加1

  单元格位置	解释
  D1	行和列都不锁定
  $D$1	行和列都锁定
  $D1	列号锁定，行号不锁定
  D$1	行号锁定，列号不锁定

  b.单列变多列：地址引用

  方法1：找到第一行和原始数据的引用关系，写出引用，然后直接往下拖。
  方法2：直接通过写出引用地址关系，然后将A替换成”=A”。只写四个地址。
  

  c.多列变单列：地址引用

  1.通过引用复制原来的数据。
  2.在第一列的最后引用第二列第一个数。
  3.在第一列最后添加的单元格往右拖，再往下拖，拖到数据完成为止。
  4.选择性粘贴第一列的数据的数值，删除多余数值。
  

  d.合并数据：跨表引用。

  '表名'!引用的表中单元格地址
  

  

• “=Excel公式”：计算公式结果来给单元格提供内容（公式中的数据可以是具体的数据，也可以引用单元格内容）

• “=函数调用表达式”：获取函数返回值，来提供单元格内容。

❀调用函数

• 见第4点

4.Excel常用函数

❀文本函数

适用版本	文本函数	作用描述	语法	例子字符串：忍一时风平浪静，退一步海阔天空。
	LEFT()	从文本字符串的第一个字符开始返回指定个数的字符。	=LEFT(字符串,8)	忍一时风平浪静，
	LEFTB()	基于所指定的字节数返回文本字符串中的第一个或前几个字符。	=LEFTB(字符串,8)	忍一时风
	RIGHT()	根据所指定的字符数返回文本字符串中最后一个或多个字符。	=RIGHT(字符串,8)	退一步海阔天空。
	RIGHTB()	根据所指定的字节数返回文本字符串中最后一个或多个字符。	=RIGHTB(字符串,8)	阔天空。
	MID()	返回文本字符串中从指定位置开始的特定数目的字符，该数目由用户指定。	=MID(字符串,2,4)	一时风平
	MIDB()	根据您指定的字节数，返回文本字符串中从指定位置开始的特定数目的字符。	=MIDB(字符串,3,4)	一时
	LEN()	返回文本字符串中的字符个数。	=LEN(字符串)	16
	LENB()	返回文本字符串中用于代表字符的字节数。	=LENB(字符串)	32
	FIND()	用于从一个文本串中定位另一个文本串的起点并返回字符数	=FIND("一",字符串)	2
	FINDB()	用于从一个文本串中定位另一个文本串的起点并返回字节数	=FINDB("一",字符串)	3
	SEARCH()	用于从一个文本串中定位另一个文本串的起点并返回字符数	=SEARCH("一",字符串)	2
	SEARCHB()	用于从一个文本串中定位另一个文本串的起点并返回字节数	=SEARCHB("一",字符串)	3
	REPLACE()	将旧字符串的指定位置（字符下标）指定字符数的字符串替换为新字符串	=REPLACE(字符串,开始位置,替换字符数,指定字符)	忍二风平浪静，退一步海阔天空。（=REPLACE(字符串,2,2,"二")）
	REPLACEB()	将旧字符串的指定位置（字节下标）指定字节数的字符串替换为新字符串	=REPLACEB(字符串,开始位置,替换字节数,指定字符)
	LOWER()	将一个文本字符串中的所有大写字母转换为小写字母。	'=LOWER("ABC你好哇")	abc你好哇
	UPPER()	将文本转换为大写字母。	=UPPER("asd经济A")	ASD经济A
	REPT()	将文本重复一定次数	=REPT("abcd")	abcdabcd
	SUBSTITUTE()	在文本字符串中用新文本替换旧文本	=SUBSTITUTE(文本字符串,旧文本,新文本)
	TRIM()	除了单词之间的单个空格之外，移除文本中的所有空格	TRIM(字符串)
	TEXT()	自定义单元格格式	TEXT(字符串)
	VALUE()	将表示数字的文本字符串转换为数字	VALUE(字符串)

注意FIND()和SEARCH()区别：

FIND()区分大小写，SEARCH()不区分大小写。FIND()的 find_text参数不能写通配符，SEARCH()的find_text参数可 以写通配符。

❀数学函数

数学函数	功能
ABS(数字)	返回数字的绝对值
INT(数字)	向小取整
MOD(数字1/数字2)	返回两数相除的余数（结果的符号和余数相同）
RAND()	[0,1)之间的随机数
RANDBETWEEN(a,b)	[a,b]之间的随机整数
ROUND(数字,保留小数位数)	将数字四舍五入到指定位数
SQRT(数字)	返回算术平方根
SUM(数值1,数值2,数值3,…)	将单个值、单元格引用或者区域相加，或者将三者的组合相加
SUMIF(条件范围,条件,求和范围)	对范围中符合指定条件的值求和
SUMIFS(求和范围,条件范围1,条件1,条件范围2,条件2...)	计算满足多条件的全部参数的值

• SUMIF(条件范围，条件，求和范围)

  1.条件需要用双引号引起来。

  2.求和范围如果和条件范围一样，求和范围可以省略。

  3.如果条件中需要引用单元格内容，将单元格地址放在引号外面，并且在单元格之前加”&”。

  4.条件中可以用”*”作为通配符代表任意符号出现任意次数。

  5.~可当转义用，将变为它本身，而不是通配符。

  SUMIF(条件范围,条件,求和范围)：

  条件范围引用单元格内容时：

  

  通配符：

  

• SUMIFS(求和范围，条件范围1，条件1,条件范围2,条件2…)

  excel中的比较运算符:
  
  >    小于
  <    大于
  =    等于
  >=   大于等于
  <=   小于等于
  <>   不等于
  

  

♬统计函数

统计函数	功能
AVERAGE()	返回其参数的平均值（如果有其他类型，只计算数值的和除以数值个数）
AVERAGEA()	返回其参数的平均值，包括数字、文本和逻辑值（如果有其他类型，只计算数值的和除以总的个数）
AVERAGEIF(条件范围,条件)	返回区域中满足给定条件的所有单元格的平均值
AVERAGEIFS(条件范围1,条件1,条件范围2,条件2...)	返回满足多个条件的所有单元格的平均值
COUNT()	计算参数列表中数字的个数
COUNTA()	计算参数列表中值的个数
COUNTIF()	计算区域内符合给定条件的单元格的数量
COUNTIFS()	计算区域内符合多个条件的单元格的数量
FREQUENCY(统计对象范围,分箱节点范围)	以垂直数组的形式返回频率分布(分箱)----------------------------------------------根据节点对应的范围对统计对象分段。
MAX()/MIN()	返回参数列表中的最大（最小）值
RANK()	返回一列数字的数字排位（数字排位是相对于列表中其他值的大小）
RANK.AVG()	返回一列数字的数字排位（数字排位是相对于列表中其他值的大小，如果多个值具有相同的排位，则返回平均排位）
RANK.EQ()	返回一列数字的数字排位，其大小与列表中其他值相关，如果多个值具有相同的排位，则返回该组值的最高排位

AVERAGEIF():

COUNTIFS():

FREQUENCY()

♪日期函数

日期函数	功能	用法
DATE()	根据年、月、日三个数值返回其表示的日期	=DATE(年,月,日)
DATEVALUE()	返回由文本字符串表示的日期的十进制数字	=DATEVALUE(“年/月/日”)
TIME()	根据时、分、秒三个数值返回其表示的时间的十进制格式	=TIME(时,分,秒)
TIMEVALUE()	返回由文本字符串表示的时间的十进制数字	=TIMEVALUE(“时:分:秒”)
DAY()	返回序列数表示的某月的天数	=DAY(“年/月/日”)
MONTH()	返回序列数表示的某年的月份	=MONTH(“年/月/日”)
YEAR()	返回序列数表示的年份	=YEAR(“年/月/日”)
SECOND()	返回时间值的秒数	=SECOND(“时:分:秒”)
MINUTE()	返回时间值的分钟数	=MINUTE(“时:分:秒”)
HOUR()	返回时间值的小时数	=HOUR(“时:分:秒”)
TODAY()	返回当前日期	=TODAY()
NOW()	返回当前日期和时间	=NOW()
EDATE()	返回与某个日期相隔N个月的日期的序列数	=EDATE(“年/月/日”,相隔的月份)
EOMONTH()	返回与指定日期相隔N个月份数的月份的最后一天	=EOMONTH(“年/月/日”,相隔的月份)
DATEDIF()	计算两个日期之间的天数、月数、年数	=DATEDIF(日期1,日期2,“Y(或者M或者D)”)

✿逻辑函数

适用版本	逻辑函数	功能	示例和结果
	AND()	用于测试所有条件是否均为TRUE	=AND(条件1,条件2,条件3,…)
	OR()	用于测试是否有为TRUE的条件	=OR(条件1,条件2,条件3,…)
	NOT()	将TRUE或者False的结果取反	=NOT(条件)
	TRUE()	返回TRUE	=TRUE()
	FALSE()	返回FALSE	=FALSE()
	IF()	判断单个条件是否为真	下面单独写出
2019	IFS()	检查是否满足一个或多个条件，且返回第一个符合条件的值	下面单独写出
	IFERROR()	使用IFERROR 函数捕获和处理公式中的错误,公式计算结果为错误时返回指定的值;否则,它将返回公式的结果。	下面单独写出
2019	SWITCH()	根据值列表计算一个值，并返回与第一个匹配值对应的结果，如果不匹配，则可能返回可选默认值。	下面单独写出

• IF()语法

  IF(条件,条件成立对应的结果，条件不成立对应的结果)
  

  

• IFS()语法

  IFS(条件1,结果1,条件2,结果2,条件3,结果3,...)
  

  注意： 后面的条件是在前面的条件不成立时判断的。

  

• IFERROR()语法

  IFERROR(值的表达式,错误时输出的值)
  

  

• SWITCH()语法

  SWICH(运算表达式,值1,结果1,值2,结果2,值3,结果3,...)
  
  计算运算表达式，如果是值1，结果为结果1，如果是值2，结果是结果2,......
  

  **注意：**只能写值，不能写条件！！！

  

✿查找与引用函数

适用版本	逻辑函数	功能	用法
	COLUMN()	返回与列号对应的数字	=COLUMN(数据)
	ROW()	返回与行号对应的数字	=ROW(数据)
	SORT()	对某个区域或数组的内容进行排序	下面有详细的
2021	SORTBY()	对某个区域按照某列数据进行排序（默认升序）	下面有详细的
2021	UNIQUE()	对某范围数据进行去重	下面有详细的
	GETPIVOTDATA()	返回数据透视表中的可见数据
	LOOKUP()	在一行或者一列中查找某个值并从另一行或者列中找到同位置的值	下面有详细的
	VLOOKUP()	按行查找表格或区域内容	下面有详细的
	HLOOKUP()	在表格的首行或数值数组中搜索值，然后返回表格或数组中所在列的指定行中的值	下面有详细的
2021	XLOOKUP()	按行查找表格或区域内容	下面有详细的

• SORT(排序序列，排序依据对应的列号，排序方式)

  a.排序序列：需要排序的对象
  b.排序依据的列号：排序时比较大小对象在排序序列中的位置，如果不辅助，c.默认按照选中数据的第一列数据的大小排序。
  1表示升序（默认）；-1表示降序。
  

  

• SORTBY(排序序列,第一排序标准,排序方式,第二排序标准,排序方式,…)

  注意：排序标准是通过提供比较对象对应的一列数据。

  

• UNIQUE(去重的对象，行列去重方式，去重方式)

  a.去重对象：提供需要去重的一行或者一列数
  b.行列去重方式：去重对象是一列数据按行去重（默认FALSE）、去重对象是一行数据按列去重（TRUE）
  c.去重方式：FALSE（默认），返回原数据去掉重复数据后的结果；TRUE，直接返回原数据中没有重复项的数据。
  

  

• LOOKUP

  LOOKUP(查找对象,查找对象所在的列,查找结果所在的列)
  

  注意：使用LOOKUP做数据查询的时候必须保证查询数据所在的列中，查找对象前面的数据必须是有序的（升序或者降序）

  

• VLOOKUP

  VLOOKUP(查找对象,同时包含查找对象和结果的序列,结果在前面给的序列中的列号,查找方式)
  

  查找方式：TRUE:近似匹配，在查找对象所在的列本身有序时使用；
  		 FALSE:精确匹配，在查找对象所在的列本身无序时使用；
  

  注意：如果结果所在的列在查找对象所在的列的前面，VLOOKUP无法查找。

  

• XLOOKUP

  XLOOKUP(查找对象,查找数据所在列,查找结果所在列)
  XLOOKUP(查找对象,查找数据所在列,查找结果所在列,查找失败返回的默认值)
  

  

• HLOOKUP

  HLOOKUP(查找对象,查找数据和结果所在行,查找结果所在的行数（第二个参数范围内）)