В этой статье мы узнаем, как найти значение из справочной сетки при использовании списка индексов. Мы можем использовать комбинацию функций «СМЕЩЕНИЕ» и «ПОИСКПОЗ», чтобы получить результат в Microsoft Excel 2010.

СМЕЩЕНИЕ: возвращает ссылку на диапазон, который представляет собой заданное количество строк и столбцов из заданной ссылки.

Синтаксис: = OFFSET (ссылка, строки, столбцы, высота, ширина)

ссылка: это ссылка на ячейку или диапазон ячеек, от которых будет применяться смещение.

rows: количество строк вверх или вниз, которые вы хотите применить в качестве смещения к диапазону.

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

высота: это необязательно. Это количество строк, которое вы хотите, чтобы возвращаемая ссылка.

ширина: это необязательно. Это количество столбцов, которое вы хотите, чтобы возвращаемая ссылка.

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

Синтаксис = ПОИСКПОЗ (искомое_значение, поиск_массив, совпадение_тип)

lookup_value: значение, которое вы хотите искать lookup_array: таблица данных содержит информацию, из которой вы хотите вернуть результат.

match_type: 1,0 и -1 — три варианта.

1 (по умолчанию): будет найдено наибольшее значение в диапазоне. Список должен быть отсортирован по возрастанию.

0: найдет точное совпадение. -1: найдет наименьшее значение в диапазоне. Список должен быть отсортирован по убыванию.

Возьмем пример:

У нас есть список некоторых случайных чисел в диапазоне A1: E6. Ячейка A9 содержит значение из диапазона столбцов, то есть A1: E1. Ячейка B9 содержит значение из диапазона строк, то есть A1: A6. Нам нужна формула, чтобы найти совпадающее значение как в столбце, так и в строке.

img1

В ячейке D9 формула будет иметь вид = СМЕЩЕНИЕ ($ A $ 1, ПОИСКПОЗ (B9, $ A $ 1: $ A $ 6,0) -1, ПОИСКПОЗ (A9, $ A $ 1: $ E $ 1,0) -1)

  • Нажмите Enter на клавиатуре.

  • Функция вернет результат 100 (ячейка C3). Столбец C — 63, а значение 3 ^ rd ^ Row — 80.

img2

Если мы изменим значение строки в ячейке B9 с 80 на 43 *, результат будет рассчитан автоматически.

img3