Estoy intentando actualizar una tabla de propuestas/precios para el cálculo de costes internos del trabajo. Nuestro proyecto calcula las horas por empleado por mes y luego se aplica una tarifa a esa estimación para obtener un precio total. No todos los trabajos utilizarán a todos los empleados.
| Empleado/Horas | 21/ene | feb/21 | mar/21 |
|---|---|---|---|
| John Smith | 10 | 15 | 20 |
| fulano de tal | 20 | 20 | 10 |
La tabla de tarifas se encuentra en otra pestaña y enumera a todos los empleados en la columna A, y su tarifa por hora por mes en las columnas B en adelante, con los mismos encabezados de fecha que las horas estimadas. La tabla de tarifas también puede incluir meses anteriores o posteriores a los meses de la pestaña de horas.
| Tabla de tarifas | dic/21 | 21/ene | feb/21 | mar/21 |
|---|---|---|---|---|
| John Smith | $12 | $12 | $12 | $13 |
| Jim Doe | $13 | $13 | $13 | $13 |
| fulano de tal | $12 | $12 | $13 | $13 |
En el pasado, tenía la tabla de horas en la parte superior de la pestaña de estimación de horas, y luego otra tabla directamente debajo que usa INDEX MATCH MATCH para encontrar la tarifa adecuada por empleado por mes y luego multiplicarla por las horas de ese empleado. arriba. Funciona, pero significa que tengo que mostrar el costo total de cada empleado, por mes, lo cual realmente no necesito hacer. Me encantaría tener una línea debajo de la columna de cada mes con el costo total de ese mes, evitando escribir la fórmula muchas veces.
=(B2 * INDEXMATCHMATCH)+(B3 * INDEXMATCHMATCH)+(B4 * INDEXMATCHMATCH)
[obviamente esta no es la fórmula real, porque sería muy larga].
¿Hay alguna manera de multiplicar las horas en la columna B por la tarifa asociada para esos empleados para ese mes y sumarlas en una celda?
| Empleado/Horas | 21/ene | feb/21 | mar/21 |
|---|---|---|---|
| John Smith | 10 | 15 | 20 |
| fulano de tal | 20 | 20 | 10 |
| Coste total | $360 | $380 | $390 |
Respuesta1
He aquí una forma que creo que estarás dispuesto a probar:
=SUM(XLOOKUP($G$2:$G3,$A$2:$A$4,INDEX($B$2:$E$4,,MATCH(H$1,$B$1:$E$1,0)))*H$2:H$3)
(Encontré algunas formas, algunas interesantes, pero son más largas y están escritas que esta fórmula y usted parece sentirse cómodo con el INDEX/MATCHenfoque, por lo que probablemente le resultará fácil de usar y mantener).
Entonces, para la fórmula tal como está escrita, copié sus tablas de datos en A1:E4 para la "Tabla de tarifas" y en G1:J3 para la tabla "Empleado/Horas". El direccionamiento en la fórmula funciona para esos rangos. Querrá ajustarlo para que se ajuste a su situación exacta.
Lea hasta el final: No se preocupe porque estoy hablando tontamente de una tabla de salida.(Solo me di cuenta al volver a leer la pregunta y la respuesta que deseaba tener los resultados en las líneas de datos de la tabla "Empleado/Horas"). La fórmula anterior funciona para ese deseo, ya que arreglé las referencias que hice mientras generaba una tabla de resultados formal. , para que puedas pegarlo según sea necesario).
Para crear la tabla de resultados, solo utilicé fórmulas como =H1para completar los encabezados superior e izquierdo de las tablas de resultados (nombres y meses). Obviamente, usted los conocerá y, por lo tanto, será fácil de ajustar según sea necesario para cualquier cálculo que configure. De manera similar, utilicé =SUM(M2#)fórmulas de suma estándar que tienen la ventaja de no necesitar ajuste, ya que se refieren a todas las matrices SPILL que producen las fórmulas de la primera fila.
La verdadera dificultad para todos los enfoques que probé fue que terminaría con una fórmula que teníaDOSDERRAME matrices en su resultado final y Excel NO recompensa tales cosas. Debido a que tenían usos distintos en cada uno, no podía refinarlos en una única matriz SPILL que le permitiría tener una única fórmula en la celda superior izquierda del bloque de celdas que componen la región de costos de la tabla de salida. Después de decidir hacer los arreglos SPILL hacia abajo en lugar de transversalmente, estaba listo para el "meollo" del problema: esos costos mensuales por empleado.
Para la "carne", como se podría decir, el costo en dólares por mes de cada empleado en un trabajo determinado, se utiliza la fórmula.
Primero INDEX/MATCH: (la columna del primer mes por ahora) hace coincidir los meses de la tabla de salida con las columnas de la "Tabla de tasas" y descubre que se necesita la columna 2 de la región del dólar. INDEXAR la región del dólar proporciona un tamaño de matriz de salida de una columna por tres filas que corresponde a la XLOOKUP()matriz de búsqueda de una columna por tres filas del exterior. Si no coincidieran así, obtendrías un error. Entonces XLOOKUP()busca el nombre del empleado, para lo cual usa una matriz ( $L2#en este caso) para obtener resultados para todos los empleados enumerados en la tabla de resultados. Estos se DERRAMAN hacia abajo, según se desee.
A continuación, copie la fórmula en cada columna de la tabla de resultados y complete los costos de todos los empleados cada mes y actualice las sumas.
(Por cierto, la suma de resultados de su ejemplo para "21 de febrero" es incorrecta, por lo que la fórmula genera $440 (15*$12=$180, 20*$13=$260, $180+$260=$440 en lugar de $380).
Entonces, para hacer la corrección, solo necesitaba seleccionar la primera fórmula que tenía en la tabla de salida, presionar F2-Edit, resaltar y copiar la fórmula, luego pasar a la tabla "Empleado/Horas", la primera fila vacía después de los datos de horas, y escriba "=SUMA(", pegue la fórmula que copié, escriba ")" y presione Entrar. Después de ajustar las referencias para que coincidan con los datos de la tabla "Empleado/Horas", la celda tenía una suma de la información calculada para la tabla de salida innecesaria. Copiar la celda y pegarla a la derecha obtuvo el resultado que desea.
Un último pensamiento: eso no ayudó a poder utilizar una matriz SPILL en todas las celdas de costo total. Triste de decir. Aún deberá copiar y pegar en la cantidad de columnas que se estén utilizando.