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补全
  

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

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

❥文本记忆输入

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

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

2.4数据验证

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

❀数据有效性

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

❀圈释无效数据

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

; 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.11 Excel 数据类型

❥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#2323.2677#.##23.272.33455550.0002.3352.7383830.00元2.74元34总共#个总共34个张三”姓名:”@姓名:张三abc@*aabcaaaaaaa12345678#,##12,345,67823[红色][

❀自定义格式语法

格式说明#数字占位符；四舍五入；不保留无意义的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(字符串)

32FIND()用于从一个文本串中定位另一个文本串的起点并返回字符数 =FIND("一",字符串) FINDB()

用于从一个文本串中定位另一个文本串的起点并返回字节数 =FINDB("一",字符串)

3SEARCH()用于从一个文本串中定位另一个文本串的起点并返回字符数 =SEARCH("一",字符串) SEARCHB()

用于从一个文本串中定位另一个文本串的起点并返回字节数 =SEARCHB("一",字符串)

3REPLACE()将旧字符串的指定位置（字符下标）指定字符数的字符串替换为新字符串 =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中的比较运算符:

>    小于
<    &#x5927;&#x4E8E;
=    &#x7B49;&#x4E8E;
>=   &#x5927;&#x4E8E;&#x7B49;&#x4E8E;
<= 小于等于 <>   &#x4E0D;&#x7B49;&#x4E8E;
</=>

♬统计函数

统计函数功能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()判断单个条件是否为真下面单独写出2019IFS()检查是否满足一个或多个条件，且返回第一个符合条件的值下面单独写出 IFERROR

()使用 IFERROR

函数捕获和处理公式中的错误,公式计算结果为错误时返回指定的值;否则,它将返回公式的结果。下面单独写出2019SWITCH()根据值列表计算一个值，并返回与第一个匹配值对应的结果，如果不匹配，则可能返回可选默认值。下面单独写出

• 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

()对某个区域按照某列数据进行排序（默认升序）下面有详细的2021UNIQUE()对某范围数据进行去重下面有详细的 GETPIVOTDATA

()返回数据透视表中的可见数据LOOKUP()在一行或者一列中查找某个值并从另一行或者列中找到同位置的值下面有详细的 VLOOKUP

()按行查找表格或区域内容下面有详细的 HLOOKUP

()在表格的首行或数值数组中搜索值，然后返回表格或数组中所在列的指定行中的值下面有详细的2021 XLOOKUP

()按行查找表格或区域内容下面有详细的

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

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

* SORTBY(排序序列,第一排序标准,排序方式,第二排序标准,排序方式,…) 注意：排序标准是通过提供比较对象对应的一列数据。
* UNIQUE(去重的对象，行列去重方式，去重方式)

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

* LOOKUP

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

* VLOOKUP

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

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

* XLOOKUP

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

* HLOOKUP

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

Original: https://blog.csdn.net/simple_daytime/article/details/126473578
Author: simple_daytime
Title: excel第一天-数据分析与Excel