Tạo danh sách liên tục Liệt kê các phần tử thỏa mãn điều kiện (1 người xem)

[Nguyễn Hồng Quang]

Gửi tới các anh, chị em và các bạn GPE lời chào buổi tối
Em có 1 bảng dữ liệu thống kê các Nhân viên tham gia các dự án
Để xử lý bài toán tạo 1 danh sách liên tục liệt kê các nhân viên tham theo 1 dự án.
Em đã nghiên cứu và mày mò sử dụng hàm Offset kết hợp aggerate kết hợp với cả Mmult để tạo danh sách liên tục. Nhưng không hiểu sao kết quả lại ra sai trật lất (giống như trong file ảnh gửi kèm). Có thể tư duy về mảng này của em đang bị lẫn ở chỗ nào đó
Mong mọi người giúp em sửa sai
Em xin cảm ơn

 

[excel_lv1.5]

Gửi tới các anh, chị em và các bạn GPE lời chào buổi tối
Em có 1 bảng dữ liệu thống kê các Nhân viên tham gia các dự án
Để xử lý bài toán tạo 1 danh sách liên tục liệt kê các nhân viên tham theo 1 dự án.
Em đã nghiên cứu và mày mò sử dụng hàm Offset kết hợp aggerate kết hợp với cả Mmult để tạo danh sách liên tục. Nhưng không hiểu sao kết quả lại ra sai trật lất (giống như trong file ảnh gửi kèm). Có thể tư duy về mảng này của em đang bị lẫn ở chỗ nào đó
Mong mọi người giúp em sửa sai
Em xin cảm ơn

Bạn đặt thiếu điều kiện thôi bạn, bạn sửa lại như vầy:
=OFFSET($A$3,AGGREGATE(15,6,ROW($1:$17)/(MMULT(N($AI$3=$B$4:$AE$19),N(ROW($1:$30)>0))>0),ROW($A1)),)

 

[Nguyễn Hồng Quang]

Bạn đặt thiếu điều kiện thôi bạn, bạn sửa lại như vầy:
=OFFSET($A$3,AGGREGATE(15,6,ROW($1:$17)/(MMULT(N($AI$3=$B$4:$AE$19),N(ROW($1:$30)>0))>0),ROW($A1)),)

Mình cảm ơn excel_lv1.5 rất nhiều. Thực sự mình rất mong bạn chỉ bảo giúp mình khi nào cần phải cài thêm điều kiện > 0 ở trong mảng (MMULT(N($AI$3=$B$4:$AE$19),N(ROW($1:$30)>0))>0)
Vì như 1 ví dụ khác khi mình thực hành Mmult mà không thêm >0 ở phía sau Mmul thì hàm vẫn cho ra kết quả đúng bình thường. (Bạn xem file Test Mmul mình gửi kèm nhé)
Ý mình là muốn biết khi nào thì thêm >0 khi nào không cần thêm
Một lần nữa rất cảm ơn bạn

 

[excel_lv1.5]

Mình cảm ơn excel_lv1.5 rất nhiều. Thực sự mình rất mong bạn chỉ bảo giúp mình khi nào cần phải cài thêm điều kiện > 0 ở trong mảng (MMULT(N($AI$3=$B$4:$AE$19),N(ROW($1:$30)>0))>0)
Vì như 1 ví dụ khác khi mình thực hành Mmult mà không thêm >0 ở phía sau Mmul thì hàm vẫn cho ra kết quả đúng bình thường. (Bạn xem file Test Mmul mình gửi kèm nhé)
Ý mình là muốn biết khi nào thì thêm >0 khi nào không cần thêm
Một lần nữa rất cảm ơn bạn

Tôi mới nói làm biếng viết dài,nhưng giải thích bạn chỗ này. Trong file test này của bạn chỗ MMULT(N($A$15:$A$21=$D$14);1) không cần dùng mmult có thể viết thành $A$15:$A$21=$D$14 cũng được vì chỉ có một cột mà, chừng nào từ 2 cột trở lên hãy dùng MMULT. Xem như viết vậy cũng được, giờ phân tích ra từng phần:
MMULT(N($A$15:$A$21=$D$14);1) tạo ra mảng {1;1;0;1;0;0;1}
ROW($1:$7)/MMULT(N($A$15:$A$21=$D$14)={1;2;3;4;5;6;7}/{1;1;0;1;0;0;1} tạo ra mảng {1;2;#DIV/0!;4;#DIV/0!;#DIV/0!;7}
Bạn dùng AGGREGATE(15;6;...;row(a1)) tức là tìm giá trị nhỏ nhất bỏ qua lỗi của mảng {1;2;#DIV/0!;4;#DIV/0!;#DIV/0!;} sẽ kết cho kết quả là 1
IFERROR(OFFSET($B$1,AGGREGATE(15,6,ROW($1:$7)/MMULT(N($A$15:$A$21=$D$14),1),ROW($A1)),),0)=OFFSET(B1,1)
-----------------------------------
Trong file bài 1 thì
MMULT(N($AI$3=$B$4:$AE$19),N(ROW($1:$30)>0)) tạo ra mảng {0;0;0;0;0;0;4;0;0;12;0;0;0;1;6;0;0}
ROW($1:$17)/MMULT(N($AI$3=$B$4:$AE$19),N(ROW($1:$30)>0))={1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17}/{0;0;0;0;0;0;4;0;0;12;0;0;0;1;6;0;0}={#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;1.5;#DIV/0!;#DIV/0!;0.75;#DIV/0!;#DIV/0!;#DIV/0!;13;2.3333;#DIV/0!;#DIV/0!} (*)
Bạn dùng AGGREGATE(15;6;...;row(a1)) tức là tìm giá trị nhỏ nhất bỏ qua lỗi của mảng (*) sẽ kết cho kết quả là 0.75
=OFFSET($A$4,AGGREGATE(15,6,ROW($1:$17)/MMULT(N($AI$3=$B$4:$AE$19),N(ROW($1:$30)>0)),ROW($A1)),)=OFFSET($A$4,0.75)=OFFSET($A$4,0)="Nguyen Van A"
=OFFSET($A$4,AGGREGATE(15,6,ROW($1:$17)/MMULT(N($AI$3=$B$4:$AE$19),N(ROW($1:$30)>0)),ROW($A2)),)=OFFSET($A$4,1.5)=OFFSET($A$4,1)="Nguyen Van B"
Vì vậy nếu không thêm>0 ở MMULT(N($AI$3=$B$4:$AE$19),N(ROW($1:$30)>0)) thì kết quả sẽ bị sai, hãy chú ý ở những chỗ tôi tô màu, hãy phân tích lại với trường hợp ROW($1:$17)/(MMULT(N($AI$3=$B$4:$AE$19),N(ROW($1:$30)>0))>0) bạn sẽ thấy rõ hơn!!!

 

[Nguyễn Hồng Quang]

..........
Vì vậy nếu không thêm>0 ở MMULT(N($AI$3=$B$4:$AE$19),N(ROW($1:$30)>0)) thì kết quả sẽ bị sai, hãy chú ý ở những chỗ tôi tô màu, hãy phân tích lại với trường hợp ROW($1:$17)/(MMULT(N($AI$3=$B$4:$AE$19),N(ROW($1:$30)>0))>0) bạn sẽ thấy rõ hơn!!!

Cảm ơn bạn, mình tư duy mảng chưa quen còn chậm, nếu không có bạn gỉai thích tường tận thế này chắc mình không qua nổi kiến thức này. Đọc đi đọc lại #3 của bạn đến giờ thì mình đã hiểu vì sao phải cài >0 rồi. Mình diễn giải tiếp thế này nếu có chỗ nào không đúng bạn bày tiếp cho mình với nhé. Khi bổ sung > 0, tức là {0;0;0;0;0;0;4;0;0;12;0;0;0;1;6;0} > 0 sẽ biến thành {0;0;0;0;0;0;true;0;0;true;0;0;0;true;true;0} rồi với hàm N(...) Nó chuyển các True trong đoạn này thành số 1. Sau khi đó đoạn Row($1:$17) / Mmult (....). Mới cho ra kết quả là {0;0;0;0;0;0;7;0;0;10;0;0;0;14;15;0}. Và cuối cùng hàm Offset(Agerrage(...)) Sẽ lần lượt cho ra các ô A9;A12;A16;A17. Cảm ơn excel_level1.5 nhiều nhiều

 

[excel_lv1.5]

Cảm ơn bạn, mình tư duy mảng chưa quen còn chậm, nếu không có bạn gỉai thích tường tận thế này chắc mình không qua nổi kiến thức này. Đọc đi đọc lại #3 của bạn đến giờ thì mình đã hiểu vì sao phải cài >0 rồi. Mình diễn giải tiếp thế này nếu có chỗ nào không đúng bạn bày tiếp cho mình với nhé. Khi bổ sung > 0, tức là {0;0;0;0;0;0;4;0;0;12;0;0;0;1;6;0} > 0 sẽ biến thành {0;0;0;0;0;0;true;0;0;true;0;0;0;true;true;0} rồi với hàm N(...) Nó chuyển các True trong đoạn này thành số 1. Sau khi đó đoạn Row($1:$17) / Mmult (....). Mới cho ra kết quả là {0;0;0;0;0;0;7;0;0;10;0;0;0;14;15;0}. Và cuối cùng hàm Offset(Agerrage(...)) Sẽ lần lượt cho ra các ô A9;A12;A16;A17. Cảm ơn excel_level1.5 nhiều nhiều

Chính xác rồi bạn, miễn sao bạn biến cái mảng mmult thành dãy nhị phan 0,1 hay true, false là được.