Excel知识点大全

Excel函数

查找引用函数

LOOKUP函数

当需要查询一行或一列并查找另一行或列中的相同位置的值时，会使用其中一个查找和引用函数 LOOKUP。

基本语法

= LOOKUP(lookup_value, lookup_vector, [result_vector])

  • lookup_value：查找的值。 Lookup_value可以是数字、文本、逻辑值、名称或对值的引用。
  • lookup_vector：查找的值所在区域。lookup_vector中的值可以是文本、数字或逻辑值。
  • result_vector（可选）：返回值的所在列。 result_vector参数必须与 lookup_vector参数大小相同。

二分原理

LOOKUP是遵循二分法原理的查找函数。二分法则是从查询区域的二分之一处开始查询，如果没有查询到结果，就通过判断选择下一个二分之一处查询，直到查询到结果。如果没有找到查询值，就将查找区域中小于或等于查找值的最大值进行匹配。如果查找的值小于查找区域中的最小值，则 LOOKUP 会返回 #N/A 错误值。

  • LOOKUP的二分法查找，是跳跃式的查找。它默认被查找的那组值是从小到大排列的。如果遇到小的就向下找更大的，如果遇到大的就向上找更小的。
  • 当二分值等于查找的值时，向下逐个查，如果向下的值也等于查找的值时，以最后一个相邻且相等的值作为符合条件的结果。
    由于二分法的查询方向的判断，所以查询值需要按照升序排列。如果顺序错误，二分法就查询的结果就会与正确值背道而驰。

特殊公式

  • =LOOKUP(1,0/(条件判断),返回结果范围）
  • =LOOKUP(1,0/((条件判断1) * (条件判断2）* … *（条件判断n)),返回结果范围）

如果有重复值，则返回最后一个符合条件的结果。

举例说明

1、查询成绩

A B
1 姓名 成绩
2 张三 75
3 李四 80
4
5 张三 =LOOKUP(A5,A1:A3,B1:B3) （75）
2、根据不同的分值，给出相应的评语
=LOOKUP(B2,{0,50,60,75,86,96;”很差”,”差”,”一般”,”较好”,”优秀”,”能手”})
=LOOKUP(B2,{0,50,60,75,86,96},{“很差”,”差”,”一般”,”较好”,”优秀”,”能手”})

VLOOKUP函数

用于搜索用户查找范围中的首列中满足条件的数据，并根据指定的列号，返回对应的值。

基本语法

= VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

  • lookup_value：查找的值。
  • table_array：查找范围。必须包含查找值和返回值，且第一列必须是查找值。
  • col_index_num：用户指定返回值在数据查找范围中的第几列。
  • [range_lookup]（可选）查找方式 ─ 默认采用模糊匹配。如果需要返回值的模糊匹配，可以指定TRUE、非0的数值或省略；如果需要返回值的精确匹配，则指定FALSE或0。

注意事项

  • 如果按模糊匹配的方式查找，VLOOKUP函数将把小于或等于查找值的最大值作为自己的查询结果。

  • 如果第四参数为TRUE、非0的数值或省略，则为模糊匹配

    1
    2
    3
    =VLOOKUP(A2,$E$2:$F$15,2,TRUE)
    =VLOOKUP(A2,$E$2:$F$15,2,1)
    =VLOOKUP(A2,$E$2:$F$15,2)

  • 如果第四参数设置为FALSE、0或者缺省参数（不写任何内容，参数位置空出来），则为精确匹配

    1
    2
    3
    =VLOOKUP(A2,$E$2:$F$15,2,FALSE)
    =VLOOKUP(A2,$E$2:$F$15,2,0)
    =VLOOKUP(A2,$E$2:$F$15,2,)

  • 如果按模糊匹配的方式查找，必须将第二参数的数据表，按首列数据进行升序排序，否则不一定返回正确的结果。

举例说明

A B C
1 货品 单价 数量
2 笔记本 6750 500
3 手机 4800 300
4
5 货品 数量
6 手机 =VLOOKUP(A6, A1:C3, 3, 0) （300）

统计求和函数

SUMIF函数

使用 SUMIF 函数可以对表范围中符合指定条件的值求和。

基本语法

= SUMIF(range, criteria, [sum_range])

  • range：条件区域，用于条件判断的单元格区域。每个范围内的单元格必须是数字或名称、数组或包含数字的引用。空白和文本值将被忽略。选定的范围可以包含标准Excel格式的日期。
  • criteria：求和条件，由数字、表达式、单元格参考、文本或函数的形式来定义将添加哪些单元格。**任何文本条件或任何含有逻辑或数学符号的条件都必须写在双引号中间 (“) **。如果条件为数字，则无需使用双引号。
  • sum_range(可选)：求和区域，需要求和的单元格、区域或引用。 sum_range的行、列数应该与第一参数的相同。如果不这样做，求和结果可能会受到影响。

注意事项

  • 忽略求和区域中的错误值：SUM不会忽略参数中的错误值，但如果使用SUMIF函数，就能通过设置参数避免他们。
    • = SUMIF(A2:A10, “<=9E+307“)
    • 9E+307相当于9×10^307，是EXCEL单元格中可以输入的最大数值，因为错误值比所有值大，只对小于或等于9E+307的数据求和就能规避错误值。
  • 如果只设置两个参数，第1参数将同时扮演条件区域和求和区域两个角色。
  • 第1参数和第3参数中的数据是一一对应的关系，只有当条件区域中的数据满足第2参数的求和条件时，才将对应的求和数据相加。
  • 如果第3参数的求和区域不等于第1参数的条件区域，计算时，SUMIF会对求和区域进行扩展或收缩，使其行列数与条件区域相同。因此，除非必须需要，不建议第3参数和第1参数尺寸设置不同。
  • SUMIF函数允许你替它设置多行多列的条件和求和区域。面对多行多列的条件区域，在计算时，SUMIF函数会依次判断这个区域中的各个数据是否满足求和的条件，如果满足则将第3参数中对应位置的数据相加，再输出最后的求和结果。

举例说明

SUMIFS函数

用于解决多条件求和问题的函数。

基本语法：

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

  • sum_range：求和区域。
  • criteria_range1：条件区域。
  • criteria1：求和条件。

注意事项

  • =SUMIFS(求和区域，条件1区域，条件1，条件2区域，条件2，……条件n区域，条件n)，最多可以给函数设置255个参数，所以在使用SUMIFS函数时，最多可以为其指定127个求和条件。
  • 与SUMIF函数不同的是：当求和区域与条件区域行列数不等时，SUMIFS函数并不会重新确定一个适合的求和区域。所以，在替函数设置参数时，应保证每个条件区域的行列数都与求和区域的行列数相同，否则公式将返回错误值。

举例说明

AVERAGEIF函数

求满足某个条件的数据的平均值，语法类似SUMIF函数。

基本语法

= AVERAGEIF**(**range, criteria, [average_range])

  • range：条件区域 。要计算平均值的一个或多个单元格，其中包括数字或包含数字的名称、数组或引用。

  • criteria：求值条件。数字、表达式、单元格引用或文本形式的条件，用于定义要对哪些单元格计算平均值。如条件可以表示为 41、”>41”、D3、”香蕉” 或 “41”。

  • average_range(可选)：求值区域。要计算平均值的实际单元格集。若忽略，则使用 range。

注意事项

  • 第3参数可以省略，如果省略，函数会将第1参数同时当做条件区域和求值区域。如果你设置了一个与第1参数尺寸不同的第3参数，函数计算时也会重新确定求值区域的大小。

举例说明

AVERAGEIFS函数

AVERAGEIFS函数用来解决按多条件求平均值的问题，其计算规则和使用方法，与SUMIFS函数完全相同。

基本语法

= AVERAGEIFS(average_range, criteria_range1, criteria1, [criteria_range2,criteria2],…)

  • average_range：求平均区域。
  • criteria_range1：条件区域。
  • criteria1：求平均条件。

举例说明

COUNTIF函数

COUNTIF函数用来解决条件计数的问题。

基本语法

= COUNTIF(range，criteria)

  • range: 单元格区域。
  • criteria: 计数条件。

举例说明

COUNTIFS函数

COUNTIFS函数用来解决多条件计数的问题。

基本语法

= COUNTIFS(criteria_range1, criteria1, criteria_range2, criteria2,…)

  • criteria_range: 条件区域。
  • criteria: 计数条件。

举例说明

数据处理函数

数值取舍

ROUND函数

ROUND函数用来按指定的小数位数，对数值进行四舍五入。

基本语法

= ROUND(number, num_digits)

  • number: 取舍的数值。
  • num_digits: 保留的小数位数。

举例说明

ROUNDUP函数

ROUNDUP函数和ROUND函数功能相似，不同之处在于ROUNDUP函数总是向下舍入数字（朝着远离0的方向）。

基本语法

= ROUNDUP(number, num_digits)

  • number: 需要向下舍入的数值。
  • num_digits: 保留的小数位数。

举例说明

ROUNDDOWN函数

ROUNDUP函数和ROUND函数功能相似，不同之处在于ROUNDUP函数总是向上舍入数字。（朝着靠近0的方向）

基本语法

= ROUNDUP(number, num_digits)

  • number: 需要向上舍入的数值。
  • num_digits: 保留的小数位数。

举例说明

TRUNC函数

TRUNC函数可以截断数值的小数部分，只保留整数部分，而不管这个数的小数部分是什么。

基本语法

= TRUNC(number, [num_digits])

  • number: 需要保留整数部分的数值。
  • num_digits(可选，默认为0): 指定取整精度。

举例说明

INT函数

INT函数可以舍去数值的小数部分，只保留整数部分，向下取整。

INT函数保留小于或等于参数中数值的最大整数。

基本语法

= INT(number)

举例说明

ROUND、ROUNDUP、ROUNDDOWN、INT、TRUNC函数区别

  • TRUNC函数和ROUNDDOWN函数功能一样。但ROUNDDOWN函数第二参数是必需的。
  • INT函数只有一个参数；TRUNC、ROUND、ROUNDUP 和 ROUNDDOWN 有两个参数，第一个参数是要取舍的数，第二个参数是保留的小数位。
  • INT函数向下取整；TRUNC函数截断；ROUND函数四舍五入；ROUNDUP函数朝远离0的方向向上取舍；ROUNDDOWN函数朝靠近0的方向向下取舍。

文本处理

CONCATENATE函数

CONCATENATE函数可将最多 255 个文本字符串联接成一个文本字符串。联接项可以是文本、数字、单元格引用或这些项的组合，且必须将希望在结果中显示的任意空格或标点符号指定为使用双引号括起来的参数。

基本语法

= CONCATENATE(text1, [text2], …)

举例说明

image-20230327114417233

PHONETIC函数

PHONETIC函数用来提取文本字符串中的拼音字符，是为日文版EXCEL设计的函数，但在中文版可以用来连接单元格中文本类型的数据。

基本语法

= PHONETIC(reference)

注意事项

  • PHONETIC函数只能有一个参数，且必须为单元格引用。
  • 在计算时，PHONETIC函数会对单元格中的公式、逻辑值、数值和错误值视而不见。

举例说明

image-20230327115733259

image-20230327115656504

LEN函数

返回文本字符串中的字符数。

基本语法

= Len(text)

举例说明

image-20230327121422445

LENB函数

返回文本字符串中用于代表字符的字节数。区别于LEN函数，LEN函数的功能为返回文本字符串中的字符数。

基本语法

= LenB(text)

举例说明

image-20230327121743877

EXACT函数

比较两个文本字符串，如果它们完全相同，则返回 TRUE，否则返回 FALSE。 函数 EXACT 区分大小写，但忽略格式上的差异。 使用 EXACT 可以检验在文档中输入的文本。

基本语法

= EXACT(text1, text2)

举例说明

image-20230327133129413

查找字符

FIND函数

Find函数用来对原始数据中某个字符串进行定位，以确定其位置。Find函数进行定位时，总是从指定位置开始，返回找到的第一个匹配字符串的位置，而不管其后是否还有相匹配的字符串。

基本语法

= FIND(find_text,within_text,[start_num])

  • find_text: 要查找的字符串。

  • within_text : 包含要查找关键字的单元格。就是说要在这个单元格内查找关键字。

  • [start_num]（可选）: 指定开始进行查找的字符数。比如start_num为1，则从单元格内第一个字符开始查找关键字。如果忽略 start_num，则假设其为 1。

举例说明

image-20230327134911688

image-20230327134951839

SEARCH函数

用来返回一个指定字符或文本字符串在字符串中第一次出现的位置。从左到右查找，忽略英文字母的大小写。

基本语法

SEARCH(find_text,within_text,[start_num])

  • find_text：要查找的文本字符串。

  • within_text：要在哪一个字符串查找。

  • [start_num]（可选）：从within_text的第几个字符开始查找。当从第一个字符开始查找时可省略。但实际上，不管你输入多少，它都是从第一个字符开始查找，只是会跳过从开始到你输入数字中间的字符。

举例说明

image-20230327140622637

FIND函数和SEARCH函数的区别

  • 只有FIND函数能区分大小写字母
  • 只能在SEARCH函数中使用通配符，如果要在公式中查找字符“?”或”*“的位置，需要在作为普通字符的”?”或”*“加上波形符”~”以作区别

image-20230327165421300

image-20230327165534360

截取字符

快捷方式

Ctrl+D、Ctrl+Enter、Ctrl+E

  • 如果要对多处数据批量录入，只需选中多重数据（区域不连续时按住ctrl再选择），然后输入内容，再按下Ctrl+Enter即可。
  • 如果需要在空白处填充数值或者文字，Ctrl+A全选区域——Ctrl+G定位——定位到空值——输入需要填充的文字或者数值——Ctrl+Enter。这里也可以填充公式，比如输入=A1*A2，就可以填充A1和A2单元格乘积的结果。

Ctrl+F、Ctrl+H

  • 查找和替换

Shift+F3

  • 显示插入的函数的参数提示框

Alt+=

  • 选中需要求和的数据区域以及求和值所处的单元格，然后按Alt+=,一键求和

格式转换

文本转数字

乘以数字1

  • 在其他单元格输入数字1，并复制。
  • 选择要转换为数字的文本区域，点击“选择性粘贴”，选择“乘”。

分列

  • 选择要转换为数字的文本所在列，点击分列

- -

  • 在单元格加“–”，即让文本变为负数再变成正数。

数据处理

批量合并相同内容单元格

  • 将光标定位在需要合并相同内容单元格的列，点击【数据】选项卡【分级显示】组中的【分类汇总】按钮，打开【分类汇总】对话框，点确认。
  • 在生成的分类列中打开定位功能，选择空值，点击合并后居中。
  • 用格式刷把分类列格式刷给原数据列。
  • 点击【数据】选项卡【分级显示】组中的【分类汇总】按钮，打开【分类汇总】对话框，点击【全部删除】按钮，删除分类汇总。
  • 最后删除空白列。

复制筛选后的数据

  • Ctrl+A 全选
  • Alt+; （选定当前选定区域中的可视单元格）或者Ctrl+G，定位条件选可见单元格
  • Ctrl+C 复制 Ctrl+V 粘贴

改变某一列的顺序

  • 按住那一列，鼠标移动到绿色边框上，Shift+拖动即可。

超过15位的数字文本如何标记重复值

  • 文本型数字在参与公式运算的时候，Excel会默认将它作为数字进行运算，但由于Excel处理数字精度只有15位，因此如果使用默认的重复值设置格式规则会出错。
  • 因此，解决问题的方法，可以在文本型数字的任意地方添加些文本，使文本型数字变成一个真正的文本(将文本连接一个”*”，Excel会把数字当作文字)，就可以利用条件格式或者函数来标记重复项。
  • 例如：=countif($A$2:$A$10,A2&”*”)

插入图片技巧

在移动时，按住ALT进行拖拽，图片会自动吸附单元格。

批量创建工作表

  • 1、选中需要创建的工作表名称区域(要在最前面额外加一个单元格作为工作表名称)，点击Excel表格菜单区域插入中的数据透视表

  • 2、在弹出“数据透视表”设置对话框，设置相应属性，下方选择放置数据透视图的位置，可以新建工作表，也可以在原有工作表创建，设置完成点击“确定”。

  • 3、Excel表格右侧区域弹出设置面板，这时将数据透视图字段中的“工作表名称”拖拽至下方“筛选”框内

  • 4、在数据图示表下拉菜单点击“选项”，点击“显示报表筛选页”即可，弹出“显示报表筛选页”对话框，鼠标单击选中“工作表名称”，点击“确定”完成。

批量创建Excel文件

  • 1、右键任意一个工作表标签，在弹出的下拉列表中选择“查看代码”。即弹出代码窗口。
  • 2、输入以下代码,点击运行
1
2
3
4
5
6
7
8
9
10
11
Sub SaveSeparately() 
Dim sht As Worksheet 
Application.ScreenUpdating = False 
ipath = ThisWorkbook.Path & "\" 
For Each sht In Sheets 
sht.Copy 
ActiveWorkbook.SaveAs ipath & sht.Name & ".xls" 
ActiveWorkbook.Close 
Next 
Application.ScreenUpdating = True 
End Sub

注意事项

不添加标题行

  • 在EXCEL中，一个连续数据区域的首行就是这个区域的标题行，标题行标明了每列数据的属性和类别，是对数据进行筛选、排序等操作的依据。

  • 数据表只用来保存有用的信息，作为数据表，不需要为其设置表格标题。

不合并单元格

  • 在数据表中，应该杜绝使用合并单元格。因为合并单元格对数据表的破坏性非常大，严重影响后期的数据统计。不让多条记录或多个字段共用一个信息，是建立数据表应该遵循的一个原则。

一个字段只记录一类数据

  • 如果将多种类别的数据保存在同一列中，会给汇总数据带来麻烦。
  • 在数据表中，不能使用一列（字段）来保存多种数据，也不能使用多列（字段）来保存同一属性的数据。

字符之间不输入空格或其他字符

  • 添加空格后的数据，会给查询和汇总数据带来麻烦。
  • 不要在字符首尾或中间添加空格或其他字符，不要使用<Alt+Enter>组合键对数据换行等。

不同位置的同一数据必须完全一致

  • 杜绝使用同音异形字，禁止混合使用全称或简称，要使用统一的标点符号、括号等。

为每条记录设置一个唯一的标识

  • 数据表中的某些关键信息可能存在重复，而表中保存的信息不足以让EXCEL区分它们，从而为查询数据信息造成障碍。
  • 在建立数据表时，为不同的记录设置一个唯一的标识信息是必须的。

不要在数据表中对数据进行分类汇总

  • 在数据表中添加了汇总的行，虽然得到了汇总结果，但同时也改变了数据表本身的结构，在数据表中增加了多余的数据信息。如果在这张表的基础上进行其他统计分析，还得剔除这些多余的数据信息，增加不必要的工作量。