Вернуть максимум для каждой строки на основе нескольких критериев

Question

Я пытаюсь вернуть значение на основе трех критериев для каждой строки.

Основываясь на прилагаемой картинке, я хочу вернуть цену максимального значения, когда цвет одинаковый, а группа одинаковая.

Например, для color = Red и group = A максимальное значение равно 7, поэтому максимальный столбец цены должен быть 50 в любой момент времени color = Red и group = A.

Если это не ясно, пожалуйста, дайте мне знать, и я сделаю все возможное, чтобы уточнить.

Answer 1

Я предполагаю, что есть только одна уникальная комбинация Color, Group и Max. Другими словами, для данной комбинации Цвет / Группа будет только один максимум. С этим предположением мы можем использовать SUMIFS() чтобы добавить их, так как есть только один.

Хитрость заключается в том, чтобы выяснить, какая строка имеет наибольшее значение в столбце Макс для комбинации Цвет / Группа. Есть два варианта, в зависимости от того, какую версию Excel вы используете:

Excel 2016: =SUMIFS(Prices,Colors,A2,Groups,B2,Maxes,MAXIFS(Maxes,Colors,A2,Groups,B2))

Старые версии: { =SUMIFS(Prices,Colors,A2,Groups,B2,Maxes,MAX(IF(Colors&Groups=A2&B2,Maxes))) }} (вводится как формула массива - нажмите Ctrl-Shift-Enter)

Я сделал именованные диапазоны, чтобы формула была удобочитаемой («Цвета» - A2:A17 и т.д.).

Answer 2

Вы можете использовать простой (относительно в любом случае) индекс / совпадение, используя MAXIFS() . Во-первых, для простоты чтения формулы я создал именованные диапазоны для разных групп. Просто возьмите строки 2:[lastRow] ваших данных и присвойте им имя, соответствующее заголовку.

(Войдите с помощью CTRL+SHIFT+ENTER и перетащите вниз):

=INDEX(Price,MATCH($A2&$B2&MAXIFS(Max,color,A2,group,B2),color&group&Max,0))

Answer 3

Как это устроено:

1. Создайте таблицу под данными, включающую Color, Group, Max & Max Price .

2. Напишите эту формулу, чтобы получить Group Maximum в ячейке B20 и заполните:

     =SUMPRODUCT(LARGE(($A$2:$A$17=$A20)*($B$2:$B$17=$B20)*($C$2:$C$17),1))
   

3. Напишите эту формулу, чтобы получить Max Price в ячейке C20 и заполните.

     =SUMPRODUCT(LARGE(($A$2:$A$17=$A20)*($B$2:$B$17=$B20)*($C$2:$C$17=$C20)*($D$2:$D$17),1))
   

4. Просто чтобы сравнить исходные значения с результатами ниже, я применил красный цвет к значениям ячеек, что является необязательным.

5. При необходимости измените ссылки на ячейки в формуле.