1. 文本数据
包括:
- 名称
- 短语
- 城市
- 国家
- 非日期或数字
1.1 替换字符
SUBSTITUTE函数
- 与查找/替换不同, 查找/替换对单元格中现有值进行更改
- 单元格中的原始数据不会改变
- 替换后的值转到新的单元格
🔐如何将下图中的brown更改为red
image.png
🔑
=SUBSTITUTE(A1,"brown","red")
1.2 提取文本
🔐如何提取A列中每个短语中的第一个单词, 换句话说, 提取从左到右第一个空格前的内容
image.png
🔑为此我们需要两个函数FIND和LEFT
image.png
FIND函数
搜索为其提供的任何字符串的短语, 这里是
" "空格, 并返回该字符串所在的位置. 对于本例中第一个短语hello world, 第一个空格时字符串中的第六个字符.
LEFT函数
从左开始, 提取指定个数的字符, 对于本例, 提取比第一个空格所在位置少一个的字符, 即从左开始的五个字符.
提示:
可以对函数进行嵌套
image.png
RIGHT函数
从右侧提取一定数量的字符, 本例中从右侧提取3个字符.
image.png
MID函数
从短语的任何位置提取, 并基于某种起点, 提取一定数量的字符, 本例中, 从第二个字符开始, 提取三个字符.
image.png
1.3 格式化字符
CONCATENATE函数
将多个单元格中的字符结合在一起
image.png
🔐如何将下图中三行合并为一行?
image.png
🔑
=CONCATENATE(B2," ",A2,"lives in ",C2,".")
TRIM函数
除去除字符之间单个空格以外的所有空格
🔐如何将下图中合并后的句子中多余的空格除去?
image.png
🔑
=TRIM(CONCATENATE(B2," ",A2,"lives in ",C2,"."))
PROPER函数
确保姓名和地点正确地(properly)首字母大写
image.png
UPPER函数
让所有字母都大写
image.png
LOWER函数
让所有字母都小写
image.png
2. 数字数据
统计函数
略, 详见下篇.
3. 数据清理
3.1 重复行
去除重复行 --- 针对Excel 2016
- 选择相应地数据集区域
- 点击
Data(数据)菜单- 点击
Remove Duplicates- 如果数据集区域包含header(顶部行), 在跳出地对话框选择
My data has headers, 就会忽略顶部行
3.2 拆分列(split)
🔐如何将下图中名字和姓氏拆分成两列?
image.png
🔑拆分列
- 选择相应地数据集区域
- 点击
Data(数据)菜单- 点击
Text to Columns- 在弹出的对话框中选择
Delimited(分隔符), 点击Next- 在弹出的对话框中选择对应的分隔符, 本例中使用的是逗号, 所以选择
Comma(逗号), 点击Next
拆分日期
=TEXT(A2, "mmmm")
3.3 排序(sort)
🔐如何对下图按照First列进行排序?
image.png
🔑排序
- 选择所有数据区域
- 点击
Data(数据)菜单- 点击
Sort(排序). (对于旁边的A-Z排序或Z-A排序, 默认情况按照第一列排序)- 在弹出的对话框中
Sort by下拉条中选择Fisrt, 点击OK
或者可以先按名字排序, 然后再按姓氏排序
- 前四步同上
- 在
Sort对话框中点击Add Level, 在then by下拉条中选择Last, 点击OK
效果如下:
image.png
3.4 过滤或筛选(filter)
筛选数据是指从数据列中选择, 符合某个特征的数据, 并忽略其他数据.
🔐找到下图中所有名字为Luis的行?
image.png
🔑筛选
- 选择所有数据区域
- 点击
Data(数据)菜单- 点击
Filter(筛选).- 在弹出的对话框中, 取消
(Select All)的选择- 在搜索框中输入
Luis, 然后点击确定
如果需要将数据视图还原为完整的原始数据集, 点击Data(数据)菜单中, Filter选项旁边的Clear(清除)选项.
