Tìm giá trị lớn nhất, giá trị nhỏ nhất với nhiều điều kiện

[longtay1111]

Nhân dịp năm mới 2022, em kính chúc các bác và gia đình thành viên trong nhóm GIAIPHAPEXCEL một năm mới dồi dào sức khỏe, gia đình An Khang, Hạnh Phúc, Vạn sự như ý ạ.
Em có bài toán tìm giá trị lớn nhất, nhỏ nhất nhiều điều kiện em muốn nhờ các bác hỗ trợ giúp em để viết công thức với ạ.
Em có viết công thức mảng để tìm giá trị lớn nhất nhiều điều kiện như MAX(IF...hay MIN(IF...nhưng kết quả đều ra =0
Theo suy đoán của em thì hàm MAX, MIN chỉ xác định giá trị là Number, còn giá trị TEXT thì sẽ không xác định được( đó là ý kiến riêng của em, em không hiểu sâu Excel nên chỉ phán đoán như vậy, nếu em hiểu sai em mong các bác thông cảm cho em với ạ).

Các bác giúp em viết hộ em công thức để giải bài toán này với ạ, em cảm ơn các bác rất nhiều.

Em có gửi trong file đính kèm, các bác vui lòng tham khảo giúp em.

Em cảm ơn.

Trân trọng

 

[leonguyenz]

Công thức tại G6.

=AGGREGATE(14,6,($B$6:$B$17=F6)*($C$6:$C$17),1)

Tính Min thì dùng đối số 15 (SMALL) thay cho 14 (LARGE).

 

[longtay1111]

Công thức tại G6.

=AGGREGATE(14,6,($B$6:$B$17=F6)*($C$6:$C$17),1)

Tính Min thì dùng đối số 15 (SMALL) thay cho 14 (LARGE).

Dạ thật tuyệt vời ạ, em chân thành cảm ơn bác rất nhiều ạ.
Em đã làm được công thức lấy GTNN theo công thức của bác là =AGGREGATE(15,3,1/($B$6:$B$17=F6)*($C$6:$C$17),1), và không phải dùng CSE mà chỉ Enter cũng được ạ.

Bác ơi bác cho em hỏi tại sao em dùng công thức MAX(IF ...trong trường này lại không được ạ, em thấy nhiều bài toán thì MAX(IF... vẫn dùng và ra kết quả đúng như AGGREGATE, em vẫn chưa giải thích được trường hợp này, bác biết bác hướng dẫn em với ạ.

Em dùng công thức tìm GTNN là {=MAX(IF($B$6:$B$17=F6,$C$6:$C$17))} nhưng kết quả đều = 0 ạ,

Em chân thành cảm ơn bác rất nhiều, chúc bác 1 tuần làm việc mới vui vẻ và nhiều niềm vui ạ

 

[befaint]

Sai bét tè nhè rồi.

Aggregate() là vốn xử lý mảng rồi, không cần gõ CSE nữa.
Cái trọng điểm ở phần xử lý mảng là phải dùng phép chia với các mảng điều kiện. Trường hợp không có đán áp nào thì kết quả đúng trả về #NUM!, nhưng dùng phép nhân trả về 0. Rất nguy hiểm.

1/($B$6:$B$17=F6)*($C$6:$C$17)

mảng chính/ mảng điều kiện 1/ mảng điều kiện 2/ ... / mảng điều kiện n.

($C$6:$C$17)/($B$6:$B$17=F6) ---> Chỉ có 1 phép chia thôi.

 

[longtay1111]

mảng chính/ mảng điều kiện 1/ mảng điều kiện 2/ ... / mảng điều kiện n.

($C$6:$C$17)/($B$6:$B$17=F6) ---> Chỉ có 1 phép chia thôi.

Thật là tuyệt vời lắm ạ, bác hướng dẫn em em đã biết ra được nhiều điều.
Em có đọc nhiều tài liệu trên GG và thấy lúc thì dùng *, lúc thì dùng /. Có trường hợp em thấy dùng * và / đều có kết quả như nhau.
Ví dụ như trong ví dụ của em gửi ở trên thì AGGREGATE(14,6,($B$6:$B$17=F6)*($C$6:$C$17),1)=AGGREGATE(14,6,$C$6:$C$17/($B$6:$B$17=F6),1) đều có kết quả như nhau khi tìm GTLN.

Nhưng khi tìm GTNN thì sẽ thuận tiện( em đang hiểu như vậy nhưng nếu về Excel có thể em chưa hiểu đúng ) thì dùng / sẽ "tiện hơn" thay vì dùng công thức AGGREGATE(15,3,1/($B$6:$B$17=F6)*($C$6:$C$17),1) để tìm GTNN thì dùng luôn công thức tìm GTLN( phép * ) và sửa 14 thành 15 là ra luôn AGGREGATE(15,3,$C$6:$C$17/($B$6:$B$17=F6),1).

Em cảm ơn bác nhiều nhiều ạ, nhờ có bác hướng dẫn em đã vỡ được rất nhiều vấn đề thắc mắc lâu nay.

Em kính chúc bác năm mới sức khỏe, gia đình hạnh phúc và gặp nhiều may mắn trong công việc ạ

Trân trọng

 

[befaint]

Ví dụ như trong ví dụ của em gửi ở trên thì AGGREGATE(14,6,($B$6:$B$17=F6)*($C$6:$C$17),1)=AGGREGATE(14,6,$C$6:$C$17/($B$6:$B$17=F6),1) đều có kết quả như nhau khi tìm GTLN.

Gõ vào ô F6 ký tự "a" xem còn "như nhau" nữa không?

Không có kết quả (#NUM!) KHÁC hoàn toàn với có kết quả bằng 0 nhé.

Bạn --- đọc --- thật --- kỹ --- bài --- phía trên nhé.

 

[viethung78]

Dùng Excel 2019, 2021 hoặc 365 là có hàm này.

 

[VetMini]

...

Nhưng khi tìm GTNN thì sẽ thuận tiện( em đang hiểu như vậy nhưng nếu về Excel có thể em chưa hiểu đúng ) thì dùng / sẽ "tiện hơn" thay vì dùng công thức AGGREGATE(15,3,1/($B$6:$B$17=F6)*($C$6:$C$17),1) để tìm GTNN thì dùng luôn công thức tìm GTLN( phép * ) và sửa 14 thành 15 là ra luôn AGGREGATE(15,3,$C$6:$C$17/($B$6:$B$17=F6),1).
..

Tiện với chả tiện. Hiểu trật lất hết rồi.

Khi tính số LỚN và số NHỎ bắt buộc phải dùng phép chia, không thể dùng phép nhân. Phép nhân ra giống như mong muốn là do may mắn. Mọt ngày đẹp trời nào đấy nó có thể cho kết quả sai.

Phép chia sẽ đưa các phần tử không thoả điều kiện về lỗi #DIV0!. Và hàm Aggregate sẽ bỏ qua các phần tử này. Trị MAX/MIN trả về là trụ MAX/MIN thực sự của mảng sau khi tính các điều kiện.

Phép nhân sẽ đưa các phần tử không thoả điều kiện về trị 0. Hàm Aggregate không bỏ qua các phần tử này. Nếu mảng có toàn số âm thì trị MAX trả về sẽ là 0. Tương tự vậy, nếu mảng có toàn số dương thì trị MIN trả về là 0.

 

[longtay1111]

Tiện với chả tiện. Hiểu trật lất hết rồi.

Khi tính số LỚN và số NHỎ bắt buộc phải dùng phép chia, không thể dùng phép nhân. Phép nhân ra giống như mong muốn là do may mắn. Mọt ngày đẹp trời nào đấy nó có thể cho kết quả sai.

Phép chia sẽ đưa các phần tử không thoả điều kiện về lỗi #DIV0!. Và hàm Aggregate sẽ bỏ qua các phần tử này. Trị MAX/MIN trả về là trụ MAX/MIN thực sự của mảng sau khi tính các điều kiện.

Phép nhân sẽ đưa các phần tử không thoả điều kiện về trị 0. Hàm Aggregate không bỏ qua các phần tử này. Nếu mảng có toàn số âm thì trị MAX trả về sẽ là 0. Tương tự vậy, nếu mảng có toàn số dương thì trị MIN trả về là 0.

Dạ, em đã hiểu bác ạ. Em chưa được học Excel bao giờ nên không hiểu sâu vấn đề được ạ, được các bác hướng dẫn giúp đỡ em đã nắm được nhiều điều mà trước đây vẫn không rõ, bởi vì em thấy nhiều tài liệu hướng dẫn dùng phép * và thực tế em thấy dùng phép nhân thì kết quả vẫn đúng nên em vẫn thắc mắc rõ ràng về mặt toán học thì 2 phép tính * và / là 2 phép tính hoàn toàn trái ngược nhau( chắc chỉ có 1*1=1/1 và 0/1=0*1 ), nhưng khi dùng có trường hợp thì lại có kết quả bằng nhau( em thấy có 1 số trường hợp không phải chỉ có hàm Aggregate mới có hiện tượng như vậy, một số hàm khác em cũng gặp phải trường hợp tương tự). May gặp được các bác tận tình hướng dẫn nên độ ngu của em đã dần giảm thâm niên, em sẽ cố gắng học hỏi được nhiều hơn qua diễn đàn này, chỉ có diễn đàn này mới giải thích rõ ràng để em nói riêng và nhiều khác nói chung hiểu sâu vấn đề. Tài liệu trên mạng thì có nhiều nhưng em nghĩ chưa chuẩn( ví dụ như trường hợp của em nêu trên, em thấy rất nhiều tài liệu dùng phép * ) nhưng trên diễn đàn này em đã hiểu hơn .

Một lần nữa em xin chân thành cảm ơn bác leonguyenz,befaint,VetMini đã giúp đỡ rất nhiệt tình những bài đăng của em trên diễn đàn, chúc các bác và gia đình một năm mới tràn đầy sức khỏe, tài lộc dồi dào và may mắn trong cuộc sống.

Em có điều gì chưa hiểu em rất mong các bác hỗ trợ em và thông cảm giúp em nhé.

Em cảm ơn

Trân trọng