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

Question

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

Например:

Я ищу формулу, которая будет искать максимальное число в диапазоне от А2 до С5 (в этом примере 9669). Затем найдите столбец, в который попадает это число (в этом примере Person C), и затем верните это значение.

Кажется, достаточно просто для одномерного диапазона, но я не могу найти ничего, что касается двухмерных.

Любая помощь будет принята с благодарностью.

Answer 1

Как заявляет сама Microsoft: «Итак, вы хотите стать настоящим опытным пользователем Excel?В этом случае вам нужно знать, как использовать формулы массива ".

Решение состоит из трех этапов:

1. определить матрицу, которая ищет максимальное значение,
2. преобразовать эту матрицу в 1 и 0,
3. умножьте эту матрицу на вектор, содержащий номера столбцов.

Шаг 1: В качестве эксперимента попытайтесь выбрать диапазон, равный размеру вашей таблицы, и введите enter =<table range> = MAX(<table range>) и вы подтвердите выбор нажатием клавиши CTRL SHIFT ENTER (ключ по умолчанию для ввода формул массива). ,

Шаг 2: Оператор двойного минуса преобразует значения TRUE/FALSE в 0 (для FALSE) и 1 (для TRUE).

Шаг 3: Используйте SUMPRODUCT и COLUMN чтобы умножить массив и создать вектор с номерами столбцов.

Таким образом, ваша формула выглядит следующим образом: SUMPRODUCT(--( <table range> = MAX( <table range> )) * COLUMN( <table range> )) (конечно, замените <table range> диапазоном или именем вашей таблицы ,

Обратите внимание, что эта формула возвращает абсолютный номер столбца таблицы найденного максимального значения. Итак, 5 для столбца E в вашем рабочем листе. Если вы хотите, чтобы относительное число в вашей таблице, вычтите из столбца левой верхней ячейки таблицы и добавьте его. Таким образом, ваша формула имеет вид: SUMPRODUCT(--( <table range> =MAX( <table range> )) * COLUMN( <table range> )) - COLUMN( <left top cell of table> ) + 1

Также обратите внимание, что эта окончательная формула использует формулы массива, но сама по себе является не массивом, а одним значением. Таким образом, чтобы ввести эту формулу, вы можете просто нажать ENTER.

Мой рабочий пример: