Функция ЛГРФПРИБЛ  
Показать всеСкрыть все  
В регрессионном анализе вычисляется экспоненциальная кривая, аппроксимирующая данные, и возвращается массив значений, описывающий эту кривую. Поскольку данная функция возвращает массив значений, она должна вводиться как формула массива.  
 
Уравнение кривой имеет следующий вид:  
 
y = b*m^x или  
 
y = (b*(m1^x1)*(m2^x2)*_) (в случае нескольких значений x),  
 
где зависимые значения y являются функцией независимых значений x. Значения m являются основанием, возводимым в степень x, а значения b постоянны. Заметим, что y, x и m могут быть векторами. Функция ЛГРФПРИБЛ возвращает массив {mn;mn-1;...;m1;b}.  
 
Синтаксис  
 
ЛГРФПРИБЛ(известные_значения_y;известные_значения_x;конст;статистика)  
 
Известные_значения_y     — множество значений y, которые уже известны в соотношении y = b*m^x.  
 
Если массив известные_значения_y имеет один столбец, то каждый столбец массива известные_значения_x интерпретируется как отдельная переменная.  
Если массив известные_значения_y имеет одну строку, то каждая строка массива известные_значения_x интерпретируется как отдельная переменная.  
Известные_значения_x     — необязательное множество значений x, которые уже известны для соотношения y = b*m^x.  
 
Массив известные_значения_x может включать одно или более множеств переменных. Если используется только одна переменная, то известные_значения_y и известные_значения_x могут быть диапазонами любой формы, если только они имеют одинаковые размерности. Если используется более одной переменной, то аргумент известные_значения_y должен быть диапазоном ячеек высотой в одну строку или шириной в один столбец (так называемым вектором).  
Если аргумент известные_значения_x опущен, то предполагается, что это массив {1;2;3;...} такого же размера, как и известные_значения_y.  
Конст     — логическое значение, которое указывает, требуется ли, чтобы константа b была равна 1.  
 
Если аргумент «конст» имеет значение ИСТИНА или опущен, то b вычисляется обычным образом.  
Если аргумент «конст» имеет значение ЛОЖЬ, то b полагается равным 1 и значения m подбираются так, чтобы удовлетворить соотношению y = m^x.  
Статистика     — логическое значение, которое указывает, требуется ли вернуть дополнительную статистику по регрессии.  
 
Если аргумент «статистика» имеет значение ИСТИНА, функция ЛГРФПРИБЛ возвращает дополнительную статистику по регрессии, т. е. возвращает массив {mn;mn-1;...;m1;b:sen;sen-1;...;se1;seb:r 2;sey;F;df:ssreg;ssresid}.  
Если аргумент «статистика» имеет значение ЛОЖЬ или опущен, функция ЛГРФПРИБЛ возвращает только коэффициенты m и константу b.  
Более подробные сведения о дополнительной статистике по регрессии, см. в разделе, посвященном функции ЛИНЕЙН.  
 
Замечания  
 
Чем больше график ваших данных напоминает экспоненциальную кривую, тем лучше вычисленная кривая будет аппроксимировать данные. Подобно функции ЛИНЕЙН, функция ЛГРФПРИБЛ возвращает массив, который описывает зависимость между значениями, но ЛИНЕЙН подгоняет прямую линию к имеющимся данным, а ЛГРФПРИБЛ подгоняет экспоненциальную кривую. Дополнительные сведения см. в разделе, посвященном функции ЛИНЕЙН.  
Если имеется только одна независимая переменная x, то значения пересечения с осью y (b) можно получить непосредственно, используя следующую формулу:    
Пересечение с осью y (b):  
ИНДЕКС(ЛГРФПРИБЛ(известные_значения_y;известные_значения_x);2)  
 
Можно использовать уравнение y = b*m^x для предсказания будущих значений y, но в Microsoft Excel предусмотрена функция РОСТ для этой цели. Дополнительные сведения см. в разделе, посвященном функции РОСТ.  
 
Формулы, которые возвращают массивы, должны быть введены как формулы массива.  
При вводе массива констант в качестве, например, аргумента известные_значения_x, следует использовать точку с запятой для разделения значений в одной строке и двоеточие для разделения строк. Знаки-разделители могут быть различными в зависимости от параметров, заданных в окне Язык и стандарты на панели управления.  
Следует помнить, что значения y, предсказанные с помощью уравнения регрессии, могут быть недостоверными, если они находятся вне диапазона значений y, которые использовались для определения коэффициентов уравнения.  
Пример 1. Коэффициенты m и константа b  
 
Чтобы этот пример проще было понять, скопируйте его на пустой лист.  
 
Копирование примера  
Создайте пустую книгу или лист.Выделите пример в разделе справки.  Примечание.   Не выделяйте заголовок строки или столбца.  
   
Выделение примера в справкеНажмите клавиши CTRL+C.На листе выделите ячейку A1 и нажмите клавиши CTRL+V.Чтобы перейти от просмотра результатов к просмотру формул, возвращающих эти результаты, нажмите клавиши CTRL+` (апостроф) или на вкладке Формулы в группе Зависимости нажмите кнопку Показать формулы.  
     
1    
2    
3    
4    
5    
6    
7    
A B    
Месяц Единицы    
11 33 100    
12 47 300    
13 69 000    
14 102 000    
15 150 000    
16 220 000    
Формула Формула    
=ЛГРФПРИБЛ(B2:B7;A2:A7; ИСТИНА; ЛОЖЬ)    
   
Примечание.   Формулу в этом примере необходимо ввести как формулу массива. После копирования примера на пустой лист выделите диапазон A9:B9, начиная с ячейки, содержащей формулу. Нажмите клавишу F2, а затем — клавиши CTRL+SHIFT+ВВОД. Если формула не будет введена как формула массива, единственное значение будет равно 1,463275628.  
 
Если формула вводится как формула массива, возвращаются коэффициенты m и константа b.  
 
y = b*m1^x1 или, используя значения из массива:  
 
y = 495,3 * 1,4633x  
 
Можно оценить количество продаж в последующие месяцы либо подставив номер месяца в качестве x в это уравнение, либо воспользовавшись функцией РОСТ.  
 
Пример 2. Полная статистика  
 
Чтобы этот пример проще было понять, скопируйте его на пустой лист.  
 
Копирование примера  
Создайте пустую книгу или лист.Выделите пример в разделе справки.  Примечание.   Не выделяйте заголовок строки или столбца.  
   
Выделение примера в справкеНажмите клавиши CTRL+C.На листе выделите ячейку A1 и нажмите клавиши CTRL+V.Чтобы перейти от просмотра результатов к просмотру формул, возвращающих эти результаты, нажмите клавиши CTRL+` (апостроф) или на вкладке Формулы в группе Зависимости формул нажмите кнопку Показать формулы.  
     
1    
2    
3    
4    
5    
6    
7    
A B    
Месяц Единицы    
11 33 100    
12 47 300    
13 69 000    
14 102 000    
15 150 000    
16 150 000    
Формула    
=ЛГРФПРИБЛ(B2:B7;A2:A7; ИСТИНА; ИСТИНА)    
   
Примечание.   Формулу в этом примере необходимо ввести как формулу массива. После копирования примера на пустой лист выделите диапазон A9:B13, начиная с ячейки, содержащей формулу. Нажмите клавишу F2, а затем — клавиши CTRL+SHIFT+ВВОД. Если формула не будет введена как формула массива, единственное значение будет равно 1,463275628.  
 
Если формула вводится как формула массива, возвращается следующая статистика по регрессии. Используйте эту клавишу для определения нужной статистики.  
 
   
 
Можно использовать дополнительную статистику по регрессии (в приведенном выше примере — ячейки A10:B13), чтобы оценить, насколько полезно полученное уравнение для предсказания будущих значений.  
 
Важно.   Методы, которые используются для проверки уравнений, полученных с помощью функции ЛГРФПРИБЛ, такие же, как и для функции ЛИНЕЙН. Однако дополнительная статистика, которую возвращает функция ЛГРФПРИБЛ, основана на следующей линейной модели:  
 
ln y = x1 ln m1 + ... + xn ln mn + ln b  
 
Это следует помнить при оценке дополнительной статистики, особенно значений sei и seb, которые следует сравнивать с ln mi и ln b, а не с mi и b. Дополнительные сведения имеются в любом справочнике по математической статистике.  
 
=ЛГРФПРИБЛ(C2;B2;0;0)  
Функция ЛИНЕЙН  
Показать всеСкрыть все  
В этой статье описан синтаксис формулы и использование функции (Функция. Стандартная формула, которая возвращает результат выполнения определенных действий над значениями, выступающими в качестве аргументов. Функции позволяют упростить формулы в ячейках листа, особенно, если они длинные или сложные.) ЛИНЕЙН в Microsoft Office Excel. Дополнительные сведения о диаграммах и выполнении регрессионного анализа см. в разделе См. также.  
 
Описание  
Функция ЛИНЕЙН рассчитывает статистику для ряда с применением метода наименьших квадратов, чтобы вычислить прямую линию, которая наилучшим образом аппроксимирует имеющиеся данные и затем возвращает массив, который описывает полученную прямую. Функцию ЛИНЕЙН также можно объединять с другими функциями для вычисления других видов моделей, являющихся линейными по неизвестным параметрам, включая полиномиальные, логарифмические, экспоненциальные и степенные ряды. Поскольку возвращается массив значений, функция должна задаваться в виде формулы массива. Инструкции приведены в данной статье после примеров.  
 
Уравнение для прямой линии имеет следующий вид:  
 
y = mx + b  
 
или  
 
y = m1x1 + m2x2 + ... + b (в случае нескольких диапазонов значений x),  
 
где зависимое значение y — функция независимого значения x, значения m — коэффициенты, соответствующие каждой независимой переменной x, а b — постоянная. Обратите внимание, что y, x и m могут быть векторами. Функция ЛИНЕЙН возвращает массив {mn;mn-1;...;m1;b}. Функция ЛИНЕЙН может также возвращать дополнительную регрессионную статистику.  
 
Синтаксис  
ЛИНЕЙН(известные_y, [известные_x], [константа], [статистика])Функция ЛИНЕЙН имеет аргументы (Аргумент. Значение, предоставляющее информацию для действия, события, метода, свойства, функции или процедуры.), указанные ниже.
 
Известные_значения_y.    Обязательный аргумент. Множество значений y, которые уже известны для соотношения y = mx + b.  
Если массив известные_значения_y имеет один столбец, то каждый столбец массива известные_значения_x интерпретируется как отдельная переменная.  
Если массив известные_значения_y имеет одну строку, то каждая строка массива известные_значения_x интерпретируется как отдельная переменная.  
Известные_значения_x.    Необязательный аргумент. Множество значений x, которые уже известны для соотношения y = mx + b.  
Массив известные_значения_x может содержать одно или несколько множеств переменных. Если используется только одна переменная, то массивы известные_значения_y и известные_значения_x могут иметь любую форму — при условии, что они имеют одинаковую размерность. Если используется более одной переменной, то известные_значения_y должны быть вектором (т. е. интервалом высотой в одну строку или шириной в один столбец).  
Если массив известные_значения_x опущен, то предполагается, что это массив {1;2;3;...}, имеющий такой же размер, что и массив известные_значения_y.  
Конст.    Необязательный аргумент. Логическое значение, которое указывает, требуется ли, чтобы константа b была равна 0.  
Если аргумент конст имеет значение ИСТИНА или опущен, то константа b вычисляется обычным образом.  
Если аргумент конст имеет значение ЛОЖЬ, то значение b полагается равным 0 и значения m подбираются таким образом, чтобы выполнялось соотношение y = mx.  
Статистика.    Необязательный аргумент. Логическое значение, которое указывает, требуется ли возвратить дополнительную регрессионную статистику.  
Если аргумент статистика имеет значение ИСТИНА, функция ЛИНЕЙН возвращает дополнительную регрессионную статистику. Возвращаемый массив будет иметь следующий вид: {mn;mn-1;...;m1;b:sen;sen-1;...;se1;seb:r2;sey:F;df:ssreg;ssresid}.  
Если аргумент статистика имеет значение ЛОЖЬ или опущен, функция ЛИНЕЙН возвращает только коэффициенты m и постоянную b.  
Дополнительная регрессионная статистика.  
 
Величина Описание    
se1,se2,...,sen Стандартные значения ошибок для коэффициентов m1,m2,...,mn.    
seb Стандартное значение ошибки для постоянной b (seb = #Н/Д, если аргумент конст имеет значение ЛОЖЬ).    
r2 Коэффициент детерминированности. Сравниваются фактические значения y и значения, получаемые из уравнения прямой; по результатам сравнения вычисляется коэффициент детерминированности, нормированный от 0 до 1. Если он равен 1, то имеет место полная корреляция с моделью, т. е. различий между фактическим и оценочным значениями y нет. В противоположном случае, если коэффициент детерминированности равен 0, использовать уравнение регрессии для предсказания значений y не имеет смысла. Дополнительные сведения о способах вычисления r2, см. в подразделе "Замечания" в конце данного раздела.    
sey Стандартная ошибка для оценки y.    
F F-статистика или F-наблюдаемое значение. F-статистика используется для определения того, является ли случайной наблюдаемая взаимосвязь между зависимой и независимой переменными.    
df Степени свободы. Степени свободы полезны для нахождения F-критических значений в статистической таблице. Для определения уровня надежности модели необходимо сравнить значения в таблице с F-статистикой, возвращаемой функцией ЛИНЕЙН. Дополнительные сведения о вычислении величины df см. в подразделе "Замечания" в конце данного раздела. Далее в примере 4 показано использование величин F и df.    
ssreg Регрессионная сумма квадратов.    
ssresid Остаточная сумма квадратов. Дополнительные сведения о расчете величин ssreg и ssresid см. в подразделе "Замечания" в конце данного раздела.    
На приведенном ниже рисунке показано, в каком порядке возвращается дополнительная регрессионная статистика.  
 
   
 
Замечания  
Любую прямую можно описать ее наклоном и пересечением с осью y:    
Наклон (m):  
чтобы определить наклон прямой, обычно обозначаемый через m, нужно взять две точки прямой (x1,y1) и (x2,y2); наклон будет равен (y2 - y1)/(x2 - x1).  
 
Y-пересечение (b):  
Y-пересечением прямой, обычно обозначаемым через b, является значение y для точки, в которой прямая пересекает ось y.  
 
Уравнение прямой имеет вид y = mx + b. Если известны значения m и b, то можно вычислить любую точку на прямой, подставляя значения y или x в уравнение. Можно также воспользоваться функцией ТЕНДЕНЦИЯ.  
 
Если имеется только одна независимая переменная x, можно получить наклон и y-пересечение непосредственно, воспользовавшись следующими формулами:    
Наклон:  
ИНДЕКС(ЛИНЕЙН(известные_значения_y;известные_значения_x);1)  
 
Y-пересечение:  
ИНДЕКС(ЛИНЕЙН(известные_значения_y;известные_значения_x);2)  
 
Точность аппроксимации с помощью прямой, вычисленной функцией ЛИНЕЙН, зависит от степени разброса данных. Чем ближе данные к прямой, тем более точной является модель, используемая функцией. Функция ЛИНЕЙН использует для определения наилучшей аппроксимации данных метод наименьших квадратов. Когда имеется только одна независимая переменная x, значения m и b вычисляются по следующим формулам:    
   
 
   
 
где x и y – выборочные средние значения, например x = СРЗНАЧ(известные_значения_x), а y = СРЗНАЧ(известные_значения_y).  
 
Функции аппроксимации ЛИНЕЙН и ЛГРФПРИБЛ позволяют вычислить прямую или экспоненциальную кривую, наилучшим образом описывающую данные. Однако они не дают ответа на вопрос, какой из двух результатов больше подходит для решения поставленной задачи. Можно также вычислить функцию ТЕНДЕНЦИЯ(известные_значения_y; известные_значения_x) для прямой или функцию РОСТ(известные_значения_y; известные_значения_x) для экспоненциальной кривой. Эти функции, если не задавать аргумент новые_значения_x, возвращают массив вычисленных значений y для фактических значений x в соответствии с прямой или кривой. После этого можно сравнить вычисленные значения с фактическими значениями. Можно также построить диаграммы для визуального сравнения.  
Проводя регрессионный анализ, Microsoft Excel вычисляет для каждой точки квадрат разности между прогнозируемым значением y и фактическим значением y. Сумма этих квадратов разностей называется остаточной суммой квадратов (ssresid). Затем Microsoft Excel подсчитывает общую сумму квадратов (sstotal). Если конст = ИСТИНА или значение этого аргумента не указано, общая сумма квадратов будет равна сумме квадратов разностей действительных значений y и средних значений y. При конст = ЛОЖЬ общая сумма квадратов будет равна сумме квадратов действительных значений y (без вычитания среднего значения y из частного значения y). После этого регрессионную сумму квадратов можно вычислить следующим образом: ssreg = sstotal - ssresid. Чем меньше остаточная сумма квадратов, тем больше значение коэффициента детерминированности r2, который показывает, насколько хорошо уравнение, полученное с помощью регрессионного анализа, объясняет взаимосвязи между переменными. Коэффициент r2 равен отношению ssreg/sstotal.  
В некоторых случаях один или более столбцов X (пусть значения Y и X находятся в столбцах) не оказывают влияния на результаты при наличии других столбцов X. Иными словами, удаление одного или более столбцов X может привести к вычислению значений Y с прежней точностью. В этом случае избыточные столбцы X будут исключены из модели регрессии. Это явление называется коллинеарностью, поскольку избыточные столбцы X могут быть представлены в виде суммы нескольких неизбыточных столбцов. Функция ЛИНЕЙН проверяет на коллинеарность и удаляет из модели регрессии все избыточные столбцы X, если обнаруживает их. Удаленные столбцы X можно определить в выходных данных ЛИНЕЙН по коэффициенту, равному 0, и по значению se, равному 0. Удаление одного или более столбцов как избыточных изменяет величину df, поскольку она зависит от количества столбцов X, в действительности используемых для прогнозирования. Подробнее о вычислении величины df см. ниже в примере 4. При изменении df вследствие удаления избыточных столбцов значения sey и F также изменяются. Часто использовать коллинеарность не рекомендуется. Однако ее следует применять, если некоторые столбцы X содержат 0 или 1 в качестве индикатора, указывающего, входит ли предмет эксперимента в отдельную группу. Если конст = ИСТИНА или значение этого аргумента не указано, функция ЛИНЕЙН вставляет дополнительный столбец X для моделирования точки пересечения. Если имеется столбец со значениями 1 для указания мужчин и 0 — для женщин, а также имеется столбец со значениями 1 для указания женщин и 0 — для мужчин, то последний столбец удаляется, поскольку его значения можно получить из столбца с "индикатором пола".  
Вычисление значения df для случаев, когда столбцы X удаляются из модели вследствие коллинеарности происходит следующим образом: если существует k столбцов известных_значений_x и значение конст = ИСТИНА или не указано, то df = n – k – 1. Если конст = ЛОЖЬ, то df = n - k. В обоих случаях удаление столбцов X вследствие коллинеарности увеличивает значение df на 1.  
Формулы, которые возвращают массивы, должны быть введены как формулы массива.  
При вводе массива констант в качестве, например, аргумента известные_значения_x следует использовать точку с запятой для разделения значений в одной строке и двоеточие для разделения строк. Знаки-разделители могут быть различными в зависимости от параметров, заданных в окне Язык и региональные стандарты на панели управления.  
Следует отметить, что значения y, предсказанные с помощью уравнения регрессии, возможно, не будут правильными, если они располагаются вне интервала значений y, которые использовались для определения уравнения.  
Основной алгоритм, используемый в функции ЛИНЕЙН, отличается от основного алгоритма функций НАКЛОН и ОТРЕЗОК. Разница между алгоритмами может привести к различным результатам при неопределенных и коллинеарных данных. Например, если точки данных аргумента известные_значения_y равны 0, а точки данных аргумента известные_значения_x равны 1, то:    
Функция ЛИНЕЙН возвращает значение, равное 0. Алгоритм функции ЛИНЕЙН используется для возвращения подходящих значений для коллинеарных данных, и в данном случае может быть найден по меньшей мере один ответ.  
Функции НАКЛОН и ОТРЕЗОК возвращают ошибку #ДЕЛ/0!. Алгоритм функций НАКЛОН и ОТРЕЗОК используется для поиска только одного ответа, а в данном случае их может быть несколько.  
Помимо вычисления статистики для других типов регрессии с помощью функции ЛГРФПРИБЛ, для вычисления диапазонов некоторых других типов регрессий можно использовать функцию ЛИНЕЙН, вводя функции переменных x и y как ряды переменных х и у для ЛИНЕЙН. Например, следующая формула:    
=ЛИНЕЙН(значения_y, значения_x^СТОЛБЕЦ($A:$C))  
 
работает при наличии одного столбца значений Y и одного столбца значений Х для вычисления аппроксимации куба (многочлен 3-й степени) следующей формы:  
 
y = m1*x + m2*x^2 + m3*x^3 + b  
 
Формула может быть изменена для расчетов других типов регрессии, но в отдельных случаях требуется корректировка выходных значений и других статистических данных.  
 
Пример 1  
Наклон и Y-пересечение  
 
Чтобы лучше понять этот пример, скопируйте его на пустой лист.  
 
Копирование примера  
Выделите пример в этом разделе. При копировании примера в приложение Excel Web App выполняйте копирование и вставку по одной ячейке за раз. Важно.   Не выделяйте заголовок строки или столбца.  
   
Выделение примера в справкеНажмите сочетание клавиш CTRL+C.Создайте пустую книгу или лист.Выделите на листе ячейку A1 и нажмите сочетание клавиш CTRL+V. При работе в Excel Web App повторите копирование и вставку для всех ячеек в примере. Важно.   Чтобы пример работал без ошибок, необходимо вставить его на листе в ячейку A1.  
Чтобы переключиться между просмотром результатов и просмотром формул, возвращающих эти результаты, нажмите клавиши CTRL+` (апостроф) или на вкладке Формулы в группе Зависимости формул нажмите кнопку Показать формулы.После копирования на чистый лист пример можно адаптировать под конкретные требования.  
 
--------------------------------------------------------------------------------  
 
     
1    
2    
3    
4    
5    
6    
7    
A B C    
Известные значения y Известные значения x    
1 0    
9 4    
5 2    
7 3    
Формула Формула Результат    
=ЛИНЕЙН(A2:A5;B2:B5;;ЛОЖЬ)  A7=2, B7=1    
   
Важно.   Формулу в этом примере необходимо ввести как формулу массива. После копирования примера в пустой лист выделите диапазон A7:B7, начиная с ячейки, содержащей формулу. Нажмите клавишу F2, а затем — клавиши CTRL + SHIFT + ВВОД. Если формула не будет введена как формула массива, единственным результатом будет значение 2.  
 
Если формула вводится как формула массива, возвращается наклон (2) и y-пересечение (1).  
 
Пример 2  
Простая линейная регрессия  
 
Чтобы лучше понять этот пример, скопируйте его на пустой лист.  
 
Копирование примера  
Выделите пример в этом разделе. При копировании примера в приложение Excel Web App выполняйте копирование и вставку по одной ячейке за раз. Важно.   Не выделяйте заголовок строки или столбца.  
   
Выделение примера в справкеНажмите сочетание клавиш CTRL+C.Создайте пустую книгу или лист.Выделите на листе ячейку A1 и нажмите сочетание клавиш CTRL+V. При работе в Excel Web App повторите копирование и вставку для всех ячеек в примере. Важно.   Чтобы пример работал без ошибок, необходимо вставить его на листе в ячейку A1.  
Чтобы переключиться между просмотром результатов и просмотром формул, возвращающих эти результаты, нажмите клавиши CTRL+` (апостроф) или на вкладке Формулы в группе Зависимости формул нажмите кнопку Показать формулы.После копирования на чистый лист пример можно адаптировать под конкретные требования.  
 
--------------------------------------------------------------------------------  
 
     
1    
2    
3    
4    
5    
6    
7    
8    
 
9    
A B C    
Месяц Продажи    
1 3100    
2 4500    
3 4400    
4 5400    
5 7500    
6 8100    
Формула Описание Результат    
=СУММ(ЛИНЕЙН(B2:B7; A2:A7)*{9;1}) Оценивает объем продаж за девятый месяц 11000    
   
В общем случае СУММ({m;b}*{x;1}) равняется mx + b, то есть значению y для данного значения x. Для этих же целей можно воспользоваться функцией ТЕНДЕНЦИЯ.  
 
Пример 3  
Множественная линейная регрессия  
 
Предположим, что застройщик оценивает стоимость группы небольших офисных зданий в традиционном деловом районе.  
 
Застройщик может воспользоваться множественным регрессионным анализом для оценки цены офисного здания в заданном районе на основе следующих переменных.  
 
Переменная Смысл переменной    
y Оценочная цена здания под офис    
x1 Общая площадь в квадратных метрах    
x2 Количество офисов    
x3 Количество входов    
x4 Время эксплуатации здания в годах    
В этом примере предполагается, что существует линейная зависимость между каждой независимой переменной (x1, x2, x3 и x4) и зависимой переменной (y), т. е. ценой здания под офис в данном районе.  
 
Застройщик наугад выбирает 11 зданий из имеющихся 1500 и получает данные, которые приведены ниже. «0,5» входа означает вход только для доставки корреспонденции.  
 
Чтобы лучше понять этот пример, скопируйте его на пустой лист.  
 
Копирование примера  
Выделите пример в этом разделе. При копировании примера в приложение Excel Web App выполняйте копирование и вставку по одной ячейке за раз. Важно.   Не выделяйте заголовок строки или столбца.  
   
Выделение примера в справкеНажмите сочетание клавиш CTRL+C.Создайте пустую книгу или лист.Выделите на листе ячейку A1 и нажмите сочетание клавиш CTRL+V. При работе в Excel Web App повторите копирование и вставку для всех ячеек в примере. Важно.   Чтобы пример работал без ошибок, необходимо вставить его на листе в ячейку A1.  
Чтобы переключиться между просмотром результатов и просмотром формул, возвращающих эти результаты, нажмите клавиши CTRL+` (апостроф) или на вкладке Формулы в группе Зависимости формул нажмите кнопку Показать формулы.После копирования на чистый лист пример можно адаптировать под конкретные требования.  
 
--------------------------------------------------------------------------------  
 
     
 
1    
2    
3    
4    
5    
6    
7    
8    
9    
10    
11    
12    
13    
 
14    
A B C D E    
Общая площадь (x1) Количество офисов (x2) Количество входов (x3) Время эксплуатации (x4) Оценочная цена (y)    
2310 2 2 20 142 000    
2333 2 2 12 144 000    
2356 3 1,5 33 151 000    
2379 3 2 43 150 000    
2402 2 3 53 139 000    
2425 4 2 23 169 000    
2448 2 1,5 99 126 000    
2471 2 2 34 142 900    
2494 3 3 23 163 000    
2517 4 4 55 169 000    
2540 2 3 22 149 000    
Формула        
=ЛИНЕЙН(E2:E12; A2:D12; ИСТИНА; ИСТИНА)        
   
Важно.   Формулу в этом примере необходимо ввести как формулу массива. После копирования примера на пустой лист выделите диапазон A14:E18, начиная с ячейки, содержащей формулу. Нажмите клавишу F2, а затем — клавиши CTRL+SHIFT+ВВОД. Если формула не будет введена как формула массива, единственное значение будет равно -234,2371645.  
 
Если формула вводится как формула массива, возвращается следующая статистика по регрессии. Воспользуйтесь этой клавишей для определения нужной статистики.  
 
   
 
Уравнение множественной регрессии y = m1*x1 + m2*x2 + m3*x3 + m4*x4 + b теперь может быть получено из строки 14:  
 
y = 27,64*x1 + 12,530*x2 + 2,553*x3 - 234,24*x4 + 52,318  
 
Теперь застройщик может определить оценочную стоимость здания под офис в том же районе (здание имеет площадь 2500 квадратных метров, три офиса, два входа, построено 25 лет назад, используя следующее уравнение:  
 
y = 27,64*2500 + 12530*3 + 2553*2 - 234,24*25 + 52318 = 158 261 р.  
 
Также можно скопировать следующую таблицу в ячейку A21 листа, созданного для данного примера.  
 
Общая площадь (x1) Количество офисов (x2) Количество входов (x3) Время эксплуатации (x4) Оценочная цена (y)    
2500 3 2 25 =D14*A22 + C14*B22 + B14*C22 + A14*D22 + E14    
Это значение может быть также вычислено с помощью функции ТЕНДЕНЦИЯ.  
 
Пример 4  
Использование статистик F и r2  
 
В предыдущем примере коэффициент детерминированности r2 равен 0,99675 (см. ячейку A17 в результатах функции ЛИНЕЙН), что указывает на сильную зависимость между независимыми переменными и продажной ценой. Можно использовать F-статистику, чтобы определить, является ли этот результат (с таким высоким значением r2) случайным.  
 
Предположим, что на самом деле взаимосвязи между переменными не существует, просто статистический анализ вывел сильную взаимозависимость по взятой равномерной выборке 11 зданий. Величина «Альфа» используется для обозначения вероятности ошибочного вывода о существовании сильная взаимозависимости.  
 
В выходных данных функции ЛИНЕЙН величины F и df используются для оценки вероятности случайного получения наибольшего значения F. Величину F можно сравнить с критическими значениями в публикуемых таблицах F-распределения, либо для вычисления возможности случайного получения наибольшего значения F можно использовать функцию Microsoft Excel FРАСП. Соответствующее F-распределение имеет степени свободы v1 и v2. Если величина n представляет количество точек данных и аргумент конст имеет значение ИСТИНА или опущен, то v1 = n – df – 1 и v2 = df. (При конст = ЛОЖЬ v1 = n – df и v2 = df). Функция Microsoft Excel FРАСП(F; v1; v2) возвращает вероятность случайного получения наибольшего значения F. В примере 4 df = 6 (ячейка B18), а F = 459,753674 (ячейка A18).  
 
Предположим, что значение "Альфа" равно 0,05, v1 = 11 – 6 – 1 = 4 и v2 = 6, а критический уровень F равен 4,53. Поскольку значение F = 459,753674 намного больше 4,53, вероятность случайного получения такого большого значения F исключительно мала (при Альфа = 0,05 гипотеза об отсутствии связи между аргументами известные_значения_y и известные_значения_x отвергается, если значение F превышает критический уровень 4,53). Использование функции Microsoft Excel FРАСП дает возможность вычислять вероятность случайного получения больших значений F. Значение вероятности FРАСП(459,753674; 4; 6) = 1,37E-7 чрезвычайно мало. Из этого можно заключить через нахождение критического уровня F в таблице или использование функции Microsoft Excel FРАСП, что уравнением регрессии можно воспользоваться для предсказания оценочной стоимости зданий под офис в данном районе. Следует учесть, что использование правильных значений v1 и v2, вычисление которых показано в предыдущем абзаце, является критически важным.  
 
Пример 5  
Вычисление T-статистики  
 
Другой тест позволяет определить, подходит ли каждый коэффициент наклона для оценки стоимости здания под офис в примере 3. Например, чтобы проверить, имеет ли срок эксплуатации здания статистическую значимость, разделим -234,24 (коэффициент наклона для срока эксплуатации здания) на 13,268 (оценка стандартной ошибки для коэффициента времени эксплуатации из ячейки A15). Ниже приводится наблюдаемое t-значение:  
 
t = m4 ÷ se4 = -234,24 ÷ 13,268 = -17,7  
 
Если абсолютное значение t достаточно велико, можно сделать вывод, что коэффициент наклона можно использовать для оценки стоимости здания под офис в примере 3. В таблице ниже приведены абсолютные значения четырех наблюдаемых t-значений.  
 
Если обратиться к справочнику по математической статистике, то окажется, что t-критическое двустороннее с 6 степенями свободы равно 2,447 при Альфа = 0,05. Критическое значение также можно также найти с помощью функции Microsoft Excel СТЬЮДРАСПОБР. СТЬЮДРАСПОБР(0,05; 6) = 2,447. Поскольку абсолютная величина t, равная 17,7, больше, чем 2,447, срок эксплуатации — это важная переменная для оценки стоимости здания под офис. Аналогичным образом можно протестировать все другие переменные на статистическую значимость. Ниже приводятся наблюдаемые t-значения для каждой из независимых переменных.  
 
Переменная t-наблюдаемое значение    
Общая площадь 5,1    
Количество офисов 31,3    
Количество входов 4,8    
Возраст 17,7    
Абсолютная величина всех этих значений больше, чем 2,447. Следовательно, все переменные, использованные в уравнении регрессии, полезны для предсказания оценочной стоимости здания под офис в данном районе.  
 
 
=ЛИНЕЙН(C2;B2;0;0)