Информационные технологии организации, обработки и анализа экономической информации в Excel. Статистические функции

МИНОБРНАУКИ РФ

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

«Южно-Российский государственный  университет экономики и сервиса»

(ФГБОУ ВПО  «ЮРГУЭС»)

Кафедра ________________________________________________________

 

Работа допущена к  защите

_______________________

                       ^{(подпись, дата)}

 

 

КУРСОВАЯ  РАБОТА

тема:___________________________________________________________

___________________________________________________________

___________________________________________________________

по дисциплине_______________________________________________

________________________________________________

 

 

Разработал   ________________________           ___________________

                            ^{(подпись)                                                                  (инициалы, фамилия)}

группа _____________

Руководитель____________ к.т.н., доц           ___________________

^{(подпись,  ученая степень,  звание)                                              (инициалы, фамилия)}

 

 

 

 

 

 

Шахты – 2013 г.

 

МИНОБРНАУКИ РФ

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

«Южно-Российский государственный университет экономики и сервиса»

(ФГБОУ  ВПО «ЮРГУЭС»)

Факультет __________________         Кафедра ____________________

 

ЗАДАНИЕ

на курсовой проект по дисциплине ______________________

на курсовую работу                 для студента  «___» курса  ________   группы

________________________________________________________________

^{(фамилия,  имя, отчество)}

Тема: ___________________________________________________________

_______________________________________________________

 

Срок выполнения _____________________________________________

Исходные данные и основные эксплуатационные требования

____________________________________________________________

____________________________________________________________

Прибор (устройство) спроектировать для _________________________

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

Объем проекта (работы) _______________________________________

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

^{(фамилия,  инициалы, учёная степень, звание, подпись)}

Задание к выполнению принял студент __________________________

^{(подпись,  фамилия, инициалы)}

Дата выдачи ____________________________________________________

 

 

 

Содержание

 

 

Введение

Многие люди полагаются на Microsoft Excel при выполнении повседневных задач. Они применяют этот продукт  для получения, обработки, анализа, совместного использования и отображения информации, от которой зависит работа предприятия. В то же время область применения Excel не ограничивается только бухгалтерским и финансовым отделами. Пользователям необходимо ежедневно получать, анализировать, создавать и просматривать важные данные. Именно поэтому приложение Excel разрабатывалось как средство, упрощающее доступ к важной деловой информации, ее подключение и анализ. Работа в Excel позволяет выполнять сложные расчеты, в которых могут использоваться данные, расположенные в разных областях электронной таблицы и связанные между собой определенной зависимостью. Для выполнения таких расчетов в Excel существует возможность вводить различные формулы в ячейки таблицы. Таблица Excel выполняет вычисления и отображает результат в ячейке с формулой. Доступный диапазон формул - от простого сложения и вычитания до финансовых и статистических вычислений. Таблица Excel - основное средство, используемое для обработки и анализа цифровой информации средствами вычислительной техники. Хотя работа с таблицами Excel в основном связана с числовыми или финансовыми операциями, они также могут использоваться для различных задач анализа данных, предоставляя пользователю большие возможности по автоматизации обработки данных.

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

Исходя из целей курсовой работы, выделим основные задачи:

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

Объектом курсовой работы является статистические данные, а предметом статистические функции Excel.

 

1. Теоретическая часть

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

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

Целый ряд статистических функций Excel предназначен для анализа  вероятностей.

1. Функции подсчета значений

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

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

• без аргумента;
• с одним аргументом; 
• с фиксированным числом аргументов;
• с неопределенным числом аргументов;
• с необязательными аргументами.

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

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

Функции можно вводить  вручную, но в Excel предусмотрен мастер функций, позволяющий вводить их в полуавтоматическом режиме и практически без ошибок. Для вызова мастера функций необходимо нажать кнопку Вставка функции на стандартной панели инструментов, выполнить команду Вставка/Функция или воспользоваться комбинацией клавиш (Shift+F3) После этого появится диалоговое окно Мастер функций, в котором можно выбрать нужную функцию.

Диалоговое окно Мастер функций (рисунок 1) используется довольно часто. Поэтому опишем его подробнее. Окно состоит из двух связанных между собой списков: Категория и Функция. При выборе одного из элементов списка Категория в списке Функция появляется соответствующий ему перечень функций.

В Microsoft Excel функции разбиты  на 12 категорий. Категория 10 недавно  использовавшихся постоянно обновляется, в зависимости от того, какими функциями вы пользовались в последнее время. Она напоминает стековую память: новая вызванная вами функция, которая в этом списке еще не числилась, займет первую строку, вытеснив тем самым последнюю функцию.

Рисунок 1 - Диалоговое окно Мастер функций

Категория Полный алфавитный перечень содержит список всех функций Excel. Остальные категории функций  будут рассмотрены по мере их применения.

При выборе какой-либо функции  в нижней части диалогового окна появляется краткое ее описание. Нажав кнопку ОК или клавишу [Enter], можно вызвать панель выделенной функции.

Функция подсчета значений: СЧЁТ(значение1; значение2; ...)

Подсчитывает количество чисел в списке аргументов. Функция  СЧЁТ используется для получения количества числовых ячеек в диапазонах ячеек.

Значение1. Обязательный аргумент. Первый элемент, ссылка на ячейку или диапазон, для которого требуется  подсчитать количество чисел.

Значение2, ... Необязательный аргумент. Можно использовать до 255 дополнительных аргументов, ссылок на ячейки или диапазонов, в которых требуется подсчитать количество чисел.

2. Поиск значений в диапазоне

1. Поиск значений в списке по вертикали

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

Синтаксис:

ВПР(искомое_значение;таблица;номер_столбца;интервальный_ просмотр)

Искомое_значение — значение, которое должно быть найдено в  первом столбце табличного массива (Массив. Объект, используемый для получения нескольких значений в результате вычисления одной формулы или для работы с набором аргументов, расположенных в различных ячейках и сгруппированных по строкам или столбцам. Диапазон массива использует общую формулу; константа массива представляет собой группу констант, используемых в качестве аргументов.). Этот аргумент может быть значением или ссылкой. Если искомое_значение меньше, чем наименьшее значение в первом столбце табличного массива, функция ВПР возвращает значение ошибки #Н/Д.

Таблица — два или  более столбцов данных. Можно использовать ссылку на диапазон или имя диапазона. Значения в первом столбце аргумента  «таблица» — это значения, в  которых выполняется поиск аргумента «искомое_значение». Эти значения могут быть текстовыми, числовыми или логическими. Текстовые значения в нижнем и верхнем регистре считаются эквивалентными.

Номер_столбца — номер  столбца в аргументе «таблица», из которого возвращается соответствующее значение.

Интервальный_просмотр —  логическое значение, определяющее, какое  соответствие должна найти функция ВПР — точное или приблизительное. Если этот аргумент имеет значение ИСТИНА или опущен, то возвращается точное или приблизительное значение. Если точное соответствие не найдено, то возвращается наибольшее значение, которое меньше, чем искомое_значение. Значения в первом столбце аргумента «таблица» должны быть расположены в возрастающем порядке, иначе функция ВПР может возвратить неправильный результат. Если данный аргумент имеет значение ЛОЖЬ, функция ВПР ищет только точное соответствие. В этом случае сортировка значений в первом столбце аргумента «таблица» не обязательна. Если в этом первом столбце имеется два или более значений, соответствующих аргументу «искомое_значение», используется первое найденное значение. Если точное соответствие не найдено, возвращается значение ошибки #Н/Д.

Пример: В данном примере выполняется поиск значений в столбце «Плотность» для нахождения соответствующих значений в столбцах «Вязкость» и «Температура» в таблице атмосферных свойств. (Значения даны для воздуха при температуре 0°C и давлении в 1 атмосферу.)

Используя приблизительное  соответствие, функция ищет в столбце A значение 1, находит наибольшее значение, которое меньше или равно 1 и составляет 0,946, а затем возвращает значение из столбца C в той же строке (100)

Рисунок 1.2.1 - Поиск значения в диапазоне с помощью функции ВПР

2. Поиск значений в списке по горизонтали

Для выполнения этой задачи используется функция ГПР. Выполняет поиск значения в верхней строке таблицы или массива (Массив. Объект, используемый для получения нескольких значений в результате вычисления одной формулы или для работы с набором аргументов, расположенных в различных ячейках и сгруппированных по строкам или столбцам. Диапазон массива использует общую формулу; константа массива представляет собой группу констант, используемых в качестве аргументов.) значений и возвращает значение того же столбца в заданной строке таблицы или массива. Функция ГПР используется, когда сравниваемые значения расположены в верхней строке таблицы данных, а возвращаемые — на несколько строк ниже.

ГПР(искомое_значение;таблица;номер_строки;интервальный_просмотр)

Искомое_значение — значение, которое требуется найти в  первой строке таблицы. «Искомое_значение»  может быть значением, ссылкой или  текстовой строкой.

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

Номер_строки — номер  строки в массиве «таблица», из которой  будет возвращено сопоставляемое значение. Если значение аргумента «номер_строки» равно 1, то возвращается значение из первой строки аргумента «таблица», если оно равно 2 — из второй строки и т. д. Если значение аргумента «номер_строки» меньше 1, функция ГПР возвращает значение ошибки #ЗНАЧ!; если оно больше, чем количество строк в аргументе «таблица», возвращается значение ошибки #ССЫЛ!.

Интервальный_просмотр —  логическое значение, которое определяет, какое соответствие должна искать функция ГПР — точное или приблизительное. Если этот аргумент имеет значение ИСТИНА или опущен, возвращается приблизительно соответствующее значение; при отсутствии точного соответствия возвращается наибольшее из значений, меньших, чем «искомое_значение». Если этот аргумент имеет значение ЛОЖЬ, функция ГПР ищет точное соответствие. Если оно не найдено, возвращается значение ошибки #Н/Д.

Пример: Поиск значения 78 658р. в строке 1, нахождение наибольшего значения, меньшего 78 658р. (50 000р.), и возвращение значения из строки 2, находящегося в том же столбце, что и 50 000р. (20%)

Рисунок 1.2.2 – Поиск  значения с помощью функции ГПР

3. Ранг, процентный ранг, персентиль, квартиль

1. Функция РАНГ

Возвращает ранг числа в списке чисел. Ранг числа — это его величина относительно других значений в списке. (Если отсортировать список, то ранг числа будет его позицией.)

РАНГ(число;ссылка;порядок)

Число — число, для  которого определяется ранг.

Ссылка — массив или  ссылка на список чисел. Нечисловые значения в ссылке игнорируются.

Порядок — число, определяющее способ упорядочения.

Если значение аргумента  «порядок» равно 0 или опущено, ранг числа определяется в Microsoft Excel так, как если бы ссылка была списком, отсортированным в порядке убывания.

Если значение аргумента  «порядок» — любое число, кроме  нуля, то ранг числа определяется в Microsoft Excel так, как если бы ссылка была списком, отсортированным в порядке возрастания.

Функция РАНГ присваивает  повторяющимся числам одинаковые значения ранга. Однако наличие повторяющихся чисел влияет на ранги последующих чисел. Например, если в списке целых чисел, отсортированных по возрастанию, дважды встречается число 10, имеющее ранг 5, число 11 будет иметь ранг 7 (ни одно из чисел не будет иметь ранга 6).

Может потребоваться  использование определения ранга  с учетом связей в учетной записи. В предыдущем примере был взят пересмотренный ранг 5.5 для числа 10. Для этого следует добавить поправочный  коэффициент (см. ниже) к значению, возвращаемому функцией РАНГ. Данный поправочный коэффициент может применяться в обоих случаях: когда ранг вычисляется в порядке убывания (аргумент «порядок» имеет нулевое значение или опущен) и в порядке возрастания (значение аргумента «порядок» не равно нулю).

Поправочный коэффициент для связанных рангов = [СЧЕТ(ссылка) + 1 – РАНГ(число, ссылка, 0) – РАНГ(число, ссылка, 1)]/2.

Пример: РАНГ(A2,A1:A5,1) равен 3. Поправочный коэффициент равен (5 + 1 – 2 – 3)/2 = 0,5, а ранг, пересмотренный с учетом связей в учетной записи, равен 3 + 0,5 = 3,5. Если то или иное число появляется в ссылке только один раз, поправочный коэффициент будет равен 0, поскольку РАНГ для связи не будет изменяться.

 

Пример 1.3.1 – Поиск значения в диапазоне с помощью функции РАНГ

2. Функция ПРОЦЕНТРАНГ

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

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

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

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

Разрядность — необязательное значение, определяющее количество значащих цифр для возвращаемого процентного значения. Если этот аргумент опущен, то функция ПРОЦЕНТРАНГ использует три цифры (0,xxx).

Если массив пуст, функция  ПРОЦЕНТРАНГ возвращает значение ошибки #ЧИСЛО!. Если разрядность < 1, функция ПРОЦЕНТРАНГ возвращает значение ошибки #ЧИСЛО!. Если x не соответствует ни одному из значений аргумента «массив», функция ПРОЦЕНТРАНГ производит интерполяцию и возвращает корректное значение процентного содержания.

 

 

 

 

Пример:

Рисуно 1.3.2 – Поиск значения в диапазоне с помощью функции ПРОЦЕНТРАНГ

3. Функция ПЕРСЕНТИЛЬ

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

ПЕРСЕНТИЛЬ(массив;k)

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

k — значение персентили  в интервале от 0 до 1 включительно.

Если массив пуст или  содержит более 8191 точек данных, функция  ПЕРСЕНТИЛЬ возвращает значение ошибки #ЧИСЛО!. Если k не является числом, функция ПЕРСЕНТИЛЬ возвращает значение ошибки #ЗНАЧ!. Если k < 0 или k > 1, функция ПЕРСЕНТИЛЬ возвращает значение ошибки #ЧИСЛО!. Если k не кратно 1/(n - 1), функция ПЕРСЕНТИЛЬ производит интерполяцию для определения значения k-ой персентили.

 

Пример:

Рисунок 1.3.3 - Поиск значения в диапазоне с помощью функции  ПЕРСЕНТИЛЬ

4. Функция КВАРТИЛЬ

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

КВАРТИЛЬ(массив;часть)

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

Часть — значение, которое  требуется вернуть.

 

Если часть равна	КВАРТИЛЬ возвращает
0	Минимальное значение
1	Первую квартиль (25-ю персентиль)
2	Значение медианы (50-ю персентиль)
3	Третью квартиль (75-ю персентиль)
4	Максимальное значение

 

Если массив пуст, функция КВАРТИЛЬ возвращает значение ошибки #ЧИСЛО!. Если значение аргумента «часть» не является целым числом, то оно усекается. Если часть < 0 или часть > 4, функция КВАРТИЛЬ возвращает значение ошибки #ЧИСЛО!. Функции МИН, МЕДИАНА и МАКС возвращают то же значение, что и функция КВАРТИЛЬ, если аргумент «часть» равен соответственно 0, 2 или 4.

 

Пример:

Рисунок 1.3.4 - Поиск значения в диапазоне с помощью функции  КВАРТИЛЬ

4. Функция ПЕРЕСТ: вычисление количества размещений

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

ПЕРЕСТ(число;число_выбранных)

Число — целое число, задающее количество объектов.

Число_выбранных —  целое число, задающее количество объектов в каждом размещении.

Оба аргумента усекаются  до целых. Если число или число_выбранных  не является числом, то функция ПЕРЕСТ возвращает значение ошибки #ЗНАЧ!. Если число ≤ 0 или число_выбранных < 0, функция ПЕРЕСТ возвращает значение ошибки #ЧИСЛО!. Если число < число_выбранных, то функция ПЕРЕСТ возвращает значение ошибки #ЧИСЛО!.

Уравнение для числа  размещений имеет следующий вид:

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

Рисунок 1.4 - Поиск значения в диапазоне с помощью функции ПЕРЕСТ

5. Вычисление средних значений

1. Функция СРЗНАЧ

Возвращает среднее  значение (среднее арифметическое) аргументов. СРЗНАЧ(число1, [число2],...)

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

Число2, ... Необязательный аргумент. Дополнительные числа, ссылки на ячейки или диапазоны, для которых  требуется вычислить среднее значение. Аргументов может быть не более 255.

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

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

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

Среднее значение — это  среднее арифметическое, которое  вычисляется путем сложения набора чисел с последующим делением полученной суммы на их количество. Например, средним значением для чисел 2, 3, 3, 5, 7 и 10 будет 5, которое является результатом деления их суммы, равной 30, на их количество, равное 6.

Медиана — это число, которое является серединой множества чисел, то есть половина чисел имеют значения большие, чем медиана, а половина чисел имеют значения меньшие, чем медиана. Например, медианой для чисел 2, 3, 3, 5, 7 и 10 будет 4.

Мода — это число, наиболее часто встречающееся в  данном наборе чисел. Например, модой для чисел 2, 3, 3, 5, 7 и 10 будет 3.

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

 

 

 

 

 

 

 

 

 

Пример:

Рисунок 1.5.1 - Поиск значения в диапазоне с помощью функции СРЗНАЧ

2. Функция СРЗНАЧЕСЛИ

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

Возвращает среднее  значение (среднее арифметическое) всех ячеек в диапазоне, которые соответствуют данному условию.

СРЗНАЧЕСЛИ(диапазон, условие, «диапазон_усреднения»)

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

Условие — условие в форме числа, выражения, ссылки на ячейку или текста, которое определяет ячейки, участвующие в вычислении среднего. Например, условие может быть выражено следующим образом: 32, "32", ">32", "яблоки" или B4.

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

Ячейки в диапазоне, которые содержат значения ИСТИНА или  ЛОЖЬ, игнорируются. Если ячейка в «диапазоне_усреднения» пустая, функция СРЗНАЧЕСЛИ игнорирует ее. Если диапазон является пустым или текстовым значением, то функция СРЗНАЧЕСЛИ возвращает значение ошибки #ДЕЛ/0!. Если ячейка в условии пустая, «СРЗНАЧЕСЛИ» обрабатывает ее как ячейки со значением 0. Если ни одна ячейка в диапазоне не соответствует условию, функция СРЗНАЧЕСЛИ возвращает ошибку «#ДЕЛ/0!».