Поиск экстремальных значений в таблицах: минимум и последний элемент
Чтобы найти минимальное значение в диапазоне, используйте функцию =МИН(диапазон). Для определения последнего заполненного значения или элемента по максимальной дате применяйте комбинацию функций ИНДЕКС и ПОИСКПОЗ (или XLOOKUP в новых версиях). Эти методы работают как с простыми списками чисел, так и со сложными таблицами с датами и условиями.
Базовый поиск минимума
Самый простой сценарий — нахождение наименьшего числа в столбце или строке. Функция МИН автоматически игнорирует текстовые значения и логические ошибки, но учитывает нули.
Синтаксис:
=МИН(A1:A100)
Если вам нужно найти минимум только среди чисел, удовлетворяющих определенному критерию (например, продажи только по городу «Москва»), используйте МИНЕСЛИ (доступна в Excel 2019 и новее) или классическую формулу массива.
Совет для старых версий: Если у вас Excel 2016 или старше, формула с условием должна вводиться как формула массива. После ввода нажмите Ctrl + Shift + Enter, чтобы заключить её в фигурные скобки {}.
Пример с условием:
Найти минимальную цену товара категории «Электроника» (категория в столбце A, цена в B):
=МИНЕСЛИ(A2:A100; "Электроника"; B2:B100)
Универсальный вариант для любой версии (работает как массив):
=МИН(ЕСЛИ(A2:A100="Электроника"; B2:B100))
Как найти последнее значение в списке
Понятие «последнее» может означать последнюю заполненную ячейку, запись с самой поздней датой или последний элемент, соответствующий условию. Выбор метода зависит от структуры ваших данных.
1. Последняя заполненная ячейка в столбце
Если данные идут сплошным списком без пропусков, достаточно посчитать количество заполненных ячеек и обратиться к ячейке с этим номером.
Формула:
=ИНДЕКС(B2:B100; СЧЁТЗ(B2:B100))
Где B2:B100 — ваш диапазон данных.
Если в списке есть пустые ячейки, но вам нужно именно последнее непустое значение снизу вверх, используйте эту надежную комбинацию (работает даже с пропусками внутри диапазона):
=ИНДЕКС(B2:B100; ПОИСКПОЗ(2; 1/(B2:B100<>"")))
(Для версий до 2019 года вводите через Ctrl+Shift+Enter)
2. Значение по последней дате
Частая задача: есть столбец с датами и столбец с показателями. Нужно найти показатель за самую свежую дату.
Логика действий:
- Найти максимальную дату в столбце дат (
МАКС). - Найти позицию этой даты (
ПОИСКПОЗ). - Вернуть значение из соседнего столбца по найденной позиции (
ИНДЕКС).
Готовая формула:
Даты в A2:A100, значения в B2:B100:
=ИНДЕКС(B2:B100; ПОИСКПОЗ(МАКС(A2:A100); A2:A100; 0))
Внимание к дубликатам: Если одна и та же максимальная дата встречается несколько раз, эта формула вернет значение из первого совпадения сверху. Чтобы получить именно последнюю запись по времени среди одинаковых дат, потребуется более сложная формула с фильтрацией по строкам.
3. Поиск с использованием современных функций (Excel 365 / 2021)
Владельцы подписки Microsoft 365 могут использовать функции ФИЛЬТР и XLOOKUP, что делает формулы читаемыми и мощными.
Последнее значение по условию через ФИЛЬТР:
Найти последний статус заказа для клиента «Иванов»:
=ПОСЛЕДНИЙ(ФИЛЬТР(C2:C100; B2:B100="Иванов"))
(Функция ПОСЛЕДНИЙ доступна в самых свежих обновлениях. Если её нет, используйте XLOOKUP).
Альтернатива через XLOOKUP (поиск с конца):
=XLOOKUP(1; 1/(B2:B100="Иванов"); C2:C100; "";; -1)
Аргумент -1 в конце заставляет функцию искать совпадение, начиная с конца списка.
Частые ошибки при работе с формулами
| Ошибка | Причина | Решение |
|---|---|---|
| #ЗНАЧ! | В диапазоне с числами есть текст или ошибки | Используйте =МИН(ЕСЛИОШИБКА(...)) или очистите диапазон |
| #Н/Д | Условие не найдено или диапазон пуст | Оберните формулу в =ЕСЛИОШИБКА(формула; "Нет данных") |
| Неверный результат | Даты хранятся как текст | Преобразуйте текст в даты через «Текст по столбцам» или функцию ДАТАЗНАЧ |
| 0 вместо минимума | В диапазоне есть пустые ячейки, которые трактуются как 0 | Убедитесь, что ячейки действительно пустые, а не содержат пробелы |
FAQ
Как найти последнее числовое значение, игнорируя текст?
Используйте формулу массива: =ИНДЕКС(A1:A100; МАКС(СТРОКА(A1:A100)*ЕЧИСЛО(A1:A100))). Она находит максимальный номер строки, где содержится число.
Можно ли найти минимум и максимум одновременно?
Да, но в разных ячейках. Для вывода в одну ячейку с текстом используйте сцепку: ="Мин: "&МИН(A1:A10)&" Макс: "&МАКС(A1:A10).
Почему формула последнего значения возвращает ошибку, если список пуст?
Функции ПОИСКПОЗ и ИНДЕКС не могут работать с пустыми ссылками. Всегда добавляйте обработку ошибок: =ЕСЛИОШИБКА(ваша_формула; "Список пуст").
Работают ли эти формулы в Google Таблицах?
Да, синтаксис функций МИН, ИНДЕКС, ПОИСКПОЗ, ФИЛЬТР в Google Sheets идентичен русскому Excel. Формулы массива в Google Sheets применяются автоматически (без Ctrl+Shift+Enter).