Bài giảng Ứng dụng excel trong địa chất công trình

Bước 1: Chọn các kiểu phía dƣới. - Để cố định khung theo chiều ngang ở phía trên, chọn hàng ở dƣới nơi bạn muốn đƣờng phân chia xuất hiện. - Để cố định khung theo chiều đứng, chọn ở phía bên phải nơi bạn muốn đƣờng phân chia xuất hiện. - Để cố định cả hai phần phía trên và bên trái, chọn ô ở phía dƣới và phía bên phải nơi bạn muốn đƣờng phân chia xuất hiện (ô R15 trong hình 3.23). Khi đó phạm vi giữa các khối đƣợc phân chia bởi đƣờng thẳng màu đen.  Bước 2: Chọn Menu\Window\Freeze Panes. Kết quả thể hiện nhƣ hình 3.23, cột F: Q, hàng 8:14 đã bị che khuất trong quá trình cuộn bảng tính.  Phục hồi cửa sổ chia cắt: Chọn Menu\Window\Unfreeze Panes. Horizontal Vertical Tiled Cascade Hình 3.21: Cửa sổ ẩn- hiện các cột (hàng) Người biên soạn: Phan Tự Hướng BÀI GIẢNG: Ứng dụng Excel trong Địa chất công trình 29 Hình 3.23: Sử dụng chức năng Freeze Panes g. Các phím tắt trong Excel  Các phím chức năng: Phím chức năng SHIFT CTRL ALT CTRL+ SHIFT ALT+ SHIFT F1 Hiển thị giúp đỡ Chèn sheet biểu đồ Chèn sheet mới F2 Sửa nội dung ô Vào chú thích ô Đặt tên mới (Save as) Lưu F3 Dán tên khối vào công thức Dán hàm vào công thức Định nghĩa khối Tạo tên khối mới F4 Lặp lại (Repeat) Find Next Đóng cửa sổ Thoát F5 Go to Tìm kiếm (Find) Phục hồi kích thước cửa sổ F6 Di chuyển đến phần cửa sổ kế tiếp Di chuyển đến phần cửa sổ trước Di chuyển đến book kế tiếp Di chuyển đến book trước đó F7 Spelling Di chuyển cửa sổ F8 Chọn ô Chèn thêm khối ô chọn khác Thay đổi kích thước cửa sổ Hiển thị hộp Macro F9 Tính toán tất cả các sheet trên tất cả các book mở Tính toán trên worksheet hiện hành Thu workbook thành biểu tượng F10 Chọn thanh thực đơn lệnh Hiển thị thực đơn phím tắt Phóng to cửa sổ F11 Tạo biểu đồ Chèn worksheet mới Hiển thị cửa sổ VBA. F12 Đặt tên mới Lưu Mở In Người biên soạn: Phan Tự Hướng BÀI GIẢNG: Ứng dụng Excel trong Địa chất công trình 30 (Save as)  Sử dụng phím tắt cho nhập dữ liệu: Phím gõ Ý nghĩa Enter Hoàn thành nhập / điều chỉnh dữ liệu trong ô ESC Hủy nhập / điều chỉnh dữ liệu trong ô F4 (Ctr+ Y) Lặp lại hành động cuối cùng Alt+ Enter Bắt đầu hàng mới trong cùng một ô Backspace Xoá ký tự bên trái điểm chèn/ phần chọn Delete Xoá ký tự bên phải điểm chèn/ xóa phần chọn Ctrl+ Delete Xóa đến cuối hàng Phím mũi tên Di chuyển Home Di chuyển về đầu hàng Shift+ F2 Vào chế độ hiệu chỉnh chú thích Ctrl+ Shift+ F3 Tạo các tên từ các nhãn cột và hàng Ctrl+ D Điền ô từ phía trên xuống dưới Ctrl+ R Điền ô từ bên trái sang phải Enter Kết thúc nhập dữ liệu vào ô và di chuyển ô xuống dưới Shift+ Enter Kết thúc nhập dữ liệu vào ô và di chuyển ô lên trên Tab Kết thúc nhập dữ liệu vào ô và di chuyển ô sang phải Shift+ Tab Kết thúc nhập dữ liệu vào ô và di chuyển ô sang trái  Sử dụng phím tắt cho chèn, xoá và sao chép dữ liệu: Phím gõ Ý nghĩa Ctrl+ C Sao chép vào vùng đệm của bộ nhớ Ctrl+ Y Dán chọn lựa từ vùng đệm của bộ nhớ vào vị trí tương ứng Ctrl+ X Cắt (di chuyển) dữ liệu vào vùng đệm của bộ nhớ Delete Xoá nội dung của các ô chọn Ctrl+ Z Huỷ bỏ hành động cuối cùng Người biên soạn: Phan Tự Hướng BÀI GIẢNG: Ứng dụng Excel trong Địa chất công trình 31 BÀI 4: SỬ DỤNG CÁC HÀM TRONG EXCEL Excel có nhiều hàm đã đƣợc định nghĩa trƣớc phục vụ nhiều dạng công việc khác nhau, hoặc có thể tự tạo ra phục vụ mục đích chuyên môn nào đó. Hàm có thể đƣợc sử dụng để thực hiện những tính toán đơn giản hay phức tạp. Trong Excel có danh sách các loại hàm sau:  Most Recently Used : Các hàm vừa mới sử dụng.  All : Tất cả các hàm.  Financial : Hàm về tài chính.  Date & Time : Hàm về ngày và giờ.  Math & trig : Hàm về toán học và lƣợng giác.  Statistical : Hàm về thống kê.  LookUp & Reference : Hàm về tìm kiếm, tham chiếu số liệu trong cơ sở dữ liệu (CSDL).  DataBase : Hàm về CSDL.  Text : Hàm về xử lý chuỗi dữ liệu.  Logic : Hàm về toán tử logic.  Information : Hàm về thông tin.  User Defined : Hàm do ngƣời sử dụng, xây dựng trong VBA. Hình 4.1: Danh sách các hàm đã được lập sẵn. Người biên soạn: Phan Tự Hướng BÀI GIẢNG: Ứng dụng Excel trong Địa chất công trình 32 Một công thức đơn giản đƣợc ký hiệu nhƣ sau: " =Tên_hàm(dữliệu1,dữliệu2, dữliệu 3,...)". Tên hàm đƣợc lập sẵn bằng cách chọn Insert\Function, khi đó danh sách các hàm sẽ hiện ra trong Paste Function (hình 4.1). Giữa tên hàm, các đối số không đƣợc có khoảng trống, nếu không sẽ bị báo lỗi. Dữ liệu trong hàm có thể là một số, chuỗi, địa chỉ một ô hoặc nhiều ô tham chiếu trong Excel. Ngoài ra, một số hàm còn có khả năng lồng vào nhau. Vídụ: =AVERAGE(2,3,5,8,C3:C5); =IF(C2>=8,"Giỏi",IF(A2>=6,"Khá","Trung bình")). Tùy theo yêu cầu công việc mỗi ngƣời mà phạm vi sử dụng các hàm khác nhau. Dƣới đây là một số hàm thƣờng hay sử dụng trong lĩnh vực kỹ thuật: 1. HÀM MATH & TRIG a. SUM(number1, number2,...) Hàm Sum tính tổng số của tất cả các tham số number1, number2,... Các tham số có thể là số, địa chỉ ô hoặc phạm vi khối ô. Ví dụ ở hình 4.2. b. ROUND(number, n) Hàm Round làm tròn số number theo con số n. Giá trị n đƣợc quy định nhƣ sau: - Nếu n= 0: Làm tròn đến hàng đơn vị hoặc lấy 0 số lẻ. - Nếu n= 1: Làm tròn lấy 1 số lẻ. - Nếu n= 2: Làm tròn đến 2 số lẻ,... - Nếu n= -1: Làm tròn đến hàng chục. - Nếu n= -2: Làm tròn đến hàng trăm,... Ví dụ: =Round(1265.263,1) bằng 1265.3; =Round(1265.263,0) bằng 1265; =Round(1265.263,-1) bằng 1270. Hình 4.2: Ví dụ về hàm tính tổng. Người biên soạn: Phan Tự Hướng BÀI GIẢNG: Ứng dụng Excel trong Địa chất công trình 33 Hàm Round thƣờng đƣợc sử dụng để làm tròn khi tính toán các chỉ tiêu cơ lý. Ví dụ quy định chỉ tiêu khối lƣợng thể tích tự nhiên, khối lƣợng riêng, khối lƣợng thể tích khô, độ sệt làm tròn sau giá trị thập phân 2 số. Chỉ tiêu độ ẩm (tự nhiên, chảy, dẻo), chỉ số dẻo, độ lỗ rỗng, độ bão hoà làm tròn sau giá trị thập phân 1 số. Chỉ tiêu hệ số rỗng, lực dính kết, hệ số nén lún làm tròn sau giá trị thập phân 2 số. c. INT(number) Hàm Int lấy phần nguyên của number, phần thập phân bị cắt bỏ. Ví dụ: =Int(26.63) bằng 26. d. MOD(number,divisor) Hàm Mod lấy phần dƣ của number chia cho divisor. Ví dụ: =Mod(12.3,6) bằng 0.3; =Mod(26,5) bằng 1. e. SQRT(number) Hàm Sqrt lấy giá trị căn bậc hai của number. Ví dụ: =Sqrt(81) bằng 9; =Sqrt(18) bằng 4.242. f. POWER(number, power) Hàm Power lấy giá trị mũ bậc power của number. Ví dụ: =Power(10,2) bằng 100; =Power(10,4) bằng 10000. g. LOG(number, base) Hàm Log lấy giá trị logbase number. Ví dụ: =Log(9,3) bằng 2; =Log(15,4) bằng 1.95. h. DEGREES(angle) Hàm Degrees cho giá trị chuyển đổi góc angle từ radian sang độ. Ví dụ: =Degrees(Pi()) bằng 180 độ; =Degrees(1) bằng 57.3 độ. i. RADIANS(angle) Hàm Radians cho giá trị chuyển đổi góc angle từ độ sang radian. Ví dụ: =Radians(180) bằng 3.141593 radian; =Radians(90) bằng 1.57 radian. Người biên soạn: Phan Tự Hướng BÀI GIẢNG: Ứng dụng Excel trong Địa chất công trình 34 j. Các hàm về lượng giác: SIN, COS, ASIN, ACOS, TAN, ATAN(number) Xác định giá trị sine, cosine, arcsine, arccosine, tangent, arctangent của góc number. Góc number là góc đƣợc tính bằng radian (không tính bằng độ). Ví dụ: =Sin(0) bằng 0; =Sin(0.52359) bằng 0.5; =Tan(0.7855) bằng 1. Hình 4.3: Ví dụ về sử dụng một số hàm 2. HÀM STATISTICAL a. AVERAGE(number1, number2,...) Hàm Average tính giá trị trung bình số học các tham số. Ví dụ: =Average(11,3,5,6,9) bằng 6.8. b. MAX(number1, number2,...) Hàm Max lấy giá trị lớn nhất trong danh sách các tham số. Ví dụ: =Max(11,3,5,6,9) bằng 11. c. MIN(number1, number2,...) Hàm Min lấy giá trị nhỏ nhất trong danh sách các tham số. Ví dụ: =Min(11,3,5,6,9) bằng 3. d. COUNT(range) Hàm Count tính tổng các ô có chứa dữ liệu kiểu số trong range. Người biên soạn: Phan Tự Hướng BÀI GIẢNG: Ứng dụng Excel trong Địa chất công trình 35 e. LARGE(range,k) Hàm Large xác định phần tử lớn thứ k trong range. f. MODE(range) Hàm Mode xác định phần tử hay gặp nhất trong range. g. RANK(number,range,option) Hàm Rank xác định thứ hạng của number trong danh sách range. - Xếp giảm dần: khi không có giá trị option hoặc option bằng 0. - Xếp tăng dần: khi giá trị option lớn hơn 0. h. VAR(range) Hàm Var xác định phƣơng sai của của vùng range. Công thức của hàm Var nhƣ sau: S 2 = VAR = 2)( 1 1 xx n i    i. STDEVP(range) Hàm Stdevp xác định chuyển vị của độ lệch bình phƣơng trung bình của vùng range. Công thức của hàm Stdevp nhƣ sau: SCM = STDEVP =    n i i xx n 1 2)( 1 k. STDEV(range) Hàm Stdev xác định độ lệch bình phƣơng trung bình của vùng range. Công thức của hàm Stdev nhƣ sau: S = STDEV=     n i i xx n 1 2)( 1 1 l. COVAR(array1,array2) Hàm Covar xác định hiệp phƣơng sai của hai mảng array1, array2. Hiệp phƣơng sai là giá trị trung bình tích số của độ lệch từng cặp giá trị tƣơng ứng trong hai mảng array1, array2. Công thức của hàm Covar nhƣ sau: Người biên soạn: Phan Tự Hướng BÀI GIẢNG: Ứng dụng Excel trong Địa chất công trình 36 Covar(X,Y) = )()( 1 yyxx n ii  Hình 4.4: Ví dụ về sử dụng một số hàm thống kê Ghi chú: Các hàm thống kê trong Excel có thể thực hiện tốt công tác xử lý thống kê chỉ tiêu cơ lý các mẫu đất đá. Tuy nhiên, công việc loại bỏ sai số thô không thực hiện đƣợc, chỉ có thể đánh dấu. 3. HÀM TEXT a. CONCATENATE(text1, text2,...) Nối các đoạn dữ liệu text1, text2,... thành một dữ liệu. Ví dụ: =CONCATENATE("Bộ môn"," địa chất"," công trình") cho kết quả "Bộ môn địa chất công trình". Hiện nay thƣờng sử dụng ký tự & để nối các chuỗi với nhau, kết quả giống nhƣ hàm trên. Ví dụ trên thể hiện nhƣ sau: ="Bộ môn"&" địa chất"&" công trình"). b. LEFT(text, num_chars) Lấy một đoạn dữ liệu tính từ đầu bên trái dài num_chars ký tự của text. Ví dụ: =LEFT("Giới hạn",4) cho kết quả "Giới"; =LEFT("Giới hạn",6) cho kết quả "Giới h". Người biên soạn: Phan Tự Hướng BÀI GIẢNG: Ứng dụng Excel trong Địa chất công trình 37 c. RIGHT(text, num_chars) Lấy một đoạn dữ liệu tính từ đầu bên phải dài num_chars ký tự của text. Ví dụ: =RIGHT("Giới hạn",3) cho kết quả "hạn"; =RIGHT("Giới hạn",5) cho kết quả "i hạn". d. UPPER(text) Biến đổi tất cả chữ thành chữ hoa. Tuy nhiên, chữ tiếng Việt thì vẫn giữ nguyên. Ví dụ: =UPPER("tiếNg Việt") cho kết quả"TIếNG VIệT". e. REPT(text, number_times) Lặp lại number_times lần dữ liệu text. Ví dụ: =REPT("Đá",5) cho kết quả "ĐáĐáĐáĐáĐá". f. TRIM(text) Xoá bỏ ký tự trống trong text, chỉ để lại 1 khoảng trống (tab) giữa các chữ. Ví dụ: =TRIM(" Microsoft Excel 2003") cho kết quả "Microsoft Excel 2003". 4. HÀM LOGIC a. IF(logical_text, value_if_true, value_if_false) Trả về value_if_true nếu điều kiện đúng (True) hoặc value_if_false nếu điều kiện sai (False). Trong đó logical_text là biểu thức logic. Các hàm if có thể lồng nhau đến 7 cấp. Ví dụ: =IF(7>5,10,20) cho kết quả là 10. b. AND(logical1, logical2,...) Trả về True nếu tất cả các tham số của nó có giá trị là đúng, trả về False nếu có ít nhất một tham số có giá trị sai. Hàm And thƣờng đƣợc lồng với hàm if. Ví dụ: =IF(AND(3>4,4=4,1<8),"Đúng","Sai") cho kết quả "Sai"; =IF(AND(3<4,4=4,1<8),"Đúng","Sai") cho kết quả "Đúng". c. OR(logical1, logical2,...) Trả về True nếu có ít nhất một tham số của nó có giá trị là đúng, trả về False nếu tất cả các tham số có giá trị sai. Hàm Or thƣờng đƣợc lồng với hàm if. Người biên soạn: Phan Tự Hướng BÀI GIẢNG: Ứng dụng Excel trong Địa chất công trình 38 Ví dụ: =IF(OR(3>4,4=4,1<8),"Đúng","Sai") cho kết quả "Đúng"; =IF(OR (3>4,4>4,1>8),"Đúng","Sai") cho kết quả "Sai". 5. HÀM LOOKUP & REFERENCE a. LOOKUP Hàm LOOKUP dùng để dò tìm một giá trị trong một dòng, một cột hoặc trong một mảng các giá trị. Hàm LOOKUP có hai hình thức là dạng vector và dạng array:  Dạng Vector: LOOKUP(lookup_value,lookup_vector,result_vector) - Lookup_value: là giá trị sẽ đƣợc tìm kiếm trong vùng Lookup_vector. Lookup_value có thể là một số, một chuỗi hay một tham chiếu. - Lookup_vector: là vùng dò tìm, vùng này có thể là một dòng hay một cột. Giá trị chứa trong vùng có thể là số, chuỗi. - Result_vector: là vùng chứa giá trị trả về (1 cột hoặc 1 dòng). Độ lớn của Result_vector phải tƣơng ứng với Lookup_vector. Lƣu ý: • Lookup_vector phải đƣợc sắp xếp theo thứ tự tăng dần nhƣ: -2,-1,0,1,2; A,B,C..Z; FALSE, TRUE. Nếu không, kết quả trả về nhiều khi không chính xác. • Nếu nhƣ không tìm thấy Lookup_value trong vùng dò tìm, hàm sẽ lấy giá trị lớn nhất có trong vùng dò tìm (Lookup_vector) mà có giá trị nhỏ hơn hoặc bằng giá trị dò tìm Lookup_vector. • Nếu giá trị nhỏ nhất trong vùng dò tìm Lookup_vector mà lớn hơn giá trị của lookup_value thì hàm báo lỗi #N/A.  Dạng mảng: LOOKUP(lookup_value,array) - Lookup_value: là giá trị sẽ đƣợc tìm kiếm trong mảng Array. Lookup_value có thể là một số, một chuỗi hay một tham chiếu. - Array: là vùng tìm kiếm, có thể là một vùng nhiều ô hay một mảng. Giá trị chứa trong array có thể là số, chuỗi, giá trị logic. Lƣu ý: • Nếu nhƣ không tìm thấy Lookup_value trong vùng dò tìm, hàm sẽ lấy giá trị lớn nhất có trong vùng dò tìm (Lookup_vector) mà có giá trị nhỏ hơn hoặc bằng giá trị dò tìm Lookup_vector. Người biên soạn: Phan Tự Hướng BÀI GIẢNG: Ứng dụng Excel trong Địa chất công trình 39 • Nếu Lookup_value nhỏ hơn giá trị nhỏ nhất trong cột hoặc hàng đầu tiên trong array thì hàm sẽ báo lỗi #NA!. • Hàm Lookup dạng mảng gần giống với hàm Hlookup và Vlookup. Điểm khác biệt là hàm Vlookup hay hàm Hlookup tìm kiếm trên dòng (hoặc cột) đầu tiên, còn hàm Lookup thì tìm kiếm tùy thuộc vào kích thƣớc của mảng. Nếu mảng (array) có số cột nhiều hơn số dòng thì hàm sẽ tìm trên dòng đầu tiên của mảng và ngƣợc lại. Nếu mảng có số cột bằng số dòng thì hàm sẽ tìm trên cột đầu tiên của mảng. • Hàm lookup luôn trả về giá trị ở cột hoặc dòng cuối cùng trong mảng. • Các giá trị ở dòng hoặc cột đầu tiên trong mảng phải đƣợc sắp xếp theo thứ tự tăng dần, nếu không kết quả trả về nhiều khi không chính xác. Hình 4.5: Ví dụ về sử dụng một số hàm tìm kiếm trong xác suất thống kê b. VLOOKUP Cú phápVLOOKUP(lookup_value,table_array,col_index_num,option_lookup) Hàm VLOOKUP là hàm dò tìm theo cột, sẽ trả về giá trị của một ô nằm trên một cột nào đó nếu thỏa mãn điều kiện dò tìm. Người biên soạn: Phan Tự Hướng BÀI GIẢNG: Ứng dụng Excel trong Địa chất công trình 40 - Lookup_value: là giá trị dùng để dò tìm, giá trị này sẽ đƣợc dò tìm trong cột đầu tiên của bảng dữ liệu dò tìm. Giá trị dò tìm có thể là một số, một chuỗi, một công thức trả về giá trị hay một tham chiếu đến một ô nào đó dùng làm giá trị dò tìm. - Table_array: là bảng dùng để dò tìm, bảng dò tìm có thể là tham chiếu đến một vùng nào đó hay Name trả về vùng dò tìm. Bảng dò tìm gồm có Rj hàng và Ci cột (i, j lớn hơn 1), trong đó cột thứ nhất của bảng dò tìm sẽ đƣợc dùng để dò tìm. - Col_index_num: là số thứ tự của cột (tính từ trái qua phải) trong bảng dò tìm chứa giá trị mà ta muốn trả về. Col_index_num phải lớn hơn hoặc bằng 1 và nhỏ hơn hoặc bằng số cột lớn nhất có trong bảng dò tìm. - Option_lookup: là tùy chọn xác định kiểu dò tìm, có 2 kiểu dò tìm: • True hoặc 1 (hoặc để trống): là kiểu dò tìm tƣơng đối, hàm sẽ lấy giá trị đầu tiên mà nó tìm đƣợc trên cột đầu tiên trong bảng dò tìm. Trong trƣờng hợp tìm không thấy, nó sẽ lấy giá trị lớn nhất mà có giá trị nhỏ hơn giá trị dò tìm. • False hoặc 0: là kiểu dò tìm chính xác, hàm sẽ lấy giá trị đầu tiên mà nó tìm đƣợc trên cột đầu tiên trong bảng dò tìm. Trong trƣờng hợp tìm không thấy, hàm sẽ trả về #N/A. c. Hàm HLOOKUP Cú pháp HLOOKUP(lookup_value,table_array,row_index_num,option_lookup) Hàm HLOOKUP là hàm dò tìm theo dòng, sẽ trả về giá trị của một ô nằm trên một dòng nào đó nếu thỏa mãn điều kiện dò tìm. - Lookup_value: là giá trị dùng để dò tìm, giá trị này sẽ đƣợc dò tìm trong dòng đầu tiên của bảng dữ liệu dò tìm. Giá trị dò tìm có thể là một số, một chuỗi, một công thức trả về giá trị hay một tham chiếu đến một ô nào đó dùng làm giá trị dò tìm. - Table_array: là bảng dùng để dò tìm, bảng dò tìm có thể là tham chiếu đến một vùng nào đó hay Name trả về vùng dò tìm. Bảng dò tìm gồm có Rj hàng và Ci cột (i,j >=1), trong đó dòng thứ nhất của bảng dò tìm sẽ đƣợc dùng để dò tìm. - Row_index_num: là số thứ tự của dòng (tính từ trên xuống dƣới) trong bảng dò tìm chứa giá trị mà ta muốn trả về. Row_index_num phải phải lớn hơn hoặc bằng 1 và nhỏ hơn hoặc bằng số cột lớn nhất có trong bảng dò tìm, ngƣợc lại hàm sẽ trả về #VALUE! hoặc #REF. Người biên soạn: Phan Tự Hướng BÀI GIẢNG: Ứng dụng Excel trong Địa chất công trình 41 - Option_lookup: là tùy chọn xác định kiểu dò tìm, có 2 kiểu dò tìm: • True hoặc 1 (hoặc để trống): là kiểu dò tìm tƣơng đối, hàm sẽ lấy giá trị đầu tiên mà nó tìm đƣợc trên cột đầu tiên trong bảng dò tìm. Trong trƣờng hợp tìm không thấy, nó sẽ lấy giá trị lớn nhất mà có giá trị nhỏ hơn giá trị dò tìm. • False hoặc 0: là kiểu dò tìm chính xác, sẽ lấy giá trị đầu tiên mà nó tìm đƣợc trên cột đầu tiên trong bảng dò tìm. Nếu tìm không thấy, hàm sẽ trả về #N/A. Hình 4.6: Biểu đồ thí nghiệm nén một trục có sử dụng hàm Hlookup Ví dụ: Biểu đồ thí nghiệm nén một trục có dạng nhƣ hình 4.6. Chỉ cần một số phép tính toán là dễ dàng xây dựng đƣợc biểu đồ này. Vấn đề khó nhất tra số hiệu chỉnh từng cấp áp lực (áp lực thay đổi phụ thuộc vào độ cứng của đất) của từng máy nén. Có thể sử dụng hàm Hlookup ở trên để tìm hệ số hiệu chỉnh đó. Trong hình 4.7, có 14 máy nén, tƣơng ứng với nó bảng tra hệ số hiệu chỉnh ứng với các cấp áp lực nén khác nhau. Hàm Hlookup tại ô E4 gồm các thông tin sau: - Lookup_value: là giá trị dùng để dò tìm là cấp áp lực nén (E19). - Table_array: là bảng dùng để dò tìm là dữ liệu gốc, phải chứa toàn bộ số hiệu máy nén, cấp áp lực nén (vùng A2:G16). Người biên soạn: Phan Tự Hướng BÀI GIẢNG: Ứng dụng Excel trong Địa chất công trình 42 - Row_index_num: là số thứ tự của máy nén (phải xắp sếp tăng liên tục, nếu khoôg sẽ bị sai), trƣờng hợp này cộng thêm 1 hàng vì hàng bắt đầu tính từ hàng 2 (A20+1). Nếu số hiệu máy nén không phải là số, sử dụng hàm Index để xác định số thứ tự máy nén trong danh sách dò tìm. - Option_lookup: chọn True Hình 4.7: Áp dụng hàm Hlookup để tra hệ số hiệu chỉnh máy nén 1 trục 6. HÀM USER DEFINED Cho dù đã xây dựng nhiều loại hàm khác nhau trong nhiều lĩnh vực, nhƣng không thể đáp ứng nhu cầu đa dạng của ngƣời sử dụng Excel. Việc xây dựng hàm riêng là cần thiết, nếu chỉ sử dụng hàm và công thức sẵn có trong Excel thì quá trình tính toán trải qua nhiều bƣớc trung gian, khó kiểm soát, dễ mắc lỗi, thậm chí một số hàm khó có thể thực hiện đƣợc theo phƣơng pháp thủ công. Trong lĩnh vực ĐCCT, có thể phát sinh những hàm khó có thể giải quyết một cách đơn thuần nhƣ sau: - Hàm tính giá trị trung bình của góc tính theo độ - phút: Theo định dạng tại hình 3.10, việc tính toán giá trị trung bình bắt buộc phải sử dụng các ô phụ. - Hàm tính sức chịu tải quy ƣớc R0 của đất: phụ thuộc lực dính kết, góc ma sát trong (tra bảng hoặc có công thức tính), khối lƣợng thể tích tự nhiên,... - Hàm tính mô đun tổng biến dạng E0: phụ thuộc vào loại đất (tra bảng), hệ số rỗng, trạng thái,... Người biên soạn: Phan Tự Hướng BÀI GIẢNG: Ứng dụng Excel trong Địa chất công trình 43 - Hàm nội suy tuyến tính một chiều, hai chiều: Có nhiều bài toán liên quan nhƣ xử lý thống kê, xác định môđun tổng biến dạng E0, hệ số K khi tính toán ứng suất dƣới đáy móng, xác định vùng biến dạng dẻo,... đều phải sử dụng đến bài toán nội suy tuyến tính. - Hàm tính toán giá trị tiêu chuẩn, độ lệnh bình phƣơng trung bình sau khi thống kê: tức là hàm xác định giá trị trung bình sau khi đã loại trừ sai số thô,... Nhờ xây dựng trên ngôn ngữ lập trình Visual Basic for Applications (VBA) sẵn có trong Excel, hàm riêng đó có thể đáp ứng phần lớn những công việc trên. Hình 4.8: Hàm Eo được xây dựng trong VBA Hình 4.9: Thủ tục xử lý thống kê xây dựng trong VBA Người biên soạn: Phan Tự Hướng BÀI GIẢNG: Ứng dụng Excel trong Địa chất công trình 44 Đƣờng link giáo trình Công thức và hàm Excel, kiến thức cơ bản VBA trong Excel: Hình 4.10: Công thức và hàm Excel, kiến thức cơ bản VBA trong Excel Đƣờng link giáo trình Tự động hóa công tác thiết kế công trình giao thông do các Thầy ở Bộ môn Tự động hóa - Trƣờng ĐH GTVT biên soạn và cung cấp miễn phí: Ngoài ra, có rất nhiều trang web trong và ngoài nƣớc chuyên thảo luận về ứng dụng của Excel và kiến thức về lập trình VBA, VSTO, VSTA để các bạn tìm hiểu và nâng cao kiến thức về Excel cho mình. 7. TÌM HIỂU CÁC LỖI TRẢ VỀ BỞI CÔNG THỨC a. Lỗi ##### Nguyên nhân do kết quả số tính bởi công thức quá lớn so với độ rộng của cột. Biện pháp khắc phục: mở rộng cột hoặc thay đổi dạng thức biểu diễn của số. b. Lỗi #DIV/0! Nguyên nhân do công thức chia cho số 0. c. Lỗi #NAME? Nguyên nhân Excel không thừa nhận chuỗi trong công thức. Lỗi này thƣờng gặp khi khai báo tên hàm sai, tên phạm vi khối ô sai, khi nhập chuỗi trong công thức mà không đặt trong 2 dấu nháy kép "", khi bỏ sót dấu hai chấm ":" trong phạm vi khối ô. Người biên soạn: Phan Tự Hướng BÀI GIẢNG: Ứng dụng Excel trong Địa chất công trình 45 d. Lỗi #NULL! Lỗi này xuất hiện khi bạn ghi rõ sự giao nhau của 2 vùng mà chúng không giao nhau. Lỗi này xảy ra khi sử dụng toán tử giao nhau (thƣờng là khoảng trắng). e. Lỗi #NUM! Lỗi #NUM! xuất hiện khi có vấn đề với số trong công thức hoặc hàm. Lỗi này xảy ra khi khai báo tham số số không thể chấp nhận đƣợc. f. Lỗi #REF! Lỗi #REF! xuất hiện khi địa chỉ ô không hợp lệ. Lỗi này thƣờng xảy ra khi xoá ô đƣợc tham chiếu bởi công thức khác, hoặc dán các ô đƣợc di chuyển lên các ô đƣợc tham chiếu bởi các công thức khác,... g. Lỗi #VALUE! Lỗi #VALUE! khi kiểu tham số hoặc toán hạng đƣợc sử dụng không đúng. Lỗi này xảy ra khi nhập chuỗi mà công thức yêu cầu là số hoặc giá trị logic (True, False). Người biên soạn: Phan Tự Hướng BÀI GIẢNG: Ứng dụng Excel trong Địa chất công trình 46 BÀI 5: BIỂU ĐỒ TRONG EXCEL Trong Excel, ngoài việc biểu diễn dữ liệu dƣới dạng những con số, bạn có thể biểu diễn chúng dƣới dạng biểu đồ nhƣ toạ độ, đƣờng thẳng, hình bánh, hình trụ,... Từ các biểu đồ đó bạn có thể đánh giá đƣợc một cách tổng quát và dễ dàng so sánh giữa các giá trị, gây đƣợc sự cảm nhận và thuyết phục ngƣời đọc. Trong lĩnh vực ĐCCT, việc ứng dụng biểu đồ có ý nghĩa hết sức quan trọng. Dùng biểu đồ trong Excel có thể thể hiện đƣợc nhiều công việc nhƣ vẽ biểu đồ cắt- nén, thành phần hạt, xuyên tĩnh, xuyên tiêu chuẩn, biểu đồ nén tĩnh cọc, nén tĩnh nền, cắt cánh, nén ngang,... 1. TẠO BIỂU ĐỒ Với chức năng Chart Wizard, Excel giúp bạn tạo ra một biểu đồ qua nhiều bƣớc thực hiện và trong mỗi bƣớc đó bạn có thể thay đổi những tuỳ chọn hoặc khai báo thêm các thông số sao cho phù hợp với yêu cầu đề ra. Các bƣớc thực hiện nhƣ sau: a. Chọn các ô chứa dữ liệu cần vẽ biểu đồ Ví dụ dƣới đây yêu cầu vẽ biểu đồ thí nghiệm thành phần hạt, trục hành thể hiện đƣờng kính hạt tính theo logarit cơ số 10, trục tung thể hiện phần trăm tích luỹ. Đầu tiên chọn các ô N10:N12 (hình 5.1). Dòng đầu tiên tƣơng ứng kích thƣớc nhóm hạt, hàng thứ hai tƣơng ứng phần trăm tích luỹ, hàng thứ ba là hàm lƣợng tƣơng ứng các nhóm hạt. Trong ô thể hiện kích thƣớc nhóm hạt, tôi đã dùng phƣơng pháp mẹo định dạng để biến ô giá trị kích thƣớc hạt cụ thể (ví dụ 0.5mm) thành nhóm hạt (thành 1.0- 0.5mm). Điều này là cần thiết để Excel có thể hiển thị biểu đồ thành phần hạt theo ý muốn (phải là số chứ không phải là là chuỗi). b. Bấm nút Chart Wizard trên thanh Standard (hình 1.4) hoặc vào Menu\Insert\Chart Xuất hiện hộp lệnh Chart Wizard (hình 5.2). Trong Chart type có nhiều loại biểu đồ, bên cạnh đó là các biểu đồ con Chart sub-type. Do nhu cầu công việc cũng nhƣ thời gian có hạn nên chỉ giới thiệu biểu đồ ứng dụng quan trọng nhất là XY (Scatter). Người biên soạn: Phan Tự Hướng BÀI GIẢNG: Ứng dụng Excel trong Địa chất công trình 47 Bạn thực hiện 4 bƣớc thiết lập tạo biểu đồ nhƣ sau:  Bước 1: Chọn kiểu biểu đồ cần vẽ- XY (Scatter). Sau đó ấn Next.  Bước 2: Kiểm tra lại dữ liệu cần vẽ. Có hai cửa sổ lựa chọn: - Data Range: kiểm tra lại vùng dữ liệu đã vào đúng chƣa? Nếu có sự thay đổi thì khai báo lại trong Data Range (hình 5.3a). - Series: Bạn có thể kiểm tra, sửa tên hoặc bổ sung tên của biểu đồ, tên trục X,Y, thêm, bớt dữ liệu (hình 5.3b). Sau đó ấn phím Next. Hình 5.1: Đầu vào ban đầu của biểu đồ thành phần hạt Người biên soạn: Phan Tự Hướng BÀI GIẢNG: Ứng dụng Excel trong Địa chất công trình 48 Hình 5.2: Bước thứ nhất của vẽ biểu đồ Hình 5.