Tìm công thức mảng tính giá trị lớn nhất theo từng hàng

[LuuGiaPhúc]

Chào các anh chị,
Em nhờ các anh chị hướng dẫn em tìm 1 công thức mảng để tìm ra ngày học gần nhất theo từng hàng trong vột G đến L
Như hình ví dụ, ở ô A3 , em dùng công thức =OFFSET($D$3,0,0,MATCH(2,1/(D : D<>"")),1)&"_"&OFFSET($F$3,0,0,MATCH(2,1/(D : D<>"")),1) thì e chỉ cần nhập công thức này ở duy nhất ô A3, nó sẽ tự động fill hết toàn bộ vùng data của em đến dòng cuối cùng có dữ liệu.

Nhưng ở Ô B3 , em muốn tìm ngày học gần nhất theo từng hàng ngang ở vùng G:L theo từng học viên (theo mã số đại lý), em dùng hàm C3=MAX(G3:L3) hoặc =Large(G3:L3,1) thì nó chỉ tính được cho 1 ô, em phải fill down xuống đến hàng cuối cùng (khoảng hơn 300.000 hàng) , vậy có hàm nào khác kiểu công thức mảng để mình chỉ nhập 1 ô công thức đầu tiên, máy sẽ tự tính hết toàn bộ các hàng cho mình hay không ạ. Nếu em dùng offset để kéo xuống thì nó lại tìm ra ngày lớn nhất của cả 1 vùng , không đúng ý em , em muốn tìm ngày lớn nhất theo từng học viên ở từng hàng ngang thôi

 

[huuthang_bd]

Bạn thử công thức này:

=MAXIFS(OFFSET(G3:L3,SEQUENCE(MATCH(2,1/(D:D<>""))-ROW()+1,1,0),),G3:L3,"<>'")

 

[LuuGiaPhúc]

Bạn thử công thức này:

=MAXIFS(OFFSET(G3:L3,SEQUENCE(MATCH(2,1/(D:D<>""))-ROW()+1,1,0),),G3:L3,"<>'")

Tuyệt vời, cảm ơn bạn rất nhiều.
À, cho mình hỏi thêm 1 câu là, do lượng data rất lớn , thường khoảng hơn 300.000 dòng , mà vùng từ D:L là kết quả của pivot table lấy từ các file report. Nên những hàm kiểu Sumifs, countifs , maxifs ... nếu tính toán sẽ chạy cực kỳ lâu, đôi lúc treo máy đến vài phút mới xong.
Vậy trong trường hợp cụ thể của bài này, ngoài hàm maxifs như vậy (kết quả vẫn đúng, mình có thử áp dụng vào file thực tế của hôm nay có 374.000 hàng thì nó quay vòng vòng phải mất gần 3 phút mới xong) thì có thêm hàm nào khác nhẹ hơn hay không ạ.

Search trên google thì thấy có người đề xuất dùng hàm large sẽ nhẹ hơn , hàm large thì không cho mình chọn lựa nhiều điều kiện phụ như maxifs , nhưng nó cũng khá phù hợp trong trường hợp đề bài này của mình là chỉ cần lấy ngày học gần nhất, nên mình có để thêm cột B để test thử nhưng không biết cách sửa hàm large cho nó thành công thức mảng như maxifs mà bạn đã làm ,

 

[LmoovoenX]

Tuyệt vời, cảm ơn bạn rất nhiều.
À, cho mình hỏi thêm 1 câu là, do lượng data rất lớn , thường khoảng hơn 300.000 dòng , mà vùng từ D:L là kết quả của pivot table lấy từ các file report. Nên những hàm kiểu Sumifs, countifs , maxifs ... nếu tính toán sẽ chạy cực kỳ lâu, đôi lúc treo máy đến vài phút mới xong.
Vậy trong trường hợp cụ thể của bài này, ngoài hàm maxifs như vậy (kết quả vẫn đúng, mình có thử áp dụng vào file thực tế của hôm nay có 374.000 hàng thì nó quay vòng vòng phải mất gần 3 phút mới xong) thì có thêm hàm nào khác nhẹ hơn hay không ạ.

Search trên google thì thấy có người đề xuất dùng hàm large sẽ nhẹ hơn , hàm large thì không cho mình chọn lựa nhiều điều kiện phụ như maxifs , nhưng nó cũng khá phù hợp trong trường hợp đề bài này của mình là chỉ cần lấy ngày học gần nhất, nên mình có để thêm cột B để test thử nhưng không biết cách sửa hàm large cho nó thành công thức mảng như maxifs mà bạn đã làm ,

Bạn thử công thức sau ở ô a3 xem sao:

=LET(a,OFFSET(D3,0,0,COUNTA(D3:D1000000)),b,a&"_"&OFFSET(a,0,2),c,MAP(a,LAMBDA(x,TEXT(MAX(OFFSET(x,0,3,1,6)),"dd/mm/yy"))),HSTACK(b,c))

 

[LuuGiaPhúc]

Bạn thử công thức sau ở ô a3 xem sao:

=LET(a,OFFSET(D3,0,0,COUNTA(D3:D1000000)),b,a&"_"&OFFSET(a,0,2),c,MAP(a,LAMBDA(x,TEXT(MAX(OFFSET(x,0,3,1,6)),"dd/mm/yy"))),HSTACK(b,c))

Woa , hơn cả mong đợi luôn, nó xử lý 1 lúc được 2 cột luôn , nhẹ, nhanh bất ngờ luôn, chỉ có điều công thức này em xài mà không tài nào hiểu được cấu trúc cũng như cách hoạt động của nó vì có nhiều hàm mới của excel 365 quá như LET , Lambda , MAP .... dù sao cũng cảm ơn bạn rất nhiều luôn

 

[LmoovoenX]

Woa , hơn cả mong đợi luôn, nó xử lý 1 lúc được 2 cột luôn , nhẹ, nhanh bất ngờ luôn, chỉ có điều công thức này em xài mà không tài nào hiểu được cấu trúc cũng như cách hoạt động của nó vì có nhiều hàm mới của excel 365 quá như LET , Lambda , MAP .... dù sao cũng cảm ơn bạn rất nhiều luôn

Bạn có thể tìm hiểu thêm ở các bài hướng dẫn về office 365 trong diễn đàn.
Tuy nhiên, nếu bạn gửi file này cho máy ở office cũ, có thể nó sẽ không mở được. Và chắc chắn các hàm ở bản office mới không hoạt động ở office cũ.

 

[DeTong]

Rút bài vì trả lời sai ý thớt...

 

[huuthang_bd]

Excel 365 thì quá đơn giản, công thức chỉ cần vầy:

=BYROW(OFFSET(G3:L3;;;MATCH(2;1/(D:D<>"")));LAMBDA(x;MAX(x)))

Giải pháp cho các bạn chưa có office 365.

Bài #2 không cần 365.

 

[LuuGiaPhúc]

Excel 365 thì quá đơn giản, công thức chỉ cần vầy:

=BYROW(OFFSET(G3:L3;;;MATCH(2;1/(D:D<>"")));LAMBDA(x;MAX(x)))

Bài #2 không cần 365.

Cảm ơn bạn huuthang_bd rất nhiều , công thức này chạy nhanh thật sự , gọn và dễ hiểu , mình áp dụng vào file được rồi.
Cảm ơn các anh chị đã tạo ra diễn đàn GPE này, quá tuyệt vời

 

[VetMini]

À, cho mình hỏi thêm 1 câu là, do lượng data rất lớn , thường khoảng hơn 300.000 dòng , ...

Search trên google thì thấy có người đề xuất dùng hàm large sẽ nhẹ hơn , hàm large thì không cho mình chọn lựa nhiều điều kiện phụ như maxifs , nhưng nó cũng khá phù hợp trong trường hợp đề bài này của mình là chỉ cần lấy ngày học gần nhất, nên mình có để thêm cột B để test thử nhưng không biết cách sửa hàm large cho nó thành công thức mảng như maxifs mà bạn đã làm ,

Search ra không đúng.
Dữ liệu nhiều thì phải chịu khó học sử dụng Data Model và DAX. Không có cách nào khác. ọội cách chỉ là đi vòng. Lười học thì dùng phần mềm khác, đừng dùng Excel nữa.