如何使用非数组公式根据条件过滤数据 [英] How to filter data based on condition using non array formula

问题描述

我有以下示例数据，并且想仅根据公式根据status条件Complete自动过滤它们.我知道如何使用array formula和VBA自定义函数进行过滤.现在，我正在使用以下数组公式对其进行过滤.由于某些限制，我想忽略VBA和array公式.是否有任何功能组合可以实现为非数组公式?

I have following sample data and want to auto filter them based on status condition Complete using formula only. I know how to filter using array formula and VBA custom function. Right now I am filtering it using following array formula. Due to some limitation, I want to ignore VBA and array formula. Is there any function combination to achieve it as non-array formula?

{=IFERROR(INDEX($B$4:$E$13,SMALL(IF($E$4:$E$13="Complete",ROW($E$4:$E$13)-ROW($E$3)),ROW(1:1)),COLUMN(A$1)),"")}

============================ 样本数据 == ==============================

============================= Sample Data =================================

   ID   User    Task                 Status
----------------------------------------------
Emp-001 Harun   Prepare Document    Complete
Emp-002 Rahim   Print               Complete
Emp-003 Karim   Binding             Progress
Emp-004 Jadhu   Packaging           Complete
Emp-005 Madhu   Prepare Document    Hold
Emp-006 Rahima  Print               Progress
Emp-007 Shila   Binding             Hold
Emp-008 Kaniz   Packaging           Complete
Emp-009 Jamila  Prepare Document    Progress
Emp-010 Rina    Print               Complete

屏幕截图:

Screenshot:

非常感谢您的帮助.

推荐答案

在G3单元格中使用以下公式，然后根据需要拖放至右下方.希望对您有帮助.

Use the following formula in G3 cell then drag and drop to down and right as needed. Hope this will help you.

=IFERROR(INDEX($B$3:$E$12,SMALL(INDEX(ROW($A$1:$A$10)-($E$3:$E$12<>"Complete")*(1E+99)*-1,, ),ROW(1:1)),COLUMN(A$1)),"")

快照:

这篇关于如何使用非数组公式根据条件过滤数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！