Содержание:
Есть ли у вас таблицы с данными в Excel, размер которых можно изменять, т.е. количество строк (столбцов) может увеличиваться или уменьшаться в процессе работы? Если размеры таблицы «плавают», то вам придется постоянно следить за этим моментом и корректировать его:
- ссылки в формулах отчета, которые ссылаются на нашу таблицу
- начальные диапазоны сводных таблиц, построенных по нашей таблице
- начальные диапазоны графиков, построенных по нашей таблице
- диапазоны для раскрывающихся списков, которые используют нашу таблицу в качестве источника данных
Все это в совокупности не даст вам заскучать 😉
Гораздо удобнее и правильнее будет создать динамический «резиновый» диапазон, размер которого будет автоматически подстраиваться под реальное количество строк и столбцов данных. Для реализации этого есть несколько способов.
Способ 1. Умная таблица
Выделите диапазон ячеек и выберите на вкладке Главная – Форматировать как таблицу (Главная – Форматировать как таблицу):
Если вам не нужен полосатый дизайн, который добавляется в таблицу как побочный эффект, то его можно отключить на появившейся вкладке Конструктор (Дизайн). Каждая созданная таким образом таблица получает имя, которое можно заменить на более удобное там же на вкладке. Конструктор (Дизайн) в поле Название таблицы (Имя таблицы).
Теперь мы можем использовать динамические ссылки на нашу «умную таблицу»:
- Таблица 1 – ссылка на всю таблицу, кроме строки заголовка (A2:D5)
- Таблица1[#Все] — ссылка на всю таблицу (A1:D5)
- Таблица1[Питер] – ссылка на столбец диапазона без первого заголовка ячейки (C2:C5)
- Таблица1[#Заголовки] — ссылка на «шапку» с названиями столбцов (A1:D1)
Такие ссылки прекрасно работают в формулах, например:
= СУММ (Таблица1[Москва]) – расчет суммы по графе «Москва»
or
=ВПР(F5;Таблица 1;3;0) – найти в таблице месяц из ячейки F5 и выдать для него сумму Санкт-Петербурга (что такое ВПР?)
Такие ссылки можно с успехом использовать при создании сводных таблиц, выбрав на вкладке Вставка – Сводная таблица (Вставка – Сводная таблица) и введя имя смарт-таблицы в качестве источника данных:
Если выделить фрагмент такой таблицы (например, первые два столбца) и создать диаграмму любого типа, то при добавлении новых строк они будут автоматически добавляться на диаграмму.
При создании выпадающих списков нельзя использовать прямые ссылки на элементы смарт-таблицы, но это ограничение можно легко обойти тактическим приемом – воспользуйтесь функцией КОСВЕННЫЕ (КОСВЕННО), который превращает текст в ссылку:
Те. ссылка на умную таблицу в виде текстовой строки (в кавычках!) превращается в полноценную ссылку, и выпадающий список ее нормально воспринимает.
Способ 2. Динамический именованный диапазон.
Если превращать ваши данные в смарт-таблицу по каким-то причинам нежелательно, то можно воспользоваться чуть более сложным, но гораздо более тонким и универсальным методом — создать в Excel динамический именованный диапазон, ссылающийся на нашу таблицу. Тогда, как и в случае со смарт-таблицей, вы сможете свободно использовать имя созданного диапазона в любых формулах, отчетах, диаграммах и т.п. Начнем с простого примера:
Сложность задачи : создать динамический именованный диапазон, который будет ссылаться на список городов и автоматически растягиваться и уменьшаться в размерах при добавлении новых городов или их удалении.
Нам понадобятся две встроенные функции Excel, доступные в любой версии: ПОИКПОЗ (СООТВЕТСТВОВАТЬ) для определения последней ячейки диапазона и ИНДЕКС (ПОКАЗАТЕЛЬ) для создания динамической ссылки.
Нахождение последней ячейки с помощью ПОИСКПОЗ
ПОИСКПОЗ(искомое_значение, диапазон, тип_соответствия) – функция, которая ищет заданное значение в диапазоне (строке или столбце) и возвращает порядковый номер ячейки, в которой оно было найдено. Например, формула ПОИСКПОЗ("Март";A1:A5;0) в результате вернет число 4, поскольку слово "Март" находится в четвертой ячейке столбца A1:A5. Последний аргумент функции Match_Type = 0 означает, что мы ищем точное совпадение. Если этот аргумент не указан, то функция перейдет в режим поиска ближайшего наименьшего значения — именно это можно с успехом использовать для поиска последней занятой ячейки в нашем массиве.
Суть трюка проста. Функция ПОИСКПОЗ ищет ячейки в диапазоне сверху вниз и теоретически должна остановиться, когда найдет ближайшее к заданному наименьшее значение. Если в качестве искомого значения указать значение, заведомо большее, чем любое доступное в таблице, то ПОИСКПОЗ дойдет до самого конца таблицы, ничего не найдет и выдаст порядковый номер последней заполненной ячейки. И нам это нужно!
Если в нашем массиве только числа, то в качестве искомого значения мы можем указать число, заведомо большее любого из чисел в таблице:
Для гарантии можно использовать число 9E + 307 (9 умножить на 10 в 307-й степени, т.е. 9 с 307 нулями) — максимальное число, с которым в принципе может работать Excel.
Если в нашем столбце есть текстовые значения, то в качестве эквивалента максимально возможного числа можно вставить конструкцию REPEAT("i", 255) — текстовую строку, состоящую из 255 букв «i» — последнюю букву алфавит. Поскольку Excel фактически сравнивает коды символов при поиске, любой текст в нашей таблице технически будет «меньше», чем такая длинная строка «yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy»:
Создайте ссылку, используя INDEX
Теперь, когда мы знаем позицию последнего непустого элемента в таблице, осталось сформировать ссылку на весь наш диапазон. Для этого мы используем функцию:
ИНДЕКС(диапазон; номер_строки; номер_столбца)
Он выдает содержимое ячейки из диапазона по номеру строки и столбца, т.е. например, функция =ИНДЕКС(A1:D5;3;4) в нашей таблице с городами и месяцами из предыдущего метода даст 1240 – содержимое из 3-й строки и 4-го столбца, т.е. ячейки D3. Если столбец только один, то его номер можно не указывать, т.е. формула ИНДЕКС(A2:A6;3) на последнем скриншоте даст «Самара».
И есть один не совсем очевидный нюанс: если ИНДЕКС не просто вводится в ячейку после знака =, как обычно, а используется как конечная часть ссылки на диапазон после двоеточия, то он уже не выдает не содержимое ячейки, а ее адрес! Таким образом, формула типа $A$2:INDEX($A$2:$A$100;3) на выходе даст ссылку на диапазон A2:A4.
И здесь на помощь приходит функция ПОИСКПОЗ, которую мы вставляем внутрь ИНДЕКС для динамического определения конца списка:
=$A$2:ИНДЕКС($A$2:$A$100; МАТЧ(REP(“I”;255);A2:A100))
Создать именованный диапазон
Осталось все это упаковать в единое целое. Открыть вкладку формула (Формулы) И нажмите Менеджер имен (имя-менеджер). В открывшемся окне нажмите кнопку Создавай (Новый), введите в поле имя нашего диапазона и формулу Диапазон (Ссылка):
Осталось нажать OK а готовый диапазон можно использовать в любых формулах, раскрывающихся списках или диаграммах.
- Использование функции ВПР для связи таблиц и искомых значений.
- Как создать выпадающий список с автоматическим заполнением
- Как создать сводную таблицу для анализа большого объема данных