Mi objetivo al respecto es realizar un seguimiento de las horas del equipo y tener un informe diario que refleje el total de horas y el tiempo de funcionamiento.
Quiero crear un archivo con 31 días de hojas de trabajo en el libro para el mes completo para que los archivos puedan organizarse por fecha.
El orden de las hojas de trabajo en el archivo es el siguiente:
La primera hoja de trabajo será un resumen inicial de las horas del mes anterior.
Las siguientes 31 hojas de trabajo son del 1 al 31 para cada día del mes.
La última hoja de trabajo básicamente contendrá la información del final del día para el último día del mes para poder copiar y pegar valores especiales solo para el inicio del resumen de inicio del próximo mes. No se verá afectado incluso si el número de días del mes varía.
Me gustaría que el libro de trabajo esté configurado y listo para usar sin que el usuario manipule las fórmulas involucradas. Todo lo que el usuario tendría que hacer es ingresar las horas de inicio en la primera hoja y luego, cada día, ingresar el total del final del día para los distintos equipos.
Ejemplo de una columna:
Hoja 1# --- Hoja de resumen de fin del mes anterior
Ubicación de la celda:(Hoja 1~~~~Celda A1)
Fórmula de celda: sin fórmula: el usuario ingresó el número para las horas de fin de mes100
Aspecto terminado:
100
Hoja 2#--- Día 1
Ubicación de la celda:(Hoja 2 ~~~~Celda A1)
Fórmula de celda: sin fórmula: el usuario ingresó el número para las horas del final del día
Uso: Total de Horas de hoy -valor ingresado al final del día por el usuario-125
Ubicación de la celda:(Hoja 2 ~~~~Celda A2)
Fórmula de celda: =+'hoja 1'!A1
Uso: Horas desde el final del día anterior - (valor extraído de la Hoja 1 ~~~~Celda A1-100
Ubicación de la celda:(Hoja 2 ~~~~Celda A3)
Fórmula de celda: =A1-A2
Uso: Tiempo de ejecución de hoy: cálculo simple de los valores de la celda Hoja 2 ~A1 restados de la celda Hoja 2~A2 "las celdas anteriores"-24
Aspecto terminado:
124
100
24
La jornada siguiente se organizará de la siguiente manera:
Hoja 3#--- Día 2
Ubicación de la celda:(Hoja 3, Celda A1)
Fórmula de celda: sin fórmula: el usuario ingresó el número para las horas del final del día
Uso: Horas totales de hoy - (valor ingresado al final del día por el usuario-148
Ubicación de la celda:(Hoja 3, Celda A2)
Fórmula de celda: =+'hoja 2'!A1
Uso: Horas desde el final del día anterior (valor extraído de la Hoja 2 - Celda A1 -124
Ubicación de la celda:(Hoja 3 - Celda A3)
Fórmula de celda: =A1-A2
Uso: Tiempo de ejecución de hoy: cálculo simple de los valores de la celda Hoja 3 - A1 menos Hoja 3~A2 "las celdas anteriores en la misma hoja" -24
Aspecto terminado:
148
124
24
Esa fue la parte fácil......
Ahora, para obtener fácilmente 31 días con varias columnas de números.
Hoja 4 #,
Hago clic derecho en la pestaña, selecciono mover o copiar, creo una copia. Cambie el nombre de la nueva pestaña a "hoja 4"
Al realizar el método anterior para crear una nueva hoja de trabajo, las fórmulas resultarán iguales que las de la hoja anterior y extraerán información del mismo lugar (hoja 2).
Celda A1es el número ingresado por el usuario y el número se transfiere como el mismo que el de la hoja copiada.
Celda A2ahora se copia como: =+'hoja 2'!A1
Celda A3se copia como el cálculo normal "=A1-A2" que se calcula a partir de celdas dentro de la misma hoja. No hay problemas.
Me gustaría la fórmula encelda A2para actualizarse automáticamente a:
=+'hoja 3'!A1al hacer la siguiente hoja.
¿Cómo consigo que la nueva hoja extraiga las fórmulas de celda corregidas sin tener que entrar y corregirlas manualmente?
Con cerca de 40+ celdas de información que deben extraerse de una hoja y transferirse al día siguiente, ingresarlas manualmente y cambiarlas más de 1240 veces para configurar un mes sería demasiado.
Respuesta1
Menú Buscar y reemplazar.http://office.microsoft.com/en-us/excel-help/find-or-replace-text-and-numbers-on-a-worksheet-HP001216390.aspx
Básicamente, desea buscar en las fórmulas '=+'hoja 3'!A1' y reemplazarlas por '=+'hoja 4'!A1'. Tuve que hacer muchas hojas de cálculo de Excel como esta antes, 2 durante todo un año y esta fue la mejor solución con la que pude trabajar.
Respuesta2
Aquí tienes un método que hará lo que quieras, asumiendo que los nombres de tus pestañas están controlados y terminan con números ascendentes como lo describe.
Te lo desglosaré:
Necesito saber en qué hoja estoy, para saber "calcular" el nombre de la hoja del día anterior. DigamosXX=
=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,256). En la Hoja 1, esto devuelve la cadena "Hoja 1", en la Hoja 2, devuelve "Hoja 2"Ahora, necesito analizar esta cadena para calcular el número y restarle 1 para obtener el número de ayer, pero solo si no soy el día 1.AA=
=IF(MID(XX,7,2)>1,MID(XX,7,2)-1,1)Por último, y aquí está la magia, use la
INDIRECTfunción para calcular mi "objetivo" sobre la marcha.=INDIRECT("'Sheet "& YY &"'!A1")
Ponlo todo junto, con las partes que tienes que personalizar en negrita/cursiva (no usé marcado de código, ya que el resaltado no parece funcionar dentro de CODE):
=INDIRECTO("'Hoja"& IF(MID(MID(CELL("nombre de archivo",A1),FIND("]",CELL("nombre de archivo",A1))+1,256),7,2)>1,MID(MID(CELL("nombre de archivo",A1),FIND("]",CELL("nombre de archivo",A1))+1,256),7,2)-1,1) &"'!A1")
Qué hay que actualizar:
- 'Hoja': reemplácela con el prefijo de su hoja (la parte antes del número)
- '7': reemplácelo con la longitud del prefijo anterior +1 (o sustitúyalo con una función)
- 'A1': reemplace con la dirección de celda de lo que desea de la hoja anterior
- No cambie el "nombre de archivo", debe permanecer así (nombre reservado especial)
- No es necesario cambiar el A1 dentro de la función CELDA. Es arbitrario y realmente no es necesario para este nombre reservado y no tiene que cambiar.