函数/域集团 - 随笔分类(第11页) - 熬肥妖

用sumif取代vlookup

摘要：问题：将补贴表里的数据按姓名填入工资表中。解题思路：这是一个典型的查找问题，可以使用VLookup函数。 =VLOOKUP(B3,P:Q,2,) 事实上，只要单位里不存在同名同姓，这题还可以用Sumif解决，不仅公式简单，还不需要在出现找不到时进行屏蔽错误值的处理。 =SUMIF(P:P,B3,Q 阅读全文

posted @ 2022-10-29 18:08 熬肥妖阅读(209) 评论(0) 推荐(0)

提取学校名

摘要：问题：学校名和专业混在一个单元格里，已知学校是“学院”“大学”或“中专” 函数公式解决： {=LEFT(A1,MAX(IFERROR(FIND(B$6:B$8,A1),))+1)} 思路：用Find查找三种学校在A1中出现的位置，不存在的（中专）返回错误值利用Iferror将错误值转成0 计算F 阅读全文

posted @ 2022-10-15 18:21 熬肥妖阅读(55) 评论(0) 推荐(0)

根据区间返回周数

摘要：问题：如果将时间区间转成周数函数公式解决： =WEEKNUM(LEFT(F3,FIND("-",F3,9)-1),2) 思路：利用Find查找“-”在F3中从第9个字符开始的位置，可以得出第三个横杠的位置，再用Left函数将左边的日期提取出来再利用WeekNum返回其所在周数最终结果是否需要阅读全文

posted @ 2022-10-15 17:57 熬肥妖阅读(42) 评论(0) 推荐(0)

条件区域循环的Sumif

摘要：问题：Sumif条件为D12:D16，求和区域从E3:E8向右，条件区域为B3:D8三列循环函数解决： =SUMIF(OFFSET($B$3:$B$8,,MOD(COLUMN(C1),3)),$D12,E$3:E$8) 思路：利用Mod(Column(C1),3)，右拉生成0、1、2、0、1、2 阅读全文

posted @ 2022-10-08 22:07 熬肥妖阅读(159) 评论(0) 推荐(0)

预防老年痴呆的公式

摘要：问题：根据各员工的百分比显示员工姓名函数公式解决：解法一：=LOOKUP(A2/MAX(A:A),MMULT(N({1,2,3}<{2;3;4}),N(IF({1},H$1:H$3)))+9^-9,G$2:G$4) 解法二：=LOOKUP(A2/MAX(A:A)-1%%,SUBTOTAL(9,O 阅读全文

posted @ 2022-10-08 21:42 熬肥妖阅读(63) 评论(0) 推荐(0)

横向排列每天的考勤时间

摘要：问题：根据姓名和日期，横向排列考勤时间。函数公式解决：姓名日期（E:F列）内存数组公式：{=UNIQUE(Sheet1!$A$1:$B$47)} 时间下拉数组公式：{=INDEX(SORT(IF((Sheet1!$A$2:$A$47=$E2)*(Sheet1!$B$2:$B$47=$F2),Sh 阅读全文

posted @ 2022-10-08 21:17 熬肥妖阅读(195) 评论(0) 推荐(0)

找出完全相同的行-函数

摘要：问题：找出两个表中完全相同的行函数解决： {=FILTER(A2:C6,MMULT((A2:C6=E2:G6)*1,{1;1;1})=3)} MMult函数计算规则：最后利用Filter函数筛选出MMult函数结果为3的行。阅读全文

posted @ 2022-10-02 17:11 熬肥妖阅读(58) 评论(0) 推荐(0)

一簿N表汇总

摘要：问题：一个工作簿中有按月分的N个工作表，按编号、月份、指标汇总，每个工作表的结构如下图。函数解决： =SUMIF(INDIRECT(INT(COLUMN(B1)/2)&"月!A:A"),$A3,INDIRECT(INT(COLUMN(B1)/2)&"月!c"&MOD(COLUMN(B1),2)+2 阅读全文

posted @ 2022-10-02 16:43 熬肥妖阅读(98) 评论(0) 推荐(0)

4*7矩阵转成3*10矩阵

摘要：问题：4*7矩阵转成3*10矩阵函数解决： {=INDEX(T(OFFSET($A$1,(ROW($1:$30)-1)/7,MOD(ROW($7:$36),7))),ROW(A1)*3+COLUMN(A1)-3)} 思路：先将4*7矩阵转换成1*30矩阵的内存数组。转换使用Offset函数，第阅读全文

posted @ 2022-10-02 16:26 熬肥妖阅读(60) 评论(0) 推荐(0)

地址与电话拆分成两列

摘要：问题：从地址与电话的混搭中拆分出彼此函数解决：电话公式：=MIDB(RIGHT(A2,12),SEARCHB("?",RIGHT(A2,12)),12) 地址公式：=LEFT(A2,LEN(A2)-LEN(C2)) 思路：先完成电话公式。电话号码（包含区号与电话之间的分隔符）和手机号最多12 阅读全文

posted @ 2022-10-02 16:05 熬肥妖阅读(758) 评论(0) 推荐(0)

预测函数

摘要：问题：函数解决： =FORECAST(2023,FILTER(B$2:B$15,$A$2:$A$15=$A19),{2020,2021,2022}) =Forecast(预测点x值,已知y值集合,已知x值集合) 示例中，预测点x值是2023年的年份，已知x值集合是过去三年的年份，因数据相对固定，故阅读全文

posted @ 2022-09-24 18:21 熬肥妖阅读(291) 评论(0) 推荐(0)

批量替换不同内容

摘要：问题：表1里的“原内容”列替换为表2里对应的“替换为”列函数解决： =VLOOKUP(A3,F:G,2,) 阅读全文

posted @ 2022-09-24 18:14 熬肥妖阅读(267) 评论(0) 推荐(0)

多对一查找四种函数公式

摘要：问题：多对一查找函数解决： {=INDEX(C:C,MATCH(F2&G2,A:A&B:B,))} =XLOOKUP(F2&G2,A:A&B:B,C:C,"查无此妖") =FILTER(C$1:C$7,(A$1:A$7=F2)*(B$1:B$7=G2),"查无此妖") =SUMIFS(C:C,A: 阅读全文

posted @ 2022-09-18 19:02 熬肥妖阅读(1145) 评论(0) 推荐(0)

提取出现次数最多的四个数

摘要：问题：假设数据源在A1:A20 函数解决公式1 =MOD(LARGE(UNIQUE(COUNTIF(A$1:A$20,A$1:A$20)/1%+A$1:A$20),ROW(A1)),100) 公式2： =MODE(IF(COUNTIF(F$1:F1,A$1:A$20),1=1,A$1:A$20) 阅读全文

posted @ 2022-09-11 18:36 熬肥妖阅读(73) 评论(0) 推荐(0)

根据价格返回所属价格段

摘要：问题：根据价格返回所属价格段，从100起，步长199。函数解决： =FLOOR(D2,200)+100&"-"&CEILING(D2,200)+99 阅读全文

posted @ 2022-09-11 18:27 熬肥妖阅读(40) 评论(0) 推荐(0)

价格段公式

摘要：问题：从100起，生成步长199的价格段公式函数解决： =ROW(A1)*200-100&"-"&ROW(A1)*200+99 阅读全文

posted @ 2022-09-11 18:23 熬肥妖阅读(369) 评论(0) 推荐(0)

按日期段计算平均销量

摘要：问题：某个产品近3天、近7天、近30天各自的日均销量解决：近3天：{=AVERAGE(IF(NOW()-A2:A28<3,B2:B28))} 近7天：{=AVERAGE(IF((NOW()-A2:A28<7)*(NOW()-A2:A28>=3),B2:B28))} 近30天：{=AVERAGE( 阅读全文

posted @ 2022-08-27 15:30 熬肥妖阅读(360) 评论(0) 推荐(0)

人民币填空

摘要：问题：按人民币金额将大写数字填于空处解决： {=TEXTJOIN({"拾","万","千","百","拾","元","角"},,TEXT(LEFT(RIGHT("　"&ROUND($A2,2)*100,9-COLUMN(A:H))),"[DBNum2]0"))&"分"} 阅读全文

posted @ 2022-08-20 20:50 熬肥妖阅读(32) 评论(0) 推荐(0)

Filter、Unique如何跳过隐藏行

摘要：问题：Filter、Unique如何跳过隐藏行数据源为A2:B12 以下公式对筛选同样有效 Filter =IFERROR(INDEX(FILTER($A$2:$B$12,($A$2:$A$12="乙")*SUBTOTAL(103,OFFSET($A$1,ROW($1:$11),))),ROW(A 阅读全文

posted @ 2022-08-20 20:45 熬肥妖阅读(256) 评论(0) 推荐(0)

简单的小问题

摘要：问题：A37输入内容后，H37自动弹出0.845 函数解决： =IF(A37<>"",0.845,"") 阅读全文

posted @ 2022-08-13 17:01 熬肥妖阅读(33) 评论(0) 推荐(0)

熬肥妖

随笔分类 - 函数/域集团

公告