Xử lý thống kê kết quả TN các mẫu đất (TCXD 74 : 1987)) (2 người xem)

[PhanTuHuong]

Tôi xin giới thiệu với các bạn phương pháp lập Chương trình xử lý thống kê kết quả thí nghiệm trong phòng. Hiện nay chưa có phần mềm nào về công việc này, nhưng trong Excel, bạn có thể làm hoàn toàn 1 cách tự động được.

Cấu trúc file bao gồm:

- Cơ sở dữ liệu để tra các hệ số t_anpha, v.
- Sử dụng các hàm sẵn có trong Excel và xây dựng các hàm nội suy để tra các hệ số trên.
- Xây dựng cửa sổ đầu vào và tiến hành loại trừ sai số thô.
- Xây dựng sheet kết qủa. Ví dụ đối với chỉ tiêu sức kháng cắt C, phi (khó nhất) thì ta xây dựng các hàm Delta, Ctc, tang_phi_tc,... Từ đó dễ dàng xác định các giá trị theo trạng thái giới hạn I, II.

 

[PhanTuHuong]

Để tra chỉ số v, bạn có thể dùng công thức VLOOKUP nhưng với t_anpha thì không dùng được, mà bắt buộc phải nội suy. Nếu dùng VLOOKUP thì sẽ cho kết quả không chuẩn xác.

Để nội suy thì có 2 cách:

- Cách 1: Nội suy gián tiếp bằng cách lập bảng tính (dạng cơ sở dữ liệu) rồi lập hàm tìm kiếm, tính toán. Cách này đơn giản, dễ sửa chữa khi cần thiết.

- Cách 2: Nội suy trực tiếp bằng cách lập sẵn các khoảng giá trị trong hàm (dưới dạng mảng), cách xác định cũng tương tự. Ưu điểm: gọn nhẹ (không cần bảng tính kèm theo). Nhược điểm: lập hàm khá phức tạp, cồng kềnh, khó sửa (vì hoa mắt bởi nhiều số giống nhau ).

Cả hai cách này đều đã được giới thiệu trong Excel và kỹ thụât.
http://www.giaiphapexcel.com/forum/showthread.php?t=486

Hình ảnh minh họa kèm theo:

 

[PhanTuHuong]

Khi việc tra bảng t_anpha, v đã xong thì ta tiến hành lập sheet vào dữ liệu ban đầu (tôi chỉ nói cho trường hợp Thí nghiệm cắt phẳng).

Nội dung cần thể hiện gồm có:

- Tên công trình, địa điểm, đơn vị lập, số hiệu lớp,...
- Bảng giá trị vào các giá trị sức kháng cắt Tô tương ứng với các giá trị cấp áp lực Pi tương ứng. Các cấp áp lực thường từ 3 đến 4. Còn số giá trị Tô thì còn tùy. Tôi đã lập cho 75 mẫu thí nghiệm, phù hợp với xây dựng và giao thông nhưng các anh bên thủy lợi, thủy điện chê ít (phải 200, 300 mẫu trở lên). Điều đó không thành vấn đề gì.
- Loại trừ sau số thô: Sau đó bạn dùng các công thức sẵn có trong Excel để tính một số đặc trưng như giá trị trung bình (Average), Chuyển vị của độ lệch bình phương trung bình Scm, Hệ số v. Từ đó có thể so sánh loại trừ giá trị nào sai. Nên sử dụng Sub cho việc loại trừ thuận tiện, kết quả còn lại đổ sang cột khác. Các giá trị bị loại được thông báo (ở dạng MsgBox hoặc đánh dấu) để dễ theo dõi.

 

[PhanTuHuong]

Nội dung đầu vào thể hiện ở hình vẽ dưới đây:

Bạn dùng Sub để loại trừ sai số thô, kết quả còn lại thể hiện ở cột bên cạnh. Giá trị loại trừ sẽ đường thông báo dưới dạng Msgbox và cột ngay cạnh.
Để thực hiện công việc này bạn hãy tham khảo mục sau (tất nhiên bạn phải bổ sung 1 số code nữa):

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

 

[PhanTuHuong]

Công việc cuối cùng là xây dựng sheet kết quả (bản in ra). Từ hướng dẫn lập các hàm tự tạo trong Excel và kỹ thuật, bạn có thể tự tạo hàm Delta, Ctc, tang_phi_tc,... (từ đó các bạn mới hiểu mục đích thống kê các giá trị thành 1 cột để làm gì ). Công việc phức tạp nhất là chuyển số liệu đã chỉnh lý sang các cột và hàng định sẵn. Công việc này đã được hướng dẫn ở đường link ở trên.

Kết quả cuối cùng thu được là các giá trị theo trạng thái giới hạn I, II của chỉ tiêu đó.

Hình minh họa:

 

[PhanTuHuong]

Đây là toàn bộ công việc xử lý thống kê kết quả thí nghiệm các mẫu đất (cụ thể là chỉ tiêu sức chống cắt C và phi).
Hy vọng từ những định hướng trên mà các bạn có thể tự làm cho mình 1 "chương trình", đáp ứng công việc trong chuyên môn của mình.
Từ đó bạn có thể nâng cao trình độ về Excel, phát triển ứng dụng sang các bài toán khác.

Chúc các bạn thành công!!!

PS: có vấn đề gì cần trao đổi thì cứ post lên nhé.

 

[PhanTuHuong]

Gửi bucthucuoi!

Em cứ bám theo định hướng này để xây dựng chương trình nhé. Ứng dụng đầu tiên là em có thể tặng bác An.

 

[Nguyễn Duy Tuân]

Anh Hướng làm thống kê trên Excel rất tuyệt! Rất chuyên nghiệp!

 

[bucthucuoi]

Phần mềm free đây!!!
Xin mời mọi người dùng thử và cho ý kiến về chương trình xử lý sức kháng cắt.Chỉ việc nhập giá trị Ti ở sheet cat phang,nếu muốn nhập lại xác suất tin cậy P thì thay đổi ở sheet tra t Chương trình em viết đang còn ở dạng sơ khai, các bác có thể chỉnh lý để được 1 sản phẩm hoàn thiện.

 

[PhanTuHuong]

Tôi đã xem thử, mọi thứ ổn cả. Bây giờ chủ yếu là tối ưu lại công việc như sau:

- Nên ghép phần bảng tra v và tô_an_pha thành 1 sheet.
- Đầu vào số liệu chỉ rõ 1 chút. Nên bổ sung các nút command cho "chuyên nghiệp".
- Sửa lại bảng kết quả cho đẹp, hài hòa để còn đưa ra sản xuất.
- Code chương trình hơi dài. Có thể tách ra thành các thủ tục nhỏ (Sub) cho dễ quản lý. VD:

Sub Me()
....
Call [COLOR="Red"]Con[/COLOR]
....
End Sub

Sub [COLOR="red"]Con[/COLOR]()
....
....
End Sub

Khi Me chạy thì Con cũng hoạt động.

Xem thêm:

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

- Chưa thấy thông báo các giá trị bị loại.
- Đưa số liệu sau khi loại trừ sai số thô ở phần trên cùng mới phù hợp.

 

[bucthucuoi]

Cám ơn thầy rất nhiều.
Em sẽ tìm cách bổ sung thêm.
Việc em bố trí các giá trị Ti còn lại ở dưới cùng vì muốn sử dụng chương trình trong trường hợp số lượng mẫu quá lớn.

 

[PhanTuHuong]

Nếu để giá trị để thống kê ở phía dưới bảng e rằng hơi bị ngược em à.

 

[bucthucuoi]

Hôm nay thi cử rảnh rỗi, em đã sửa đổi lại chương trình:Để bắt đầu nhập số liệu mới press 'Nhap so lieu' để xoá hết số liệu cũ, sau đó 'Ket qua'.
Em cũng rất muốn tách thành nhiều Sub nhưng còn đang lúng túng trong việc sử dụng biến toàn cục.

 

[PhanTuHuong]

To bucthucuoi.

Em nên sửa đoạn này:

Sub xoa()
'xoa man hinh
Sheets("ketqua").Select
Range("A8:O23").ClearContents
Worksheets("cat phang").Select
Range("F5:K47").ClearContents
Range("B546").ClearContents
Range("Q13").ClearContents
Range("Q15").ClearContents
Range("Q17").ClearContents
Range("Q20").ClearContents
Range("Q22").ClearContents
End Sub

Thành (cho ngắn):

Sub xoa()
'xoa man hinh
Sheets("ketqua").Select
Range("A8:O23").ClearContents
Worksheets("cat phang").Range("F5:K47","B546", "Q13", "Q15", "Q17", "Q20", "Q22").ClearContents
End Sub

Ngoài ra nên thiết kế cho đẹp

 

[bucthucuoi]

Trong quá trình rút gọn lại code của chương trình em lại gặp thắc mắc mới:em muốn viết hàm TB(X) tính giá trị trung bình của một mảng, tuy nhiên X lại chỉ có thể khai báo dạng Range.Vậy muốn sử dụng X như 1 mảng thì phải khai báo ntn?

 

[nvson]

Trong quá trình rút gọn lại code của chương trình em lại gặp thắc mắc mới:em muốn viết hàm TB(X) tính giá trị trung bình của một mảng, tuy nhiên X lại chỉ có thể khai báo dạng Range.Vậy muốn sử dụng X như 1 mảng thì phải khai báo ntn?

Từ khoá ParamArray cho phép thủ tục chấp nhận một mảng các tham số truyền vào.
VD:

Public Function trungbinh(ParamArray So())
Dim x, tong
For Each x In So
    i = i + 1
    tong = tong + x
Next x
If i Then trungbinh = tong / i
End Function

Sử dụng: Tại một ô bất kỳ bạn đánh

=trungbinh(1,2,3,4)  -----> sẽ cho kết quả bằng 2.5

 

[phanngoctuan]

ai có tiêu chuẩn 74-1987 thi up lên cho anh em xem với.

 

[nvson]

Hôm nay thi cử rảnh rỗi, em đã sửa đổi lại chương trình:Để bắt đầu nhập số liệu mới press 'Nhap so lieu' để xoá hết số liệu cũ, sau đó 'Ket qua'.
Em cũng rất muốn tách thành nhiều Sub nhưng còn đang lúng túng trong việc sử dụng biến toàn cục.

Góp ý với bạn chút:
Bạn xem lại thuật toán loại trừ sai số thô và các sai số để thỏa mãn cho đặc trưng của đất:
- Loại sai số thô: phải loại giá trị mà trị tuyệt đối của nó và giá trung bình lớn nhất lớn hơn V*σtb, sau đó tính toán lại rồi loại dần.
-Loại sai số: Với sức chống cắt (C, φ) thì hệ số biến đổi V < 0.3 hoặc 0.4 (áp dụng cho đất sét dẻo cứng ~ nửa cứng; than bùn...). Trong chương trình khi loại sai số không thấy bạn đề cập đến vần đề này..
- Các giá trị τ sau khi loại sao lại khác với các giá trị gốc.
Để rút gọn code bạn có thể sử dụng các hàm xác suất thông kê có sẵn trong Excel để tính như: Avedev, DevSQ, stdev, stdevA, stdevP, stdevPA...

 

[PhanTuHuong]

Góp ý với bạn chút:
Bạn xem lại thuật toán loại trừ sai số thô và các sai số để thỏa mãn cho đặc trưng của đất:
- Loại sai số thô: phải loại giá trị mà trị tuyệt đối của nó và giá trung bình lớn nhất lớn hơn V*σtb, sau đó tính toán lại rồi loại dần.
-Loại sai số: Với sức chống cắt (C, φ) thì hệ số biến đổi V < 0.3 hoặc 0.4 (áp dụng cho đất sét dẻo cứng ~ nửa cứng; than bùn...). Trong chương trình khi loại sai số không thấy bạn đề cập đến vần đề này..
- Các giá trị τ sau khi loại sao lại khác với các giá trị gốc.
Để rút gọn code bạn có thể sử dụng các hàm xác suất thông kê có sẵn trong Excel để tính như: Avedev, DevSQ, stdev, stdevA, stdevP, stdevPA...

Loại sai số thô nhẽ ra phải làm 2 vòng, nhưng thường 1 vòng là ổn rồi. Để loại được 1 vài giá trị cũng không dễ dàng gì.

Như Sơn nói thì chuyển sang lĩnh vực phân chia lớp đất dựa theo chỉ tiêu cơ lý. Nếu đúng như thế này thì rất khó, hiện không ai làm cả.
Em thử đọc phần kiểm tra sự đúng đắn khi phân chia đơn nguyên ĐCCT dựa theo hệ số biến đổi V xem sao?

 

[longnh]

Bác Huóng cùng mọi người cho hỏi chút.
Trong 4578 có phần nào tính ra Gama1 Gama2 không nhỉ? em thấy hình như chỉ loại sai số là xong có đúng không?