Tengo un archivo en el que los datos se ingresan en hojas de trabajo separadas (TAB) por día. El diseño es idéntico. Estoy buscando una solución para tomar un promedio dinámico de los últimos 1 a 3 meses de esas TAB separadas. Cada día se crea una nueva TAB, hacerlo a mano cada mes lleva mucho tiempo. Alguna sugerencia ?
Respuesta1
Puedes probar con datos > Consolidación o fórmula 3D
Ejemplo: tienes las siguientes hojas Ene Feb Mar Resumen
Suponiendo n cada hoja, tienes algunos números en A1
Ir a la hoja de resumen, ingrese la siguiente fórmula en A1 =average('Jan:Mar'!A1)
Respuesta2
Para hacer eso, completaremos 2 pasos:
1. Obtenga los nombres de la primera y última hoja.
Según tengo entendido, estás agregando una hoja nueva para cada día. Deberá asegurarse de que los nombres de las hojas tengan el mismo formato. Por ejemplo, supongamos que el formato es: DD-MM-AA (por lo que el 1 de agosto de 2020 será 08-01-20).
En este paso queremos generar untexto de rango de hojade los últimos 3 meses (por ejemplo:01-03-20:01-01-20), para ser utilizado por la fórmula INDIRECTA para crear una fórmula 3D (en el segundo paso).
Supongamos quefecha LDsostiene elúltimofecha relevante (por lo que puede ser una referencia a otra celda, o una fórmula como HOY() o HOY()-1, etc.). Ahora, decide cuál es elprimerofecha relevante. Si faltan 3*30 días para la última fecha, utilice:
=LDATE-3*30
O, si deben ser exactamente 3 meses antes de la FECHA, utilice:
=DATE(YEAR(LDATE),MONTH(LDATE)-3,DAY(LDATE))
Llamemos a esta primera cita comofecha de aprobación.
Finalmente, este será nuestro texto de rango de hojas formateado (tenga en cuenta que esto supone que ha formateado los nombres de las hojas como DD-MM-AA, simplemente cámbielos a cualquier otro formato si es necesario):
="'"&TEXT(LDATE,"DD-MM-YY")&":"&TEXT(FDATE,"DD-MM-YY")&"'!"
El valor ahora debería verse así:'14-07-20:14-04-20'!
Llamemos a todo el rango comoGAMA DE HOJAS.
2. INDIRECTO + Fórmula 3d
La función INDIRECTA devuelve una referencia válida de una cadena de texto determinada, y 'Hoja1:Hoja4'!A1:B2 es un rango de todas las celdas A1:B2 en todas las hojas entre la Hoja1 y la Hoja4.Entonces, lo siguiente obtendrá el promedio de su rango dinámico (suponiendo, por ejemplo, que necesita el promedio de todas las celdas A3):
=AVERAGE(INDIRECT(SHEETRANGE&"A3"))
O, si queremos que todo suceda en una celda (excepto LDATE, que usted debe definir):
=AVERAGE(INDIRECT("'"&TEXT(LDATE,"DD-MM-YY")&":"&TEXT(DATE(YEAR(LDATE),MONTH(LDATE)-3,DAY(LDATE)),"DD-MM-YY")&"'!"&"A3"))
¡Buena suerte!