У меня в рейтинге 7 профессионалов. Ранг 1 самый высокий. Я пытаюсь решить эту проблему, где я могу найти самый высокий ранг и второй самый высокий доступный за каждый месяц. Например, в январе у Маркуса есть значение под его именем, поэтому я хочу, чтобы в столбце верхнего ранга было указано 20, а во втором - в списке Пола, поскольку у него ненулевое значение ниже его имени. Я хочу, чтобы Энтони не обращали внимания на январь, поскольку под его именем нет никакой ценности.
Немного переставил таблицу результатов, чтобы обеспечить некоторую гибкость. Посмотрите изображение внизу для ссылки на ячейку и выложите. Вместо того, чтобы указывать высший ранг и 2-й ранг, я помещаю заголовок над тем, что говорит RANK, а затем под этим ставим число, представляющее позицию в ранге. Я сделал это, чтобы вы могли просто перетащить формулу вправо, и она получит ранг на основе номера ранга в заголовке.
Используя контрольные диапазоны в приведенном ниже примере, поместите следующую формулу в J3 и скопируйте вниз и вправо, как требуется.
=IFERROR(INDEX($2:$2,AGGREGATE(15,6,COLUMN($B$3:$H$3)/(INDEX($B$3:$H$6,MATCH($I3,$A$3:$A$6,0),0)<>""),J$2)),"None")
давайте разберем эту формулу на отдельные компоненты
Давайте начнем с формулы агрегата, в которой есть несколько формул:
AGGREGATE(15,6,COLUMN($B$3:$H$3)/(INDEX($B$3:$H$6,MATCH($I3,$A$3:$A$6,0),0)<>""),J$2)
Причина, по которой я вытащил эту часть, состоит в том, что все, что происходит внутри нее, подобно операциям с массивами. Давайте еще немного углубимся, посмотрим на часть спички, а затем вернемся оттуда:
MATCH($I3,$A$3:$A$6,0)
Эта формула возвращает целое число, представляющее, где значение в I3 (январь) находится в списке $ A $ 3:$ A $ 6. Другими словами, мы пытаемся выяснить, какую строку в вашей справочной таблице вы хотите видеть. В этом случае его 1 для первого элемента в списке. Если бы января не было в списке, он вернул бы ошибку, которая распространялась бы вверх по формулам.
Далее давайте посмотрим на формулу индекса:
INDEX($B$3:$H$6,MATCH($I3,$A$3:$A$6,0),0)
INDEX(TABLE, ROWS TO GO DOWN, COLUMNS TO MOVE OVER)
Подумайте о формуле индекса, как определите свою таблицу, пройдитесь по Y строкам и затем перейдите по X столбцам. В этом случае $ B $ 3:$ H $ 6 представляет ваши данные без заголовков / меток строк. Формула MATCH сообщает нам, сколько строк будет уменьшено. 0 в конце делает изящный маленький трюк, который является чем-то особенным. Поскольку вы не можете перейти через 0 столбцов, в этом особом случае 0 INDEX интерпретирует это как использование всех столбцов, также известных как вся строка определенной таблицы. на стороне заметить, что подобное происходит, когда вы используете 0 для количества строк, чтобы идти вниз.
Следующим шагом является операция с массивом и сравнение.
INDEX() <> ""
По сути, он проходит через каждый столбец и проверяет, является ли запись пустой или в ней что-то есть. Если оно пустое, оно вернет FALSE, а не пустое вернет TRUE. Теперь причина, по которой мы это делаем, заключается в том, что следующим шагом мы поделим значение на TRUE или FALSE. Excel преобразует TRUE и FALSE в 1 и 0 соответственно, когда они отправляются с помощью математической операции. Итак, здесь происходят две важные вещи. Деление на 1 ничего не меняет. это по существу ничего не делает. Деление на 0 вызывает ошибку деления на 0, которая будет распространяться вверх. Это на самом деле то, что нам нужно. Теперь, что мы на самом деле делим?
COLUMN($B$3:$H$3)
Это все еще в части массива совокупной формулы. Поэтому он будет создавать список, так как массив перебирает каждую ячейку. Таким образом, в основном происходит список номеров столбцов SPREADSHEET. и, что более важно, список номеров столбцов электронной таблицы, разделенный на 1, который мы хотим, и разделенный на 0, что дает ошибку, которую мы не хотим. Итак, теперь давайте посмотрим на совокупность:
AGGREGATE(15,6,COLUMN($B$3:$H$3)/(INDEX($B$3:$H$6,MATCH($I3,$A$3:$A$6,0),0)<>""),J$2)
AGGREGATE(Function number, OPERATION #, formula, Option value)
В основном мы выбираем функцию 15, которая будет перебирать список результатов функции и упорядочивать их от наименьшего к наибольшему. Функция 15 также заставляет агрегат выполнять операции с массивами. Не все функциональные номера будут. 6 сообщает совокупность значениям игнорирования ошибок. Таким образом, все эти ошибки деления на 0 игнорируются, так же как и потенциальная ошибка не найденного соответствия из функции соответствия. Формула, которую мы обсуждали выше относительно того, что он делает, и значение параметра в этом случае - это то, какую позицию в верхней части списка вы хотите. 1 возвращает наименьшее число, 2 - второе наименьшее число и так далее. В этом случае я установил, чтобы он смотрел на число в заголовке вместо жесткого кодирования в формулу. В конце концов AGGREGATE в этом случае собирается вернуть единственное целое число, соответствующее номеру столбца SPREADSHEET, который вы ищете. Затем вернемся в ИНДЕКС:
INDEX($2:$2,AGGREGATE(...))
В этом случае я взял всю строку 2 в качестве таблицы. Поскольку его всего 1 строка, мне не нужно указывать строки вниз и столбцы, как в предыдущей таблице. Это выглядит как список, и мне просто нужно указать, как далеко вниз по списку я хочу пойти. Таким образом, поскольку у нас есть номер столбца SPREADSHEET, нам нужно просмотреть всю строку таблицы, где находится заголовок, чтобы номер столбца таблицы, возвращаемый агрегатными строками, совпадал с таблицей таблицы в индексе.
В приведении, где никто не найден, IE нет ранга номер 1, Aggregate возвращает ошибку. Чтобы справиться с этим, мы используем функцию IFERROR. если ошибки нет, функция работает как обычно и возвращает свой результат. Если есть ошибка, она возвращает в нашем случае "нет".