Lớp học GPE tháng 10 - TPHCM: Conditional Formatting và Data Validation (tối 4, 6/10) | Excel cơ bản (tối 9, 11, 13/10) |
Thuần thục các hàm dò tìm (tối 10, 12/10) | Tất tần tật về PivotTable (tối 16, 18, 20/10) |
Tất tần tật về Filter và Advanced Filter (tối 23, 25/10) | Name động và biểu đồ (tối 24, 26, 28/10)

Đăng ký học Khởi đầu cùng Google Spreadsheet - 2 chủ nhật 1 và 8/10 - TPHCM

Đăng ký học Xây dựng ứng dụng Form bằng VBA - 2 chủ nhật 15 và 22/10 - TPHCM

Hàm nội suy 1 chiều và 2 chiều

Thảo luận trong 'Excel và Kỹ Thuật' bắt đầu bởi connhangheo, 16 Tháng mười một 2007.

  1. connhangheo

    connhangheo Thành viên thường trực

    em mới học VBA, mọi người xem file em viết 2 hàm này rồi cho em ý kiến để em hoàn thiện hơn, Thanks
     

    Các file đính kèm:

    • noisuy.xls
      Kích thước:
      28.5 KB
      Đọc:
      6,111
  2. keeponlylove

    keeponlylove Thành viên mới

    Em không hiểu cách dùng hàm ntn?trong hàm noiuy1(D6:E13,3.5,3) cái 3.5 và 3 có nghĩa là gì?bác có thể giải thích ko?thank.
     
  3. connhangheo

    connhangheo Thành viên thường trực

    em xin giải thích hàm của em như sau, mọi người cho ý kiến về code giúp em
     

    Các file đính kèm:

  4. tvduc

    tvduc Thành viên mới

    Cảm ơn mọi người nhiều!minh không biết về VBA nhưng moi ngừoi có thể hướng dẫn cách lập biểu thứ nọi suy đo hay khong, và một bảng tính nào đó tôi muốn sử dụng nó thì làm thế nào, chẵng nhẽ mơ file nay lên rồi sửa trên đó hay sao?
    cam on rất nhiều.
     
  5. connhangheo

    connhangheo Thành viên thường trực

    cái này bạn phải biết 1 ít về VBA
    Nếu bây h bạn có 1 bảng giá trị và cần tìm giá trị nội suy trong bảng đó thì bạn sẽ làm thực hiện như sau :
    bạn phải biết công thức nó như thế nào đúng không :
    - Với hàm nội suy 1 chiều thì công thức như sau :
    =noisuy1(bảng giá trị, giá trị cần nội suy, thứ tự của cột cần lấy giá trị nội suy)
    Tham số thứ 3 (thứ tự của cột cần lấy giá trị nội suy) cần dùng trong trường hợp bảng giá trị nội suy của bạn có nhiều cột. Ví dụ khi bạn khi bạn nội suy sức chịu tải tiêu chuẩn của đất dính (trong sách Cơ Học Đất) thì giá trị cần nội suy của bạn sẽ là hệ số rỗng e, nhưng vì có nhiều giá trị độ sệt IL khác nhau (mỗi giá trị là 1 cột) nên bạn cần phải biết thứ tự của cột là bạn cần lấy giá trị nội suy.
    -Với hàm nội suy 2 chiều thì công thức là thế này:
    =noisuy2(bảng giá trị,giá trị cần nộ suy thứ nhất, giá trị cần nội suy thứ 2)

    hàm của mình nó chưa hoàn chỉnh, trong thời gian này mình đang phải làm nhiều bài tập TKMH và chuẩn bị ôn thi nên khá bận, khi nào có thời gian nhiều hơn mình sẽ học hỏi để hoàn thiện nó tốt hơn. Ví dụ như phần bắt lỗi mình cũng chưa làm,và còn 1 số cái khác nữa. Nhưng nếu dùng tạm thì cũng vẫn ổn. Mong mọi người góp ý và cũng hoàn thiện hàm này cho anh em trong forum học hỏi. Thanks
     
    Chỉnh sửa lần cuối bởi điều hành viên: 12 Tháng một 2013
  6. phhxd

    phhxd Thành viên mới

    Mình đã dùng phần code của bạn, rất cám ơn :D Làm bài tập lớn cơ đất nhàn đi rất nhều!
    Tuy nhiên trong quá trình làm mình thấy là khi giá trị của hàng ngang và dọc, chẳng may bằng và ứng với các giá trị của cột ngang đầu tiên và dọc đầu tiên là hàm sẽ làm việc ko ổn. Ví dụ cột dọc là 0.15 0.30 0.60; cột ngang là 0 0.25 0.5 Giá trị cần tra ở hàng ngang ứng với giá trị 0 là có vấn đề :)
    Mình chưa có 1 tí kiến thức nào về VBA nên lần này may mắn tìm được bài viết của bạn, có điều thắc mắc như trên... Thank bạn :)
     
  7. zigzag

    zigzag Thành viên mới

    NSTT:
    Function NSTT(xnew, xx, yy)
    For i = 1 To xx.Count
    If xx(i) > xnew Then
    Exit For
    End If
    Next i
    NSTT = (xnew - xx(i - 1)) / (xx(i) - xx(i - 1)) * (yy(i) - yy(i - 1)) + yy(i - 1)
    End Function

    đây là hàm nội suy tuyến tính 1 chiều! có bác nào viết cho tôi hàm tìm nghiệm của 1 pt được không! Thanks!
     
    Lần chỉnh sửa cuối: 26 Tháng hai 2008
  8. duongthanh85

    duongthanh85 Thành viên mới

    Dùng hàm nội suy 2 chiều để tìm phương trình một mặt được không nhỉ?
    Cụ thể là từ 1 dãy số liệu z(i)=F(x(i), y(i)
    Để tìm ra dạng hàm của z=f(x,y)

    Mong bạn có thể giúp mình, nếu có thể thì cả phần lý thuyết là tốt nhất. Thanks!
     
  9. PhanTuHuong

    PhanTuHuong Excel & AutoCad & VBA

    Hàm bạn connhangheo viết ổn đấy, cái khó nhất là tra vùng biên. Cứ thế phát huy --=0 !
    Khai báo biến đầy đủ và chính xác (tôi thì hay nhầm và quên !$@!! ).
     
  10. SA_DQ

    SA_DQ Thành viên danh dự

    Với nội suy 1 chiều, trước.

    PHP:
    Function noisuy1(vungtra As RangeAs Doublecot As Integer) As Double
        
    'ham noi suy 1 chieu
        Dim ktra As Boolean
        Dim i As Integer
        Dim x1 As Double, x2 As Double, y1 As Double, y2 As Double
        For i = 1 To vungtra.Cells.Count
         kiemtra = False
         If vungtra.Cells(i, 1) <= X And vungtra.Cells(i + 1, 1) >= X Then
          x1 = vungtra.Cells(i, 1): x2 = vungtra.Cells(i + 1, 1)
          y1 = vungtra.Cells(i, cot): y2 = vungtra.Cells(i + 1, cot)
          noisuy1 = (y2 - y1) * (X - x1) / (x2 - x1) + y1
          ktra = True
         End If
        Next i
        If ktra = False Then
         MsgBox "gia tri can tim ko nam trong bang tra", vbInformation
         Exit Function
        End If
    End Function 
    Bạn còn phí phạm tài nguyên & thời giờ:
    Khi tìm ra hàm rồi, nên thoát ngay vòng lặp/thoát ngay hàm, cũng OK!

    Cái này mình chỉ mới suy đón thôi, cũng mạnh dạn nêu ra đây:
    Nếu giá trị các cột là tăng (Giảm dần) & trong cột cũng tăng dần
    có nghĩa là
    1 8
    2 14
    7 15
    thì bạn nên duyệt hàng đầu trước đề tìm trị trong ô nào > X;
    Sau khi tìm ra, ta quay lại cột trước đó & tìm trong cột đó thôi.
     
    Lần chỉnh sửa cuối: 5 Tháng ba 2008
  11. SA_DQ

    SA_DQ Thành viên danh dự

    Xin giới thiệu 1 cách khác để lập hàm nội suy 2 chiều

    * Nhược điểm lớn nhất của hàm nội suy 2 chiều ( tại #1) là vòng lặp thứ hai lại phải duyệt toàn bộ các cells trong vùng chọn. Nhưng thực chất ta chỉ cần:
    - Ở vòng lặp đầu tạm chấp nhận vì duyệt chỉ trong hàng đầu
    - Vòng sau chỉ cần duyệt cột đầu của vùng dữ liệu; Vì ta đã khai báo 2 biến dạng Range (Rng & Clls), nên lưu trong nó (kèm theo 1 cách đương nhiên) các trị số cột & hàng của chúng.

    PHP:
    Option Explicit
    Function NoiSuyGPE(VungTra As RangexX As DoubleyY As Double) As Double
        
    'Ham Noi Suy 2 Chieu Tai GPE.COM (Sa_DQ)'
        
    Dim iW As IntegerjI As Integer
        Dim x1 
    As Doublex2 As Doubley1 As Doubley2 As Double
        Dim a11 
    As Doublea12 As Doublea21 As Doublea22 As Double
        Dim t1 
    As Doublet2 As Double
        
        Dim RgNg 
    As RangeRgDoc As Range
        Dim Rng 
    As RangeClls As Range
        Set RgNg 
    VungTra.Cells(11).Resize(1VungTra.Columns.Count 1)
        
    Set RgDoc VungTra.Cells(11).Resize(VungTra.Rows.Count1)
        
    1    For Each Rng In RgNg
    2      
    If Rng <= yY And Rng.Offset(, 1) >= yY Then
    3        y1 
    Rng:           y2 Rng.Offset(, 1)
            For 
    Each Clls In RgDoc
    5        
    If Clls <= xX And Clls.Offset(1) >= xX Then
             x1 
    Clls:         x2 Clls.Offset(1)
            
    '3*'
    7       a11 Cells(Clls.RowRng.Column):  a12 Cells(Clls.RowRng.Column 1)
            
    a21 Cells(Clls.Row 1Rng.Column): a22 Cells(Clls.Row 1Rng.Column 1)
    9         t1 = (a12 a11) * (yY y1) / (y2 y1) + a11
             t2 
    = (a22 a21) * (yY y1) / (y2 y1) + a21
    11         NoiSuyGPE 
    = (t2 t1) * (xX x1) / (x2 x1) + t1
             
    Exit For
    13        End If
            
    Next Clls
    15      End 
    If
        
    Next Rng
    17    
    If NoiSuyGPE 0 Then  '!'
            
    msgbox "gia tri can tim ko nam trong bang tra"vbInformation
    19    End 
    If
    End Function 
     
  12. hung1981

    hung1981 Thành viên mới

    Mình đã thử hàm của bạn rồi
    Nó có một nhược điểm là tra 1 chiều cùng tăng hay thì được nhưng khi x tăng mà y giảm thì chịu thua
    Mình đã nghiên cứu sửa lại
    chỗ
    For i = 1 To vungtra.cells.count
    thành
    For i = 1 To vungtra.Rows.Count
    thì chạy ok
    Không biết góp ý của mình có đúng hay không
    Chúng ta cùng thảo luận nhé
     
  13. hung1981

    hung1981 Thành viên mới

    Hàm noisuy2 bạn mắc lỗi giống hàm nội suy 1 chỗ chọn giá trị vòng lặp
    Mình edit lại thấy chạy tốt hơn
    Bạn thử xem
     
  14. SA_DQ

    SA_DQ Thành viên danh dự

    Tự cải tiến tiếp tục:

    Quan sát dòng lệnh 9 & 10 mình thấy còn có thể rút gọn thêm (về cách viết) để đỡ rườm rà. Đó là tính trước 2 biểu thức, như sau
    Tính trước cho y2: tại phần sau của dòng lệnh 3 sẽ là
    y2 = Rng.Offset(, 1) - y1
    Tương tự như vậy, tại phần sau của dòng lệnh 7 sẽ là:
    a12 = Cells(Clls.Row, Rng.Column + 1) - a11
    & tại phần sau của dòng lệnh 8 là:
    a22 = Cells(Clls.Row + 1, Rng.Column + 1) - a12
    Sau đó trông vô 2 biểu thức tại dòng lệnh 9 & 10 dẻ thiện cảm hơn!
    , như sau:
    PHP:
                             t1 = (a12 ) * (yY y1) / (y2 ) + a11
     t2 
    = (a22 ) * (yY y1) / (y2 ) + a21 
    Vấn đề nữa là: Dòng lệnh 17 còn phải viết lại cho chuẩn hơn!. . . .
    Sau cùng: Mình còn dư 2 biến khai báo chưa dùng (Do lịch sử để lại!)

    Mã:
    Option Explicit
    [B]Function NoiSuyGPE(VungTra As Range, xX As Double, yY As Double) As Double[/B]  
     [COLOR="Blue"] 'Ham Noi Suy 2 Chieu Tai GPE.COM (Sa_DQ)'[/COLOR] 
       [COLOR="Silver"]Dim iW As Integer, jI As Integer[/COLOR]
        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
        Dim Rng As Range, Clls As Range
        Set RgNg = VungTra.Cells(1, 1).Resize(1, VungTra.Columns.Count - 1)
        Set RgDoc = VungTra.Cells(1, 1).Resize(VungTra.Rows.Count, 1)
        
    1    For Each Rng In RgNg
    2      If Rng <= yY And Rng.Offset(, 1) >= yY Then
    3        y1 = Rng:         [COLOR="Silver"]  y2 = Rng.Offset(, 1)[/COLOR]  
          For Each Clls In RgDoc
    5        If Clls <= xX And Clls.Offset(1) >= xX Then
             x1 = Clls:         x2 = Clls.Offset(1)
            '3*'
    7       a11 = Cells(Clls.Row, Rng.Column):  [COLOR="silver"]a12 = Cells(Clls.Row, Rng.Column + 1)[/COLOR]       
           a21 = Cells(Clls.Row + 1, Rng.Column): [COLOR="silver"]a22 = Cells(Clls.Row + 1, Rng.Column + 1)[/COLOR]
    9        [COLOR="silver"] t1 = (a12 - a11) * (yY - y1) / (y2 - y1) + a11
             t2 = (a22 - a21) * (yY - y1) / (y2 - y1) + a21[/COLOR]
    11         NoiSuyGPE = (t2 - t1) * (xX - x1) / (x2 - x1) + t1
             Exit For
    13        End If
            Next Clls
    15      End If
        Next Rng
    17    If NoiSuyGPE = 0 Then  '!'
            msgbox "gia tri can tim ko nam trong bang tra", vbInformation
    19    End If
    End Function 
     
    Lần chỉnh sửa cuối: 8 Tháng ba 2008
  15. vietlge

    vietlge Thành viên mới

    Chào bác Connhangheo!
    em down cái file excel tính hàm nội suy 1 chiều và 2 chiều về tính toán!
    kết quả tốt lắm
    nhưng mà bác ơi! Không hiểu sao khi vùng tra nằm tại một số ô trên Work sheet thì không thực hiện được 2 hàm trên. Khi mình cop vùng tra ra chỗ khác vẫn trên work sheet đó thì lại làm được.
    Kể cả khi vùng tra ok rồi thực hiện phép tính ok rồi nhưng nếu mình xóa một ô or một cột nào đó mà ko ảnh hưởng đến vùng tra đó thì hàm tính tự nhiên lại ko tính được nữa.
    mình không hiểu thế là thế nào? bạn có thể giải thích cho mình và hướng dẫn mình cách giải quyết ko?
    mình nghĩ do macro bị virus bởi vì khi bật file excel lên thì thấy có dòng chữ:
    Macros may contain viruses!....
    Mình phải làm sao đây?
    giúp mình nhé!
    thanks and best regards!

    chào bác connhangheo!
    em insert hai cái code của bác vào dùng thử thấy tốt! nhưng mà có một vấn đề là trên work sheet ý, khi vùng tra ở một ô nào đó thì không thực hiện được hai hàm đó bác ạ
    còn khi em cop vùng tra ra chỗ khác vẫn trên sheet đó thì lại thực hiện được
    nó như thế nên rất khó để trình bày
    như thế có phải là do macros của bác bị virus ko?
    hay là bị vấn đề gì? mong bác giải quyết hộ vấn đề này!
    cảm ơn bác nhiều!
     
    Chỉnh sửa lần cuối bởi điều hành viên: 12 Tháng một 2013
  16. Dinhbeo

    Dinhbeo Thành viên mới

    Đúng vậy mình cũng gặp lỗi y như trên-+*/
     
  17. dangvutuan

    dangvutuan Thành viên mới

    Mình là dân kỹ thật nên sử dụng nội suy từ rất lâu (từ năm 1998) nên cũng đã viết ra hàm nội suy để dùng riêng. Cũng phải qua thực tế sử dụng rất nhiều lần mới viết được một hàm ưng ý (vì trong lĩnh vực kỹ thuật rất cần sự chính xác và tin cậy), tiện đây xin giới thiệu để các bạn tham khảo:
    ' One Directions Interpolate Function
    ' Author : Dang Vu Tuan
    '
    Function NS(table_array, Lookup_value)
    Dim NumRows As Integer, i As Integer
    Dim Max, Min
    Dim Range1 As Range, Range2 As Range

    NumRows = table_array.Rows.Count
    Set Range1 = table_array.Columns(1)
    Set Range2 = table_array.Columns(2)
    ' check for case if val = last value in range1
    If Lookup_value = Range1.Cells(NumRows) Then
    NS = Range2.Cells(NumRows)
    Exit Function
    End If
    ' Get Max and Min
    Max = Range1.Cells(1)
    Min = Range1.Cells(1)
    For i = 1 To NumRows
    If Max <= Range1.Cells(i) Then Max = Range1.Cells(i)
    If Min >= Range1.Cells(i) Then Min = Range1.Cells(i)
    Next i
    ' Return an error if lookup_value is not within range1
    If Lookup_value > Max Or Lookup_value < Min Then
    NS = "Out of range" 'Evaluate("NA()")
    Exit Function
    End If
    ' Do linear interpolation
    For i = 1 To NumRows - 1
    If (Lookup_value >= Range1.Cells(i) And Lookup_value <= Range1.Cells(i + 1)) Or (Lookup_value <= Range1.Cells(i) And Lookup_value >= Range1.Cells(i + 1)) Then
    If (Range1.Cells(i) - Range1.Cells(i + 1)) <> 0 Then
    NS = (Range2.Cells(i + 1) + (Range2.Cells(i) - Range2.Cells(i + 1)) * (Lookup_value - Range1.Cells(i + 1)) / (Range1.Cells(i) - Range1.Cells(i + 1)))
    Else
    NS = Range2.Cells(i)
    End If
    Exit Function
    End If
    Next i
    End Function



    Và đây là hàm nội suy hai chiều:

    ' Two Directions Interpolate Function
    ' Author : Dang Vu Tuan
    '
    Function NS2(Data_Range As Range, x, y)
    Dim A(), xMax, yMax, xMin, yMin
    Dim Nx, Ny, i, J, k1, k2, k3, k4, k12, k34
    'Get the data
    Nx = Data_Range.Columns.Count
    Ny = Data_Range.Rows.Count
    ReDim A(Nx, Ny)
    For i = 1 To Nx
    For J = 1 To Ny
    A(i, J) = Data_Range(J, i)
    Next J
    Next i
    'Check data
    xMax = A(2, 1)
    xMin = A(2, 1)
    For i = 2 To Nx
    If xMax < A(i, 1) Then xMax = A(i, 1)
    If xMin > A(i, 1) Then xMin = A(i, 1)
    Next i
    yMax = A(1, 2)
    yMin = A(1, 2)
    For J = 2 To Ny
    If yMax < A(1, J) Then yMax = A(1, J)
    If yMin > A(1, J) Then yMin = A(1, J)
    Next J
    If x < xMin Or x > xMax Or y < yMin Or y > yMax Then
    NS2 = "Out of range"
    Exit Function
    End If
    'Do linear interpolation
    For i = 2 To Nx - 1
    If (A(i, 1) <= x And x <= A(i + 1, 1)) Or (A(i, 1) >= x And x >= A(i + 1, 1)) Then
    For J = 2 To Ny - 1
    If (A(1, J) <= y And y <= A(1, J + 1)) Or (A(1, J) >= y And y >= A(1, J + 1)) Then
    k1 = A(i, J)
    k2 = A(i + 1, J)
    k3 = A(i, J + 1)
    k4 = A(i + 1, J + 1)
    If (A(i + 1, 1) - A(i, 1)) = 0 Then
    k12 = k1
    k34 = k3
    Else
    k12 = k1 + (k2 - k1) * (x - A(i, 1)) / (A(i + 1, 1) - A(i, 1))
    k34 = k3 + (k4 - k3) * (x - A(i, 1)) / (A(i + 1, 1) - A(i, 1))
    End If
    If (A(1, J + 1) - A(1, J)) = 0 Then
    NS2 = k12
    Else
    NS2 = k12 + (k34 - k12) * (y - A(1, J)) / (A(1, J + 1) - A(1, J))
    End If
    Exit Function
    End If
    Next J
    End If
    Next i
    End Function



    Hai hàm trên mình viết để dễ theo dõi nên thực sự còn có thể compact được hơn nữa.
     
  18. duong0h5

    duong0h5 Thành viên mới

    Mình đang cần cái này rất gấp
    mình đã tải file về
    nhưng kô biết cách nào để đưa hàm nội suy vào bảng tính của mình được còn bảng tính của bạn thì ok
    để tiện cho việc tra, mình co thể để nó ở từng SHEET của excel được kô
     
  19. newcomer007

    newcomer007 Thành viên mới

    mình đã down file của ban về nhưng không dùng được, no bị lỗi #Name, chac do minh thieu cài chi đó đúng không?bạn nào help mình với, thanks alot
     
  20. ptlong04x1

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

    Bạn vào Tools\Macro\Security --> chọn Medium, sau này nếu mở file ra Excel có hỏi gì đó thì bạn phải chọn Enable Macro.
     

Chia sẻ trang này