随笔分类 - 函数/域集团
这是26个字母10个数字和一堆符号纠结的故事！
摘要：问题：导航表中制作各工作表目录和各表数据汇总 函数公式解决： B4公式 =HYPERLINK("#"&A4&"!a1",A4) C4公式 =SUM(INDIRECT(A4&"!G:G"))
阅读全文
摘要：问题：根据总表中指定条件，跳转到分表中的指定数据。 子表只有一个“数据表1”： =IF(COUNTIFS(数据表1!A:A,A2),HYPERLINK("#数据表1!a"&MIN(IF(A2=数据表1!A$2:A$7,ROW($2:$7)))&":d"&MAX(IF(A2=数据表1!A$2:A$7,
阅读全文
摘要：问题：未使用自定义名称也能实现递归？ 递归公式： =LAMBDA(x,IF(x,m(x-1)+3,1))(100) 使用以上公式需要自定义名称m，计算结果是301。 如果不使用自定义名称，可以使用以下公式： =LET(m,LAMBDA(x,IF(x,m(x-1)+3,1)),m(100)) 另外，为
阅读全文
摘要：使用Lambda函数实现递归可以不使用自定义名称。 以斐波那契数列为例，使用自定义名称的公式如下： fs=LAMBDA(x,IF(x<=2,1,fs(x-1)+fs(x-2))) fs是自定义的名称，单元格中的公式如下： =fs(B5) 不使用自定义名称的公式如下： =LET(fx,LAMBDA(n
阅读全文
摘要：问题：找出每种商品在数据源中与指定日期最近的单价 函数公式解决： =SUM((MIN(ABS(B2-B$11:B$16))=ABS(B2-B$11:B$16))*(A2=A$11:A$16)*C$11:C$16) 公式使用了Sum多条件求和的格式化用法： =Sum((条件区域1=条件1)*(条件区域
阅读全文
摘要：问题：根据A1的字符数，生成动态的序列数，如A1有5个字符则生成1、2、3、4、5序列数。 =ROW(INDIRECT("1:"&LEN(A1))) =SEQUENCE(LEN(A1)) Row函数的参数只能是引用，如需要让引用的内容动态化，可以使用间接引用，即嵌套Indirect。 Sequenc
阅读全文
摘要：问题：A1包含“大”字，B1返回s，否则返回m。 函数公式解决： =IF(ISNUMBER(FIND("大",A1)),"s","m") =IF(COUNTIF(A1,"*大*"),"s","m") 都是套路了……
阅读全文
摘要：问题：高亮显示非0最小值 条件格式解决：选取数据区域（B4:J4）》开始》条件格式》新建规则》使用公式》设置条件 =MINIFS($B4:$J4,$B4:$J4,">0")=B4 多说一句：条件格式中的公式相当于If函数的第一参数。
阅读全文
摘要：问题：多条件查找，条件有列有行，结果在一矩阵中 解决方法很多，举两例： 查找公式 =XLOOKUP(LOOKUP("做",$A$17:$A17),$A$4:$A$13,XLOOKUP($B17&C$16,$B$3:$J$3&$B$2:$J$2,$B$4:$J$13)) 求和公式 =SUM(($A$4
阅读全文
摘要：展开编号 =DROP(fx(COUNTA(B:B)-1),1) fx=LAMBDA(x,IF(x>0,VSTACK(fx(x-1),SEQUENCE(INDEX(Sheet4!$B:$B,x+1),,INDEX(Sheet4!$C:$C,x+1))))) 使用Lambda定义x 当x小于等0时，返回
阅读全文
摘要：问题：根据编号展开内容 函数公式解决： 产品=TOCOL(IF(SEQUENCE(,MAX(B:B))>B2:B4,a,A2:A4),2) 编号=DROP(fx(COUNTA(A:A)-1),1) 其中fx=LAMBDA(x,IF(x>0,VSTACK(fx(x-1),SEQUENCE(INDEX(
阅读全文
摘要：p142，第二行： 通用公式写法有误，应为： =CEILING.MATH(DATE(指定年份,指定月份,7(n-1)-k),7)+k+1 特别感谢 @黄焕龙 同学捉虫的见义勇为行为！
阅读全文
摘要：问题：A列连续相同的项目对应金额的和，注意：相同项目名不连续的不合计。 函数公式解决： =IF(A2=A1,"",SUM(B2:B16)-SUM(D3:D16)) 这是解决合并单元格求和的“套路”，用同行开始到数据表最后所有数据的和减去公式所在单元格下一个单元格至最后所有数据的和。
阅读全文
摘要：问题：间接引用Indirect函数如何使用相对引用 Indirect函数使用A1样式可以和Row、Column等函数嵌套，实现类似相对引用的效果。正经相对引用需要使用R1C1样式，相对引用的写法如下图：
阅读全文
摘要：问题：存在包含指定内容的返回“是”，否则返回“否”。 函数公式解决： 查找范围为同行：=IF(COUNTIF(B2,"*"&A2&"*"),"是","否") 查找范围不限定行：=IF(COUNTIF(B:B,"*"&A2&"*"),"是","否") CountIf的条件使用前后都带通配符星号，表示“
阅读全文
摘要：问题：数据源在不同分表，需要将符合条件的数据查找出来。 函数公式解决： =XLOOKUP($A2,VSTACK('1:3'!$A$1:$A$15),VSTACK('1:3'!B$1:B$15)) Vstack用以将三个分表进行纵向堆叠，以此作为Xlookup的查找区域和结果区域。
阅读全文
摘要：问题：对数据进行随机排序 函数公式解决： =SORTBY(A2:B21,RANDARRAY(20) =SORTBY(A2:B21,RANDARRAY(COUNTA(A:A)-1)) 以RandArray作为SortBy的排序依据
阅读全文
摘要：问题：将表2的数据2填入表1数据2中，表1数据1为简称，表2数据1为全称 函数公式解决： =SUMIF(D:D,@SORTBY(D$2:D$9,MMULT(COUNTIF(A2,"*"&MID(D$2:D$9,SEQUENCE(,9),1)&"*")*(SEQUENCE(,9)<=LEN(D$2:D
阅读全文
摘要：问题：如下图A2:A5数据，包装尺寸在一个单元格里如何计算其表面积 A2数据结构：立方体1长*宽*高+立方体2长*宽*高…… A5数据结构：立方体1长*宽*高*数量+立方体2长*宽*高*数量 借助辅助单元格以函数公式解决 第一步，按“+”拆分单元格 B2公式：=TEXTSPLIT(A2,"+") 公
阅读全文
摘要：问题：根据J列数据分别统计等于1的客户数；大于1小于等于5的客户数；大于5小于等于7的客户数；大于7小于等于10的客户数；大于10小于等于15的客户数 函数公式思路1： P4公式 =COUNTIF(J:J,O4) P5公式 =COUNTIFS(J:J,">"&O4,J:J,"<="&O5) P4公式
阅读全文
浙公网安备 33010602011771号