Một số hàm tự tạo liên quan đến kỹ thuật (4 người xem)

[PhanTuHuong]

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:

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é:

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

 

[SA_DQ]

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

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

Để 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!

 

[PhanTuHuong]

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.

 

[workman]

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.

 

[PhanTuHuong]

:* 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.

 

[SA_DQ]

:* 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.

Đú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!

 

[PhanTuHuong]

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

 

[PhanTuHuong]

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.

 

[PhanTuHuong]

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.

 

[PhanTuHuong]

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:

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

 

[thanguct]

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:

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

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

 

[ngo15]

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.

 

[ThanhThao]

Đú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.

 

[hieu_66]

Cám ơn các bạn!

--------
PhanTuHuong: Lần sau bạn phải gõ có dấu đấy nhé.

 

[kelvin]

Đú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ô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
-----------------------

 

[ThanhThao]

Application.Volatile True

Bạn Kelvin có thể giải thích chức năng này không vậy?

 

[PhanTuHuong]

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:

'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:

'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

 

[PhanTuHuong]

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

Để 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.

 

[tue]

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!!!!!!

 

[nvson]

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.