Bài giảng Microsoft Excel - Võ Hà Quang Định

MICROSOFT EXCEL mis.ueh.edu.vn Võ Hà Quang Định - MIS 1 Nội dung • Khái niệm cơ bản • Các bước thực hiện một bảng tính • Các công cụ nâng cao • Macro • Q&A Võ Hà Quang Định - MIS 2 Cơ bản Võ Hà Quang Định - MIS 3 Cơ bản Võ Hà Quang Định - MIS 4 • Tập tin Bảng tính - WorkBook (*.XLSx) • Trang Bảng tính - Sheet (Sheet) • Cột và Hàng - Row & Column (A,B,C, … - 1,2,3, …) • Ô và Vùng - Cell & Reference (A1,B5, … - A1:B10, C5:C15, …) • Tên Ô - Vùng • Địa chỉ: dùng để xác định vị trí của ô hoặc vùng []!|| | Các bước thực hiện 1. Xác định bài toán 2. Nhập số liệu thô và thực hiện các phép tính cho bảng. 3. Các công cụ chuyên dùng. 4. Định dạng bảng tính. 5. In bảng tính. Võ Hà Quang Định - MIS 5 1. Xác định bài toán • Mục tiêu? • Xác định nội dung: – Dữ liệu cần có? – Cách thức xử lý? – Kết quả đạt được? •  số lượng bảng dữ liệu? •  Các cột dữ liệu của một bảng? Võ Hà Quang Định - MIS 6 1. Xác định bài toán • Ví dụ: – Quản lý điểm – môn học trong 1 lớp / 1 khối lớp? – Quản lý Thu – Chi tại cửa hàng? – Quản lý Hàng Hóa? – Quản lý Kế toán? • Thiết kế các bảng dữ liệu Võ Hà Quang Định - MIS 7 2. Nhập dữ liệu – Tính toán • Kiểu dữ liệu? • Các kiểu dữ liệu: – Văn bản (text) – Số (Number) – Ngày – Giờ (Date – Time) – Luận Lý (Logical) – Công thức (Formula) Ví dụ: Võ Hà Quang Định - MIS 8 • Phép toán: & + - * / ^ + - And, Or, Not Tổng hợp 2. Nhập dữ liệu – Tính toán • Tham chiếu trong công thức: – Giá trị cụ thể – Địa chỉ ô, vùng / tên ô, vùng – Toán tử – Hàm • Ví dụ: thành tiền = số lượng * đơn giá * tỷ giá Võ Hà Quang Định - MIS 9 2. Nhập dữ liệu – Tính toán Địa chỉ tương đối • A5, B7 • Chức năng Copy? – Ô chứa: thay đổi – Ô tham chiếu: không • Chức năng Cut? – Ô chứa: không – Ô tham chiếu: thay đổi Địa chỉ tuyệt đối • A$5, $B7, $C$10 • Chức năng Copy? – Ô chứa: không – Ô tham chiếu: không • Chức năng Cut? – Ô chứa: không – Ô tham chiếu: thay đổi Võ Hà Quang Định - MIS 10 2. Nhập dữ liệu – Tính toán • Hàm - functions • Cú pháp chung: • Ví dụ: TODAY(), MID(,,) • Cần: – Mục tiêu của hàm. – Kết quả của hàm. – Các loại tham số tương ứng. – Viết đúng hàm.  Võ Hà Quang Định - MIS 11 Tên hàm() 2. Tính toán - Hàm • Một số qui ước: – Bt: biểu thức cho kết quả kiểu bất kỳ – Bts: …… kết quả kiểu số – Btc: …… kết quả kiểu chuỗi – Btn: …… kết quả kiểu ngày – Btg: …… kết quả kiểu giờ – Btl: ……. kết quả kiểu luận lý • Chú ý: – Tham số: biểu thức, tên / địa chỉ, giá trị hằng Võ Hà Quang Định - MIS 12 2. Tính toán - Hàm • Về chuỗi Võ Hà Quang Định - MIS 13 Tin học đại cương Số Mã số LEFT(,) Tin học A1-34 34 RIGHT() đại cương A2-5 5 MID(,,) học đại A3-123 123 UPPER() TIN HỌC LOWER() tin học PROPER() Học Đại 2. Tính toán - Hàm • Về số Võ Hà Quang Định - MIS 14 ABS() 5 -5 INT() 2 1 MOD(,) 1 0.7 1.7 1 2 ROUND(,) 1234.568 3 4 PRODUCT(,,…,) 5 6 SUM(,,…,) 7 8 SUMIF(,) >=5 9 10 1234.57 3024 25 21 2. Tính toán - Hàm • Về ngày – giờ Võ Hà Quang Định - MIS 15 NOW() TODAY() DAY() MONTH() YEAR() HOUR() BD KT Vượt MINUTE() 8:00 18:20 ??? 01/08/2012 22:33 01/08/2012 1 8 2012 22 33 2. Tính toán - Hàm • Về ngày – giờ Võ Hà Quang Định - MIS 16 DATE(,,) DATEVALUE() TIME(,,) TIMEVALUE() [1] 1(Sunday) --> 7(Saturday) WEEKDAY(,) kiểu= 2 1(Monday) --> 7(Sunday) 3 0(Monday) --> 6(Sunday) 01/08/2012 0:00 15/08/2012 8:10 PM 9:10:00 PM 4 3 2 2. Tính toán - Hàm • Về thống kê Võ Hà Quang Định - MIS 17 MIN() 1 3 6 MAX() 2 5 5 AVERAGE() 7 4 COUNT() 3 9 3 COUNTA() 4 11 2 COUNTBLANK() 5 #N/A COUNTIF(,) >=5 a 15 1 RANK(,[,]) 7 b ####### 1 7 3.67 6 7 1 2 2. Tính toán - Hàm • Về luận lý – Để viết biểu thức điều kiện – Xử lý tình huống có nhiều trường hợp xảy ra • Excel không có toán tử and, or, not • Mà cung cấp hàm – AND(, , ….) – OR(, , ….) – NOT(, , ….) Võ Hà Quang Định - MIS 18 AND OR NOT TRUE TRUE TRUE TRUE FALSE TRUE FALSE FALSE TRUE TRUE FALSE TRUE FALSE TRUE FALSE FALSE FALSE FALSE FALSE TRUE 2. Tính toán - Hàm • Hàm IF(,,) – Điểm đậu là 21.5 – DTB >=9: Giỏi; >=7: Khá; >=5: TB – Võ Hà Quang Định - MIS 19 =IF(DTB>=9,”Giỏi”,IF(DTB>=7,”Khá”,”TB”)) =IF(TC>=21.5,”Đậu”,”Rớt”) =IF(DTB>=9,”Giỏi”,IF(DTB>=7,”Khá”,IF(DTB>=5,”TB”,””))) 2. Tính toán - Hàm • Hàm dò tìm INDEX(,,) – DTB >=9: Giỏi; >=7: Khá; >=5: TB Võ Hà Quang Định - MIS 20 =INDEX($B$5:$B$7,IF(F4>=$A$5,1,IF(F4>=$A$6,2,3)),1) Vùng giá trị GT dò tìm 2. Tính toán - Hàm • Hàm dò tìm MATCH(,, ) kiểu = 1  tăng dần 0  chính xác -1 giảm dần – DTB >=9: Giỏi; >=7: Khá; >=5: TB =INDEX($B$5:$B$7, MATCH(F4,$A$5:$A$7,-1),1) – Điều chỉnh bảng dữ liệu? Võ Hà Quang Định - MIS 21 2. Tính toán - Hàm • Hàm dò tìm VLOOKUP(,, [,]) HLOOKUP(,, [,]) Là trường hợp hai đặc biệt của hàm Index. Võ Hà Quang Định – MIS 22 Vùng giá trị Vùng giá trị Cột dò tìm Dòng dò tìm