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

• Изучить корреляционную связь между стоимостными (физическими) объёмами по определенной товарной группе, оформленными различными таможнями юту за определенный период времени.

Решение.

• Построить сводную таблицу на основе листа с исходными данными со следующим макетом: поля «год», «месяц» поместить в строку, поле «таможня» в столбец, поле «стоимость» (или «вес») в данные; операция над данными – суммирование, общие итоги убрать.

• Оставить в сводной таблице 6 таможен с наибольшим количеством данных.

• Назвать лист «Корреляция»

• Построить квадратную таблицу (корреляционную матрицу), в которой заголовками строк и столбцов являются названия таможен (см. рисунок 12). В частности, для построения столбца с названиями таможен скопируйте их из сводной таблицы и установите курсор в левый верхний угол таблицы (на Рис.12 – это ячейка B31). Далее, в меню следует выбрать Правка – Специальная вставка. В появившемся окне диалога установить маркер напротив функции Транспонировать.

• На пересечении каждой строки и столбца вычислить коэффициент корреляции стоимостных (или физических) объемов, оформленных соответствующими таможнями. Для этого воспользоваться функцией КОРРЕЛ(*;*) (1 аргумент функции – массив данных по таможне из строки; 2 – массив данных по таможне из столбца). Например, если массив данных по Краснодарской таможне занимает ячейки С5:С28, а по Ростовской – F5:F28, то соответствующий коэффициент корреляции, расположенный на пересечении Краснодарской и Ростовской таможен, будет равен КОРРЕЛ (С5:С28; F5:F28). Пример представлен на рисунке 13.

• В результате должна получиться симметричная таблица с единицами на главной диагонали.

• Выбрать 2 таможни с наибольшим по модулю коэффициентом корреляции, охарактеризовать связь между таможнями, изобразить соответствующее корреляционное поле.

Решение.

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

• Скопировать данные по выбранным таможням в два расположенных рядом столбца. Первый столбец назвать X (факторный признак), а второй – Y (результативный признак); рядом с переменными в скобках указать названия таможен.

• Выделить столбцы с данными по выбранным таможням без заголовков.

• Нажать на пиктограмму «Мастер диаграмм» и выбрать в качестве типа диаграммы «Точечная». Затем нажать кнопку «Далее».

• В появившемся окне диалога перейти на закладку Ряд и в строке «Имя» указать «Стоимость» (или «Вес»). Затем нажать кнопку «Далее».

• В появившемся окне диалога перейти на закладку Заголовки. Назвать диаграмму «Корреляционное поле», на осях Х и Y подписать названия соответствующих таможен.

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

Решение.

• Правой кнопкой мыши щелкнуть по точечной диаграмме и в появившемся контекстном меню выбрать «Добавить линию тренда».

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

• Правой кнопкой мыши щелкнуть по уравнению регрессии на диаграмме и выбрать в контекстном меню «Формат подписей данных» и установить числовой формат с двумя знаками после запятой. Пример представлен на рисунке 14.

• На основе уравнения регрессии рассчитать теоретические значения по таможне, принятой за результативный признак ( Y ), в отдельном столбце. Для этого построить формулу с коэффициентами уравнения регрессии, подставив вместо X первое значение массива данных по таможне, принятой за X. Скопировать формулу вниз на весь столбец. В заголовок столбца записать уравнение регрессии. Пример представлен на рисунке 15.