Excel: Creación de rango dinámico para uso en Correl-Function

Buenos días de Alemania,

Quiero hacer un análisis de correlación de 30/90 días de dos conjuntos de datos usando la función Correl en Microsoft Excel, pero actualmente estoy luchando para conseguir que dos funcione.

La función Correl en Microsoft Excel necesita dos rangos de datos como entrada: =Correl(range1,range2)

Range1 y Range2 son tablas de datos de tamaño y estructura similares, que consisten en una columna de fecha y una columna de valor. Cada día se añade un nuevo conjunto de datos (fecha y valor) a cada tabel.

Me las arreglé para encontrar la última entrada y la entrada hace 30/90 días a través de esta función: =INDEX(fCorrelations!K:K,COUNTA(fCorrelations!G:G)) =INDEX(fCorrelations!K:K,COUNTA(fCorrelations!G:G)-Date!H1)

Esas funciones devuelven los valores que estoy buscando, pero para mi propósito necesito conseguir la "dirección" de las células que consisten en los valores.

He intentado implementar las funciones arriba en la función de Correl, pero tengo un error. =CORREL(INDEX(fCorrelations!K:K,COUNTA(fCorrelations!G:G)),INDEX(fCorrelations!K:K,COUNTA(fCorrelations!G:G)-Date!H1))

¿Puede alguien ayudar, cómo conseguir los rangos dinámicamente?

Muchas gracias por adelantado.

Pregunta hecha hace 3 años, 4 meses, 26 días - Por bytebard

2 Respuestas:

  • Para crear un rango dinámico en Excel que puedas utilizar en la función CORREL, puedes emplear la función OFFSET. Aquí te muestro cómo puedes hacerlo:

    • Define dos celdas, una para especificar el número de días (por ejemplo en la celda A1) y otra para la última fila de tus datos (por ejemplo en la celda A2).
    • Utiliza la función OFFSET para crear el rango dinámico para cada conjunto de datos:

    Para el primer rango de datos (range1):

    =OFFSET(fCorrelations!K:K, COUNTA(fCorrelations!G:G) - A2 + 1 - A1, 0, A1, 1)

    Para el segundo rango de datos (range2):

    =OFFSET(fCorrelations!K:K, COUNTA(fCorrelations!G:G) - A2 + 1 - A1 - A1, 0, A1, 1)

    • Ahora puedes utilizar estos rangos dinámicos en la función CORREL de la siguiente manera:

    =CORREL(INDIRECT(ADDRESS(ROW(A2) - A1 + 1, COLUMN(fCorrelations!K:K)) & ":" & ADDRESS(ROW(A2), COLUMN(fCorrelations!K:K))), INDIRECT(ADDRESS(ROW(A2) - A1 - A1 + 1, COLUMN(fCorrelations!K:K)) & ":" & ADDRESS(ROW(A2) - A1, COLUMN(fCorrelations!K:K))))

    Recuerda ajustar las referencias de las celdas A1 y A2 en las fórmulas según la ubicación real de tus datos. Espero que esta solución te sea de ayuda. ¡Buena suerte con tu análisis de correlación!

    Respondida el Dec 22, 2020 a las 08:33 - por Gpt

    Votos positivos: 0 | Votos negativos: 0

  • Paso 1: Crear nombres de rango dinámicos

    Ve a la pestaña "Fórmulas". Haz clic en "Administrador de nombres". En el cuadro de diálogo "Nuevo nombre", crea dos nuevos nombres: UltimaFecha30Dias: =OFFSET(INDICE(fCorrelaciones!K:K;CONTARA(fCorrelaciones!G:G));-30;0) UltimaFecha90Dias: =OFFSET(INDICE(fCorrelaciones!K:K;CONTARA(fCorrelaciones!G:G));-90;0)

    Paso 2: Usar los nombres de rango como entrada para la función CORREL

    En una celda en blanco, ingresa la siguiente función:

    
=CORREL(UltimaFecha30Dias;UltimaFecha90Dias)

    Explicación:

    Los nombres de rango UltimaFecha30Dias y UltimaFecha90Dias ahora hacen referencia a las filas que contienen los valores correspondientes a las fechas de los últimos 30 y 90 días. La función CORREL utiliza estos rangos como entrada y devuelve el coeficiente de correlación entre ellos.

    Nota:

    Asegurarse de que los rangos de datos en fCorrelaciones!K:K sean del mismo tamaño y estructura. Los nombres de rango deben actualizarse cada vez que se agreguen nuevos datos a la hoja de cálculo. Para hacer esto, simplemente vuelve a ejecutar el Asistente de nombres y haz clic en "Editar" para cada nombre.

    Respondida el Dec 22, 2020 a las 08:36 - por Gemini

    Votos positivos: 0 | Votos negativos: 0