Excel之常用操作总结

前言：从今天起，会给大家安利从事业务数据分析师必备的技能，而Excel的操作又是业务数据分析工作必备的基本技能，所以未来几篇文章会从Excel常用操作、常用函数公式和可视化等方面来进行。

PS：文中所用Excel为2016版，有需要的小伙伴可私信或者关注微信公众号：数据分析文摘（微信号：sjfxwz）回复关键字“excel”获取下载链接。

工欲善其事必先利其器，相信大家在处理数据的时候，Excel在大多时候都是首选工具，而各类数据分析工作岗位的招聘中，Excel的相关使用（函数公式透视表可视化等）都是必会项，今天就来总结一下Excel操作中常用的快捷键、格式和快速操作等。

1.1.1 快捷键

Excel的快捷键很多，这里主要介绍几个常用的。

Ctrl+C，复制。

Ctrl+V，粘贴。

Alt+Enter，换行。

Ctrl+A，选择整张表。

Ctrl+Z，撤回当前操作。

Ctrl+空格键，选定整列。

Shift+空格键，选定整行。

Ctrl+Enter，以当前单元格为始，往下填充数据和函数。

Ctrl+方向键，对单元格光标快速移动，移动到数据边缘（空格位置）。

Ctrl+Shift+方向键，对单元格快读框选，选择到数据边缘（空格位置）。

Ctrl+S，快速保存，建议大家在日常操作数据时多按ctrl+s键，以便意外发生，大半天的工作都白做了。

1.1.2 格式转换

通常可将Excel格式分为数值、文本、时间。

数值常见整数型 Int和小数/浮点型 Float。两者的界限很模糊。

文本分为中文和英文，存储字节，字符长度不同。

时间格式在Excel中可以和数值直接互换，也能用加减法进行天数换算。

时间格式有不同表达。例如2018年12月28日，2018/12/28，2018-12-12等。当数据源多就会变得混乱。我们可以用自定义格式规范时间。这里了解一下时间格式的概念，列举是一些较通用的范例。

YYYY代表通配的四位数年格式

MM代表通配的两位数月格式

DD代表通配的两位数日格式

HH代表通配的的两位数小时（24小时）格式

hh代表通配的两位数小（12小时制）格式

mm代表通配的两位数分格式

ss代表通配的两位数秒格式

例如2018/12/28可以写成：yyyy/MM/dd

2018-12-28 23:59:59可以写成：yyyy-MM-ddHH:mm:ss

图 1.1.2.1

1.1.3 填充

1、自动填充单元格以节省时间

以下是在 Excel 中使用填充柄的方法：

1）单击写有数字100的单元格。

2）将光标置于单元格的右下角，直到 它变为十字形：

图 1.1.3.1

3）单击十字形并向下拖动三个单元格。Excel 将自动填充单元格的总计：110、120 和130。该操作称为“向下填充”。

4）单击写有200的黄色单元格，然后再次填充，但这一次，将填充柄向右 拖动以填充单元格。此操作称为“向右填充”。

图 1.1.3.2

2、使用填充柄复制单元格

Excel可自动基于某个序列填充一些单元格。例如，可在一个单元格中键入 农产品，然后用填充柄其他单元格。

图 1.1.3.3

1）单击写有单词“农产品”的单元格。将光标置于单元格的右下角，直到它变成十字形，然后向下拖动三个单元格。

2）现在选择写有单词“水果”的单元格。再次将光标置于右下角，变成十字形时进行双击。这是另一种向下填充方式，可用于需要填充一长列的情况。

图 1.1.3.4

3、填充序列

Excel 可基于序列自动填充一些单元格。例如，可在某单元格键入 1 月，然后在其他单元格中填充 2 月、3 月等。

图 1.1.3.5

1）单击写有单词“1 月”的单元格。

2）将光标置于单元格的右下角，直到它变成十字形，然后向右拖动两个单元格。Excel 检测到序列，并填充“2 月”和“3 月”。

3）现在选择写有“第 1 周”的单元格。

4）再次将光标置于右下角，当变成十字形时，双击它。

图 1.1.3.6

3.1.4 拆分

1、拆分数据

图 1.1.4.1

1）在“名字”下方的单元格中，键入“电子邮件”列中的名字：Nancy、Andy 等等。

2）当看到淡出的建议列表时，立即按Enter。

图 1.1.4.2

此建议列表称为“快速填充”。快速填充会在你键入一致模式时检测，并提供单元格填充建议。当看到淡出的列表时，按Enter。

3）尝试使用另一种方式来快速填充：单击包含Smith 的单元格。单击“开始”>“填充”>“快速填充”。现在，所有姓氏在其各自列中。

图 1.1.4.3

或者同时按住“Ctrl+E”键，也可实现快速填充。

图 1.1.4.4

2、基于分隔符拆分列

快速填充非常方便。但是如果想要将数据一次拆分到多个列，则它不是此作业的最佳工具。在此情况下，尝试使用“分列”：

图 1.1.4.5

1）单击并拖动，选择从Nancy到Yvonne的单元格。

2）在“数据”选项卡上，单击“分列”。请确保选择了“分隔符号”，然后单击“下一步”。

3）在“分隔符号”下方，确保只选中“逗号”复选框，然后单击“下一步”。

4）单击“常规”选项。

5）最后，单击“目标区域”框，键入$D$32。然后单击“完成”。

图 1.1.4.6

3.1.5 转置

通过转置来变换数据位置，当需要旋转列和行时，可在 Excel 中进行转置。

图 1.1.5.1

1）单击并从“物品”拖动到“20”，选中两行单元格。

2）现在，复制单元格。按“Ctrl+C”

3）单击黄色单元格。

4）在“开始”选项卡上，单击“粘贴”按钮下的箭头。

图 1.1.5.2

5）单击“选择性粘贴”，然后在底部，单击“转置”复选框。单击“确定”。

图 1.1.5.3

备注：“选择性粘贴”快捷键是Ctrl+Alt+V。

3.1.6 排序和筛选

1、排序和筛选

图 1.1.6.1

1）假设我们希望各部门按字母顺序排序。单击“部门”列，然后单击“开始”>“排序和筛选”>“升序”。

2）将“12 月”的金额从最大到最小排序。单击“十二月”列中的任意单元格，然后单击“开始”>“排序和筛选”>“降序”。

3）现在，对数据进行筛选，使其仅显示“烘焙品”行。按 Ctrl+A 选择所有单元格，然后单击“开始”>“排序和筛选”>“筛选”。

4）“筛选”按钮出现在首行。在“部门”单元格上，单击“筛选”按钮 ，然后单击以清除“全选”复选框。然后，单击选中“烘焙品”。

5）单击“确定”，将仅显示“烘焙品”行。现在清除筛选，单击“部门”的筛选按钮，然后单击“清除筛选”...

拓展延伸：完成步骤 5 后，尝试按字母顺序对两列进行排序。方法如下：首先按字母顺序对“部门”进行排序（即左侧的步骤 1）。然后单击“开始”>“排序和筛选”>“自定义排序”。将“类别”添加为次要条件。单击“确定”后，“部门”将进行排序，并且在每个部门内，“类别”行也将按字母顺序排序。

2、按日期或按颜色排序

Excel 中有多种排序方法。以下是其中两种排序方式，但这次将使用右键单击菜单：

图 1.1.6.2

1）如果希望按日期排序。右键单击一个日期，然后单击“排序”>“升序”。行将按“消费日期”升序排序。

2）已有三个单元格填充了黄色。你可以按该颜色对行进行排序。右键单击一个黄色单元格，然后单击“排序”>“将所选单元格颜色放在最前面”。

小提示：你无法像清除筛选一样清除排序。因此，如果不想保留排序，请按 Ctrl+Z 来撤消。

3、对数据进行筛选的更多方法

许多人通过键入公式来查找高于平均值或大于特定金额的金额。但是当特殊筛选可用时，则无需键入公式。

图 1.1.6.3

1）在“住宿”单元格中，单击筛选按钮 ，然后单击“数字筛选”>“高于平均值”。Excel 会计算“住宿”列的平均金额，然后仅显示金额大于该平均值的行。

2）现在添加次要筛选。在“餐饮”单元格上，单击筛选按钮 ，然后单击“数字筛选”>“大于...”，然后键入25。单击“确定”。在已筛选出超过平均值的三行中，Excel 会显示“餐饮”金额大于 25 的两行。

3.1.7 表格

1、用表格轻松处理工作

表格可以为你带来特殊的功能和便利。下面介绍如何创建表格：

图 1.1.7.1

1）单击上面的数据，然后单击“插入”>“表格”>“确定”。

图 1.1.7.2

2）现在，你创建了一个表格，即一个具有特殊功能的单元格的集合。对于初学者：表格提供了镶边行，更易于阅读。

图 1.1.7.3

3）你也可以轻松创建新行。在“肉类”下方的空单元格中，键入一些文本，然后按 Enter。表格将出现一个新行。

4）还可以轻松新建列：在表格的右下角，单击调整大小的句柄 并将其向右拖动 2 列。

5）请注意这两列的创建和格式设置方式，并且文本“一月”和“二月”已填充。

图 1.1.7.4

2、表格中的计算列

表格为我们提供方便的一个示例：计算列。输入公式，表格将会自动填充。工作方式如下：

图 1.1.7.5

1）选择“汇总”下方的单元格。

2）按“Alt+=”

图 1.1.7.6

3）按“Enter”

4）SUM 公式会向下填充，无需手动操作。

图 1.1.7.7

3、表格中的汇总行

表格中的另一个方便之处是汇总行。不同于键入 SUM 公式，Excel 可轻松进行总计。对于 AVERAGE 公式和许多其他公式同理。工作方式如下：

图 1.1.7.8

1）选择上面表格中的任意单元格。

2）在 Excel 窗口顶部将出现“表格工具”选项卡。

3）在该选项卡上，单击“汇总行”。

4）总计¥24,000将被添加到表格底部。

5）但是如果想要了解平均值呢？单击包含¥24,000的单元格。

6）单击向下箭头 ，然后单击“平均值”。将显示平均金额为¥3,000。

图 1.1.7.9

3.1.8 下拉列表

1、插入下拉列表

下拉列表使数据输入更容易。方法如下：

图 1.1.8.1

1）我们希望对于上面的每种食品，仅三个部门名称是有效项。这些部门为农产品、肉类和烘焙品。

2）单击并拖动，将“部门”下方的黄色单元格全部选中。

3）在“数据”选项卡上，单击“数据验证”。在“允许”下，单击“序列”。

4）在“来源”框中，键入“农产品, 肉类, 烘焙品”。请确保在它们之间输入英文逗号。完成后，单击“确定”。

5）现在单击“苹果”旁边的黄色单元格，你会看到一个下拉菜单。

图 1.1.8.2

2、下拉列表的最佳做法：使用表格。

我们刚刚介绍了如何为部门列表插入下拉菜单。但如果此列表发生了更改会怎么样？例如，如果新增了一个叫做“奶制品”的部门？必须更新数据有效性对话框。但是，更有效的方法是先创建一个表格：

图 1.1.8.3

1）在 F 列中，单击表示某个部门的单元格。例如，单击“肉类”。

2）按“Ctrl+T”创建表，然后按“确定”。

3）现在，再次设置数据有效性。在 D 列中，选择“部门”下方的所有空白单元格。

4）在“数据”选项卡上，单击“数据验证”。在“允许”下，单击“序列”。

5）在“来源”框中单击，然后单击向上箭头按钮

图 1.1.8.4

6）单击并拖动，仅选择 F 列中的“农产品”、“肉类”和“烘焙品”单元格。然后单击向下箭头按钮

图 1.1.8.5

7）在“来源”框中应会看到：=$F$32:$F$34。（如果没有看到，可输入以上信息。）单击“确定”。

8）现在，单击下拉箭头。只显示三个部门：农产品、肉类和烘焙品。但如果在“烘焙品”下方的 F 列内添加新部门，新部门将会更新在下拉菜单中。

图 1.1.8.6

3.1.9 分析

1、快速分析数据

下面介绍了分析数据以快速确定模式和趋势的方法：

图 1.1.9.1

1）单击并拖动以选择右侧的所有单元格，然后单击右下角的此按钮：

图 1.1.9.2

2）在出现的面板上，单击“数据条”。“十月”、“十一月”和“十二月”列下的单元格中将出现可视化其金额的特殊数据条。

图 1.1.9.3

3）现在清除数据条。再次单击此按钮：

图 1.1.9.4

4）在出现的面板上，单击右侧的“清除格式”按钮。

2、快速制作图表

可以随时使用“插入”选项卡创建图表。但下面是制作图表的另一种方式，使用“快速分析”按钮。这一次，我们将使用键盘快捷键：

图 1.1.9.5

1）单击右侧单元格中的数据，然后按“Ctrl+Q”

2）在出现的窗格上，单击“图表”。

3）单击第一个“簇状柱形图”按钮。

4）随即显示一个新的簇状柱形图。移动到任何所需位置。请注意，每个产品有三个柱形，每个柱形表示各月的销售额。

图 1.1.9.6

3、快速制作迷你图

假设要在这些数据的右侧增加一些趋势线，显示三个月内金额的上升或下降情况。无需制作 8 个小折线图。可以改为制作迷你图。

图 1.1.9.7

1）单击右侧单元格中的任一数据，然后按“Ctrl+Q”

2）在出现的面板上，单击“迷你图”，然后单击“折线图”按钮。

3）迷你图随即出现在“十二月”列的右侧。每条折线表示该行的数据，并显示金额是上升还是下降。

4）若要清除迷你图，请单击迷你图并拖动以进行全选。“迷你图工具设计”选项卡将显示在窗口的顶部。若未显示，则选择“设计”选项卡，然后单击最后的“清除”按钮。

图 1.1.9.8

3.1.10 图表

1、推荐使用的图表

图 1.1.10.1

1）单击右侧数据中的任意位置，然后单击“插入” >“推荐的图表”。

2）你会看到几个建议。单击左侧第二个名为“簇状柱形图”的图表。然后单击“确定”。

3）柱形图显示每年与会者的总人数。可将其移动到任何所需位置。

4）现在，可以添加趋势线。选择图表，“图表工具”选项卡将显示在 Excel 窗口的顶部。

5）在“图表工具”选项卡上，单击“设计”。然后单击“添加图表元素”>“趋势线”>“线性”。现在，创建了一条趋势线，显示随时间推移销售数量的大致走向。

图 1.1.10.2

2、横坐标轴和纵坐标轴

在学校里，你可能已经学过了什么是 x 轴和 y 轴。Excel 也有这两个轴，但其名称不同。

在Excel 中，其名称为：

• 位于底部的 x 轴称为横坐标轴。

• 指示上下的 y 轴称为纵坐标轴。

每个坐标轴都可以是数值轴或分类轴。

• 数值轴表示数值。例如，数值轴可以表示金额、小时数、持续时间、温度等。右侧的纵坐标轴是数值轴。

• 分类轴表示日期、人名、产品名称等。右侧的横坐标显示有年份，因此是分类轴。

图 1.1.10.3

3.1.11 数据透视表

1、使用数据透视表汇总数据

1）查看“日期”、“销售人员”、“产品”和“金额”列。你能快速确定哪个产品是最赚钱的吗？或者哪个销售人员的销售额名列前茅？此时可使用下方的数据透视表。

2）创建数据透视表时，单击几个按钮即可汇总数据。现在我们知道了哪个产品是最赚钱的。

3）现在，将介绍如何透视数据，以便可以找出哪位销售人员的销售额名列前茅。右键单击数据透视表中的任意单元格，然后单击“显示字段列表”。

4）将显示“数据透视表字段”窗格。在窗格底部的“行”下方，单击“产品”，然后单击“删除字段”。

5）在窗格顶部，单击“销售人员”复选框。现在可以知道谁是业绩领先的销售人员。

图 1.1.11.1

2、创建数据透视表

现在，你将自己创建数据透视表，以便在需要汇总数据时知道如何制作数据透视表。

图 1.1.11.2

1）单击右侧单元格中的数据，然后单击“插入”菜单上的“数据透视表”。

2）在出现的对话框中，单击“现有工作表”，然后在“位置”框中键入 C42。单击“确定”。

3）右侧将显示“数据透视表字段”窗格。

4）在窗格顶部，单击“产品”复选框。

图 1.1.11.3

执行该操作时，“产品”字段将添加到窗格底部的“行”区域。而且，产品数据会在新数据透视表中显示为“行标签”。

5）在窗格顶部，单击“金额”复选框。

图 1.1.11.4

执行该操作时，“金额”字段将添加到窗格底部的“值”区域。同时，数据透视表中的每个产品的金额都已汇总。

图 1.1.11.5

题外话：Excel的熟练使用需要通过动手去操作、练习并应用到实际工作中，这里的文章只能做个总结归纳，建议有需要的同学可以去听一下网易云课堂王佩丰老师的Excel课程，该课程从Excel常用功能、函数与公式和图表可视化等方面讲解，并且该课程免费，这里附上课程链接：https://study.163.com/course/courseMain.htm?courseId=670032