Лабораторная работа
Базы данных в Excel
Текущая и две последующие лабораторные работы посвящены изучению средств и методов работы с таблицами данных предоставляемых табличным процессором Excel. Работы могут проводиться на компьютерах с различными операционными системами. Поэтому большая часть рисунков представлена как для ОС Windows так и для ОС Mac-OS-X. Рисунки для ОС Mac-OS-X имеют те же номера что и для ОС Windows но завершаются пометкой «mac».
Цель занятия.
Изучение ввода данных, поиска данных, сортировки и фильтрации данных, подведение промежуточных итогов по данным базы.
Теоретическое обоснование.
Таблицы Excel можно использовать в качестве баз данных. В этом случае применяется следующая терминология: столбцы таблицы называются полями, верхняя строка – заголовками полей, остальные строки – записями (рис.1).
Рис.1
Основными задачами при работе с базами данных являются:
- ввод данных, поиск данных, сортировка и фильтрация данных, подведение промежуточных итогов по данным базы.
Эти операции доступны через пункт меню ДАННЫЕ (DATA) (рис.2).
Рис.2
Рис.2-mac
Ввод данных в таблицу можно осуществлять обычным заполнением или копированием ячеек, так и используя режим формы. Вызов формы осуществляется следующим образом:
Выделяется любая ячейка таблицы
Выбирается пункт меню: ДАННЫЕ -> Форма (DATA-Form…).
На экране появится форма, приведенная на рис.3.
Рис.3
Рис.3-mac
Перемещение по записям:
Стрелки вверх и вниз.
Переход к первой или последней записи:
Ctrl+стрелка вверх, Ctrl+стрелка вниз.
Перемещение по полям формы:
Клавиши TAB и SHIFT+TAB.
Добавление или удаления записи:
Кнопки Добавить (New) и Удалить (Delete).
Копирование данных из поля предыдущей записи:
Ctrl+кавычки (клавиша с буквой Э).
Поиск данных (записей) по критерию.
Этот режим позволяет отображать в форме только те записи, которые удовлетворяют заданному критерию.
Переход в режим поиска записей по критерию осуществляется выбором команд: ДАННЫЕ -> Форма -> Критерии (DATA-Form…-Criteria). Затем следует задать в полях условия поиска. При этом можно использовать следующие символы-заменители:
? - заменяет один символ,
* - заменяет любое количество символов,
< > <= >= = используются для записи условий.
После задания критерия нажатие кнопок Назад и Далее будет приводить к переходам только на те записи, которые удовлетворяют заданным условиям.
Для нашего примера таблицы (рис.1) при задании критериев как на рис.4 отобрана будет только 1-я запись (рис.3).
Рис.4
Для упорядочивания данных служит операция сортировки: ДАННЫЕ -> Сортировка… (DATA-Sort…)
Перед вызовом команды надо поставить курсор в одно из полей таблицы. После выбора команды ДАННЫЕ -> Сортировка (DATA-Sort…) откроется диалоговое окно рис.5.
Рис.5
Рис.5-mac
Как видно в этом окне можно задать три критерия сортировки. В полях Сортировать по (Sort by), Затем по (Then by), В последнюю очередь по (Then by) задаются названия сортируемых полей. Справа размещены переключатели направления сортировки: по возрастанию (Ascending) – от первой буквы алфавита к последней, от меньшего числа к большему, от более ранней даты к более поздней и по убыванию (Descending)– в обратном направлении. Переключатели Идентифицировать поля по подписям и Идентифицировать поля по обозначениям позволяют соответственно включать и не включать верхнюю строку таблицы в процессе сортировки. Кнопка Параметры открывает диалоговое окно, позволяющее установить последовательность нестандартной сортировки.
Для базы представленной на рис.1 зададим критерии сортировки рис.6.
Рис.6
Рис.6-mac
Сортировка будет осуществляться по полю Жанра затем Цены и в последнюю очередь по Количеству заказанных экземпляров. Результат сортировки представлен на рис.7.
Рис.7
Рис.7-mac
Для +задаваемым условиям и перевода остальных в скрытое состояние служит фильтрация данных: ДАННЫЕ->Фильтр->Автофильтр (Data-Filter-AutoFilter) или ДАННЫЕ->Фильтр->Расширенный фильтр… (Data-Filter-Advanced Filter…)
Рассмотри сначала работу с автофильтром. При выборе команды ДАННЫЕ->Фильтр->Автофильтр (Data-Filter-AutoFilter) в строке названий полей размещаются кнопки раскрывающихся списков (рис.8). С помощью этих кнопок в выпадающих окнах можно задавать критерии отбора строк (фильтрации). При задании критериев в нескольких полях они объединяются по принципу логического умножения.
Рис.8
Рис.8-mac
Кроме выбора одного критерия в выпадающем окне (например, ЖЗЛ на рис.8) можно выбрать пункт Условие (Custom Filter…). В этом случае откроется окно, в котором можно задать более сложный критерий выбора для текущего поля. На рис.9 для поля Цена задан диапазон цен больше 80 и по 240 рублей.
Рис.9
Рис.9-mac
Результат выполнения сложного условия фильтрации представлен на рис.10.
Рис.10
Кнопки полей, для которых выполнена фильтрация, выделяются синим цветом. Чтобы отменить автофильтр снимите флажок Автофильтр (Data-Filter-AutoFilter) (повторное выполнение команды ДАННЫЕ->Фильтр->Автофильтр).
Теперь рассмотрим использование расширенного фильтра (ДАННЫЕ->Фильтр->Расширенный фильтр…) (Data-Filter-Advanced Filter…)для более сложных критериев фильтрации.
Возможности расширенной фильтрации рассмотрим на примере решения следующей задачи.
На основе имеющейся базы (таблицы) нам надо сформировать таблицу из полей Заказчик, Адрес заказчика, Автор, Название, Жанр с записями соответствующими условиям:
заказ оформлен в период 04.01.2000-30.05.2000 на книги издательства Молодая гвардия при цене книги более 50 рублей, а также (или) заказ оформлен в период 02.02.2000-14.02.2000 на книги издательства Чужое ЭХО а также при цене книги менее 400 рублей, а также (или) все заказы, оформленные в период 01.05.2000-30.05.2000, а также (или) все заказы на книги Издательства Крымский мост-9Д.Для решения задачи необходимо выполнить следующие действия:
Создать область критерия. Для этого заполнить ячейки как показано на рис.11.
Рис.11
Здесь столбцы соответствуют критериям, отобранным для фильтрации. Каждая строка описывает соответствующее условие из четырех условий фильтрации сформулированных выше. Условия, записываемые в одной строке, объединяются условием И. Условия, записанные в разных строках, объединяются условием ИЛИ.
Такую таблицу удобно расположить на отдельном листе. Мы так и сделаем. Создадим такую таблицу на листе 2.
Далее на том же листе где находится база справа или ниже ее заполняем ячейки именами полей таблицы, которая будет создана после фильтрации (Заказчик, Адрес заказчика, Автор, Название, Жанр). Мы сделаем это в области расположенной справа от базы.
Рис.12
Устанавливаем курсор на любую ячейку базы данных и вызываем диалоговое окно Расширенный фильтр (ДАННЫЕ->Фильтр->Расширенный фильтр…) (Data-Filter-Advanced Filter…) (Рис. 13).
Рис.13
Рис.13-mac
Устанавливаем переключатель скопировать результат в другое место. В этом случае мы сможем видеть и исходную базу и таблицу с отфильтрованными данными. Задаем исходный диапазон (соответствует координатам базы). Задаем диапазоны условий и размещения результата. Для понимания координат см. рис.11 и рис.12.
Нажимаем OK и получаем результат представленный на рис.14.
Рис.14
Теперь перейдем к рассмотрению процесса получения итогов по данным базы. Эту процедуру рассмотрим на примере решения конкретной задачи. Но прежде чем сформулировать эту задачу внесем некоторые изменения в базу данных. После поля количество, обозначающее количество книг в заказе, добавим поле сумма, обозначающее сумму заказа. В ячейки записей на пересечении с полем Сумма вставим формулу произведения содержимого ячеек полей Цена и Количество. Для этого, предварительно выделив ячейку первой записи поля Сумма в поле ввода формулы введем формулу =I3*J3; в ячейку поля Сумма второй записи введем формулу =I4*J4 и т. д. Для ускорения этого процесса после ввода формулы в ячейку первой записи можно выделить эту ячейку однократным нажатием и отпусканием левой кнопки мыши, затем поставить курсор мыши на правый нижний угол ячейки и нажав левую кнопку мыши тянуть ее вниз до конца таблицы. Произойдет копирование формулы во все ячейки. В результате будем иметь таблицу как на рис.15.
Рис.15
Теперь сформулируем задачу получения итогов по базе данных. Требуется получить результаты продаж книг в натуральном и денежном эквиваленте по книгам каждого жанра отдельно и вместе. Для решения данной задачи вначале отсортируем порядок записей по полю Издательство (ДАННЫЕ -> Сортировка…) (Data-Sort…). Результат на рис.16.
Рис.16
Теперь вызываем команду Итоги… (ДАННЫЕ ->Итоги…) (Data-Subtotals…). Результатом станет появление диалогового окна рис.17. Задаем значение полей как показано на рисунке. В поле При каждом изменении в: (At each change in) указываем имя поля по группам позиций которого требуется получение итогов. В поле Операция (Use function) указываем операцию над значениями ячеек. В нашем случае это сумма как для количеств книг так и для величин сумм заказов. В поле добавить итоги по: указываем те поля для которых собственно требуется получение итогов. Это Количество и Сумма заказа. Нажимаем OK и получаем результат рис.18.
Рис.17
Рис.17-mac
Рис.18
Слева появилась карта структуры базы данных. Кнопки со знаком минус позволяют свернуть соответствующую ей группу данных, от которой остается только одна итоговая строка, а на кнопке появляется знак плюс (рис.19).
Рис.19
Задание.
Изучить теоретическое обоснование. В соответствии со структурой базы данных показанной на рис.15 создать собственное наполнение ее данными. Сохранить файл. Не менее двадцати записей. Таблицу или копию экрана поместить в файл отчета. Предложить три критерия поиска и выполнить поиск по ним. Описание критериев и выборки записей, удовлетворяющие им поместить в файл отчета. Предложить три критерия сортировки и выполнить их. Описание критериев или копии экрана с ними, а также получаемые после сортировок таблицы поместить в файл отчета. С помощью Автофильтра произвести фильтрацию по сложному условию по одному полю, двум полям и трем полям одновременно. Описание сложных условных фильтраций или копии экранов с ними, а также виды получаемых таблиц поместить в отчет. Сформулировать критерии расширенной фильтрации. Выполнить расширенную фильтрацию. Таблицы с заданием критериев расширенной фильтрации и полученную таблицу поместить в отчет. Предложить и сформулировать виды итогов. Получить таблицу итогов. Описание получаемых итогов и таблицу с ними поместить в отчет. Оформить отчет.Заключение.
В результате выполнения лабораторной работы вы должны знать и уметь выполнять:
ввод данных,
поиск данных в базе,
сортировку и фильтрацию данных,
подведение промежуточных итогов по данным базы.
Лабораторная работа
Консолидация баз данных и сводные таблицы Excel.
Цель занятия.
Изучение консолидации данных базы данных и построения сводных таблиц и диаграмм по данным исходных таблиц.
Теоретическое обоснование.
Консолидация данных
Консолидация – это объединение данных, находящихся в одной таблице, разных таблицах на одном рабочем листе, на разных листах или разных книгах. Важным при этом является то, что таблицы должны иметь одинаковую структуру. Итоговая таблица, получаемая в результате консолидации, будет иметь одну строку-заголовок с ключевым полем (ключевым считается самое левое поле исходной таблицы или ее выделенной части) и данные, преобразованные в зависимости от того, какая функция преобразования будет выбрана.
Применение консолидации данных рассмотрим на примере. В качестве исходных данных будем использовать исходную базу данных созданную вами в предыдущей работе (см. рис.15 предыдущей работы).
В таблице повторяются названия издательств. Зададимся целью получить объединенные данные по продажам для каждого издательства за период с января по апрель 2000 года. Консолидированную таблицу разместим в новом листе.
Решение задачи складывается из следующих шагов.
Создаем новый лист. Находясь в этом листе вызываем диалоговое окно Консолидация (ДАННЫЕ->Консолидация…) (Data-Consolidate…) (рис.1).
Рис.1
Рис.1-mac
Заполняем поля окна, как показано на рисунке. В поле Функция (Function) задается функция, применяемая к консолидируемым данным. Поскольку мы хотим получить объединенные данные по продажам для каждого издательства, то выбираем функцию Сумма (Sum).
В поле Ссылка (Reference) задается диапазон, для которого выполняется консолидация данных. База данных находится на Листе 1. Поэтому указываем его до координат записей с января по апрель (см. рис.15 предыдущей работы). Чтобы в верхней строке консолидированной таблицы отобразились названия полей, а ключевым полем считался крайний левый столбец выделенного фрагмента, включаем соответствующие переключатели. Включение флажка Создавать связи с исходными данными позволяет получить консолидированную таблицу в которой будут происходить изменения при изменении в исходной базе данных (в консолидируемых полях). После нажатия OK получаем консолидированную таблицу (рис.2).
Рис.2
Поскольку поля Жанр и Цена нас не интересуют их можно удалить и получить модифицированную консолидированную таблицу (рис.3).
Рис.3
Сводные таблицы.
Сводные таблицы являются средством трансформации и анализа исходных данных. Как и для предыдущих задач, процесс построение сводной таблицы рассмотрим на конкретном примере. Прежде всего, сформулируем описание структуры сводной таблицы. Сводная таблица должна нести наглядную информацию о количестве заказанных книг по каждому издательству и жанру.
Сводные таблицы создаются с помощью Мастера сводных таблиц за три шага.
Шаг 1. Выделяем любую ячейку исходной таблицы. Выбираем команду ДАННЫЕ->Сводная таблица… (Data-PivotTable Report…). Результатом станет появление окна рис.4.
Рис.4
Рис.4-mac
Устанавливаем переключатели как показано на рисунке и нажимаем кнопку Далее (Next).
Шаг 2. В диалоговом окне Мастера сводных таблиц и диаграмм (рис.5.) задаем диапазон ячеек исходной таблицы. Нажимаем кнопку Далее (Next).
Рис.5
Рис.5-mac
Шаг 3. В диалоговом окне Мастера сводных таблиц и диаграмм (рис.6.) устанавливаем переключатель новый лист чтобы сводная таблица была построена на новом листе. Нажимаем кнопку Макет (Layout…).
Рис.6
Рис.6-mac
В появившемся диалоговом окне (рис.7) показан макет сводной таблицы. Перетаскиваем кнопки с наименованиями полей исходной таблицы на план-макет сводной таблицы.
Выбор кнопок для перетаскивания и место их расположения на самом макете определяется требуемой структурой сводной таблицы.
Рис.7
Рис.7-mac
В соответствии с требованиями к структуре сводной таблицы сформулированных выше расположим названия издательств в строках, названия жанров в столбцах, а количества заказанных книг соответствующего издательства и жанра составят содержание поля данных (рис.8).
Рис.8
Рис.8-mac
Нажимаем OK. и вернувшись в окно рис.6 нажимаем кнопку Готово (Finish).
В результате создается новый лист и в нем сводная таблица (рис.9).
Рис.9
Рис.9-mac
С помощью кнопки Мастер диаграмм на панели Сводные таблицы можно выбрать тип диаграммы для графического представления данных сводной таблицы.
Рис.10
Задание.
Изучить теоретическое обоснование. Проанализировав структуру базы данных созданной вами в ходе предыдущей лабораторной работы сформулировать и реализовать требование по построению таблицы консолидированных данных. Сохранить файл, содержащий консолидированные данные. Задание по консолидации и таблицу или копию экрана поместить в файл отчета. Проанализировав структуру базы данных созданной вами в ходе предыдущей лабораторной работы сформулировать и реализовать требование по построению сводной таблицы данных. Сохранить файл, содержащий сводные таблицу и диаграмму. Задание по построению сводной таблицы, саму таблицу или копию экрана поместить в файл отчета. Оформить отчет.Заключение.
В результате выполнения лабораторной работы вы должны знать и уметь выполнять:
консолидацию данных базы
построения сводных таблиц и диаграмм по данным исходных таблиц
Лабораторная работа
Построение диаграмм в Excel
Цель занятия.
Изучение способов графического отображения табличных данных.
Теоретическое обоснование.
С помощью диаграмм данным таблиц можно придавать большую наглядность. Возможности табличного процессора Excel по отображению диаграмм рассмотрим на примере таблицы представленной на рис.3 предыдущей лабораторной работы. Прежде всего, выделим все данные и заголовки столбцов и строк этой таблицы (рис.1). Эти заголовки составят список условных обозначений рядов данных на диаграмме (легенда) и подпись для оси Х.
Рис.1
Вызываем Мастер диаграмм (Chart Wizard), щелчком по пиктограмме на панели инструментов, или командой Вставка->диаграмма… (Insert->Chart…). На экране появится диалоговое окно Мастер диаграмм (шаг 1 из 4) (Chart Wizard - Step 1 of 4) (рис.2).
На вкладке Стандартные (Standart Types) в списке Тип (Chart Types) приведены 14 типов диаграмм, для каждой из которых в списке Вид (Chart sub-type) указаны возможные подтипы. Нажимая кнопку Просмотр результата (Press and Hold to View Sample) можно увидеть прототип результата. Выберите один из вариантов. Мы будем рассматривать далее вариант Гистограмма (Column).
Рис.2
Рис.2-mac
Нажатие кнопки Далее ведет к появлению диалогового окна Мастер диаграмм (шаг 2 из 4) (Chart Wizard - Step 2 of 4) (рис.3). В центре окна приведено изображение диаграммы, построенной, в соответствии с выбранным вами типом диаграммы на шаге 1. Справа в прямоугольнике приведена информация о легенде. Если надо изменить данные, на основании которых построена диаграмма, это можно сделать в поле Диапазон (Data range). Переключатели Ряды в (Series in) позволяют изменить ориентацию данных (выбор из строк или из столбцов).
Рис.3
Рис.3-mac
Нажимаем Далее (Next) и переходим к шагу 3 (рис.4).
Рис.4
Рис.4-mac
На экране появляется диалоговое окно Мастер диаграмм (шаг 3 из 4) (Chart Wizard - Step 3 of 4) с помощью которого можно добавить к диаграмме различные элементы, совершенствующие ее вид: линии сетки, подписи данных на диаграмме, надписи к осям, заголовок и др. Настраиваем картинку по своему вкусу и щелкаем кнопку Далее.
Рис.5
Рис.5-mac
В появившемся окне Мастер диаграмм (шаг 4 из 4) можно указать на каком листе следует поместить диаграмму (рис.5). Нажимаем кнопку Готово (Finish) и получаем результат (рис.6).
Рис.6
Если в появившемся рисунке видны не все надписи, следует изменить пропорции рисунка, сначала выделив его, а затем растянув (рис.7).
Рис.7
Если на гистограмме нам не нужны данные по количеству книг то соответствующую модификацию можно реализовать на шаге 2 Мастера диаграмм (Chart Wizard - Step 2 of 4) (рис.8). переходим на вкладку Ряд (Series). На этой вкладке кроме диаграммы предусмотрен список Ряд (Series), в котором отображаются названия обрабатываемых рядов данных. В данном случае у нас два ряда с названиями К-во и Сумма заказов.
Рис.8
Рис.8-mac
Выделяем ряд с именем К-во и нажатием кнопки Удалить (Remove) удаляем его (рис.9).
В поле Имя (Name) указывается адрес ячейки, в которой записано имя ряда данных Сумма заказа. В поле Значения (Values) указан диапазон ячеек, в котором записаны числовые значения ряда Сумма заказа. Адрес является полным (с указанием Листа) и абсолютный (со знаком $). В поле Подписи оси Х (Category (X) axis labels) указаны адреса ячеек исходной таблицы, из которых берутся значения названий издательств для подписей оси Х дмаграммы. Щелкаем Далее и переходим к шагу 3 (рис.10).
Рис.9
На экране появляется диалоговое окно Мастер диаграмм (шаг 3 из 4) с помощью которого можно добавить к диаграмме различные элементы, совершенствующие ее вид: линии сетки, подписи данных на диаграмме, надписи к осям, заголовок и др. Настраиваем картинку по своему вкусу и щелкаем кнопку Далее.
Рис.10
Рис.10-mac
В появившемся окне Мастер диаграмм (шаг 4 из 4) можно указать на каком листе следует поместить диаграмму (рис.5). Нажимаем кнопку Готово и получаем результат (рис.11).
Рис.11
Как мы уже писали данные таблицы можно отображать с помощью различных типов диаграмм. Причем выбор можно делать не только на шаге 1 Мастер диаграмм, но и после построения диаграммы. Для этого выделяем диаграмму, нажимаем правую кнопку мыши и в всплывающем окне выбираем команду Тип диаграммы (Chart Type…) (рис.12).
Рис.12
Рис.12-mac
Результатом станет появление окна Тип диаграммы (рис.13).
Рис.13
Рис.13-mac
Пользуясь этим средством рассмотрим кратко основные характеристики типов диаграмм.
Гистограмму (Column) мы уже строили на предыдущих рисунках (рис. 6, 7, 11). Она позволяет подчеркнуть характер изменения данных во времени. Имеет 7 подтипов (ри
с.2).
Линейная (Bar) подчеркивает сопоставление значений данных (рис.14). Имеет 6 подтипов (рис.13).
Рис.14
График (Line) отражает изменения значений данных за равные промежутки времени (рис.15). Имеет 7 подтипов.
Рис.15
Круговая (Pie) может представлять только один ряд данных и позволяет наглядно оценить вклад каждого элемента ряда в общую сумму (рис.16). Имеет 6 подтипов.
Рис.16
Точечная (XY Scatter) представляет две группы чисел в виде одного ряда точек в прямоугольных координатах (рис.17). Может отображать взаимосвязь между несколькими рядами данных. По оси Х отображаются интервалы, а не конкретные значения параметра. Имеет 5 подтипов.
Рис.17
С областями (Area) показывает сумму значений и вклад каждого элемента в общую сумму (рис.18). Имеет 6 подтипов.
Рис.18
Кольцевая (Doughnut) как и круговая показывает вклад каждого элемента в общую сумму, но в отличие от круговой позволяет обрабатывать несколько рядов данных (рис.19). Каждое кольцо представляет отдельный ряд данных. Например, для гистограммы на рис.6 вид кольцевой диаграммы будет как на рис.20. Имеет два подтипа.
Рис.19
Рис.20
Лепестковая (Radar) представляет каждому элементу свою ось, исходящую из начала координат (рис.21). Линиями соединяются значения из одного ряда. Позволяет сравнивать значения из нескольких рядов данных. Имеет 3 подтипа.
Рис.21
На вкладке Нестандартные (Custom Types) окна шага 1 Мастера диаграмм представлено 20 типов нестандартных диаграмм. На рисунке 22 представлена Нестандартная черно-белая гистограмма (B&W Column).
Рис.22
После построения любой диаграммы можно изменить ее вид, вызвав панель инструментов Диаграммы (рис.23). Команда ВИД->ПАНЕЛИ ИНСТРУМЕНТОВ->Диаграммы (View->Toolbars->Chart). Используя кнопку Тип Диаграммы можно изменять ее тип.
Рис.23
Рис.23-mac
Так от вида диаграммы на рис.22 можно перейти к диаграмме вида представленной на рис.24.
Рис.24
Задание.
Изучить теоретическое обоснование. Для таблицы консолидированных данных из предыдущей лабораторной работы получить и сохранить в файле отчета все типы диаграмм описанные в данной работе. Оформить отчет.Заключение.
В результате выполнения лабораторной работы вы должны знать типы диаграмм и уметь выполнять построение различных типов диаграмм для графического представления данных из таблиц базы.
