随笔分类 - 函数/域集团
这是26个字母10个数字和一堆符号纠结的故事！
摘要：函数公式中的参数绝大部分情况下是可以用以下四种： 常量 引用 公式运算（包括函数、四则运算等） 自定义名称 有少部分函数的参数只支持引用，比如Sumif的第一、第三参数，Subtotal除第一参数以外的其他所有参数等等。 但是，有一个函数的参数的特殊用法，只要是引用就会甩脸子，这个函数就是XLook
阅读全文
摘要：问题：先加后再再加如何用公式 分解1：先乘后加 A2*B2+A3*B3…… =SUMPRODUCT(A2:A11,B2:B11) {=SUM(A2:A11*B2:B11)} 分解2：先加后乘再加 (A2+B2)*C2+(A3+B3)*C3…… =SUMPRODUCT(A2:A11*C2:C11+B2
阅读全文
摘要：问题：一个单元格里的宽*高，需要将各自加上10 函数公式解决： 宽：=LEFT(E3,FIND("*",E3)-1)+10 高：=MID(E3,FIND("*",E3)+1,9)+10 Find部分，找出字符串中*所在的位置 宽的部分用左取函数Left，提取字符串中*前的字符，结果再加上10 高的部
阅读全文
摘要：问题：如下图，结果按E、F列表的顺序，每一行根据A:C表中的数据进行扩展。 函数公式解决： H5公式 {=SMALL(IF(F$5:F$9=TRANSPOSE(A$5:A$17),E$5:E$9),ROW(A1))} I5公式 =XLOOKUP(H5,E:E,F:F) J5公式 =INDEX(FIL
阅读全文
摘要：问题：根据成绩列出前三名成员。 函数公式解决： =INDEX(SORTBY($F$1:$L$1,$F2:$L2,-1),COLUMN(A1)) SortBy函数的基础用法，任性一下下，不解释:P 问题升级：前三名指不包含重复项的前三，如科目3，不重复的成绩有100、90和50，只要成绩在50及以上都
阅读全文
摘要：问题：将下图中的公司名单独提取出来 函数公式解决： =RIGHT(LEFT(C6,FIND("公司",C6)+1),LENB(LEFT(C6,FIND("公司",C6)+1))-LEN(LEFT(C6,FIND("公司",C6)+1))) LEFT(C6,FIND("公司",C6)+1)部分，提取出
阅读全文
摘要：问题：各国家所有公司数量从多到少排列，如下图黄色区域。 函数公式解决： ｛=IFERROR(INDEX(SORTBY(UNIQUE(FILTER($A$2:$A$18,$B$2:$B$18=E$2))&"/"&COUNTIFS($A:$A,UNIQUE(FILTER($A$2:$A$18,$B$2:
阅读全文
摘要：此问题为纵横循环序列数的延续，在第一行输入任意数字，如存在1-40之间的数，则公式返回的结果从中排除。 函数公式解决： =SMALL(IF(ISNA(MATCH(SEQUENCE(40),$1:$1,)),SEQUENCE(40),""),MOD(COLUMN(A1)+ROW(A1)-2,41-CO
阅读全文
摘要：问题如上图。 函数公式解决： 公式1=IF(G1<5,0,IF(G1<15,0.5,1)) 公式2=LOOKUP(G1,{-99,0;5,0.5;15,1}) 公式3=MEDIAN(0,1,CEILING(G1-5,10)/20) 公式1是传统的If多层嵌套解法，先判断G1是否小于5，是则返回0；嵌
阅读全文
摘要：问题：表一结构如下图，根据D3的退休时间2007年9月（实际为真日期“2007-9-1”），在2007年1月之后、2008年7月之前，返回数据应为2007年1月所在行；再根据级别为一级，返回对应的值。 函数公式解决： =LOOKUP(D3,--LEFT(B7:B10,FIND("月",B7:B10)
阅读全文
摘要：Filter函数可以说是包揽了一切查找，嗯，够辛苦。 有木有想过，如果Filter遇上图片，又是怎样的风景呢？ 就是这个样几滴…… 这里的图片可以是两种，一种是由Image函数生成的，另一种是放置在单元格中的图片。 其实，这并不是Filter的独享，因为图片作为单元格对象，所以几乎可以说是所有函数，
阅读全文
摘要：问题：生成一个纵横周期为40的循环序列数。 函数公式解决： =MOD(COLUMN(A1)+ROW(A39),40)+1 思路： 循环序列数的模式化公式（纵向）是： =Mod(Row(A周期),周期)+1 周期为40的公式是： =MOD(ROW(A40),40)+1 再要加上横向，即在Mod的第一个
阅读全文
摘要：问题：某个条件区域为空，直接使用Sumifs的结果错误。 解决：H2单元格连接空文本 =SUMIFS(C:C,A:A,G2,B:B,H2&"") 绝大多数情况下，空单元格被引用后返回的结果是0，但作为Sumif这类函数的条件区域参数，默认为非数值，即空文本，这时就可以将相应的条件转成文本型数字。这一
阅读全文
摘要：问题：在另一个表中引用“=轮休!$B$2:$G$5="休"” 结果却不正确 解决： 公式本身没有问题，但是在输入的时候，组合键不应使用<Ctrl+Enter>，而是<Ctrl+Shift+Enter>，三键的结果才是数组。 补充： <Ctrl+Enter>相当于复制，是在单元格中批量录入相同内容的组
阅读全文
摘要：问题：对数据表进行按月求和，表中存在真日期列。 函数解决： =SUMIFS(C:C,A:A,">="&E2,A:A,"<="&EOMONTH(E2,0)) 利用Sumifs函数设定两个条件，第一个条件是大于等于E2，即指定月的第一天；第二个条件是小于等于指定月的最后一天，即利用EOMONTH函数获得
阅读全文
摘要：问题：B2:G6是各区域连续六周的销量数据，现需要计算每个区各得过几次第一、第二、第三、第四、第五。 =SUM(N(MAP(SEQUENCE(,6),LAMBDA(a,INDEX(SORTBY($A$2:$A$6,OFFSET($A$2:$A$6,,a),-1),ROW(A1))))=B$8)) 使
阅读全文
摘要：问题：每行数据下方插入三行空行 函数公式解决： =IF(MOD(ROW(A4),4),"",INDEX(A:A,ROW(A4)/4)) Mod(Row(A4),4)是生成0、1、2、3、0、1、2、3……这样的循环序列数，以此作为If函数的第一参数，当结果为1、2、3时，即相当于True，返回空文本
阅读全文
摘要：问题：：根据若干班级的成绩和名次，列出前三名的班级 函数公式解决： =INDEX(SORTBY(I$3:I$6,K$3:K$6),ROW(A1)) 公式先用SortBy函数，将班级按名次升序排序，如果数据源中没有名次，则可以按成绩降序排序。再用Index函数逐一提取前三名。 如果成绩没有重复，亦可以
阅读全文
摘要：问题：下列多对一查找，使用Xlookup结果出错，但是查找值又确实是存在的，是什么原因，又该如何解决？ 解决：用Clean函数去除非打印字符 。 一些系统导出的数据会存在非打印字符，这时可以使用Clean函数将其去除，公式改成如下： =XLOOKUP(A2&CLEAN(B2),E$2:E$736&F
阅读全文
摘要：问题：积分表列出所有学科积分为5分时的最低课时，需要根据C列的实际课时，计算对应的积分。规则为：大于等于积分表中的课时时积分为5，大于等于一半时积分为4，其余为3。 函数公式解决： =LOOKUP(C2,SUMIF(F:F,A2,G:G)*{0;0.5;1},{3;4;5}) 利用Sumif找出各学
阅读全文
浙公网安备 33010602011771号