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!

 

[ndu96081631]

Chạy đua tốc độ đây:

Option Explicit
Sub Loc()
    Dim DS As Range, Ma As Range, LocMa As Range
    Dim Temp As Range, Clls As Range
    Dim iR As Integer
    Application.ScreenUpdating = False
    Set DS = [A1].CurrentRegion
    DS.Sort Key1:=[A2], Order1:=1, Key2:=[B2], Order2:=2, Header:=1
    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 Clls In LocMa
      iR = Application.WorksheetFunction.Match(Clls, Ma, 0)
      Clls.Offset(, 1) = [A1].Offset(iR - 1, 1)
    Next Clls
    Application.ScreenUpdating = True
End Sub

Tôi nghĩ code này sẽ rất nhanh! Dử liệu 10.000 dòng, lọc trong nháy mắt

 

[ThuNghi]

Chạy đua tốc độ đây:

Vậy thì không được rồi, làm biến đổi dữ liệu gốc. Nên có phần phụ để làm Loc.
Hình như match và ... khônh nhanh hơn Vlookup.

 

[ndu96081631]

Vậy thì không được rồi, làm biến đổi dữ liệu gốc. Nên có phần phụ để làm Loc.
Hình như match và ... khônh nhanh hơn Vlookup.

Đây chỉ là giãi thuật thôi! Nếu không muốn làm thay đổi dử liệu gốc thì thiếu gì cách: Copy sang 1 vùng tạm chẳng hạn!... Ngoài ra nếu vùng dử liệu có cột STT thì ta có thể lợi dụng cột này để sort dử liệu trả về lại từ đầu
MATCH theo kiểu này thì không biết có nhanh hơn VLOOKUP không nhưng nếu tôi dùng MATCH theo kiểu dò không chính xác thì chắc sẽ nhanh hơn nữa đấy (Vì theo tôi được biết hàm Match dò không chính xác, khi nó tìm được giá trị hợp lý là nó ra kết quả ngay chử không tìm hết cả vùng)

 

[SA_DQ]

NDU à, để khỏi ảnh hưởng đến 2 cột 'A:B' gốc, với file dữ liệu của bạn ta phải chi trả 1 khoảng thời gian là:
0.1097585 - 0.075755 đó; mình làm thử rồi; Về tương đối mà nói, chiếm gần 1/2 thời gian đó !
Chỉ chưa thử với VLOOKUP() thôi; chứ thử theo cách MATCH() & phương thức tìm kiếm FIND() thì chúng tương đương với nhau về thời gian;
Góp ý nhỏ: Bạn nên xài lại các biến đối tượng, chứ không nên khai nhiều quá như vậy(!); Tiết kiệm là quốc sách mà!

      Set DS = Ma.Find(Clls, lookat:=xlValue)
      If Not DS Is Nothing Then Clls.Offset(, 1) = DS.Offset(, 1)
'      iR = Application.WorksheetFunction.Match(Clls, Ma, 0)'
'      Clls.Offset(, 1) = [A1].Offset(iR - 1, 1)'

Nhưng quả thực thấy bạn tiến bộ trong VBA mà mính chóng cả mặt!
Nhất là CurentRegion & Resize! Mình hết vốn để khoe với mọi người rồi!
Khâm phục vô cùng!
Thôi nha; Hãy gọi mình là sư chính đi nha!

 

[ndu96081631]

Cảm ơn sư phụ!
Em vừa nghĩ ra thêm 2 cách nữa, anh nhận xét thế nào nhé
Cách 1: Dùng AutoFilter trên cột A theo điều kiện của từng cell trong cột F... Mổi lần Filter như vậy ta chọn vùng SpecialCells(xlCellTypeVisible) rồi lấy MAX của nó

Option Explicit
Sub Loc()
    Dim DS As Range, Ma As Range, LocMa As Range
    Dim Temp As Range, Clls As Range, VDK As Range
    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 Clls In LocMa
        DS.AutoFilter Field:=1, Criteria1:=Clls.Value
        Set VDK = [A1].SpecialCells(xlCellTypeVisible)
        Clls.Offset(, 1) = Application.WorksheetFunction.Max(VDK)
    Next Clls
    DS.AutoFilter
    Application.ScreenUpdating = True
End Sub

Cách 2: Vẩn dùng các sort dử liệu, nhưng trứoc đó lưu nó vào vùng nhớ tạm, khi xong việc thì trả dử liệu về như củ:

Option Explicit
Sub Loc()
    Dim DS As Range, Ma As Range, LocMa As Range
    Dim Temp As Range, Clls As Range
    Dim Luu As Variant
    Application.ScreenUpdating = False
    Set DS = [A1].CurrentRegion
    Luu = DS.Value
    DS.Sort Key1:=[A2], Order1:=1, Key2:=[B2], Order2:=2, Header:=1
    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 Clls In LocMa
        Clls.Offset(, 1) = Application.WorksheetFunction.VLookup(Clls, DS, 2, 0)
    Next Clls
    DS.Value = Luu
    Application.ScreenUpdating = True
End Sub

Tiến bộ thêm chút nào đều nhờ có các sư phụ trên diển đàn chỉ vẽ!
Mong sư phụ Sa và các sư phụ khác hướng dẩn thêm để tối ưu hóa code
Chân thành cảm ơn!

 

[Mr Okebab]

Cho em mượn File của bác NDU nhé

Thử 1 cái xem sao:

Sub OB()
    Dim iR As Long, HC As Long, i As Long
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    With Sheet1
        HC = .Range("A65000").End(xlUp).Row
        .Range("A1:A" & HC).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("I4"), Unique:=True
        iR = .Range("I65000").End(xlUp).Row
        For i = 5 To iR
            .Range("J" & i).FormulaArray = "=MAX(IF(R2C1:R" & HC & "C1=RC[-1],R2C2:R" & HC & "C2,0))"
        Next
        With .Range("J5:J" & iR)
                .Calculate
                .Value = .Value
        End With
        Call .Range("I4:J" & iR).Sort(.Range("I5"), xlAscending, , , , , , xlYes)
    End With
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
End Sub

Thân!

 

[ngo15]

Trình của các bạn quả là thâm hậu. Tôi mới đang nghiên cứu để hiểu hết các code trước mà đã lại có một loạt code mới.
To SA_DQ: Bạn cho hỏi code để biết thời gian thực hiện một macro?
Xin cám ơn.

 

[ndu96081631]

Lúc đầu tôi cũng định làm theo kiểu dùng thuộc tính FormulaArray nhưng không nghĩ ra được (còn non tay)
File này hay lắm!
(Còn thiếu chổ Clear vùng lọc)

 

[SA_DQ]

Cách của mình theo kiểu "Nhà Nông" í mà!

To SA_DQ: Bạn cho hỏi code để biết thời gian thực hiện một macro?

Ta mượn tạm Code của BAB vậy nha

Sub OB()
    Dim iR As Long, HC As Long, i As Long
    Dim Timer_ As Double         '<<==| '
    
    Timer_ = Timer                '<<==| '
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    With Sheet1
        HC = .Range("A65000").End(xlUp).Row
        .Range("A1:A" & HC).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("I4"), Unique:=True
        iR = .Range("I65000").End(xlUp).Row
        For i = 5 To iR
            .Range("J" & i).FormulaArray = "=MAX(IF(R2C1:R" & HC & "C1=RC[-1],R2C2:R" & HC & "C2,0))"
        Next
        With .Range("J5:J" & iR)
                .Calculate
                .Value = .Value
        End With
        Call .Range("I4:J" & iR).Sort(.Range("I5"), xlAscending, , , , , , xlYes)
    End With
    MsgBox Timer - Timer_      '<<==| '
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
End Sub

 

[hoangdanh282vn]

Chạy đua về thời gian : với 65500 dòng mất 0.48s

Sub Locmax2()
Dim i As Long
Dim tg As Double
    tg = Timer
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
    Range("F2:G65536").Clear
    Range("A2:B" & [A65536].End(xlUp).Row).Copy Destination:=[I1]
    Range("I1:J" & [I65536].End(xlUp).Row).Sort Key1:=[I1], _
        Order1:=xlAscending, Key2:=[J1], Order2:=xlDescending
    Range("A1:A" & [A65536].End(xlUp).Row).AdvancedFilter Action:=xlFilterCopy, _
        CopyToRange:=[F1], Unique:=True
    Range("F2:F" & [F65536].End(xlUp).Row).Sort Key1:=[F2], Order1:=xlAscending
    For i = 2 To [F65536].End(xlUp).Row
        Cells(i, 7).Value = Application.WorksheetFunction.Index(Range("J1:J" & [I65536].End(xlUp).Row), _
        Application.WorksheetFunction.Match(Cells(i, 6), Range("I1:I" & [I65536].End(xlUp).Row), 0), 1)
    Next
    Range("I1:J65536").Clear
    tg = Timer - tg
    MsgBox tg
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub

 

[Mr Okebab]

Chạy đua về thời gian : với 65500 dòng mất 0.48s

Sub Locmax2()
Dim i As Long
Dim tg As Double
    tg = Timer
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
    Range("F2:G65536").Clear
    Range("A2:B" & [A65536].End(xlUp).Row).Copy Destination:=[I1]
    Range("I1:J" & [I65536].End(xlUp).Row).Sort Key1:=[I1], _
        Order1:=xlAscending, Key2:=[J1], Order2:=xlDescending
    Range("A1:A" & [A65536].End(xlUp).Row).AdvancedFilter Action:=xlFilterCopy, _
        CopyToRange:=[F1], Unique:=True
    Range("F2:F" & [F65536].End(xlUp).Row).Sort Key1:=[F2], Order1:=xlAscending
    For i = 2 To [F65536].End(xlUp).Row
        Cells(i, 7).Value = Application.WorksheetFunction.Index(Range("J1:J" & [I65536].End(xlUp).Row), _
        Application.WorksheetFunction.Match(Cells(i, 6), Range("I1:I" & [I65536].End(xlUp).Row), 0), 1)
    Next
    Range("I1:J65536").Clear
    tg = Timer - tg
    MsgBox tg
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub

Rất sáng tạo ở chỗ lợi dụng Sort Desceding ở cột giá trị, sau đó dùng Index + Match để lấy giá trị đầu tiên (lớn nhất).

Thân!

 

[ndu96081631]

Chạy đua về thời gian : với 65500 dòng mất 0.48s

Bài này gần giống cách 2 ở bài #25 của tôi, nhưng:
1> Bạn copy dử liệu sang nơi khác sẽ mất thời gian hơn, và có thể đè lên dử liệu khác (trong trường hợp cột I có dử liệu)... Tôi thì không làm vậy, copy dử liệu vào vùng tạm
2> Dùng INDEX dài dòng hơn là VLOOKUP

 

[Mr Okebab]

Bài này gần giống cách 2 ở bài #25 của tôi, nhưng:
1> Bạn copy dử liệu sang nơi khác sẽ mất thời gian hơn, và có thể đè lên dử liệu khác (trong trường hợp cột I có dử liệu)... Tôi thì không làm vậy, copy dử liệu vào vùng tạm
2> Dùng INDEX dài dòng hơn là VLOOKUP

Đúng là 2 bài này có cách giải thuật giống nhau (sáng tạo)
Tuy nhiên :

- Bác NU dùng cách lưu giá trị cũ vào vùng nhớ tạm, sau khi xử lý xong sẽ cho nhận lại giá trị cũ : Điều này cũng tiện, nhưng để nói là nhanh hơn cách của hoangdanh thì không hẳn, bởi bác phải chiếm 1 vùng bộ nhớ khá lớn cho biến Luu, khi nhận ngược lại giá trị thì Excel cũng phải copy từng giá trị trong biến Luu vào từng Cell trong vùng.
Vì vậy, có nên chăng hoangdanh không copy nữa, mà nhận giá trị trực tiếp luôn :

Range("I1:J" & [A65536].End(xlUp).Row-1).Value = _
       Range("A2:B" & [A65536].End(xlUp).Row).Value

Việc đè mất dữ liệu thì bác NU tối ưu hơn vì không làm mất dữ liệu, vì chỉ là 1 biến Luu thôi (tuy nhiên có thể làm mất công thức (nếu có)), Còn hoangdanh thì chắc chắn phải có 1 giải pháp dự phòng để không bị đè, đó là 1 vùng Temp được quy định sẵn, vì vậy ở mỗi phương pháp nên có 1 giải pháp dung hòa


- Index và Vlookup thì Vlookup sẽ viết gọn hơn, tuy nhiên lại không linh động như Index(match) được. Còn tốc độ thì có lẽ là như nhau.

Cách viết khác (tuy chẳng ngắn hơn)
​

With WorksheetFunction
         Cells(i, 7).Value = .Index(Range("J1:J" & [I65536].End(xlUp).Row), _
            .Match(Cells(i, 6), Range("I1:I" & [I65536].End(xlUp).Row), 0), 1)
 End With

Ngoài ra, HoangDanh dùng rất nhiều [I65536].End(xlUp).Row) , đây là 1 con số đã đươc xác định, vì vậy nên cho nó vào 1 biến, VD như LastRow chẳng hạn, vừa gọn lại vừa nhanh hơn.

​

Thân!

 

[ndu96081631]

Ngoài ra, HoangDanh dùng rất nhiều [I65536].End(xlUp).Row) , đây là 1 con số đã đươc xác định, vì vậy nên cho nó vào 1 biến, VD như LastRow chẳng hạn, vừa gọn lại vừa nhanh hơn.

Cảm ơn sư phụ đã nhận xét, tôi lại học thêm 1 chiêu nữa!
Việc dùng End(xlUp) cũng hay nhưng tôi thấy không tiện bằng Current Region và Resize (chiêu này học của sư phụ Sa)

 

[Mr Okebab]

Cảm ơn sư phụ đã nhận xét, tôi lại học thêm 1 chiêu nữa!
Việc dùng End(xlUp) cũng hay nhưng tôi thấy không tiện bằng Current Region và Resize (chiêu này học của sư phụ Sa)

Vâng, xác định vùng thì Current Region và Resize nhanh hơn và có vẻ tiện hơn, tuy nhiên End(xlUp) lại có thế mạnh riêng là xác định hàng rất tốt, phục vụ cho nhiều kiểu giải thuật.
Mà Resize cũng lại phải có số hàng, cho nên cũng tùy từng điều kiện ạ.
Cái này chắc bác SA và bác quá rành rồi.

Thân!

P/S : Đừng gọi em là sư phụ, giải thuật của em chưa bằng của bác đâu.

 

[SA_DQ]

Cần giải pháp dung hòa?

Việc đè mất dữ liệu thì bác NU tối ưu hơn vì không làm mất dữ liệu, vì chỉ là 1 biến Luu thôi (tuy nhiên có thể làm mất công thức (nếu có)), Còn hoangdanh thì chắc chắn phải có 1 giải pháp dự phòng để không bị đè, đó là 1 vùng Temp được quy định sẵn, vì vậy ở mỗi phương pháp nên có 1 giải pháp dung hòa

Vố đầu chương trình thêm 8 cột mới trước cột 'A'
Nếu nó cự nự thì nghỉ chơi, tìm cách khác!
Nếu được thì cuối chương trình xóa bớt 6 cột, OK(?!)

 

[ndu96081631]

Vố đầu chương trình thêm 8 cột mới trước cột 'A'
Nếu nó cự nự thì nghỉ chơi, tìm cách khác!
Nếu được thì cuối chương trình xóa bớt 6 cột, OK(?!)

Lúc trước em cũng làm 1 code theo kiểu này:
1> Copy 2 cột A và B, paste theo kiểu Insert copy cells vào trước cột A
2> Thêm 1 cột rổng phía sau cột B để có thể dùng Current Region
3> Em sẽ xử lý dử liệu theo 2 cột mới này (hiện giờ nó cũng là A và B)
4> Làm xong thì xóa chúng đi
Làm thì OK nhưng em thấy không hay lắm nên không post lên (vì cãm giác tốc độ chậm hơn)

 

[hoangdanh282vn]

With WorksheetFunction
Cells(i, 7).Value = .Index(Range("J1:J" & [I65536].End(xlUp).Row), _
.Match(Cells(i, 6), Range("I1:I" & [I65536].End(xlUp).Row), 0), 1)
End With

With WorksheetFunction <= Cái này quá hay, em không nghĩ là có thể dùng With được.
Còn :

Ngoài ra, HoangDanh dùng rất nhiều [I65536].End(xlUp).Row) , đây là 1 con số đã đươc xác định, vì vậy nên cho nó vào 1 biến, VD như LastRow chẳng hạn, vừa gọn lại vừa nhanh hơn.

Em cứ nghĩ là đưa nó vào biến thì sẽ chậm hơn là để nó tự nhiên, vì chưa có check lại nên em mới đưa nó ra như thế. Cám ơn anh vì điều này, sẽ còn học hỏi nhiều ở anh và mọi người.

 

[ndu96081631]

Chú ý thêm 1 chuyện nhỏ nữa:
[A65536].End(xlUp).Row sẽ có lúc cho kết quả sai khi dử liệu của bạn vừa vặn đến dòng 65536
Thử xem khi ấy kết quả [A65536].End(xlUp).Row có khi = 1 cũng không chừng

 

[ngo15]

Khi cột A không liên tục (Có dòng rỗng) CurrentRegion có vẻ không lấy tiếp được số liệu xuống dưới nữa.

 

[ndu96081631]

Khi cột A không liên tục (Có dòng rỗng) CurrentRegion có vẻ không lấy tiếp được số liệu xuống dưới nữa.

Hoàn toàn chính xác!
Trong trường hợp này thì buộc phải dùng thuộc tính End

 

[vdcuong]

Xin chào GPE. Em có một bài tập ở file đính kèm, em đã giải quyết được phần lớn nhưng có 2 công thức ở ô J33, J34 em nghĩ mãi chưa ra cách làm. Thực ra em mới chỉ đi nghiên cứu excel mới có 1 tuần nên chưa còn kém lắm. Mong thư trả lời từ các vị tiền bối.

 

[duongdv]

Xin chào GPE. Em có một bài tập ở file đính kèm, em đã giải quyết được phần lớn nhưng có 2 công thức ở ô J33, J34 em nghĩ mãi chưa ra cách làm. Thực ra em mới chỉ đi nghiên cứu excel mới có 1 tuần nên chưa còn kém lắm. Mong thư trả lời từ các vị tiền bối.

Sao không thầy file đính kèm của bạn nhỉ? Nếu không post được bạn gửi vào mail cho tôi, tôi sẽ post lên giúp bạn
duongdv1010@gmail.com

 

[vdcuong]

Rất cám ơn duongdv. mình đã gửi file vào mail của bạn, mong bạn giúp đỡ. Thông cảm vì mình không thể upload được lên diễn đàn.

 

[duongdv]

Rất cám ơn duongdv. mình đã gửi file vào mail của bạn, mong bạn giúp đỡ. Thông cảm vì mình không thể upload được lên diễn đàn.

Mình post bài giúp bạn vdcuong các bạn xem và giúp bạn ấy nhé.
(To vdcuong! lần sau bạn nên chuyển thành dạng *.xls rồi gửi lên diễn đàn vì với định dạng .xlsx không phải máy thành viên nào cũng đọc được do không cài office2007)

 

[chucuoi92]

Mình post bài giúp bạn vdcuong các bạn xem và giúp bạn ấy nhé.
(To vdcuong! lần sau bạn nên chuyển thành dạng *.xls rồi gửi lên diễn đàn vì với định dạng .xlsx không phải máy thành viên nào cũng đọc được do không cài office2007)

Bài này anh duongdv thừa sức làm sao anh không giải luôn đi ! hay là muốn để các đàn em có cơ hội học hỏi

 

[duongdv]

Bài này anh duongdv thừa sức làm sao anh không giải luôn đi ! hay là muốn để các đàn em có cơ hội học hỏi

Có thể mình sẽ làm được. Nhưng rất tiếc là máy mình không cài office2007 nên không mở được file này vì có định dạng .xlsx.

 

[huuthang_bd]

Mình post bài giúp bạn vdcuong các bạn xem và giúp bạn ấy nhé.
(To vdcuong! lần sau bạn nên chuyển thành dạng *.xls rồi gửi lên diễn đàn vì với định dạng .xlsx không phải máy thành viên nào cũng đọc được do không cài office2007)

Câu 13 dùng công thức mảng này

=MIN(IF(H5:H14>=500,F5:F14,""))

Câu 14 dùng công thức này

=SUMPRODUCT(--((F5:F14<500)+(G5:G14>=200)>0),H5:H14)

 

[vdcuong]

Bài này anh duongdv thừa sức làm sao anh không giải luôn đi ! hay là muốn để các đàn em có cơ hội học hỏi

Bây giờ mình mới thấy là mình đã hiểu sai ý bài. cho mình hỏi giả sử mình có câu hỏi này:
"Tìm giá trị nhập kho nhỏ nhất của các mặt hàng (tổng các mặt hàng: cá, tôm,...) với điều kiện giá trị tồn kho > 800 (giá trị tồn kho ở đây là tổng giá trị tồn kho của các mặt hàng: cá, tôm,...)"
Mong các bạn xây dựng giùm mình công thức. Hì, ý mình lúc đầu ở ô J33 là hiểu theo ý này.

To duongdv: Rất cám ơn vì đã giúp tôi post file và những nhắc nhở bổ ích.

Một lần nữa rất cám ơn sự giúp đỡ của 2 bạn.

 

[chucuoi92]

Bây giờ mình mới thấy là mình đã hiểu sai ý bài. cho mình hỏi giả sử mình có câu hỏi này:
"Tìm giá trị nhập kho nhỏ nhất của các mặt hàng (tổng các mặt hàng: cá, tôm,...) với điều kiện giá trị tồn kho > 800 (giá trị tồn kho ở đây là tổng giá trị tồn kho của các mặt hàng: cá, tôm,...)"
Mong các bạn xây dựng giùm mình công thức. Hì, ý mình lúc đầu ở ô J33 là hiểu theo ý này.

To duongdv: Rất cám ơn vì đã giúp tôi post file và những nhắc nhở bổ ích.

Một lần nữa rất cám ơn sự giúp đỡ của 2 bạn.

thế thì bạn dùng công thức mảng này:{=MIN(IF(H5:H14>800,F5:F14,""))}

 

[huuthang_bd]

Bây giờ mình mới thấy là mình đã hiểu sai ý bài. cho mình hỏi giả sử mình có câu hỏi này:
"Tìm giá trị nhập kho nhỏ nhất của các mặt hàng (tổng các mặt hàng: cá, tôm,...) với điều kiện giá trị tồn kho > 800 (giá trị tồn kho ở đây là tổng giá trị tồn kho của các mặt hàng: cá, tôm,...)"
Mong các bạn xây dựng giùm mình công thức. Hì, ý mình lúc đầu ở ô J33 là hiểu theo ý này.

Thế thì bạn dùng công thức mảng này:

=MIN(IF(SUMIF(D5:D14,D5:D14,H5:H14)<=800,"",SUMIF(D5:D14,D5:D14,F5:F14)))

 

[vdcuong]

Thế thì bạn dùng công thức mảng này:

=MIN(IF(SUMIF(D5:D14,D5:D14,H5:H14)<=800,"",SUMIF(D5:D14,D5:D14,F5:F14)))

Chào bạn. Tôi nghĩ chắc bạn vẫn chưa hiểu ý tôi. Ý của tôi là thế này:

Ta xây dựng một bảng phụ sau:

tên hàng: nhập kho; Tồn kho
Tôm: 1510; 1108
Cá: 1232; 1192
Mực: 944, 631
Bào Ngư: 1301; 918
Nghêu: 908; 552

(Xin lỗi vì mình không upload được bảng lên)

Xác định giá trị nhỏ nhất nhập kho của từng mặt hàng trên nếu tồn kho >800

Tôi muốn công thức ở J33 mà không phải thông qua cái bảng phụ này. Không biết có thể xây dựng được công thức không nhi?
Chắc giờ các bạn đã hiểu ý tôi
Rất cám ơn các bạn đã quan tâm!

 

[huuthang_bd]

Chào bạn. Tôi nghĩ chắc bạn vẫn chưa hiểu ý tôi. Ý của tôi là thế này:

Ta xây dựng một bảng phụ sau:

tên hàng: nhập kho; Tồn kho
Tôm: 1510; 1108
Cá: 1232; 1192
Mực: 944, 631
Bào Ngư: 1301; 918
Nghêu: 908; 552

(Xin lỗi vì mình không upload được bảng lên)

Xác định giá trị nhỏ nhất nhập kho của từng mặt hàng trên nếu tồn kho >800

Tôi muốn công thức ở J33 mà không phải thông qua cái bảng phụ này. Không biết có thể xây dựng được công thức không nhi?
Chắc giờ các bạn đã hiểu ý tôi
Rất cám ơn các bạn đã quan tâm!

Là sao nhỉ? Bạn đã thử công thức ở bài #51 chưa?
Theo cái bảng của bạn, kết quả công thức trả về là 1232 mà chưa đúng à? Nghêu và mực, tồn kho <800 thì bỏ qua. Còn lại tôm, cá và bào ngư thì nhập kho của cá (1232) là nhỏ nhất. Không hiểu bạn muốn gì nữa???

 

[vdcuong]

Rất xin lỗi bạn huuthang_bd vì sự kiểm tra công thức không chính xác. Mình quên đây là công thức mảng nên không ấn CTRL+SHIFT+ENTER.
Rất cám ơn bạn.

 

[kicon]

Hi các Anh Chị, em có bài toán tìm giá trị max cho từng vị trí ,có ai chỉ giúp em bằng code cho lẹ hay bằng hàm cũng được...Thanks các Anh Chị