Использование макроса для автоматизации повторяющихся операций Excel

ExcelИспользование макроса для автоматизации повторяющихся операций Excel.

Это страшное слово «Макрос», на самом деле, не такая уж и сложная штука.

Любой начинающий пользователь Excel может самостоятельно запрограммировать типичные операции, абсолютно ничего не зная ни о каких языках программирования.

Для этого MS Excel имеет очень интересный инструмент, под названием макрорекордер — надстройка, которая включается через меню параметров Excel.

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

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

А вот если мы проделаем все операции единожды, а потом просто запустим записанный алгоритм, то переделка всего документа осуществится нажатием двух клавиш. Для этого и предназначен макрорекордер, записывающий алгоритм наших действий на своем языке программирования, а, затем, воспроизводящий его.

Итак, мы имеем прайс-лист, который нужно раскидать по листам в соответствии с группой клиентов (см.рисунок. как вы понимаете — компания вымышленная, и даже цены по всей номенклатуре установлены нереальные). Алгоритм действий несложен:

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

Макрос готов. Давайте проверим — удалим со всех листов, кроме «Свод» все данные, и нажмем сочетание клавиш Ctrl+п. Как видите — все работает. Теперь, чтобы обновить прайс-листы, не нужно будет нудно выполнять одни и те же операции.

Однако, такой способ записи макросов далеко не идеальный. Когда будете экспериментировать, поймете, что нередко возникают непонятные результаты, которые в алгоритм не закладывались. Поэтому, все же, лучше начинать учится работать с кодом, и программировать макросы на языке программирования VBA. Началам работы с VBA в Excel будут посвящены мои дальнейшие статьи.

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

Сводные таблицы в ExcelСводные таблицы Excel.

Чтобы анализировать большое количество данных, простое подведение итогов в нижней строке таблицы недостаточно.

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

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

К примеру:

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

Одним словом, если все это программировать формулами, то придется повозиться. Парой-тройкой кликов здесь не обойдешься. Вот тут и самое время обратиться к уникальному инструменту Excel, под названием «Сводные таблицы«.

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

Установите табличный курсор в любом поле нашего массива, и выберите в закладке меню «Вставка» пункт «Сводная таблица«. Обратите внимание — программа сама определила диапазон, который у нас будет обрабатываться (его границы отмечены бегущим пунктиром). Однако, диапазон будет определен неверно, если какая-то строка таблицы или какой-то столбец будут полностью пустыми. В этом случае будет выделен диапазон до пустой строки или столбца.

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

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

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

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

Например, нам необходимо определить, какие суммы затрачиваются на выплату окладной части заработной платы по управлениям. Управления будем размещать в строках. Поэтому захватываем мышкой поле «Управление» и переносим его к области «Названия строк«. Теперь, поскольку нам необходимо вычислить затраты по окладной части, захватываем поле «Оклад«, и переносим его к области «Значения«. Вот что у нас получилось:

Но это еще не все. К примеру, нам нужно вывести еще и данные по отделам, но чтобы группировка по управлениям не терялась. Наименования отделов можно представить в колонках. для этого поле «Отдел» переносим к области «Названия столбцов«. Но это будет не совсем удобно (см.рис).

Правильнее будет, если отделы мы разместим в строках и сгруппируем по управлениям. Захватываем поле «Отдел» и переносим на поле «Названия строк» ниже поля «Управление«. Согласитесь, в таком виде таблица становится намного понятнее.

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

Можно разместить колонки «Оклад» и «Премия» рядом. Если, при добавлении этих полей, они размещаются в одну колонку, посмотрите на область «Названия строк«. У вас там должен появиться пункт «∑ значения«. Переместите его к области «Названия столбцов»

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

Предположим, нас не интересуют никакие иные управления, кроме управления маркетинга. Чтобы отфильтровать значения по какому либо из значений поля «Управление«, перенесем это поле к области «Фильтр отчета«. Это поле появится в верхней части сводной таблицы. Нажимаем на кнопку выбора из списка (серый квадратик с черным треугольничком), и выбираем пункт «Управление маркетинга«.

Кстати, если есть желание отфильтровать по нескольким подразделениям, то для этого есть специальная галочка в нижней части диалогового окна. А в этот раз жмем «ОК«, и получаем значения, отфильтрованные по критерию «Управление маркетинга».

Вставка формул в сводную таблицу

Теперь было бы неплохо посчитать сумму выплат оклада и премии. Это можно сделать с помощью формулы в сводной таблице:

  • устанавливаем табличный курсор в любом поле сводной таблицы;
  • жмем вкладку меню «Параметры«;
  • выбираем пункт «формулы«-«вычисляемое поле«.

В открывшемся окне заносим имя нашего поля (я назвал «Общая ЗП»), а в пункте «Формула» заносим операции с интересующими нас полями: =Оклад + Премия. Жмем «ОК». В области значений появляется требуемый результат.

Изменение параметра полей значений и оформление

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

Устанавливаем табличный курсор в поле значений, и выбираем во вкладке меню «Параметры» пункт «Параметры поля«. В открывшемся окне находим пункт «Количество» и выбираем его. При желании можно сменить наименование этого поля (у меня — «Количество сотрудников«).

Настройки представления таблицы можно осуществить во вкладке меню «Конструктор«. Я, например, выбрал «Макет отчета» — «Показать в сжатой форме» и поставил галочку в пункте «Чередующиеся строки«. Вот такая таблица у меня получилась:

и вот такое у нее размещение полей по областям.

Условное форматирование

Условное форматирование ExcelУсловное форматирование в Excel.

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

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

Допустим у вас есть список сотрудников (на рисунке он вымышленный), и вам необходимо в этом списке выделить тех, кто моложе 25 лет (менеджерам по персоналу часто приходится сортировать сотрудников по разным признакам). Это можно сделать очень просто, использовав условное форматирование:

Выделяем все ячейки с возрастом, и на вкладке «Главная» жмем на кнопку «Условное форматирование». В выпавшем списке подводим курсор к надписи «Правила выделения ячеек» и в появившемся меню справа кликаем мышкой на строке  «Меньше».

В открывшемся окне выбираем значение, которое будут выступать условием (в нашем случае — 25), и цвета заливки и шрифта соответствующих условию значений. Смотрим результат, и меняем, по необходимости. Можно выбрать «Пользовательский формат», и настроить цвета по своему усмотрению.

Например, мне нужно сделать желтую заливку, а шрифт выделить жирным и красным. На закладке «Шрифт» выбираю «Полужирный», а на закладке «Заливка» на палитре выбираю желтый цвет. Вот что получилось.

Помимо выделения цветом, для повышения наглядности, можно использовать различные значки и стрелки. Рассмотрим эту возможность на другом примере.

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

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

Для более продвинутого форматирования ячеек используется ввод условий вручную. Для этого в меню условного форматирования имеется пункт «Создать правило». Откроется окно со следующим списком:

Тем, кто предпочитает видеоинструкции, предлагаю неплохой вариант.