После урока сможешь: строить диаграмму, создавать умную таблицу и собирать сводную таблицу из кода VBA — это последний шаг автоматизации: от ручной работы (главы 3-4) к коду, который делает всё сам.
Вспомни: типы диаграмм (4.2), умная таблица (3.6) и области сводной — строки/столбцы/значения/фильтры (4.1) уже разобраны руками. Здесь — та же логика, но управляемая кодом.
Диаграммы из кода: Shapes.AddChart2
Диаграмму создают методом Shapes.AddChart2, указывая тип: xlColumnClustered (гистограмма), xlPie (круговая), xlLine (график) — те же 5 типов из темы 4.2, только заданные кодом, а не мышью в меню «Вставка». Дальше настраивают заголовок, легенду, цвета через свойства объекта Chart.
Данные для диаграммы часто сначала считают в коде: например, сумму выручки — функцией листа Excel, вызванной прямо из VBA: Application.WorksheetFunction.Sum(Range("B2:B6")). Такой же приём встретится в задании после итога урока.
Dim cht As Chart
Set cht = Sheets("Лист1").Shapes.AddChart2(251, xlColumnClustered).Chart
' 251 = стиль оформления, xlColumnClustered = тип "гистограмма" (как в теме 4.2)
cht.SetSourceData Source:=Range("A1:B6") ' данные: заголовок + 5 товаров
cht.ChartTitle.Text = "Выручка по товарам" ' заголовок диаграммы
cht.HasLegend = True ' показать легенду
Таблицы из кода: ListObject
Умная таблица (тема 3.6) в VBA называется ListObject. Создают её кодом методом ListObjects.Add — например, из диапазона с готовыми заголовками: ListObjects.Add(xlSrcRange, Range("A1:B6"), , xlYes), где последний параметр отвечает за то, есть ли в диапазоне строка заголовков. В уже существующую таблицу добавляют строку: ListObject.ListRows.Add. Удаляют: ListObject.ListRows(n).Delete. Как и при ручном создании (Ctrl+T), таблица сама расширяется и сохраняет структурные ссылки, только теперь строки добавляет код.
Dim tbl As ListObject
Set tbl = Sheets("Лист1").ListObjects.Add(xlSrcRange, Range("A1:B6"), , xlYes)
' xlSrcRange - таблица строится из диапазона; xlYes - в A1:B6 есть строка заголовков
tbl.ListRows.Add ' новая строка снизу таблицы
tbl.ListRows(1).Range.Cells(1, 1).Value = "Груша" ' заполняем первую ячейку новой строки
Сводные таблицы из кода: PivotTableWizard
Сводную из кода создают через PivotTableWizard, а поля распределяют по 4 областям (тема 4.1) свойствами xlRowField (строки), xlColumnField (столбцы), xlDataField (значения), с функцией агрегирования xlSum/xlAverage — те же 4 области и 5 типов расчёта, что и вручную, просто заданные в коде.
Dim pt As PivotTable
Set pt = Sheets("Лист1").PivotTableWizard(TableDestination:=Range("E1"), TableName:="Свод1")
pt.PivotFields("Товар").Orientation = xlRowField ' товар - в строки
pt.PivotFields("Выручка").Orientation = xlDataField ' выручка - в значения
pt.PivotFields("Выручка").Function = xlSum ' функция агрегирования - сумма
РУКАМИ (главы 3-4) КОДОМ (VBA, тема 5.5) ---------------------------------------------------------------- Вставка -> Диаграмма Shapes.AddChart2 (тип xlPie/xlColumnClustered) Ctrl+T (умная таблица) ListObject, ListRows.Add Вставка -> Сводная таблица PivotTableWizard, xlRowField/xlDataField Один и тот же результат — разница только в том, кто нажимает кнопки: человек мышью или макрос кодом.
Проверь себя
Контрольный вопрос. Какой метод VBA создаёт диаграмму на листе?
Подсказка: Диаграмма технически — один из видов фигур (Shapes) на листе.
Проверить ответ →
Реальная проверка — на нашей платформе, бесплатно и без регистрации. Открыть задачу и ввести ответ →
Контрольный вопрос. Тип xlPie в Shapes.AddChart2 создаст диаграмму какого вида?
Подсказка: Pie по-английски — «пирог», то есть круг, разделённый на сектора.
Проверить ответ →
Реальная проверка — на нашей платформе, бесплатно и без регистрации. Открыть задачу и ввести ответ →
Контрольный вопрос. Как в VBA называется объект «умная таблица» (Ctrl+T)?
Подсказка: Историческое название из ранних версий Excel, сохранившееся в объектной модели VBA.
Проверить ответ →
Реальная проверка — на нашей платформе, бесплатно и без регистрации. Открыть задачу и ввести ответ →
Задание. Напиши строку кода сам. Умная таблица уже лежит в переменной tbl. Нужно добавить в неё одну новую строку снизу. Впиши строку целиком.
Подсказка: Сначала переменная таблицы, через точку — коллекция её строк, ещё через точку — метод добавления.
✅ Готово, если: ты ввёл(а) верный ответ, и онлайн-проверка его приняла.
Проверить ответ →
Реальная проверка — на нашей платформе, бесплатно и без регистрации. Открыть задачу и ввести ответ →
Контрольный вопрос. В PivotTableWizard свойство xlDataField отвечает за то же, что при ручной работе называется областью…
Подсказка: Data (данные) — это то, что вычисляется, а не категория группировки.
Проверить ответ →
Реальная проверка — на нашей платформе, бесплатно и без регистрации. Открыть задачу и ввести ответ →
Задание. Напиши строку кода сам. Сводная таблица лежит в переменной pt. Нужно поместить поле с названием Товар в область «Строки». Впиши строку целиком (обращение к полю по имени, его свойство Orientation и нужная константа).
Подсказка: Схема: переменная сводной, через точку PivotFields с именем поля в кавычках, через точку Orientation, знак равно и константа области — для строк это xlRowField.
✅ Готово, если: ты ввёл(а) верный ответ, и онлайн-проверка его приняла.
Проверить ответ →
Реальная проверка — на нашей платформе, бесплатно и без регистрации. Открыть задачу и ввести ответ →
Задание. Допиши пропущенное имя параметра — он указывает, КУДА (в какую ячейку) поместить создаваемую сводную таблицу. Впиши слово без двоеточия и знака равенства: PivotTableWizard(___:=Range(«E1″), TableName:=»Свод1»)
Подсказка: Это слово встречается в материале урока прямо рядом с примером PivotTableWizard — оно про место назначения сводной таблицы.
✅ Готово, если: ты ввёл(а) верный ответ, и онлайн-проверка его приняла.
Проверить ответ →
Реальная проверка — на нашей платформе, бесплатно и без регистрации. Открыть задачу и ввести ответ →
Задание. В сводной таблице через код указали xlDataField с функцией xlAverage по столбцу «Оценка». Оценки: 4, 5, 3. Какое число покажет сводная? Впиши число.
Подсказка: xlAverage — среднее арифметическое: сложи все значения и раздели на их количество.
✅ Готово, если: ты ввёл(а) верный ответ, и онлайн-проверка его приняла.
Проверить ответ →
Реальная проверка — на нашей платформе, бесплатно и без регистрации. Открыть задачу и ввести ответ →
Задание. Дан макрос:
For i = 1 To 5<br> Cells(i, 1).Value = 100 + i * 20<br>Next i<br>total = Application.WorksheetFunction.Sum(Range("A1:A5"))<br>Range("B1").Value = totalКакое значение окажется в ячейке B1? Впиши число.
Подсказка: Посчитай значение для каждой строки (100 + i*20 при i от 1 до 5), потом сложи все пять.
✅ Готово, если: ты ввёл(а) верный ответ, и онлайн-проверка его приняла.
Проверить ответ →
Реальная проверка — на нашей платформе, бесплатно и без регистрации. Открыть задачу и ввести ответ →
Контрольный вопрос. Отметь все верные утверждения про диаграммы/таблицы/сводные из VBA (не менее двух вариантов).
Выберите все верные варианты.
Подсказка: Два варианта либо занижают возможности VBA, либо путают функции агрегирования с типами диаграмм.
Проверить ответ →
Реальная проверка — на нашей платформе, бесплатно и без регистрации. Открыть задачу и ввести ответ →
Итог
Итог. Shapes.AddChart2 строит любой из знакомых типов диаграмм кодом. ListObject — объектное имя умной таблицы, ListRows.Add добавляет строки программно. PivotTableWizard распределяет поля по тем же 4 областям (xlRowField/xlColumnField/xlDataField/фильтры) и тем же функциям агрегирования (xlSum/xlAverage), что и ручная сводная. Вся глава 5 — один и тот же Excel, только управляемый кодом вместо мыши.
Мини-проект: собери отчёт целиком (практика)
Необязательное практическое задание — настоящий проект, не для автопроверки. Открой Excel, создай новый модуль (Insert → Module) и напиши один макрос, который сам: 1) заполняет диапазон A1:B6 таблицей «Товар / Выручка» для 5 товаров; 2) считает их сумму через
Application.WorksheetFunction.Sum; 3) строит гистограмму (Shapes.AddChart2, типxlColumnClustered) по выручке с заголовком «Выручка по товарам»; 4) сохраняет книгу как*.xlsm. Запусти макрос (F5 или Alt+F8) и убедись, что таблица, сумма и диаграмма появились сами — без единого клика мышью. Сохрани файл или сделай снимок экрана результата — это и есть доказательство, что макрос реально работает (не то же самое, что просто посчитать сумму в уме).
- Структура таблицы — в A1:B6 есть заголовки и ровно 5 строк товаров
- Сумма верна — результат Application.WorksheetFunction.Sum совпадает с ручным подсчётом по столбцу B
- Тип диаграммы — построена именно гистограмма (xlColumnClustered), с заголовком «Выручка по товарам»
- Файл сохранён как .xlsm — не .xlsx, иначе макрос при следующем открытии пропадёт
