Catatan
Ini adalah salah satu rangkaian artikel yang memperincikan penyelesaian yang dihantar untuk cabaran Podcast 2316.
Walaupun saya menjangkakan kebanyakan penyelesaian Power Query atau VBA untuk masalah ini, terdapat beberapa penyelesaian formula yang menarik.
Hussein Korish mengirimkan penyelesaian dengan 7 formula unik, termasuk formula larik dinamik.
| Formula Sel | ||
|---|---|---|
| Julat | Formula | |
| K13: K36 | K13 | = INDEX (PENAPIS (JIKA (LEN (TRANSPOSE (FILTER ($ H $ 3: $ AA $ 3, H3: AA3> LEN (H3: AA3))))> 2, TRANSPOSE (FILTER ($ H $ 3: $ AA $ 3, H3) : AA3> LEN (H3: AA3))), ""), JIKA (LEN (TRANSPOSE (FILTER ($ H $ 3: $ AA $ 3, H3: AA3> LEN (H3: AA3))))> 2, TRANSPOSE ( FILTER ($ H $ 3: $ AA $ 3, H3: AA3> LEN (H3: AA3))), "") ""), PERTANDINGAN (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 (PERTANDINGAN (K13, $ H $ 3: $ AA $ 3,0) ) + COLUMNS ($ 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, TRANSPOSE (PERTANDINGAN (K13, $ H $ 3: $ AA $ 3,0) ) + COLUMNS ($ 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, TRANSPOSE (PERTANDINGAN (K13, $ H $ 3: $ AA $ 3,0) ) + COLUMNS ($ 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, TRANSPOSE (PERTANDINGAN (K13, $ H $ 3: $ AA $ 3,0) ) + COLUMNS ($ L $ 12: $ P $ 12) -COLUMNS (O $ 12: $ P $ 12)) |
| P13: P36 | P13 | = SUM (L13: O13) |
| J13: J36 | J13 | = INDEX ($ B $ 4: $ B $ 9, PERTANDINGAN (MOD (COUNTA ($ J $ 12: J12) -1, COUNTA ($ B $ 4: $ B $ 9)) + 1, SEQUENCE (COUNTA ($ B $ 4: $ B) $ 9), 1,1), 0)) |
| Rumus array dinamik. | ||
Prashanth Sambaraju menghantar penyelesaian formula lain yang menggunakan lima formula.
Rumus yang digunakan di atas:
| Formula Sel | ||
|---|---|---|
| Julat | Formula | |
| J15: J38 | J15 | = JIKA (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 ("Pekerja", "", 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)) |
| S15: Q38 | S15 | = SUM (M15: P15) |
René Martin menghantar penyelesaian formula ini dengan tiga formula unik:
Rumus yang digunakan di atas:
| Formula Sel | ||
|---|---|---|
| Julat | Formula | |
| I12: N12 | I12 | = A3 |
| I13: O13, O14: O36 | I13 | = JIKA (COLUMN () = 9, OFFSET ($ A $ 2, MOD (ROW (A1), 6) +1,0), IF (COLUMN () = 10, "Employee" & ROUNDUP (ROW (A1) / 6, 0), JIKA (COLUMN () = 15, SUM (E13: H13), OFFSET ($ G $ 3, MOD (ROW (A6), 6) + 1, ROUNDUP (ROW (A1) / 6,0) * 5- 7 + COLUMN (A1))))) |
| I14: N36 | I14 | = JIKA (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)))) |
Penyelesaian alternatif dari René Martin:
| Formula Sel | ||
|---|---|---|
| Julat | Formula | |
| I12: N12 | I12 | = A3 |
| I13: O13, O14: O36 | I13 | = JIKA (COLUMN () = 9, OFFSET ($ A $ 2, MOD (ROW (A1), 6) +1,0), IF (COLUMN () = 10, "Employee" & ROUNDUP (ROW (A1) / 6, 0), JIKA (COLUMN () = 15, SUM (E13: H13), OFFSET ($ G $ 3, MOD (ROW (A6), 6) + 1, ROUNDUP (ROW (A1) / 6,0) * 5- 7 + COLUMN (A1))))) |
| I14: N36 | I14 | = JIKA (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 dihantar dalam penyelesaian formula. Pertama, Roger memadamkan lajur yang tidak diperlukan dari data asal. Roger menunjukkan bahawa anda boleh meninggalkannya di sana, tetapi kemudian anda harus menyesuaikan nombor indeks lajur dengan tepat.
Roger menggunakan tiga julat bernama. Angka ini menunjukkan _rows dipilih.
He also added _Cols as B3:U3. He redefined my Ugly_Data as B4:U9.
Roger’s solution is two formulas, copied down and one formula copied down and across.
Return to the main page for the Podcast 2316 challenge.
To read the last article and Bill’s composite solution: Composite Solution to Podcast 2316 Challenge
