Nota
Questo è uno di una serie di articoli che descrivono in dettaglio le soluzioni inviate per la sfida Podcast 2316.
Mentre mi aspettavo principalmente soluzioni Power Query o VBA al problema, c'erano alcune fantastiche soluzioni di formula.
Hussein Korish ha inviato una soluzione con 7 formule uniche, inclusa una formula di matrice dinamica.
| Formule cellulari | ||
|---|---|---|
| Gamma | Formula | |
| K13: K36 | K13 | = INDEX (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))), "") ""), 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) ) + COLONNE ($ L $ 12: $ P $ 12) -COLONNE (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) ) + COLONNE ($ L $ 12: $ P $ 12) -COLONNE (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) ) + COLONNE ($ L $ 12: $ P $ 12) -COLONNE (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) ) + COLONNE ($ L $ 12: $ P $ 12) -COLONNE (O $ 12: $ P $ 12)) |
| P13: P36 | P13 | = SOMMA (L13: O13) |
| J13: J36 | J13 | = INDICE ($ B $ 4: $ B $ 9, CORRISPONDENZA (MOD (COUNTA ($ J $ 12: J12) -1, COUNTA ($ B $ 4: $ B $ 9)) + 1, SEQUENCE (COUNTA ($ B $ 4: $ B $ 9), 1,1), 0)) |
| Formule di matrice dinamica. | ||
Prashanth Sambaraju ha inviato un'altra soluzione formula che utilizza cinque formule.
Le formule usate sopra:
| Formule cellulari | ||
|---|---|---|
| Gamma | Formula | |
| 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 ("Employee", "", ROUNDUP (ROWS ($ J $ 15: J15) / 6,0)) |
| M15: P38 | M15 | = OFFSET ($ A $ 3, $ J15, MATCH ($ L15, $ B $ 3: $ AA $ 3,0) + MOD (COLUMNS ($ A: A), 5)) |
| Q15: Q38 | Q15 | = SOMMA (M15: P15) |
René Martin ha inviato questa soluzione formula con tre formule uniche:
Le formule utilizzate in precedenza:
| Formule cellulari | ||
|---|---|---|
| Gamma | Formula | |
| 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), IF (COLUMN () = 15, SUM (E13: H13), OFFSET ($ G $ 3, MOD (ROW (A6), 6) + 1, ROUNDUP (ROW (A1) / 6,0) * 5- 7 + COLONNA (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)))) |
Una soluzione alternativa di René Martin:
| Formule cellulari | ||
|---|---|---|
| Gamma | Formula | |
| 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), IF (COLUMN () = 15, SUM (E13: H13), OFFSET ($ G $ 3, MOD (ROW (A6), 6) + 1, ROUNDUP (ROW (A1) / 6,0) * 5- 7 + COLONNA (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)))) |
L'MVP di Excel Roger Govier ha inviato una formula di soluzione. Prima di tutto, Roger ha cancellato le colonne non necessarie dai dati originali. Roger fa notare che potresti lasciarli lì, ma poi devi regolare i numeri di indice delle colonne in modo appropriato.
Roger ha utilizzato tre intervalli denominati. Questa figura mostra _rows selezionati.
Ha anche aggiunto _Cols come B3: U3. Ha ridefinito i miei Ugly_Data come B4: U9.
La soluzione di Roger sono due formule, copiate in basso e una formula copiata in basso e in verticale.
Torna alla pagina principale per la sfida Podcast 2316.
Per leggere l'ultimo articolo e la soluzione composita di Bill: Composite Solution to Podcast 2316 Challenge
