excel职场培训课件

职场培训课件Excel概览与职场价值Excel作为全球最受欢迎的电子表格软件，已成为现代职场的必备工具Excel全球以上企业将作为标准办公工具•80%Excel几乎所有行业都将用于数据分析和报表制作•Excel熟练掌握已成为职业发展的关键技能•Excel从数据录入到高级分析，贯穿职场工作的各个环节•Excel界面基础Excel菜单区与功能区工作表结构常用快捷键菜单区位于窗口顶部，包含文件、开始、工作表由行（数字标识）和列（字母标识）组成Excel插入等选项卡功能区显示与当前选项卡相关的的网格单元格是行列交叉点，通过坐标（如所有工具和命令，采用图标式设计，便于快速找）定位底部标签页可切换不同工作表A1到所需功能工作簿与工作表管理基本操作高效管理技巧新建工作簿文件新建或合理分区提升效率•Ctrl+N添加工作表点击按钮或右键菜单•+按数据类型分表原始数据、计算结果、图表展示•重命名工作表双击标签或右键重命名•按时间周期分表月度、季度、年度•移动工作表拖拽标签或右键移动复制•/按部门功能分表销售、库存、财务•/删除工作表右键标签选择删除（谨慎操作）•使用工作表组进行批量操作•工作表颜色标记右键标签选择颜色•单元格与区域操作单元格格式设置批量输入与编辑区域定义与引用数字格式（货币、百分比、日期）自动填充（拖拽、）相对引用（）绝对引用（）Ctrl+D/R A1vs$A$1字体、大小、颜色、边框序列填充（数字、日期、文本）混合引用（）$A1,A$1对齐方式与文本控制多选单元格同时输入（）名称管理器定义常用区域Ctrl+Enter条件格式（数据条、色阶）查找替换（）结构化引用（表格引用）Ctrl+F/H数据录入规范有效性设置与下拉列表批量填充技巧数据数据验证，可设置拖拽填充数字序列、日期序列、自定义列表•快速填充（）智能识别模式•Flash Fill数值范围限制（最小最大值）•/文本分列将合并数据拆分为多列•日期范围限制（在指定期间内）•去除重复项数据删除重复项•文本长度限制（如手机号位）•11自定义公式验证（复杂条件）•下拉列表选项（减少输入错误）•错误提示与输入信息可自定义，帮助用户正确录入排序与筛选基础排序操作基础筛选功能单列排序点击列头部箭头自动筛选数据筛选••多列排序数据排序，添加多级规文本筛选包含、开始于、结束于••则数值筛选大于、小于、等于、前项•10自定义排序设置自定义列表（如季度）•日期筛选本月、上季度、特定时间段•按颜色或图标排序基于条件格式结果•高级筛选技巧数据高级筛选复杂条件组合•自定义筛选条件区域设置•结果提取到指定位置•使用通配符和模糊匹配•*筛选唯一记录消除重复项•数据清洗案例批量去重处理文本拆分与错误值处理客户数据库去重流程使用函数提取手机号码区号•FIND/MID分列功能拆分姓名、地址信息排序数据以便目视检查相似项•

1.函数处理计算错误使用条件格式标记重复值•IFERROR

2.数据透视表汇总前的数据净化数据删除重复项，选择关键列•

3.函数检测特定列的重复次数

4.COUNTIF合并相似但不完全相同的记录

5.案例客户名录中存在多种变形（如张

三、张

三、张三先生）的处理方法条件格式应用突出显示单元格规则数据条与色阶销售数据异常标红案例根据条件自动标记单元格直观显示数值大小和分布设置条件格式自动发现异常大于小于指定值的数据数据条长度表示数值大小销售额低于目标的单元格标红•/••包含特定文本的单元格色阶颜色深浅表示数值大小增长率负值使用红色数据条•••重复值或唯一值标记图标集使用不同图标表示范围库存低于安全水平的产品高亮•••今天本周本月日期突出显示自定义规则调整显示效果•//•数据有效性与保护输入规范限制锁定单元格与表保护通过数据有效性功能设置规范限制输入类型整数、小数、日期、时间•设定数值范围最小值最大值•/创建下拉列表固定选项减少错误•自定义公式验证复杂业务规则•输入提示与错误信息用户友好提醒•案例采购单中的数量必须为正整数，单价在市场区间内，供应商从预设列表选择选择需要保持可编辑的单元格

1.右键设置单元格格式保护取消锁定选项

2.审阅保护工作表设置密码

3.选择允许的操作（如仅在解锁区域输入）

4.文件保护文件信息保护工作簿

5.数据透视表详解数据透视表基础字段布局与应用数据透视表是最强大的分析工具之一行区域分类数据（产品、地区）Excel•列区域交叉分类（月份、部门）快速汇总大量数据，无需复杂公式••值区域汇总数据（销售额、数量）动态调整分析维度，灵活探索数据••筛选区域控制显示范围筛选、分组、排序一站式完成••可视化展示结果，直观呈现洞察•创建步骤选择数据源插入数据透视表拖拽字段到四个区域数据图表快速创建条形图折线图饼图适用场景适用场景适用场景各部门业绩对比月度销售趋势市场份额分布•••产品销量排名股价波动分析预算分配比例•••预算与实际对比温度等连续数据人员构成分析•••创建选择数据插入推荐图表条形图创建选择数据插入折线图可添加标记创建选择数据插入饼图可添加标签和点分离扇区基础公式与算术运算函数函数函数SUM SUBTOTALAVERAGE语法数字数字语法函数代码区域区域语法数字数字=SUM1,

[2],…=SUBTOTAL,1,

[2],…=AVERAGE1,

[2],…用途计算一组数值的总和用途计算筛选后的数据，忽略隐藏行用途计算一组数值的平均值示例计算到单元格的示例计算筛选后示例计算到单=SUMA1:A10A1A10=SUBTOTAL9,A1:A100=AVERAGEC5:C15C5C15总和的总和元格的平均值技巧可结合条件使用，如函数代码注意忽略文本值，但计入值为的单元格=SUMIFB1:B10=1=AVERAGE,2=COUNT,9=SUM,0已完成,A1:A10,011=STDEV运算符与优先级算术运算符运算优先级加法括号•+

1.减法幂运算•-

2.^乘法乘法和除法•*

3.*/除法加法和减法•/

4.+-幂运算连接运算符•^

5.百分比•%逻辑与文本函数逻辑函数文本函数函数逻辑测试为真值为假值例优秀一般IF=IF,,=IFD5100,,嵌套条件值条件值值例优良IF=IF1,1,IF2,2,3=IFA190,,IFA175,及格不及格,IFA160,,函数逻辑逻辑例通过不通过AND=AND1,2,...=IFANDA160,B160,,函数逻辑逻辑例缺勤迟到扣分正常OR=OR1,2,...=IFORA1=,A1=,,函数逻辑例正常值异常值NOT=NOT=IFNOTA1100,,从左右提取指定字符数提取前个字符LEFT/RIGHT/=LEFTA1,3A13从指定位置提取字符从第位开始提取个字符MID=MIDA1,2,3A123连接文本连接、空格和CONCATENATE=CONCATENATEA1,,B1A1B1删除多余空格清除中的多余空格TRIM=TRIMA1A1查找与引用函数函数VLOOKUP语法查找值查找区域返回列索引模糊匹配=VLOOKUP,,,[]案例订单号订单表根据订单号查找第列的发货日期=VLOOKUP,,5,FALSE5注意事项查找值必须在表格第一列•表示精确匹配，表示近似匹配•FALSE TRUE返回列索引从开始计数•1函数HLOOKUP语法查找值查找区域返回行索引模糊匹配=HLOOKUP,,,[]案例产品编号产品表根据产品编号查找第行的库存量=HLOOKUP,,3,FALSE3适用于查找值在表格第一行的横向查找场景函数XLOOKUP语法查找值查找数组返回数组未找到时=XLOOKUP,,,[]案例员工列薪资列未找到根据员工查找对应薪资=XLOOKUP ID,ID,,ID优势查找值可在任意位置•可向左查找（不能）•VLOOKUP支持精确、模糊、通配符匹配•组合INDEXMATCH语法返回区域查找值查找区域=INDEX,MATCH,,0案例客户灵活查找客户信息=INDEXC2:C100,MATCH ID,A2:A100,0日期时间函数实战基础日期函数日期计算函数时间函数返回当前日期开始日期结束日期单位计算日期差时分秒构建时间•TODAY•DATEDIF,,•TIME,,返回当前日期和时间开始日期天数计算工作日时间提取小时•NOW•WORKDAY,•HOUR年月日构建日期开始日期结束日期计算工时间提取分钟•DATE,,•NETWORKDAYS,•MINUTE作日天数日期提取年份时间提取秒•YEAR•SECOND日期月数返回指定月数后的月末日期提取月份•EOMONTH,计算时间差小时制计算•MONTH•24日期日期提取日•DAY实际应用案例工时计算项目周期离职率计算计算员工加班时间计算项目已进行天数与剩余天数计算员工工作年限=IFB2-A2TIME8,0,0,B2-A2-TIME8,0,0*24,0=DATEDIF项目开始日期,TODAY,d=DATEDIF入职日期,TODAY,y年DATEDIF入职日期,TODAY,ym个月为上班时间，为下班时间，计算超过小时的加班A2B28=DATEDIFTODAY,项目截止日期,d时间结合条件格式标记即将到期的项目数学与统计函数应用计数函数条件汇总函数其他常用函数计算数值的个数条件求和销售部最大值最小值COUNT=COUNTA1:A100SUMIF=SUMIFA1:A10,MAX/MIN/=MAXA1:A50/,B1:B10=MINA1:A50计算非空单元格数多条件求和第大小的值COUNTA=COUNTAB1:B50SUMIFS=SUMIFSC1:C10,A1:A10,LARGE/SMALL n/=LARGEB1:B20,3销售部第大的值,B1:B10,50003计算空单元格数四舍五入数值小数位数COUNTBLANK ROUND=ROUND,条件平均值=COUNTBLANKC1:C20AVERAGEIF=AVERAGEIFD1:D20,合格条件计数,E1:E20向上下舍入COUNTIF=COUNTIFD1:D30,=60ROUNDUP/ROUNDDOWN/多条件平均值结果为AVERAGEIFS=ROUNDUP

123.456,

1123.5多条件计数男=AVERAGEIFSF1:F20,G1:G20,2023,H1:H20,COUNTIFS=COUNTIFSE1:E10,一季度,F1:F10,=30信息、错误处理函数信息函数错误处理函数ISBLANK检查单元格是否为空=ISBLANKA1返回TRUE/FALSEISNUMBER检查是否为数字=ISNUMBERB2返回TRUE/FALSEISTEXT检查是否为文本=ISTEXTC3返回TRUE/FALSEISFORMULA检查是否为公式=ISFORMULAD4返回TRUE/FALSEISLOGICAL检查是否为逻辑值=ISLOGICALE5返回TRUE/FALSECELL返回单元格信息=CELLformat,A1返回格式信息ISERROR检查是否为任何错误=ISERRORA1返回TRUE/FALSEISERR检查是否为除#N/A外的错误=ISERRB2返回TRUE/FALSEISNA检查是否为#N/A错误=ISNAVLOOKUP...返回TRUE/FALSEIFERROR替换错误值=IFERROR公式,错误时显示的值IFNA仅替换#N/A错误=IFNAVLOOKUP...,未找到数据清洗自动修正案例区域与名称管理区域引用技巧命名管理器使用使用键循环切换引用类型•F4•A1→$A$1→A$1→$A1→A1相对引用复制时会随位置变化•绝对引用复制时保持不变•混合引用行或列保持固定•动态区域创建自动扩展区域•OFFSET+COUNTA区域引用与公式交互示例=SUMPRODUCT部门列=销售部*月份列=1月*销售额列通过区域判断条件，实现多条件筛选计算创建与管理名称公式名称管理器为常用区域创建名称（如销售数据、产品列表）

1.为常用公式创建名称（如销售税率、提成比例）

2.基于行列标题自动创建名称选择数据创建基于所选内容创建

3./在公式中使用名称替代单元格引用

4.使用函数动态引用名称

5.INDIRECT公式调试与追踪审阅工具使用错误追踪与排查公式评估器使用技巧提供多种审阅工具帮助调试公式常见错误类型及解决方法逐步评估复杂公式Excel公式公式审核追踪箭头类型不匹配，检查文本数值选择含公式的单元格

1.•#VALUE!/

1.显示公式的输入单元格（蓝色箭头）引用无效，检查删除的单元格公式公式审核公式求值

2.•#REF!

2.显示公式的输出单元格（红色箭头）除数为零，添加错误处理逐步计算公式的每个部分

3.•#DIV/0!

3.可视化呈现公式依赖关系名称错误，检查函数拼写识别哪一步出现问题

4.•#NAME

4.识别循环引用和错误来源未找到值，检查查找参数特别适合嵌套函数调试

5.•#N/A

5.高阶数据透视表多重汇总与计算字段分组与切片器在值区域添加多个计算字段•同一字段的不同汇总方式（求和、计数、平均值）•不同字段组合分析（销量、金额、利润）•创建计算字段数据透视表工具分析字段、项与集计算字段•创建计算项在行列区域添加基于现有项的计算•/使用显示值为创建特殊计算•占总计百分比、累计、排名等•字段分组功能日期分组（年、季、月、日）•数值分组（分段区间）•文本分组（自定义分类）•切片器与辅助列插入切片器直观筛选控件•连接多个数据透视表同时控制•时间轴特殊的日期筛选器•图表美化与动态展示图表设计与格式化数据驱动动态图表业绩趋势精美展示案例专业图表设计的关键点创建随数据变化的动态图表业绩趋势图表优化步骤选择合适的图表类型传达信息使用表格作为数据源使用组合图表柱形折线••

1.+删除冗余元素，保持简洁定义动态命名区域添加目标线与实际对比••

2.使用直观的颜色对比表示数据函数创建自动扩展数据范围使用辅助坐标轴展示不同量级数据••OFFSET

3.添加清晰的标题与标签结合下拉列表实现数据切换添加数据标签突出关键点••

4.调整坐标轴比例更准确呈现数据使用引用不同数据区域••INDIRECT迷你图与条件可视化迷你图（）条件格式可视化Sparklines迷你图是嵌入单元格内的微型图表，可以在有限空间内展示数据趋势插入迷你图选择线条柱形输赢•//适用场景多行数据各自趋势对比•设计技巧•标记高点低点首尾点•//调整颜色突出关键信息•与数值结合展示更全面•案例在销售报表中，每个产品行末添加迷你图，直观展示月度销售趋势使用条件格式创建嵌入式数据可视化色阶（热力图）

1.选择数据条件格式色阶

2.直观展示数值分布与密度

3.数据条

4.选择数据条件格式数据条

5.在单元格内展示数值大小对比

6.自定义规则

7.多表数据关联跨表引用基础引用同一工作簿中的其他工作表工作表名单元格引用=!示例销售数据=!A1三维引用（多表同位置）=SUMJan:Mar!A1跨工作簿引用引用其他工作簿的数据工作簿名工作表名单元格引用=[.xlsx]!注意事项源文件移动会导致引用失效•可使用外部数据链接管理•数据整合技巧将多表数据整合的方法函数动态引用•INDIRECT合并查询•Power Query数据模型与关系建立•合并工作表功能•年度多部门预算整合案例案例描述公司各部门（销售、市场、研发、行政）分别维护各自的预算表，需要在主表中汇总展示使用跨表引用销售部销售部市场部市场部

1.SUMIF=SUMIF!A:A,Q1,!B:B+SUMIF!A:A,Q1,!B:B+...使用动态引用部门名称预算金额

2.INDIRECT=SUMPRODUCTINDIRECT!脚本与自动化简介Office脚本概述操作记录器使用Office脚本是的新功能，可以自动化重复性任务Office Excel无需编程知识，通过录制操作创建•可以保存、编辑和重复运行•支持在网页版和桌面版中使用•Excel可以在组织内共享，提高团队效率•替代了传统宏的部分功能，更安全•适用场景数据清理、格式化、报表生成等重复性工作访问方式自动化录制操作

1.录制步骤

2.点击开始录制

3.执行需要自动化的操作

4.点击停止录制

5.命名并保存脚本

6.记录内容包括

7.数据输入和编辑

8.格式设置

9.脚本编辑器与共享脚本编辑器基础脚本共享与集成Office脚本使用TypeScript语言，但可以通过图形界面进行简单编辑•访问脚本编辑器自动化所有脚本编辑•编辑器功能•修改已录制的步骤•添加注释说明脚本用途•调整参数和条件•测试运行和调试•高级用户可以直接编写代码修改示例调整格式应用范围、更改筛选条件、添加条件判断常见自动化案例12批量导入与整理数据透视与图表自动化CSV自动化数据处理流程报表生成自动化CSV创建脚本导入文件创建脚本更新数据源

1.CSV

1.自动清理数据（去重、去空格）自动刷新数据透视表

2.

2.标准化格式（日期、数字）应用特定筛选条件

3.

3.拆分或合并列更新图表数据范围

4.

4.添加计算列调整格式与样式

5.

5.案例每日销售数据自动导入、清洗并添加到主数据库案例根据最新数据自动更新销售业绩仪表板3每日报表一键生成端到端报表自动化使用定时触发

1.Power Automate自动收集多个数据源

2.运行脚本处理数据

3.Excel生成标准化报表

4.自动发送邮件或保存到

5.SharePoint职场实战案例Excel销售部门•销售漏斗分析表•客户转化率跟踪•销售业绩仪表板•区域销售对比•佣金自动计算关键技能VLOOKUP查询客户信息，数据透视表汇总销售额，条件格式突出高价值客户采购与库存•库存水平监控•采购订单自动生成•供应商评估矩阵•ABC分析表•安全库存计算关键技能IF函数自动提醒补货，SUMIFS分析不同类别库存价值，INDEX-MATCH查找最优供应商财务部门•预算vs实际对比表•现金流预测模型•费用分析报表•部门成本分摊•财务比率仪表板关键技能财务函数NPV,IRR，数据透视表分析开支，条件格式突出预算差异人力资源•考勤与加班统计•员工评估矩阵•招聘漏斗跟踪•薪资与绩效分析•人员流动预警高级技巧与效率提升批量操作快捷键插件工具与宏概览掌握这些快捷键可大幅提升工作效率空格空格选择整列整行•Ctrl+/Shift+/扩展选择到数据区域末尾•Ctrl+Shift+↓/→快速求和•Alt+=打开格式对话框•Ctrl+1插入当前日期•Ctrl+;插入当前时间•Ctrl+Shift+;编辑单元格•F2单元格内换行•Alt+Enter开启关闭筛选•Ctrl+Shift+L/实用插件推荐Excel数据获取与转换•Power Query数据建模与分析•Power Pivot实用功能集合•Kutools200+高级数据处理工具•XLTools宏简介VBA自动化复杂重复任务•培训总结与答疑学习资源推荐职场提升路径持续学习的渠道技能提升阶梯Excel微软官方帮助中心•Excel基础操作熟练（个月）

1.1-2技巧网、之家•Excel Excel常用函数掌握（个月）

2.2-3站教程视频•B Excel数据分析能力（个月）

3.3-6《数据处理与分析实战》•Excel自动化与高级应用（个月）

4.6+技能认证考试•Excel常见问题解答在线社区学员经常遇到的问题学习与交流社区Excel复杂函数的嵌套使用知乎话题••Excel大数据量文件的性能优化技术论坛••Excel数据透视表的高级应用微信公众号技巧精选••Excel跨工作簿引用失效问题专业微信群••Excel QQ/自动化流程的搭建方法上的开源项目••GitHub Excel。