Извлечение крайних значений в диапазоне Excel без сортировки

Чтобы получить первое значение из диапазона в Excel, используйте функцию =INDEX(диапазон; 1). Для получения последнего непустого значения примените формулу =LOOKUP(2; 1/(диапазон<>"" ); диапазон) или комбинацию INDEX с COUNTA. Эти методы работают мгновенно, не требуют макросов и сохраняют актуальность при изменении данных. Ниже приведены проверенные решения для разных версий программы.

Универсальный способ: функция INDEX для начала списка

Самый надежный и понятный метод получения первого элемента — использование функции INDEX. Она возвращает значение из массива по указанному номеру позиции. Поскольку нам нужен самый первый элемент, позиция всегда равна 1.

Базовая формула:

=INDEX(A2:A100; 1)

Где A2:A100 — это ваш целевой диапазон. Формула вернет содержимое ячейки A2, даже если она пуста (вернет 0 или пустую строку).

Работа с пропусками в начале списка

Если в начале диапазона могут быть пустые ячейки, и вам нужно найти именно первую заполненную, стандартный INDEX не подойдет. В этом случае используйте связку с функцией MATCH, которая найдет позицию первого непустого элемента:

=INDEX(A:A; ПОИСПОЗ(ИСТИНА; A:A<>""; 0))

В англоязычной версии: =INDEX(A:A, MATCH(TRUE, A:A<>"", 0))

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

Поиск конца списка: LOOKUP и OFFSET

Найти последнее значение сложнее, так как количество строк может меняться. Существует два основных подхода: современный (через LOOKUP) и классический (через OFFSET).

Метод 1: Функция LOOKUP (Рекомендуемый)

Этот трюк использует особенность функции LOOKUP, которая ищет последнее совпадение в отсортированном массиве. Мы искусственно создаем массив, где искомое значение (2) заведомо больше любых возможных результатов (1), заставляя функцию идти до самого конца.

Формула для последнего непустого значения:

=ПОИСКПОЗ(2; 1/(A2:A100<>"" ); A2:A100)

В англоязычной версии: =LOOKUP(2, 1/(A2:A100<>""), A2:A100)

Как это работает:

1. A2:A100<>"" создает массив ИСТИНА/ЛОЖЬ.
2. 1/(...) превращает ИСТИНА в 1, а ЛОЖЬ в ошибку (#ДЕЛ/0!).
3. LOOKUP игнорирует ошибки и находит последнюю единицу, возвращая соответствующее значение из исходного диапазона.

Метод 2: Функция OFFSET (Альтернатива)

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

Формула:

=СМЕЩ(A2; СЧЁТЗ(A2:A100)-1; 0)

В англоязычной версии: =OFFSET(A2, COUNTA(A2:A100)-1, 0)

Здесь СЧЁТЗ определяет количество заполненных ячеек. Вычитая 1, мы получаем номер смещения от начальной ячейки A2 до последней заполненной.

Продвинутые решения для Excel 365 и 2021

Владельцы современных версий Excel могут использовать динамические массивы и функцию LET для улучшения читаемости и гибкости формул.

Использование LET для чистоты кода

Функция LET позволяет задать имя диапазону один раз и использовать его многократно. Это удобно, если диапазон сложный или длинный.

=LET(
  данные; A2:A100;
  первое; ИНДЕКС(данные; 1);
  последнее; ПОИСКПОЗ(2; 1/(данные<>"" ); данные);
  {"Первое: "&первое; "Последнее: "&последнее}
)

Эта формула вернет вертикальный массив с подписями и значениями.

Фильтрация перед извлечением

Если нужно получить первое/последнее значение не просто из списка, а только среди тех, что соответствуют условию (например, продажи больше 1000 руб.), используйте FILTER.

Первое значение по условию:

=ИНДЕКС(ФИЛЬТР(A2:A100; A2:A100>1000); 1)

Последнее значение по условию:

=ПОСЛЕДН(ФИЛЬТР(A2:A100; A2:A100>1000))

Примечание: Функция ПОСЛЕДН (TAKE с отрицательным аргументом или специальная функция в новых обновлениях) может отсутствовать в некоторых сборках. В таком случае используйте связку INDEX + ROWS на отфильтрованном массиве.

Сравнение методов извлечения данных

Частые ошибки при работе с формулами

1. Ошибка #Н/Д (#N/A): Возникает, если диапазон полностью пуст.
   • Решение: Оберните формулу в ЕСЛИОШИБКА: =ЕСЛИОШИБКА(ваша_формула; "Нет данных").
2. Неверный разделитель: В русской локали используется точка с запятой ;, в английской — запятая ,.
   • Решение: Проверьте настройки региона или замените разделители в формуле.
3. Ссылка на весь столбец в старых версиях: Использование A:A в формуле массива (как в трюке с LOOKUP) в очень старых версиях Excel (до 2007) может вызвать ошибку или сильное торможение.
   • Решение: Ограничьте диапазон конкретными строками, например A2:A10000.
4. Горизонтальные диапазоны: Если данные расположены в строке (A1:Z1), функции СТРОКИ (ROWS) нужно заменить на СТОЛБЦЫ (COLUMNS), хотя для INDEX и LOOKUP это часто не критично, если правильно указана размерность.

FAQ

Можно ли получить последнее числовое значение, игнорируя текст? Да. Измените условие в формуле LOOKUP. Вместо проверки на пустоту <>"", используйте ISNUMBER: =ПОИСКПОЗ(2; 1/(ЕЧИСЛО(A2:A100)); A2:A100)

Как вывести первое и последнее значение в одну ячейку через запятую? Используйте оператор сцепки &: =ИНДЕКС(A2:A100;1) & ", " & ПОИСКПОЗ(2; 1/(A2:A100<>"" ); A2:A100)

Работают ли эти формулы с датами? Да, даты в Excel хранятся как числа, поэтому все перечисленные методы (INDEX, LOOKUP, OFFSET) корректно обрабатывают их. Не забудьте установить формат ячейки с результатом как «Дата».