Работа со списками – БАЗАМИ ДАННЫХ в MS Excel.

Представление ЭТ в виде списка

СПИСОК – это один из способов организации данных на рабочем листе. Данные, организованные в список, в терминах Excel часто называются Базой Данных (БД). При этом строки таблицы называются записями, а столбцы – полями. Имена полей должны располагаться в первой строке списка, причем каждое имя – в одной ячейке.

Ввод, просмотр и редактирование списка

Для ввода, просмотра и редактирования списка удобно использовать Форму данных: Данные - Форма

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

Для внесения исправлений также использовать форму.

1. Вызвать диалоговое окно командой меню Данные, Форма.

2. Нажать кнопку Критерии . При этом поля формы очистятся, а название кнопки Критерии заменится на Правка .

Для удаления записи из списка сначала ее нужно найти, а затем удалить, нажав кнопку Удалить .

Выбор данных из БД (Организация различных запросов)

С помощью фильтров можно выводить на экран и просматривать данные списка. Быстро и удобно можно просматривать нужные записи с помощью автофильтра. Более сложные запросы к базе данных можно реализовать с помощью расширенного фильтра.

Чтобы установить автофильтр, следует:

· Выделить область БД;

· Задать Данные – Фильтр - Автофильтр

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

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

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

· выделить область БД;

· задать Данные – Фильтр – Расширенный фильтр;

· в диалоговом окне «Расширенный фильтр» в поле Исходный диапазон: указать интервал ячеек, содержащий область БД

· в поле Диапазон условий: указать интервал ячеек, содержащий область критериев;

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

· нажать кнопку ОК.

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

Для вычисления итогов следует выполнить следующие действия:

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

· Выделить область БД и выполнить команду меню Данные, Итоги.

· В открывшемся диалоговом окне Промежуточные итоги выполнить следующие действия:

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

• В поле ввода Операция по умолчанию будет выбрана функция Сумма (или выбрать нужную функцию)

• В поле ввода Добавить итоги по выбрать поля, по которым подводятся итоги

• Оставить отметки для флажка Заменить текущие итоги и для флажка Итоги под данными.

• Нажать кнопку ОК .

На экране появятся строки с промежуточными итогами для каждой группы записей и общий итог. Обратите внимание на кнопки, которые появились слева от таблицы. Кнопки с цифрами 1, 2, 3 обеспечивают степень детализации показа данных в таблице: кнопка 3 – вся таблица с промежуточными итогами и общим итогом;

кнопка 2 – только итоги: промежуточный и общий; кнопка 1 – только общий итог. Той же цели служат и кнопки со знаками + и –. Нажать на каждую из кнопок и посмотреть, как свернулась структурная схема слева. После этого развернуть весь список со всеми итогами, нажав кнопку 3 .

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

Защита данных

Для защиты данных от изменений, а также возможности скрыть формулы следует использовать команды:

1 Сервис, Защита, Защитить лист (Сервис, Защита, Снять защиту листа).

2. Выделить ячейки, содержимое которых не может меняться. Выполнить команду меню Формат, Ячейки, вкладка Защита: установить флажок Защищаемая ячейка. (Для обратных действий- Формат, Ячейки, вкладка Защита: сбросить флажок Защищаемая ячейка.

3. Выделить столбцы, формулы в которых должны быть скрыты. Выполнить команду меню Формат, Ячейки, вкладка Защита установить флажки Защищаемая ячейка и Скрыть формулы.

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

Работа со списками (Базами данных)

Теоретический материал: представление электронной таблицы (ЭТ) в виде списка; средства обработки списков в Excel: сортировка данных, отбор записей из списка, формирование промежуточных итогов.

1. Вызвать программу Excel. Создать новую рабочую книгу.

2. На Листе 1 создать таблицу «Урожайность и себестоимость овощей»

Урожайность и себестоимость овощей
Овощи	Отделение	Посевная площадь, га	Урожайность, ц/га	Затраты, млн.руб.	Ввало-вой сбор, ц	Затраты, тыс.руб.
						на 1 га	на 1 ц
Капуста ранняя	I	130	180,5	234,4
Капуста поздняя	I	103	272,5	169,0
Огурцы	II	80	39,1	86,0
Помидоры	II	40	19,3	23,0
Свекла	I	120	383,4	298,0
Морковь	I	93	290,5	232,0
Прочие	II	90	76,5	136,6
Итого
В среднем

Для расчета значений столбцов «Валовой сбор, ц» и «Затраты, тыс.руб.» использовать формулы:

Валовой сбор = (посевная площадь, га) * (урожайность, ц/га);

;

.

Рассчитать показатели «Итого» и «В среднем» при помощи встроенных статистических функций СУММ и СРЗНАЧ.

3. Преобразовать исходную таблицу в базу данных (рис.1):

Рис.1. Представление ЭТ в виде списка

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

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

6. Используя расширенный фильтр, отобрать овощи, урожайность которых менее 100 ц/га, а площадь посева более 50 га.

Для использования расширенного фильтра следует:

· создать область критериев на том же рабочем листе, что и база данных. Для её формирования следует скопировать в свободное место рабочего листа имена полей, по которым будет выполняться фильтрация данных, а затем в ячейках под соответствующими именами полей ввести сами критерии (рис.2. А15:В16);

Рис.2. БД, область критериев и записи, отобранные расширенным фильтром

· выделить область БД;

· задать Данные – Фильтр – Расширенный фильтр;

· в диалоговом окне «Расширенный фильтр» в поле Исходный диапазон: указать интервал ячеек, содержащий область БД (рис.3);

· в поле Диапазон условий: указать интервал ячеек, содержащий область критериев (рис.3);

· в группе Обработка определить, будет ли фильтрация списка выполняться на том же месте или результат будет помещен в другое место рабочего листа. В последнем случае в поле Поместить результат в диапазон: задать интервал ячеек, где предполагается разместить отфильтрованные записи (рис.3);

· нажать кнопку ОК.

Рис.3. Диалоговое окно Расширенный фильтр

7. По базе данных, используя функцию ДМАКС, определить максимальную урожайность. Формат записи статистических функций баз данных: имя _функции (база_данных; поле; критерий),

где: база_данных – диапазон ячеек БД, включающий имена столбцов;

поле – имя столбца (в кавычках), номер столбца или адрес ячейки с именем столбца БД;

критерий- диапазон ячеек, содержащий условие отбора. Диапазон включает имя/имена столбца(ов) и ячейку(и) с условием(ями) отбора (рис.4.)

Рис.4. Диалоговое окно функции ДМАКС

8. Используя расширенный фильтр (рис.5), отобрать из базы данных запись с максимальной урожайностью (предварительно создав область критериев- ячейки А24:А25 на рис. 6):

Рис.5. Диалоговое окно «Расширенный фильтр»

Рис.6. Область критериев (А24:А25) для расширенного фильтра и результат его использования (А26: Н27)

9. Используя расширенный фильтр, отобрать из базы данных записи с урожайностью выше среднего уровня (область критериев – ячейки D15:D16 -рис. 2), результат отбора представлен на рис.7.

Рис.7. Отбор записей с урожайностью овощей выше среднего уровня (180,26 ц/га)

10. Используя функцию СЧЕТ, определить количество записей с урожайностью выше среднего уровня – рис.8 (результат показан в ячейке F15).

.

Рис.8. Диалоговое окно функции БСЧЕТ

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

· выделить область БД;

· выполнить команду Данные-Итоги:

· в диалоговом окне Промежуточные итоги указать имя поля Отделение, при каждом изменении значений в котором нужно подсчитать итоги;

· в списке Операция: выбрать нужную функцию для вычисления итогов (выбрать Сумма);

· в списке Добавить итоги по: определить по каким полям предполагается подвести итоги (Посевная площадь, Валовой сбор, Затраты).

Итоговая таблица должна иметь следующий вид – рисунок 9. Чтобы убрать все итоги, выделить любую ячейку списка, выполнить команду меню Данные, Итоги и в диалоговом окне Промежуточные итоги нажать кнопку Убрать все.

Рис.9 . База данных с итоговыми вычислениями

12. Обеспечить защиту данных листа, а также скрыть формулы в столбцах G и H. Выделить эти столбцы и выполнить команды Формат, Ячейки, вкладка Защита: установить флажки Защищаемая ячейка и Скрыть формулы. Далее выполнить команду меню Сервис – Защита – Защитить лист.