随笔分类 - 函数/域集团
这是26个字母10个数字和一堆符号纠结的故事！
摘要：问题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
阅读全文
摘要：问题：同一条件下所有人员去重后合并到一个单元格 函数公式解决： =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))),"")))
阅读全文
摘要：问题：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
阅读全文
摘要：问题：如何解决循环问题 找到循环引用的单元格，删除或重写其中的公式。 循环的种类： A1单元格引用=B1，同时B1单元格引用=A1 A2单元格引用=A1，A2向下填充至A8，同时A1单元格引用=A8 C3单元格里输入公式=OFFSET(A1,2,2)，公式虽然没有引用C3，但公式的结果指向的是C3单
阅读全文
摘要：先看看这个：华丽丽地越过了多维引用的MakeArray 再看看这个：有了ByRow和ByCol，MM函数还要怎么混？ 原来，能越过多维引用的不止以上几位，还有一位Map 题目要求1，将A10:C17这个区域里的每一行进行最大的计算，再把结果进行求和； 题目要求2：将A10:C17这个区域里的每一行进
阅读全文
摘要：问题：统计各区县物业点的数量，物业点需要去重重复项 函数公式解决： =COUNTA(UNIQUE(FILTER(K$2:K$21,A$2:A$21=M3))) 思路： 利用Filter找出各区所有物业点，在外面套Unique函数去重重复项，最后再用Counta计数。
阅读全文
摘要：问题：计算每月前一个星期日是几号 函数解决： =CEILING(DATE(K5,1,1)-7,7)+1 下载地址
阅读全文
摘要：问题：从总成绩表中找出部分同学部分科目的成绩，不同班级的同学存在同名同姓。 函数公式解决： =SUMIFS(OFFSET($B:$B,,MATCH(K$1,$C$1:$G$1,)),$B:$B,$J2,$A:$A,$I2) 思路： 当查找结果为唯一数值时，可以用Sumifs代替查找函数 =Sumif
阅读全文
摘要：问题：找出三列中的共同 函数公式解决： {=FILTER(C1:C3,COUNTIF(A:A,C1:C3)*COUNTIF(E:E,C1:C3))} 思路： 分别用两个Countif返回C列中存在于A列和D列的个数，如有存在返回1，否则返回0 将两个Countif相乘 以此作为Filter函数的第二
阅读全文
摘要：问题：一至九全部出现至少一次后进行标注 函数公式解决： B2=IF(SUM(N(COUNTIF(INDIRECT("a"&IFNA(XMATCH("全",B$1:B1,,-1),)+1):A2,{"一","二","三","四","五","六","七","八","九"})=0)),"","全") 思路
阅读全文
摘要：问题：如何生成一组重复与循环的序列数，如图所示。 函数公式解决： 重复 =INT(ROW(A5)/5) （5为重复次数） 循环 =MOD(ROW(A5),5)+1 （5为循环值） 思路： Row函数是返回参数所在单元格行的函数，借此生成序列数。 如果重复次数为5，则以Row(A5)，即5为起始值，除
阅读全文
摘要：先看看这个：九九乘法表新玩法 里面有个MakeArray的解法，但其实，MakeArray的强大之处还不止于此，比如，人家可以华丽丽地越过多维引用。 比如下图所示，需要对每天记录中的最大值进行求和，传统的多维引用公式有点烧脑…… =SUM(MAXIFS(OFFSET(B1:D1,ROW(1:8),)
阅读全文
摘要：问题：Vlookup不能整列引用？ Vlookup大叔表示不背这个锅！ 数据源是xlsx文件，共1048276行；公式所在工作簿是xls文件，共65536行，两者不对等，故而不能引用整列。 解决： 方法1：将公式所在工作簿升级为xlsx文件。 方法2：公式不引用整列，改成以下 =VLOOKUP(A2
阅读全文
摘要：Lambda可以说是相当颠覆函数传统概念的一个家伙，在这之前，但凡工作表函数公式的组成部分都是至少三部分：等号、函数名、一对半角的小括号。而这对半角小括号就是这函数的一堵围墙，围墙内的属于这个函数，围墙外的与这个函数无关。 可是我们来看看人家Lambda函数长啥样了（以九九乘法表公式为例）： =LA
阅读全文
摘要：问题：统计各省二级地市的数量 函数解决： 省级名：=INDEX(FILTER(A:A,B:B="省级"),ROW(A1)) 二级地市数量：=COUNTIFS(B:B,"二级地市",D:D,XLOOKUP(F2,A:A,D:D)) 二级地市数量（不加辅助列）：=SUM((B:B="二级地市")*(LE
阅读全文
摘要：问题：矩阵转一列 函数解决： 先行后列：{=INDIRECT(TEXT(SMALL(ROW($2:$4)*100+COLUMN(A:B),ROW(A1)),"r0c00"),)&""} 行列后行：{=INDIRECT(TEXT(MOD(SMALL(ROW($2:$4)*100+COLUMN(A:B)
阅读全文
摘要：问题： 一列数据中，一部分内容是数字，另一部分内容是数字和字母混合，要筛选出其中之一 解决： 添加辅助列，使用以下公式 =ISNUMBER(-CLEAN(A2)) 再根据辅助列筛选 思路：A列中的“数字”其实为文本型数字，且结尾还有一不可见字符，所以用Clean函数去除。再加上负号，将文本型数字强制
阅读全文
摘要：问题：利润在指定区间内的获得指定工资，如图所示。 函数解决： =LOOKUP($K2,SORT($D$5:$D$9+0),SORT(F$5:F$9)) 思路： 两次利用Sort函数，将利润区间和对应工资升序排列，再利用Lookup查找原理得出对应工资。 （Ps，请忽略具体的工资数，有点少~）
阅读全文
摘要：问题：A列中比C1大的数，从小到大排列 函数解决： 内存数组：{=SORT(FILTER(A1:A20,A1:A20>C1))} 下拉公式：{=SMALL(IF(A$1:A$20>C$1,A$1:A$20),ROW(A1))} 思路： 内存数组公式，利用Filter函数筛选出比C1大的数，再利用So
阅读全文
摘要：问题：无规律的字符串中，最后一个是字母的去掉，是数字的保留。 函数解决： =IF(ISERROR(-RIGHT(A1)),LEFT(A1,LEN(A1)-1),A1) 解题思路： LEFT(A1,LEN(A1)-1)是去掉最后一位字符的模式化公式 RIGHT(A1)提取出最后一个字符，在前面加上负号
阅读全文
浙公网安备 33010602011771号