#google-sheets
#google-таблицы
Вопрос:
Я пытаюсь создать динамическую панель мониторинга в Excel, чтобы менеджеры могли искать, на какой метрике нужно сосредоточиться для ряда компаний. Данные могут выглядеть как приведенная ниже таблица.
| A | B | C | D |
|---|---|---|---|
| Показатель 1 | Показатель 2 | Показатель 3 | |
| Компания 1 | 2.1 | 4.3 | 0.1 |
| Компания 2 | 5.0 | 3.2 | 3.1 |
| Компания 3 | 4.5 | 1.2 | 0.1 |
| Компания 4 | 1.0 | 1.0 | 3.1 |
| Компания 5 | 3.8 | 3.0 | 0.1 |
| Компания | Формула |
Итак, для компании 1 и т. Д. Я хочу показать, что метрика 2 должна быть основной областью внимания, для компании 2 это метрика 1 и т. Д.
Пока я пытаюсь сделать это, используя формулы index, match и max. Однако это не работает.
=index(B2:D6, match(B8, A2:A6, 0), match(max(B2:D6), B2:D6, 1))
Первая часть сопоставления работает правильно, но это нахождение максимального значения для правильного номера столбца, которое не работает. Я надеюсь, что у кого-то здесь есть представление о том, что я делаю неправильно.
Комментарии:
1. Каков ваш ожидаемый результат?
Ответ №1:
Вы можете использовать:
=INDEX(B1:D1,,MATCH(MAX(INDEX(B2:D6,MATCH(A8,A2:A6,0),0)),INDEX(B2:D6,MATCH(A8,A2:A6,0),0),0))
Дополнение:
Чтобы получить верхние N показателей, используйте AGGREGATE :
=INDEX($B$1:$D$1,,MATCH(AGGREGATE(14,4,INDEX($B$2:$D$6,MATCH($A$8,$A$2:$A$6,0),0),COLUMN(A1)),INDEX($B$2:$D$6,MATCH($A$8,$A$2:$A$6,0),0),0))
Скопируйте формулу вправо настолько, насколько вам нужно.
Комментарии:
1. Спасибо! Я всегда удивляюсь, когда вижу людей, которые много знают о такой теме. Я не знаю, возможно ли это, но есть ли способ получить топ-3 возможностей (или другое число)? Набор данных, с которым я работаю, насчитывает более 20, и было бы полезно иметь больше, но не все из них.
2. @MathiasLaursen В excel вы можете попытаться заменить
MAXнаAGGREGATE, чтобы получить верхние N показателей, просто обратите внимание, что если в строке есть несколько одинаковых значений, будет возвращена первая метрика. Я добавляю формулу для ответа.
Ответ №2:
попробуйте:
=IFNA(HLOOKUP(MAX(INDIRECT(
ADDRESS(MATCH(A9, A:A, 0), 2)amp;":"amp;
ADDRESS(MATCH(A9, A:A, 0), COLUMNS(1:1)))), {INDIRECT(
ADDRESS(MATCH(A9, A:A, 0), 2)amp;":"amp;
ADDRESS(MATCH(A9, A:A, 0), COLUMNS(1:1))); B1:1}, 2, 0))
