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

[kelvin]

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

Bạn vào VBE\Object Browser\"Key" và kết hợp với Tự điển Anh Việt

 

[SONGBA]

cảm ơn anh PhanTuHuong !
em sẽ nguyên cứu và ứng dụng

 

[PhanTuHuong]

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

Bạn thử vào đây xem:

http://www.giaiphapexcel.com/forum/showthread.php?t=303

 

[dnvylong]

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.

 

[nvson]

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

 

[Chuotdong]

Các bạn có thể chỉ cần dùng các hàm trong Excel là có thể nội suy 2 chiều được rồi, chưa cần VBA và dành cho nhưng ai không biết VBA như tôi:http://www.4shared.com/file/6886396/8b973c48/NoiSuy_TuyenTinh.html

 

[Hamvui]

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

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

 

[erosion]

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.

 

[nvson]

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.

Cú pháp hàm noisuy2 là:

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:

=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)

 

[erosion]

cám ơn a nvson
bay giờ thì em đã rõ

 

[thanhlamct]

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!

 

[trungnt]

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

 

[BE_GOLDENDRAGON]

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

 

[deathofwar]

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

 

[sinhvienvn2005]

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

 

[dinhthuanxd]

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.

 

[PhanTuHuong]

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.

 

[SA_DQ]

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á.

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!

​

 

[lsxinh]

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.

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ỉ???

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

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.

 

[PhanTuHuong]

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.

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.