Динамический диапазон с автоподстройкой размеров

127026 04.03.2015 Скачать пример

Есть ли у вас таблицы с данными в Excel, размеры которых могут изменяться, т.е. количество строк (столбцов) может увеличиваться или уменьшаться в процессе работы? Если размеры таблицы «плавают», то придется постоянно мониторить этот момент и подправлять:

  • ссылки в формулах отчетов, которые ссылаются на нашу таблицу
  • исходные диапазоны сводных таблиц, которые построены по нашей таблице
  • исходные диапазоны диаграмм, построенных по нашей таблице
  • диапазоны для выпадающих списков, которые используют нашу таблицу в качестве источника данных

Все это в сумме не даст вам скучать ;)

Гораздо удобнее и правильнее будет создать динамический «резиновый» диапазон, который автоматически будет подстраиваться в размерах под реальное количество строк-столбцов данных. Чтобы реализовать такое, есть несколько способов.

Способ 1. Умная таблица

Выделите ваш диапазон ячеек и выберите на вкладке Главная – Форматировать как Таблицу (Home – Format as Table):

dynamic_range1.png

Если вам не нужен полосатый дизайн, который добавляется к таблице побочным эффектом, то его можно отключить на появившейся вкладке Конструктор (Design). Каждая созданная таким образом таблица получает имя, которое можно заменить на более удобное там же на вкладке Конструктор (Design) в поле Имя таблицы (Table Name).

dynamic_range3.png

Теперь можно использовать динамические ссылки на нашу «умную таблицу»:

  • Таблица1 – ссылка на всю таблицу кроме строки заголовка (A2:D5)
  • Таблица1[#Все] – ссылка на всю таблицу целиком (A1:D5)
  • Таблица1[Питер] – ссылка на диапазон-столбец без первой ячейки-заголовка (C2:C5)
  • Таблица1[#Заголовки] – ссылка на «шапку» с названиями столбцов (A1:D1)

Такие ссылки замечательно работают в формулах, например:

=СУММ(Таблица1[Москва]) – вычисление суммы по столбцу «Москва»

или

=ВПР(F5;Таблица1;3;0) – поиск в таблице месяца из ячейки F5 и выдача питерской суммы по нему (что такое ВПР?)

Такие ссылки можно успешно использовать при создании сводных таблиц, выбрав на вкладке Вставка – Сводная таблица (Insert – Pivot Table) и введя имя умной таблицы в качестве источника данных:

dynamic_range4.png

Если выделить фрагмент такой таблицы (например, первых два столбца) и создать диаграмму любого типа, то при дописывании новых строк они автоматически будут добавляться к диаграмме.

При создании выпадающих списков прямые ссылки на элементы умной таблицы использовать нельзя, но можно легко обойти это ограничение с помощью тактической хитрости – использовать функцию ДВССЫЛ (INDIRECT), которая превращает текст в ссылку:

dynamic_range5.png

Т.е. ссылка на умную таблицу в виде текстовой строки (в кавычках!) превращается в полноценную ссылку, а уж ее выпадающий список нормально воспринимает.

Способ 2. Динамический именованный диапазон

Если превращение ваших данных в умную таблицу по каким-либо причинам нежелательно, то можно воспользоваться чуть более сложным, но гораздо более незаметным и универсальным методом – создать в Excel динамический именованный диапазон, ссылающийся на нашу таблицу. Потом, как и в случае с умной таблицей, можно будет свободно использовать имя созданного диапазона в любых формулах, отчетах, диаграммах и т.д. Для начала рассмотрим простой пример:

dynamic_range6.png

Задача: сделать динамический именованный диапазон, который ссылался бы на список городов и автоматически растягивался-сжимался в размерах при дописывании новых городов либо их удалении.

Нам потребуются две встроенных функции Excel, имеющиеся в любой версии – ПОИКСПОЗ (MATCH) для определения последней ячейки диапазона и ИНДЕКС (INDEX) для создания динамической ссылки.

Ищем последнюю ячейку с помощью ПОИСКПОЗ

ПОИСКПОЗ(искомое_значение;диапазон;тип_сопоставления) – функция, которая ищет заданное значение в диапазоне (строке или столбце) и выдает порядковый номер ячейки, где оно было найдено. Например, формула ПОИСКПОЗ(“март”;A1:A5;0) выдаст в качестве результата число 4, т.к. слово «март» расположено в четвертой по счету ячейке в столбце A1:A5. Последний аргумент функции Тип_сопоставления = 0 означает, что мы ведем поиск точного соответствия. Если этот аргумент не указать, то функция переключится в режим поиска ближайшего наименьшего значения – это как раз и можно успешно использовать для нахождения последней занятой ячейки в нашем массиве.

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

Если в нашем массиве только числа, то можно в качестве искомого значения указать число, которое заведомо больше любого из имеющихся в таблице:

dynamic_range7.png

Для гарантии можно использовать число 9E+307 (9 умножить на 10 в 307 степени, т.е. 9 с 307 нулями) – максимальное число, с которым в принципе может работать Excel.

Если же в нашем столбце текстовые значения, то в качестве эквивалента максимально большого числа можно вставить конструкцию ПОВТОР(“я”;255) – текстовую строку, состоящую из 255 букв «я» - последней буквы алфавита. Поскольку при поиске Excel, фактически, сравнивает коды символов, то любой текст в нашей таблице будет технически «меньше» такой длинной «яяяяя….я» строки:

dynamic_range8.png

Формируем ссылку с помощью ИНДЕКС

Теперь, когда мы знаем позицию последнего непустого элемента в таблице, осталось сформировать ссылку на весь наш диапазон. Для этого используем функцию:

ИНДЕКС(диапазон; номер_строки; номер_столбца)

Она выдает содержимое ячейки из диапазона по номеру строки и столбца, т.е. например функция =ИНДЕКС(A1:D5;3;4) по нашей таблице с городами и месяцами из предыдущего способа выдаст 1240 – содержимое из 3-й строки и 4-го столбца, т.е. ячейки D3. Если столбец всего один, то его номер можно не указывать, т.е. формула ИНДЕКС(A2:A6;3) выдаст «Самару» на последнем скриншоте.

Причем есть один не совсем очевидный нюанс: если ИНДЕКС не просто введена в ячейку после знака =, как обычно, а используется как финальная часть ссылки на диапазон после двоеточия, то выдает она уже не содержимое ячейки, а ее адрес! Таким образом формула вида $A$2:ИНДЕКС($A$2:$A$100;3) даст на выходе уже ссылку на диапазон A2:A4.

И вот тут в дело вступает функция ПОИСКПОЗ, которую мы вставляем внутрь ИНДЕКС, чтобы динамически определить конец списка:

=$A$2:ИНДЕКС($A$2:$A$100; ПОИСКПОЗ(ПОВТОР("я";255);A2:A100))

Создаем именованный диапазон

Осталось упаковать все это в единое целое. Откройте вкладку Формулы (Formulas) и нажмите кнопку Диспетчер Имен (Name Manager). В открывшемся окне нажмите кнопку Создать (New), введите имя нашего диапазона и формулу в поле Диапазон (Reference):

Динамический именованный диапазон

Осталось нажать на ОК и готовый диапазон можно использовать в любых формулах, выпадающих списках или диаграммах.

Ссылки по теме

 



07.03.2015 13:58:19
Николай, Спасибо искренне за Вашу работу
Информация с видео наглядней и запоминается намного лучше
10.03.2015 15:18:20
Одно маленькое замечание. Если изначально создавать динамический диапазон на основании пустых, незаполненных ячеек, при создании выпадающего списка Excel выдаст собщение о том, что при вычислении диапазона произоша ошибка, так как формула дает ошибку #Н\Д. Подавление ошибок тоже ничего не даст. Так что если нужно создать динамический диапазон с изначально пустыми ячейками (как было в моем случае) - надо, например, ставить пробел в первую ячейку диапазона.
23.03.2015 21:22:48
Динамический вертикальный диапазон: через ГПР, которая цепляется за шапку столбца, а мы получаем необходимые номера строк:

=ЕСЛИОШИБКА(ГПР($A$1;$A:$A;ЕСЛИ(СЧЁТЕСЛИ($A$1:A1;A1)=1;СТРОКА(A1);"");0);""), а далее отбираем индекс/наименьший то что нам нужно... массивно.
=ИНДЕКС($D:$D;НАИМЕНЬШИЙ(ЕСЛИ(НЕ(ЕОШ(D1));СТРОКА($D$1:$D$100);"";);СТРОКА());1)
30.03.2015 12:00:00
Николай, день добрый.
А почему вы брали такой вычурный вариант с ПОИСКПОЗ+ПОВТОР ? Почему не очевидный и компактный вариант со СЧЁТЗ, например? Уверен, этот вариант был в ваших предыдущих статьях...
01.04.2015 13:07:39
Если в таблице есть пустые строки (ячейки), то СЧЕТЗ будет считать конец таблицы неправильно.
01.04.2015 13:43:30
Точно-точно, об этом я не подумал :)
04.04.2015 08:10:48
Николай, спасибо!
Возник вопрос. Как сделать, чтобы при изменении размеров (добавление или сокращение количества записей) "умной" таблицы (Таблица1 из первого примера) ниже "умной" таблицы добавлялись или убирались строки? То есть, чтобы между нижним краем "умной " таблицы и значениями ниже (в данном примере ячейка B9), всегда было фиксированное количество строк.
С Уважением, Виктор.
06.04.2015 10:32:05
Добрый день.

Мне кажется, при поиске последней занятой позиции  в текстовом списке нужно принудительно увеличивать найденное значение на единицу:

=ПОИСКПОЗ(ПОВТОР("я";255);$A$2:$A$100)+1

В противном случае, фокус поиска (при поиске именно текстовых данных) встаёт на предпоследнюю позицию, а не на последнюю.
Косвенно это подтверждает и скриншот (где демонстрируется поиск позиции в списке городов)
07.04.2015 15:30:58
Здравствуйте, Николай!

Прекрасные статьи вы пишете, а с учетом видеоуроков вообще великолепно
получается.Да и сайт ваш-один из лучших по обучению работе с Ексель.
Спасибо вам за вашу работу, надеюсь и дальше будете продолжать в том же духе.
 У меня возник вопрос по динамическому диапазону. Есть постоянно
меняющийся диапазон значений - столбец с добавлением ячеек в
нижележащие строки. В столбце имеются пустые ячейки. К сожалению,
избежать этого нельзя в силу ряда особенностей ввода данных. Т.е.
наличие пустых ячеек в столбце обязательно, скажем так.
 Мне нужно решить две задачи с этими данными. Первая: посчитать
среднее арифметическое для определенного количества строк, начиная
с последней заполненной. Т.е., ввели данные в последнюю строку,
сместились, например, на 10 ячеек(строк) вверх, внутрь диапазона,
так сказать,и посчитали среднее для всех значений, которые вошли
в эти 10 ячеек. Повторюсь, в диапазоне обязательно имеются пустые,
не заполненные ничем, ячейки.
 Вторая задача: исходные условия те же, что и в предыдущей, но считать
среднее нужно, сместившись вверх не на 10 ячеек(строк) подряд, как в первом
случае, а на 10 ЗАПОЛНЕННЫХ ячеек(строк), игнорируя пустые. Т.е. расчет ведется
только для 10 заполненных ячеек.При этом, надо понимать, эти 10 заполненных
ячеек могут приходиться на 15,20,30 и тд. ячеек, идущих подряд, начиная с нижней.
Получается, что расчеты нужно вести для динамического
диапазона, находящегося внутри другого динамического диапазона. Я не могу сообразить,
как это можно сделать. Может быть, поможете в решении данной проблемы? Из вашей статьи
я понял, как с помощью ПОИСКПОЗ определить последнюю заполненную ячейку и узнать её
содержимое с помощью ИНДЕКС. Но вот как выделить динамический диапазон внутри такого же
динамического диапазона и провести расчет для определенного количества ячеек - тут моих
знаний не хватает. Буду благодарен так же всем помимо Николая, кто как-то поможет
решить указанную проблему.  
09.04.2015 09:06:02
По описанию врубиться в проблему сложновато. Есть файл с конкретной задачей, где это нужно применить? Шлите в почту с комментариями - посмотрю что можно сделать.

На форуме, кстати, искали-спрашивали? "Коллективный Разум" - штука мощная :)
08.04.2015 11:59:03
Вообще при работе с "умной" таблицей или "умным" списком (ведь его тоже можно сделать "умной" таблицей) отпадает необходимость в таких сложных формулах. При добавлении новой/последней записи в "умный" список она сразу же попадает в контейнер объекта (т.е. данного списка) и появляется в выпадающем списке! Это самый разумный и быстрый способ при добавлении новых записей в именованный диапазон, без использования формул
08.04.2015 14:40:55
Я пробовал работать и с "умными" и со сводными таблицами. Вещь хорошая, но мне не подходит по ряду причин. Опытным путем выяснил, что наилучшее решение дает запись именно формулой. Макрос тоже не решит проблему в полной мере. Только формула. Сложная, конечно, получится, поэтому и прошу помощи у Николая, вся надежда на него.
30.04.2015 13:24:26
Здравствуйте! Спасибо за полезные материалы! Подскажите, пожалуйста, как реализовать динамический диапазон (с помощью поискпоз и индекс) не на 1 столбец, как было в обоих ваших примерах, а на таблицу из нескольких десятков строк и столбцов? Пробовал указывать диапазон таблицы и с запасом, но как диапазон она не выделяется. Спасибо!
17.06.2015 18:56:40
Добрый день, Николай! "Умные" таблицы вещь классная конечно, но у меня такое представление, может старомодное немного, что формула все-таки надежнее. Так вот после написания формулы (в диспетчере имен), список этого имени (не выпадающий, а просто список в ряд), который я вызываю далее и который мне нужен собственно для другой таблицы весь порушился. При просмотре в диспетчере имен в тех именах, где я просто указал диапазон, идут данные, например, {"Вася";"Дима"; и т.д.},  а в случае с формулой - {...}, и вызываемый из этого имени список в ряд больше не отображается. Как можно справится с этой проблемой? Спасибо за дельные советы!
24.06.2015 16:52:24
Спасибо огромное! Реализовала! А ведь так давно мучилась с этим динамическим диапазоном! Как говорится в известном мультфильме, лучше день потерять, потом за пять минут долететь!
05.09.2015 13:51:00
Здравствуйте. Как можно из VBA пробежаться циклом по такому именованному диапазону?
Спасибо!!
10.09.2015 07:26:06
Здравствуйте! Подскажите, пожалуйста, как в Excel 2013 выполнить автоматическое добавление пустых строк перед итогом, если в "умную" таблицу были добавлены данные? В видео эта ситуация показана для первой тааблицы: после добавления сентября пустых строк уже не останется и, если добавить октябрь, то он "залезет" на строку, в которой "Сумма по Москве". :cry: Новая настройка формул для итогов (в связи с новыми данными в таблице) не принципиальна, так как мне нужно просто отодвинуть следующий раздел в Excel, если в "умной" таблице сверху появились ноые строки. Спасибо заранее :)
06.04.2016 15:34:40
Добрый день!

подскажите, а почему файлы с такими "умными" таблицами работают дольше чем с простыми формулами? Имею файл - решила сделать его "умным", заменила обычные таблицы на такие "умные", в итоге постоянно проходит автообновление/пересчет при попытке сдвинуться с ячейки и вводе каждой буквы подвисает на 5-10 сек.
В чем может быть проблема?
K S
26.05.2017 14:01:19
добрый день!
спасибо за удивительно полезный ресурс.
у меня вопрос вот какой: принципиально ли с точки зрения размера файла ссылки делать динамическими или можно выделить весь столбец в формуле?

в файле огромное количетво раз используется формула =СУММЕСЛИМН(FRIS!K$13:K$1048576;FRIS!$A$13:$A$1048576... это конечно некрасиво, но работает?
Заранее спасибо за ответ.
06.03.2018 13:45:09
Здравствуйте. У меня формулой XML подтягивается из Инета и преобразуется через XPath. Вручную я его в таблицу конечно преобразовываю, но захотел сделать умную таблицу. И тут проблема, когда захожу в "Размер таблицы" и задаю диапазон формулой по смещению относительно количества загруженных строк, то Excel автоматом диапазон формулой превращает в статичный. И никак не могу обойти данный процесс.
25.05.2018 04:17:51
Подскажите пожалуйста, а как с помощью числа в ячейке определить последовательный интервал чисел в динамическом (саморастягивающемся) диапозоне?
Например в ячейке A1 ввожу число 10, и у меня  в строке №3 заполняются ячейки со значениями 1,2,3,4,5,6,7,8,9,10
А если я введу число 12 то соответственно в строке №3 заполнится уже 12 ячеек (к примеру с A3 по L3) наполнятся значениями 1, 2,3,4,5,6,7,8,9,10,11,12  итд.
Заранее спасибо за ответ ))
20.02.2019 18:40:03
Недавно открыл для себя этот сайт и Просто склоняю голову перед объёмом знаний Николая!!!!!!!!!!
Ексель для меня засиял новыми горизонтами :), а надстройку, обязательно куплю, просто БОМБА!

Но можно вопрос не по теме? Вдруг здесь пробегают осведомлённые люди
А может кто-то знает, а есть ли в Гугл таблицах что то подобное созданию умных таблиц ( в Excel), когда становится возможным использовать в формулах названия столбцов)? нашел про добавление именных диапазонов, но как сделать ,чтобы при добавлении строк в таблицу автоматически расширялись именные диапазоны. Интересно работают ли формулы Николая в Гугл таблицах?
Спасибо. Извините за отнятое время, если что
Наверх