ARTICLE · 1329401
Excel数据处理高级功能应用实战
点蓝色字关注“内训课”
Excel数据处理高级功能应用实战
【课程目标】
本课程为工具类培训，面向所有想学数据分析工具的人员。
本课程的主要目的是，帮助学员掌握Excel数据分析工具，学会数据预处理，简化工作量，提升效率，避免重复工作；学会数据可视化，直观呈现各种业务信息和含义。
通过本课程的学习，达到如下目的：
1、熟悉Power Query数据预处理操作，能够处理各种数据源
2、掌握透视表，以及高级的统计函数，实现数据汇总和计算
3、掌握Excel可视化功能，及制图技巧，制作美观的图形
4、理解动态图表原理，掌握动态图表的制作
5、熟悉Excel高级函数的应用
6、熟悉模拟运算、规划求解等高级功能
【授课时间】
1-2天时间（每天6个小时）
可以根据具体的需求选择部分课程内容。
【授课对象】
销售部、营业厅、市场营销部、运营分析部、业务支撑部等业务及应用人员。
本课程由浅入深，结合原理主讲软件工具应用，不需要太深的数学知识，但希望掌握数据分析的相关人员。
【学员要求】
1、每个学员自备一台便携机(必须)。
2、便携机中事先安装好Excel 2016版本以上。
注：讲师可以提供分析数据源。
【授课方式】
数据整理 + 分析数据 + 数据呈现 + 工具实践操作
采用互动式教学，围绕业务问题，展开数据分析过程，全过程演练操作，让学员在分析、分享、讲授、总结、自我实践过程中获得能力提升。
【课程大纲】
第一部分：数据分析基础
1、数据分析的六步曲
Ø明确目的、收集数据（需要人员参与）
Ø整理数据、分析数据、数据可视化（可以借助工具）
Ø形成结论（需要人员参与）
2、Excel的主要功能
Ø数据读取、保存、共享
Ø数据预处理（整理、清洗、集成、派生等）
Ø数据计算（汇总、统计、透视表等）
Ø数据可视化（图表）
第二部分：Excel数据处理
1、传统工具最大问题：重复工作
2、数据预处理的四大任务
Ø数据集成、数据清洗、样本处理、变量处理
1、Power Query高级功能
2、多数据源读取
演练：从文本/Excel/数据库/Web页获取数据源
3、数据组合/数据集成
Ø样本追加：横向合并
Ø字段合并：纵向合并/连接类型
Ø表连接的类型（左/右/完全外连接、内连接、左反/右反连接）
演练3：多表合并，实现在职、离职、新入职员工名单
4、数据预处理
Ø文件夹合并
演练1：文件夹下多文件单工作表合并
演练2：文件夹下多文件多工作表合并
Ø多工作表合并
演练1：单文件多个规范工作表合并
演练2：单文件多个不规范工作表合并
3、异常数据处理
Ø重复值处理
Ø缺失值处理
Ø无效值/错误值
Ø离群值处理
4、Power Query数据预处理实战
Ø文本字段拆分列/拆分行（按分隔符、字符数、非数字到数字等）
Ø展开列表为行/分组依据
Ø去重/排序/跳过行/第一行作标题
Ø新增列/新增索引/新增条件列
Ø文本提取/文本移除/文本转数字
演练1：销售人员绩效自动排名
演练2：借贷凭证数据处理
演练3：采购计划处理（多表合并处理）
演练4：文本数字混合计算金额
5、排序和筛选
Ø排序依据、顺序
演练：学历如何排序（自定义顺序）
Ø筛选：简单筛选、复合筛选、自定义筛选
第三部分：Excel条件格式
1、条件格式：根据条件来设置单元格的格式
2、条件规则类型及条件
Ø根据值条件、值排名设置条件
Ø根据内容条件
Ø根据公式自定义条件
3、设置格式
Ø字体、填充、边框、对齐等等
Ø已有样式：数据条、色阶、图标集
演练：单元格自行添加边框
演练：高亮显示选中内容的单元格
演练：自动添加箭头显示同比/环比变化
4、表格美化技巧
Ø突出显示
Ø前后项突出
Ø数据条
Ø色阶
Ø图标集
Ø迷你图
5、自定义格式：<POSITIVE>;<NEGATIVE>;<ZERO>;<TEXT>
6、Text函数改变数据格式
第四部分：Excel高级函数
1、Excel常用函数种类
2、文本函数
a)查找：Find/Search
b)替换：Substitue/Replace
c)提取：Left/Right/Mid/TextBefore/TextAfter
d)合并：Concat/TextJoin/&
e)分割：TextSplit
演练：身份证号提取籍贯、出生日期、性别
演练：住址省市区县乡镇街道等拆分
3、逻辑函数
a)条件判断：IF/IFS
b)逻辑运算：AND/OR/NOT
c)函数计算：Let/Lambda/Map/Reduce/ByCol/ByRow
d)名称管理器
演练：自定义函数及调用
4、信息函数
ØISBLANK/ISERROR/ISNA
ØISNUMBER/ISTEXT/ISFORMULA/ISREF
ØISODD/ISEVEN/ISLOGICAL
5、查找和引用函数
a)值查找：VLookup/HLookup/XLookup
b)序号查找：Match/XMatch
c)条件筛选：Filter
d)提取数据：Take/Drop/ChooseCols/ChooseRows
e)引用函数：Index/Offset/Indirect
演练：销量数据查找
演练：销量数据筛选
演练：下拉菜单、多级下拉联动菜单
6、统计函数
a)计数：Count/CountA/CountBlank/CountIF/CountIFS
b)均值：Average/AverageIF/AverageIFS
c)极值：Max/Min/Large/Small
d)分类统计：GroupBy/PivotBy
演练：销量统计、分类统计
7、数学和三角函数
a)汇总：Sum/SubTotal
b)条件汇总：SumIF/SumIFS/SumProduct
8、日期和时间函数
a)时间提取：Year/Month/Day/Hour/Minute/Second/Weekday
b)时间间隔：YearFrac/Days/Networkdays/Workday
9、正则表达式
演练：电话号码提取、费用数字提取
10、分级显示:组合/取消组合
第五部分：Excel模拟运算
1、单变量求解
2、模拟运算表
3、方案管理器
第六部分：Excel规划求解
1、规划求解工具介绍
a)求取极值的最优输入
b)约束条件使用
c)全局最优及局部最优
2、如何实现产品最优定价
演练：利润最大化下的产品生产与分配
演戏：成本最小化下的产品组成配比
第七部分：Excel数据分析
1、数据统计常用工具
Ø透视表
Ø数据分析
2、透视表原理与操作技巧
Ø汇总方式
Ø值显示方式：无、百分比、差异等
Ø创建动态透视表
3、透视表高级功能
a)三种布局
b)值显示方式：百分比、排名等
c)总计/小计的显示
d)合并类别项
e)分层分级透视表
演练：销售分析报表
4、数据分析
Ø描述统计
Ø相关分析（原因分析）
Ø方差分析（影响因素分析）
Ø回归分析（多因素关键探索与预测）
演练：影响消费水平的因素分析
演练：影响销量的关键要素分析
第八部分：Excel可视化
1、数据呈现的两种格式：图与表
2、常用图形类型及选择原则
Ø对比：柱状图、条形图
Ø趋势：折线图
Ø分布：直方图、箱线图
Ø结构：饼图、瀑布图
3、常用图表画图技巧
Ø柱状图的画图原则
Ø直方图的画图原则
Ø饼图的画图原则
Ø折线图的画图原则
Ø……
演练：基本图形绘制(柱状图、饼图、折线图、直方图、散点图/气泡图/象限图)
演练：复杂图形绘制(双坐标图、漏斗图、雷达图)
4、图形美化原则
第九部分：Excel动态图表
1、动态图表的实质
2、动态图表实现方式
a)筛选器、切片器（单行、复选）
b)动态钻取
c)函数实现（OFFSET/INDEX/MATCH/VLOOKUP/CHOOSE/IF）
d)控件（单行、复选、下拉框等）
e)定义名称
演练：贷款金额计算及模拟运算
3、名称管理器
结束：课程总结与问题答疑。
讲师：傅一航
傅一航，大数据专家。
计算机软件与理论硕士研究生，华为工作十年，五项国家专利，在华为工作期间获得华为数项奖项，曾在英国、日本、荷兰等海外市场做项目，对大数据技术有深入的研究。
傅老师专注于大数据分析与挖掘、机器学习等应用技术，以及大数据系统部署解决方案。旨在将大数据的数据分析、数据挖掘、数据建模应用于行业及商业领域，解决行业实际的问题。
1、让管理更高效：将大数据应用于企业管理，用大数据探索企业发展规律和行业发展趋势，有效预判市场变化和需求，基于规律和预判来进行管理决策，并实现组织架构演变、人才新技能培养、生产流程优化，以及服务效率提升，最终匹配市场未来的变化需要，提升企业管理效率。
2、让决策更科学：将大数据应用于运营决策，用大数据呈现企业整体经营状况，诊断运营问题和风险，找到业务短板，全面理解组织、产品、人员、营销、财务等要素间的相关性，实现企业资源的最优化配置，提升科学决策能力。
3、让营销更精准：将大数据应用于市场营销，解决营销中的用户群细分和品牌定位，客户价值评估，分析用户需求，产品设计优化，产品最优定价等实际问题，实现精准营销和精准推荐，以最小的营销成本实现最大化的营销效果。
出版书籍
《商业分析思维与实践--用数据分析解决商业问题》，北京大学出版社，2024年1月上架。
课程特色
傅老师目前致力于将大数据技术应用于通信、金融、电商、互联网、制造业、政府等领域。傅老师的课程最大特色：实战性强！“围绕业务问题+搭建分析框架+运用分析方法+建立分析模型+熟悉分析工具+形成业务策略”。以商业目标为起点，基于实际的业务应用场景（明确目的），搭建全面系统的业务框架和分析维度（分析思路），选择最合适的方法（分析方法），深入浅出的理论讲解（分析模型），使用简单实用的工具操作（分析工具），对分析结果进行有效的解读（数据可视化），最终形成具体的业务建议，实现业务分析/数据分析的闭环。
重思路：数据思维+分析框架；
重体系：分析过程+分析阶段；
重实战：分析方法+分析模型+分析工具；
重落地：可视化+数据解读+业务策略。
课程主题
大数据的培训主题有：
董事长总经理高管的课程：
《数字化战略与数字化变革》
《大数据思维与应用创新》
《大数据思维与商业模式创新，赋能企业增长》
《大数据思维与大数据决策，提升决策能力》
大数据市场营销的课程：
《大数据时代的精准营销》
《数说营销----大数据营销分析实战与沙盘》
《市场营销大数据分析实战培训》
《大数据助力市场营销与服务提升》
大数据分析应用类的课程：
《大数据分析综合能力提升实战》
《大数据建模与模型优化实战培训》
《大数据挖掘之SPSS工具入门与提高》
《金融行业风险预测模式实战培训》
《数据分析及生产运营实际应用》
大数据分析语言Python课程：
《Python开发基础实战培训》
《Python数据分析与可视化实战》
《Python数据建模与模型优化实战》
《Python数据挖掘专题分析》
《Python机器学习算法实战》
《Python RPA办公流程自动化》
《大数据建模大赛实战辅导》
服务客户
傅老师曾提供过培训咨询服务的客户遍及通信、金融、交通、制造、政府等行业，其中包括中移动、华为、施耐德、富士康、平安集团、中国银行、西部航空、广州地铁、东风日产、广州税务、良品铺子、中冶赛迪、埃森哲、海天集团、正泰电器、徐工集团等公司和单位。
银行/邮政/保险/证券等金融行业培训客户
中国银行（江西、湖南、上海）
农业银行（上海、广东、四川、北京、天津、福建、湖南）
工商银行（北京、上海、浙江、广东、河南、河北）
建设银行（上海）、
交通银行（上海、重庆）
中信银行（广东、河北）
招商银行（广东、湖南、湖北、辽宁、江苏）
平安银行（广东、上海、北京、辽宁、贵州、安徽、河南、江苏）
平安集团（银行、人寿、产险、医保科技、平安普惠等）
光大银行（北京、上海、黑龙江）
浦发银行（北京、上海）
兴业银行（上海、浙江、江西）
农商行（浙江，广东深圳、佛山、顺德、东莞）
广发银行（广州）、民生银行（北京）、瑞穗银行（上海）、微众银行（广东深圳）
邮储银行（宁波、广西）
邮政（广东、山东、广西、重庆、安徽、河南、黑龙江、江西）
证券（上交所、郑交所、大商所、安信证券、陆金所、中金所、）
保险（天安财险、中华人寿、太平洋保险、）
其它如：广电银通，广汽理汇，金融壹帐通，马上消费金额、易鑫集团、五矿经易期货、杭州银货通科技、中邮金融科技、招商信诺、招联金融…
制造行业培训客户
富士康（太原、成都）、施耐德、宁波海天、中冶赛迪、正泰电器、大疆、三一重工、徐工集团、创维、索菲亚、ABB、延峰海纳川、昌硕科技、村田电子、博西家用电器、深圳YKK、雅图仕、沁园、浦林成山、翔路腾龙、泰科、万家乐、亿力机电、中汽研、一汽解放锡柴、一汽资本、航空特种结构研究所、昌硕科技、方太米博、诺仕达、达索、凯斯纽荷兰、苏美达、兴发铝业等。
通信/运营商行业培训客户
华为、爱立信；
中称动（安徽、北京、福建、甘肃、广东、广西、贵州、海南、河北、河南、黑龙江、湖北、湖南、吉林、江苏、辽宁、内蒙古、宁夏、山东、山西、陕西、上海、沈阳、四川、西藏、新疆、云南、浙江、重庆）
中移技术、中移终端、中移在线（陕西、贵州、四川、河南、广东、湖南、云南、山西、重庆、）
中联通（联通研究院、联通视频科技、广东、湖北、新疆、江苏、吉林、北京、广东、北京、山东、天津、黑龙江、）
中电信（福建、上海、广东、河北、江苏、香港、上海）
还有天翼爱音乐、湖南特音…、等等
交通/航空/能源/物流培训客户
航空：西部航空、贵宾公司、南方航空、
交通：广州地铁、深圳巴士、海顺海事、
车汽：联友科技、柳州五菱、东风商用、东风出行、东风日产、保时捷、
能源：宝鸡电力、神南矿业、宁夏国电、深圳水务、上海城投水务、中石化、中海油、云南电网、珠海港兴、天津国电客服、忻州供电局、中广核、延长壳牌、
物流：安能物流、顺丰速运、
直销/零售/电商/IT/互联网等行业培训客户
直销：新时代、无限极
化妆品：欧莱雅、妮维雅、玫琳凯、
零售：良品铺子、顶新国际、周大福、华润、
电商：智汇创想、欣盛商、
IT互联网：字节跳动、壹药网、上海找钢网、西部数据、文思海辉、
政府等其他行业
烟草：贵阳烟草、丽水烟草局、浙江烟草、湖州烟草、台州烟草、
政府：赣州监狱、广州税务、深圳会展中心、内蒙古社科联、
化工：重庆国际复材、广东立白、
农牧业：挑战牧业、天康生物、
饮料：东鹏特饮、
餐饮：岭南集团
咨询：埃森哲、
文旅：欢乐港湾、诺仕达、佛山传媒、
学校：上海财大、
……
学员评价
傅老师是我目前听过的很少忽悠而多干货的老师，能够将理论讲得深入浅出，将案例讲深讲透，将实战讲得易理解易操作。在课堂中，他能把枯燥的数据说得有生命，在课堂上，他能对学员关注和付出。我不是对数据很喜欢的人，但仍然在课堂中能够感觉到数据的生命力。五天的课让我进入到数据构成的多彩、多维的世界，值得！
——学员分享
某金融行业---《大数据变革与商业模式创新》
傅老师运用全面翔实的案例和不拘一格的语言，全方位剖析大数据发展以来在工具、思维和文化上带来的变革，生动阐述数据分析过程六部曲、数据战略七大思维等经典概述，立体呈现大数据时代企业所面临的机遇与挑战。结合当前关注焦点和时代热点话题，傅老师现场分享了第一代传统营销、第二代互联网营销、第三代大数据营销的进阶升级和精准营销实战应用。在为学员呈现一场思维见识领域盛宴的同时，傅老师还与学员进行了积极互动和现场答疑，在相互交流中启迪智慧、开拓思维，在思想碰撞中点燃大数据时代下的创新引擎，为全行在未来发展中进一步把握经济大势、开展前瞻预判、实施精准决策提供了重要思想指引。
吉林某企业——《数据分析与数据挖掘应用培训》 学员：张经理
五天的培训，让我对数据分析与数据挖掘有了进一步的了解，也学到了技术。以前参加过培训，两天的培训我都觉得有时很难，而这次连续五天的培训，我听课过程当中既然感觉到时间过得很快。
贵州某运营商——《“数”说营销----大数据营销实战》 学员：刘经理
傅老师的课程，开拓了我营销的思维，大数据营销，重在利用数据为营销服务。用户细分、用户特征提取、营销费用预算、客户流失预警，原来可以这样利用大数据，以后不再需要“拍脑袋”了,呵呵。
某制造业学员——《数据分析与经营分析实战培训》学员：于工（经理）
傅老师的课程全程高能，信息量巨大，干货满满，我们已经建议公司安排后续高级课程，期待再次学习，点赞！
某邮政学员——《Python数据分析实战培训》 学员：袁同学
虽然我是学统计专业的，但从未如此系统地学习过数据分析，感谢傅老师这四天的精彩的授课，我可太喜欢你的课程了，让我如此全面系统地了解数据分析，学习Python这门语言，这是我在学校里未曾接触过的。能够将业务问题融入在数据分析中，用最简单地方法解决复杂的业务问题，高赞！
客服微信号：baihuaxiaolu
