废话就不唠了,直接上案例开搞!
案例01 | 提取唯一值(去重)
▼大部分同学已经放弃 =IFERROR(INDEX(A:A,SMALL(IF(MATCH($A$2:$A$16,$A$2:$A$16,)=ROW($A$2:$A$16)-1,ROW($A$2:$A$16)),ROW(A1))),'')
▼ 简单到没朋友
=UNIQUE(A2:A16)
案例02 | 查询所有满足条件的值
▼非常香的万金油,新手晕菜 =IFERROR(INDEX($A$1:$F$10,SMALL(IF($A$2:$A$10=$B$12,ROW($A$2:$F$10)),ROW(A1)),COLUMN(A1)),'')
=FILTER(A2:F10,A2:A10=B12)
案例03 | 拼接单元格内容
▼100列,已猝死! =B2 & ' ' & C2& ' ' & D2& ' ' & E2& ' ' & F2& ' ' & G2& ' ' & C2& ' ' & C2
=TEXTJOIN(' ',,B2:G2)
=CONCAT(B2:G2&' ')
案例04 | 多条件判断
▼晕了没有? =IF(B2<50,'F',IF(B2<60,'E',IF(B2<70,'D',IF(B2<80,'C',IF(B2<90,'B',IF(B2<100,'A','满分'))))))
▼IFS不用再考虑括号嵌套问题 =IFS(B2=100,'满分',B2>=90,'A',B2>=80,'B',B2>=70,'C',B2>=60,'D',B2>=50,'E',TRUE,'F')
=IF(B2=100,'满分',MID('ABCDEF',10-INT(B2/10),1))
联系客服