¿Puede aplicar una función integrada que tome un rango (por ejemplo, PROMEDIO ()) y que se aplique a varios rangos?

Actualmente estoy terminando una unidad de análisis de datos bastante básica con un pequeño segmento de Excel y tuve una tarea anterior que implicaba analizar algunos datos de vivienda.

Una tarea consistió en promediar y redondear los precios de la vivienda residencial de algunas ciudades para cada período de tiempo.

Los datos de destino están en la hoja de trabajo Residential_Houses, formateada de manera que las ciudades son columnas y los períodos de tiempo son filas que abarcan: C7:J30. Con el objetivo de tener los promedios de cada fila en la hoja de trabajo Analysis abarcando C7:C30.

Mi solución simple fue escribir la fórmula:

=ROUND(AVERAGE(Residential_Houses!C7:J7), 2)

En Analysis!C7 y luego usó la función "Autocompletar" de Excel para generar las fórmulas para Analysis!C8:C30. Esto funcionó bien y los resultados fueron correctos, pero perdí marcas por no usar explícitamente la fórmula de matriz de celdas.

En cambio, la respuesta de muestra que me dieron fue esta línea monolítica:

{=ROUND((Residential_Property!C7:C30+Residential_Property!D7:D30+Residential_Property!E7:E30+Residential_Property!F7:F30+Residential_Property!G7:G30+Residential_Property!H7:H30+Residential_Property!I7:I30+Residential_Property!J7:J30)/8,2)}

Parece claro que la intención de este código es tomar el promedio de las columnas C-J para cada fila 7-30, pero evita el uso de la función incorporada AVERAGE (). Esto funciona, pero es engorroso y no se escalaría si hubiera más de unas pocas columnas en estos datos de prueba.

El quid de mi pregunta aquí es cómo se pueden combinar funciones que ya operan en rangos , con fórmula de matriz de celdas para procesar múltiples rangos con un solo ¿fórmula?

Mi intuición me dice que podría ser algo como esto:

{=AVERAGE((C7:J7):(C30:J30))}

O algo que involucre direccionamiento indirecto, pero parece que no puedo encontrar ninguna información detallada sobre el tema. Todos los recursos de fórmulas de matriz que he encontrado hasta ahora se han limitado a aritmética básica y rangos de una sola dimensión. ¿Podría alguien señalarme en la dirección correcta aquí, o esto simplemente no es posible con la fórmula de matriz?

EDITAR : para cualquiera que se encuentre con esto en el futuro. Elegí la solución de Barry porque me ayudó a entender cómo generalizar este concepto (reemplazando el rango con una función que puede dividir un rango). Pero como señala Scott, OFFSET () es volátil; así que INDEX () es una mejor manera de hacer esto si está trabajando con grandes conjuntos de datos.

2
Jesse Wyatt 14 nov. 2017 a las 21:04

2 respuestas

La mejor respuesta

De manera similar a la sugerencia de Scott, SUBTOTAL puede procesar una matriz de rangos creados por una función OFFSET, por lo que con el primer argumento de SUBTOTAL como 1 (para AVERAGE) esta fórmula dar una matriz de promedios redondeados para cada fila

=ROUND(SUBTOTAL(1,OFFSET(C7:J30,ROW(C7:J30)-ROW(C7),0,1)),2)

Ingresado en un rango vertical y confirmado con CTRL + SHIFT + ENTER

O puede usar el mismo OFFSET dentro de una función AVERAGE como esta:

=ROUND(AVERAGE(OFFSET(C7:J30,ROW(C7:J30)-ROW(C7),0,1)),2)

3
barry houdini 14 nov. 2017 a las 19:34

Aquí hay una fórmula que se puede ingresar como una fórmula de matriz y obtener resultados. También se puede ingresar normalmente y copiar.

=ROUND(AVERAGE(INDEX($C$7:$J$30,ROW()-MIN(ROW($C$7:$J$30))+1,0)),2)

enter image description here

3
Scott Craner 14 nov. 2017 a las 18:42