Тема: информационные технологии табличных процессоров. Работа с базой данных в Excel. Сортировка, фильтры.

Цель работы:

- изучить возможности для организации данных в Excel в виде списка или базы данных;

- освоить технологию обработки cписков в Excel;

- научиться извлекать определенные записи и поля из баз данных.

Теоретические сведения

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

Для учета данных о сотрудниках на предприятиях используют самые разнообразные методы. В одних организациях существуют журналы учета, куда информация вносится вручную, в других применяются классические базы данных для учета кадров, в третьих используются СУБД Access. Но в большинстве случаев на небольших предприятиях учет данных о сотрудниках ведется в электронных таблицах Microsoft Excel.

Приложение Microsoft Excel обладает богатыми встроенными средствами для обработки и анализа данных. Аналогом простой базы данных в Excel служит список.

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

Рисунок 8.1 База данных. Пищевая фабрика

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

Проверка данных при вводе. Если с файлом работает сразу несколько пользователей, желательно контролировать тип вводимой ими информации и свести к минимуму ошибки ввода. В Excel выполнение подобных условий проверяется при помощи средства, которое называется проверкой ввода. Для этого надо:

- выделить ячейки столбца, для которого устанавливается проверка ввода;

- на ленте Данные в группе Работа с данными выбрать команду Проверка данных;

- на вкладке Параметры в области Условие проверки выбрать Тип данных: Любое значение (используется для отмены проверки ввода), Целое число, Действительное, Список, Дата, Время, Длина текста и Другой (формат, для которого можно задать собственную формулу, например,"м"or"ж"). При выборе значения внизу окна появляются дополнительные поля для ввода условий или ограничений – например, минимального и максимального допустимого значения;

- на вкладке Сообщение для ввода можно установить флажок Отображать подсказку, если ячейка является текущей и ввести сообщение, чтобы оно появлялось на экране при выделении ячеек;

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

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

Чтобы отсортировать список надо:

- установить курсор в ячейку списка;

- выполнить команду Сортировка на ленте Данные в группе Сортировка и Фильтр;

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

Выбор настраиваемого порядка позволяет задать нестандартный порядок сортировки. Для этого надо в диалоговом окне Списки выбрать НОВЫЙ СПИСОК, в поле Элементы списка ввести значения, образующие пользовательский порядок сортировки, после чего последовательно выбрать кнопки Добавить и ОК (рисунок 8.2).

Рисунок 8.2 Создать свой список сортировки

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

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

- выводить итоговую сумму;

- отображать список в виде структуры, что позволяет разворачивать и сворачивать разделы с помощью щелчка мыши.

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

Режим структуры, в котором оказывается список после выполнения команды Итоги, позволяет просматривать различные части списка с помощью кнопок, расположенных на левом поле (рисунок 8.3).

Рисунок 8.3 Просмотр списка в режиме структуры.

Кнопки, расположенные в верхнем левом углу, определяют количество выводимых уровней данных. Кнопки со значками "+" и "-" предназначены для свертывания \ развертывания отельных групп.

Чтобы удалить промежуточный и окончательные итоги, надо повторно выполнить команду Промежуточные итоги, а затем щелкнуть по кнопке Убрать все.

Автофильтр. Отфильтровать список - значит показать только те записи, которые удовлетворяют заданному критерию.Чтобы установить или убрать автофильтр надо на ленте Данные в группе Сортировка и фильтр выбрать команду Фильтр. После этого нажать кнопку со стрелкой возле названия какого-либо поля, чтобы раскрыть список его элементов и выбрать отображаемые значения или задать условие отбора. На экране появятся только те записи, которые отвечают заданному условию. В случае необходимости можно продолжить фильтрацию, нажимая кнопки со стрелками на других полях.

Показать все записи по отфильтрованному полю, не убирая фильтр, можно выбрав в списке фильтра критерий Снять фильтр с….

Показать все записи по всем полям, не убирая фильтр, команда Очистить.

Для данных разного типа существуют дополнительные автофильтры, которые находятся в списке критериев Текстовые фильтры, Числовые фильтры, Фильтры по дате и т.д.

Если выделить какое-то числовое поле (например, Цена), а в списке критериев выбрать Числовые фильтры, то появится список дополнительных фильтров (рисунок 8.4), которые позволяют:

- задать критерий в виде неравенства – критерии равно, не равно, больше, больше или равно, меньше, меньше или равно, между;

- вывести первые N значений – критерий Первые 10: после выбора в списке Числовых фильтров команду Первые 10…, необходимо в появившемся окне указать число значений (N), а также способ вычисления: количество элементов списка, % от количества элементов;

- определить условие по среднему значению в указанном столбце – критерии Выше среднего, Ниже среднего;

- самостоятельно задаваемый фильтр – критерий Настраиваемый фильтр.

Рисунок 8.4. Дополнительные числовые фильтры

Настраиваемый фильтр позволяет задать критерии из одного или двух условий. Простое условие состоит: из имени поля (атрибута); варианта условия (равно, не равно, больше, меньше, больше или равно, меньше или равно; начинается с, не начинается с, заканчивается на или не заканчивается на; содержит, не содержит); слова или числа для сравнения. Сложное условие состоит из двух простых, соединенных союзами И или ИЛИ. При написании значений в условиях сравнения в фильтрах можно использовать подстановочные знаки (Таблица 8.1).

Таблица 8.1 Подстановочные знаки.

Знак	Значение
?	один любой символ
*	любое количество символов
~	используют, когда в тексте надо найти подстановочные знаки (символы «?», «*» или «~»)

Расширенный фильтр. Расширенный фильтр позволяет сформировать более сложные условия, в том числе состоящие из более, чем двух условий. Перед вызовом команды Расширенный фильтр, необходимо сформировать критерии. Для удобства лучше формировать критерии на отдельном листе (можно дать ему имя, например, Критерии) и давать критериям имена Кр1, Кр2 и т.д. Основное правило: если критерии связаны между собой операцией И, то они должны располагаться в одной строке, а если ИЛИ, то в разных. После формирования критерия, вызывают расширенный фильтр: на ленте Данные в группе Сортировка и фильтр команда Дополнительно. Восстановить исходный список можно выбрав на ленте Данные в группе Сортировка и фильтр команду Очистить.

Задание 1. Создать базу данных (рисунок 8.5).

Рисунок 8.5 База данных

Задание 2. Выполнить задания по проверке данных (рисунок 8.6).

Рисунок 8.6. Задания. Проверка ввода данных

Задание 3. Создать базу данных о работе пищевой фабрики за один месяц в соответствии с таблицей 8.2:

Таблица 8.2 База данных. Пищевая фабрика

Цех	Продукция	Цена	Количество по плану	Фактическое количество
Макаронный	Спагетти	2,2
Кондитерский	Торт «Сказка»	7,5
Консервный	Зеленый горошек	2,3
Консервный	Кетчуп	2,2
Макаронный	Лапша	2,1		2,5
Макаронный	Макароны	1,8
Консервный	Майонез	1,4
Кондитерский	Бисквит «Лето»	4,1
Кондитерский	Зефир	5,5
Макаронный	Рожки	1,6
Консервный	Паштет печеночный	1,8

Задание 4. Выполнить задания в соответствии с вариантом, выданным преподавателем (таблица 8.3).

Таблица 8.3

Номер варианта	Номера заданий
	1,2,3,6,7,8,9,11,13,15
	1,2,4,6,7,8,9,12,14,16
	1,2,5,6,7,8,9,11,15,17
	1,2,3,6,7,8,9,12,16,18
	1,2,4,6,7,8,9,11,17,19
	1,2,5,6,7,8,9,12,18,20
	1,2,3,6,7,8,9,11,19,13
	1,2,4,6,7,8,9,12,20,14
	1,2,5,6,7,8,9,11,13,15
	1,2,3,6,7,8,9,12,14,16

Задания:

1. Подсчитать процент выполнения плана.

2. Подсчитать стоимость изготовленной продукции каждого наименования.

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

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

5. Отсортировать сведения о работе пищевой фабрики в алфавитном порядке цехов, внутри каждого цеха в алфавитном порядке продукции.

6. Подсчитать суммарную стоимость изготовленной продукции

7. Найти максимальное количество по плану.

8. Найти минимальную цену за единицу продукции.

9. Подсчитать средний процент выполнения плана.

10. Используя автофильтр вывести на экран сведения о цехах, процент выполнения плана в которых больше 100% и меньше 130%.

11. Используя автофильтр вывести на экран сведения о трех цехах, план выполнения которых наибольший.

12. Используя автофильтр вывести на экран сведения о двух цехах, значение выполнения плана которых было наименьшим.

13. Используя расширенный фильтр, выдать сведения о той продукции макаронного цеха, план производства которой был перевыполнен, а стоимость была меньше 100 (цех, продукция, процент выполнения плана, стоимость изготовленной продукции).

14. Используя расширенный фильтр, выдать сведения о той продукции консервного цеха, план производства которой был недовыполнен или цена продукции меньше 2 (цех, продукция, процент выполнения плана, цена продукции).

15. Используя расширенный фильтр, выдать сведения о той продукции консервного цеха, план производства которой был недовыполнен или цена единицы продукции была больше 3 (цех, продукция, процент выполнения плана, цена продукции).

16. Используя расширенный фильтр, выдать сведения о той продукции цехов, план производства которой был недовыполнен или цена единицы продукции была больше 2,5 (цех, продукция, процент выполнения плана, цена продукции).

17. Используя расширенный фильтр, выдать сведения о той продукции макаронного цеха, план производства которой был перевыполнен или фактическое количество больше 200 (цех, продукция, процент выполнения плана, фактическое количество).

18. Используя расширенный фильтр, выдать сведения о той продукции кондитерского цеха, план производства которой был перевыполнен или фактическое количество больше 150 (цех, продукция, процент выполнения плана, фактическое количество).

19. Используя расширенный фильтр, выдать сведения о той продукции консервного цеха, план производства которой был перевыполнен или количество по плану больше 450 (цех, продукция, процент выполнения плана, количество по плану).

20. Используя расширенный фильтр, выдать сведения о той продукции консервного цеха, план производства которой был недовыполнен или цена продукции меньше 2 (цех, продукция, процент выполнения плана, цена продукции).

Задание 5. Создать таблицу 8.4.

Таблица 8.4.

Дата	Номер магазина	Фамилия товароведа	Наименование товара	Цена единицы товара	Количество товара (единиц)	Общая цена товара
08.04.99		Семенова И.И.	Пальто мужское
07.04.99		Семенова И.И.	Сапоги женские
05.04.99		Чебик Ф.А.	Платье женское
03.04.99		Петренко А.И.	Детские колготки
03.04.99		Петренко А.И.	Пальто мужское
03.04.99		Петренко А.И.	Пальто мужское
05.04.99		Чебик Ф.А.	Платье женское
08.04.99		Костенко К.И.	Плащ женский
09.04.99		Корбунова Н.Н.	Сапоги женские
04.04.99		Огурцов П.Н.	Туфли мужские
04.04.99		Огурцов П.Н.	Туфли мужские
04.04.99		Огурцов П.Н.	Туфли мужские
03.04.99		Петренко А.И.	Пальто мужское
08.04.99		Костенко К.И.	Детские колготки
08.04.99		Костенко К.И.	Плащ женский
09.04.99		Корбунова Н.Н.	Плащ женский
04.04.99		Огурцов П.Н.	Детские колготки
04.04.99		Чебик Ф.А.	Зонт женский
05.04.99		Чебик Ф.А.	Детские колготки
05.04.99		Чебик Ф.А.	Платье женское
03.04.99		Семенова И.И.	Плащ женский
09.04.99		Корбунова Н.Н.	Зонт женский
09.04.99		Корбунова Н.Н.	Сапоги женские
06.04.99		Семенова И.И.	Детские колготки

Задание 6. Выполнить задания по сортировке значений таблицы 8.4.

1. Отсортировать по фамилии товароведа по возрастанию сортировать по, по наименованию товара по возрастанию затем по, по количеству товара по убыванию в последнюю очередь по

2. Отсортировать по дате по убыванию сортировать по, по номеру магазина по возрастанию затем по, по наименованию товара по убыванию в последнюю очередь по.

3. Отсортировать по номеру магазина по убыванию сортировать по, по фамилии товароведа по возрастанию затем по, по цене единицы товара по убыванию в последнюю очередь по.

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

5. Отсортировать по фамилии товароведа по алфавиту, затем по цене единицы товара по убыванию, в последнюю очередь по дате по возрастанию.

6. Отсортировать по номеру магазина по возрастанию, затем по наименованию товара по алфавиту, в последнюю очередь по количеству товара по убыванию.

7. Отсортировать по цене единицы товара по возрастанию, затем по фамилии товароведа по алфавиту в последнюю очередь по дате по убыванию.

Контрольные вопросы.

1. Для каких целей применяются электронные таблицы?

2. Для чего необходимы базы данных?

3. Какие возможности предоставляет программа Microsoft Excel для работы с базами данных?

4. Что такое запись?

5. Что такое поле?

6. Что такое тип данных? Зачем необходимо указывать типы полей?

7. Данные каких типов могут быть записаны в ячейку?

8. Как установить проверку вводимых в список значений?

9. Как записываются абсолютные и относительные адреса ячеек?

10. Что такое сортировка? Как отсортировать список по двум и более ключам?

11. Что такое фильтр?

12. Какие виды фильтров вы знаете?

13. В чем отличие сортировки списка от фильтрации списка?

14. Что такое расширенный фильтр?

15. Как сформировать критерий для расширенного фильтра?

Литература[3, 7, 8].

Лабораторная работа №9

Тема: Информационные технологии табличных процессоров. Анализ данных в EXCEL. Моделирование расчетов «Что-если…».

Цель работы:

- научиться осуществлять в Excel анализ данных с использованием аппарата прогнозирования развитие некоторых ситуаций;

- моделировать расчеты “Что-Если”с использованием команды Подбора параметров.

Теоретические сведения

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

Для вызова команды Подбор параметра нужно сделать активной вкладку Данные, в группе команд Работа с данными нажать на кнопку Анализ “что-если” , а затем в списке этой кнопки выбрать пункт Подбор параметра.

Задание 1. В новой книге на Листе1 в интервале ячеек А1:Н20 создайте таблицу 1 (рисунок 9.1).

Рисунок 9.1 Сведения о работе рыбообрабатывающего завода

Задание 2. На Листе 2 в интервале ячеек А1:F16 создайте Таблицу 2 (рисунок 9.2).

Рисунок 9.2 Сведения о работниках рыбообрабатывающего завода

Подсчитайте итоговую сумму окладов по всему заводу.

Задание 3. Необходимо рассчитать ра з мер квартальной премии, квартальной заработной платы, отчислений в пенсионный фонд за квартал по каждому работнику завода, а также по всему заводу.

Условием начисления премии является перевыполнение плана по выпуску продукции за квартал. Причем рабочим премия начисляется в размере 20% от суммы окладов за квартал, остальным работающим - 10%. Отчисления в пенсионный фонд за квартал - 2% от квартальной заработной платы.

Для выполнения задания проделайте следующие действия:

- на Листе 2 в ячейке G2 задайте текст - Квартальный оклад;

- в ячейке G3 формулу: =3*D3;

- формулу скопируйте в интервал ячеек G4:G16;

- в ячейке Н2 задайте текст – Премия;

- задайте текст в ячейке А19 - Процент начисления премии, в ячейке А20 – Рабочим, в ячейке В20 – Всем служащим;

- введите значения: в ячейку А21 – 20%, в ячейку В21 - 10%;

- в ячейку С19 введите текст - % отчисления в пенсионный фонд, объедините ячейку С19 и С20;

- в ячейку С21 задайте значение 2%;

- ячейке Н3 задайте с помощью Мастера функция формулу для расчета премии:

=ЕСЛИ(Лист1!$K$21>=100%;ЕСЛИ(C3="Рабочий";G3*$A$21;G3*$B$21);0)

В этой формуле:

- первый аргумент: Лист1!$K$21>=100% - проверка выполнения квартального плана;

- второй аргумент – если план перевыполнен, то начисляется премия. Начисление премии производится по формуле: ЕСЛИ(C3="Рабочий";G3*$A$21;G3*$B$21);

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

- третий аргумент - 0, так как, если план не выполнен, то премия не начисляется.

Диалоговое окно Мастера функций при задании аргументов вложенной в формулу функции Если будет иметь следующий вид (рисунок 9.3):

Рисунок 9.3 Диалоговое окно Мастера функций Если

Диалоговое окно Мастера функций при задании аргументов первой функции Если будет иметь следующий вид (рисунок 9.4):

Рисунок 9.4 Диалоговое окно Мастера функций Если

- в ячейке I2 задайте текст - Квартальная зарплата;

- в ячейку I3 введите формулу: =G3+H3, которую скопируйте в интервал ячеек I4:I16;

- в ячейке J2 задайте текст - Отчисления в пенсионный фонд;

- в ячейку J3 введите формулу: =I3*$C$21, которую скопируйте в интервал ячеек J4:J16;

- подсчитайте итоговые значения по квартальным окладам, премии, квартальной зарплате, отчислениям в пенсионный фонд;

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

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

- скопируйте интервал ячеек А2:I16 на новый лист, начиная с ячейки А2;

- переименуйте новый лист, дав ему имя Подбор параметра;

- скопируйте интервал ячеек А19:В21 листа 2 в соответствующий интервал ячеек листа Подбор параметра;

- в соответствующие ячейки листа Подбор параметра введите поясняющий текст (рисунок 9.5):

Рисунок 9.5 Описание вычислений подбора параметра

- в ячейки В27:D27 занесите соответственно значения сумм окладов рабочих, служащих и общую сумму. Для этого:

1) выделите базу данных (интервал ячеек A2:I16);

2) используя команду Автофильтр, отберите записи базы данных, относящиеся к рабочим;

3) для отобранных записей в ячейке D17 подсчитайте сумму месячных окладов рабочих. Значение этой суммы сумму занесите в ячейку В27;

4) отобразите все записи, а затем отберите записи, относящиеся к остальным служащим (работников, не относящихся к рабочим – то есть не рабочие);

5) для отобранных записей в ячейке D17 подсчитайте значение суммы месячных окладов служащих. Эту сумму занесите в ячейку C27;

6) снимите действие фильтра;

7) в ячейке D17 будет находиться итоговое значение суммы месячных окладов рабочих и служащих. Занесите это значение в ячейку D27;

8) в ячейке I17 будет находиться итоговое значение суммы квартальной зарплаты рабочих и служащих.

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

1) в ячейке B28 задайте формулу для подсчета доли суммы месячных окладов рабочих в общей сумме: =B27/$D27;

2) скопируйте формулу из ячейки B28 в интервал ячеек C28:D28.

- отформатируйте информацию в ячейках В28:D28, задав процентный формат;

- в ячейку А33 (эта ячейка будет целевой) занесите формулу для подсчета квартального фонда зарплаты:

=3*B33+3*B33*(A21*B28+B21*C28)

- ячейка В33 будет являться ячейкой параметра. Целевая ячейка связана с ячейкой параметра формулой;

- сделайте активной вкладку Данные. В группе команд Работа с данными из списка кнопки Анализ “что-если” выберите пункт Подбор параметра;

- в диалоговом окне Подбор параметра задайте адрес целевой ячейки, ее значение, а также адрес ячейки параметра (рисунок 9.6):

 

Рисунок 9.6 Подбор параметра

 

В ячейке В33 будет рассчитано значение параметра - сумма месячных окладов. Отформатируйте это значение, оставив два знака после запятой (рисунок 9.7):

Рисунок 9.7 Вычисления подбора параметра

Контрольные вопросы.

1. Назначение команды Подбор параметра? Привести пример.

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

3. Что такое ячейкапараметра при использовании команды Подбор параметра?

4. Как должны быть связаны между собой целевая ячейка и ячейка параметра?

Литература[3, 7, 8]

Лабораторная работа №10

Тема: Основы программирования в интегрированной среде Visual Basic. Инструментальная среда разработки Visual Basic Application (VBA). Макрорекордер. Использование макросов.

Цель работы:

- научиться создавать, редактировать, удалять макросы;

- освоить работу с макросами.

Теоретические сведения

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

 

 

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

Модуль – совокупность описаний, инструкций и процедур, сохраненная под общим именем. Существуют модули двух типов: стандартный модуль и модуль класса Visual Basic.

Макрорекордер- это небольшая программа, встроенная в Excel, которая переводит любое действие пользователя на язык программирования VBA и записывает получившуюся команду в программный модуль. Если включить макрорекордер на запись, а затем начать создавать свой еженедельный отчет, то макрорекордер начнет записывать команды вслед за каждым действием и, в итоге, получается макрос создающий отчет, как если бы он был написан программистом. Такой способ создания макросов не требует знаний пользователя о программировании и VBA. У такого способа есть свои плюсы и минусы:

- макрорекордер записывает только те действия, которые выполняются в пределах окна Microsoft Excel. Закрытие Excel или переключение в другую программу приведет к остановке записи;

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

- если во время записи макроса макрорекордером была введена ошибка, она будет записана. Однако можно нажать на кнопку отмены последнего действия (Undo) - во время записи макроса макрорекордером она не просто возвращает предыдущее состояние, но и стирает последнюю записанную команду на VBA.

Создание макроса в среде Microsoft Visual Basic. Для ввода команд и формирования программы, т.е. создания макроса необходимо открыть специальное окно - редактор программ на VBA, встроенный в Microsoft Excel.

В старых версиях (Excel 2003 и старше) для этого нужно выбрать в меню Сервис - Макрос - Редактор Visual Basic (Toos - Macro - Visual Basic Editor).

В новых версиях (Excel 2007 и новее) для этого нужно сначала отобразить вкладку Разработчик (Developer). Выбирать Файл - Параметры - Настройка ленты (File - Options - Customize Ribbon) и включить в правой части окна флажок Разработчик (Developer). Теперь на появившейся вкладке будут доступны основные инструменты для работы с макросами, в том числе и нужная кнопка Редактор Visual Basic (Visual Basic Editor, рисунок 10.1).

Рисунок 10.1 Окно вызова редактора Visual Basic

 

Задание 1. На вкладке Разработка в группе Код выберите команду Visual Basic (рисунок 10.2).

Рисунок 10.2 Редактор Visual Basic

При необходимости в меню Insert выберите команду Module. Модули будут созданы автоматически для всех листов книги.

В окне программы модуля введите или скопируйте нужный текст макроса. Для запуска данного макроса из окна модуля нажмите клавишу F5.

Когда макрос будет создан, в меню File выберите команду Close and Return to Microsoft Excel.

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

- нажмите кнопку Microsoft Office , а затем щелкните Параметры Excel;

- в категории Личная настройка в группе Основные параметры работы с Excel установите флажок Показывать вкладку "Разработчик" на ленте, а затем нажмите кнопку ОК.

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

- на вкладке Разработчик в группе Код нажмите кнопку Безопасность макросов (рисунок 10.1);

- в группе Параметры макросов выберите переключатель Включить все макросы, а затем дважды нажмите кнопку ОК.

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

- на вкладке Разработчик в группе Код нажмите кнопку Запись макроса;

- в поле Имя макроса введите имя макроса.

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

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

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

В списке Сохранитьв выберите книгу, в которой необходимо сохранить макрос.

Если нужно, чтобы данный макрос был всегда доступен при работе в приложении Excel, выберите вариант Личная книга макросов. При выборе варианта Личная книга макросов создается скрытая личная книга макросов (Personal.xlsb) (если она еще не существует), в которой сохраняется данный макрос. В Microsoft Windows Vista эта книга сохраняется в папке C:\Users\имя_пользователя\Application Data\Microsoft\Excel\XLStart. В Microsoft Windows XP эта книга сохраняется в папке C:\Documents and Settings\имя_пользователя\Application Data\Microsoft\Excel\XLStart, откуда она будет автоматически загружаться при каждом запуске приложения Excel. Если необходимо автоматически выполнять макрос из личной книги в другой книге, необходимо также сохранить эту книгу в папке XLStart, чтобы при запуске приложения Excel открывались обе книги.

- введите описание макроса в поле Описание;

- для начала записи макроса нажмите кнопку ОК;

- выполните действия, которые нужно записать;

- на вкладке Разработчик в группе Код нажмите кнопку Остановить запись .

Можно также нажать кнопку Остановить запись слева от строки состояния.

Задание 3. Копирование части макроса для создания другого макроса

- откройте книгу, содержащую макрос, который нужно скопировать;

- на вкладке Разработчик в группе Код нажмите кнопку Макросы;

- в поле Имя макроса выберите имя макроса, который нужно скопировать;

- нажмите кнопку Изменить;

- в окне редактора Visual Basic выделите строки макроса, которые нужно скопировать. Для копирования макроса целиком включите в выделенную часть строки слова Sub и End Sub;

- в меню Edit выберите команду Copy. Можно также щелкнуть правой кнопкой мыши, а затем в контекстном меню выбрать команду Copy или нажать клавиши CTRL+C;

- в поле Procedure окна кода выберите модуль, куда нужно поместить текст;

- в меню Edit выберите команду Paste.

Можно также щелкнуть правой кнопкой мыши, а затем в контекстном меню выбрать команду Paste или нажать клавиши CTRL+V. Личную книгу макросов (Personal.xls) редактировать нельзя, поскольку она является скрытой книгой, которая всегда открыта. Сначала его необходимо отобразить с помощью команды Показать. Файл также можно открыть в редакторе Visual Basic, нажав клавиши ALT+F11.

Задание 4. Назначение макроса объекту, графическому объекту и элементу управления

- щелкните на листе правой кнопкой мыши объект, графический объект или элемент управления, которому нужно назначить существующий макрос, а затем в контекстном меню выберите команду Назначить макрос;

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

Задание 5. Удаление макроса. Выполните одну из процедур, описанных ниже:

- откройте книгу, содержащую макрос, который нужно удалить;

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

1) на вкладке Вид в группе Окно нажмите кнопку Отобразить окно;

2) в разделе Показать скрытое окно книги выберите пункт "PERSONAL" и нажмите кнопку ОК;

3) в списке Находится в выберите рабочую книгу с макросом, который требуется удалить. Например, выберите вариант Эта книга;

4) в поле Имя макроса выберите имя макроса, который нужно удалить;

5) нажмите кнопку Удалить.

Задание 6. Вывод на экран названий месяцев. При составлении различных отчетов и сводок в качестве названий строк часто используются названия месяцев. Создадим макрос, который будет выводить на экран столбец с названиями месяцев в ячейках A1:A12. Для этого выполните следующие действия:

- установите курсор в какую-нибудь ячейку книги, за исключением A1;

- откройте окно редактораVisual Basic;

- введите в поле ввода Имя макроса название создаваемого макроса Месяц_абс. В поле ввода Описание введите текст: Введите названия месяцев. Нажмите кнопку Параметры. Появится окно диалога “Запись макроса”;

- введите клавишу быстрого вызова макроса. Для этого перейдите в поле ввода Ctrl, переключитесь на латинский регистр, удалите находящуюся там букву и введите букву m. После этого нажмите кнопку OK. Начиная с этого момента, осуществляется запись макроса;

- выполните последовательность действий, которую будет выполнять макрос: установите курсор в ячейку A1; введите слово январь; поместите указатель мыши в правый нижний угол ячейки A1, при этом указатель мыши изменит вид н