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

Делись добром ;)