Hàm Maxif _ Tính giá trị lớn nhất với điều kiện cho trước (1 người xem)

  • Thread starter Thread starter lsxinh
  • Ngày gửi Ngày gửi
Liên hệ QC

Người dùng đang xem chủ đề này

lsxinh

Thành viên chính thức
Tham gia
29/11/08
Bài viết
93
Được thích
62
Lập hàm maxif giúp tính giá trị lớn nhất với điều kiện cho trước.
Tôi có một bảng tính có dạng thế này(tôi chưa biết cách chèn hình ảnh hay bảng tính vào trong bài viết).:

A|B|C
TGian | h (Thủy triều)| Dòng chảy
1 g| 01 (m)| 3m/s
2 | 2| 5
3 | 3| 2
4 | 4| 6
5 | 5| 8,2
6 | 4| 13
7 | 3| 6
8 | 2| 4
9 | 1| 8




Tôi muốn lập một hàm gọi tên là Maxif, có chức năng tìm các giá trị lớn nhất với điều kiện nào đó.
Ví du: Điều kiện là Cao độ thủy triều tối thiểu là 2.
Yêu cầu Tìm giá trị vận tốc dòng chảy lớn nhất ứng với trường hợp thủy triều có cao độ lớn hơn 2

Tôi dùng mã như sau mà không được.

PHP:
Public Function TimMaxif(SLcaodo As Variant, _
          SLVantoc As Variant, DieukienCaodo As Variant) As Variant
 TimMaxif = 0
 SLcaodo.Select  ' <=| ?'
 Dim Caodo As Variant,  Vantoc As Variant

 For Each Caodo In SLcaodo
      For Each Vantoc In SLVantoc
           If Caodo > DieukienCaodo And Vantoc > TimMaxif Then
               TimMaxif = Vantoc
           End If
      Next
 'Next thứ 2 bị thiếu rồi!'

End Function

Có ai biết chỉ dùm tôi với!
 
Chỉnh sửa lần cuối bởi điều hành viên:
Hãy đối chiếu với hàm sau

PHP:
Option Explicit
Function MaxIf(Table_ As Range, hMin As Range) As Double
 MaxIf = Table_.Columns.Count
 If MaxIf <> 2 Then
   MaxIf = -1 * MaxIf:                     Exit Function
 Else
   Dim Clls As Range, Rng As Range
   For Each Clls In Table_.Cells(1, 1).Resize(Table_.Rows.Count)
      If Clls.Value > hMin Then
         If Rng Is Nothing Then
            Set Rng = Clls.Offset(, 1)
         Else
            Set Rng = Union(Rng, Clls.Offset(, 1))
         End If
      End If
   Next Clls
   MaxIf = Application.WorksheetFunction.Max(Rng)
 End If
End Function

Cú pháp hàm trên (theo bảng trên) sẽ là

=maxif(B2:C10,B4)
 
Upvote 0
Mình đã gửi bài giờ không thấy đâu.
Nay mình tham gia 1 hàm
(Trong hàm có sử dụng hàm Access nên vào VBA--Tools--Refe..--chọn: Microsoft Access 11.0 Object Library)
Mã:
Public Function TimMaxif(vungmax As Range, vungtc As Range, tc As String)
Dim so As Long
so = 0
If vungmax.Rows.Count <> vungtc.Rows.Count Then Exit Function
For i = 1 To vungtc.Rows.Count
If eval(vungtc.Cells(i) & tc) = -1 Then
If so < vungmax.Cells(i) Then so = vungmax.Cells(i)
End If
Next
TimMaxif = so
End Function
Cú pháp: =TimMaxif(B2:B10,C2:C10,">3")

vungmax: Vùng tìm giá trị Max
vungtc: Vùng đối chiếu TC
tc: chuỗi tiêu chuẩn bạn gõ trong ngoặc kép

(Kích thước vungmax và vungtc phải bằng nhau)
Hàm có thể tùy biến tìm: Giờ cuối cùng có dòng chảy >3, Mức thủy triêud cao nhất khi dòng chảy >3 hoặc dòng chảy cao nhất >3
 

File đính kèm

Lần chỉnh sửa cuối:
Upvote 0
Yêu cầu này có cần phải viết code riêng hay không vậy? (vì hàm Max của excel đã làm được việc này rồi mà). Nếu viết code thi phải tổng quát hơn, trong trường hợp có nhiều điều kiện thì sao? Nghĩa là n điều kiện?
 
Lần chỉnh sửa cuối:
Upvote 0
Cụ thể dùng Max và if trong excel như thế nào để tổng quát được?

Yêu cầu này có cần phải viết code riêng hay không vậy? (vì hàm Max của excel đã làm được việc này rồi mà). Nếu viết code thi phải tổng quát hơn, trong trường hợp có nhiều điều kiện thì sao? Nghĩa là n điều kiện?

Có ví dụ nào không? Viết ra cho tôi tham khảo với!
 
Upvote 0
PHP:
Option Explicit
Function MaxIf(Table_ As Range, hMin As Range) As Double
 MaxIf = Table_.Columns.Count
 If MaxIf <> 2 Then
   MaxIf = -1 * MaxIf:                     Exit Function
 Else
   Dim Clls As Range, Rng As Range
   For Each Clls In Table_.Cells(1, 1).Resize(Table_.Rows.Count)
      If Clls.Value > hMin Then
         If Rng Is Nothing Then
            Set Rng = Clls.Offset(, 1)
         Else
            Set Rng = Union(Rng, Clls.Offset(, 1))
         End If
      End If
   Next Clls
   MaxIf = Application.WorksheetFunction.Max(Rng)
 End If
End Function

Cú pháp hàm trên (theo bảng trên) sẽ là

=maxif(B2:C10,B4)

Cảm ơn HYen17 rất nhiều, tôi dùng hàm này đã chạy được rồi.
Nhưng tôi thấy nên để hMin As Range thay bằng hMin as Variant thì mình có thể Chọn một Ô trực tiếp trong bảng tính hoặc gõ vào một giá trị để làm điều kiện.
Tớ đang nghiên cứu nhưng vân chưa hiểu đoạn code này lắm (mới to te đạng học VBA).
Có cách nào đơn giản hơn không?
 
Lần chỉnh sửa cuối:
Upvote 0
Nhưng tôi thấy nên để hMin As Range thay bằng hMin as Variant thì mình có thể Chọn một Ô trực tiếp trong bảng tính hoặc gõ vào một giá trị để làm điều kiện.
Hoàn toàn tán đồng!

Mình đang nghiên cứu nhưng vân chưa hiểu đoạn code này lắm (mới to te đạng học VBA). Có cách nào đơn giản hơn không?
Tất nhiên có cách, như sau:

Khai 1 biến trung gian;

Trong vòng lặp, sau mỗi thành tố, ta so với biến trung gian; Nếu ô chứa trị lớn hơn biến trung gian, thì biến trung gian nhận giá trị mới này! . . .

Mình cảm thấy như vậy sẽ chậm hơn cách trên của mình hay sao ấy (?!):-=

Nếu muốn hiểu sâu dòng lệnh nào trog hàm trên, sẵn lòng giải thích trong khả năng!
 
Upvote 0
Có ví dụ nào không? Viết ra cho tôi tham khảo với!
Chỉ cần dùng Max, không cần dùng thêm hàm IF. Muốn lấy ví dụ thì lấy ngay ví dụ của bạn đấy thôi.
Tham khảo công thức bên dưới. Gõ công thức xong nhớ ấn tổ hợp phím Ctrl+Shift+Enter
PHP:
{=MAX(($B$2:$B$10>2)*($C$2:$C$10))}
Thêm bao nhiêu điều kiện nữa thì cứ bấy nhiêu dấu "*" nhân lồng trong hàm Max.
 

File đính kèm

Lần chỉnh sửa cuối:
Upvote 0
Cảm ơn SeaLand

Mình đã gửi bài giờ không thấy đâu.
Nay mình tham gia 1 hàm
(Trong hàm có sử dụng hàm Access nên vào VBA--Tools--Refe..--chọn: Microsoft Access 11.0 Object Library)
Mã:
Public Function TimMaxif(vungmax As Range, vungtc As Range, tc As String)
Dim so As Long
so = 0
If vungmax.Rows.Count <> vungtc.Rows.Count Then Exit Function
For i = 1 To vungtc.Rows.Count
If eval(vungtc.Cells(i) & tc) = -1 Then
If so < vungmax.Cells(i) Then so = vungmax.Cells(i)
End If
Next
TimMaxif = so
End Function
Cú pháp: =TimMaxif(B2:B10,C2:C10,">3")

vungmax: Vùng tìm giá trị Max
vungtc: Vùng đối chiếu TC
tc: chuỗi tiêu chuẩn bạn gõ trong ngoặc kép

(Kích thước vungmax và vungtc phải bằng nhau)
Hàm có thể tùy biến tìm: Giờ cuối cùng có dòng chảy >3, Mức thủy triêud cao nhất khi dòng chảy >3 hoặc dòng chảy cao nhất >3
Tôi thấy hàm của SeaLand có vẻ đơn giản mà tổng quát hơn?
Tôi không hiểu chỗ dùng hàm eval để làm gì vậy?
Mà liên quan đến access ở chỗ nào vậy, độc lập excel không làm được sao?
Sealand và HYen17 thêm chú thích vào hộ tôi với nhé!
Cảm ơn nhiều!
 
Upvote 0
Chưa chắc mèo nào cắn miễu nào

Nếu bạn vẫn thích thú với cách viết hàm tự tạo, thì thêm 1 tham khảo cho bạn:

PHP:
Option Explicit
Function Max_If(hMin, Tbl As Range) As Double
1 'Tim Cuc Dai Tai Cot 2:'
 Dim mRng As Range, Clls As Range 'Khai Bao 2 Bien Kieu Range'
 Set mRng = Tbl.Cells(1, 2).Resize(Tbl.Rows.Count)
 Max_If = Application.WorksheetFunction.Max(mRng)
2 'Tim O Chua Dieu Kien'
 Set Clls = mRng.Find(Max_If, , xlFormulas, xlWhole)
3 ' Loai Ket Qua Khi Khong Thoa Dieu Kien'
 If Clls.Offset(, -1) < hMin Then Max_If = -99
End Function

Về tốc độ, hàm này dám thử thách với 2 hàm tự tạo bên trên
 
Upvote 0
Chỉ cần dùng Max, không cần dùng thêm hàm IF. Muốn lấy ví dụ thì lấy ngay ví dụ của bạn đấy thôi.
Tham khảo công thức bên dưới. Gõ công thức xong nhớ ấn tổ hợp phím Ctrl+Shift+Enter
PHP:
{=MAX(($B$2:$B$10>2)*($C$2:$C$10))}
Thêm bao nhiêu điều kiện nữa thì cứ bấy nhiêu dấu "*" nhân lồng trong hàm Max.

Hay quá, đúng là khó ai có thể nói là mình giỏi Excel được,có rất nhiều cách giải quyết, đơn giản đỡ phải viết code phức tạp. Nhưng dù sao dùng code cũng đỡ được khâu tự động hóa, chứ nếu sử dụng nó trong cả một chương trình lớn, mỗi lần lại phải nhấn Crtl + Shift + Enter thì cũng hơi bất tiện.
Tại sao phải nhấn tổ hợp phím Ctrl + Shift + Enter vậy Bác ca_dafi ơi
Cảm ơn các Bác đã nhiệt tình trả lời topic này.
 
Lần chỉnh sửa cuối:
Upvote 0
Nếu bạn vẫn thích thú với cách viết hàm tự tạo, thì thêm 1 tham khảo cho bạn:

PHP:
Option Explicit
Function Max_If(hMin, Tbl As Range) As Double
1 'Tim Cuc Dai Tai Cot 2:'
 Dim mRng As Range, Clls As Range 'Khai Bao 2 Bien Kieu Range'
 Set mRng = Tbl.Cells(1, 2).Resize(Tbl.Rows.Count)
 Max_If = Application.WorksheetFunction.Max(mRng)
2 'Tim O Chua Dieu Kien'
 Set Clls = mRng.Find(Max_If, , xlFormulas, xlWhole)
3 ' Loai Ket Qua Khi Khong Thoa Dieu Kien'
 If Clls.Offset(, -1) < hMin Then Max_If = -99
End Function

Về tốc độ, hàm này dám thử thách với 2 hàm tự tạo bên trên

Xem lại, phần này, e rằng bác có nhầm không nhỉ, như thế này chỉ tìm ra MAX và xác định có thỏa mãn đ/k không nếu không thì =-99 như vậy có trường hợp sai, vì dù sao vẫn có MAX với đ/k dù cho nó nhỏ hơn MAX toàn thể

Vì thế hàm này cho kết quả không chính xác
.
 
Upvote 0
Tôi muốn lập một hàm gọi tên là Maxif, có chức năng tìm các giá trị lớn nhất với điều kiện nào đó.
Ví du: Điều kiện là Cao độ thủy triều tối thiểu là 2.

Có khi nào Hổ thấy thủy triều đạt - 99 mét hay chưa zậy? Cái này chắc sóng thần nhấn chìm cả nước Lào ấy chứ!

Hơn nữa tham số của hàm hMin là do ta cung cấp mà!
Tất nhiên để khoảng hở vậy để TigerTiger chen vô chứ.

Xin cảm ơn nha! :-=
 
Upvote 0
Có khi nào Hổ thấy thủy triều đạt - 99 mét hay chưa zậy? Cái này chắc sóng thần nhấn chìm cả nước Lào ấy chứ!

Hơn nữa tham số của hàm hMin là do ta cung cấp mà!
Tất nhiên để khoảng hở vậy để TigerTiger chen vô chứ.

Xin cảm ơn nha! :-=

Tôi ở Hải phòng, thủy triều cao nhất của nhất là 5m. 99 mét thì :-=:-=:-=
Cảm ơn các bác đã nhiệt tình trả lời topic này!
 
Upvote 0
Hay quá, đúng là khó ai có thể nói là mình giỏi Excel được,có rất nhiều cách giải quyết, đơn giản đỡ phải viết code phức tạp. Nhưng dù sao dùng code cũng đỡ được khâu tự động hóa, chứ nếu sử dụng nó trong cả một chương trình lớn, mỗi lần lại phải nhấn Crtl + Shift + Enter thì cũng hơi bất tiện.
Tại sao phải nhấn tổ hợp phím Ctrl + Shift + Enter vậy Bác ca_dafi ơi
Cảm ơn các Bác đã nhiệt tình trả lời topic này.
Ctrl + Shift + Enter để kết thúc 1 công thức mảnh.
Sử dụng công thức Max bên trên là 1 dạng của công thức mảng.

Riêng đoạn tô đậm màu đỏ, bạn nói mình chưa hiểu!?
 
Upvote 0
Nếu dữ liệu nhiều thì hàm mảng sẽ gây nặng nề & lặc lè như mang thai vậy!
Khà,. . khà, . . .

Chúc tuần làm việc vui vẽ! :-=
Cái ý tô đậm màu đỏ :
sử dụng nó trong cả một chương trình lớn, mỗi lần lại phải nhấn Crtl + Shift + Enter thì cũng hơi bất tiện.
Em không hiểu là tại sao mỗi lần phải nhấn Ctrl+Shift+Enter thì cũng hơi bất tiện ấy ??

Còn riêng trong bài toán này, có ai chứng minh giúp Hàm sẵn có (hàm MAX của excel) trong hàm mảng bên dưới so với hàm tự tạo, cái nào nhanh hơn không vậy!?

Còn một điều nữa, Nâng mức điều kiện lên 5 điều kiện thử xem, hàm tự tạo sẽ chạy như thế nào?
 
Lần chỉnh sửa cuối:
Upvote 0
Ý tôi là mỗi lần chạy lại phải bấm Ctrl+Shift+Enter vậy cũng hơi bất tiện.
Nếu mình lập ra thì mình biết là phải tổ hợp phím thìm mới có kết quả, còn nếu người khác xem nếu không ghi chú, không hiểu lại tưởng mình làm sai.
Thế thôi, cảm ơn ca_dafi vì hàm Max điều kiện rất hay!
 
Upvote 0
Ý tôi là mỗi lần chạy lại phải bấm Ctrl+Shift+Enter vậy cũng hơi bất tiện.
Nếu mình lập ra thì mình biết là phải tổ hợp phím thìm mới có kết quả, còn nếu người khác xem nếu không ghi chú, không hiểu lại tưởng mình làm sai.

Là sao nhỉ! Công thức chỉ gõ một lần. Nếu vùng dữ liệu có thay đổi thì kết quả công thức vẫn thay đổi theo! Đâu cần phài bấm Ctrl+Shift+Enter nữa đâu!?
Tổ hợp phím Ctrl+Shift+Enter là để kết thúc một công thức mảng. gõ xong rồi thì copy công thức qua chỗ khác cũng được, đâu có cần ctrl+shift+enter lại nữa đâu.

Nói chung là mình vẫn chưa hiểu ý bạn chỗ này!
 
Upvote 0
Là sao nhỉ! Công thức chỉ gõ một lần. Nếu vùng dữ liệu có thay đổi thì kết quả công thức vẫn thay đổi theo! Đâu cần phài bấm Ctrl+Shift+Enter nữa đâu!?
Tổ hợp phím Ctrl+Shift+Enter là để kết thúc một công thức mảng. gõ xong rồi thì copy công thức qua chỗ khác cũng được, đâu có cần ctrl+shift+enter lại nữa đâu.

Nghĩa là không phải Ctrl + Shift + Enter thêm nữa khi dùng, tôi lại cứ tưởng là mỗi lần dùng lại phải Ctrl + Shift + Enter, Cảm ơn anh nhiều, tôi đọc bài chữa kỹ, hiểu nhầm!
 
Upvote 0
Web KT

Bài viết mới nhất

Back
Top Bottom