☰

Функция для возврата ссылок на ячейки (адресов) N наибольших значений

tag-icon • tag-icon microsoft-excel worksheet-function
Функция для возврата ссылок на ячейки (адресов) N наибольших значений

Я делаю таблицу для отслеживания результатов выборов и возвращаю избранных представителей. Мы используем систему пропорционального представительства, применяямодифицированный метод Сент-Лаге. Результаты выборов вносятся в электронную таблицу, а затем делятся на заданное число (1,4, 3, 5 и т. д., до 15, в общей сложности восемь частных, как предписано применимым законом), чтобы получить частное, из которого следует распределить места. Используя условное форматирование, электронная таблица теперь выделяет восемь самых высоких чисел, показывая, кто какую позицию займет. Все это работает так, как и предполагалось.

Нам нужно а) вернуть список восьми успешных кандидатов и б) заставить этот список возвращать ссылку на ячейку (например, D7), а не число. Я поигрался с использованием функции LARGEи успешно возвращаю восемь верхних частных в C24:C31, используя формулу, которая, по сути, =LARGE($C$2:$J$21, ROW()-23). Однако я не могу заставить ее возвращать координаты сетки для этих частных.

На снимке экрана ниже показан макет того, чего я хочу добиться. Ячейки, выделенные зеленым цветом, выделены в результате условного форматирования; это восемь самых больших значений в C2:J21. Ячейки, выделенные желтым цветом, — это то, чего я хочу добиться. Для ясности: данные в ячейке C2:J5 генерируются формулой, аналогично значения в C24:C31 — это те же восемь значений, которые выделены зеленым цветом выше; они генерируются формулой. Скрытые строки с 6 по 21 по сути являются дубликатами строк с 1 по 5. Я загрузил электронную таблицу наГугл Диск

Скриншот рассматриваемой электронной таблицы

решение1

Это сложно. Мы можем использовать

=МИН(ЕСЛИ($C$2:$J$21=ценить, СТРОКА($C$2:$J$21)))
чтобы найти номер первой строки, в которойvalueпоявляется. Аналогично,
=МИН(ЕСЛИ($C$2:$J$21=ценить, СТОЛБЕЦ($C$2:$J$21)))
находит номер первого столбца, в которомvalueпоявляется. Выше приведены формулы массива.

Если верхние восемь значений C2:J21уникальны, мы можем использовать вышесказанное, чтобы найти значение в этой сетке. Затем

ИНДЕКС($A$1:$J$21,номер_строки,номер_столбца)
проиндексирует эту ячейку, и мы можем использовать
ЯЧЕЙКА("адрес", ИНДЕКС(выше))
чтобы получить адрес строки и столбца этой ячейки.

Итак, введите

=ЯЧЕЙКА("адрес", ИНДЕКС($A$1:$J$21, МИН(ЕСЛИ($C$2:$J$21=C24,СТРОКА($C$2:$J$21))), МИН(ЕСЛИ($C$2:$J$21=C24,СТОЛБЕЦ($C$2:$J$21)))))
в ячейку B24, нажмите Ctrl+ Shift+ Enterи перетащите/заполните вниз до B31.

Примечания:

  • CELL("address", …)возвращает абсолютный адрес (со знаками доллара). Если они вам не нужны, вы можете использовать 
    =ПОДСТАВИТЬ(ЯЧЕЙКУ(бла бла бла), "$", "")
  • Это не очень хорошо обрабатывает дублирующиеся значения. Например, если E3также содержится 8 (в дополнение к D4), то будет сообщено, что они оба находятся в D3(первой строке и первом столбце). Это может быть очень трудно исправить, но это было бы возможнотест произошло ли такое совпадение, чтобы можно было внести ручную коррекцию. 
  • Хотя все это можно сделать в одной ячейке, в долгосрочной перспективе вам может упростить жизнь использование вспомогательных столбцов для значений строк и столбцов.

Этот ответ частично основан на трюке изэтот ответБарри Гудини.

Связанный контент