Сделать практические работы в excel, каждое в отдельном файле excel, задание в практической .

Практическая работа 1. Работа со статистическими таблицами

Задание: создать таблицу, занести в нее данные из выданного Вам варианта.

Ход работы.

1. Открываем новую книгу.

2. Делаем активной ячейку А1. Вносим в неё название Вашей таблицы.

3. Выделяем ячейку В2. Вносим в неё первую цифру из Вашего варианта.

4. Вносим все остальные данные Вашего варианта в столбец В.
5. В ячейке А2 вносим цифру 1, в ячейку А3 вносим цифру 2, затем делаем активными обе эти ячейки при помощи мыши (удерживая правую клавишу выделяем в «рамочку» обе ячейки, либо щелчком мыши делаем активной ячейку А2, зажимаем на клавиатуре клавишу Shift и щёлкаем мышью на ячейке А3.

6. Подводим мышью курсор к правому нижнему углу рамки выделения, наводим его на небольшой чёрный квадрат в этом углу рамки. При этом курсор меняется на жирный «+». Нажимаем левую клавишу мыши и, удерживая её, выделяем ячейки столбца А до последней цифры Вашего ряда данных. Отпускаем кнопку мыши. В результате получаем нумерацию от 1 до n (объём выборочной совокупности) в столбце А.

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

7. Переименуйте «Лист 1». Назовите его «Неранжированный ряд А»

8. Выделите столбцы А и В, скопируйте их и вставьте в «Лист 2». Лист 2 переименуйте: «Ранжированный ряд А»

9. Ранжирование ряда производим следующим образом: выделяем ячейки с данными (в ряду В), затем во вкладке «Главная» справа находим блок «Редактирование», в нём нажимаем кнопку «Сортировка и фильтр»

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

10. Посчитаем амплитуду значений в ряду данных. Для этого сделаем активной ячейку D2, напишем в ней «Амплитуда». Сделаем активной ячейку D3. Введём в неё формулу для расчёта амплитуды. Для этого нам необходимо из максимального значения вычесть минимальное. В ячейку вводим знак =, выделяем первое значение ранжированного ряда, ставим минус и выделяем последнее значение ранжированного ряда, нажимаем Ent r . В ячейке D3 отображается амплитуда ряда данных. Формулу расчёта Вы можете посмотреть в строке формул (см. рис. 2.1.5)

Практическая работа 2.  Составление интервальных рядов

1. Расчёт количества интервалов. Формула для расчёта k =5 lg n, где k – число интервалов, n – объём выборочной совокупности.

Ход работы:

 Копируем столбцы А и В с листа «Ранжированный ряд А» и вставляем их в «Лист 3». Переименовываем «Лист 3» в «Интервальный ряд А».

 Делаем активной ячейку D2, вводим текст: «Количество интервалов»

 Делаем активной ячейку D3, вводим формулу: =5*LOG10, затем открываем скобку, выделяем последнюю значимую ячейку в столбце А, закрываем скобку и нажимаем Ent r . В этой формуле * - это знак умножения, LOG10 – десятичный логарифм, в скобках у вас окажется объём выборочной совокупности (см. рис. 2.2.1)

Для удобства работы, примем количество интервалов для всех вариантов равным 6.

2. Расчёт ширины интервалов.

Формула расчёта ширины интервала: h=A/k=xmax-xmin/k, где h – ширина интервала, k – количество интервалов, A - амплитуда

Ход работы:

 Делаем активной ячейку D6, вводим текст «Ширина интервала»

 В ячейке D7 вводим формулу: ='Ранжированный ряд А'!D3/6 и нажимаем Ent r . Первая часть формулы содержит пересылку на лист «Ранжированный ряд А», ячейку D3, содержащую рассчитанную ранее амплитуду. Знак / обозначает деление, 6 – количество интервалов. Можно заменить в строке формул 'Ранжированный ряд А'!D3 непосредственно на амплитуду, представленную числом.

 Для того, чтобы округлить число в ячейке, необходимо щёлкнуть на активной ячейке правой клавишей мыши, выбрать в выпавшем меню строку «Формат ячейки». Эта же функция доступна в меню «Главная», блок «Ячейки». В этом блоке Вам необходимо выбрать кнопку «Формат», и из предложенных вариантов работы с форматом ячейки выбрать «Формат ячейки». В окне формата ячейки (см. рис. 2.2.3) выбираем вкладку «Число, в столбике предложенных числовых форматов выбираем формат «числовой», а справа выбираем необходимое число знаков после запятой. Если необходимо округлить число до целого, выбираем число десятичных знаков, равное нулю

3. Границы интервалов.

В ячейку D9 вводим текст «Интервалы». Готовим таблицу для 6 интервалов (см. рис. 2.2.4). В ячейке Е10 указываем xmin – это нижняя граница первого интервала. В ячейке F10 необходимо прописать формулу =E10+D7, где D7 – это ширина интервала, а E10 – это нижняя граница первого интервала.

Нижней границей 2 интервала является верхняя граница предыдущего интервала, увеличенная на единицу. Задаём в ячейке Е11 формулу =F10+1. Верхняя граница интервала задаётся в ячейке F11 следующей формулой: =E11+D7. Далее заполняем таблицу по аналогии.

Если всё выполнено верно, верхняя граница 6 интервала равна или больше xmax.

Практическая работа 3. Характеристики распределения

Для работы с характеристиками распределения создадим новый лист и обозначим его как «Характеристики распределения А».

Для работы нам потребуется ранжированный ряд, поэтому копируем его в лист «Характеристики распределения А». Левее оформляем таблицу эмпирического распределения (см. рис. 2.3.1), где в столбцах должны быть отражены рассчитанные Вами в предыдущей работе интервалы от большего значения к меньшему. Последним столбцом таблицы будет сумма характеристик распределения в ряду.

1. Абсолютные частоты

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

Ход работы:

В главном меню выбираем вкладку «Данные». Затем делаем активными ячейки столбца В, содержащие ранжированный ряд. После этого в блоке «Сортировка и фильтр» выбираем кнопку «Фильтр». Нажимаем её, и видим, что вверху ранжированного ряда появляется небольшая стрелочка вниз (см. рис. 2.3.2)

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

В появившееся окно вводим нижнюю границу первого интервала и нажимаем «ОК»

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

Для того, чтобы рассчитать абсолютную частоту второго интервала снова нажимаем «Фильтр», выбираем в числовых фильтрах «больше или равно». В окне «Пользовательский автофильтр» (см. рис. 2.3.6) в верхнем правом окне вводим нижнюю границу второго интервала, в нижнем левом окне выбираем «меньше или равно», в нижнем правом окне вводим верхнюю границу второго интервала. Нажимаем «ОК».

Обратите внимание, что у Вас должна стоять галочка на знаке «И», а не «ИЛИ».

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

По аналогии рассчитываем абсолютные частоты для остальных интервалов.

Теперь рассчитаем сумму абсолютных частот. Для этого сделаем активной ячейку, где должна отобразиться сумма, вернёмся в главном меню к вкладке «Главная», в блоке редактирование нажмём знак суммы (см. рис. 2.3.8) и Enter . В ячейке отобразится сумма абсолютных частот, которая, если всё сделано верно, должна совпасть с объёмом выборки n.

Эту же операцию можно проделать другим способом. Делаем активной ячейку, в которой должна отобразиться сумма, ставим знак =, нажимаем знак функции fx, и выбираем из предложенных вариантов функцию «СУММ», нажимаем «ОК». В появившемся новом окне вводим через точку с запятой необходимый диапазон ячеек либо выделяем нужные ячейки непосредственно в таблице (в нашем примере это ячейки E6;J6) и нажимаем «ОК». В результате, в активной ячейке отображается сумма всех абсолютных частот, а в строке формул отображается формула =СУММ(E6:J6).

2. Относительная частота

Формула расчёта относительной частоты: f,% = f/n•100%, где f – абсолютная частота интервала, n – объём выборки.

Ход работы:

Делаем активной ячейку Е7 (вторая строка таблицы эмпирического распределения, первый интервал). Вводим формулу в ячейку: нажимаем =, щелчком левой клавиши мыши выбираем ячейку с абсолютной частотой первого интервала, затем ставим знак / (деление), щёлчком мыши выделяем ячейку, отображающую объём выборки, ставим знак *(умножение) и вводим 100. В результате, формула имеет следующий вид: =E6/A31*100, где E6 – это ячейка с абсолютной частотой первого интервала, A31 – ячейка, отображающая объём выборки. Нажимаем .

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

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

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

Обратите внимание, что иногда математическая сумма всех целых чисел, отображающих относительные частоты интервалов, может оказаться не 100%, а 99 или 101% (в нашем примере 20+40+27+7+0+7=101, хотя в ячейке суммы указано 100%. Почему так получается? Дело в том, что даже при отображении в таблице целых чисел, программа «помнит», что на самом деле происходило их округление. В нашем примере в четвёртом и шестом интервалах относительная частота не 7%, а 6,67%, а в четвёртом интервале не 27%, а 26,67 %. Из-за округления у нас образовался «лишний» процент. Когда Вы будете оформлять научную работу, или использовать относительные частоты интервалов для оформления, например, статьи, не забудьте от этого «лишнего» процента избавиться. В нашем примере можно заменить 27 на 26%, либо в четвёртом и шестом интервалах принять относительную частоту за 6,5%.

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

3. Обеспеченность

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

Ход работы:

Когда у нас уже просчитаны абсолютные частоты, посчитать обеспеченность достаточно просто - достаточно сложить абсолютные частоты предыдущих интервалов с абсолютной частотой интервала, для которого рассчитывается обеспеченность. В таком варианте формулы для расчёта обеспеченности интервалов будут иметь следующий вид: =E8+F6, где E8 – обеспеченность за предыдущий интервал, F6 – абсолютная частота за интервал, для которого рассчитывается обеспеченность.

После того, как рассчитаете обеспеченность для второго интервала, достаточно сделать эту ячейку (в нашем примере это ячейка F7) активной, и потянуть за квадрат в её правом нижнем углу, распространяя таким образом формулу на все интервалы. Обеспеченность последнего интервала, если всё сделано верно, равна объёму выборки, поскольку все элементы выборки больше минимального значения или равны ему.

4. Относительная обеспеченность

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

Можно решить эту задачу и другим способом. Достаточно ввести формулу в ячейку относительной обеспеченности первого интервала (см. рис. 2.3.14):

=E8*100/K6,

где E8 – обеспеченность интервала, K6 – сумма всех абсолютных частот, равная объёму выборки. Аналогично считается относительная обеспеченность для всех остальных интервалов.

Обратите внимание, что относительная обеспеченность последнего интервала равна 100%.

5. Частость

Показывает частоту, выраженную в долях от единицы. Формула её расчёта: w=fi/n, где w – частость, fi – абсолютная частота, n – объём выборки.

Ход работы:

Делаем активной ячейку для расчёта частости первого интервала. Вводим формулу (см. рис. 2.3.15): =Е6/А31, где Е6 – абсолютная частота данного интервала, А31 – ячейка, в которой отображается объём выборки.

Нажимаем Enter . Повторяем формулу для каждого интервала.

В столбце «сумма» рассчитываем сумму всех частостей. Сумма должна быть равна 1.

После расчётов выделяем ячейки с частостями и меняем формат ячеек на числовой. Округление устанавливаем до двух знаков после запятой.

6. Плотность

Формула для расчёта плотности: d=fi/h, где d – плотность, fi – абсолютная частота, h – ширина интервала

 Ход работы:

Делаем активной ячейку для расчёта плотности первого интервала. Вводим формулу (см. рис. 2.3.16): =Е6/11, где Е6 – абсолютная частота данного интервала, 11 – это шаг интервала. Нажимаем Enter . Делаем ячейку с рассчитанной плотностью активной, наводим курсор на квадрат в правом нижнем углу активной ячейки и, удерживая левую клавишу мыши, распространяем формулу на все интервалы. Округляем полученные данные до сотых.

7. Относительная плотность

Формула для расчёта плотности: Δd=w/h, где Δd – относительная плотность, w –частость, h – ширина интервала

Ход работы:

Делаем активной ячейку для расчёта относительной плотности первого интервала. Вводим формулу (см. рис. 2.3.17):

=Е10/11, где Е10 – частость данного интервала, 11 – это шаг интервала. Нажимаем Enter . Делаем ячейку с рассчитанной относительной плотностью активной, наводим курсор на квадрат в правом нижнем углу активной ячейки и, удерживая левую клавишу мыши, распространяем формулу на все интервалы. Округляем полученные данные до сотых.