Перейти к содержимому
Наше приложение «Дешевые авиабилеты» в AppGallery >>

VBA Excel. Создание таблицы (умной, обычной)

    Создание таблицы с помощью кода VBA Excel. Создание умной и обычной пользовательской таблицы. Указание стиля умной таблицы, добавление строки итогов. Примеры.

    Создание и удаление умной таблицы

    Создание умной таблицы

    Создается умная таблица Excel с помощью следующего кода:

    В данном примере:

    ActiveSheet — лист, на котором создается таблица, может быть любой лист рабочей книги Excel.

    Range(«$A$1:$L$15») — диапазон, который преобразуется в таблицу. Можно использовать и такую форму: Range(Cells(1, 1), Cells(15, 12)), где индексы строк и столбцов можно заменить переменными.

    xlNo — указывает, что первая строка выбранного диапазона не содержит заголовки столбцов (граф) будущей таблицы, и их необходимо добавить. В этом случае будет добавлена дополнительная строка с наименованиями столбцов по умолчанию: Столбец1, Столбец2, Столбец3 и т.д., которые в дальнейшем можно переименовать (количество строк в таблице, включая строку заголовков, получится на одну больше, чем в указанном диапазоне). Если в диапазоне уже содержатся заголовки столбцов будущей таблицы, то следует указать вместо xlNo значение xlYes. В этом случае первая строка указанного диапазона будет преобразована в строку заголовков, а если она будет не заполнена, то добавятся названия столбцов по умолчанию: Столбец1, Столбец2, Столбец3 и т.д. (количество строк в таблице, включая строку заголовков, будет то же, что и в указанном диапазоне).

    МояТаблица1 — имя, присваиваемое создаваемой таблице. Имя должно быть без пробелов: при указании в коде VBA названия таблицы с пробелами, во время его выполнения Excel заменит пробелы знаками подчеркивания (по крайней мере, так происходит в Excel 2016).

    Таблица будет создана со стилем по умолчанию (TableStyleMedium2 в Excel 2016).

    Стиль умной таблицы

    Присвоение стиля таблице (изменение стиля) осуществляется с помощью свойства TableStyle объекта ListObjects:

    Свойство TableStyle может принимать следующие значения:

    • TableStyleLight (светлый) с индексом от 1 до 21 (в Excel 2016);
    • TableStyleMedium (средний) с индексом от 1 до 28 (в Excel 2016);
    • TableStyleDark (темный) с индексом от 1 до 11 (в Excel 2016).

    Например, TableStyleLight5, TableStyleMedium24, TableStyleDark8.

    Чтобы отменить стиль таблицы в коде VBA, необходимо свойству TableStyle присвоить пустую строку:

    Этому коду соответствует выбор в разделе «Конструктор» на ленте инструментов Excel самого первого значка стилей в разделе «Светлый».

    Добавление строки итогов

    Строка итогов умной таблицы добавляется следующим образом:

    Удаляется строка итогов умной таблицы так:

    Удаление умной таблицы

    Удалить умную таблицу очень просто:

    Создание пользовательской таблицы

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

    Для создания такой таблицы необходимо:

    Подробнее о создании пользовательской таблицы в Примере 2.

    Примеры создания таблиц

    Задание для примеров

    Набор данных для создания таблиц
    Набор данных для примеров создания таблиц

    Допустим, на лист Excel переданы данные для заполнения табличной части товарного чека со следующими условиями:

    • в табличной части 5 граф: № п/п, Наименование, Количество, Цена и Сумма;
    • сколько наименований добавил пользователь неизвестно.

    Нам необходимо:

    • определить количество строк;
    • добавить строку заголовков;
    • отобразить сетку (границы ячеек);
    • добавить строку итогов.

    Таблицу будем оформлять двумя способами: путем создания умной и пользовательской таблиц.

    Пример 1 — умная таблица

    Упаковываем набор данных из задания в умную таблицу:

    Результат выполнения кода Примера 1 получится такой:

    Умная таблица из заданного набора данных
    Умная таблица из заданного набора данных

    Пример 2 — «обычная» таблица

    Упаковываем набор данных из задания в пользовательскую таблицу:

    Результат выполнения кода Примера 2 получится такой:

    Пользовательская таблица из заданного набора данных
    Пользовательская таблица из заданного набора данных

    Если решите поэкспериментировать с моим кодом, добавьте любые данные в пять колонок на активном листе Excel, количество строк может быть любым, в пятой графе должны быть числа.


    О работе с умной таблицей (обращение к ячейкам, строкам и столбцам; добавление и удаление строк и столбцов) рассказано в статье VBA Excel. Работа с умной таблицей


    Содержание рубрики VBA Excel по тематическим разделам со ссылками на все статьи.

    4 комментария для “VBA Excel. Создание таблицы (умной, обычной)”

    1. Не совсем удобно изучать пример, когда в коде используется стиль ссылок R1C1, а таблица с A, B, C, D и т.д.
      1) что значит xlSrcRange ?
      2) что значит Range(Cells(1, 1),Cells(a, 5)) — почему нельзя сразу указать (1, 5)? в начале же вроде присвоили a = (1, 1), не совсем понятно зачем все это дублировать
      3) как здесь идет расчет сетки Range(Cells(1, 1), Cells(a + 1, 5)) ? и здесь Cells(a + 2, 4) ?
      4) что значит SUM(R[-» & a & «]C:R[-1]C)» обычно формула СУММ по иному показывает.

      Расчет сетки покажите на основании картинок, чтобы понятно было со ссылками типа А1:Е5 = это заголовки например и т.д.

      1. Евгений

        Привет, Рустем!

        1) xlSrcRange – это константа, которая указывает, что таблица будет создана в диапазоне ячеек, заданном значением следующего параметра (в первом примере это – Range(Cells(1, 1),Cells(a, 5))).

        2) Range(Cells(1, 1),Cells(a, 5)) – это прямоугольный диапазон, ограниченный верхней левой ячейкой Cells(1, 1) и правой нижней – Cells(a, 5). Строка a = Cells(1, 1).CurrentRegion.Rows.Count записывает в переменную a количество строк исходной таблицы без заголовков (Задание для примеров). Использование переменной a полезно, когда неизвестно количество строк в исходной таблице.

        3) Range(Cells(1, 1), Cells(a + 1, 5)) – это прямоугольный диапазон, включающий в себя строку заголовков. Cells(a + 2, 4) – это ячейка, в которую записано слово «Итого:». Границы добавляются к ячейке Cells(a + 2, 5).

        4) Строку .FormulaR1C1 = "=SUM(R[-" & a & "]C:R[-1]C)" можно заменить строкой .Formula = "=SUM(E2:E" & a + 1 & ")".

    2. Григорий

      Евгений, день добрый!

      А возможно сделать, чтобы макрос сам определял границы умной таблицы и не включал в неё пустые столбцы/строки?

      1. Евгений

        Добрый день, Григорий!

        Чтобы определить границы обычной таблицы для ее преобразования в умную, надо знать адрес хотя бы одной ячейки, входящей в обычную таблицу. Допустим, мы знаем, что в обычную таблицу входит ячейка Cells(4, 5), тогда мы можем определить диапазон таблицы и преобразовать его в умную таблицу:

        Свойство Range.CurrentRegion возвращает диапазон, окруженный пустыми строками и столбцами.

    Обсуждение закрыто.