Soluções de fórmulas - Dicas do Excel

Índice

Nota

Este é um de uma série de artigos detalhando soluções enviadas para o desafio Podcast 2316.

Embora eu esperasse principalmente soluções de Power Query ou VBA para o problema, havia algumas soluções de fórmula interessantes.

Hussein Korish enviou uma solução com 7 fórmulas exclusivas, incluindo uma fórmula de matriz dinâmica.

7 fórmulas únicas

Fórmulas celulares
Alcance	Fórmula
K13: K36	K13	= INDEX (FILTRO (SE (LEN (TRANSPORAR (FILTRO ($ H $ 3: $ AA $ 3, H3: AA3> LEN (H3: AA3))))> 2, TRANSPOR (FILTRO ($ H $ 3: $ AA $ 3, H3 : AA3> LEN (H3: AA3))), ""), SE (LEN (TRANSPOSE (FILTER ($ H $ 3: $ AA $ 3, H3: AA3> LEN (H3: AA3))))> 2, TRANSPOSE ( FILTRO ($ H $ 3: $ AA $ 3, H3: AA3> LEN (H3: AA3))), "") ""), CORRESPONDÊNCIA (SEQUÊNCIA (CONTAGEM ($ J $ 13: $ J $ 36) ,, 1,1) , SEQUÊNCIA (CONTAGEM ($ J $ 13: $ J $ 36) / CONTAGEM ($ B $ 4: $ B $ 9) ,, 1, CONTAGEM ($ 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) ) + COLUNAS ($ L $ 12: $ P $ 12) -COLUNAS (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) ) + COLUNAS ($ L $ 12: $ P $ 12) -COLUNAS (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) ) + COLUNAS ($ L $ 12: $ P $ 12) -COLUNAS (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) ) + COLUNAS ($ L $ 12: $ P $ 12) -COLUNAS (O $ 12: $ P $ 12))
P13: P36	P13	= SOMA (L13: O13)
J13: J36	J13	= ÍNDICE ($ B $ 4: $ B $ 9, CORRESPONDÊNCIA (MOD (CONTAGEM ($ J $ 12: J12) -1, CONTAGEM ($ B $ 4: $ B $ 9)) + 1, SEQUÊNCIA (CONTAGEM ($ B $ 4: $ B $ 9), 1,1), 0))
Fórmulas de matriz dinâmica.

Prashanth Sambaraju enviou outra solução de fórmula que usa cinco fórmulas.

Solução de 5 fórmulas

As fórmulas usadas acima:

Fórmulas celulares
Alcance	Fórmula
J15: J38	J15	= IF (MOD (ROWS ($ J $ 15: J15), 6) = 0,6, MOD (ROWS ($ J $ 15: J15), 6))
K15: K38	K15	= OFFSET ($ A $ 3, J15, J $ 15,1,1)
L15: L38	L15	= CONCATENATE ("Funcionário", "", ROUNDUP (LINHAS ($ J $ 15: J15) / 6,0))
M15: P38	M15	= OFFSET ($ A $ 3, $ J15, CORRESPONDÊNCIA ($ L15, $ B $ 3: $ AA $ 3,0) + MOD (COLUNAS ($ A: A), 5))
Q15: Q38	Q15	= SOMA (M15: P15)

René Martin enviou nesta fórmula solução com três fórmulas exclusivas:

Solução de 3 fórmulas

As fórmulas usadas acima:

Fórmulas celulares
Alcance	Fórmula
I12: N12	I12	= A3
I13: O13, O14: O36	I13	= IF (COLUNA () = 9, OFFSET ($ A $ 2, MOD (ROW (A1), 6) +1,0), IF (COLUMN () = 10, "Funcionário" & ROUNDUP (ROW (A1) / 6, 0), SE (COLUNA () = 15, SOMA (E13: H13), DESLOCAMENTO ($ G $ 3, MOD (LINHA (A6), 6) + 1, ROUNDUP (LINHA (A1) / 6,0) * 5- 7 + COLUNA (A1)))))
I14: N36	I14	= IF (COLUNA () = 9, OFFSET ($ A $ 2, MOD (ROW (A2), 6) +1,0), IF (COLUMN () = 10, "Funcionário" & ROUNDUP (ROW (A2) / 6, 0), OFFSET ($ G $ 3, MOD (ROW (A7), 6) + 1, ROUNDUP (ROW (A2) / 6,0) * 5-7 + COLUNA (A2))))

Uma solução alternativa de René Martin:

Fórmulas celulares
Alcance	Fórmula
I12: N12	I12	= A3
I13: O13, O14: O36	I13	= IF (COLUNA () = 9, OFFSET ($ A $ 2, MOD (ROW (A1), 6) +1,0), IF (COLUMN () = 10, "Funcionário" & ROUNDUP (ROW (A1) / 6, 0), SE (COLUNA () = 15, SOMA (E13: H13), DESLOCAMENTO ($ G $ 3, MOD (LINHA (A6), 6) + 1, ROUNDUP (LINHA (A1) / 6,0) * 5- 7 + COLUNA (A1)))))
I14: N36	I14	= IF (COLUNA () = 9, OFFSET ($ A $ 2, MOD (ROW (A2), 6) +1,0), IF (COLUMN () = 10, "Funcionário" & ROUNDUP (ROW (A2) / 6, 0), OFFSET ($ G $ 3, MOD (ROW (A7), 6) + 1, ROUNDUP (ROW (A2) / 6,0) * 5-7 + COLUNA (A2))))

O MVP Roger Govier do Excel enviou uma fórmula de solução. Primeiramente, Roger excluiu as colunas desnecessárias dos dados originais. Roger aponta que você pode deixá-los lá, mas então você tem que ajustar os números do índice da coluna apropriadamente.

Roger usou três intervalos nomeados. Esta figura mostra _rows selecionados.