一些用于数据整理的excel函数

我们经常要从外部数据源（如数据库、文本文件或网页等）将数据导入excel中，但是此类数据往往比较混乱，无法满足我们的要求，因此在进行数据分析之前，需要将这些数据进行整理清洗，excel由于将数据的管理和展示都在同一层面上，并且其函数功能也不逊色，因此在大多数情况下都可以使用excel来进行初步的数据整理：

1.删除通用前导字符串（例如其后紧跟冒号和空格的标签）或后缀（例如字符串后面的已无效或不必要的插入语），在字符串内查找和替换子字符串、提取字符串的特定部分或确定字符串的长度等字符串操作，可使用以下函数：
FIND、FINDB
SEARCH、SEARCHB
REPLACE、REPLACEB
SUBSTITUTE
LEFT、LEFTB
RIGHT、RIGHTB
LEN、LENB
MID、MIDB

2.某些情况下，文本表现为混用的形式，这尤其表现在其大小写形式方面。您可以使用三个大小写转换函数中的一个或多个函数，将文本转换为小写字母（如电子邮件地址）、大写字母（如产品代码）或专有名称（如名称或书名）。
LOWER()：将文本字符串中的所有大写字母转换为小写字母。
PROPER()：将文本字符串的首字母以及文本中任何非字母字符之后的其他字母转换成大写。将所有其他字母转换成小写字母。
UPPER()：将文本转换为大写字母。

3.某些文本值包含前导空格、尾随空格或多个嵌入空格字符（Unicode 字符集值 32 和 160），或非打印字符（Unicode 字符集值 0 到 31、127、129、141、143、144 和 157）。在排序、筛选或搜索时，这些字符有时可能会引发意外结果。例如，在外部数据源中，用户可能会无意地犯一些拼写错误，例如添加多余空格字符，或者从外部源导入的文本数据可能包含嵌入文本中的非打印字符。由于轻易不会注意到这些字符，可能会很难理解出现的意外结果。若要删除这些不需要的字符，您可以使用 TRIM、CLEAN 和 SUBSTITUTE 函数的组合。
CODE()：返回文本字符串中第一个字符的数字代码。
CLEAN()：删除文本中 7-bit ASCII 代码的前 32 个非打印字符（值 0 到 31）。
TRIM()：删除文本中的 7-bit ASCII 空格字符（值 32）。
SUBSTITUTE()：可使用 SUBSTITUTE 函数将较高值的 Unicode 字符（值 127、129、141、143、144、157 和 160）替换为 7-bit ASCII 字符，这也是 TRIM 和 CLEAN 函数的设计初衷。

4.数字存在以下两种主要问题时，您需要对数据进行清理：数字被意外导入为文本时，以及需要按照组织的标准对负号进行更改时。
DOLLAR()：将数字转换为文本格式并应用货币符号。
TEXT()：将数值转换为特定数字格式的文本。
FIXED()：将某数值舍入为指定的小数位，使用句点和逗号将数字设置为小数格式，并将结果返回为文本
VALUE()：将代表数字的文本字符串转换为数字。

5.由于存在许多不同的日期格式，并且这些格式的数字部分代码或其他包含斜杠或连字符的字符串可能会令人迷惑，因此日期和时间通常需要进行转换和重新设置格式。
DATE()：返回表示特定日期的连续序列数。如果输入函数之前的单元格格式为“常规”，则结果的格式将为日期。
DATEVALUE()：将表示为文本的日期转换为序列数。
TIME()：返回特定时间的小数。如果输入函数之前的单元格格式为“常规”，则结果的格式将为日期。
TIMEVALUE()：返回表示为文本字符串形式的时间的小数。小数是介于 0（零）和 0.99999999 之间的一个值，代表从 0:00:00 (12:00:00 AM) 到 23:59:59 (11:59:59 P.M.) 范围内的时间。

6.Excel中的多数分析和格式功能都假定数据存在于单个二维平面表中。某些情况下，您可能希望将行转换为列，并将列转换为行。其他情况下，数据甚至不是以表格形式构建的，您需要通过一种方式将数据从非表格形式转换为表格形式。
TRANSPOSE()：将垂直单元区域返回为水平区域，反之亦然。

7.我们有时需要查找和更正两个或多个表进行连接时的匹配错误。这可能要调整自不同工作表的表格，例如，查看两个表中的所有记录或比较表并查找不匹配的行。
LOOKUP()：从单行或单列区域或数组中返回一个数据。LOOKUP 函数有两种语法形式：向量形式和数组形式。
HLOOKUP()：在表格或数值数组的首行查找指定的数值，并由此返回表格或数组当前列中指定行处的数值。
VLOOKUP()：在表格数组的首列查找值，并由此返回表格数组当前行中其他列的值。
INDEX()：返回表或区域中的值或对值的引用。INDEX 函数有两种语法形式：数组形式和引用形式。
MATCH()：返回在指定方式下与指定数值匹配的数组中元素的相应位置。如果需要找出匹配元素的位置而不是匹配元素本身，则应该使用 MATCH 函数而不是 LOOKUP 函数。
OFFSET()：从一个单元格或单元格区域返回对指定了行数和列数的区域的引用。返回的引用可以为一个单元格或单元格区域。并可以指定返回的行数或列数

巴特西

一些用于数据整理的excel函数

最新文章

热门文章