Soluciones de fórmulas: consejos de Excel

Tabla de contenido

Nota

Este es uno de una serie de artículos que detallan las soluciones enviadas para el desafío Podcast 2316.

Si bien esperaba principalmente soluciones de Power Query o VBA para el problema, hubo algunas soluciones de fórmulas interesantes.

Hussein Korish envió una solución con 7 fórmulas únicas, incluida una fórmula de matriz dinámica.

7 fórmulas únicas

Fórmulas celulares
Rango	Fórmula
K13: K36	K13	= INDICE (FILTER (IF (LEN (TRANSPOSE (FILTER ($ H $ 3: $ AA $ 3, H3: AA3> LEN (H3: AA3))))> 2, TRANSPOSE (FILTER ($ H $ 3: $ AA $ 3, H3 : AA3> LEN (H3: AA3))), ""), IF (LEN (TRANSPOSE (FILTER ($ H $ 3: $ AA $ 3, H3: AA3> LEN (H3: AA3))))> 2, TRANSPOSE ( FILTRO ($ H $ 3: $ AA $ 3, H3: AA3> LEN (H3: AA3))), "") ""), COINCIDIR (SECUENCIA (CONTAR ($ J $ 13: $ J $ 36) ,, 1,1) , SECUENCIA (CONTAR ($ J $ 13: $ J $ 36) / CONTAR ($ B $ 4: $ B $ 9) ,, 1, CONTAR ($ B $ 4: $ B $ 9)), 1))
L13: L36	L13	= OFFSET ($ H $ 3, MOD (COUNTA ($ J $ 12: J12) -1, COUNTA ($ B $ 4: $ B $ 9)) + 1, TRANSPOSE (MATCH (K13, $ H $ 3: $ AA $ 3,0) ) + COLUMNAS ($ L $ 12: $ P $ 12) -COLUMNAS (L $ 12: $ P $ 12))
M13: M36	M13	= OFFSET ($ H $ 3, MOD (COUNTA ($ J $ 12: J12) -1, COUNTA ($ B $ 4: $ B $ 9)) + 1, TRANSPOSE (MATCH (K13, $ H $ 3: $ AA $ 3,0) ) + COLUMNAS ($ L $ 12: $ P $ 12) -COLUMNAS (M $ 12: $ P $ 12))
N13: N36	N13	= OFFSET ($ H $ 3, MOD (COUNTA ($ J $ 12: J12) -1, COUNTA ($ B $ 4: $ B $ 9)) + 1, TRANSPOSE (MATCH (K13, $ H $ 3: $ AA $ 3,0) ) + COLUMNAS ($ L $ 12: $ P $ 12) -COLUMNAS (N $ 12: $ P $ 12))
O13: O36	O13	= OFFSET ($ H $ 3, MOD (COUNTA ($ J $ 12: J12) -1, COUNTA ($ B $ 4: $ B $ 9)) + 1, TRANSPOSE (MATCH (K13, $ H $ 3: $ AA $ 3,0) ) + COLUMNAS ($ L $ 12: $ P $ 12) -COLUMNAS (O $ 12: $ P $ 12))
P13: P36	P13	= SUMA (L13: O13)
J13: J36	J13	= ÍNDICE ($ B $ 4: $ B $ 9, MATCH (MOD (COUNTA ($ J $ 12: J12) -1, COUNTA ($ B $ 4: $ B $ 9)) + 1, SECUENCIA (COUNTA ($ B $ 4: $ B $ 9), 1,1), 0))
Fórmulas de matriz dinámica.

Prashanth Sambaraju envió otra solución de fórmula que usa cinco fórmulas.

Solución de 5 fórmulas

Las fórmulas utilizadas anteriormente:

Fórmulas celulares
Rango	Fórmula
J15: J38	J15	= SI (MOD (FILAS ($ J $ 15: J15), 6) = 0,6, MOD (FILAS ($ J $ 15: J15), 6))
K15: K38	K15	= COMPENSACIÓN ($ A $ 3, J15, J $ 15,1,1)
L15: L38	L15	= CONCATENAR ("Empleado", "", REDONDEAR (FILAS ($ J $ 15: J15) / 6,0))
M15: P38	M15	= COMPENSACIÓN ($ A $ 3, $ J15, PARTIDA ($ L15, $ B $ 3: $ AA $ 3,0) + MOD (COLUMNAS ($ A: A), 5))
Q15: Q38	Q15	= SUMA (M15: P15)

René Martin envió esta solución de fórmula con tres fórmulas únicas:

Solución de 3 fórmulas

Las fórmulas utilizadas en lo anterior:

Fórmulas celulares
Rango	Fórmula
I12: N12	I12	= A3
I13: O13, O14: O36	I13	= IF (COLUMN () = 9, OFFSET ($ A $ 2, MOD (ROW (A1), 6) +1,0), IF (COLUMN () = 10, "Empleado" & REDONDEAR (ROW (A1) / 6, 0), SI (COLUMNA () = 15, SUMA (E13: H13), DESPLAZAMIENTO ($ G $ 3, MOD (FILA (A6), 6) + 1, REDONDEO (FILA (A1) / 6,0) * 5- 7 + COLUMNA (A1)))))
I14: N36	I14	= SI (COLUMNA () = 9, DESPLAZAMIENTO ($ A $ 2, MOD (FILA (A2), 6) +1,0), SI (COLUMNA () = 10, "Empleado" & REDONDEAR (FILA (A2) / 6, 0), DESPLAZAMIENTO ($ G $ 3, MOD (FILA (A7), 6) + 1, REDONDEO (FILA (A2) / 6,0) * 5-7 + COLUMNA (A2))))

Una solución alternativa de René Martin:

Fórmulas celulares
Rango	Fórmula
I12: N12	I12	= A3
I13: O13, O14: O36	I13	= IF (COLUMN () = 9, OFFSET ($ A $ 2, MOD (ROW (A1), 6) +1,0), IF (COLUMN () = 10, "Empleado" & REDONDEAR (ROW (A1) / 6, 0), SI (COLUMNA () = 15, SUMA (E13: H13), DESPLAZAMIENTO ($ G $ 3, MOD (FILA (A6), 6) + 1, REDONDEO (FILA (A1) / 6,0) * 5- 7 + COLUMNA (A1)))))
I14: N36	I14	= SI (COLUMNA () = 9, DESPLAZAMIENTO ($ A $ 2, MOD (FILA (A2), 6) +1,0), SI (COLUMNA () = 10, "Empleado" & REDONDEAR (FILA (A2) / 6, 0), DESPLAZAMIENTO ($ G $ 3, MOD (FILA (A7), 6) + 1, REDONDEO (FILA (A2) / 6,0) * 5-7 + COLUMNA (A2))))

El MVP de Excel Roger Govier envió una solución de fórmula. En primer lugar, Roger eliminó las columnas innecesarias de los datos originales. Roger señala que podría dejarlos allí, pero luego debe ajustar los números de índice de la columna de manera apropiada.

Roger usó tres rangos con nombre. Esta figura muestra _fileras seleccionadas.