Excel to MySQL 常用操作

[qingquan-li]

一、VLOOKUP 函数

参考：https://support.microsoft.com/zh-cn/office/0bbc8083-26fe-4963-8ab8-93a18ad188a1

使用场景实例：

表格 A 中含有字段：user_id、username、gender，表格 B 中含有字段：user_id、age。 此时可以使用 VLOOKUP 函数，通过匹配 2 个表的 user_id 字段，将表格 B 中的 age 字段匹配到表格 A 。

VLOOKUP 函数语法：

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

= VLOOKUP （你想要查找的内容，要查找的位置，包含要返回的值的区域中的列号，返回近似或精确匹配-表示为 1/TRUE 或 0/FALSE）

您需要四条信息才能构建 VLOOKUP 语法：

1. 要查找的值，也被称为查阅值。
2. 查阅值所在的区域。 请记住，查阅值应该始终位于所在区域的第一列，这样 VLOOKUP 才能正常工作。 例如，如果查阅值位于单元格 C2 内，那么您的区域应该以 C 开头。
3. 区域中包含返回值的列号。 例如，如果指定 B2： D11 作为区域，则应将 B 作为第一列，将 C 作为第二列进行计数，依此类推。
4. （可选）如果需要返回值的近似匹配，可以指定 TRUE；如果需要返回值的精确匹配，则指定 FALSE。 如果没有指定任何内容，默认值将始终为 TRUE 或近似匹配。

注意：

1. lookup_value 查阅值对应的字段，数据类型要一致，不然会报错：#N/A
2. xlsx格式的表格，可以与 csv 格式的表格进行匹配。

实例：

# 用学号，匹配学生档案表的，身份证号：
=VLOOKUP(A3,'[学生档案.xlsx]Worksheet'!$B$2:$N$8000,5,FALSE)

# 用学号，匹配通讯录表的，手机号：
=VLOOKUP(A3,'[通讯录.csv]Sheet1'!$D$2:$G$7900,4,FALSE)

二、Excel 删除重复项目

1. 删除列的重复值：

参考：https://support.microsoft.com/zh-cn/office/查找和删除重复项-00e35bea-b46a-4d5d-b28e-66a552dc138d

1. 选择包含要删除的重复值的列单元格
2. 单击“数据”>“删除重复项”
3. 在“列”下选中或取消选中要删除重复项的列

2. 删除单列重复值（如ID／学号）对应的行：

参考：https://www.sohu.com/a/194672425_267316

1. 全选单元格（数据行较多时，选择顶部的部分单元格行，然后使用 command+shift+↓ 一拉到底）
2. 单击“数据”>“删除重复项”
3. 在“列”下选中或取消选中要删除重复项的列，一般选择如ID／学号等可以作为唯一值的列

三、在单元格中插入当前日期和时间

参考：https://support.microsoft.com/zh-cn/office/b5663451-10b0-40ab-9e71-6b0ce5768138 以下快捷键只针对 macOS 。

插入当前日期： control + ;

插入当前时间： command + ;

插入当前日期和时间： control + ; 然后按空格键，然后按 command + ;

四、把 Excel 数据导入到 MySQL

参考：如何把excel数据导入navicat及常见问题：https://zhuanlan.zhihu.com/p/52735010

• 首次导入 Excel 表格，选择 追加 。 追加 含义：数据库中没有该表→创建数据表。

• 如果数据库表中的数据，和更新的Excel表格中的数据行一致（只是行中某些字段的数据不一样）：使用 更新 、 追加或更新 。

• 如果数据库表中的数据，和更新的Excel表格中的数据行不一致（不仅是某些字段不一致），例如多了或少了几个数据行，则使用复制。

五、查看列中是否含有长度大于50的字段

在需要查询的列（例如 B 列）旁边插入一列（例如 C 列，作为辅助列）， 在 C2 单元格中执行公式（在表格顶部 fx 输入公式）计算 B2 单元格字段的长度：

=LEN(B2)

然后在 C 列中下拉填充，应用公式到整列，计算出 B 列所有单元格字段的长度。 然后选中 Excel 表格 → 数据 → 筛选，即可在 C 列中查看 B 列所有字段长度的计算结果，即可查看到是否含有大于 50 的字段。