Excel 公式积累-不常用又酷炫的小点

1、动态渐变进度条

=IFS(C2=0%,"未开始",C2=-1%,"有阻塞",C2<100%,"进行中",C2=100%,"已完成")

 

2、自动计算空单元格个数

统计B4到B64中间有空单元格的个数

=COUNTBLANK(B4:B63)

3、勾选☑️行自动整行文本加删除线

4、多条件统计个数

=(SUMIFS(E4:E63,A4:A63,0)-SUMIFS(E4:E63,B4:B63,""))/(COUNT(E4:E63)-COUNTBLANK(B4:B63)-COUNTIF(A:A,1))

提取单元格中数值部分

=CONCAT(IFERROR(--MID(A1,SEQUENCE(LEN(A1)),1),""))/10

 还没研究出提取整个小数，只能简单提取出数据部分，并➗10，来达到目的

 

当A列需要根据其他列的值来突出显示时

腾讯云文档写法

wps写法

返回单元格中指定字符后面的内容

=RIGHT(F167,LEN(F167)-FIND("，",F167))

根据指定字符分割内容并返回前面的内容

=LEFT(G167,FIND("比赛",G167)-1)

统计指定范围不为空的单元格总数，并过滤掉那些包含公式但显示为空的单元格

输入以下数组公式，按 Enter：
=COUNTA(FILTER(F:F, F:F<>""))
    
输入以下数组公式，然后按 Ctrl + Shift + Enter，而不是只按 Enter：    
=SUM(IF(LEN(F:F) > 0, 1, 0))

根据指定范围统计空值单元格数

嵌套公式

获取指定范围空值单元格数量
=COUNTIF(B2:B23,"")

获取指定范围最后一个不为空单元格行数
=COUNTIF(B2:MAX(FILTER(ROW(A2:A30),ISBLANK(A2:A30)=FALSE())),"")

根据指定列（A列2~30行）最后一个不为空的单元格行数 动态计算 当前列的（B列2~30行） 空值 单元格数
=COUNTIF(B2:INDEX(B2:B30, MAX(FILTER(ROW(A2:A30) - ROW(A2) + 1, ISBLANK(A2:A30) = FALSE))), "")
/*
解释：

FILTER(ROW(A2:A30), ISBLANK(A2:A30) = FALSE())：这个部分会返回一个数组，包含非空单元格的行号。
MAX(...)：获取这些行号中的最大值，表示最后一个非空单元格的行号。
ROW(A2) - 1：确保行号相对于 A2:A30 的起始行调整，使其与 B2:B30 的索引对齐。
INDEX(B2:B30, ...)：使用 INDEX 函数根据计算出的最大行号来确定 B 列的实际结束单元格。
COUNTIF(...)：最终计算从 B2 到计算出的结束单元格范围内的空单元格数量。
这样，整个公式将动态调整计算范围，确保始终只计算到 A 列中最后一个非空单元格对应的 B 列单元格。
*/
拖动公式时，保持参照列A列不变，可持续参照A列统计后续列的空值单元格数
=COUNTIF(B2:INDEX(B2:B30, MAX(FILTER(ROW($A$2:$A$30) - ROW($A$2) + 1, ISBLANK($A$2:$A$30) = FALSE))), "")
/*
解释
$A$2:$A$30 使用绝对引用，这样在拖动公式时，A 列的范围不会发生变化。
B2:B30 使用相对列引用，这样当您将公式向右拖动时，这个范围会自动更新为 C2:C30、D2:D30 等。
这种设置允许您在拖动公式时检测不同列的空值数量，而 A 列的参考保持不变。
*/

提取内容中的性别

=IFERROR(IF(FIND("男", A1), "男"), IF(FIND("女", A1), "女"))

 

=IF(ISNUMBER(SEARCH("男",A8)),"男",IF(ISNUMBER(SEARCH("女",A8)),"女",""))

根据性别，提取之后的内容

=IF(ISNUMBER(SEARCH("男",A8)),MID(A8,SEARCH("男",A8)+1,LEN(A8)),IF(ISNUMBER(SEARCH("女",A8)),MID(A8,SEARCH("女",A8)+1,LEN(A8)),""))

根据指定内容截取字符串

=LEFT(S3, MIN(
    IFERROR(FIND("男", S3), LEN(S3) + 1),
    IFERROR(FIND("女", S3), LEN(S3) + 1),
    IFERROR(FIND("X", S3), LEN(S3) + 1),
    IFERROR(FIND("L", S3), LEN(S3) + 1),
    IFERROR(FIND("2XL", S3), LEN(S3) + 1),
    IFERROR(FIND("3XL", S3), LEN(S3) + 1)
) - 1)

 

把一列多项内容快速分成多列