Министерство образования и науки РФ

Федеральное государственное бюджетное образовательное учреждение

высшего профессионального образования

Факультет экономический

Кафедра системотехники

ИССЛЕДОВАНИЕ

МЕТОДОВ ОБРАБОТКИ ЭКОНОМИЧЕСКОЙ ИНФОРМАЦИИ

В ТАБЛИЧНОМ ПРОЦЕССОРЕ EXCEL

Пояснительная записка

(СТ. 000000.020 ПЗ)

Руководитель

___________________

                                                                               дата         оценка       роспись

Выполнила

___________________

                                                                                                  дата сдачи              роспись

2011

Кафедра системотехники

ЗАДАНИЕ

НА КУРСОВУЮ РАБОТУ ПО ИНФОРМАТИКЕ

Студент

Факультет ЭФ группа

Тема РГР: исследование методов обработки статистической информации в табличном процессоре EXCEL.

Вариант 16

Имеется информация о нормативной трудоемкости единицы продукции (чел.-дни) по цехам (табл. 35). Количество цехов – больше 15. Имеются данные о показателях за базисный и отчетный периоды (табл.36).

              Таблица 35 – Нормативная трудоемкость

              Таблица 36 – Отчет

Примечание. Показатели отчетного периода больше или равны показателям базисного периода; в таблице задать показатели для более 10 цехов.

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

1)     текущие строки : номер цеха, нормативная трудоемкость в базисный период (объем продукции, объем продукции в нормо-днях = нормативная трудоемкость * объем продукции, отработано чел.- дней, выработка за один отработанный человеко – день = объем продукции в нормо-днях / отработано чел.-дней), нормативная трудоемкость в отчетный период (аналогично графам базисного периода), индексы производительности труда = выработка в отчетном периоде / выработка в базисном периоде;

2)     итоговую строку по показателям объема производства в нормо-днях и отработано чел.-дней в базисном и отчетном периодах. Графы, содержащие выработки и индексы, рассчитываются аналогично текущим строкам.

Ответить на запросы:

1)     какой максимальный и минимальный объем выпущенной продукции в отчетный период;

2)     сколько цехов имеют нормативную трудоемкость от 100 до 500 чел.-часов;

3)     сколько цехов имеют выработку за один отработанный человеко-день от 20 до 35 единиц на человека и при этом выпускают больше 200 единиц;

4)     сколько требуется рабочих 3-го цеха для выполнения задания в 450 единиц в течение 5 дней. Если данных по 3-му цеху нет, то в ответ на запрос вывести: «Нет данных»;

5)     определите, каков индекс производительности у работников7-го цеха. Если данных по этому цеху нет, то в ответ на запрос вывести: «Нет данных»;

6)     какой цех имеет максимальный индекс производительности труда.

Указания к решению задачи

1.      Выполнить слияние двух БД (табл. 35 и 36).

2.      Отсортировать результирующую БД по номеру цеха.

3.      Ввести дополнительные столбцы для отклонения, расположить все столбцы в соответствии с получаемым документом.

4.      Выполнить вычисление отклонений.

5.      Ввести дополнительную запись для итоговой строки.

6.      Выполнить вычисление итогов.

7.      Вычислить ответы на запросы.

8.      Выдать документ на печать.

Задание выдано 01.10.11

   Руководитель ______________ Доррер А.Г.

Реферат

Расчетно-графическая работа представляет собой решение задачи по расчету нормативной трудоемкости единицы продукции. Расчет выполнен с помощью табличного процессора EXCEL 7.0 на ПК PENTIUM166.

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

              Ключевые слова: ПВЭМ, EXCEL, ТАБЛИЦА.

              Цель работы - создание таблиц расчета статистических характеристик экспериментальных данных.

              Метод исследования – табличный процессор EXCEL.

              Данная таблица позволяет определить:

1)     каков максимальный и минимальный объем выпущенной продукции в отчетный период;

2)     сколько цехов имеют нормативную трудоемкость от 100 до 500 чел.-часов;

3)     сколько цехов имеют выработку за один отработанный человеко-день от 20 до 35 единиц на человека и при этом выпускают больше 200 единиц;

4)     сколько требуется рабочих 3-го цеха для выполнения задания в 450 единиц в течение 5 дней;

5)     какой индекс производительности у работников 7-го цеха;

6)     какой цех имеет максимальный индекс производительности труда.

Содержание

Введение              6

1 Сводные таблицы в табличном процессоре Excel              7

2 Решение задачи              10

2.1 Входная и выходная информация              10

2.2 Схема алгоритма              12

2.3 Протокол контрольного расчета              13

2.3.1 Таблицы в числовом виде              14

2.3.2 Таблицы в формульном виде              15

3 Инструкция по работе с таблицей              16

Заключение              17

Список используемой литературы              18

Приложения              19

Введение

Современные технологии обработки информации часто приводят к тому, что возникает необходимость представления данных в виде таблиц. В языках программирования для такого представления служат двухмерные массивы. Для табличных расчетов характерны относительно простые формулы, по которым производятся вычисления, и большие объемы исходных данных. Такого рода расчеты принято относить к разряду рутинных работ, для их выполнения следует использовать компьютер. Для этих целей созданы электронные таблицы (табличные процессоры) — прикладное программное обеспечение общего назначения, предназначенное для обработки различных данных, представимых в табличной форме.

Электронная таблица (ЭТ) позволяет хранить в табличной форме большое количество исходных данных, результатов, а также связей (алгебраических или логических соотношений) между ними. При изменении исходных данных все результаты автоматически пересчитываются и заносятся в таблицу. Электронные таблицы не только автоматизируют расчеты, но и являются эффективным средством моделирования различных вариантов и ситуаций. Меняя значения исходных данных, можно следить за изменением получаемых результатов и из множества вариантов решения задачи выбрать наиболее приемлемый.

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

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

Сводная таблица является специальным типом таблицы, которая суммирует информацию из конкретных полей списка или базы данных. При создании сводной таблицы с помощью мастера вы можете задать нужные поля, организацию таблицы ( ее макет ) и тип выполняемых вычислений. После построения таблицы можно изменить ее расположение для просмотра данных под другим углом зрения. Именно возможность изменения ориентации таблицы, например транспонирование заголовков столбцов в заголовки строк и наоборот, дала сводной таблице ее название и делает ее мощным аналитическим инструментом.

Сводная таблица - это так же еще один инструмент обработки больших списков ( баз данных ). Поскольку в этом случае сразу подводятся итоги, выполняется сортировка и фильтрация списков, то сводная таблица является более мощным инструментом обработки данных, который в EXCEL называется мастер сводных таблиц.

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

При создании сводной таблицы можно использовать один из четырех типов источников данных:

   список Excel;

   внешний источник данных, доступный через Microsoft Query;

   несколько диапазонов консолидации ( отдельных списков Excel );

   другую сводную таблицу.

    

Запуск мастера сводных таблиц

Чтобы приступить к созданию сводной таблицы, выберите в меню Данные команду Сводная таблица. Excel запустит мастер сводных таблиц , который будет сопровождать нас в процессе создания таблицы , включающем в себя следующие шаги:

1. Задание типа источника данных.

2. Указание местонахождения исходных данных или получение их из внешнего источника с помощью Microsoft Query.

3. Задание макета таблицы и выбор итоговой функции.

4. Указание места для размещения таблицы.

Шаг 1 : задание типа источника данных

При установке переключателя Во внешнем источнике данных первого окна диалога мастера сводных таблиц появится изображение ЭВМ , передающей данные на терминал.

Шаг 2 : указание местонахождения исходных данных

После задания типа источника данных нужно нажать кнопку Далее. Мастер сводных таблиц откроет свое второе окно диалога , предлагающее указать место , где находятся исходные данные. Если источником данных является список Excel и перед выбором команды Сводная таблица выделена только одна ячейка в этом списке , то мастер сводных таблиц правильно заполнит это окно диалога. Если исходный список находится в неоткрытой книге Excel , нужно нажать кнопку Обзор в этом окне диалога. После нахождения нужного файла надо ввести имя или ссылку на диапазон , где находится список.

Шаг 3 : задание макета таблицы

После задания исходных данных нужно нажать кнопку Далее , чтобы перейти в окно диалога. В этом окне диалога мы можем сформировать макет ( структуру ) сводной таблицы и указать , какая информация должна отображаться в теле таблицы. Точнее говоря , речь идет только о начальном макете сводной таблицы , поскольку после создания таблицы ее можно реорганизовать в любое время.

В центре этого окна находится диаграмма макета , на которой показаны три оси таблицы ( строк , столбцов и страниц ) и область данных. В правой части окна диалога отображаются кнопки для каждого поля источника данных. Чтобы задать структуру таблицы , необходимо просто перетащить эти кнопки в различные области диаграммы макета. Если нужно удалить поле из сводной таблицы , следует перетащить его кнопку за пределы этой диаграммы.

По умолчанию мастер сводных таблиц применяет функцию Сумма к числовым значениям в области данных и использует функцию Кол-во значений для нечисловых значений. Чтобы использовать другую итоговую функцию , например Среднее или Максимум , надо дважды щелкнуть на заголовке поля после его перетаскивания в область данных и затем в окне диалога Вычисление поля сводной таблицы выбрать необходимую функцию. Можно использовать следующие функции :

СУММ ( ) - сумма ;

СЧЕТ ( ) - количество значений ;

СРЗНАЧ ( ) - среднее ;

МАКС ( ) - максимум

МИН ( ) - минимум ;

ПРОИЗВЕД ( ) - произведение;

СТАНДОТКЛОН ( ) - несмещенное отклонение;

СТАНДОТКЛОНП ( ) - смещенное отклонение;

ДИСП ( ) - несмещенная дисперсия;

ДИСПР ( ) - смещенная дисперсия.

Шаг 4 : указание места для размещения таблицы

В окне диалога , укажем место , где будет располагаться сводная таблица.

Чтобы поместить таблицу на новом рабочем листе , установим переключатель Новый лист. В противном случае установим переключатель Существующий лист и введем имя диапазона на ссылку.

После нажатия кнопки Готово Excel почти без промедления создает сводную таблицу. Если таблица основана на внешних данных , Excel сначала выполнит запрос , а затем построит сводную таблицу.

Когда выделение находится в пределах сводной таблицы , то по умолчанию Excel выводит на экран панель инструментов Сводные таблицы. Если мы не видим этой панели инструментов , в меню Сервис выбираем команду Настройка и затем устанавливаем флажок Сводные таблицы.

Excel также позволяет нам воспользоваться некоторыми специализированными средствами которые находятся на панели инструментов сводной таблицы.

2.      Решение задачи

2.1 Входная и выходная информация

В качестве входной информации выступают исходные данные, которые в соответствии с заданием на курсовую работу содержатся в таблицах 35 и 36.

Таблица 35 – Нормативная трудоемкость

Таблица 36 - Отчет

Окончание таблицы 36

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

3)     текущие строки : номер цеха, нормативная трудоемкость в базисный период (объем продукции, объем продукции в нормо-днях = нормативная трудоемкость * объем продукции, отработано чел.- дней, выработка за один отработанный человеко – день = объем продукции в нормо-днях / отработано чел.-дней), нормативная трудоемкость в отчетный период (аналогично графам базисного периода), индексы производительности труда = выработка в отчетном периоде / выработка в базисном периоде;

4)     итоговую строку по показателям объема производства в нормо-днях и отработано чел.-дней в базисном и отчетном периодах. Графы, содержащие выработки и индексы, рассчитываются аналогично текущим строкам.

Ответить на запросы:

7)     какой максимальный и минимальный объем выпущенной продукции в отчетный период;

8)     сколько цехов имеют нормативную трудоемкость от 100 до 500 чел.-часов;

9)     сколько цехов имеют выработку за один отработанный человеко-день от 20 до 35 единиц на человека и при этом выпускают больше 200 единиц;

10) сколько требуется рабочих 3-го цеха для выполнения задания в 450 единиц в течение 5 дней. Если данных по 3-му цеху нет, то в ответ на запрос вывести: «Нет данных»;

11) определите, каков индекс производительности у работников7-го цеха. Если данных по этому цеху нет, то в ответ на запрос вывести: «Нет данных»;

12) какой цех имеет максимальный индекс производительности труда.

2.2 Схема алгоритма

2.3  Протокол  контрольного  расчета

1)     Какой максимальный и минимальный объем выпущенной продукции в отчетный период?

   Для расчета максимального объема выпущенной продукции используем следующую формулу: =МАКС(F3:F17)

   Для расчета минимального объема выпущенной продукции в отчетный период используем: =МИН(F3:F17)

2)     Сколько цехов имеют нормативную трудоемкость от 100 до 500 чел.-часов?

   1 способ расчета – табличная формула: {=СЧЁТ(ЕСЛИ('Нормативная трудоемкость'!B2:B16>100;ЕСЛИ('Нормативная трудоемкость'!B2:B16<500;1)))}

   2 способ расчета – функция базы данных: =БСЧЁТА('Нормативная трудоемкость'!A1:B16;'Нормативная трудоемкость'!A1;C24:D25)

3)     Сколько цехов имеют выработку за одни отработанный человеко-день от 20 до 35 единиц на человека и при этом выпускают больше 200 единиц?

   1 способ расчета – табличная формула: {=СЧЁТ(ЕСЛИ(I3:I17>20;ЕСЛИ(I3:I17<35;ЕСЛИ(F3:F17>200;1))))}

   2 способ расчета – функция базы данных: =БСЧЁТА(A1:I17;A1;E24:G25)

4)     Сколько требуется рабочих 3-го цеха для выполнения задания в 450 единиц в течение 5 дней. Если данных по 3-му цеху нет, то в ответ на запрос вывести: «Нет данных».

   Для выполнения запроса используем следующую формулу: =ЕСЛИ(ЕНД( ВПР(3;A3:E17;5));"нет данных";ВПР(3;A3:E17;5))

5)     Определить какой индекс производительности труда у работников 7-го цеха. Если данных по этому цеху нет, то в ответ на запрос вывести: «Нет данных».

  Для выполнения запроса используем формулу: =ЕСЛИ(ЕНД(ПОИСКПОЗ( 7;A3:A17));"нет данных";ВПР(7;A3:J17;10))

6)     Какой цех имеет максимальный индекс производительности труда?

  Для выполнения запроса используем формулу: =ДВССЫЛ(АДРЕС(2+ ПОИСКПОЗ(МАКС(J3:J17);J3:J17);1))
2.3.1 Таблицы в числовом виде

Таблица 35 – Нормативная трудоемкость

Таблица 36 – Отчет

Итоговая таблица (приложение А)

2.3.2 Таблица в формульном виде

Таблица 35 – Нормативная трудоемкость

Таблица 36  ( Приложение Б)

Итоговая таблица (Приложение В)

3. Инструкция по работе с таблицами

Таблица с расчетно-графической работой в формате Excel находится на сетевом диске Н в папке группы 82-3  в подпапке Solodovnikova Vika файле RGR 16 variant.

Значения столбцов «Нормативная трудоемкость», «Объем продукции» (в базисный и отчетный периоды) и «Отработано чел.-дней» (в базисный и отчетный периоды) заполнялись автоматически случайными числами с помощью функции СЛЧИСЛ. Для фиксации значения в этих ячейках после ввода формулы нажималась клавиша F9, при этом для расчета при отличных параметрах пользователь может подставить другие значения без выполнения дополнительных операций.

При расчете 1 задания используются функции МАКС и МИН. При расчете 2 и 3 задания первым способом считается использование табличного вида функции функции СЧЕТ, ЕСЛИ. При изменении параметров расчета, необходимо изменить значение в формуле соответствующее этому параметру. Для приведения формулы в табличный вид, после ее ввода в строку формул нажимается комбинация клавиш Ctrl+Shift+Enter. При расчете заданий 2 и 3 вторым способом считается использование функции баз данных БДСЧЕТА. Для изменения параметров расчета при использовании функции базы данных необходимо изменить значения параметров в диапазоне критериев. При выполнении 5 задания использовались функции ЕСЛИ, ЕНД, ПОИСКПОЗ, ВПР. И, наконеч при выполнении 6 задания использовалась функция ДВССЫЛ, АДРЕС, ПОИСКПОЗ и МАКС.

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

При изменении значений параметров в таблице и формулах зависящие от них значения пересчитываются автоматически.

Заключение

Одной из самых продуктивных идей в области компьютерных информационных технологий стала идея электронной таблицы. Многие фирмы разработчики программного обеспечения для ПК создали свои версии табличных процессоров - прикладных программ, предназначенных для работы с электронными таблицами. Из них наибольшую известность  приобрели Lotus 1-2-3 фирмы Lotus Development, Supercalc фирмы Computer Associates, Multiplan и Excel фирмы Microsoft.

Табличные процессоры (ТП) - удобный инструмент для экономистов,  бухгалтеров, инженеров, научных работников - всех тех, кому приходится работать с большими массивами числовой информации. Эти программы позволяют создавать таблицы, которые (в отличие от реляционных баз данных) являются динамическими, т. е. содержат так называемые вычисляемые поля, значения которых автоматически пересчитываются по заданным формулам при изменении значений исходных данных, содержащихся в других полях. При работе с табличными процессорами создаются документы - электронные таблицы (ЭТ). Электронная таблица (документ) создается в памяти компьютера. В дальнейшем ее можно просматривать, изменять, записывать на магнитный диск для хранения, печатать на принтере.

Список использованных источников

1.      Веденеева, Е.А. Функции и формулы Excel 2007 / Е.А. Веденеева. – СПб [и др.] : Питер, 2008. – 384с.

2.      Гладкий, А.А. Excel 2007. Трюки & эффекты / А.А. Гладкий, А.А. Чиртик. – М. : СПб; Н.Новгород: Питер, 2007. – 368 с.

3.      Уэйн Л. Винстон Microsoft Excel: анализ данных и построение бизнес-моделей / Пер. с англ. – М. : Издательство – торговый дом «Русская Редакция», 2005. – 576с.

4.      Харвей, Грег  Microsoft Offise Excel 2007. Полный справочник.: Пер. с англ. – М. : ООО ИД Вильямс, 2009. – 672с.

5.      ГОСТ 2.001-70- ГОСТ 2.321-84. Сборник стандартов на оформлению конструкторской документации (ЕСКД).

6.      ГОСТ 2.105-95. Общие требования к текстовым документам. Межгосударственный стандарт (ЕСКД).

7.      ГОСТ 19.001-77-19. 781-90. Сборник стандартов на оформление программной документации (ЕСПД).