Вычитание матриц, но в excel

Рейтинг: 0Ответов: 3Опубликовано: 26.07.2023

Такой вопрос - можно ли реализовать что-то похожее на вычитание матриц в Excel?

У меня есть документ с 2-мя таблицами: верхняя - где производится расчёт, нижняя - с исходными данными. И я хочу реализовать такую схему - в ячейки "текущий ур." и "требуемый ур." табл. 1 вводить цифры, соответствующие числам из колонки "уровень" табл.2, и при вводе обоих чисел должна выводится разница соответствующих значений в колонках "ресурсы". Т.е., если я введу "текущий ур. 1" и "требуемый ур. 5", то в колонке "ресурсы 1" табл. 1 появится разница 2000-300. Также и в остальных колонках табл. 1. И если потом я введу "текущий ур. 5" и "требуемый ур. 7", то в колонке "ресурсы 1" табл. 1 появится разница 3000-2000.

Таблицы

excel
Источник: Stack Overflow на русском

Ответы

▲ 0Принят

Можно создать формулу, которая пригодна для копирования во все нужные ячейки (AM11:AR14) без изменений. Для этого нужно использовать абсолютную адресацию столбцов "текущий ур." и "требуемый ур.", а так же отдельно пронумеровать столбцы в верхней таблице.

Дополнительно в 26-ую строку нужно добавить уровень 0.

Для нумерации столбцов нужно ввести числа от 2 до 7 в ячейки с AM$8 по AR$8 (число должно соответствовать номеру столбца в нижней таблице).

Формула для ввода в ячейку AM11:

=ВПР($AL11;$AJ$23$AP$36;AM$8;ЛОЖЬ)-ВПР($AK11;$AJ$23$AP$36;AM$8;ЛОЖЬ)

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

  1. Скопировать ячейку AM11, потом выделить AN11:AR11 и вставить. Выделить AM12:AR14 и вставить.
  2. Выделить AM11 и размножить её вправо до AR11 включительно, потом имеющееся выделение размножить вниз до AR14 включительно.
  3. Выделить AM11:AR14, начиная с AM11, нажать F2, потом Ctrl+Enter.
▲ 1

Не очень понятно при чем здесь матрицы, но ваша задача решается, например, при помощи функции ВПР:

=ВПР(AL11;$AJ$27$AP$36;3;ЛОЖЬ)-ВПР(AK11;$AJ$27$AP$36;3;ЛОЖЬ)

Но вам придется, либо избавится от нолей в верхней табличке, либо добавить строчку уровня 0 в нижнюю, либо обернуть формулу в кучу проверок :)

Источники:

  1. Поиск значений с помощью функций ВПР, ИНДЕКС и ПОИСКПОЗ
  2. Функция ВПР
▲ 0

Растянуть на всю таблицу:

=ВПР($AL11;$AJ$27:$AP$36;СТОЛБЕЦ(B1);)-ВПР($AK11;$AJ$27:$AP$36;СТОЛБЕЦ(B1);)

или

=ИНДЕКС($AJ$27:$AP$36;$AL11;СТОЛБЕЦ(B1))-ИНДЕКС($AJ$27:$AP$36;$AK11;СТОЛБЕЦ(B1))

Один из вариантов защиты от ошибки при AL11 = 0 или AK11 = 0:

=ЕСЛИОШИБКА(ВПР(...)-ВПР(...);)