Vba excel изменить размер таблицы
Есть ли у вас таблицы с данными в Excel, размеры которых могут изменяться, т.е. количество строк (столбцов) может увеличиваться или уменьшаться в процессе работы? Если размеры таблицы «плавают», то придется постоянно мониторить этот момент и подправлять:
- ссылки в формулах отчетов, которые ссылаются на нашу таблицу
- исходные диапазоны сводных таблиц, которые построены по нашей таблице
- исходные диапазоны диаграмм, построенных по нашей таблице
- диапазоны для выпадающих списков, которые используют нашу таблицу в качестве источника данных
Все это в сумме не даст вам скучать ;)
Гораздо удобнее и правильнее будет создать динамический «резиновый» диапазон, который автоматически будет подстраиваться в размерах под реальное количество строк-столбцов данных. Чтобы реализовать такое, есть несколько способов.
Способ 1. Умная таблица
Выделите ваш диапазон ячеек и выберите на вкладке Главная – Форматировать как Таблицу (Home – Format as Table):
Если вам не нужен полосатый дизайн, который добавляется к таблице побочным эффектом, то его можно отключить на появившейся вкладке Конструктор (Design). Каждая созданная таким образом таблица получает имя, которое можно заменить на более удобное там же на вкладке Конструктор (Design) в поле Имя таблицы (Table Name) .
Теперь можно использовать динамические ссылки на нашу «умную таблицу»:
Такие ссылки замечательно работают в формулах, например:
=СУММ(Таблица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) :
Осталось нажать на ОК и готовый диапазон можно использовать в любых формулах, выпадающих списках или диаграммах.
Искал в на форуме и вообще в инете, но не нашёл.
Вопрос к профессионалам.
Есть ли в Excel 2013 возможность изменять размер динамической таблицы по вертикали при помощи макроса?
Искал в на форуме и вообще в инете, но не нашёл.
Вопрос к профессионалам.
Есть ли в Excel 2013 возможность изменять размер динамической таблицы по вертикали при помощи макроса? AwdBor
Вопрос к профессионалам.
Есть ли в Excel 2013 возможность изменять размер динамической таблицы по вертикали при помощи макроса? Автор - AwdBor
Дата добавления - 09.07.2015 в 03:50
_Boroda_, про это да не совсем. Чтобы этот вопрос решался рекордером - я бы не спрашивал)
Допустим, таблица содержит 500 строк. И периодически появляется необходимость передвинуть нижнюю границу таблицы, к примеру, в начало.
На строку номер 20. А потом опять вниз передвинуть, на строку 150 и т.д.
В примере на Лист1 таблица, в которой данные зависят от размера таблицы на Лист2.
На Лист1 вверху кнопки.
Щелк мышкой по "5 вниз" - и нижняя граница таблицы на Лист2 на 5 строк вниз опустилась (независимо от того, какого размера была изначально).
Еще раз щёлкнуть - и нижняя граница таблицы уже в итоге на 10 строк ниже стала.
Щёлк мышкой по "1 вверх" - и нижняя граница таблицы на Лист2 поднялась вверх на одну строку и т.д.
Т.е. на фактически для такой задачи нужны четыре макроса.
Если я правильно понимаю, надо объявлять переменные вместо $A$1:$B$4 , а то еще и циклы использовать?
Или проще будет убрать так называемую умную таблицу и колдовать при помощи СМЕЩ, СУММЕСЛИ и т.п.?
_Boroda_, про это да не совсем. Чтобы этот вопрос решался рекордером - я бы не спрашивал)
Допустим, таблица содержит 500 строк. И периодически появляется необходимость передвинуть нижнюю границу таблицы, к примеру, в начало.
На строку номер 20. А потом опять вниз передвинуть, на строку 150 и т.д.
В примере на Лист1 таблица, в которой данные зависят от размера таблицы на Лист2.
На Лист1 вверху кнопки.
Щелк мышкой по "5 вниз" - и нижняя граница таблицы на Лист2 на 5 строк вниз опустилась (независимо от того, какого размера была изначально).
Еще раз щёлкнуть - и нижняя граница таблицы уже в итоге на 10 строк ниже стала.
Щёлк мышкой по "1 вверх" - и нижняя граница таблицы на Лист2 поднялась вверх на одну строку и т.д.
Т.е. на фактически для такой задачи нужны четыре макроса.
Если я правильно понимаю, надо объявлять переменные вместо $A$1:$B$4 , а то еще и циклы использовать?
Или проще будет убрать так называемую умную таблицу и колдовать при помощи СМЕЩ, СУММЕСЛИ и т.п.? AwdBor
Допустим, таблица содержит 500 строк. И периодически появляется необходимость передвинуть нижнюю границу таблицы, к примеру, в начало.
На строку номер 20. А потом опять вниз передвинуть, на строку 150 и т.д.
В примере на Лист1 таблица, в которой данные зависят от размера таблицы на Лист2.
На Лист1 вверху кнопки.
Щелк мышкой по "5 вниз" - и нижняя граница таблицы на Лист2 на 5 строк вниз опустилась (независимо от того, какого размера была изначально).
Еще раз щёлкнуть - и нижняя граница таблицы уже в итоге на 10 строк ниже стала.
Щёлк мышкой по "1 вверх" - и нижняя граница таблицы на Лист2 поднялась вверх на одну строку и т.д.
Т.е. на фактически для такой задачи нужны четыре макроса.
Если я правильно понимаю, надо объявлять переменные вместо $A$1:$B$4 , а то еще и циклы использовать?
Или проще будет убрать так называемую умную таблицу и колдовать при помощи СМЕЩ, СУММЕСЛИ и т.п.? Автор - AwdBor
Дата добавления - 09.07.2015 в 08:28
= Мир MS Excel/Логика Resize() - Мир MS Excel
Войти через uID
Войти через uID
Добрый день объясните пожалуйста логику работы Resize() а то я что то не понимаю простите если что я тут новенький
Добрый день объясните пожалуйста логику работы Resize() а то я что то не понимаю простите если что я тут новенький Elhust
Причем еще вот что
1. Если Ресайз применяется к диапазону, то подсчет идет от левой верхней ячейки этого диапазона (если диапазон А3:С5, то считать будем от А3)
2. Аргументы должны быть положительны. Если нужно выделение налево и/или вверх, то делаем сначала Offset, перемещаясь так, чтобы попасть в ту ячейку, которая стала бы левой верхней, а потом оттуда уже Resize.
Примерно вот так: [vba]
[/vba] - выделение от текущей ячейки на 1 вниз и на 1 вправо (то есть просто выделяется сама текущая ячейка)
Для поиграться файлик простенький во вложении
Причем еще вот что
1. Если Ресайз применяется к диапазону, то подсчет идет от левой верхней ячейки этого диапазона (если диапазон А3:С5, то считать будем от А3)
2. Аргументы должны быть положительны. Если нужно выделение налево и/или вверх, то делаем сначала Offset, перемещаясь так, чтобы попасть в ту ячейку, которая стала бы левой верхней, а потом оттуда уже Resize.
Примерно вот так: [vba]
[/vba] - выделение от текущей ячейки на 1 вниз и на 1 вправо (то есть просто выделяется сама текущая ячейка)
Для поиграться файлик простенький во вложении _Boroda_
[/vba] - выделение от текущей ячейки на 1 вниз и на 1 вправо (то есть просто выделяется сама текущая ячейка)
Для поиграться файлик простенький во вложении Автор - _Boroda_
Дата добавления - 20.04.2017 в 10:19
_Boroda_, Вот это благодарность добрый вы человек =) как говориться больше знаешь больше можешь ) спасибо and_evg,
_Boroda_, Вот это благодарность добрый вы человек =) как говориться больше знаешь больше можешь ) спасибо and_evg, Elhust
Mayseven [Источник] 12.10.2016, 11:48
не надо умничать
Ответ: я так понимаю извинения не принимаются .
Вот что это такое? что за беспредел, челу дали - 1 реп за то, что с умничал, мне дали -15 реп за то что я хотел снизить репу за может и не весомую причину и плюс +20% замечания
Mayseven [Источник] 12.10.2016, 11:48
не надо умничать
Ответ: я так понимаю извинения не принимаются .
Вот что это такое? что за беспредел, челу дали - 1 реп за то, что с умничал, мне дали -15 реп за то что я хотел снизить репу за может и не весомую причину и плюс +20% замечания 85Muslim85
Вот что это такое? что за беспредел, челу дали - 1 реп за то, что с умничал, мне дали -15 реп за то что я хотел снизить репу за может и не весомую причину и плюс +20% замечания Автор - 85Muslim85
Дата добавления - 21.04.2017 в 07:39
Здравствуйте.
С помощью макрорекордера записал код сжатия контейнера умной таблицы до одной ячейки
[vba]
[/vba]
Подскажите каким образом (не ссылаясь на фиксированный диапазон) можно растянуть таблицу дабы она захватила границы конечных данных?
[vba]
Здравствуйте.
С помощью макрорекордера записал код сжатия контейнера умной таблицы до одной ячейки
[vba]
[/vba]
Подскажите каким образом (не ссылаясь на фиксированный диапазон) можно растянуть таблицу дабы она захватила границы конечных данных?
[vba]
[/vba]
Подскажите каким образом (не ссылаясь на фиксированный диапазон) можно растянуть таблицу дабы она захватила границы конечных данных?
[vba]
[/vba]это последняя строка столбца A,
это все, что я могу сказать без файла примера. Автор - Nic70y
Дата добавления - 27.02.2019 в 21:16
Sub Макрос2()
u_1 = Cells(Rows.Count, "b").End(xlUp).Row
u_2 = Cells(Rows.Count, "c").End(xlUp).Row
u_3 = Cells(Rows.Count, "d").End(xlUp).Row
u_4 = Application.Max(u_1, u_2, u_3) + 1
ActiveSheet.ListObjects("Таблица1").Resize Range("a1:a" & u_4)
End Sub
[p.s.]извиняюсь за апдэйты, глюки каие-то с файлом были[/p.s.]
Sub Макрос2()
u_1 = Cells(Rows.Count, "b").End(xlUp).Row
u_2 = Cells(Rows.Count, "c").End(xlUp).Row
u_3 = Cells(Rows.Count, "d").End(xlUp).Row
u_4 = Application.Max(u_1, u_2, u_3) + 1
ActiveSheet.ListObjects("Таблица1").Resize Range("a1:a" & u_4)
End Sub
[p.s.]извиняюсь за апдэйты, глюки каие-то с файлом были[/p.s.] Nic70y
Sub Макрос2()
u_1 = Cells(Rows.Count, "b").End(xlUp).Row
u_2 = Cells(Rows.Count, "c").End(xlUp).Row
u_3 = Cells(Rows.Count, "d").End(xlUp).Row
u_4 = Application.Max(u_1, u_2, u_3) + 1
ActiveSheet.ListObjects("Таблица1").Resize Range("a1:a" & u_4)
End Sub
[p.s.]извиняюсь за апдэйты, глюки каие-то с файлом были[/p.s.] Автор - Nic70y
Дата добавления - 27.02.2019 в 22:21
Nic70y, По идеи необходимо захватить весь диапазон с данными, через макрорекордер это должно быть так, как показано в следующем примере.
Но захват в моем примере происходит путем фиксированного диапазона "$A$1:$D$10" прописанного в коде Макроса2.
Вопрос – как произвести захват данных не ссылаясь на фиксированный диапазон?
Ну типа захват в области конечной заполненной строки и конечного столбца (заголовка).
Nic70y, По идеи необходимо захватить весь диапазон с данными, через макрорекордер это должно быть так, как показано в следующем примере.
Но захват в моем примере происходит путем фиксированного диапазона "$A$1:$D$10" прописанного в коде Макроса2.
Вопрос – как произвести захват данных не ссылаясь на фиксированный диапазон?
Ну типа захват в области конечной заполненной строки и конечного столбца (заголовка). Сергей13
Но захват в моем примере происходит путем фиксированного диапазона "$A$1:$D$10" прописанного в коде Макроса2.
Вопрос – как произвести захват данных не ссылаясь на фиксированный диапазон?
Ну типа захват в области конечной заполненной строки и конечного столбца (заголовка). Автор - Сергей13
Дата добавления - 27.02.2019 в 22:43
Sub Макрос2()
u_1 = Cells(1, Columns.Count).End(xlToLeft).Column
u_3 = Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 To u_1
u_2 = Cells(Rows.Count, i).End(xlUp).Row
If u_2 > Cells(Rows.Count, i - 1).End(xlUp).Row Then u_3 = u_2
Next
ActiveSheet.ListObjects("Таблица1").Resize Range(Cells(1, 1), Cells(u_3 + 1, u_1))
End Sub
Sub Макрос2()
u_1 = Cells(1, Columns.Count).End(xlToLeft).Column
u_3 = Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 To u_1
u_2 = Cells(Rows.Count, i).End(xlUp).Row
If u_2 > Cells(Rows.Count, i - 1).End(xlUp).Row Then u_3 = u_2
Next
ActiveSheet.ListObjects("Таблица1").Resize Range(Cells(1, 1), Cells(u_3 + 1, u_1))
End Sub
Sub Макрос2()
u_1 = Cells(1, Columns.Count).End(xlToLeft).Column
u_3 = Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 To u_1
u_2 = Cells(Rows.Count, i).End(xlUp).Row
If u_2 > Cells(Rows.Count, i - 1).End(xlUp).Row Then u_3 = u_2
Next
ActiveSheet.ListObjects("Таблица1").Resize Range(Cells(1, 1), Cells(u_3 + 1, u_1))
End Sub
With [Таблица1].ListObject
.Resize .Range.Resize(2, 1)
End With
With [Таблица1].ListObject.Range.CurrentRegion
.ListObject.Resize .Resize(.Rows.Count - IsEmpty(.Cells(2, 1)))
End With
With [Таблица1].ListObject
.Resize .Range.Resize(2, 1)
End With
With [Таблица1].ListObject.Range.CurrentRegion
.ListObject.Resize .Resize(.Rows.Count - IsEmpty(.Cells(2, 1)))
End With
With [Таблица1].ListObject
.Resize .Range.Resize(2, 1)
End With
With [Таблица1].ListObject.Range.CurrentRegion
.ListObject.Resize .Resize(.Rows.Count - IsEmpty(.Cells(2, 1)))
End With
Nic70y, Смысл всего этого, это независимая сортировка столбцов.
Для этого нужно после вводы данных под любым столбцом, динамической таблицы, произвести последовательную сортировку столбцов, отсюда следует что перед сортировкой необходимо временно убрать таблицу, а после сортировки захватить все данные.
Все это в одном коде.
Ваш код как бы рабочий, но захват данных, в зависимости от того где были введены данные, происходит не корректно, то есть не полный захват данных.
Для полного понимания создал файл из представленных кодов (Вашего и krosav4ig и кода сортировки). Для теста введите данные в ячейку 9I и выполните код кнопкой, захват будет не полный.
Nic70y, Смысл всего этого, это независимая сортировка столбцов.
Для этого нужно после вводы данных под любым столбцом, динамической таблицы, произвести последовательную сортировку столбцов, отсюда следует что перед сортировкой необходимо временно убрать таблицу, а после сортировки захватить все данные.
Все это в одном коде.
Ваш код как бы рабочий, но захват данных, в зависимости от того где были введены данные, происходит не корректно, то есть не полный захват данных.
Для полного понимания создал файл из представленных кодов (Вашего и krosav4ig и кода сортировки). Для теста введите данные в ячейку 9I и выполните код кнопкой, захват будет не полный. Сергей13
krosav4ig, Для работы Вашего кода необходимо минимум две заполненные строки, иначе выдает ошибку, в последствии код работает, но независимо от количества заполненных строк не захватывает две последние строки с данными.
krosav4ig, Для работы Вашего кода необходимо минимум две заполненные строки, иначе выдает ошибку, в последствии код работает, но независимо от количества заполненных строк не захватывает две последние строки с данными. Сергей13
Здравствуйте. Срочно нужна помощь. Я не силен в VBA, а мне необходимо сделать программный код. Есть две умные таблицы на разных листах. Необходимо чтобы при добавления данных в таблицу 1, в таблице 2 автоматически увеличивался диапазон таблицы и подставлялись данные. Файл прилагаю.
Извиняюсь, если такая тема уже есть.
За ранее благодарю за понимание и помощь.
Из умной таблицы в текстбокс
Что я делаю не так Me.CBHumans.List = Array(EmployeesListObj.Range(2))
(excel_2010_VBA) Сортировка умной таблицы
Здравствуйте. Проблема заключается в том, что если указать конкретное имя таблицы, то сортировка.
Поместить в Комбобокс отфильтрованную колонку умной таблицы
Ребят, как в Комбобокс поместить отфильтрованную колонку умной таблицы. от а до я Private.
Здравствуйте. Условие такое - при заполнении новой строки в Таблице1, в Таблице2 автоматически должна добавиться новая строка и заполнится формулами которые я потом пропишу.
За ранее благодарю.
Доброго времени суток Костя. В таблице 1 можно по вводу номера по порядку сделать.
Сможешь помочь. Срочно надо.
Chelgash, а попробуйте вот такой макрос, его надо поставить в модуль первого листа. Добавляется строка в таблицу второго листа, если вы что-то введете в столбец А таблицы первого листа.
Burk, Добровго времени суток. Поставил Ваш код в модуль первого листа и при нажатии мышкой на любую ячейку, выдает ошибку. Complite Error: Variable not Defined в строке - L = LB.Range.Rows.Count
Добавлено через 7 минут
LB - это, как я понимаю, глобальная переменная объекта листа т.е. умной таблицы (Таблица 1)
а RG - Что такое, не объявленная переменная.
Chelgash, Chelgash, кое-что переделывал без проверки, в 3 строке поставил Static RG as Range, несколько раз запускал, всё нормально и добавляется строка во вторую таблицу. Напишите, что у вас будет, только после ввода номера в первую таблицу надо выйти из ячейки с номером мышкой или энтером.
Добавлено через 6 минут
Chelgash, Rg объявлена как Static, только надо добавить тип, как я написал выше. LB не глобальная, а локальная в коде 1 листа. Она ведь не объявлена как Public. Если опять будут проблемы, пришлите снова файл с макросами.
Читайте также: