Excel筛选需求：保留已观测鸟类及对应分类表头

核心思路 #

通过辅助列标记需保留的行：先标记已观测物种，再逆向判断科、目表头是否有下属已观测物种，最后筛选保留所有标记行。

方法1：辅助列自动标记（适合大数量数据） #

步骤1：标记已观测物种行 #

在空白列（如H列）首行（H2）输入公式，下拉填充至所有行：

=IF(AND(A2<>1,B2<>1),IF(G2=1,1,0),0)

此公式仅给已观测的物种行（非目/科表头）标记1，其他行暂标0。

步骤2：标记需保留的科表头行 #

在H列的科表头行（B列=1的行）输入公式，下拉填充所有科行：

=IF(B2=1,SUMPRODUCT(
    (ROW($B:$B)>ROW(B2))*
    (ROW($B:$B)<=IFERROR(MATCH(TRUE,$B:$B=1,ROW(B2)+1),ROWS($B:$B)))*
    ($H:$H=1)
),0)

该公式统计当前科表头到下一个科/目表头之间的已观测物种数，若结果>0，说明此科需保留。

步骤3：标记需保留的目表头行 #

在H列的目表头行（A列=1的行）输入公式，下拉填充所有目行：

=IF(A2=1,SUMPRODUCT(
    (ROW($A:$A)>ROW(A2))*
    (ROW($A:$A)<=IFERROR(MATCH(TRUE,$A:$A=1,ROW(A2)+1),ROWS($A:$A)))*
    ($H:$H>=1)
),0)

该公式统计当前目表头到下一个目表头之间的已观测物种+需保留的科数量，结果>0则此目需保留。

步骤4：筛选并导出保留行 #

• 选中H列，按Ctrl+G打开定位框，选择「定位条件」→「常量」，仅勾选「数字」，确定后选中所有需保留的行。
• 右键选中区域→「复制」，粘贴到新工作表即可完成筛选。

方法2：手动筛选（适合小数据量） #

• 筛选G列值为1的行，给这些已观测物种的所属目、科表头行添加高亮标记（比如黄色）。
• 取消筛选，选中所有未高亮的物种行、无对应已观测物种的目/科表头行，批量删除。

公式优化提示 #

• 公式中的$B:$B、$H:$H可替换为实际数据范围（如$B$2:$B$500），提升计算速度。
• Excel 365用户可改用XLOOKUP简化下一个表头的定位，例如科表头的下一行号可写为：XLOOKUP(TRUE,OFFSET($B2,1,0):$B$500=1,ROW(OFFSET($B2,1,0):$B$500),"",,1)

内容的提问来源于stack exchange，提问作者CarlT