12 2022 档案

汉字数字按数值规则排序
摘要：问题：汉字数字（一、二、三等）按数值规则排序 函数公式解决： {=SORTBY(A3:A8,LOOKUP(LEFT(A3:A8),{"八",8;"二",2;"九",9;"六",6;"七",7;"三",3;"四",4;"五",5;"一",1})*100+MID(A3:A8,2,3))} 常量部分是汉字 阅读全文
posted @ 2022-12-31 17:06 熬肥妖 阅读(297) 评论(0) 推荐(0)
填写开票数量
摘要：问题：填写开票数量，每次数量不超过B列的数量，累计不超过F列的总数 A品的开票数量依次为100、0、0 B品的开票数量依次为200、30、0 C品的开票数量依次为200、200、20、0 函数公式解决： =MIN(B2,MAX(,SUMIF(E:E,A2,F:F)-SUMIF(A$1:A1,A2,B 阅读全文
posted @ 2022-12-31 16:17 熬肥妖 阅读(144) 评论(0) 推荐(0)
统计考勤表中的不同加班
摘要：问题：下图中的考勤分别统计 如果表中有星期数据在第9行，且以数值显示（Weekday(日期,2)的结果），假日加班公式如下： =SUMIF(B$9:AE$9,">5",B3:AE3) 如果表中有星期数据在第10行，且以中文显示（Text(日期,"aaa")的结果），延时加班公式如下： =SUMIFS 阅读全文
posted @ 2022-12-30 16:54 熬肥妖 阅读(404) 评论(0) 推荐(0)
计算占比差异（Power Pivot）
摘要：用数据透视表实现以下功能： 数据1占比：=C2/SUMIF(A:A,A2,C:C) 数据2占比：=D2/SUMIF(A:A,A2,D:D) 占比差异：=F2-G2 创建数据透视表时勾选【将此数据添加到数据模型】 进入Power Pivot：【数据】》【管理数据模型】 Dax公式如下： 1占比:=su 阅读全文
posted @ 2022-12-29 16:08 熬肥妖 阅读(1133) 评论(0) 推荐(0)
完整的科目名称（Excel函数集团）
摘要：递归之三——完整的科目名称一文中的示例，如果不用Lambda，倒也不是就完全无解，公式可以写成： =TEXTJOIN(" / ",,IFERROR(XLOOKUP(--LEFT(A2,LEN(A2)-{4,2,0}),A:A,B:B),"")) 公式中，用Left函数分别提取A2单元格的，总长度减4 阅读全文
posted @ 2022-12-28 14:35 熬肥妖 阅读(224) 评论(0) 推荐(0)
关于文档损坏或丢失的那些事……
摘要：小妖同学几乎每天都会遇到有同学问某文档损坏了丢失了怎么办？通常我都会先问句“备份了没？”，基本上得到的回答都是“没有”，如果有备份还会来问么？然后从我指尖冒出来的就是三个字“请节哀”！ 这个回答当然是被很多同学批评啦~ 备份大家都在做，可一旦发生文档丢失或损坏，损失还是很大，因为……在此引用某色（E 阅读全文
posted @ 2022-12-23 14:42 熬肥妖 阅读(118) 评论(0) 推荐(0)
递归之三——完整的科目名称（Excel函数集团）
摘要：使用了递归的Lambda，参数必须是序列数吗？ 当然不是！ 来看看这个例子：根据科目代码和科目名称，用公式完成完整的科目名称 自定义的名称是Itm，Itm的参数是Lambda中定义的参数x，也就是A列。公式如下： Itm=LAMBDA(x,IF(LEN(x)>4,Itm(--LEFT(x,LEN(x 阅读全文
posted @ 2022-12-22 17:47 熬肥妖 阅读(254) 评论(0) 推荐(0)
递归之二（Excel函数集团）
摘要：递归，应该算是个数学问题吧，但它并不只能解决数学问题，还可以解决Excel里的迭代问题。 ExcelHome的系列丛书之一，《Excel2019函数与公式应用大全》的第481页示例25-4，就是一个带计算器的迭代问题，这类问题不用迭代也可以，但需要加辅助列：E列是序列数，F列是计算结果，当E列大于2 阅读全文
posted @ 2022-12-22 17:28 熬肥妖 阅读(248) 评论(0) 推荐(0)
递归之一（Excel函数集团）
摘要：递归，这名词出现在了Excel函数集团，是的，你没看错！ 但递归在工作表函数里，也不是无限制的用，而是有以下条件： 需要Lambda出马 需要一个开关 需要自定义名称 先祭一个最简单的例子，定义一个名称，其参数是几，结果就是几。 x是Lambda自定义的参数，也是Fx的参数 x是数值 开关可以用If 阅读全文
posted @ 2022-12-22 17:07 熬肥妖 阅读(300) 评论(0) 推荐(0)
工作表函数的代码嘴脸（可定义名称的函数合集）
摘要：2024-1-25更新 综合练习： 转换结构-一列转二维表 依次替换 递归示例 Let LET函数 Lambda Lambda公式，函数公式or函数名？ 递归之一 递归之二 递归之三——完整的科目名称 MakeArray 华丽丽地越过了多维引用的MakeArray ByRow/ByCol 有了ByR 阅读全文
posted @ 2022-12-22 16:03 熬肥妖 阅读(245) 评论(1) 推荐(0)
带条件的矩阵去重合并（Power Query）
摘要：问题：根据条件将矩阵中的唯一值合并到一个单元格内 let 源 = Excel.CurrentWorkbook(){[Name="表1"]}[Content], 逆透视的其他列 = Table.UnpivotOtherColumns(源, {"条件"}, "属性", "值"), 删除的列 = Tabl 阅读全文
posted @ 2022-12-17 17:58 熬肥妖 阅读(160) 评论(0) 推荐(0)
矩阵去重合并（Power Query）
摘要：问题：保留矩阵中的唯一值，再合并到一起 let 源 = Excel.CurrentWorkbook(){[Name="表1"]}[Content], 逆透视的列 = Table.UnpivotOtherColumns(源, {}, "属性", "值"), 删除的列 = Table.RemoveCol 阅读全文
posted @ 2022-12-17 17:53 熬肥妖 阅读(139) 评论(0) 推荐(0)
隐藏人名的第二个字
摘要：问题：将人名的第二个字改成* 函数公式解决： =REPLACE(A1,2,1,"*") 阅读全文
posted @ 2022-12-17 17:33 熬肥妖 阅读(219) 评论(0) 推荐(0)
按行数拆分工作表
摘要：问题：300行数据，按行数拆分，每个工作表30行，共拆成10个工作表。 解决： 第一步，添加辅助列（I列），I1:I30中输入数字1 第二步，I31输入=I1+1，下拉至I300 第三步，数据》拆分表格》工作表 第四步，设置拆分，依据：I列；拆分到不同的新工作表 拆分效果 第五步，选取10个工作表， 阅读全文
posted @ 2022-12-17 17:30 熬肥妖 阅读(323) 评论(0) 推荐(0)
根据业务模式和比例计算提成比例
摘要：问题：根据A:B两列给定的条件，计算不同业务模式下不同比例对应的提成比例 解决方案： 第一步，修改A:B两列数据如下图A:C 第二步：函数公式解决 =LOOKUP(F3,OFFSET(B$2:C$2,MATCH(E3,A:A,)-2,,COUNTIF(A:A,E3))) Offset部分提取的是对应 阅读全文
posted @ 2022-12-17 17:18 熬肥妖 阅读(76) 评论(0) 推荐(0)
从思维导图到PPT，只需要一秒钟
摘要：第一步：打开WPS，创建思维导图 第二步：完善思维导图 第三步：导出》脑图PPT 第四步：选择PPT风格》保存PPT 完成 阅读全文
posted @ 2022-12-17 15:49 熬肥妖 阅读(755) 评论(0) 推荐(0)
复赛和排名（分组排名）
摘要：问题1：按不同科目进行排名 函数公式解决： =SUM(1*IF(B$2:B$25=B2,C$2:C$25>=C2)) 此公式不受BC两列顺序的影响 问题2：排名前30%的可以参加复赛 函数公式解决： =IF(SUM(1*IF(B$2:B$25=B2,C$2:C$25>=C2))<=ROUND(COU 阅读全文
posted @ 2022-12-17 15:40 熬肥妖 阅读(90) 评论(0) 推荐(0)
带条件的矩阵去重合并
摘要：问题：同一条件下所有人员去重后合并到一个单元格 函数公式解决： =CONCAT(UNIQUE(IFERROR(INDEX(FILTER(B$2:D$10,A$2:A$10=F2),N(IF(1,ROW($3:$20)/3)),N(IF(1,MOD(ROW($3:$20),3)+1))),""))) 阅读全文
posted @ 2022-12-17 15:29 熬肥妖 阅读(87) 评论(0) 推荐(0)
矩阵去重合并
摘要：问题：A2:C5区域去除重复项后再合并到一个单元格内 函数公式解决： =CONCAT(UNIQUE(T(OFFSET(A1,ROW(3:14)/3,MOD(ROW(3:14),3))))) ROW(3:14)/3生成1、1、1、2、2、2、3、3、3……这样的重复序列 MOD(ROW(3:14),3 阅读全文
posted @ 2022-12-17 15:18 熬肥妖 阅读(101) 评论(0) 推荐(0)
循环引用
摘要：问题：如何解决循环问题 找到循环引用的单元格，删除或重写其中的公式。 循环的种类： A1单元格引用=B1，同时B1单元格引用=A1 A2单元格引用=A1，A2向下填充至A8，同时A1单元格引用=A8 C3单元格里输入公式=OFFSET(A1,2,2)，公式虽然没有引用C3，但公式的结果指向的是C3单 阅读全文
posted @ 2022-12-17 15:06 熬肥妖 阅读(174) 评论(0) 推荐(0)
同样华丽丽地越过了多维引用的Map（Excel函数集团）
摘要：先看看这个：华丽丽地越过了多维引用的MakeArray 再看看这个：有了ByRow和ByCol，MM函数还要怎么混？ 原来，能越过多维引用的不止以上几位，还有一位Map 题目要求1，将A10:C17这个区域里的每一行进行最大的计算，再把结果进行求和； 题目要求2：将A10:C17这个区域里的每一行进 阅读全文
posted @ 2022-12-03 18:19 熬肥妖 阅读(178) 评论(0) 推荐(0)
筛选后去重
摘要：问题：统计各区县物业点的数量，物业点需要去重重复项 函数公式解决： =COUNTA(UNIQUE(FILTER(K$2:K$21,A$2:A$21=M3))) 思路： 利用Filter找出各区所有物业点，在外面套Unique函数去重重复项，最后再用Counta计数。 阅读全文
posted @ 2022-12-03 16:41 熬肥妖 阅读(101) 评论(0) 推荐(0)
只显示一半的水平轴标签
摘要：问题：图表/数据透视图的水平轴标题只显示一半 普通图表 将“分类”列移出图表的数据源，适当调“产品”列数据位置 数据透视图 在数据源添加辅助列，使用函数公式 =REPT(" ",COUNTIF(A$2:A2,A2)) 数据透视表中以“辅助列”代替“分类”列 阅读全文
posted @ 2022-12-03 16:35 熬肥妖 阅读(245) 评论(0) 推荐(0)