Введение в динамические диапазоны
Функция ВПР часто используется для поиска информации, хранящейся в таблицах Excel. Так, например, если у нас есть список имен и возрастов людей:
А затем мы можем в соседней ячейке использовать функцию ВПР, чтобы определить возраст Пола:
Пока это довольно стандартно. Но что произойдет, если нам нужно добавить еще несколько имен в список? Очевидной мыслью было бы изменить диапазон в ВПР. Однако в действительно сложной модели может быть несколько ссылок на ВПР. Это означает, что нам придется изменить каждую ссылку - при условии, что мы знаем, где они находятся.
Однако Excel предоставляет альтернативный способ - ДИНАМИЧЕСКИЙ диапазон. Это диапазон, который автоматически расширяет обновления. Это идеально, если ваши списки постоянно расширяются (например, данные о продажах по месяцам).
Чтобы настроить динамический диапазон, нам нужно иметь имя диапазона, поэтому мы назовем его AGE_DATA. Подход к настройке динамических диапазонов в Excel 2007 и более ранних версиях Excel отличается:
В Excel 2007 нажмите «Определить имя» под формулами:
В более ранних версиях Excel нажмите «Вставить», а затем «Имена».
Во всплывающем окне введите имя нашего динамического диапазона - «ВОЗРАСТНЫЕ ДАННЫЕ»:
В поле «Относится к» нам нужно ввести диапазон наших данных. Это будет достигнуто с помощью функции СМЕЩЕНИЕ. У этого есть 5 аргументов:
= СМЕЩЕНИЕ (Ссылка, Строки, Столбцы, Высота, Ширина)
- Ссылка - это адрес ЛЕВОГО ВЕРХНЕГО угла нашего диапазона - в данном случае ячейки B5.
- Строки - это количество строк из ВЕРХНЕГО СЛЕВА, которое мы хотим, чтобы этот диапазон был - в данном случае это будет 0.
- Cols - это количество строк из ВЕРХНЕГО СЛЕВА, которое мы хотим, чтобы этот диапазон был - который в данном случае будет равен 0
- Высота диапазона - см. Ниже.
- Ширина диапазона - это 2, если у нас есть ДВА столбца в нашем диапазоне (имя человека и их возраст)
Теперь высота диапазона должна варьироваться в зависимости от количества записей в нашей таблице (сейчас их 7).
Конечно, нам нужен способ подсчета строк в нашей таблице, который обновляется автоматически, поэтому один из способов сделать это - использовать функцию COUNTA. Это просто подсчитывает количество непустых ячеек в диапазоне. Поскольку наши имена находятся в столбце B, количество записей в наших данных равно COUNTA (B: B).
Обратите внимание, что если вы поместите это в ячейку, вы получите значение 8, поскольку оно включает заголовок Names. Однако это не имеет значения.
Итак, в поле «Относится к» мы помещаем:
= СМЕЩЕНИЕ ($ B $ 5,0,0; counta (B: B); 2)
И нажмите кнопку ОК. Теперь наш динамический диапазон создан.
Теперь вернитесь к формулам ВПР и замените диапазон $ B: 4: $ C11 на имя нашего нового динамического диапазона AGE_DATA, чтобы у нас было:
Пока ничего не изменилось. Однако если мы добавим в нашу таблицу еще несколько имен:
И в ячейке, где у нас был Пол, замените его новым именем, например Педро (которого не было в исходном списке):
И мы видим, что Excel автоматически возвращает возраст Педро, хотя мы не меняли формулы ВПР. Вместо этого объем динамического диапазона увеличился за счет включения дополнительных имен.
Динамические диапазоны очень полезны при увеличении объемов данных, особенно когда требуются таблицы VLOOKUP и PIVOT.
