Opmerking
Dit is een van een reeks artikelen over oplossingen die zijn ingezonden voor de Podcast 2316-uitdaging.
Hoewel ik voornamelijk Power Query- of VBA-oplossingen voor het probleem verwachtte, waren er enkele coole formule-oplossingen.
Hussein Korish stuurde een oplossing met 7 unieke formules, waaronder een dynamische matrixformule.
| Celformules | ||
|---|---|---|
| Bereik | Formule | |
| K13: K36 | K13 | = INDEX (FILTER (ALS (LEN (TRANSPONEREN (FILTER ($ H $ 3: $ AA $ 3, H3: AA3> LEN (H3: AA3))))> 2, TRANSPONEREN (FILTER ($ H $ 3: $ AA $ 3, H3 : AA3> LEN (H3: AA3))), ""), IF (LEN (TRANSPONEREN (FILTER ($ H $ 3: $ AA $ 3, H3: AA3> LEN (H3: AA3)))))> 2, TRANSPONEREN ( FILTER ($ H $ 3: $ AA $ 3, H3: AA3> LEN (H3: AA3))), "") ""), WEDSTRIJD (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, TRANSPONEREN (WEDSTRIJD (K13, $ H $ 3: $ AA $ 3,0) ) + KOLOMMEN ($ L $ 12: $ P $ 12) -COLUMNS (L $ 12: $ P $ 12)) |
| M13: M36 | M13 | = OFFSET ($ H $ 3, MOD (COUNTA ($ J $ 12: J12) -1, COUNTA ($ B $ 4: $ B $ 9)) + 1, TRANSPONEREN (WEDSTRIJD (K13, $ H $ 3: $ AA $ 3,0) ) + KOLOMMEN ($ L $ 12: $ P $ 12) -COLUMNS (M $ 12: $ P $ 12)) |
| N13: N36 | N13 | = OFFSET ($ H $ 3, MOD (COUNTA ($ J $ 12: J12) -1, COUNTA ($ B $ 4: $ B $ 9)) + 1, TRANSPONEREN (WEDSTRIJD (K13, $ H $ 3: $ AA $ 3,0) ) + KOLOMMEN ($ L $ 12: $ P $ 12) -COLUMNS (N $ 12: $ P $ 12)) |
| O13: O36 | O13 | = OFFSET ($ H $ 3, MOD (COUNTA ($ J $ 12: J12) -1, COUNTA ($ B $ 4: $ B $ 9)) + 1, TRANSPONEREN (WEDSTRIJD (K13, $ H $ 3: $ AA $ 3,0) ) + KOLOMMEN ($ L $ 12: $ P $ 12) -COLUMNS (O $ 12: $ P $ 12)) |
| P13: P36 | P13 | = SOM (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)) |
| Dynamische matrixformules. | ||
Prashanth Sambaraju stuurde een andere formule-oplossing die vijf formules gebruikt.
De hierboven gebruikte formules:
| Celformules | ||
|---|---|---|
| Bereik | Formule | |
| J15: J38 | J15 | = ALS (MOD (RIJEN ($ J $ 15: J15), 6) = 0,6, MOD (RIJEN ($ J $ 15: J15), 6)) |
| K15: K38 | K15 | = OFFSET ($ A $ 3, J15, J $ 15,1,1) |
| L15: L38 | L15 | = CONCATENATE ("Medewerker", "", ROUNDUP (RIJEN ($ J $ 15: J15) / 6,0)) |
| M15: P38 | M15 | = OFFSET ($ A $ 3, $ J15, MATCH ($ L15, $ B $ 3: $ AA $ 3,0) + MOD (KOLOMMEN ($ A: A), 5)) |
| V15: V38 | V15 | = SOM (M15: P15) |
René Martin stuurde deze formule-oplossing met drie unieke formules:
De formules die in het bovenstaande worden gebruikt:
| Celformules | ||
|---|---|---|
| Bereik | Formule | |
| I12: N12 | I12 | = A3 |
| I13: O13, O14: O36 | I13 | = ALS (KOLOM () = 9, VERSCHUIVING ($ A $ 2, MOD (RIJ (A1), 6) +1,0), ALS (KOLOM () = 10, "Werknemer" & AFRONDEN (RIJ (A1) / 6, 0), ALS (KOLOM () = 15, SOM (E13: H13), VERSCHUIVING ($ G $ 3, MOD (RIJ (A6), 6) + 1, AFRONDEN (RIJ (A1) / 6,0) * 5- 7 + KOLOM (A1))))) |
| I14: N36 | I14 | = ALS (KOLOM () = 9, VERSCHUIVING ($ A $ 2, MOD (RIJ (A2), 6) +1,0), ALS (KOLOM () = 10, "Werknemer" & AFRONDEN (RIJ (A2) / 6, 0), VERSCHUIVING ($ G $ 3, MOD (RIJ (A7), 6) + 1, AFRONDEN (RIJ (A2) / 6,0) * 5-7 + KOLOM (A2)))) |
Een alternatieve oplossing van René Martin:
| Celformules | ||
|---|---|---|
| Bereik | Formule | |
| I12: N12 | I12 | = A3 |
| I13: O13, O14: O36 | I13 | = ALS (KOLOM () = 9, VERSCHUIVING ($ A $ 2, MOD (RIJ (A1), 6) +1,0), ALS (KOLOM () = 10, "Werknemer" & AFRONDEN (RIJ (A1) / 6, 0), ALS (KOLOM () = 15, SOM (E13: H13), VERSCHUIVING ($ G $ 3, MOD (RIJ (A6), 6) + 1, AFRONDEN (RIJ (A1) / 6,0) * 5- 7 + KOLOM (A1))))) |
| I14: N36 | I14 | = ALS (KOLOM () = 9, VERSCHUIVING ($ A $ 2, MOD (RIJ (A2), 6) +1,0), ALS (KOLOM () = 10, "Werknemer" & AFRONDEN (RIJ (A2) / 6, 0), VERSCHUIVING ($ G $ 3, MOD (RIJ (A7), 6) + 1, AFRONDEN (RIJ (A2) / 6,0) * 5-7 + KOLOM (A2)))) |
Excel MVP Roger Govier stuurde een formule-oplossing. Allereerst heeft Roger de onnodige kolommen uit de originele gegevens verwijderd. Roger wijst erop dat je ze daar kunt laten staan, maar dan moet je de kolomindexcijfers op de juiste manier aanpassen.
Roger gebruikte drie genoemde bereiken. Deze afbeelding toont _rows geselecteerd.
Hij voegde ook _Cols toe als B3: U3. Hij definieerde mijn Ugly_Data opnieuw als B4: U9.
Roger's oplossing is twee formules, gekopieerd naar beneden en één formule gekopieerd naar beneden en dwars.
Keer terug naar de hoofdpagina voor de Podcast 2316-uitdaging.
Om het laatste artikel en de samengestelde oplossing van Bill te lezen: Composite Solution to Podcast 2316 Challenge
