Excel快速入门之数据分析

大家好，欢迎来到IT知识分享网。

常用快捷键

快捷键很多，暂时不用都记，用到再说…….

• ctrl + c：复制选定的单元格或区域
• ctrl + v：粘贴剪贴板中选定的单元格或区域
• ctrl + x：剪切选定的单元格或区域
• ctrl + z：撤销上一步操作
• ctrl + shift + L：快速开启对列筛选功能
• ctrl + F：打开“查找”对话框

必会函数

在单元格中输入 = 号，就可以输入想要的函数了

注意格式要求：

任何文本条件或任何含有逻辑或数学符号的条件都必须使用双引号 (“) 括起来。 如果条件为数字，则无需使用双引号。如：“>32”

且和条件相连时要相隔 & ，如：”>=”&DATE(YEAR(B4),MONTH(B4),1)

1.  求和

1.1  sum

官方解释：SUM 函数添加值。 你可以将单个值、单元格引用或是区域相加，或者将三者的组合相加。

使用起来也很简单如：

• =SUM(A2:A10) 添加单元格 A2：10 中的值。
• =SUM(A2:A10, C2:C10) 添加单元格 A2：A10 以及单元格 C2：C10 中的值。

选中单元格后 Alt+= 快速求和快捷键

注意：

一般的公式相加，如 =A1+B1+C1，当其中一列被删除时，公式不会更新排除已删除的行，会返回#REF!错误。但是 sum 函数会自动更新

1.2  sumif

官方解释：可以使用 SUMIF 函数对 范围 中符合指定条件的值求和。

那与 sum 函数不同就是可以给它增加单个条件，是单条件求和

语法格式：

SUMIF(range, criteria, [sum_range])

• range：必需。从这个区域找值
• criteria：必需。值要满足什么条件。格式可以为数字、表达式、单元格参考、文本或函数的形式。 可包括的通配符字符 – 问号（？）以匹配任意单个字符，星号（*）以匹配任意字符序列。 如要查找实际的问号或星号，在该字符前键入波形符（~）
• sum_range：可选。要对哪个区域求和。其尺寸一般与 range 的尺寸相同

例子：

=SUMIF(B2:B25,”>5″,A2:A25)

表示在B2:B25的范围中找大于5的数值的行，求这些行在A2:A25这个范围的和。

1.3  sumifs

官方解释：用于计算其满足多个条件的全部参数的总量。

语法格式：

SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)

• sum_range：必需。要求和的单元格区域
• criteria_range1：必需。要满足的第一个条件的单元格区域
• criteria1：必需。定义将计算 criteria_range1 中的哪些单元格的和的条件
• criteria_range2, criteria2：可选。还可以添加的条件，最多可以添加127个区域/条件

例子：

=SUMIFS(A2:A9, B2:B9, “=香*”, C2:C9, “卢宁”)

计算以“香”开头并由“卢宁”售出的产品的总量。然后计算 Sum_range A2:A9 中同时满足这两个条件的单元格的总量。

1.4  subtotal

可以通过该函数实现，对指定单元格区域进行 function_num 中指定的分类汇总的函数。特点是当对指定单元格区域进行筛选时，结果会跟随改变，如 sum 函数是不会跟着变的

语法格式：

SUBTOTAL(function_num,ref1,[ref2],…)

• function_num：必需。数字 1-11 或 101-111，用于指定要为分类汇总使用的函数。如果使用 1-11，将包括手动隐藏的行，如果使用 101-111，则排除手动隐藏的行；始终排除已筛选掉的单元格。

隐藏行是啥：程序中“开始”选项卡上“单元格”组中“格式”命令的“隐藏和取消隐藏”子菜单下面

Function_num (包括隐藏行)	Function_num (忽略隐藏行)	函数
1	101	AVG
2	102	COUNT
3	103	COUNTA
4	104	MAX
5	105	MIN
6	106	PRODUCT
7	107	STDEV
8	108	STDEVP
9	109	SUM
10	110	VAR
11	111	VARP

• ref1：必需。要对其进行分类汇总计算的第一个命名区域或引用。
• ref2：可选。要对其进行分类汇总计算的第 2 个至第 254 个命名区域或引用。

例子：

=SUBTOTAL(9,A2:A5)

使用 9 作为第一个参数,算出的单元格 A2:A5 中分类汇总的值之和。

2.  判断

if 函数

它可以对值和期待值进行逻辑比较。

IF 函数中有两个结果。 第一个结果是比较结果为 True，第二个结果是比较结果为 False。

语法格式：

if(条件,结果为True时返回值,结果为False时返回值)

基础用法：

=IF(C2=1,”Yes”,”No”)

当 C2 等于 1 时，返回 Yes，否则返回 No

也可以嵌套使用：

=IF(C2>1000,IF(B2<50,”合格”,”不合格”),”合格”)

当 C2 大于 1000 时，且如果 B2<50 则合格，否则不合格

3.  表格匹配

3.1  vlookup 函数

在表格或区域中按行匹配查找内容

注意：要查找的内容一定要位于要查找单元格区域最左边的列，不然会报错

语法格式：

VLOOKUP(查找值，包含查找值的范围，包含返回值的范围中的列号，近似匹配 (1/TRUE) 或精确匹配 (0/FALSE))

例子：

=VLOOKUP(B30,’数据1-8月’!D:E,2,FALSE)

返回精确查找 B30 这个单元格在D:E单元格区域的第二列

3.2  xlookup 函数

XLOOKUP 函数按行查找表或区域中的项，不再受 vlookup 函数要匹配的单元格一定要在单元格区域最左边的这条限制

注意：XLOOKUP 在 Excel 2016 和 Excel 2019 中不可用

语法格式：

XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode]) 

参数	说明
lookup_value	必需。要查找的值
lookup_array	必需。要搜索的数组或区域
return_array	必需。要返回的数组或区域
if_not_found	可选。如果没找到匹配值，则返回该定义的值。            没定义该值的话，默认返回 #N/A
match_mode	可选。指定匹配的类型。 0   完全匹配，如果未找到则返回 #N/A。（默认） -1  完全匹配，如果未找到则返回下一个较小的项。 1   完全匹配，如果未找到则返回下一个较大的项。 2   通配符匹配，如 * 、？、~ 等
search_mode	可选。指定搜索模式。 1  从第一项开始搜索。（默认） -1 从最后一项开始搜索。 2  执行依赖于 lookup_array 按升序排序的二进制搜索。 如果未排序，将返回无效结果。

3.3  match 函数

官方解释：使用 MATCH 函数在范围单元格中搜索特定的项，然后返回该项在此区域中的相对位置。

返回的是位置，不是这个元素本身，一般搭配 index 函数使用，为 index 提供单元格位置

语法格式：

MATCH(lookup_value, lookup_array, [match_type])

• lookup_value    必需。 要匹配的值。 参数可以为值（数字、文本或逻辑值）或对数字、文本或逻辑值的单元格引用。
• lookup_array    必需。 要搜索的单元格区域。
• match_type    可选。 数字 -1、0 或 1。 指定匹配规则。 此参数的默认值为 1。为 1时，查找小于或等于 lookup_value 的最大值。为 0 时，查找完全等于 lookup_value 的第一个值。为 -1 时，查找大于或等于 lookup_value 的最小值。

例子：

=MATCH( 25 ,A1:A3 ,0)

如果 A1:A3 区域中包含值 5、25 和 38，则返回数字 2，因为 25 是该区域中的第二项。

3.4  index 函数

官方解释：INDEX 函数返回表格或区域中的值或值的引用。

语法格式：

INDEX(array , row_num , [column_num])

• array    必需。 要在哪个单元格区域或数组常量中匹配
• row_num    必需，要匹配的行。当为 0 时，返回所有行
• column_num    可选。 要匹配的列。当为 0 时，返回所有列

例子：

=INDEX(A2:B3 ,2 ,1)

返回位于区域 A2:B3 中第二行和第一列交叉处的数值

index 和 match 两个函数的组合拳非常实用，一定要掌握

使用小tips

为了使长公式更易于阅读，可在编辑栏中插入换行符。 只需在将文本换到新行前按 Alt+Enter。

掌握了以上知识，已经能开发简单的周报了！加油