Giải pháp Công thức - Mẹo Excel

Ghi chú

Đây là một trong loạt bài viết chi tiết các giải pháp được gửi đến cho thử thách Podcast 2316.

Trong khi tôi mong đợi hầu hết các giải pháp Power Query hoặc VBA cho vấn đề, có một số giải pháp công thức tuyệt vời.

Hussein Korish đã gửi đến một giải pháp với 7 công thức duy nhất, bao gồm một công thức mảng động.

7 công thức độc đáo

Công thức ô
Phạm vi	Công thức
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 ( BỘ LỌC ($ 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, VẬN CHUYỂN (TRẬN ĐẤU (K13, $ H $ 3: $ AA $ 3,0) ) + CỘT ($ 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, VẬN CHUYỂN (TRẬN ĐẤU (K13, $ H $ 3: $ AA $ 3,0) ) + CỘT ($ 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, VẬN CHUYỂN (TRẬN ĐẤU (K13, $ H $ 3: $ AA $ 3,0) ) + CỘT ($ 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, VẬN CHUYỂN (TRẬN ĐẤU (K13, $ H $ 3: $ AA $ 3,0) ) + CỘT ($ L $ 12: $ P $ 12) -COLUMNS (O $ 12: $ P $ 12))
P13: P36	P13	= SUM (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))
Công thức mảng động.

Prashanth Sambaraju đã gửi trong một giải pháp công thức khác sử dụng năm công thức.

5 công thức giải pháp

Các công thức được sử dụng ở trên:

Công thức ô
Phạm vi	Công thức
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 ("Nhân viên", "", ROUNDUP (ROWS ($ J $ 15: J15) / 6,0))
M15: P38	M15	= OFFSET ($ A $ 3, $ J15, TRẬN ĐẤU ($ L15, $ B $ 3: $ AA $ 3,0) + MOD (COLUMNS ($ A: A), 5))
Q15: Q38	Q15	= SUM (M15: P15)

René Martin đã gửi đến giải pháp công thức này với ba công thức độc đáo:

3 công thức giải pháp

Các công thức được sử dụng ở trên:

Công thức ô
Phạm vi	Công thức
I12: N12	I12	= A3
I13: O13, O14: O36	I13	= IF (COLUMN () = 9, OFFSET ($ A $ 2, MOD (ROW (A1), 6) +1,0), IF (COLUMN () = 10, "Nhân viên" & 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 + COLUMN (A1))))
I14: N36	I14	= IF (COLUMN () = 9, OFFSET ($ A $ 2, MOD (ROW (A2), 6) +1,0), IF (COLUMN () = 10, "Nhân viên" & ROUNDUP (ROW (A2) / 6, 0), OFFSET ($ G $ 3, MOD (ROW (A7), 6) + 1, ROUNDUP (ROW (A2) / 6,0) * 5-7 + COLUMN (A2))))

Một giải pháp thay thế từ René Martin:

Công thức ô
Phạm vi	Công thức
I12: N12	I12	= A3
I13: O13, O14: O36	I13	= IF (COLUMN () = 9, OFFSET ($ A $ 2, MOD (ROW (A1), 6) +1,0), IF (COLUMN () = 10, "Nhân viên" & 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 + COLUMN (A1))))
I14: N36	I14	= IF (COLUMN () = 9, OFFSET ($ A $ 2, MOD (ROW (A2), 6) +1,0), IF (COLUMN () = 10, "Nhân viên" & 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 đã gửi một giải pháp công thức. Trước hết, Roger đã xóa các cột không cần thiết khỏi dữ liệu ban đầu. Roger chỉ ra rằng bạn có thể để chúng ở đó, nhưng sau đó bạn phải điều chỉnh số chỉ mục cột một cách thích hợp.

Roger đã sử dụng ba phạm vi được đặt tên. Hình này hiển thị _rows được chọn.