Bien, para contextualizar, este enlace me ayudó a comenzar, pero ahora estoy atascado al intentar aplicarlo a mi caso de uso: Excel: suma valores basados en una columna que coincide con otra columna en otra tabla.
Tengo un registro por horas donde ingreso mi trabajo, incluida la fecha, el proyecto y la información de la tarea a la que se facturará, y la hora de inicio y finalización de las horas trabajadas, que calcula cuántas horas he trabajado desde ese momento. ingresado (redondeado a los 15 minutos más cercanos).
Así es (aproximadamente) cómo se ve el registro ("timeLog") con datos de ejemplo:
| A | B | C | D | mi | |
|---|---|---|---|---|---|
| 1 | Fecha | Proyecto y tarea | Hora de inicio | Hora de finalización | Horas |
| 2 | 1/1/2023 | Proyecto 1: Tarea 1 | 08 A.M | 10:00 A.M | 2 |
| 3 | 1/1/2023 | Proyecto 2: Tarea 1 | 10:00 A.M | 16:00 | 6 |
| 4 | 6/1/2023 | Horario de vacaciones | 08 A.M | 16:00 | 8 |
| 5 | 7/1/2023 | tiempo de enfermedad | 08 A.M | 12:00 PM | 4 |
| 6 | 6/2/2023 | Proyecto 1: Tarea 1 | 08 A.M | 16:00 | 8 |
[Transcrito deesta imagen.]
En otra hoja, tengo una tabla que enumera todos los proyectos y tareas asociadas (columna "ID de proyecto y tarea"). Cada proyecto tiene un tipo asociado (es decir, horas trabajadas, vacaciones, enfermedad o vacaciones).
Así es como se ve la tabla de información del proyecto ("projectTable"):
| A | B | |
|---|---|---|
| 1 | ID de proyecto y tarea | Escriba identificación |
| 2 | Proyecto 1: Tarea 1 | Trabajó |
| 3 | Proyecto 2: Tarea 1 | Trabajó |
| 4 | Horario de vacaciones | Día festivo |
| 5 | tiempo de enfermedad | Enfermo |
[Transcrito deesta imagen.]
Para calcular la cantidad de enfermedad/vacaciones que acumulo, necesito calcular cuántas horas he trabajado en un período de facturación determinado. Sin embargo, también necesito que el total de horas que envío, incluidas las enfermedades/vacaciones/días festivos, permanezcan en el registro por horas porque las uso para los cálculos generales de FTE.
Esto es lo que estoy usando actualmente para sumar todas las horas en un período de facturación (26/12/22-25/1/23) que están asociadas con un proyecto con el ID de tipo "trabajado":
=SUM(IFERROR(IF(COUNTIFS(projectTable[Project & Task ID],FILTER(timeLog[Project and Task],(timeLog[Date]<=DATE(2023,1,25))*(timeLog[Date]>DATE(2022,12,25)),0),projectTable[Type ID],"Worked")>0,timeLog[Hours],0),0))
Primero, uso FILTER para obtener todas las entradas de la tabla durante el período especificado.
FILTER(timeLog[Project and Task],(timeLog[Date]<=DATE(2023,1,25))*(timeLog[Date]>DATE(2022,12,25)),0)
Luego, CONTAR.SI verifica si algún proyecto es del TypeID "trabajado":
COUNTIFS(projectTable[Project & Task ID],FILTER(timeLog[Project and Task],(timeLog[Date]<=DATE(2023,1,25))*(timeLog[Date]>DATE(2022,12,25)),0),projectTable[Type ID],"Worked")
Luego, IF devuelve el valor de horas para cada una de esas filas, IFERROR 'corrige' un error que seguía recibiendo y SUM calcula el total de horas "trabajadas" para el período.
Sin embargo, esta no parece la mejor manera de hacerlo: es frágil, devuelve errores que no esperaba y, en general, siento que lo estoy haciendo de manera incorrecta. ¿Cuál es una mejor manera de hacer esto?
Respuesta1
No he estudiado su solución, pero, de un vistazo, estoy de acuerdo en que parece innecesariamente complicada. Le sugiero que agregue una sexta columna a su registro horario, que contenga
=VLOOKUP(B2, projectTable!$A$2:$B$5, 2, FALSE)
Esto traducirá el "Proyecto y Tarea" del registro horario al "Tipo ID" de la tabla de información del proyecto, de la siguiente manera:
| A | B | C | D | mi | F | |
|---|---|---|---|---|---|---|
| 1 | Fecha | Proyecto y tarea | Hora de inicio | Hora de finalización | Horas | Escriba identificación |
| 2 | 1/1/2023 | Proyecto 1: Tarea 1 | 08 A.M | 10:00 A.M | 2 | Trabajó |
| 3 | 1/1/2023 | Proyecto 2: Tarea 1 | 10:00 A.M | 16:00 | 6 | Trabajó |
| 4 | 6/1/2023 | Horario de vacaciones | 08 A.M | 16:00 | 8 | Día festivo |
| 5 | 7/1/2023 | tiempo de enfermedad | 08 A.M | 12:00 PM | 4 | Enfermo |
| 6 | 6/2/2023 | Proyecto 1: Tarea 1 | 08 A.M | 16:00 | 8 | Trabajó |
Entonces se convierte en una simple cuestión de usar COUNTIFSy SUMIFS. Por ejemplo, en otro lugar (por ejemplo, A8:B10), ingrese los tipos en A8:A10, ingrese
=SUMIFS($E$2:$E$6, $A$2:$A$6,">"&DATE(2022,12,25), $A$2:$A$6,"<"&DATE(2023,1,23), $F$2:$F$6,A8)
en B8, y arrastre/rellene hacia abajo:
| A | B | ||
|---|---|---|---|
| 8 | Trabajó | 8 | |
| 9 | Día festivo | 8 | |
| 10 | Enfermo | 4 |
Por supuesto, las fechas codificadas se pueden reemplazar por referencias de celda.
Tenga en cuenta que respondí una pregunta vagamente similar, pero más compleja,pregunta hace ocho años.