Một số hàm tự tạo liên quan đến kỹ thuậ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)
PhanTuHuong

PhanTuHuong

VBA & VB.NET for Excel & AutoCad
Thành viên danh dự
Tham gia
13/6/06
Bài viết
7,210
Được thích
24,721
Tôi xin giới thiệu một số hàm đơn giản liên quan đến kỹ thuật, một số hàm đó có thể có sẵn hoặc chưa có trong Excel.
1. Hàm tính giá trị trung bình:

Mã: 
Function Trungbinh(Arr) As Single
  Dim Tong As Single
  Dim i As Integer
  i = 0
  Tong = 0
  For Each Cell In Arr
    If Cell.Value > 0 Then
       Tong = Tong + Cell.Value
       i = i + 1
    Else
       i = i
    End If
  Next Cell
    Trungbinh = Tong / i
End Function
2. Hàm nội suy 1 chiều:
Ví dụ có 2 dãy số liệu A và B. Hàm Vlookup chỉ tìm kiếm những giá trị B(i) tương ứng với A(i). Còn những giá trị ở giữa thì ta phải nội suy. Ví dụ đơn giản dưới đây, để hoàn thiện thì bạn bổ sung code trong Elseif nhé:

Mã: 
Function MangB(SoA, Arr1, Arr2) As Single
  Dim A1, A2, B1, B2 As Single

  For Each Cell In Arr1
     A1 = Cell.Offset(-1, 0)
     A2 = Cell.Offset(0, 0)
     B1 = Cell.Offset(-1, 1)
     B2 = Cell.Offset(0, 1)

     If Cell.Value > SoA Then
       MangB = (SoA - A1) * (B2 - B1) / (A2 - A1) + B1
       Exit For
     End If
  Next Cell
End Function


mời các bạn tải file kèm theo
 

File đính kèm

Lần chỉnh sửa cuối:
PhanTuHuong đã viết:
Tôi xin giới thiệu 1. Hàm tính giá trị trung bình:
Function Trungbinh(Arr) As Single
Dim Tong As Single: Dim i As Integer
i = 0: Tong = 0
For Each Cell In Arr
If Cell.Value > 0 Then
Tong = Tong + Cell.Value: i = i + 1
. . . .
End Function
Nhấp chuột vào đây để mở rộng...
If Cell.Value > 0 Then
/(/ếu giá trị ô đó chính = 0 hay < 0 thì hàm này sẽ bỏ qua sao? Xin Bác tác gia xét & giải thích thêm!
2. Hàm nội suy 1 chiều:
Ví dụ có 2 dãy số liệu A và B. Hàm Vlookup chỉ tìm kiếm những giá trị B(i) tương ứng với A(i). Còn những giá trị ở giữa thì ta phải nội suy.
Function MangB(SoA, Arr1, Arr2) As Single
Dim A1, A2, B1, B2 As Single
11 'Đề nghị thêm vô đây!
For Each Cell In Arr1
. . . . .
Next Cell
End Function
Nhấp chuột vào đây để mở rộng...
Để mọi người đều có thể xài hàm này, theo mình cần thêm vô các lệnh tại dòng 11, đại loại như sau
If Application.Type(Application.VLooKup(...,..., ..,..))<>16 then
MangB=Application.VLooKup(. . .,. . ., . . ,..)
else
. . . .
End If
Tac gia xin cho í kiến! Mạo muội vài lời, bỏ qua nếu không phải!
 
Lần chỉnh sửa cuối:
Gửi bác SA_DQ!

Vì các số tính trung bình của bọn em (kỹ thuật) là số thực, nếu nhỏ hơn 0 thì sẽ bị sai.
 
Lần chỉnh sửa cuối:
Hàm trung bình có phải để tìm MEAN không? Nếu thế thì excel có sẵn hàm AVERAGE rồi, bác thử dùng xem sao.

Average(Array)

Tôi không hiểu nội suy tuyến tính để làm gì. Bác PhanTuHuong chịu khó giới thiệu lại về căn bản được không? Thực sự tôi mất căn bản trầm trọng về những hàm này.
 
:* Rất là khó giải thích!
Chỉ dân kỹ thuật hay dùng thôi. Nó liên quan đến tra bảng.
 
Lần chỉnh sửa cuối:
:* Rất là khó giải thích!
Chỉ dân kỹ thuật hay dùng thôi. Nó liên quan đến tra bảng.
Nhấp chuột vào đây để mở rộng...
Đúng là kế toán không chấp nhận gần đúng; chỉ có đúng & đúng mà thôi!
To PTuHuong bạn giới thiệu luôn hàm nội suy hai & 3 chiều luôn đi, để anh chị em tham khảo luôn;
VD cho hỏi: Tôi đã có giản đồ trạng thái của hệ PB-Sn; của Pb - Bi & hệ Sn - Bi ; VĐề nêu ra là cần bao nhiêu điểm thực nghiệm của hệ 3 cấu tử nêu trên thì ta có thể nội suy toàn bộ hệ này; Bạn cho cao kiến về vấn đề này giúp mình với! Xin cảm ơn trước!
 
Chỉnh sửa lần cuối bởi điều hành viên:
3. Hàm nội suy 2 chiều:
Hàm nội suy 2 chiều được bàn luận rất nhiều ở bên ketcau.com và nhiều người đã lập ra. Tôi xin giới thiệu hàm của anh PMXD viết và được nhiều người sử dụng đánh giá cao:

'================================================= =====
Function TraBang2Chieu(ByVal GiaTriCot, ByVal GiaTriHang, VungChon As Range)
Dim i As Long, j As Long
Dim TangAnPha
Dim NoiSuy1 As Double, NoiSuy2 As Double

For i = 1 To UBound(VungChon.Value, 2) ' Theo phuong ngang
If GiaTriCot = VungChon(1, i) Then
For j = 1 To UBound(VungChon.Value, 1) - 1
If (GiaTriHang - VungChon(j, 1)) * (GiaTriHang - VungChon(j + 1, 1)) <= 0 Then
TangAnPha = (VungChon(j + 1, i) - VungChon(j, i)) / (VungChon(j + 1, 1) - VungChon(j, 1))
TraBang2Chieu = VungChon(j, i) + (GiaTriHang - VungChon(j, 1)) * TangAnPha
GoTo Thoat:
End If
Next j
ElseIf (GiaTriCot - VungChon(1, i)) * (GiaTriCot - VungChon(1, i + 1)) < 0 Then
For j = 1 To UBound(VungChon.Value, 1) - 1
If (GiaTriHang - VungChon(j, 1)) * (GiaTriHang - VungChon(j + 1, 1)) < 0 Then
TangAnPha = (VungChon(j, i + 1) - VungChon(j, i)) / (VungChon(1, i + 1) - VungChon(1, i))
NoiSuy1 = VungChon(j, i) + (GiaTriCot - VungChon(1, i)) * TangAnPha

TangAnPha = (VungChon(j + 1, i + 1) - VungChon(j + 1, i)) / (VungChon(1, i + 1) - VungChon(1, i))
NoiSuy2 = VungChon(j + 1, i) + (GiaTriCot - VungChon(1, i)) * TangAnPha

TangAnPha = (NoiSuy2 - NoiSuy1) / (VungChon(j + 1, 1) - VungChon(j, 1))
TraBang2Chieu = NoiSuy1 + (GiaTriHang - VungChon(j, 1)) * TangAnPha
GoTo Thoat:
End If
Next j
End If
Next i

Thoat:
'TraBang = UBound(VungChon.Value, 2)
End Function
 
5. Hàm nội suy 2 chiều: (Tiếp tục)

Tôi xin giới thiệu tiếp hàm nội suy 2 chiều do tôi lập ra, đọc dễ hiểu hơn anh PMXD nhưng hạn chế về chiều tăng hay giảm. Để hoàn thiện phải bổ sung thêm 1 số điều kiện biên nữa.

Function Noisuy2chieu(SoX, SoY, X) As Single
Dim i, j As Integer
Cao = X.Rows.Count 'Xác định số hàng
Rong = X.Columns.Count 'Xác định số cột
Dim A1, A2, B1, B2, Y1, Y2, Tim As Single 'Các điểm chặn xung quanh để tính nội suy
For i = 2 To Cao + 1
If X(i, 1) > SoX Then
n = i
For j = 2 To Rong + 1
If X(1, j) > SoY Then
m = j
B1 = X(n - 1, m)
B2 = X(n, m)
A1 = X(n - 1, m - 1)
A2 = X(n, m - 1)
Y1 = (SoY - X(1, m - 1)) * (B1 - A1) / (X(1, m) - X(1, m - 1)) + A1
Y2 = (SoY - X(1, m - 1)) * (B2 - A2) / (X(1, m) - X(1, m - 1)) + A2
Tim = (SoX - X(n - 1, 1)) * (Y2 - Y1) / (X(n, 1) - X(n - 1, 1)) + Y1
Exit For
End If
Next j
Exit For
End If
Next i
Noisuy2chieu = Tim
End Function

Bạn nào quan tâm có thể tải file kèm theo.
 

File đính kèm

Gửi anh Workman:

- Đúng là trong Excel có nhiều hàm sẵn có, nhưng trong kỹ thuật có trường hợp sử dụng các hàm này thì phải bổ sung thêm hoặc bỏ qua 1 số điều kiện (hàm lồng lằng nhằng) nên viết luôn bằng VBA cho tiện.

- Phương pháp bình phương bé nhất hay dùng trong khối kỹ thuật ở lĩnh vực xác suất thống kê, tôi sẽ đề cập đến phương pháp này sau.

Gửi chị HYen:

Vấn đề chị hỏi như nội suy 3 chiều và giản đồ trạng thái, tôi chưa có nghiên cứu gì :.
Nếu có thể chị cứ gửi lên diễn đàn để mọi người cùng thảo luận.

Cám ơn anh chị và các bạn đã quan tâm đến mục này.
 
Hàm tính tổng giá trị 1 ô cùng địa chỉ trong toàn bộ sheets

Có những công việc, bạn phải tính tổng của 1 ô cùng địa chỉ của toàn bộ Sheet trong Workbook. Nếu khối lượng các sheet ít thì cộng thủ công đơn giản, nhưng khi nhiều thì rất mất công.
Tôi xin giới thiệu hàm tính tổng đơn giản (sửa lại code của 1 thành viên) như sau:
Mã: 
Function Sumall(Cel As Range)
    Dim wSht As Worksheet
    Dim Diachi
    Tong = 0
    Diachi = Cel.Address
        For Each wSht In Worksheets
            Tong = Tong + wSht.Range(Diachi).Value
        Next
            Sumall = Tong
End Function
--=--
 
PhanTuHuong đã viết:
Có những công việc, bạn phải tính tổng của 1 ô cùng địa chỉ của toàn bộ Sheet trong Workbook. Nếu khối lượng các sheet ít thì cộng thủ công đơn giản, nhưng khi nhiều thì rất mất công.
Tôi xin giới thiệu hàm tính tổng đơn giản (sửa lại code của 1 thành viên) như sau:
Mã: 
Function Sumall(Cel As Range)
    Dim wSht As Worksheet
    Dim Diachi
    Tong = 0
    Diachi = Cel.Address
        For Each wSht In Worksheets
            Tong = Tong + wSht.Range(Diachi).Value
        Next
            Sumall = Tong
End Function
--=--
Nhấp chuột vào đây để mở rộng...
Xin lối anh (chị )PhantuHuong thật ra câu hỏi lần trước em muốn hỏi vể hàm sau đây.thông thường các sheet trong bảng tính của chúng ta giống nhau và có một shees gọi là sheet tổng hợp .Giá trị ô cel của sheet này sẽ bằng tổng các ô cel của các sheet còn lại vì vậy trên cơ sở đoạn code của anh(chị )phantuhuong tôi đã tìm được cho mình hàm cần tìm .đoạn code sau đây sẽ tính tổng một ô có cùng địa chỉ trong tất cả các sheets trừ sheet đầu tiên:
Function Sumall1(Cel As Range)
Dim wSht As Worksheet
Dim Diachi
Tong = 0
Diachi = Cel.Address
For i = 2 To Sheets.Count
Tong = Tong + Sheets(i).Range(Diachi).Value
Next
Sumall1= Tong
End Function
 
To thanguct, khi thay đổi số liệu ở một sheet nào đó thì công thức không tự tính lại mặc dù ở tool/calculation tôi đã để automatic. Mà phải quay lại cell có công thức ấn đúp một cái nó mới thực hiện, không tiện lắm nếu như có nhiều ô như vậy.
 
Đúng như bạn ngo15 nói, khi thay đổi giá trị ở các sheet thì công thức không cập nhật. Vì vậy cần phải sửa lại.
 
Cám ơn các bạn!

--------
PhanTuHuong: Lần sau bạn phải gõ có dấu đấy nhé.
 
Chỉnh sửa lần cuối bởi điều hành viên:
ThanhThao đã viết:
Đúng như bạn ngo15 nói, khi thay đổi giá trị ở các sheet thì công thức không cập nhật. Vì vậy cần phải sửa lại.
Nhấp chuột vào đây để mở rộng...
Để công thức cập nhật được:
-------------------
Function Sumall1(Cel As Range)
Dim wSht As Worksheet
Dim Diachi
Dim Tong As Long, i As Integer
Tong = 0
Diachi = Cel.Address
Application.Volatile True
For i = 2 To Sheets.Count
Tong = Tong + Sheets(i).Range(Diachi).Value
Next
Sumall1 = Tong
End Function
-----------------------
 
Tôi xin giới thiệu thêm 2 hàm tính giá trị lớn nhất và nhỏ nhất trong 1 cột. Những hàm này đã có sẵn trong Excel nhưng có ý nghĩa về mặt thuật toán (gán hay bổ sung thêm các yếu tố cần thiết) để các bạn tham khảo:

1. Hàm tính giá trị nhỏ nhất:

Mã: 
'Xac dinh gia tri nho nhat trong mang la 1 cot
Function Nhonhat(Mang) As Double
    Dim Nho As Double
    Dim i, n, Diem As Integer
        n = Mang.Rows.Count
        Nho = Mang.Cells(1)
        Diem = 1
    For i = 2 To n
        If Nho > Mang.Cells(i) Then
            Nho = Mang.Cells(i)
            Diem = i
        End If
    Next
    Nhonhat = Nho
End Function

2. Hàm tính giá trị lớn nhất:
Mã: 
'Xac dinh gia tri lon nhat trong mang la 1 cot
Function Lonnhat(Mang) As Double
    Dim Lon As Double
    Dim i, n, Diem As Integer
        n = Mang.Rows.Count
        Lon = Mang.Cells(1)
        Diem = 1
    For i = 2 To n
        If Lon < Mang.Cells(i) Then
            Lon = Mang.Cells(i)
            Diem = i
        End If
    Next
    Lonnhat = Lon
End Function

:bleh: :bleh:
 
PhanTuHuong đã viết:
2. Hàm nội suy 1 chiều:
Ví dụ có 2 dãy số liệu A và B. Hàm Vlookup chỉ tìm kiếm những giá trị B(i) tương ứng với A(i). Còn những giá trị ở giữa thì ta phải nội suy. Ví dụ đơn giản dưới đây, để hoàn thiện thì bạn bổ sung code trong Elseif nhé:
Function MangB(SoA, Arr1, Arr2) As Single
Dim A1, A2, B1, B2 As Single

For Each Cell In Arr1
A1 = Cell.Offset(-1, 0)
A2 = Cell.Offset(0, 0)
B1 = Cell.Offset(-1, 1)
B2 = Cell.Offset(0, 1)

If Cell.Value = SoA Then
MangB = B2
Exit For
ElseIf Cell.Value > SoA Then
MangB = (SoA - A1) * (B2 - B1) / (A2 - A1) + B1
Exit For
End If
Next Cell
End Function
Nhấp chuột vào đây để mở rộng...

Để nội suy tuyến tính 1 chiều, không nhất thiết phải dùng VBA, mà bạn có thể dùng các hàm sẵn có trong Excel. Các bạn có thể xem trong file kèm theo.
 

File đính kèm

có anh chị nào biết cánh giải bài toán quy hoạch tuiyến tính bằng Excel không ? nói dùm cho biết với . càng chi tiết càng tốt
cảm ơn trước nhe!!!!!!
 
Mình cũng viết hàm nội suy 1 chiều và 2 chiều.
Các bạn dùng thử xem.
 

File đính kèm

cảm ơn anh PhanTuHuong !
em sẽ nguyên cứu và ứng dụng
 
Làm sao để gọi mảng 1 chiều nằm trong 1 mảng 2 chiều ?

Trong lập trình VBA, giả sử bạn có 1 mảng 2 chiều, và muốn gọi 1 mảng 1 chiều trong đó ra để thực hiện các phép tính đối với mảng 1 chiều.
Việc này xuất phát từ ý tưởng viết lại hàm nội suy 2 chiều trong đó có sử dụng hàm nội suy 1 chiều đã viết sẵn. Nếu tính thủ công thì e là đọan chương trình sẽ rất dài do phải xét tới các trường hợp: (dãy x tăng,dãy y tăng) ; (dãy x tăng, dãy y giảm);(dãy x giảm,dãy y tăng) ; (dãy x giảm, dãy y giảm). Bởi vì sự tăng giảm của dãy thay đổi thì cách tìm cận nội suy cũng thay đổi theo. (cận nội suy là các giá trị kẹp trên,kẹp dưới của biến cần nội suy). Mặc dù hầu hết các bảng tra đều có dạng (dãy x tăng, dãy y tăng), nhưng mình muốn giải quyết hết tất cả các trường hợp, mong mọi người chỉ giáo.
 
Mã: 
Public Function NoiSuy100(x As Double, x1 As Double, x2 As Double, y1 As Double, y2 As Double) As Double
NoiSuy100 = ((x - x1) * (y2 - y1)) / (x2 - x1) + y1
End Function
'So lieu hang cot phai xap xep tang dan hoac giam dan
Public Function NoiSuy200(x As Double, x1 As Range, y1 As Range, n As Integer, HC As String)
Dim i As Integer
If HC = "row" Then
    i = x1.Columns.Count
    If x1.Cells(1, 2) > x1.Cells(1, 1) Then
        For i = 1 To x1.Columns.Count - 1
            If x1.Cells(1, i) >= x Then Exit For
        Next
    Else
        For i = 1 To x1.Columns.Count - 1
            If x1.Cells(1, i) <= x Then Exit For
        Next
    End If
    NoiSuy200 = NoiSuy100(x, x1.Cells(1, i - 1), x1.Cells(1, i), y1.Cells(n, i - 1), y1.Cells(n, i))
Else
    i = x1.Rows.Count
    If x1.Cells(2, 1) > x1.Cells(1, 1) Then
        For i = 1 To x1.Rows.Count - 1
            If x1.Cells(i, 1) >= x Then Exit For
        Next
    Else
        For i = 1 To x1.Rows.Count - 1
            If x1.Cells(i, 1) <= x Then Exit For
        Next
    End If
    NoiSuy200 = NoiSuy100(x, x1.Cells(i - 1, 1), x1.Cells(i, 1), y1.Cells(i - 1, n), y1.Cells(i, n))
End If
End Function
Bạn tham khảo thêm file help tôi đã upload lên tại bài số 21 nhé!
 
Lần chỉnh sửa cuối:
Hàm nội suy tôi kiếm được, bổ sung cho các bác xem...


Như bạn biết, trong Excel để tìm kiếm một giá trị thoả mãn điều kiện nào đó, ta có thể dùng hàm Vlookup hay Hlookup. Tuy nhiên, hai hàm này chỉ cho phép tìm kiếm theo 1 điều kiện mà thôi. Ví dụ chúng ta có một bảng dữ liệu như hình 1, yêu cầu đặt ra là tìm điểm toán của một học sinh trong danh sách theo 2 điều kiện giới tính và tên. Vậy ta phải làm thế nào đây?

Tôi sẽ giới thiệu với các bạn một hàm tự tạo để làm việc này.
Trước tiên, bạn vào menu Tools\Macro\Visual Basic Editor (Alt + F11). Tại cửa sổ Microsoft Visual Basic, vào menu Insert\Module và nhập đoạn mã sau vào module vừa tạo.

Function FindTwoCondition(Table As Range, Val1 As Variant, _
Val1Occrnce As Integer, Val2 As Variant, Val2Col As Integer, ResultCol As Integer)
'Tabel la bang du lieu
'Val1 Dieu kien thu nhat
'Val1Occrnce gia tri thu n cua dieu kien trong cot
'Val2 dieu kien thu hai
'Val2Col cot thu n cua dieu kien thu 2
'ResultCol cot thu n can tim

Dim i As Integer, iCount As Integer
Dim rCol As Range

For i = 1 To Table.Rows.Count
If Table.Cells(i, 1) = Val1 And _
Table.Cells(i, Val2Col) = Val2 Then
iCount = iCount + 1
End If

If iCount = Val1Occrnce Then
FindTwoCondition = Table.Cells(i, ResultCol)
Exit For
End If
Next i
End Function
Nhấp chuột vào đây để mở rộng...
Sau khi đã nhập xong đoạn mã trên, bạn quay trở lại màn hình làm việc Excel bằng cách ấn Alt+Q. Bây giờ ta có thể sử dụng hàm vừa tạo như những hàm mà Excel đã hỗ trợ. Ví dụ tôi cần tìm Điểm Toán của người có tên là "Sơn" và có giới tính là "Nữ".
Trước tiên tôi lập bảng điều kiện như hình 2, tại ô I6 tôi nhập công thức sau:

=FindTwoCondition($B$4:$F$13,I4,1,I5,3,4)

Trong đó:

- $B$4:$F$13: Vùng dữ liệu
- I4: Tên cần tìm
- 1: Tìm tên Sơn đầu tiên
- I5: Giới tính cần tìm
- 3: Số thứ tự của cột Giới tính trong vùng dữ liệu
- 4: Số thứ tự của cột Điểm Toán trong vùng dữ liệu

Kết quả sẽ trả về là 7. Chú ý ở đây hàm không phân biệt chữ thường chữ hoa.

Lâm Quang Bình
Binhlq77@yahoo.co.uk
 
kính gửi anh nvson
em đã sử dụng thử chương trình của anh
trước hết cám ơn anh sơn đã gửi chương trình cho anh em sử dụng
và em có 1 số thắc mắc như sau
trong hàm nội suy 2
cú pháp hàm như sau
=noisuy2(0.5,B3:B13,E 3:E 13,11,"row")
với số cột hàng trong Y 1 là 11
thế nhưng kết quả lúc nào cũng bằng 0
xin anh sơn chỉ giúp cho e.
 
erosion đã viết:
kính gửi anh nvson
em đã sử dụng thử chương trình của anh
trước hết cám ơn anh sơn đã gửi chương trình cho anh em sử dụng
và em có 1 số thắc mắc như sau
trong hàm nội suy 2
cú pháp hàm như sau
=noisuy2(0.5,B3:B13,E 3:E 13,11,"row")
với số cột hàng trong Y 1 là 11
thế nhưng kết quả lúc nào cũng bằng 0
xin anh sơn chỉ giúp cho e.
Nhấp chuột vào đây để mở rộng...
Cú pháp hàm noisuy2 là:
Mã: 
noisuy2(X, X1, Y1, n, HC)
Trong đó:
X1: Vùng dữ liệu biến nội suy
Y1: Vùng dữ liệu biến cần nội suy
n: Số dòng hoặc số cột (nằm trong vùng Y1)
HC: Nội suy theo hàng hay theo cột
HC = "row" nếu nội suy theo hàng
HC <> "row" nội suy theo cột
Bạn sử dụng:
Mã: 
=noisuy2(0.5,B3:B13,E 3:E 13,11,"row")
Như vậy là bạn nội suy 2 chiều theo cột nhưng tham số HC bạn lại đặt là "row" tức là nội suy theo hàng.
Bạn chỉ cần đặt lại tham số HC là "Column" là được thôi và chú ý tham số n. Trong vùng nội suy của bạn là E3:E13 thì n=1 (vì nó chỉ có 1 cột).
Mình sẽ lấy 1 VD tổng quát hơn cho bạn tham khảo:
Nội suy theo cột:
=noisuy2(0.5,B3:B13,E3:H13,n,"Column")
Với n=1 nó sẽ nội suy vùng Y theo cột E
n=2 --> nội suy theo cột F
n=3 --> nội suy theo cột G
n=4 --> nội suy theo cột H
n=5 --> nội suy theo cột I
(n = 1 ~ tổng số cột trong vùng Y1)

Nội suy theo hàng:
=noisuy2(0.5,B15:F15,B18:F25,n,"row")
Với n=1 nó sẽ nội suy vùng Y theo hàng 18
n=2 --> nội suy theo hàng 19
n=3 --> nội suy theo hàng 20
.....
(n = 1 ~ tổng số dòng trong vùng Y1)
 
cám ơn a nvson
bay giờ thì em đã rõ
 
hôm nay tôi mới tham gia vào diễn đàn nhưng tôi camt thấy rất thích thú
Tôi có rất nhiêu thắc mắc về excel, đặc biệt là những vấn đề liên quan đến kỹ thuật. Để lần sau tôi sẽ tổng hợp và gửi những thắc mắc của mình, hy vọng la các bạn sẽ giáp đáp cho tôi. Xin chân thành cảm ơn!
 
Nội suy 1 chiều và 2 chiều

Trong vấn đề nội suy 1 và 2 chiều nếu anh (chị) nào không rành về VBA thì vẫn làm được mà không qúa khó. Giải quyết được 2 vấn đề nêu trên chỉ thông qua 1 hàm if và 1 hàm max mà thôi tuy hơi dài 1 chút nhưng rất dễ hiểu và chân phương (cách giải quyết rất nông dân). Sẵn đây tôi gợi ý luôn, đối với bảng nối suy 2 chiều: trước tiên trên sheet có bảng tra, bạn cần tạo ra một bảng mẫu mới giống như bảng cần tra đã có dữ liệu và các ô bên trong để trống. Khi một số liệu bất kỳ nằm trong khoảng kẹp của hai cột hoặc hai hàng của bảng tra thì gõ công thức tại vị trí ô trống trong bảng tra mới như sau: If(cột1(hoặc hàng1 của bảng tra)<số liệu đầu vào<cột2(hoặc hàng2 của bảng tra);"cho kết qủa nội suy bằng cách dùng công thức tam suất toán học (cái này ai cũng biết)";còn không đúng thì gán bằng =0")....tương tự cho các ô còn lại trong bảng mới. Kết qủa có được trong bảng mới sẽ có một trị số được lộ ra còn các ô khác đều bằng không. Lúc này anh chị sẽ làm một động tác nhẹ nhàng là dùng hàm max---->sẽ được kết qủa cần tìm. Lưu ý hàm if ở trên tôi chỉ diễn giải cho dễ hiểu nên không đúng qui tắc và bảng tra 2 chiều ở đây là số lớn >=0, nếu <0 thì dủng hàm min. Chúc anh chị làm thành công nhé...@#!^%@$@!^%-=09=
 
Lần chỉnh sửa cuối:
Hàm nội suy 1 chiều ( sưu tầm)
Mong các bác cho ý kiến .
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)
If Lookup_value = Range1.Cells(NumRows) Then
NS = Range2.Cells(NumRows)
Exit Function
End If
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
If Lookup_value > Max Or Lookup_value < Min Then
NS = "Out of range" 'Evaluate("NA()")
Exit Function
End If
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
 
Gọi lại hàm vừa tạo

Làm thế nào để gọi hàm vừa tạo xong ?mình đã thử nhưng kết quả ra giá trị 0,như mình đã thừ hàm noisuy1chieu của chị Hương ,thử thay kết quả khác thì kết quả là: #NAME,không biết tại sao nữa,bạn nào có thể giúp mình được k?đang tập tành về VB nên chưa rành%#^#$
 
nội suy

bác nào biết gán cho các ô của cùng 1 cột công thức mà bác hướng xây dựng trong file noisuymotchieu.rar ko?
thí dụ gán công thức cho cột F với yêu cầu: số lượng công thức gán cho các ô này ko đc lớn hơn hay nhỏ hơn số dòng nội suy.Cụ thể, ở đây vùng nội suy chỉ gồm 10 dòng thì công thức cũng chỉ gán cho 10
 

File đính kèm

Lần chỉnh sửa cuối:
Các bạn cho tôi hỏi những hàm mà bạn viết ra như vậy thì phải coppy vào đâu để sử dụng được? Mong chỉ giáo sớm.
 
Bạn phải tìm hiểu về khái niệm macro và VBA (như dưới đây).

Để các hàm sử dụng được, bạn vào menu Tools/Macros/Security... Sau đó chọn Low hay Medium thì các hàm(Function) và thủ tục (Sub) mới hoạt động được.
 
mấy thông tin này quá cũ rồi mà vẫn còn dùng. lại còn ca tụng nhau nghe lố quá.
Nhấp chuột vào đây để mở rộng...
Bạn sai hoàn toàn;
Thứ nhất: Cũ người mới ta; Hay bạn viết cái mới để thay thế & chứng tỏ nó ưu việc hơn đi! Xin đón chào tác phẩm của bạn;
Thứ nhì: Bạn mới là lố bịch, & thiếu lịch sự khi chê bai người khác như vậy! Bạn hãy tạo ra sản phẩm gì đi, cho cộng đồng này, lúc đó chúng ta cùng nhau tung hô bạn mệt nghỉ!!
Xin chờ bạn với lòng kiên trì vô hạn định! Nếu cuộc đời này còn cho phép, mình sẽ chờ bạn với số ngày = số bài mình đã đưa lên diễn đàn này; Thân ái!
​
 
Chỉnh sửa lần cuối bởi điều hành viên:
workman đã viết:
Hàm trung bình có phải để tìm MEAN không? Nếu thế thì excel có sẵn hàm AVERAGE rồi, bác thử dùng xem sao.

Average(Array)

Tôi không hiểu nội suy tuyến tính để làm gì. Bác PhanTuHuong chịu khó giới thiệu lại về căn bản được không? Thực sự tôi mất căn bản trầm trọng về những hàm này.
Nhấp chuột vào đây để mở rộng...

Nội suy tuyến tính là thế này:
Nhà tớ có 3 anh em.
Anh cả 9 tuổi được 3 cái kẹo
Tớ là út 3 tuổi được 1 cái kẹo
Vậy Anh thứ hai tớ 6 tuổi được 2 cái kéo.
Và nếu anh thứ 2 tớ 4 tuổi thì được ăn: 1+(4-3)*(3-1)/(9-3) = 1.33 cái.
Giả thiết là càng lớn tuổi thì được càng nhiều kẹo.

Phải không bác phantuhuong nhỉ???

deathofwar đã viết:
Làm thế nào để gọi hàm vừa tạo xong ?mình đã thử nhưng kết quả ra giá trị 0,như mình đã thừ hàm noisuy1chieu của chị Hương ,thử thay kết quả khác thì kết quả là: #NAME,không biết tại sao nữa,bạn nào có thể giúp mình được k?đang tập tành về VB nên chưa rành%#^#$
Nhấp chuột vào đây để mở rộng...

Dùng hàm noisuy1chieu của Bác Hướng kết quả là #Name, không biết tại sao???
Hàm: noisuy2chieu thì dùng ngon, cám ơn bác hướng. Nhưng bác bảo hàm noisuy2chieu hạn chế cụ thể là ở chỗ nào vậy.
 
Chỉnh sửa lần cuối bởi điều hành viên:
lsxinh đã viết:
Dùng hàm noisuy1chieu của Bác Hướng kết quả là #Name, không biết tại sao???
Hàm: noisuy2chieu thì dùng ngon, cám ơn bác hướng. Nhưng bác bảo hàm noisuy2chieu hạn chế cụ thể là ở chỗ nào vậy.
Nhấp chuột vào đây để mở rộng...

Bạn chịu khó đọc code là hiểu lỗi do đâu. Còn hàm noisuy2chieu hạn chế ở phần nội suy ở biên trên dưới số liệu, nên cần bổ sung code mới hoàn thiện được.
 
Nội Suy

Rất cảm ơn anh vì sự chia xẻ này!
Tôi muốn dùng các hàm nội suy 1, 2 chiều của anh mỗi khi mở Excel lên thì tôi phải làm sao?
Mong anh chỉ bảo.
Nếu không phiền anh có thể gửi vào mail cho tôi được không: bktec84@yahoo.com
Thank you very much!
 
cám ơn bạn nhiều.file của bạn giúp dc mình rất nhiều.
 
bktec84 đã viết:
Rất cảm ơn anh vì sự chia xẻ này!
Tôi muốn dùng các hàm nội suy 1, 2 chiều của anh mỗi khi mở Excel lên thì tôi phải làm sao?
Mong anh chỉ bảo.
Nếu không phiền anh có thể gửi vào mail cho tôi được không: bktec84@yahoo.com
Thank you very much!
Nhấp chuột vào đây để mở rộng...

Bạn tìm hiểu thêm phần AddIns (hoặc tìm kiếm trong diễn đàn) để các hàm này cập nhật vào trong thư viện, giống như các hàm sẵn có trong excel.
 
PhanTuHuong đã viết:
Tôi xin giới thiệu một số hàm đơn giản liên quan đến kỹ thuật, một số hàm đó có thể có sẵn hoặc chưa có trong Excel.
1. Hàm tính giá trị trung bình:

Mã: 
Function Trungbinh(Arr) As Single
  Dim Tong As Single
  Dim i As Integer
  i = 0
  Tong = 0
  For Each Cell In Arr
    If Cell.Value > 0 Then
       Tong = Tong + Cell.Value
       i = i + 1
    Else
       i = i
    End If
  Next Cell
    Trungbinh = Tong / i
End Function
2. Hàm nội suy 1 chiều:
Ví dụ có 2 dãy số liệu A và B. Hàm Vlookup chỉ tìm kiếm những giá trị B(i) tương ứng với A(i). Còn những giá trị ở giữa thì ta phải nội suy. Ví dụ đơn giản dưới đây, để hoàn thiện thì bạn bổ sung code trong Elseif nhé:

Mã: 
Function MangB(SoA, Arr1, Arr2) As Single
  Dim A1, A2, B1, B2 As Single
 
  For Each Cell In Arr1
     A1 = Cell.Offset(-1, 0)
     A2 = Cell.Offset(0, 0)
     B1 = Cell.Offset(-1, 1)
     B2 = Cell.Offset(0, 1)
 
     If Cell.Value > SoA Then
       MangB = (SoA - A1) * (B2 - B1) / (A2 - A1) + B1
       Exit For
     End If
  Next Cell
End Function


Cảm ơn anh vì chia sẻ của mình. Lần trước tôi đã hỏi ảnh về cách làm sao để có thể sử dụng công thức này cho mọi file Excel mỗi khi mở ra, nhưng nhận được câu trả lời của anh không mấy rõ ràng. Mong anh chỉ bảo tôi để chúng ta cùng tiến bộ. Cảm ơn anh!
Ah tôi đã làm như sau nhưng không sao sử dụng được mong anh giải thích dùm:
Tools/Macro/Visual Basic Editor: Insert/ Modul; Insert Procedure; sau đó viết:
Function MangB(SoA, Arr1, Arr2) As Single
Dim A1, A2, B1, B2 As Single

For Each Cell In Arr1
A1 = Cell.Offset(-1, 0)
A2 = Cell.Offset(0, 0)
B1 = Cell.Offset(-1, 1)
B2 = Cell.Offset(0, 1)

If Cell.Value > SoA Then
MangB = (SoA - A1) * (B2 - B1) / (A2 - A1) + B1
Exit For
End If
Next Cell
End Function
Nhưng không sử dụng được và nó báo nối: #NAME?
xin anh giúp tôi. Chân thành cảm ơn.
Nhấp chuột vào đây để mở rộng...
 
PhanTuHuong đã viết:
Bạn tìm hiểu thêm phần AddIns (hoặc tìm kiếm trong diễn đàn) để các hàm này cập nhật vào trong thư viện, giống như các hàm sẵn có trong excel.
Nhấp chuột vào đây để mở rộng...
Tôi làm theo hướng dẫn của phần AddIns nhưng vẫn không được thì làm thế nào? Anh chỉ giúp tôi với. tôi tìm trên diễn đàn cũng không thấy nói về phần này. Thank you!

Toi da dowload file zip cua anh ve nhu cho chay tren excel 2007 thi no báo #NAME là làm sao? Mong anh giải thích rùm.
Cảm ơn anh
 
Chỉnh sửa lần cuối bởi điều hành viên:
trungnt đã viết:
Trong vấn đề nội suy 1 và 2 chiều nếu anh (chị) nào không rành về VBA thì vẫn làm được mà không qúa khó. Giải quyết được 2 vấn đề nêu trên chỉ thông qua 1 hàm if và 1 hàm max mà thôi tuy hơi dài 1 chút nhưng rất dễ hiểu và chân phương (cách giải quyết rất nông dân). Sẵn đây tôi gợi ý luôn, đối với bảng nối suy 2 chiều: trước tiên trên sheet có bảng tra, bạn cần tạo ra một bảng mẫu mới giống như bảng cần tra đã có dữ liệu và các ô bên trong để trống. Khi một số liệu bất kỳ nằm trong khoảng kẹp của hai cột hoặc hai hàng của bảng tra thì gõ công thức tại vị trí ô trống trong bảng tra mới như sau: If(cột1(hoặc hàng1 của bảng tra)<số liệu đầu vào<cột2(hoặc hàng2 của bảng tra);"cho kết qủa nội suy bằng cách dùng công thức tam suất toán học (cái này ai cũng biết)";còn không đúng thì gán bằng =0")....tương tự cho các ô còn lại trong bảng mới. Kết qủa có được trong bảng mới sẽ có một trị số được lộ ra còn các ô khác đều bằng không. Lúc này anh chị sẽ làm một động tác nhẹ nhàng là dùng hàm max---->sẽ được kết qủa cần tìm. Lưu ý hàm if ở trên tôi chỉ diễn giải cho dễ hiểu nên không đúng qui tắc và bảng tra 2 chiều ở đây là số lớn >=0, nếu <0 thì dủng hàm min. Chúc anh chị làm thành công nhé...@#!^%@$@!^%-=09=
Nhấp chuột vào đây để mở rộng...
cach nay rat thu cong. Chỉ dành để tra 1 lần một giá trị. nếu vấn đề là cần tra một dãy giá trị trong 1 cột và cột bên là kết quả nội suy tương ứng thì cách này bó tay
 
PhanTuHuong đã viết:
Tôi xin giới thiệu một số hàm đơn giản liên quan đến kỹ thuật, một số hàm đó có thể có sẵn hoặc chưa có trong Excel.
1. Hàm tính giá trị trung bình:

Mã: 
Function Trungbinh(Arr) As Single
  Dim Tong As Single
  Dim i As Integer
  i = 0
  Tong = 0
  For Each Cell In Arr
    If Cell.Value > 0 Then
       Tong = Tong + Cell.Value
       i = i + 1
    Else
       i = i
    End If
  Next Cell
    Trungbinh = Tong / i
End Function
2. Hàm nội suy 1 chiều:
Ví dụ có 2 dãy số liệu A và B. Hàm Vlookup chỉ tìm kiếm những giá trị B(i) tương ứng với A(i). Còn những giá trị ở giữa thì ta phải nội suy. Ví dụ đơn giản dưới đây, để hoàn thiện thì bạn bổ sung code trong Elseif nhé:

Mã: 
Function MangB(SoA, Arr1, Arr2) As Single
  Dim A1, A2, B1, B2 As Single
 
  For Each Cell In Arr1
     A1 = Cell.Offset(-1, 0)
     A2 = Cell.Offset(0, 0)
     B1 = Cell.Offset(-1, 1)
     B2 = Cell.Offset(0, 1)
 
     If Cell.Value > SoA Then
       MangB = (SoA - A1) * (B2 - B1) / (A2 - A1) + B1
       Exit For
     End If
  Next Cell
End Function


mời các bạn tải file kèm theo
Nhấp chuột vào đây để mở rộng...


Bạn có thể tham khảo Đoạn Code Của Mình như sau
Nhấp chuột vào đây để mở rộng...
Mã: 
Function NS(SoX, X, Y) As Double
Dim i As Integer
Dim n, cao As Integer
cao = X.Rows.Count   'So hang cua bang gia tri cho truoc
i = 1
For i = 1 To cao
If X(i) <= SoX And SoX <= X(i + 1) Then
n = i
NS = Y(n) + (SoX - X(n)) * (Y(n + 1) - Y(n)) / (X(n + 1) - X(n))
Exit For
End If
Next i
End Function
Theo cách này nội suy đúng cho những giá trị biên
Nhấp chuột vào đây để mở rộng...
 

File đính kèm

Lần chỉnh sửa cuối:
Sửa lại mã nguồn của bạn đây

PhanTuHuong đã viết:
5. Hàm nội suy 2 chiều: (Tiếp tục)

Tôi xin giới thiệu tiếp hàm nội suy 2 chiều do tôi lập ra, đọc dễ hiểu hơn anh PMXD nhưng hạn chế về chiều tăng hay giảm. Để hoàn thiện phải bổ sung thêm 1 số điều kiện biên nữa.

Function Noisuy2chieu(SoX, SoY, X) As Single
Dim i, j As Integer
Cao = X.Rows.Count 'Xác định số hàng
Rong = X.Columns.Count 'Xác định số cột
Dim A1, A2, B1, B2, Y1, Y2, Tim As Single 'Các điểm chặn xung quanh để tính nội suy
For i = 2 To Cao + 1
If X(i, 1) > SoX Then
n = i
For j = 2 To Rong + 1
If X(1, j) > SoY Then
m = j
B1 = X(n - 1, m)
B2 = X(n, m)
A1 = X(n - 1, m - 1)
A2 = X(n, m - 1)
Y1 = (SoY - X(1, m - 1)) * (B1 - A1) / (X(1, m) - X(1, m - 1)) + A1
Y2 = (SoY - X(1, m - 1)) * (B2 - A2) / (X(1, m) - X(1, m - 1)) + A2
Tim = (SoX - X(n - 1, 1)) * (Y2 - Y1) / (X(n, 1) - X(n - 1, 1)) + Y1
Exit For
End If
Next j
Exit For
End If
Next i
Noisuy2chieu = Tim
End Function

Bạn nào quan tâm có thể tải file kèm theo.
Nhấp chuột vào đây để mở rộng...
Mình đã viết hoàn chỉnh đây Đúng cho những giá trị ở biên.
Nhấp chuột vào đây để mở rộng...
Mã: 
Function NSZ(Xo, Yo, Z) As Single
Dim i, j As Integer
        Cao = Z.Rows.Count 'Xac dinh so hang
        Rong = Z.Columns.Count 'Xac dinh so cot
    Dim A1, A2, B1, B2, Y, X As Single 'Cac diem goc va khoang delta X,Y
    For i = 2 To Cao
         If Z(i, 1) <= Xo And Xo <= Z(i + 1, 1) Then
                n = i
                    For j = 2 To Rong
                        If Z(1, j) <= Yo And Yo <= Z(1, j + 1) Then
                            m = j
                            Y = Z(1, m + 1) - Z(1, m)
                            X = Z(n + 1, 1) - Z(n, 1)
                            B1 = Z(n, m + 1)
                            B2 = Z(n + 1, m + 1)
                            A1 = Z(n, m)
                            A2 = Z(n + 1, m)
                            [COLOR=magenta]NSZ = (A1 * (Z(1, m + 1) - Yo) * (Z(n + 1, 1) - Xo) + A2 * (Xo - Z(n, 1)) * (Z(1, m + 1) - Yo) + B1 * (Yo - Z(1, m)) * (Z(n + 1, 1) - Xo) + B2 * (Xo - Z(n, 1)) * (Yo - Z(1, m))) / (X * Y)[/COLOR] 'Nho viet tren 1 dong
                        End If
                    Next j
    Exit For
        End If
    Next i
 
End Function
 
Lần chỉnh sửa cuối:
em cám ơn rất nhiều vì đã cho em có được tập tài liệu này! chúc Anh(Chị) sức khỏe và công tác tốt.
 
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

G
AddIn convert ngày dương lịch sang âm lịch và ngược lại + đọc ngày tháng (1 người xem)
Trả lời
20
Đọc
10K
du trung kien
D
E
Đánh số cho các cặp số chứng từ và số chứng từ đối ứng
Trả lời
11
Đọc
204
HieuCD
H
8
Hàm trong excel
Trả lời
3
Đọc
53
Hoàng Tuấn 868
H
N
Chuyển giờ công từ bảng công vào bảng tính
Trả lời
16
Đọc
272
ngouyen1505
N
N
Tạo COM Add-In bằng Visual C++
    • Thích
Trả lời
7
Đọc
644
nguyendang95
N
T
Tìm số hóa đơn trong chuỗi
Trả lời
8
Đọc
343
tranhungdao12a3
T
Nga1852k
lỗi excel không chèn được dòng và không xóa được dòng
Trả lời
2
Đọc
129
Nga1852k
Nga1852k
D
  • Question Question
xin các cao thủ trợ giúp , hoặc nhận làm code có trả phí ạ
Trả lời
14
Đọc
1K
tung1423
tung1423
D
xin hỏi về lỗi mô đun ẩn.
Trả lời
3
Đọc
141
ongke0711
ongke0711
H
Xin anh chị hướng dẫn công thức excel ạ.
Trả lời
9
Đọc
269
Phan Thế Hiệp
Phan Thế Hiệp
OverAC
  • Dán lên cao
  • Suggestion Suggestion
Chúc mừng sinh nhật GiaiPhapExcel 19 tuổi và tặng ebook "Lập trình VBA phần cơ bản." (1 người xem)
    • Thích
    • Yêu thích
Trả lời
4
Đọc
1K
LeThangbk
L
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
2 3 4
Trả lời
381
Đọc
44K
tuanktt3
T
M
Add-in Excel kết nối cơ sở dữ liệu
Trả lời
1
Đọc
100
ptm0412
ptm0412
Đ
tách dữ liệu từ một dòng thành hai dòng
Trả lời
2
Đọc
144
Phuocam
Phuocam
longhuy
Lỗi error 20728: F
Trả lời
0
Đọc
73
longhuy
longhuy
H
Trang tính chỉ hiển thị vùng có dữ liệu
Trả lời
7
Đọc
605
vinhkm6
vinhkm6
mrjun80
Có hàm nào đếm tổng các số nằm trong 1 ô không mọi người
Trả lời
12
Đọc
493
Phan Thế Hiệp
Phan Thế Hiệp
N
Hàm DATEDIF trong Excel 2016
Trả lời
16
Đọc
14K
Phan Thế Hiệp
Phan Thế Hiệp
Tiger Nau
Đánh số thứ tự trên tờ vé số (1 người xem)
Trả lời
6
Đọc
178
ptm0412
ptm0412
T
Dò giá trị nằm trong khoảng giá trị
Trả lời
1
Đọc
94
thao nguyen01
T
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