Bài giảng Ứng dụng Excel trong địa chất công trình - Phan Tự Hướng

76 trang ngocly 3080

Download

Bạn đang xem 20 trang mẫu của tài liệu "Bài giảng Ứng dụng Excel trong địa chất công trình - Phan Tự Hướng", để tải tài liệu gốc về máy bạn click vào nút DOWNLOAD ở trên

Tài liệu đính kèm:

bai_giang_ung_dung_excel_trong_dia_chat_cong_trinh_phan_tu_h.pdf

Nội dung text: Bài giảng Ứng dụng Excel trong địa chất công trình - Phan Tự Hướng

BÀI GIẢNG ỨNG DỤNG EXCEL TRONG ĐỊA CHẤT CÔNG TRÌNH Người biên soạn: ThS. Phan Tự Hướng Hà Nội, 2009
Người biên soạn: Phan Tự Hướng GIỚI THIỆU Trong lĩnh vực Địa chất công trình - Địa kỹ thuật (ĐCCT - ĐKT), chúng ta sẽ gặp một số bài toán nhƣ tính toán, xây dựng các biểu đồ thí nghiệm trong phòng (thí nghiệm cắt, nén một trục, thành phần hạt, nén cố kết, cắt ba trục, ), xử lý thống kê kết quả thí nghiệm trong phòng, vẽ biểu đồ thí nghiệm hiện trƣờng (xuyên tĩnh, nén tĩnh nền, nén tĩnh cọc, thí nghiệm nén ngang, cắt cánh, ), bảng tổng hợp chỉ tiêu cơ lý đất đá, chƣơng trình tính toán nền móng, Với sự phát triển mạnh mẽ của công nghệ thông tin, những bài toán trên có thể đƣợc giải quyết một cách nhanh chóng, chính xác bằng những công cụ ứng dụng. Đó là phần mềm Excel. Phần mềm Excel có bảng tính linh hoạt cùng với biểu đồ sẵn có giúp chúng ta giải quyết nhiều bài toán về thí nghiệm trong phòng, ngoài trời, tính toán nền móng, Excel còn xây dựng sẵn các dạng tƣơng quan cùng với phƣơng trình tƣơng quan, phục vụ cho công việc xây dựng mối tƣơng quan giữa các đại lƣợng. Các hàm thống kê sẵn có trong Excel giúp chúng ta xác định các đặc trƣng thống kê của đất đá. Tuy nhiên, nếu chỉ sử dụng đơn thuần các công thức trong Excel thì nhiều bài toán không thể giải quyết một cách hoàn chỉnh đƣợc. Để có thể giải quyết đƣợc các bài toán trên một cách hoàn toàn tự động, chúng ta phải sử dụng ngôn ngữ lập trình ứng dụng Visual Basic for Applications (VBA) trong Excel. VBA giúp chúng ta lập các hàm tuỳ biến và thủ tục để giải quyết một vấn đề nào đó. Hiện nay, sinh viên những khoá đại cƣơng đƣợc trang bị kiến thức về Visual Basic, đó là nền tảng để phát triển ngôn ngữ VBA. VBA là ngôn ngữ dễ giao tiếp, thân thiện với ngƣời sử dụng. Không chỉ riêng với ngành kỹ thuật, VBA có ứng dụng rất hiệu quả đối với ngành kinh tế, tài chính, kế toán, xây dựng, Trong đợt tái bản này, tôi đã bổ sung thêm kiến thức về Excel nói chung, liên quan đến lĩnh vực ĐCCT - ĐKT nói riêng. Hy vọng đây là tài liệu bổ ích cho các bạn sinh viên, kỹ sƣ ĐCCT - ĐKT trong công tác chuyên môn của mình. Mọi góp ý, bổ sung xin liên hệ theo email: tuhuongdcct36@gmail.com. BÀI GIẢNG: Ứng dụng Excel trong Địa chất công trình 2
Người biên soạn: Phan Tự Hướng BÀI 1: MỞ ĐẦU Phần mềm Excel chủ yếu dùng để tính toán, xây dựng các biểu đồ thí nghiệm đất đá trong phòng, hiện trƣờng, Đây là chƣơng trình có bảng tính mạnh nhất hiện nay, cho phép thực hiện nhiều công việc khác nhau. Mục đích của bài giảng là trang bị cho sinh viên những kiến thức cơ bản về phần mềm Excel, giúp sinh viên có thể sử dụng ngay khi viết đồ án môn học, đồ án tốt nghiệp và tham gia sản xuất sau này. Chƣơng trình học là 2003 và hiện nay đã có Excel 2010. Về cơ bản, từ Excel 97 đến 2003 không có nhiều thay đổi. Excel 2007 đánh dấu sự thay đổi mạnh mẽ, thể hiện sự phát triển vƣợt bậc của Excel. Các điều khiển menu ở các phiên bản trƣớc đƣợc thay đổi bằng Ribbon tiện lợi hơn, bảng tính có kích thƣớc lớn hơn, các chức năng phong phú, đa dạng, dung lƣợng file giảm đi nhiều, Phạm vi bài giảng này đề cập đến Excel 2003, một số chức năng có thay đổi trong các phiên bản khác nhau. Hình 1.1: Biểu tượng Excel 2007 trên Destop Titler bar Menu bar Standard Toolbar Formular bar Formatting Toolbar Column heading Active cell Worksheet Row heading Sheet name Hình 1.2: Cửa sổ Excel BÀI GIẢNG: Ứng dụng Excel trong Địa chất công trình 3
Người biên soạn: Phan Tự Hướng 1. KHỞI ĐỘNG EXCEL Nháy kép vào biểu tƣợng Excel trên màn hình (hình 1.1), cửa sổ phần mềm Excel sẽ hiện ra (hình 1.2). 2. CỬA SỔ ỨNG DỤNG CỦA EXCEL Cửa sổ Excel bao gồm các thành phần sau: a. Thanh tiêu đề (Title bar) Biểu tƣợng Excel và tên file (Book1.xls). b. Thực đơn lệnh (Menu bar) Bao gồm các menu lệnh, trong mỗi menu có nhiều các menu con (hình 1.3). Hình 1.3: Thanh Menu Hình 1.4: Thanh công cụ chuẩn (Standard Toolbar) Hình 1.5: Thanh định dạng (Formatting Toolbar) c. Các thanh công cụ (Toolbars) Toolbar là các thanh công cụ, trên mỗi thanh này có nhiều nút lệnh, giúp bạn thực hiện nhanh các chức năng lệnh thay vì thực hiện bằng lệnh đơn. Excel ngầm định hiển thị sẵn hai thanh công cụ Standard và Formatting vì chúng thƣờng đƣợc sử dụng tới (hình 1.2, 1.4, 1.5). Khi làm việc, nếu muốn dùng thanh nào thì cho hiển thị lên, nếu không thì giấu đi để giải phóng không gian trên màn hình. d. Thanh công thức (Formular bar) Thanh công thức có 2 phần: phần bên trái hiển thị địa chỉ ô hiện hành hoặc tên khối (Name box); phần bên phải hiển thị nội dung của ô, giúp cho việc xem dữ liệu nhập trong ô, hoặc xem lại công thức và cho phép điều chỉnh. BÀI GIẢNG: Ứng dụng Excel trong Địa chất công trình 4
Người biên soạn: Phan Tự Hướng e. Cửa sổ Workbook Là cửa sổ làm việc chính. Trong 1 workbook có tối đa 255 Sheet, mỗi sheet đƣợc xem nhƣ là một bảng tính dùng để chứa dữ liệu, biểu đồ, hình ảnh, Mặc nhiên trong workbook có sẵn 3 sheet, bạn có thể thay đổi số sheet có sẵn mặc định này khi tạo workbook mới bằng cách chọn: Menu\ Tools\ Options\ General, khai báo số sheet trong Sheets in new workbook. Ngoài ra, trong General bạn có thể quy định font chữ, kích cỡ chữ (Size) mặc định cho cửa sổ workbook. Worksheet là tƣ liệu sơ cấp để lƣu trữ và làm việc. Một worksheet gồm nhiều ô đƣợc thiết lập bởi các cột và các hàng, đó là thành phần của worksheet và đƣợc gọi là bảng tính. Các cột của bảng tính đƣợc tính bằng chữ, từ A, B, C, đến IV. Các hàng của bảng tính đƣợc tính từ 1 đến 16384. Để dễ hình dung, chúng ta coi workbook giống nhƣ quyển sách, worksheet giống nhƣ các trang sách, các ô giống nhƣ chữ trong trang sách đó. Giao giữa cột và hàng gọi là ô (cell), mỗi một ô có một địa chỉ riêng biệt thông qua và . Ví dụ: A1, B4, AB16. Tên của các sheet hiện hành ở trên thanh tab, nằm phía dƣới cửa sổ workbook. Để di chuyển giữa các sheet ta bấm lên tên của sheet đó hoặc dùng tổ hợp phím Ctrl+ PageDown, Ctrl+ PageUp. Tên của sheet hiện hành sẽ đƣợc sáng và đậm hơn. Góc phải và phía dƣới là thanh trạng thái, thể hiện vị trí màn hình trong bảng tính. 3. QUẢN LÝ WORKBOOK Hình 1.6: Cửa sổ lưu workbook chưa có tên. BÀI GIẢNG: Ứng dụng Excel trong Địa chất công trình 5
Người biên soạn: Phan Tự Hướng a. Lưu workbook Khi bạn lƣu workbook lần đầu tiên, bạn phải đặt tên file và địa chỉ bạn muốn lƣu. Rồi sau đó mỗi lần lƣu workbook, Excel sẽ cập nhật với những thay đổi sau cùng nhất. . Lưu workbook mới chưa có tên: - Chọn Menu\ File\ Save hoặc ấn nút Save trên thanh Standard, cửa sổ Save as hiện ra (hình 1.6). - Chọn ổ đĩa và thƣ mục (folder) trong hộp Save in. Bạn có thể tạo folder mới để lƣu workbook bằng cách bấm Create New folder rồi gõ tên folder mới, OK. Trong hộp File name, gõ tên workbook. Excel tự động chèn đuôi cho file là xls. Hình 1.7: Lưu workbook tự động . Tạo một bản sao cho workbook (lưu workbook với một tên mới): - Mở workbook muốn tạo bản sao cho nó hay tên mới. - Chọn Menu\ File\ Save As. Trong hộp File name, gõ tên workbook mới và đƣờng dẫn. - Bấm nút Save. . Lưu workbook tự động: - Chọn Menu\ Tools\ Options\ Save. - Đánh dấu chọn Save AutoRecover info every, nhập khoảng thời gian bằng phút để Excel tự động lƣu (hình 1.7). BÀI GIẢNG: Ứng dụng Excel trong Địa chất công trình 6
Người biên soạn: Phan Tự Hướng - Chọn các lựa chọn khác nếu bạn muốn. Ghi chú: Nếu lệnh AutoSave không có trên thực đơn Tools, bạn phải cài đặt chức năng này bằng cách chọn Menu\ Tools\ Add-Ins, đánh dấu chọn AutoSave trong danh sách Add-Ins available. b. Tạo workbook mới - Chọn Menu\ File\ New hoặc ấn nút New trên thanh Standard, cửa sổ New hiện ra. - Chọn General, chọn biểu tƣợng workbook. c. Mở file workbook có sẵn trong đĩa - Chọn Menu\ File\ Open hoặc ấn nút Open trên thanh Standard, cửa sổ Look in hiện ra. - Chọn ổ đĩa, folder chứa file cần mở, chọn file cần mở rồi bấm Open. d. Đóng cửa sổ workbook đang làm việc - Chọn Menu\ File\ Close hoặc Ctrl+ F4, cửa sổ Microsoft Excel hiện ra (hình 1.8). - Chọn Yes để lƣu file, No để không lƣu file, Cancel để trở về. Hình 1.8: Hộp thoại khi thoát ra khỏi Excel BÀI GIẢNG: Ứng dụng Excel trong Địa chất công trình 7
Người biên soạn: Phan Tự Hướng BÀI 2: NHẬP VÀ HIỆU CHỈNH DỮ LIỆU Khi tiến hành nhập, điều chỉnh dữ liệu hay trình bày, xử lý bảng tính, thông thƣờng phải xác định ô hay khối ô (vùng) mà bạn muốn tác động lên nó trƣớc. Chính vì vậy mà cần biết cách chọn ô nhƣ thế nào cho nhanh và đúng, mục đầu tiên sau đây sẽ đề cập đến vấn đề đó. 1. CHỌN Ô a. Chọn ô đơn - Cách 1: Bấm chuột trên ô cần chọn. - Cách 2: Dùng các phím di chuyển di chuyển đến ô cần chọn. b. Chọn khối ô phạm vi liền kề nhau - Cách 1: giữ chuột trên ô ở 1 góc của khối ô cần chọn (con trỏ chuột có dạng tại ô B2) kéo đến ô ở góc đối diện của khối ô (D4) và nhả chuột (hình 2.1). - Cách 2: Chọn 1 ô ở góc, giữ Shift và bấm trên ô ở góc đối diện hoặc gõ các phím di chuyển tới. Hình 2.1: Chọn khối ô liền kề nhau bằng cách giữ và kéo chuột c. Chọn khối ô không liền nhau Chọn ô hay khối ô thứ nhất, giữ phím Ctrl và chọn ô hay khối ô khác. d. Chọn toàn bộ các ô trên hàng hoặc cột - Chọn hàng : bấm trên tên tiêu đề hàng (Row heading). - Chọn cột : bấm trên tên tiêu đề cột (Column heading). e. Chọn toàn bộ các ô trên sheet Bấm trên ô giao giữa 2 thanh tiêu đề cột và hàng (hình 2.1), hoặc Ctrl+A. BÀI GIẢNG: Ứng dụng Excel trong Địa chất công trình 8
Người biên soạn: Phan Tự Hướng 2. CHỌN CÁC SHEET TRONG WORKBOOK Chọn Cách thực hiện Một sheet Bấm trên tên của tab sheet Hai hay nhiều sheet kề nhau Bấm trên tên của tab sheet thứ nhất, giữ phím Shift và bấm trên tên của tab sheet thứ hai. Các sheet không kề nhau Bấm trên tên của tab sheet thứ nhất, giữ phím Ctrl và bấm trên tên của tab sheet khác. Tất cả các sheet trong Bấm nút phải chuột trên tên của một tab sheet, chọn Select workbook All Sheets trên thực đơn tắt. 3. CÁC KIỂU DỮ LIỆU TRONG EXCEL Dữ liệu nhập có thể là nhãn hay chuỗi ký tự, số hoặc công thức. a. Nhãn, chuỗi ký tự (Label/ String) Chuỗi là tổ hợp của số, khoảng trống, chữ và các ký tự không phải là số. Chuỗi tự động đƣợc canh lề trái trong ô. Khi chuỗi nhập dài hơn độ rộng cột thì nó tràn qua ô bên phải nếu ô bên phải không có dữ liệu. Nếu ô bên phải có dữ liệu thì chỉ có một phần chuỗi vừa đúng độ rộng của cột đƣợc hiển thị. Nhƣ vậy, để thấy toàn bộ dữ liệu trong ô, bạn cần phải điều chỉnh lại độ rộng cột bằng cách giữ và kéo chuột trên biên bên phải của tiêu đề cột sang phải, hoặc bấm đúp để tự động điều chỉnh độ rộng cột dựa vào chuỗi dài nhất chứa trong các ô trên cột (hình 2.2). b. Số (Number) Số là tổ hợp của số, không đƣợc có khoảng trống, chữ và các ký tự không phải là số, trừ ký tự "." (quy định ở Việt Nam là ","). Vị trí số mặc nhiên nằm bên phải ô và có dạng số tự nhiên (General number). Khi số nhập quá lớn hoặc quá nhỏ thì số có dạng số mũ (scientific). Điều chỉnh Hình 2.2: Điều chỉnh độ rộng của cột BÀI GIẢNG: Ứng dụng Excel trong Địa chất công trình 9
Người biên soạn: Phan Tự Hướng c. Công thức (Formular) Trong Excel, bạn có thể tự lập công thức hoặc sử dụng các hàm đã đƣợc lập sẵn. Các hàm đƣợc lập sẵn để xử lý số liệu, chuỗi, tính toán trong tài chính, kế toán, Các hàm này sẽ đƣợc học trong tiết 4. . Cú pháp của công thức Để phân biệt với các dữ liệu khác, công thức đƣợc bắt đầu từ ký tự "=", tiếp theo là các toán hạng và toán tử. Toán hạng có thể là các giá trị không thay đổi đƣợc (giá trị hằng số), địa chỉ ô hay phạm vi khối ô, chuỗi ký tự, tên khối hoặc hàm. . Các toán tử tính toán trong công thức - Toán tử số học: Toán tử Tên Ví dụ Kết quả + Cộng =2+2 4 - Trừ/ âm =4-2 2 * Nhân =4*2 8 / Chia =12/4 3 ^ Luỹ thừa =4^2 16 % Dấu phần trăm =7*2% 0.14 () Ngoặc =3-(8/2) -1 - Toán tử so sánh (so sánh hai giá trị và kết quả là True hoặc False): Toán tử Tên Ví dụ Kết quả = Bằng =7=9 False > Lớn hơn =7>9 False = Lớn hơn hoặc bằng =7>=9 False Khác =7<>9 True - Toán tử nối chuỗi: Toán tử Tên Ví dụ Kết quả & Nối hai chuỗi ="Hoa"&"hồng" Hoahồng - Toán tử tham chiếu: Toán tử Tên Ví dụ Toán tử tham chiếu phạm vi nằm giữa 2 ô, kết quả là một tham chiếu đến tất cả các ô giữa 2 địa chỉ ô. BÀI GIẢNG: Ứng dụng Excel trong Địa chất công trình 10
Người biên soạn: Phan Tự Hướng : =Sum(B5:C8) , Toán tử kết hợp, nó liên kết nhiều tham chiếu thành =Sum(C3,D5:E7) một tham chiếu. . Sao chép công thức từ ô này sang ô khác kề với nó trên cùng hàng hay cột Khi cần sao chép công thức, ta di chuột xuống góc phải dƣới cùng của ô công thức gốc, khi đó con trỏ chuyển thành ký hiệu , giữ phím phải chuột và kéo (theo phƣơng đứng hoặc phƣơng ngang) đến các ô cần sao chép công thức. Dữ liệu sẽ tự động điền theo phƣơng của quá trình sao chép. . Tham chiếu ô Công thức có thể có tham chiếu đến ô. Nếu bạn muốn ô chứa có liên quan đến giá trị của một ô khác, thì bạn lập công thức với toán tử tham chiếu đến ô đó. Ô chứa công thức là ô phụ thuộc, vì giá trị trong ô đó phụ thuộc vào giá trị của ô khác. Khi dữ liệu của ô mà công thức tham chiếu đến thay đổi, thì giá trị trong ô chứa công thức cũng thay đổi theo. . Sự khác nhau giữa địa chỉ tương đối và tuyệt đối Khi bạn tạo công thức, địa chỉ của ô hay khối ô thƣờng đƣợc căn cứ vào vị trí tƣơng đối đối với ô chứa công thức đó. Địa chỉ của ô đƣợc xác định theo vị trí cột và hàng. Ví dụ: AC2, E10, Địa chỉ Tên gọi Ý nghĩa =A2 Địa chỉ tƣơng đối Các địa chỉ cột và hàng sẽ đƣợc thay đổi khi ô chứa công thức đƣợc sao chép đến vị trí khác. =$A$2 Địa chỉ tuyệt đối Các địa chỉ cột và hàng sẽ đƣợc giữ nguyên khi ô chứa công thức đƣợc sao chép đến vị trí khác. 4. NHẬP VÀ SỬA DỮ LIỆU a. Nhập dữ liệu Khi nhập dữ liệu vào ô trên sheet, thực hiện các bƣớc sau: - Chọn ô cần nhập dữ liệu. - Gõ dữ liệu cần nhập vào ô (dữ liệu có thể là số, chuỗi hoặc công thức). BÀI GIẢNG: Ứng dụng Excel trong Địa chất công trình 11
Người biên soạn: Phan Tự Hướng - Kết thúc nhập bằng cách gõ phím Enter hoặc gõ phím tab, khi đó ô sáng di chuyển xuống phía dƣới 1 ô. Nếu sau khi nhập dữ liệu (chƣa gõ Enter), muốn bỏ dữ liệu đang nhập thì gõ phím Escape. Ghi chú: Sau khi kết thúc nhập, nếu bạn muốn xóa dữ liệu trong ô thì chọn lại ô và ấn phím Delete hay Spacebar + Enter. b. Sửa dữ liệu Sau khi nhập dữ liệu, bạn có thể sửa lại dữ liệu trong ô bằng cách sau: - Cách 1: Bấm đúp lên ô cần sửa, di chuyển điểm chèn đến vị trí cần sửa. - Cách 2: Gõ phím F2, điểm chèn nằm sau dữ liệu trong ô. - Cách 3: Bấm lên thanh công thức (Formular bar) tại vị trí cần điều chỉnh. Sau đó thực hiện các thay đổi hoặc điều chỉnh nội dung dữ liệu một các bình thƣờng. Cuối cùng gõ phím Enter để hoàn tất việc sửa đổi hoặc Escape để hủy bỏ. 5. XỬ LÝ DỮ LIỆU a. Xóa dữ liệu trong ô Chọn một hoặc nhiều ô có dữ liệu cần xoá. Nếu bạn chỉ xoá phần dữ liệu của ô thì ấn phím Delete. Nếu bạn muốn xoá các nội dung khác thì chọn Menu\ Edit\ Clear, có 4 trƣờng hợp chọn nhƣ sau: - All: xoá toàn bộ dữ liệu, định dạng, chú thích. - Formats: chỉ xoá phần định dạng của ô. - Contents: chỉ xoá phần dữ liệu trong ô (giống nhƣ phím Delete). - Comments: chỉ xoá phần chú thích trong ô. b. Huỷ bỏ lỗi, hành động vừa thực hịên Bấm nút Undo trên thanh Standard Toolbar để huỷ bỏ hành vi vừa thực hiện. Nếu bạn không muốn bỏ hành vi đó thì bấm nút Redo . c. Lặp lại hành động cuối cùng Nếu muốn lặp lại một hành động cuối cùng (vừa thực hiện xong) thì chọn Menu\ Edit\ Repeat hoặc ấn phím F4. BÀI GIẢNG: Ứng dụng Excel trong Địa chất công trình 12
Người biên soạn: Phan Tự Hướng d. Di chuyển và sao chép dữ liệu trong ô - Bƣớc 1: Chọn các ô chứa dữ liệu cần di chuyển hoặc sao chép. - Bƣớc 2: Bấm nút Cut (Menu\ Edit\Cut hoặc Ctrl+X) nếu muốn di chuyển. Bấm nút Copy (Menu\ Edit\Copy hoặc Ctrl+C) nếu muốn sao chép. - Bƣớc 3: Xác định vị trí ô cần di chuyển hoặc sao chép đến, chỉ cần xác định ô đầu tiên vị trí cần dán (có thể nằm trên sheet, workbook khác hoặc cùng sheet). - Bƣớc 4: Bấm nút Paste (Menu\ Edit\Paste hoặc Ctrl+V) để dán. e. Di chuyển hoặc sao chép một phần nội dung của một ô vào một ô khác - Bƣớc 1: Bấm đúp trên ô chứa dữ liệu (hoặc ấn phím F2) cần di chuyển hoặc sao chép. - Bƣớc 2: Chọn các ký tự cần di chuyển hoặc sao chép bằng cách bôi đen. - Bƣớc 3: Bấm nút Cut (Menu\ Edit\Cut hoặc Ctrl+X) nếu muốn di chuyển. Bấm nút Copy (Menu\ Edit\Copy hoặc Ctrl+C) nếu muốn sao chép. - Bƣớc 4: Bấm đúp vào vị trí ô cần di chuyển hoặc sao chép đến. - Bƣớc 5: Bấm nút Paste (Menu\ Edit\Paste hoặc Ctrl+V) để dán. Sau đó gõ phím Enter. f. Điền dữ liệu tự động vào các ô liền kề nhau Bằng cách giữ và kéo Fill handle của ô, bạn có thể sao chép dữ liệu của nó đến các ô khác trên cùng hàng hoặc cột. Fill handle là hình vuông đen nhỏ nằm ở góc phải của ô hay khối ô đang chọn. Khi con chuột điểm trên dấu Fill handle này, con trỏ chuột biến đổi thành dấu cộng nhỏ +. Nếu ô chứa các chu kỳ số, ngày hoặc thời gian mà Excel có thể mở rộng trong một dãy, thì các giá trị tăng dần thay vì sao chép. Ngoài ra, bạn có thể tạo một dãy tự động điền khác của riêng mình nhƣ danh sách tên các bạn cùng lớp, Muốn tạo danh sách khai báo thì chọn Menu\ Tools\Options\Custom Lists, sau đó lập danh sách trong phần New List (hình 2.3). Ngoài ra, có thể nhập một danh sách hiện đang có trong bảng tính bằng cách bấm vào nút đƣợc khoanh đỏ (nằm bên trái nút Import) để chọn vùng danh sách. Sau đó bấm nút Import để cập nhật danh sách. BÀI GIẢNG: Ứng dụng Excel trong Địa chất công trình 13
Người biên soạn: Phan Tự Hướng Hình 2.3: Danh sách để điền dữ liệu tự động. Hình 2.4 là một ví dụ về điền dữ liệu tự động về ngày trong tuần. Nếu bạn muốn điền nhanh một dãy số tự nhiên thì khi kéo Fill handle, cần phải giữ thêm phím Ctrl (con chuột có dạng ++). Hình 2.4: Điền dữ liệu tự động. BÀI GIẢNG: Ứng dụng Excel trong Địa chất công trình 14
Người biên soạn: Phan Tự Hướng BÀI 3: ĐỊNH DẠNG BẢNG TÍNH Trong Excel, việc định dạng bảng tính có ý nghĩa quan trọng. Việc định dạng bảng tính cũng nhƣ thiết kế giao diện, càng đẹp thì càng hấp dẫn ngƣời sử dụng, ngƣời đọc. Điều đó thể hiện thông qua việc bố trí các mục, phông chữ (font), căn lề, tô đƣờng viền, màu sắc chữ, màu nền, Sau đây ta nghiên cứu từng phần. 1. ĐỊNH DẠNG DỮ LIỆU VÀ Ô Trên màn hình có thanh định dạng thể hiện trạng thái hay kiểu định dạng của ô hay khối ô (hình 3.1). Khi muốn thay đổi thì chọn dữ liệu và ô (khối ô) cần định dạng, vào Menu\Format\Cells hoặc Ctrl+1, cửa sổ Format Cells hiện ra (hình 3.2). Nội dung định dạng gồm có Font, Alignment, Border, Patterns, Number, Protection. Khi đối tƣợng lựa chọn dữ liệu trong ô thì cửa sổ Format Cells chỉ có nội dung Font. Trạng thái chữ thể hiện trong Preview. Hình 3.1: Thanh định dạng (Format Toolbar) a. Thay đổi định dạng chữ trong ô (tab Font- hình 3.2) . Font chữ, kiểu Font (Font Style): chọn kiểu font chữ trong List Box ở dƣới. Trong hộp Font Style có các dạng lựa chọn sau: - Regular: chữ bình thƣờng (không bị nghiêng, không bị đậm, ). - Italic hoặc nhấp nút : chữ bị nghiêng. - Bold hoặc nhấp nút : chữ đậm. - Bold Italic: chữ đậm và nghiêng. Nếu chọn một ô (khối ô) thì toàn bộ nội dung đều cùng kiểu font chữ lựa chọn, còn nếu chọn một đoạn dữ liệu (bằng cách bôi đen) thì chỉ thay đổi trong phạm vi chọn. Tƣơng tự với các dạng định dạng khác. Lƣu ý chữ tiếng Việt với kiểu gõ TCVN thì có ký hiệu ".Vn ", còn chữ in thì đuôi có thêm chữ H. Ví dụ: ".VnTime", ".VnTimeH". Bạn có thể sử dụng kiểu gõ Unicode với các font chữ còn lại. . Kích cỡ chữ (Font Size): chọn kích cỡ chữ trong Size hoặc chọn . BÀI GIẢNG: Ứng dụng Excel trong Địa chất công trình 15
Người biên soạn: Phan Tự Hướng . Màu sắc chữ (Color): chọn màu sắc chữ trong Color hoặc chọn . Mặc định màu Automatic. . Gạch chân dưới chữ (Underline): chọn kiểu gạch chân dƣới chữ ở dƣới Underline hoặc chọn . Có gạch đơn, gạch kép, gạch rời, Mặc định None. . Vị trí chữ trên dòng (Effects): chọn kiểu vị trí chữ trên dòng, có các kiểu sau: - Strikethrough : Chữ bị gạch ở giữa, ví dụ nhƣ 1000. - Superscript : Chữ ở trên cao, ví dụ nhƣ 103. - Subscript : Chữ ở dƣới thấp, ví dụ nhƣ 103. Hình 3.2: Hộp thoại định dạng ô. b. Thay đổi vị trí chữ trong một ô hoặc nhiều ô (tab Alignment- hình 3.3) . Vị trí chữ theo phương ngang (Horizontal): Bạn có thể chọn vị trí chữ ở theo phƣơng ngang đối với ô nhƣ sau: - Left hoặc chọn : căn trái chữ trong ô. - Center hoặc chọn : căn giữa chữ trong ô. - Right hoặc chọn : căn phải chữ trong ô. Mặc định canh lề General, tức là khi dữ liệu ở dạng chuỗi thì tự động canh lề bên trái, ở dạng số thì tự động canh lề bên phải. . Vị trí chữ theo phương đứng (Vertical): Bạn có thể chọn vị trí chữ theo phƣơng đứng đối với ô nhƣ sau: BÀI GIẢNG: Ứng dụng Excel trong Địa chất công trình 16
Người biên soạn: Phan Tự Hướng - Top : vị trí ở trên trong ô. - Center : vị trí ở giữa trong ô. - Bottom : vị trí ở dƣới trong ô. Hình 3.3: Hộp thoại định dạng Alignment . Dòng chữ quay trong ô (Orientation): Bạn có thể chọn dòng quay theo ý muốn bằng cách chọn góc quay theo độ. . Kiểu chữ phân bổ trong ô: Bình thƣờng dữ liệu trong 1 ô tự tràn sang các ô khác khi nó có độ dài lớn hoặc ô có chiều rộng hẹp. Để khắc phục nhƣợc điểm này, tuỳ yêu cầu của ngƣời sử dụng mà Excel đã đƣa ra nhiều dạng xử lý khác nhau, cụ thể nhƣ dƣới đây: - Wrape Text: Cho toàn bộ dữ liệu thể hiện trong 1 ô hiện hành, các chữ tự ngắt và đƣợc dồn theo hàng ngang (hình 3.4a). Muốn xem đƣợc hết thì phải kéo rộng chiều cao hàng ra. - Shrink to fit: Nếu dữ liệu trong ô nằm gọn trong ô thì không tác dụng, nhƣng khi dài hơn ô thì sẽ tự động co nhỏ lại để hiển thị toàn bộ dữ liệu cho vừa ô (hình 3.4b). - Merge Cells: Ghép các ô đƣợc chọn thành 1 ô, dữ liệu sẽ thể hiện trên toàn bộ ô ghép đó (hình 3.4c). Chú ý: Khi ghép các ô đều có dữ liệu thì dữ liệu của ô đầu sẽ đè lên dữ liệu ô sau. Khi đó Excel sẽ cảnh báo có thực hiện hay không? BÀI GIẢNG: Ứng dụng Excel trong Địa chất công trình 17
Người biên soạn: Phan Tự Hướng Ví dụ: ô A2 chứa dữ liệu "Tin học ứng dụng", nội dung thể hiện sẽ khác nhau theo mục đích khác nhau. (a) (b) (c) Hình 3.4: Các kiểu chữ phân bổ trong ô c. Thay đổi đường viền trong một ô hoặc nhiều ô (tab Border- hình 3.6) Khi bạn đã thể hiện dữ liệu trên các ô, chỉnh sửa lại, công việc tiếp theo là cần tô các đƣờng viền, khung cho chuẩn, cho đẹp (hình 3.5). Hình 3.5: Tô đường viền (khung) của ô Hình 3.6: Cửa sổ tô đường viền (khung) của ô Nội dung gồm các phần sau: - Vị trí đƣờng viền trong ô (Border): Bạn chọn vị trí các đƣờng viền (nút chìm xuống là có tác dụng). Kiểu to bao gồm có tô ngang trên, tô ngang giữa, tô ngang cuối, tô dọc trái, tô dọc giữa, tô dọc phải, tô chéo trái sang phải và phải sang trái, BÀI GIẢNG: Ứng dụng Excel trong Địa chất công trình 18
Người biên soạn: Phan Tự Hướng - Kiểu đƣờng viền (Style): Chọn kiểu nét đƣờng viền nhƣ nét đơn, nét đôi, nét đứt, nét đậm, - Màu sắc đƣờng viền (Color): Bạn có thể tuỳ ý lựa chọn màu sắc trong hộp cuộn thả xuống. d. Thay đổi màu nền trong ô (tab Patterns- hình 3.7) Bạn có thể thay đổi màu nền của các ô. Có hai sự lựa chọn là màu sắc của nền (Color) và ký hiệu nền (Pattern). - Thay đổi màu nền (Color): Chọn màu trên bảng mầu. - Thay đổi tô nền (Patterns): Chọn ký hiệu nền trên hộp cuộn thả xuống. Hình 3.7: Cửa sổ tô màu nền của ô e. Định dạng số (tab Number- hình 3.10) Theo quy định ở Việt Nam, số thập phân có dấu “.” để phân chia, nhƣng theo tiêu chuẩn Quốc tế (do máy tính cài đặt tự động), dấu có ký hiệu “,”. Ví dụ: 15,35 (TCVN) có nghĩa là 15.35 (Quốc tế). Bạn có thể thay đổi theo quy định ở Việt Nam bằng cách thay đổi trong phần Start\Settings\Control Panel\Regional Options\Numbers\Decimal symbol. Nhƣng theo tôi, trong lĩnh vực kỹ thuật nên để số liệu theo mặc định Quốc tế. BÀI GIẢNG: Ứng dụng Excel trong Địa chất công trình 19
Người biên soạn: Phan Tự Hướng . Dữ liệu dạng số mặc định (General): Trong Excel, dữ liệu trong ô mặc định ở dạng General. Tùy dạng dữ liệu vào mà Excel có thể tự động chuyển sang Number, Time, Date, . Dữ liệu dạng số (Number): Dạng này đƣợc phổ biến dùng trong tính toán. Bạn có thể lựa chọn số chữ số sau dấu "." bằng cách vào giá trị Decimal places (hình 3.8). Bạn có thể tăng (hoặc giảm) số chữ số sau dấu "." bằng cách lựa chọn (hoặc ) trong thanh Formatting Toolbar. Hình 3.8: Cửa sổ định dạng số Hình 3.9: Cửa sổ định dạng Custom BÀI GIẢNG: Ứng dụng Excel trong Địa chất công trình 20
Người biên soạn: Phan Tự Hướng . Dữ liệu dạng ngày (Date): Bạn có thể chọn thứ tự ngày (d), tháng (m), năm (y) theo ý muốn, đƣợc thể hiện trong Type. Ví dụ: 10-12-1989 hoặc 10-12, 12-1989, . Dữ liệu dạng giờ (Time): Bạn chọn kiểu quy định thời gian, có nhiều kiểu đƣợc thể hiện ở Type. Ký hiệu AM là biểu thị thời gian từ 0h00 đến 12h00, PM là biểu thị thời gian từ 12h00 đến 24h00. . Dữ liệu dạng chuỗi (Text): Khi ở dạng chuỗi, dữ liệu sẽ tự động canh lề bên trái ô (kể cả số). . Dữ liệu tự lựa chọn (Custom): Khi bạn muốn lựa chọn kiểu dữ liệu cho riêng mình, bạn chọn Custom. Trong Type, chuỗi nằm trong “ ” đƣợc tự động điền vào dữ liệu (hình 3.9, 3.10). Trong hình 3.9, giá trị ở ô A1 là 250 (thể hiện trên thanh Formular Bar) nhƣng thể hiện tại ô là Mác bê tông là 250# . Nguyên nhân là do ô đó đƣợc định dạng Type: "Mác bê tông là " 0"#" . Trong thực tế, thƣờng thể hiện góc ở dạng độ-phút (ví dụ nhƣ 12030'), nhƣng Excel lại không mặc định hiển thị nhƣ vậy. Việc tính toán đối với kiểu định dạng đó khá phức tạp vì Excel coi đó là chuỗi. Mặt khác các hàm lƣợng giác thì chỉ tính toán với góc thể hiện bằng rađian. Để thể hiện góc ở dạng độ - phút, trong Type gõ nội dung định dạng ##"°"##"'" trong ô cần định dạng. Sau đó chỉ cần nhập số vào. Hình 3.10: Cửa sổ định dạng góc theo độ - phút BÀI GIẢNG: Ứng dụng Excel trong Địa chất công trình 21
Người biên soạn: Phan Tự Hướng Ghi chú: Một số kiểu font chữ không thể hiện đầy đủ góc theo độ - phút, phải có quy ƣớc khi nhập góc phút, nếu phút nhỏ hơn 10 thì phải thêm số 0 ở trƣớc. Ví dụ để nhập 9005' thì bạn phải gõ 905. Tuy góc xuất hiện trên bảng tính là 9005' nhƣng giá trị đó là số 905. f. Bảo mật ô (tab Protection- hình 3.13) Khi bạn muốn bảo mật công thức hoặc đề phòng vào nhầm ô thì bạn chọn chế độ bảo mật ô. Lệnh bảo mật ô chỉ có tác dụng trong trƣờng hợp khóa Sheet hoặc Workbook (hình 3.11). Hình 3.11: Cửa sổ lệnh bảo mật ô Hình 3.12: Cửa sổ lệnh bảo mật Sheet Lệnh bảo mật ô có hai lựa chọn nhƣ sau: BÀI GIẢNG: Ứng dụng Excel trong Địa chất công trình 22
Người biên soạn: Phan Tự Hướng . Locked: Khoá ô (không cho thay đổi nội dung) nhƣng cho nhìn toàn bộ nội dung ô. . Hidden: Khóa ô vào không cho hiện nội dung ô, đề phòng vô tình thay đổi. Khi chọn những ô cần bảo mật, bạn vào Tools\Protection\Protect Sheet hoặc Protect Workbook. Cửa sổ Protect Sheet hoặc Protect Workbook hiện ra yêu cầu nhập Password, nội dung Password đƣợc mã hóa thành (hình 3.12). Có nhiều lựa chọn trong Protect Sheet trong danh sách Allow all users of this worksheet to nhƣ sau: . Select locked cells: Cho phép di chuyển chuột vào ô bị khoá. . Select unlocked cells: Cho phép di chuyển chuột vào ô không bị khoá. Giữ và kéo chuột Giữ và kéo chuột Hình 3.13: Điều chỉnh độ rộng của cột và chiều cao hàng 2. XỬ LÝ CỘT, HÀNG VÀ Ô a. Thay đổi độ rộng của cột và chiều cao hàng . Thay đổi độ rộng của cột: Giữ và kéo chuột trên biên cạnh phải của tiêu đề cột (địa chỉ cột) sang phải để tăng độ rộng của cột hoặc sang trái để giảm độ rộng của cột (khi giữ và kéo con trỏ chuột có dạng ). Xem hình 3.13. . Thay đổi chiều cao của hàng: Cách thực hiện tƣơng tự nhƣ thay đổi chiều rộng của cột. Chú ý: - Có thể thay đổi độ rộng cho nhiều cột ( hoặc chiều cao hàng) bằng cách chọn các cột (hàng) cần thay đổi rồi mới thực hiện sự thay đổi. Các cột (hàng) đƣợc sau khi thay đổi sẽ có độ rộng (độ cao) bằng nhau. - Nhấp đúp chuột trên bên cạnh phải của tiêu đề cột để làm cho độ rộng cột vừa khít với dữ liệu dài nhất mà cột đó chứa. BÀI GIẢNG: Ứng dụng Excel trong Địa chất công trình 23
Người biên soạn: Phan Tự Hướng - Nhấp đúp chuột trên cạnh dƣới của tiêu đề hàng để làm cho chiều cao hàng vừa khít với nội dung trên nó, chiều cao tự động đƣợc chỉnh này phụ thuộc vào kích cỡ chữ lớn nhất đƣợc chọn trên hàng. b. Chèn ô, hàng hoặc cột . Chèn ô trống: Chọn phạm vi các ô cần chèn thêm các ô trống mới, số đƣợc chèn sẽ đúng bằng số ô đƣợc chọn. Cách thực hiện nhƣ sau: - Chọn các ô cần chèn. - Chọn Insert\Cells Khi đó hộp thoại Insert hiện ra. Chọn Shift cells righ nếu bạn muốn chèn các ô mới vào bên trái các ô đƣợc chọn và di dời các ô đƣợc chọn sang bên phải. Chọn Shift cells down nếu bạn muốn chèn và di dời các ô tƣơng ứng xuống phía dƣới (hình 3.14). Hình 3.14: Ví dụ về thực hiện chèn các ô trống . Chèn ô được sao chép hoặc di chuyển vào giữa các ô có sẵn: - Chọn các ô chứa dữ liệu cần di chuyển hoặc sao chép. - Chọn Cut nếu muốn di chuyển, chọn Copy nếu muốn sao chép. - Chọn ô ở góc trái nơi cần di chuyển hoặc sao chép đến. Chọn Insert\Copied Cells (nếu bạn sao chép) hoặc Cut Cells (nếu bạn di chuyển). Khi đó cửa sổ Insert Paste hiện ra nhƣ hình 3.17, bạn lựa chọn các hƣớng di dời. Hình 3.15: Sao chép và chèn các ô BÀI GIẢNG: Ứng dụng Excel trong Địa chất công trình 24
Người biên soạn: Phan Tự Hướng . Chèn hàng: Chọn một ô (hay khối ô chứa các địa chỉ hàng) trên hàng phía dƣới hàng cần chèn thêm hàng mới. Chọn Insert\Rows hoặc nhấp nút phải chuột vào tên địa chỉ hàng trong các hàng đƣợc chọn và chọn Insert. Số hàng đƣợc chèn thêm tƣơng đƣơng với số hàng đƣợc chọn thông qua số ô. Ví dụ: để chèn thêm hai hàng phía trên hàng số 5, tiến hành chọn hai ô chứa địa chỉ 2 hàng 5 và 6 nhƣ A5:A6, hoặc chọn toàn bộ 2 hàng 5 và 6. Chọn Insert\Rows. . Chèn cột: Chọn một ô (hoặc khối ô chứa các địa chỉ cột) trên cột phía bên phải cột cần chèn thêm cột mới. Chọn Insert\Columns hoặc nhấp nút phải chuột vào tên địa chỉ cột trong các cột đƣợc chọn và chọn Insert. Số cột đƣợc chèn thêm tƣơng đƣơng với số cột đƣợc chọn thông qua số ô. Ví dụ: để chèn thêm 3 cột phía bên trái cột B, tiến hành chọn ba ô chứa địa chỉ 3 cột B,C,D nhƣ B1:D1 hoặc chọn toàn bộ 3 cột B,C,D. Chọn Insert\ Columns. c. Xóa ô, hàng hoặc cột . Xoá ô: Chọn ô hay khối ô cần xoá bằng cách bôi đen, chọn Edit\Delete (hình 3.16). Cửa sổ hộp thoại Delete hiện ra. Chọn Shift cells left nếu kéo các ô bên phải sang đè lên khối ô bị xoá. Chọn Shift cells up nếu muốn kéo các ô từ dƣới lên trên đè lên khối ô bị xoá. . Xoá hàng, cột: Chọn ô hay khối ô có chứa các địa chỉ hàng hay cột cần xóa, hoặc chọn toàn bộ hàng hay cột cần xóa. Chọn Edit\Delete , cửa sổ hộp thoại Delete hiện ra (hình 3.16). - Chọn Entire row : xóa hàng. - Chọn Entire Column : xóa cột. Hình 3.16: Hộp thoại xóa ô, hàng hoặc cột. BÀI GIẢNG: Ứng dụng Excel trong Địa chất công trình 25
Người biên soạn: Phan Tự Hướng 3. LÀM VIỆC VỚI WORKBOOK VÀ WORKSHEET a. Chèn worksheet mới Khi bạn muốn thêm worksheet mới, bạn có thể chọn các cách sau: . Cách 1: Chọn Menu\ Insert\ Worksheet (hình 3.17). Hình 3.17: Thêm 1 worksheet mới . Cách 2: Nhấp nút phải trên tên sheet cần chèn vào phía bên trái của nó, chọn Insert, sau đó chọn biểu tƣợng của worksheet trong General (hình 3.19). Worksheet mới sẽ đƣợc chèn trƣớc Worksheet hiện hành. b. Di chuyển và sao chép sheet Mở các file cần di chuyển hoặc sao chép các sheet đến. Thực hiện các bƣớc sau: . Bƣớc 1: Chọn các sheet muốn di chuyển hoặc copy, chọn Menu\ Edit\ Move or Copy Sheet, cửa sổ Move or Copy sẽ hiện ra (hình 3.18). . Bƣớc 2: Trong hộp To Book, chọn workbook nào sẽ nhập các sheet. Trong hộp Before sheet, nhấp chọn vị trí sheet đƣợc di chuyển hoặc copy trong workbook mới. . Bƣớc 3: Đánh dấu Create a copy nếu muốn sao chép, còn không thì là di chuyển. Hình 3.18: Cửa sổ sao chép hoặc di chuyển các sheet Chú ý: Hãy cẩn thận khi di chuyển hoặc sao chép các sheet. Các tính toán hoặc biểu đồ dựa trên dữ liệu trong worksheet có thể trở nên không đúng. BÀI GIẢNG: Ứng dụng Excel trong Địa chất công trình 26
Người biên soạn: Phan Tự Hướng c. Xóa các sheet trong workbook . Bƣớc 1: Chọn các sheet muốn xoá. . Bƣớc 2: Chọn Edit\Delete Sheet hoặc nhấp nút phải chuột trên tab Sheet, chọn Delete (hình 3.19). Hình 3.19: Cửa sổ xóa sheet trong workbook d. Giấu toàn bộ hay một phần của workbook . Giấu workbook: Chọn workbook cần giấu, vào Menu\Window\Hide. . Hiển thị lại workbook bị giấu: Chọn Menu\Window\Unhide. Trong hộp Unhide workbook, nhấp đúp trên tên của workbook đƣợc giấu để hiển thị lại. . Giấu sheet: Chọn các sheet cần giấu, vào Menu\Format\Sheet\Hide (hình 3.20a). . Hiển thị lại sheet bị giấu: Chọn Menu\Format\Sheet\Unhide. Trong hộp Unhide sheet, nhấp đúp trên tên của sheet đƣợc giấu để hiển thị lại (hình 3.20b). . Giấu hàng hoặc cột: Chọn các hàng (cột) cần giấu, vào Menu\Format\ Row (Column), chọn Hide (hình 3.21). . Hiển thị hàng và cột bị giấu: Chọn các hàng phía trên và phía dƣới của các hàng bị giấu. Vào Menu\Format\Row\Unhide. Thực hiện tƣơng tự đối với các cột. (a) (b) Hình 3.20: Cửa sổ ẩn- hiện các sheet BÀI GIẢNG: Ứng dụng Excel trong Địa chất công trình 27
Người biên soạn: Phan Tự Hướng Hình 3.21: Cửa sổ ẩn- hiện các cột (hàng) e. Hiển thị nhiều workbook cùng một lúc Phƣơng pháp này đƣợc dùng khi bạn muốn mở nhiều workbook để cùng làm việc. Để dễ điều khiển, chọn Window\Arrange, các dạng sắp xếp vị trí các workbook sẽ hiện ra để bạn lựa chọn (hình 3.22). Tiled Horizontal Vertical Cascade Hình 3.22: Cửa sổ hiện nhiều workbook cùng một lúc f. Giữ các nhãn cột và hàng thấy được khi bạn cuộn màn hì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. BÀI GIẢNG: Ứng dụng Excel trong Địa chất công trình 28
Người biên soạn: Phan Tự Hướng 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: CTRL+ ALT+ Phím chức năng SHIFT CTRL ALT SHIFT SHIFT F1 Hiển thị giúp đỡ Chèn sheet Chèn biểu đồ sheet mới F2 Sửa nội dung ô Vào chú thích ô Đặt tên mới Lưu (Save as) F3 Dán tên khối vào Dán hàm vào công Định nghĩa Tạo tên công thức thức khối 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ổ Di chuyển đến phần Di chuyển đến phần Di chuyển đến Di chuyển cửa sổ kế tiếp cửa sổ trước book kế tiếp đến book F6 trước đó F7 Spelling Di chuyển cửa sổ F8 Chọn ô Chèn thêm khối ô Thay đổi kích Hiển thị hộp chọn khác thước cửa sổ Macro Tính toán tất cả các Tính toán trên Thu workbook sheet trên tất cả các worksheet hiện thành biểu F9 book mở hành tượng F10 Chọn thanh thực Hiển thị thực đơn Phóng to cửa đơn lệnh phím tắt sổ F11 Tạo biểu đồ Chèn worksheet Hiển thị cửa mới sổ VBA. F12 Đặt tên mới Lưu Mở In BÀI GIẢNG: Ứng dụng Excel trong Địa chất công trình 29
Người biên soạn: Phan Tự Hướng (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 BÀI GIẢNG: Ứng dụng Excel trong Địa chất công trình 30
Người biên soạn: Phan Tự Hướng 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. BÀI GIẢNG: Ứng dụng Excel trong Địa chất công trình 31
Người biên soạn: Phan Tự Hướng 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. BÀI GIẢNG: Ứng dụng Excel trong Địa chất công trình 32
Người biên soạn: Phan Tự Hướng 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. BÀI GIẢNG: Ứng dụng Excel trong Địa chất công trình 33
Người biên soạn: Phan Tự Hướng 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. BÀI GIẢNG: Ứng dụng Excel trong Địa chất công trình 34
Người biên soạn: Phan Tự Hướng 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: 2 1 2 S = VAR = (x x) n 1 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: n 1 2 SCM = STDEVP = (xi x) n i 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: n 1 2 S = STDEV= (xi x) n 1 i 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: BÀI GIẢNG: Ứng dụng Excel trong Địa chất công trình 35
Người biên soạn: Phan Tự Hướng 1 Covar(X,Y) = (x x)(y y) n  i i 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". BÀI GIẢNG: Ứng dụng Excel trong Địa chất công trình 36
Người biên soạn: Phan Tự Hướng 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. BÀI GIẢNG: Ứng dụng Excel trong Địa chất công trình 37
Người biên soạn: Phan Tự Hướng Ví dụ: =IF(OR(3>4,4=4,1 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. BÀI GIẢNG: Ứng dụng Excel trong Địa chất công trình 38
Người biên soạn: Phan Tự Hướng • 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. BÀI GIẢNG: Ứng dụng Excel trong Địa chất công trình 39
Người biên soạn: Phan Tự Hướng - 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. BÀI GIẢNG: Ứng dụng Excel trong Địa chất công trình 40
Người biên soạn: Phan Tự Hướng - 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). BÀI GIẢNG: Ứng dụng Excel trong Địa chất công trình 41
Người biên soạn: Phan Tự Hướng - 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, BÀI GIẢNG: Ứng dụng Excel trong Địa chất công trình 42
Người biên soạn: Phan Tự Hướng - 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 BÀI GIẢNG: Ứng dụng Excel trong Địa chất công trình 43
Người biên soạn: Phan Tự Hướng Đƣờ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 ô. BÀI GIẢNG: Ứng dụng Excel trong Địa chất công trình 44
Người biên soạn: Phan Tự Hướng 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). BÀI GIẢNG: Ứng dụng Excel trong Địa chất công trình 45
Người biên soạn: Phan Tự Hướng 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). BÀI GIẢNG: Ứng dụng Excel trong Địa chất công trình 46
Người biên soạn: Phan Tự Hướng 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 BÀI GIẢNG: Ứng dụng Excel trong Địa chất công trình 47
Người biên soạn: Phan Tự Hướng Hình 5.2: Bước thứ nhất của vẽ biểu đồ (a) (b) Hình 5.3: Bước thứ 2 của vẽ biểu đồ (a) (b) (c) (d) (e) Hình 5.4: Bước thứ 3 của vẽ biểu đồ . Bước 3: Chọn và sửa các thông số của biểu đồ cần vẽ, gồm các phần Titles, Axes, Gridlines, Legend, Data labels (hình 5.4). Sau đây ta đi vào từng phần: - Titles: Tên của biểu đồ, tên chỉ dẫn trục X, Y (hình 5.4a). - Axes: Bật- tắt giá trị trục X,Y (hình 5.4b). - Gridlines: Bật- tắt lƣới giá trị lớn (major), bé (minor) của trục X,Y (hình 5.4c). - Legend: Bật- tắt chỉ dẫn biểu đồ và vị trí của nó (hình 5.4d). BÀI GIẢNG: Ứng dụng Excel trong Địa chất công trình 48
Người biên soạn: Phan Tự Hướng - Data labels: Chọn None nếu không muốn hiện nội dung gì cả, chọn Series Name nếu muốn hiển thị giá trị tỷ lệ nhóm hạt và phần trăm tích luỹ, chọn X value nếu muốn hiển thị giá trị đƣờng kính hạt, chọn Y value nếu muốn hiển giá trị phần trăm tích luỹ và hàm lƣợng các nhóm hạt (hình 5.4e). . Bước 4: Là bƣớc cuối cùng. Bạn xác định vị trí của biểu đồ đã chọn riêng ở một sheet (As new sheet) hay trong một sheet đã có sẵn (As object in- hình 5.5). Nhấn phím Finish để kết thúc công việc, biểu đồ hiện ra (hình 5.6). Hình 5.5: Bước cuối cùng của vẽ biểu đồ là chọn vị trí hiển thị Chart Title Series Label Value(X) axis minor gridlines Plot area Legent Value(Y) axis Value(Y) axis minor gridlines Title Value(X) axis Value(Y) axis major gridlines major gridlines Chart Value(Y) axis area Value(X) axis Title Value(X) axis Hình 5.6: Biểu đồ đã được vẽ xong và các đối tượng của nó 2. HIỆU CHỈNH BIỂU ĐỒ Khi tạo xong một biểu đồ, Excel cho phép thay đổi những lựa chọn có sẵn (thêm hay bớt một số thành phần) sao cho phù hợp với yêu cầu. Sau khi tạo ra biểu đồ, xét về mặt kiểu dáng, hình dạng và những yếu tố cơ bản xem nhƣ đạt yêu cầu. Tuy vậy, BÀI GIẢNG: Ứng dụng Excel trong Địa chất công trình 49
Người biên soạn: Phan Tự Hướng những yếu tố khác nhƣ kích thƣớc, màu sắc, vị trí, phông chữ, thƣờng không đạt nhƣ ý muốn, cần phải thực hiện thêm một số thao tác hiệu chỉnh bằng các công cụ có sẵn. Ví dụ nhƣ hình 5.6 chƣa phản ánh đúng biểu đồ thí nghiệm thành phần hạt theo yêu cầu. a. Định dạng lại các đối tượng, thành phần của biểu đồ Bạn có thể định dạng các đối tƣợng đã tạo sẵn bên trong biểu đồ nhƣ màu sắc, đƣờng nét, phông chữ, Tùy đối tƣợng mà có kiểu định dạng khác nhau. Các đối tƣợng chính trong bản vẽ có thể định dạng lại đƣợc trình bày trong hình 5.6. Các bƣớc thực hiện nhƣ sau: Chọn đối tƣợng trong biểu đồ (ví dụ Chart area). Chọn Format/Format Chart Area hoặc ấn phải chuột, trong menu chọn Format Chart Area, cửa sổ định dạng hiện ra (hình 5.7). - Định dạng màu nền, khung bao quanh: Trong cửa sổ Patterns, bạn lựa chọn màu nền và màu các khung bao quanh (hình 5.7a). - Định dạng phông chữ (nếu đối tƣợng là chữ): Trong cửa sổ Font, bạn lựa chọn phông chữ, kích cỡ, kiểu chữ. Auto Scale là chức năng tự động điều chỉnh cỡ chữ khi thay đổi kích thƣớc (hình 5.7b). Đây là chức năng mới nhƣng không hữu ích vì kích cỡ chữ thay đổi không theo ý muốn, nên bỏ chức năng này. (a) (b) Hình 5.7: Cửa sổ định dạng một số đối tượng của biểu đồ BÀI GIẢNG: Ứng dụng Excel trong Địa chất công trình 50
Người biên soạn: Phan Tự Hướng b. Điều chỉnh và bổ sung một số yếu tố trong biểu đồ dạng XY Trong biểu đồ dạng XY, chuỗi Series (hình 5.6) đƣợc coi là đối tƣợng quan trọng và đƣợc bổ sung thêm một số các yếu tố về định dạng và điều khiển. Hình 5.8: Cửa sổ định dạng chuỗi dữ liệu Hình 5.9: Cửa sổ định dạng số trên trục X hoặc Y Các bƣớc thực hiện nhƣ sau: Nhấn kép vào ký hiệu Series trên biểu đồ, cửa sổ Format Data Series hiện ra nhƣ hình 5.8. Trong Patterns, bạn có hai lựa chọn là Line và Marker. . Line: lựa chọn loại và màu sắc đƣờng nối giữa các điểm (marker). Có các lựa chọn sau: - Automatic: Excel tự động gán màu và và độ dày (Weight) của nét. - None: không hiện đƣờng nét. - Custom: tuỳ chọn kiểu đƣờng (Style), màu (Color), độ dày đƣờng (Weight). BÀI GIẢNG: Ứng dụng Excel trong Địa chất công trình 51
Người biên soạn: Phan Tự Hướng - Smoothed line: tác dụng làm cong các đƣờng nối. . Marker: lựa chọn kiểu đánh dấu (marker). - Automatic: Excel tự động chọn kiểu marker. - None: không hiện marker. - Custom: tuỳ chọn kiểu marker (Style), màu bao ngoài (Foreground), màu bên trong (Background). - Size: kích cỡ của marker. - Shadow: làm nổi hẳn các marker trên biểu đồ. Chuyển sang Scale (hình 5.9), bạn có các lựa chọn định dạng trục X nhƣ sau: - Minimum: giá trị trục hoành nhỏ nhất (ví dụ này là 0.001). - Maximum: giá trị trục hoành lớn nhất (ví dụ này là 1000). - Major unit: đƣờng bƣớc nhảy của đƣờng lƣới lớn (ví dụ này là 10). - Minor unit: đƣờng bƣớc nhảy của đƣờng lƣới nhỏ (ví dụ này là 10). - Value (Y) axis Crosses at: xác định giá trị trục X giao với trục Y. - Logarithmic scale: thể hiện trục dƣới dạng logarit, nhỏ nhất là 0.001, lớn nhất là 1000. - Value in reverse order: quay ngƣợc hƣớng giá trị trục. - Value (Y) axis Crosses at maximum value: xác định giá trị trục X giao với giá trị lớn nhất của trục Y. Hình 5.10: Biểu đồ thành phần hạt đã hiểu chỉnh một phần BÀI GIẢNG: Ứng dụng Excel trong Địa chất công trình 52
Người biên soạn: Phan Tự Hướng Trong các lựa chọn trên, cần bỏ dấu tích Auto trƣớc các chức năng trên, nhằm loại bỏ khả năng tự động thay đổi thiết lập giá trị lớn nhất, nhỏ nhất, đƣờng lƣới, biểu đồ. Tuy nhiên việc lựa chọn đó còn phụ thuộc vào tuỳ loại biểu đồ. Ví dụ nhƣ đối với biểu đồ xuyên tĩnh (hình 10 phần Phụ lục), độ sâu xuyên luôn thay đổi tuỳ từng công trình nên phải để chế độ Auto cho Maximum. Hình 5.11: Định dạng kiểu đường lưới Value(Y) axis minor gridlines Hình 5.12: Định dạng kiểu đường lưới minor Y trong biểu đồ Để thêm trục Y bên phải biểu đồ, nhấn kép vào ký hiệu Series trên biểu đồ, cửa sổ Format Data Series hiện ra nhƣ hình 5.8, chuyển sang Axis (hình 5.13). Chọn Secondary axis trong Plot series on. Nếu biểu đồ chỉ có một chuỗi series thì không BÀI GIẢNG: Ứng dụng Excel trong Địa chất công trình 53
Người biên soạn: Phan Tự Hướng hiển thị trục tung thứ 2 Secondary axis. Đó chính là lý do để chọn chuỗi Series2 (là quan hệ giữa đƣờng kính nhóm hạt và hàm lƣợng các nhóm hạt, mặc dù không sử dụng) nhƣ hình 5.3b. Sau đó định dạng chuỗi Series2 không hiển thị bằng cách chọn None trong Marker (hình 5.8). Hình 5.13: Thêm trục tung bên phải biểu đồ Hình 5.14: Biểu đồ thành phần hạt đã tương đối hoàn chỉnh Tiếp theo là công tác chỉnh sửa kiểu đƣờng nét cho phù hợp, đối tƣợng chỉnh sửa có thể là Value (X) axis, Value (Y) axis, Value(Y) axis minor gridlines, Value(Y) axis major gridlines, Value(X) axis minor gridlines, Value(X) axis major gridlines, (hình BÀI GIẢNG: Ứng dụng Excel trong Địa chất công trình 54
Người biên soạn: Phan Tự Hướng 5.6). Đầu tiên chọn đối tƣợng Value(Y) axis minor gridlines, sau đó bấm phải chuột và chọn Format Gridlines (hình 5.11). Việc chọn kiểu đƣờng nét giống nhƣ hình 5.8. Sau khi chỉnh sửa, biểu đồ thành phần hạt đã tƣơng đối hoàn chỉnh (hình 5.14). Công việc xây dựng biểu đồ thành phần hạt của chúng ta đã hoàn thành. c. Xác định phương trình tương quan thực nghiệm Nhƣ chúng ta đã học, có hai dạng tƣơng quan thực nghiệm là tuyến tính và phi tuyến. Từ biểu đồ quan hệ XY, Excel cho phép xác lập mối tƣơng quan giữa các đại lƣợng, thể hiện bởi phƣơng trình tƣơng quan, đƣờng hồi quy và hệ số tƣơng quan Pearson (R). Ví dụ: quan hệ giữa hệ số nén lún a (cm2/kG) và độ ẩm W (%) nhƣ bảng dƣới: W(%) 18.3 18.9 19.6 20.2 22.3 24.6 28.3 30.2 33.4 36.3 39.3 41.2 a(cm2/kG) 0.011 0.013 0.018 0.021 0.022 0.026 0.031 0.033 0.036 0.041 0.046 0.049 Hình 5.15: Lập phương trình tương quan Yêu cầu lập biểu đồ quan hệ giữa hai đại lƣợng theo 4 bƣớc nhƣ trên. Sau khi vẽ và chỉnh sửa số liệu xong, bạn bấm chuột vào ký hiệu chuỗi (lúc đó tất cả chuyển màu khác), bấm phải chuột và chọn Add Trendline (hình 5.15). Khi đó, cửa sổ các loại phƣơng trình tƣơng quan hiện ra nhƣ hình 5.16a, có hai lựa chọn là Type và Options. . Type: Lựa chọn phƣơng trình tƣơng quan nào đã định trƣớc. Excel đã lập sẵn các phƣơng trình tƣơng quan sau: - Linear: Phƣơng trình tuyến tính, có dạng y= ax+b BÀI GIẢNG: Ứng dụng Excel trong Địa chất công trình 55
Người biên soạn: Phan Tự Hướng - Logarithmic: Phƣơng trình logarit, có dạng y= aLn(x)+b - Polynomial: Phƣơng trình bậc n, có dạng y= axn+bxn-1+ +kx+l. Trong đó, bạn chọn bậc n trong Order. - Power: Phƣơng trình Luỹ thừa, có dạng y= bxa Trong ví dụ này, lựa chọn trƣờng hợp Linear (hình 5.16a). Còn các trƣờng hợp khác tuỳ chọn kiểu quan hệ. . Options: Bao gồm các lựa chọn dƣới đây: - Trendline name: Chọn chuỗi số liệu (nếu có nhiều tƣơng quan) mà bạn định lập phƣơng trình tƣơng quan, mặc định là Automatic. - Forecast: mở rộng đƣờng hồi quy trên đồ thị về phía trƣớc và phía sau so với giới hạn số liệu đầu vào. Chọn 1 và 1. - Display equation on chart: Hiển thị phƣơng trình tƣơng quan trên biểu đồ theo nguyên tắc phƣơng pháp bình phƣơng bé nhất. - Display R-squared value on chart: Hiển thị giá trị bình phƣơng của hệ số tƣơng Pearson (R2) trên biểu đồ nhƣ hình 5.16b. (a) (b) Hình 5.16: Phương trình tương quan và các thông số của nó Kết quả thể hiện trên hình 5.17. Đối với mối tƣơng quan dạng phi tuyến, cách thực hiện tƣơng tự. Đối với một số mối quan hệ khi chƣa xác định đƣợc chính xác quy luật phụ thuộc tƣơng quan của hai đại lƣợng với nhau, bạn thử từng dạng khác nhau và nên chọn trƣờng hợp nào cho giá trị R2 nào là lớn nhất (nghĩa là mối quan hệ chặt nhất - xem thêm mục tiếp theo). Chú ý rằng có thể mối tƣơng quan trong phạm vi đang xét là chặt nhất nhƣng khi ngoại suy ra 2 phía, có thể phƣơng trình đó không phù hợp. BÀI GIẢNG: Ứng dụng Excel trong Địa chất công trình 56
Người biên soạn: Phan Tự Hướng BiÓu ®å W- a 0.06 y = 0.0015x - 0.0124 0.05 R2 = 0.9749 0.04 /kG) 2 0.03 a(cm 0.02 a(cm2/kG) HÖ sè HÖ nÐn lón 0.01 Linear (a(cm2/kG)) 0.00 0 10 20 30 40 50 §é Èm W (%) Hình 5.17: Kết quả xây dựng phương trình tương quan d. Phương pháp bình phương bé nhất Khi làm việc với các đại lƣợng tƣơng quan với nhau, bạn có thể sử dụng một số hàm xác định các thông số của mối tƣơng quan đó. Đối với mối phụ thuộc tƣơng quan tuyến tính, hệ số tƣơng quan đƣợc sử dụng để đánh giá mức độ chặt của mối tƣơng quan đó. Theo định nghĩa, hệ số tƣơng quan giữa đại lƣợng y và đại lƣợng x đƣợc xác định theo biểu thức sau:  R = xy S x S y Trong đó: Sx, Sy - Độ lệch tiêu chuẩn trong phân phối riêng của đại lƣợng x và y, đó chính là độ lệch bình phƣơng trung bình của x và y (sử dụng hàm STDEV trong Excel). xy - Hiệp phƣơng sai của y và x (dùng hàm Covar trong Excel). Hiệp phƣơng sai của y và x là kỳ vọng của [(x - X )(y - Y ) (E[(x - X )(y-Y )) và đƣợc xác định nhƣ sau: 1 xy = (x x)(y y) n  i i xy đƣợc dùng làm thƣớc đo quan hệ giữa hai đại lƣợng y và x. Nếu y và x đồng biến thì hiệp phƣơng sai dƣơng, nếu y và x nghịch biến thì hiệp phƣơng sai âm. Từ công thức định nghĩa trên, có thể xác định hệ số tƣơng quan của phƣơng trình tƣơng quan tuyến tính theo công thức toán sau (dùng hàm Pearson trong Excel): BÀI GIẢNG: Ứng dụng Excel trong Địa chất công trình 57
Người biên soạn: Phan Tự Hướng (x x)(y y) R =  i i 2 2 (xi x) (yi y) Các tính chất của hệ số tương quan (R): - Giới hạn của hệ số tƣơng quan: -1 R 1 - Nếu R > 0, y và x có sự phụ thuộc tƣơng quan tuyến tính thuận (hình 5.18a). - Nếu R < 0, y và x có sự phụ thuộc tƣơng quan tuyến tính nghịch (hình 5.18b). - Nếu R = 1, giữa y và x có sự phụ thuộc hàm số bậc nhất (hình 5.18c). - Nếu R = 0, giữa y và x không có sự phụ thuộc tƣơng quan. R phản ánh mức độ phụ thuộc tƣơng quan,R càng lớn thì mức độ phụ thuộc tƣơng quan tuyến tính giữa y và x càng chặt chẽ. (a) (b) (c) Hình 5.18: Mối phụ thuộc tương quan Theo Kalomenxki, mức độ phụ thuộc tƣơng quan giữa các đại lƣợng ngẫu nhiên nghiên cứu đƣợc đánh giá qua giá trị tuyệt đối của hệ số tƣơng quan nhƣ sau: 0 < R 0,5 : Mức độ phụ thuộc tƣơng quan rất yếu. 0,5 <R 0,7 : Mức độ phụ thuộc tƣơng quan yếu. 0,7 <R 0,9 : Mức độ phụ thuộc tƣơng quan chặt. 0,9 <R 1,0 : Mức độ phụ thuộc tƣơng quan rất chặt. Với mối tƣơng quan tuyến tính y = ax+b, có thể sử dụng các hàm Intercept, Linest, Slope, Forecast. Các hàm này đƣợc mô tả nhƣ sau: Cú pháp hàm Mô tả INTERCEPT(Known_y’s, Trả về giá trị y khi x=0 (tức là y=b) bằng cách dùng mảng giá trị x, y đã Known_x’s) biết. Known_x’s là mảng độc lập, Known_y’s là mảng phụ thuộc tương quan. Có thể đăng nhập các số trực tiếp. LINEST(known_y's, Trả về hệ số góc a của kiểu tương quan. Const là giá trị logical, nhằm known_x's,const,stats) định rõ có hay không giá trị b. - Nếu const là TRUE hoặc bỏ qua, b được tính toán bình thường. BÀI GIẢNG: Ứng dụng Excel trong Địa chất công trình 58
Người biên soạn: Phan Tự Hướng - Nếu const là FALSE, b sẽ được đặt là 0 phương trình trở thành y= ax. SLOPE(known_y's,known_x's) Trả về hệ số góc a của kiểu tương quan. PEARSON(array1,array2) Trả về hệ số tương quan Pearson dựa trên các giá trị đã cho. Hệ số này chính là hệ số tương quan R trong lý thuyết xác suất thống kê. RSQ(known_y's,known_x's) Trả về bình phương của hệ số tương quan Pearson (R2) dựa trên các giá trị đã cho. FORECAST(x,known_y's, Ngoại suy giá trị y khi biết giá trị x theo đường tương quan y = ax+b. known_x's) Trong thí nghiệm cắt phẳng ở trong phòng, các hàm trên đƣợc sử dụng để xác định lực dính kết C và góc ma sát trong từ phƣơng trình sức kháng cắt (hình 5.19). Hình 5.19: Các hàm xác định theo phương pháp bình phương bé nhất áp dụng trong thí nghiệm cắt phẳng 1 trục trong phòng BÀI GIẢNG: Ứng dụng Excel trong Địa chất công trình 59
Người biên soạn: Phan Tự Hướng BÀI 6: IN BẢNG TÍNH Sau khi đã hoàn thành việc xử lý dữ liệu, tính toán và trang trí, bạn có thể tiến hành các thao tác in ấn để thể hiện các kết quả trên giấy. 1. THIẾT LẬP CÁC THÔNG SỐ IN a. Kiểm tra và cài đặt máy in Máy in thƣờng đã đƣợc cài đặt trong Window, máy in hiện hành đƣợc cài đặt trong Start\ Setting\Printers. Trƣớc khi in, bạn phải kiểm tra xem máy in đã đƣợc cài đặt hay chƣa. Khi thực hiện lệnh in, máy in và cấu hình của nó sẽ đƣợc hiển thị trong mục Printer (hình 6.3). Bạn có thể xem hoặc thay đổi cấu hình máy in trong phần Properties. b. Định dạng giấy in Chọn File/Page Setup, cửa sổ Page Setup hiện ra (hình 6.1). Cửa sổ gồm các phần Page, Margins, Header/Footer, Sheet. Sau đây ta làm việc với từng phần. . Định dạng trang in: chọn Page (hình 6.1a). Có các lựa chọn sau: - Portrait : chọn giấy đứng. - Landscape : chọn giấy ngang. - Paper size : chọn cỡ giấy, thƣờng chọn A4 (297x210mm). - Adjust to : đặt tỷ lệ in so với kích thƣớc thực tế, mặc định 100%. (a) (b) Hình 6.1: Cửa sổ định dạng giấy để in BÀI GIẢNG: Ứng dụng Excel trong Địa chất công trình 60
Người biên soạn: Phan Tự Hướng . Định dạng lề in: chọn Margins (hình 6.1b). Bạn chọn lề trái, phải, trên, dƣới của trang giấy. (a) (b) Hình 6.2: Cửa sổ định dạng lề và tiêu đề in . Chọn tiêu đề đầu và tiêu đề cuối: chọn Header/Footer (hình 6.2a). Một số mặc định tên tiêu đề trong hộp thả xuống mục Header và Footer, mặc định là (none). Nếu bạn muốn tạo riêng thì vào Custom Header và Custom Footer . Chọn Sheet: nhƣ hình 6.2b. Có một số lựa chọn sau (mặc định không): - Print area : chọn vùng in. - Print titles : in tiêu đề, tiêu đề hàng hoặc cột có thể đƣợc lặp lại nếu chọn Rows to repeat at top hoặc Columns to repeat at left. - Gridlines : in đƣờng lƣới ô. - Black and White : chỉ in ra màu đen và trắng. - Draft quanlity : in nháp (chất lƣợng in kém). - Row and column headings: in tên tiêu đề hàng và cột. c. Xem trang trước khi in Trong quá trình định dạng giấy in, bạn có thể xem trƣớc trang in trong phần Print Preview (hình 6.3) hoặc chọn File\Print Preview. Nên xem trƣớc khi in đề phòng sai lầm. 2. LỆNH IN Khi thiết lập các thông số in xong, bạn thực hiện in nhƣ sau: . Cách 1: Chọn File\Print. BÀI GIẢNG: Ứng dụng Excel trong Địa chất công trình 61
Người biên soạn: Phan Tự Hướng . Cách 2: Nhấn phím Ctr+P. Cửa sổ in sẽ hiện ra (hình 6.3). Thông số máy in sẽ hiện ra ở phần Printer, bạn có thể thay đổi trong Properties Bạn cần khai báo số trang in trong Number of copies, mặc định là 1. Cuối cùng là lựa chọn những trang in, từ trang (From) đến trang (To) hoặc in tất cả (All). Hình 6.3: Cửa sổ lệnh in BÀI GIẢNG: Ứng dụng Excel trong Địa chất công trình 62
Người biên soạn: Phan Tự Hướng PHỤ LỤC: SẢN PHẨM CỦA EXCEL TRONG ĐCCT - ĐKT Tôi xin giới thiệu một số sản phẩm ứng dụng trên Excel trong lĩnh vực ĐCCT - ĐKT. Đây là những tài liệu tôi sƣu tầm từ nhiều nguồn khác nhau trên Internet. 1. THÍ NGHIỆM TRONG PHÒNG Hình 1: Biểu đồ thí nghiệm cắt phẳng 1 trục trong phòng Hình 2: Biểu đồ thí nghiệm nén cố kết BÀI GIẢNG: Ứng dụng Excel trong Địa chất công trình 63
Người biên soạn: Phan Tự Hướng Hình 3: Biểu đồ thí nghiệm xác định giới hạn chảy theo Casagrande Hình 4: Xây dựng vòng tròn Mohr's trong thí nghiệm cắt BÀI GIẢNG: Ứng dụng Excel trong Địa chất công trình 64
Người biên soạn: Phan Tự Hướng Hình 5: Chương trình xử lý thống kê chỉ tiêu cơ lý đất đá Hình 6: Kết quả xử lý thống kê chỉ tiêu sức kháng cắt của đất BÀI GIẢNG: Ứng dụng Excel trong Địa chất công trình 65
Người biên soạn: Phan Tự Hướng 2. THÍ NGHIỆM NGOÀI TRỜI Hình 7: Biểu đồ thí nghiệm cắt cánh trong hố khoan Hình 8: Biểu đồ thí nghiệm nén ngang tronmg hố khoan BÀI GIẢNG: Ứng dụng Excel trong Địa chất công trình 66
Người biên soạn: Phan Tự Hướng Hình 9: Biểu đồ thí nghiệm nén tĩnh cọc bê tông Hình 10: Biểu đồ thí nghiệm xuyên tĩnh CPT BÀI GIẢNG: Ứng dụng Excel trong Địa chất công trình 67
Người biên soạn: Phan Tự Hướng 3. TÍNH TOÁN NỀN MÓNG VÀ CÁC CHƯƠNG TRÌNH KHÁC Hình 11: Chương trình vẽ mặt cắt ĐCCT và lập báo cáo kết quả khảo sát ĐCCT GeoSection 3.0 có giao diện trên nền bảng tính Hình 12: Nhập dữ liệu hố khoan trong GeoSection 3.0 BÀI GIẢNG: Ứng dụng Excel trong Địa chất công trình 68
Người biên soạn: Phan Tự Hướng Hình 13: Tính toán độ lún Hình 14: Tính toán ổn định mái dốc theo phương pháp Bishop BÀI GIẢNG: Ứng dụng Excel trong Địa chất công trình 69
Người biên soạn: Phan Tự Hướng Hình 15: Tính sức chịu tải của cọc theo đất nền BÀI GIẢNG: Ứng dụng Excel trong Địa chất công trình 70
Người biên soạn: Phan Tự Hướng Hình 16: Tính toán ổn định mái dốc theo phương pháp Fellenius BÀI GIẢNG: Ứng dụng Excel trong Địa chất công trình 71
Người biên soạn: Phan Tự Hướng TÀI LIỆU THAM KHẢO [1]. Mark Dodge, Craig Stinson, 2000. Microsoft Excel 2000 toàn tập. Nhà xuất bản trẻ. [2]. Phan Tự Hƣớng, 2006. Ứng dụng ngôn ngữ VBA trong Excel để giải một số bài toán trong Địa chất công trình. Tạp chí khoa học Mỏ- Địa chất. [3]. Tô Xuân Vu, Hoàng Kim Bảng, 2002. Bài giảng Tin học ứng dụng cho chuyên ngành Địa chất công trình. Trƣờng Đại học Mỏ- Địa chất. [4]. Diễn đàn GPE, 2008. Công thức và hàm trong Excel. [5]. Phan Tự Hƣớng, 2009. Lập trình VBA trong Excel (tái bản lần 1). Nhà xuất bản Thống kê. [6]. John Walkenbach, 2001. Excel 2002 Power Programming with VBA. M&T Books. [7]. Microsoft, 2003. Visual Basic Editor Help, Microsoft Office. [8]. Các trang web: www.giaiphapexcel.com www.caulacbovb.com www.ketcau.com www.cadviet.com BÀI GIẢNG: Ứng dụng Excel trong Địa chất công trình 72
Người biên soạn: Phan Tự Hướng MỤC LỤC GIỚI THIỆU 2 BÀI 1: MỞ ĐẦU 3 1. KHỞI ĐỘNG EXCEL 4 2. CỬA SỔ ỨNG DỤNG CỦA EXCEL 4 a. Thanh tiêu đề (Title bar) 4 b. Thực đơn lệnh (Menu bar) 4 c. Các thanh công cụ (Toolbars) 4 d. Thanh công thức (Formular bar) 4 e. Cửa sổ Workbook 5 3. QUẢN LÝ WORKBOOK 5 a. Lưu workbook 6 b. Tạo workbook mới 7 c. Mở file workbook có sẵn trong đĩa 7 d. Đóng cửa sổ workbook đang làm việc 7 BÀI 2: NHẬP VÀ HIỆU CHỈNH DỮ LIỆU 8 1. CHỌN Ô 8 a. Chọn ô đơn 8 b. Chọn khối ô phạm vi liền kề nhau 8 c. Chọn khối ô không liền nhau 8 d. Chọn toàn bộ các ô trên hàng hoặc cột 8 e. Chọn toàn bộ các ô trên sheet 8 2. CHỌN CÁC SHEET TRONG WORKBOOK 9 3. CÁC KIỂU DỮ LIỆU TRONG EXCEL 9 a. Nhãn, chuỗi ký tự (Label/ String) 9 b. Số (Number) 9 c. Công thức (Formular) 10 4. NHẬP VÀ SỬA DỮ LIỆU 11 a. Nhập dữ liệu 11 b. Sửa dữ liệu 12 5. XỬ LÝ DỮ LIỆU 12 a. Xóa dữ liệu trong ô 12 b. Huỷ bỏ lỗi, hành động vừa thực hịên 12 c. Lặp lại hành động cuối cùng 12 d. Di chuyển và sao chép dữ liệu trong ô 13 e. Di chuyển hoặc sao chép một phần nội dung của một ô vào một ô khác 13 f. Điền dữ liệu tự động vào các ô liền kề nhau 13 BÀI GIẢNG: Ứng dụng Excel trong Địa chất công trình 73
Người biên soạn: Phan Tự Hướng BÀI 3: ĐỊNH DẠNG BẢNG TÍNH 15 1. ĐỊNH DẠNG DỮ LIỆU VÀ Ô 15 a. Thay đổi định dạng chữ trong ô (tab Font- hình 3.2) 15 b. Thay đổi vị trí chữ trong một ô hoặc nhiều ô (tab Alignment- hình 3.3) 16 c. Thay đổi đường viền trong một ô hoặc nhiều ô (tab Border- hình 3.6) 18 d. Thay đổi màu nền trong ô (tab Patterns- hình 3.7) 19 e. Định dạng số (tab Number- hình 3.10) 19 f. Bảo mật ô (tab Protection- hình 3.13) 22 2. XỬ LÝ CỘT, HÀNG VÀ Ô 23 a. Thay đổi độ rộng của cột và chiều cao hàng 23 b. Chèn ô, hàng hoặc cột 24 c. Xóa ô, hàng hoặc cột 25 3. LÀM VIỆC VỚI WORKBOOK VÀ WORKSHEET 26 a. Chèn worksheet mới 26 b. Di chuyển và sao chép sheet 26 c. Xóa các sheet trong workbook 27 d. Giấu toàn bộ hay một phần của workbook 27 e. Hiển thị nhiều workbook cùng một lúc 28 f. Giữ các nhãn cột và hàng thấy được khi bạn cuộn màn hình 28 g. Các phím tắt trong Excel 29 BÀI 4: SỬ DỤNG CÁC HÀM TRONG EXCEL 31 1. HÀM MATH & TRIG 32 a. SUM 32 b. ROUND 32 c. INT 33 d. MOD 33 e. SQRT 33 f. POWER 33 g. LOG 33 h. DEGREES 33 i. RADIANS 33 j. Các hàm về lượng giác: SIN, COS, ASIN, ACOS, TAN, ATAN 34 2. HÀM STATISTICAL 34 a. AVERAGE 34 b. MAX 34 c. MIN 34 d. COUNT 34 e. LARGE 35 BÀI GIẢNG: Ứng dụng Excel trong Địa chất công trình 74
Người biên soạn: Phan Tự Hướng f. MODE 35 g. RANK 35 h. VAR 35 i. STDEVP 35 k. STDEV 35 l. COVAR 35 3. HÀM TEXT 36 a. CONCATENATE 36 b. LEFT 36 c. RIGHT 37 d. UPPER 37 e. REPT 37 f. TRIM 37 4. HÀM LOGIC 37 a. IF 37 b. AND 37 c. OR 37 5. HÀM LOOKUP & REFERENCE 38 a. LOOKUP 38 b. VLOOKUP 39 c. Hàm HLOOKUP 40 6. HÀM USER DEFINED 42 7. TÌM HIỂU CÁC LỖI TRẢ VỀ BỞI CÔNG THỨC 44 a. Lỗi ##### 44 b. Lỗi #DIV/0! 44 c. Lỗi #NAME? 44 d. Lỗi #NULL! 45 e. Lỗi #NUM! 45 f. Lỗi #REF! 45 g. Lỗi #VALUE! 45 BÀI 5: BIỂU ĐỒ TRONG EXCEL 46 1. TẠO BIỂU ĐỒ 46 a. Chọn các ô chứa dữ liệu cần vẽ biểu đồ 46 b. Bấm nút Chart Wizard trên thanh Standard (hình 1.4) hoặc vào Menu\Insert\Chart 46 2. HIỆU CHỈNH BIỂU ĐỒ 49 a. Định dạng lại các đối tượng, thành phần của biểu đồ 50 b. Điều chỉnh và bổ sung một số yếu tố trong biểu đồ dạng XY 51 BÀI GIẢNG: Ứng dụng Excel trong Địa chất công trình 75
Người biên soạn: Phan Tự Hướng c. Xác định phương trình tương quan thực nghiệm 55 d. Phương pháp bình phương bé nhất 57 Các tính chất của hệ số tương quan (R): 58 BÀI 6: IN BẢNG TÍNH 60 1. THIẾT LẬP CÁC THÔNG SỐ IN 60 a. Kiểm tra và cài đặt máy in 60 b. Định dạng giấy in 60 c. Xem trang trước khi in 61 2. LỆNH IN 61 PHỤ LỤC: SẢN PHẨM CỦA EXCEL TRONG ĐCCT - ĐKT 63 1. THÍ NGHIỆM TRONG PHÕNG 63 2. THÍ NGHIỆM NGOÀI TRỜI 66 3. TÍNH TOÁN NỀN MÓNG VÀ CÁC CHƢƠNG TRÌNH KHÁC 68 TÀI LIỆU THAM KHẢO 72 BÀI GIẢNG: Ứng dụng Excel trong Địa chất công trình 76