Я пытаюсь найти максимальную цифру и год/годы, к которым она может быть отнесена.
- ГОДЫ 2000-2010 (A1:J1)
- ДАННЫЕ (A2:J2)
- МАКСИМУМ ДАННЫХ помещается в A3
Я могу использовать ИНДЕКС/ПОИСКПОЗ, чтобы получить ПЕРВЫЙ МАКСИМАЛЬНЫЙ ГОД, где он совпадает с МАКСИМАЛЬНЫМ значением ДАННЫХ, но это МАКСИМАЛЬНОЕ значение может появиться за несколько лет (например, 2002, 2005, 2009).
ПЕРВЫЙ МАКСИМАЛЬНЫЙ ГОД в B3
У меня есть формула в C3, которая теперь пытается начать другое сопоставление индекса, используя (OFFSET, 0, B3), чтобы поиск начался после FIRST MAX YEAR, но по какой-то причине она возвращает мне первый встреченный год.
=INDEX(A1:J1,MATCH(A3,OFFSET(A2:J2,0,MATCH(B3, A1:J1,0))))
Я округлил свои числа так, чтобы они имели один десятичный знак, и я знаю, что у меня есть два совпадающих МАКСИМАЛЬНЫХ числа ДАННЫХ в моем диапазоне A2:J2..
Обновление: Изображение, которое, надеюсь, пояснит более наглядно.
Есть идеи?
Спасибо, Майкл.
ОТВЕЧАТЬ:
Я перешел по ссылке Абдула в комментариях ниже и нашел формулу, которая очень хорошо соответствовала моим требованиям.
=ЕСЛИОШИБКА(ИНДЕКС(A1:K1,НАИМЕНЬШИЙ(ЕСЛИ(A2:K2=$A$3,СТОЛБЕЦ(A2:K2)-СТОЛБЕЦ(ИНДЕКС(A2:K2,1,1))+1),nth)),"")
Ячейка n имеет номер 2. Поэтому она ищет второе вхождение моей ячейки A3.
Надеюсь, это поможет людям. ИЭксельджетотличный сайт. Очень рекомендую.
решение1
Это можно сделать с формулами, введенными только в ячейки B3, C3 и т. д. Но я предлагаю добавить вспомогательную строку (строку 4), чтобы сделать формулы более читабельными.
В ячейку B4 введите:
=MATCH($A3,OFFSET($A2:$K2,0,A4),0)+A4
Это возвращает 6номер столбца первого появления 19.0 в строке 2. Скопируйте эту формулу вправо, и вы получите 8и, 11за которыми следует #N/Aв оставшихся ячейках. Теперь введите в ячейку B3:
=INDEX($A1:$K1,B4)
И скопируйте формулу вправо. Вы получите годы, в которые было найдено максимальное значение: 2005, 2007а 2010затем #N/Aв оставшихся ячейках.
Если вы не хотите видеть #N/Aзначения, вы можете использовать IFERRORфункцию, чтобы изменить их на пустые.
решение2
Мне нравится ответ Блэквуда, так как он не требует формулы массива. Но если вы не можете выдержать вспомогательную строку или вам нужно сделать это с помощью одной формулы, вы можете использовать это в A4 и заполнить справа:
=IFERROR(SMALL(IF($A$2:$K$2=$A$3,$A$1:$K$1),COLUMN()),"")
Это формула массива, поэтому ее нужно вводить с помощью CTRL Shift Enter, а не просто Enter.
Вот как это работает. IF() создает массив лет, где данные соответствуют A3 (максимальное значение данных) и FALSE во всех остальных случаях:
{False;False;False;False;False;2005;False;2007;False;False;2010}
И затем SMALL() выбирает 1-е, 2-е, 3-е и т. д. наименьшие значения из этого массива, используя номер столбца в качестве индекса. Он проверяет только числа и игнорирует значения "False" в массиве.
Наконец, IFERROR() заменяет любые ошибки #N/A пробелом.