Praktikum_2008

Рис. 3.39

§13. Использование Excel для управления базами данных

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

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

141

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

1.Имена полей (столбцов) должны быть указаны в верхней строке списка (строке заголовка).

2.Каждая запись размещается на отдельной строке.

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

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

Рис. 3.40

Операцию сортировки можно произвести с использованием команды Данные—› Сортировка и фильтр—›Сортировка. В

142

появившемся диалоговом окне следует выбрать параметры сортировки – столбец, порядок сортировки. Так как в таблице есть заголовки, нужно поставить флажок в Мои данные содержат заголовки (рис. 3.40). Результат сортировки представлен на рис.3.41.

Рис. 3.41

Рис. 3.42

143

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

Результат такой сортировки показан на рис. 3.43.

Рис. 3.43

Ещё один способ, облегчающий решение задачи выбора нужной записи, — использование фильтров. Выделяют столбец, по данным которого предполагается производить фильтрацию, например столбец «ФИО», и два раза щелкают по Фильтр (Дан-

ные—›Сортировка и фильтр—›Фильтр) (рис. 3.44) .

144

Рис. 3.44

Пусть пользователя интересуют фамилии Макаров и Сидоров. Нажав на кнопку списка, следует поставить флажки у нужных фамилий (рис. 3.45) и «ОК». После этого на экране останутся записи, содержащие информацию о Макарове и Сидорове (рис. 3.46). Все отфильтрованные записи отображаются на экране, и пользователь имеет возможность работать с ними, как с обычной таблицей Excel.

Рис. 3.45

Наряду с простым фильтром имеется ещё одна разновидность фильтрации – расширенный фильтр. Она даёт возможность произвести фильтрацию на части таблицы, а полученный результат перенести в заданное место, например на новый лист.

145

Рис. 3.46

Задание

Самостоятельно в таблицу «Мониторы» (рис. 3.5) добавьте еще одно устройство, например принтер, и столбец «код устройства», например 100 - код мониторов, 200 – код принтеров. Выполните отбор по кодам устройств и по ценам.

§14. Анализ данных в Excel с помощью сводных таблиц

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

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

Перечислим ряд вопросов, которые могут интересовать руководителя:

1.У какого отделения больше сумма продаж?

2.Какой тип проекта, какое отделение продает больше?

3.Как распределена сумма продаж по разным типам проектов

вотделениях?

Сводная таблица поможет ответить на эти вопросы.

146

Рис. 3.47

Создать сводную таблицу можно следующим образом:

Вставка →Таблицы → Сводная таблица (рис. 3.48 ).

Рис. 3.48

147

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

Рис. 3.49

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

148

Рис. 3.50

§15. Создание и использование макросов в Excel

Язык макрокоманд Visual Basic Application (VBA) – основной инструмент для настройки и автоматизации рутинных операций в

Excel.

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

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

149

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

Создаем макрос: Вид→Макросы→Запись макроса. Затем выполняем действия для построения графика: переходим на Лист2, щелкаем по кнопке Мастера диаграмм, переходим на Лист1 и в таблице выделяем данные для отображения на графике

– столбцы «Название монитора» и «Цена монитора» (рис. 3.5). Получаем график.

Для запуска макроса необходимо выполнить Вид→Макросы→ выбрать макрос и нажать Выполнить. На Листе2 будет построен график.

Для создания макросов также используют язык программирования VBA. Рассмотрим пример макроса для вычисления площади треугольника.

Сначала создадим макрос, выполнив следующие действия:

Вид→Макросы→Запись макроса→в диалоговом окне за-

дать имя макроса и сохранить (рис. 3.51).

Рис. 3.51

Остановить запись

(Вид→Макросы→Макросы→Остановить запись). Затем вы-

150