Шаг 1: Понимание основ функции ИНДЕКС

Функция ИНДЕКС возвращает значение или ссылку на ячейку в таблице или диапазоне. У нее есть две основные формы синтаксиса:

ИНДЕКС(массив; номер_строки; [номер_столбца])

или

ИНДЕКС(ссылка; номер_строки; [номер_столбца]; [номер_области])

• массив/ссылка: Диапазон ячеек, из которого нужно извлечь данные.
• номер_строки: Номер строки в массиве/ссылке, из которой нужно вернуть значение. Это ключевой момент: номер строки всегда должен быть положительным целым числом.
• номер_столбца: (Необязательно) Номер столбца в массиве/ссылке. Также должен быть положительным целым числом.

Шаг 2: Почему отрицательные номера вызывают ошибку?

При попытке использовать формулу типа

=ИНДЕКС(A1:B10; -1; 1)

вы получите ошибку #ЗНАЧ! (VALUE!) или #ССЫЛКА! (REF!). Причина проста: в логике Excel не существует «-1-й» строки или «-2-го» столбца в заданном диапазоне. Функция ожидает, что вы укажете позицию элемента, отсчитывая от начала диапазона.

Шаг 3: Корректное использование ИНДЕКС и альтернативы

3.1. Исправление отрицательного номера

Если вы случайно ввели отрицательное число, просто исправьте его на соответствующее положительное. Например, чтобы получить значение из первой строки диапазона A1:B10 и первого столбца, используйте:

=ИНДЕКС(A1:B10; 1; 1)

3.2. Получение элемента с конца диапазона

Если ваша цель — получить элемент, отсчитывая с конца диапазона, вам потребуется комбинировать ИНДЕКС с другими функциями, например,

СТРОКИ()

(ROWS()) или

СЧЁТЗ()

(COUNTA()).

Пример: Получить значение из предпоследней строки диапазона A1:A10:

=ИНДЕКС(A1:A10; СТРОКИ(A1:A10) - 1)

Здесь

СТРОКИ(A1:A10)

вернет 10, и мы вычтем 1, чтобы получить 9-ю строку (предпоследнюю).

3.3. Использование ИНДЕКС в связке с ПОИСКПОЗ (MATCH)

Часто ИНДЕКС используется в комбинации с ПОИСКПОЗ для поиска значения по определенному критерию. Например, чтобы найти цену продукта «Монитор» в таблице:

=ИНДЕКС(B2:B10; ПОИСКПОЗ("Монитор"; A2:A10; 0))

Здесь

ПОИСКПОЗ("Монитор"; A2:A10; 0)

вернет номер строки, в которой находится «Монитор», а ИНДЕКС извлечет соответствующее значение из столбца B. Подробнее о поиске данных можно узнать в статье Поиск с учетом регистра в Excel: инструкция и решения.

3.4. Современные альтернативы: ПРОСМОТРX (XLOOKUP)

В более новых версиях Excel (Microsoft 365, Excel 2021) функция

ПРОСМОТРX

значительно упрощает многие задачи, для которых раньше требовались связки ИНДЕКС+ПОИСКПОЗ. Она позволяет искать значения как вперед, так и назад, и даже с конца диапазона, без сложных вычислений индексов.

=ПРОСМОТРX("Монитор"; A2:A10; B2:B10)

Частые ошибки / Устранение неполадок

• Ошибка #ЗНАЧ! или #ССЫЛКА!:
  • Причина 1: Использование отрицательных, нулевых или дробных значений для номер_строки или номер_столбца.
  • Решение: Убедитесь, что все номера строк и столбцов являются положительными целыми числами.
  • Причина 2: номер_строки или номер_столбца превышает размерность заданного массива/ссылки. Например, вы ищете 15-ю строку в диапазоне из 10 строк.
  • Решение: Проверьте, что указанные номера находятся в пределах диапазона. Используйте

    СТРОКИ()

    и

    СТОЛБЦЫ()

    для динамического определения размеров.

• Неправильные данные:
  • Причина: В качестве номер_строки или номер_столбца передается текст или логическое значение.
  • Решение: Убедитесь, что аргументы являются числовыми. Если вы получаете число из другой формулы, убедитесь, что она возвращает корректный числовой тип. Иногда требуется округлить число в Excel, чтобы получить целое значение для ИНДЕКС.
• Диапазон не определен или пуст:
  • Причина: массив/ссылка ссылается на несуществующий диапазон или пустую ячейку, которая должна содержать диапазон.
  • Решение: Проверьте правильность написания диапазона. Убедитесь, что он содержит данные.
• Проблемы с отображением:
  • Если ячейка с результатом ИНДЕКС отображается некорректно, возможно, дело в форматировании. Иногда требуется настроить автоувеличение высоты ячейки в Excel, чтобы увидеть весь контент.

Для отладки формул используйте функцию «Вычислить формулу» (на вкладке «Формулы» или Alt + M + V). Это поможет пошагово увидеть, как Excel обрабатывает каждый аргумент.