ВПР максимум минимум — полная сводка пороговых значений и максимальных и минимальных результатов по категориям.
ВПР максимум минимум Решите задачу за 10 секунд. Ниже порога. Максимум (≤)ВПР(приблизительно), выше порога Минимум (≥)обрабатывается с помощью XLOOKUP или INDEX+MATCH(-1), а возврат максимального/минимального и связанных значений (например, названий продуктов) по категориям решается с помощью комбинации MAXIFS/MINIFS + XMATCH.
Quick Fix
- ≤ максимум (Сортировка по возрастанию в столбце А):
=VLOOKUP(H2, A2:C100, 3, TRUE) - ≥ минимум (сортировка не требуется, рекомендуется):
=XLOOKUP(H2, A2:A100, C2:C100, , 1) - Максимальное значение по категории:
=MAXIFS(C2:C100, A2:A100, K2) - Предмет этой стоимости:
=LET(m,MAXIFS(C2:C100,A2:A100,K2), INDEX(B2:B100, XMATCH(1,(A2:A100=K2)*(C2:C100=m))))
Концепция: аппроксимация и сортировка ВПР
ВПР TRUE Приблизительное соответствие основано на справочном столбце. По возрастаниюКогда "≤ порог 중 Максимальное значение” выбран. Напротив, “≥ порога 중 Минимум» — это XLOOKUP match_mode=1 или MATCH's -1(Базовый уровень По убыванию) реализовано.
Практический пример ① Сопоставление пороговых значений
| А (стандарт) | B (оценка) | C (пособие) |
|---|---|---|
| 60 | Бронза | 1% |
| 80 | Серебро | 3% |
| 95 | Золото | 5% |
| 110 | Платина | 7% |
=VLOOKUP(97, A2:C5, 2, TRUE) =XLOOKUP(97, A2:A5, B2:B5, , 1)Практический пример 2. Максимум/Минимум + связанные значения по категориям
| Категория | Товар | Цена |
|---|---|---|
| A | Альфа | 12 |
| A | Вершина | 19 |
| B | бета | 18 |
| B | болт | 18 |
| B | Храбрый | 22 |
=MAXIFS(C2:C6, A2:A6, F2)=LET(m,MAXIFS(C2:C6,A2:A6,F2), INDEX(B2:B6, XMATCH(1,(A2:A6=F2)*(C2:C6=m))))Все ничьи возвращаются (365):
=FILTER(B2:B6, (A2:A6=F2)*(C2:C6=MAXIFS(C2:C6, A2:A6, F2)))Замена/Расширение
- XLOOKUP:
match_mode± 1,search_modeПервый/последний контроль с ±1 - ИНДЕКС+ПОИСКПОЗ/XПОИСКПОЗ: ≥/≤ преобразование в направлении выравнивания
- ФИЛЬТР·СОРТИРОВКА: Лучшие N, тай-брейк
Контрольный список и меры предосторожности
VLOOKUP(...,TRUE)это стандартный столбец По возрастанию 필수- ≥ Минимум
XLOOKUP(...,1)또는MATCH(-1)+По убыванию - Найти значения по категории → строка:
MAXIFS/MINIFS+XMATCH/INDEX - Определить политику разрешения ничьей (первый/последний/оба)
- Смешанные числа/текст рассчитываются после очистки.
УСТРАНЕНИЕ НЕПОЛАДОК
| симптомы | причина | 해결 |
|---|---|---|
| Приблизительное значение соответствия неверно | Не отсортировано | Измените порядок сортировки ссылочного столбца на возрастающий или XLOOKUP. |
| ≥ Минимальная сложность реализации | Ограничения структуры VLOOKUP | Используйте XLOOKUP match_mode=1 |
| Какой ряд связан? | Дублировать макс/мин | Сначала с XMATCH, FILTERВернуть все в |
| Матч не удался | Смешанные цифры/текст, пробелы | ОБРЕЗКА·ЧИСТКАОрганизовать по |
Связанная статья
- Руководство по ВПР
- Руководство XLOOKUP
- Руководство MAXIFS·MINIFS
- Приложение ФИЛЬТР·СОРТБИ
- TRIM·CLEAN очистка данных