Использование динамических именованных диапазонов
| Категория: Формулы, просмотров: 32483, опубликовано: 12.05.2007 |
Одна из распространенных проблем при работе с таблицами состоит в том, что иногда мы не знаем точный размер области листа, заполненной нашими данными. Кроме того, в будущем в таблицу могут заноситься новые данные, или из нее могут быть удалены какие-то строки, т.е. размер таблицы может меняться.
Стандартное решение такой задачи - делая ссылку на таблицу, захватить пустые ячейки - выделить "с запасом". Такой способ имеет два серьезных недостатка:
- пустых строк может не хватить
- пустые ячейки, хоть и не содержат данных, но в некоторых ситуациях весьма заметно замедляют обработку данных
Красивым решением будет использовать динамический именованный диапазон - его размеры меняются автоматически, в зависимости от количества непустых ячеек в заданной области данных. Рассмотрим следующий пример - вот такую таблицу, количество записей в которой в будущем может меняться:
Чтобы создать динамический именованный диапазон, ссылающийся на эту таблицу, откроем меню Вставка - Имя - Присвоить (Insert - Name - Define) и введем туда Имя диапазона (например Железо) и формулу, которая будет ссылаться на заполненный диапазон:
=СМЕЩ($A$1;0;0;СЧЁТЗ($A:$A);3)
Функция СМЕЩ (OFFSET) выдает ссылку на диапазон, смещенный относительно исходного на заданное количество ячеек и имеет несколько аргументов:
- Ссылка - исходная ячейка, от которой идет отсчет - с нее начинается наш диапазон ($A$1)
- Смещение по строкам и столбцам - сдвиг начала диапазона относительно Ссылки - по нулям
- Высота - количество ячеек по вертикали, из которых состоит наш диапазон - определяем количество непустых ячеек в столбце А при помощи функции подсчета значений СЧЁТЗ($A:$A)
- Ширина - количество ячеек по горизонтали - в нашей таблице три столбца, поэтому = 3
Теперь динамическую ссылку Железо можно использовать в любой операции (фильтрации, консолидации, построении сводных таблиц, создания выпадающих списков в ячейках и т.д.) вместо выделения диапазона мышью.
Комментарии:
| Andrey G. 30.11.2007 |
sorry za translit akak budet СЧЁТЗ($A:$A) na angliskom excele?. |
30.11.2007 |
counta. |
30.11.2007 |
Andrey G COUNTA кажись. |
| Алекс 30.11.2007 |
Все ништяк, только нужно заметить что в именованном диапазоне нельзя оставлять пустых строк (записей). . |
| Антоха 11.04.2008 |
Алекс, это можно решить добавив столбец номер и по нему считать значения в высоте!. |
| Albert 18.08.2008 |
Добрый день! Очень интересный прием, но у меня почему-то этот диапазон не виден при попытке перейти к нему через меню ПЕРЕХОД и не появляется в поле имен. Хотя в диспетчере имен этот диапазон присутствует. Не пойму, что я делаю не так.. |
| Владимир 23.11.2008 |
Великолепное решение! Применений масса. Правда Excel 2007 имя не видит (как писал Albert), а жаль, может кто знает, как это решить , зато в VBA распознает без проблем. А вопрос такой - средствами VBA это сделать не трудно, скажем по событию change и перебору ячеек, но что эффективнее. А может кто знает, как красивше в VBA?. |
| Виталий 22.02.2009 |
Суппер решение. Подскажите пожалуйста как теперь этот диапозон умножить например на 2 и поместить данные на другой лист,причем если размер диапозона на первом листе изменяется то на другом листе ,который умножен на 2 также меняется размер диапозона. |
| Константин 30.07.2009 |
Зачем изобретать велосипед? 1. Создаём список 2. Присваиваем имя диапазону списка. 3. При изменении размеров списка, изменится и размер диапазона. Для того, собственно списки и существуют, имхо.. |
| Юрий 31.07.2009 |
Константин, проверьте Вашу версию, когда после создания списка из 10 строк, и присвоения ему имени, Вы добавите туда парочку. Количество строк в Вашем диапазоне НЕ ИЗМЕНИТСЯ. А в динамическом - изменится. На то он и динамический. Не вводите честной народ в заблуждение :-). |
| Алекс 09.08.2009 |
Контсантин, Ваш способ работает, если каждый раз вставлять строку внутрь диапазона. Но в таком случае, нет необходимости именовать диапазон, т.к. все формулы автоматически скорректируются. Если же просто добавлять текст (значения) в последнюю строку, то высота диапазона не изменится. Юрий, я бы сразу предупредил читателей, что у данного способа есть жесткое ограничение: список не может содержать пустые строки!. |
| Наталья 24.08.2009 |
А можно ли как-нибудь использовать динамический диапазон в диаграммах??? Я попробовала - рассчитался текущий диапазон, построилась диаграмма и в дальнейшем при добавлении данных диапазон для других формул пересчитывается, а для диаграммы остается неизменным.. |
| Алекс 24.08.2009 |
По поводу динамического добавления рядов, думаю без макросов не получится. (Может кто еще подскажет) А вот динмаическое добавление значений (ось Х) - это элементарно, просто надо в поле значения добавить имя динамического диапазона. например =Книга1!ДанныеА так же и в поле "Подписи горизонтальной оси" =Книга1!ПодписиХ см. http://office.microsoft.com/ru-ru/excel/HA011098011049.aspx . |
| Виктор 06.11.2009 |
>>когда после создания списка из 10 строк, и присвоения ему имени, Вы добавите туда парочку. Количество строк в Вашем диапазоне НЕ ИЗМЕНИТСЯ. А в динамическом - изменится. На то он и динамический. Не вводите честной народ в заблуждение :-). а у меня все расширяется, как надо. Хоть строку добавить, хоть 10 строк, хоть через функцию списка (где звездочка стоит), хоть сам, через вставку. Никакого ограничения в 10 строк нет. Екцель 2003 . |
| Дмитрий 01.12.2009 |
Я так понимаю, что этот динамический диапазон нельзя применять во взаимосвязанных выпадающих списках?. |
| Нурбол 21.12.2009 |
А что делать, если есть выше этой таблицы пустые строки? У меня обычно сверху название для всей таблицы. Столбцы со стоимостями в самом правом столбце. Попробую изобразить: :Начало попытки изображения: Название таблицы за период с"______" по "__________" №пп Наименование позиции Цена количество Стоимость ИТОГО 125820 1 Товар 1 100 1000 100000 2 Товар 2 12 300 3600 3 Товар 3 5 551 2755 4 Товар 4 150 112 16800 5 Товар 5 10 10 100 6 Товар 6 95 27 2565 :Конец попытки изображения: Так вот в строке итого где значение "125820" попытался вставить такую формулу "=СУММ(Продажи)", а диапазон "продажи" - "=СМЕЩ(Лист1!$E$7;0;0;СЧЁТ(Лист1!$E:$E)+3;1)". при этом Ексель ругается на ошибку циклической ссылки. Если вставить эту функцию не в этот столбец, а допустим рядом - ругаться не будет. А мне нужно в этом же столбце. что делать?. |
| Нурбол 21.12.2009 |
Попытка изобразить не очень удалась. В общем, там значение Итог "125820" должно стоять в том же столбце где и стоимость, т.е. сразу под "Стоимость" и над "100 000". |
| Alex 21.12.2009 |
в таком случае нельзя использовать СЧЁТ(Лист1!$E:$E) замените на: =СМЕЩ($E$7;0;0;СЧЁТ($E7:$E10000)+3;1). |
| Rustem 02.06.2010 |
Считаю, что тема использования динамических именованных диапазонов не раскрыта, нужна не только инструкция по созданию, но и по применению, например для консолидации данных из разных таблиц в одной.. |
| Ольга 12.08.2010 |
Скажите, пожалуйста, как в большом выпадающем динамическом списке на несколько тысяч фамилий быстро прийти к нужной букве?. |