计算机二级 office —— Excel（1）

一、鼠标光标

1. 为白色十字：选择光标。

2. 为黑色十字：填充光标。

二、数据的输入和填充（自定义序列）

1. 快速输入前导零：

  ①. 法一：比如我们要在序号那一列输入“001”等，但是输入001后又变为了1，这时我们需要点击开始操作框的数字区域，点开下拉框，选择“文本”，之后再输入“001”就可以达到想要效果了。

  ②. 法二：在英文状态下，在单元格中输入后单引号+001，即可实现当前单元格输入“001”。

  ③. 二者的区别：法一可以实现当前列的前导零，法二仅能实现当前单元格的前导零。

2. 快速输入一列顺序序号：首先在前三个单元格输入序号，比如“001”“002”“003”，然后框选三个单元格，将鼠标放在第三个单元格的右下角，可以看到选择光标变成了填充光标，这时往下拖拉（即从上往下进行填充），即可实现输入一列序号，按照前三个单元格的等差数列规律进行填充。

3. 快速复制一列某单元格数字：点击该单元格，同上所述在填充光标情况下往下拖拉，拖拉到底后，右下角会出现有＋号的小方块，点击小方块，勾选“复制单元格”，即可实现复制操作。

4. 自定义序列：如果我们输入“星期一”，然后从左往右进行填充，即可实现从“星期一”到“星期日”的填充。如果我们要实现从“周一”到“周日”却达不到该效果，这时我们会用到自定义序列。点击文件，然后点击“选项”，再点击“高级”，往下拉，找到“编辑自定义列表”，然后添加新序列，然后再进行上述操作后就可以实现想要的效果了。

三、数据的有效性

  为了避免出现错误数据，我们可以用到数据有效性。

  ①. 框选需要设置有效条件的单元格，在数据操作框的数据工具区域，点击“数据有效性”，点击第一个“数据有效性”，会弹出弹框，我们可以在“设置”这个操作栏里设置自己需要的有效性条件，设置完成后点确定，当我们再次输入数据时，如果所输入数据不符合刚才所设置的条件，则会弹出下图所示的弹框，这便做到了数据的有效性。

  ②. 然而有时候会有人不了解上面弹框的意思，这就需要我们设置一下“出错警告”，在“出错警告”框中设置出错警告的内容，这样在输入无效数据时弹出的框我们很容易理解是哪里出错了。如下图示例所示。

  ③. 当然还可以在别人输入信息时候给个提示，如下图示例所示。设置步骤：在上图所示的操作栏“出错警告”左面有个“输入信息”，根据需求设置一下就OK了。

  ④. 有时我们会输错字，比如让你填入是哪个部门（前提是，知道都有哪些部门），你一不小心打错字了。所以制作表格的人可以提前这样设置：框选需要设置的那一列单元格，还是那个弹出框，在“设置”操作栏的允许下拉框中选择“序列”，然后输入默认的部门名称，用,连接（英文逗号），如下图示例所示。这样在该列的每个单元格都会出现下拉框，直接选择即可。

四、行和列的调整

  行：选择某行单元格，在左侧点击让光标变成向右的箭头，然后往下拖，然后选择被选中的几行中某一行的第一个单元格，等光标变成黑色十字形，然后往下拖拉到你需要的高度，松手后即可发现被选中的几行均变成了相同高度。

  列：列与行相似。

五、添加边框和底纹

1. 边框：可以设置已有边框样式，也可以点击其它边框，设置颜色、线条等。
   
2. 底纹：点击即可按题目要求设置颜色。

六、单元格样式（不常考）、表格样式（常考）、条件样式（常考）

  这三个样式都在开始操作框的样式区域。

1. 单元格样式：按照题目要求选择样式即可。
   

2. 表格样式：按照题目要求选择所需样式即可。可选样式如下图所示。当你设置后，会出现表格工具栏，在该操作栏常考“删除重复项”、“转换为区域”、“汇总行”。
   

3. 条件格式（必考）：

  ①. 框选需要设置的单元格，点击“条件格式”的“新建规则”，然后根据要求设置即可。如下图示例所示。

  ②. 也可以选择使用已有的条件格式进行设置。

七、主题

  根据题目要求选择即可。

八、页面设置（打印标题、打印区域、分隔符）

1. 打印区域：有时需要打印某表格中某几行数据，可以设置打印区域。首先框选打印区域，然后打开页面布局操作框的页面设置区域，点击“打印区域”的“设置打印区域”，然后可以发现框选区域会出现虚线框，这时候点开文件的“打印”，可以看到打印效果。
2. 打印标题：上面操作打印出来的是个没有表头标题的，可以点击打印标题进行设置。
3. 分隔符：有时需要分区域打印表格内容，这是可以在分割位置的第一个单元格上设置分隔符，这样打印时可以分隔打印。

九、工作簿的保护

  ①. 点开“审阅”操作框的“保护工作簿”，弹出下图1所示的弹框，设置密码后，对工作簿内的工作表就起到了一定的保护作用，如下图2所示。如果想要撤销保护作用，可以再点击“保护工作簿”，输入密码即可。

  ②. 在右键工作表时，有个“隐藏”，点击即可隐藏该工作表。如果想取消隐藏，可以再右键点击“取消隐藏”。在工作表隐藏情况下，点击设置工作簿的保护，这样别人就不会看到该表格数据了。

  ③. 对工作簿进行加密：点击“文件”操作框，再点开“保护工作簿”的下拉框，点击“用密码进行加密”，设置密码后，这样别人打开该工作簿就会输入密码之后才能看到内容。

十、工作表的基本操作

  工作表常考以下几种操作：

  ①. 插入、删除、重命名、工作表标签颜色、隐藏均点击操作即可。
  ②. 移动或复制：当我们需要把一个Excel中的工作表移动或复制到另一个Excel时，就需要这个操作了。进行这步操作的前提是，两个Excel均处于打开状态。然后右键点击“移动或复制”，如下图所示。然后在工作簿中选择所要移动或复制到的工作簿（只有两个工作簿均处于打开状态，该下拉框内才有另一个工作簿），如果你只是想要移动，再直接点击“确定”即可；如果你是想要复制，就需要勾选“建立副本”，然后再点击“确定”。

  ③. 日常办公中我们会需要用到“保护工作表”这一操作。选中要保护的单元格，点击审阅操作框的“保护工作表”，设置密码，这样当我们点击该区域单元格时，会弹窗提醒我们无法进行修改。

  ④. 日常生活中我们有时需要设置除了某个区域单元格可以编辑数据其他区域只读，我们可以这样操作：首先我们选中可编辑区域单元格，然后点开开始操作框—字体区域—右下角，再点击“保护”操作栏，取消勾选“锁定”，然后我们再进行“保护工作表”的操作，即可实现 除该区域可以编辑以外，其他地方只读。

  ⑤. 设置用户可编辑区域：选中可编辑区域，点击“允许用户编辑区域”，然后点击“新建”，设置区域和密码，最后点击“保护工作表”，即可实现—给用户密码，让用户只可以编辑该区域。

十一、多工作表操作

  按住 Ctrl ，将多个工作表选中，这样在其中一个工作表的操作都会同时操作在其他选中的工作表。

十二、函数

1. 以英文的=开头，乘*，除/。常考SUM、SUMIF、SUMIFS、IF、vlookup。

2. SUM(求和的范围或数据)函数：在框内输入=SUM，然后按 Tab 键，会出现前括号( ，或者直接输入前括号( ，然后框选要相加区域，可以看到中间是以冒号:连接的，这是连续的（或者按Ctrl + 鼠标左键 依次选中，可以看到使用逗号连接的，这是不连续的。也可以分别选中单元格，点每个单元格中间输入英文逗号），最后按 Enter 键即可完成求和运算。如果想要该列均实现该求和运算，往下拖动即可。

3. SUMIF(条件范围，条件，求和范围)函数：对满足条件的单元格求和（如果求和范围和条件范围一样，求和范围可以不写）。比如可以实现“计算男员工的业绩之和”这一操作，在框内输入=SUMIF(，也可以按照上面说的按Tab键。然后框选条件范围——性别那一列的区域，然后输入英文逗号，然后输入英文双引号，双引号内输入条件，比如这里输入"男"，然后再输入英文逗号，再框选和性别对应的求和区域，最后按 Enter 键即可完成对满足条件的单元格求和。如下图所示。

4. SUMIFS求和范围，条件范围，条件，条件范围，条件)函数：如下图实例所示。其中“*车间”表示以车间俩字结尾的。
   

5. AVERAGE()函数：求平均值。

6. MAX()函数：求最高的。

7. MIN()函数：求最小。

8. IF(逻辑测试，如果是真 的值，如果是假 的值)函数：如下图示例所示。
   
   if函数可以嵌套多个，如下图示例所示。
   

9. ABS()函数：取绝对值——=ABS(数值1-数值2)。

10. INT()函数： 取整——=INT(数值1-数值2)。

11. TRUNC()函数：取整——=TRUNC(数值1-数值2)。与INT()的区别是，二者取整后为正数时相同，为负数时相差1，其中INT() 比TRUNC() 小1。

12. ROUND()函数：四舍五入——ROUND(数值，小数点个数)。

13. LEFT(text文本，截取个数)：左截取。例如=LEFT(“左中右”，1)，结果为“左”；=LEFT(“左中右”，2)，结果为“左中”。其中1表示从左截取一个，2表示从左截取两个，3…。

14. RIGHT(text文本，截取个数)：右截取。和上面类似，是从右开始截取。

15. MID(text文本，从几开始截取，截取个数)：中间截取。

如下图所示示例——从身份证号截取出生年月日：

如下图所示示例——从出生年月日得到年龄：

16. WEEKDAY()函数：介绍如下图所示。

例如根据星期几判断是否加班，如下图所示。

17. VLOOKUP(查找变量、表数组，列序号，精确匹配/近似匹配)函数：如下图示例所示，根据某个表内编号从另个表中区域查找该编号，然后返回所需信息所在列序号。

18. LOOKUP(查找变量，查找区域，返回数据的区域)函数：如下图示例所示。

19. HLOOKUP()函数
20. RANK.EQ(要比较数据，范围，升序或降序)函数：默认降序，可不填。


21. COUNT()函数：计数函数。

十三、迷你图、图表

1. 创建迷你图：在某行数据的后一列点击插入操作栏的迷你图区域的“折线图”，然后框选数据范围，点击确定即可。然后可以看到出现了迷你图工具栏，我们可以自由变换成柱形图、高点低点、颜色等。删除迷你图时点击“清除”，想要前几个是折线图后几个是柱形图时，先取消组合再设置。
   
   
   

2. 创建图表：框选数据，然后点击插入图表，即可创建，可以看到出现了图表工具操作栏，可以进行“切换行/列”等操作，例如点击“选择数据”可以重新选择编辑数据。在“格式”操作栏也可以进行颜色等的修改。

3. 如何将图表移动到单独的工作表？
   点击图表工具操作栏的位置区域的“移动图表”，勾选“新工作表”，可以取个名字，点击“确定”即可实现。也可以通过复制粘贴实现。

4. 在图表工具操作栏的“设计”里：其中“切换行/列”、“移动图表”常考。数据选错时可以点击“选择数据”，“图表布局”、“图表样式”可以根据题目要求进行选择设置。
   

5. 在图表工具操作栏的“布局”里：可以点击最左侧的下拉框直接选中某系列，对其进行别的设置（如修改颜色使其更醒目），也可以选择图例，给图例加个背景色。点击“图标标题”可以添加题目要求的标题；还可以点击“坐标轴标题”添加坐标轴标题；点击“图例”可以设置图例小方块位置；点击“数据标签可以在柱形图等加上数据，更方便看；还可以选中图表右键点击“设置图标区格式”，进行填充等操作；
   

6. 如何更改垂直坐标轴的数值？
   选中垂直坐标轴，右键点击“设置坐标轴格式”，设置下图箭头所指位置即可。
   

十四、合并计算

  例如，我们第一个表放了语文成绩，第二个表放了数学成绩，准备在第三个表合计总成绩，这就需要用到合并计算。操作步骤如下：

  首先打开准备放总成绩的表格，然后点开数据操作框的数据工具区域的“合并运算”，点击下图1所示箭头所指位置，分别选中“语文成绩”表格的数据区域，然后点击添加，“数学成绩”表格也一样，都添加后，然后勾选“最左侧”，可以看到下图2所示效果。点击确定，可以看到下图3所示效果。（这里如果勾选“首行”，可以看到语文数学成绩都出现在总成绩表里了，可以自己试试）

十五、排序与筛选

1. 可以框选需要排序的单元格，然后点击“排序”的左侧俩箭头直接进行升序、降序操作。点击后会出现下图所示的弹窗，点击排序即可。
   
2. 按多个条件进行排序的，可以点击“排序”，然后在关键字中输入条件等，可以再添加条件，最后点确定即可。
   
3. 自定义排序：如果公司有要求，要求先排某部门，再排……首先点击“排序”后选择“自定义排序”，如下图1所示。然后输入序列，点击“添加”，最后点击确定即可。
   
   
4. 有时还会要求按照单元格颜色进行排序，在下图所示位置选择“单元格颜色”，在选择位于顶端底端即可。
   
5. 筛选：框选数据区域，然后点击“筛选”，然后可以看到表头显示了下拉框，根据需求操作即可。
   有时需要多条件筛选，比如筛选出一班语文成绩>90的，二班数学成绩<80的，我们可以这样操作：先照下图1所示列出条件，然后选中数据区域，再点击筛选旁边的“高级”，然后照下图2所示，在条件区域框内选择下图1所写的条件（框选），点击确定即可。
   
   

十六、分类汇总

  ①. 比如我们想求出各个班各科的平均成绩，就需要分类汇总。首先框选班级那一列的数据，对数据按班级进行升序或降序，然后框选所有数据，点击数据操作框的分级显示区域的“分类汇总”，在“分类字段”中选择“班级”，“汇总方式”选择“平均值”，再勾选所有科目，勾选“每组数据分页”，点击确定，最后可以显现如图2所示的分页预览效果，方便让各个班级打印。

  ②. 如何把汇总好的数据单独放在一个表里？
  先把单元格折合成下图1所示样子，然后点击开始操作框边际区域的“定位条件”，再勾选“可见单元格”，点击确定，然后右键复制平均值的单元格数据，打开新的工作表粘贴进去，可以看到粘贴的是选中的平均成绩。

十七、数据透视表

  先框选数据区域，然后点击上图所示的“数据透视表”的“数据透视表”，会弹出下图1所示的弹窗，然后点击确定。右面会出现“数据透视表字段列表”（如下图2所示）。如果不慎关闭了字段列表，可以点击数据透视表工具——选项操作栏——显示区域——“字段列表”，字段列表就显示出来了。当我们勾选所有要添加到报表的字段后，可以看到昨面出现下图2所示效果，再点击“数值”区域“求和项……”的下拉框，点击“值字段设置”，将计算类型变成“平均值”，点击确定（可以再点击确定之前设置数字格式），可以看到语文成绩那个位置变成了平均值，右下角数值里也是，如下图3所示效果。如果需要数据透视图，可以点击数据透视表工具——选项-工具区域-数据透视图，插入即可。

十八、模拟分析

十九、获取外部数据

1. 自文本：点击“自文本”，选择该文本所在目录，打开该目录，会出现下图所示弹窗。根据要求一步一步完成导入即可。（文本里的逗号、分号等必须是英文的；其次，如果有数字，选中该列数字，在第三步勾选“文本”）
   
2. 自网站：点击自网站，输入网址，点击“转到”，找到表格，再点击表格左上角的箭头，可以看到箭头变成了√，再点击“导入”，再点击“确定”，即可。

二十、分列

  在数据区域后插入一列，选中数据区域，点击分列，根据需求如下图所示一步一步完成分列即可（也可以勾选“固定宽度”）。

二十一、插入链接

  和Word中的操作类似。