Tìm giá trị max của một array thõa điều kiện ràng buộc? (1 người xem)

[stratovarious]

Các bác cho e hỏi:
Em có một cột A1 - A10, Cột B1-B10, cột C1-10
Có 2 vấn đề đặt ra:
1. Em muốn tìm giá trị lớn nhất ở cột C nhưng tương ứng giá trị ở cột A phải phải thõa điều kiện cho trước, chẳng hạn: =2
Công thức: {=max(if((A1:A10=2)*(C1:C10),(C1:C10)))}
2. Nhưng bây giờ em muốn tìm giá trị lớn nhất ở cột C tương ứng giá trị ở cột A và B phải thõa điều kiện cho trước, chẳng hạn (A) = 2,(B) = 3 thì không biết Excel có hỗ trợ làm việc này không ạ?
Em thử kết hợp thêm hàm and rồi nhưng k đc ạ. Mong các bác xem qua giúp đỡ.
Cám ơn các bác ạ!

 

[FunnyBoy]

Bạn dùng:
{=MAX((A1:A10=2)*(B1:B10=3)*(C1:C10))}

 

[ngo15]

Các bạn cho hỏi nếu viết bằng code VBA thì sẽ thế nào nhỉ? Xin cám ơn.

 

[cadafi]

Các bạn cho hỏi nếu viết bằng code VBA thì sẽ thế nào nhỉ? Xin cám ơn.

Làm giống y như lập công thức vậy bạn à! Bạn đưa yêu cầu cụ thể xem, ví dụ: viết hàm cho ra kết quả hay thông báo kết quả ra msgbox hay gán vào kết quả vào 1 cell nào đó trên sheet !???

 

[SA_DQ]

Chắc bạn muốn thấy giá trị này

Các bạn cho hỏi nếu viết bằng code VBA thì sẽ thế nào nhỉ? Xin cám ơn.

Option Explicit
Sub MaxFor2Columns()
 Dim lRow As Long, Wz As Long
 Dim MaxValue As Double
 
 For Wz = 2 To [d65432].End(xlUp).Row
   With Cells(Wz, 5)
      If .Offset(, -1) = 2 And .Value = 3 Then
         If .Offset(, 1) > MaxValue Then MaxValue = .Offset(, 1)
      End If
   End With
 Next Wz
 MsgBox MaxValue 
End Sub

 

[hoangdanh282vn]

Một code khác cho bạn tham khảo

Sub timMax()
Dim i As Long
[j1] = 0
For i = 1 To [A65536].End(xlUp).Row
    If Cells(i, 1) = [e2] And Cells(i, 2) = [e3] And Cells(i, 3) >= [j1] Then
    [j1] = Cells(i, 3)
    End If
Next
End Sub

 

[Mr Okebab]

Cho mình mượn File của hoangdanh282vn một xíu nhé :

Cho góp vui 1 UDF : Các điều kiện có thể là chữ, hoặc số hoặc ngày.

Function MaxDK(DK1 As Variant, MangDK1 As Range, DK2 As Variant, MangDK2 As Range, MangGT As Range) As Double
    Application.Volatile (False)
    
    If MangDK1.Columns.Count <> 1 Then Exit Function
    If MangDK2.Columns.Count <> 1 Then Exit Function
    If MangGT.Columns.Count <> 1 Then Exit Function
    If MangDK1.Rows.Count <> MangDK2.Rows.Count Then Exit Function
    If MangDK1.Rows.Count <> MangGT.Rows.Count Then Exit Function
    If MangDK1.Rows.Count = 0 Then Exit Function
    
    
    Dim i As Long
    For i = 1 To MangDK1.Rows.Count
        If MangDK1(i, 1) = DK1 Then
            If MangDK2(i, 1) = DK2 Then
                If MaxDK < MangGT(i, 1) Then MaxDK = MangGT(i, 1)
        End If: End If
    Next
End Function

Thân!

 

[ndu96081631]

Yêu cầu này nếu dùng VBA thì lồng hàm SUMPRODUCT vào là nhanh gọn nhất, nhưng về mặt hiệu quả thì chẳng đáng tí nào vì dù sao độ dài công thức cũng như nhau!

 

[ngo15]

Sau khi áp dụng code các bạn hướng dẫn tôi vẫn không làm được bài của mình. Yêu cầu cũng đơn giản thôi: Dùng VBA để trích lọc danh sách duy nhất từ cột A, sắp xếp theo thứ tự tăng dần sang cột F. Tại cột G tìm giá trị lớn nhất của danh sách vừa lọc theo cột C. Câu 1 trên diễn đàn đã có nhiều và tôi cũng đã làm được. Mong các bạn giúp đỡ câu 2 như file đính kèm. Xin cám ơn

 

[ngo15]

Quên chưa có File đính kèm, Nay xin gửi lại

 

[SA_DQ]

Bạn xem & đối chiếu, nhất là các dòng lệnh > 2

Quên chưa có File đính kèm, Nay xin gửi lại

Option Explicit

Sub DSachMax()
' * * * Macro recorded 7/31/2008 by SA (GPE.COM) * * *'
 Dim lRow As Long, dlRow As Long, Ww As Long:
 Dim jJ As Integer, MuaMax As Integer
1 'C1'
 Sheet1.Select:                                       lRow = [a65432].End(xlUp).Row
 Application.ScreenUpdating = False:                  Range("A1:A" & lRow).Select
 Selection.AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("F4"), Unique:=True
 dlRow = [f65432].End(xlUp).Row:                      Range("F4:F" & dlRow).Select
 Selection.Sort Key1:=Range("F5"), Order1:=xlAscending, Header:=xlGuess, OrderCustom:=1
2 'C2'
 For jJ = 5 To dlRow
   For Ww = 2 To lRow
      If Cells(jJ, 6) = Cells(Ww, 1) Then
         If Cells(Ww, 2) > MuaMax Then MuaMax = Cells(Ww, 2)
      End If
   Next Ww
   Cells(jJ, 7) = MuaMax:                             MuaMax = 0
 Next jJ
End Sub

 

[ndu96081631]

Yêu cầu này có lẽ không cần dùng UDF, bạn xem file này thế nào nhé
Code:

Sub Loc()
    Dim DS As Range, Ma As Range, LocMa As Range
    Dim Temp As Range, Cll1 As Range, Cll2 As Range
    Dim Max As Double
    Application.ScreenUpdating = False
    Set DS = [A1].CurrentRegion
    Set Ma = DS.Resize(DS.Rows.Count, 1)
    Range("F5:G1000").ClearContents
    Ma.AdvancedFilter Action:=2, CopyToRange:=[F4], Unique:=True
    Set Temp = [F4].CurrentRegion
    Set LocMa = Temp.Offset(1, 0).Resize(Temp.Rows.Count - 1, 1)
    LocMa.Sort Key1:=[F5], Order1:=1, Header:=0
    For Each Cll1 In LocMa
      For Each Cll2 In Ma
         If Cll2 = Cll1 And Cll2.Offset(, 1) >= Max Then Max = Cll2.Offset(, 1)
      Next Cll2
      Cll1.Offset(, 1) = Max: Max = 0
    Next
    Application.ScreenUpdating = True
End Sub

 

[mymichau]

Em có 1 ví dụ liên quan đến hàm Max.
Sub CongDong()
'CSDL = C4:C11
Range("CSDL").Select
Selection.Resize(Selection.Rows.Count + 5).Select
MsgBox Selection.Address 'C4:C15
End Sub

Vậy muốn dòng chọn sẽ tăng thêm theo giá trị của Max(B4:B100), nhưng trong VBA không lấy giá trị của Cell “E3” thì viết lệnh như thế nào? Em làm chưa được, xin các anh chỉ giúp

 

[ndu96081631]

Được chứ!

Sub CongDong()
'CSDL = C4:C11
Range("CSDL").Resize(Range("CSDL").Rows.Count + Range("E3")).Select
  MsgBox Selection.Address
End Sub

 

[ndu96081631]

Nhìn bài #11 với bài của mình (bài #12) thấy gần giống nhau đến 9, 10 về giãi thuật!
Xem như không uổng công sức theo bác SA và các sư phụ VBA học đạo!
(các bạn ơi cố lên để vượt... sư phụ Sa.... Mà khó quá... Adi phò... phò...)

 

[ThuNghi]

Nhìn bài #11 với bài của mình (bài #12) thấy gần giống nhau đến 9, 10 về giãi thuật!
Xem như không uổng công sức theo bác SA và các sư phụ VBA học đạo!
(các bạn ơi cố lên để vượt... sư phụ Sa.... Mà khó quá... Adi phò... phò...)

Tôi kết hợp Code của bạn + thêm Sort và Vlookup chạy nhanh hơn. Bạn thử nhân dữ liệu thành 5000 dòng là thấy ngay. Xin góp vui

Sub LocTN()
    Dim DS As Range, Ma As Range
    Dim eRow As Integer, iRows As Integer, i As Integer
    Application.ScreenUpdating = False
    Sheet1.Select
    Range("I1:J1000").ClearContents
    Range("F5:G1000").ClearContents
    Set DS = [A1].CurrentRegion
    eRow = DS.Rows.Count
    Range("I1:J" & eRow).Value = DS.Value
    Set DS = [I1].CurrentRegion
    With DS
        .Sort Key1:=Range("I2"), Order1:=xlAscending, Key2:=Range("J2") _
        , Order2:=xlDescending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
        False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2 _
        :=xlSortNormal
    End With
    Set Ma = DS.Resize(DS.Rows.Count, 1)
    With Ma
        .AdvancedFilter Action:=2, CopyToRange:=[F4], Unique:=True
    End With
    ActiveWorkbook.Names("Extract").Delete
    eRow = [F4].CurrentRegion.Rows.Count
    iRows = eRow - 1
    Range("G5:G" & 5 + iRows - 1).Value = WorksheetFunction.VLookup(Range("F5:F" & 5 + iRows - 1).Value, DS, 2, 0)
    Range("I1:J1000").ClearContents
    Set DS = Nothing
    Set Ma = Nothing
    Application.ScreenUpdating = True
End Sub

 

[mymichau]

To ndu96081631, xin lổi ghi sai "C3", thay vì "E3"
Em không muốn thay bằng giá trị "E3", mà đưa hàm Max vào trong VBA luôn.
VD
Sub CongDong()
'CSDL = C4:C11
Range("CSDL").Select
Selection.Resize(Selection.Rows.Count + "HMax").Select
MsgBox Selection.Address 'C4:C15
End Sub
Câu lệnh báo lổi, nhưng chưa sữa được.

 

[ndu96081631]

To ndu96081631, xin lổi ghi sai "C3", thay vì "E3"
Em không muốn thay bằng giá trị "E3", mà đưa hàm Max vào trong VBA luôn.
VD
Sub CongDong()
'CSDL = C4:C11
Range("CSDL").Select
Selection.Resize(Selection.Rows.Count + "HMax").Select
MsgBox Selection.Address 'C4:C15
End Sub
Câu lệnh báo lổi, nhưng chưa sữa được.

Vậy thì làm theo 1 trong 2 cách sau:
Cách 1:

Sub CongDong()
Max = Application.WorksheetFunction.Max(Range("B:B"))
Range("CSDL").Resize(Range("CSDL").Rows.Count + Max).Select
  MsgBox Selection.Address
End Sub

Cách 2:

Sub CongDong()
Range("CSDL").Resize(Range("CSDL").Rows.Count + Evaluate("HMax")).Select
  MsgBox Selection.Address
End Sub

 

[hoangdanh282vn]

Sau khi áp dụng code các bạn hướng dẫn tôi vẫn không làm được bài của mình. Yêu cầu cũng đơn giản thôi: Dùng VBA để trích lọc danh sách duy nhất từ cột A, sắp xếp theo thứ tự tăng dần sang cột F. Tại cột G tìm giá trị lớn nhất của danh sách vừa lọc theo cột C. Câu 1 trên diễn đàn đã có nhiều và tôi cũng đã làm được. Mong các bạn giúp đỡ câu 2 như file đính kèm. Xin cám ơn

Bạn xem code này thử, mình thì thích ngắn gọn, còn tốc độ thì còn học hỏi nhiều

Sub Locmax()
Dim i, r As Long
    Range("F2:G65536").Clear
    Range("A2:B" & [A65536].End(xlUp).Row).Copy Destination:=[F2]
    r = [F65536].End(xlUp).Row
    Range("F2:G" & r).Sort Key1:=Range("F2"), Order1:=xlAscending, Key2:=Range("G2") _
        , Order2:=xlDescending
    For i = r To 3 Step -1
        If Cells(i, 6) = Cells(i - 1, 6) Then Range("F" & i, "G" & i).Clear
    Next
    Range("F2:G" & r).Sort Key1:=Range("F2"), Order1:=xlAscending
 End Sub

 

[ndu96081631]

Tôi kết hợp Code của bạn + thêm Sort và Vlookup chạy nhanh hơn. Bạn thử nhân dữ liệu thành 5000 dòng là thấy ngay. Xin góp vui

Tôi còn nghĩ ra 2 cách nữa:
-Cách 1: Add Name để có thể dùng thuộc tính FormulaArray
-Cách 2: AutoFilter với cột A = Clls (Clls là những cells dò tìm trong cột F).. Cột B sort giãm dần rồi lấy cell đầu tiên sau khi Filter (chính là MAX)
Nghĩ vậy nhưng chưa làm ra!