Это глава из книги: Майкл Гирвин. Ctrl+Shift+Enter. Освоение формул массива в Excel.
Предыдущая глава Оглавление Следующая глава
Функция ЛИНЕЙН рассчитывает статистику для ряда с применением метода наименьших квадратов, чтобы вычислить прямую линию, которая наилучшим образом аппроксимирует имеющиеся данные и затем возвращает массив, который описывает полученную прямую. Функция ЛИНЕЙН может также возвращать дополнительную регрессионную статистику (подробнее см. справку MS Excel).
Рис. 22.1. Четыре аргумента функции ЛИНЕЙН
Скачать заметку в формате Word или pdf, примеры в формате Excel
Линейная регрессия
На рис. 22.2 показан набор данных (он уже анализировался в главе 9, когда мы обсуждали функции НАКЛОН, ОТРЕЗОК, ПРЕДСКАЗ и ТЕНДЕНЦИЯ). Поскольку ЛИНЕЙН является функцией массива и вы хотите, чтобы она вернула два значения, выполните следующие действия:
- Выделите диапазон D2:Е2. Функция ЛИНЕЙН возвращает массив из двух значений, расположенных по горизонтали, но не по вертикали.
- Введите известные значения y. Это – баллы, которые студенты заработали на последнем тестировании.
- Введите известные значения х. Это количество часов, которые студенты потратили на подготовку к тестам.
- Опустите аргумент [конст].
- Опустите аргумент [статистика].
- Введите формулу с помощью Ctrl+Shift+Enter.
Рис. 22.2. Функция ЛИНЕЙН возвращает наклон и отрезок, если массив расположен в горизонтальном диапазоне
Рис. 22.3. Функция массива ЛИНЕЙН заменяет две отдельные функции – НАКЛОН и ОТРЕЗОК
Если вам всё же нужно вывести результаты функции ЛИНЕЙН в вертикальный массив, воспользуйтесь ухищрением (рис. 22.4).
Рис. 22.4. Формулы для вывода результатов в вертикальный массив
Если вы хотите отобразить не только наклон и отрезок, но и дополнительные статистики, выделите диапазон на один столбец больше, чем столбцов с переменными х, и высотой 5 строк. Как показано на рис. 22.5, поскольку у вас лишь одна переменная х, выделите диапазон Е2:F6 (2 столбца по 5 строк). Третьему и четвертому аргументам присвойте значения ИСТИНА: вы хотите, чтобы b считалось обычным образом, и хотите вывести дополнительные статистики. После ввода формулы нажатием Ctrl+Shift+Enter, результат должен соответствовать рис. 22.6 (подробнее о десяти статистиках см. Простая линейная регрессия).
Рис. 22.5. Когда требуется дополнительная статистика для одной переменной, выделите диапазон 2*5; функция ЛИНЕЙН вернет 10 значений; чтобы увеличить изображение кликните на нем правой кнопкой мыши и выберите Открыть картинку в новой вкладке
Рис. 22.6. Функция ЛИНЕЙН возвращает 10 статистик
В главе 8 было показано, как с помощью формулы преобразовать таблицу в столбец. На рис. 22.7 приведена формула, позволяющая представить результаты работы функции ЛИНЕЙН (которые она возвращает в диапазон 2*5) в вертикальном столбце.
Следующие элементы являются аргументами функции ИНДЕКС:
- аргумент массив: функция ЛИНЕЙН($B$2:$B$12;$A$2:$A$12;ИСТИНА;ИСТИНА) возвращает диапазон из пяти строк и двух столбцов.
- аргумент номер_строки: ОСТАТ(ЧСТРОК(E$1:E1)-1;5)+1 возвращает следующие значения 1,2,3,4,5,1,2,3,4,5 при копировании формулы вдоль столбца от Е1 до Е10.
- аргумент номер_столбца: ЦЕЛОЕ((ЧСТРОК(E$1:E1)-1)/5)+1 возвращает 1,1,1,1,1,2,2,2,2,2 при копировании формулы вдоль столбца от Е1 до Е10.
Рис. 22.7. Преобразование диапазона вывода формулы ЛИНЕЙН из 2*5 в вертикальный
Формула в Е1 не требует ввода с помощью Ctrl+Shift+Enter.
Множественная регрессия
В случае множественной регресии, когда значения y зависят от двух переменных х1 и х2, функция ЛИНЕЙН возвращает 12 статистик (подробнее см. Введение в множественную регрессию и Построение модели множественной регрессии). На рис. 22.8 используются следующие обозначения:
- y = зависимая переменная
- x1 = независимая переменная 1 = баллы за домашнее задание
- x2 = независимая переменная 2 = часов изучал последний столбец тест = гр.
Чтобы выполнить множественную регрессию:
- Выделите диапазон В3:D7 (число столобцов = число переменных +1; число строк всегда равно 5).
- Наберите формулу {=ЛИНЕЙН(D13:D23;B13:C23;ИСТИНА;ИСТИНА)}. Для аргумента известные_значения_х, выделите оба столбца значений x из диапазона В13:С23.
- Введите функцию с помощью клавиш Ctrl+Shift+Enter.
- Обратите внимание, что несмотря на то, что значения х1 указаны в диапазоне В13:С23 до значений х2, наклон сначала указан для х2.
Рис. 22.8. Для двух переменных x1 и х2 функция ЛИНЕЙН выполняет множественную регрессию
Если вас раздражают знаяения ошибки #Н/Д дополните вашу формулу функцией ЕСЛИОШИБКА (рис. 22.9).
Рис. 22.9. Вы можете избавиться от ошибок #Н/Д «обернув» ЛИНЕЙН функцией ЕСЛИОШИБКА
Пример с тремя переменными не должен вызвать затруднений (рис. 22.10).
Рис. 22.10. Множественная регрессия для трех независимых переменных
Добрый день!
У меня следующая ситуация: значения двух независимых переменных x1 и x2 содержаться на разных листах. Перенести их на один лист не получается, потому что наборов данных несколько сотен и делать для каждого набора отдельную вкладку — не вариант. Можно ли как-то обойти требование что x1 и x2 должны содержаться в едином диапазоне?
нет
Чтобы использовать в качестве факторов данные, расположенные на разных листах или просто даже в разных местах, нужно вместо выделения таблицы факторов, которой у вас нет, прописать формулу для этой матрицы. А это будет сумма матриц, состоящих из нулей и значений нужного фактора в нужной строке или столбце. Если зависимая переменная — строка, то умножаете каждый фактор слева на вектор-столбец из нулей и одной единицы в нужном месте — размерность вектора равна количеству факторов. Если зависимая переменная — столбец, то наоборот умножаете справа на вектор-строку. И складываете эти матрицы. И получится у вас тогда нужная таблица.