Klecova_Informacionnye_tehnologii_elektronnye_t

Кнопки

В таблице были созданы кнопки, отображающие следующие действия:

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

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

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

•построение диаграммы;

•сортировка данных по ФИО по алфавиту (рис.1.33);

•сортировка данных по начислено по убыванию (рис.1.34).

Рис.1.33

41

Рис.1.34

Диаграмма

Диаграмма была построена по столбцу Всего взносов

(рис.1.35.)

Рис.1.35

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

42

Рис.1.36

Рис. 1.37

В соответствии с заданием, ниже основной таблицы была создана транспонированная таблица (рис.1.36).

43

Для удобства работы с основной таблицей шапка таблицы была закреплена (рис. 1.37).

Заключение

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

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

1.В чем различие абсолютной и относительной адресации при создании формул и макросов?

2.Как вставить в ячейку функцию, встроенную в Excel?

3.Как задаются аргументы в интегрирующих функциях суммы, среднего, максимума, минимума?

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

5.Различаются ли форматы хранения и отображения дат и времени? Каков принцип хранения дат и времени?

6.С помощью каких команд меню можно начать запись макро-

са?

7.Для чего в Excel предусмотрены разные уровни безопасно-

сти?

8.Как создать кнопку на рабочем листе?

9.Как назначить на кнопку макрос?

10.Какие существуют варианты размещения диаграмм?

11.Какова последовательность сортировки данных?

12.Как сортируются пустые значения при сортировке по возрастанию? по убыванию?

Список литературы

1.Эффективная работа: Microsoft Office Excel 2003 / М. Додж,

К. Стинсон. СПб.: Питер, 2005.

2.Джеллен Б., Сирстад Т. Применение VBA и макросов в Microsoft Excel / Пер. с англ. и ред. А. В. Журавлева. М.: Вильямс, 2006.

3.Кошелев В.Е. Excel2007. М.: ООО «Бином-Пресс», 2008.

44

Работа 2

РАБОТА С ТАБЛИЦАМИ (БАЗОЙ ДАННЫХ) В ЭЛЕКТРОННЫХ ТАБЛИЦАХ

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

Задание на лабораторную работу

Импортировать данные из базы данных формата dbf.

Создать проверку заданных параметров при вводе. Изучить реакцию программы на ввод некорректных данных при выборе различных типов сообщений об ошибке.

Ввести три строки данных через форму данных.

Реализовать следующие макросы, привязав их к соответствующим кнопкам:

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

•Отменить итоги и получить исходную таблицу. Создать кноп-

ку Отменить итоги.

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

Фильтр.

•Создать макрос по отмене автофильтра. Создать кнопку От-

менить фильтр.

•Создать расширенный фильтр по заданным значениям. Поместить результаты расширенного фильтра на другой лист.

•Построить график по заданным значениям. Поместить график на отдельный лист.

45

Теоретические аспекты

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

лиц[3].

Таблица (база данных) в MS Excel отличается следующим:

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

•каждый столбец должен содержать информацию одного типа;

•под данные каждого типа, по которым будет осуществлены сортировка, поиск или другие операции, нужно отвести отдельный столбец;

•в базе данных не должно быть пустых строк или столбцов;

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

Excel идентифицирует данные как базу данных, если курсор стоит на заголовке первого столбца.

Для работы с таблицами используется специальная вкладка Ленты – Конструктор (Работа с таблицами).

Создание и удаление таблицы

Чтобы из диапазона данных создать таблицу, следует выделить необходимый диапазон данных, в разделе Таблицы вкладки Вставка щелкнуть кнопку Таблица (рис.2.1), а затем в открывшемся диалоговом окне Создание таблицы (см. рис.2.1) проверить диапазон данных в поле Расположение данных в таб-

1 В версии Microsoft Excel 2003 таблицы (базы данных Excel) назвались списками.

46

лице и нажать на кнопку ОК. Если у выбранных данных есть заголовки, необходимо установить флажок Таблица с заголовками и нажать кнопку OK. После этого выбранный диапазон данных выделится с помощью стиля форматирования, заданного по умолчанию. Одновременно на Ленте появится вкладка Конструктор (Работа с таблицами), которая содержит средства для внесения изменений в созданную таблицу (при выделении ячейки вне таблицы она «исчезает») [3]. На рис.2.2 представлена таблица из лабораторной работы №1 (см. рис 1.37.), преобразованная в «базу данных».

Таблицу также можно создать «с нуля» аналогичным образом.

Рис.2.1

Рис.2.2 47

Таблицу на рабочем листе можно преобразовать в обычный диапазон Microsoft Excel. Это сделает для него недоступными специальные возможности таблиц. Для этого в разделе Сервис

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

тором выбрать команду Таблица→Преобразовать в диапазон.

Данные в таблицу можно ввести в список тремя способами:

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

•импортировать или скопировать данные из другого файла;

•использовать форму ввода.

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

Импорт данных

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

Рис.2.3 48

При нажатии пункта меню Office→Открыть можно открывать внешне файлы различных форматов, данные из которых затем можно распределять по столбцам электронной таблицы (рис.2.3). Во всех случаях пункт меню Открыть меню Office импортирует внешние файлы целиком [1].

Чтобы открыть файл базы данных, например Visual FoxPro или dBase, необходимо выбрать пункт меню Office→Открыть и затем в диалоговом окне Открытие документа выбрать тип Файлы dBase в раскрывающемся списке типов файлов (см. рис.2.3). После этого в диалоговом окне будут отображаться только файлы с расширением dbf.

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

С помощью меню Office→Открыть файл базы данных dBase импортируется целиком в необновляемую таблицу Excel, т.е. изменения в файле базы данных dBase не будут отражены в импортированной таблице Excel.

Проверка данных

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

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

При создании контролирующего правила для ячейки или диапазона можно также задать подсказку, появляющуюся при активизации ячейки. Excel такую подсказку позволяет задать даже без соответствующего правила [1].

49

Чтобы установить критерий проверки, необходимо выделить диапазон ячеек и выбрать пункт меню Проверка в меню Дан-

ные. Откроется диалоговое окно Проверка вводимых значе-

ний (рис.2.4).

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

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

Рис.2.4

50