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

Liên hệ QC
Hoàng Trọng Nghĩa

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,662
Được thích
16,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ẻ.

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:
Sắp xếp theo thời gian sắp xếp theo bầu chọn
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)
Nhấp chuột vào đây để mở rộng...
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
Hoàng Trọng Nghĩa đã viết:
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ẻ.
Nhấp chuột vào đây để mở rộng...
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
excel_lv1.5 đã viết:
Vầy chắc gọn hơn
Nhấp chuột vào đây để mở rộng...
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
ptm0412 đã viết:
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 >=
Nhấp chuột vào đây để mở rộng...
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
excel_lv1.5 đã viết:
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
Nhấp chuột vào đây để mở rộng...
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
ptm0412 đã viết:
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ấp chuột vào đây để mở rộng...
.
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
Hoàng Trọng Nghĩa đã viết:
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ẻ.
Nhấp chuột vào đây để mở rộng...
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
befaint đã viết:
.
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
Nhấp chuột vào đây để mở rộng...
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
befaint đã viết:
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
Nhấp chuột vào đây để mở rộng...
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
excel_lv1.5 đã viết:
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
Nhấp chuột vào đây để mở rộng...

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
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
VetMini đã viết:
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.
Nhấp chuột vào đây để mở rộng...
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
ptm0412 đã viết:
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
Nhấp chuột vào đây để mở rộng...
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
Upvote 0
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
Upvote 0
Bạn phải đăng nhập hoặc đăng ký để trả lời bài viết tại đây.
Web KT

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

GPE-Trợ Lý
KHÓA HỌC BỨT PHÁ KỸ NĂNG EXCEL
    • Thích
Trả lời
2
Đọc
56
ptm0412
ptm0412
Tuấn Bụng Bự
  • Question
Tìm Bộ 8 số xuất hiện nhiều nhất trong random( 1 - 80)
Trả lời
7
Đọc
180
huuthang_bd
huuthang_bd
H
Hàm đếm số ngày thứ bảy và chủ nhật trong một khoảng thời gian
Trả lời
14
Đọc
19K
huuthang_bd
huuthang_bd
N
Giúp sửa mã code
Trả lời
16
Đọc
226
ptm0412
ptm0412
dung09031989
Xin giúp công thức tô màu các ô phía sau khi kế hoạch đặt hàng thiếu so với tồn kho.?
Trả lời
0
Đọc
1
dung09031989
dung09031989
V
[Nhờ giúp đỡ] Cách tìm thời gian bị trùng trong excel ở nhiều thời gian khác nhau
Trả lời
1
Đọc
16
tranvansau278
T
C
Công thức tự nhập liệu số lượng nhập khi bị âm
Trả lời
0
Đọc
8
cherry0187
C
N
Vẽ biểu đồ kết hợp nhiều yếu tố trong excel
Trả lời
1
Đọc
13
hoangminhtien
hoangminhtien
ongke0711
  • Question
Tải hóa đơn điện tử (https://hoadondientu.gdt.gov.vn/) Excel Vba
    • Thích
    • Yêu thích
11 12 13
Trả lời
244
Đọc
13K
HeSanbi
HeSanbi
P
Tổng hợp dữ liệu từ các sheet theo chọn lọc
Trả lời
2
Đọc
62
phutdm
P
dunghtgiga
  • Đã khóa
Tính bốn tuần cho một tháng
Trả lời
9
Đọc
256
ptm0412
ptm0412
zarra
Cần giúp tạo form in mã vạch cho vật tư theo SL cụ thể.
Trả lời
3
Đọc
94
HUONGHCKT
H
Mr Đạo
in giấy ra bị lệch xuống dưới
    • Thích
2
Trả lời
29
Đọc
26K
Hoàng Tuấn 868
H
H
Không xem được ảnh trên Excel
Trả lời
3
Đọc
1K
huyentran9292
H
T
Xin form quản lý dữ liệu học viên của Trung tâm Đào tạo
Trả lời
2
Đọc
207
hakiru188
H
C
Code Lọc Dữ Liệu
Trả lời
10
Đọc
165
Cúc Hr
C
Y
Cách ẩn công thức 1 ô trong sheet
Trả lời
8
Đọc
113
hoangminhtien
hoangminhtien
vuha
  • Question
Lỗi chọn dòng thứ 6 trong sheet được chọn: rows("6:6").select
Trả lời
12
Đọc
157
ptm0412
ptm0412
Nguyencaocuong86
Tô màu dữ liệu trùng lặp trong excel.
Trả lời
10
Đọc
139
Nguyencaocuong86
Nguyencaocuong86
Hoangquyenbong
  • Question
Nhờ viết code lọc dữ liệu tại các sheet khác nhau.
Trả lời
12
Đọc
161
Hoangquyenbong
Hoangquyenbong
Xem thêm…

Thành viên có số lượng bài viết cao nhất tháng

Thành viên có điểm tương tác cao nhất tháng

Back
Top Bottom