ЛАБОРАТОРНАЯ РАБОТА № 10
(Обязательно выполнить рассматриваемый Пример)
Тема: Базы данных в MS Excel
Цель работы: Изучение возможностей пакета MS Excel при работе с базами данных. Приобретение навыков создания и обработки БД.
Довольно часто возникает необходимость хранить и обрабатывать данные представленные в виде таблиц.
Информация, хранящаяся в таблицах, организована в виде строк и столбцов. Каждая строка таблицы, называемая записью, содержит данные об одном объекте. В столбце, называемом полем, содержатся сведения о каком-либо свойстве всех объектов хранящихся в таблице.
Т.о., можно сказать, что База данных (БД) - это совокупность различных записей, обладающих определенными свойствами.
В первой строке любой базы данных обязательно должны быть указаны имена полей. Максимальный размер базы данных в MS Excel определяется возможностями версии Excel (число строк и число столбцов в листе).
БД может быть сформирована на одном листе. Один лист может содержать несколько БД, но активной и доступной для выполнения различных операций в данный момент времени может быть только одна из них.
Рассмотрим процесс построения и обработки базы данных на следующем примере.
ПРИМЕР 10.1.
| Наименование товара | Дата | Поставщик | Город | Закупочная цена (руб) | Отпускная цена | Транспортные расходы | Количество | Прибыль |
| Колбаса | 02.10.03 | Колбико | Макеевка | 15,80 | 18,00 | 20 руб. | ||
| Творог | 05.10.03 | Ромол | Харьков | 2,20 | 2,50 | 45 руб. |
Сформируем БД на первом листе MS Excel, после чего лист переименуем (рис. 10.1).
Сформируем поле Прибыль. Для этого в ячейку I2 введем формулу:
I2=(F2-E2)*H2-G2
С помощью маркера заполнения скопируем эту формулу в ячейки I2:I18.
В результате лист примет вид, изображенный на рис. 10.2.
|
| Рис. 10.1. База данных "Поставщики товара" |
Рис. 10.2. Формирование поля "Прибыль"
|
Сортировка баз данных
Для сортировки базы установим курсор в область таблицы и выполним команду меню Главная 
Сортировка и фильтр Настраиваемая сортировка или меню Данные Сортировка и отсортируем таблицу по полю Наименование товара по возрастанию (алфавиту).
|
| Рис. 10.3. Диалоговое окно Сортировка диапазона |
Добавление итогов в базу данных
Предварительно скопируем нашу БД на второй лист и будем работать с копией на втором листе.
Прежде, чем формировать итоги по какому-либо полю, необходимо выполнить по нему сортировку. Посчитать суммарную прибыль при продаже всех продуктов можно при помощи функции СУММ. Если же воспользоваться пунктом меню Итоги, то появляется возможность рассчитать сумму прибыли по каждому товару или по каждому поставщику. Итак, для суммирования прибыли по каждому из товаров сделаем следующее:
Отсортируем БД по наименованию товаров (рис. 10.3) и выполним команду Данные Промежуточные итоги, просуммируем прибыль по каждому товару (рис. 10.4). Рабочий лист примет вид, изображенный на рис. 10.5.
|
| Рис. 10.4. Диалоговое окно Промежуточные итоги |
Рис. 10.5. Итог по прибыли по всем видам товара
|
Преобразовываем итоговую таблицу и строим круговую диаграмму (рис. 10.5а).
Рис. 10.5а
Фильтрация базы данных
Процесс поиска и отбора информации в базе данных MS Excel называется фильтрацией. В MS Excel режим фильтрации можно выбрать двумя способами: меню Главная®Сортировка и фильтр®Фильтр илименю Данные® Фильтр.
Вернемся на первую страницу книги MS Excel. Для включения фильтра необходимо:
- «Кликнуть» курсором мыши в любом месте базы данных, в нашем случае диапазон A1:I18.
- Выполнить команду Данные Фильтр. Щелкнуть по кнопке списка справа от нужного поля (например, поле Наименование товара). Окно БД примет вид, изображенный на рис. 10.6.
- В качестве условия отбора можно выбрать либо любое значение из списка (поставить «галочку») у нужного наименования или пункт Текстовые фильтры Настраиваемый фильтр.
|
| Рис. 10.6 |
Выберем в качестве условия значение Колбаса. В результате в БД останется информация, касающаяся только поставок колбасы (рис. 10.7).
|
| Рис. 10.7. Фрагмент отфильтрованной БД |
Результат будет тем же, если из списка предложенных фильтров выбрать Условие…, и в открывшемся диалоговом окне (рис. 10.8), ввести в качестве условия " равно Колбаса ".
|
| Рис. 10.8. Диалоговое окно Пользовательский автофильтр |
Кроме того, диалоговое окно Пользовательский автофильтр позволяет устанавливать различные условия фильтрации, выбрав их из предлагаемого списка (рис. 10.9).
|
| Рис. 10.9. Фрагмент диалогового окна Пользовательский автофильтр |
Расширенный фильтр
Для выделения из БД более сложных условий можно воспользоваться командой Данные Дополнительно Расширенный фильтр. Рассмотрим работу с расширенным фильтром на примере выделения из БД записей донецких производителей колбасы. Для этого определим область для хранения условий отбора. Каждое условие записывается в две ячейки: в верхнюю - имя поля, в нижнюю - знак отношения (>,<,>=,<=,< >) и значение. В нашем случае в ячейку K1 скопируем Наименование товара, в ячейку K2 - Колбаса, в ячейку L1 - Город, в ячейку L2 - Донецк (рис. 10.10).
|
| Рис. 10.10. Область для хранения условий отбора |
Теперь выполним команду Данные Дополнительно Расширенный фильтр (рис.10.11). В результате в БД останется информация, касающаяся только поставок донецких производителей колбасы (рис. 10.11a).
|
| Рис. 10.11. Диалоговое окно расширенный фильтр |
Рис. 10.11а
В данном случае два условия соединены логическим действием " И ", т.е. одновременно выполняется два условия.
Для объединения с помощью " ИЛИ " необходимо между именем поля и условием пропустить строчку (рис. 10.12). В этом случае в БД останется информация только по Донецку и всем производителям колбасы (рис. 10.12а).
|
Рис. 10.12.
 Рис. 10.12а.
|
При копировании отфильтрованных данных в другое место необходимо, чтобы копируемый диапазон начинался со строки, в которой указываются имена полей БД.
Сводная таблица
Сводные таблицы - одно из наиболее мощных средств Excel по работе с базами данных. Они полезны как для анализа, так и для обобщения информации, хранящейся в БД.
Создадим из нашей БД сводную таблицу для расчета прибыли по каждому товару. Выполним команду Вставка Сводная таблица. В первом диалоговом окне Мастер сводных таблиц (рис. 10.13) указывается вид создаваемого отчета - сводная таблица или сводная диаграмма.
|
| Рис. 10.13. Первое диалоговое окно Мастер сводных таблиц |
В поле Таблица или диапазон второго диалогового окна Мастер сводных таблиц (рис. 10.14) необходимо указать диапазон, на основании которого строится сводная таблица. В нашем случае: 'Поставщики товара'!$A$:$I$18. Необходимо так же установить переключатель в положение, указывающее, где будет размещена сводная таблица - на новом листе или на уже существующем.
| Рис. 10.14. Второе диалоговое окно Мастер сводных таблиц После установки параметров и нажатия клавиши «ОК» произойдет автоматический переход на следующий лист книги (рис. 10.15) |
| Рис. 10.15. Третье диалоговое окно Мастер сводных таблиц |
Поля БД, на основании которой строится сводная таблица, представлены в правой части окна. Отмечая необходимые поля и перетаскивая в нужные области (Название строк/Название столбцов) пользователь задает необходимую структуру сводной таблицы (рис. 10.16).
В рассматриваемом примере создается сводная таблица, состоящая из строк с наименованием товара и столбцов с названием города из которого этот товар доставлен. Отмечаем нужные поля: Наименование товара; Город; Прибыль. Поле Город попадает в область «Название строк». С помощью мыши «перетащим» это поле в область «Названия столбцов».
Допустимыми операциями подведения итоговявляются: сумма, количество значений, среднее арифметическое, максимальное и минимальное значение, произведение, количество чисел, несмешанное и смешанное отклонение, несмешанная и смешанная дисперсия. В нашем примере в качестве допустимой операции выбрана сумма. Смены и выбор новой операции показаны на рис. 10.17.
Рис. 10.16. Диалоговое окно для создания макета сводной таблицы
|
 
|
| Рис. 10.17. Диалоговое окно Параметры поля значений |
| После оформления таблицы на рабочем листе будет создана сводная таблица, представленная на рис. 10.18. |
Рис. 10. 18. Сводная таблица подсчета прибыли по каждому товару
По сводной таблице строим столбиковую диаграмму (рис. 10.19).
Рис. 10.19
|
ВАРИАНТЫ ЗАДАНИЙ
Общие требования ко всем заданиям:
1. Создайте табличный документ и сохраните его в личной папке.
2. Дополните таблицу данными и формулами в соответствии с условием задания (в таблице должно быть не менее 12 записей).
3. Заполните или рассчитайте нужные поля, согласно заданным условиям в Вашем варианте.
4. Используя функцию Промежуточные итоги…, заполнить поля отмеченные звездочкой и постройте круговую диаграмму.
5. Установите фильтры в соответствии с вариантом задания.
6. Получите сводные таблицы для исходной и отфильтрованной таблиц по нескольким показателям с помощью мастера сводных таблиц. Строки и столбцы для сводных таблиц выберите самостоятельно. Сохраните этот вариант.
7. Переставьте местами строки и столбцы сводных таблиц (сохраните оба варианта).
8. Создайте столбиковые диаграммы по сводным таблицам.
9. В столбцах, отражающих цену и стоимость товара, формат ячеек должен быть денежным.
