Excel: формула массива внутри другой формулы

Question

Столбец B копирует значения из столбца A и, если оно не является минимальным значением в данном диапазоне, добавляет 1, в противном случае он остается как есть: =IF(NOT($A2=MIN($A$2:$A$7)),$A2+1,$A2)

C2 находит ячейку с минимальным ненулевым значением из диапазона A2:A7 с =MIN(IF(A2:A7>0,A2:A7)) (с помощью CTRL SHIFT ENTER).

Можно ли использовать эту формулу массива в качестве подвыражения в другой формуле? Скажем, я хочу добавить 1, если значение в A2:A7 НЕ является минимальным нулевым значением?

Так что в приведенном выше примере ничего не будет добавлено к A6 . И значения D2:D7 будут:

6    
5    
4    
3    
1    
1

Answer 1

Не уверен, возможно ли вообще использовать эту конкретную формулу массива в качестве подвыражения в другой формуле.

Тем не менее, есть обходной путь. Вам просто нужна формула без массива, которая определяет минимальное ненулевое значение.

Такая формула может быть создана с помощью функции SMALL() :

=SMALL($A$2:$A$7,COUNTIF($A$2:$A$7,0)+1)

Приводит к рабочему решению

где формула в D2 - это просто формула в B2 с заменой функции MIN() функцию SMALL() :

=IF(NOT($A2=SMALL($A$2:$A$7,COUNTIF($A$2:$A$7,0)+1)),$A2+1,$A2)

Обратите внимание, что эта формула не введена в массив!

---

Я предпочитаю писать эти типы формул с рефакторингом, чтобы они не использовали NOT() а ссылка находится вне IF():

=$A2+IF($A2=SMALL($A$2:$A$7,COUNTIF($A$2:$A$7,0)+1),0,1)

Моя настоящая любимая факторизация, однако, заключается в следующем:

=$A2+($A2<>SMALL($A$2:$A$7,COUNTIF($A$2:$A$7,0)+1))

К сожалению, многие люди считают это хакерским и трудным для чтения, поэтому я склонен не использовать его.

Answer 2

Ответ @RobinCTS вдохновил меня на внесение полезных исправлений в обе ваши захватывающие формулы.

Ваша первая формула должна быть написана так, как показано ниже в ячейке C2, чтобы найти минимальное ненулевое значение.

{=MIN(IF(A2:A7>0,A2:A7,A2+1))}

В ячейке D2 напишите эту формулу массива, чтобы добавить 1, если не минимальное ненулевое значение, и заполните ее.

{=IF(A2<>MIN(IF(A2:A7>0,A2:A7,A2+1)),A2+1,A2)}

NB. Обе приведенные выше формулы являются формулами массива, поэтому завершите оба с помощью Ctrl+Shift+Enter.