Excel表格公式大全

Excel 函数笔记

1. TEXTJOIN 函数

• 功能：将多个文本字符串连接成一个单一的字符串，并可以指定分隔符和是否忽略空值。
• 语法：

TEXTJOIN(分隔符, 是否忽略空值, 要连接的文本1, [要连接的文本2], ...)

• 参数说明：

  • 分隔符：用于连接文本的字符或字符串，例如逗号 ,、分号 ; 或换行符 CHAR(10)。
  • 是否忽略空值：布尔值，TRUE 表示忽略空值，FALSE 表示保留空值。
  • 要连接的文本：可以是单元格、单元格范围或直接输入的文本。

• 使用场景：

  • 将多列数据合并为一列，例如将姓名和地址合并。
  • 将多行数据合并为一个单元格，例如将一个列表中的所有项目合并为一个长字符串。

• 注意事项：

  • 如果分隔符为空字符串 “”，则不会在文本之间添加任何分隔符。
  • 如果要连接的文本范围中包含错误值（如 #N/A），公式会返回错误。可以通过 IFERROR 函数处理错误值。
    Excel 2016 及更高版本支持此函数。

CONCATENATE 函数

功能：将多个文本字符串连接成一个单一的字符串。
语法：
excel
复制
CONCATENATE(文本1, [文本2], …)
参数说明：
文本：可以是单元格、单元格范围或直接输入的文本。
使用场景：
将多个文本片段组合成一个完整的字符串，例如将名字和姓氏拼接。
与 CHAR 函数结合，生成特殊字符（如换行符）。
注意事项：
CONCATENATE 不支持分隔符，如果需要分隔符，可以使用 TEXTJOIN。
如果需要连接的文本中包含空值，空值会被忽略。
Excel 2007 及更高版本支持此函数。

CHAR 函数

功能：返回指定数字代码对应的字符。
语法：
excel
复制
CHAR(数字代码)
参数说明：
数字代码：字符的 ASCII 码。例如，10 是换行符，13 是回车符。
使用场景：
在文本中插入特殊字符，例如换行符（CHAR(10)）或制表符（CHAR(9)）。
注意事项：
需要了解常用字符的 ASCII 码：
换行符：10
回车符：13
制表符：9
空格：32
Excel 2003 及更高版本支持此函数。

ISNUMBER 函数

功能：检查某个值是否为数字。
语法：
excel
复制
ISNUMBER(值)
参数说明：
值：可以是单元格、单元格范围或直接输入的值。
使用场景：
与 SEARCH 或 MATCH 函数结合，判断是否找到匹配项。
用于数据验证，检查输入是否为数字。
注意事项：
如果值是数字，返回 TRUE；否则返回 FALSE。
Excel 2003 及更高版本支持此函数。

SEARCH 函数

功能：在文本中查找指定的字符或字符串，并返回其起始位置。
语法：
excel
复制
SEARCH(要查找的文本, 在哪里查找, [起始位置])
参数说明：
要查找的文本：需要查找的字符串。
在哪里查找：要搜索的文本范围或单元格。
起始位置（可选）：从哪里开始查找，默认为 1。
使用场景：
检查一个字符串是否包含另一个字符串。
与 ISNUMBER 结合，判断是否找到匹配项。
注意事项：
如果找到匹配项，返回匹配项的起始位置（数字）；否则返回错误值 #VALUE!。
SEARCH 函数不区分大小写。如果需要区分大小写，可以使用 FIND 函数。
Excel 2003 及更高版本支持此函数。

IF 函数

功能：根据条件返回不同的值。
语法：
excel
复制
IF(条件, 值如果为真, [值如果为假])
参数说明：
条件：需要判断的逻辑表达式。
值如果为真：如果条件为 TRUE，返回的值。
值如果为假（可选）：如果条件为 FALSE，返回的值。
使用场景：
根据条件返回不同的结果，例如判断成绩是否及格。
与 ISNUMBER 和 SEARCH 结合，根据查找结果返回不同的值。
注意事项：
IF 函数可以嵌套使用，但嵌套层数过多会导致公式难以理解。
如果需要对多行或多列进行条件判断，可以将 IF 函数作为数组公式使用（按 Ctrl+Shift+Enter 输入）。
Excel 2003 及更高版本支持此函数。

数组公式

功能：对多个单元格或范围进行批量计算。
使用方法：
输入公式后，按 Ctrl+Shift+Enter，而不是直接按 Enter。
Excel 会自动在公式两边加上 {}，表示这是一个数组公式。
使用场景：
对多行或多列数据进行条件判断或计算。
例如，IF(ISNUMBER(SEARCH(A2, TC表!B:B)), TC表!G:G, “”) 需要作为数组公式使用。
注意事项：
数组公式会占用更多计算资源，如果数据量较大，可能会导致性能问题。
在 Excel 365 或 Excel 2021 中，数组公式已经自动支持动态数组，无需按 Ctrl+Shift+Enter 输入。
示例公式
假设你的目标是：
检查 A2 的内容是否在 TC表!B:B 中的每一行出现。
如果出现，则从 TC表!G:G 中取出对应的值。
将所有符合条件的值用逗号和换行符连接起来。
可以使用以下公式（需要按 Ctrl+Shift+Enter 输入）：
excel
复制
=TEXTJOIN(“,” & CHAR(10), TRUE, IF(ISNUMBER(SEARCH(A2, TC表!B:B)), TC表!G:G, “”))
公式解析
“,” & CHAR(10)：分隔符，用逗号和换行符分隔。
TRUE：忽略空值。
IF(ISNUMBER(SEARCH(A2, TC表!B:B)), TC表!G:G, “”)：
对 TC表!B:B 的每一行进行判断。
如果 A2 的内容在 TC表!B:B 的某一行中出现，则返回 TC表!G:G 中对应的值。
否则返回空字符串。
数组公式：按 Ctrl+Shift+Enter 输入，使公式对整个范围进行批量计算。