导图社区秒可职场 Excel课程精粹集

秒可职场 Excel课程精粹集

这是一个关于秒可职场 Excel课程精粹集的思维导图，通过全面的内容覆盖和实用的技能教学，旨在帮助学员快速掌握Excel的高级操作和数据分析技能，提升工作效率和职场竞争力。

编辑于2024-08-21 19:20:05

Excel
数据分析精进
Excel实战训练营

紫极斗罗林派玮

秒可职场 Excel课程精粹集

社区模板帮助中心，点此进入>>

紫极斗罗林派玮

相似推荐
大纲

夏令营
- 1.3k
- 0
- 22
- 7
胡老师
2019商城活动时间表
- 1.1k
- 81
- 69
- 6
紫水晶
部门规划
- 2.3k
- 6
- 17
- 2
卿上卿
技术部门
- 951
- 51
- 13
- 3
ABC儿童摄影阿杰18028111820
项目经理管理培训
- 1.9k
- 92
- 104
- 13
Jemie
创业计划书经典案例
- 2.4k
- 20
- 205
- 12
111
项目复盘的九步法
- 5.0k
- 442
- 400
- 38
唐老师15950739729
篮球大纲
- 2.0k
- 6
- 56
- 3
别人家的孩子
调研事项
- 1.2k
- 77
- 42
- 1
™D
PPT统计图图像展示-职场工具报告
- 1.3k
- 21
- 80
- 5
拾捌

秒可职场 Excel课程精粹集

Excel实战训练营

图/表

数据透视表+字段

超级表:Ctrl+T/开始-套用表格格式

切片器+报表链接

数据透视图(设计、格式)

图表(图表设计、格式)

条件格式-数据条

数据验证

迷你图

开发工具-插入-表单控件

数据

数据-分列-分割符号/固定宽度

数据-分类汇总；数据透视表:设计-分类汇总

把字段多次拖进值

数据透视表分析-分组选择

数据透视表分析-插入日程表→时间线→报表链接

数据透视表分析-选项-显示报表筛选项

数据透视表分析-字段、项目和集-计算字段→输入名称和公式

超级表还原为普通表：表设计-转化为区域

审阅-保护工作表/保护工作簿

插入-三维地图。开始-设置-时间和语言-区域-国家和地区:美国

其它操作

TXT联动：从文本/CSV→转换数据→123→文本→替换当前转换→删除不需要的列→关闭并上载

右键形状标签→链接→本文档中的位置→选择对应工作表→每个工作表对应形状标签设置颜色

选择性粘贴：转置、跳过空单元、运算

取消合并单元格→Ctrl+G定位-空值→【=↑/=←】→【Ctrl+Enter】

打印与保护：拉动小方块调整页边距/将所有列调整为一页

数据透视表选项→合并且居中排列带标签的单元格、水平并排、取消勾选更新时自动调整列宽

制作折线图，利用错误值NA()来替代0

鼠标右键图表绘图区→另存为模板→插入-柱形图-更多柱形图-模板

编辑顶点→右键顶点→勾选平滑顶点

制作看板：选择性粘贴-链接的图片、照相机拍照单元格

联动：Excel中复制表格或图表→PPT或Word中，选择性粘贴-Microsoft Excel工作表/图表对象

口诀

真日期有三种，一横一撇年月日

标题之外有条件，统统放到筛选区。数字往上放列里，数字往左放行里

快捷键操作

Ctrl+E填充

Ctrl+shift+→+↓选中

Ctrl+shift+enter识别为数组公式

Ctrl+；录入日期

Ctrl+Shift+；录入时间

函数操作

开始-自动求和-求和/平均值/计数/最大值/最小值。Alt=快速求和

F9查看公式部分对应的结果

“*”标识任意字符串，“?”标识一个字符串

公式-名称管理器；公式-根据所选内容创建；公式-用于公式

函数

常用函数

IF(测试条件,真值,假值)

COUNT(计算单元格的区域)计算区域中包含数字的单元格的个数

COUNTIF(区域,条件)计算在区域内的单元格之中，满足条件的单元格数量

COUNTIFS(区域1,条件1,区域2,条件2……区域N,条件N)计算区域中满足多个给定条件的单元格个数

SUM(区域1,区域2……区域N)求参数中所有区域的和

SUMIF(区域,条件,求和区域)计算在区域中满足条件的单元格，并在求和区域进行累加

SUMIFS(求和区域,查找区域1,条件1,查找区域2,条件2……查找区域N,条件N)指定区域内同时满足多个条件的值进行求和

AVERAGEIFS(求平均值区域,条件区域,条件)计算平均值

查找函数

（vlookup）VLOOKUP函数(查找值，数据源，列序数，精确匹配0)

多对一查询：查找依据用连接符&

一对多查询：不同列

（lookup）LOOKUP：

(查找目标,起步线标准,结果区域）起步线是从小到大的升序序列

(查找值,查找区域,查找范围)在查找区域(起步线)中查找指定的值，并返回结果中对应位置的值

其它函数

（right）RIGHT(文本字符串,位数)从右往左拆分截取文本字符串

（left）LEFT(文本字符串,位数)从左往右拆分截取文本字符串

（len）LEN(文本字符串)计算字符串长度

（lenb）LENB(文本字符串)计算字符串长度，1个汉字计算2个字符数

（find）FIND(符号,目标字符串,1)查找一个字符串在另外一个字符串中的位置

（mid）MID(文本字符串,第几位,取几位)把一个字符串从第几位开始拆分，拆分几位

（text）TEXT(指定值,指定格式)文本型数字的转化。设置一个值，显示为指定的格式。指定格式两边需要加英文状态下的双引号

（datedif）DATEDIF(开始日期,结束日期,计算类型)计算年龄：=DATEDIF(F4,TODAY(),"Y")

（trim）TRIM:去除空格

=TODAY()：当前日期。=TODAY()+1是明天，=TODAY()-1是昨天，=TODAY()-2是前天

=NOW()：现在时间

=YEAR()：某一日期年份。=YRAT(B1)

=MONTH()：某一日期月份。=MONTH(B1)

=DAY()：某一日期具体天数。=DAY(B1)

=DATE()：组合完整年月日真日期。=DATE(B3,B4,B5)。=DATE(B3,B4+1,0)，下个月第0天=这个月最后一天

计算两日期相隔年数:Y：=DATEDIF(G1,G2,"Y")

计算两日期相隔月数:M：=DATEDIF(G1,G2,"M")

计算两日期相隔天数:D：=DATEDIF(G1,G2,"D")

计算实际工作天数：=DATEDIF(C3,D3,"D")+1

计算净工作日天数：=NETWORKDAYS(K1,K2)

（match）MATCH(查找值,查找区域,0)

（index）INDEX(查找范围,行号,列号)

（round）ROUND(需要进行四舍五入的数字，指定的位数）Round函数返回一个数值，该数值是按照指定的小数位数进行四舍五入运算的结果

（randbetween）RANDBETWEEN(最小整数,最大整数)在1到100之间随机取数：=RANDBETWEEN(1,100)。F9刷新

数据分析精进

文本处理函数

=SUBSTITUTE（substitute）(要进行替换的文本,替换什么,替换为什么,[指定目标])

=REPLACE（replace）(要进行替换的文本，从第几个字符开始替换,一共要替换多少个字符,替换为什么)

=REPT（rept）(要重复的文本,重复的次数)不同字体（尤其是Webdings）下的符号效果不同

=TRIM（trim）(要删除空格的文本)

=CLEAN（clean）(想要删除非打印字符的文本)

=CELL（cell）(指定所需单元格信息类型，需要引用的单元格)返回引用中第一个单元格的格式、位置或内容等有关信息

=CELL("filename",A2)，获得A2单元格的文件路径信息。filename代表的是操作当前表格的文件名

SUMPRODUCT函数

计数:=SUMPRODUCT((条件一)*(条件二)*(条件三)*......) 在给定的几个“条件”中，将“条件”相乘，并返回乘积之和（计数）

求和:=SUMPRODUCT((条件一)*(条件二)*(条件三)*......,求和列)

求和:=SUMPRODUCT((条件一)*(条件二)*(条件三)*......*求和列)

求和:=SUMPRODUCT((条件一)*(条件二)*(条件三)*......,求和区域)

=SUMPRODUCT((数组1条件1)*(数组1条件2)*....,(数组2条件1)*(数组2条件2)...)

=SUMPRODUCT(((条件1)+(条件2)+(条件3))*(条件4),求和列)

=SUMPRODUCT((条件1)*(条件2)*求和列)

N函数转化“,”分隔的参数为数值，“*”乘后的结果即为数值

=N(value)（要转换的值）返回转化为数值后的值

=ISNUMBER（isnumber）利用它将返回结果返回TRUE和FALSE，如果返回TRUE，就进入计算，如果返回FALSE就不参与计算

查找函数

VLOOKUP查找函数

逆向查找：=VLOOKUP(查找值,IF({1,0},查找值所在列,返回值所在列)2,0)

多条件查找：构建辅助列，将N个条件组合为唯一1个条件

其它查找函数

=LOOKUP(查找值,查找区域,结果区域)在查找区域（起步线)中查找指定的值，并返回结果区域中对应位置的值

=LOOKUP("座",查找区域) 在查找区域中查找“座”，并返回这一列中最后一个值（文本）

=LOOKUP(9E+307,查找区域) =LOOKUP(最大数值,查找区域)在查找区域中查找“9E+307”，并返回这一列中最后一个值（数值）

=LOOKUP (1,0/(查找条件),结果区域)在结果区域中查找满足查找条件的最后一个单元格

=LOOKUP(1,0/(A:A<>""),A:A)

=LOOKUP(1,0/((查找条件1)*(查找条件2)*......*(查找条件N)),结果区域)在结果区域中查找满足所有查找条件的单元格

D4=IFERROR(LOOKUP(1,0/COUNTIF(C4,"*"&$G$4:$G$12&"*"),$G$4:$G$12),"非辖区")（查找条件：0/COUNTIF里最大的数）

E4=IFERROR(LOOKUP(1,0/FIND($G$4:$G$12,C4),$G$4:$G$12),"非辖区")（查找条件：0/FIND里最大的数）

查找与引用函数

=OFFSET（offset）(起始位置，偏移的行数，偏移的列数，[返回的行数]，[返回的列数])

偏移的行数：为正数时，向下偏移N行；为负数时，向上偏移N行；偏移的行数不包括自身

偏移的列数：为正数时，向右偏移N列；为负数时，向左偏移N列；偏移的列数不包括自身

[返回的行数]：即返回多少行，返回的行数包括自身；返回的行数是正数时，表示向下返回多少行，是负数时，表示向上返回多少行

[返回的列数]：即返回多少列，返回的列数包括自身；返回的列数是正数时，表示向右返回多少列，是负数时，表示向左返回多少列

offset函数的作用就是用来构建动态的引用区域。F3调出粘贴名称的对话框

公式-名称管理器-新建：引用位置:=OFFSET(员工档案!$A$1,0,0,COUNTA(员工档案!$A:$A),COUNTA(员工档案!$1:$1))

最小值和最大值函数

=SMALL（small）(数据区域,提取区域中的第几个最小值)返回数组中第k个最小值

=LARGE（large）(数据区域,提取区域中的第几个最大值)返回数组中第k个最大值

函数解析

H3=IF($O$2="升序",SMALL(IF($L$2="合同金额",$C$3:$C$17,IF($L$2="发货金额",$D$3:$D$17,IF($L$2="回款金额",$E$3:$E$17,$F$3:$F$17))),ROW(A1)),LARGE(IF($L$2="合同金额",$C$3:$C$17,IF($L$2="发货金额",$D$3:$D$17,IF($L$2="回款金额",$E$3:$E$17,$F$3:$F$17))),ROW(A1)))

解析：用IF函数根据L2单元格的内容来返回与L2单元格所属的金额类型对应的数据区域，然后再依次提取这个区域中的最小值或最大值

G3=INDEX($B$3:$B$17,MATCH(H3,IF($L$2="合同金额",$C$3:$C$17,IF($L$2="发货金额",$D$3:$D$17,IF($L$2="回款金额",$E$3:$E$17,$F$3:$F$17))),0))

解析：MATCH函数查找H3单元格内容对应IF函数内金额类型对应的数据区域得出的行作为INDEX函数参数里的行

统计函数

=QUARTILE（quartile）(数组或数值区域，返回哪个四分位点)返回一组数据的四分位点

第二个参数按四分位从小到大依次为，数字0-4

Quart参数的值在0~4之间

参数为0，表示需要返回最小值

参数为1，表示需要返回第一个四分位点，即25%处的数据

参数为2，表示需要返回第二个四分位点，即50%处的数据，也就是中值

参数为3，表示需要返回第三个四分位点，即75%处的数据

交叉查询

=MAXIFS（maxifs）(求值区域，条件区域1，条件1，[条件区域2]，[条件2]……)返回指定区域的最大值

=INDEX MATCH（index match）交叉查询：match找到行、列值，index根据行、列数字找到交叉的单元格

=COUNTA（counta）统计非空单元格的个数

=MOD 奇数:除以2有余数的就是奇数，偶数:除以2可以整除无余数

=MOD(ROW(),2)=1 row返回的行号除以2

A4=MID($B$3,COLUMN(),1) 向右填充公式，column返回列号，列号是多少就从第几个字符开始提取，提取1位数

C6=RANDBETWEEN(50,90)：randbetween函数返回50到90之间的随机数

日期和时间函数

A2=DATE(2019,J1,DAY(DATE(2019,J1+1,0)))

=DATE(年,月,日)

年：2019

月：J1单元格

日：DAY函数得到当前月份最后一天，即下一月第0天

引用函数

=ADDRESS（address）(行号,列号,引用类型,引用样式,"工作表名称")

第3个参数指定引用类型

绝对引用=1（默认引用方式）

绝对行/相对列=2

相对行/绝对列=3

相对引用=4

第4个参数A1用逻辑值指定引用样式

A1样式=1或TRUE（默认样式）

R1C1样式=0或FALSE

A1单元格：如果写成A1样式就是$A$1

R1C1样式：R代表第几行，C代表第几列，就是R1C1

INDIRECT（indirect）

=INDIRECT("文本字符串")(引用单元格)

文本两端加""'：表名'!单元格地址→"'表名'!单元格地址"

单元格不是文本，双引号括住第一个单引号，连接B6单元格的内容，再用双引号连接'!L1，等于这个语法："'"表名'"!单元格地址"

常规操作

向下填充(锁定列)和向右填充(锁定行)

公式-公式求值

多表合并计算:SUM+Shift连选工作表+Enter

开始-合并计算:引用位置:工作表的数据区域→添加→勾选创建指向源数据的链接

选择性粘贴为值，转化为真正的文本而不是公式

开始-排序和筛选-筛选：筛选按钮→文本筛选→不包含，手动输入“部”字，明细数据全部被隐藏，只留下汇总数据

数据透视表和数据透视图向导→多重合并计算数据区域→自定义页字段/创建单页字段

数据透视图分析-字段按钮-全部隐藏

选择性粘贴为数值→选项-高级→编辑自定义列表→导入$C$2:$C$13→排序→其他排序选项→升序排序→其他选项→取消每次更新报表自动排序→主关键字排序

Alt+；只选中可见区域

二维表转一维表：双击最右下方的汇总值，生成一张明细表一维表格

格式操作

设置数据标签格式-标签选项-数字-格式代码:字体微软雅黑和字符代码25B2："▲ "0→添加

设置坐标轴格式-数字→类别:百分比→格式代码0%

插入三角形→右键-编辑顶点→右键平滑顶点

系列重叠：两个数据不同柱形图之间的间距

间隙宽度：同数据相同柱形图之间的间隙，即柱形图的宽度

面积图渐变90°透明

开发工具-插入-复选框→复制和移动复选框→控件内文本对应单元格链接(绝对引用)，链接单元格的单元格设置颜色

双重数据验证

选中所有省份城市区域所在列→公式-根据所选内容创建→默认勾选首行

选择K列→数据-数据验证-序列→选择标题行区域。在K列设置省份下拉选项

选中L列→数据-数据验证-序列→=INDIRECT($K1)

数组

数组公式：Ctrl+Shift+Enter

垂直数组，以分号“,”分隔各元素

水平数组，以逗号“;”分隔各元素

二维数组，以逗号分隔同一行的不同元素，以分号分隔不同行

商务图表精选

常规操作

重复上一步操作快捷键F4

移动图表时按Alt键可快速对齐单元格

当两组数据差距较大时，更改图表类型→增加次坐标轴

图片格式-删除背景→标记要保留的区域/标记要删除的区域

定义单元格名称框为：类型，数据-数据验证：允许-序列，来源=类型（名称框内容）

开发工具-数值调节钮，右键移动

（column）COLUMN(单元格或单元格区域)返回引用单元格或单元格区域的列号;如果参数中不填写内容,则返回当前单元格的列号

图类操作

复合饼图(子母图)：第二绘图区中的值修改为4。设置数据标签格式-标签选项→勾选类别名称、值、显示引导线

双层叠加饼图/二级数据饼图：饼图分离

环形柱状图：单个数据系列的最大值，不超过环形角度270°，并由最外环往最内环逐级递减呈现数据（升序）

迷你图：设计-清除

瀑布图：单独选中柱形→设置数据点格式-系列选项-勾选设置为汇总（原来悬空的柱形与横坐标轴相连）

散点图：添加趋势线-设置趋势线格式-显示公式

树状图：设置数据系列格式-系列选项-标签选项-横幅

南丁格尔玫瑰图：辅助源→插入圆环图→辅助粘贴辅助源进圆环图9次→填充和环形同色的边框色→无填充或填充白色等调整环形数量

温度计图：勾选次坐标轴；坐标轴选项设置最大值。选择数据→取消勾选图例项“系列1”→分类间距:500%→重新勾选图例项“系列1”→分类间距:500%

比率图：插入簇状柱状图→间隙宽度:0%→Ctrl+C/V→层叠并缩放/伸展→移动100%系列至上方

条件格式

选择数据区域→设置单元格格式-数字-自定义:;;;（如:"预算内";"超预算";""）

第1个分号之前，代表数字大于0，显示“A”效果

第1~2个分号间，代表数字小于0，显示“B”效果

第2~3个分号间，代表数字等于0，显示“C”效果

第3个分号之后，代表如果是文本，显示“D”效果

数字格式

1、文本格式(身份证)：设置单元格格式-文本；'+数字

2、小数：设置单元格格式-数值→调整小数位数

3、货币符号：开始-数字-千位分割样式；设置单元格-货币

4、会计专用：开始-数字-千位分割样式→0显示为-

5、日期和时间：

短日期:开始-数字-常规-短日期

设置单元格格式-自定义-yyy/m/d:h:mm:ss

6、统一加前缀和后缀：设置单元格格式-自定义

增加前缀如[合同号:]→输入["合同号:"00000]（00000表示想显示的数值位数）

增加后缀如[KG]→输入[G/通用格式"KG"]

商业分析思维与案例解剖

数据分析流程

明确问题或需求

数据获取：公司内部数据

数据处理：清洗无效数据、处理缺失值、数据一致化处理

数据分析：描述性分析、诊断性分析

可视化呈现：基本要求:准确、真实、容易理解；加分项:美观

撰写报告：输出结论、提出建议

数据分析-明确问题或需求-拆解业务流程：

看到广告→【在直播间购买商品:观看直播→点击商品→购买（加入购物车→下单→付款）】→收到商品，确认收货

业内专用术语：直播曝光→观看直播→点击商品→加入购物车→下单→付款→交易成功

数据分析-类型

描述性分析:解释发生了什么

诊断性分析:解释为什么会发生

人货场分析模型

⑴.把货通过场卖给人

人消费者你

货商品酱油

场连接消费者和商品的要素超市

⑵.直播中

人用户

货产品

场主播

清洗数据

筛选空值：筛选→倒三角→按需筛选→删除空值、废弃数值

分列：插入新列→规定分隔符→分列

某列查找替换单位为空值

单位换算：插入列→筛选以万/月为薪资单位的岗位，薪资数值乘以10；筛选以万/年为薪资单位的岗位，薪资数值乘以10再除以12

TRANSPOSE（transpose）转置函数

S2=TRANSPOSE(H32:N32)

转化率漏斗图：R3=($S$2-S3)/2

对比分析法

自己(过去的自己)、目标(计划)、行业(优劣势)

多维度分析：时间、产品、用户

异常值分析

⑴.识别异常值：条件格式（数据条/色阶）、图表（柱形图/折线图）

⑵.异常值产生原因

数据本身有错误：记录错误、处理错误

数据本身无错误：业务策略有问题

⑶.业务策略分析

时间维度分析异常值

产品维度分析异常值

门店维度分析异常值

用户维度分析异常值

矩阵分析法

用平均值作为分隔线：求出平均值、选择X轴和Y轴→设置坐标轴格式-纵坐标轴交叉-坐标轴值

用公司指标作为分隔线

设置绘图区格式：图片或纹理填充-剪贴板→向右偏移-5%，向下偏移-18%

四种产品类型

金牛产品（成熟期）：是成熟市场的领导者，能为企业提供稳定的净现金流

明星产品（成长期）：代表着目前很成功、很赚钱的产品，应继续保持

问题产品（导入期）：用户有一定需求，但产品本身有问题，需要改进

瘦狗产品（衰退期）：说明市场需求低微，收益率低甚至亏损，应考虑淘汰

KANO模型分析法

⑴.问卷调研：用户需求类型

必备需求>期望需求>魅力需求>无差异需求>反向需求

必须做的需求：必备需求:用户认为必须有的功能，比如酒店提供干净的床单

最好要做的需求：期望需求:有的话用户会满意，没有的话用户会不满意的功能，比如酒店提供质量好的洗漱用品

值得做的需求：魅力需求:超过用户预期的功能，比如酒店免费给你升级房间

坚决不做的需求：无差异需求:用户觉得有没有都无所谓的功能，比如酒店杯子的花纹很漂亮；反向需求:用户很反感的功能，比如酒店的过度打扰

⑵.Better-Worse系数

Better系数：这个数值越高，就代表有这个功能，越多用户会满意

Better系数=(魅力需求用户个数+期望需求用户个数)/总人数(不包括反向需求)

Better系数>0.5，有这个功能的话，大部分用户会满意;且越接近1，用户的满意程度越高

Worse系数：这个数值越高，就代表没有这个功能，越多用户会不满意

Worse系数=(期望需求用户个数+必备需求用户个数)/总人数(不包括反向需求)

Worse系数>0.5，没有这个功能的话，大部分用户会不满意;且越接近1，用户的满意程度越低

Better系数>0.5 Worse系数>0.5→属于期望需求

Better系数<0.5 Worse系数>0.5→属于必备需求

Better系数<0.5 Worse系数<0.5→属于无差异需求

Better系数>0.5 Worse系数<0.5→属于魅力需求

⑶.插入三维气泡图，改变坐标轴的交叉点位置：分别设置横、纵坐标轴的[坐标轴值]

⑷.需求类型→气泡大小 (重要程度：数值: 0-1)

必备需求→0.8

期望需求→0.6

魅力需求→0.4

无差异需求→0.2

漏斗分析法

⑴.招聘流程：[确认招聘需求→发布招聘信息→简历初步筛选]→笔试→邀约面试→[HR面试→用人部门复试→高管面试]→发Offer→[入职体检→入职培训→通过试用期]

⑵.年度招聘汇报[六步走战略]：招聘完成率→未入职分析→环节拆分→团队分析→渠道分析→招聘漏斗图

⑶.漏斗分析法的其它运用场景：

社群运营岗:从添加用户账号到产品成交的转化分析

电商岗:从浏览商品到订单成交的转化分析

财务岗:从营业收入到净利润的转化分析

商务岗:从销售合同到正式回款的货款分析

质量管理岗:产品生产一次通过率的数据分析

RFM分析法

⑴.R越小，F越大，M越大，客户创造的价值越大

R:Recency最近一次消费时间与统计时间的间隔

F:Frequency消费频率

M:Monetary消费金额

⑵.如果想获得大额满减券，不妨反向操作一下，减少点外卖的频率

⑶.分析流程

需求:对上半年的客户进行分层，制定针对性营销策略

步骤1:对数据进行简单处理，只保留涉及R、F、M计算的数据

步骤2:计算R、F、M

步骤3:对R、F、M打分

步骤4:对客户进行分层

步骤4.1:计算R、F、M打分平均值并比较

步骤4.2:根据RFM对客户进行分层

步骤5:对各类客户进行分析

步骤6:对各类客户制定营销方案

⑷.RFM分析有局限性

需要结合行业数据进行区间划分!

只能对有消费(注册)记录的用户进行分类

秒可职场 Excel课程 精粹集

秒可职场 Excel课程 精粹集

秒可职场 Excel课程精粹集

秒可职场 Excel课程精粹集