(课程学习笔记)Excel学习

1.用好快捷键，工作效率提升3倍
1.1快手选数，再也不用鼠标点到手抽筋
如何快速选中某一行莫一列或者说整个表格的所有数据呢？
ctrl+shift+↑、↓、←、→，碰到空单元格会停止，再按一次方向键即可。
若是想微调数据，不想所有数据都要？
shift+↑、↓、←、→进行微调。
想快速到达某一单元格的最极端那个单元格怎么操作？
ctrl+↑、↓、←、→
不连续单元格的选取？
ctrl+鼠标左键的选取
连续单元格的选取？
开始单元格+shift+结束单元格
连续区域快的选取？
一.开始单元格+shift+结束单元格
二.在名称框中输入两个单元格的位置信息,比如A10:Q45然后按回车键
快速找到某个单元格？
在名称框中输入单元格的位置信息,比如Q45然后按回车键
如何查找所有带公式的单元格？
ctrl+G，选择公式选项，查找

1.2快手选数，行列数据瞬间转移
列数据的交换？
鼠标选中一列数据，鼠标放到列边缘，当鼠标变成一个“四向箭头”时，按住shift，再挪到目标列前面，就实现了列交换。
行数据的交换？
鼠标选中一行数据，鼠标放到行边缘，当鼠标变成一个“四向箭头”时，按住shift，再挪到目标行前面，就实现了行交换。
单元格或者区域快数据的交换？
鼠标选中数据，鼠标放到区域边缘，当鼠标变成一个“四向箭头”时，按住shift，再挪到目标区域前面，就实现了区域交换。
什么时“工作组”？“工作组”的作用？
工作组是指几个工作表合成一起形成的叫做工作组，当ctrl+shift+pgdn/pgup 选中多个表时就形成了工作组，对任意一张表的操作同时会体现到其他表。

1.3快手选数，花样数据分分钟填完
数字填充：关于序列的填充，有步长和无步长？
选中数字1，把鼠标放到单元格右下角，当出现黑色+号，学名叫填充柄，按住鼠标左键往下拉，填充完之后会出现自动填充选项，可以选择复制单元格和填充序列。如果序列要求有步长比如说1，11，21，31这样的，可以在步长设置里为10，或者在序列前两格填写1，11，excel会自动认为序列步长为10并按此序列增长。
日期填充：仅仅以工作日填充？
选中第一个单元格的日期往下拉，填充完之后在自动填充选项中选中按照工作日填充功能。

1.4快手美化，一目了然的条件格式
如何让数据变得突出、直观、形象？
条件格式-数据条-渐变填充/实心填充
如何制作数据旋风图？

条件格式-数据条-渐变填充/实心填充，然后条件格式-管理规则-编辑规则-条形图方向：一个从右往左，一个相反方向从左往右
数据的排序？
选中一列，然后排序-升序/降序/自定义排序
如何将销量低于平均值或者前三名的数据高光显示？
选中一列，然后条件格式-项目选取规则
表格美化神器“表格样式”怎么使用？
选中单元格，然后表格样式，就可以给表格美化样式
如何将重复的数据高光显示？
选中一列，然后条件格式-突出显示单元格规则

1.5快手粘贴，你不知道的复制粘贴
如何原封不动的拷贝原表(包括列宽)?
先选中要复制的内容，粘贴过去之后，右键-选择性粘贴-仅粘贴列宽
如何将列多行少的表格进行90度转过来？这样做方便分析
选中原表格，使用转置功能即可

2.图表制作，数据1秒可视化
2.1快速入门
基础线状图：柱形图、条形图、雷达图、折线图、饼图
如何绘制一张图表？
第一步：选表格，选中数据，插入-全部图表-选择合适的表格类型
第二步：选样式，选中表，图表工具-样式更改
第三步：选颜色，选中表，更改颜色
如何更改图表的元素样式？
左键选中图表-右边出现一列图表元素-更改各种样式
2.2柱形图

簇状柱形图：适用于1到2个数据系列的比较，侧重数据绝对值的比较
堆积柱形图：把簇状柱形图从肩并肩变成了叠罗汉，把比较的数据加在了一起
百分比堆积柱形图：堆积图的进一步变身，从绝对值的比较变成了百分比的比较
2.3条形图
条形图：就是躺下来的柱形图，用于1-2个数据系列的比较，如果碰到横坐标名称太长，就可以用条形图来规避这个问题

簇状条形图：适用于1到2个数据系列的比较，侧重数据绝对值的比较
堆积条形图：把簇状柱形图从肩并肩变成了叠罗汉，把比较的数据加在了一起
百分比堆积条形图：堆积图的进一步变身，从绝对值的比较变成了百分比的比较
2.4雷达图
雷达图：侧重于从宏观角度体现数据对比，也叫"蜘蛛图"

带数据标记的雷达图：和基本雷达图差不多，在数字节点上用圆圈强调
填充雷达图：把中心区域用色块填充
2.5专业图表

如何制作专业图表？
一.选表格，选中数据，插入-全部图表-组合图，把簇状柱形图改为堆积柱形图
二.美化，修改标题，左对齐，字体修改为微软雅黑(微软雅黑电脑自带，简洁大气)
三.副标题，插入-文本框-横排文本框，字体微软雅黑
四.美化柱子，颜色用对比色/相近色(小技巧：公司logo颜色)
五.修改折线颜色,要突出颜色，体现折线处的数据，选中折线-图表元素-数据标签-居上方
六.图例位置改变到左上方
七.脚注区，一般放数据来源，指标的注解或说明。插入-文本框-横排文本框，字体微软雅黑
八.图表背景颜色，选一个浅色，因为白色比较刺眼

3文件关联，excel快速协作其他文件(自动更新数据)
3.1Word、PPT怎么样与Excel联动？
和PPT关联：先选中excel中的表格或图表，复制-再到ppt中，开始选项卡中选中选择性粘贴，选择粘贴链接，Microsoft Excel对象
和Word关联：同上
在word或者ppt中可以修改数据吗，会产生联动吗？
可以，只要文件之间产生了链接关系就可以修改，双击表格或者图片，会跳到excel可以修改数据
注意点
作为数据源的excel文件名、目录、删除或修改就会导致链接失效
解决方案：文件-信息-编辑指向文件的链接,重新指向新的excel文件就行了
3.2excel多表合并
图片、PDF转为文字或者表格，此类功能wps需要会员

4.五类函数，搞定职场90%问题
excel公式是一组以"="开头的运算。
计算运算符：+ - * / ^ % 加减乘除幂百分乘
比较运算符：> < >= <= <> 大于小于 大于等于 小于等于 不等于
单元格引用：
行列都不固定是相对引用，如A2
行列都固定，加入了$号，像船锚，就是绝对引用 ，如$A$2
只固定行或者只固定列就是混合引用，如A$2、$A2
4.1常见函数
条件判断：IF函数。
例：
目的：判断成绩所属的等次。
方法：
1、选定目标单元格。
2、在目标单元格中输入公式：=IF(C3>=90,“优秀”,IF(C3>=80,“良好”,IF(C3>=60,“及格”,“不及格”)))。
3、Ctrl+Enter填充。
解读：
IF函数是条件判断函数，根据判断结果返回对应的值，如果判断条件为TRUE，则返回第一个参数，如果为FALSE，则返回第二个参数。

条件求和：SUMIF、SUMIFS函数。
目的：求男生的总成绩和男生中分数大于等于80分的总成绩。
方法：
1、在对应的目标单元格中输入公式：=SUMIF(D3:D9,“男”,C3:C9)或=SUMIFS(C3:C9,C3:C9,">=80",D3:D9,“男”)。

解读：
1、SUMIF函数用于单条件求和。暨求和条件只能有一个。易解语法结构为：SUMIF（条件范围，条件，求和范围）。
2、SUMIFS函数用于多条件求和。暨求和条件可以有多个。易解语法结构：SUMIFS（求和范围，条件1范围，条件1，条件2范围，条件2,……条件N范围，条件N）。

条件计数：COUNTIF、COUNTIFS函数。
目的：计算男生的人数或男生中成绩>=80分的人数。
方法：
1、在对应的目标单元格中输入公式：=COUNTIF(D3:D9,“男”)或=COUNTIFS(D3:D9,“男”,C3:C9,">=80")。

解读：
1、COUNTIF函数用于单条件计数，暨计数条件只能有一个。易解语法结构为：COUNTIF(条件范围，条件).
2、COUNTIFS函数用于多条件计数，暨计数条件可以有多个。易解语法结构为：COUNTIFS（条件范围1，条件1，条件范围2，条件2……条件范围N，条件N）。

数据查询：VLOOKUP函数。
目的：查询相关人员对应的成绩。
方法：
在目标单元格中输入公式：=VLOOKUP(H3,B3:C9,2,0)。

解读：
函数VLOOKUP的基本功能就是数据查询。易解语法结构为：VLOOKUP（查找的值，查找范围，找查找范围中的第几列，精准匹配还是模糊匹配）。

逆向查询：LOOKUP函数。
目的：根据学生姓名查询对应的学号。
方法：
在目标单元格中输入公式：=LOOKUP(1,0/(B3:B9=H3),A3:A9)。

解读：
公式LOOKUP函数的语法结构为：LOOKUP（查找的值，查找的条件，返回值的范围）。本示例中使用的位变异用法。查找的值为1，条件为0。根据LOOKUP函数的特点，如果 LOOKUP 函数找不到 lookup_value，则该函数会与 lookup_vector 中小于或等于 lookup_value 的最大值进行匹配。

查询好搭档：INDEX+MATCH 函数
目的：根据姓名查询对应的等次。
方法：
在目标单元格中输入公式：=INDEX(E3:E9,MATCH(H3,B3:B9,0))。

解读：
1、INDEX函数：返回给定范围内行列交叉处的值。
2、MATCH函数：给出指定值在指定范围内的所在位置。
3、公式：=INDEX(E3:E9,MATCH(H3,B3:B9,0))，查询E3:E9中第MATCH(H3,B3:B9,0)行的值，并返回。

提取出生年月：TEXT+MID函数。
目的：从指定的身份证号码中提取出去年月。
方法：
1、选定目标单元格。
2、输入公式：=TEXT(MID(C3,7,8),“00-00-00”)。
3、Ctrl+Enter填充。

解读：
1、利用MID函数从C3单元格中提取从第7个开始，长度为8的字符串。
2、利用TEXT函数将字符的格式转换为“00-00-00”的格式，暨1965-08-21。

计算年龄：DATEDIF函数。
目的：根据给出的身份证号计算出对应的年龄。
方法：
1、选定目标单元格。
2、输入公式：=DATEDIF(TEXT(MID(C3,7,8),“00-00-00”),TODAY(),“y”)&“周岁”。
3、Ctrl+Enter填充。

解读：
1、利用MID获取C3单元格中从第7个开始，长度为8的字符串。
2、用Text函数将字符串转换为：00-00-00的格式。暨1965-08-21。
3、利用DATEDIF函数计算出和当前日期（TODAY（））的相差年份（y）。

中国式排名：SUMPRODUCT+COUNTIF函数。
目的：对成绩进行排名。
方法：
1、选定目标单元格。
2、在目标单元格中输入公式：=SUMPRODUCT((C$3:C$9>C3)/COUNTIF(C$3:C$9,C$3:C$9))+1。
3、Ctrl+Enter填充。

解读：公式的前半部分(C$3:C$9>C3)返回的是一个数组，区域C$3:C$9中大于C3的单元格个数。后半部分COUNTIF(C$3:C$9,C$3:C$9)可以理解为：*1/COUNTIF(C$3:C$9,C$3:C$9),公式COUNTIF(C$3:C$9,C$3:C$9)返回的值为1，只是用于辅助计算。所以上述公式也可以简化为：=SUMPRODUCT((C$3:C$9>C3)*1)+1。