Excel: значение из одной ячейки на основе максимального значения с несколькими критериями в другом столбце

Я пытаюсь получить значение в столбце 8 на основе значения MAX (столбец Range4) с несколькими критериями (Range1, Range2, Range3), но Excel выдает ошибку «# N / A». Где ошибка?

VLOOKUP(MAX(IF(Range1=2013;IF(Range2="april";IF(Range3="Alexa";Range4))));Range5;8;FALSE)

Часть

{MAX(IF(Range1=2013;IF(Range2="april";IF(Range3="Alexa";Range4))))}

работает только (дает мне правильное значение), но не внутри функции ВПР.

Спасибо!

Пример набора данных для тестирования:

First_day   Last_day    Week_Num    Week_Range  Month   Year    Rank_name   Rank_value
01/04/2013  07/04/2013  14  1-7 april   april   2013    Alexa   10122
08/04/2013  14/04/2013  15  8-14 april  april   2013    Alexa   9670
15/04/2013  21/04/2013  16  15-21 april april   2013    Alexa   9130
22/04/2013  28/04/2013  17  22-28 april april   2013    Alexa   8340
29/04/2013  05/05/2013  18  29-5 april  april   2013    Alexa   7543
31/03/2014  06/04/2014  14  31-06 april april   2014    Alexa   11428
07/04/2014  13/04/2014  15  07-13 april april   2014    Alexa   7159
14/04/2014  20/04/2014  16  14-20 april april   2014    Alexa   7027
21/04/2014  27/04/2014  17  21-27 april april   2014    Alexa   6675
28/04/2014  04/05/2014  18  28-04 april april   2014    Alexa   5379

Эквивалентность диапазонов и столбцов:

  • Диапазон1 = столбец «Год»
  • Диапазон2 = столбец «Месяц»
  • Range3 = Столбец "Rank_name"
  • Range4 = Столбец «Week_Num»
  • Range5 = столбец "Rank_value"

excel vlookup max criteria
person JuanMor    schedule 10.06.2014    source источник
comment
Вы ввели формулу ВПР с помощью Ctrl + Shift + Enter? Не похоже. Кроме того, вы должны указать значение для части else вызовов IF. Это необязательно, но обычно рекомендуется указывать значение по умолчанию.   -  person ApplePie    schedule 10.06.2014

Ответы (2)


arrow_upward
0
arrow_downward

Пожалуйста, попробуй:

=INDEX(Range8;MATCH(MAX(IF(Range1=2013;IF(Range2="april";IF(Range3="Alexa";Range4))));Range4;0))

с помощью Control + Shift + Enter.

person pnuts    schedule 10.06.2014
comment
Извините, но я боюсь, что формула иногда работает неправильно и дает мне неправильные значения. Когда у меня есть одинаковые значения месяца в Range2 и разные значения года в Range1, excel дает мне значение Range4 на основе первого значения, найденного в Range1, без учета критериев года. Пример: строка 1 = {2012; апрель; Alexa; 15} и строка 2 = {2013; апрель; Alexa; 1000}. Тогда excel дает мне 15 вместо 1000. Пожалуйста, помогите и еще раз спасибо. - person JuanMor; 11.06.2014
comment
Как я могу поделиться здесь некоторыми образцами данных? Сайт пока не позволяет загружать скриншоты :-( - person JuanMor; 11.06.2014
comment
Я надеюсь, что новый блок csv в Q может помочь. Если что-то еще нужно, скажите, пожалуйста. - person JuanMor; 12.06.2014

arrow_upward
0
arrow_downward

Проблема с существующей формулой (от pnuts, 10 июня 2014 г.) заключается в следующем: формула сначала сужает список вложенными if, а затем выбирает максимальное значение Week_Num из тех строк, которые соответствуют вашим критериям. Но после определения этого максимума функция ПОИСКПОЗ игнорирует критерии (они использовались только для нахождения максимума из Range4). Если у вас есть другие строки (даже если они не соответствуют критериям) с одинаковым Week_Num, MATCH найдет первую строку из всего списка, которая имеет то же Week_Num в Range4. Одним из решений является добавление тех же критериев к "lookup_array" MATCH, которые использовались в "lookup_value".

= ИНДЕКС (Диапазон8; ПОИСКПОЗ (МАКС (ЕСЛИ (Диапазон1 = 2013; ЕСЛИ (Диапазон2 = «апрель»; ЕСЛИ (Диапазон3 = «Алекса»; Диапазон4)))); ЕСЛИ (Диапазон1 = 2013; ЕСЛИ (Диапазон2 = «апрель» ; ЕСЛИ (Range3 = "Alexa"; Range4))); 0))

Опять же, используя Control-Shift-Enter.

Думаю, я правильно ввел формулу выше, но я не собирал лист для проверки, так что ...

Также имейте в виду, что с этой формулой все еще возникает проблема в том случае, если ни одна строка в списке не соответствует критериям. В этом случае вы получите ошибку. Решение состоит в том, чтобы добавить ЕСЛИОШИБКУ в начале всего этого.

= ЕСЛИОШИБКА (ИНДЕКС (Диапазон8; ПОИСКПОЗ (МАКС (ЕСЛИ (Диапазон1 = 2013; ЕСЛИ (Диапазон2 = «апрель»; ЕСЛИ (Диапазон3 = «Alexa»; Диапазон4)))); ЕСЛИ (Диапазон1 = 2013; ЕСЛИ (Диапазон2 = " апрель "; ЕСЛИ (Range3 =" Alexa "; Range4))); 0))," ")

person Chuck Trese    schedule 06.01.2015