Нахождение максимальной разницы между двумя строками одинакового размера при игнорировании пустых ячеек в Excel

#excel #excel-formula #max #difference #array-difference

#excel #excel-формула #макс #разница #массив-разница

Вопрос:

У меня есть две строки A (предполагается, что они не имеют нулей) и B (могут содержать пустые ячейки) в Excel. A и B оба имеют одинаковую длину.

Какова правильная формула Excel для вычисления максимального положительного увеличения между A и B ?

Пример:

 Row A = [10, 4, 8, 20, 4, 5, 7, 2, 9, 5]
Row B = [0, 20, 4, null, 10, 9, 2, null, null, null]

Expected answer for A - B: max([10 - 0, 4 - 20, 8 - 4, 4 - 10, 5 - 9, 7 - 2]) = max(10, -16, 4, -6, -4, 5) = 10

Если Row B это все null , ожидаемый ответ для A - B null .

Я бы хотел избежать создания новой строки для вычисления этого значения.

Ответ №1:

Предположим, у вас есть данные строки A от A1 до J1 и данные строки B от A2 до J2.

Затем вы можете использовать эту функцию массива, чтобы пропустить разницу, если ячейка пуста в строке B, а затем вычислить максимальное количество различий, используя функцию массива как:

 =MAX(IF(NOT(ISBLANK(A2:J2)),A1:J1-A2:J2))

Чтобы войти в функцию массива, после ввода или вставки вышеуказанной функции нажмите Ctrl Shift Enter.

Ссылки: https://www.excel-easy.com/functions/array-formulas.html и https://exceljet.net/formula/max-if-criteria-match

Ответ №2:

Или попробуйте эту формулу без массива,

 =AGGREGATE(14,6,(A2:J2<>"")*(A1:J1-A2:J2),1)

1. Если вы хотите учесть ситуацию, когда все изменения были отрицательными, то =AGGREGATE(14,6,(A4:J4-A5:J5)/(A5:J5<>»»),1) может быть предпочтительнее (то, как сформулирован вопрос, этого, вероятно, не должно произойти).

2. Aggregate — это формула массива с той лишь разницей, что это собственная формула массива. Следует придерживаться тех же ограничений на ссылку на диапазон. Единственное отличие заключается в том, что для формулы собственного массива не требуется ctrl-shift-enter в более старых версиях.