Chú ý: Các thành viên học lớp "Lập trình VBA trong Excel" có thể trao đổi bài ở đây (2 người xem)

NH_DK · 30/10/10

ptm0412 đã viết:
Viết hàm (Function) là để dùng trên sheet hoặc dùng trong các thủ tục/ hàm khác. Hàm có tham số của hàm, trong bài trên là tham số rngVung, tham số này lấy 1 vùng từ bảng tính (As Range mà).
Thế mà trong hàm lại cố định rngVung là "Ten" (B1:C10), rồi tìm 'Ngoc" trong vùng đó. Thế là dù cho dùng hàm bao nhiêu lần, ở bất kỳ đâu, cũng đều cho 1 kết quả như nhau:
- hoặc là "Ngọc" nếu có "Ngoc" trong B1:C10,
- hoặc là lỗi nếu không có
Ngoài ra, nếu người dùng gõ: =TimTen1(D110), nó cũng đi tìm trong B1:C10! Chứ không tìm trong vùng D110 mà ngừoi dùng muốn.

Tại em đang muốn vận dụng cái Name nên em mặc định đường dẫn như vậy mà?
Em cám ơ ý kiến đóng góp của anh Mỹ nha!

ptm0412 · 30/10/10

NH_DK đã viết:
Tại em đang muốn vận dụng cái Name nên em mặc định đường dẫn như vậy mà?
Em cám ơ ý kiến đóng góp của anh Mỹ nha!

Viết 1 code để vận dụng cái gì đó, thì ít nhất cũng nên nghĩ đến việc ứng dụng 1 chút. Thí dụ viết hàm để tìm 1 tên nào đó có trong danh sách có sẵn hay không.
Thí dụ:

PHP:

Private Function TimTen(ByVal SName As String) As String
 Dim rngCell As Range, VungDS As Range
  Set VungDS = Worksheets("Sheet2").Range("TEN") 
  TimTen = "Khong"
    For Each rngCell In VungDS
        If rngCell = SName Then
            TimTen = "Co"
        End If
    Next
  Set VungDS = Nothing
End Function

vuminh1601 · 30/10/10

MinhCong đã viết:
Em xin đưa cách lập hàm MaxIf bằng cách tìm tất cả các cells có chứa mã cần tính đưa vào 1 biến tạm sau đó dùng hàm Max. Code của Em như sau, mong Thầy và các Anh xem góp ý cho Em nhé!

Mã:

Private Function MaxIf(rngVung_ma As Range, sCriteria As String) Dim tmp(1 To 65500, 1 To 1), Clls As Range, jR As Long For Each Clls In rngVung_ma If Clls.Value = sCriteria Then jR = jR + 1 tmp(jR, 1) = Clls.Offset(, 1) End If Next MaxIf = WorksheetFunction.Max(tmp) End Function

Cám ơn bác đã đóng ghóp ý kiến cho các thành viên trong lớp.
Nhưng bọn e vẫn chưa học về biến tạm bác ạh.
qua đây bác nói qua qua cho bọn em chút được không?

ndu96081631 · 30/10/10

vuminh1601 đã viết:
Cám ơn bác đã đóng ghóp ý kiến cho các thành viên trong lớp.
Nhưng bọn e vẫn chưa học về biến tạm bác ạh.
qua đây bác nói qua qua cho bọn em chút được không?

Đấy là phương pháp dùng mảng, hơi trừu tượng 1 chút ---> Các bạn mới học tốt nhất không cần nghĩ đến làm gì cho mệt óc ---> Cứ làm theo lối suy nghĩ bình thường là được rồi

MinhCong · 30/10/10

Thầy và các Anh cho Em hỏi, Em có ý tưởng lập hàm Maxif bằng cách AutoFilter sau đó dùng hàm Subtotal để tìm giá trị lớn nhất. Em viết như sau:

Mã:

Private Function MaxIf(rngVung_ma As Range, sCriteria As String)
  With rngVung_ma.Resize(, 1)
    .AutoFilter (1), Criteria1:=sCriteria
    MaxIf = WorksheetFunction.Subtotal(104, [COLOR=red].Select[/COLOR])
    .AutoFilter
  End With
End Function

Sao nó lại báo lỗi nhỉ? Cái chỗ .Select Em phải thay như thế nào?

ndu96081631 · 30/10/10

MinhCong đã viết:
Thầy và các Anh cho Em hỏi, Em có ý tưởng lập hàm Maxif bằng cách AutoFilter sau đó dùng hàm Subtotal để tìm giá trị lớn nhất. Em viết như sau:

Mã:

Private Function MaxIf(rngVung_ma As Range, sCriteria As String) With rngVung_ma.Resize(, 1) .AutoFilter (1), Criteria1:=sCriteria MaxIf = WorksheetFunction.Subtotal(104, [COLOR=red].Select[/COLOR]) .AutoFilter End With End Function

Sao nó lại báo lỗi nhỉ? Cái chỗ .Select Em phải thay như thế nào?

Viết hàm theo kiểu này, nếu dùng được thì có họa chăng chỉ áp dụng cho code khác mà thôi (tức 1 đoạn code nào đó ứng dụng hàm này) ---> Nếu gõ trên bảng tính, ăn chắc 100% chẳng ra kết quả gì (hoặc báo lỗi)
Lý do vì: Những hàm tự tạo viết với mục đích gõ trên bảng tính chỉ làm nhiệm vụ tính toán rồi ra kết quả... Nó không có khả năng thay đổi bất cứ thứ gì trên bảng tính cả (chẳng hạn như Format Cells, AutoFiler, Advanced Filer...vân vân...)

khanhhoan · 31/10/10

Em vẫn chưa hiểu tại sao trong bài lập hàm MinIf mà hàm khai báo kiểu Double mà lại cần có MyMin = 2147483647. Các AC giải thích giúp dùm em được không ah?

Nguyễn Duy Tuân · 31/10/10

khanhhoan đã viết:
Em vẫn chưa hiểu tại sao trong bài lập hàm MinIf mà hàm khai báo kiểu Double mà lại cần có MyMin = 2147483647. Các AC giải thích giúp dùm em được không ah?

Hàm MinIf và MaxIf là tìm giá trị nhỏ nhất, lớn nhất theo điều kiện nào đó. Lưu ý, giá trị nhỏ nhất và giá trị lớn nhất phải nằm trong vùng dữ liệu được chọn.

vuminh1601 · 31/10/10

khanhhoan đã viết:
Em vẫn chưa hiểu tại sao trong bài lập hàm MinIf mà hàm khai báo kiểu Double mà lại cần có MyMin = 2147483647. Các AC giải thích giúp dùm em được không ah?

Cái này chỉ làm hàm tìm giá trị MIN khai báo MyMin = 2147483647 không phải hàm MinIf tìm giá trị nhỏ nhất theo điều kiện.
Tại sao lại phải có dãy số MyMin = 2147483647 và (MyMax = - 2147483647) mình vẫn chưa được rõ chỗ này (#111 và #112). Nhưng nếu ko khai báo thì KQ sẽ bị sai khi tìm giá trị MIN và MAX nếu trong vùng toàn là số âm (Làm như Code VD trên lớp) tôi đã test lại rùi. Đúng như bac HYEN17 đã nêu xem #111

PhanTuHuong · 31/10/10

vuminh1601 đã viết:
Tại sao lại phải có dãy số MyMin = 2147483647 và (MyMax = - 2147483647) mình vẫn chưa được rõ chỗ này (#111 và #112). Nhưng nếu ko khai báo thì KQ sẽ bị sai khi tìm giá trị MIN và MAX nếu trong vùng toàn là số âm (Làm như Code VD trên lớp) tôi đã test lại rùi.

Khi khai báo biến, bạn cần chú ý phạm vi biến có thể nhận giá trị. Ví dụ như:

Byte Store integer values in the range of 0 - 255
Integer Store integer values in the range of (-32,768) - (+ 32,767)
Long Store integer values in the range of (- 2,147,483,468) - (+ 2,147,483,468)
Single Store floating point value in the range of (-3.4x10-38) - (+ 3.4x1038)

Vì vậy, khi tìm giá trị nhỏ nhất (MyMin) thì ta cứ gán nó bằng giá trị lớn nhất (chính là dãy số mà bạn hỏi đó, có thể giá trị đó chưa được chính xác lắm) mà nó có thể có. Sau đó dùng vòng lặp so sánh để tìm được giá trị nhỏ nhất. Trường hợp lớn nhất thì làm ngược lại.

Theo tôi, nên hạn chế sử dụng các hàm sẵn có trong Excel (Worksheet Functions) vì là đặc thù riêng của Excel, nếu muốn phát triển hơn thì nên tự xây dựng hàm cho mình bằng kiến thức lập trình.

VD như bạn tìm người trẻ nhất trong danh sách thì đầu tiên gán MyMin = 150 tuổi đã (coi là tuổi khó có ai đạt được), sau đó dùng vòng lặp nếu ai kém hơn thì gán MyMin bằng tuổi đó ---> từ đó tìm được tuổi trẻ nhất.

Nguyễn Duy Tuân · 31/10/10

vuminh1601 đã viết:
Cái này chỉ làm hàm tìm giá trị MIN khai báo MyMin = 2147483647 không phải hàm MinIf tìm giá trị nhỏ nhất theo điều kiện.
Tại sao lại phải có dãy số MyMin = 2147483647 và (MyMax = - 2147483647) mình vẫn chưa được rõ chỗ này (#111 và #112). Nhưng nếu ko khai báo thì KQ sẽ bị sai khi tìm giá trị MIN và MAX nếu trong vùng toàn là số âm (Làm như Code VD trên lớp) tôi đã test lại rùi. Đúng như bac HYEN17 đã nêu xem #111

Code trên lớp chỉ là ví dụ đơn giản về thuật toán. Hình như mọi người đang bị sai về hai hàm này. Hãy gán giá trị đầu tiên ở đầu danh sách, chứ không phải các số 21...., -21.....

huongchuoi · 31/10/10

[ Nguyên văn bởi vuminh1601
Cái này chỉ làm hàm tìm giá trị MIN khai báo MyMin = 2147483647 không phải hàm MinIf tìm giá trị nhỏ nhất theo điều kiện.
Tại sao lại phải có dãy số MyMin = 2147483647 và (MyMax = - 2147483647) mình vẫn chưa được rõ chỗ này (#111 và #112). Nhưng nếu ko khai báo thì KQ sẽ bị sai khi tìm giá trị MIN và MAX nếu trong vùng toàn là số âm (Làm như Code VD trên lớp) tôi đã test lại rùi. Đúng như bac HYEN17 đã nêu xem #111
Code trên lớp chỉ là ví dụ đơn giản về thuật toán. Hình như mọi người đang bị sai về hai hàm này. Hãy gán giá trị đầu tiên ở đầu danh sách, chứ không phải các số 21...., -21.....
.]

thầy ơi, nhưng vấn đề là tại sao khi mình làm hàm mymax thì ko cần gán giá trị đầu danh sách còn làm hàm mymin thì phải gán ah? em ko hiểu nguyên nhân vì sao? thầy giải thích hộ em ah.

khanhhoan · 31/10/10

huongchuoi đã viết:
thầy ơi, nhưng vấn đề là tại sao khi mình làm hàm mymax thì ko cần gán giá trị đầu danh sách còn làm hàm mymin thì phải gán ah? em ko hiểu nguyên nhân vì sao? thầy giải thích hộ em ah.

Theo lý giải của các thầy cộng thêm việc test thử với 1 giá trị âm trong vùng thì em có thể lý giải là lúc đầu máy sẽ chạy tự động từ giá trị nhỏ nhất ( tức - 2147483647) đến giá trị lớn nhất (2147483647), suy ra hàm MyMax không cần đặt giá trị. Còn với hàm MyMin, nếu tất cả giá trị trong vùng âm thì mặc nhiên nó sẽ lớn hơn 0 và máy sẽ chọn giá trị nhỏ nhất là 0, còn nếu ta đặt 1 số âm trong đó thì số âm sẽ được chọn là min. Nếu ta đặt giá trị MyMin =2147483647, tức là giá trị tới hạn của kiểu long thì máy sẽ bắt buộc phải chạy từ lớn về nhỏ, do vậy sẽ tìm được giá trị nhỏ nhất.
Đây chỉ là suy luận chủ quan của em. Mong mọi người cho ý kiến.
Tuy vậy em vẫn còn thắc mắc là tại sao máy không chọn giá trị tới hạn kiểu Double hoặc Single mặc dù hàm khai báo theo kiểu Double, do vậy nếu giá trị cần so sánh nhỏ hơn - 2147483647 hoặc lớn hơn 2147483647 thì sẽ tìm thế nào? Em đã thử giá trị trong 1 cell là 214748399999 thì hàm MyMax báo lỗi #VALUE

vuminh1601 · 31/10/10

Xin mời ...http://www.giaiphapexcel.com/forum/group.php?do=discuss&discussionid=13

Nguyễn Duy Tuân · 1/11/10

Hàm MyMin, MyMax

Đây là đáp án về hàm MyMin, MyMax. Còn MyMinIf, MyMaxIf các bạn có thể viết trên cơ sở đáp án này.

Mã:

Function MyMax(ByVal rngNum As Range) As Double
    Dim MyCell As Range
    MyMax = rngNum.Cells(1, 1).Value
    For Each MyCell In rngNum
        If MyCell.Value > MyMax Then
            MyMax = MyCell.Value
        End If
    Next
End Function

Function MyMin(ByVal rngNum As Range) As Double
    Dim MyCell As Range
    MyMin = rngNum.Cells(1, 1).Value
    For Each MyCell In rngNum
        If MyCell.Value < MyMin Then
            MyMin = MyCell.Value
        End If
    Next
End Function

sealand · 1/11/10

Anh Tuân à, nếu dùng hàm IIf() sẽ gọn hơn cấu trúc If....End if :

Mã:

Function MyMin1(ByVal rngNum As Range) As Double
    Dim MyCell As Range
    MyMin1 = rngNum.Cells(1, 1).Value
    For Each MyCell In rngNum
    MyMin1 = IIf(MyCell.Value < MyMin1, MyCell, MyMin1)
    Next
End Function
'------------------------------------------------------------------------
Function MyMax1(ByVal rngNum As Range) As Double
    Dim MyCell As Range
    MyMax1 = rngNum.Cells(1, 1).Value
    For Each MyCell In rngNum
    MyMax1 = IIf(MyCell.Value > MyMax1, MyCell, MyMax1)
    Next
End Function

Nguyễn Duy Tuân · 1/11/10

sealand đã viết:

Anh Tuân à, nếu dùng hàm IIf() sẽ gọn hơn cấu trúc If....End if :

Mã:

Function MyMin1(ByVal rngNum As Range) As Double
    Dim MyCell As Range
    MyMin1 = rngNum.Cells(1, 1).Value
    For Each MyCell In rngNum
    MyMin1 = IIf(MyCell.Value < MyMin1, MyCell, MyMin1)
    Next
End Function
'------------------------------------------------------------------------
Function MyMax1(ByVal rngNum As Range) As Double
    Dim MyCell As Range
    MyMax1 = rngNum.Cells(1, 1).Value
    For Each MyCell In rngNum
    MyMax1 = IIf(MyCell.Value > MyMax1, MyCell, MyMax1)
    Next
End Function

Vâng, đúng vậy anh ạ. hàm IIF chỉ dùng để nhận giá trị đơn, còn If..Then thì nhận khối lệnh. Vì thế tùy từng trường hợp dùng IIF hay If..Then.
Vì lớp học này các học viên chưa học một hàm nào mà đang học ngôn ngữ nên chưa dùng hàm. Sau khi học ngôn ngữ ổn rồi thì chuyển sang học các hàm và các đối tượng Excel.

Vì tại sao chúng em cho bài tập viết hàm MyMin, MyMax mặc dù Excel cái này rồi. Cái học viên có được từ bài tập này đó là cách viết UDF - hàm người dùng trên bảng tính, biết hơn về thuật toán, từ đây tiến tới các thuật toán sắp xếp, ...

Cảm ơn anh đã chia sẻ.

MinhCong · 1/11/10

Dựa trên cơ sở hàm MyMax và MyMin của Thầy Tuân, Em viết 2 hàm MyMaxIf và MyMinIF như sau:

Mã:

Function MyMaxIf(ByVal rngVung_ma As Range, ByVal sCriteria As String, ByVal rngNum As Range) As Double
  Dim iR As Long, enR As Long
  enR = rngNum.Rows.Count
  MyMaxIf = Application.WorksheetFunction.Min(rngNum)
    For iR = 1 To enR
        If (rngVung_ma.Cells(iR, 1).Value = sCriteria) And (rngNum.Cells(iR, 1).Value > MyMaxIf) Then
            MyMaxIf = rngNum.Cells(iR, 1).Value
        End If
    Next
End Function
'-----------------------------------------------------------------------------------------------
Function MyMinIf(ByVal rngVung_ma As Range, ByVal sCriteria As String, ByVal rngNum As Range) As Double
  Dim iR As Long, enR As Long
  enR = rngNum.Rows.Count
  MyMinIf = Application.WorksheetFunction.Max(rngNum)
    For iR = 1 To enR
        If (rngVung_ma.Cells(iR, 1).Value = sCriteria) And (rngNum.Cells(iR, 1).Value < MyMinIf) Then
            MyMinIf = rngNum.Cells(iR, 1).Value
        End If
    Next
End Function

Nguyễn Duy Tuân · 1/11/10

MinhCong đã viết:

Dựa trên cơ sở hàm MyMax và MyMin của Thầy Tuân, Em viết 2 hàm MyMaxIf và MyMinIF như sau:

Mã:

Function MyMaxIf(ByVal rngVung_ma As Range, ByVal sCriteria As String, ByVal rngNum As Range) As Double
  Dim iR As Long, enR As Long
  enR = rngNum.Rows.Count
  MyMaxIf = Application.WorksheetFunction.Min(rngNum)
    For iR = 1 To enR
        If (rngVung_ma.Cells(iR, 1).Value = sCriteria) And (rngNum.Cells(iR, 1).Value > MyMaxIf) Then
            MyMaxIf = rngNum.Cells(iR, 1).Value
        End If
    Next
End Function
'-----------------------------------------------------------------------------------------------
Function MyMinIf(ByVal rngVung_ma As Range, ByVal sCriteria As String, ByVal rngNum As Range) As Double
  Dim iR As Long, enR As Long
  enR = rngNum.Rows.Count
  MyMinIf = Application.WorksheetFunction.Max(rngNum)
    For iR = 1 To enR
        If (rngVung_ma.Cells(iR, 1).Value = sCriteria) And (rngNum.Cells(iR, 1).Value < MyMinIf) Then
            MyMinIf = rngNum.Cells(iR, 1).Value
        End If
    Next
End Function

Không dùng bất kỳ hàm nào trong WorksheetFunction nhé. MinhCong thử làm cách khác xem thế nào.

ndu96081631 · 1/11/10

MinhCong đã viết:

Dựa trên cơ sở hàm MyMax và MyMin của Thầy Tuân, Em viết 2 hàm MyMaxIf và MyMinIF như sau:

Mã:

Function MyMaxIf(ByVal rngVung_ma As Range, ByVal sCriteria As String, ByVal rngNum As Range) As Double
  Dim iR As Long, enR As Long
  enR = rngNum.Rows.Count
  MyMaxIf = Application.WorksheetFunction.Min(rngNum)
    For iR = 1 To enR
        If (rngVung_ma.Cells(iR, 1).Value = sCriteria) And (rngNum.Cells(iR, 1).Value > MyMaxIf) Then
            MyMaxIf = rngNum.Cells(iR, 1).Value
        End If
    Next
End Function
'-----------------------------------------------------------------------------------------------
Function MyMinIf(ByVal rngVung_ma As Range, ByVal sCriteria As String, ByVal rngNum As Range) As Double
  Dim iR As Long, enR As Long
  enR = rngNum.Rows.Count
  MyMinIf = Application.WorksheetFunction.Max(rngNum)
    For iR = 1 To enR
        If (rngVung_ma.Cells(iR, 1).Value = sCriteria) And (rngNum.Cells(iR, 1).Value < MyMinIf) Then
            MyMinIf = rngNum.Cells(iR, 1).Value
        End If
    Next
End Function

Nếu dùng đến hàm MAX và MIN, thôi thà gõ trực tiếp lên bảng tính luôn cho xong ----> Nếu có ý định mai này dùng vào những ứng dụng khác thì tạm thời quên các hàm Excel đi
Ít ra cũng phải vầy:

PHP:

Function MaxIf(CritRng As Range, Criteria, ResRng As Range) As Double
  Dim TmpMax As Double, i As Long, j As Long
  TmpMax = -9.99999999999999E+307
  For i = 1 To CritRng.Rows.Count
    For j = 1 To CritRng.Columns.Count
      If CritRng(i, j) = Criteria Then
        If ResRng(i, j) > TmpMax Then TmpMax = ResRng(i, j).Value
      End If
    Next
  Next
  MaxIf = TmpMax
End Function

PHP:

Function MinIf(CritRng As Range, Criteria, ResRng As Range) As Double
  Dim TmpMin As Double, i As Long, j As Long
  TmpMin = 9.99999999999999E+307
  For i = 1 To CritRng.Rows.Count
    For j = 1 To CritRng.Columns.Count
      If CritRng(i, j) = Criteria Then
        If ResRng(i, j) < TmpMin Then TmpMin = ResRng(i, j).Value
      End If
    Next
  Next
  MinIf = TmpMin
End Function

Khi viết hàm nên tổng quát 1 chút ---> Phải dùng được với 1 vùng 1 cột nhiều dòng hoặc 1 dòng nhiều cột (thậm chí là 1 vùng nhiều cột nhiều dòng cũng chơi luôn)
Tổng quát hơn nữa thì phải vầy:

PHP:

Function MaxIf(ByVal CritArr, ByVal Criteria, ByVal ResArr) As Double
  Dim TmpMax As Double, TmpCrit, TmpRes, i As Long, j As Long
  TmpMax = -9.99999999999999E+307
  TmpCrit = CritArr: TmpRes = ResArr
  For i = LBound(TmpCrit, 1) To UBound(TmpCrit, 1)
    For j = LBound(TmpCrit, 2) To UBound(TmpCrit, 2)
      If TmpCrit(i, j) = Criteria Then
        If TmpRes(i, j) > TmpMax Then TmpMax = TmpRes(i, j)
      End If
    Next
  Next
  MaxIf = TmpMax
End Function

PHP:

Function MinIf(ByVal CritArr, ByVal Criteria, ByVal ResArr) As Double
  Dim TmpMin As Double, TmpCrit, TmpRes, i As Long, j As Long
  TmpMin = 9.99999999999999E+307
  TmpCrit = CritArr: TmpRes = ResArr
  For i = LBound(TmpCrit, 1) To UBound(TmpCrit, 1)
    For j = LBound(TmpCrit, 2) To UBound(TmpCrit, 2)
      If TmpCrit(i, j) = Criteria Then
        If TmpRes(i, j) < TmpMin Then TmpMin = TmpRes(i, j)
      End If
    Next
  Next
  MinIf = TmpMin
End Function

Không hề quan tâm đến Range luôn... Khéo léo, có thể kết hợp 2 hàm này làm 1, chẳng hạn:

PHP:

Function SummaryIf(ByVal CritArr, ByVal Criteria, ByVal ResArr, ByVal FuncType As String) As Double
  '.......................
End Function

Tìm MAX hay MIN sẽ dựa vào tham số FuncType ---> Đại khái thế

Chú ý: Các thành viên học lớp "Lập trình VBA trong Excel" có thể trao đổi bài ở đây (2 người xem)

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

Let's patience

Bad Excel Member

Thành viên thường trực

Huyền thoại GPE

Thành viên gắn bó

Huyền thoại GPE

Thành viên chính thức

Nghị Hách

Thành viên thường trực

VBA & VB.NET for Excel & AutoCad

Nghị Hách

Thành viên mới

Thành viên chính thức

Thành viên thường trực

Nghị Hách

Thành viên gạo cội

Nghị Hách

Thành viên gắn bó

Nghị Hách

Huyền thoại GPE

Sinh nhật lần 19 GPE