Remarque
Ceci fait partie d'une série d'articles détaillant les solutions envoyées pour le défi Podcast 2316.
Alors que je m'attendais principalement à des solutions Power Query ou VBA au problème, il y avait des solutions de formule intéressantes.
Hussein Korish a envoyé une solution avec 7 formules uniques, y compris une formule de tableau dynamique.
| Formules cellulaires | ||
|---|---|---|
| Gamme | Formule | |
| K13: K36 | K13 | = INDEX (FILTRE (SI (LEN (TRANSPOSE (FILTRE ($ H $ 3: $ AA $ 3, H3: AA3> LEN (H3: AA3)))))> 2, TRANSPOSE (FILTRE ($ H $ 3: $ AA $ 3, H3 : AA3> LEN (H3: AA3))), ""), IF (LEN (TRANSPOSE (FILTER ($ H $ 3: $ AA $ 3, H3: AA3> LEN (H3: AA3))))> 2, TRANSPOSE ( FILTRE ($ H $ 3: $ AA $ 3, H3: AA3> LEN (H3: AA3))), "") ""), MATCH (SEQUENCE (COUNTA ($ J $ 13: $ J $ 36) ,, 1,1) , SEQUENCE (COUNTA ($ J $ 13: $ J $ 36) / COUNTA ($ B $ 4: $ B $ 9) ,, 1, COUNTA ($ 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) ) + COLONNES ($ L $ 12: $ P $ 12) -COLONNES (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) ) + COLONNES ($ L $ 12: $ P $ 12) - COLONNES (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) ) + COLONNES ($ L $ 12: $ P $ 12) -COLONNES (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) ) + COLONNES ($ L $ 12: $ P $ 12) -COLONNES (O 12 $: $ P $ 12)) |
| P13: P36 | P13 | = SOMME (L13: O13) |
| J13: J36 | J13 | = INDEX ($ B $ 4: $ B $ 9, MATCH (MOD (COUNTA ($ J $ 12: J12) -1, COUNTA ($ B $ 4: $ B $ 9)) + 1, SEQUENCE (COUNTA ($ B $ 4: $ B 9 $), 1,1), 0)) |
| Formules matricielles dynamiques. | ||
Prashanth Sambaraju a envoyé une autre solution de formule qui utilise cinq formules.
Les formules utilisées ci-dessus:
| Formules cellulaires | ||
|---|---|---|
| Gamme | Formule | |
| J15: J38 | J15 | = SI (MOD (RANGÉES ($ J $ 15: J15), 6) = 0,6, MOD (RANGÉES ($ J $ 15: J15), 6)) |
| K15: K38 | K15 | = OFFSET ($ A $ 3, J15, J $ 15,1,1) |
| L15: L38 | L15 | = CONCATENER ("Employé", "", ROUNDUP (ROWS ($ J $ 15: J15) / 6,0)) |
| M15: P38 | M15 | = OFFSET ($ A $ 3, $ J15, MATCH ($ L15, $ B $ 3: $ AA $ 3,0) + MOD (COLONNES ($ A: A), 5)) |
| Q15: Q38 | Q15 | = SOMME (M15: P15) |
René Martin a envoyé cette solution de formule avec trois formules uniques:
Les formules utilisées dans ce qui précède:
| Formules cellulaires | ||
|---|---|---|
| Gamme | Formule | |
| I12: N12 | I12 | = A3 |
| I13: O13, O14: O36 | I13 | = IF (COLUMN () = 9, OFFSET ($ A $ 2, MOD (ROW (A1), 6) +1,0), IF (COLUMN () = 10, "Employee" & ROUNDUP (ROW (A1) / 6, 0), SI (COLONNE () = 15, SOMME (E13: H13), OFFSET ($ G $ 3, MOD (RANG (A6), 6) + 1, ROUNDUP (ROW (A1) / 6,0) * 5- 7 + COLONNE (A1))))) |
| I14: N36 | I14 | = IF (COLUMN () = 9, OFFSET ($ A $ 2, MOD (ROW (A2), 6) +1,0), IF (COLUMN () = 10, "Employee" & ROUNDUP (ROW (A2) / 6, 0), OFFSET ($ G $ 3, MOD (ROW (A7), 6) + 1, ROUNDUP (ROW (A2) / 6,0) * 5-7 + COLUMN (A2)))) |
Une solution alternative de René Martin:
| Formules cellulaires | ||
|---|---|---|
| Gamme | Formule | |
| I12: N12 | I12 | = A3 |
| I13: O13, O14: O36 | I13 | = IF (COLUMN () = 9, OFFSET ($ A $ 2, MOD (ROW (A1), 6) +1,0), IF (COLUMN () = 10, "Employee" & ROUNDUP (ROW (A1) / 6, 0), SI (COLONNE () = 15, SOMME (E13: H13), OFFSET ($ G $ 3, MOD (RANG (A6), 6) + 1, ROUNDUP (ROW (A1) / 6,0) * 5- 7 + COLONNE (A1))))) |
| I14: N36 | I14 | = IF (COLUMN () = 9, OFFSET ($ A $ 2, MOD (ROW (A2), 6) +1,0), IF (COLUMN () = 10, "Employee" & ROUNDUP (ROW (A2) / 6, 0), OFFSET ($ G $ 3, MOD (ROW (A7), 6) + 1, ROUNDUP (ROW (A2) / 6,0) * 5-7 + COLUMN (A2)))) |
Excel MVP Roger Govier a envoyé une solution de formule. Tout d'abord, Roger a supprimé les colonnes inutiles des données d'origine. Roger souligne que vous pouvez les laisser là, mais que vous devez ensuite ajuster les numéros d'index des colonnes de manière appropriée.
Roger a utilisé trois plages nommées. Cette figure montre les _rows sélectionnés.
Il a également ajouté _Cols comme B3: U3. Il a redéfini mon Ugly_Data comme B4: U9.
La solution de Roger consiste en deux formules, copiées vers le bas et une formule copiée vers le bas et à travers.
Revenez à la page principale du défi Podcast 2316.
Pour lire le dernier article et la solution composite de Bill: Solution composite au défi Podcast 2316
