Требуется значение MAX от (например, 1-100 в F:F), НО мне может понадобиться удалить некоторые ячейки из списка

Question

Требуется значение MAX из (например, 1-100 в F:F). НО мне может понадобиться убрать некоторые ячейки из списка.

Например, у меня есть F:F Каждая ячейка будет содержать значение от 1 до 100, но мне могут не понадобиться определенные ячейки, но не всегда одинаковые.

Есть ли формула для поиска списка (не функциональных), например, например:

=MAX(F:F)-(F4,F8,F163,F354,F574)

Я знаю, что это не работает, но я буду время от времени добавлять и удалять ячейки из этого списка, поскольку список очень обширный, например:

=MAX(F1:F5,F7:F16,F18:F41,F43:F66,F68:F91,F93:F197,F199:F219,F221:F291,F293:F312,F314:F900)

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

Answer 1

Вы можете использовать эту формулу формулы массива:

=MAX(IF(ISERROR(MATCH("F" & ROW($F$1:INDEX($F:$F,MATCH(1E+99,$F:$F))) & "|",$H$1:INDEX($H:$H,MATCH("ZZZ",$H:$H))&"|",0)),$F$1:INDEX($F:$F,MATCH(1E+99,$F:$F))))

Заметки:

1. Будучи формулой массива, она должна быть подтверждена с помощью Ctrl-Shift-Enter вместо Enter при выходе из режима редактирования. Если все сделано правильно, тогда Excel поместит {} вокруг формулы.

2. Ячейки, которые должны игнорироваться, должны быть помещены в ячейку по одному адресу за раз. Шахта в столбце H.

Описание:

1. Существо и формула массива мы хотим ограничить ссылками на набор данных. $F$1:INDEX($F:$F,MATCH(1E+99,$F:$F)) сделает это. Это создаст диапазон, который включает в себя F1 до последней ячейки в столбце F, который имеет номер. Ссылка на столбец H находит каждую ячейку от H1 до последней ячейки со строкой.

2. Функция If тогда возвратит массив чисел и FALSE . False будет в том случае, если номер строки равен ссылке на ячейку.

3. "|" добавлен, чтобы гарантировать, что мы не получаем ложные негативы. Без него ссылка в столбце H F2 исключит все ссылки на ячейки, начинающиеся с F2 включая F20 , F21 ...

4. MAX () будет игнорировать FALSE возвращенные в массиве, и найдет наибольшее число, оставшееся в массиве, возвращаемое оператором IF.

---

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

=AGGREGATE(14,6,$F$1:INDEX($F:$F,MATCH(1E+99,$F:$F))/(ISERROR(MATCH("F" & ROW($F$1:INDEX($F:$F,MATCH(1E+99,$F:$F))) & "|",$H$1:INDEX($H:$H,MATCH("ZZZ",$H:$H))&"|",0))),1)

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