第一课 excel快速入门
1.1操作界面
文件保存
保存为xlxs，则是新版本，为了让使用旧版本的同事也能兼容，可以将文件保存为xls。——文件选项卡->选项->保存，再将默认保存文件的类型改为xls即可。
恰好显示选定区域
选定想要显示的单元格区域，然后点击右下角的显示百分比数字->点击“恰好显示选定区域”
最近使用的表格记录
选项->“高级”里面的“显示”->”最近显示的工作簿“改为0.
保存的设置
选项->“保存”->默认保存时间间隔和保存的位置。
选项，这个按钮很重要，很多设置都在里面的
选项->“快速访问工具栏”
可以将一些不在选项卡中的工具添加至左上角的“快速访问工具栏”中，比如“添加一个计算器”
选项->“自定义功能区”
可以将不在选项卡里的工具添加至选项卡中，比如“添加一个电子邮件”
表格编辑
- 双击单元格。
- 点击单元格后，按“F2”。
-
制作表头
二级表头可以点“开始”->“边框”->“其他边框”，画一个斜线。三级表头则需要自己在“插入”->“形状”，选择直线自己画。
1.2单元格操作
选择连续区域
选定第一个单元格，然后1.按着鼠标拖动。2.按住“shift”键，再去选定最后一个单元格
选择不连续区域
选定第一个单元格，1.然后按住“ctrl”键，就可以选择不连续的区域了。2.按下键盘的“shift”+F8。这样就不用按“ctrl”也可以选定非连续区域，再按esc可以退出这个模式
“shift”+“ctrl”+→↑←↓
选定某个单元格，然后按“shift”+“ctrl”+→↑←↓，可以选定该单元格上下左右的所有单元格，按两次这样的操作，“右”+“下”，这样可以把该单元格的下面所有列都选中，不需要手动拖。
移动单元格
鼠标左键单击某个或某一些单元格，然后将鼠标移动至选定单元格的边沿处，等光标变成“四角箭头”，按住鼠标左键并移动。
插入单元格+shift
在上面操作的基础上，按住“shift”即可，可以插入至某列或某单元格之间。
复制单元格(快捷键）
复制单元格
选定好要复制的单元格区域，ctrl+C，然后选择一个空白的单元格再按ctrl+V。
剪切单元格
选择连续行 / 列
点击单元格外围的A、B、C、D或1/2/3/4就能选择某行或列了，然后配合shift和ctrl键，选择连续或不连续
选择不连续行 / 列
列宽和行高调整
选择某一行或者某一列，然后将光标移至列的边缘，然后拖动或者双击。“双击”是自动调整合适的宽度
隐藏某几列 / 行
取消隐藏
自己隐藏了某几列单元格后，如非必要应该取消隐藏，拿到别人的工作簿，要记得先取消隐藏。点击左上角的“小三角”全选工作表，然后点击“开始”，“格式”，“可见性”，取消隐藏行和列各做一次即可。
插入列或行
选定单元格或列后，1.右键，“插入”，可以插入。2.快捷键，shift+ctrl+“=”
删除列或行
插入复制/剪切的单元格
先进行复制/剪切的操作，然后选择要插入的单元格那里，右键，“插入复制的单元格”
1.3工作表操作
重命名工作表
复制、移动工作表
右键点击工作表，“复制”或者“移动”工作表，“移动”工作表勾选“添加副本”即可新建一个复制样本并移动。
移动工作表至其他工作簿
同上，选择移至其他工作簿这样就会新建一个以该工作表为基础的新工作簿
选定全部工作表
右键点击工作表，点击“选定全部工作簿”，这样可以同时对不同工作表的相同位置的单元格进行编辑。比如，“将A2单元格输入123，全部的工作表的A2单元格将变为123.”
保护工作表
保护工作簿
保护文件
允许用户编辑部分区域
点击“审阅”，点击“允许用户编辑部分区域”，这样在保护工作表的状态下就可以只让用户编辑部分区域。
第二课excel常用功能
2.1序列与填充
直接填充
1.光标变为“细十字架”，然后拖动。2.双击“细十字架”。eg.除了数字，还可以填充如“AA001”的数列。
选项卡的填充
点击“开始”，“填充”，里面有序列填充，可以设置步长产生很多序列，比如1,3,5,7奇数序列。还有智能填充。
001的输入
直接在单元格里只输入001的话，前面的0会被默认清除掉，在最前面加上一个单引号可以使得001得以存在
日期型数据的填充
智能填充
填充“城市”等非数字的序列，可以在“文件”、“选项”、“高级”，找到 “创建用于排序和填充的列表”，点击”自定义列表“，然后自己导入想要的序列。
向上/下/左/右填充
在“开始”，“填充”里可以找到向上下左右填充，也可以用快捷键，向右填充ctrl+”R”，向下填充ctrl+“D”
公式填充
单元格里是公式的话，可以进行填充，公式一般是等号开头的。行/列变化的话，若公式中引用了单元格的话，那么单元格行/列也会跟着变化，可以按”F4”来锁定单元格的变化，也可以自己加“$”符号进去，让单元格不变。
拆分后的单元格填充
本来拆分后的单元格全部都应该是那一个相同的数据，但是只有第一个有数据，可以拆分后，按ctrl+”G“或“F5”，定位空白值，再令其等于有数据的第一个单元格即可。最后要按ctrl+“回车”。
任意空白单元格的批量填充
先ctrl+”G”，定位空白处的单元格，然后对他们编辑或者改变底纹颜色，最后ctrl+“回车”
2.2排序与筛选
升序/降序排序
“开始”，“排序”，选择升序或降序排序即可，点击后，有两种。“扩展选定区域”和“以当前选定区域”。
日期的规范
如果日期等不是统一的格式，（需要先将数据的格式全部转换为”日期“，可以通过“数据”选项卡，“分列”然后点击“默认”两次，选择为“日期”格式，然后回车。这样就可以把不同的日期格式转换为相同格式。比如将2021/1/7和2021.1.7转换为相同的日期格式，然后才可以进行排序。
单元格的绿色小三角
有些数字的单元格左上方有绿色小三角，表面该单元格不是数字型而是文本型。先“排序”一遍，然后绿色三角的单元格就会出现在一起，然后将他们全部变成数字型单元格，再进行一遍排序。
多列排序
点击“开始“，”排序和筛选“，”自定义排序“。ps：里面还有按笔画排序哦。
多列排序+自定义序列
在上面的基础上，再选择”自定义序列“。然后就可以将自己的想要的序列作为排序的依据了，自定义序列的作用不仅仅是填充哦。
自动筛选
点”开始“，”排序和筛选“的”筛选“，重新点击筛选可以取消”筛选“状态。除了可以筛选数字和文本，还可以筛选单元格的格式如底纹等，以及”大于60的数据“。
”重新应用“
筛选里的“重新应用”，是筛选后的数据又发生了更改，但是筛选不会自动更新，需要人为手动点击“重新应用“。
2.3查找和选择
查找
区分大小写
有时，查找出的数据不分大小写，可以在”查找“的基础上，点击”选项“，打开”高级查找对话框“，勾选上”区分大小写“。
单元格匹配
”查找“的基础上，勾选”单元格匹配“是精确匹配，不勾选则是模糊匹配。eg：”如果查找‘鼠标’，’有线鼠标‘则不会被查找出来。这叫精确匹配。”
格式查找
通配符‘*’和‘？’
通配符‘*’代表任意个字符长度的值，’?’代表一个长度的字符。查找时可以利用通配符来查找。
定位
按ctrl+”G”或者在“开始”，”查找“->“定位”。“定位”和“查找”的区别是，查找是找到有哪些单元格包含这些数据，”定位“则是精确匹配，而且可以定位到单元格，并可以对它们进行编辑。
在每行插入一个新的行
要先选择两列空白列，然后第一列输入“1’，在第二列的对角位置输入”1“，然后下拉填充，ctrl+”G”调用定位功能，选择“数值”并确定，然后右键，“插入行”
保留模板公式
单元格里的数字有些是“数值”有些是“公式”，可以全选工作表，然后“定位”，“常量”，随便选择一个选择上的单元格，点击鼠标右键，点击“清除内容”。最后只有公式被保留下来了。
定位出公式的错误值
选中所需单元格区域，然后”定位“，选择“公式”，只勾选“错误值”。
定位空值
行内容差异单元格
这个可以找出不同的行有差异的单元格。利用这个特点可以在最下面各插入一个空行。先插入一个空行，复制前面那一行再错位粘贴到空行，定位行内容差异单元格。最后添加新行。
复制筛选后的单元格区域
2.4其他常用功能
插入图片
直接复制图片，然后粘贴。
- 点击“插入”，“图片”.
- 批量插入图片，粘贴代码
其中的参数有文件位置和照片宽度以及高度
插入网页超链接
点击“插入”选项卡，“插入超链接”，插入文件超链接
点击“插入”选项卡，“插入超链接”，插入特殊字符
点击“插入”选项卡，“插入“特殊符号”自动换行
点击“开始”，点击“自动换行。强制换行
在单元格里要输入回车，按alt+”回车“。只显示单元格宽度的部分，不往后延伸。
选中该单元格，单击鼠标右键，或按ctrl+”1”，调用”单元格格式“，”水平对齐方式选择填充“，点击”确定“。自适应单元格宽度
选中该单元格，ctrl+“1”，水平对齐选择“靠左”，勾选“缩小字体填充”。批注
在保护工作表的状态下，我们可以利用批注，来写下我们的想法，而不用编辑工作表。点击“审阅”，点击“新建批注”第三课excel高级功能
3.1数据分列
分列
“数据”，“分列”，选择“固定宽度”（如果要分列的单元格是有下划线等的，可以”分割线“），点击可以划线，然后再将每段依次选择是否导入以及导入区域。3.2快速填充
快速填充
快速填充，就是根据原有的单元格列，自己在另一行再写一个自己想要的结果，然后双击“细十字”填充，再点击”快速填充”。这个相当于是对“函数”的补充，因为有时我们对“函数”的运用是不够的，或者说我们用函数来解决问题是十分复杂且麻烦的。这个很好地解决“常规”填充的不足，这个可以叫它”傻瓜填充“。3.3删除重复项
删除重复项
点击”数据“，”删除重复项“3.4数据校验
数据验证
“数据”，“数据验证”，这个功能是让其他人在填你做的表时，你可以让某列只能填11位长度的手机号，又或者只能是2的倍数，或者是汉字。“输入验证”中的“来源”是你选定的可以填写进该单元格的数据，“比如，性别栏你设置为只可以填写‘男’或‘女’”。还有“弹出消息以提醒我们应该填什么数据的功能”。序列的数据验证
同上，还是“数据验证”选项卡，在里面勾选“序列”可以多列联动，比如，选择了”男“，则身份证号的倒数第二位应该是奇数，所以可以分类去检验单元格填写是否正确。3.5合并计算
合并计算
“数据”、“合并计算”。这个是普通的“计算”如“求和”等的进阶版，可以直接帮你找出你要计算的数据，然后计算它们。“例如，表格里面有‘销售日期’和‘销售员’、‘销售金额’三列。要计算出某天的总销售额或某人的总销售额，就可以直接用这个功能，直接帮你计算出全部的数据。”数据比对-引申用法
先将数据进行“合并计算“，然后数据就会到一起去，这样再利用“行内容差异单元格”这一功能就可以进行数据的比对了。3.6分级显示
分级显示-数据的快速隐藏和展开
“数据“，”创建组“和”取消组合“。我们可以利用该功能来进行数据的快速隐藏和展开。手动去逐列逐列地隐藏和展开是不方便的，所以可以用这个功能来创建3.7分类汇总
分类汇总
“数据”，“分类汇总”。可以自动汇总所要数据，而无需手动添加公式。3.8选择性粘贴
选择性粘贴
这个功能是“粘贴”的扩展，除了可以粘贴“数值”、“公式”还可以粘贴“批注”等。还能将复制的数据进行运算后在粘贴。很有用的功能。跳过空值（粘贴）
这个”选择性粘贴“可以实现不同列数据的合并转置粘贴
可以将列和行的数据转置一下，本来是竖着的可以将其变为横着的。3.9高级筛选
高级筛选
“数据”、“高级”。是普通的筛选的进阶版，普通筛选只能在一列上进行筛选，和简单的数值比较，符合筛选条件的结果会显示，其他则隐藏。这个高级筛选可将需要的数据直接在新的地方粘贴过去去除重复项
同上。这个功能可以将重复项只保留一次，并提取到其他地方。第四课 函数
如何学习excel中的函数呢？只要你学会了常用、关键函数的用法，理解背后的原理，善用系统帮助，即使在工作中遇到了新的函数，学习起来也是非常方便的。4.1快速入门函数
函数使用
点击“公式”，里面有很多函数分类，比如说”求和“、”财务“、”文本”、“逻辑”、“日期和时间”。函数举例
例如，我们要输入求和函数sum（）。可以在“公式”选项卡中找到该函数，也可以自己手动输入“=sum（）”。在输入的过程中，会有“参数”的注释弹出来，告诉你这个函数有几个参数，功能分别是哪些，参数之间应该用英文的逗号隔开。最开始的等号说明这是一个公式，最开始都要有等号。插入函数
“公式”、“插入函数”。如果你不知道要用什么函数，你可以在那里面搜索。输入你想要实现的功能，例如”求和“，就会出现很多与“求和”相关的函数。不知道某个函数的功能可以点击“帮助”参数
输入函数时出现的注释，如果参数是可选的，注释里会有一个括号，这样你在输入该函数时，它的参数长度就是可变的。单元格引用
单元格引用是函数参数输入里面的非常重要的一环。有三种引用格式，“相对引用”，“绝对引用”、“混合引用”。也就是在引用单元格时在行和列前面是不是加了“$”符号，没加就是相对引用，行和列都加了就是”绝对引用“，只加了一个是”混合引用“。输入公式是在一个单元格输入的，输入好之后还需要下拉填充或者是右拉填充，这样才能对一列数据进行公式的运用。在填充时，加了‘$’后该单元格引用就不会随着填充而发生数值改变。例如，”A1“和”A$1”这两种引用，后面一个行号‘1’就不会随着填充而发生改变。前者’A1’则会改变。向下填充行号会发生改变，向右填充列号会发生改变。加’$’符号
按键盘的“F4”键。用英文符号
公式里面一定要用英文的符号，虽然excel会帮你转换逗号和括号，但务必养成习惯，因为其他的符号不会帮你转换。日期的比较
日期作为一个数值来进行比较时，和其他文本或数字不一样，正确的写法是在数值前面加上两个英文横线‘—’，如{—”2021/1/1“ }。第二种方法是，使用date（）函数。如date（2021,1,1）。4.2文本函数
格式整理
说明：以下函数均为格式整理的函数。有时我们为了美观会加一些“强制换行”、“空格”等，或者从其他数据库导出数据的话，会有一些非打印字符包含在数据里。如果直接使用这些数据进行处理会影响准确性甚至难以计算清除非打印字符
clean()函数
功能：删除文本中所有的非打印字符。参数：文本。清除前后的空格
trim()函数
功能：删除文本中的字符串前后的空格。参数：文本。替换
substitute()函数
功能：替换。参数：文本，被替换值，新的值。
说明：公式都可以嵌套大写转换
upper()函数
功能：将所有的字母转换为大写。参数：字母文本。小写转换
lower()函数
功能：将所有的字母转换为小写写。参数：字母文本。首字母大写其他小写
proper()函数
功能：将所有单词的首字母转换为大写。参数：字母文本。格式转换
text()函数
功能：将数值、数字转换为特定数字格式表示的文本。参数：选定的数字单元格，待显示的格式。例：{=text（A2，“0000-00-00”）}其中A2单元格是”20210213“，这样就可将其转换为“2021-02-13:”的文本了。
text函数将数据变为文本后，无法参与计算，可以在text()前面加两个英文横线“—”，或者在text函数后面加一个乘1, “*1”。然后这时得到的数据就是数字了，点击“开始”选项卡，将格式改为日期就可以了。
用text()函数将日期变为星期几。=text(A2,“aaaa”)。若要英文的星期几，将“aaaa”改为“dddd”。
将数据转换为带单位的文本形式。比如将“3500”转换为“3500.00元”，可以这样写：{=text(3500,”0.00元“)}。其他的功能自己以后在百度百科上查一下就行了或者看“帮助”。数据替换
replace()函数
功能：替换文本里面从左开始第几位，替换几个字符。参数：4个。substitute()函数
功能：替换掉文本中特定的字符或字符串。参数：3或4个。数据提取
left()函数
功能：提取字符串中从左边开始数的几个字符。mid()函数
功能：提取中间几个字符right()函数
功能：提取从右边开始数的几个字符。说明：这三个函数的提取长度参数可以大于实际的字符串长度，提取结果仍然可以满足要求。例如：提取“广场店robin”后面的robin，可以写提取10个字符，虽然robin只有5个字符。
说明：因为这些函数都是提取固定长度、固定位置的字符，当长度不定的数据或者是要提取的数据在文本中位置不确定的时候，这是提取就会很困难。所以我们用find()函数搭配上面三个函数来提取数据。find()函数
功能：找到文本中的某个字符，并返回该文本在字符串中的位置，是第几位。参数：3个。search()函数
功能：和find()差不多。find函数不支持1.英文大小写。2.通配符‘*’和‘？’。而search函数是支持的。
说明：将find()函数找到的位置也就是它的返回值，作为left，mid、right（）位置参数，这样就可以提取出想要的数据了。数据长度
len()函数
得到数据的字符长度，这个函数可以搭配上面所有函数使用，这样就可以很方便、准确地提取到我们想要的数据。lenb()函数
得到数据的字节长度，也可以搭配上面所有函数。因为中文字符占两个字节，英文字符占一个字节，所以这两个函数搭配使用可以得到文本中中英文字符各占多少。从而更好的提取数据。数据连接
‘&‘ 功能：连接文本或单元格里的文本。例如：“=A2&”：“&B2”。其中的冒号作为文本一定要用双引号括起来。concatenate()函数
功能：和’&‘相同。例如：“=concatenate(A2,”:”,B2)”。这个函数和’&‘符号用于数量不多的单元格连接，不能用于单元格区域（一整列等）的连接。而要用phonetic()函数。phonetic()函数
功能：连接连续的单元格区域。参数：“A2:A7”这种。不能连接非连续的单元格，数字、日期、时间和公式生成的值。只能连接文本和文本型数字concat()函数
功能：这个函数是concatenate和phonetic的合成版，既能连接非连续区域也可以连接单元格区域，用这个函数可以概括其他连接函数了textjoin()函数
功能：在连接单元格的基础上还可以在每个单元格之间加入分隔符。这是其他函数没有的功能。参数：3或4个。判断相同
exact()函数
功能：判断两个单元格或字符串是否完全相同（区分大小写）。char()函数
功能：用于返回数字对应的代码，比如10对应的是强制换行，65~93对应的是A~Z。相当于C语言里面的ASCII码。强制换行符生效必须将该单元格的格式调为“自动换行”。row()函数
功能：返回当前的行号column()函数
功能：返回当前的列号4.3逻辑函数
判断函数
if()函数
功能：判断条件是否为真。如：=if（A2>=60, “合格”,”不合格”).如果条件为真则返回前面的”合格“，否则”不合格“。多个条件之间如果是”且“的关系，则用乘号”*“连接，如果是“或”的关系，则用“+”加号连接。ifs()函数
功能：这是分步判断，在if函数的基础上加了分步判断的功能。可以解决if函数的多重嵌套问题。逻辑判断函数
and()函数
功能: 逻辑值的”且“，可以代替上面所说的“*”。or()函数
功能: 逻辑值的”或“，可以代替上面所说的“+”。函数公式应注意的细节
1.函数、公式中使用的符号必须是英文符号。
2.文本型数字无法参与数学运算。
3.公式中和日期的比较应该先把比较值转换为数值再进行比较。
4.写函数时要理清需求和逻辑，如果使用到多个函数多个步骤，要使用函数嵌套的话，可以增加辅助列把分步结果写出来，再把公式合并起来，得出最后的结果。第五课 函数进阶
5.1查找引用函数
查找
vlookup()函数
查找就用这个函数就好了，这个简单点。学会这个就差不多可以解决工作中所遇到的所有查找问题了。四个参数依次是，“要查找的文本/单元格”，“查找的单元格区域”，“查找的单元格在第几列”，“精确匹配or近似匹配”，自己看看这个函数在excel里的帮助就差不多能学会怎么使用这个函数了
注意：1.查找的依据列必须是查找区域的首列。2.若要查找的列是在待返回的列的后面，这样可以自己再增加一个辅助列或者用if()函数的一个数组公式来实现它们的重组。如：“=vlookup(B3, IF({1,0}, C:C, A:A),2,0)。2.第二个参数，查找的单元格区域一般要加”$”符号，如果向下填充的时候行号就会发生变化，导致查找区域实际上变窄了，从而导致结果出错。不加$符号，将单元格区域以“A:C”这种列的形式也可以避免错误发生。lookup()函数
这个函数似乎比vlookup还要简单！这个函数有两个版本，一个是三个参数的：“要查找的文本/单元格”，“查找的单元格向量（只能是一行或者一列的区域，不能同时多行多列）”，“返回的单元格向量”。但是这个函数查找值的话，需要你先把列变为升序排列。不然会出错。hlookup()函数
和vlookup的区别在于，hlookup是按行查找，vlookup是按列查找index()+match()函数
先用match()函数找到它在哪一行，哪一列，然后用index()函数把那个位置上的值找出来。index()函数
功能：返回特定行列交叉处单元格的值或引用。match()函数
功能：用于返回特定值、特定顺序的项在数组中的相对位置。是index()函数的逆运算。引用
indirect()函数
返回单元格的间接引用。也就是编程里面的间接取址。关键是这个函数用于以下两个功能，1.创建下拉菜单。2.汇总多个工作表。n()函数
返回单元格的值。如果是文本型数据则返回0，这个函数可以用在一列单元格既有数字又有文本的情况下，将文本都变为0，就可以参与运算了。5.2日期时间函数
说明：日期也是数字，所以也可以参与计算，在excel里面，1900/1/1是起始日期，它对应的数值是1.然后过了一天，日期对应的数值就会加1。一天的时间对应的是1，所以6点钟就是0.25,12点是0.5,18点是1。日期标准格式是用“/”分隔的，而不是用逗号“，”。时间直接乘以24，可以得到小时数，再乘以60，可以得到分钟数，再乘以60可以得到秒数。日期时间
当前日期和时间输入 当前日期输入：“ctrl”+ “; “或者用today()函数。当前时间输入：“ctrl” + “shift” + “; “或者用now()函数-today()函数。两者的区别是通过按键盘的快捷键输入的日期和时间是不会随着时间的变化而变化的，但是公式的输入方式是会随着时间更新的。year()函数
输入日期或者日期对应的序号，就可以得到这个日期对应的年份month()函数
输入日期或者日期对应的序号，就可以得到这个日期对应的月份day()函数
输入日期或者日期对应的序号，就可以得到这个日期对应的号数hour()函数
输入时间或时间对应的小数，就可以得到这个日期对应的小时数minute()函数
输入时间或时间对应的小数，就可以得到这个日期对应的分钟数second()函数
输入时间或时间对应的小数，就可以得到这个日期对应的秒数date()函数
合并年月日得到日期time()函数
合并时分秒得到时间weekday()函数
得到日期对应的星期几weeknum()函数
算出现在是今年的第几周text()函数
之前提到过这个函数，现在再提一遍是为了学习它在日期和时间上的应用。
提取日期：=TEXT(TODAY(),”yyyy-mm-dd”)。
提取时间：=TEXT(NOW(),”hh:mm:ss”)。
有星期几的日期：=TEXT(TODAY(),”yyyy-mm-dd aaaa”)。日期运算
日期也是数字，只是当你设置为日期格式时，他显示为日期，1900/1/1对应的数字就是‘1’，日期也可以直接相加减，日期也可以和数字相加减。如：2021/1/25+1就会变为2021/1/26时间运算
同上。若想得到超过24小时的时间显示，比如32个小时显示为32:00:00，可以这样写=text(now()+1,”[h]:mm:ss”)datedif()函数
两个日期相减的函数，返回年、月或日数。networkdays()函数
返回两个日期之间的工作日数，还可以减去你想添加的节假日（非周末），最后得到工作日数。workday()函数
计算起始日期之前或者之后相隔指定个数的工作日的某一个日期，常用于计算到期日，预计交货日期或者制定项目计划等场景。edate()函数
计算出之前或者之后指定月份的日期，多用于合同到期日的计算。eomonth()函数
计算出之前或者之后指定月份的该月的最后一天的日期，多用于提醒合约到期的那个月的上一个月的最后一天。还可以利用这个函数计算这个月还剩多少天和上个月的最后一天是哪天。=eomonth(today(),0)-today()计算剩几天。=EOMONTH(TODAY(),-1)计算上个月的最后一天是哪一天。5.3数学函数
加减乘除
四则运算可以通过符号如“+”或者函数的形式来实现。例如：=A2+B2或=A2/B2。函数实现的话，相加是sum()函数，相减是imsub()函数，相乘是product()函数，相除是quotient()函数。用符比较简单次方
power()函数或者用“^”符号，这个符号或者函数还可以用于开方，如：16^(1/4)就是开四次方根开方
sqrt()函数
开平方绝对值
abs()函数
组合数计算
combin()函数
返回从给定元素数目的集合中，提取若干元素的组合数。排列组合里的“组合”求余
mod()函数
这个函数可以对2求余判断是偶数还是奇数，还可以用于隔行填色、隔列求和。取整
int()函数
向下取整到最接近的整数trunc()函数
按照指定的小数位数进行截位round()函数
指定位数的四舍五入计算
也可以通过设置数字格式来达到位数的选择。5.4统计函数
计数函数
count()函数
返回包含数字的单元格的个数counta()函数
返回非空单元格的个数countblank()函数
返回空单元格的个数countif()函数
条件判断，也可以用于数据验证中，限制重复数据输入。countifs()函数
多条件判断
说明：与单元格比较‘大于等于’的写法容易写错，应该是这样写的：=countif(B2:B11,”>=“&A2）求和函数
sum()函数
对选定的单元格区域相加起来sumif()函数
对满足条件的单元格区域相加sumifs()函数
多条件的相加函数sumproduct()函数
给定的几组数组中，对应区域的单元格相乘然后把他们的积相加平均值函数
average()函数
返回算数平均值averageif()函数
返回满足条件的平均值averageifs()函数
返回满足多条件的平均值极值函数
max()函数
返回最大值min()函数
返回最小值large()函数
返回第几大的值small()函数
返回第几小的值排名函数
rank()函数
用于得出一组数据的排名rank.avg()函数
这个和上面有些不同，不过没关系rank.eq()函数
这个也是频率函数
frequence()函数
返回一组数据中的频率分布综合函数（神器）
subtotal()函数
这个函数包含了average、sum、count等11种统计功能，几乎是其他函数的综合。可以手动输入也可以将数据表创建为超级表，然后就可以使用这个函数了。 这个函数的11个功能分别是输入1-11作为函数的一个参数，以选择其中一个功能。
可以建立动态序号，隐藏某些行后可以更新序号5.5数组入门
数组是什么
数组是单元格区域，一行或一列就是一维数组，多列就是多维数组。数组计算或输入公式时要按shift+ctrl+enter。数组计算可以代替填充。5.6函数常见错误
我们使用函数和公式出现错误时，往往不知道如何修改，不知道哪里出错。所以这里列出了8种常用的函数错误，以及如何改正。#NAME?
1.函数名称错误：可以通过打几个字母后通过“tab”键选择函数，而不是自己打全拼。这样就不会出错了。
2.引用的文本没有双引号”
3.引用的单元格不存在了，因为被引用的单元格被修改了，导致函数引用出现错误。
4.引用的单元格区域是用”：“来作为标识的，如A2:A7。如果冒号不小心忘记打了，就会导致错误。#N/A
不存在的意思。一般出现在使用vlookup函数时，单元格区域引用未加“$”符号，也就是未使用绝对引用。#
单元格的宽度不能显示全部内容（一般为数字）调整宽度即可#DIV/0!
除数为0：这个错误值是在除数为0的时候，但是我们也可以利用这个错误在lookup函数中，找出我们想要的值，不想要的则显示为这个错误。#VALUE!
公式需要的是数字或逻辑值时却使用了文本作为其参数。#NUM!
1.函数的参数不被允许，如sqrt函数是不能用负数作为开平方的底数的。
2.函数的计算值超过excel的上限或下限。
3.日期计算时，起始日期大于结束日期。如datedif函数。#NULL
使用了不正确的区域运算符或区域引用之间出现了交叉运算符（空格）来代替不交叉的区域。”交叉“在这里是指两个单元格区域有重合的部分。#REF!
引用的单元格被删除了错误修正帮手
“公式”选项卡中，“公式审核”功能，里面有“显示公式”，单元格里有公式的话就会全部显示出来。“追踪引用单元格”，这个可追踪引用的单元格，这样就不会出现引用的单元格被删除了。如果想知道某个单元格被哪些其他的单元格引用的话，可以相应地点击“追踪从属单元格”.最后的就是”错误检查”,会给出公式错误的帮助。错误值转换为文本的函数
iferror()函数
当出现错误时，该函数可以将错误值转换为其他文本了。ifna()函数
只当出现了#N/A错误时，才会转换为文本。第六课 数据展示
6.1条件格式
在“开始”选项卡中，找到“条件格式”。就有下面这5个功能选项卡。突出显示单元格
满足条件的单元格将会以高亮或颜色、填充等形式来突出显示。最前/最后规则
筛选出前5%等或后5%等数据，并突出显示数据条
添加带颜色的数据条，值越大，数据条的长度越长色阶
为单元格区域添加颜色渐变，值大的用某种颜色，值小的用另外的某种颜色，这样数值大小就一目了然了。图标集
可以为所选的数字单元格，在单元格的前面添加符号。
说明：选项卡中还有三个条件，可以直接在这里新建、编辑、删除规则6.2数据透视表入门
点击“插入”选项卡，“数据透视表”。即可创建数据透视表。这个功能可以将表格的数据变为“分析表”的形式，直观清晰，更加直观的还有“数据透视图”。
点击“数据透视表”后，我们可以将所需行或列作为字段拖动进“透视表”中，不需要的则不用添加进去。除了这个添加已有的字段。还能使用“求和”、”平均值“、条件查找等功能作为透视表的一部分。透视表常见问题
1.没有行/列标题，也就是没有表头。导致字段无法生成。
2.标题的名称相同。虽然这样也可以生成字段，但是容易让我们产生混淆从而导致错误。
3.存在不规范日期，可使用“分列”功能来规范日期格式。
4.存在文本型数字。导致计算时出现错误，结果为0.
5.有合并的单元格在源数据表中。导致透视表中出现空白项，取消合并单元格即可恢复错误。6.3图表入门
图表选择指南
根据我们的需求，选择合适的图表来反映我们的数据，可以达到直观明了的效果。下图是美国著名统计学家所画的图表选择图，更多信息可以参见这个网址：https://zhuanlan.zhihu.com/p/23433179如下
透视图插入
点击“插入”，“数据透视图”，根据选择指南选择合适的类型图，比如柱状图和条形图等。图表的3个快捷键
生成透视表后，表的右上角有三个按钮，分别是“加号”、“毛笔”和“漏斗”，“加号”里面是”图表元素“，可以添加折线、坐标轴等。“毛笔”里可以更改样式和颜色。“漏斗”是筛选功能。快速布局
生成透视表后，再点击透视图，点击“设计” 选项卡，点击“快速布局”，可以更改透视图的类型，比如由柱状图改为折线图。第七课 excel表格规范
7.1获取外部数据
文本导入
说明：有时我们除了对着账单等进行手工导入信息，还会借助外部文件进行数据导入，直接生成数据源。比如从中国统计网等网站直接导入数据至excel，或者导入文件名为txt或CSV的一些通讯录文件之类的，如果了解数据库的话，还可以将数据库的数据直接导出至excel里面。
点击”数据“，看到“获取外部数据”功能组，这里有包括access、网站、文本、SQL SERVER和XML数据等类型。txt文件导入
txt文件也就是文本类型的文件，以后缀名txt结尾的文件。导入方式有两种。1.excel打开 导入，点击“文件”、“ 打开”、“浏览”，然后选择txt文件打开。excel会自动使用“分列”功能来对文本进行分列。2.点击“数据”、“自文本”，选择要导入的文本文件，点击”导入“。还是和第一种方式一样，”分列“功能。第二种方式可以实现当文本文件更新时这个文件也能更新。还有就是复制粘贴更新。网站导入
点击数据选项卡的“自网站导入”，有些网站不支持导入表格的功能所以还是直接复制粘贴比较好7.2表格设计规范
隐藏行或列
如果数据不需要显示，或者相距太远就可以把他们隐藏起来冻结窗格或窗口拆分
“视图”选项卡里面的冻结窗格或窗口拆分的功能。为了方便查看。自定义格式
按ctrl+“1”可以调用单元格格式设置，然后点击“自定义”、点击general，在general后面加上“元”字，这样就可以让单元格实际上是数字，但是显示的是XX元。分类汇总
可以对单元格的区域进行汇总，不会影响透视表的生成。7.3超级表的应用
超级表有很多功能。想知道是什么自行搜索。创建超级表
选中要创建的单元格区域，按“ctrl”+“L”或”ctrl”+”T”或者在“插入”里面找到表格。创建之后
点击“设计”，然后有“标题行”、”汇总行“等可以进行勾选，就是之前介绍过的subtotal函数，有11种功能的那个。公式重用和数据扩展
在超级表里输入公式时，会自动重复使用函数，对一个进行输入函数，对整个标题的一列都是相同的公式。这个可以在“文件”、“选项”中可以设置是否自动重用函数。切片器
转换为普通区域
在“设计”中可以看到转换为“普通区域”。7.4系统模板应用
excel里面有很多表格的模板，可以加以利用。第八课 高效数据录入
说明：从第八课开始就是中级篇了，之前的都是基础篇，从这一课开始我们就是要利用之前学的基础功能来解决工作中的实际问题。8.1快速录入操作
回车后光标下移设置
按下回车后，光标会往下或往右移动，可以通过“文件”、“选项”设置为向右或者向左。按tab键则是向右移动无法更改使用填充
使用快捷键ctrl+D向下填充，或者ctrl+R向右填充。快速填充
提取身份证中的出生日期、快速拆分数据、数据重组合并、银行卡号增加空格等。详见第三课的第二节。重复数据录入
如果每次都要输入某些较长的文本，我们可以通过设置“自动更正”选项来讲长文本替换为特定的短文本。点击“文件”、“选项”找到“校对”，然后再将你要替换的文本和更正后的文本输进去，就可以了。提供下拉菜单
当你想让别人从特定的数据中选择一个输入你可以点击“数据”、“有效性”，选择其中的“序列”格式，然后以英文逗号隔开各个选项。alt+↓
调出该列之前输入过的数据。数据缩放
录入数据自动增大或缩小100倍，10倍等，在“选项”中找到“高级”，自动插入小数点，就可以将数据缩放了。定位选择 批量录入
利用ctrl+G调用定位功能，找到空白的单元格再在编辑栏中输入“未录入”，最后按ctrl+enter，所有的空白单元格都可以变为“未录入”。也可以定位其他的值。生成模拟数
rand()函数、randbetween()函数可以产生随机数。每次都会刷新。一定要按ctrl+enter才是批量录入。这样就不需要再点击填充了。8.2实用录入技巧
0开头的数据输入
在数据最开头加上一个英文的单引号即可，将数字保存为文本型数字。身份证号码输入
身份证是18位的数据，这种超过11位的数据系统会默认以科学计数法的形式显示，所以可以在最前面加上一个英文单引号或者修改格式为文本。手机号码间隔显示
选中手机号的单元格区域，按键盘的ctrl+1调用单元格格式设置，“分类”选择“自定义”，将“类型”修改为“000-0000-0000”或者其他格式。这样就可以方便大家读取手机号数据不会出错。这样比手动添加横线要快很多。而且数据类型还是数字，值也没有发生改变，只是显示形式变了。银行卡号的间隔显示
因为银行卡号是超过了11位的，所以不能通过像对手机号那样来修改格式，只能通过公式的方式来解决这个问题。可以采用replace()函数来实现，只需要在第5位、第10位、第15位、第20位添加一个空格即可。或者手动对一个数据进行空格的添加，再采用快速填充的方式来进行填充也可以。8.3数字录入技巧
分数正确录入
直接输入“1/3”是会默认转换为日期格式的，所以要这样输入才行。“0 1/3”。0和1/3之间加一个空格。0代表整数部分，1/3代表小数部分。如果输入“1 1/2”则是一又二分之一，也就是1.5.以“万”来显示
ctrl+1调用单元格格式，”自定义“，然后输入格式为[0!.0,”万元“]。后面的‘0’个数是你想显示的小数点后的位数。多个‘0’的输入
当我们输入多个零的时候，容易输错，我们可以这样输入，先输入前面的非零部分数字，然后输入‘’，再输入‘0’的个数，后面有几个‘0’就输入几。比如，1200000有5个零，就输入“125”。数据会默认以科学计数法显示，再将数字格式从“科学计数法”转换为“常规”即可8.4其他技巧
限制重复录入
这个技巧之前讲过，点击“数据”，“数据有效性”然后在“公式”栏输入countif函数，判断最开始的单元格是否只有一个。eg. ”=countif(A$1:A1,A1)<=1”这样就可以限制重复数据录入。二级下拉菜单
说明：这个可以自行百度,打字有些复杂。先把自己的下拉菜单对应的数据源写好来。定义“公式”中的“名称管理器”，定义好名称后然后用“数据验证”来做。单元格内强制换行
按alter+enter可以实现强制换行，清除强制换行符的话，可以采用右键点击该单元格再清除单元格格式、特殊字符或clean()函数。第九课 初级数据处理
9.1表格转换
行列转置
将单元格区域复制后点击选择性粘贴，点击“转置”粘贴。或者使用transpose()函数。一维表、二维表转换
1.数据透视表实现。点击“数据”、“数据透视表”，选择单元格区域并创建透视表，透视表创建完成之后我们点击右下角的单元格系统会自动为我们生成一个新的工作表，里面有一维的工作表。
2.“数据”选项卡的“从表格”。2016版本新增功能，点击“数据”、“从表格”，选择要创建的原始单元格区域，找到”逆透视列“功能就可以转换为一维的工作表了。点击”关闭并上载“9.2空行处理
添加空行
在数据的最边上添加一个辅助列，然后输入1，填充至最后一列数据，将这些数字复制，并粘贴在这一列的下面，然后点击这一列升序排列，每行就会有一个空行。（如果想要每行增加3个空行只要将数字复制3遍即可。）删除空行
选择单元格区域，按ctrl+G定位功能，定位空行。找到空行后删除他们。
删除多列同时为空的空行：多列都为空才视为空行的话，这样需要增加一个辅助列，再利用countblank函数来计算出一行的空白单元格数量，再调用定位功能。9.3数据对比
同行两列是否相同对比
可以用if()函数，或者选中两列后按ctrl+\ 就可以找出不同的单元格。两列对比
查找这列的数据在另外以来列是否存在，不一定要求是同一行，可以用vlookup函数。
二维单元格的对比，用vlookup函数和sumifs函数不大方便，如果表格的顺序也是一样的，可以直接选择性粘贴，然后运算点击‘减‘，可以明显找到不一致的数据。9.4数据查找
数据一对一查找
vlookup()+match()函数，match查找出所在列，再用vlookup查找出值。一对多查找、表格联动
先说联动。可以将公式里的值写在一个单元格中，用下拉菜单的形式限制用户的输入，然后公式调用这个单元格，对这个单元格进行修改的话，引用了该单元格的公式都会变化，以达到联动的效果，下面例子中的“班级 一班”就是这样的。一对多查找：可以先利用countif函数计算出相同的次数，再和单元格用“&”连接起来，这样就可以避免vlookup函数不能一对多查找的缺点了。高亮显示 条件格式应用
要高亮显示满足条件的单元格，点击“条件格式”、“新建规则”、“使用公式确定要设置格式的单元格”这个类型。
index函数和match函数的应用，找出单元格区域中的某个值。第十课 高级数据处理
10.1组合工作表
切换工作表
ctrl+pageup或者ctrl+pagedown，快速切换工作表，就右键点击左下角，可以快速跳转到自己想要的工作表上。组合工作表
将全部工作表组合起来，然后可以对它们进行相同的操作。右键点击某个工作表，然后点击“选定全部工作表”，再进行相同的操作可以取消选定全部工作表。
对多个工作表的相同区域进行求和：可以用公式=sum(‘*’!B3).还可以这样输入公式：=sum(‘sheet1’:’sheet3’!B3)这样sheet1/2/3的B3单元格都会被加起来。10.2合并工作表
跨工作表的单元格引用：适用于数据量不多、工作表也不多的情况。手动引用单元格，再填充。把所有的工作表都填充过来以后，最后记得将引用过来的数据粘贴一遍，按值粘贴。
WPS有付费功能合并工作表/簿，office有查询编辑器，需要自己再官网下载。WPS的功能很好，直接就解决了工作表汇总的问题，开通会员即可。
公式法：还能用函数的方式来解决这个问题。10.3拆分工作表
汇总表拆分
汇总表先变为透视表，然后将相应的字段拖动至“行”、“列”、“值”等。如果要针对每条数据来拆分成几个工作表，点击“分析”、“选项”功能的下拉三角形，“使用显示报表筛选项”。系统就会针对每个对象生成对应的工作表，一个工作表是一个文件。10.4合并工作簿
- VBA代码合并工作簿
- 共享工作簿后，比较和合并工作簿：先将这两个功能添加至选项卡中，然后共享工作簿，注意这个作为共享工作簿的工作簿需要自己先做好，规范格式，并且其他用户在填写的时候也不能随意更改工作簿的信息。共享之后合并。
- 手动合并工作簿
10.5拆分工作簿
VBA代码拆分工作簿：将一个文件中的多个工作表拆分为多个工作簿文件第十一课 数据透视表应用
11.1插入数据透视表
选中自己的数据源表，然后点击“插入”、“数据透视表”。选择现有工作表或者其他工作表的某个位置插入透视表，然后再根据字段将它们放入“列”、“行”、“筛选”、“值”中。11.2报表布局、样式和选项
选中“设计”选项卡，可以更改透视表的样式，以及“分类汇总”、“总计”、“报表”、“空行”等功能可以使用。
选中透视表后，点击“分析”选项卡，或者选择某个单元格后右键，再点击“数据透视表选项”。这里面有很多功能。
- 将空单元格显示为某个特定值
- 更新时不自动调整字段
- 更改为数据表样式（在“显示”里）
11.3值字段设置和计算字段
当行标题或列标题拖动到值字段时，字段属于数字类型默认的计算类型是求和，若是文本类型则计算类型是计数。还有其他类型可选。点击“值”里面的某个值字段，然后选择“值字段设置”。在“值汇总方式”页签下看到计算类型，可以选择不同的计数效果。比如“总计的百分比”，可以得出总计后所占的比例。
excel除了可以对已有的字段进行统计分析，还可以自定义字段做统计分析。点击“分析”选项卡的”计算“区域找到”字段、项目和集“。点击它后再点击”计算字段“。可以在里面利用已有的字段加上加减乘除四则运算生成新的字段用于统计分析。11.4筛选、排序和组合
透视表的筛选功能
报表筛选、标签筛选、值筛选。1.报表筛选：将字段放在“筛选”栏中，即可实现报表筛选。2.标签筛选：点击透视表内的行标签和列标签的下拉三角。即可实现筛选。3.值筛选：点击行标签或列标签，输入大于等于小于某个值，将满足条件的行或列筛选出来。透视表排序
将透视表的各个字段进行排序。右键点击行、列字段，点击“移动”即可。或者点击行标签，点击“升序”或“排列”。如果是要选择某个字段的话，那就点击该字段的一个单元格，右键点击“排序”即可。透视表组合
日期类组合：将日期类字段拖入列或行中，再在透视表中选择该日期字段的某一单元格，右键，点击“组合”功能，可以将日期按照“日”，“月”，“季度”等方式查看。
数字类组合：将数字类型字段拖入列或行中，也是相同的方式，右键点击“组合”，可以将数字按一定范围组合起来。
文本类组合：将几个如人名的文本组合起来，选中要组合的那几个单元格，然后右键点击“组合”。可以将这几个文本划分为一个新的数据组。11.5数据刷新和透视表操作
数据刷新
“分析”选项卡下面的“刷新”。1.当明细表（数据源表）中数据发生变化时，这个透视表中的数据不会更新，可以点击“刷新”来更新透视表。2.当一个明细表建立了多个透视表是时，我们可以点击“分析”选项卡下的“全部刷新”来更新透视表。添加超级表
当明细表作为透视表的数据来源时，我们对明细表进行一行/列的数据添加时，透视表不会将新的区域视为数据的一部分，每次需要手动更改透视表数据来源区域。这时可以将明细表创建为超级表，然后添加新的行或列。再进行透视表的刷新即可。透视表的删除
想删除整个透视表时，或更改某一个单元格时，会提醒你无法更改透视表数据。删除整个透视表可以选中比透视表大一些的区域，这样就可以按delete删除透视表了。11.6切片器、日程表和透视图
切片器
实现数据的筛选。点击分析选项卡，点击”插入切片器“，对某个字段插入其切片器。 切片器生成后，点击上面的某项数据透视表就会只显示那项相关的所有数据。可以插入多个切片器。
选中切片器后，上方的“切片器工具”中可以对切片器的样式等，功能都比较简单。日程表
在“插入切片器”的旁边。插入日程表后，将以日期为格式的字段放入日程表中，这样可以筛选出来某个日期的的数据。数据透视图
根据你生成的数据透视表生成对应的、可随透视表更新的透视图。选中透视表，点击“分析”、点击“数据透视图”选项，就可以生成透视图了，然后在设计里面可以找到“样式”对透视图进行设计。11.7多表合并透视
多表合并透视
将多个表合并到一个透视表中，可以查看两个表的数据。按alt+D+P，再勾选“多重合并计算数据区域”。然后就是选择你要生成透视表的两个及以上的数据源表。第十二课 常用图表&动态图表
12.1图表设计原则
1.用对图表类型
针对你想表达的意思，来选择合适的图表类型，可以回顾第6课的图表选择指南。2.足够简洁
图表中有很多元素描述和辅助线等，只要能够表达出数据的意思就可以省去很多辅助线等。3.配色友善
系统模板中的图表很多都设计得非常不错，只是配色可能太丑了。如红字蓝底是很伤眼的，千万不要采用这种配色。可以去网上查询一些简单的配色小知识。4.突出重点
怎么才能让你需要突出的数据做得更显眼呢？可以修改颜色，制作辅助列，图标替换等技巧，或者用动态图表的形式来突出你的重点。12.2图表优化技巧
配色方案网站
站酷网：https://www.zcool.com.cn/这个网站搜索图表，再去找相应的、合适的图表。它里面有些图表的配色比excel自带的图表要好。
花瓣网：https://huaban.com/ 这个网站也是设计素材网站，在首页搜索“图表”关键字，可以有很多图表，不过这类网站更适合拿来做PPT的配图。这两个视频推荐的网站差不多同类型的网站。图标应用网站
用一些图标代替原有图中的柱形、饼状等，能够更加形象地表达数据的含义，提高图表的展示力。常用的网站有iconfont和easyicon这两个网站。里面有很多不同类型的矢量图标。接下来讲图标替换技巧会提到怎么使用这些图标。1.修改配色方案
先在“站酷”等网站上搜索并找到自己觉得合适的配色图表，然后再用自己下载取色器软件如colorpix等进行取色，再返回excel中将自己的图表（柱形图等）修改为所取得的颜色。2.图标替换
先在“iconfont”等网站上找到你所需要的图标，然后返回到你已经根据单元格区域的数据生成的excel图表中，将图标复制到你的条形上（假设为条形图）。右键点击条形图，点击“设置数据系列格式”。再选中你已经粘贴的图标，再点击右边的“线条与填充”找到“层叠”就可以将条形替换为自己的图标了。3.调整峰值颜色
选中某一个条形，修改其颜色为红色等，以突出它。4.建立辅助列
巧用辅助列。例：3个项目的完成进度，进度以百分比形式反映。构建一个辅助列，是未完成的进度，用1-“完成进度”即可。然后用环状图作为透视图。再将未完成的部分采用白色作为其颜色。这样就会比较明显突出完成的进度。这是构建辅助列的功能，做出对比的效果。5.建立次坐标轴
一个图里面有多个不同维度的数据条。比如折线图中有两条线，但是它们的数值量级不一样。可以再设置一个纵坐标轴来反应折线的变化。选中该条折线，然后在“设置数据系列格式”、“系列”选项，勾选“将系列绘制再次坐标轴”6.设置对数
当数据中有很大的差距时，将它们反应在同一个坐标轴中也还是不太直观，这是可以将坐标轴设置对数为其单位或说量级。选中该坐标轴，在最右边点击“格式设置”、“坐标轴选项”、“对数刻度”，一般可以用10、2、5作为对数刻度。在可以设置12.3动态图表制作
“动态图表：根据你鼠标选择的折线或其他类型的东西，会发生颜色等的自动变化，以达到动态显示的效果，这叫做动态图表。不是GIF图哦。
点击“开发工具”选项卡，“插入”功能下的第2个组合框。把那个组合框放到表格中来。选中它再点击鼠标右键，点击”设置控件格式“。这个步骤太多，写出来比较费时间，而且也不一定能被理解，建议看原视频的第12课或者百度搜索怎么做动态表格。又是组合框控件，又是index函数和vlookup函数。”第十三课 表格美化设计
说明：高级篇13.1表格模板应用
excel模板应用
点击“文件”选项卡，点“新建” ，里面可以找到很多表格模板，可以利用那些对你工作有帮助的表格模板来直接进行数据的录入。也可以自己上网找模板。
“三表”概念：明细表、汇总表、参数表。
如果是公式计算得到的数值，如果不再需要其随着其他单元格改变而改变，最好将其粘贴为“值”。13.2表格格式套用
“开始”选项卡下有“表格样式”选项。13.3单元格样式使用
ctrl+1调用单元格格式13.4商务表格设计
弱化边框颜色
将明细表的除了标题行以外的部分的边框弱化，让表格看起来不会那么条条框框。设置间隔背景
将第一行不变，第二行单元格的背景设置为浅灰色或者其他颜色，然后选中第1、2行，点击格式刷，将剩余的其他行都刷成这个格式。除了这个方法还可以在“单元格格式”里面选择“使用公式确定要设置格式的单元格”，这个方法也能达到相同效果。修改字体
最开始表格都是统一用的宋体，那我们把它们修改为非衬线字体。1.将标题行的字都改为加黑粗体，背景色调为较深的背景色，然后将字体颜色改为白色。2.标题行下面的具体数据部分，中文用微软雅黑，数字和字母用Arial字体。统一将除标题外的数据部分的字体大小改为9号字体，行高设置为22.。标题行的行高略大于22即可，28,30的样子都可以。如果明细表中还有汇总行的话，还得将汇总行的字体加粗，以便于和具体数据进行区分。表格的信息可视化
可视化不是什么高深的东西，就是让表格看上去有颜色或者说有图标的样子，不是那种全部都是数据的表格，给人一种头晕、想逃避数据的感觉。有了图标就会让人更愿意看这个表格的感觉。利用“开始”中的“条件格式”的“图标集”，那里有一些图标可以添加进表格中。第十四课 数据可视化
14.1条件格式
突出重复值
“开始”选项卡中的“条件格式”、“突出显示单元格”可以将重复值找出来。针对多列都重复的情况才算重复值的话，我们可以构建辅助列，利用“&”符号将两列连接在一起，然后再利用条件格式来判断是否为重复值。数据条
当比较金额等数据时，在该列的单元格中添加“数据条”会让它更显眼和一目了然。“条件格式”下的“数据条”。另外“色阶”也能表现出数据的大小。图标集
图标集可以添加区间范围，将多少至多少的数据用什么颜色的图标形状，其他区间又用另外的颜色。这样可以突出数据在各区间情况。（可将区间节点的数据放在另外的单元格中，这样不用每次修改条件格式中的值，改那个单元格的值就行了。）突出一整行数据
“条件格式”中的“新建规则”，点击“使用公式确定要设置的单元格类型”，然后写公式，注意要突出的是一整行数据，公式里面的单元格引用应该是，“列”用绝对引用，加“$”符号，“行”不用美元符号。14.2迷你图
“插入”选项卡中，“迷你图”区域，里面有折线图、柱形图、盈亏图。这个迷你图是画在一个单元格里面的，所以叫做迷你图，也能很好地反映出数据的可视化。14.3函数和特殊字符
14.4应用商店和加载项
三维地图
“如果明细表中是有关于城市、地点的数据的话，我们可以利用excel自带的三维地图来将数据做在地图中。
选中数据，然后点击“插入”、“三维地图”。出现三维地图后，点击右边的上部“添加图层”。里面会有之前选中的明细表数据的字段标题。剩下的操作自己再去摸索一下就行了。（这个功能好像不是很常用）”应用商店
“插入”选项卡中有“应用商店”选项，在应用商店里面，有很多加载项。里面的加载项有各种各样的功能，而且大部分是免费。第十五课 批量操作
15.1制作表格目录
所需使用的公式
自定义名称(公式1) =INDEX(GET.WORKBOOK(1),ROW(A1))&T(NOW())
工作表链接(公式2) =IFERROR(HYPERLINK(“#’”&MID(目录,FIND(“]”,目录)+1,99)&”‘!A1”,MID(目录,FIND(“]”,目录)+1,99)),”0”)
返回目录 (公式3) =HYPERLINK(“#目录!A1”,”返回目录”)
第1个公式中的“&T(NOW())”是为刷新名称管理器的，起到刷新的作用。
怎样制作EXCEL工作表目录
第一步，建立目录工作表。在我们已经有多个sheet页工作数据的excel文件中新建一个工作表，命名为“目录”
第二步，单击A1单元格，切换到【公式】选项卡。
第三步，单击【定义名称】，弹出【新建名称】对话框，在“名称”文本框中输入“目录”。
第四步，在“引用位置”文本框输入公式1。
第五步，创建带链接的目录。A1单元格输入公式2。
第六步，将公式3复制到各个工作表中。
ps：WPS把这种方式给禁用了，但是你单击工作表，右键点击“创建工作表目录”，可以直接创建目录，一点不麻烦就是要开通会员而已hhh。
表格目录：一个工作簿里面可能会有很多工作表，可以通过左下方的工作表名称来切换，但是太多工作表会导致切换的非常慢，而且也不方便找到到底是哪个工作表。这是我们新建一个工作表，并将其位置放在最前面。作为我们的表格目录。在表格目录中，我们点击单元格所对应的工作表就可以进入该工作表了。
注意：表格中有get.workbook ()函数和hyperlink()函数，应当将表格保存为.xlsm文件。xls文件也行，不过最好是.xlsm文件15.2制作文件目录
文件夹中有很多文件，不方便查看。我们也可以利用excel建立起一个表格，然后利用excel的超链接功能，跳转到文件夹中的各个文件，就像我们上面建立表格目录那样。1.手动输入hyperlink函数，或者单击单元格右键，点击“超链接”来创建链接。
制作方法：
1.在要建立文件目录的文件夹中，按Windows+”R”来打开运行对话框，然后输入”CMD”打开对话框，然后在这个对话框输入“dir”+“空格”+“文件夹所在位置”+“空格”+“/b>”+“空格”+“生成表格的位置”+“\文件命名.xls”。全部输入完之后“回车”。例子：[dir C:\制作文件目录/b> C:\制作文件目录\文件目录.xls]。如果看上面的说明觉得麻烦的话看这个就好了。
例子：[dir C:\制作文件目录/b> C:\制作文件目录\文件目录.xls]
2.打开创建的excel表格文件，可以看到这个人表格里面已经把该文件夹下的所有文件都放在单元格里了，接下来创建hyperlink函数的超链接即可。hyperlink函数的第一个参数是链接位置，所以将文件位置粘贴至参数1的位置，注意最好将“C:\” C盘删掉。然后用“&”连接单元格中存放的某个具体文件的名字。最后参数2写你希望显示的名字就好。最后将文件保存为.xlsm格式。以保证hyperllink函数能够正常运行。
公式：=hyperlink(“\制作文件目录\”&”A1”,”打开对应文件”)15.3批量修改表格名称
方法：若要批量修改工作表的名称，可以将表格关闭，在文件夹中，找到该表格文件，再将其文件名后缀改为.rar。也就是压缩文件。再打开rar文件，找到“xl”文件夹，找到“workbook.xml文件，将其拖动txt文件中打开。另外txt文件还有替换功能，可以将工作表名称的公共部分全都替换掉。如果不是公共部分只能手动修改了。这样就可以批量修改了。最后记得将文件后缀名改回至xlsx15.4批量修改文件名称
采用excel+批处理方法：
1.将文件夹中的文件名批量修改，在文件夹中新建一个excel表格，命名为“表A”。然后打开表格
2.打开“公式”选项卡，点击“公式”、“名称管理器”、“新建名称”，在“名称”栏中输入“修改名字”，“引用位置”栏输入公式{=files(“C:\修改文件名称*“)}。其中[ C:\修改文件名称\ ]是文件夹的位置。
3.点击A1单元格，输入公式“=index(修改名字,ROW(A1))” 按回车。然后下拉填充。就会得到文件名。
4.在B1单元格输入你在文件名中要添加的字，例如”2021年“，下拉填充。然后在C1单元格输入”=A1&B1”。下拉填充。再把C列数据复制粘贴为值。
5.然后在D1单元格输入{ =”REN”&” “&A1&” “&B1 }。其中“REN”关键字是作为bat文件的一个重命名（rename）命令。
6.将C列中新的文件名，复制到txt文件中，将excel表格关闭。把txt文件后缀名改为.bat，打开。双击打开bat文件，也叫批处理命令文件。15.5批量插入图片
方法：
1.将要插入的图片都放在一个文件夹中，文件命名应该与单元格中名字匹配
2.粘贴代码的公式： {=”
“}其中文件位置需要从文件夹中复制到这里来。A1中应照片的名字，这样才能将对应的照片复制到对应的行里面来。将公式下拉填充。
3.将下拉填充得到的代码全都粘贴到一个txt文件中，再全选粘贴到excel表格中。15.6批量新建文件夹
例如：针对每个员工新建一个文件夹。方法：
1.将姓名全都写在一列中，假设是A列，第一个员工姓名放在A1单元格中，然后在B1单元格中输入{=”MD”&” “&A1}。然后下拉填充公式。
2.创建一个txt记事本文件，把B列的数据复制到记事本文件中，点击保存。再将其后缀名由.txt修改为.bat。然后双击bat文件，在该文件夹中就会生成以员工姓名命名的文件夹。第十六课 Word、PPT联动
我们一般使用excel来制作表格，使用word来制作文档，使用PPT来做展示汇报。如果要在word或PPT中使用表格文件的话，可以直接引用在excel中做好的表格。16.1Word联动
表格粘贴
1.直接在word中创建表格。在word中点击“插入”，“表格”。就会创建一个几乘几的空白表格
2.复制excel中的表格，粘贴至word中，这可以保留excel中的格式，和在excel中的表格是一模一样的。选择性粘贴有很多中格式的，还可以用其他格式进行粘贴。
3.其他格式粘贴中，有一种是可以更新数据的，当你的excel表格中数据发生了变化，点击word中的表格，右键点击，可以看到更新表格这个按钮。邮件合并
点击word中“邮件”选项卡，“开始邮件合并”，“电子邮件”，找到对应的excel表格，表格中的明细表，一行数据就是要发给一个人的邮箱的数据。
ps：这里不大好展开来讲，点击链接，去百度知道看看吧。
word邮件合并16.2PPT联动
和word一样，也是表格粘贴，先复制excel表格，然后选择性粘贴。里面有可以更新表格的一种粘贴方式。表格粘贴
点击“视图”选项卡，我们来看看里面的选项卡有哪些功能。16.3表格视图
工作簿视图
1.普通视图，这是我们默认的视图模式，平时打开一个表格文件，就是这个视图模式。
2.分页视图，在这个视图环境下，可以看到我们要打印时的所需页数，默认是A4纸张。其中蓝色的虚线是分割线，我们可以手动移动分割线，以调整每页的打印区域。如果要使整个表格打印在一张纸上，我们将蓝色的分割线拖到边缘位置就好了。除了调整这个打印页数，还可以调整打印区域，一些区域打印出来，另一些则不打印出来。按ctrl+P调用“打印预览”。
3.页面布局视图，这个模式主要用于打印设置上，可以直接编辑页眉页脚。
4.自定义视图，这个模式可以保存我们的表格中出现的表格状态或者说“图片”。比如我们做了一次筛选后，可以将筛选后的视图保存在“自定义视图”中，不用对筛选后的图进行保存，节省时间和存储空间。显示和显示比例
“显示”和“显示比例”这两个选项的功能都比较简单，自己肯定能看懂哒。窗口
冻结窗格：一般都是冻结标题行或者标题列。如果想冻结首列的同时冻结首行，可以选中B2单元格，然后点击“冻结窗格”的“冻结拆分窗格”。这样就可以了。当然你也可以点击其他单元格，以冻结更多的行和列。
新建窗口和重排窗口：新建窗口再重排窗口，这个功能主要用于数据核对。在对比的同时，再勾选“并排查看”和“同步滚选”。这样更好的进行数据的比对核验。16.4表格打印
ctrl+P：调用打印预览。
这个打印预览下面有很多设置。可以帮助你更好地打印出表格文件。超级表+页面布局视图
为了打印出比较好看的表格，有底纹，间隔背景等的表格，我们先将表格变成超级表，然后在“页面布局”选项卡下，这个选项卡中有很多选项可以点击。比如“主题”中选择合适的格式、“颜色”、“字体”中选择颜色和字体等。还有页边距和纸张方向等可调。“页面布局”选项卡就是为了打印而生的。页眉页脚
页眉页脚除了可以添加文字还可以添加图片作为logo来进行打印，进入页眉页脚设置后，点击左中右三个区域任意一个，点击“设计”，“图片”可以插入图片进页眉或者页脚。“打印标题：选项卡
这个选项里面有很多东西可以设置，打印时会用到。
若有收获，就点个赞吧
0 人点赞
