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

Hoàng Trọng Nghĩa · 27/9/21

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.

VetMini · 28/9/21

ptm0412 đã viết:
Ít nhất thì tôi không trả về 0. Không "SAI" (sic)

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.

Hau151978 · 28/9/21

excel_lv1.5 đã viết:
Trường hợp so sánh arr(0)>1 không bị lỗi bạn ạ

Vậy là khả năng doc của MS cũng bị lỗi, như vậy là so sánh variant với số hoặc string hoặc variant thì được còn so sánh string với số thì lỗi.

Cá ngừ F1 · 28/9/21

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).

concogia · 28/9/21

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).

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

Cá ngừ F1 · 28/9/21

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

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

ptm0412 · 28/9/21

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.

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.

ptm0412 · 28/9/21

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.

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.

VetMini · 28/9/21

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ả.

ptm0412 · 28/9/21

VetMini đã viết:
Một thuật toán khác:

Trường hợp bảng dò là dữ liệu 1 dòng hoặc 1 cột mới sort và match được. Bảng nhiều dòng nhiều cột thì không, trừ khi phải dùng mảng chuyển 2 chiều thành 1 chiều rồi sort mảng

Cá ngừ F1 · 28/9/21

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ả.

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?

VetMini · 28/9/21

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?

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.

ptm0412 · 29/9/21

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.

Hoàng Trọng Nghĩa · 29/9/21

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

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.

befaint · 29/9/21

Hoàng Trọng Nghĩa đã viết:
For Each rng In rngData

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

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ì?

Hoàng Trọng Nghĩa · 29/9/21

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ì?

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.

befaint · 29/9/21

Hoàng Trọng Nghĩa đã viết:
Do chủ đích là thực hiện trên Range nên đưa tham số rngData vào để dễ xử lý.

Em có hỏi về truyền tham số đâu anh.

Hoàng Trọng Nghĩa · 29/9/21

befaint đã viết:
Em có hỏi về truyền tham số đâu anh.

Giả sử anh ghi công thức =FindClosestNumber(A1,B1) em sẽ truyền thế nào?

befaint · 29/9/21

Hoàng Trọng Nghĩa đã viết:
Giả sử anh ghi công thức =FindClosestNumber(A1,B1) em sẽ truyền thế nào?

Anh truyền rồi đó thôi. @@

ptm0412 · 29/9/21

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.

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)

Hoàng Trọng Nghĩa · 29/9/21

befaint đã viết:
Anh truyền rồi đó thôi. @@

Ý là sau khi nhận tham số là range A1 sau đó "chép Range vào Array để xử lý" thế em chép làm sao vào array đó mà!

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

Chuyên gia GPE

Ăn cùng góc phố

Thành viên tích cực

( ͡° ͜ʖ ͡°)

File đính kèm

Gội rồi mới Cạo

( ͡° ͜ʖ ͡°)

Bad Excel Member

Bad Excel Member

Ăn cùng góc phố

Bad Excel Member

( ͡° ͜ʖ ͡°)

Ăn cùng góc phố

Bad Excel Member

Chuyên gia GPE

|||||||||||||

Chuyên gia GPE

|||||||||||||

Chuyên gia GPE

|||||||||||||

Bad Excel Member

Chuyên gia GPE

Facebook

Group