Я пытался найти способ заполнить колонку рейтингов (изображение ниже), не вдаваясь в длинную формулу. Я пробовал VLOOKUPи 'INDEX /MATCH`, но зашел в тупик.
В обычном случае VLOOKUPесть только максимум 2 столбца для table_array, т.е. G2:J3 на основе моего примера. Но я хотел выбрать table_array на основе года (G3:G5), есть ли способ сделать это?
Любая помощь приветствуется!
решение1
Попробуйте эту формулу:
=IF(OR(D3>VLOOKUP(B3,G:J,2,FALSE),D3=VLOOKUP(B3,G:J,2,FALSE)),$H$2,IF(AND(D3<VLOOKUP(B3,G:J,2,FALSE),OR(D3>VLOOKUP(B3,G:J,3,FALSE),D3=VLOOKUP(B3,G:J,3,FALSE))),$I$2,$J$2))
решение2
Ваши определения могут быть недостаточно продуманными. У вас есть диапазоны, которые кажутся минимальными пороговыми значениями (оценка должна быть >=, чтобы соответствовать этому рейтингу). Однако есть оценки ниже «Плохо» без названия рейтинга. Если пороговые значения являются максимальными для каждого рейтинга, то нет рейтинга лучше, чем пороговое значение «Хорошо». Если пороговые значения являются смешанными (высокие для «Плохо», низкие для «Хорошо»), это будет определять среднее значение, так как же использовать среднее пороговое значение?
Вот подход к решению, который вы можете изменить, если переопределите диапазоны. Он основан на том, что пороговые значения являются минимумами для каждого рейтинга. Для этого требуется, чтобы таблица рейтингов была в порядке возрастания. Он масштабируем, так как вам нужно только настроить диапазон таблицы поиска; он не требует отдельной формулы поиска для каждого года:
Значения, которые не определены вашими пороговыми значениями рейтинга, возвращают ошибку; неясно, как вы хотите ее исправить. Формула в E3:
=INDEX($H$2:$J$2,,MATCH(D3,INDIRECT("$H$"&MATCH(B3,$G$1:$G$5,0)&":$J$"&MATCH(B3,$G$1:$G$5,0))))
ИНДЕКС возвращает название рейтинга на основе результата СООТВЕТСТВИЯ. СООТВЕТСТВИЕ сравнивает оценку с пороговыми значениями рейтинга.
Он определяет, какую строку таблицы поиска использовать, сопоставляя год. INDIRECT создает правильный диапазон поиска из этого результата.