Каждый знает, как сделать лист с расчетами: ссылками вручную. Но если таких элементов 30 или 40? Это будет колоссальная трата времени. При этом при добавлении новых элементов в список на листе со списком, необходимо будет добавлять элементы на листе с расчетами, при этом соблюдая порядок.

Для автоматизированного решения можно воспользоваться уже знакомыми нам по прошлым выпускам Excelling at Excel (Excelling at Excel вып.1: Преодолеваем ограничения функции ВПР) функциями OFFSET (СМЕЩ) и MATCH (ПОИСКПОЗ).

Логика решения такова: элементы на листе Элементы и на листе Расчеты идут в одинаковом порядке, соответственно, элемент под номером n идет в след за элементом n - 1. Определив положение элемента n - 1 в исходном списке, легко получаем наименование элемента n.

В выпуске, посвященным циклам (Excelling at Excel вып.2: Циклы в Excel без VBA), мы решали эту задачи при помощи дополнительного столбца с порядковыми номерами элементов.

Сейчас мы обойдемся без вспомогательных столбцов.

Шаг 1. В первую строку листа с расчетами вставляем ссылку на ячейку с первым элементом из списка:

=Элементы!А2

Шаг 2. В ячейке, где должен быть второй элемент, пишем следующую формулу:

=OFFSET(Элементы!$A$1;MATCH(A1;Элементы!$A:$A;0);0)

Формула определяет позицию (функция MATCH) предыдущего элемента в списке (А1) на листе Элементы, а затем смещается (функция OFFSET) на соответствующее количество строк и, тем самым, получает наименование следующего элемента.

Шаг 3. Копируем строки сколько это необходимо.

ВАЖНО! Решение имеет несколько ограничений:

1. Название элементов в списке НЕ ПОВТОРЯЕТСЯ;

2. Количество строк между элементами на листе Расчеты ОДИНАКОВО.