Применение электронных таблиц для статистической обработки данных

     Введение

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

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

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

Задачи курсовой работы: 1) рассмотреть области применения электронных таблиц для статистической обработки информации; 2) изложение функциональных возможностей табличного процессора и общей методологии использования электронной таблицы в профессиональной работе с данными; 3) изучить основные элементы электронной таблицы; 4) пример статистической обработки данных в электронной таблице MS Excel.

Объектом курсовой работы является: Процесс обработки информации в электронных таблицах.

Предмет курсовой работы: Статистическая обработка данных в электронной таблице MS Excel.

1 Теоретические основы организации электронных таблиц

1.1 Основные понятия

Электронная таблица – самая распространенная и мощная информационная технология для профессиональной работы с данными. Для управления электронной таблицей созданы специальные программные продукты – табличные процессы.

Электронные таблицы - Это программа для создания и использования документов с автоматическим расчетом вносимых данных. Основная особенность - это связь между собой ячеек.

Основное назначение табличного процессора - автоматизация расчетов в табличной форме.

Одной из самых оригинальных идей в области компьютерных информационных технологий стала идея электронной таблицы. Для пользователя Windows наибольший интерес представляют «мощные» приложения, которые в последнее время стали чрезвычайно популярны. К их числу относятся, прежде всего, текстовый процессор MS Word, табличный процессор MS Excel, СУБД MS Access. Одним из лидеров в ряду электронных таблиц сегодня является табличный процессор MS Excel. Использование подобных интегрированных гибких программных систем - характерная черта современных информационных технологий. При этом разработчики стремятся к тому, чтобы расширение возможностей системы не усложняло работу пользователя. Во всех приложениях Windows пользователь имеет дело с одинаковым интерфейсом. Используются стандартные приемы работы, как с системными средствами, так и с прикладными программами.

Первые разработки электронных таблиц принадлежат к 70-ым годам, хотя первая промышленная система появилась уже на персональных компьютерах - ею стала система 1-2-3 фирмы Lotus. Электронные таблицы имеют, пожалуй, меньше аналогов в реальной жизни, чем текстовые процессоры или базы данных - именно за счет своей динамичности, автоматизма выполнения операциями над данными, которых не достичь на бумаге.

Каждая электронная таблица состоит из 256 столбцов и 16384 строк. Строки пронумерованы начиная с 1. Столбцы обозначаются буквами латинского алфавита. Пересечение строки и столбца образует ячейку, имеющую свой адрес - имя столбца и номер строки. Все данные таблицы размещаются в ячейках. Электронные таблицы не единственный тип документа, создаваемый в Excel. Дополнительно к таблицам можно получить диаграммы, которые будут помещаться в собственных окнах на экране.

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

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

Строка - все ячейки одного горизонтального ряда.

1.2 Области применения электронных таблиц

Широкое распространение программ обработки электронных таблиц во многом объясняется универсальными возможностями их применения поскольку без вычислений, в широком смысле этого слова, не обойтись в самых различных сферах нашей жизни (однако на практике такое преимущество, по данным разработчиков, нередко приводит к тому, что «средний пользователь» применяет только пять процентов функциональных возможностей, заложенных в Ехсеl).

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

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

Ехсеl можно использовать как для решения простых задач учета, так и для составления различных бланков, деловой графики и даже полного баланса Фирмы. Например, на предприятии с помощью Ехсеl можно облегчать решение таких задач, как обработка заказов и планирование производства, расчет налогов и заработной платы, учет кадров и издержек управление сбытом и имуществом и многих других.

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

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

1.3 Возможности табличных процессоров.

      создание и редактирование электронных таблиц;

      создание многотабличных документов;

      оформление и печать электронных таблиц;

      построение диаграмм, их модификация и решение экономических задач графическими методами;

      создание многотабличных документов, объединенных формулами;

      работа с электронными таблицами как с базами данных: сортировка таблиц, выборка данных по запросам;

      создание итоговых и сводных таблиц;

      использование при построении таблиц информации из внешних баз данных;

      создание слайд-шоу;

      решение оптимизационных задач;

      решение экономических задач типа “что – если” путем подбора параметров;

      разработка макрокоманд, настройка среды под потребности пользователя и т.д.

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

1.4 Основные элементы электронной таблицы.

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

Объекты электронной таблицы – это её элементы. Основными элементами электронной таблицы являются:

      строка;

      столбец;

      ячейка;

      лист;

      блок ячеек.

Кроме того, элементом электронной таблицы может являться диаграмма.

Каждая строка определяется номером, обозначенным на левой границе рабочего поля. Каждый столбец определяется латинскими буквами (A-Z, AA-AZ, BA-BZ и т. д.), обозначенными на верхней границе рабочего поля. Каждая ячейка определяется пересечением строки и столбца и обозначается соответствующими буквой и номером, что составляет её адрес. Например: B5, G23, AA1.

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

Блок ячеек определяется как прямоугольная область смежных ячеек. Адрес блока состоит из адресов ячеек в противоположных углах блока, например: B13:C19, A1:J27.

Электронная таблица имеет трёхмерную структуру. Она состоит из листов, объединённых в книгу. На экране виден только верхний лист, остальные можно выбирать в нижней части рабочего поля. Листы можно добавлять, удалять, копировать.

Диаграмма служит для графического представления данных и результатов.

Элементы или вся электронная таблица в табличном редакторе могут быть защищены от изменений. Защита информации может быть установлена с помощью Главного меню. Обычно защищают данные, которые не должны изменяться пользователем-исполнителем в процессе использования электронной таблицы – заголовки, “шапки” таблиц, расчётные формулы.

С объектами электронной таблицы можно совершать действия с помощью:

      «мыши»;

      меню (главного, встроенного);

      буфера обмена.

      Основные действия:

      копирование;

      перемещение;

      удаление (очистка);

      заполнение (автозаполнение).

При выполнении действий нужно быть внимательным и соблюдать следующие правила:

1) Копируя или перемещая данные, следует указывать, ЧТО копируется (перемещается) и КУДА.

2) Как всегда, перед выполнением действия с объектом электронной таблицы, он должен быть выбран (выделен).

3) Как и в текстовом редакторе, любое действие можно отменить и повторить кнопками в стандартной панели.

1

2 Статистическая обработка данных в MS Excel

2.1 Представление электронной таблицы Microsoft Excel

Рабочая область Excel состоит из таблицы, минимальный адрес-ячейка. Каждая ячейка имеет свой адрес, он определяется по имени столбца и № строки. Его можно посмотреть в имени ячейки (см. выше)

Рабочая книга - это многостраничный документ Excel, каждая страница которого называется рабочим листом. Рабочий лист состоит из 16384 столбцов (от A до XFD) и 1485076 строк. Каждая ячейка имеет собственный электронный адрес.

Рисунок 1 – Вид электронной таблицы MS Excel.

Ввод данных.

В ячейке рабочего листа могут быть введены данные трёх типов:

1) числа (представляют собой величины, использующихся в вычислениях)

2) формулы (инструкции, вводимые в ячейки, в соответствии с которыми производятся вычисления). Начинаются с "="

3) текст (информация, не являющаяся ни числом, ни формулой. Текст обрабатывается как последовательность символов).

Данные электронной таблицы.

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

Типы данных:

1) Текстовый тип данных - текст (набор символов). Действия над текстовыми данными производятся аналогично действиям над объектами в текстовом процессе.

2) Числовой тип данных: последовательность цифр, которые могут быть разделены запятой и начинаться с плюса или минуса. Над числовыми данными производятся различные математические операции.

Тип данных даты

Процентный формат: переводит числа в %. Значение ячейки умножается на 100

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

Настраивать формат можно двумя способами: через главную панель инструментов и через контекстное меню.

Построение диаграмм.

Диаграмма - это средство отображения числовой информации в наглядном виде.

Чтобы отобразить числовую информацию в виде диаграммы, нужно выделить диапазон с информацией, нажать вкладку «Вставить» и нажать нужный Вам тип диаграммы. Она бывает соответствующих видов:

Функции в Excel.

Чтобы найти какое-либо значение можно использовать функции.

Все функции делятся по категориям:

      функции надстроек и автоматизации.

      функции кубов.

      функции для работы с базами данных.

      функции даты и времени.

      инженерные функции.

      финансовые функции.

      информационные функции.

      логические функции.

      функции ссылки и поиска.

      математические и тригонометрические функции.

      статистические функции.

      текстовые функции.

      условное форматирование.

      условное форматирование-оформление ячеек в зависимости от введенных данных.

Находится на Главной ленте - Панель инструментов - стиль.

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

При создании условного формата можно ссылаться на другие ячейки на листе, например =FY2006!A5, но внешние ссылки на другую книгу использовать нельзя.

Чтобы настроить изменение цвета ячейки в зависимости от её значения, нужно нажать условное  форматирование на главной ленте и выбрать тип правила. Правило можно создать самому, выбрав в контекстном меню создать правило, при этом можно настроить свой тип оформления, а не предложенный программой. Правилом может служить любая условная функция, ссылающуюся на ячейку этого листа.

Чтобы удалить или изменить правило, нужно в контекстном меню условного форматирования выбрать «управление правилами» и сделать соответствующие поправки.

2.2 Статистическая обработка данных в MS Excel

  MS Excel позволяет использование таблиц в качестве базы данных. Сама по себе структура из строк и столбцов электронной таблицы отлично подходит для создания информационных списков. Списки позволяют работать эффективно с большими наборами данных. Список – упорядоченный набор данных, имеющих одинаковую структуру, и состоит из трех основных элементов:1. записи;2. поля;3. заглавной строки. Каждый элемент списка занимает одну строку, в которой данные распределяются по нескольким полям (столбцам). В программе Excel списки являются специальным видом таблиц, для которых имеются операции для их обработки. При работе со списками часто требуются разные представления одних и тех же данных, для этого Excel позволяет использовать встроенные типы сортировки, а также создавать собственные.

Таблица - Расчет относительных показателей

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

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

Рисунок 2 - Список MS Excel

Примеры обработки данных в электронной таблице MS Excel:

1)                 Вычисление успеваемости и качества знаний.

Создайте таблицу, подсчитывающую успеваемость и качество знаний по классам и по всей параллели в целом, используя стандартную функцию СУММ

1.   Заполните ячейки A1:E5 конкретными значениями (в соответствии с рисунком )

Рисунок 3 - Вычисление успеваемости и качества знаний.

2. Посчитаем количество пятерок во всей параллели. В ячейке B6 напишем формулу = СУММ (B2:B5).

3. Скопируем эту формулу на ячейки C6, D6 и E6.

• Щелкнуть правой кнопкой мыши на ячейке B6. Из контекстного меню выбрать Копировать. После этого ячейка будет выделена пунктирной рамкой.

• Выделите ячейки C6, D6 и E6 и нажмите клавишу Enter.

     4. Подсчитаем успеваемость в 9а классе. Успеваемость рассчитывается по формуле:

где К4-5 – количество учащихся, имеющих итоговые оценки «4» или «5»;

N – общее количество обучающихся.

В ячейку G2 запишем формулу =СУММ(B2:C2)/СУММ(B2:E2)

5. Подсчитаем качество знаний в 9а классе. Качество знаний рассчитывается по формуле:

где К4-5 – количество учащихся, имеющих итоговые оценки «4» или «5»;

N – общее количество обучающихся.

В ячейку G2 запишем формулу = СУММ (B2:C2) / СУММ (B2:E2)

6. Скопируем формулы из ячеек F2 и G2 в ячейки F3:G6.

7. Установим для ячеек F2:G6 процентный формат.

• Выделить ячейки F2:G6.

• Выбрать элемент главного меню Формат – Ячейки…- закладка Число

• В появившемся окне выбрать формат Процентный.

• Установить количество десятичных знаков – 1.

• Нажать OK.

2)     Подсчет степени обученности.

В соответствии с методикой В.П. Симонова различают пять показателей степеней обученности: различение, запоминание, понимание, элементарные умения и навыки, перенос.

1. Различение – самый низший показатель степени обученности, характеризующийся тем, что обучаемый отличает данный объект, процесс или явление от их аналогов только тогда, когда ему предъявляют их в готовом виде.

2. Запоминание – второй показатель степени обученности, отражающий усвоение определенного количества информации без ее понимания, результатом является неосознанное воспроизведение.

3. Понимание – третий показатель степени усвоения теории обучаемым, который определяется наличием у него собственного мнения, суждения относительно какого-либо объекта, процесса, явления. Осознанное воспроизведение информации.

4. Элементарные умения и навыки – четвертый показатель обученности, при котором обучающийся на практике применяет теорию в алгоритмизированных, стандартизированных ситуациях; выполняет практические задания по трафарету, шаблону, образцу.

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

Для определения степени обученности учеников (СОУ), то есть фактической эффективности учебной деятельности учителя (Эф), следует пользоваться следующей методикой.

1.   Если в практике учителя реализуется пятый (высший) уровень требований, то показатель степени обученности определяется по следующей формуле:

где К5 – число обучающихся, имеющих за четверть (полугодие) оценку «5»;

К4 – число обучающихся, имеющих итоговую оценку «4»;

К3 – число обучающихся с оценкой «3»;

N – общее число обучающихся, включая тех, которые имеют неудовлетворительные оценки.

2.      На третьем-четвертом уровне требований учителя степень обученности определяется по формуле:

3.      Если учитель работает на первом-втором (низшем) уровне требований, то показатель степени обученности определяется по формуле:

Таким образом, сказать, какой степени обученности достигли конкретные ученики, не зная уровень требований, на котором выставлены оценки, практически невозможно. Однако, зная, например, что данный учитель работает на третьем уровне требований, мы можем конкретно сказать, каков уровень обученности в его классе.

3)                 1. Построим таблицу исходных данных:

Рисунок 4 - Степень обученности учащихся

2. Подсчитаем количество оценок по всей параллели, пользуясь стандартной функцией СУММ. Не забудьте про копирование формул!

3. Подсчитаем СОУ для первого уровня требований: Для этого запишем в ячейку F2 следующую формулу:

                 = (0,36*B2+0,16*C2+0,04*D2) / СУММ (B2:E2)

4. Пользуясь маркером автозаполнения, «распространим» эту формулу на ячейки F3:F6.

5. Выделим ячейки и зададим количество цифр, отображаемых после запятой – 2. Формат – Ячейки – закладка Число – числовой формат Числовой – Число десятичных знаков – 2.

6. Поступим аналогичным образом при высчитывании СОУ для третьего и пятого уровней требования.

Рисунок 5 - Степень обученности учеников для третьего и пятого уровней требования.

Выводы и рекомендации

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

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

Электронные таблицы часто используются в качестве простых баз данных или как приложение для построения графиков и диаграмм. В настоящее время разработано большое количество электронных таблиц: Microsoft Excel, Lotus 1-2-3, Corel, Calc, Works, QuattroPro, Суперплан и другие. Среди них выделяют коммерческий продукт — Microsoft Excel и открытый продукт — OpenOffice.org Calc.

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

1

Библиографический список

1. СТО ЮРГУЭС 01–2009. Стандарт организации. Выпускные квалификационные работы. Курсовые проекты (работы). Основные требования к объёму и оформлению / ГОУ ВПО «Южно-Рос. гос. ун-т экономики и сервиса». – Шахты: ГОУ ВПО «ЮРГУЭС», 2009. – 44 с.

2. Методические указания к выполнению курсовой работы для студентов очной, заочной и дистанционной форм обучения, по направлению 100100.62 «Сервис» О.В. Шемет.

3. Элементы электронной таблицы и действия с ними. http://www.echudaeva.edurm.ru/index.php?option=com_content&view=article&id=57:2012-01-14-04-37-21&catid=5:---9-&Itemid=7 (2.05.2012 15:51)

4. Электронные таблицы EXCEL2007. http://excel-2007-year.narod.ru/index.htm (02.05.2012 15:56)

5. Понятия об электронных таблицах http://freepapers.ru/download/11941.zip (02.05.2012 16:11)

6. Использование электронных таблиц в экономических расчетах.

http://kref.ru/info/matmetody/5224/ (02.05.2012 16:30)

7. Электронные таблицы, назначение и основные функции.

http://www.examens.ru/otvet/11/9/807.html (02.05.2012 16:40)

8. Электронные таблицы.

http://txtbooks.ru/informatika/8-klass/82-ugrinovich-8-klass/121-23-42-elektronnye-tablitsy.html (02.05.2012 17:10)

9. Построение диаграмм.

http://excel-2007-year.narod.ru/diagram.htm (02.05.2012 17:15)

10. Функции в Excel.

http://excel-2007-year.narod.ru/function.htm (02.05.2012 18:45)

11. Обработка педагогических данных в Microsoft Excel.

http://wiki.iteach.ru/images/4/4e/ Обработка_ педагогических_ данных_excel.pdf (11.05.2012 00:57)

1