Урок 60. Макрос для таблицы

Приходилось ли вам выполнять при форматировании документа несколько раз повторять одни и те же команды? Предположим, в документе 50 таблиц. И каждую надо привести в порядок. Повторяющиеся заголовки, выравнивание назначить, да мало ли чего ещё сделать. И вот раз за разом повторяются одни те же команды. Так что знакомимся с понятием МАКРОС В ТАБЛИЦЕ.

В офисных программах есть замечательная возможность: объединить несколько команд в одну макрокоманду. Макрокоманда – это последовательность команд, которые будут работать автоматически при запуске макроса.

Вот определение, которое я взяла с любимого ресурса https://dic.academic.ru/dic.nsf/ruwiki/15081:

Во «офисных» продуктах (OpenOffice.org, Microsoft Office и др.), в графических программах (например, CorelDRAW) при обработке макроса автоматически выполняется заданная для каждого макроса последовательность действий — нажатия на клавиши, выбор пунктов меню и т. д.

Я приложила к уроку документ с несколькими таблицами (скачать файл тут). Я удалила текст документа (всё-таки авторское право и всё такое…):

Макрос для таблицы

По окончании урока вы сможете:

  1. Составить алгоритм форматирования таблицы
  2. Настроить ленту «Разработчик»
  3. Записать макрос форматирования таблицы
  4. Проверить макрос в действии
  5. Добавить кнопку «Макрос» на панель быстрого доступа

Начало создания макроса

Макрос для отчетов формата Word

Для того, чтобы создать или просмотреть макрос для отчета формата Word необходимо:

Если макроса там не будет, его можно создать самостоятельно, введя в данном окне строки начала и конца макроса, а между ними — сам текст макроса:

Sub ПослеВыполненияОтчета(ob As Variant, app As Variant)

Внимание! В Word 2007 закладка «Разработчик» по умолчанию скрыта. Чтобы вывести ее на показ необходимо:

нажать кнопку Office в верхнем левом углу, нажать кнопку "Параметры Word";

Макрос для отчетов формата Excel

Для того, чтобы создать или просмотреть макрос для отчета формата Excel, необходимо:

Внимание! В Excel 2007 закладка «Разработчик» по умолчанию скрыта. Чтобы вывести ее на показ необходимо:

нажать кнопку Office в верхнем левом углу, нажать кнопку "Параметры Excel";

Обычно, когда мы обновляем исходные данные сводной таблицы, сводная таблица не обновляется, пока мы не нажмем Анализировать > обновление вручную. Это будет довольно скучно, если сводная таблица останется на другом листе, и вы можете забыть ее обновить. В этой статье будет представлен VBA для автоматического обновления сводной таблицы в Excel.

Автоматическое обновление сводной таблицы с помощью VBA

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

1. На панели вкладок листа щелкните правой кнопкой мыши вкладку листа, содержащую исходные данные, и выберите Просмотреть код в контекстном меню. Смотрите скриншот:

2. В открывшемся окне Microsoft Visual Basic для приложений вставьте код.

VBA: автоматическое обновление указанной сводной таблицы

Ноты:
(1) В коде рабочих листов («имя листа«). Сводные таблицы («Имя сводной таблицы«) .PivotCache.Refresh, замените имя листа на имя листа, содержащего указанную сводную таблицу, и замените Имя сводной таблицы с именем указанной сводной таблицы.
(2) Выберите любую ячейку в указанной сводной таблице, вы получите ее имя в Анализировать вкладку (или Опции вкладка). Смотрите скриншот:

3. Сохраните код.

С этого момента, когда вы изменяете данные в исходных данных, указанная сводная таблица будет обновляться автоматически.

Легко объединяйте несколько листов / книг / файлов CSV в один рабочий лист / книгу

Объединение десятков листов из разных книг в один может оказаться утомительным занятием. Но с Kutools for Excel’s Объединить (рабочие листы и рабочие тетради) утилиту, вы можете сделать это всего за несколько кликов! Полнофункциональная бесплатная 30-дневная пробная версия!
объявление объединить листы книги 1

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

Как создать плоскую сводную таблицу

Известно, что при формировании сводной таблицы по умолчанию, Excel строит ее в древововидном виде. Это неплохо, когда нужно быстро получить обобщенные данные, но когда требуется в дальнейшем продолжить обработку таблицы, такой вид не годится. Нам на помощь придет плоская сводная таблица, которую можно сделать в несколько простых приемов.

Рассмотрим в качестве учебного примера ситуацию с простым отчетом по продажам. Есть несколько записей о выручке, которую получили наши агенты в разных городах в разное время. Нужно обобщить информацию по месяцам и фамилиям для последующего расчета премий.

Как создать плоскую сводную таблицу

1. Создаем сводную таблицу

Подробно останавливаться на этом пункте не буду, так как у нас на сайте есть отличная статья на эту тему: «Как создать сводную таблицу в Excel». Если Вы еще не знаете как это сделать, рекомендую прочитать, иначе двигаемся дальше.

Как создать плоскую сводную таблицу

2. Изменяем макет сводной таблицы на табличный

Для этого кликните на любую ячейку сводной таблицы, вверху справа должен появиться блок меню «Работа со сводными таблицами«. В нем выберите меню «Конструктор«, далее подменю «Макет отчета» и там «Показать в табличной форме«.

Как создать плоскую сводную таблицу

Как создать плоскую сводную таблицу

Выполнив эти действия, мы получаем сводную таблицу уже не в древовидном виде, а в форме таблицы. Однако для полноценной работы, нам мешают итоговые строки и отсутствие данных в части ячеек. Сейчас мы это исправим.

Как создать плоскую сводную таблицу

3. Удаляем итоговые строки

Для удаления итоговых строк кликните правой кнопкой мыши на любую итоговую ячейку, к примеру «Январь 2017 Итог». В появившемся контекстном меню выберите «Параметры поля» и там в блоке «Итоги» выберите «Нет«.

Как создать плоскую сводную таблицу

Как создать плоскую сводную таблицу

Итак, итоги удалены. Осталось добавить в пустые строки дублирующие данные и плоская сводная таблица будет готова!

4. Заполняем пропущенные данные

Если Вы обратите внимание на сводную таблицу в самом начале урока, то заметите что дата проставлена не во всех ячейках, сейчас мы это исправим. Для этого снова вызовите контекстное меню, нажав правой кнопкой мыши на любую из дат и снова выберите «Параметры поля. «, там перейдите в раздел «Разметка и печать» и установите галочку напротив пункта «Повторять подписи элементов«.

Понимание пользовательской формы

Разверните окно UserForm.xlsx — UserForm1.

Вы находитесь в режиме дизайна сейчас. Вы можете вставить элементы управления в пользовательскую форму и написать код для соответствующих действий. Элементы управления доступны в панели инструментов. Свойства UserForm находятся в окне свойств. UserForm1 (подпись UserForm) указывается в разделе «Формы» в проводнике проектов.

Понимание UserForm

  • Измените заголовок UserForm на Project Report — Daily в окне свойств.
  • Измените имя пользовательской формы на ProjectReport.

Отчет о проекте

Изменения отражаются в пользовательской форме, свойствах и проводнике проекта.

Умные Таблицы Excel – секреты эффективной работы

В MS Excel есть много потрясающих инструментов, о которых большинство пользователей не подозревают или сильно недооценивает. К таковым относятся Таблицы Excel. Вы скажете, что весь Excel – это электронная таблица? Нет. Рабочая область листа – это только множество ячеек. Некоторые из них заполнены, некоторые пустые, но по своей сути и функциональности все они одинаковы.

Таблица Excel – совсем другое. Это не просто диапазон данных, а цельный объект, у которого есть свое название, внутренняя структура, свойства и множество преимуществ по сравнению с обычным диапазоном ячеек. Также встречается под названием «умные таблицы».

Как создать Таблицу в Excel

  • В наличии имеется обычный диапазон данных о продажах.
  • Для преобразования диапазона в Таблицу выделите любую ячейку и затем Вставка → Таблицы → Таблица
  • Есть горячая клавиша Ctrl+T.
  • Появится маленькое диалоговое окно, где можно поправить диапазон и указать, что в первой строке находятся заголовки столбцов.

Как правило, ничего не меняем. После нажатия Ок исходный диапазон превратится в Таблицу Excel.

Перед тем, как перейти к свойствам Таблицы, посмотрим вначале, как ее видит сам Excel. Многое сразу прояснится.

Структура и ссылки на Таблицу Excel

Каждая Таблица имеет свое название. Это видно во вкладке Конструктор, которая появляется при выделении любой ячейки Таблицы. По умолчанию оно будет «Таблица1», «Таблица2» и т.д.

Если в вашей книге Excel планируется несколько Таблиц, то имеет смысл придать им более говорящие названия. В дальнейшем это облегчит их использование (например, при работе в Power Pivot или Power Query). Я изменю название на «Отчет». Таблица «Отчет» видна в диспетчере имен Формулы → Определенные Имена → Диспетчер имен.

Но самое интересное заключается в том, что Эксель видит не только целую Таблицу, но и ее отдельные части: столбцы, заголовки, итоги и др. Ссылки при этом выглядят следующим образом.

=Отчет[#Все] – на всю Таблицу=Отчет[#Данные] – только на данные (без строки заголовка)=Отчет[#Заголовки] – только на первую строку заголовков=Отчет[#Итоги] – на итоги=Отчет[@] – на всю текущую строку (где вводится формула)=Отчет[Продажи] – на весь столбец «Продажи»=Отчет[@Продажи] – на ячейку из текущей строки столбца «Продажи»

Для написания ссылок совсем не обязательно запоминать все эти конструкции. При наборе формулы вручную все они видны в подсказках после выбора Таблицы и открытии квадратной скобки (в английской раскладке).

Выбираем нужное клавишей Tab. Не забываем закрыть все скобки, в том числе квадратную.

  1. Если в какой-то ячейке написать формулу для суммирования по всему столбцу «Продажи»
  2. =СУММ(D2:D8)
  3. то она автоматически переделается в
  4. =Отчет[Продажи]

Т.е. ссылка ведет не на конкретный диапазон, а на весь указанный столбец.

Читайте также:  Пример построения финансовой модели предприятия в Excel

Это значит, что диаграмма или сводная таблица, где в качестве источника указана Таблица Excel, автоматически будет подтягивать новые записи.

Свойства Таблиц Excel

1. Каждая Таблица имеет заголовки, которые обычно берутся из первой строки исходного диапазона.

2. Если Таблица большая, то при прокрутке вниз названия столбцов Таблицы заменяют названия столбцов листа.

Очень удобно, не нужно специально закреплять области.

3. В таблицу по умолчанию добавляется автофильтр, который можно отключить в настройках. Об этом чуть ниже.

4. Новые значения, записанные в первой пустой строке снизу, автоматически включаются в Таблицу Excel, поэтому они сразу попадают в формулу (или диаграмму), которая ссылается на некоторый столбец Таблицы.

Новые ячейки также форматируются под стиль таблицы, и заполняются формулами, если они есть в каком-то столбце. Короче, для продления Таблицы достаточно внести только значения. Форматы, формулы, ссылки – все добавится само.

5. Новые столбцы также автоматически включатся в Таблицу.

6. При внесении формулы в одну ячейку, она сразу копируется на весь столбец. Не нужно вручную протягивать.

Настройки Таблицы

  • В контекстной вкладке Конструктор находятся дополнительные инструменты анализа и настроек.
  • С помощью галочек в группе Параметры стилей таблиц
  • можно внести следующие изменения.
  • — Удалить или добавить строку заголовков
  • — Добавить или удалить строку с итогами
  • — Сделать формат строк чередующимися
  • — Выделить жирным первый столбец
  • — Выделить жирным последний столбец
  • — Сделать чередующуюся заливку строк
  • — Убрать автофильтр, установленный по умолчанию
  • В видеоуроке ниже показано, как это работает в действии.

В группе Стили таблиц можно выбрать другой формат. По умолчанию он такой как на картинках выше, но это легко изменить, если надо.

  1. В группе Инструменты можно создать сводную таблицу, удалить дубликаты, а также преобразовать в обычный диапазон.
  2. Однако самое интересное – это создание срезов.
  3. Срез – это фильтр, вынесенный в отдельный графический элемент. Нажимаем на кнопку Вставить срез, выбираем столбец (столбцы), по которому будем фильтровать,

и срез готов. В нем показаны все уникальные значения выбранного столбца.

  • Для фильтрации Таблицы следует выбрать интересующую категорию.
  • Если нужно выбрать несколько категорий, то удерживаем Ctrl или предварительно нажимаем кнопку в верхнем правом углу, слева от снятия фильтра.
  • Попробуйте сами, как здорово фильтровать срезами (кликается мышью).

Для настройки самого среза на ленте также появляется контекстная вкладка Параметры. В ней можно изменить стиль, размеры кнопок, количество колонок и т.д. Там все понятно.

Ограничения Таблиц Excel

Несмотря на неоспоримые преимущества и колоссальные возможности, у Таблицы Excel есть недостатки.

1. Не работают представления. Это команда, которая запоминает некоторые настройки листа (фильтр, свернутые строки/столбцы и некоторые другие).

2. Текущую книгу нельзя выложить для совместного использования.

3. Невозможно вставить промежуточные итоги.

4. Не работают формулы массивов.

5. Нельзя объединять ячейки. Правда, и в обычном диапазоне этого делать не следует.

О целесообразности и возможности сводных таблиц

Оптимально формировать сводный отчет на основе исходной таблицы, если она отвечает следующим параметрам:

  • содержится несколько сотен строк;
  • пользователю нужно представлять одни и те же данные в разных разрезах, выбирать информацию по заданному условию, группировать, а встроенный фильтр и группировка справляются плохо.

Требования к исходной таблице:

    у каждого столбца есть заголовок (первый вариант – неправильный; второй – правильный);

Нерациональная организация информации:

Нельзя будет вывести итоги, например, только по городу.

Лучше значения ввести следующим таким образом.

Удаление

Чтобы избавиться от созданной конструкции, сведенной из нескольких таблиц, достаточно удалить лист, на котором она располагается. Для этого по нему следует нажать правой кнопочкой мышки и выбрать строку «Удалить».

Делаем сводную таблица в Excel - пошаговая инструкция

Посредством описанной инструкции может быть создана сводная таблица из двух в excel. Предоставленной информации и видео достаточно, чтобы освоить начальные навыки работы с подобными конструкциями.

Поделись с друзьями!

Макрос для создания сводной таблицы в Excel

​Quick Access Toolbar​ «Файл Data.xlsx не​ Col столбец A​ также использование цикла​ и выходим из​ принципиально не отличается​Кнопка​ Имя должно начинаться​ например, Вы делаете​- используются в​ данных обратно, вывод​ непосредственно ссылаться к​ TableName:=»ТаблицаМ»​ сводных таблиц использовать​Часть 3: Массивы​ шрифт станет синим:​ код:​(Панель быстрого доступа).​ найден! » &​ рабочего листа Лист​Do Until​ цикла For iRowNumber​ от создания макроса​Изменить​

Создание сводных таблиц макросом

​ с буквы и​ для создания еженедельного​ большинстве случаев, когда​ суммы прописью и​ таблице через имя,​With ActiveSheet​ макрос, получим неограниченные​Часть 4: Процедуры Function​Вы также можете использовать​Sub Macro1 ()​В поле​ _ «Пожалуйста добавьте​

​ 2 Set Col​. В данном примере​ = i Exit​ в обычном программном​(Edit)​ не содержать пробелов​ отчета для руководства​ речь идет о​ т.д.​

Тестовая база для примера.

​ вместо того чтобы​.Name = «Анализ»​ возможности.​ и Sub​ конструкцию​ For n =​Choose commands from​ рабочую книгу в​ = Sheets(«Лист2»).Columns(«A») i​

​ мы не будет​ For End If​ модуле. Разница только​открывает редактор Visual​ и знаков препинания.​ компании. Поэтому мы​ макросах. Для создания​Макрос​ каждый раз указывать​.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)​

​Источник информации для сводных​Часть 5: Условные операторы​Case​ 1 To 5​(Выбрать команды из)​ папку C:\Documents and​ = 1 ‘При​ совершать какие-либо действия​ Next i ‘Сообщаем​ в том, что​ Basic (см. выше)​Сочетание клавиш​ переходим ко второму​ такого модуля выберите​- это запрограммированная​

​ место и диапазон,​End With​ таблиц всегда один​Часть 6: Циклы​, чтобы связать выполнение​ Cells(n, 1) =​ выберите​ Settings и нажмите​ помощи цикла считываем​ с массивом, хотя​ пользователю во всплывающем​ макрос выполняет последовательность​ и позволяет просмотреть​- будет потом​ способу создания макросов,​ в меню​ последовательность действий (программа,​ где будет находиться​With ActiveSheet.PivotTables(«ТаблицаМ»)​ и тот же​Часть 7: Операторы и​ действия с появлением​ n Next n​All Commands​ OK» Resume End​ значения ячеек столбца​ в реальной практике​ окне найдена ли​ действий с объектами​

​ и отредактировать текст​ использоваться для быстрого​ а именно. ​Insert — Module​

Код-Visual Basic.

​ процедура), записанная на​ сводная таблица.​.SmallGrid = True​ – база данных.​ встроенные функции​ определённого значения в​ End Sub​(Все команды).​

​ Sub​
​ Col до тех​ программирования после того,​
​ искомая строка ‘Если​
​ книги (ячейками, формулами​
​ макроса на VBA.​
​ запуска макроса. Если​
​Макрорекордер​
​. В появившееся окно​
​ языке программирования Visual​
​Далее с помощью инструкции​
​.PivotFields(«Оборот»).Orientation = xlDataField​
​ Набор данных образующих​
​Часть 8: Объектная модель​
​ ячейке. В следующем​
Исходный код макроса.

​Сохраните файл. Чтобы выполнить​Найдите команду​Урок подготовлен для Вас​

Сгенерирована сводная таблица.

​ пор, ‘пока не​ как данные записаны​ заданная строка найдена,​

​ и значениями, листами,​

Описание VBA-кода макроса для создания сводной таблицы Excel

​Чтобы не запоминать сочетание​ забудете сочетание или​- это небольшая​ нового пустого модуля​ Basic for Applications​ конструктора With собираем​.PivotFields(«Год»).Orientation = xlPageField​ базу для обработки​ Excel​ примере отображается сообщение,​ макрос, перейдите​Option Button​ командой сайта office-guru.ru​ встретится пустая ячейка​ в массив, над​ указываем в какой​ диаграммами и т.д.),​ клавиш для запуска​ вообще его не​ программа, встроенная в​ можно вводить команды​ (VBA). Мы можем​ блок инструкций методов,​.PivotFields(«Месяц»).Orientation = xlRowField​

​ в Excel может​Часть 9: События в​ содержание которого зависит​View​(Кнопка), нам нужна​Источник: http://www.excelfunctions.net/Excel-Macro-Example.html​ Do Until IsEmpty(Col.Cells(i))​ ними такие действия,​ ячейке найдено совпадение​ а пользовательская функция​ макроса, лучше создать​ введете, то макрос​ Excel, которая переводит​ на VBA, набирая​ запускать макрос сколько​ свойств и параметров​.PivotFields(«Магазины»).Orientation = xlColumnField​ быть не только​ Excel​ от указанного возраста​>​ та, что относится​Перевел: Антон Андронов​ ‘Выполняем арифметические операции​ как правило, совершаются.​

​ If iRowNumber =​ — только с​ кнопку и назначить​ можно будет запустить​ любое действие пользователя​ их с клавиатуры​ угодно раз, заставляя​ структуры. Все дальнейшие​End With​ на листах рабочей​Часть 10: Ошибки VBA​ человека.​Macros​ к разделу​Автор: Антон Андронов​ над значением текущей​’Процедура Sub сохраняет​ 0 Then MsgBox​ теми значениями, которые​ ей нужный макрос.​ через меню​ на язык программирования​ или копируя их​ Excel выполнять последовательность​ опции будут реализованы​End Sub​ книги, а также​Примеры по VBA​Sub MacroName() Dim​>​Form Control​Создаём макрос при помощи​ ячейки dVal =​ значения ячеек столбца​ «Строка » &​ мы передадим ей​ Кнопка может быть​Сервис — Макрос -​ VBA и записывает​

​ из другого модуля,​ любых нужных нам​ внутри свойства ActiveSheet.​Теперь достаточно лишь запустить​ и во внешних​Более подробное описание по​ CellValue As Integer​View Macros​(Элементы управления формы).​ команды Запись макроса​ Col.Cells(i).Value * 3​

​ A активного листа​ sFindText & «​ как аргументы (исходные​ нескольких типов:​ Макросы — Выполнить​ получившуюся команду в​ с этого сайта​ действий, которые нам​ То есть будут​ макрос выбрав инструмент:​ источниках.​ Excel VBA можно​ CellValue = ActiveCell.Value​(Вид > Макросы​ Выделите ее и​

​Цикл FOR​ — 1 ‘Следующая​ в массиве Sub​ не найдена» Else​ данные для расчета).​Кнопка на панели инструментов​

​ программный модуль. Если​ или еще откуда​ не хочется выполнять​ касаться листа, в​ «РАЗРАБОТЧИК»-«Код»-«Макросы»-«CreateTableM»-«Выполнить»:​Для описания способа создания​ найти на сайте​ Select Case CellValue​ > Макросы), выберите​ нажмите​Конструкция IF​

​ команда записывает полученный​ GetCellValues() Dim iRow​ MsgBox «Строка «​Чтобы создать пользовательскую функцию​

​ в Excel 2003​ Macros — Run)​ мы включим макрорекордер​ нибудь:​ вручную.​ котором будет создана​В результате будет автоматически​ сводных таблиц средствами​ Microsoft Office.​ Case 60 To​ из списка название​Add​Конструкция CASE​ результат в столбец​

Поля сводной таблицы

Обратим особое внимание на один из шагов составления сводной таблички – выбор полей и распределение по областям. Для того чтобы понять метод работы с окном «Поля таблицы», рассмотрим его элементы по отдельности.

Читайте также:  Примеры функции ИНДЕКС для создания массивов значений в Excel

В более новых версиях Microsoft Excel окно выглядит немного иначе, но функции сохраняются.

  1. Форматы окна «Поля таблицы». В меню можно выбрать, какие разделы будут показаны на экране.
  2. Список полей, которые добавляются в отчет.
  3. В поле «Фильтры» нужно переместить показатели для дальнейшей фильтрации данных.
  4. Поле «Столбцы» должно содержать указания о том, какие данные вывести в столбцах.
  5. Назначение области «Строки» почти то же, что и у области «Столбцы» – указываем данные, которые будут выведены в строках.
  6. В области «Значения» должны оказаться поля с числовым форматом для вычислений.

Если в источниках много данных, можно воспользоваться функцией «Отложить обновление макета», чтобы предотвратить ошибки в работе программы.

Что значит макрос в Excel 2016 и для чего он предназначен?

Процесс записи макросов не отличается от ранних версий; еще в одном простом примере расскажу о другом способе вызова макрокоманд. Создание макросов позволит Вам познакомится с навыками программирования и кто знает, может это интересное дело Вас увлечёт.

Представим, что у нас есть довольно громоздкая таблица, которую каждый раз приходится очищать от данных предыдущего отчета. Задача: сделать в документе кнопку, при нажатии на которую автоматически очищаются нужные ячейки. Начнем.

Для начала не забудем сделать настройки MSoffice 2016. Так же идем в «Параметры», но далее идем в «Настроить ленту»

После «Разработчик» станет видимым на «Ленте». Переходим туда. Наша личная книга макросов по прежнему на месте, откроем ее:

Запустим наш предыдущий макрос, любуемся, как программа сама создает и заполняет таблицу. В нашем примере есть две графы «Количество» и «Цена», содержимое которых приходится очищать. Мы автоматизируем процесс.

Как создать макрос в Excel

Всего можно задать макрос двумя способами:

  • Автоматически;
  • Вручную.

Автоматическая запись макросов подойдет для всех пользователь, даже для тех, кто не умеет работать с кодом. В этом случае просто идет записывание определенных действий в программе Excel, которые вы выполняете в данный момент времени. Таким образом можно создать примитивный макрос, однако, как вы понимаете, его применение на практике будет весьма ограничено.

В случае с ручной записью макросов вам потребуются знание специальных языков программирования. так как код придется набирать вручную. Но, несмотря на то, что этот способ кажется сложнее, он может быть применим во многих ситуациях. Из-за его сложности мы не будет рассматривать данный способ в нашей статье, а поговорим о том, как можно сделать автоматический макрос, а также запустить его выполнение.

Включение автоматической записи макросов

Перед тем, как начать работу с макросами в программе Excel вам потребуется выполнить их включение. У нас уже есть подробная статья по этой теме, и вы можете прочитать её по представленной ссылке.

Здесь же будет предоставлена только краткая инструкция по их включению:

  1. Откройте табличный редактор MS Excel и нажмите на кнопку «Файл», что расположена в верхней части окна.
  2. Здесь, в левом меню, выберите пункт «Параметры».

Итак, вы подключили макросы, но ничего не произошло, так как их ещё никто не записал, а новых из сторонних источников вы не добавляли. В этой статье мы не будем рассматривать, как записать макрос, используя программный код. Вместо этого посмотрим, как сделать так, чтобы программа сама составляла макросы, исходя из ваших действий. Эта инструкция будет понятна и выполнима для всех пользователей, вне зависимости от их уровня владения компьютером и Excel.

  1. Снова перейдите во вкладку «Разработчик», которая должна была появится после выполнения предыдущей инструкции.
  2. Здесь нажмите на кнопку «Запись макроса». Она расположена в блоке «Код» и в некоторых версиях может быть без подписи, поэтому смотрите её расположение на скриншоте ниже. Оно не меняется в зависимости от версии продукта.

После этого включится автоматическая запись действий для встроенного в программу макроса. Будут записываться все ваши действия в программе до тех пор, пока вы не остановите запись. Давайте для примера создадим макрос, который будет при запуске складывать содержимое нескольких ячеек. В нашем случае формула для макроса будет выглядеть так: =C4+C5+C6.

На нашем сайте есть вводная статья по работе с формулами в Excel. Если вы сейчас плохо понимаете, о чём идёт речь, то рекомендуем ознакомиться с данным материалом по ссылке.

Давайте запишем наш первый макрос:

  1. Выделите ячейку, в которой будет записана формула.
  2. Пропишите формулу. В данном случае это формула вида a+b+c. Вместо букв потребуется вставить номера ячеек. Вид формулы можете посмотреть выше или перейти по ссылке с отдельной статьёй. Помните, что ячейки, которые вы указываете в формуле, не должны быть пустыми. В противном случае вместо решения вы получите ошибку.
  3. Макрос записал ваше действие с формулой. Нажмите Enter, чтобы она посчиталась.

Запускаем макрос

Вы записали макрос, который при выполнении операции будет считать содержимое нескольких ячеек по заданной формуле. Однако, чтобы он работал, вам потребуется выполнить его запуск:

    Снова откройте вкладку «Разработчик» и там найдите большую кнопку «Макросы». Она расположена в блоке «Код». Также вы можете воспользоваться сочетанием клавиш Alt+F8.

Если при создании макроса вы задавали ему комбинацию горячих клавиш для вызова, то можете использовать её. В таком случае вам не придётся проделывать тех действий, что описаны в инструкции выше.

После запуска макрос должен будет задать данные ячейкам и сложить их между собой, как вы это делали при записи.

Редактируем макрос

Записанные макросы, а также те, которые вы скачали с других источников, можно редактировать с помощью встроенных инструментов. Однако для этого вам потребуется знание кода, на котором написан макрос. В качестве примера мы рассмотрим, как его отредактировать, а также ознакомимся с азами требуемого синтаксиса:

    Перейдите во вкладку «Разработчик», а затем откройте «Макросы» или воспользуйтесь комбинацией клавиш Alt+F8.

  • Sub – это команда отвечающая за начало записи макроса. Она обязательно стоит в самом начале, затем идёт наименование макроса, которое задал пользователь, а затем сам код макроса;
  • End Sub. Стоит в самом конце кода. Отвечает за остановку записи, следовательно, и выполнения макроса;
  • Range(«…»).Select. Это оператор, отвечающий за выбор ячейки. Например, команда Range(«A1»).Select выберет ячейку с идентификатором A1;
  • ActiveCell.FormulaR1C1. Отвечает за работу с формулами, которые используются при записи макроса. Также может быть использовано для других расчётов.

В итоге у вас должно получится, что в формулу добавится ещё одна ячейка, а в ней будет число 1158 или любое, которое вы зададите. При выполнении макроса вы получите указанные ячейки заполненными, а формулу посчитанной.

Во время работы с большими макросами вы можете столкнуться с тем, что его выполнение занимает много времени, а также потребляет значительную часть ресурсов машины. Обычно, данная проблема больше актуальна для старых компьютеров. Если вы хотите ускорить процесс выполнения, то в код придётся добавить команду Application.ScreenUpdating = False. Она отвечает за выполнение обновления экрана во время вычислительных действий. Если у неё после равно стоит значение «False», то обновление экрана после каждого действия макроса будет отключено, что позволит значительно сократить потребление вычислительных ресурсов компьютера. В конце вы только увидите результат вычисления.

Для возобновления обновления экрана после выполнения каждой задачи следует прописать команду Application.ScreenUpdating = True.

Также для оптимизации работы макросов в Excel можно воспользоваться командой Application.Calculation = xlCalculationManual, которая ставится в начальной позиции кода (примерное расположение отмечено на скриншоте). В конце кода вам нужно будет написать команду Application.Calculation = xlCalculationAutomatic. Первая команда отключит автоматический пересчёт результата после каждого изменения в ячейках, а вторая наоборот, включит его, но только уже после выполнения основной части макроса. Благодаря этому Excel будет считать результат только один раз, а не после каждого выполненного действия, что значительно сэкономит время и ресурсы. Правда, иногда подобного рода команды могут исказить результат подсчёта, поэтому в определённых условиях от использования этой команды приходится отказаться.

Создание макроса с чистого листа

Этот вариант подойдёт только для продвинутых пользователей, которые хорошо знакомы с языком Basic, так как на нём пишутся все макросы. Программа позволяет не только редактировать макрос в специальном редакторе, но и писать его с нуля в таком же редакторе.

  1. Перейдите во вкладку «Разработчик» и нажмите там на кнопку «Visual Basic».
  2. Откроется среда разработки, в которую вам и нужно будет прописывать код. Он пишется вручную, но некоторые плагины для офисного пакета Майкрософт могут значительно ускорить написание макросов.

Сами макросы позволяют значительно ускорить процесс работы в Excel и сопутствующих продуктах. В основном они завязана на автоматизации рутинных процессов, которые постоянно повторятся и никак (или почти никак) не меняются. Макросы можно записать без знаний кода и отредактировать/оптимизировать их, если имеются базовые представления о синтаксисе Basic. Если у вас имеются какие-либо вопросы по статье или предложения, то напишите их в комментариях ниже.

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

Прежде чем создавать Сводную таблицу необходимо подготовить таблицу с исходными данными для дальнейшего анализа:

  • во-первых, необходимо озаглавить все столбцы;
  • во-вторых, нужно проследить, чтобы не было пустых ячеек и строк (иначе при создании Сводной таблицы не исключены ошибки при автоматическом заполнении пробелов, что может исказить информацию);
  • в-третьих, в столбце могут находиться значения только одного формата (например, в столбце «Дата покупки» допустимо значение лишь типа Дата, «Название» будет содержать только текстовые строки и так далее);
  • в-четвертых, в ячейках не должно быть перечисления (то есть неправильно записывать в ячейку адрес в виде перечисления: «Город, улица, дом, квартира». Можно создать столбцы для каждого значения или оставить что-то одно, например, город, если этой информацией можно обойтись).

Требования очень похожи на те, что выдвигают при проектировании реляционных баз данных, поэтому специалистам (или хотя бы тем, кто имеет базовое представление) в этой области не сложно будет провести параллель и освоить новый программный инструмент.

Вот мы и добрались до главного вопроса, который нас интересовал – создание Сводной таблицы в Excel. Начнем разбираться.

В статье мы будем приводить примеры на учебной таблице, где отображена информация о закупках товара, предположим, магазином одежды.

Пример сводной таблицы excel

Сначала откроем таблицу, убедимся, что она удовлетворяет всем требованиям и со спокойной душой перейдем к следующему шагу. Найдем вкладку «Вставка» на панели быстрого доступа. Перед нами появится лента с доступным функционалом. Мы создаем Сводную таблицу, поэтому ищем одноименную кнопку. Она будет самой первой на панели.

где сводные таблицы в excel

«Кликнем» на значок, и перед нами появится диалоговое окно с перечнем необходимых параметров:

  • диапазон ячеек, откуда будут браться данные (это могут быть ячейки из текущей таблицы Excel или внешний источник данных – другая таблица Excel или модели данных из СУБД)
  • Место, куда будет помещен отчет сводной таблицы (либо на текущий лист, либо на новый. Рекомендуется создавать на новом листе, но Вы сами делаете выбор, опираясь на свою задачу)

настройка сводной таблицы

Выбрав все параметры, нажимаем ОК, и на новом листе выделяется область, где будет располагаться Сводная таблица, а справа появится панель управления, с помощью которой мы сможем превратить обычную таблицу в информативный отчет.

Полдела сделано — Сводная таблица создана, осталось заполнить ее данными. Для этого обратимся к окошку справа «Поля сводной таблицы».

Сверху мы видим область, где перечислены все атрибуты исходной таблицы: «Дата поступления», «Наименование товара», «Отдел» и так далее.

Теперь можно крутить, вертеть данные как угодно, придумывая концепции для Сводной таблицы.

Для начала попробуем объединить данные по отделам и высчитать суммарную стоимость товаров, пришедших в каждый отдел.

Для добавления поля в таблицу, нужно поставить галочку напротив названия атрибутов в списке полей в верхнем правом углу. В нашем случае — «Отдел» и «Стоимость товара в партии».

В окошке, отведенном под Сводную таблицу, появились три строки с названиями отделов (левый столбик) и стоимостью товара по каждому отделу. Помимо суммы можно считать максимум, минимум, среднее значение и делать другие математические операции. Итоговый результат по всем трем отделам указывается в самом низу таблицы.

Посмотрите, какие места заняли данные в нижней части панели настроек: поле «Отдел», содержащее текстовые значения, переместилось в область «Строки», а численные данные — в «Значения».

То же самое произойдет, если мы добавим в таблицу любое числовое поле (например, «Количество»): у текущей таблицы появится столбец, и в области «Значения» — «Сумма по полю Количество».

Теперь в нашей таблице отображается информация не только о том, на какую сумму пришло товара в каждый отдел, но и в каком количестве.

Если нас не устраивает такая ориентация таблицы, то из области «Строки» можно перетащить «Отдел» в область «Столбцы»: передвиньте курсор, удерживая кнопку мышки в нажатом положении.

Таблица стала намного компактнее: в строчке отображается результат вычислений, а по столбцам – отделы, которым соответствуют данные.

Такое расположение может быть удобным при маленьком количестве отделов (как в примере), но если их много, то мы получим очень вытянутую таблицу, которую невозможно будет охватить взглядом и в которой трудно будет найти необходимые данные. Такая таблица нам точно не нужна.

Как мы уже отметили, сумма — это далеко не единственная операция, применимая к данным. Посмотреть, какие формулы нам доступны, можно, нажав на название поля в области «Значения» и выбрав «Параметры полей значений».

Появится окно со списком операций, которые можно применять к данным. Среди них: количество, среднее, максимум, минимум, отклонение, дисперсия и другие, — все, что может понадобиться для качественного анализа.

Для каждой строчки в поле «Значения» можно применять разные формулы. Например, добавим в таблицу поле «Цена» и отобразим максимальные значения по каждому отделу.

Итак, какие выводы мы можем сделать по получившейся таблице? В магазин больше всего закупили Аксессуаров (на сумму 267660 руб.), причем самый дорогой товар стоил 2700 руб.

Как Вы могли заметить, мы еще ни разу не пользовались областью «Фильтры» на панели параметров. Этот раздел нужен для того, чтобы можно было сортировать данные по заданным критериям.

К примеру, добавим в Сводную таблицу данные, чтобы можно было отфильтровать записи по «Дате поступления». Для этого поставим напротив поля «Дата поступления» галочку. Это поле сразу переместиться в область «Строки».

Данные добавили, теперь нужно создать фильтр. Для этого просто перетаскиваем «Дату поступления» в область «Фильтры», как мы это делали раньше со столбцами и строками. Получим вот что:

Над всеми строками появилось еще одно поле — «Дата поступления», в котором мы можем выбрать дату, нажав на треугольник в конце строчки (Все).

Теперь легко узнать на какую суммарную стоимость пришел товар, сколько стоил самый дорогой в определенный день. Например, за 04.05.2017.

Помимо выставления определенной даты, можно выбирать название отделов. То есть можно сделать Сводную таблицу, где будет содержаться информация о том, на какую сумму 04.05.2017 поступило Аксессуаров и какова цена самого дорогостоящего товара.

Power Query: мощь и простота работы с данными в Excel

«Ручной привод» в работе с данными, частое явление. Многие пользователи Excel, обрабатывают данные «привычным» для себя способом, с минимальной автоматизацией, тратя кучу времени. Мало, кто слышал и использует волшебный инструмент — Power Query.

Power Query: мощь и простота работы с данными в Excel Microsoft Excel, Аналитика, Офис, Продуктивность, Microsoft office, Бухгалтерия, Отдел кадров, Маркетинг, Длиннопост

Почему Power Query?

Power Query — технология подключения к данным, с помощью которой можно обнаруживать, подключать, объединять, преобразовывать и уточнять данные из различных источников для последующего анализа. Функции Power Query доступны в Excel и Power BI.

Аргументы ЗА изучение надстройки:

1. Простой способ преобразовать данные, без использования формул и сводных таблиц;

2. Быстрый способ, вы можете много сделать с данными, в несколько кликов мыши;

3. Разовая настройка, сформируйте запрос один раз и обновляйте его, когда происходит изменение данных в источнике, или настройте автоматическое обновление.

Возможности Power Query

Используя надстройку, вы сможете быстро:

1. Загружать данные из широкого спектра источников и подключаться к ним:

Power Query: мощь и простота работы с данными в Excel Microsoft Excel, Аналитика, Офис, Продуктивность, Microsoft office, Бухгалтерия, Отдел кадров, Маркетинг, Длиннопост

2. Собирать данные из файлов всех основных типов данных (XLSX, TXT, CSV, JSON, HTML, XML. ), по одному или несколько за раз, например из всех файлов указанной папки или непосредственно с листа(-ов) книги;

3. Выполнять слияние источников данных для дальнейшего анализа и моделирования с помощью Power Pivot и PowerView;

4. Выполнять очистку данных от мусора;

5. Причёсывать данные: исправлять регистр, числа-как-текст, разбирать текст на столбцы и склеивать обратно, делить дату на составляющие (год, квартал, месяц, день недели. ) и т.д.;

6. Настраивать представление таблиц: используя фильтры, сортировку, изменение порядка столбцов, транспонирование, добавление итогов, разворачивая кросс-таблицы в плоские и сворачивать обратно;

7. Подтягивать данные из одной таблицы в другую по совпадению одного или нескольких параметров, заменяет знакомую многим функцию ВПР и ее аналоги.

Power Query: где искать, как установить?

Для Excel 2016, 2019 или Office 365: надстройка уже находится на вкладке Данные ► Получить и преобразовать:

Power Query: мощь и простота работы с данными в Excel Microsoft Excel, Аналитика, Офис, Продуктивность, Microsoft office, Бухгалтерия, Отдел кадров, Маркетинг, Длиннопост

Для версий 2013 и 2010: загрузите надстройку (официальный сайт Microsoft) выбрав версию, подходящую для вашего устройства. Как только вы загрузите файл, откройте его и следуйте инструкциям.

После этого автоматически откроется вкладка POWER QUERY на ленте:

Power Query: мощь и простота работы с данными в Excel Microsoft Excel, Аналитика, Офис, Продуктивность, Microsoft office, Бухгалтерия, Отдел кадров, Маркетинг, Длиннопост

Если вкладка не появилась, вам нужно ее отобразить:

1. Перейдите на вкладку Файл ► Параметры ► Надстройки;

2. В опциях Надстройки выберите Надстройки COM, нажмите Перейти;

3. Отметьте галочкой Microsoft Power Query for Excel ► ОК, вкладка появится на ленте.

Редактор запросов

Окно редактора запросов, содержит следующие элементы:

Power Query: мощь и простота работы с данными в Excel Microsoft Excel, Аналитика, Офис, Продуктивность, Microsoft office, Бухгалтерия, Отдел кадров, Маркетинг, Длиннопост

1. Лента редактора запросов: Файл, Главная, Преобразование, Добавление столбца, Просмотр;

2. Запросы — окно с перечнем созданных запросов, можно свернуть / развернуть;

3. Строка формул, можно отобразить или скрыть в меню Просмотр ► Панель формул;

4. Сетка предварительного просмотра, в которой выводятся результаты каждого шага запроса;

5. Меню для редактирования данных, открывается при нажатии на шапку столбца правой кнопкой мыши;

Панель параметры запроса:

6. Свойства — редактируемое поле названия запроса;

7. Примененные шаги запроса, включающая каждый шаг запроса. Шаги можно редактировать, выбирая в списке, изменять последовательность шагов, добавлять новые или удалять.

Power Query — запросы, которые может создавать любой, указывая системе, куда обратиться и какие действия выполнить. Команды записываются на языке М. Язык не требует знаний и навыков программиста: код генерируется автоматически. При помощи мыши вы можете решать почти все задачи, стоящие перед вами. Но иногда запрос нужно все-таки поправить, еще реже – написать полностью вручную.

Далее, выйдет серия статей о работе в Power Query, подписывайтесь, чтобы быть в курсе.

Ссылка на основную публикацию