Excel操作
1 快捷键
| 快捷键 | 功能 |
|---|---|
| Enter / ↓ | 到下方单元格 |
| Tab/ → | 到右方单元格 |
| Tab | 抛开函数的最后一行（任选一个单元格）：表格自动增加一行 |
| shift+Tab / ← | 到左方单元格 |
| ↑ | 到上方单元格 |
| Alt+↓ | 选择上方单元格出现的文字 |
| Ctrl+; | 自动输入今天的日期 |
| Ctrl+shift+; | 自动输入现在的时间 |
| Ctrl+1 | 设置单元格格式 |
| Ctrl+T | 转换为表格 |
| Ctrl+→/↓/←/↑ | 将选取框快速移动到四个角落 |
| Ctrl+shift+→/↓/←/↑ | 快速选取某一行/列的所有单元格，整个单元格的选取就要按两次方向键，如→↓ |
| Fn+F4 | 重复上一次指令的功能 |
| Fn+F9 | 重复运算单元格内的公式 |
| Ctrl+~ | 显示所有公式 |
| Alt+Enter | 单元格内换行 |
| Alt+= | 自动加总 |
2 宽度和高度的调整
- 单一行/列的手动调整：拖动行与行/列与列之间
- 根据文字自动调整：双击目标行/列的索引（靠右侧）的间隔
- 调整行距和列高相同：比如调整行距，将所有目标选框框起来，然后双击任意一个列索引间隔
3 对齐
合并后居中：一键合并且居中，用于标题。再点击一次，就可以解除并还原
4 表格样式(线型和填充)
4.1 一个单元格的样式:
方法一：自定义设置
方法二：直接选择“单元格样式”里面的预设
4.2 多个/所有单元格的样式:
- 创建：不需要框选所有表格，选择插入-表格-选择表格样式，或选择“套用表格格式”。
- 不需要奇偶行的颜色差异：取消勾选“镶边行”
- 设置列的奇偶颜色差异：勾选“镶边列”
- 如果需要自定义设置或更多的颜色：页面布局-颜色
- 对列进行自动求和：设计-勾选汇总行，即可对最后一列进行自动求和（见自动加总函数），再利用➕往左拖拽，就可以对所有列进行求和，也可以更改函数
- 关闭样式，即关闭表格的设计，选择设计-转换为区域
5 日期&时间
5.1 日期
创建：最好使用的格式，excel可以自动识别：2021/4/10
变换日期格式：
- 右击单元格-设置单元格格式-选择一个目标格式。
- 自定义：yyyy"年"m"月"d"日" 表示 1981年5月4日，字符串注意用""。
- 中文格式：[DBNUM1]m"月"d’'日" 表示 七月二日
- 星期：aaa
- 常规：自1900年以来的日期总数
自动输入今天的日期：Ctrl+;
自动填充设置：填充好后（拖动➕填充，双击➕填充）-右下角的自动填充选项-设置填充方式
正数日：=TODAY()-H3, 再将格式转化为"常规"
5.2 时间
创建：09:00 / 09:00 AM / 09:00 a和下午 17:00 / 5:00 PM / 5:00 p
自定义时间格式：
- 比如显示上午09:00，改成上午/下午hh:mm。上午和下午（PM，AM）用斜线隔开。一个h表示一位小时的显示。
- 完整的为yyyy/hh/mm/ss
- 常规：0:00设为0，24:00设为1
- [h]表示小时：会将日期计算在内，比如说日期之间相减而计算的时长，可以超过24h
- [m]表示分钟，同上计算依据过去的分钟
6 单元格内容修改
单击单元格：上方选框修改
双击单元格：直接修改
7 公式计算
创建：
- 输入“=”
- 点击要运算的单元格
- 输入运算符
- Enter
8 数字格式
图标：
- 第一个：设置数字的显示格式，如加上$，日期格式等。格式刷可以用于复制
- 百分比样式：%
- 千位分隔样式：用于计数
- 增加和减少小数位数
9 打印
9.1 打印网格
方式一：页面布局-勾选网格线的打印
方式二：手动绘制。
- 绘制单线（边框）：开始-字体-边框-线条颜色-线形
- 绘制网格线（内部边框）：开始-字体-边框-绘制边框网格
- 清除线：shift+拉出对角线
- 退出：Esc/再点击一下边框
方式三：框选出目标范围，开始-字体-边框-选择一个样式
9.2 分页设定
查看 / 修改打印的分页情况：
视图-分页预览，拖动分页线即可。或者利用分页符：选中下一页的第一行-页面布局-分隔符-插入分页符
将标题栏打印到每一页纸：页面布局-打印标题-顶端标题行-选中第一行-确定
将分页还原到最初状态：页面布局-分隔符-重设所有分页符
调节指定宽高的页面数：页面布局-调整为合适大小，比如将宽度设为1，高度设位3。就会打印1*3=3页纸。其缩放比例会因此调整
打印部分单元格：框选目标单元格-页面布局-打印区域-设置打印区域。清除：打印区域-取消打印区域
9.3 页眉和页脚设计
查看整个Excel打印出来的结果/添加页眉和页脚：视图-页面布局
页码：
- 点击需要显示的页码位置（如右下角的小格子）-设计-页码-点击空白区域。
- 修饰外观：在&[页码]左右两侧添加文字即可
- 添加总页数：设计- 页数
日期和时间：设计-当前日期，当前时间
任意文本：再目标位置输入文字即可
套用预设页眉页脚：设计-选中页眉/页脚的下拉选框-勾选即可
图片：设计-图片-选择一个图片-设置图片格式-宽和高设置整个图片相对于纸张大小的百分比
9.4 水印
新建工作表-插入-艺术字-输入字体并设置想要的形式-倾斜-打开一个word/ppt-复制文字到word/ppt-另存为图片-在Excel中间上方的格子里放入刚刚的图片-设置图片格式，放大宽高（比如都放大200%），图片颜色设为冲蚀（将图片颜色减淡）-在图片上方按Enter，将图片移动至中间
10 函数
10.1 自动加总函数
- 选择填写的框-点击编辑栏的求和按钮-框选需要计算的区域（可以用Ctrl选择不相邻的区域）- Enter
- 修改：点击单元格右边的下拉列表-选择其他函数如平均值
10.2 常用函数
10.2.1 数值查找
- LARGE(array, k):第k大的数据
- SMALL(array, k):第k小的数据
10.2.2 条件判断
- IF:条件判断。只有两种判断结果，可以搭配条件格式（见13筛选）来用颜色填充区别不同的结果
- IFS: 有多个判断结果
10.2.3 数据查询（精确查询和近似查询某个区域）
-
VLOOKUP（被查询值，查询范围，被传回的列数）:"L"型筛选查询序列名在首行，建议在原表格之外另外设置一个副表格，副表格里面写上查询的依据和查询的结果。
函数的参数：
第1个是查询的依据（选择副表格里的，并且该内容位于原表格的第一列）
第2个是整个原表格
（注：对于筛选查询，前两个参数一般要设置绝对参照，见10.3）
第3个是填写的第几列
第4个是否近似匹配（精准匹配最好填FALSE；如果要近似匹配，则原表格的第一列必须是递增的，就会找到最相近的一个结果）
可以用来代替IFS进行逻辑值判断：另外建立一个判断的副表格，利用VLOOKUP并设置绝对参照（见10.3）即可
隐藏表格：可以把原表格选中，右键隐藏 -
HLOOKUP（被查询值，查询范围，被传回的行数）:7字型查询，序列名在左侧
-
XLOOKUP(关键字，关键字所在的栏，传回的栏，错误信息，近似比对，查询顺序)：
近似比对：-1==》小的，1==》大的，0==》精确比对，2==》模糊比对，比如李*==》李小明，利用正则表达式
查询顺序：1：正序，-1：逆序 -
INDEX(行/列范围，顺位)/INDEX(二维范围，行，列): 返回顺位对应的单元格的内容
-
MATCH(要查询的对象，查询的范围，比对方式)：和INDEX相反，查询的范围必须是单行/单列。比对方式：0是精准比对,1是小于（小于查询对象的最大值），-1是大于（大于查询对象的最小值）
-
INDEX和MATCH搭配使用达到VLOOKUP的用途（横/纵向查询）
INDEX(显示的序列，MATCH(关键字，关键字所在的序列))
INDEX(二维表的数据，MATCH找出副表格关键字所在二维表行的位置，MATCH找出要副表格显示的序列名称所在二维表名称列的位置)，配合副表格和下拉选框，实现整个二维表的查询 -
INDIRECT(名称)和下拉选单(用于快速选择内容)：
INDIRECT返回名称对应的所有表格内容
（1） 设定副表格父选项的下拉选单：数据-数据验证-“允许"设为"序列”-“来源"设为原表格的对应内容（原表格的父选项），也可以手动输入文字，用”,"分隔。
（2）将原表格的父选项和子选项的表格设置名称（见17 名称）
（3）设定副表格子选项的下拉选单：数据-数据验证-“允许"设为"序列”-"来源"设为INDIRECT(副表格父选项的单元格) -
FIND(要搜寻的文字，资料来源[，搜寻的起点])：搜寻文字在资料中的位置，不写搜寻起点，则从第一个开始查询，相同的为显示找到的第一个所处位置。写了搜寻的起点，返回的位置还是位于整个资料中的位置。
用途：搭配LEFT/RIGHT/MID来抓取字数，当字数不固定时，根据单元格里的标记性符号，来计算每个单元格的抓取字数。对于多个相同的标记性符号，可以多次套用FIND和LEN, 比如：
羽絨外套-女-M ==》=LEFT(B3,FIND("-",B3)-1)==》羽絨外套*
=RIGHT(B3,LEN(B3)-FIND("-",B3,FIND("-",B3)+1))==> M -
CHOOSE（选项序号，选项1，选项2，…） 选项少的情况，直接用choose，不要用INDEX搭配副表格。返回随机文本的：
=CHOOSE(RANDBETWEEN(1,2),"A卷","B卷")
10.2.4 错误判断
- IFERROR(要检查的公式，显示的错误讯息)
对于VLOOKUP的筛选查询表，可以设置为：
=IF(C3="","",IFERROR(VLOOKUP($C$3,$E$2:$K$12,2,FALSE),"查无此人")))
如果查询依据（C3）是空的，显示值就是空，如果有文字， 就先判断公式是否错误，错误就显示错误信息，没有错误就显示VLOOKUP的查询结果
10.2.5 计数
- COUNT：自动忽略非数字信息，只计算数字信息的计数
- COUNTA：非空白单元格（全部）的计数
- COUNTIF（array，计数内容）:注意文字信息要用""。为了方便，可以将第二个参数引到另一个单元格，可以随意修改
- COUNTIFS（array1, criteria1,array2, criteria2,…）: 多个计数内容criteria的统计。
对于文本= =>“文本criteria”/G2（利用单元格，便于修改）
对于判断语句==>">1000" / “>”&G2(用&串联字符与单元格)/">=2018/8/1"。判断符号可以用来判断日期
10.2.6 时间
- MONTH(array):将日期array提取出月份，便于提取月份信息，对于多出的月份栏，可以将其隐藏，恢复成原表格的外观
- TODAY(): 现在的日期
- NOW(): 现在的日期+时间
- DATEDIF(开始日期，结束日期，计算单位)：计算天数，天"d"/月"m"/年数"y"。
| 单位类型 | 说明 |
|---|---|
| Y | 年数 |
| M | 月数 |
| D | 天数 |
| MD | 天数，忽略月、年 |
| YM | 月数，忽略年、日 |
| YD | 天数，忽略年 |
- NETWORKDAYS(开始日期，结束日期，假日)： 考虑假日并计算天数
- NETWORKDAYS.INTL(开始日期，结束日期，自定周末，假日)
自定周末的参数，图片来源：
10.2.7 计算
- SUMIFS(加总范围，array1, criteria1,array2, criteria2,…)：选择性相加，将符合criteria的行的加总范围相加。
- SUMPRODUCT(array1[, array2]) :将array对应相乘，然后将相乘的结果求和。
求加权平均：=SUMPRODUCT(分数,权重)/SUM(权重)
判断某个内容出现的次数（COUNTIF）：- -将BOOL转化为0/1，SUMPRODUCT(- -(array='xx'))
实现SUMIF：=SUMPRODUCT((criteria1)*(criteria2))
判断后满足条件的相加（SUMIF）再相乘：=SUMPRODUCT((criteria1)*(criteria2)*要乘的array) - LEN(资料位置)：显示文字+空格的字数
- ROUNDUP(数值，位数): 向上进位，位数为0==》整数，1==》小数第一位，-1==》十位数
- LET(变量名1，变量值1，变量名2，变量值2，公式)：变量名不需要加引号
用途1：重复项，定义名称
用途2：多个列有计算公式，利用LET简单合并
=LET(
小計,SUMIF(表1[顧客姓名],J3#,表1[金額]),
運費,IF(小計>=800,0,80),
小計+運費)
- PMT(利率，期数，贷款总金额）:计算每期还款金额，注意期数和利率的单位必须一致
=PMT(年利率/12,期数（月）,-贷款金额), 就能得到正值
10.2.8 排序
- RANK.EQ(比较项，比较范围,[0/1])：用于排名，排名相同，用同一个数表示
0或不写：递减排序；1：递增排序 - RANK.AVG(比较项，比较范围)：用于排名，对于排名相同，用平均数(小数点)表示
10.2.9 提取文字
- LEFT/RIGHT(资料位置，抓取字数)：提取的字数，从左/右提取n个字
- MID(资料位置，开始位置，抓取字数)：从开始位置抓取n个字
- CONCAT(串联的序列):将文字串联起来
也可以用&串联：
=B2&" "&C2&" "&"收"
CHAR(10) :换行
10.2.10 随机数
- RANDBETWEEN(最小值，最大值)：随机返回区间内的一个整数。如果想要随机返回任意文本，可以搭配
INDEX(资料范围,RANDBETWEEN(1,LEN(资料范围))).F9可以重新运算公式 - RAND(): 产生0-1之间的随机数，不会有重复项。
产生数目均匀分配的随机结果：
=CHOOSE(ROUNDUP(RANK.AVG(D3,$D$3:$D$14)/6,0),"A组","B组")
=CHOOSE(ROUNDUP(RANK.AVG将RAND产生的乱序进行排序/数列总数的一半,0),"A组","B组")搭配CHOOSE
RANK.AVG将RAND产生的乱序进行排序取前几。搭配INDEX
=INDEX($C$3:$C$14,RANK(D7,$D$3:$D$14))
将乱序内容复制-选择性粘贴"值"，防止其自动重新计算
10.2.11 逻辑判断
- AND(BOOL1, BOOL2,…) :且。如果要返回的不是TRUE\FALSE，利用IF(AND(…),内容1，内容2)
- OR(BOOL1, BOOL2,…) :或
10.3 设定绝对参照
在用➕进行自动填充函数计算结果时，每下移一格，函数的计算范围也会跟着下移，如果要控制计算范围不移动，可以选中并按Fn+F4
避免使用➕：在自動下移的單元格后加#
10.4 资料验证&下拉选框
数据-数据验证，设置正确的数据判断区间，提示输入信息，错误信息提示对话框
下拉选框见INDIRECT函数
11 冻结窗格&分割视窗
- 选择不需要冻结的第一行-视图-冻结窗格
- 选择第二视窗的第一行-视图-拆分：可以让两个视窗分别滚动，便于比较。
可以拖动分隔栏，分配两个视窗的显示比例，推到最顶端，就可以关闭分割功能
12 排序
- 单列排序：点选需要排序列的任何一个储存格（不需要框选整栏的资料），点击开始-降序/升序，则整个文件会依据选择的那一列进行排序。
- 多栏排序：不需要特定选择某个储存格，点击开始-自定义排序-选择主要关键字的标题栏-选择排序方式-添加条件-选择次要关键字的标题栏-选择排序方式
- 中文排序：笔画数量排序
- 自定义序列（主要用于中文或英文的自定义排序）：开始-自定义排序-选择主要关键字-自定义序列-输入排序的文字。
另外，对于➕的拖动，可以利用这个功能自定义文字，拖动产生目标序列
13 筛选
创建筛选：点击开始-筛选-选择需要筛选的列-取消全选-勾选需要筛选的内容。可以多个多类别筛选
还原单个筛选：目标栏中清除筛选
还原整个文件：点击开始-清除/筛选
模糊筛选（更细致的）：
- 文本筛选：等于、不等于、开始于、结尾于、包含、不包含
- 数字筛选：=，>, >=, <, <=, 介于，前十项(设置最大/小的n项)，高于平均值，低于平均值
- 日期筛选：等于，之前，之后，介于，周，月，季度，年，本年度截至到现在，期间所有日期
- 色彩筛选：先将几个储存格填充为某颜色，再选择“按颜色筛选”
- 色彩排序：选择“按颜色排序”，对于单个颜色–>直接选择颜色图标；对于多个颜色–>自定义排序-关键字设为有颜色格子的序列名-排序依据设位单元格颜色-次序设位目标颜色
切片器（适用于多次筛选的查看）：
- 设计-插入切片器-勾选需要筛选的列-确定-点击想要观看的选项，也可用Ctrl来多选。
- 面板可以拖拽调节大小
- 如果一个面板的选项过多：在选项栏中，将列的值调为2设为多栏显示
- 删除面板：Delete
- 清除筛选器：面板右上角图标
条件格式（不隐藏其他行的筛选/突出显示）：
- 选中需要标记的列 - 开始 - 条件格式-突出显示单元格规则 - 等于 - 点击任意一个需要标记的单元格-（“设置为”中选择一个标记字体颜色or填充颜色，也可以自定义）- 确定
- 对二维表进行按行标红+自定义：突出显示单元格规则-其他规则-设置一个返回BOOL的公式
=OR($ C3=“有”,AND($ E3>1000,$ D3>5))，注意行前面＋$,将行锁定，当单元格右移判断公式条件时，公式单元格的行不会随之变化 - 当后续行增加文字时，会自动变更颜色
- 新增/清除/更改格式：选取栏位-开始-条件格式-管理规则-新建/编辑/删除规则。删除规则还可以用清除规则-清除所选单元格/整个工作表的规则
- 表示数据的大小(单元格内的迷你图)
横向条形图：选取要套用的列-数据条
色阶：利用颜色不同来显示数值的大小，选取要套用的列-色阶。可以在管理规则里面修改颜色or表示方式（比如改成数据条）
图标集：利用图标显示大小。可以利用管理规则，来修改或查看每个图标所代表的数值范围
14 工作表
- 重命名：双击名称即可修改
- 删除：右击后选择删除
- 同一个Excel里的工作表复制：Ctrl并向旁边拖动，即可自动复制一个新的相同的工作表
- 不同Excel工作表的复制/移动：将两个Excel都打开，然后右击想要复制的工作表-移动或复制-选择另一个Excel-勾选建立副本（如果不勾选，则是单纯的移动）
- 工作组：按住Ctrl，点击要编辑的工作表，右击组合，再对任意一个工作表进行编辑，即可对所有工作表进行相同的编辑。
取消：右击任意一个工作表-取消组合工作表 - 隐藏工作表：右击-隐藏。右击-取消隐藏-选择要取消隐藏的工作表
- 多个工作表的合并计算：新建工作表-数据-合并计算（默认使求和，也可以使用其他的函数）-点击“引用位置”-框选第一个工作表内需要计算的单元格-如果有序列标题，就勾选“首行”和“最左列”-点击“添加”，添加到“所有引用位置”-切换到第二个工作表，注意单元格是正确的框选状态-勾选“创建指向源数据的链接”（可以在汇总工作表里看到每一个工作表的具体数据）
15 图表
- 移动：设计-移动图表。就可以把图表放到已有的工作表里面，或者新建一个工作表里
- “添加图表元素”：在图表里添加元素
坐标轴、坐标轴标题、图表标题、数据标签（图上标具体数值），数据表（图像的下方列一个表，适用于表不大的情况），误差线，图例 - 右击图表-选择设置** 格式-就可以设置颜色，文字属性，形状等等
- 数据资料的修改和新增：
修改：直接修改单元格数据，图表会自动修正
新增：修改好单元格数据后-点击图表-将新增的部分框选进去 / 右击图表-框选整个新的数据 - 暂时取消某数据的显示：点击图表右上方的图表筛选器-取消勾选目标数据-应用
- 更改图表设计：点击设计-图表样式，套用Excel预设的图表样式，可以点击样式左边的“更改颜色”，将目标样式进一步换个颜色
- 更改图表类型：右键选择“更改图表类型”，可以转化为折线图，三维形式的柱状图等其他类型
- 切换横坐标和纵坐标：设计-切换行/列
- 条形图：更改其样式，可以选择“图片或纹理填充”，选择一个图片来作为条形，对于形变不好看的图片，可以勾选“层叠”，或者勾选“层叠并缩放”，并指定“单位/图片”==》表示每n格纵坐标的数值，放置一张图片
- 饼图：将某一块突出显示，双击那一块然后拖拽即可，然后设置阴影等效果
- 迷你图：框选需要显示迷你图的区域-插入-折线/柱形/盈亏-框选索要绘制的数据区域。
可以设置高点、首点、低点、尾点、负点、标记（所有的数据点都打点），并为这些点设计颜色样式
清除：设计里的橡皮擦图标 - 散点图： 可以添加趋势线-观察走势
- 雷达图
- 组合图表：用于有两种不同属性样本的绘制，通常两个样本数值差异大或者单位不一致，有两个y坐标轴
- 甘特图:
建立：插入堆积条形图-依此在"选择数据"中加入堆积的数据-将开始（第一个）数据设为无填充
调节坐标轴的边界和单位，使图像填充到整个图形
将坐标轴的顺序反过来：坐标轴选项：逆序类别
条形图上再画另一个数据的条形图：第一个隐藏条形图的误差线-更多选项-正偏差-无线端-误差量设位自定义，指定为需要绘制的另一个数据-将误差线的宽度设置和底部条形图宽度一致
坐标轴的线条设置：只有靠近表格的一侧直线
16 数据透视表（筛选、排序、计算、绘图）
- 选择任意一个单元格 - 插入-数据透视表，会自动定位所有表格。勾选序列名称，属于文字（统计某个文字的元素次数：将该元素拖移到行和值。当进行多个文字统计时，只需要将行里的元素替换到即可，值只是代表了显示的方式）的= =》“行”；属于数值的==》“值”，且会自动求和
- 序列列表、筛选、列、行和值直接是可以随意拖拽的
- 排序：右击数值，可以排序
- 筛选：
（1）点击“行标签”的下拉选项，可以进行筛选；从xx中清除筛选；
（2）将要筛选的的目标序列名拖拽到“筛选”选框中，就会展示该序列中对应的“行”和“值”（一维），需要选择展示目标序列中的哪一个元素，并自动求和
（3）将要筛选的的目标序列名拖拽到“列”选框中，就会直接展示该序列的所有元素对应的所有“行”和“值”（二维），并自动求和。如果对这个二维表中任意一个单元格感兴趣：双击该单元格，就会自动显示所有与该单元格有关的单元格 - 修改序列名称、值显示方式、计算类型（如求和，计数，平均值，极值等）：同一个序列可以出现两次，比如对于数据的统计，一列为原数据，再将另一列设置为百分比：点击目标右侧的下拉选项-值字段设置
- 自定义群组：
（1）按Ctrl并选择所有目标单元格-右键组合-在资料编辑框中修改群组名称
（2）取消群组：右击群组名-“取消群组”
（3）日期的设置：添加进日期的序列，会自动产生“月”的序列，右击任意一月的单元格-组合，在步长和起始时间里设置群组的时间段组合，比如步长可以设置为“季度”，就可以显示每个季度的群组；或者将步长设置为“日”，再设置天数步长，就可以按天数设置群组。
在不勾选日期序列的情况下，用日程表：分析-插入日程表-点击要显示的时间/拖拽滑杆控制其长度/右上角切换时间单位（年，月，季度，日），就可以看到对应“行”和“值”的数据。按Delete即可删除日程表 - 图表显示： 分析-数据透视图
- 刷新： 表格中有数据更改/新增（要将新内容框选进来）时，在分析-刷新，才能在透视图中用新的资料。或者先将表格设置为动态表格（见 4 表格样式）
- 配合切片器：实现了筛选功能，可以查看多个条件下的某类型数据（一维/二维）
17 名称（数列变量名）
定义：
- 选中所有单元格（注意不要把第一行的名称选进去）-公式-定义名称
- 选中所有单元格，在左上角直接输入要定义的名字-Enter
再利用公式时，可以"=SUM(名称)" - 选中所有单元格（把名称也选进去）- 公式-根据所选内容创建-如果名称在最左端就选"最左列"，还有"首行"等。
修改/删除:
公式-名称管理器-编辑/删除
18 技巧
18.1 数据分列
选中所有单元格-数据-分列。
固定宽度的分隔，可以直接拖动分隔滑杆来分隔
18.2 插入多行/列
选取要插入的列数，右键插入即可
18.3 单元格的快速复制和移动
移动：
框选要复制/移动的单元格-拖动边框到目标位置
按住Shift拖动：原来的单元格会被自动删除
复制：
按住Ctrl拖动：在目标新位置复制一份
18.4 重复资料
标红：再新的一栏用%将每一行的单元格内容串联-选中原资料-条件格式-新建规则-“使用公式确定要设置格式的单元格”-输入公式"=COUNTIF(新建的串联内容,串联内容的第一个单元格并取消锁定)>1"-设置标红的格式-隐藏串联内容
删除：选中所有单元格-数据-删除重复值-勾选要删除的关键字
防止输入重复内容：选中判断的序列所在整行/列-数据-资料验证-允许设为自定义-公式设为=COUNTIF(A:A,A1)=1
18.5 转置
复制单元格-选择性粘贴-转置
18.6 快速对列表加减乘除
在任意空白单元格写上要加减乘除的数值-复制该数值-选中列表-点击"选择性粘贴"-加/减/乘/除
18.7 显示公式
公式-显示公式 / Ctrl+~
18.8 对角线的绘制
- 边框-绘制边框-拖移绘制一条对角线
- 右键单元格-设置单元格格式-点击对角线
18.9 批注/注解
右键单元格-插入批注
添加图片注解：删除作者名字-右键批注的边框选中"设置批注格式"-颜色与线条-颜色-填充效果-图片
批注边框可以拖动调整其大小
19 保护工作表
不需要锁定修改的单元格：右击-设置单元格格式-保护-取消锁定，利用Tab键就可以直接跳到下一个未锁定的单元格
隐藏公式：将有公式的单元格框选-右击-设置单元格格格式-保护-勾选隐藏
隐藏私密的单元格/工作表:选中后右键隐藏
设定特殊区域让特殊的人可以操作修改：框选后点击"审阅-允许编辑区域"，可以设置多个编辑区域，修改和删除，为该区域任取一个名字，然后设置密码1-确定后点击"保护工作表"，设置解除锁定的密码2
最后一步锁定防止他人操作：
审阅-保护工作表-设定密码-设置锁定的操作。无法取消隐藏单元格
审阅-保护工作簿-设定密码：无法新增/删除/取消隐藏工作表
防止陌生人打开/修改文件；
（1）文件-信息-保护工作簿
（2）保存-工具-一般选项-设定保护密码
20 进度追踪表
创建✔：在开始工具栏的空白处右键-自定义功能区-勾选开发工具-开发工具的插入-点击✔-拖动它到单元格正中的位置-用➕复制到其他单元格
设置与✔同步的TRUE/FALSE：右击✔-设置控件格式-控制-单元格连接里填上TRUE/FALSE的单元格,这里的TRUE和FALSE不需要引号
设置与TRUE/FALSE同步的小图标：=IF(I3=TRUE,1,0)，在开始-条件格式-图表集里选一个大致符合的，再通过管理规则进一步调整其值范围和具体图标，勾选"仅显示图标"
当有三个判断结果可以套用IF，注意图标可以用Win+；，且必须用引号引起来
=IF(I3=TRUE,1,IF($C$3>G3,0,"🕒"))
完成进度的圆环图：
（1）建立单元格，里面的数据是利用公式根据原表格计算的，便于数据修改后，圆环图随之改变。
（2）插入一个圆环图，并修改标题和外观
（3）绘制圆环图的刻度：设计-选择数据-添加-系列值设为{1,1,1,1,1,1,1,1,1,1}==>绘制一个十等分的圆环图
将十等分的圆环图设置为灰色（该颜色将会被透出）的填充
为了使两个圆环图互相重叠，设计-更改图表类型-勾选覆盖在上的圆环图的"次坐标轴"
将要显示刻度的地区，填充设为透明
（4）插入文本框，里面的内容指向单元格的数据
（5）再稍微修饰外观就好
21 布尔逻辑
有判断符>、>=、<、<=、<>(不等于)、=的结果。
22 爬取数据
爬取：数据-自网站-粘贴上网页连接
刷新数据：
- 手动：数据-全部刷新
- 自动：设计-刷新-连接属性-设定刷新频率和打开文件时自动刷新
爬取数据同时进行整理(如制作图表)：数据-自网站-数据
23 三维地图
Alt+拖动鼠标：变更视角
鼠标靠近数据点：查看具体数值
添加批注：右击数据点
调整颜色、宽度和高度：图层选项
调整方柱形状：上方工具栏的"形状"
调整地图的样式
设置动画：场景选项-调整效果和持续时间-创建视频
调整好后，点击"截捕屏幕"
24 问卷&Power query&整合数据Power pivot
1. 网络问卷：www.forms.office.com
2. Power query:
- 数据-来自表格/区域
- 对于相同类型的数据，要实现相同的公式：转换-逆透视列
合并表格：需要转换成表格才能用，并为每一个表格命名
- 不同工作簿，表格格式全部相同：数据-获取数据-从其他来源-空白查询
输入Excel.CurrentWorkbook(),筛选出所有需要合并的表格，点击Content右边的展开按钮
取消勾选“使用原始资料行名称作为前置词” - 不同工作簿，表格内容部分相同（每个元素的个数可以不同），横向的栏位扩展 ：
对于被合并的工作表：数据-来自表格/区域-关闭并载入至-只建立连线
对于合并的工作表（如果是Power query得到的，说明它们处于连线状态，就不需要建立连线）：数据-获取数据-合并查询-合并-勾选要合并的工作表和相同的栏位-点击展开按钮-选择要扩展的其他栏位 - 不同Excel表格之间的合并：
获取数据-来自文件-从文件夹-选择文件夹-转换数据-将不需要的列删除（比如文件后缀名，修改时间等）-添加列-自定义列-在自定义列公式中输入“=Excel.Workbook([Content])”-删除Content列
对于日期数据：主页-数据类型-日期
当原始分表格有数据变动，只需在汇总表右键刷新即可
3. Power pivot:
当多个工作表里面拥有相同的列数据，就可以整合工作表：添加到数据模型-关系图视图-将相同的列用鼠标拖移连接起来（不可以多对多）
25 宏(录制操作步骤为快捷键)
制作：开发工具-录制宏-命名（英文不可有空格，用_连接）-操作-停止录制-保存为“启用宏的工作簿”
运用:开发工具-宏。到一个目标（另一个）工作表时，会锁定在A1，记录每一个单元格都是利用它的坐标，如果表格是不断添加修改的，就要点击“使用相对引用”
固定图片位置不动： 右击-大小和属性-属性-不随单元格改变位置和大小
制作与宏连接的按钮：画一个按钮，右键连接宏
26 模拟分析
- 已知函数和运算结果，反算运算变量
数据-模拟分析-单变量求解。目标单元格：运算结果的单元格；目标值：运算结果；可变单元格：运算变量 - 反算一个列表
数据-模拟分析-模拟运算表：
- 如果是一个列：表格第一列是运算结果，第二列是空白变量，第一行第二列是公式，第一行第一列是空，就在“输入引用列的单元格”中指定第一行第二列中的运算结果（与表格第一列一致）
- 如果是一个二维表：选中整个二维表，表格第一行第一列是公式，第一行和第一列是运算结果/变量，在“输入引用行/列的单元格”中指定对应的公式运算结果/变量
- 对于公式的那个单元格，可以设置单元格格式-自定义-类型中修改为任意中文，或者输入;;;，让该栏显示空白
- 如果表格较大，公式-计算选项-除模拟运算表外，自动重算
- 对比不同方案的情况，即记录和修改单元格内可能出现的值并进行比对
先将可变单元格命名-数据-模拟分析-方案管理器-选中目标单元格。双击每一个方案时，单元格内的值会自动修改，所以，依据这些单元格而设置的公式计算结果也会更改。
方案管理器-摘要，就会将这些单元格的值列在一个表格里，便于查看 - 数据-分析-规划求解:线形规划求解变量为多少时，结果可以取最大/最小/目标值，可以添加约束条件
2479
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
