Как использовать формулы индекса и соответствия для нескольких критериев

Question

У меня есть таблица, содержащая информацию об Олимпийских играх, со следующими полями в столбцах:

A) Спортсмен, B) Возраст, C) Страна, D) Год, E) Дата церемонии закрытия, F) Спорт G) Золотые медали.

Я хочу ответить на этот вопрос о данных:

Как зовут спортсмена, который выиграл наибольшее количество золотых медалей в следующей стране за любой год?

Страна Норвегия (в D38). Любая помощь?

Я изначально использовал формулу:

 =INDEX(OlympicAthletes!A:A,MATCH(MAX(OlympicAthletes!G:G),OlympicAthletes!G:G,0),1)

Тем не менее, это только возвращает Майкл Фелпс. Мне нужна формула, чтобы вернуть спортсмена с самыми высокими золотыми медалями для Норвегии. Спасибо!

Answer 1

Возможно, вы захотите подумать о том, чтобы поместить свои данные в сводную таблицу. Это позволяет суммировать и запрашивать ваши данные несколькими способами. В частности, если вы создали сводную таблицу с заголовками строк Year, затем Country, затем Athlete, а затем добавьте золотые медали в область данных. Это очень точно разделит данные и даст вам доступ к фильтрам, которые вы можете использовать для более удобного запроса.

Answer 2

Вы можете сделать это с помощью повторяющейся формулы массива. Это не так красиво, но вы получите то, что хотите. Введите следующее и введите, нажав Ctrl+Shift+Enter.

=INDEX(A:A,MAX(IF(C:C="Norway",IF(G:G=MAX(IF(C:C="Norway",G:G,-1)),ROW(G:G),-1),-1)))

Что это делает (помимо того, что вы оцените SQL):

• IF(C:C="Norway",G:G,-1) представляет массив длиной столбца C, который содержит значение из столбца G, если страна для этой записи - Норвегия, и -1 если страна не Норвегия.
• MAX(IF(C:C="Norway",G:G,-1)) возвращает максимальное значение из этого массива, которое должно быть максимальным количеством медалей, выигранных норвежцем в вашей таблице.
• IF(C:C="Norway",IF(G:G=MAX(IF(C:C="Norway",G:G,-1)),ROW(G:G),-1),-1) проверяет все строки на наличие двух условий: во-первых, спортсмен является норвежцем, а во-вторых, что количество золотых медалей, завоеванных этим спортсменом, равно максимальному числу, выигранному норвежцем. Результатом этого выражения является массив длиной столбца C, который содержит номер строки, если оба условия выполнены, или -1 если они не выполнены.
• MAX(IF(C:C="Norway",IF(G:G=MAX(IF(C:C="Norway",G:G,-1)),ROW(G:G),-1),-1)) возвращает максимальное значение из этого массива. Это будет номер строки матча. Если окажется, что более одной строки соответствует критериям, будет возвращена строка с наибольшим номером строки.
• Наконец, INDEX(A:A,MAX(IF(C:C="Norway",IF(G:G=MAX(IF(C:C="Norway",G:G,-1)),ROW(G:G),-1),-1))) возвращает значение (имя) из столбца A в номере строки, идентифицированной как отвечающая критериям.

Быстрое слово предупреждения:

Помните, что использование полных ссылок на столбцы, например, A:A , в формуле массива окажет заметное негативное влияние на скорость вычисления. Если это вообще возможно, вам следует использовать ограниченные диапазоны, например, A1:A10 , особенно если вы создаете несколько из этих формул в своей рабочей книге.