1、Excel工作场景和知识点总结

Excel1：工作场景和知识点总结

参考：

1、戴师兄–戴你玩转数据分析
2、菊花酱数据分析

1 Excel发挥战斗力的场景

• 地量级数据的存储

  我们日常所用的各种数据表格，基本都以excel的.xlsx或者.xls格式进行存储。并且因为大家电脑上都有excel，这就使excel的通用性很高(我用excel做好一个表发给你，你直接就能打开)。但如果我用power BI和tableau做，那么就需要将结果发布成一个网页。如果公司的数据是BI格式，并么对方必须要有对应的软件才能打开查看，也没有直接传递excel文件来的方便。

  • 理论上：1048576行以内、700M以下的数据对于07及以后版本的Excel都能装得下；

  • 实际操作：大部分公司会规定导入导出的Excel文件的大小不要超过100M，行数不要超过10w行；

  • 一旦超过100M，直接用没有存储上限的csv格式或数据库存储，用BI工具（PowerQuery和PrepBuilder）才是正确的选择，能流利的运行excel处理十万行以上的数据。（现在大多数办公电脑的性能也很难流利的运行excel处理十万行以上的数据）

• 一次性的数据处理与分析（重复的操作避免使用excel）

  • 因为Excel的操作是做一步是一步，如果再让你重新做一次，除了设定好复杂的函数引入，就只能重新操作一遍了；

  • 而Powerquery和PreBuilder这样的流程化处理工具则会记录我们的每一步操作，只需要重新运行，就可以反复执行之前的操作非常的方便。

• 一次性的做表或做图

• 复杂且无法优化的数据报表

  例如很多公司都存在的异常复杂，但格式又不准修改的大型报表。这种报表里一般会有各种合并单元格和独立的表格区域,跟数据库和BI工具里方方正正的表格完全不是一回事。此时你就只能用excel进行处理，因为excel的灵活性足够高，你想怎么做就怎么做。

注意：

慎用W P S和office 365的云端同步功能：公司的数据只在公司的设备上处理，千万不要同步到个人的账户和设备上。

2 Excel和SQL、Python 的不同之处

• Excel对于数据量不是很大、不需要实时更新结果、分析结果相对独立、能够快速响应的业务场景非常友好

• SQL则适用于大量的、复杂的、存储在数据库中的数据（很难用单个Excel进行记录的数据），你可以用SQL来进行数据的查询和分析；

• Python则可以高效地处理更加复杂的数据，还可以编写一些自动化脚本帮助你处理那些重复性的工作，提高工作效率

• 所以，相对来说，Excel是一个轻量的、相对比较快捷的一款工具，并且上手也非常容易 。

3 Eecel的高频场景

3.1 输入与输出

3.1.1 输入

核心逻辑就是如何在excel的单元格中输入各种内容，如何借助智能填充等功能，提高输入效率，以及如何用各种格式调整、表格展示，并按不同的文件格式输出.

• 基础概念
• 内容填充
• 数据类型
• …

3.1.2 展示

• 显示不全
• 冻结窗格
• 排版类型
• …

3.1.3 输出

• 图片
• 文件
• 加密
• …

3.2 计算与处理

主要是对表格数据进行各种处理与计算有很多基础的功能，例如筛选替换排序与计算，还有各种公式和函数以及非使用的数据透视表功能，目的都是对数据做处理。

3.3 可视化

虽然处理大量级数据的能力不强，默认的配色和样式也不够美，但是excel里做图的自由度是真的高，可以随意组合各种图表更改各种标签，还能借助迷你图和条件格式在单元格里做可视化，有非常多使用的小功能。

3.4 连接匹配与自动化

主要是vlookup、xlookup、index match等看似复杂，但理解后简单到不行的函数，掌握后，就能根据列名和条件，自动识别匹配数据是实现各种自动化报表的利器.

4 Excel用户级别

5 Excel基本功能

5.1 Excel基本工作界面

快速访问工具栏：不同版本的Excel中这一部分不一样，可以人为地添加一些快速访问工具，比如新建、打开、保存、撤销/恢复、排序等等；

功能区：主要作用就是设置工作表区域的格式，功能区由很多个选项卡组成，比如开始、插入、页面布局、公式、数据……选项卡也可以人为进行设置，可以手动添加一些需要的选项卡或者隐藏一些不常用的选项卡，每个选项卡下面会有不同的组，每个组下面包含具体的命令。

名称框：实时显示当前活动对象的名称信息，比如单元格地址（由行号和列标组成，举例A1就是指第A列第1行，也就是第1行第1列）、图表名称、其他对象名称等。名称框还可以用于定位到目标单元格（在名称框中输入A6，则会自动定位到第6行第1列所在单元格）。

编辑栏：可以输入或者编辑单个单元格（也就是左边名称框中定位的那个单元格）的具体内容，可能是数据也有可能是公式。

工作表区域：主要功能就是用来填写数据，它的格式可以被功能区中的命令所改变。

状态栏：实时显示Excel在当前进行的作业，包括公式的计算进度、是否在录制宏等。还可以显示当前选中区域的汇总值（计数、求和、平均值、最大最小值……）具体显示何种汇总值，可以右键状态栏选择需要显示的值

5.2 工作表区域

工作簿：Excel中用来存储并处理工作数据的文件。Excel文档就是工作簿.

工作表：也称为Sheet，工作表区预包含所有的数据，比如数字、文本内容、图表、窗体、形状等。

一个工作簿中可以包含多个工作表，默认名称为Sheet1、Sheet2、……（可以手动修改Sheet的名
字）

单元格：Excel表格中最基础的单元，以长方形形状显示在工作表区域，位于行列交叉处，用列标和行号表示单元格。单元格的主要作用是存储数据和显示数据。可以存储的数据类型有：

• 数字:包括数字、日期和时间。

• 文本：类似于姓名、性别、住址等。

• 逻辑值：包括True和False。

• 错误值：#VALUE！、#DIV/0!、#NAME！、#N/A、#REF!、#NULL！等。

• 公式：包括运算表达式和函数公式。

  注意：单元格中如果是数值就会默认靠右对齐，如果是文本就会默认靠左对齐

6 Excel基本操作

6.1 保护功能

6.1.1 保护工作簿

限制删除或增加工作表数量、修改工作表名称，隐藏工作表等。可以通过设置密码的
方式进行保护

• 法一：通过功能区选项卡中审阅下的更改中的保护工作簿来实现

• 法二：通过功能区选项卡中文件–信息（如下图所示）（此时需要密码才能打开工作簿）；若想取消保护工作簿，可以从下图中，将密码删去。

  

6.1.2 保护工作表

针对工作表的保护，限制删除或增加行、列；限制单元格录入数据等

6.1.3 部分单元格可编辑

先取消可编辑单元格的"锁定"，然后设置保护工作表（保护工作表里会勾选“保护工作表及锁定的单元格内容” ）

• 锁定操作：
  1. 选择要保护的单元格
  2. 点击功能区里开始选项卡 下字体/对齐方式/数字的斜下三角下拉菜单，在跳出的窗口里选择保护分栏里的取消勾选锁定

6.2 数据输入

6.2.1 数据输入

6.2.1.1 添加默认序列

操作

1. 文件–选项–高级–常规–编辑自定义列表

2. 在弹出的窗口里点击添加，输入e,w,q（英文逗号）

   此时在单元格里输入e，用填充柄进行填充时，就会按照e、w、q的顺序依次填充

6.2.1.2 填充序列

• 纯数字：按住Ctrl用填充柄进行填充时，数字依次+1（此时会变成两个十字，一大一小)
  • 例子：等差序列
  • 操作：
    1. 选中一个有数值的单元格，光标放在该单元格左下角右击向下滑动
    2. 滑动到指定位置后松开，在跳出的菜单里选择序列
    3. 序列产生在列（向下进行填充），终止值是根据你选中的单元格格数来决定（若无法匹配，则不能执行填充操作；也可以只确定步长值，终止值不填）
• 文本（包括英文）+数字：按住Ctrl用填充柄进行填充时，数字依次+1
• 日期格式：按照年/月/日来进行填充
  • 操作：
    • 选中一个有日期的单元格，光标放在该单元格左下角按住鼠标右键向下滑动
    • 滑动到指定位置后松开，在跳出的菜单里选择序列
    • 序列产生在列（向下进行填充），类型选择日期，日期单位选择日/月/年，终止值是根据你选中的单元格格数来决定（若无法匹配，则不能执行填充操作；也可以只确定步长值，终止值不填）
• 多个不连续单元格输入相同数据
  • 操作
    1. 按住Ctrl选中多个单元格，在最后选中的单元格里输入值
    2. 输入完后，点击Ctrl+Eter
• 文本记忆式输入法
  • 例子：接下来从已经输入的值里进行挑选
  • 操作：
    • 右击下面要输入的单元格，点击从下拉列表中选择

6.2.1.3 数据验证

从下拉菜单中选择输入值

操作：

1. 数据选项卡–数据工具group–数据验证

2. 跳出的窗口里选择设置专栏里，允许：序列。来源：输入你要放在下拉菜单里的值（比如：人事部,销售部,市场部,用英文逗号)

   注意

   来源中还有另两种方法：

   • 法一：把你要输入的值依次放进单元格里，然后再来源那边括住这些区域就行
   • 法一：把你要输入的值依次放进单元格里，但那片区域进行命名（选中那片区域–点击公式选项卡下的定义的名称group里的名称管理器–在弹出的窗口里点击新建–命名名称，然后再来源那边输入之前命名的名称即可）

6.2.1.4 数字自定义格式

案例1：对可选值进行编码，比如输入1为男；0为女

操作1

1. 选中要填入值的单元格区域，选择开始选项卡里的字体组里的斜下按钮，在弹出的窗口里选择数字分栏下的自定义中的G/通用格式

2. 在类型下面输入：[=1]“男”;[=0]“女”

   语法：[=自编码值]“原本要输入的值”;[=自编码值]“原本要输入的值”

   注意：所有符号均为英文；只能为二取值进行编码

案例2：

工龄每增加一年，工资加+5000，单元格格式为：777元/年

操作2

1. 在空单元格里输入777
2. 选中777的单元格，选择开始选项卡里的字体组里的斜下按钮，在弹出的窗口里选择数字分栏下的自定义中的G/通用格式
3. 在类型下面输入：0"元/年"（注意为英文的引号）

案例3：

设置格式：正数-默认色；负数-红色+括号（括号里是负数的绝对值）；零-蓝色

操作3

1. 选中填入数值的单元格，选择开始选项卡里的字体组里的斜下按钮，在弹出的窗口里选择数字分栏下的自定义中的G/通用格式

2. 在类型下面输入：0.0%;[红色](0.0%);[蓝色]0.0（注意为英文的引号）

   注意：

   • 0.0%：保留一位小数

   • 数字格式最多可包含四个代码部分，各个部分用分号分隔。这些代码部分按先后顺序定义正数、负数、零值和文本的格式。 （<正数>;<负数>;<零>;<文本>）

   • 0.0%;[红色](0.0%);[蓝色]0.0;绿色：文本用绿色来标注

     

6.2.2 文本数字转化为数值数字

文本数字是无法直接求和的

6.2.2.1 操作

使用6个公式之一转换，若结果没变化，将其单元格格式设置为“常规”：

• =A1*1
• =A1/1
• =A1+0
• =A1-0
• =--A1(减负运算,两个减号)
• =VALUE(A1)

注意：双击后会出问题

6.2.3 标准化规范化

包括：不规范日期的处理方式、批量修改不规范格式

例1

将不规范日期格式转化为规范日期格式

• 不规范的日期格式:2014.1.1,无法用YEAR()函数对其进行转化
• 规范的日期格式:2014/1/1,可以用YEAR()函数对其进行转化

处理方法

1. 开始功能区下的编辑组里的查找和选择下拉菜单里的查找和替换，查找内容：.,替换内容为/,点击全部替换

   也可以使用SUBSTITUTE函数：=SUBSTITUTE(A2,".","/")*1

   • A2:2014.1.1

例2

将不规范日期格式转化为规范日期格式

• 不规范的日期格式:980101
• 规范的日期格式:98/01/01

处理方法

1. 使用函数进行转换为文本：=TEXT(F3,"##-##-##")

   • F3:980101
   • “##-##-##”:98-01-01（年-月-日）
   • 英文双引号和逗号

2. 使用公式将文本转化为数字=--TEXT(F3,"##-##-##")（减负运算）

   注意：要显示:,必须加一个强字符!或-；–是将文本时间转换为数字时间

   比如：=--TEXT(A10,"00!:00!:00")

例3

将不规范日期格式转化为规范日期格式

• 不规范的日期格式:20140101
• 规范的日期格式:2014/01/01

处理方法

1. 数据工具栏里的数据工具组里的分列
2. 在跳出窗口里的列数据格式选择日期，点击完成

日期与数字之间的转化：1900/1/1等同于0

例4

批量取消单元格里的单引号

操作：

1. 格式刷复制一个空单元格。
2. 格式刷到这个区域即可

6.2.4 组合键

• Ctrl+A ：全选

• Ctrl+C ：复制

• Ctrl+V ：粘贴

• Ctrl+Z ：撤销

• Ctrl+Shift+上下左右键 ：选中多行多列（非空）

• Ctrl+Enter ：一键输入重复内容

• 选中求和区域，再看ALT+ = ：快速求和

  也可以选中要小计的单元格（若不连续的，辅助Ctrl键,或者，选中数据大体区域，再定位空值单元格【操作：开始功能区下的编辑组里的查找和选择下拉菜单里的定位条件，弹出窗口里选择空值】）再按Ctrl+ Enter

• shift+F8 ：激活“添加选定”模式，分别单击不连续的单元格或单元格区域即可选定，而不必按
  住Ctrl键不放，一直选择不连续的单元格。（如果没有激活成功，可以按shift+FN+F8）

• 填充柄：双击/拖拽，复制一列同类型公式，一次几百个、几万个等

6.2.5 拆分冻结

操作：

1. 视窗选项卡下面窗口组里的冻结窗口的下拉菜单

   • 冻结首行
   • 冻结首列
   • 冻结窗口：点击要冻结的单元格，再点击冻结窗口

   方便查看后面数据每一个值所对应的是什么字段含义
   （若不连续的，辅助Ctrl键,或者，选中数据大体区域，再定位空值单元格【操作：开始功能区下的编辑组里的查找和选择下拉菜单里的定位条件，弹出窗口里选择空值】）再按Ctrl+ Enter

• shift+F8 ：激活“添加选定”模式，分别单击不连续的单元格或单元格区域即可选定，而不必按
  住Ctrl键不放，一直选择不连续的单元格。（如果没有激活成功，可以按shift+FN+F8）

• 填充柄：双击/拖拽，复制一列同类型公式，一次几百个、几万个等

6.2.5 拆分冻结

操作：

1. 视窗选项卡下面窗口组里的冻结窗口的下拉菜单

   • 冻结首行
   • 冻结首列
   • 冻结窗口：点击要冻结的单元格，再点击冻结窗口

   方便查看后面数据每一个值所对应的是什么字段含义