Анализ данных средствами Microsoft Excel

Содержание

Введение

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

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

              Цель учебной практики: освоить технологии бизнес-анализа данных в Excel 2007, используя функции пакета Анализ данных и некоторые статистические функции.

              Задачи:

1 Использование финансовых функций MS Excel в экономических расчетах.

2 Использование стандартных функций в экономических расчетах, использование процедур сортировки и фильтрации данных.

              3 Использование элементов построения и редактирования графических объектов при обработке экономической информации.

Анализ данных средствами Microsoft Excel

Постановка задачи: провести статистический анализ данных, используя функции Excel и пакет сервисных программ Анализ данных. Исходные данные для выполнения работы представлены в таблице 1. Наименования торговых организаций задаются произвольным образом.

Таблица 1 – Исходные данные

Задание 1

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

Решение.

В таблице 2 представлены результаты решения задачи. В ячейке С10 была использована формула =СУММ(С4:С9), в ячейках D10, E10, F10, G10, H10, I10, J10, K10 использована аналогичная формула. В ячейке L4 формула =СУММ(С4:K4), в ячейках L5, L6, L7, L8, L9, L10 использована аналогичная формула.

Таблица 2 – Использование функции СУММ

Задание 2

Задать значение плановой годовой выручки и с помощью функции СЧЕТЕСЛИ подсчитать, сколько торговых организаций перевыполнили план за год.

Решение.

В таблице 3 представлен результат решения задачи. В ячейке С15 была использована формула =СЧЁТЕСЛИ(L5:L9;">14000").

Таблица 3 – Использование функции СЧЕТЕСЛИ

Задание 3

Используя функцию СРЗНАЧ, подсчитать среднюю ежемесячную выручку всех торговых организаций и среднюю выручку каждой за год.

Решение.

В таблице 4 представлен результат решения задачи. В ячейке С11 была использована формула =СРЗНАЧ(C4:C9), в ячейках D11, E11, F11, G11, H11, I11, J11, K11 использована аналогичная формула. В ячейке M4 формула = =СРЗНАЧ(C4:K4), в ячейках M5, M6, M7, M8, M9 использована аналогичная формула.

Таблица 4 – Использование функции СРЗНАЧ

Задание 4

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

Решение.

Рисунок 1 –Месячная выручка магазина №1

              Рисунок 2 –Месячная выручка магазина №2

Задание 5

Используя функцию РАНГ, подсчитать место каждой торговой организации по объему продаж за год.

Синтаксис функции: РАНГ(число;ссылка;порядок)

Число – это число в массиве, для которого определяется ранг.

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

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

Решение.

В таблице 5 представлен результат решения задачи. В ячейке N4 формула =РАНГ(L4;$L$4:$L$9), в ячейках N5, N6, N7, N8, N9 использована аналогичная формула.

Таблица 5 – Использование функции РАНГ

Задание 6

Используя функцию ПРОЦЕНТРАНГ, оценить для каждой торговой организации, какова доля значений месячных выручек, не превосходящих 1500 тыс. руб.

Синтаксис функции: ПРОЦЕНТРАНГ(массив;x;разрядность)

Массив – это массив или интервал данных с численными значениями, для которых определяют относительное положение.

х – это значение, для которого определяется процентное содержание.

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

Решение.

В таблице 6 представлен результат решения задачи. В ячейке M4 формула =ПРОЦЕНТРАНГ(C4:K4;1500), в ячейках M5, M6, M7, M8, M9 использована аналогичная формула.

Таблица 6 - Использование функции ПРОЦЕНТРАНГ

Задание 7

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

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

Синтаксис функции: МЕДИАНА(число1;число2; ...)

Число1, число2, ... – это от 1 до 30 чисел, для которых определяется медиана. Аргументы должны быть числами или именами, массивами или ссылками, содержащими числа. Excel проверяет все числа, содержащиеся в аргументах, которые являются массивами или ссылками. Если аргумент, который является массивом или ссылкой, содержит тексты, логические значения или пустые ячейки, то такие значения игнорируются; но ячейки, которые содержат нулевые значения, учитываются.

Для нахождения медианы (и других показателей ранжирования) также можно использовать функцию КВАРТИЛЬ. Квартиль – это значения признака, делящего данный ряд на 4.

Синтаксис функции: КВАРТИЛЬ(массив;к)

Массив – это массив или интервал ячеек с числовыми значениями, для которых определяется значения квартилей.

Если аргумент к=0, то функция возвращает минимальное значение (т.е. работает аналогично функции МИН); если к=1, то функция возвращает первую квартиль; если к=2, то функция возвращает медиану массива (т.е. работает аналогично функции МЕДИАНА); если к=3, то функция возвращает третью квартиль; если к=4, то функция возвращает максимальное значение (т.е. работает аналогично функции МАКС).

Решение.

В таблице 7 представлен результат решения задачи. В ячейке M4 формула =МЕДИАНА(C4:K4), в ячейках M5, M6, M7, M8, M9 использована аналогичная формула. В ячейке N4 формула =КВАРТИЛЬ(C4:K4;1), в ячейках N5, N6, N7, N8, N9 использована аналогичная формула.

Таблица 7 - Использование функций МЕДИАНА и КВАРТИЛЬ

Задание 8

Подсчитать для множества суммарных годовых выручек магазинов, сколько значений попадает в интервалы от 0 до 5000, от 5001 до 10000, от 10001 до 15000, от 15001 до 20000 тыс. руб., а также свыше 20000 тыс. руб., используя функцию ЧАСТОТА.

Синтаксис функции: ЧАСТОТА(массив_данных;массив_карманов)

Массив_данных – это массив чисел, для которых вычисляются частоты. Если массив_данных не содержит значений, то функция ЧАСТОТА возвращает массив нулей.

Массив_карманов – это массив правых концов тех интервалов, в которых группируются значения аргумента массив_данных.

Функция ЧАСТОТА возвращает распределение частот в виде вертикального массива, причем количество элементов в возвращаемом массиве на единицу больше числа элементов в массив_карманов. Дополнительный элемент в возвращаемом массиве содержит количество значений, больших, чем максимальное значение в интервалах. Для работы с этой функцией необходимо сначала выделить область, куда попадут результаты вычисления, а после задания исходных данных в поле функции выйти не как обычно, нажатием клавиши Enter или кнопки ОК, а нажатием клавиш Ctrl + Shift + Enter.

Решение.

В таблице 8 представлен результат решения задачи. В ячейках M4:M8 -массив правых концов тех интервалов, в которых группируются значения аргумента массив_данных. В ячейке N4 формула =ЧАСТОТА(L4:L9;M4:M8), в ячейках N5, N6, N7, N8 использована аналогичная формула.

Таблица 8 - Использование функций ЧАСТОТА

Задание 9

              Вычислить эти же частоты с помощью пакета сервисных программ Анализ данных (команда Гистограмма) (рис. 3), где поля Входной интервал и Интервал карманов соответствуют аргументам Массив_данных и Массив_карманов функции ЧАСТОТА. Построить гистограмму ЧАСТОТА (ОБЪЕМ РЕАЛИЗАЦИИ). Проанализировать характер поведения графика Интегральный процент.

Рисунок 3 – Диалоговое окно Гистограмма

Решение.

Рисунок 4 – Гистограмма «Объем реализации»

              Проанализировав характер поведения графика Интегральный процент мы видим, что на промежутках от 10000 до 15000 интегральный процент возрастает до 100%. Это означает что 100 % филиалов сделали годовую выручку в размере от 10000 до 15000.

Задание 10

С помощью функции КОРРЕЛ (категория Статистические) найти коэффициенты корреляции выручки трех любых торговых организаций (попарно) за весь год. Сделайте выводы.

Синтаксис функции: КОРРЕЛ(массив1;массив2).

Коэффициент корреляции используется для определения наличия взаимосвязи между двумя различными рядами данных Xi , Yi , i = 1... n  и имеет вид:

.

О хорошей корреляции говорят значения К, по модулю близкие к единице. Знак «+» соответствует прямой взаимосвязи, знак «» – обратной.

Решение.

В таблице 9 представлен результат решения задачи. В ячейке А12 использована следующая формула: =КОРРЕЛ(C4:K4;C5:K5). В ячейках А13, А14 аналогичная формула.

Таблица 9 - Использование функций КОРРЕЛ

С помощью функции КОРРЕЛ я нашла коэффициенты корреляции выручки магазинов №1, №2, №3, сравнивая их попарно за весь год. При сравнивании магазинов №2 и №3 (ячейка А13)  и магазинов №1 и №3 (ячейка А14) видно что значения коэффициентов положительные, значит между ними прямая взаимосвязь. А при сравнении магазинов №1 и №2 (ячейка А12), коэффициент отрицательный, и значит между ними обратная взаимосвязь.

Задание 11

Excel имеет специальный аппарат для графического анализа моделей, в том числе построения по заданному в виде таблицы временному ряду {ti , yi} аппроксимационных зависимостей (линий тренда) P(t), которые приближенно отражают функциональную связь y=f(t).

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

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

Excel позволяет выбрать один из пяти типов линии тренда P(t) – линейный, логарифмический, экспоненциальный, степенной или полиномиальный (2...6 степени) и проверить (по различным критериям), какой из типов лучше всего подходит в данной ситуации.

Критерием может служить критерий R2 (коэффициент детерминации, или достоверность аппроксимации), автоматическое вычисление которого встроено в диалоговое окно Линия тренда,

Чем ближе коэффициент детерминации к единице, тем лучше тренд.

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

Таблица 10 – Прибыль торговых организаций

Проверить линейную, полиномиальную (n=2), экспоненциальную, степенную линии: Тип | Построение линии тренда (рис. 4).

Рисунок 5 – Диалоговое окно Линия тренда\Тип

Для каждого тренда:

а) выдать аналитическую зависимость Прибыль(месяц): Показывать уравнение на диаграмме (рис. 4);

б) подсчитать по этим зависимостям соответствующую теоретическую (трендовую) численность, заполнив столбцы Теория; 

в) найти погрешность С–Т (разницу между статистической и трендовой численностью);

г) рассчитать квадратичные отклонения Si (i=1…4), используя функцию СУММКВ.

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

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

Решение.

В таблице 11 представлен результат решения задачи. В ячейке С35 формула =-14,73*A35+1597, в ячейках С36, С37, С38, С39, С40, С41, С42, С43 использована аналогичная формула. В ячейке E35 формула =-18,93*A35^2+174,6*A35+1250, в ячейках E36, E37, E38, E39, E40, E41, E42, E43, E45 использована аналогичная формула. В ячейке G35 формула =1586*EXP(-0,01*A35), в ячейках G36, G37, G38, G39, G40, G41, G42, G43 использована аналогичная формула. В ячейке I35 формула =1533*A35^-0,01, в ячейках I36, I37, I38, I39, I40, I41, I42, I43 использована аналогичная формула. В ячейке D35 формула =B35-C35, в ячейках D36, D37, D38, D39, D40, D41, D42, D43, F36, F37, F38, F39, F40, F41, F42, F43, H36, H37, H38, H39, H40, H41, H42, H43,  J36, J37, J38, J39, J40, J41, J42, J43 использована аналогичная формула.

В ячейке D44 формула =СУММКВ(D35:D43), в ячейках F44, H44, J44 аналогичная формула.

Таблица 11 – Прибыль торговой организации №1

Рисунок 6 – График прибыли организации №1

При сравнении отклонений (таблица 11) видно, что лучшим трендом является полиномиальный, т.к. у него самое меньшее отклонение. По нему оцениваем прибыль организации за 10 месяц. Следовательно прибыль магазина №1 на октябрь составит 1103 тыс. руб.

   Заключение

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

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

Во втором задании я нашла сколько организаций перевыполнили план за год с помощью функции СЧЕТЕСЛИ.

В третьем задании с помощью функции СРЗНАЧ, я нашла среднюю ежемесячную выручку всех торговых организаций и среднюю выручку каждой за год.

В четвертом задании с помощью Мастера диаграмм, построила диаграммы месячной выручки.

В пятом задании было посчитано место каждой торговой организации по объему продаж за год с помощью функции РАНГ.

В шестом задании с помощью функции ПРОЦЕНТРАНГ, было оценено для каждой торговой организации, какова доля значений месячных выручек, не превосходящих 1500 рублей.

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

В восьмом  задании была использована функции ЧАСТОТА. В этом задании годовые выручки магазинов были разбиты по интервалам.

В девятом задании были вычислены те же частоты, но с помощью пакета сервисных программ Анализ данных.

В десятом задании с помощью функции КОРРЕЛ найдены коэффициенты корреляции выручки трех организаций за весь год.

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

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

1 Балдин, К. В.   Информационные     системы   в   экономике [Текст] / К. В. Балдин,

В. Б.Уткин. – М. : Издательско – торговая корпорация «Дашков и К0» , 2005. – 395 с.

2 Барановская, Т. П. Информационные системы и технологии в экономике [Текст]: учебник для вузов / Т. П. Барановская [и др.]. - М. : Финансы и статистика, 2005.- 414с.

3 Информатика [Текст]: учебник / под ред. Н. В. Макаровой. - М. : Финансы и статистика, 2004.-768 с.

4 Советов, Б. Я. Информационные технологии [Текст]: учебник для вузов / Б. Я. Советов, В. В. Цехановский. – М. : Высшая школа, 2005. -264с.

5              Шафрин, Ю. А. Информационные технологии [Текст]: в 2 ч. Ч. 2. Офисная технология и информационные системы / Ю. А. Шафрин.- М. : Лаборатория Базовых Знаний, 2000.- 336 с.

6              Середа, О. В. Превращаем текст в число [Текст] / О. В. Середа // CHIP. – 2006. - №4. – с.85

7              Информатика и информационные технологии [Электронный ресурс] / Центр информационных технологий. – М. : «Дашков и Ко», 2008.- Режим доступа : http: // www.tening.ru.