Лекция 9. Работа с базами данных в MS Excel. Создание макросов

Основные понятия и термины

MS Excel может работать как с простыми и небольшими по размерам, так и с более сложными, занимающими большой объем дискового пространства списками данных. Эти списки часто называются базами данных. Однако в MS Excel база данных – это список, состоящий из одного или более столбцов.

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

Использование формы данных для ввода новых данных

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

Для этого:

1. Выделить ячейку внутри списка.

2. Выбрать команду Данные\Форма\Добавить.

3. Ввести данные в соответствующие поля. Для быстрого перемещения от поля к полю используется клавиша TAB. Нажатие клавиши ENTER эквивалентно нажатию кнопки Добавить. Поэтому после ввода последнего поля следует нажать ENTER.

4. Чтобы вернуться к рабочему листу следует нажать кнопку Закрыть.

5. Чтобы вычисляемые поля не занимали место в форме данных, перед выбором команды Данные\Форма следует скрыть столбцы с формулами.

В форме данных для просмотра записей используется полоса прокрутки.

Использование формы данных для поиска данных и редактирования

Чтобы с помощью формы данных найти необходимые записи, нужно:

1. Выделить какую-либо ячейку списка.

2. Выполнить команду Данные\Форма\ кнопка Критерии.

3. Перейти к полю, которое будет участвовать в определении критерия, и ввести критерий поиска. Перейти к следующему полю, если для поиска необходим не один критерий.

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

Для редактирования данных в базе данных следует открыть форму данных, задать критерий и перейти к первой, удовлетворяющей критерию, записи. Отредактировать поля этой записи. При необходимости отменить сделанные изменения до перехода к другой записи нужно нажать кнопку Вернуть. Можно ввести новый критерий и отредактировать другие записи. Можно удалить найденную запись (кнопка Удалить). Удаленные записи не могут быть восстановлены. Чтобы сохранить последние изменения в базе данных и вернуться к рабочему листу следует нажать кнопку Закрыть.

Сортировка данных

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

При выборе команды Данные\Сортировка появляется диалоговое окно. Выбор в списках Сортировать по, Затем по, В последнюю очередь по определяет поля, используемые для упорядочения списка.

Выбор в списках Сортировать по, Затем по, В последнюю очередь по определяет поля, используемые для упорядочения списка. Порядок действий при сортировке:

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

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

3. Выполнить Данные\Сортировка.

4. В раскрывающемся списке Сортировать по выбрать первое поле, по которому следует упорядочивать данные. Это поле также называется первым ключом сортировки.

5. Выбрать переключатель по возрастанию или по убыванию.

6. Второе поле сортировки Затем по используется если возникает повторение значения первого поля. А третье поле сортировки В последнюю очередь по используется, если повторяется значение и первого и второго поля. При использовании этих полей также устанавливается переключатель по возрастанию или по убыванию.

7. Нажать ОК.

Создание промежуточных итогов

1. Выделить ячейку базы данных, выполнить команду Данные\Итоги. Появится диалоговое окно Промежуточные итоги.

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

3. Выбрать операцию, выполняемую над данными.

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

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

6. Чтобы выводились только общие итоги следует в диалоговом окне Промежуточные итоги отключить опцию Итоги под данными.

Чтобы удалить промежуточные итоги следует выполнить команду Данные\Итоги\Убрать все.

С помощью структуры списка можно вывести:

1. Только общие итоги (кнопка 1 слева от строки с наименованиями столбцов)

2. Общие и промежуточные итоги (кнопка 2).

3. Полный список (кнопка 3).

Поиск данных с помощью Автофильтра

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

Выделить ячейку внутри списка. Выполнить команду Данные\Фильтр\Автофильтр.

Раскрыть список, соответствующий полю, которое следует включить в критерий.

Выбрать желаемый критерий, а именно: Все, Первые 10, Условие.

Например, чтобы показать данные только по автомобилю Порше 911 (лаб. раб.8, п.9.) следует:

– раскрыть список, соответствующий полю Товар (кнопка со стрелкой справа от названия поля);

– выбрать строку Условие;

– в диалоговом окне Пользовательский Автофильтр указать равно Порше 911.

Чтобы отобразить полный список следует выполнить команду Данные\Фильтр\Отобразить все.

Чтобы снять Автофильтр со списка следует выполнить Данные\Фильтр\Автофильтр (снять галочку)

Расширенный фильтр

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

1. Если несколько условий введено в одной строке, показаны будут только те записи, которые удовлетворяют всем условиям одновременно (функция И()) (рис. 10.2).

	КОД ЗАКАЗА	ТОВАР	ЦЕНА	КОЛИЧЕСТВО	СУММА
			<10000	>10

Рис. 10.2. Пример записи условия с использованием логической функции И()

2. Если по одному условию введено в разных строках, то показаны будут те записи, которые удовлетворяют хотя бы одному условию (функция ИЛИ ()) (рис. 10.3).

КОД ЗАКАЗА	ТОВАР	ЦЕНА	КОЛИЧЕСТВО	СУММА
	Пежо
		<10000
			>10

Рис. 10.3. Пример записи условия с использованием логической функции ИЛИ()

Порядок поиска данных с помощью Расширенного фильтра:

1. В диапазоне критериев ввести условия поиска.

2. Выделить ячейку внутри списка (базы данных) и выполнить команду Данные\Фильтр\Расширенный фильтр. Появится диалоговое окно Расширенный фильтр.

3. Установить параметр в группе Обработка. При выборе переключателя Фильтровать список на месте не удовлетворяющие критерию записи будут скрыты. При выборе переключателя Скопировать результат в другое место список останется нетронутым, а соответствующие критерию поиска записи будут помещены в указанный пользователем диапазон.

4. В поле Исходный диапазон должен быть указан диапазон базы данных, включающий и заголовок и все данные базы.

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

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

7. Нажать ОК.

Создание макросов

Макрорекодер – это средство EXEL, которое записывает все производимые над рабочим листом действия и интерпретирует их как последовательность команд Visual Basic. Эти команды формируют процедуру (макрос), которую можно выполнить, чтобы повторно произвести записанные в ней действия.

Порядок действий:

1. Выполнить команду Вид\Панели инструментов\Форма.

2. Выполнить щелчок по кнопке с именем Кнопка. Курсор примет вид крестика. Нарисовать кнопку на рабочем листе. Кнопку можно также нарисовать, используя панель инструментов Рисование.

3. Закрыть диалоговое окно Назначить макрос объекту.

4. Выполнить команду Сервис\Макрос\Начать запись. Откроется диалоговое окно Запись макроса. В нем следует указать имя макроса, например, Переход и нажать кнопку ОК.

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

6. Выполнить команду Сервис\Макрос\Остановить запись.

7. По нарисованной кнопке выполнить щелчок правой кнопкой мыши и выполнить Назначить макрос.

8. В диалоговом окне Назначить макрос объекту выбрать макрос с назначенным в пункте 4 именем (Переход).

9. По нарисованной кнопке выполнить щелчок правой кнопкой мыши и выполнить команду Изменить текст. Присвоить кнопке текст, отражающий действие данной кнопки (например, Переход на лист Баланс или Баланс).

10. Выполнить по кнопке щелчок указателем мыши (в виде пальчика) и проверить правильность работы записанного макроса.

Приложение А

Основные понятия
Рабочее поле	Состоит из строк и столбцов. Максимальное количество строк - 16384, столбцов - 256. Каждое пересечение строки и столбца образует ячейку.
Номер строки	Определяет ряд в электронной таблице, обозначен на левой границе рабочего поля
Буква столбца	Определяет колонку в электронной таблице. Буквы находятся на верхней границе рабочего поля. Колонки нумеруются от A-Z, затем AA-AZ, затем BA-BZ и т.д.
Ячейка	Первичный элемент таблицы, содержащей данные. Каждая ячейка имеет уникальный адрес, состоящий из буквы столбца и номера строки.
Указатель ячейки	Светящийся прямоугольник, определяющий текущую ячейку.
Активная (текущая) ячейка	Помечена указателем. По умолчанию ввод данных и некоторые другие действия относятся к текущей ячейке.
Блок	Представляет собой прямоугольную область смежных ячеек.
Адрес блока	Состоит из координат противоположных углов блока, разделенных двоеточием.
Абсолютная адресация	При копировании формул позволяет сохранить ссылку на конкретную ячейку ($A$2,$R$4 и т.д.)
Относительная адресация	При ее использовании в формулах MS Excel запоминает расположение относительно текущей ячейки (A12, N7 и т.д.)
Смешанная адресация	При копировании формул один параметр адреса изменяется, а другой - нет ($G5, A$18 и т. д.)

Элементы интерфейса и окна приложения Microsoft Excel

Элемент	Описание
Кнопка управления окном приложения	Значок открытого в окне приложения; раскрывает системное меню приложения
Строка меню	Список команд меню, расположенный под заголовком приложения
Меню	Раскрывающийся список команд
Вешка горизонтальной и вертикальной разбивки окна	Двойная линия в конце полосы прокрутки, которую можно перетащить на другую позицию полосы, чтобы разделить окно на две области для независимого просмотра одного и того же документа
Строка состояния	Строка в нижней части окна приложения, в которой поясняются выделенные команды или даются указания и инструкции
Ярлычки рабочих листов	Ярлычки с именами рабочих листов рядом с полосой горизонтальной прокрутки; позволяют выбрать определенный лист в рабочей книге
Кнопки прокрутки ярлычков	Кнопки слева от ярлычков рабочих листов, помогают быстро перебирать листы рабочей книги
Вешка разбивки ярлычков	Двойная линия между ярлычками и полосой горизонтальной прокрутки. Перетаскивается влево или вправо для настройки границы между областью отображения ярлычков и областью отображения полосы горизонтальной прокрутки
Панель инструментов	Панель, содержащая кнопки, обеспечивающие быстрый доступ к таким командам и процедурам, как выбор шрифта, построение диаграммы, рисование и т.д.
Строка формул	Строка, в которую вводится текст, числа или формулы; расположена ниже строки меню или панели инструментов
Строка состояния	Строка внизу окна приложения, поясняющая действия, которыеMS Excel готов выполнить по указанной команде

Способы ссылки на группы ячеек

Обозначение	Группа ячеек
F3	Ячейка на пересечении столбца F и строки-3
Е10:Е20	Ячейки с 10-й по 20-ю в столбце Е
В15:Е15	Ячейки с В по Е в строке 15
5:5	Все ячейки строки 5
5:10	Все ячейки строк с 5-й по 10-ю
В:В	Все ячейки столбца В
B:J	Все ячейки столбцов c B no J
А10:Е20	Прямоугольная область пересечения строк с 10-й по 20-ю и столбцов с А по Е

Сообщения об ошибках ввода

Сообщение	Описание ошибки
#####	Ширина ячейки недостаточна для отображения результата вычисления или отрицательный результат вычислении в ячейке, отформатированной как данные типа даты или времени
#ЗНАЧ (#VALUE!)	Неверный тип аргумента или операнда. Например, указание в качестве аргумента ячейки с текстом, когда требуется число
#ДЕЛ/0(#01У/0!)	Деление на 0
#ИМЯ (#NAME!)	MS Excel не может распознать текст, введенный в формулу, например неверное имя функции
#Н/Д (#N/A)	Данные ячейки одного из аргументов формулы в данный момент недоступны
*#ССЫЛКА (#REF!)	Неверная ссылка на ячейку
#ЧИСЛО(#МиМ!)	Невозможно вычислить результат формулы, либо он слишком велик или мал для корректного отображения в ячейке
#ПУСТО (#NULL!)	Результат поиска пересечения двух непересекающихся областей, то есть неверная ссылка

В MS Excel используется четыре вида операторов: арифметические, текстовые, операторы сравнения и операторы ссылок.

Арифметические операторы

Оператор	Назначение	Пример	Результат
+	Сложение	5+5
-	Вычитание	6-1
*	Умножение	4*3
/	Деление	7/2	3,5
%	Процент	50%	0,5
^	Возведение в степень	5^2

Операторы сравнения (условные операторы)

Оператор	Назначение	Пример	Результат
=	Равно	5=8	ЛОЖЬ
>	Больше	8>5	ИСТИНА
<	Меньше	5<6	ИСТИНА
>=	Больше или равно	6>=3	ИСТИНА
<=	Меньше или равно	7<=1	ЛОЖЬ
<>	Не равно	1<>1	ЛОЖЬ

Текстовый оператор объединения

Оператор	Назначение	Пример	Результат
&	Объединяет несколько текстовых строк в одну	“Генеральный” & “секретарь”	Генеральный секретарь

Операторы ссылки

Оператор	Значение	Пример
: (двоеточие)	Оператор диапазона используется для ссылки на все ячейки между крайними ячейками диапазона, включая эти ячейки	СУММ(D5:D10)
; (точка с запятой)	Оператор объединения объединяет несколько ссылок в одну ссылку	СУММ(A5;A10;A15)

Дата добавления: 2014-01-06