Динамический диапазон с автоподстройкой размеров
Есть ли у вас таблицы с данными в Excel, размеры которых могут изменяться, т.е. количество строк (столбцов) может увеличиваться или уменьшаться в процессе работы? Если размеры таблицы «плавают», то придется постоянно мониторить этот момент и подправлять:
- ссылки в формулах отчетов, которые ссылаются на нашу таблицу
- исходные диапазоны сводных таблиц, которые построены по нашей таблице
- исходные диапазоны диаграмм, построенных по нашей таблице
- диапазоны для выпадающих списков, которые используют нашу таблицу в качестве источника данных
Все это в сумме не даст вам скучать ;)
Гораздо удобнее и правильнее будет создать динамический «резиновый» диапазон, который автоматически будет подстраиваться в размерах под реальное количество строк-столбцов данных. Чтобы реализовать такое, есть несколько способов.
Способ 1. Умная таблица
Выделите ваш диапазон ячеек и выберите на вкладке Главная – Форматировать как Таблицу (Home – Format as Table):
Если вам не нужен полосатый дизайн, который добавляется к таблице побочным эффектом, то его можно отключить на появившейся вкладке Конструктор (Design). Каждая созданная таким образом таблица получает имя, которое можно заменить на более удобное там же на вкладке Конструктор (Design) в поле Имя таблицы (Table Name).
Теперь можно использовать динамические ссылки на нашу «умную таблицу»:
- Таблица1 – ссылка на всю таблицу кроме строки заголовка (A2:D5)
- Таблица1[#Все] – ссылка на всю таблицу целиком (A1:D5)
- Таблица1[Питер] – ссылка на диапазон-столбец без первой ячейки-заголовка (C2:C5)
- Таблица1[#Заголовки] – ссылка на «шапку» с названиями столбцов (A1:D1)
Такие ссылки замечательно работают в формулах, например:
=СУММ(Таблица1[Москва]) – вычисление суммы по столбцу «Москва»
или
=ВПР(F5;Таблица1;3;0) – поиск в таблице месяца из ячейки F5 и выдача питерской суммы по нему (что такое ВПР?)
Такие ссылки можно успешно использовать при создании сводных таблиц, выбрав на вкладке Вставка – Сводная таблица (Insert – Pivot Table) и введя имя умной таблицы в качестве источника данных:
Если выделить фрагмент такой таблицы (например, первых два столбца) и создать диаграмму любого типа, то при дописывании новых строк они автоматически будут добавляться к диаграмме.
При создании выпадающих списков прямые ссылки на элементы умной таблицы использовать нельзя, но можно легко обойти это ограничение с помощью тактической хитрости – использовать функцию ДВССЫЛ (INDIRECT), которая превращает текст в ссылку:
Т.е. ссылка на умную таблицу в виде текстовой строки (в кавычках!) превращается в полноценную ссылку, а уж ее выпадающий список нормально воспринимает.
Способ 2. Динамический именованный диапазон
Если превращение ваших данных в умную таблицу по каким-либо причинам нежелательно, то можно воспользоваться чуть более сложным, но гораздо более незаметным и универсальным методом – создать в Excel динамический именованный диапазон, ссылающийся на нашу таблицу. Потом, как и в случае с умной таблицей, можно будет свободно использовать имя созданного диапазона в любых формулах, отчетах, диаграммах и т.д. Для начала рассмотрим простой пример:
Задача: сделать динамический именованный диапазон, который ссылался бы на список городов и автоматически растягивался-сжимался в размерах при дописывании новых городов либо их удалении.
Нам потребуются две встроенных функции Excel, имеющиеся в любой версии – ПОИКСПОЗ (MATCH) для определения последней ячейки диапазона и ИНДЕКС (INDEX) для создания динамической ссылки.
Ищем последнюю ячейку с помощью ПОИСКПОЗ
ПОИСКПОЗ(искомое_значение;диапазон;тип_сопоставления) – функция, которая ищет заданное значение в диапазоне (строке или столбце) и выдает порядковый номер ячейки, где оно было найдено. Например, формула ПОИСКПОЗ(“март”;A1:A5;0) выдаст в качестве результата число 4, т.к. слово «март» расположено в четвертой по счету ячейке в столбце A1:A5. Последний аргумент функции Тип_сопоставления = 0 означает, что мы ведем поиск точного соответствия. Если этот аргумент не указать, то функция переключится в режим поиска ближайшего наименьшего значения – это как раз и можно успешно использовать для нахождения последней занятой ячейки в нашем массиве.
Суть трюка проста. ПОИСКПОЗ перебирает в поиске ячейки в диапазоне сверху-вниз и, по идее, должна остановиться, когда найдет ближайшее наименьшее значение к заданному. Если указать в качестве искомого значение заведомо больше, чем любое имеющееся в таблице, то ПОИСКПОЗ дойдет до самого конца таблицы, ничего не найдет и выдаст порядковый номер последней заполненной ячейки. А нам это и нужно!
Если в нашем массиве только числа, то можно в качестве искомого значения указать число, которое заведомо больше любого из имеющихся в таблице:
Для гарантии можно использовать число 9E+307 (9 умножить на 10 в 307 степени, т.е. 9 с 307 нулями) – максимальное число, с которым в принципе может работать Excel.
Если же в нашем столбце текстовые значения, то в качестве эквивалента максимально большого числа можно вставить конструкцию ПОВТОР(“я”;255) – текстовую строку, состоящую из 255 букв «я» - последней буквы алфавита. Поскольку при поиске Excel, фактически, сравнивает коды символов, то любой текст в нашей таблице будет технически «меньше» такой длинной «яяяяя….я» строки:
Формируем ссылку с помощью ИНДЕКС
Теперь, когда мы знаем позицию последнего непустого элемента в таблице, осталось сформировать ссылку на весь наш диапазон. Для этого используем функцию:
ИНДЕКС(диапазон; номер_строки; номер_столбца)
Она выдает содержимое ячейки из диапазона по номеру строки и столбца, т.е. например функция =ИНДЕКС(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):
Осталось нажать на ОК и готовый диапазон можно использовать в любых формулах, выпадающих списках или диаграммах.
Ссылки по теме
- Использование функции ВПР (VLOOKUP) для связывания таблиц и подстановки значений
- Как создать автоматически наполняющийся выпадающий список
- Как создать сводную таблицу для анализа большого массива данных
Информация с видео наглядней и запоминается намного лучше
=ЕСЛИОШИБКА(ГПР($A$1;$A:$A;ЕСЛИ(СЧЁТЕСЛИ($A$1:A1;A1)=1;СТРОКА(A1);"");0);""), а далее отбираем индекс/наименьший то что нам нужно... массивно.
=ИНДЕКС($D:$D;НАИМЕНЬШИЙ(ЕСЛИ(НЕ(ЕОШ(D1));СТРОКА($D$1:$D$100);"";);СТРОКА());1)
А почему вы брали такой вычурный вариант с ПОИСКПОЗ+ПОВТОР ? Почему не очевидный и компактный вариант со СЧЁТЗ, например? Уверен, этот вариант был в ваших предыдущих статьях...
Возник вопрос. Как сделать, чтобы при изменении размеров (добавление или сокращение количества записей) "умной" таблицы (Таблица1 из первого примера) ниже "умной" таблицы добавлялись или убирались строки? То есть, чтобы между нижним краем "умной " таблицы и значениями ниже (в данном примере ячейка B9), всегда было фиксированное количество строк.
С Уважением, Виктор.
Мне кажется, при поиске последней занятой позиции в текстовом списке нужно принудительно увеличивать найденное значение на единицу:
=ПОИСКПОЗ(ПОВТОР("я";255);$A$2:$A$100)+1В противном случае, фокус поиска (при поиске именно текстовых данных) встаёт на предпоследнюю позицию, а не на последнюю.
Косвенно это подтверждает и скриншот (где демонстрируется поиск позиции в списке городов)
Прекрасные статьи вы пишете, а с учетом видеоуроков вообще великолепно
получается.Да и сайт ваш-один из лучших по обучению работе с Ексель.
Спасибо вам за вашу работу, надеюсь и дальше будете продолжать в том же духе.
У меня возник вопрос по динамическому диапазону. Есть постоянно
меняющийся диапазон значений - столбец с добавлением ячеек в
нижележащие строки. В столбце имеются пустые ячейки. К сожалению,
избежать этого нельзя в силу ряда особенностей ввода данных. Т.е.
наличие пустых ячеек в столбце обязательно, скажем так.
Мне нужно решить две задачи с этими данными. Первая: посчитать
среднее арифметическое для определенного количества строк, начиная
с последней заполненной. Т.е., ввели данные в последнюю строку,
сместились, например, на 10 ячеек(строк) вверх, внутрь диапазона,
так сказать,и посчитали среднее для всех значений, которые вошли
в эти 10 ячеек. Повторюсь, в диапазоне обязательно имеются пустые,
не заполненные ничем, ячейки.
Вторая задача: исходные условия те же, что и в предыдущей, но считать
среднее нужно, сместившись вверх не на 10 ячеек(строк) подряд, как в первом
случае, а на 10 ЗАПОЛНЕННЫХ ячеек(строк), игнорируя пустые. Т.е. расчет ведется
только для 10 заполненных ячеек.При этом, надо понимать, эти 10 заполненных
ячеек могут приходиться на 15,20,30 и тд. ячеек, идущих подряд, начиная с нижней.
Получается, что расчеты нужно вести для динамического
диапазона, находящегося внутри другого динамического диапазона. Я не могу сообразить,
как это можно сделать. Может быть, поможете в решении данной проблемы? Из вашей статьи
я понял, как с помощью ПОИСКПОЗ определить последнюю заполненную ячейку и узнать её
содержимое с помощью ИНДЕКС. Но вот как выделить динамический диапазон внутри такого же
динамического диапазона и провести расчет для определенного количества ячеек - тут моих
знаний не хватает. Буду благодарен так же всем помимо Николая, кто как-то поможет
решить указанную проблему.
На форуме, кстати, искали-спрашивали? "Коллективный Разум" - штука мощная
Спасибо!!
подскажите, а почему файлы с такими "умными" таблицами работают дольше чем с простыми формулами? Имею файл - решила сделать его "умным", заменила обычные таблицы на такие "умные", в итоге постоянно проходит автообновление/пересчет при попытке сдвинуться с ячейки и вводе каждой буквы подвисает на 5-10 сек.
В чем может быть проблема?
спасибо за удивительно полезный ресурс.
у меня вопрос вот какой: принципиально ли с точки зрения размера файла ссылки делать динамическими или можно выделить весь столбец в формуле?
в файле огромное количетво раз используется формула =СУММЕСЛИМН(FRIS!K$13:K$1048576;FRIS!$A$13:$A$1048576... это конечно некрасиво, но работает?
Заранее спасибо за ответ.
Например в ячейке 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 итд.
Заранее спасибо за ответ ))
Ексель для меня засиял новыми горизонтами
Но можно вопрос не по теме? Вдруг здесь пробегают осведомлённые люди
А может кто-то знает, а есть ли в Гугл таблицах что то подобное созданию умных таблиц ( в Excel), когда становится возможным использовать в формулах названия столбцов)? нашел про добавление именных диапазонов, но как сделать ,чтобы при добавлении строк в таблицу автоматически расширялись именные диапазоны. Интересно работают ли формулы Николая в Гугл таблицах?
Спасибо. Извините за отнятое время, если что
Николай , спасибо вам за вашу работу !
"При вычислении ’Источник’ возникает ошибка. Вы хотите продолжить?" офис 365,
даже в вашем файле, не воспроизводится вставка проверки данных.
я правильно помню, что ДВССЫл ресурсоемкая формула, использование её при вводе ссылку на столбец умной таблицы, не замедлит обсчеты, если много строк с условием проверки данных?