Excel
2024-10-21 16:58:44 1 举报AI智能生成
xecel
作者其他创作
大纲/内容
删除区域数据选择区域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
手机号码
正负数不同颜色
数字显示样式
使用=文本数字格/1或+0或- -文本数字格转为纯数字
不规则数字
选择区域——开始——编辑——查找与选择——将.替换/
变为2023/3/10
2023.3.10
不规则日期格式1
变为2022/3/10
220310
不规则日期格式2
选中区域——数据——数据工具——分列——前面两步直接点击——第三步点日期
一步到位
变为文本进行转换
注：在选择替换时复制，然后粘贴为值，然后替换
利用前两者方法
20220310
不规则日期格式3
变为12：20：57
122057
不规则时间格式
数据格式规范
冻结窗口——冻其他行——点击第二行第二列······
视图
冻结窗格
excel基本操作
使用替换将空值换成0
选择查找下拉找定位——选择空值 在编辑框输入0 ctrl加enter
定位空值
定位条件——公式——只勾错误——delete
定位错误
定位条件
复制内容，选择复制的区域——开始的粘贴——选择性粘贴——设置
运算
复制第二列到第一列下方——设一个辅助列——输入1填充全，在下方输入1.2——使用等差序列——点数据——排序——填充列表——排序
将两列数据合成一列（见缝插针）
选择性粘贴
单元格匹配
查找替换
定位单元格
复制首行——定位空值然后复制
设置辅助列——输入1填充全，在下方输入1.2——使用等差序列——点数据——排序——填充列表——排序
各行插入空行
先设置公式——名称管理器——单列或者单行。数据验证——序列——输入=名称。再改变名称的取值范围
多行多列变一个单元格
ctrl+e(填充会自己总结规律)
拆分数据
合并数据
增加空格
位置调整
快速填充
数据——分列——如果以其他字为分列要确保以一个字分列
分列
（行转为列）先分列，将全部复制，然后选择一格，选择性粘贴，转置，（如果要去除其他成分可以快速填充）
分列+选择性粘贴+快速填充
选择填入的区域——数据——合并计算——分别选择要合并的区域然后计算
合并计算
可以同时筛选两个条件
分列练习
在开始样式的条件格式设置
条件格式
excel数据处理
计算公式
公式
绝对引用F4
引用
错误类型
=rand()
生成随机0-1小数
生成一定范围的数
=int(x1)
取整
取余
=round()
四舍五入
=abs()
取绝对值
=sqrt()
算术平方根
数学函数
原数据：ORD202302220001
提取前缀
提取年
提取月
提取编号
=LOWER(A2)
小写全缀
PROPER(A2)
大写首字母
提取
查找与替换
text函数
文本函数
逻辑与判断
统计函数
日期函数
查找引用函数
excel基本公式和函数
选中数据 插入图表 添加图标元素 右键图表打开设置图表区域格式
正常类别不宜超过六个，如果超过六个建议使用条形图或者字母饼图
饼图
堆积折线图无法对比各图例的，只能使用普通折线图。
折线图
簇状条形图：类别与类别比，也可以图例与图例比，但是比不了类别的总量
堆积条形图可以查看总量，也可以某类别图例之间比，也可以某个图例各类别比
百分比堆积条形图可以查看某图例在类别的比重
条形图
看两连续型变量的关系
散点图
子主题
使用条形图——加标签——进入格式设置——增加次坐标轴然后逆序——两条坐标轴设置负值——把多余的东西删除
对比分析
加一条辅助列——将前面的累加（=SUM($B$2:B2)······）——全选，使用堆积簇状条形图——右击选择数据——下移，其他按要求设置
或直接全选设置瀑布图——将汇总的右击设置汇总
瀑布图
选择数据——条形堆积——设置坐标轴格式——逆序类别——选择数据逆序把辅助列变成无填充
或者直接生成
漏斗图
复杂不如透视表
动态图
有三种
雷达图
选择数据——簇状条形图——切换行列——添加元素的线条
增加辅助列——圆环图——切换行列——设置数据系列格式——系列选项圆环大小——颜色一条一条设置
商业图表
其他图
excel可视化
选定数据——插入——表格——取名
选择插入的位置——插入——数据透视表——可以选定范围或者=某某表
值有很多选项
有行、列、值、筛选
双击透视表可以看到具体数据
二维表转为一维
选择多重合并计算
插入——切片器
锁定切片器——右击设置形状大小——大小与属性——锁定取消
数据透视表
提取可以不进入query，清洗必须进入
文件，选项，可以选择用不用类型检测
标题旁边可以选择数据类型
视图的列分发和列质量可以看到信息
主页和转换有第一行用作标题
选择列，然后设置其他内容
选择要透视的几列后就行了
逆透视
透视表
功能
数值列
日期列
转换
示例中的列
自定义的列（只有这个函数有用，在上面的无法实现）
常规
选中列后
从文本
其他跟转换一样
添加列
追加的列数必一样
当追加的某列的值不一样可以新建一列备注数据是什么
追加查询
上下拼接
当有重复项时如学号姓名可以添加索引列
如果有可当索引列的可以用来当主键
左右拼接
跟左右拼接一样，当有名字重复时要用聚合的最大值
联接
数据合并
数据提取清洗等 power query
关系图里事实表一张，维度表有多张维度表负责筛选，事实表负责聚合
在关系视图界面点击事实表的键连接维度表
多张表建立关系
加减乘除幂
算术运算符
运算符将两个值进行比较结果为逻辑值（TRUE 或 FALSE）
比较运算符
逻辑运算符 (&&) 和 (||) 组合
逻辑运算符
& （与号） 连接或串联两个值以生成一个连续文本值
文本连接符
DAX中有四种运算符
日期时间函数
FIND ：返回一个文本字符串在另一个文本字符串中的起始位置。LEFT、RIGHT： 从文本字符串开头（结尾）返回指定数量的字符。LEN ：返回文本字符串中的字符数。MID ：在提供开始位置和长度的情况下，从文本字符串中间返回字符串。REPLACE ：根据指定的字符数，将部分文本字符串替换为不同的文本字符串。SUBSTITUTE： 在文本字符串中将现有文本替换为新文本。COMBINEVALUES： 将两个或更多个文本字符串联接成一个文本字符串。EXACT ：比较两个文本字符串，如果它们完全相同，则返回 TRUE；否则返回 FALSE。LOWER、UPPER ：将文本字符串中的所有字母都转换为小写（大写）。VALUE ：将表示数值的文本字符串转换为数值
格式转换
创建日期表
函数RELATED实现从其他表取值。事实表从维度表索取相关列，必须表和表之间建立可用关系才能使用。
（运算重点）
RELATEDTABLE是维度表从事实索取
关系函数
• AND与判断。检查两个参数是否均为 TRUE，如果两个参数都是 TRUE，则返回 TRUE。 • OR或判断。检查某一个参数是否为 TRUE，如果是，则返回 TRUE。 • NOT 非判断。将 FALSE 更改为 TRUE，或者将 TRUE 更改为 FALSE
• SWITCH有两种常用场景，单值条件判断和多值条件判断。两种条件判断写法不同。• 单值条件判断下SWITCH的第一个参数往往写的是列名，条件是当列名的值等于某值时候，结果是什么。• 多值条件判断下SWITCH的第一个参数往往写的是布尔值，条件的结果是布尔值，结果再是什么。
逻辑函数
处理空值
计算列和行上下文
销售额汇总 = SUM('订单明细表'[订单总金额])销售额均值 = AVERAGE('订单明细表'[订单总金额])销售额最大值 = MAX('订单明细表'[订单总金额])销售额最小值 = MIN('订单明细表'[订单总金额])
聚合函数
创建度量值表只放度量值主页的输入数据，创建先建立一个度量值，然后把另一个删了就成了度量值表
销售额汇总度量值：使用了计算列“订单总金额”列。内存增加。销售额汇总2度量值：没有新建计算列，使用原数据表的“商品单价”和“订货数量”。不增加内存。
针对单列数据计算，无差别无论是迭代函数还是聚合函数，在运算的过程中都不会考虑BLANK
在计算利润率时，度量值是先把前面的汇总再除于后面的汇总在计算列时是一行前的数除以一行后的数，然后把每行的率相加得到错误不合逻辑的值
迭代函数
COUNT 计算指定列中包含非空值的行数。（不包括布尔值）COUNTA 计算指定列中包含非空值的行数。COUNTAX 在对表计算表达式的结果时统计非空白结果数。COUNTBLANK 对列中的空白单元格数目进行计数。COUNTROWS 统计指定表中或由表达式定义的表中的行数。COUNTX 在针对表计算表达式的结果时，对包含数字或计算结果为数字的表达式的行数目进行计数。DISTINCTCOUNT 对列中的非重复值数目进行计数。DISTINCTCOUNTNOBLANK 对列中的非重复值数目进行计数。
RELATEDTABLE是一个关系函数。关系函数RELATED函数可以在事实表中使用，通过表关联主键从维度表索取一个一端表的维度数据。RELATEDTABLE则是在维度表中使用，通过表关联主键从事实表中提取所有满足筛选条件的行。由于不能使得维度表行数变多，通常RELATEDTABLE函数会结合聚合迭代函数使用。
Count系列函数
数据分析的本质就是分组聚合，数据列和计算列负责筛选的维度，度量值负责将筛选结果聚合。分组筛选的维度往往不是单一的，如果想要在度量值聚合的时候添加筛选条件，就需要使用筛选器函数。迭代函数的作用目标是表，可以对这张表添加筛选条件，从而达到为迭代函数添加筛选。表函数FILTER函数可以对一张表添加筛选条件，并返回筛选后的表
font color=\"#e9d66f\
FILTER函数
FILTER是对迭代函数的作用目标表添加筛选条件，使用非常不方便，不能做到随时添加筛选条件。这时可以使用CALCULATE筛选函数为度量值添加筛选条件。
订单状态为A的美国订单销售额 = font color=\"#e9d66f\
效果等同于2022年已发货订单金额 = SUMX( FILTER(font color=\"#e9d66f\
CALCULATE函数
移除筛选主要应用场景为计算占比。
订单金额（all类别） = font color=\"#e9d66f\
不同销售同类别占比 = font color=\"#e9d66f\
订单金额（美国） = font color=\"#e9d66f\
销售金额美国占比 = font color=\"#e9d66f\
REMOVEFILTERS筛选器修改函数
相较于REMOVEFILTERS功能一样，但多了个可以筛选表可以在新建表上用
ALLSELECTED()动态占比 = font color=\"#e9d66f\
ALL表函数
筛选器函数
同比（同比增长率）是以上年同期为基期相比较，即本期某一时间段与上年某一时间段相比
环比（环比增长率）是与上一个相邻统计周期相比较，即n月与第 n-1月的比较
同比增长率 = //YOYfont color=\"#e9d66f\
进阶font color=\"#fd1625\
去年同期订单金额 = font color=\"#e9d66f\
环比增长率MOM% = font color=\"#e9d66f\
上月同期订单金额 = font color=\"#e9d66f\
同比/环比
累计分析表示某个指标累计到某个时间的积累量，通常衍生出分析指标达成率
销售额达成率 = font color=\"#e9d66f\
进阶财年销售额达成率 = font color=\"#fd1625\
上财年整年销售额 = font color=\"#e9d66f\
财年累计销售额 = font color=\"#e9d66f\
累计分析
累计分析还有一种比较特殊的情况，是整个项目累计至今的销售额，由于YTD还涉及到按年累计，所以并不能完成累计至今的统计
迄今为止的累计销售额 = font color=\"#e9d66f\
销售总额（all） = font color=\"#e9d66f\
累计分析2
期间分析是每N个月统计的值，也就是移动滑窗汇总或者移动平均
3m期间销售额 =font color=\"#e9d66f\
1-3月从1月往后一单的销售金额3M移动平均销售额 = font color=\"#e9d66f\
3m期间销售额(往前) = font color=\"#e9d66f\
3M移动平均销售额(往前) = font color=\"#e9d66f\
期间分析
时间智能函数
度量值与筛选上下文
销售金额从大到小排序当dqjye_为第一个时，整列的销售金额>=dqjye_就汇总返回当dqjye_为第二个时，整列的销售金额>=dqjye_就汇总以此类推就能得到累计交易额
其他计算
power pivot
powerBI
excel
0 条评论
回复 删除
下一页
