Блог

У меня есть справочная таблица, 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))
...
 

A1 in Supplier Bb Data содержит значение для поиска, «Bb».

Эта формула работает; она корректно возвращает номера строк Purchases , которые включают POs для поставщика Bb. ( 3 , 5 , 7 8 , и т.д.)

Однако я хочу иметь возможность возвращать более конкретные данные: строки, где Supplier = Bb И PO_Date >= 1/1/2018 , И 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 на эти ячейки, потому что они являются формулами массива, так что это не проблема.

Функция 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))
 

• Вам не хватало : '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))
   

Кроме того, вы можете объединить свой SMALL IF с функцией SUMPRODUCT (Ctrl Shift Enter):

 =IFERROR(SUMPRODUCT(SMALL(IF((Purchases!$B$1:$B$8=$A$1)*(Purchases!$C$1:$C$8>=$A$2)*(Purchases!$C$1:$C$8<=$A$3)=0,"",(ROW(Purchases!$B$1:$B$8))),ROW(1:1))),"")