Excel 思维导图模板_ProcessOn思维导图、流程图

excel基本操作

小技能

删除区域数据选择区域ctrl加-

在数字前面加单引号数据类型会变为文本

excel标准日期是以/为分，例如：2024/3/10

全部选中一列——ctrl+shift+箭头下

保护功能

设置密码打开文件

文件 —— 信息 —— 保护工作簿 ——— 用密码进行加密——— 输入密码

保护工作簿

审阅——保护工作簿——输入密码

保护工作表

审阅——保护工作表——输入密码

部分单元可编辑

选定区域——右键设置单元格格式——保护——取消锁定——保护工作表

破解密码

破解的是保护工作表的密码

把文件后缀名改为zip——打开——xl——worksheet——把<sheetProtection algorithmName="SHA-512" hashValue="S7wjawHrk11Nu2nFLiQOjo+BszQDGvI72XWAjStYXKEceCfoCnWWQKw+Ol8sQADyXLS5oGVI5INls/+uMhcLuA==" saltValue="Ao6EXqRxdoa5aKJkKHHekQ==" spinCount="100000" sheet="1" objects="1" scenarios="1"/>删除——改回后缀名

数据快速输入

自定义列表

文件——更多选项——高级——刷到最底下——自定义列表——自由设置

数字递增

文本加数字可以直接下拉

纯数字需要按住ctrl下拉

填充

选择区域——开始——编辑——左一中间点列表——设置

文本记忆（鸡肋）

前面输入的内容可以点右键点下拉列表选择（如果中间有空格就不行）

数据验证下来列表

选择区域——数据——数据工具的数据验证——选序列——设置来源（英文分号;）

数字显示样式

布尔值显示中文

选中区域右击——设置单元格格式——数字——自定义（[=1]"男";[=0]"女"）输入1是为男

添加单位

选中区域右击——设置单元格格式——数字——自定义（0.0"元"）输入3显示3.0元

手机号码

选中区域右击——设置单元格格式——数字——自定义（000-0000-0000）输入11位号码170-9522-4042

正负数不同颜色

选中区域右击——设置单元格格式——数字——自定义（找到#,##0;[红色]-#,##0）

数据格式规范

不规则数字

使用=文本数字格/1或+0或- -文本数字格转为纯数字

不规则日期格式1

2023.3.10

变为2023/3/10

选择区域——开始——编辑——查找与选择——将.替换/

不规则日期格式2

220310

变为2022/3/10

重起一列放置——输入=TEXT(A2,"00-00-00")——变为文本22-03-10——另起一列输入=--选择单元格（或者前面学的）

不规则日期格式3

20220310

一步到位

选中区域——数据——数据工具——分列——前面两步直接点击——第三步点日期

利用前两者方法

变为文本进行转换

注：在选择替换时复制，然后粘贴为值，然后替换

不规则时间格式

122057

变为12：20：57

另起一列输入=TEXT(A2,"00!:00!:00")

冻结窗格

视图

冻结窗口——冻其他行——点击第二行第二列······

excel数据处理

定位条件

定位空值

使用替换将空值换成0

选择查找下拉找定位——选择空值在编辑框输入0 ctrl加enter

定位错误

定位条件——公式——只勾错误——delete

选择性粘贴

运算

复制内容，选择复制的区域——开始的粘贴——选择性粘贴——设置

将两列数据合成一列（见缝插针）

复制第二列到第一列下方——设一个辅助列——输入1填充全，在下方输入1.2——使用等差序列——点数据——排序——填充列表——排序

单元格匹配

定位单元格

查找替换

单元格匹配

各行插入空行

设置辅助列——输入1填充全，在下方输入1.2——使用等差序列——点数据——排序——填充列表——排序

复制首行——定位空值然后复制

多行多列变一个单元格

先设置公式——名称管理器——单列或者单行。数据验证——序列——输入=名称。再改变名称的取值范围

快速填充

ctrl+e(填充会自己总结规律)

拆分数据

合并数据

增加空格

位置调整

分列

数据——分列——如果以其他字为分列要确保以一个字分列

分列+选择性粘贴+快速填充

（行转为列）先分列，将全部复制，然后选择一格，选择性粘贴，转置，（如果要去除其他成分可以快速填充）

合并计算

选择填入的区域——数据——合并计算——分别选择要合并的区域然后计算

分列练习

可以同时筛选两个条件

条件格式

在开始样式的条件格式设置

excel基本公式和函数

公式

计算公式

引用

绝对引用F4

错误类型

数学函数

生成随机0-1小数

=rand()

生成一定范围的数

=randbetween(x1,x2)

取整

=int(x1)

取余

=mod(x1,x2)

四舍五入

=round()

取绝对值

=abs()

算术平方根

=sqrt()

文本函数

提取

原数据：ORD202302220001

提取前缀

=left(A2,3)

提取年

=MID(A2,4,4)

提取月

=MID(A2,8,2)

提取编号

=RIGHT(A2,4)

小写全缀

=LOWER(A2)

大写首字母

PROPER(A2)

查找与替换

=LEFT(A2,7) ——=RIGHT(A2,8)

text函数

=TEXT(A3,"yyyy年mm月dd日")

=TEXT(A3,"yyyy年m月d日")

格式五=TEXT(A2,"yyyy-mmm-ddd")

格式六=TEXT(A2,"dddd")

格式七=TEXT(A2,"aaaa")

格式八=TEXT(A2,"aaa")

统计函数

逻辑与判断

日期函数

查找引用函数

excel可视化

选中数据插入图表添加图标元素右键图表打开设置图表区域格式

饼图

正常类别不宜超过六个，如果超过六个建议使用条形图或者字母饼图

折线图

堆积折线图无法对比各图例的，只能使用普通折线图。

条形图

簇状条形图：类别与类别比，也可以图例与图例比，但是比不了类别的总量

堆积条形图可以查看总量，也可以某类别图例之间比，也可以某个图例各类别比

百分比堆积条形图可以查看某图例在类别的比重

散点图

看两连续型变量的关系

其他图

对比分析

使用条形图——加标签——进入格式设置——增加次坐标轴然后逆序——两条坐标轴设置负值——把多余的东西删除

子主题

瀑布图

加一条辅助列——将前面的累加（=SUM($B$2:B2)······）——全选，使用堆积簇状条形图——右击选择数据——下移，其他按要求设置

或直接全选设置瀑布图——将汇总的右击设置汇总

漏斗图

选择数据——条形堆积——设置坐标轴格式——逆序类别——选择数据逆序把辅助列变成无填充

或者直接生成

动态图

复杂不如透视表

雷达图

有三种

商业图表

选择数据——簇状条形图——切换行列——添加元素的线条

增加辅助列——圆环图——切换行列——设置数据系列格式——系列选项圆环大小——颜色一条一条设置

数据透视表

选定数据——插入——表格——取名

选择插入的位置——插入——数据透视表——可以选定范围或者=某某表

有行、列、值、筛选

值有很多选项

双击透视表可以看到具体数据

二维表转为一维

选择多重合并计算

插入——切片器

锁定切片器——右击设置形状大小——大小与属性——锁定取消

powerBI

数据提取清洗等 power query

提取可以不进入query，清洗必须进入

文件，选项，可以选择用不用类型检测

视图的列分发和列质量可以看到信息

标题旁边可以选择数据类型

转换

主页和转换有第一行用作标题

功能

透视表

选择列，然后设置其他内容

逆透视

选择要透视的几列后就行了

数值列

日期列

添加列

常规

示例中的列

自定义的列（只有这个函数有用，在上面的无法实现）

从文本

选中列后

子主题

其他跟转换一样

数据合并

上下拼接

追加查询

追加的列数必一样

当追加的某列的值不一样可以新建一列备注数据是什么

左右拼接

当有重复项时如学号姓名可以添加索引列

如果有可当索引列的可以用来当主键

联接

跟左右拼接一样，当有名字重复时要用聚合的最大值

power pivot

多张表建立关系

关系图里事实表一张，维度表有多张
维度表负责筛选，事实表负责聚合

在关系视图界面点击事实表的键连接维度表

计算列和行上下文

DAX中有四种运算符

算术运算符

加减乘除幂

比较运算符

运算符将两个值进行比较
结果为逻辑值（TRUE 或 FALSE）

逻辑运算符

逻辑运算符 (&&) 和 (||) 组合

文本连接符

& （与号）连接或串联两个值以生成一个连续文本值

数学函数

日期时间函数

年 = YEAR('销售记录表'[订单日期])
月 = MONTH('销售记录表'[订单日期])
日 = DAY('销售记录表'[订单日期])
时 = HOUR('销售记录表'[订单日期])
分 = MINUTE('销售记录表'[订单日期])
秒 = SECOND(('销售记录表'[订单日期]))
季度 = QUARTER('销售记录表'[订单日期])
星期 = WEEKDAY('销售记录表'[订单日期],2)
周 = WEEKNUM('销售记录表'[订单日期])

偏移2个月 = EDATE('订单明细表'[订单日期],2)
偏移3个月的最后一天 = EOMONTH('订单明细表'[订单日期],3)
年份差异整数比例 = YEARFRAC('订单明细表'[订单日期],"2020-12-31")
日期之间工作日天数 = NETWORKDAYS('订单明细表'[订单日期],"2020-12-31")
日期之间天数 = DATEDIFF('订单明细表'[发货日期],'订单明细表'[送达日期],DAY)

当前日期时间 = NOW()
当前日期 = TODAY()
创建日期 = DATE(2023,12,31)
创建时间 = TIME(23,11,31)
当前UTC时间 = UTCNOW()
当前UTC日期 = UTCTODAY()
日期表 = CALENDAR(DATE(2022,10,25),DATE(2022,11,5))
自动日期表 = CALENDARAUTO()

文本函数

FIND ：返回一个文本字符串在另一个文本字符串中的起始位置。
LEFT、RIGHT：从文本字符串开头（结尾）返回指定数量的字符。
LEN ：返回文本字符串中的字符数。
MID ：在提供开始位置和长度的情况下，从文本字符串中间返回字符串。
REPLACE ：根据指定的字符数，将部分文本字符串替换为不同的文本字符串。
SUBSTITUTE：在文本字符串中将现有文本替换为新文本。
COMBINEVALUES：将两个或更多个文本字符串联接成一个文本字符串。
EXACT ：比较两个文本字符串，如果它们完全相同，则返回 TRUE；否则返回 FALSE。
LOWER、UPPER ：将文本字符串中的所有字母都转换为小写（大写）。
VALUE ：将表示数值的文本字符串转换为数值

格式转换

月 = FORMAT(MONTH([Date]),"00")
季度 = "Q" & format([Date],"Q")
年度季度 = FORMAT([Date],"YYYY") & "Q" & FORMAT([Date],"Q")
年月日 = FORMAT([Date],"YYYY/MM/DD")

创建日期表

关系函数

函数RELATED
实现从其他表取值。事实表从维度表索取相关列，必须表和表之间建立可用关系才能使用。

（运算重点）

RELATEDTABLE是维度表从事实索取

逻辑函数

• AND与判断。检查两个参数是否均为 TRUE，如果两个参数都是 TRUE，则返回 TRUE。
• OR或判断。检查某一个参数是否为 TRUE，如果是，则返回 TRUE。
• NOT 非判断。将 FALSE 更改为 TRUE，或者将 TRUE 更改为 FALSE

• SWITCH有两种常用场景，单值条件判断和多值条件判断。两种条件判断写法不同。
• 单值条件判断下SWITCH的第一个参数往往写的是列名，条件是当列名的值等于某值时候，结果是什么。
• 多值条件判断下SWITCH的第一个参数往往写的是布尔值，条件的结果是布尔值，结果再是什么。

处理空值

度量值与筛选上下文

利润率：计算列是一行一行算利润率，再由分组后相加。度量值先分组算利润总值和销售额总值，再相除求利润率。
占有率：当前值/总值。让总值不受视觉对象筛选，为总值的度量值添加不受筛选的限制，calculate(度量值，[为这个度量值添加的筛选条件])。
三种筛选条件：布尔筛选，筛选器修改函数（removefilter(),ALL(),ALLSELECTED()）,表筛选函数（FILTER）

聚合函数

销售额汇总 = SUM('订单明细表'[订单总金额])
销售额均值 = AVERAGE('订单明细表'[订单总金额])
销售额最大值 = MAX('订单明细表'[订单总金额])
销售额最小值 = MIN('订单明细表'[订单总金额])

迭代函数

创建度量值表
只放度量值
主页的输入数据，创建
先建立一个度量值，然后把另一个删了就成了度量值表

销售额汇总2 = SUMX('订单明细表','订单明细表'[商品单价] * '订单明细表'[订货数量])
销售额均值2 = AVERAGEX('订单明细表','订单明细表'[商品单价] * '订单明细表'[订货数量])
销售额最大值2 = MAXX('订单明细表','订单明细表'[商品单价] * '订单明细表'[订货数量])
销售额最小值2 = MINX('订单明细表','订单明细表'[商品单价] * '订单明细表'[订货数量])

销售额汇总度量值：使用了计算列“订单总金额”列。内存增加。
销售额汇总2度量值：没有新建计算列，使用原数据表的“商品单价”和“订货数量”。不增加内存。

针对单列数据计算，无差别
无论是迭代函数还是聚合函数，在运算的过程中都不会考虑BLANK

在计算利润率时，度量值是先把前面的汇总再除于后面的汇总
在计算列时是一行前的数除以一行后的数，然后把每行的率相加得到错误不合逻辑的值

Count系列函数

COUNT 计算指定列中包含非空值的行数。（不包括布尔值）
COUNTA 计算指定列中包含非空值的行数。
COUNTAX 在对表计算表达式的结果时统计非空白结果数。
COUNTBLANK 对列中的空白单元格数目进行计数。
COUNTROWS 统计指定表中或由表达式定义的表中的行数。
COUNTX 在针对表计算表达式的结果时，对包含数字或计算
结果为数字的表达式的行数目进行计数。
DISTINCTCOUNT 对列中的非重复值数目进行计数。
DISTINCTCOUNTNOBLANK 对列中的非重复值数目进行计数。

RELATEDTABLE是一个关系函数。关系函数RELATED函数可以在事实表中使用，通过表关联主键从维度表索取一个
一端表的维度数据。RELATEDTABLE则是在维度表中使用，通过表关联主键从事实表中提取所有满足筛选条件的
行。由于不能使得维度表行数变多，通常RELATEDTABLE函数会结合聚合迭代函数使用。

成交订单金额 = SUMX(
RELATEDTABLE('订单表'), // 根据类型编号去获取订单表的记录，多行多列为表
'订单表'[订货数量] * RELATED('产品表'[产品单价])
)

筛选器函数

FILTER函数

数据分析的本质就是分组聚合，数据列和计算列负责筛选的维度，度量值负责将筛选结果聚合。分组筛选
的维度往往不是单一的，如果想要在度量值聚合的时候添加筛选条件，就需要使用筛选器函数。迭代函数的作用目标是表，
可以对这张表添加筛选条件，从而达到为迭代函数添加筛选。
表函数FILTER函数可以对一张表添加筛选条件，并返回筛选后的表

美国订单金额 =
SUMX(
// 针对表添加美国的筛选
FILTER(
'订单表', // 对哪张表进行筛选
RELATED('国家表'[国家])="美国" // 针对订单表进行何止筛选
),
'订单表'[订货数量] * RELATED('产品表'[产品单价])
)

CALCULATE函数

FILTER是对迭代函数的作用目标表添加筛选条件，使用非常不方便，不能做到随时添加筛选条件。这时可
以使用CALCULATE筛选函数为度量值添加筛选条件。

订单状态为A的美国订单销售额 =
CALCULATE(
sumx('订单表'[订货数量] * RELATED('产品表'[产品单价])),
'订单明细表'[订单状态] = "A",
'国家表'[国家名称] = "美国" )

效果等同于
2022年已发货订单金额 =
SUMX(
FILTER(
'订单表',
RELATED('日期表'[YearName]) = "2022年" && RELATED('订单状态表'[订单状态]) = "已发货"
),
'订单表'[订货数量] * RELATED('产品表'[产品单价])
)

REMOVEFILTERS筛选器修改函数

移除筛选主要应用场景为计算占比。

订单金额（all类别） =
CALCULATE(
[订单金额],
//参数2：可以是筛选器修改函数
REMOVEFILTERS('产品类别表'[产品类别]) //移除某表或某列筛选
)

不同销售同类别占比 =
DIVIDE(
[订单金额],
[订单金额（all类别）]
)

订单金额（美国） =
CALCULATE(
[订单金额],
REMOVEFILTERS('销售顾问表'[销售人员]),
REMOVEFILTERS('产品类别表'[产品类别]),
'国家表'[国家] = "美国"
)

销售金额美国占比 =
DIVIDE(
CALCULATE(
[订单金额],
'国家表'[国家] = "美国"
),
[订单金额（美国）]
)

ALL表函数

相较于REMOVEFILTERS功能一样，但多了个可以筛选表
可以在新建表上用

表 = ALL('订单表'[订单ID],'订单表'[销售ID])
只会出现订单表的这两行

ALLSELECTED()
动态占比 =
DIVIDE(
[订单金额],
CALCULATE(
[订单金额],
ALLSELECTED('销售顾问表'[销售人员])
) // 用这个就可以在外部使用筛选器查看人员的100%占比
)

时间智能函数

同比/环比

同比（同比增长率）是以上年同期为基期相比较，即本期某一时间段与上年某一时间段相比

环比（环比增长率）是与上一个相邻统计周期相比较，即n月与第 n-1月的比较

去年同期订单金额 =
CALCULATE(
[订单金额],
DATEADD(
'日期表'[Date], // 第一个参数必须是日期表的data列
-1, // 偏移量
YEAR // 按照年进行偏移
)
)

同比增长率 = //YOY
DIVIDE(
[订单金额] - [去年同期订单金额],//增长量
[去年同期订单金额]//基期量
)

进阶
YoY% =
VAR period_ =CALCULATE( // 定义一个变量表示去年同期
[订单金额],
DATEADD('日期表'[Date],-1,YEAR)
)
RETURN // 变量只能在return中使用
DIVIDE(
[订单金额] - period_,
period_
)

上月同期订单金额 =
CALCULATE(
[订单金额],
DATEADD(
'日期表'[Date],
-1,
MONTH
)
)

环比增长率MOM% =
DIVIDE(
[订单金额] - [上月同期订单金额],
[订单金额]
)

进阶
MoM% =
VAR period_ =CALCULATE( // 定义一个变量表示去年同期
[订单金额],
DATEADD('日期表'[Date],-1,MONTH)
)
RETURN // 变量只能在return中使用
DIVIDE(
[订单金额] - period_,
period_
)

累计分析

累计分析表示某个指标累计到某个时间的积累量，通常衍生出分析指标达成率

财年累计销售额 =
CALCULATE(
[订单金额],
DATESYTD(
'日期表'[Date],
"12/31")
)

上财年整年销售额 =
CALCULATE(
[订单金额],
PREVIOUSYEAR(
'日期表'[Date],
"12/31" // 个别企业会以6/30作为财年结束)
)

销售额达成率 =
DIVIDE(
[财年累计销售额],
[上财年整年销售额] * 1.1
)

进阶
财年销售额达成率 =
VAR TheYearYTD_ = CALCULATE( // 定义今年累计销售额
[订单金额],
DATESYTD('日期表'[Date],"12/31")
)
VAR LastYearAll_ = CALCULATE( // 定义去年累计销售额
[订单金额],
PREVIOUSYEAR('日期表'[Date],"12/31")
)
VAR r_ = 1.1 // 定义增长率
RETURN
DIVIDE(
TheYearYTD_,
LastYearAll_ * r_
)

累计分析2

累计分析还有一种比较特殊的情况，是整个项目累计至今的销售额，由于YTD还涉及到按年累计，所
以并不能完成累计至今的统计

迄今为止的累计销售额 =
CALCULATE(
[订单金额],
DATESBETWEEN(
'日期表'[Date],
BLANK(),// 相当于1899//12/31，不能写min,会受到是绝对下筛选影响
MAX('日期表'[Date]) //按照月份分组的当月最后一天
)
)

销售总额（all） =
CALCULATE(
[订单金额],
REMOVEFILTERS('日期表'[YearAndMonthNumber],'日期表'[YearAndMonthName])
)

迄今为止的累计销售额 =
CALCULATE(
[订单金额],
DATESBETWEEN(
'日期表'[Date],
BLANK(),// 相当于1899//12/31，不能写min,会受到是绝对下筛选影响
MAX('日期表'[Date]) //按照月份分组的当月最后一天
)
)

期间分析

期间分析是每N个月统计的值，也就是移动滑窗汇总或者移动平均

1-3月从1月往后一单的销售金额
3M移动平均销售额 =
CALCULATE(
[销售均值],
DATESINPERIOD(
'日期表'[Date],
MIN('日期表'[Date]),
3,
MONTH
)
)

3m期间销售额 = CALCULATE(
[订单金额],
DATESINPERIOD(
'日期表'[Date],
MIN('日期表'[Date]),
3,
MONTH
)
)

3M移动平均销售额(往前) =
CALCULATE(
[销售均值],
DATESINPERIOD(
'日期表'[Date],
MAX('日期表'[Date]),
3,
MONTH
)
)

3m期间销售额(往前) =
CALCULATE(
[订单金额],
DATESINPERIOD(
'日期表'[Date],
MAX('日期表'[Date]),
3,
MONTH
)
)

其他计算

销售金额从大到小排序
当dqjye_为第一个时，整列的销售金额>=dqjye_就汇总
返回
当dqjye_为第二个时，整列的销售金额>=dqjye_就汇总
以此类推就能得到累计交易额

销售排名 = RANKX(
ALL('销售顾问表'),
[交易额]
)