Bài giảng Tin học ứng dụng trong hóa học - Chương 2: MS Excel

CHƯƠNG 2: MS EXCEL 
TIN HỌC ỨNG DỤNG TRONG 
HÓA HỌC 
TRƯỜNG ĐẠI HỌC CÔNG NGHIỆP TP.HCM 
KHOA CÔNG NGHỆ HOÁ HỌC 
NỘI DUNG 
1. GIỚI THIỆU VỀ MS – EXCEL 
2. MỘT SỐ HÀM TRONG EXCEL 
3. XÂY DỰNG ĐỒ THỊ - PHƯƠNG 
TRÌNH HỒI QUY TUYẾN TÍNH 
4. GIẢI PHƯƠNG TRÌNH TOÁN HỌC 
5. PHỤ LỤC EXCEL 
1.GIỚI THIỆU VỀ MS EXCEL 
NỘI DUNG 
1.1 KHỞI ĐỘNG – KẾT THÚC 
1.2 CỬA SỔ - THANH CÔNG CỤ 
1.3 LÀM VIỆC VỚI TẬP TIN (FILE) 
1.GIỚI THIỆU VỀ MS EXCEL 
1.1 KHỞI ĐỘNG – KẾT THÚC 
Khởi động MS Excel bằng cách: 
B1. Nhấp chuột nút Start. 
B2. Chọn All Programs Microsoft Office 
 Microsoft Excel 2010. 
B3. Nhấp chuột vào biểu tượng 
Hoặc: nhấp đúp vào biểu tượng 
1.GIỚI THIỆU VỀ MS EXCEL 
1.1 KHỞI ĐỘNG – KẾT THÚC 
Thoát chương trình MS Excel bằng cách: 
Cách 1: Click vào nút Close góc trên bên 
phải cửa sổ. 
Cách 2: Nhấn tổ hợp phím tắt 
Cách 3: Vào tab File chọn Exit 
Ô địa chỉ 
Thanh tiêu đề 
TAB 
thành 
phần 
Thanh Zoom 
Các biểu 
tượng chức 
năng trong 
TAB 
Thanh công thức 
Thanh truy 
xuất nhanh 
Worksheet 
1.GIỚI THIỆU VỀ MS EXCEL 
THANH CÔNG CỤ RIBBON 
1.2 CỬA SỔ - THANH CÔNG CỤ 
1.GIỚI THIỆU VỀ MS EXCEL 
1.2 CỬA SỔ - THANH CÔNG CỤ 
Thanh công cụ Ribbon bao gồm: 
 Home: Lệnh cắt, dán, sao chép, định dạng tài 
liệu, chèn hay xóa dòng hoặc cột, sắp xếp, tìm 
kiếm và lọc dữ liệu, 
Tab đang chọn 
Nhóm lệnh Mở hộp thoại 
Các ngăn chứa 
lệnh (Tabs) 
Ngăn lệnh theo 
ngữ cảnh 
1.GIỚI THIỆU VỀ MS EXCEL 
1.2 CỬA SỔ - THANH CÔNG CỤ 
 Insert: Chèn các loại đối tượng vào bảng tính 
như: bảng biểu, vẽ sơ đồ, đồ thị, ký hiệu,  
 Page layout: Chứa các nút lệnh về việc hiển thị 
bảng tính và thiết lập in ấn. 
1.GIỚI THIỆU VỀ MS EXCEL 
1.2 CỬA SỔ - THANH CÔNG CỤ 
 Formulas: Chèn công thức, đặt tên vùng (range), 
điều khiển việc tính toán của Excel. 
 Data: Các nút lệnh thao đối với dữ liệu trong và 
ngoài Excel, các danh sách, phân tích dữ liệu. 
1.GIỚI THIỆU VỀ MS EXCEL 
1.2 CỬA SỔ - THANH CÔNG CỤ 
 Review: kiễm lỗi chính tả, hỗ trợ dịch từ, chú 
thích vào các ô, các thiết lập bảo vệ bảng tính. 
 View: Thiết lập các chế độ hiển thị của bảng tính 
như: phóng to, thu nhỏ, chia màn hình. 
1.GIỚI THIỆU VỀ MS EXCEL 
1.2 CỬA SỔ - THANH CÔNG CỤ 
 Add - ins: Xuất hiện khi Excel mở một tập tin có 
sử dụng các tiện ích bổ sung, các hàm bổ sung. 
 Chart tools: Các Tab bổ sung khi chọn vào đồ thị 
hay hình ảnh. 
1.GIỚI THIỆU VỀ MS EXCEL 
1.2 CỬA SỔ - THANH CÔNG CỤ 
1.GIỚI THIỆU VỀ MS EXCEL 
1.2 CỬA SỔ - THANH CÔNG CỤ 
Tab File Option 
1.GIỚI THIỆU VỀ MS EXCEL 
1.2 CỬA SỔ - THANH CÔNG CỤ 
Các mục trong hộp thoại Excel Option: 
 General: thiết lập tùy chọn thông dụng như giao 
diện, thông tin cá nhân, 
 Formulas: tùy chọn liên quan công thức tính. 
 Proofings: định dạng văn bản và kiểm tra lỗi chính 
tả. 
 Save: tùy chọn về sao lưu, phục hồi bảng tính. 
 Advanced: tùy chọn nâng cao như in ấn, sao 
chép, biểu đồ, công thức, 
1.GIỚI THIỆU VỀ MS EXCEL 
1.2 CỬA SỔ - THANH CÔNG CỤ 
Các thành phần trong Excel: 
Workbook: tập 
tin làm việc. 
Worksheet: làm việc 
với dữ liệu (bảng tính). 
Chartsheet: sheet chỉ 
chứa đồ thị. 
Sheet tab: nơi chứa tất cả các sheet và chartsheet. 
1.GIỚI THIỆU VỀ MS EXCEL 
1.2 CỬA SỔ - THANH CÔNG CỤ 
Địa chỉ 
tương đối 
Địa chỉ 
tuyệt đối 
Cell: ô tính gọi tên bằng 
tên cột và số thứ tự dòng. 
1.GIỚI THIỆU VỀ MS EXCEL 
1.2 CỬA SỔ - THANH CÔNG CỤ 
Thao tác trên ô cell: chọn ô cell click chuột phải. 
Format cells 
Bao gồm các tab: 
Number, Aligment, 
Font, Border, Fill và 
Protect 
1.GIỚI THIỆU VỀ MS EXCEL 
1.2 CỬA SỔ - THANH CÔNG CỤ 
Định dạng văn bản và số: 
1.GIỚI THIỆU VỀ MS EXCEL 
1.2 CỬA SỔ - THANH CÔNG CỤ 
Các thao tác trong excel: 
Thao tác cột, dòng 
Thao tác Worksheet 
Thao tác cell 
1.GIỚI THIỆU VỀ MS EXCEL 
1.2 CỬA SỔ - THANH CÔNG CỤ 
Định dạng trên tab Home: 
Định dạng 
bảng 
Sắp xếp và 
lọc dữ liệu 
Định dạng 
ô tính 
Tìm kiếm và 
thay thế dữ liệu Định dạng dữ 
liệu có điều kiện 
1.GIỚI THIỆU VỀ MS EXCEL 
1.2 CỬA SỔ - THANH CÔNG CỤ 
Các tính năng dán đặc biệt: Paste Special 
1.GIỚI THIỆU VỀ MS EXCEL 
1.2 CỬA SỔ - THANH CÔNG CỤ 
Hộp thoại Paste Special: mục Paste 
1.GIỚI THIỆU VỀ MS EXCEL 
1.2 CỬA SỔ - THANH CÔNG CỤ 
Hộp thoại Paste Special: mục Paste 
1.GIỚI THIỆU VỀ MS EXCEL 
1.2 CỬA SỔ - THANH CÔNG CỤ 
Hộp thoại Paste Special: mục Operation 
1.3 LÀM VIỆC VỚI TẬP TIN (FILE) 
1.GIỚI THIỆU VỀ MS EXCEL 
 Tạo tập tin mới: 
Tạo workbook rỗng: Blank Workbook Create 
1.3 LÀM VIỆC VỚI TẬP TIN (FILE) 
1.GIỚI THIỆU VỀ MS EXCEL 
 Lưu tập tin: 
Cách 1: File Save 
Cách 2: Click biểu tượng Save trên thanh 
truy xuất nhanh. 
Cách 3: nhấn phím “Ctrl + S” 
1.3 LÀM VIỆC VỚI TẬP TIN (FILE) 
1.GIỚI THIỆU VỀ MS EXCEL 
 Điều chỉnh trang in: Page Layout Page Setup 
Canh lề 
trang 
Chọn khổ 
giấy 
Chiều 
trang in 
Chọn 
vùng in 
Ngắt 
trang 
Thêm hình 
nền 
In tiêu đề 
cột và 
dòng 
1.3 LÀM VIỆC VỚI TẬP TIN (FILE) 
1.GIỚI THIỆU VỀ MS EXCEL 
Mở hộp thoại Page Setup: 
Header/ 
Footer Sheet 
1.3 LÀM VIỆC VỚI TẬP TIN (FILE) 
1.GIỚI THIỆU VỀ MS EXCEL 
 In tập tin: File Print hay Ctrl + P 
Số lượng bản in 
Chọn máy in 
Chọn sheet cần in 
Chọn số trang in 
Thiết lập chế độ sắp giấy 
khi in nhiều bản 
Thiết lập chiều trang in 
Khổ giấy và canh lề trang in 
2. MỘT SỐ HÀM TRONG EXCEL 
NỘI DUNG 
2.1 CÔNG THỨC TRONG EXEL 
2.2 CÁC HÀM TRONG EXCEL 
2.3 MỘT SỐ HÀM TOÁN HỌC CƠ BẢN 
2. MỘT SỐ HÀM TRONG EXCEL 
2.1 CÔNG THỨC TRONG EXCEL 
Công thức trong Excel được bắt đầu bằng dấu “=” 
2. MỘT SỐ HÀM TRONG EXCEL 
2.1 CÔNG THỨC TRONG EXCEL 
Các toán tử trong công thức: 
2. MỘT SỐ HÀM TRONG EXCEL 
2.1 CÔNG THỨC TRONG EXCEL 
Các toán tử trong công thức: 
2. MỘT SỐ HÀM TRONG EXCEL 
2.1 CÔNG THỨC TRONG EXCEL 
Các toán tử trong công thức: 
2. MỘT SỐ HÀM TRONG EXCEL 
2.1 CÔNG THỨC TRONG EXCEL 
Thứ tự ưu tiên của toán tử trong công thức: 
Toán tử Mô tả Ưu tiên 
: (hai chấm) (1 khoảng 
trắng) , (dấu phẩy) 
Toán tử tham chiếu 1 
– Số âm (ví dụ –1) 2 
% Phần trăm 3 
^ Lũy thừa 4 
* và / Nhân và chia 5 
+ và – Cộng và trừ 6 
& Nối chuỗi 7 
= = So sánh 8 
2. MỘT SỐ HÀM TRONG EXCEL 
2.2 CÁC HÀM TRONG EXCEL 
 Nhóm hàm về thống kê: Average, Count, Devsq,.. 
 Nhóm hàm về phân phối xác xuất: Fisher, TDIST,.. 
 Nhóm hàm về tương quan và hồi qui tuyến tính: 
Linest, Slope, 
 Nhóm hàm về tài chính. 
2. MỘT SỐ HÀM TRONG EXCEL 
2.2 CÁC HÀM TRONG EXCEL 
 Nhóm hàm về toán học và lượng giác: ABS, Sum, 
Rank, 
 Nhóm hàm logic: True, False,.. 
 Các hàm về quản lý cơ sở dữ liệu: Daverage, 
Time, Hlookup,.. 
 Các hàm về xử lý văn bản và dữ liệu: Text, 
Lower,.. 
2. MỘT SỐ HÀM TRONG EXCEL 
2.2 CÁC HÀM TRONG EXCEL 
Các lỗi công thức tính: 
2. MỘT SỐ HÀM TRONG EXCEL 
2.3 MỘT SỐ HÀM TOÁN HỌC CƠ BẢN 
Cho một bảng tính sau, hãy: 
1. Tính tổng các cột C,D,E 
2. Tính trị trung bình các cột 
C,D,E 
3. Tìm ô có giá trị tổng lớn 
nhất 
4. Tìm ô có giá trị tổng nhỏ 
nhất 
5. Nếu ô nào có giá trị <5, thì 
đánh giá là Rớt, còn <=5.0 
thì đánh giá là Đạt 
6. Xếp hạng cho các giá trị ở 
các ô 
2. MỘT SỐ HÀM TRONG EXCEL 
2.3 MỘT SỐ HÀM TOÁN HỌC CƠ BẢN 
Hàm SUM(number1,[number2],) 
2. MỘT SỐ HÀM TRONG EXCEL 
2.3 MỘT SỐ HÀM TOÁN HỌC CƠ BẢN 
Hàm AVERAGE(number1,[number2],) 
2. MỘT SỐ HÀM TRONG EXCEL 
2.3 MỘT SỐ HÀM TOÁN HỌC CƠ BẢN 
Hàm MAX(number1,[number2],) 
2. MỘT SỐ HÀM TRONG EXCEL 
2.3 MỘT SỐ HÀM TOÁN HỌC CƠ BẢN 
Hàm MIN(number1,[number2],) 
2. MỘT SỐ HÀM TRONG EXCEL 
2.3 MỘT SỐ HÀM TOÁN HỌC CƠ BẢN 
Hàm IF(logical_test,value_if_true,value_if_false) 
2. MỘT SỐ HÀM TRONG EXCEL 
2.3 MỘT SỐ HÀM TOÁN HỌC CƠ BẢN 
Hàm RANK(number,Ref,order) 
2. MỘT SỐ HÀM TRONG EXCEL 
2.3 MỘT SỐ HÀM TOÁN HỌC CƠ BẢN 
Hàm ROUND(number,digits_number) 
Hàm ABS(number) 
Hàm SQRT(number) 
Hàm VLOOKUP (lookup_value,table_array, 
row_index_num,range_lookup) 
TREND(Known_y’x, Known_x’y, New_x’s, Const) 
3. XÂY DỰNG ĐỒ THỊ - PHƯƠNG 
TRÌNH HỒI QUY TUYẾN TÍNH 
NỘI DUNG 
3.1 CÁC DẠNG BIỂU ĐỒ 
3.2 CÁC BƯỚC XÂY DỰNG ĐỒ THỊ 
3.3 PHƯƠNG TRÌNH HỒI QUY TUYẾN 
TÍNH 
3.1 CÁC DẠNG BIỂU ĐỒ 
3. XÂY DỰNG ĐỒ THỊ - PHƯƠNG TRÌNH 
HỒI QUY TUYẾN TÍNH 
3.1 CÁC DẠNG BIỂU ĐỒ 
3. XÂY DỰNG ĐỒ THỊ - PHƯƠNG TRÌNH 
HỒI QUY TUYẾN TÍNH 
3.1 CÁC DẠNG BIỂU ĐỒ 
Các thành phần trong đồ thị: 
Tên trục X 
Trục X 
Trục Y 
Tên trục Y 
Bảng dữ liệu Đường 
lưới đứng 
Đường 
lưới ngang 
Ký hiệu 
đường 
Tọa độ 
điểm 
Đồ thị Vùng 
đồ thị 
Tên đồ 
thị 
3.2 CÁC BƯỚC XÂY DỰNG ĐỒ THỊ 
3. XÂY DỰNG ĐỒ THỊ - PHƯƠNG TRÌNH 
HỒI QUY TUYẾN TÍNH 
Vẽ đồ thị cho số liệu như bảng sau: 
3.2 CÁC BƯỚC XÂY DỰNG ĐỒ THỊ 
3. XÂY DỰNG ĐỒ THỊ - PHƯƠNG TRÌNH 
HỒI QUY TUYẾN TÍNH 
Bước 1: chọn vùng 
dữ liệu A3:D9 
Bước 2: chọn dạng 
đồ thị. 
3.2 CÁC BƯỚC XÂY DỰNG ĐỒ THỊ 
3. XÂY DỰNG ĐỒ THỊ - PHƯƠNG TRÌNH 
HỒI QUY TUYẾN TÍNH 
Bước 3: chọn 
chart layout để 
ghi tiêu đề cho 
đồ thị. 
3.2 CÁC BƯỚC XÂY DỰNG ĐỒ THỊ 
3. XÂY DỰNG ĐỒ THỊ - PHƯƠNG TRÌNH 
HỒI QUY TUYẾN TÍNH 
Bước 4: đảo 
chuỗi dữ liệu 
dòng/ cột hay cột 
/ dòng chọn 
3.2 CÁC BƯỚC XÂY DỰNG ĐỒ THỊ 
3. XÂY DỰNG ĐỒ THỊ - PHƯƠNG TRÌNH 
HỒI QUY TUYẾN TÍNH 
Bước 5: hiệu chỉnh đồ thị(thay đổi dạng đồ thị, trục 
hoành, trục tung,) 
3.2 CÁC BƯỚC XÂY DỰNG ĐỒ THỊ 
3. XÂY DỰNG ĐỒ THỊ - PHƯƠNG TRÌNH 
HỒI QUY TUYẾN TÍNH 
3.3 PHƯƠNG TRÌNH HỒI QUY TUYẾN TÍNH 
3. XÂY DỰNG ĐỒ THỊ - PHƯƠNG TRÌNH 
HỒI QUY TUYẾN TÍNH 
NỘI DUNG: 
Khi tiến hành đo quang bằng phương pháp dãy 
chuẩn một dãy mẫu chuẩn, cho kết quả: 
Mẫu số 1 2 3 4 5 6 
Cppm 0 5 10 15 20 25 
A Abs 0 0,113 0,231 0,302 0,425 0,552 
Xác định phương trình dãy chuẩn theo dạng: 
A = a + b. C 
3.3 PHƯƠNG TRÌNH HỒI QUY TUYẾN TÍNH 
3. XÂY DỰNG ĐỒ THỊ - PHƯƠNG TRÌNH 
HỒI QUY TUYẾN TÍNH 
Bước 1: chọn vùng dữ liệu B2:C7 
3.3 PHƯƠNG TRÌNH HỒI QUY TUYẾN TÍNH 
3. XÂY DỰNG ĐỒ THỊ - PHƯƠNG TRÌNH 
HỒI QUY TUYẾN TÍNH 
Bước 2: kết quả đồ thị 
3.3 PHƯƠNG TRÌNH HỒI QUY TUYẾN TÍNH 
3. XÂY DỰNG ĐỒ THỊ - PHƯƠNG TRÌNH 
HỒI QUY TUYẾN TÍNH 
Bước 3: hiển thị phương trình hồi quy. 
4. GIẢI PHƯƠNG TRÌNH TOÁN HỌC 
NỘI DUNG 
4.1 GIẢI PT BẬC 2 
4.2 GIẢI HỆ PT BẬC 1 
4.1 GIẢI PT BẬC 2 
4. GIẢI PHƯƠNG TRÌNH TOÁN HỌC 
NỘI DUNG: 
Giải phương trình bậc 2 một ẩn số: 
2X2 - 3X + 1 = 0 
 Dùng hàm Goalseek 
4.1 GIẢI PT BẬC 2 
4. GIẢI PHƯƠNG TRÌNH TOÁN HỌC 
Bước 1: nhập dữ liệu với F(x) = 2X2 – 3X + 1 
4.1 GIẢI PT BẬC 2 
4. GIẢI PHƯƠNG TRÌNH TOÁN HỌC 
Bước 2: vào Goalseek: tab Data nhóm Data 
Tools What if analysis, chọn dòng Goal Seek 
4.1 GIẢI PT BẬC 2 
4. GIẢI PHƯƠNG TRÌNH TOÁN HỌC 
Bước 3: thay đổi X để F(x) = 0. 
4.1 GIẢI PT BẬC 2 
4. GIẢI PHƯƠNG TRÌNH TOÁN HỌC 
Nhận kết quả nghiệm thứ nhất. 
4.1 GIẢI PT BẬC 2 
4. GIẢI PHƯƠNG TRÌNH TOÁN HỌC 
Tìm nghiệm thứ hai: B1 B3 
4.1 GIẢI PT BẬC 2 
4. GIẢI PHƯƠNG TRÌNH TOÁN HỌC 
Nhận nghiệm thứ hai. 
4.2 GIẢI HỆ PT BẬC 1 
4. GIẢI PHƯƠNG TRÌNH TOÁN HỌC 
NỘI DUNG: 
Giải hệ phương trình bậc 1 
X1 + 3X2 + 2X3 = 13 
4X1 - 2X2 + 2X3 = 14 
2X1 + X2 + X3 = 9 
 Dùng hàm Solver 
4.2 GIẢI HỆ PT BẬC 1 
4. GIẢI PHƯƠNG TRÌNH TOÁN HỌC 
Mở Solver 
bằng cách: 
File 
Option 
4.2 GIẢI HỆ PT BẬC 1 
4. GIẢI PHƯƠNG TRÌNH TOÁN HỌC 
4.2 GIẢI HỆ PT BẬC 1 
4. GIẢI PHƯƠNG TRÌNH TOÁN HỌC 
Bước 1: nhập dữ liệu. 
342 GIẢI HỆ PT BẬC 1 
4. GIẢI PHƯƠNG TRÌNH TOÁN HỌC 
Bước 2: nhập giá trị biến và tính VT 
4.2 GIẢI HỆ PT BẬC 1 
4. GIẢI PHƯƠNG TRÌNH TOÁN HỌC 
Bước 3: dùng hàm Solver 
4.2 GIẢI HỆ PT BẬC 1 
4. GIẢI PHƯƠNG TRÌNH TOÁN HỌC 
Bước 4: cài đặt VT = VP 
4.2 GIẢI HỆ PT BẬC 1 
4. GIẢI PHƯƠNG TRÌNH TOÁN HỌC 
4.2 GIẢI HỆ PT BẬC 1 
4. GIẢI PHƯƠNG TRÌNH TOÁN HỌC 
Bước 5: chọn Keep solver solution 
4.2 GIẢI HỆ PT BẬC 1 
4. GIẢI PHƯƠNG TRÌNH TOÁN HỌC 
Kết quả nghiệm: 
5. PHỤ LỤC EXCEL 
a. Không in những vùng ẩn: 
 Ẩn dòng hay cột chứa thông tin: Click chuột phải vào 
đối tượng chọn Hide 
Ngoài ra, ẩn thông tin 
bằng cách tô màu chữ 
giống với màu nền của 
cell hay chèn hình ảnh 
đè lên. Hide 
5. PHỤ LỤC EXCEL 
b. Không in các đối tượng: 
 Không muốn in các đối tượng: Click chuột phải vào 
đối tượng chọn Size and Properties 
Click bỏ chọn 
Print object 
5. PHỤ LỤC EXCEL 
c. Giấu bảng tính Excel: 
 Mở tab Developer: File Option mục Customize 
Ribbon click chọn Developer 
Click chọn 
Developer 
5. PHỤ LỤC EXCEL 
c. Giấu bảng tính Excel: 
Xuất hiện tab Developer: chọn sheet cần giấu nhóm 
Controls chọn Properties 
Click chọn : 
 -1-xlSheetVisible: hiện 
 2-xlSheetVeryHidden: ẩn 
5. PHỤ LỤC EXCEL 
d. Khóa và bảo vệ các ô chứa công thức: 
Bước 1: Click chuột phải vào ô chứa công thức chọn 
Format cells mục Protection bỏ chọn Locked 
Format Cells 
Click bỏ chọn 
Locked 
5. PHỤ LỤC EXCEL 
d. Khóa và bảo vệ các ô chứa công thức: 
Bước 2: tab Home Find & Select Go To Special 
Click chọn 
Formulas 
5. PHỤ LỤC EXCEL 
d. Khóa và bảo vệ các ô chứa công thức: 
Bước 3: Click chọn Locked hay Hiden trong mục 
Protection của hộp thoại Format Cells. 
Click chọn 
Bước 4: Home nhóm Cells 
Format chọn Protect sheet 
5. PHỤ LỤC EXCEL 
d. Khóa và bảo vệ các ô chứa công thức: 
Sử dụng Data Validation: tab Data nhóm Data tools 
 Data Validation 
Chọn mục 
Settings 
Nhập: =“ ” 
 Khi sửa công thức sẽ nhận được cảnh báo từ hệ 
thống nhưng vẫn có thể xóa công thức được. 
Tùy chọn 
mục Custom 
Add your company slogan