Работа с базами данных в MS Excel

ЛЕКЦИЯ 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).

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

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

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

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

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

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

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

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

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

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

7. Нажать ОК.

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

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

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

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

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

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

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

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

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

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

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

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

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

Приложение А

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

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

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

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

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

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

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

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