Excel: сопоставьте функцию через 2-й массив, затем вызовите функцию в результирующем массиве столбцов

Я хотел бы использовать одну формулу в excel без использования VBA или макросов, чтобы пересечь двумерный массив и создать одно значение. Формула будет делать следующее:

Во-первых, перебирайте каждую строку данных, вызывая функцию (в моем случае MAX) для каждой строки. Это вернет столбец значений. Затем вызовите одну функцию (в моем случае SUM) в этом столбце значений. Это должно вернуть одно значение.

В терминологии программирования, если мне задан двумерный массив значений в строчном порядке, я хотел бы сопоставить функцию MAX по 2-мерному массиву, а затем вызвать SUM в результирующем массиве 1-d.

Есть ли способ сделать это в Excel в одной формуле ячейки?

Спасибо. Пожалуйста, дайте мне знать, если я смогу сделать этот вопрос более ясным в любом случае, или если я буду задавать этот вопрос где-то в другом месте.

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

{=SUM(SUBTOTAL(4,OFFSET($A$2:$E$2,ROW($A$2:$A$6)-ROW($A$2),0)))} 

Это формула массива. Введите формулу в ячейке без фигурных скобок и нажмите [CTRL] + [SHIFT] + [ENTER]. Затем фигурные скобки должны появиться автоматически.

Как это работает: Часть ROW ($ A $ 2: $ A $ 6) получает массив чисел строк {2,3,4,5,6}. -ROW ($ A $ 2) вычитает стартовую строку, поэтому результирующий массив равен {0,1,2,3,4}. Затем часть OFFSET сдвигает $ A $ 2: $ E $ 2 на {0,1,2,3,4} строки и приводит к массиву векторов строк {$ A $ 2: $ E $ 2, $ A $ 3: $ E $ 3, …, $ A $ 6: $ E $ 6}. Из них SUBTOTAL получает значения MAX для каждой строки, а SUM суммирует эти значения MAX.

Вместо SUM в качестве формулы массива в этом случае мы также можем использовать SUMPRODUCT. Это автоматически получает свои параметры в контексте массива. Таким образом, нет необходимости вводить формулу в виде формулы массива:

 =SUMPRODUCT(SUBTOTAL(4,OFFSET($A$2:$E$2,ROW($A$2:$A$6)-ROW($A$2),0))) 
  • Как изменить положительное значение на ноль в Excel
  • Как реплицировать ячейки, если они содержат строку в excel
  • Формула Excel Как получить данные с другого листа
  • Excel: строки таблицы не отсортированы правильно
  • создавая формулу с минимумом нескольких ячеек, которая не превышает некоторого значения
  • Если в таблице существуют два значения в одной строке
  • Формула Excel - конвертировать формат даты текста
  • Возврат последнего значения из строки в Excel
  • Строка номера Excel автоматически основана на критериях
  • сопоставление определенного значения из группы значений в формуле excel
  • Если значение существует в одной таблице, проверка значения существует в той же относительной ссылке в другой таблице
  • Interesting Posts

    Excel 2013: Скрытие «0 часов 00 минут» прошедшее время

    VBA; Неверная ошибка процедуры или аргумента

    Сравнение индексов с критериями данных

    Как можно масштабировать диаграмму веб-частей веб-частей SharePoint Excel?

    Как отражать даты в определенных ячейках, соответствующих элементу списка, выбранному в Excel?

    Как автоматически отправлять SQL-запрос в электронную таблицу Excel через регулярные промежутки времени

    Изменение формата языка TEXT в Microsoft Office Excel

    Одновременное подключение рабочей книги Excel через Интернет?

    Найти и подсчитать каждое появление имени – это столбец A, затем прочитать значение из столбца B

    Excel CLSID в службе dcom, отличающийся тем, что CLSID в Registy вызывает ошибку

    Векторизация логических операторов в Excel

    FindNext – возврат нескольких совпадений (с небольшой модификацией)

    Как использовать pastespecial с End (xlUp)

    Как поместить файл Excel в домашний каталог, чтобы R-проект можно было открыть на разных компьютерах?

    Openpyxl удаление ячеек / очистка содержимого

    Давайте будем гением компьютера.