Excel: как найти максимальное значение в диапазоне на основе соседних ячеек с одинаковым значением

У меня есть столбец A с именами (с дубликатами), столбец B с кодами языков и столбец C с числовыми значениями. Я изо всех сил пытаюсь создать формулу, которая бы находила наибольшее значение в столбце C, но только для те строки с одинаковыми именами — и возвращают значение столбца B для строки с этим самым высоким значением для каждого уникального имени в столбце A. Выходные данные должны быть в других столбцах, например:


Думаю, это то, что вы ищете: = INDEX ($ B: $ B , МАКС (ЕСЛИ (($ C: $ C = MAXIFS ($ C: $ C, $ A: $ A, $ E2)) * ($ A: $ A = $ E2), ROW ($ C: $ C) , ""))) Введите формулу в ячейку F2 с помощью ctrl + shift + enter , поскольку это формула массива.

Я ищу максимальное значение в столбце C, если значение в столбце A равно значению в ячейке в столбце E той же строки. Затем я проверяю, соответствует ли каждое значение в столбце C этому максимальному значению, и проверяю равно ли значение в столбце A значению в столбце E. Если оба значения верны ret Найдите в них номера строк и найдите максимальное значение из этих номеров строк. Результатом является номер строки с максимальным значением, равным имени, которое вы искали. Использование индекса приводит к положению этого номера строки в нужном столбце ( B).

Если вы используете Office 365, вы можете ввести = UNIQUE (A: A) в ячейку E2 чтобы получить перечисленные уникальные имена (до добавления формулы, упомянутой выше в F2



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

Я хотел бы найти максимальное значение в столбце C и вернуть связанный блок «AM» (A3, A16, A29) в E5.

Затем я хотел бы найти следующее по величине значение в столбце C и вернуть связанный блок «AM» (A3, A16, A29) в F5.

Я не могу опубликовать изображение таблицы, но я добавил ссылку на файл Dropbox. Заранее спасибо!


1

Ну, вы не ответили, согласны ли вы с использованием вспомогательного столбца, но я бы сделал это так:

В столбце D вы можете указать что-то вроде этого:

  = MAX (C5: C13)  

В ячейке D3 и = MAX (C18: C26) в D16 и т. д..

Затем в E5 вы можете использовать эту формулу:

  = INDEX ($ A: $ A, MAX (IF (LARGE ($ D  : $ D, 1) = $ D: $ D, ROW ($ D: $ D))))  

В F5:

  = INDEX ($ A: $ A, MAX (IF (LARGE ($ D: $ D, 2) = $ D: $ D, ROW ($ D: $ D))))  

Обратите внимание, что = MAX () эквивалентно = LARGE (, 1) , так что первая формула также может быть записана как = INDEX ($ A: $ A, MAX (IF (MAX ($ D: $ D) = $ D: $ D, ROW ($ D: $ D))))

Теперь это формулы массива, поэтому вам нужно использовать Ctrl + Shift + Enter вместо обычного Enter , чтобы они работали должным образом.

Кроме того, последнее примечание: вы можете изменить диапазоны $ A : $ A и $ D: $ D на что-то вроде $ A3: $ A40 и $ D3: $ D40 , чтобы формула работала быстрее, поскольку требуется обрабатывать меньше ячеек.

Улучшите этот ответ
ответил 15 декабря ’13 в 19:02
  • Спасибо, Джерри! Допустима вспомогательная колонка. При использовании второй формулы я получаю # NUM1 возврат. — RCK, 15 дек. ’13 в 19:53
  • @RCK Я не понимаю, как это происходит oo Хм, обычно вы получаете #NUM! когда есть значение больше, чем может представить excel … — Джерри, 15 декабря 2013 г., 20:14
  • Еще раз спасибо, Джерри. Я смог изменить дизайн листа и использовать ИНДЕКС/ПОИСКПОЗ, чтобы найти свои решения. — RCK 16 дек. ’13 в 2:46
добавить комментарий |

Ну, вы не ответили, согласны ли вы с использованием вспомогательного столбца, но я бы сделал это так :

В столбце D можно поместить что-то вроде этого:

  = MAX (C5: C13)  

В ячейке D3 и = MAX (C18: C26) в D16 и т. д.

Затем в E5 вы можете использовать эту формулу :

  = ИНДЕКС ($ A: $ A, MAX (IF (LARGE ($ D: $ D, 1) = $ D: $ D, ROW ($ D:  $ D))))  

В F5:

  = INDEX ($ A: $ A, MAX (IF (LARGE  ($ D: $ D, 2) = $ D: $ D, ROW ($ D: $ D))))  

Обратите внимание, что = MAX ( ) эквивалентен = LARGE (, 1) , так что первую формулу можно также записать как = INDEX ($ A: $ A, MAX (IF (MAX ($ D: $ D) = $ D: $ D, ROW ($ D: $ D))))

Теперь это формулы массива, так что вы необходимо использовать Ctrl + Shift + Enter вместо обычного Enter , чтобы заставить их работать правильно.

Также, последнее примечание, вы можете изменить диапазоны $ A: $ A и $ D: $ D на что-то вроде $ A3: $ A40 и $ D3: $ D40 , чтобы формула работала быстрее, поскольку нужно обрабатывать меньше ячеек.

Оцените статью
logicle.ru
Добавить комментарий