Рис. 161

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

Рис. 162

7. ИСПОЛЬЗОВАНИЕ МАКРОСОВ

Макрос – макрокоманда, т.е. укрупненная команда. Другими словами макрос – это программа, объединяющая несколько команд (действий) выполняемых последовательно. Записывается макрос на VBA (Visual Basic for

83

Applications). В Excel существует возможность создавать макросы без программирования, т.е. записывать их с использованием макродекодера. Макродекодер используют для записи действий, выполняемых пользователем в макрос (т.е. программу), после чего можно воспроизводить записанные действия. Особенность программы написанной таким образом - записанные действия пользователя в точности воспроизводятся и все. Другими словами не будет возможности сделать выбор некоторой последовательность команд в зависимости от изменяющегося условия, невозможно будет зациклить (повторить некоторые действия), не будет возможности выводить различные сообщения с помощью диалоговых окон и прочее. Все это и многое другое можно будет сделать, если усовершенствовать написанный с помощью макродекодера макрос, вручную добавив к нему команды (операторы) языка Visual Basic for Applications. В этой главе разберемся, как пользоваться макродекодером для написания макросов и способы запуска созданного макроса.

Пусть нам необходимо написать макрос, который форматирует любую ячейку следующим образом: текст – красным цветом, жирным, размер кегля – 14, с подчеркиванием, шрифт – Times New Roman.

Чтобы начать запись макроса необходимо на ленте «Разработчик» выбрать команду «Запись макроса» (рис. 163).

Рис. 163

В открывшемся диалоговом окне обязательно только указать имя макроса, вся остальные опции можно игнорировать. Имя предлагается по умолчанию, сначала макрос1, затем макрос2 и т.д. Однако рекомендуется использовать мнемоническое имя, отражающее содержание макроса. Пусть в нашем случае макрос называется «Форматирование». Если назначить макросу сочетание клавиш, его можно будет запустить на выполнение этим сочетанием. Для этого необходимо указать латинскую букву в соответствующем поле (рис. 164), но не повторяющую стандартных сочетаний клавиш. Пусть в нашем случае макросу будет назначено сочетание клавиш CTRL+SHIFT+T. Если макрос сохранить в этой книге, то запустить его можно будет только в этой книге. В области «описание» можно в произвольной форме описать, какие действия производит макрос (рис. 164).

84

Рис. 164

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

Рис. 165

Рис. 166

85

Затем обязательно остановить запись макроса, нажав на соответствующую команду в строке состояния (рис. 171) или на ленте разработчик (рис. 172).

Рис. 169

86

Рис. 170

Рис. 171

Рис. 172

Чтобы увидеть доступные в этой книге макросы, нужно на ленте разработчик выбрать команду «Макросы» (рис. 173). Чтобы открыть написанный макродекодером код в открывшемся диалоговом окне нужно выбрать из списка доступных макросов нужный и нажать на команду «Войти» (рис. 174). Откроется окно VBA (рис. 175).

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

87

Рис. 173

Рис. 174

Рис. 175

88

Способы запуска макроса на выполнение:

1.Сделать нужную ячейку листа (ту ячейку, которую требуется отформатировать так, как записано в макросе) активной, выбрать нужный макрос в списке доступных макросов (рис. 174) и нажать на команду «Выполнить» (рис. 174). Активная ячейка на листе отформатируется описанным в макросе способом.

2.Сделать нужную ячейку листа активной и нажать комбинацию клавиш CTRL+SHIFT+T (она назначена макросу, выполняющему форматирование ячейки). Активная ячейка на листе отформатируется описанным в макросе способом.

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

Выбираем элемент управления «Кнопка» (рис. 176), рисуем ее физические размеры (рис. 177) в появившемся (рис. 178) диалоговом окне выбираем макрос, который нужно связать с этой кнопкой (рис. 179).

Рис. 176

Рис. 177

89

Рис. 178

Рис. 179

90

Рис. 180

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

Задания для самостоятельной работы по темам «Сводные таблицы» и «Макросы»

Создать программу для обработки базы данных «Склад».

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

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

3.На листе «Приход товара» создать сводную таблицу для базы на листе «Приход товара подробно». Сводная таблица должна содержать три элемента: в поле строка – наименование товара, в поле данных – количество и цена партии,

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

– добавлять товар на склад (с помощью формы добавлять строку в таблицу на листе «Приход товара подробно»);

– упорядочивать записи (строки в таблице на листе «Приход товара подробно») по дате поступления и по наименованию товара (сортировка);

91