Ðề: Mỗi tuần một hàm trong Excel!
Tôi cũng xin post một hàm dùng để tính dầm CN chịu M, Q và M-xoắn
Public Sub Uphang_xoan_cnhat(M As Single, Q As Single, MX As Single, b As Single, h As Single, a As Single, Fk As Single, Fn As Single, Fk1 As Single, Fn1 As Single, Ra As Single, Rad As Single, Rn As Single, Rk As Single, anfa0 As Single, fd As Single, u As Single, u_min As Single, ktraDam As Boolean)
'Thu tuc tinh Fk, Fn, Fk1, Fn1 de chong xoan (voi fd va u chon truoc)
Dim h0 As Single, b0 As Single, delta_Fk As Single, kq As Boolean
Dim C(1 To 100) As Single, MM As Single, MM_min As Single, tam As Single
Dim m0 As Single, m_d As Single, v As Single, qd As Single, Qdb As Single
Dim Fk_max As Single, Fk_min As Single, Fk1_max As Single, Fk1_min As Single
Dim x As Single, i As Long
'**************************************************************
'Kiem tra dk han che cua Mx:
If MX > (0.1 * Rn * h * b ^ 2) Then
ktraDam = False 'Mx qua lon, khong thoa dk han che.
Exit Sub 'Khong the tke dam voi kich thuoc da chon
End If
'Neu thoa man dk han che, ta kiem tra cac gia tri tai trong
If MX = 0 Then MX = 1 'de tranh loi chia cho 0
If MX < 0 Then MX = -MX 'Chi tinh toan voi cac gia tri duong
If Q < 0 Then Q = -Q 'Chi tinh toan voi cac gia tri duong
If Q = 0 Then Q = 1 'de tranh loi chia cho 0
If M < 0 Then M = -M 'Chi tinh toan voi cac gia tri duong
v = M / MX
If v >= 10 Then v = 10 'can thiet de dam bao mo khong qua nho
'neu khong ctrinh se lam tang Fk de thoa dk m_d<3*m0
u = mdl01.min2so(u_min, h / 3)
'Ngay tu dau, chon luon u nho nhat de chong xoan.
If Fk < 1.57 * 0.0001 Then Fk = 1.57 * 0.0001 'Toi thieu 2fi10
If Fn < 1.57 * 0.0001 Then Fn = 1.57 * 0.0001 'Toi thieu 2fi10
If Fk1 < 1.57 * 0.0001 Then Fk1 = 1.57 * 0.0001 'Toi thieu 2fi10
If Fn1 < 1.57 * 0.0001 Then Fn1 = 1.57 * 0.0001 'Toi thieu 2fi10
delta_Fk = 0.1: h0 = h - a: b0 = b - a
'**************************************************************
'TINH TOAN KIEM TRA THEO M VA MX:
m0 = 1 / ((2 + 4 * v * Sqr(b / (2 * h + b))) * (2 * h + b) * b)
Fk_min = (Rad * fd) / (3 * Ra * (2 * h + b) * u * m0) ':Fk_max = 3 * Fk_min
If Fk < Fk_min Then Fk = Fk_min
kq = False
Do
x = (Ra * (Fk - Fn)) / (Rn * b)
If x > anfa0 * h0 Then 'Qua nhieu thep, dam bi nut
Fk = -1: Fn = -1: ktraDam = False: Exit Sub
End If
m_d = Rad * fd / (Ra * Fk * (2 * h + b) * u)
'Tinh MM_min (Kha nang chong xoan nho nhat):
C(1) = (2 * h + b) / 100
MM_min = (Ra * Fk * (h0 - 0.5 * x) * (1 + m_d * C(1) * C(1)) * b) / (C(1) + v * b)
For i = 2 To 100
C(i) = i * C(1)
tam = (Ra * Fk * (h0 - 0.5 * x) * (1 + m_d * C(i) * C(i)) * b) / (C(i) + v * b)
If MM_min >= tam Then MM_min = tam
Next
If MX <= 0.95 * MM_min Then
kq = True
Else
If Fk < (0.02 * b * h0) Then
Fk = Fk + delta_Fk
Else 'Khong the tke dam voi kich thuoc, fd, u da chon
ktraDam = False
Fk = -1: Fn = -1
Exit Sub
End If
End If
Loop Until kq = True
'**************************************************************
'TINH TOAN KIEM TRA THEO MX VA Q:
'Kiem tra dk:
If MX <= 0.5 * Q * b Then
qd = Rad * fd / u 'Voi u=u_min
Qdb = Sqr(8 * Rk * b * h0 * h0 * qd)
If (Q + 3 * MX / h) <= Qdb Then
Exit Sub 'Khong can kiem tra tiep nua.
End If
End If
'm0 = 1 / ((2 + 4 * v * Sqr(h / (2 * b + h))) * (2 * b + h) * h)
'Fk1_min = (Rad * fd) / (3 * Ra * (2 * b + h) * u * m0) ':Fk1_max = 3 * Fk1_min
'If Fk1 < Fk1_min Then Fk1 = Fk1_min '(Khong can dung cac lenh nay)
kq = False
Do
x = (Ra * (Fk1 - Fn1)) / (Rn * h)
If x > anfa0 * h0 Then 'Qua nhieu thep, dam bi nut
Fk = -1: Fn = -1: ktraDam = False: Exit Sub
End If
m_d = (Rad * fd) / (Ra * Fk1 * (2 * b + h) * u)
'Tinh MM_min (Kha nang chong xoan nho nhat):
C(1) = (2 * b + h) / 100
MM_min = (Ra * Fk1 * (b0 - 0.5 * x) * (1 + m_d * C(1) ^ 2) * h) / (C(1) * (1 + Q * b / (2 * MX)))
For i = 2 To 100
C(i) = i * C(1)
tam = (Ra * Fk1 * (b0 - 0.5 * x) * (1 + m_d * C(i) ^ 2) * h) / (C(i) * (1 + Q * b / (2 * MX)))
If MM_min >= tam Then MM_min = tam
Next
If MX <= 0.95 * MM_min Then
kq = True
Else
If Fk1 < (0.01 * b0 * h) Then
Fk1 = Fk1 + delta_Fk
Else 'Khong the tke dam voi kich thuoc, fd, u da chon
ktraDam = False
Fk1 = -1: Fn1 = -1
Exit Sub
End If
End If
Loop Until kq = True
'Ket qua ta duoc Fk, Fn, Fk1, Fn1, u=u_min, fd
End Sub
Tôi cũng xin post một hàm dùng để tính dầm CN chịu M, Q và M-xoắn
Public Sub Uphang_xoan_cnhat(M As Single, Q As Single, MX As Single, b As Single, h As Single, a As Single, Fk As Single, Fn As Single, Fk1 As Single, Fn1 As Single, Ra As Single, Rad As Single, Rn As Single, Rk As Single, anfa0 As Single, fd As Single, u As Single, u_min As Single, ktraDam As Boolean)
'Thu tuc tinh Fk, Fn, Fk1, Fn1 de chong xoan (voi fd va u chon truoc)
Dim h0 As Single, b0 As Single, delta_Fk As Single, kq As Boolean
Dim C(1 To 100) As Single, MM As Single, MM_min As Single, tam As Single
Dim m0 As Single, m_d As Single, v As Single, qd As Single, Qdb As Single
Dim Fk_max As Single, Fk_min As Single, Fk1_max As Single, Fk1_min As Single
Dim x As Single, i As Long
'**************************************************************
'Kiem tra dk han che cua Mx:
If MX > (0.1 * Rn * h * b ^ 2) Then
ktraDam = False 'Mx qua lon, khong thoa dk han che.
Exit Sub 'Khong the tke dam voi kich thuoc da chon
End If
'Neu thoa man dk han che, ta kiem tra cac gia tri tai trong
If MX = 0 Then MX = 1 'de tranh loi chia cho 0
If MX < 0 Then MX = -MX 'Chi tinh toan voi cac gia tri duong
If Q < 0 Then Q = -Q 'Chi tinh toan voi cac gia tri duong
If Q = 0 Then Q = 1 'de tranh loi chia cho 0
If M < 0 Then M = -M 'Chi tinh toan voi cac gia tri duong
v = M / MX
If v >= 10 Then v = 10 'can thiet de dam bao mo khong qua nho
'neu khong ctrinh se lam tang Fk de thoa dk m_d<3*m0
u = mdl01.min2so(u_min, h / 3)
'Ngay tu dau, chon luon u nho nhat de chong xoan.
If Fk < 1.57 * 0.0001 Then Fk = 1.57 * 0.0001 'Toi thieu 2fi10
If Fn < 1.57 * 0.0001 Then Fn = 1.57 * 0.0001 'Toi thieu 2fi10
If Fk1 < 1.57 * 0.0001 Then Fk1 = 1.57 * 0.0001 'Toi thieu 2fi10
If Fn1 < 1.57 * 0.0001 Then Fn1 = 1.57 * 0.0001 'Toi thieu 2fi10
delta_Fk = 0.1: h0 = h - a: b0 = b - a
'**************************************************************
'TINH TOAN KIEM TRA THEO M VA MX:
m0 = 1 / ((2 + 4 * v * Sqr(b / (2 * h + b))) * (2 * h + b) * b)
Fk_min = (Rad * fd) / (3 * Ra * (2 * h + b) * u * m0) ':Fk_max = 3 * Fk_min
If Fk < Fk_min Then Fk = Fk_min
kq = False
Do
x = (Ra * (Fk - Fn)) / (Rn * b)
If x > anfa0 * h0 Then 'Qua nhieu thep, dam bi nut
Fk = -1: Fn = -1: ktraDam = False: Exit Sub
End If
m_d = Rad * fd / (Ra * Fk * (2 * h + b) * u)
'Tinh MM_min (Kha nang chong xoan nho nhat):
C(1) = (2 * h + b) / 100
MM_min = (Ra * Fk * (h0 - 0.5 * x) * (1 + m_d * C(1) * C(1)) * b) / (C(1) + v * b)
For i = 2 To 100
C(i) = i * C(1)
tam = (Ra * Fk * (h0 - 0.5 * x) * (1 + m_d * C(i) * C(i)) * b) / (C(i) + v * b)
If MM_min >= tam Then MM_min = tam
Next
If MX <= 0.95 * MM_min Then
kq = True
Else
If Fk < (0.02 * b * h0) Then
Fk = Fk + delta_Fk
Else 'Khong the tke dam voi kich thuoc, fd, u da chon
ktraDam = False
Fk = -1: Fn = -1
Exit Sub
End If
End If
Loop Until kq = True
'**************************************************************
'TINH TOAN KIEM TRA THEO MX VA Q:
'Kiem tra dk:
If MX <= 0.5 * Q * b Then
qd = Rad * fd / u 'Voi u=u_min
Qdb = Sqr(8 * Rk * b * h0 * h0 * qd)
If (Q + 3 * MX / h) <= Qdb Then
Exit Sub 'Khong can kiem tra tiep nua.
End If
End If
'm0 = 1 / ((2 + 4 * v * Sqr(h / (2 * b + h))) * (2 * b + h) * h)
'Fk1_min = (Rad * fd) / (3 * Ra * (2 * b + h) * u * m0) ':Fk1_max = 3 * Fk1_min
'If Fk1 < Fk1_min Then Fk1 = Fk1_min '(Khong can dung cac lenh nay)
kq = False
Do
x = (Ra * (Fk1 - Fn1)) / (Rn * h)
If x > anfa0 * h0 Then 'Qua nhieu thep, dam bi nut
Fk = -1: Fn = -1: ktraDam = False: Exit Sub
End If
m_d = (Rad * fd) / (Ra * Fk1 * (2 * b + h) * u)
'Tinh MM_min (Kha nang chong xoan nho nhat):
C(1) = (2 * b + h) / 100
MM_min = (Ra * Fk1 * (b0 - 0.5 * x) * (1 + m_d * C(1) ^ 2) * h) / (C(1) * (1 + Q * b / (2 * MX)))
For i = 2 To 100
C(i) = i * C(1)
tam = (Ra * Fk1 * (b0 - 0.5 * x) * (1 + m_d * C(i) ^ 2) * h) / (C(i) * (1 + Q * b / (2 * MX)))
If MM_min >= tam Then MM_min = tam
Next
If MX <= 0.95 * MM_min Then
kq = True
Else
If Fk1 < (0.01 * b0 * h) Then
Fk1 = Fk1 + delta_Fk
Else 'Khong the tke dam voi kich thuoc, fd, u da chon
ktraDam = False
Fk1 = -1: Fn1 = -1
Exit Sub
End If
End If
Loop Until kq = True
'Ket qua ta duoc Fk, Fn, Fk1, Fn1, u=u_min, fd
End Sub
Ghi chú