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.
| 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.
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:
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.
Ele também adicionou _Cols como B3: U3. Ele redefiniu meu Ugly_Data como B4: U9.
A solução de Roger consiste em duas fórmulas, copiadas e uma fórmula copiada para baixo e transversalmente.
Volte para a página principal do desafio Podcast 2316.
Para ler o último artigo e a solução composta de Bill: Solução composta para o desafio Podcast 2316
