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

    В данном случае следует иметь в виду, что при такой обработке MS EXCEL  позволяет иметь не более 16 независимых переменных (исходных данных). Если фактически количество влияющих параметров превышает эту величину, следует из всего множества параметров выделить те, влияние которых на конечный результат несущественно. Естественно, следует заранее предусмотреть цель использования полученных таким способом зависимостей. В данной задаче число переменных равно 33, следовательно, установить несущественно влияющие переменные необходимо.

    Рассмотрим последовательность выполнения расчетов при обработке результатов вычислений.

1. В строке, расположенной ниже результатов вычислений, в столбце А будем последовательно проводить вычисления по каждому параметру. Начиная от столбца Е в этой строке (строка 62) последовательно запишем условные обозначения переменных величин. В данной задаче их достаточно много и они будут расписаны от столбца Е до столбца АК (рис. 4). В строке 63 запишем «среднее» значение переменных.

2. В той же последовательности в столбце А будем называть имя переменной, а в столбце D проводить вычисления стоимости проведения печи при различных значениях переменной. Так, при установлении степени влияния мощности пласта вычисления проводятся в ячейках от D64 до D69. Для этого в столбце Е (столбец выделен для переменной «мощность пласта») от строки 64 до строки 69 запишем возможные значения мощности пласта (в порядке возрастания, желательно не менее 6 значений), в данном случае от 0,8 м до 1,8 м.

3. В модель в блок исходных данных вводятся величины переменной «мощность пласта» в указанных выше значениях и результаты вычислений стоимости проведения 1 м печи заносятся в ячейки от D64 до D69. Важно, что при этих вычисления значения остальных переменных в модели должны быть установлены на «среднем» уровне.

Рис. 4 Фрагмент части листа модели при вычислении степени влияния переменных.

4. Устанавливается известными приемами MS EXCEL зависимость, в данном случае, стоимости проведения печи от мощности пласта. Уравнение такой зависимости записывается в столбце А  (y=869,02x+734,99).

5. В модели значение переменной «мощность пласта» устанавливается на «среднем» уровне, в данном случае 1,2 м. и все указанные выше пункты вычислений выполняются для следующей переменной – «ширина печи». Такие вычисления проводятся до переменной «глубина ведения работ» (строки 147 – 152).

6. Принцип отнесения переменных к существенно и не существенно влияющим на результат состоит в следующем.

6.1. Вычисляется процент изменения результата при изменении параметра на 100%. Если процент изменения расчетной величины менее 5% - влияние такой величины считать несущественным.

6.2. Вычисление степени влияния проводится согласно формулы

                S =100*((В_К-В_Н)/В_Н)/(П_К-П_Н)/П_Н),%

       где В_К и В_Н – соответственно вычисленные значения переменной при начальном (Н) и конечном (К) значении параметра,

                 П_К и П_Н – соответственно начальное и конечное значение параметра. [4]

          Так, для рисунка 4 составляющие этой формулы при вычислении степени влияния мощности пласта находятся в ячейках: В_К – D69, В_Н - D64, П_К – Е69, П_Н – Е64. Тогда в ячейке столбца А в строках, относящихся к мощности пласта запись будет иметь вид

                             =100*((D69-D64)/D64)/(E69-E64)/E64),%

Рис.5  Фрагмент части листа модели с вычисленными значениями степени влияния факторов

6.3. Если вычисленное значение превышает 5%, в ячейке под вычисленной величиной делается пометка – закрашенная ячейка в столбце А, согласно которой в дальнейшем будет переформатирована расчетная таблица. На рисунке 5 показан фрагмент таблицы вычислений.

7. Те переменные, влияние которых на результат выявилось несущественным, необходимо вынести за пределы таблицы вычислений. Таким образом, количество переменных, существенно влияющих на результат, остается равным 11.

8. Ранее были установлены зависимости затрат на проведение печи от каждого влияющего фактора и уравнения таких зависимостей указаны в столбце А. Если зависимость носит не линейный характер, то необходимо ввести дополнительные переменные, зависимость от которых искомой величины линейна. Так, как видно из рисунка 6, зависимость стоимости проведения от категории отбойности угля имеет вид

                          y = 12,44x² - 8,314x + 1739,2

где х – значение категории отбойности угля. В этом случае вводится дополнительная переменная х²_, т.е.квадрат задаваемого значения категории отбойности угля.

    В данном примере квадратичная зависимость стоимости проведения имеет место и для переменной «превышение температуры воздуха сверх нормативов», т.е. тоже вводится дополнительная переменная.

    На рисунке 6 приведен лист вычислений (столбец D) при изменении 14 переменных (две из них дополнительные). Переменные указаны в строке 62 в столбцах от Е до R.

    Для получения уравнения регрессии открываем в EXCEL «сервис» - «анализ данных» - «регрессия» - «ok». На всплывшей вкладке указываем «входной интервал по Y» - D64:D129 (выделяется столбец в этом интервале) и «входной интервал по Х – Е64:R129 (выделяется массив таблицы в этом интервале). После нажатия клавиши «ok» высвечивается новый лист с данными о коэффициентах при переменных и о свободном члене уравнения (таблица 6).  

Таблица 6 Коэффициенты регрессии

Рис. 6 Лист  с данными, подготовленными к обработке способом «регрессии» (начало)

Рис. 6 Лист с данными, подготовленными к обработке способом «регрессии» (средняя часть)

Рис. 6 Лист с данными, подготовленными к обработке способом «регрессии»  (окончание)

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

, грн/м

Условные обозначения указаны на рис. 6.