![如何使用 CSE 公式将范围过滤到某个范围?](https://linux22.com/image/1527808/%E5%A6%82%E4%BD%95%E4%BD%BF%E7%94%A8%20CSE%20%E5%85%AC%E5%BC%8F%E5%B0%86%E8%8C%83%E5%9B%B4%E8%BF%87%E6%BB%A4%E5%88%B0%E6%9F%90%E4%B8%AA%E8%8C%83%E5%9B%B4%EF%BC%9F.png)
我有一个范围 A1:C100,A 列和 B 列中的单元格可能为空。我想使用 CSE 公式,过滤 A1:C100,仅保留 A 列和 B 列不为空的行,并将其填充到范围 D1:F100 中。
在 SQL 中这很简单,例如
select A, B, C from [A1:C100] where A!="" and B!=""
但是我如何使用 CSE 公式在 Excel 中写入这个?
答案1
查看此示例,其中有一个备用列和三种数组公式。
范围A1:B100
由随机字符串填充,带有空白单元格,范围C1:C100
只是一些数字(也可以是字符串)。
RangeH1:H100
用作辅助列,它实际上过滤并累积和中非空单元格的行A
号B
。
H1
:数组公式用于查找第一个包含非空单元格A
和B
列的行号:
=MATCH(1,SIGN(LEN(TRIM($A$1:$A$100))*LEN(TRIM($B$1:$B$100))),0)
H2
:另一个数组公式,用于查找所有其余行号,其中包含A
和B
列中的非空单元格:
=IF(ROW()>SUM(SIGN(LEN(TRIM($A$1:$A$100))*LEN(TRIM($B$1:$B$100)))),"",MATCH(1,SIGN(LEN(TRIM(INDIRECT(ADDRESS((1+H1),1)&":$A$100")))*LEN(TRIM(INDIRECT(ADDRESS((1+H1),2)&":$B$100")))),0)+H1)
该公式很长,因此为了以防万一,这里将其格式化以显示结构:
IF(
ROW()>
SUM(
SIGN(
LEN(TRIM($A$1:$A$100))*LEN(TRIM($B$1:$B$100))
)
)
,""
,MATCH(1,
SIGN(
LEN(TRIM(INDIRECT(ADDRESS((1+H1),1)&":$A$100")))
*LEN(TRIM(INDIRECT(ADDRESS((1+H1),2)&":$B$100")))
)
,0
)+H1
)
在中输入最后一种数组公式D1
:
=IF(ROW()>SUM(SIGN(LEN(TRIM($A$1:$A$100))*LEN(TRIM($B$1:$B$100)))),"",INDEX($A$1:$C$100,$H1,COLUMN()-COLUMN($D$1)+1))
将单元格复制到E1
,F1
;选择并复制范围D1:F1
;选择范围D2:F100
并粘贴复制的单元格。
通过增加更多的辅助列,公式可以变得更短。
强制性说明:进入 数组公式,按Ctrl+ Shift+Enter 而不是只Enter按标量公式)。