时间戳

首页 > 解决方案 > Excel：SMALL()、IF() 和 ROW() 使用多个条件查找值的行号 - AND() 不起作用

excel - Excel：SMALL()、IF() 和 ROW() 使用多个条件查找值的行号 - AND() 不起作用

问题描述

我有一个Purchases类似于此的参考电子表格：

        A           B              C              D
1       Ord_ID      Supplier       PO_Date        Receipt_Quantity
2       PO101       Aa             11/1/2017      5
3       PO102       Bb             12/1/2017      12
4       PO103       Cc             12/15/2017     100
5       PO104       Bb             1/15/2018      8
6       PO105       Dd             2/1/2018       30
7       PO106       Bb             3/1/2018       15
8       PO107       Bb             4/1/2018       10
...

我有一个单独的工作表Supplier Bb Data，它应该返回Purchases供应商 Bb 的数据行号。公式如下所示，位于单元格A10、A11、A12等中：

=SMALL(IF('Purchases'!$B:$B=$A$1, ROW('Purchases'!$B:$B)), ROW(1:1))
=SMALL(IF('Purchases'!$B:$B=$A$1, ROW('Purchases'!$B:$B)), ROW(2:2))
=SMALL(IF('Purchases'!$B:$B=$A$1, ROW('Purchases'!$B:$B)), ROW(3:3))
...

A1inSupplier Bb Data包含要查找的值，“Bb”。

这个公式有效；它正确地返回行号，Purchases其中包括供应商 Bb 的采购订单。( 3, 5, 7, 8, 等)

但是，我希望能够返回更具体的数据：Supplier = BbAND PO_Date >= 1/1/2018， AND的行PO_Date <= 3/1/2018。因此，返回5和7。

看起来很简单；我试过这个（假设1/1/2018和3/1/2018分别在单元格A2和A3中）：

=SMALL(IF(AND('Purchases'!$B:$B=$A$1, 'Purchases'!$C$C>=$A$2, 'Purchases!$C$C<=$A$3), 
ROW('Purchases'!$B:$B)), ROW(1:1))

当我尝试此操作时，excel 仅返回0第一行，并为所有后续行返回错误。

在语句中AND()根本不起作用？IF()我还能尝试什么来让它发挥作用？

我总是按下Ctrl+Shift+Enter这些单元格，因为它们是数组公式，所以这不是问题。

标签： excelif-statementexcel-formulaexcel-2016

解决方案

AND 函数自己执行循环（数组样式）计算。随后，AND 或 OR 函数无法与数组样式 (CSE) 公式中的其他函数配合使用。在嵌套的 IF 中堆叠条件或使用 AGGREGATE 来满足您的目的。

'array style with CSE
=SMALL(IF(Purchases!$B:$B=$A$1, IF(Purchases!$C:$C>=$A$2, IF(Purchases!$C:$C<=$A$3, ROW($B:$B)))), ROW(1:1))

'standard style without CSE
=AGGREGATE(15, 7, ROW(B:B)/((Purchases!B:B=A$1)*(Purchases!C:C>=A$2)*(Purchases!C:C<=A$3)), ROW(1:1))
  • 你错过了一个:in 'Purchases'!$C$C。
  • 工作簿中的所有工作表都具有相同的行数。ROW(B:B)可以用来代替ROW('Purchases'!$B:$B)).
  • ROW(1:1)表示您正在向下拖动连续的k个参数。无需将列锁定为绝对值。

  • 如果不使用全列引用，具有循环计算的数组公式或函数的执行效率会更高。考虑以下：

    =AGGREGATE(15, 7, ROW($1:$999)/((Purchases!B$1:B$999=A$1)*(Purchases!C$1:C$999>=A$2)*(Purchases!C$1:C$999<=A$3)), ROW(1:1))

推荐阅读