Excel: как пропустить определенную строку в массиве

Question

Я пытаюсь пропустить определенную строку в массиве, а не значение.

Например, предположим, что мы находимся в столбце А:

0
3
0 <--- Row 3
0

как мне пропустить 3-ю строку в A1:A4, чтобы small(A1:A4, 2) было равно 0, а small(A1:A4, 3) было равно 3.

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

Спасибо за помощь!

Answer 1

Другой подход заключается в том, что INDEXфункция создает массив строк, которые вы хотите сохранить (и существует много способов создать этот массив). В вашем конкретном примере, используя AGGREGATEвместо SMALL:

=AGGREGATE(15,4,INDEX($A$1:$A$4,IF(1,N({1,2,4}))),2)

Если у вас всего четыре строки и вы хотите проигнорировать только одну, подход не имеет значения. Если ваши диапазоны больше, а выбор строк для игнорирования сложнее (и их больше одной), создание массива номеров строк для сохранения может быть проще.

Answer 2

Если вы собираетесь суммировать числа, используйте функцию СУММПРОИЗВ:

=SUMPRODUCT((ROW(A1:A4)<>3)*(A1:A4))

Если вы собираетесь сделать что-то еще, то, скорее всего, вам понадобится формула с массивом и ЕСЛИ внутри:

IF(ROW(A1:A4)<>3,A1:A4)

Это сделает формулу формулой массива, которую необходимо будет подтверждать сочетанием клавиш Ctrl-Shift-Enter вместо Enter при выходе из режима редактирования.

Например, если нам нужен MAX:

=MAX(IF(ROW(A1:A4)<>3,A1:A4))

Вводится с помощью Ctrl-Shift-Enter.

Для маленьких:

=SMALL(IF(ROW(A1:A4)<>3,A1:A4),3)

Введенный с помощью Ctrl-Shift-Enter вернет 3не0

---

Другая форма АГРЕГАТА, которая аналогична приведенной выше малой форме:

=AGGREGATE(15,7,A1:A4/(ROW(A1:A4)<>3),3)

Answer 3

Чтобы пропустить указанную 3-ю строку в диапазоне A1:A4

Попробуйте ввести эту обычную формулу:

=SMALL(IF({1;1;0;1},A1:A4),3)

вернет результат3

и,

=SMALL(IF({1;1;0;1},A1:A4),2)

вернет результат0