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

Лабораторная работа 3

Тема. Обработка данных в электронных таблицах MS Excel.

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

Когда данные становятся списками

В процессе обработки информации с помощью компьютера важное место занимают базы данных.

В Excel в качестве базы данных используется список, который представляет собой совокупность строк рабочего листа, содержащих однотипные данные, например, дату, названия фирм, фамилии менеджеров и т.д., рис.3.1.

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

Данные в столбцах (полях) должны быть одного типа и каждое поле имеет свое уникальное имя.

Таким образом, база данных является множеством записей, состоящих из полей данных.

Ввод данных, создание структуры данных

Вычисление суммы продаж в рублях

После заполнения всей таблицы заполним поле Сумма,руб. с учетом приведенного курса у.е.. Для этого используем стандартную функцию ExcelЕСЛИ из категории Логические.Формат функции:

ЕСЛИ (<условие>; <результат, если <условие>=True>;

<результат, если<условие>=False>)

Итак, заполняем поле Сумма,руб.Для этого в ячейку E9 введите формулу:

Е9= ЕСЛИ (А9=”январь”;$С$4;ЕСЛИ (А9=”февраль”;$С$5;$С$6))*D9

и скопируйте ее вниз до конца таблицы. Установите «рублевый формат».

Вычисление общей суммы продаж

В ячейках D2 и E2 вычислите общую сумму продаж в y.e. и руб., возпользовавшись Автосуммой.Установите в этих ячейках формат «у.е.» и «рублевый» формат, соответственно.

Создание автофильтра

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

Для создания Автофильтра выполните следующие действия:

· выделите ячейки А8:Е8, содержащих заголовки столбцов (имена полей);

· Во вкладке Данные\Сортировка и фильтр нажмите на кнопку Фильтр ;

· в таблице, в каждой из выделенных ячеек, появятся кнопки автофильтра (рис.3.4).

Нажав на соответствующую кнопку автофильтра можно выбрать «нужное значение» в появившемся списке возможных значений (рис. 3.4).

Рис.3.4. Созданный автофильтр.

Можно, например, произвести фильтрацию по любому из полей: Менеджер, Кому и т.д.

Для отмены фильтрации нажмите кнопку автофильтрации и выберите в открывающемся списке (Выделить все).

Выборка из базы данных

Структура выборки

Работать с автофильтром легко и просто, но если требуется постоянно видеть отфильтрованный список, то удобнее сделать выборку из базы данных, которая будет автоматически «перевычисляться» при добавлении новой информации.

Для создания такой выборки будем использовать функцию БДСУММ для работы с базами данных (категория «Работа с базами данных»). Эта функция выполняет операции выборочно, т.е. только с данными, соответствующими заданным критериям.

Формат функции:

БДСУММ(<база данных>;<имя поля>;<критерий выбора>)

<база данных> -диапазон ячеек базы данных (в нашем случае – А8:Е100);

<имя поля> -определяетполебазы данных, над которым выполняется операция. Параметр <имя поле> задается как текст с названием поля в двойных кавычках или как номер столбца. При этом надо помнить, что первый столбец диапазона базы данных имеет номер 1;

<критерий> -ссылка надиапазон ячеек, задающих критерий выбора строк в базе данных. Чтобы задать условия отбора для отдельного поля, например для поля «Менеджер», введите условия в ячейки, расположенные в смежных строках.

Пример 3.1. Подготовить информацию об объемах продаж, каждого менеджера за 1-ый квартал, чтобы иметь возможность сравнивать результаты их работы. Создаваемая выборка будет иметь вид, как показано на рис.3.7.

Рис. 3.7.

1. Выберите для этого новый лист. Назовите его Менеджеры. Заполните диапазон ячеек А1:С4.

2. Далее в ячейку А5 введите формулу для вычисления суммы продаж Иванова И.И.. Для этого активизируйте ячейку А5 и с помощью Мастера функций выберите функцию БДСУММ (из категории Работа с базой данных).В появившемся окне функции задайте параметры:

· в поле База_данных -введите диапазон ячеек А8:Е100 листа Продажии заморозьте с помощью клавиши F4;

· в полеПоле - введите текст Сумма,у.е. («Сумма,у.е.» вводится без пробелов, как соответствующее поле на листе «Калькуляция»);

· в поле Критерий – введите диапазон ячеек А3:А4 листа Менеджеры (это блок критериев);

· нажмите кнопку ОК.

Таким образом, в ячейке А5 записана формула:

А5= БДСУММ(Продажи! $A$8:$E$100;"Сумма,у.е.";A3:A4)

Если вы все проделали правильно, то в ячейке А5 запишется результат всех продаж менеджера Иванова И.И. Скопируйте формулу из ячейки А5 в ячейки В5, С5. Созданная выборка имеет вид, как показано на рис. 3.7.

Теперь добавьте новые записи в базу данных на листе Продажи и посмотрите, как будет меняться выборка на листе Менеджеры, как будут меняться общие и промежуточные итоги на листе Продажи.

Применяя описанный выше способ, вы можете создавать любые выборки из базы данных.

Контрольное задание 1. Добавить новые записи в базу данных, ввести изменения о продажах на листе Продажии проанализировать изменения на листе Менеджеры.

Сводные таблицы

Сводная таблица – это таблица, которая используется для быстрого подведения итогов или объединения больших объемов данных

Создание сводных таблиц

Для построения сводных таблиц используетсяпиктограмма - Мастер сводных таблиц,находящаяся во вкладке Вставкав группе Таблицы.Вкладку удобнее вывести на панель быстрого доступа.

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

Порядок построения сводной таблицы:

Активизируйте любую ячейку базы данных рабочего листа Продажи(можно всю базу $A$8:$E$15). Щелкните на пиктограммево вкладке Вставка (рис.3.9). После чего будет запущено окно Создание сводной таблицы.

В поле Диапазон уже находится адрес вашей базы данных $A$8:$E$15 (Вводить диапазон значений «с запасом» не требуется). Ниже можно выбрать, куда следует поместить сводную таблицу, для удобства рекомендуется помещать её на новый лист (рис.3.9). Далее нажимаем ОК.

Рис.3.9. Окно Создание сводной таблицы

Справа появится редактор списка полей сводной таблицы:

Перетащите мышью · поле Кому в область Строк. · поле Менеджер в область Столбцов. · поле Сумма, у.е. в область Значений.     Рис. 3.10. Список полей сводной таблицы.

Итог ваших действий показан на рис. 3.11 и будет размещен на новом рабочем листе, назовите этот лист Св-табл-1. Установите формат «у.е».

Рис. 3.11. Сводная таблица на рабочем листе Св-табл-1

Изменения в сводной таблице

Добавление нового элемента.

В сводную таблицу всегда можно вставить или удалить какое-либо поле. Добавим поле Дата в сводную таблицу на рис.3.13. Для этого выделите любую ячейку сводной таблицы и в появившемся справа редакторе списка полей сводной таблицы перетащите поле Дата в область Строк.

MS Excel добавит строки с Датами, как показано на рис.3.13.

Рис. 3.13.Сводная таблица после добавления поля Датав область строк и форматирования.

Изменение порядка столбцов

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

Добавление гистограмм.

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

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

· выбрать вкладку Главная\Условное форматирование .

· в открывшемся меню в разделе Гистограммы выбрать подходящий цвет.

Один из вариантов гистограмм для сводной таблицы приведен на рис.3.14.

Рис. 3.14. Гистограммы в сводной таблице

Как удалить общие итоги.

· Откройте ранее созданную сводную таблицу, рис.3.13.

· Выделите произвольную ячейку таблицы.

· Зайдите во вкладку Конструктор

· В группе Макет нажмите на кнопку Общие Итоги

· В появившемся меню выберите Отключить для строк и столбцов

После этого постройте гистограмму, используя Мастер диаграмм. Один из возможных вариантов такой гистограммы приведен на рис.3.15.

Рис. 3.15. Гистограмма сводной таблицы рабочего листа Св-табл-1.

Редактирование диаграмм

Диаграмма, созданная автоматически, нуждается в доработке. Каждый элемент диаграммы можно изменить. Для этого существует несколько способов.

1. Щелкните на нужном элементе диаграммы левой кнопкой мышки. Вокруг элемента появится масштабная рамка с маркерами. Сейчас можно сделать некоторые изменения уже известными вам способами: изменить размеры и положение отдельного элемента диаграммы; откорректировать заголовок диаграммы и т.д.

2. Щелкните на каком-либо элементе диаграммы правой кнопкой мыши и выберите в появившемся контекстном меню строку Формат <имя элемента>; в появившемся окне введите соответствующие параметры.

Лабораторная работа 3

Тема. Обработка данных в электронных таблицах MS Excel.

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