Solusi Formula - Tips Excel

Catatan

Ini adalah salah satu dari rangkaian artikel yang merinci solusi yang dikirim untuk tantangan Podcast 2316.

Meskipun saya mengharapkan sebagian besar solusi Power Query atau VBA untuk masalah tersebut, ada beberapa solusi rumus yang keren.

Hussein Korish mengirimkan solusi dengan 7 formula unik, termasuk formula array dinamis.

7 rumus unik

Rumus Sel
Jarak	Rumus
K13: K36	K13	= INDEKS (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 ( FILTER ($ H $ 3: $ AA $ 3, H3: AA3> LEN (H3: AA3))), "") ""), MATCH (URUTAN (COUNTA ($ J $ 13: $ J $ 36) ,, 1,1) , URUTAN (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) ) + KOLOM ($ L $ 12: $ P $ 12) -KOLOM (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) ) + KOLOM ($ L $ 12: $ P $ 12) -KOLOM (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) ) + KOLOM ($ L $ 12: $ P $ 12) -KOLOM (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) ) + KOLOM ($ L $ 12: $ P $ 12) -KOLOM (O $ 12: $ P $ 12))
P13: P36	P13	= SUM (L13: O13)
J13: J36	J13	= INDEKS ($ B $ 4: $ B $ 9, MATCH (MOD (COUNTA ($ J $ 12: J12) -1, COUNTA ($ B $ 4: $ B $ 9)) + 1, URUTAN (COUNTA ($ B $ 4: $ B $ 9), 1,1), 0))
Rumus array dinamis.

Prashanth Sambaraju mengirimkan solusi formula lain yang menggunakan lima formula.

5 rumus solusi

Rumus yang digunakan di atas:

Rumus Sel
Jarak	Rumus
J15: J38	J15	= JIKA (MOD (BARIS ($ J $ 15: J15), 6) = 0,6, MOD (BARIS ($ J $ 15: J15), 6))
K15: K38	K15	= OFFSET ($ A $ 3, J15, J $ 15,1,1)
L15: L38	L15	= CONCATENATE ("Karyawan", "", ROUNDUP (BARIS ($ J $ 15: J15) / 6,0))
M15: P38	M15	= OFFSET ($ A $ 3, $ J15, MATCH ($ L15, $ B $ 3: $ AA $ 3,0) + MOD (KOLOM ($ A: A), 5))
P15: P38	P15	= SUM (M15: P15)

René Martin mengirimkan solusi formula ini dengan tiga formula unik:

Solusi 3 rumus

Rumus yang digunakan di atas:

Rumus Sel
Jarak	Rumus
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 + KOLOM (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 (BARIS (A7), 6) + 1, ROUNDUP (BARIS (A2) / 6,0) * 5-7 + KOLOM (A2))))

Solusi alternatif dari René Martin:

Rumus Sel
Jarak	Rumus
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 + KOLOM (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 (BARIS (A7), 6) + 1, ROUNDUP (BARIS (A2) / 6,0) * 5-7 + KOLOM (A2))))

MVP Excel Roger Govier dikirim dalam solusi formula. Pertama, Roger menghapus kolom yang tidak perlu dari data asli. Roger menunjukkan bahwa Anda dapat membiarkannya di sana, tetapi kemudian Anda harus menyesuaikan nomor indeks kolom dengan tepat.

Roger menggunakan tiga rentang bernama. Gambar ini menunjukkan baris yang dipilih.