[Chia sẻ] Hàm UDF tìm số lớn gần nhất và số nhỏ gần nhất (1 người xem)

Liên hệ QC

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

Đang trực tuyến: 1 (Thành viên: 0, Khách: 1)
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,725
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
Hàm UDF của anh @Hoàng Trọng Nghĩa có thể đưa thêm phần gợi ý vào cú pháp không anh. Như em copy hàm này và giờ chưa biết đưa các tham số vào như nào?
Ví dụ với dãy số trong File.
Tìm số lớn gần nhất ở đây theo e hiểu là số lớn thứ 2 trong dẫy số (tương tự với số nhỏ gần nhất). Nếu dùng hàm Large(array,k), k hiểu là vị trí (tính từ lớn nhất), vậy mình có thể ko chỉ tìm số lớn gần nhất, mà hàm này tổng quát hơn (ví dụ như lọc Top 10 chẳng hạn).
 

File đính kèm

Upvote 0
Cá ngừ F1 đã viết:
Hàm UDF của anh @Hoàng Trọng Nghĩa có thể đưa thêm phần gợi ý vào cú pháp không anh. Như em copy hàm này và giờ chưa biết đưa các tham số vào như nào?
Ví dụ với dãy số trong File.
Tìm số lớn gần nhất ở đây theo e hiểu là số lớn thứ 2 trong dẫy số (tương tự với số nhỏ gần nhất). Nếu dùng hàm Large(array,k), k hiểu là vị trí (tính từ lớn nhất), vậy mình có thể ko chỉ tìm số lớn gần nhất, mà hàm này tổng quát hơn (ví dụ như lọc Top 10 chẳng hạn).
Nhấp chuột vào đây để mở rộng...
Tui đi vô lớp học có 47 em học sinh, tui cao 1 mét 65.
1) Tìm cho tui thằng lùn nhất trong nhóm những thằng...cao hơn tui _ FindClosestNumber( Chiều cao tất cả 47 học sinh, Chiều cao của tui, 0 )
2) Tìm cho tui thằng cao nhất trong nhóm những thằng....lùn hơn tui _ FindClosestNumber( Chiều cao tất cả 47 học sinh, Chiều cao của tui, 1)
Híc
 
Upvote 0
concogia đã viết:
Tui đi vô lớp học có 47 em học sinh, tui cao 1 mét 65.
1) Tìm cho tui thằng lùn nhất trong nhóm những thằng...cao hơn tui _ FindClosestNumber( Chiều cao tất cả 47 học sinh, Chiều cao của tui, 0 )
2) Tìm cho tui thằng cao nhất trong nhóm những thằng....lùn hơn tui _ FindClosestNumber( Chiều cao tất cả 47 học sinh, Chiều cao của tui, 1)
Híc
Nhấp chuột vào đây để mở rộng...
Wow, em đã hiểu, cảm ơn bác cò, vậy thì phải dùng Large kết hợp với Rank
 
Upvote 0
VetMini đã viết:
Nếu hàm dùng trên bảng tính thì nó nên trả về #N/A (no value is avaiable). Chính xác định nghĩa của lỗi NA.
Nhấp chuột vào đây để mở rộng...
Tôi vốn định cho bằng "NA" nên viết như thế, khi thấy nó ra #VALUE! thì buồn ngủ đến, thôi post đại (gần 1 giờ khuya). Đi nằm rồi mới nhớ ra và biết nguyên nhân do khai báo hàm As Double. Do ngứa tay làm nhanh nên vậy. :D
 
Upvote 0
VetMini đã viết:
"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.
Nhấp chuột vào đây để mở rộng...
Tôi hiểu điều này. Khi viết hàm hoặc code bất kỳ phải lường trước đến mức tối đa các tình huống, test 1 cách có phương pháp càng nhiều trường hợp càng tốt (ví dụ trường hợp NA).
Nếu code lớn viết hẳn 1 User guide trong đó hướng dẫn chi tiết quy trình chuẩn bị, chạy, và sau khi chạy. Đặc biệt phải lưu ý người dùng những việc không được phép làm.

1632822466010.png
 
Upvote 0
Một thuật toán khác:
1. sort dãy số.
2. dùng hàm Match để tìm gần đúng.
3. số bên phải/trái nó là số cần tìm.
Bản thân tôi nghĩ là phép sort cũng gồm phép duyệt và so sánh. Như vậy nó chỉ tốn năng lượng hơn các thuật toán kia chút xíu. Chủ yếu là ở chỗ chép đi chép lại các phần tử mảng.
Match gần đúng dùng phép dòg nhị phân cho nên rất hiệu quả.
 
Upvote 0
Upvote 0
VetMini đã viết:
Một thuật toán khác:
1. sort dãy số.
2. dùng hàm Match để tìm gần đúng.
3. số bên phải/trái nó là số cần tìm.
Bản thân tôi nghĩ là phép sort cũng gồm phép duyệt và so sánh. Như vậy nó chỉ tốn năng lượng hơn các thuật toán kia chút xíu. Chủ yếu là ở chỗ chép đi chép lại các phần tử mảng.
Match gần đúng dùng phép dòg nhị phân cho nên rất hiệu quả.
Nhấp chuột vào đây để mở rộng...
Nếu như trong dãy số có nhiều số bằng nhau, thì bên phải/trái nó cũng chính là nó. Vậy thuật toán có cần remove duplicate ko anh?
 
Upvote 0
Cá ngừ F1 đã viết:
Nếu như trong dãy số có nhiều số bằng nhau, thì bên phải/trái nó cũng chính là nó. Vậy thuật toán có cần remove duplicate ko anh?
Nhấp chuột vào đây để mở rộng...
Tại bạn không quen dùng hàm Match gần đúng. Nếu có nhiều số trùng nhau thì hàm Match sẽ trả về vị trí cuối cùng của trị ấy. Bên trái nó là số bằng hoặc nhỏ hơn, bên phải luôn luôn là số lớn hơn (trừ phi nó là số lớn nhất trong dãy và hiển nhiên là không có số bên phải).
Quen dùng rồi sẽ tự biết thuật dò trái dò phải.
 
Upvote 0
Bổ sung phương pháp test sau khi viết code (sub, function) đơn giản:
Bài này giới hạn trong việc viết hàm đơn giản, ví dụ như code trong chủ đề này: Tìm số gần nhất (lớn hơn hoặc nhỏ hơn, chấp nhận kết quả bằng hay không bằng)
Hàm có 3 tham số: Vùng dò tìm (as Range), giá trị dò tìm (as double) và loại kết quả dò (as byte). Kết quả trả về double. Lần lượt test ít nhất như sau:
1. Vùng dò tìm:
- Cho số ngẫu nhiên bao gồm số âm/ dương, số nguyên/ thập phân, bao gồm cả số 0
- Các giá trị sắp xếp ngẫu nhiên/ sort sẵn tăng dần, sort sẵn giảm dần (có thể bỏ qua nếu chắc chắn trong code duyệt hết các phần tử)
- Cho xen số có giá trị vô cùng bé, vô cùng lớn (âm/ dương)
- Cho xen giá trị text, giá trị ngày tháng
- Cho xen giá trị trả về từ công thức
- Cho xen giá trị lỗi do công thức
- Thay đổi kích thước vùng: 1 cột nhiều dòng, 1 dòng nhiều cột, nhiều dòng nhiều cột.
- Thay đổi kích thước chỉ 1 cell, rất rất nhiều cells

2. Giá trị dò tìm
- Cho giá trị số ngẫu nhiên: nguyên/ phân, âm/ dương/ zero
- Cho giá trị số vô cùng lớn, vô cùng bé, số lớn hơn / bé hơn vùng dò tìm
- Cho giá trị có sẵn trong vùng dò tìm
- Cho giá trị trả về từ công thức
- Cho giá trị text, giá trị ngày tháng
- Cho giá trị lỗi trả về từ công thức
- Cho giá trị là 1 range nhiều hơn 1 cell

3. loại kết quả (loại byte)
- cho lần lượt các giá trị đúng của byte: 0, 1, 2, ...
- Cho giá trị âm (không phải byte)
- cho giá trị lấy từ 1 cell
- cho giá trị lấy từ nhiều cells
- cho giá trị lấy từ cell là kết quả của công thức
- cho giá trị text. giá trị ngày tháng

Sau khi test tất cả các trường hợp (có thể nghĩ ra, vì mỗi người có giới hạn), mỗi trường hợp sinh ra lỗi:
- Kết quả sai do code: sửa code.
- Phân biệt lỗi #NUM, #VALUE, #N/A, ... để trả về kết quả hay sửa code. Nếu sửa code cho kết quả lỗi đúng từng loại thì tốt hơn
- Lỗi do người dùng: Phải ghi chú và viết hướng dẫn

TB:
Bản thân tôi mặc dù biết như vậy nhưng chỉ thực hiện được hơn nửa những việc ấy. Do chủ quan nên đôi khi ỷ lại vào thuật toán, vào phương pháp viết code nên bỏ qua 1 số trường hợp. Chẳng hạn tôi viết vòng lặp duyệt hết rồi thì không cần test việc sắp xếp dữ liệu. Nhưng ít nhất cũng phải test các trường hợp hiển nhiên như tìm thấy/ không tìm thấy. Do vẫn còn những trường hợp bị bỏ qua (cố tình hoặc vô tình) nên rất ít khi tôi mạnh dạn "chia sẻ", viết chỉ để tham khảo và bàn luận. Kể cả bài này.
 
Upvote 0
ptm0412 đã viết:
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
Nhấp chuột vào đây để mở rộng...
Do mấy nay bận rộn nên chỉ xem lướt qua các bài viết mà không đi vào chi tiết, hôm nay với bài góp ý này tôi thấy thuật toán rất hay, nhưng cũng có vài chi tiết nhỏ thôi cần fix lại tí là ổn.

Trước hết phải khai báo cho biến tmp:
PHP: 
Dim tmp As Double

Và để trả về kết quả không tìm thấy giá trị cần tìm tại đây:
PHP: 
FindClosestNumber = IIf(tmp <> 0, tmp, "NA")

Thì đầu tiên phải thay kiểu dữ liệu trả về cho hàm bằng Variant thay vì Double:
PHP: 
Function FindClosestNumber(ByVal rngData As Range, dblNumber As Double, Optional ByVal bytNum As Byte) As Variant

Có lẽ chỗ này anh ptm0412 viết vội nên không quan tâm hoặc không để ý.

Nhưng nếu muốn trả về lỗi #N/A! có lẽ chúng ta nên chuyên nghiệp hơn nếu sử dụng hàm CVErr:
PHP: 
FindClosestNumber = IIf(tmp <> 0, tmp, CVErr(xlErrNA))

Bây giờ thì mình fix lại những chi tiết nhỏ nhặt ở trên thành hàm hoàn chỉnh theo cách của mình:
Mã: 
Function FindClosestNumber(ByVal rngData As Range, dblNumber As Double, Optional ByVal bytNum As Byte) As Variant
    Dim rng As Range
    Dim tmp As Double
    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, CVErr(xlErrNA))
End Function

Rất cám ơn anh ptm0412.
 
Upvote 0
Hoàng Trọng Nghĩa đã viết:
For Each rng In rngData
Nhấp chuột vào đây để mở rộng...
Sao anh không chép Range vào Array để xử lý. Nếu đã phải dùng VBA thì phải lý do gì đó, nếu không dùng hàm Aggregate() còn hơn.

Hoàng Trọng Nghĩa đã viết:
If Val(rng.Value) > dblNumber Then
Nhấp chuột vào đây để mở rộng...
Code có hoạt động khi trong rngData có kết quả của công thức là #VALUE, #N/A, #DIV0... không anh? Nếu có hoạt động thì kết quả trả về là gì?
 
Upvote 0
befaint đã viết:
Sao anh không chép Range vào Array để xử lý. Nếu đã phải dùng VBA thì phải lý do gì đó, nếu không dùng hàm Aggregate() còn hơn.


Code có hoạt động khi trong rngData có kết quả của công thức là #VALUE, #N/A, #DIV0... không anh? Nếu có hoạt động thì kết quả trả về là gì?
Nhấp chuột vào đây để mở rộng...
1) Do chủ đích là thực hiện trên Range nên đưa tham số rngData vào để dễ xử lý. Giả sử tham số rngData được thay thế bằng arrData kiểu biến Variant thì người ta đưa gì vào cũng được hoặc người ta ghi trên sheet: =FindClosestNumber("Nghĩa đẹp trai",6) nó cũng chấp nhận, trong khi dùng rngData nó trả ngay về kết quả #VALUE! mà chúng ta không cần phải bẫy lỗi ở trong đó.

2) Code có hoạt động khi tham số có lỗi hay không thì xin thưa tự nó trả về lỗi #VALUE!
Nhưng nếu ta thêm On Error Resume Next thì nó sẽ bỏ qua lỗi này và tính các rng khác như thường.
 
Upvote 0
Hoàng Trọng Nghĩa đã viết:
Do mấy nay bận rộn nên chỉ xem lướt qua các bài viết mà không đi vào chi tiết, hôm nay với bài góp ý này tôi thấy thuật toán rất hay, nhưng cũng có vài chi tiết nhỏ thôi cần fix lại tí là ổn.
Nhấp chuột vào đây để mở rộng...
Nếu đọc hết tất cả các bài sẽ thấy:
- bài 4 tôi mở rộng ra chấp nhận số gần nhất là chính nó
- bài 26 tôi kể do trễ nên post đại, đi ngủ vẫn nhớ ra lý do là do double. Bản thân chuỗi "NA" mang nghĩa not available theo tiếng Anh. Tôi không định dùng chuẩn "#N/A"
- bài 32 tôi tổng kết phương pháp test
Và những bài khác của những người khác, cũng xứng đáng để học hỏi (kể cả tôi)
 
Upvote 0
excel_lv1.5 đã viết:
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
Nhấp chuột vào đây để mở rộng...
Cám ơn bạn, hàm ngắn gọn cám ơn bạn.
 
Upvote 0
excel_lv1.5 đã viết:
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
Nhấp chuột vào đây để mở rộng...
Nếu giải thuật tìm bao gồm cả hai trị thì người ta viết một hàm trả về cả hai trị và một hàm để chọn một trong hai.
Lợi điểm: khi cần tìm cả hai thì chỉ cần tính 1 lần.

Function FindClosestVal(ByVal rng As Range, ByVal num As Double, Optional ByVal n As Byte = 1)
' hàm duyệt các số trong range rng và trả về giá trị gần nhất với num
' tham số n (trị 0/1) xác định trị ấy nhỏ hơn n (0), hay lớn hơn n (1)
FindClosestVal = FindClosestVals(rng, num)(n - 1)
End Function

Function FindClosestVals(ByVal rng As Range, ByVal num As Double)
' hàm duyệt các số trong range rng và trả về một mảng hai phần tử
' phần tử thứ nhất (0) là số gần nhất với num và nhỏ hơn num (max của các số nhỏ hơn num)
' phần tử thứ hai (1) là số gần nhất với num và lớn hơn num (min của các số lớn hơn num)
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
ElseIf cell.Value > num Then
If (cell.Value < res(1)) Or res(1) = "No result" Then res(1) = cell.Value
End If
Next
FindClosestVals = res
End Function
 
Lần chỉnh sửa cuối:
Upvote 0
Thử thực hiện tương đối đầy đủ các bước test nên trong bài #32, viết lại như sau (vẫn chưa chắc là hết mọi trường hợp)
- Giá trị dò là variant
- tùy chọn tìm là boolean: false hoặc 0 là tìm lớn hơn gần nhất, true hoặc số khác 0 là tìm nhỏ hơn gần nhất
- kết quả hàm là variant
- tùy chọn tìm mặc định false và có thể bỏ trống
- giá trị lỗi, giá trị text, ô trống trong vùng dò tìm bị bỏ qua.

PHP: 
Function ClosestNum(ByVal SearchData As Range, Num, Optional ByVal CloseType As Boolean)
    Dim rng As Range, tmp, Cll As Range
    '' Error of Num
    If Not IsNumeric(Num) Then ClosestNum = "Not number": Exit Function
    ''----------
    If CloseType = 0 Then ''Close type = false
        For Each Cll In SearchData
            If Not IsError(Cll.Value) And Not IsEmpty(Cll) And IsNumeric(Cll) Then  '' Ignore data error and blank cell
                If Cll.Value >= Num Then
                    If IsEmpty(tmp) Then '' tmp is null/ empty
                         tmp = Cll.Value
                    ElseIf Not IsEmpty(tmp) And Cll.Value <= tmp Then
                          tmp = Cll.Value
                    End If
                End If
            End If
        Debug.Print Cll.Address, Cll.Value, tmp
        Next
    Else '' close type =true
        For Each Cll In SearchData
            If Not IsError(Cll.Value) And Not IsEmpty(Cll) And IsNumeric(Cll) Then '' Ignore data error and blank cell
                If Cll.Value <= Num Then
                    If IsEmpty(tmp) Then '' tmp is null/ empty
                         tmp = Cll.Value
                    ElseIf Not IsEmpty(tmp) And Cll.Value >= tmp Then
                          tmp = Cll.Value
                    End If
                End If
            End If
        Next
    End If
ClosestNum = IIf(Not IsEmpty(tmp), tmp, "NA")
End Function

Bảng test:

1632988111296.png
 
Lần chỉnh sửa cuối:
Upvote 0
Test đầy đủ hơn nữa:
Giá trị dò tìm:
- Giá trị dò tìm rỗng
- giá trị tìm là text
- Giá trị tìm là giá trị lỗi
- giá trị dò tìm tham chiếu 2 ô
- giá trị dò tìm tham chiếu ô rỗng
- Giá trị tìm là ngày tháng
Vùng dò tìm:
- vùng dò tìm toàn lỗi
- vùng dò tìm chỉ 1 ô
- vùng dò tìm chỉ 1 ô và là ô lỗi
- vùng dò tìm 1 ô rỗng
- Vùng dò tìm là ngày tháng

1633044946992.png


1633044745813.png

Code:
Mã: 
Một bài đố nhỏ cho ai rảnh và quan tâm
:) :D Chỉ sợ ngày mai ai cũng đua ra đường do nới lỏng giãn cách HCM
 
Lần chỉnh sửa cuối:
Upvote 0
Thuật toán so sánh:

Nếu tôi viết hàm FindClosestVals như bài #42 thì tôi dùng thuật toán so sánh như sau:
Lý thuyết hội tụ nhị phân: nếu một số nằm giữa hai số x1 và x2 gần y nhất thì số ấy có thể thay thế x1 hoặc x2

Function FindClosestVals(ByVal rng As Range, ByVal num As Double)
' hàm duyệt các số trong range rng và trả về một mảng hai phần tử
' phần tử thứ nhất (0) là số gần nhất với num và nhỏ hơn num (max của các số nhỏ hơn num)
' phần tử thứ hai (1) là số gần nhất với num và lớn hơn num (min của các số lớn hơn num)
Const SORATTO = 9E100#
Dim cell As Range, x0 As Double, x1 As Double
x0 = -SORATTO
x1 = SORATTO
For Each cell In rng
If cell.Value > x0 And cell.Value < x1 Then
If cell.Value < num Then
x0 = cell.Value
ElseIf cell.Value > num Then
x1 = cell.Value
End If
End If
Next
FindClosestVals = Array( IIF(x0 > -SORATTO, x0, "No Results"), IIF(x1 < SORATTO, x1, "No Results") )
End Function
 
Upvote 0
ptm0412 đã viết:
Câu đố ế. Chắc tại dễ quá nên bị chê, kể cả @phihndhsp cũng không thèm làm.
Nhấp chuột vào đây để mở rộng...
dạ do dạo này em bị dí quá nên không tham gia được hihi, với những dạng tìm kiếm số gần nhất thì em cũng dùng tìm kiếm nhị phân để làm, dữ liệu sort trước, nếu mảng 2 chiều hay nhiều chiều thì cũng chuyển về mảng 1 chiều và sắp xếp, sau đó dùng tìm kiếm nhị phân đề xét thằng nào gần nhất theo chặn trên hay chặn dưới. còn những dạng bài toán nhiều dữ liệu thì em chưa có làm, dạ chắc rảnh rảnh em sẽ làm và học hỏi thầy ạ hihi.
 
Upvote 0
concogia đã viết:
Cho bài đàng hoàng mới làm chứ, có hình không sao mà làm, mà bài này đi xa quá với bài 1 của chú Nghĩa đẹp "chai" rồi Thầy
Hihi
Nhấp chuột vào đây để mở rộng...
Y chang á anh. Chỉ là test lại từng lỗi ra kết quả của từng lần test, chứ code bài 1 lỗi.
Ngoài ra số và ngày có bà con nên áp dụng tìm ngày gần nhất (trái/ phải) luôn. Làm xong có khi tự gọi là siêu hàm với người ta.
 
Upvote 0
Bạn phải đăng nhập hoặc đăng ký để trả lời bài viết tại đây.

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

L
Nhờ anh chị giúp đỡ công thức hàm len (3 người xem)
Trả lời
4
Đọc
19
LOGANWover
L
N
Chuyển dấu chấm thành dấu chấm phẩy trong dữ liệu
Trả lời
2
Đọc
26
ngouyen1505
N
T
Khóa cột cập nhật dữ liệu
Trả lời
1
Đọc
89
HUONGHCKT
H
ongke0711
  • Question Question
Tải hóa đơn điện tử (https://hoadondientu.gdt.gov.vn/) Excel Vba (2 người xem)
    • Thích
    • Yêu thích
    • Cảm ơn
3 4 5
Trả lời
412
Đọc
50K
beckentan
B
H
  • Question Question
Xin giúp đỡ về tạo hàm excel với C# và excel-dna
Trả lời
4
Đọc
114
hdcv.excel
H
befaint
Chia sẻ tâm tư, xả nỗi lòng
    • Thích
6 7 8
Trả lời
761
Đọc
63K
befaint
befaint
B
Bỏ lọc và sort trong VBA
Trả lời
1
Đọc
59
Bảo_Anh_
B
K
Điền giá trị ô thay vì địa chỉ ô
Trả lời
5
Đọc
211
DeTong
D
T
Xin hỏi về bảng lương
Trả lời
1
Đọc
123
SA_DQ
SA_DQ
T
Nhờ anh chị xem giúp file bị lỗi gì mà chạy rất chậm/lag - File tính tiền nhà trọ
Trả lời
3
Đọc
110
Thaik05
T
J
chiết khấu ở hóa đơn bán lẻ, lưu vào sheet tổng hợp
Trả lời
2
Đọc
85
le_vis
L
2
  • Question Question
Hỏi cách in phiếu điều tra PCGD-XMC từ file dữ liệu mẫu xuất ra từ hệ thống PCGD-XMC của Bộ GD
    • Thích
Trả lời
8
Đọc
722
Hoàng Tuấn 868
H
B
Sau 1 tháng Mã Lại reset về 1 trở lên (1 người xem)
    • Chấp nhận
Trả lời
23
Đọc
377
SA_DQ
SA_DQ
H
Đánh số thứ thự phiếu thu chi
Trả lời
16
Đọc
329
HangktGTN
H
4
HỌC ĐẠI LÝ THUẾ
Trả lời
1
Đọc
59
Hoàng Tuấn 868
H
B
Chuyển số tiền thành chữ
Trả lời
3
Đọc
92
bongngongo
B
S
Tạo sheet mới từ dữ liệu 2 sheet khác ĐVT
Trả lời
1
Đọc
116
Hoàng Tuấn 868
H
N
  • Question Question
Cho em hỏi về mail merge bằng vba (1 người xem)
Trả lời
5
Đọc
181
Hoàng Tuấn 868
H
H
Tìm kiếm thông tin chi tiết trên sheet tổng hợp
Trả lời
1
Đọc
110
LOGANWover
L
Gà Công Nghệ
Tô màu họ và tên bị trùng từ 2 sheet khác nhau
Trả lời
6
Đọc
179
Gà Công Nghệ
Gà Công Nghệ
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

Thời gian đếm ngược.

000
Ngày
00
Giờ
00
phút
00
giây
Thân mời tham dự sự kiện sinh nhật GPE 2025
Back
Top Bottom