Анализ данных средствами 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  и имеет вид:

.

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