[Chia sẻ] Hàm UDF tìm số lớn gần nhất và số nhỏ gần nhất

Blue Softs Liên hệ QC

Hoàng Trọng Nghĩa

Chuyên gia GPE
Thành viên BQT
Moderator
Tham gia
17/8/08
Bài viết
8,418
Được thích
16,189
Giới tính
Nam
Nếu như công thức trong Excel ta tính được số lớn gần nhất:
Mã:
{=MIN(IF(C3:F3>G3,C3:F3))}
và số nhỏ gần nhất:
Mã:
{=MAX(IF(C3:F3<G3,C3:F3))}

Thì hàm tự tạo mà tôi sẽ trình bày dưới đây cũng có thể tính được như công thức trên:
tính số lớn gần nhất:
Mã:
=FindClosestNumber(C3:F3,G3)
và số nhỏ gần nhất:
Mã:
=FindClosestNumber(C3:F3,G3,1)

Và đây là hàm FindClosestNumber:
Mã:
Function FindClosestNumber(ByVal rngData As Range, dblNumber As Double, Optional ByVal bytNum As Byte) As Double
    Dim arrNum()
    Dim n As Long
    Dim rng As Range
    If bytNum = 0 Then
        For Each rng In rngData
            If Val(rng.Value) > dblNumber Then
                n = n + 1
                ReDim Preserve arrNum(1 To n)
                arrNum(n) = Val(rng.Value)
            End If
        Next
        If n Then FindClosestNumber = WorksheetFunction.Min(arrNum)
    Else
        For Each rng In rngData
            If Val(rng.Value) < dblNumber Then
                n = n + 1
                ReDim Preserve arrNum(1 To n)
                arrNum(n) = Val(rng.Value)
            End If
        Next
        If n Then FindClosestNumber = WorksheetFunction.Max(arrNum)
    End If
End Function

Ai có nhu cầu thì sử dụng, không có thì thôi, và nếu ai có hàm nào hay hơn, ngắn gọn hơn thì chia sẻ.

P/s: Giải thích các tham số trong hàm:
PHP:
Function FindClosestNumber(ByVal rngData As Range, dblNumber As Double, Optional ByVal bytNum As Byte) As Double

1) rngData: Vùng dữ liệu cần so sánh
2) dblNumber: Giá trị dạng số cần so sánh
3) bytNum: Nếu bằng 0 thì hàm trả về giá trị gần nhất lớn hơn giá trị cần so sánh và nếu khác 0 thì hàm trả về giá trị gần nhất nhỏ hơn giá trị cần so sánh. Mặc định là 0 nên không cần ghi ra tham số nếu tìm giá trị lớn gần nhất.

Như vậy FindClosestNumber tìm 2 kiểu giá trị lớn hơn hoặc nhỏ hơn gần nhất so với giá trị cần so sánh tùy thuộc vào bytNum.
 
Lần chỉnh sửa cuối:

ptm0412

Bad Excel Member
Thành viên BQT
Super Moderator
Tham gia
4/11/07
Bài viết
11,623
Được thích
32,633
Donate (Momo)
Donate
Giới tính
Nam
Nghề nghiệp
Consultant
Mã:
If bytNum = 0 Then
        For Each rng In rngData
            If Val(rng.Value) > dblNumber Then
                n = n + 1
                ReDim Preserve arrNum(1 To n)
                arrNum(n) = Val(rng.Value)
            End If
        Next
        If n Then FindClosestNumber = WorksheetFunction.Min(arrNum)
Không dùng mảng, không dùng Min Max của Excel. Nếu không có thì trả về lỗi #VALUE!
Mã:
Function FindClosestNumber(ByVal rngData As Range, dblNumber As Double, Optional ByVal bytNum As Byte) As Double
    Dim rng As Range
    If bytNum = 0 Then
        For Each rng In rngData
            If Val(rng.Value) > dblNumber Then
                If tmp = 0 Then
                     tmp = Val(rng.Value)
                ElseIf tmp <> 0 And Val(rng.Value) < tmp Then
                      tmp = Val(rng.Value)
                End If
            End If
        Next
    Else
        For Each rng In rngData
            If Val(rng.Value) < dblNumber Then
                If tmp = 0 Then
                     tmp = Val(rng.Value)
                ElseIf tmp <> 0 And Val(rng.Value) > tmp Then
                      tmp = Val(rng.Value)
                End If
            End If
        Next
    End If
FindClosestNumber = IIf(tmp <> 0, tmp, "NA")
End Function
 
Upvote 0

excel_lv1.5

Thành viên tích cực
Tham gia
20/10/17
Bài viết
937
Được thích
1,718
Giới tính
Nam
Nếu như công thức trong Excel ta tính được số lớn gần nhất:
Mã:
{=MIN(IF(C3:F3>G3,C3:F3))}
và số nhỏ gần nhất:
Mã:
{=MAX(IF(C3:F3<G3,C3:F3))}

Thì hàm tự tạo mà tôi sẽ trình bày dưới đây cũng có thể tính được như công thức trên:
tính số lớn gần nhất:
Mã:
=FindClosestNumber(C3:F3,G3)
và số nhỏ gần nhất:
Mã:
=FindClosestNumber(C3:F3,G3,1)

Và đây là hàm FindClosestNumber:
Mã:
Function FindClosestNumber(ByVal rngData As Range, dblNumber As Double, Optional ByVal bytNum As Byte) As Double
    Dim arrNum()
    Dim n As Long
    Dim rng As Range
    If bytNum = 0 Then
        For Each rng In rngData
            If Val(rng.Value) > dblNumber Then
                n = n + 1
                ReDim Preserve arrNum(1 To n)
                arrNum(n) = Val(rng.Value)
            End If
        Next
        If n Then FindClosestNumber = WorksheetFunction.Min(arrNum)
    Else
        For Each rng In rngData
            If Val(rng.Value) < dblNumber Then
                n = n + 1
                ReDim Preserve arrNum(1 To n)
                arrNum(n) = Val(rng.Value)
            End If
        Next
        If n Then FindClosestNumber = WorksheetFunction.Max(arrNum)
    End If
End Function

Ai có nhu cầu thì sử dụng, không có thì thôi, và nếu ai có hàm nào hay hơn, ngắn gọn hơn thì chia sẻ.
Vầy chắc gọn hơn

Mã:
Function FindClosestNumber(ByVal rng As Range, ByVal num As Double, Optional ByVal n As Byte = 1)
Dim cell As Range, res
res = Array("No result", "No result")
For Each cell In rng
    If cell.Value < num Then
        If (cell.Value > res(0)) Or res(0) = "No result" Then res(0) = cell.Value
    End If
    If cell.Value > num Then
        If (cell.Value < res(1)) Or res(1) = "No result" Then res(1) = cell.Value
    End If
Next
FindClosestNumber = res(n - 1)
End Function
 
Upvote 0

ptm0412

Bad Excel Member
Thành viên BQT
Super Moderator
Tham gia
4/11/07
Bài viết
11,623
Được thích
32,633
Donate (Momo)
Donate
Giới tính
Nam
Nghề nghiệp
Consultant
Tôi lại thích viết dài mà tường minh, đơn giản hơn.
Chỗ này:
If (cell.Value > res(0)) Or res(0) = "No result"
Là có lợi dụng If bỏ qua lỗi và không phải ai cũng biết: Nếu res(0) = "No result" thì phần chữ đỏ lỗi Type MisMatch
Nếu người dùng điền tham số thứ ba (n As Byte) là 0 thì lỗi res(n - 1) kết quả là #VALUE! mặc dù 0 là Byte (hợp lệ)
Bài đã được tự động gộp:

Mở rộng:

Nếu định nghĩa số gần nhất nếu bằng 1 trong các cells bảng dò thì bằng chính nó thì sửa < thành <= và > thành >=
 
Lần chỉnh sửa cuối:
Upvote 0

excel_lv1.5

Thành viên tích cực
Tham gia
20/10/17
Bài viết
937
Được thích
1,718
Giới tính
Nam
Tôi lại thích viết dài mà tường minh, đơn giản hơn.
Chỗ này:
If (cell.Value > res(0)) Or res(0) = "No result"
Là có lợi dụng If bỏ qua lỗi và không phải ai cũng biết: Nếu res(0) = "No result" thì phần chữ đỏ lỗi Type MisMatch
Nếu người dùng điền tham số thứ ba (n As Byte) là 0 thì lỗi res(n - 1) kết quả là #VALUE! mặc dù 0 là Byte (hợp lệ)
Bài đã được tự động gộp:

Mở rộng:

Nếu định nghĩa số gần nhất nếu bằng 1 trong các cells bảng dò thì bằng chính nó thì sửa < thành <= và > thành >=
n tôi để quên ghi chú là chỉ bằng 1 hoặc 2 thôi, giống như các hàm excel khi không có nó sẽ lỗi
còn cái màu đỏ đâu có lỗi đâu bạn, trừ khi gán trực tiếp thì mới lỗi, tại sao lại như vậy thì tôi không biết1632791162936.png
 
Upvote 0

ptm0412

Bad Excel Member
Thành viên BQT
Super Moderator
Tham gia
4/11/07
Bài viết
11,623
Được thích
32,633
Donate (Momo)
Donate
Giới tính
Nam
Nghề nghiệp
Consultant
còn cái màu đỏ đâu có lỗi đâu bạn, trừ khi gán trực tiếp thì mới lỗi, tại sao lại như vậy thì tôi không biết
Trước đây theo tôi biết thì đó là đặc điểm của If bỏ qua lỗi khi sử dụng And hoặc Or. Nhưng theo thử nghiệm của bạn thì lại có trường hợp không lỗi, cũng thấy lần đầu. Chính vì thế tôi thường tránh lỗi này.
Trong khi đó hàm IIf mà tôi dùng thì sẽ bị lỗi nếu 1 trong hai giá trị bị lỗi.
 
Upvote 0

befaint

|||||||||||||
Tham gia
6/1/11
Bài viết
12,458
Được thích
15,514
Nhưng theo thử nghiệm của bạn thì lại có trường hợp không lỗi, cũng thấy lần đầu.
.
Nhìn code là phát hiện ra ngay mà anh.
Anh chạy thử 2 cái này xem.

PHP:
Option Explicit

Sub vidu1()
    Dim txt As Variant
    txt = "abc"
    MsgBox (txt > 1)
End Sub

Sub vidu2()
    Dim txt As String
    txt = "abc"
    MsgBox (txt > 1)
End Sub
 
Upvote 0

HieuCD

Chuyên gia GPE
Tham gia
14/9/10
Bài viết
8,491
Được thích
17,821
Nếu như công thức trong Excel ta tính được số lớn gần nhất:
Mã:
{=MIN(IF(C3:F3>G3,C3:F3))}
và số nhỏ gần nhất:
Mã:
{=MAX(IF(C3:F3<G3,C3:F3))}

Thì hàm tự tạo mà tôi sẽ trình bày dưới đây cũng có thể tính được như công thức trên:
tính số lớn gần nhất:
Mã:
=FindClosestNumber(C3:F3,G3)
và số nhỏ gần nhất:
Mã:
=FindClosestNumber(C3:F3,G3,1)

Và đây là hàm FindClosestNumber:
Mã:
Function FindClosestNumber(ByVal rngData As Range, dblNumber As Double, Optional ByVal bytNum As Byte) As Double
    Dim arrNum()
    Dim n As Long
    Dim rng As Range
    If bytNum = 0 Then
        For Each rng In rngData
            If Val(rng.Value) > dblNumber Then
                n = n + 1
                ReDim Preserve arrNum(1 To n)
                arrNum(n) = Val(rng.Value)
            End If
        Next
        If n Then FindClosestNumber = WorksheetFunction.Min(arrNum)
    Else
        For Each rng In rngData
            If Val(rng.Value) < dblNumber Then
                n = n + 1
                ReDim Preserve arrNum(1 To n)
                arrNum(n) = Val(rng.Value)
            End If
        Next
        If n Then FindClosestNumber = WorksheetFunction.Max(arrNum)
    End If
End Function

Ai có nhu cầu thì sử dụng, không có thì thôi, và nếu ai có hàm nào hay hơn, ngắn gọn hơn thì chia sẻ.
Góp vui
Mã:
Function FindClosestNum(ByVal rng As Range, ByVal num As Double, Optional ByVal bMin As Boolean = True)
  Dim cell As Range, res
  If Not bMin Then res = "No"
  For Each cell In rng
    If cell.Value <> num And (cell.Value < num) = bMin Then
      If (cell.Value > res) = bMin Then res = cell.Value
    End If
  Next
  If res = Empty Then FindClosestNum = "No" Else FindClosestNum = res
End Function
 
Upvote 0

excel_lv1.5

Thành viên tích cực
Tham gia
20/10/17
Bài viết
937
Được thích
1,718
Giới tính
Nam
.
Nhìn code là phát hiện ra ngay mà anh.
Anh chạy thử 2 cái này xem.

PHP:
Option Explicit

Sub vidu1()
    Dim txt As Variant
    txt = "abc"
    MsgBox (txt > 1)
End Sub

Sub vidu2()
    Dim txt As String
    txt = "abc"
    MsgBox (txt > 1)
End Sub
Nếu như bạn nói khai báo String thì lỗi, vậy trường hợp này thì sao
1632796755597.png
 
Upvote 0

excel_lv1.5

Thành viên tích cực
Tham gia
20/10/17
Bài viết
937
Được thích
1,718
Giới tính
Nam
Như thế arr đang là variant mà.

Bạn làm như thế này này
PHP:
Sub c()
Dim arr() as string
' rồi làm tiếp xem

End sub
Nếu khai báo vậy thì không nạp mảng được, mà ý tôi hỏi là mặc dù arr là variant hay cái khác, nhưng rõ ràng là phần tử 0 của nó đã nhận dạng là String chứ đâu phải variant, mà sao vẫn không lỗi1632797502755.png
 
Upvote 0

befaint

|||||||||||||
Tham gia
6/1/11
Bài viết
12,458
Được thích
15,514
Nếu khai báo vậy thì không nạp mảng được, mà ý tôi hỏi là mặc dù arr là variant hay cái khác, nhưng rõ ràng là phần tử 0 của nó đã nhận dạng là String chứ đâu phải variant, mà sao vẫn không lỗiView attachment 266813

Bạn thử cái này nhé.
Còn sâu xa trong array nó như thế nào thì chắc phải nhờ tới anh nào xem được trong array người ta lập trình như thế nào, còn mình thì chịu thua rồi.

PHP:
Sub vidu3()
    Dim arr() As String
    ReDim arr(0 To 1)
    arr(0) = "a"
    arr(1) = "b"
    MsgBox arr(0) > 1
End Sub

Sub vidu4()
    Dim arr() As Variant
    ReDim arr(0 To 1)
    arr(0) = "a"
    arr(1) = 1
    MsgBox arr(0) > 1
End Sub
 
Upvote 0

VetMini

Chuyên gia GPE
Tham gia
21/12/12
Bài viết
12,350
Được thích
15,911
Tất cả các code trên đều đi theo con đường muôn thuở của GPE: hàm người dùng có cả đống tham mà chả có một lời giải thích (comments)

Đầu tiên hết, định nghĩa rõ thế nào là "gần nhất" (closest number/value)?
Tôi không rõ định nghĩa của quý vị thế nào. Nếu là tôi định nghĩa thì số bằng nó (nếu có) là số gần nó nhất.
Tất cả các code trên đều trả về số KHÁC nó.

Đáng lẽ hàm phải có các giải thích sau:
' hàm trả duyệt mảng rng và trả về giá trị gần nhất với trị num. (và không phải là trị num)
' tham số bMin cho biết đặc tính giá trị trả về --> 1: nhỏ hơn num; 0: lớn hơn num

Vì code của quý vị không chấp nhận số bằng số dò cho nên nếu mảng chỉ chứa toàn số bằng số dò thì kết quả trả về, theo mặc định, là 0. Tức là SAI.
 
Upvote 0

ptm0412

Bad Excel Member
Thành viên BQT
Super Moderator
Tham gia
4/11/07
Bài viết
11,623
Được thích
32,633
Donate (Momo)
Donate
Giới tính
Nam
Nghề nghiệp
Consultant
Tất cả các code trên đều đi theo con đường muôn thuở của GPE: hàm người dùng có cả đống tham mà chả có một lời giải thích (comments)

Đầu tiên hết, định nghĩa rõ thế nào là "gần nhất" (closest number/value)?
Tôi không rõ định nghĩa của quý vị thế nào. Nếu là tôi định nghĩa thì số bằng nó (nếu có) là số gần nó nhất.
Tất cả các code trên đều trả về số KHÁC nó.

Đáng lẽ hàm phải có các giải thích sau:
' hàm trả duyệt mảng rng và trả về giá trị gần nhất với trị num. (và không phải là trị num)
' tham số bMin cho biết đặc tính giá trị trả về --> 1: nhỏ hơn num; 0: lớn hơn num

Vì code của quý vị không chấp nhận số bằng số dò cho nên nếu mảng chỉ chứa toàn số bằng số dò thì kết quả trả về, theo mặc định, là 0. Tức là SAI.
Hỏng có tôi á.
- Code tôi dài nhưng đơn giản, đọc là hiểu, khỏi comment :p :p
- Hàm tìm không thấy trả về lỗi,
- bài #4 có mở rộng >=, <= cho việc định nghĩa lại "chấp nhận số bằng số dò"
Còn tham số bytNum thì phải viết 1 cuốn "User guide" chứ comment trong code người dùng không có đọc
 
Upvote 0

VetMini

Chuyên gia GPE
Tham gia
21/12/12
Bài viết
12,350
Được thích
15,911
Hỏng có tôi á.
- Code tôi dài nhưng đơn giản, đọc là hiểu, khỏi comment :p :p
- Hàm tìm không thấy trả về lỗi,
- bài #4 có mở rộng >=, <= cho việc định nghĩa lại "chấp nhận số bằng số dò"
Còn tham số bytNum thì phải viết 1 cuốn "User guide" chứ comment trong code người dùng không có đọc
1. đọc là hiểu: bạn hơi chủ quan. Bằng chứng là ... tôi không hiểu

2. trả về lỗi: hàm phải chú thích là "không thấy thì trả về ...". Lý do: dân chuyên nghiệp sử dụng hàm để phân tích và xem xét dữ liệu. Sau khi áp dụng hàm cho một cột thì việc đầu tiên là dò tìm "outliers" tức là tìm lỗi. (*1)

3. chấp nhận bằng số dò: cái tham cuối cùng có thể dùng bit value. Bit 2 cho biết có chấp nhận bằng hay không. Bit 1 cho biết là số lớn hay nhỏ. Ví dụ trị 11 (tức 3) cho biết tìm số lớn và chấp nhận bằng, 10 (tức 2) cho biết tìm số nhỏ và chấp nhận bằng, 01 (tức 1) tìm số lớn và khong chấp nhận bằng, 00 (tức 0) tìm số nhỏ và không chấp nhận bằng.

4. "User Guide": ở đây ta nói về hàm người dùng chứ không phải hàm căn bản của Excel. Khi bạn nhận một code hàm/sub thì việc đầu tiên bạn làmm là đọc phần chú thích về cách hoạt động và cách dùng (*2). Một code đúng chuẩn luôn luôn có phần giải thích tham số.

(*1) ở đây tôi thấy nhiều người thích bẫy lỗi công thức. Đó là điều tôi rất ít khi làm, cực chẳng đã mới dùng.

(*2) nếu không có phần giải thích thì việc đầu tiên tôi l;àm là đọc lướt nhanh qua chỗ bên trái code (bên trái dấu bằng) để biết đại khái code này sẽ sửa đổi những trị gì. Vì vậy, đối với tôi các code dùng dấu : để gộp nhiều dòng lại thành 1 sẽ trở nên khó đọc và khó debug.

(*3) tiêu đề thứt này có từ "chia sẻ" : tôi bàn đây trên tinh thần "chia sẻ" cách thức, kỹ thuật trình bày hàm người dùng. Chứ code kiếc thì cái hàm này chả có mấy thực dụng.
 
Upvote 0

Hau151978

Thành viên tích cực
Tham gia
19/10/11
Bài viết
1,477
Được thích
1,428
Upvote 0

Cá ngừ F1

( ͡° ͜ʖ ͡°)
Thành viên BQT
Moderator
Tham gia
1/1/08
Bài viết
2,492
Được thích
3,606
Donate (Momo)
Donate
Giới tính
Nam
Nghề nghiệp
Quan hệ.. và quan hệ..
Tìm số lớn gần nhất/nhỏ gần nhất sao không kết hợp hàm Large/Small với Min/max cho nhanh anh @Hoàng Trọng Nghĩa nhỉ.
 
Upvote 0

excel_lv1.5

Thành viên tích cực
Tham gia
20/10/17
Bài viết
937
Được thích
1,718
Giới tính
Nam
Upvote 0
Top Bottom