Excel基础24讲

大批量输入公式前不用输入=的操作：文件，选项，高级，Lotus公式
XLW 工作区可同时调出多个窗格作对比，编辑其中一个窗格另外窗格也会跟随变化视图，新建窗口，全部重排。一个窗口变成两个窗口进行数据对比：视图 → 新建窗口 →全部重拍 → 垂直并排 → 保存工作区（XLW)
Shift可连选，Ctrl也可连选，但两种连选不一样
交换两列的次序：选中其中一列，将鼠标放在边框线上，会变成上下左右的方位键，拉到另一列的旁边即可
同时选中多列再去调整列宽可以将所有列宽一起调整。选择多列在其中一个边框上双击也可将所有列宽调整成刚好能容纳的宽度。行也是如此。
怎样到达表格的最后：将鼠标放在任意一个单元格的下边线上，双击，会自动跳到最后一个单元格。放到上边线双击是跳到第一个单元格。左右也是如此。
冻结窗格：视图，冻结窗格，可做各种冻结操作。
填充柄：按Ctrl往下拖可以按顺序排下去，Ctrl +; 键可以直接输入今天日期。随便写个日期，变成加号可以拖动时用右键往下拖会出现多种可选择的拖动方式，比如按工作日填充、按月填充等，很智能。左键拖拽可以实现复制或序列顺序
也可以自定义列表。文件，选项，高级，数据上面的编辑自定义列表，自己编辑即可。

2.工作簿，工作表，单元格

新建工作表：点击右侧小加号，双击重命名
更改工作表标签颜色：右键
交换两列数据的位置：选中目标列 → 按住shift键 → 拖动目标列至目标位置
插入/删除多个工作表：左键选中第一个工作表，按住shift，选中最后一个工作表，右键插入/删除
插入多行/列：选中n行/n列，右键插入
交换两列数据位置：选中目标列，鼠标放置右边线处，按住shift键拖动目标列至目标位置
调整行高列宽：选中多列 → 鼠标放置右边线双击 → 自动调整多列宽度；选择多列 → 手动调整列宽度（也可以同时调整多行宽度，并且都是被一致调整的）
框选多行：名称框中输入（起始行：目标行，比如2:900,选中表格第2至900行的数据）

3.使用小工具，冻结窗格，填充柄，查找和替换

冻结窗格

定义：依据当前被选中单元格锁定表格行与列，滚屏时，被冻结的标题行总显示在上面，标题列显示在左面
位置：选项卡 → 视图 → 冻结窗格
冻结首行：视图 - 冻结窗格 - 冻结首行（即可实现滚动表格，而首行不动）
冻结多行：选择下一行单元格（以冻结前3行为例，选中第4行首个单元格，选择冻结拆分窗格命令）
同时冻结行与列：选择交界处单元格，冻结窗格。选中某一单元格，选择冻结拆分窗格命令，被选中的单元格符合以下规律：总是冻结被选中单元格上方和左侧的单元格，下方和右侧单元格是可变动范围

填充柄

定义：鼠标放置在单元格右下方变成黑色实心加号拖拽自动填充单元格
用法：选中n个单元格 → 加号拖拽。
拖拽规律为序列填充，按住Ctrl再拖拽可实现复制填充
自定义填充顺序：文件 - 选项 - 高级 - 编辑自定义列表 - 输入序列添加
其它格式拖拽：选中单元格，移动至右下角按住右键拖拽会弹出填充快捷菜单，可以选择想要的填充格式

小技巧

快速到达表格最前&最后：选中单元格，在表格的上边线双击到达表格最前，表格下边线双击到达表格最后
显示今天日期快捷键：Ctrl + : ，直接向下拖拽默认序列拖拽

第二讲：单元格格式设置

1.使用单元格格式工具美化表格

“设置单元格格式”对话框在哪里？

选中单元格 - 右键 - 设置单元格格式
开始选项卡 - 单元格功能区

合并单元格

多个小单元格合并成一个大单元格，常用于标题
选中多个单元格 - 开始选项卡 - 合并后居中
合并多行：选中多个单元格 - 开始选项卡 - 跨行合并（避免用上述操作合并3次）

边框线

若不设置，打印不会有边框
选中整个表格区域的单元格 → 开始选项卡 → 边框 → 选择所有框线
如需设置边框格式，选中单元格区域后，右击选择设置单元格格式

斜线

设置斜线：在单元格内右键 - 设置单元格格式 - 边框 - 选择斜线
斜线双表头（通过双行文字实现）：表头1单元格设置斜线 - 双击单元格 - 写表头2 - alt + enter - 左右对齐，空格设置格式 - 回车
为什么应用空格调整而不直接设置右对齐和左对齐？因为对齐是针对整个单元格内容，不能同时设置一行左对齐，另一行右对齐
如果需要三个斜线：插入 - 形状 - 直线 - 鼠标绘制斜线
填充文字：通过双行文字实现，回车（alt + enter）分行，文字左对齐，第一行输入空格将文字右对齐

格式刷

先在已经完成格式设置的表格里应用格式刷，可以刷到另一个新表格，就会应用一样的格式
双击格式刷按钮，可以保持格式刷状态，刷新多个单元格的格式，直到按下esc退出

2.单元格数据格式

定义：在Excel当中，会去掉无意义的数字格式。比如，输入2400.00，会直接省略成2400，输入007，会变成7。所以需要设置单元格数字格式

路径：选中单元格 - 右键设置单元格格式 - 可以设置不同的数字类别（但数值永远是数值，只是显示格式改变了）

如果一组数字是文本格式，那么这组数字不能运算，但仍然可以把它改成数字：选中文本格式的数字，然后这些文本格式数字的左上角会出现黄色棱形感叹号，点击这个感叹号，选择“转换成数字”，就可以转换成数字了

自定义数字格式：可以实现在所有数值前面或后面加内容，如在数值后加“元”字，并且这些数值还可以做相加等运算（因为设置格式不改变值）

自定义格式：；；；(表示隐藏)

yyyy/m/d (2013/2/6)；yyyy/mm/dd (2013/02/06)；

3个m表示月份英文简写，4个m表示月份英文全称，d含义相同：

yyyy-mm-dd (2013-02-06) ；

dd-mmm-yyyy(06-Frb-2013)

aaaa表示：星期几(日期转换成星期)

周aaa表示：周几

3.使用“分列”工具

将一组txt内容复制黏贴到Excel表格，数据会全部在A列，此时找到数据选项卡 - 分列，根据实际情况选择通过【分隔符号】或者【固定宽度】分列

一般是通过【分隔符号】来分列，找到数据里可以用来分隔的符号

如果一次分列没有完成，可以再次找到能够分隔的符号，进行二次分列

日期是以文本格式输入的，无法通过“设置单元格格式”改为其他格式，可以通过分列的第3步修改列数据格式，修改成常规或者日期，修改成功后就可以修改格式了

第三讲：查找，替换与定位

1.查找与替换

快捷键：查找Ctrl + F ，替换Ctrl + H

补充：替换选项卡里有 “选项” 功能，可选择 “单元格匹配”，避免一些不必要的替换

按值查找

开始选项卡 - 查找和选择
替换选项卡里有“选项”功能，选择“单元格匹配”，避免一些不必要的替换

按格式查找

如按颜色查找替换：格式按钮 - 填充 - 选择颜色

模糊查找

认识通配符：* 和 ?都是通配符

指的是任意一个或多个值
?（英文的半角问号）指的是一个模糊字
注意：像 “张?” 替换成 “经理的亲戚” ，这样 “张三” 也会被替换成 “经理的亲戚三” 。此时需要开启单元格匹配，张?表示一个模糊字，这样单元格内 “张三三” 就不会被替换了。用 ? 时，常常需要勾选单元格匹配，为了限制字符
规避通配符的作用，用波浪线 ~(指让后面的字符不生效，例如：将张?替换成经理张~?)

2.定位工具

通过名称框定位单元格及区域位置

如在名称框输入A900：B1000即可选中该单元格区域
在名称框输入100:200即可选中100到200整行

定义名称

为某个单元格区域自定义名称，以后可以直接使用这个名称查找，通过输入名称，或运用 “查找和替换” - “转到”
选择某个区域，然后在名称框中输入一个名字，然后回车，这个名字就被保留了下来，下一次就可以比较容易找到

定位条件

查找和替换 - 转到 - 定位条件
查找和替换 - 定位条件

使用定位条件解决以下问题

为有批注的单元格设置红色填充色：查找和替换 - 定位条件里选择批注 - 选中 - 可以自行修改颜色
为有公式的单元格设置红色填充色：查找和替换 - 定位条件里选择公式 - 选中 - 可以自行修改颜色
填充解除单元格合并后遗留的空白单元格：查找和替换 - 定位条件里面选择空值 - 选中 - 输入= - 并按下上方向键 - 全部填充快捷键（Ctrl + enter）
批量删除图片：查找和替换 - 定位条件里选择对象 - 选中 - 按删除键删除，如果没有图片，执行上述操作后Excel会警告 “找不到对象”，除此之外，在查找和替换里找到 “选择对象”，也可以只框选图片

批注

标志：右上角红色小三角
插入批注：选中单元格 - 右键 - 插入批注 - 编辑文字
显示/隐藏单个批注：选中单元格 - 右键 - 显示隐藏批注
显示所有批注：审阅选项卡 - 显示所有批注
删除所有批注：选中区域，右键删除批注
更改批注框形状：插入 - 形状 - 选择目标形状
在批注中插入图片：设置批注格式 - 颜色与线条 - 填充 - 颜色 - 填充效果 - 图片

取消合并单元格后空白内容处理

选中区域 - 定位条件：空值 → 在一个空单元格内输入 “=”
若填充与上/下方相同的内容，则按下上方向键，然后软回车（Ctrl + enter）
若填充其它内容，则手动输入再软回车
选中一个区域，在一个单元格输入数据，按下软回车，则该区域所有单元格都被填上相同的数据

第四讲：排序与筛选

1.排序

注意：执行排序操作的时候避免选中某列，如果以当前选中的区域排序会导致数据错乱，可以选择全部数据或者这一列中的某个单元格

简单排序

开始 - 排序和筛选 - 升序/降序

多条件排序

如将语数外成绩排序，当数学成绩一样时按照语文成绩排，语文成绩一样时按英语成绩排：排序和筛选 - 自定义排序 - 添加排序依据
也可以分别进行单列排序：先依次排次要关键字，再排主要关键字（先排英文，再排语文）

按颜色排序

排序和筛选 - 自定义排序 - 添加排序依据 - 单元格颜色

自定义排序次序

如果要排序的值是文字，默认按照拼音首字母排序
如果要改变，那么选择次序为自定义序列

利用排序插入行制作工资条（格式：表头 + 个人信息）

假设第一行是工资表表头，下方11行是员工个人信息
再复制10行工资表表头
在第一行表头旁的空白列输入0，在员工个人信息输入1-11
其它10行表头输入1.5-10.5
最后将数字按照升序排序即可

打印要求每页都有表头

页面布局 - 页面设置 - 工作表 - 设置顶端标题行 - 选中第一行 - 打印
设置后，第一行名称栏会显示print titles

2.筛选

使用筛选

点击筛选区域的任意单元格 - 排序和筛选 - 筛选 - 第一行出现下拉箭头
筛选完了，让原本所有数据都出现，在下拉箭头勾选 “全选”
想要把筛选出来的数据复制到另一个表中去，却发现粘贴了原来的整个表，解决方法：筛选完成后 - 查找和选择 - 定位条件 - 可见单元格 - 复制

在筛选中使用多个条件

数字筛选：根据大于/小于/等于等条件来筛选
同时筛选多列：分别进行筛选，在上一列的筛选结果中再筛选
文本筛选：输入开头/结尾能匹配的关键词

高级筛选

筛选不重复值：数据选项卡 - 高级筛选 - 可选择在原区域/其它位置显示筛选结果 - 选中列表区域 - 选中条件区域（没有就不动）- 选中复制到哪个单元格 - 勾选不重复的记录。应用场景为找出不重复的数量
使用常量条件区域：条件区域指的是在数据中找到符合条件的需要另外输出条件，且（与）的条件写在同一行，或的条件错开位置，条件也可以输入大于小于

且的情况：

部门	科目划分
一车间	邮寄费

或的情况：

部门	科目划分
一车间
	邮寄费

第五讲：分类汇总与数据有效性

1.分类汇总工具

认识分类汇总

数据选项卡 - 分类汇总
按什么分类，汇总什么，怎么汇总

使用分类汇总前先排序

选中要分类所属列的某个单元格 - 点击【开始 - 排序和筛选】或【数据】里的【升序】或【降序】- 完成排序操作
点击【数据】里的【分类汇总】，选择分类字段（即根据哪一项进行分类），汇总项（即对哪一项进行汇总）汇总方式（可对汇总项进行求和，计数，求平均值等操作）

分类汇总的嵌套

根据多个字段进行分类汇总，即依次根据不同的字段进行重复操作
选择主要关键字，次要关键字进行排序
依次对主要关键字为分类字段先进行分类汇总，第二次起注意取消 “替换当前分类” 的勾选
可以通过点击工作表左上角的【1,2,3,4】来查看结果

复制分类汇总的结果区域

选中结果区域 - 选择【开始 - 查找和选择 - 定位条件】- 选择【可见单元格】（可用快捷键alt+;）- 复制

使用分类汇总批量合并内容相同的单元格

将需要批量合并的列先排序
数据 - 分类汇总 - 将该列表头设置为【分类字段和汇总项】- 将汇总方式改为计数（不改也问题不大）- 确定
完成后会自动新生成 - 列计数 - 选中新列并选择【开始 - 查找和选择 - 定位条件】- 定位到【空值】- 合并单元格
数据 - 分类汇总 - 全部删除，即可删除分类汇总的数据
选中新出现的列 - 开始 - 格式刷 - 选中目标列，即可粘贴格式，合并内容相同的单元格（或者先复制再【选择性粘贴 - 格式】）

2.设置数据有效性

设置整数数据有效性

仅能输入500~1000之间的整数
选中目标列 - 数据选项卡 - 数据有效性 - 选择允许【整数】，数据【介于】，最小值【500】，最大值【1000】- 确定

设置文本长度数据有效性

仅能输入字符长度为8位的产品编码
选中目标列 - 数据选项卡 - 数据有效性 - 允许【文本长度】，数据【等于】，长度【8】- 确定

设置序列制作下拉框

仅能输入现金、转账、支票
选中目标列 - 数据选项卡 - 数据有效性 - 允许【序列】，来源【现金,转账,支票】- 确定

数据有效性的其他设置

输入法切换：可以选择只能以中文或英文输入，功能能否实现和个人电脑输入法设置有关
单元格信息：可以将【输入无效数据时现实的出错警告】改为【警告】并手动输入警告信息引起别人注意，相当于一种半保护状态
单元格信息保护：选中目标区域 - 数据选项卡 - 数据有效性 - 允许【自定义】- 输入一个逻辑值为 “false” 的公式（可以直接输入0），这样公式无法成立表格也无法被编辑

第六讲：认识数据透视表

常见数据透视表

字段名称：原始表格中列的字段
报表筛选：如果把某个字段拖拽到这个里面，它就会变为筛选项，相当于根据该字段对透视表做筛选
列和行：如果想在透视表的行上显示什么就把对应字段拖拽到行上，想在列上显示什么就把什么拖拽到列标签
值：就是透视表中的数据，有求和，计数，求最大值等多种汇总依据
创建步骤：选中任意一个单元格，点击【插入 - 数据透视表 - 确定】→ 点中生成的数据透视表 → 选择【数据透视表选项 - 显示】→ 勾选【经典透视表布局】（可做可不做，看个人习惯）→ 根据需要将字段拉入数据透视表中的行/列/值，即完成一个简单的数据透视表

更改数据透视表汇总方式

熟知的默认汇总方式是求和，可以双击表头值字段，可以更改汇总方式（如更改为计数，平均值等）
双击表中汇总后的数值可以得到一张新表，能够查看详细记录
双击表头第一个单元格，除了可以更改汇总方式，也可以选择【无】，则去掉分类汇总（不同版本/电脑不一定实现，也可以通过点击数据透视表中的任一单元格，此时表格顶端会出现“设计”功能选项卡，点击“设计”中的子菜单 “分类汇总”，选择 “不显示分类汇总”）

数据透视表中的组合

对日期进行组合：选中日期和其它字段拉入数据透视表中的 “行字段”中，注意放的位置，一级字段放前面，如果日期字段是以天为统计维度，可以选中任一日期右击组合，能够修改统计维度为月/季度/年
对数据进行划分区间的统计：选中数据字段拉入“行字段”中，再从右侧选中数据字段拉入 “值字段”，选中 “行字段” 中任意一个数据 - 右击 - 组合 - 确定起止和数值间隔

汇总多列数据

将姓名字段拉入 “行字段” - 再将工号字段拉入 “行字段”，此时姓名和公号字段呈现上下分布
但是针对姓名和工号默认出现了汇总，可通过设计 - 分类汇总 - 不显示分类汇总隐藏
目的是统计生产数量的求和项，平均产量，最大产量和最小产量，所以重复4次将生产数量拉入 “值字段”，此时如果4行生产数量呈现上下分布，新生成了一行数据和汇总，可以把数据字段拉到汇总字段上
双击 “求和项”，更改【值字段汇总方式】也可以单击右键，选择【值字段设置】进行更改
更改值字段名称：选中后双击 - 在字段名称内更改，也可以在编辑栏中更改，不可与原有字段重复
更改表格样式：点中透视表中任意单元格，选择窗口最上面出现的【数据透视表工具 - 设计】，选择喜欢的样式

利用筛选字段自动创建工作表

插入数据透视表，将某字段拖入“报表筛选字段”中，则可以筛选数据
但是如果要同时展示多个字段的数据透视表，也可以利用筛选字段自动创建
准备要新建表格的一列数据 - 插入数据透视表 - 将字段拖入“报表筛选字段”和“值字段”中 - 打开数据透视表分析选项卡 - 找到【选项】- 选择【显示报表筛选页】就自动生成了新的工作表
但是每一张工作表上都有透视表，需要删除这些透视表：按住shift键选中所有生成的工作表 - 复制空白区域覆盖生成的透视表，就去掉了透视表区域，新的工作表也批量生成了