Posted Updated 18 minutes read (About 2751 words)

excel

查重

  1. 数据格式要一致

    在进行集合运算时，我们需要确保参与运算的数据集合的格式一致，否则可能会出现计算错误的情况。

  2. 数据重复要去重

在进行集合运算时，我们需要注意去重，否则可能会出现重复计算的情况。

VLOOKUP(lookup_value, table_array, col_index_num, range_lookup)

=VLOOKUP(要查找的内容、要查找的位置、包含要返回的值的范围内的列号、返回表示为 1/TRUE 或 0/FALSE 的近似或精确匹配项)。

=VLOOKUP（查找值，包含查找值的范围，包含返回值的范围中的列号，近似匹配 (TRUE) 或精确匹配 (FALSE)）。

  1. 要查找的值，也被称为查阅值。

  2. 查阅值所在的区域。 请记住，查阅值应该始终位于所在区域的第一列，这样 VLOOKUP 才能正常工作。 例如，如果查阅值位于单元格 C2 内，那么您的区域应该以 C 开头。

  3. 区域中包含返回值的列号。 例如，如果指定 B2:D11 作为区域，那么应该将 B 算作第一列，C 作为第二列，以此类推。

  4. （可选）如果需要返回值的近似匹配，可以指定 TRUE；如果需要返回值的精确匹配，则指定 FALSE。 如果没有指定任何内容，默认值将始终为 TRUE 或近似匹配。

Alt text

交集 intersection

Alt text

对 range_lookup 使用绝对引用

通过使用绝对引用，可以向下填充公式，这样该公式始终在完全精确的查找区域内查找。

=C2

单元格 C2

单元格 C2 中的值。

=A1:F4

单元格 A1 到 F4

所有单元格中的值，但您必须在键入公式后按 Ctrl+Shift+Enter。

=Sheet2!B2

Sheet2 上的单元格 B2

Sheet2 上单元格 B2 中的值。

若要引用 用途
列 A 和行 10 交叉处的单元格 A10
在列 A 和行 10 到行 20 之间的单元格区域 A10:A20
在行 15 和列 B 到列 E 之间的单元格区域 B15:E15
行 5 中的全部单元格 5:5
行 5 到行 10 之间的全部单元格 5:10
列 H 中的全部单元格 H:H
列 H 到列 J 之间的全部单元格 H:J
列 A 到列 E 和行 10 到行 20 之间的单元格区域 A10:E20

绝对引用、相对引用和混合引用之间的区别

相对引用 公式中的相对单元格引用（如 A1）是基于包含公式和单元格引用的单元格的相对位置。 如果公式所在单元格的位置改变，引用也随之改变。 如果多行或多列地复制或填充公式，引用会自动调整。 默认情况下，新公式使用相对引用。 例如，如果将单元格 B2 中的相对引用复制或填充到单元格 B3，将自动从 =A1 调整到 =A2。

复制的公式具有相对引用

Alt text

复制的公式具有相对引用
绝对引用 公式中的绝对单元格引用（如 $A$1）总是在特定位置引用单元格。 如果公式所在单元格的位置改变，绝对引用将保持不变。 如果多行或多列地复制或填充公式，绝对引用将不作调整。 默认情况下，新公式使用相对引用，因此您可能需要将它们转换为绝对引用。 例如，如果将单元格 B2 中的绝对引用复制或填充到单元格 B3，则该绝对引用在两个单元格中一样，都是 =$A$1。

复制的公式具有绝对引用

Alt text

复制的公式具有绝对引用
混合引用 混合引用具有绝对列和相对行或绝对行和相对列。 绝对引用列采用 $A1、$B1 等形式。 绝对引用行采用 A$1、B$1 等形式。 如果公式所在单元格的位置改变，则相对引用将改变，而绝对引用将不变。 如果多行或多列地复制或填充公式，相对引用将自动调整，而绝对引用将不作调整。 例如，如果将一个混合引用从单元格 A2 复制到 B3，它将从 =A$1 调整到 =B$1。

复制的公式具有混合引用

Alt text

Alt text

复制的公式具有混合引用

使用通配符

如果 range_lookup 为 FALSE 且 lookup_value 为文本，您可在 lookup_value 中使用通配符 - 问号 (?) 和星号 (*)。 问号匹配任何单个字符。 星号匹配任何字符序列。 如果要查找实际的问号或星号，则在字符前键入代字号 (~)。

例如，=VLOOKUP(“Fontan?”,B2:E7,2,FALSE) 将使用最后一个字母搜索 Fontana 的所有实例，该实例可能有所不同。

请确保您的数据中不包含错误的字符

在第一列中搜索文本值时，请确保第一列中的数据没有前导空格、尾部空格、直引号（’ 或 “）与弯引号（‘或“）不一致或非打印字符。 否则，VLOOKUP 可能返回意外的值。

要获得准确的结果，请尝试使用 CLEAN 函数或 TRIM 函数删除单元格中表格值后后面的后置空格。

删除文本中的空格和非打印字符

删除文本中所有不能打印的字符。 对从其他应用程序导入的文本使用 CLEAN，将删除其中含有的当前操作系统无法打印的字符。 例如，可以使用 CLEAN 删除某些通常出现在数据文件开头和结尾处且无法打印的低级计算机代码。

CLEAN 函数用于删除文本中 7 位 ASCII 码的前 32 个非打印字符（值为 0 到 31）。 在 Unicode 字符集中，有附加的非打印字符（值为 127、129、141、143、144 和 157）。 CLEAN 函数自身不删除这些附加的非打印字符。

除了单词之间的单个空格之外，移除文本中的所有空格。 对于从另一个可能含有不规则间距的应用程序收到的文本，可以使用 TRIM。

重要: TRIM 函数专用于剪裁文本中的 7 位 ASCII 空格字符（值 32）。 在 Unicode 字符集中，存在名为不间断空格字符的附加空格字符，其十进制值为 160。 通常在网页中使用此字符作为 HTML 实体  。 TRIM 函数本身不会删除此不间断空格字符。

可使用 SUBSTITUTE 函数，将值较高的 Unicode 字符（值 127、129、141、143、144、157 和 160）替换为 7 位 ASCII 字符（TRIM 和 CLEAN 函数专门用于此类字符）。

将一个单元格的内容分配到相邻列中

您可以分割单元格的内容，并将构成部分分布到多个相邻单元格中。 例如，如果工作表包含”全名”列，可以将该列拆分为两列 -“名字”列和”姓氏”列。

在“数据”选项卡上的“数据工具”组中，单击“分列”。

删除重复值

在” 数据”选项卡 上的”数据 工具 “ (，单击” 删除重复) “。

删除重复值时，唯一的影响是单元格或表格区域的值。 单元格或表格区域外部的其他值不会更改或移动。 删除重复项时，将保留列表中值的第一次出现，但会删除其他相同的值。

由于要永久删除数据，因此建议先将原始单元格或表格区域复制到另一个工作表或工作簿，然后再删除重复值。

突出显示唯一值或重复值

若要突出显示唯一值或重复值，请使用”开始”选项卡上”样式”组中”条件格式”命令

在”开始”选项卡上的”样式”组中，单击”条件格式”的小箭头，然后单击”突出显示单元格规则”，然后选择”重复值”。

TRANSPOSE 函数

你需要切换或旋转单元格。 可通过复制、粘贴和使用“转置​​”选项来执行此操作。 但这样做会创建重复的数据。

首先选择一些空白单元格。 但请确保选择的单元格数量与原始单元格数量相同，但方向不同。

Alt text

输入公式后，Ctrl+Shift+Enter。 为什么？ 因为 TRANSPOSE 函数仅在数组公式中 使用，所以这就是数组公式的完成方式。 简而言之，数组公式是应用于多个单元格的公式。 由于在步骤 1 (选择了多个单元格，不是吗？) ，该公式将应用于多个单元格。 下面是按 Ctrl+Shift+Enter 后的结果：

并集 union

差集 A-B 查找A列中与B列不同的部分

在c列（结果列）第一行输入：=IF(COUNTIF($B:$B,A1)=0,A1,””)
【向下复制公式。鼠标放在C1单元格右下角处，变为黑色十字“＋”时，右单击鼠标，向下拉动进行填充。】

Alt text

COUNTIF

COUNTIF 是一个统计函数，用于统计满足某个条件的单元格的数量；例如，统计特定城市在客户列表中出现的次数。

COUNTIF 的最简形式为：

=COUNTIF(要检查哪些区域? 要查找哪些内容?)

例如：

=COUNTIF(A2:A5,”London”)

=COUNTIF(A2:A5,A4)

数据 数据

苹果 32

橙子 54

桃子 75

苹果 86

公式 说明

=COUNTIF(A2:A5,”苹果”) 统计单元格 A2 到 A5 中包含“苹果”的单元格的数量。 结果为“2”。

=COUNTIF(A2:A5,A4) 统计单元格 A2 到 A5 中包含“桃子”（A4 中的值）的单元格的数量。 结果为 1。

if

IF 函数是 Excel 中最常用的函数之一，它可以对值和期待值进行逻辑比较。

因此 IF 语句可能有两个结果。 第一个结果是比较结果为 True，第二个结果是比较结果为 False。

例如，=IF(C2=”Yes”,1,2) 表示 IF(C2 = Yes, 则返回 1, 否则返回 2)。

Categories

×