Excel At Excel вып.4: Универсальные ссылки на список
Ситуация: есть список каких-либо элементов на одном листе, а на другом листе - эти же элементы только уже с добавлением ряда показателей под каждым элементом.
Лист с элементами:
Лист с расчетами:
Каждый знает, как сделать лист с расчетами: ссылками вручную. Но если таких элементов 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. Количество строк между элементами на листе Расчеты ОДИНАКОВО.