Вопрос по statistics, excel, excel-2003, regression – Отсутствующие значения в функциях MS Excel LINEST, TREND, LOGEST и GROWTH

3

Используя функцию GROWTH (или LINEST, TREND или LOGEST, все создают одну и ту же проблему) в Excel 2003. Но существует проблема, заключающаяся в том, что если некоторые данные отсутствуют, функция отказывается выдавать результат:

Вы можетескачать файл здесь.

Есть ли обходной путь? Ищете простое и элегантное решение.

Я неЯ не хочу очевидного обходного пути избавления от пропущенного значения - это будет означать удаление столбца, что также повредит график, и это создаст проблемы в других моих таблицах, где у меня будет больше строк и пропущенных данных в разных столбцах. Другой очевидный обходной путь - использовать одни данные для регрессии, а другие - для графика, но опять же, это раздражает и только вносит беспорядок в лист !!

Есть ли способ отличить - это значение NA?

Другая идея - пропустить пропущенные значения в выражении. Можно ли обратиться к набору ячеек, который не является непрерывным? Как вместо=GROWTH($B2:$AH2; $B1:$AH1; B1) как в моем примере, используйте что-то вроде:

=GROWTH({$B2:$I2,$K2:$AH2}; {$B1:$I1,$K1:$AH1}; B1)

Конечно, я хотел бы избежать написания своих собственных выражений. Мне нужно объяснить это моим коллегам, как все это сделать, и это будет гораздо сложнее. Я хочу простое и элегантное решение.

Ваш Ответ

3   ответа
2

Чтобы использовать ЛИНЕЙН с пропущенными значениями, вам нужно как обычно создать X-матрицу (r строк на c столбцов) и Y-вектор (r строк на один столбец). Вам также необходимо создать дополнительный столбец в X-матрице, который будет служить индикаторной переменной. Поместите этот столбец непосредственно слева от матрицы X. Таким образом, если матрица X начинается в столбце B, поместите дополнительный столбец в столбец A. Установите значение этого индикатора в ноль для каждой строки, которую вы хотите пропустить. Установите значение этого индикатора на единицу для каждой строки, которую вы хотите включить. Умножьте каждый второй столбец в X-матрице и Y-векторе на эту переменную индикатора. Поместите эту новую расширенную матрицу X и новый вектор Y в любое место таблицы. Теперь у вас должна быть новая X-матрица (r строк на c + 1 столбцов) и Y-вектор с прямыми нулями для каждой строки, которая будет пропущена. ЭТО КРИТИЧНО!

Теперь используйте функцию LINEST как обычно, указав весь Y-вектор и расширенный r ×(c + 1) X-матрица (с указанием столбца индикатора в качестве первых двух параметров функции "Ложь" (то есть ноль) в качестве третьего параметра и либо "ПРАВДА" (то есть один) или "ЛОЖНЫЙ" (то есть ноль) в качестве четвертого параметра функции. Правильные оценки параметров появляются в первом ряду вывода LINEST. Все остальные выходные значения LINEST неверны, за исключением значения в пятой строке и втором столбце (остаточная сумма квадратов), если вы указали "ПРАВДА" чтобы получить статистику.

Если вы указали четвертый параметр функции как "ПРАВДА" чтобы получить статистику, вам нужно исправить вывод на неверные значения. Значения в строках 2,3 и 4 расширенного вывода неверны; значение в строке 5 столбца 1 также неверно. Вы должны исправить их.

Сделайте копию первой строки ЛИНЕЙНОГО вывода в другом месте на рабочем листе. Если вы указалиПРАВДА" для статистики, затем зарезервируйте четыре пустых строки под этой копией. Скопировать значение столбца 2 строки 5 из исходного вывода LINEST в столбец 2 строки 5 нового выходного пространства

Шаг первый: Вычислите правильное число степеней свободы, чтобы заменить значение в строке 4 столбца 2 вывода LINEST. Найти количество параметров в модели; это с + 1. Вы можете использовать функцию COUNT для подсчета количества столбцов в расширенной X-матрице. Затем сложите все значения в столбце индикатора X-матрицы. Предположим, что четыре строки имеют все нулевые значения. Используйте функцию SUM: это дает r - 4 = количество строк с «1» в столбце индикатора. Правильные степени свободы - это разница: SUM (столбец индикатора) - COUNT (столбцы расширенной X-матрицы). Это значение, которое должно быть помещено в строку 4 столбца 2 нового выходного пространства.

Шаг второй: исправьте строку 2 и строку 3, столбец 2. Разделите неправильный d.f. (строка 4, столбец 2) в исходном выводе LINEST с правильным значением d.f. (строка 4, столбец 2) в новом выходном пространстве. Возьмите квадратный корень этого частного. Умножьте значения в строке 2 и в строке 3 столбца 2 в исходном пространстве вывода LINEST на этот поправочный коэффициент, чтобы получить правильные стандартные ошибки параметров и правильную стандартную ошибку Y.

Шаг третий: исправьте сумму квадратов регрессии. Исходный вывод LINEST имеет значение для суммы квадратов из-за не исправленной регрессии для среднего значения в строке 5 столбца 1 выходных данных; мы хотим, чтобы сумма квадратов регрессии была скорректирована на среднее значение. Нам нужно рассчитать поправку на среднее значение. Это сумма квадратов значений вектора Y, деленная на сумму значений столбца индикатора. Вычтите это значение из строки 5 столбца 1 исходного вывода LINEST и поместите ответ в строку 5 столбца 1 нового выходного пространства.

Шаг четвертый: Исправьте коэффициент F в строке 4 столбца 1. Нам нужно вычислить средние квадраты из-за регрессии и из-за невязок. Среднеквадратичное значение из-за регрессии (числитель в F-соотношении) - это значение в строке 5 столбца 1 нового выходного пространства, деленное на c - количество столбцов в исходной матрице X до увеличения. Среднеквадратичное значение из-за остатков (знаменатель в F-соотношении) - это строка 5 столбца 2 нового выходного пространства, разделенная на строку 4 столбца 2 нового выходного пространства. Вычислите коэффициент F из этих двух промежуточных значений и поместите результат в строку 4 столбца 1 нового выходного пространства.

Шаг пятый: Исправьте значение R-квадрата в строке 3 столбца 1. Это 1 - (строка 5 столбца 2, разделенная на сумму строки 5 столбца 1 и строки 5 столбца 2), используя значения из нового выходного пространства.

Проверьте свою работу: сделайте копию расширенной матрицы X и вектора Y в другом месте электронной таблицы. Замените любые записи на ноль для тех строк, которые имеют ноль в переменной индикатора. Удалите все ячейки в строках с нулями, сдвинув ячейки вверх. Теперь у вас должны быть X-матрица и вектор Y с меньшим количеством строк, но без пропущенных значений. Удалить столбец индикатора. Теперь используйте LINEST, чтобы запустить регрессионную модель для этого сокращенного набора данных, но на этот раз установите третий параметр в TRUE (включая константу). Эти результаты должны быть идентичны результатам, полученным в новом выходном пространстве.

Это может быть длинный ответ, но он очень хороший. Спасибо Yugmorf
Вау, это очень длинный ответ, кажется сложным. Не могли бы вы подвести итог в 3-4 строки, или, может быть, лучше, скачать мой лист Excel и продемонстрировать его на нем (вы можете загрузить его на любойбесплатный файлообменник) TMS
3

что это старый ... но если вы или кто-то еще может искать ответ, вы пытались использоватьFORECAST функционировать? Он рассчитает тренд с отсутствующими значениями (до тех пор, покат любой# N / A» клетки).

В моем случае мне нужно было создать график без пропусков с пропущенными значениями, но мне также нужно было рассчитать тренд по данным. Итак, сначала яd связать график с набором данных, который поместил # N / A для каждого пропущенного значения: например,IF(ISBLANK(B2),NA(),B2)

Но тогда яd рассчитать прогнозные числа с исходными данными:=FORECAST(B1,$B2:$AH2,$B1:$AH1)

Если только яЯ что-то упустил, что должно позаботиться об этом. В итоге вы получаете два ряда одинаковых чисел, но у одного есть пробелы дляFORECAST расчет, а другой заменяет каждый пробелNA() для графа.

0

Чтобы избежать разрыва на графике, поместите в ячейки, где отсутствуют данные=NA() функция - это производит#N/A error, и такие типы ошибок обрабатываются диаграммами точно так, как вы хотите: линия интерполируется между доступными точками, которые окружают отсутствующую. Узнайте больше здесь:http://www.j-walk.com/ss/excel/usertips/tip024.htmЕсли вам нужна линия тренда - зачемВы используете встроенную процедуру для этого? Я добавил экспоненциальный к вашим данным, и он соответствует 100% к вашимGROWTH расчетные значения. И это правильно обрабатывает#N/Aтакже. Чтобы убедиться, что линия тренда соответствует вашим данным - просто замените#N/A временно со средним из двух соседних ячеек (297 для вашего образца) - он будет рассчитыватьGROWTH сериал, а тыпосмотримS точно соответствует добавленной линии тренда. Читайте здесь о трендах:http://office.microsoft.com/en-001/excel-help/add-a-trendline-to-a-chart-HP005198462.aspx а такжеhttp://www.computergaga.com/excel/2003/intermediate/charts/add_a_trendline.html

Ваш файл с примененными решениями передается:https://www.dropbox.com/s/j7htrk9ih2jtcq6/TrendlineNA.xls

Надеюсь, это было полезно!

@ Томас Вместо необработанных данных с отсутствующей точкой дляGROWTH входные данные вы должны интерполировать промежуточную строку данных, используяAVERAGE из 2 соседних точек. Этот набор следует использовать тогда какGROWTH ввод - не ваши исходные данные. Если это не такЭто соответствует вашим потребностям - я полагаю, что проверка VBA - ваш единственный вариант. Peter L.
Питер, спасибо, но 1) не делаетt работают - ячейки B4, C4, ... AH4 вашего файла содержат ошибку, а не вычисленное значение !! Вы видите реальные ценности там? Если да, то какая у вас версия MS Excel? 2) встроенная экспоненциальная линия тренда показывает нечто отличное от логарифмической регрессии - см.artax.karlin.mff.cuni.cz/~ttel5535/pub/SOtrendlineTomas.xls TMS
позволь нампродолжить это обсуждение в чате Peter L.
объявление 1) тогда=NA() для меня бесполезно - у меня нет проблем с графиком! Только сGROWTH расчет объявления 2) но почему два эксп. линии отличаются моим примером выше? Там'Нет более подходящего типа линии тренда. А что ты имеешь в виду под "проверка ошибок аргументов функции РОСТ "? TMS

Похожие вопросы