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

1. Из всего диапазона отсортированных данных A4:C33 выделить мышью диапазон ячеек первой группы, для чего необходимо отсчитать в ранжированном ряду количество строк, соответствующее числу предприятий первой группы (графа 3 табл.2.2);
2. Нажать на панели инструментов кнопку для выбора цвета заливки;
3. Выбрать цвет заливки по собственному усмотрению;

►Внимание! Цвет желательно брать контрастный, чтобы четко отделить одну группу от другой.

4. Выполнить действия 1–3 для всех групп, выбирая контрастные цвета для цветовой заливки очередной группы.

      Результаты  работы алгоритмов 1.1 и 1.2 для демонстрационного  примера представлены в табл.2.1–ДП.

Алгоритм 1.3. Расчет суммарных  групповых значений результативного  признака

1. В ячейке (D41), выделенной для суммарного значения результативного признака Выпуск продукции первой группы, перед формулой поставить знак равенства «=»;
2. В качестве аргумента функции СУММ() указать диапазон ячеек из табл. 2.1 с результативными значениями у_i первой группы (визуально легко определяется по цвету заливки диапазона);

►Внимание! Здесь возможен ошибочный захват мышью столбца значений первого (факторного) признака Х. Необходимо проконтролировать правильность задания аргумента функции СУММ().

3. Enter;
4. Выполнить действия 1–3 поочередно для всех групп, используя цветовые заливки диапазонов.

Алгоритм 1.4. Расчет средних  групповых значений результативного  признака

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

1. В ячейке (Е41), выделенной для среднего значения результативного признака Выпуск продукции первой группы, перед формулой поставить знак равенства «=»;
2. Enter;
3. Выполнить действия 1–2 поочередно для всех групп;
4. В ячейках (C46, D46 и E46), выделенных для расчета итоговых сумм:

0. Перед формулой поставить знак равенства «=»;

• Enter.

     Результаты  работы алгоритмов 1.3 и 1.4 для демонстрационного  примера приведены в табл. 2.2–ДП.

Задача 2. Оценка тесноты  связи изучаемых  признаков на  основе эмпирического корреляционного отношения

Задача  решается в два этапа:

1. Расчет внутригрупповых дисперсий результативного признака.
2. Расчет эмпирического корреляционного отношения.

Алгоритм 2.1. Расчет внутригрупповых  дисперсий результативного  признака

1. В ячейке, выделенной для внутригрупповых дисперсий первой группы (D52), перед формулой поставить знак равенства «=»;
2. В качестве аргумента функции ДИСПР() указать диапазон ячеек из табл. 2.1 со значениями y_i первой группы – визуально легко определяется по цвету заливки диапазона;

►Внимание! Здесь возможен ошибочный захват мышью столбца значений первого (факторного признака) Х. Необходимо проконтролировать правильность задания аргумента функции ДИСПР().

3. Enter;
4. Выполнить действия 1–3 поочередно для всех групп, используя цветовые заливки диапазонов.
5. Для расчета итоговой суммы в табл. 2.3 (в ячейке C57) перед формулой необходимо поставить знак равенства «=»;
6. Enter.

      Результат работы алгоритма 2.1 для демонстрационного  примера представлен в табл.2.3–ДП.

Алгоритм 2.2. Расчет эмпирического  корреляционного  отношения

1. В ячейке, выделенной для общей дисперсии (А63), перед формулой поставить знак равенства «=»;
2. Enter;
3. В ячейке, выделенной для средней из внутригрупповых дисперсий (В63), перед формулой поставить знак равенства «=»;
4. Enter;

►Примечание. В случае если при выполнении вычисления в ячейке В63 выдается сообщение "Ошибка в формуле", то разделительный знак «,» между аргументами функции СУМПРОИЗВ(Д1,Д2) необходимо заменить на знак «;».

5. В ячейке, выделенной для значения межгрупповой (факторной) дисперсии (С63), перед формулой поставить знак равенства «=»;
6. Enter;
7. В ячейке, выделенной для эмпирического корреляционного отношения (D63), перед формулой поставить знак равенства «=»;
8. Enter.

      Результат работы алгоритма 2.2 для демонстрационного  примера представлен в табл.2.4–ДП.

Задание 2

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

Алгоритм  выполнения Задания 2

Алгоритм 1. Расчет параметров уравнения линейной регрессии и проверки адекватности модели исходным данным

1. Сервис => Анализ данных => Регрессия => ОК;
2. Входной интервал Y <= диапазон ячеек таблицы со значениями признака Y – Выпуск продукции (С4:С33);
3. Входной интервал X – диапазон ячеек таблицы со значениями признака X – Среднегодовая стоимость основных производственных фондов (В4:В33);
4. Метки в первой строке/Метки в первом столбце – НЕ активизировать;
5. Уровень надежности <= 68,3 (или 68.3);
6. Константа–ноль – НЕ активизировать;
7. Выходной интервал <= адрес ячейки заголовка первого столбца первой выходной результативной таблицы (А75);
8. Новый рабочий лист и Новая рабочая книга – НЕ активизировать;
9. Остатки – Активизировать;
10. Стандартизованные остатки – НЕ активизировать;
11. График остатков – НЕ активизировать;
12. График подбора – НЕ активизировать;
13. График нормальной вероятности – НЕ активизировать;

14. ОК.

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