Hàm nội suy 2 chiều nâng cao

[bangkd]

Chào ACE gia đình GPE,

Ở thread này, em xin bàn về hàm nội suy nâng cao nội suy 2 chiều một bảng tra cho sẵn.

Đặt vấn đề: Cho một bảng tra, cần tra một giá trị (teta hoặc beta) trong bảng tra, biết giá trị cần tra phụ thuộc vào 2 hệ số (ép si lon và V/f'c) đã cho trước.

Mục tiêu: lập một hàm excel có thể tra được giá trị trên. Đối với các giá trị ép si lon hay V/f'c nằm ngoài bảng thì báo lỗi, ko tra nữa. Hàm lập cho Excel 2010.

Ví dụ: Cho trước V/f'c = 0.086, ép si lon = 1.61 thì ta tra được teta = 39.48. Beta cũng làm tương tự


Xin ACE chỉ giáo

File excel bảng tra dưới đây

 

[SA_DQ]

Bạn xem trong file đính kèm

& mình chỉ có thể làm trên E2003 để bạn tham khảo mà thôi!

Thật tiếc vì không thỏa hết iêu cầu của bạn!

 

[bangkd]

Cảm ơn SA_DQ

Đoạn code này chỉ dùng cho sheet Data anh viết, còn các sheet khác không khả dụng.

Vậy mời anh phát triển tiếp để áp dụng cho cả các sheet khác trong file excel.

Thanks

 

[SA_DQ]

Bạn thay giúp các vấn đề sau:

(1) Nội dung hàm giờ sẽ là:

Option Explicit
Function NS2_GPE(xX As Double, yY As Double, Optional Vung2 As Boolean = True)   '*'
 'Hàm Noi Suy 2 Chieu Cai Tién Vói Fuong Thúc FIND():'
 Dim Col As Byte, Rws As Long
 Dim x1 As Double, x2 As Double, y1 As Double, y2 As Double
 Dim a11 As Double, a12 As Double, a21 As Double, a22 As Double
 Dim t1 As Double, t2 As Double
 Dim RgNg As Range, RgDoc As Range, ClsX As Range, ClsY As Range
1 Dim Sh As Worksheet, VungTra As Range
 
2 Set Sh = ThisWorkbook.Worksheets("Data")
3 Set VungTra = Sh.Range("CSDL")
 Col = VungTra.Columns.Count:                   Rws = VungTra.Rows.Count
 Set RgNg = VungTra.Cells(1, 1).Resize(, Col - 1)
 y1 = Application.WorksheetFunction.Lookup(yY, RgNg)
 Set ClsY = RgNg.Find(y1, , xlFormulas, xlWhole)
 y2 = ClsY.Offset(, 1).Value:                   Col = ClsY.Column
 If Vung2 Then
   Set RgDoc = VungTra.Cells(1).Offset(1 + Rws \ 2).Resize(Rws \ 2)
 Else
   Set RgDoc = VungTra.Cells(1, 1).Resize(Rws \ 2)
 End If
 x1 = Application.WorksheetFunction.Lookup(xX, RgDoc)
 Set ClsX = RgDoc.Find(x1):                     Rws = ClsX.Row
 x2 = ClsX.Offset(1).Value
 
4 a11 = Sh.Cells(Rws, Col):                     a22 = Sh.Cells(Rws + 1, Col + 1)
5 a12 = Sh.Cells(Rws, Col + 1):                 a21 = Sh.Cells(Rws + 1, Col)
 t1 = (a12 - a11) * (yY - y1) / (y2 - y1) + a11
 t2 = (a22 - a21) * (yY - y1) / (y2 - y1) + a21
 NS2_GPE = (t2 - t1) * (xX - x1) / (x2 - x1) + t1
 Exit Function
End Function

(2) Bạn gán cho vùng tra từ [B5:M28] có tên là "CSDL"

Chú í: không nên trộn các ô B5 với các ô xung quanh nó; Muốn làm đẹp trang tính có thể dùng cách khác.

Macro/Function không ưa ô trộn bao giờ!

(3) Cú pháp hàm khi xài: Ta bỏ đi tham số đầu ở các hàm, như chỉ còn =NS2_GPE(0.079,1.65)

Chúc thành công!

 

[bangkd]

Revised

(1) Nội dung hàm giờ sẽ là:

Option Explicit
Function NS2_GPE(xX As Double, yY As Double, Optional Vung2 As Boolean = True)   '*'
 'Hàm Noi Suy 2 Chieu Cai Tién Vói Fuong Thúc FIND():'
 Dim Col As Byte, Rws As Long
 Dim x1 As Double, x2 As Double, y1 As Double, y2 As Double
 Dim a11 As Double, a12 As Double, a21 As Double, a22 As Double
 Dim t1 As Double, t2 As Double
 Dim RgNg As Range, RgDoc As Range, ClsX As Range, ClsY As Range
1 Dim Sh As Worksheet, VungTra As Range
 
2 Set Sh = ThisWorkbook.Worksheets("Data")
3 Set VungTra = Sh.Range("CSDL")
 Col = VungTra.Columns.Count:                   Rws = VungTra.Rows.Count
 Set RgNg = VungTra.Cells(1, 1).Resize(, Col - 1)
 y1 = Application.WorksheetFunction.Lookup(yY, RgNg)
 Set ClsY = RgNg.Find(y1, , xlFormulas, xlWhole)
 y2 = ClsY.Offset(, 1).Value:                   Col = ClsY.Column
 If Vung2 Then
   Set RgDoc = VungTra.Cells(1).Offset(1 + Rws \ 2).Resize(Rws \ 2)
 Else
   Set RgDoc = VungTra.Cells(1, 1).Resize(Rws \ 2)
 End If
 x1 = Application.WorksheetFunction.Lookup(xX, RgDoc)
 Set ClsX = RgDoc.Find(x1):                     Rws = ClsX.Row
 x2 = ClsX.Offset(1).Value
 
4 a11 = Sh.Cells(Rws, Col):                     a22 = Sh.Cells(Rws + 1, Col + 1)
5 a12 = Sh.Cells(Rws, Col + 1):                 a21 = Sh.Cells(Rws + 1, Col)
 t1 = (a12 - a11) * (yY - y1) / (y2 - y1) + a11
 t2 = (a22 - a21) * (yY - y1) / (y2 - y1) + a21
 NS2_GPE = (t2 - t1) * (xX - x1) / (x2 - x1) + t1
 Exit Function
End Function

(2) Bạn gán cho vùng tra từ [B5:M28] có tên là "CSDL"

Chú í: không nên trộn các ô B5 với các ô xung quanh nó; Muốn làm đẹp trang tính có thể dùng cách khác.

Macro/Function không ưa ô trộn bao giờ!

(3) Cú pháp hàm khi xài: Ta bỏ đi tham số đầu ở các hàm, như chỉ còn =NS2_GPE(0.079,1.65)

Chúc thành công!

Cảm ơn anh đã tham gia trả lời!

Anh xem lại đoạn code nhé, vì khi chạy có báo lỗi Syntax error

 

[bangkd]

Mở rộng vấn đề:
Trong trường hợp tổng quát, cần viết một hàm có thể thực hiện nội suy 2 chiều một giá trị trong một bảng bất kỳ. Bảng này, để đơn giản, có thể giới hạn kích thước rộng và dài < 50 cell.

Trường hợp đoạn code của SA_DQ là chỉ trả lời cho vấn đề được hỏi ở đầu Thread.

Tìm hiểu trên internet thì vấn đề viết hàm nội suy tuyến tính hay phi tuyến đều đã có. Nhưng vẫn chưa cái nào triệt để hoàn toàn.

Vậy mời mọi người tiếp tục cho ý kiến về vấn đề được mở rộng này. Mục tiêu là tạo ra một hàm ưu việt nhất

 

[SA_DQ]

/-)ành fải nói có sách mách có chứng vậy

Anh xem lại đoạn code nhé, vì khi chạy có báo lỗi Syntax error

Bạn chạy thử trên file mình gởi xem có lỗi đó nữa không nha.

(/ậy mời mọi người tiếp tục cho ý kiến về vấn đề được mở rộng này. Mục tiêu là tạo ra một hàm ưu việt nhất

Cũng chưa rõ hết í bạn;
Hay bạn thử thêm 1 tham biến cho hàm kiểu tùy chọn, để hàm được cung cấp ngay từ đầu vùng bảng số liệu mà nó cần dùng để tra xem sao;

Ý mình rõ hơn sẽ là

Function NS2_GPE(xX As Double, yY As Double, Optional Vung2 As Boolean = True, Optional VungTra As Range)  
 '. . . . . . . . . '
 End Fuction

 

[bangkd]

Continue...

Chào chú, cảm ơn chú đã trả lời. File đính kèm ok rồi chú ạ.

Cũng chưa rõ hết í bạn;

Ý cháu là cần có một hàm tự tạo để có thể nội suy hai chiều tuyến tính trong một bảng bất kì. Tất nhiên đó là bảng với form bình thường, như cái bảng cháu đã gửi. Khi đó, bảng tra cháu đã nêu chỉ là một trường hợp. Theo cháu thì có thêm phần lựa chọn vùng trong cú pháp của hàm, để người dùng tự chọn.

Có hai trường hợp xảy ra trong thực tế cháu gặp về điều kiện nội suy:

TH1. Các giá trị tra chỉ nằm trong giới hạn bảng tra, ngoài vùng biên thì báo lỗi. Ví dụ, giới hạn hàng là 2.5 (giá trị biên), các giá trị tra ứng với hàng mà > 2.5 thì báo là lỗi, không nội suy nữa.

TH2. Các giá trị cần tra có thể nằm ngoài vùng biên. Đối với các giá trị này, lấy luôn giá trị biên làm giá trị để nội suy.

Tổng hợp hai trường hợp trên, hàm có thể có dạng

NS_2D_GPE(Vùng_tra, giá_trị_hàng_cần_tra, giá_trị_cột_cần_tra, THi)

trong đó, i = 1 or 2 để người dùng lựa chọn kiểu điều kiện nội suy (như đã viết ở trên)
Nếu việc gộp trường hợp này gây khó khăn, có thể tách ra làm hai hàm, tương ứng với riêng từng trường hợp.

Không biết cháu viết vậy đã rõ í chưa ?!

Với kinh nghiệm của chú, rất mong chú đưa ra bài toán.

Chúc chú sức khoẻ.

 

[ChanhTQ@]

Bạn thử kiểm xem hàm đã viết có đáp ứng cho trường hợp thứ 2 bạn nêu chưa?

Còn trường hợp I thì cũng không gì khó lắm. Đường hướng là Tìm xem tham biến của hàng hay của cột đưa vào có nhỏ hơn hay lớn hơn các trị biên hay không.
Chuyện này ta cần thêm bước dùng hàm MIN() & MAX() của excel kiểm các cực trị của hàng hay của cột.

Nhưng mình vẫn hỏi thêm, rằng vùng tra có còn 2 vùng gộp lại hay chỉ là 1 vùng biến thiên tăng hay giảm dần đều?

Mong sẽ tiếp tục cùng bạn hoàn thành ý tưởng về nội suy.

 

[bangkd]

Nhưng mình vẫn hỏi thêm, rằng vùng tra có còn 2 vùng gộp lại hay chỉ là 1 vùng biến thiên tăng hay giảm dần đều?

Trong bài toán ban đầu lúc mở thread này, cháu chỉ muốn nội suy 2 tham số teta và beta. Đúng ra là tách riêng hai bảng này ra, không cần gộp vào một bảng. Nhưng các chú trả lời lun cho trường hợp gộp lại. Không sao, vẫn đáp ứng được yêu cầu nội suy.

Trong phần mở rộng vấn đề, tùy mức độ xử lý, có thể làm từng trường hợp (tăng hoặc giảm). Ứng với mỗi hàm được viết ra, nếu có các yêu cầu của hàm nội suy thì cần viết chú ý phạm vi áp dụng. Cố gắng tổng quát nhất có thể.

Vậy khi ACE GPE viết hàm, xin nêu ra luôn tên hàm, cấu trúc hàm và phạm vi áp dụng (các yêu cầu khi sử dụng hàm).

 

[ptm0412]

Nội suy 2 chiều không khó
Viết hàm nội suy thông thường (suy luận đơn giản) cũng đáp ứng được:
- Vùng tra bất kỳ
- Tham chiếu vùng tra ở sheet khác
- Bảng tra có số dòng cột bất kỳ, không hạn chế
- Nếu giá trị cần tra nằm ngoài giá trị có thể tra, hàm trả về #VALUE

Tra bảng nào thì vùng tra là bảng đó, gộp 2 bảng làm chi cho rắc rối? rồi giả sử 2 bảng không cách nhau bằng đó dòng mà nhiều hơn? Rồi 2 bảng lệch cột? rồi 2 bảng nằm 2 sheet?

Thí dụ:

Function Noisuy2D(XValue, YValue, Rng As Range) As Double
Dim XArr, YArr, SArr
Dim X1, X2, Y1, Y2
Dim A11, A21, A12, A22
Dim XValue1, XValue2, YValue1, YValue2
Dim Tmp1, Tmp2
    SArr = Rng.Cells(2, 2).Resize(Rng.Rows.Count - 1, Rng.Columns.Count - 1).Value
    XArr = Rng.Cells(1, 2).Resize(1, Rng.Columns.Count - 1).Value
    YArr = Rng.Cells(2, 1).Resize(Rng.Rows.Count - 1, 1).Value
        X1 = Application.Match(XValue, XArr, 1)
        Y1 = Application.Match(YValue, YArr, 1)
        X2 = X1 + IIf(X1 = Rng.Columns.Count - 1, 0, 1)
        Y2 = Y1 + IIf(Y1 = Rng.Rows.Count - 1, 0, 1)
    XValue1 = XArr(1, X1): XValue2 = XArr(1, X2)
    YValue1 = YArr(Y1, 1): YValue2 = YArr(Y2, 1)
A11 = SArr(Y1, X1): A21 = SArr(Y2, X1)
A12 = SArr(Y1, X2): A22 = SArr(Y2, X2)
    If (YValue2 - YValue1) = 0 Then
        Tmp1 = A11
        Tmp2 = A12
    Else
        Tmp1 = A11 + (YValue - YValue1) * (A21 - A11) / (YValue2 - YValue1)
        Tmp2 = A12 + (YValue - YValue1) * (A22 - A12) / (YValue2 - YValue1)
    End If
    If XValue2 - XValue1 = 0 Then
        Noisuy2D = Tmp1
    Else
        Noisuy2D = Tmp1 + (XValue - XValue1) * (Tmp2 - Tmp1) / (XValue2 - XValue1)
    End If
End Function

Cú pháp hàm: =Noisuy2D(giá trị X, giá trị Y, vùng tra)

Ghi chú: X là theo chiều ngang, tức là tra theo dòng ngang trên cùng, Y là theo chiều đứng, nghĩa là tra theo cột đứng bên trái.
Nếu muốn giá trị cần tra nằm ngoài giá trị có thể tra mà vẫn tính bằng giá trị nhỏ nhất hoặc lớn nhất, thì sửa lại, thêm tham số, thành 1 hàm khác. Vì tính chất của các phép tính nhân chia (không thể chia cho 0), nên phải tính lại hoàn toàn, 2 hàm gộp 1 dài bằng hoặc hơn 2 hàm để riêng, nên cần cân nhắc có nên gộp hay không.

Ghi chú: Tải lại file đã sửa lỗi dưới đây

 

[ptm0412]

Cải tiến hàm nội suy 2 chiều:

- Thêm tham số NoCalc: Nếu giá trị cần tra nằm ngoài vùng cần tra:

• Cứ tính bằng cách lấy giá trị biên (Min hoặc Max): NoCalc = False hoặc NoCalc = 0
• Không tính và trả kết quả lỗi: NoCalc True hoặc NoCalc = 1 hoặc để trống

- Sửa lỗi hàm bài trên khi Giá trị cần tra vừa bằng giá trị biên
- Bắt buộc vùng tra phải chuẩn:

• Dòng 1 và cột 1 (các giá trị để tra) bắt buộc phải sắp xếp tăng dần.
• Các giá trị bên trong biến thiên tuỳ ý theo thực tế
• Dòng 1 và cột 1 (các giá trị để tra) nếu sắp theo thứ tự giảm dần, hoặc không sắp thứ tự, tính sai ráng chịu. Ai biểu không lập bảng tra chuẩn.

Function Noisuy2D(XValue, YValue, Rng As Range, Optional NoCalc = True) As Double
Dim XArr, YArr, SArr 'Dong tra, cot tra va bang tra'
Dim X1, X2, Y1, Y2 'vi tri can duoi va vi tri can tren cua XValue va YValue'
Dim XValue1, XValue2, YValue1, YValue2 'can duoi va can tren cua XValue va YValue'
Dim A11, A21, A12, A22 'Gia tri tra duoc cho cac can tren va can duoi'
Dim Tmp1, Tmp2 'Cac tinh toan trung gian'
    SArr = Rng.Cells(2, 2).Resize(Rng.Rows.Count - 1, Rng.Columns.Count - 1).Value
    XArr = Rng.Cells(1, 2).Resize(1, Rng.Columns.Count - 1).Value
    YArr = Rng.Cells(2, 1).Resize(Rng.Rows.Count - 1, 1).Value
    If NoCalc Then
    'Khong tinh neu gia tri tra ngoai vung co the tra'
        X1 = Application.Match(XValue, XArr, 1)
        Y1 = Application.Match(YValue, YArr, 1)
        X2 = X1 + IIf(X1 = Rng.Columns.Count - 1, 0, 1)
        Y2 = Y1 + IIf(Y1 = Rng.Rows.Count - 1, 0, 1)
    Else
    'Co tinh neu gia tri tra ngoai vung co the tra'
        If XValue <= XArr(1, 1) Then
            X1 = 1: X2 = X1
        ElseIf XValue >= XArr(1, Rng.Columns.Count - 1) Then X1 = Rng.Columns.Count - 1: X2 = X1
        Else
            X1 = Application.Match(XValue, XArr, 1): X2 = X1 + 1
        End If
        If YValue <= YArr(1, 1) Then
            Y1 = 1: Y2 = Y1
        ElseIf YValue >= YArr(Rng.Rows.Count - 1, 1) Then Y1 = Rng.Rows.Count - 1: Y2 = Y1
        Else
            Y1 = Application.Match(YValue, YArr, 1): Y2 = Y1 + 1
        End If
    End If
    XValue1 = XArr(1, X1): XValue2 = XArr(1, X2)
    YValue1 = YArr(Y1, 1): YValue2 = YArr(Y2, 1)
A11 = SArr(Y1, X1): A21 = SArr(Y2, X1)
A12 = SArr(Y1, X2): A22 = SArr(Y2, X2)
    If YValue2 - YValue1 = 0 Then
        Tmp1 = A11
        Tmp2 = A12
    Else
        Tmp1 = A11 + (YValue - YValue1) * (A21 - A11) / (YValue2 - YValue1)
        Tmp2 = A12 + (YValue - YValue1) * (A22 - A12) / (YValue2 - YValue1)
    End If
    If XValue2 - XValue1 = 0 Then
        Noisuy2D = Tmp1: Exit Function
    Else
        Noisuy2D = Tmp1 + (XValue - XValue1) * (Tmp2 - Tmp1) / (XValue2 - XValue1)
    End If
End Function

 

[bangkd]

Acknowledgement

Cám ơn chú Mỹ đã tham gia thảo luận.

Phần trả lời của chú đã đúng hết với mong muốn của cháu rồi !

 

[kicon]

Cám ơn nhiều em đang cần cái này lắm