Estoy usando el de CRECIMIENTO (o de ESTIMACION o TENDENCIA o LOGEST, todos hacen los mismos problemas) de la función en Excel 2003. Pero hay un problema, que si algunos de los datos que faltan, la función se niega a dar resultado:

Los valores que faltan en MS Excel ESTIMACION, TENDENCIA, LOGEST y de las funciones de CRECIMIENTO

Puede descarga el archivo aquí.

¿Hay alguna solución? Buscando solución fácil y elegante.

  1. No quiero que la obvia solución de deshacerse de la falta de valor que significaría para eliminar la columna y que también dañaría a la gráfica, y tendría problemas en mis otras mesas donde tengo más filas y de falta de datos en columnas diferentes. Obvio solución es utilizar uno de los datos para la regresión y el otro para la gráfica, pero de nuevo, esto es muy molesto y sólo hace lío en la hoja!!

  2. ¿Hay alguna forma de saber excell – este valor es NA?

  3. Otra idea sería omitir el valor que falta(s) en la expresión. Es posible abordar un conjunto de células que no es continua? Como lugar de =GROWTH($B2:$AH2; $B1:$AH1; B1) como en mi ejemplo, usar algo como:

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

  4. Me gustaría, por supuesto, como para evitar escribir mis propias expresiones. Tengo que explicar esto a mis colegas de cómo hacer todo esto y sería mucho más complicado. Quiero una solución fácil y elegante.

InformationsquelleAutor TMS | 2013-01-31

3 Comentarios

  1. 3

    Sé que esto es viejo…pero en caso de que usted o alguien más podría estar todavía en busca de una respuesta, ¿has probado a utilizar el FORECAST función? Se calculará la tendencia de los valores perdidos (siempre y cuando no las hay «#N/A» de las células).

    En mi caso, necesitaba crear un sin espacio gráfico con los valores que faltan, pero también necesitaba para calcular una tendencia de los datos. Así que primero me gustaría enlace de la gráfica de un conjunto de datos que se colocan en un #N/A por cada valor que falta:
    por ejemplo, IF(ISBLANK(B2),NA(),B2)

    Pero luego me gustaría calcular el pronóstico de los números con los datos originales:
    =FORECAST(B1,$B2:$AH2,$B1:$AH1)

    A menos que me estoy perdiendo algo, que debe cuidar de él. Básicamente terminas con dos filas de números idénticos, pero uno tiene espacios en blanco para los FORECAST de cálculo, y la otra reemplaza cada espacio en blanco con un NA() para el gráfico.

  2. 2

    Este resulta ser «trivial» si usted sabe el truco.

    El uso de ESTIMACION con valores perdidos, usted necesita para crear el X-matrix (r filas y c columnas) y Y-vector (r filas y una columna) como de costumbre. También es necesario crear una columna adicional en la X de la matriz de servir como un indicador de la variable. El lugar esta columna situada inmediatamente a la izquierda de la X de la matriz. Por lo tanto, si la X de la matriz de empezar en la columna B, poner la columna adicional en la columna A. Establecer este valor del indicador a cero para cada fila que desea omitir. Establezca este valor del indicador a uno por cada fila que se desea incluir. Multiplique cada columna en la X de la matriz y de la Y-vector por este indicador variable. Poner en marcha este nuevo aumentada X de la matriz y de nuevo Y de vectores en otro lugar de la hoja de cálculo. Usted debe ahora tener un nuevo X-matrix (r filas y c+1 columnas) y Y-vector con las filas de ceros en línea recta a través de cada fila que se omite. ESTO ES FUNDAMENTAL!

    Ahora el uso de la ESTIMACION de la función normal , especificando la totalidad de la Y-vector y la expansión de la r×(c+1) X-matrix (con el indicador de columna incluida como los dos primeros parámetros de la función, «Falso» (es decir, cero) como el tercer parámetro y lo «VERDADERO» (es decir, un) o «FALSE» (es decir, cero) como el cuarto parámetro de la función. La correcta estimación de parámetros aparecen en la primera fila de la ESTIMACION de salida. Todos los otros ESTIMACION de los valores de salida están mal, excepto el valor en la quinta fila y la segunda columna (la suma de cuadrados residual) si se especifica «TRUE» para obtener estadísticas.

    Si se especifica el cuarto parámetro de la función como «VERDADERO» para obtener las estadísticas, es necesario para corregir la salida para los valores incorrectos. Los valores de las filas 2, 3 y 4 de la ampliación de la salida son incorrectos; el valor de la fila 5, columna 1 es también incorrecta. Necesita solucionarlos.

    Hacer una copia de la primera fila de la ESTIMACION de salida en otro lugar de la hoja de cálculo. Si se especifica «TRUE» para las estadísticas, entonces la reserva de cuatro filas en blanco por debajo de esta copia. Copia la fila 5, columna 2 el valor de la original de ESTIMACION de salida a la fila 5, columna 2 del nuevo espacio de salida

    Primer paso: Calcular el número correcto de grados de libertad de sustituir el valor en la fila 4, columna 2 de la ESTIMACION de salida. Encontrar el número de parámetros en el modelo; esto es c+1. Usted podría utilizar la función COUNT para contar el número de columnas en la ampliación de la X-matriz. A continuación, agregue todos los valores en el indicador de la columna de la X-matriz. Supongamos que cuatro filas tienen todos los valores cero. Usar la función SUMA: esto le da r – 4 = número de filas con «1» en la columna del indicador. La correcta grados de libertad es la diferencia: SUMA(indicador de columna) – COUNT(aumentada X-matriz de columnas). Este es el valor que debe ser colocado en la fila 4, columna 2 del nuevo espacio de salida.

    Paso Dos: Revisión de la fila 2 y la fila 3 columna 2. Dividir el mal d.f. (la fila 4, columna 2) en el original ESTIMACION de salida por el correcto d.f. (la fila 4, columna 2) en el nuevo espacio de salida. Tomar la raíz cuadrada de este cociente. Varios de los valores en la fila 2 y en la fila 3 columna 2 en el original ESTIMACION espacio de salida por este factor de corrección para obtener el corregir los errores estándar de los parámetros y la correcta error estándar de Y.

    Paso Tres: Corregir la suma de los cuadrados de la regresión. El original de ESTIMACION de salida tiene el valor de la suma de cuadrados debido a la regresión no corregidos para la media de la fila 5, columna 1 de la salida; queremos que la suma de los cuadrados de la regresión corregido para la media. Tenemos que calcular la corrección de la media. Esta es la suma de la Y valores del vector al cuadrado, dividido por la suma de la columna del indicador de valores. Restar este valor en la fila 5, columna 1 de la original de ESTIMACION de salida y poner la respuesta en la fila 5, columna 1 del nuevo espacio de salida.

    Paso Cuatro: Corregir el F-ratio en la fila 4, columna 1. Tenemos que calcular la media de cuadrados debido a la regresión y debido a los residuos. La media de cuadrados debido a la regresión (numerador en la F-ratio) es el valor de la fila 5, columna 1 del nuevo espacio de salida dividida por c, el número de columnas en el original X de la matriz previo a la cirugía. La media de cuadrados debido a los residuos (denominador en la F-ratio) es la fila 5, columna 2 de la nueva salida de el espacio dividido por la fila 4, columna 2 del nuevo espacio de salida. Calcular la F-relación de estos dos valores intermedios y colocar el resultado en la fila 4, columna 1 del nuevo espacio de salida.

    Paso cinco: Corregir el R-cuadrado valor en la fila 3 columna 1. Este es 1 – (fila 5, columna 2, dividido por la suma de la fila 5 columna 1 y la fila 5, columna 2), utilizando los valores del nuevo espacio de salida.

    Comprobar su trabajo: Hacer una copia de la aumentada de la matriz X y y del vector en otro lugar de la hoja de cálculo. Reemplazar las entradas con cero para las filas que tienen cero en el indicador de la variable. Borrar todas las celdas en filas con ceros, desplazar celdas hacia arriba. Ahora debe tener un X-matriz y y del vector con menos filas, pero no los valores que faltan. Borrar el indicador de la columna. Ahora uso ESTIMACION para el abeto de un modelo de regresión para este conjunto reducido de datos, pero esta vez con el tercer parámetro a TRUE (incluir una constante). Estos resultados deben ser idénticos a los resultados que tenemos en el nuevo espacio de salida.

    • Wow, este es un muuuucho respuesta, parece complicado. Por favor puede resumir en 3-4 líneas, o tal vez mejor, descargar mi hoja de excel y demostrar en ella (que se puede cargar en cualquier gratis servicio de alojamiento de archivos)
    • Esta tal vez una respuesta larga, pero es que es muy buena. Gracias
  3. 0

    Mi solución consta de 2 partes:

    1. Para evitar la brecha en el gráfico poner en las células, donde faltan datos =NA() función – produce #N/A error, y tales tipos de error son manejados por los gráficos exactamente como usted desea: línea se interpola entre los puntos que rodean a una falta uno. Leer más aquí: http://www.j-walk.com/ss/excel/usertips/tip024.htm
    2. Si usted necesita una línea de tendencia – ¿por qué no se utiliza el incorporado en la rutina para esto? He añadido un exponencial uno a sus datos, y que se adapta al 100% a su GROWTH valores calculados. Y se maneja correctamente #N/A‘s así. Para asegurarse de que la línea de tendencia se ajuste a los datos – simplemente reemplazar #N/A temporalmente con un promedio de dos vecino células (297 para el ejemplo) – se calcula GROWTH serie entonces, y verás que es exactamente añadido de la línea de tendencia. Lea aquí acerca de las líneas de tendencia: http://office.microsoft.com/en-001/excel-help/add-a-trendline-to-a-chart-HP005198462.aspx y http://www.computergaga.com/excel/2003/intermediate/charts/add_a_trendline.html

    El archivo con las soluciones aplicadas es compartida: https://www.dropbox.com/s/j7htrk9ih2jtcq6/TrendlineNA.xls

    Esperanza, que fue muy útil!

    • Pedro, gracias, pero 1) no funciona – las celdas B4, C4, … AH4 de su archivo de contener el error, y no el valor calculado!! ¿Puedes ver los valores reales hay? Si sí, lo MS Excel versión tienes? 2) la incorporada en el exponencial de la línea de tendencia muestra algo diferente de la log-lineal de la regresión, consulte artax.karlin.mfp.cuni.cz/~ttel5535/pub/SOtrendlineTomas.xls
    • 1) =NA() sólo se aplica a la tabla, NO GROWTH cálculos. Función aún no DEBE tener errores en los datos de origen 2) Para su primera muestra de archivo de las líneas son idénticos – por favor vea la pantalla (yo reemplazado temporalmente de la celda vacía, con 297): floomby.ru/s1/FZPGS por lo Tanto tengo 2 opciones: 1) encontrar el adecuado a la línea de tendencia de tipo 2) incluir comprobaciones de errores para GROWTH los argumentos de la función, pero estoy bastante seguro de que las fórmulas no será sencillo.
    • ad 1), a continuación, =NA() es de ninguna utilidad para mí – yo no tengo ningún problema con el gráfico! Sólo con GROWTH cálculo ad 2) pero, ¿por qué los dos exp. líneas difieren en mi ejemplo anterior? No hay más apropiada de la línea de tendencia tipo. Y ¿qué entiende usted por «error de cheques para el CRECIMIENTO de los argumentos de la función»?
    • el propósito de =NA() es únicamente para hacer gráfico continuo – como en mi archivo de ejemplo. Y «comprobaciones de errores para el CRECIMIENTO de los argumentos de la función» medios por la falta de puntos que necesita para calcular la interpolación de valores mediante el uso de fórmulas adicionales – ya sea dentro de GROWTH o con datos adicionales fila para estos.
    • 1) yo no quiero hacer el gráfico continuo, por el contrario; 2) esto no se ve como una solución, porque para esto se necesita de regresión de primera y estamos en un círculo..
    • En lugar de los datos en bruto con el punto que falta para GROWTH de entrada debe interpolar datos intermedios fila utilizando AVERAGE de 2 vecino puntos. Este conjunto debe ser utilizado luego como GROWTH de entrada – no los datos originales. Si no se adapte a sus necesidades – supongo VBA validación es su única opción.
    • Pedro, el PROMEDIO de los 2 vecino puntos no dará el mismo resultado que la omisión de la punto – sólo mediante la regresión valor pronosticado para ese punto.
    • permítanos continuar esta discusión en el chat
    • Resumen (para los demás): no hemos de chat, problema sin resolver, esta respuesta no ofrece ninguna solución.

Dejar respuesta

Please enter your comment!
Please enter your name here