转自：Youtube - PaPaYa
练习课件：素材
【全套】Excel零基础入门进阶到函数，Excel自学教程从小白到高手超详细实操教程
Excel
单元格使用技巧:
显示今日日期Alt+;
一列中持续写入重复内容可Alt+箭头向下
选中想重复一列货币格式符号用格式刷
单元格线条:开始→绘制边框网格→下拉选择 （清楚线条按shift） Esc退出画笔
数值运算
=A1*B1（输入=点击运算的单元格*重复一遍后按回车）
一列/范围想加:＝SUM(M1,M2) 或按ctrl选中特定数字
分割视窗
视图→选择要冻结的部分下一格后→冻结窗格
对比月份同理选择拆分即可
资料排序
开始→排序和筛选（自定义排序方式）
资料筛选
开始→排序和筛选→筛选（取消√选择要显示的物品）（文本筛选）
格式化表格
插入→左上角表格→出现设计→表格样式&插入切片器
设定格式化表格的条件（颜色标记）
开始→条件格式（整列全选）
工作表设定&合并
底部加号→增加表格
复制工作表→按住ctrl拖动鼠标左键
移动表格→右键
多表想加→数据→合并计算
图表制作
图表
数字右键可更改
更换背景图→柱形图油漆桶→图片或材料填满
打印分页设定
视图→分页预览→拖动虚线→选择整行后加分隔符
页面布局→每页打印标题
特定行列框选→打印区域
页首页尾&表格加水印
视图→左上角整页模式
显示页码:设计→左上角页码&总页数（斜杠隔开）
增加logo:设计→图片→设定图片格式
浮水印:新增一张空白表→插入→右上角文字→艺术字→复制到word/ppt另存图片→插入图片（调整高度可用Enter）
函数
加总:常用→右上角自动加总（=SUM（C3:C6） 其中:即为引数）
大小:＝LARGE/SMALL（C3:C6,数字）
逻辑函数IF
点击自设函数IF→选择判断单元格例如81（C7）→Logical_test:C7＞＝60→Value_if_true:及格→
Value_if_false:不及格
多个判断同理IFS→Logical_test:C7＞＝90
→Value_if_true:A...Logical_test2:XXX
注意:不可70＞XXX＞80
单元格显示颜色:开始→条件格式→突出显示单元格规则→等于→设置颜色
VLOOKUP函数&绝对参照设定
选择该函数→lookup_value:指定单元格即关键字→table_array:框选范围单元→clo_index_num:该行某个:2（栏数）
range_lookup:False 注意:只可递增查询
绝对参照:在公式上按F4锁定（N/A）
注意:下拉显示其余需锁定关键字和查询范围
同时修改对应栏数
IFERROR函数&资料验证
IFERROR（要检查的公式，公式错误的讯息）
不重复显示只留下一个错误讯息，其余为引号即可
空白学号对应空白资料:
IF（C3＝“”，“”，IFERROR（VLOOKUP...），“查无此人”）
自定义验证:资料→资料验证
COUNTIFS&SUMIFS 函数应用
＝COUNTA（）
＝COUNTIF（资料范围，条件）（文字的话加“”）
嗯
＝COUNTIFS（C2:C14,F2,D2:D14,“>1000”）
其中“>1000” 可以写成“>”&G2
＝COUNTIFS（C2:C14,G2,D2:D14,H2）
其中H2即为日期A2新增所属月份栏
＝MONTH（A2）即截取日期（5/2）内月份（5）
＝SUMIFS（E2:E14,C2:C14,G2,D2:D14,H2）
定义名称&INDIRECT函数
公式→中间定义名称
＝SUM（金额）
数据→资料验证→设置下拉框
公式→根据所选内容创建
然后资料验证→设定来源→＝INDIRECT（＄B＄3）
小技巧
将内容挤在同一个列单元格分离出来→数据→分列→分隔符号→勾选空格
姓氏分离→同理固定宽度→将分割线放在姓氏中间
ctrl+shift+空格→全选资料单元格
ctrl+shift→选择行单元格 ↓选择列单元格
单元格左上角方块→全选整页单元格
ctrl+/ctrl－增加减少一行单元格
ctrl+拖动鼠标→复制一行单元格
shift+拖动鼠标→插入到两行单元格之间
数据→删除重复值
转置表格→右键复制→选择性粘贴→转置
资料同时加上数值→例如数值加5→复制5→框选区域单元格选择性粘贴→运算选加
ctrl+～→显示公式
数据→模拟分析→选择指定单元格→单变量求解
表格对角线→开始→绘制边框
（鼠标右键→设置单元格格式→边框）（同一单元格换行→Alt+enter）
注解→右键输入批注（插入图片注解→先删除内容再右键设置批注格式）
表格数值格式
百分之1/2 前加0和空格 否则显示为日期
要显示001而不是1→‘单引号加数字（或开始→文本）
开始→数字→自定义→加#即为整数（手机号前为0也则输入000－0000000）
@“等”代表储存格内文字
*重复指定符号@*.
＄ 100而不是＄100可＄*空格0 （其中0代表数值）
,代表“千”例如＄0,“K” 显示 33K
0.00正值,（0.00负值）,0.00零值，@文字
_代表留白即希望格式对齐
颜色区分即前面［蓝色］0.00_）（也可以指定［＞90］为蓝色）
;代表隐藏
时间格式&工龄与工时计算
[DBNum1]1变为一 aaaa 星期几
超过预设单元格双击左键拉开
ctrl+;（固定）/=TODAY()（实时更新）显示当前日期
ctrl+shift+;/=NOW()（实时更新）显示当前时间
hh:mm:ss秒
［h］:mm加［］代表累计已过小时
=TODAY()-H3(就职日期)
=DATEDIF(开始，结束，计算单位)
=DATEDIF(H3,TODAY(),“y”)
MD两日之间天数忽略月年
YM两日之间月数忽略日年
YD两日之间天数忽略年数
计算轮班天数:
=NETWORKDAYS(L3,M3,P3:P4) 其中P3:P4为休假
进阶:=NETWORKDAYS.INLT(L3,M3,1,P3:P4)
周末参数取值:
1:星期六日
2:星期日一
3:星期一二
4:星期二三
5:星期三四
6:星期四五
7:星期五六
11:仅星期日
12:仅星期一
13:仅星期二
14:仅星期三
15:仅星期四
16:仅星期五
17:仅星期六
计算成绩排名:即号码不变，成绩排序
=RANK.EQ(D3(指定),D3:D10(比较范围)，排序方式)
注意:使用上面公式拖动+下拉重复使用公式，需应用绝对参照(全选范围后按F4)，否则指定和比较范围同时下移一位 例如:D4,D4:D10
如果排名按升序则在比较范围后,加非0的数，降序0或不填
用LEFT函数提取表格中的文字资料:
Part.1
=LEFT(资料位置，抓取字数)
同理RIGHT一样
=MID(资料来源，开始位置，抓取字数)
=FIND(“要搜寻的文字”，资料来源,搜寻起点（可选）)
=LEN(全选资料统计字数)
Part.2
混合作用抓取内容:
抓取羽绒外套:
eg.羽绒外套－女－M
=LEFT(资料位置，抓取字数)
↓
抓取字数填FIND(“要搜寻的文字”，资料来源)
例:=LEFT(B3,FIND(“-”,B3)－1)
抓取性别:
=MID(资料位置，开始位置，抓取字数)
↓
开始位置填FIND(“要搜寻的文字”，资料来源)
=MID(B3,FIND(“－”,B3)+1,1)
抓取M:
FIND(“要搜寻的文字”，资料来源)
↓
FIND(“要搜寻的文字”，资料来源，搜寻起点)
RIGHT(B3,LEN(B3)-FIND(“－”,B3,FIND(“－”,B3)+1))
INDEX&MATCH－VLOOKUP:
HLOOKUP(被查询值，查询的范围，要传回的行数)
取得顺位:INDEX(列/行范围，第几位)
进阶INDEX(资料范围，列数，行数) 确定数为框选后末端角的数
查找顺位:MATCH(查找的对象，查找的范围(单行或列)，比对方式(0为完全符号))
INDEX(范围，行数)
↑
MATCH(对象，范围，大致符号)
指定两个单元格:创建资料验证，下拉选择菜单
查找对象即选指定单元格
当选择菜单某个内容INDEX中行或列相应跟着变化
INDEX(C3:F10,MATCH(I2,D3:D10,0),MATCH(H3,C2:F2,0))
保护表格:
右键→储存格格式→保护→取消勾选锁定
审阅→保护工作表/多表保护/部分允许编辑
加密表格文件:①档案→咨询→保护活页簿→以密码加密
②另存，右下角(保存健旁边)工具→一般选项
重复检查:
例如:空白格上输入:=A2&B2&C2&D2然后下拉重复→设定格式化→醒目提示→重复的值 混合 COUNTIF:框选范围→设定格式化→新增规则→使用公式→=COUNTIF(辅助列资料范围，指定＄去掉即＄E2)>1→格式→填满标签→隐藏辅助列
移除重复:数据→数据工具→移除重复项
资料重复验证:数据→数据验证→设定→自订→=COUNTIF(A:A,A1)=1 同时可以增加错误提示
随机函数:
=RANDBETWEEN(最小值，最大值)
=INDEX(C3:C14,RANDBETWEEN(1,12))
INDEX(资料范围，顺位)
按F9重新抽取
=CHOOSE(RANDBETWEEN(1,2),“A卷”，“B卷”)
分组:RAND不重复
排名函数:RANK(自己，比较的对象) 注意:F4锁定
=CHOOSE（ROUNDUP(RANK(D3,＄D＄3:＄D＄14)/6,0),“A组”，“B组”）
组别不变动→全选→右键选择性贴上→值
进度追踪表:
视图→隐藏网格线→开始→下框线→绘制框线格式→线条颜色→ESC
空白区→自订功能区→开发工具→表单控件（√）→鼠标左键填满→右键→设置控件格式→控制→指定单元格显示→重复一列→开始→条件格式→图示集→套用IF(判断问题(A4)，yes(1),no(0))→条件格式→选中单元格→管理规则→编辑规则→类型选数字&只显示图示→进度
今天日期=TODAY()
进度:=IF（A4=TRUE,1,IF（＄C＄3>=G3,0,win+;叫出emoji）注意:“符号”）
布林逻辑:
类比IF: IF（E3>=1000,TRUE,FALSE）
=E3>=1000
=C3=“有”
=AND（D3>=5,E3>=1000）
=OR（C3=“有”,E3>=1000）
=OR（C3=“有”,AND(E3>=1000,D3>=5)
=IF（OR（C3=“有”,AND(E3>=1000,D3>=5)）,“可胜利”，“条件不符”）
复制OR（C3=“有”,AND(E3>=1000,D3>=5)）→框选范围单元格→条件格式→新增规则→使用公式→=OR（＄C3=“有”,AND(＄E3>=1000,＄D3>=5)） →格式→填满颜色→符合条件小时色彩
SUMPRODUCT:数量×单价
=SUMPRODUCT（C3:C8,D3:D8）
加权平均:=SUMPRODUCT（C3:C6,D3:D6）/SUM（D3:D6）
=SUMPRODUCT（--（C3:C9=“有”））或=SUMPRODUCT（（C3:C9=“有”）*1）
=SUMPRODUCT（（C3:C9=C11）*（D3:D9=D11）*可选）
内测工具——简单公式: 普通版本下列函数不可用
插入→表格框选→=SUMIF(表1[列2]，指定#，表1[列4]) 注意:加#可选中整列，新增时不用下拉粘贴
FILTER(资料范围，筛选条件)
百分比:=C5/SUM(＄C＄5:＄C＄12)
网页表格抓取:
复制网页地址→数据→自网站
