Excel 学习笔记
数据量较大时，通常会使用 SQL 进行数据清洗和数据筛选，但是当数据量较小，且电脑没有安装数据库时，Excel 通常会是较好的选择。Excel 用于数据清洗的函数主要有6大类且通常嵌套使用，这篇文章仅进行记录备忘。
Excel 常用快捷键
（个人常用快捷键）
| 快捷键 | 作用 |
|---|---|
| Ctrl + Arrow Keys | 光标移动到表格某一侧的最后一个单元格 |
| Ctrl + Shift + Arrow Keys | 快速框选某一侧的所有单元格 |
| Ctrl + Space Key | 选定整列 |
| Shift + Space Key | 选定整行 |
| Alt + Enter | 换行 |
文本清洗函数
（用于清洗表格中的脏数据）
| 函数名 | 作用 | 用法 |
|---|---|---|
| Find | 找出一个字符产的位置 | FIND(“string”, cell) |
| Left Right | 取左侧/右侧起第几个字符 | LEFT(cell, step) |
| MID | 从左往右设起始位并截取 | MID(cell, start, step) |
| Concatenate | 拼接多个字符串 | CONCATENATE(cell, “string”) |
| Replace | 精确定位替换 | REPLACE(cell, start, step, new_text) |
| Substitute | 关键字符替换 | SUBSTITUTE(cell, old_text, new_text) |
| Text | 修改文本格式 | TEXT(cell, format) |
| Trim | 删除字符串左右多余的空格 | TRIM(cell) |
| Len | 计算单元格中的字符长度 | LEN(cell) |
关联匹配函数
（将多个表合并为一个表）
| 函数名 | 作用 | 用法 |
|---|---|---|
| VLookUp | 将表二的内容匹配还原到表一（两张表必须有一处值相同） | VLOOKUP(2cell_from_sheet2, sheet2!2cell_as_sheet1:1cell_as_sheet2, column, TrueOrFalse) |
| Index | 索引某个区域内的某个单元格 | INDEX(cell1:cell2, rowNum, columnNum) |
| Match | 返回某个数在选定区间内的位置 | MATCH(Num, cell1:cell2, 0) |
| Row | 返回某个单元格是第几行 | ROW(cell) |
| Column | 返回某个单元格时第几列 | COLUMN(cell) |
| Offset | 以某个单元格为参照系设置偏移量 | OFFSET(cell, rowsStep, colsStep, height, width) |
| Hyperlink | 将某个字符串设置为超链接 | HYPERLINK(address) |
计算统计函数
（对单元格内的值进行计算）
| 函数名 | 作用 | 用法 |
|---|---|---|
| SumProduct | 对一个区域内的数值，列间相乘行间相加 | SUMPRODUCT(cell1:cell2, cell3:cell4) |
| Count | 统计区域内的有效数值 | COUNT(cell1: cell2) |
| Max, Min | 求出区域内的最大最小值 | MAX(cell1: cell2) |
| Rank | 求出某个数值在区间内的排序后的位置 | RANK(cell1, cell2:cell3) |
| RandBetween | 随机出区间内的一个数值 | RANDBETWEEN(min, max) |
| Average | 求出范围内的平均值 | AVERAGE(cell1, cell2) |
| Quartile | 求出区间内的分位的值 | QUARTILE(cell1: cell2, 1~4) |
| Stdev | 求出区间内的标准差 | STDEV(cell1: cell2) |
| Int | 数值向左取整 | INT(cell) |
| Round | 四舍五入取整，可添加取舍条件，可正负 | Round(cell, -9~9) |
| CountIf | 获取区间内满足条件的个数 | COUNTIF(cell1: cell2, “>10”) |
| CountIfs | 判断多个 CountIf 的条件 | COUNTIFS(cell1: cell2, “>10”, cell3: cell4, “string”) |
| SunIf | 统计满足条件区间的相同行其他参数 | SUNIF(cell1: cell2, “string”, cell3: cell4) |
| AverageIf | 统计区间内满足条件的数值相同行其他参数的平均数 | AVERAGEIF(cell1: cell2, “string”, cell3: cell4) |
时间序列函数
（提取时间序列单元格中的具体时间数值）
| 函数名 | 作用 |
|---|---|
| Year | 返回单元格中的年份数值 |
| Month | 返回单元格中的月份数值 |
| Day | 返回单元格中的日期数值 |
| Date | 拼接年月日 |
| Now | 返回当前时间，精确到秒 |
| Today | 返回当前时间，精确到日期 |
| Weeknum | 查询某日是该年的第几周 |
| Weekday | 返回某日是一周中的第几天 |
评论
发表评论