Microsoft Excel - средство для создания списков и баз данных на рабочем листе
1.9 Заключение
Программа MS Excel, представляет собой не просто удобное средство для создания списков и баз данных на рабочем листе, а так же мощный инструмент по анализу данных и выбору оптимального решение на основе имеющихся данных. При представлении информации на листе рабочей книги очень часто используются списки, так как это удобный способ представления данных и возможность использования целого ряда мощных инструментов MS Excel по обработке и анализу данных.
2. Практическая часть
2.1 Общая характеристика задачи
Рассмотрим следующую задачу:
Пекарня реализует свою продукцию через три торговые точки: две булочные и кафе.
1. Создать сводку о реализации товара за один день по приведенной ниже форме (табл.2.1) и провести вычисления по графе "Сумма, руб." с расчетом итогов по торговым точкам.
2. Сформировать итоговую таблицу (табл.2.2), выполнив консолидацию по категории, результаты представить в графическом виде.
3. Создать сводную таблицу, выполнив группировку данных по виду хлебобулочных изделий (белый хлеб, черный хлеб), для всех торговых точек.
Таблица 2. 1
Наименование |
Количество, шт. |
Цена, руб. |
Сумма, руб. |
|
Булочная № 1 |
||||
Городской |
50 |
10 |
||
Ржаной |
75 |
12 |
||
Бородинский |
20 |
11 |
||
Батон белый |
25 |
14 |
||
Лаваш |
20 |
20 |
||
ИТОГО |
||||
Булочная № 2 |
||||
Городской |
120 |
10 |
||
Ржаной |
100 |
12 |
||
Лаваш |
20 |
20 |
||
Калач |
10 |
15 |
||
ИТОГО |
||||
Кафе "XXI век" |
||||
Выпечка |
150 |
13 |
||
Батон белый |
7 |
14 |
||
Ржаной |
5 |
12 |
||
Лаваш |
50 |
20 |
||
ИТОГО |
||||
ВСЕГО |
Таблица 2.2
Наименование |
Количество, шт. |
Цена, руб. |
Сумма, руб. |
|
Городской |
||||
Ржаной |
||||
Бородинский |
||||
Выпечка |
||||
Батон белый |
||||
Лаваш |
||||
Калач |
||||
ИТОГО |
2.2 Описание алгоритма решения задачи
1. Запускаем табличный процессор MS Excel.
2. Создаем книгу с именем "Пекарня".
3. Лист 1 переименовываем в лист с названием "Сводка о реализации товара".
4. На рабочем листе "Сводка о реализации товара" MS Excel создаем таблицу "Сводка о реализации товара за один день".
5. Заполняем таблицу "Сводка о реализации товара за один день" исходными данными (рис.2.1).
excel список база лист
Рисунок 2.1 Сводка о реализации товара за один день
6. Вычисление по графе "сумма, руб." для товаров
для "Булочной №1":
заносим в ячейку D4 формулу:
=B4*C4
Размножаем введенную в ячейку D4 формулу для остальных ячеек (с D4 по D8) данной графы.
для "Булочной №2":
заносим в ячейку D11 формулу:
=B11*C11
Размножаем введенную в ячейку D11 формулу для остальных ячеек (с D11 по D14) данной графы.
для "Кафе XXI век":
заносим в ячейку D17 формулу:
=B17*C17
Размножаем введенную в ячейку D17 формулу для остальных ячеек (с D17 по D20) данной графы.
7. Расчет итогов по торговым точкам
для "Булочной №1":
заносим в ячейку D9 формулу:
=СУММ (D4: D8)
для "Булочной №2":
заносим в ячейку D15 формулу:
=СУММ (D11: D14)
для "Кафе XXI век":
заносим в ячейку D21 формулу:
=СУММ (D17: D20)
Суммарный итог для трех торговых точек:
заносим в ячейку D21 формулу:
=СУММ (D9+D15+D21)
Сводка о реализации товара за один день представлена на рис.2.2
Рисунок 2.1 Сводка о реализации товара за один день
8. Формирование итоговой таблицы
Для формирования итоговой таблицы выполняем консолидацию данных по категории, в данном случае по виду хлебобулочных изделий. Для этого создаем новый рабочий лист и называем его "Итоговая таблица".
Выполняем команду Данные > Консолидация.
Задаем параметры для диалогового окна "Консолидация"
8.1 В поле "Функция" указываем функцию Сумма, которая показывает тип объединения данных.
8.2 В поле "Ссылка" вводим ссылку на диапазон, находящийся на листе "Сводка о реализации товара": Сводка о реализации товара! $A$4: $D$9 и добавляем в список диапазонов. Таким образом, данные о булочной №1 будут добавлены в список для консолидации данных. Для булочной №2 делаем ссылку в меню окна консолидации: Сводка о реализации товара! $A$11: $D$15 и добавляем в список диапазонов. Для кафе "XXI век" делаем ссылку в меню окна консолидации: Сводка о реализации товара! $A$17: $D$21 и добавляем в список диапазонов.
8.3 Устанавливаем опции Значения левого столбца. В результате Excel будет подбирать данные по виду хлебобулочных изделий.
8.4 Для того, чтобы консолидация была динамической, установим опцию "Создавать связи с исходными данными" и нажимаем кнопку "ОК". В результате Excel создаст структуру, содержащую внешние ссылки. В результате чего у нас создается таблица, которая объединяет значения по виду хлебобулочных изделий для трех торговых точек и показывает значения об общем количестве, цене и сумме определенного вида хлебобулочного изделия, находящегося в данных торговых точках, которая представлена на рис.2.3.
Рисунок 2.3 Консолидация данных
Для представления полученных результатов в графическом виде на панели инструментов выбираем Вставка>Диаграмма и в меню "Мастер диаграмм" и выбираем тип диаграммы Линейчатая первый тип, которая отображает значения различных категорий, затем жмем кнопку "Далее", и в появившемся окне вводим диапазон значений, в нашем случае A4: А21 и D4: D21 и указываем, что ряды в столбцах. Нажимаем кнопку "Далее", в появившемся окне, во вкладке заголовки вводим по оси Х "Наименование хлебобулочных изделий", по оси Y "Сумма, руб", остальные настройки оставляем по умолчанию и нажимаем кнопку "Далее", где указываем, что данная диаграмма будет находится на листе "Итоговая таблица", нажимаем на кнопку "Готово", в появившейся диаграмме изменяем цвет для разного вида хлебобулочного изделия, для наглядности, рис.2.4.
Рисунок 2.4 Итоговая диаграмма
9. Создание сводной таблицы
Для создания сводной таблицы создадим новый лист с названием "Сводная таблица" и скопируем итоговую таблицу на этот лист.
Данные в первом столбце имеют текстовый формат, а данные второго, третьего и четвертого - числовой формат. Добавим еще одно поле-вид хлебобулочного изделия, где присвоим каждому реализуемому товару - вид хлебобулочного изделия, к которому он относится. Городской, выпечка, батон белый, лаваш и калач относятся к белому хлебу, а ржаной и бородинский к черному хлебу. Выполним группировку столбцов: "Наименование" и "Вид хлебобулочных изделий", для этого выделяем столбец А1: А21 и заходим в данные>группа и структура>группировать столбцы. Далее выполним сортировку данных по виду хлебобулочных изделий, для этого заходим в данные>сортировка>сортировать по "Вид хлебобулочных изделий" и идентифицировать поля по подписям, рис.2.5.
Рисунок 2.5 Группировка столбцов
Для создания сводной таблицы: заходим в данные>сводная таблица, в мастере сводных таблиц выбираем пункты: создать сводную таблицу на основе данных, находящихся в списке или базе данных Microsoft Excel и вид создаваемого документа - сводная таблица, затем нажимаем на кнопку "Далее". Указываем диапазон, предварительно нажав на , для того чтобы скрыть столбец "Наименование", содержащий исходные данные: Сводная таблица! $B$1: $E$19, затем нажимаем на кнопку "Далее" и в появившемся окне "Мастер сводных таблиц и диаграмм" выбираем пункт "Параметры", где в поле "Формат" убираем галочки с пунктов "Общая сумма по столбцам" и "Общая сумма по строкам", затем нажимаем на кнопку "Готово". Из появившегося окна: "Список полей сводной таблицы" переносим мышью "Вид хлебобулочных изделий" в поле строк, а пункт-количество и сумма переносим в область данных, чтобы происходило суммирование по полю количество и сумма. В итоге получаем сводную таблицу, изображенную на рис.2.6.
Рисунок 2.6 Сгруппированные и отсортированные данные и сводная таблица
Список использованной литературы
1. Информатика. Лабораторный практикум для студентов 2 курса всех специальностей. - М.: ВЗФЭИ, 2006
2. Е.К. Овчаренко, О.П. Ильина, Е.В. Балыбердин "Финансово - экономические расчеты в Exsel", Москва, 2009 г.
3. http://www.microsoft.com/rus/ - Российская страница компании Microsoft.
4. Уемпен Ф., Пейд Д. Excel. - М.: Феникс, 2008. - 592 с.
5. Заболотнова Е.Ю. Организация данных в виде таблиц. Практическая работа с программой Microsoft Excel. - http://www.nbuv.gov.ua/books/ /2007/excel/
6. Левин А. Самоучитель работы на компьютере. - М.: Нолидж, 2007. - 643с.
7. Информатика. Методические указания по выполнению курсовой работы. М.: ВЗФЭИ, 2006