2、Excel：基础概念、表格结构与常见函数

Excel：基础概念、表格结构与常见函数

数据：八月成交数据&八月获客数据

1.0 Excel的数据处理

• 定位条件（空值、错误、可见单元格）
• 选择性粘贴（值、运算、转置、跳过空单元格）
• 查找和替换（相同格式单元格、单元格匹配）
• 数据验证
• 快速填充（Ctrl+E快捷键）
• 合并计算

1.1 定位条件

包括：空值、错误、可见单元格

1.1.1 定位空值

操作

1. 选中全部的数据区域
2. 开始功能区下的编辑组里的查找和选择下拉菜单里的定位条件，弹出窗口里选择空值
3. 设置填充颜色
4. 输入某个值后，再按Ctrl+Enter

1.1.2 创建分类汇总&复制分类汇总结果

样例(3级汇总)

目标：将第三张图片中的数据区域创建两个分组（第一张图片&第二张图片）

创建分类汇总操作

1. 选中图三的数据区域（row2-row7）

   必须要连选，也就是要连着的行或者列

2. 点击数据功能区中分级显示组里的组合下拉菜单里的组

   快捷键：Alt+Shift+向右的键

   取消分组快捷键：lt+Shift+向左的键

3. 选中图三的数据区域（row9-row14）

   必须要连选，也就是要连着的行或者列

4. 点击数据功能区中分级显示组里的组合下拉菜单里的组

   此时第二张图的效果完成

5. 选中图三的数据区域（row2-row15）

   必须要连选，也就是要连着的行或者列

6. 点击数据功能区中分级显示组里的组合下拉菜单里的组

   此时第三张图的效果完成

目标：

我们只想复制第二张图，但是对于多级汇总来说复制第二张图相当于复制第三张图

复制分类汇总结果操作

1. 选中第二张图中的数据区域
2. 开始功能区下的编辑组里的查找和选择下拉菜单里的定位条件，再弹出的窗口里选择：可见单元格
3. 点击Ctrl+C进行复制，在空白区域点击Ctrl+V进行粘贴

1.1.3 删除错误的单元格

目的：

删除数据区域中显示#VALUE!的单元格

操作

1. 选中数据区域，开始功能区下的编辑组里的查找和选择下拉菜单里的定位条件
2. 在弹出的窗口里选择：公式下面的错误（取消勾选公式下面的其他选项）
3. 按下Delete

1.2 选择性粘贴

值、运算、转置、跳过空单元格

实例1

要求：

使用选择性粘贴方法实现“金额”数据列数据增加25%

操作

1. 在空白单元格中输入1.25，单元格格式设定为常规，并Ctrl+C进行复制，选中金额字段列

2. 点击开始功能区下的编辑板组里的粘贴下拉菜单里的选择性粘贴，在弹出的页面里选择运算下的乘

   目的：是让1.25*金额字段列的每个值，此时你回发现金额字段列的数值是乘过1.25过后的结果

实例2

要求：

使用选择性粘贴方法实现文本型数据转换为数值型数据

操作

1. 在空白单元格中输入1，单元格格式设定为常规，并Ctrl+C进行复制，选中需要修改的文本型数据区域
2. 点击开始功能区下的编辑板组里的粘贴下拉菜单里的选择性粘贴，在弹出的页面里选择运算下的乘

实例3

要求：

选择性粘贴，将两列数据合为一列

操作

借助辅助列，在部门名称的每一个单元格下面插入一个空单元格

1. 在金额字段列后面加入一个辅助列，字段列命名为辅助列

2. 在辅助列的第一个单元格里输入1，第二个单元格输入2，用填充柄向下递增填充

3. 一共50条记录，在字段列的第50条记录下面输入1.2（1和2中间的任何一个值即可）

   由于后面没有记录，无法用填充柄进行填充

4. 点击开始功能区的编辑组里的填充下拉菜单里的序列，在弹出的窗口里：序列产生在列，步长值：1；终止值51.2

5. 选中辅助列字段，点击开始功能区的编辑组里的排序和筛选下拉菜单里的自定义排序

6. 弹出的窗口里选择扩展选定区域

7. 弹出的窗口里：排序依据选择辅列；排序依据： 单元格值；次序：升序

   此时在部门名称的每一个单元格下面插入一个空单元格

8. 复制金额字段列的数据区域（从第二行开始）

9. 选中部门名称字段列的第三行开始的区域，点击开始功能区下的编辑板组里的粘贴下拉菜单里的选择性粘贴

10. 弹出的窗口里选择跳过单元格

1.3 查找和替换

包括相同格式单元格、单元格匹配

目标：

将成绩为0的替换为“补考”

注意：

替换时，70中的0也被替换为“补考”

操作

1. 选中数据区域（快捷键：Ctrl+Shift+向右+向下）
2. 点击开始选项卡下面编辑组里的查找和选择下拉菜单里的替换
3. 弹出的窗口里点击选项
4. 弹出的窗口里:查找内容：0；替换为：补考；，弹出的窗口里勾选单元格匹配（只有单元格的值为0才进行匹配)

1.4 数据验证

目标：

名字字段值用下拉列表来进行选择，可选择的范围为一个大区域

注意：

之前的数据验证方法（序列值）只能对某一行或者某一列进行采样，无法对区域进行采样

操作

1. 选中填充值数据区域中的单列
2. 点击公式选项卡里的定义的名称组里的名称管理器
3. 弹出的窗口里点击新建
4. 弹出的窗口里名称名称为姓名
5. 点击需要引入下拉菜单的单元格
6. 点击数据选项卡下面的数据工具组里的数据验证下拉菜单里的数据验证
7. 弹出的窗口里，允许：序列；来源：=姓名
8. 点击公式选项卡里的定义的名称组里的名称管理器
9. 弹出的窗口里双击姓名，引用位置改为填充值数据区域中的全部数据区域

1.5 快速填充

（Ctrl+E快捷键）

目标：

提取省市字段的XX市（或者年份2015-11-17的2015）

操作

1. 在市字段列的第一个单元格里输入沈阳

2. 用填充柄向下进行填充，选择自动填充

   快捷方法：输入“沈阳辽宁”，放在下一个单元格，按住Ctrl+e（有个bug，需要前面有两列辅助列用来爬取市和省）

1.6 数据分列&去重

要求

对原始数据进行分列

操作

分列

1. （需要将原始数据分成两列）在旁边插入两列空白数据列

2. （将VS替换）点击开始选项卡–编辑组–查找和选择（下拉菜单）–替换

3. 弹出的窗口里，查找内容：VS，替换为：,(英文)，不要勾选单元格匹配，点击全部替换

   单元格匹配：精确匹配，单元格的内容只能是VS，不能含有其他内容

4. 点击数据选项卡–数据工具组–分列

5. 弹出的窗口里选择分隔符号–勾选逗号–下一步–完成

6. 弹出的窗口里

   也可以先将V作为分隔符号，再将S作为分隔符号（分隔符号只能有一个），但是比较繁琐

去重

1. 将分列得到的两列数据的第二列数据粘贴到第一列的下面，从而拼成一列数据
2. 选中这列数据（不包括列名）点击数据选项卡----数据工具组–删除重复项

1.7 数据整理

将网上复制来的的文本分列，得到右边一列数据

文本内容：01.我的母亲02.金瓶似的小山03.请喝一杯下马酒04.爱在天地间01. 东女国02. 吉祥的酥油灯03. 月亮升起来04. 美女天仙(藏语)05. 牧人06. 鸿雁07. 索呀啦(藏语)08. 雪域名城09. 慈母颂(藏语)10. 这山这水11. 故乡情(藏语)12. 黑帐篷05.草原夜色美06.一剪梅07.再唱山歌给党听08.国家09.驼铃10.远飞的大雁11.阿瓦古丽12.边关有我01.草原上升起不落的太阳02.走天涯03.美丽的草原我的家04.多情总为无情伤05.阿尔斯楞的眼睛06.敖包相会07.这片草原08.姑娘我爱你09.草原之夜10.故乡11.泪在投降12.慈祥的母亲13.天堂的草原01.西海情歌02.我和草原有个约定03.呼伦贝尔大草原04.蓝色的蒙古高原05.陪你一起看草原06.草原恋07.牧人08.卓玛09.父亲的草原母亲的河10.天边11.故乡12.手心里的温柔13.雨中飘荡的回忆

思路

对.进行分列，对分列后得到的数据进行转置

操作

1. 选中文本内容单元格

2. 点击数据选项卡–数据工具组–分列

3. 弹出的窗口里选择分隔符号–勾选其他:.–下一步–完成

   分裂的数据会放在一行里

4. 按住Ctrl+Shift+向右选中这些分列后得到的单元格，复制

5. （选择性粘贴+转置）点击空白单元格，点击开始选项卡–剪切板具组–粘贴（下拉菜单）–选择性粘贴，弹出的窗口里选择下面的转置

   剪切（Ctrl+X)是无法进行选择性粘贴

6. (快速填充)在后面一个单元格里输入我的母亲，填充柄向下填充，点击

   你会发现美女天仙（藏语）02被快速填充为美女天仙（藏语，原因是：Excel将非文字之外的其他符号都统一定义为一类，都将其忽略，需要进行替换

7. （替换）点击开始选项卡–编辑组–查找和选择（下拉菜单）–替换

8. 弹出的窗口里，查找内容：(藏语，替换为：(藏语)，不要勾选单元格匹配，点击全部替换

1.8 生成1-1000的序列数

操作1

1. 空白单元格输入1
2. 点击开始选项卡–编辑组–填充（下拉菜单）–序列
3. 弹出的窗口里，终止值：1000

操作2

1. 空白单元格A1输入1
2. 输入A1000，按回车
3. 输入1000
4. A2输入2，向下进行填充

1.9 全年合并

合并上下半年的数据

上半年：

下半年

需要合并的表：（列名和行名一一对应）

思想：

合并计算

操作

1. 选择需要合并的表区域（包括行字段和列字段）

2. 点击数据功能卡–数据工具卡–合并计算

3. 弹出的窗口里，列用位置：上半年的数据区域（包括行字段和列字段），点击添加；列用位置：下半年的数据区域（包括行字段和列字段），点击添加；

4. 点击弹出窗口最下面的最左列，点击确定

   最左列和首行都是标签索引

   如果标签不是一一对应？

   操作还是跟上面一样，合并计算会自动根据需要合并的表区域的字段名称进行计算

1.10 合并计算

数据来源：八月成交数据

数据初探

业务背景

数据来源行业：金融行业（根据应收利息和逾期金额字段来判断）

可以猜测：

• 业务主体：某互联网金融公司（类似支付宝）
• 也业务模式：给城市线下推广
• 推广产品：小额现金贷（类似借呗）

熟悉字段含义

解释各字段含义：

• 日期：标记下面的字段数据属于哪一天；
• 业务组：在全国各城市线下展业的业务小组名称，以及所属的组织架构；
• 成交额：属于该业务组的所有用户在当天贷款的金额总和；
• 应收利息：用户所贷金额对应的利息金额大小；
• 逾期金额：用户到了时间没还钱而产生的逾期金额。（若按期还款则为0）

了解数据

• 查看每一列有哪些数值，表格有多少行等等，方便进行接下来的处理和分析；

• 查看每一列的具体数值和行数。

  • 我们可以看到数据的日期为二零二零年二月一日至三十一日，而单日成交额最高约四十五万。筛选出最高成交额的记录后可以看到是八月十六日由广州一组完成的。

  快捷方法：

  点击任意一个有数据的单元格，然后点击开始栏目下右侧的排序和筛选，接着点击筛选，可以看到每一列的名称。

  

  注意：

  • 日期、文本和数值型的数据各自支持不同的逻辑筛选:

    • 日期可以筛选出某一天前的数据;

    • 文本可以筛选出特定开头的数据;

    • 数值支持大于小于等于等比较逻辑.

  • 如果我们要取消筛选，可以再次点击字段筛按钮全选数据；

  • 如果筛选的字段比较多，我们想要一次性取消所有筛选可以点击功能栏的筛选先取消，然后再点一次筛选就可以重新进行筛选字段了;

  • 筛选模式下对数据进行排序，点击升降序即可。