Giáo trình Chuyên đề Bảng tính Excel

 và tất cả các thể loại này đều biểu thị 
 một phần giá trị của toàn bộ hình tròn. 
 o Bar: biểu đồ thanh. Sử dụng khi: 
 . Các nhãn trục quá dài. 
 . Các giá trị được biểu thị là các quãng thời gian 
 o Area: biểu đồ vùng. Được dùng khi muốn biểu diễn sự thay đổi theo 
 thời gian và hướng sự chú ý đến tổng giá trị theo một khuynh hướng 
 nào đó. 
 o X Y(Scatter): biểu đồ phân tán. Sử dụng khi : 
 0812 114 345 |  tinhocsaoviet.com Trang 27 
Trung Tâm Tin Học Sao Việt 90/47 Đồng Khởi, Tân Phong, Biên Hòa 
 . Muốn thay đổi thang tỷ lệ của trục ngang. 
 . Muốn đặt trục đó theo tỷ lệ lô-ga-rit. 
 . Các giá trị của trục ngang được cách quãng không đều. 
 . Có nhiều điểm dữ liệu trên trục ngang. 
 . Muốn điều chỉnh thang đo trục độc lập của biểu đồ tán xạ để 
 cung cấp thêm thông tin về các dữ liệu có chứa cặp hoặc nhóm 
 giá trị. 
 . Muốn biểu thị điểm giống nhau giữa các tập hợp dữ liệu lớn 
 thay vì sự khác biệt giữa các điểm dữ liệu. 
 . Muốn so sánh nhiều điểm dữ liệu mà không liên quan đến thời 
 gian, càng nhiều dữ liệu được đưa vào thì càng đưa ra những 
 so sánh tốt hơn. 
 o Stock: biểu đồ chứng khoán. Thường dùng trong việc minh họa 
 những dao động lên xuống của giá cổ phiếu hay lượng mưa hàng 
 ngày, nhiệt độ hàng năm. 
 o Surface: biểu đồ bề mặt. Sử dụng để biểu diễn mối quan hệ giữa các 
 lượng dữ liệu lớn (trường hợp dạng biểu đồ khác khó xem) 
 o Radar: so sánh các giá trị tổng hợp của một vài chuỗi dữ liệu 
 o Combo : biểu đồ kết hợp.Biểu đồ này kết hợp 2 hay nhiều dạng biểu 
 đồ khác nhau để biểu diễn cho nguồn dữ liệu đa dạng. 
 - Bước 6: Điều chỉnh màu sắc cho đồ thị. Chọn đồ thị Chart 
 Tools Design Change Colors bảng màu xuất hiện và chọn màu tùy 
 thích. 
 b. Các thao tác trên đồ thị 
 - Di chuyển và thay đổi kích thước đồ thị 
 o Di chuyển: chọn đồ thị Chart Tools Design Location Move 
 Chart 
 0812 114 345 |  tinhocsaoviet.com Trang 28 
Trung Tâm Tin Học Sao Việt 90/47 Đồng Khởi, Tân Phong, Biên Hòa 
 Đặt chart ở 
 worksbook mới 
 Đặt chart ở trong 
 cùng workbook 
 o Hoặc: R_Click trên đường bao của đồ thị, chọn Move chart. 
 - Thay đổi kích thước đồ thị: đưa chuột vào đường bao của đồ thị, kéo chuột 
 di chuyển đến kích thước mong muốn 
 - Sao chép đồ thị: chọn đồ thị nhấn Ctrl-C, di chuyển đến nơi đặt nhấn 
 Ctrl-V 
 - Xóa đồ thị: chọn đồ thị nhấn Delete. 
 - R_Click trên đồ thị chọn Format Chart Area chọn thành phần muốn 
 hiệu chỉnh. 
 Thao tác với đối tượng đồ họa(viết sau-giống word) 
 a. Chèn ClipArt 
 b. Chèn SmartArt 
 c. Chèn Shape 
 d. Chèn Image 
 Đánh giá dữ liệu bằng Sparklines 
Sparklines cách nhanh và đơn giản nhất để thêm thành phần đồ thị cỡ nhỏ (mini) 
trong một ô (cell). Sparklines tập trung vào các giá trị tối đa và tối thiểu bằng 
các màu sắc khác nhau để phân tích xu hướng dữ liệu như: tiêu dùng, doanh 
thuVí dụ, ta có bảng dữ liệu sau, dùng Sparklines để xem tỉ lệ lao động theo 
mỗi năm của từng nhóm tuổi. Tùy thuộc vào dữ liệu mà ta sẽ chọn dạng 
Sparklines là Column/Line/WinLoss. 
 0812 114 345 |  tinhocsaoviet.com Trang 29 
Trung Tâm Tin Học Sao Việt 90/47 Đồng Khởi, Tân Phong, Biên Hòa 
 a. Cách thực hiện: 
 - Bước 1: chọn dòng dữ liệu muốn thống kê 
 - Bước 2: Vào INSERT Sparklines chọn dạng, màn hình xuất hiện 
 Dòng dữ liệu đã chọn ở Bước 1 
 Chọn nơi đặt Sparklines 
 Nhấn OK để hoàn tất 
 b. Hiệu chỉnh Sparklines 
 Chọn ô chứa sparklines cần hiệu chỉnh, R_Click Sparklines Tools chọn 
 nhóm cần hiệu chỉnh: 
 - Sparkline: hiệu chỉnh vùng dữ liệu hay vị trí đặt sparklines 
 - Type: điều chỉnh dạng sparklines 
 - Show: chọn cách hiển thị điểm trong sparklines. 
 - Style: chọn kiểu hiển thị cho loại sparklines và màu sắc 
 0812 114 345 |  tinhocsaoviet.com Trang 30 
Trung Tâm Tin Học Sao Việt 90/47 Đồng Khởi, Tân Phong, Biên Hòa 
 - Group: nhóm sparklines hay gỡ bỏ các sparklines. 
 Chia sẻ dữ liệu 
 Chia sẻ bảng tính 
 a. Lưu trữ với định dạng excel (.xlsx) 
 o Ctr + S hoặc File Save, màn hình xuất hiện chọn vị trí lưu 
 . OneDrive: lưu trữ bảng tính qua dịch vụ điện toán đám mây 
 . Computer: lưu trữ bảng tính trong máy tính 
 . Add a Place: chọn một dịch vụ lưu trữ dữ liệu khác. 
 Chọn ổ đĩa/thư mục 
 cần lưu 
 - Màn hình xuất hiện tiếp theo sau khi chọn nơi lưu 
 0812 114 345 |  tinhocsaoviet.com Trang 31 
Trung Tâm Tin Học Sao Việt 90/47 Đồng Khởi, Tân Phong, Biên Hòa 
 1.Chọn ổ đĩa/ 
 thư mục cần lưu 
 2.Nhập tên tập 
 tin 
 3.Nhấn Save 
 b. Lưu workbook với định dạng tùy ý 
 - Các dạng có thể lưu: 
 o .xlsm: định dạng excel có chứa 
 macro 
 o .xls: định dạng excel 97-2000 
 o .mhtm, .mhtml: định dạng web 
 o .xltx: định dạng template dùng 
 cho việc tạo bảng định dạng mẫu 
 o .txt/ .csv: định dạng text 
 o .pdf: định dạng pdf 
 o .xps: định dạng XPS 
Khi lưu workbook với một trong các định dạng này ta có thể lựa chọn các tùy chọn nội 
dung muốn thêm vào trong workbook trong mục Options. 
 c. Lưu workbook trên OneDrive 
 0812 114 345 |  tinhocsaoviet.com Trang 32 
Trung Tâm Tin Học Sao Việt 90/47 Đồng Khởi, Tân Phong, Biên Hòa 
 1. Chọn OneDrive 
 d. Chia sẻ workbook qua e.mail 
 File Share màn hình xuất hiện chọn Email chọn định dạng tập tin 
 muốn đính kèm: Send as Attachment/Send a link/ Send as PDF/Send as 
 XPS/Send as Internet Fax. nhập tài khoản OutLook Express send. 
 Quản lý ghi chú (comments) 
 - Thêm ghi chú :R_Click trên ô cần ghi chú, chọn Insert Comment nhập nội 
 dung Enter 
 - Xóa ghi chú : R_Click trên ô có ghi chú Delete Comments 
 - Sửa ghi chú : R_Click trên ô có ghi chú Edit Comments 
 - Ẩn/hiện ghi chú : R_Click trên ô có ghi chú Show/Hide Comments 
 Sắp xếp và lọc dữ liệu 
Sort (sắp xếp) và Filter (lọc) là những tính năng cho phép chúng ta thao tác dữ liệu 
trong một bảng tính được thiết lập dựa trên các tiêu chuẩn đã có. 
 Lọc dữ liệu 
 - Click vào cột hoặc chọn các cột có chứa dữ liệu muốn lọc 
 - Tab Data Filter, click vào mũi tên phía dưới ô tiêu đề, có 3 dạng hiển thị 
 tương ứng đối với kiểu dữ liệu. 
 0812 114 345 |  tinhocsaoviet.com Trang 33 
Trung Tâm Tin Học Sao Việt 90/47 Đồng Khởi, Tân Phong, Biên Hòa 
 o Nếu cột dữ liệu là kiểu text, ta chọn Text Filters, chọn tùy chọn và 
 nhập dữ liệu lọc tương ứng 
 o Nếu cột dữ liệu là kiểu số, ta chọn Number Filter và nhập dữ liệu lọc 
 tương ứng 
 o Nếu cột dữ liệu là kiểu ngày, ta chọn Date Filters, chọn các tùy chọn 
 dữ liệu lọc tương ứng. 
 Sắp xếp dữ liệu 
 - Để thực hiện một sắp xếp theo chiều tăng dần hay giảm dần trên một cột: 
 0812 114 345 |  tinhocsaoviet.com Trang 34 
Trung Tâm Tin Học Sao Việt 90/47 Đồng Khởi, Tân Phong, Biên Hòa 
 . Đánh dấu các ô muốn được sắp xếp 
 . Click Sort & Filter trên tab Data 
 . Kích nút Sort Ascending (A-Z) hay 
 Sort Descending (Z-A) 
 - Tùy chỉnh sắp xếp 
 . Muốn sắp xếp nhiều hơn một cột 
 Click Sort & Filter 
 Chọn cột muốn sắp xếp đầu tiên (ưu tiên) 
 Chọn Add level chọn cột tiếp theo muốn sắp xếp OK 
 0812 114 345 |  tinhocsaoviet.com Trang 35 
Trung Tâm Tin Học Sao Việt 90/47 Đồng Khởi, Tân Phong, Biên Hòa 
 CHƯƠNG 2. PHẦN BÀI TẬP EXCEL CƠ BẢN 
 BÀI TẬP 1 
Thực hành các thao tác cơ bản trên Sheet: Thêm, đổi tên sheet, copy & paste, điền 
dữ liệu tự động, định dạng sheet, table. Sử dụng địa chỉ tương đối, tuyệt đối 
 1. Nhập bảng tính trên Sheet1, sau đó đổi tên Sheet thành GP. 
 2. Dùng Autofill để điền dữ liệu tự động cho số thứ tự (STT) và Tháng. 
 3. Dùng Format table để định dạng bảng trên. 
 4. Định dạng cột tổng bán hàng, chi phí bán và lợi nhuận đơn vị tiền tệ là $, có 
 dấu phân cách hàng nghìn 
 5. Nhập công thức cho ô lợi nhuận (E2) = Tổng bán hàng – Chi phí bán 
 6. Sao chép công thức sang các dòng còn lại. 
 7. Tạo thêm sheet khác kế sheet GP. Đặt tên là Comm. Nhập dữ liệu và định 
 dạng ở Sheet Comm đúng mẫu sau (cột A I, dòng 1 10) : 
 Copy vùng dữ liệu từ B1 đến C9 của sheet GP vào ô A3 của Sheet Comm và 
 chọn 
 0812 114 345 |  tinhocsaoviet.com Trang 36 
Trung Tâm Tin Học Sao Việt 90/47 Đồng Khởi, Tân Phong, Biên Hòa 
 chế độ sao chép là Transpose. Khi đó, bảng tính Comm sẽ có dạng sau :
 8. Tính tiền hoa hồng 1 tại ô B9,B10 với tỉ lệ hoa hồng tương ứng ở B6, B7. 
 Sau đó sao chép công thức ở B9,B10 sang các ô còn lại từ C9,C10 I9,I10 
 9. Tạo thêm sheet thứ 3 có tên là Sales. Sao chép dữ liệu Tháng ở Sheet GP 
 sang sheet Sales đặt ở vị trí như hình dưới 
 10. Chọn ô B4 của Sheet Sales, tạo liên kết dữ liệu đến ô C2 của sheet GP. Sau 
 đó sao chép công thức cho các ô còn lại trên cột B của sheet Sales. 
 11. Tính Tổng bán hàng cho tất cả các tháng tại ô B12. Tính tỉ lệ cho từng tháng 
 = tổng bán hàng mỗi tháng/giá trị tại ô B12. 
 12. Trở lại Sheet GP, tạo liên kết dữ liệu để lấy dữ liệu từ cột C của sheet Sales 
 sang cột F của sheet GP. 
 0812 114 345 |  tinhocsaoviet.com Trang 37 
Trung Tâm Tin Học Sao Việt 90/47 Đồng Khởi, Tân Phong, Biên Hòa 
 BÀI TẬP 2 
Nội dung chính: thực hành sử dụng các dạng địa chỉ và định dạng dữ liệu 
 1. Lập công thức để điền giá trị cho cột thành tiền. 
 2. Lập công thức tính thuế cho tháng 1 tại F4, sau đó chép công thức sang 
 tháng 2, 3 
 3. Tính tổng tiền cho các ô ở dòng tổng cộng 
 BÀI TẬP 3 
 Nội dung chính: sử dụng các hàm IF, ROUND, MIN, MAX, AVG
 1. Điểm trung bình (ĐIỂM TB) được tính theo công thức: =(Điểm LT + 
 Điểm TH *2) / 3. Làm tròn 2 số lẻ. 
 2. Kết quả: nếu ĐIỂM TB>=5 thì đậu, ngược lại là rớt 
 0812 114 345 |  tinhocsaoviet.com Trang 38 
Trung Tâm Tin Học Sao Việt 90/47 Đồng Khởi, Tân Phong, Biên Hòa 
 3. Xếp loại như sau: 
 - Nếu 0 < Điểm TB < 5 thì Yếu. 
 - Nếu 5 <= Điểm TB < 7 thì Trung bình 
 - Nếu 7 <= Điểm TB < 8 thì Khá. 
 - Nếu 8 <= Điểm TB < 9 thì Giỏi 
 - Nếu 9 <= Điểm TB <= 10 thì XS 
 4. Tính Cao nhất, Trung bình, Thấp nhất 
 BÀI TẬP 4 
Nội dung: Sử dụng hàm IF và các hàm thao tác trên chuỗi, định dạng bảng 
tính. Sắp sếp dữ liệu. 
Bảng tính 1 
 1. Mã xuất nhập khẩu: 2 ký tự đầu của MÃ NV 
 2. Tên sản phẩm: nếu ký tự thứ 3 là G ghi là Gạo, là D ghi là đường, là T ghi là 
 Than 
 3. Số hiệu: 3 ký tự cuối chuyển về số 
 4. Lọc ra những nhân viên có số hiệu là 3. 
 Bảng tính 2 
 0812 114 345 |  tinhocsaoviet.com Trang 39 
Trung Tâm Tin Học Sao Việt 90/47 Đồng Khởi, Tân Phong, Biên Hòa 
 1. Định dạng LƯƠNG CƠ BẢN có dấu phân cách hàng nghìn, đơn vị là VNĐ 
 2. Chức vụ: dựa vào 2 ký tự đầu của MÃ GV: nếu là HT ghi là hiệu trưởng, nếu 
 là HP ghi là hiệu phó, còn lại ghi là Giáo viên 
 3. BẬC LƯƠNG là 2 ký tự cuối của MÃ GV 
 4. Thêm vào cột PHỤ CẤP sau cột LƯƠNG CƠ BẢN. Nếu chức vụ là hiệu 
 trưởng thì phụ cấp 200000, nếu là hiệu phó phụ cấp 150000, còn giáo viên 
 phụ cấp 100000. 
 5. Thêm vào cột THỰC LÃNH = LƯƠNG CƠ BẢN * HỆ SỐ + PHỤ CẤP. 
 6. Sắp xếp dữ liệu theo Bậc lương tăng dần, THỰC LÃNH giảm dần. 
Bảng tính 3 
 1. Tiền thuê = số ngày*giá 
 - Số ngày = ngày đi- ngày đến 
 - Giá dựa vào ký tự trái của số phòng và bảng giá phòng. Dùng IF kết 
 hợp LEFT và địa chỉ tuyệt đối. 
 2. Tiền giảm: Nếu khách bắt đầu thuê phòng vào ngày 15 thì được giảm 30% 
 tiền phòng của ngày đó. 
 3. Tổng trả = Tiền thuê – tiền giảm. 
 4. Tính tổng số tiền thu được. 
 5. Có bao nhiêu loại phòng trong khách sạn đã cho thuê 
 0812 114 345 |  tinhocsaoviet.com Trang 40 
Trung Tâm Tin Học Sao Việt 90/47 Đồng Khởi, Tân Phong, Biên Hòa 
 0812 114 345 |  tinhocsaoviet.com Trang 41 
Trung Tâm Tin Học Sao Việt 90/47 Đồng Khởi, Tân Phong, Biên Hòa 
 BÀI TẬP 5 
Nội dung : Định dạng dữ liệu, sử dụng chức năng Sort, Filter, FreezePane, các hàm 
ngày tháng, MIN, MAX ,AVG ,SUM , COUNT 
1. Nhập liệu bảng tính trên, dữ liệu Họ tên tự thêm vào 
2. Định dạng Lương là VNĐ, có dấu phân cách hàng nghìn. 
3. Thêm vào cột Phòng Ban kế cột Lương, điền dữ liệu cho cột Phòng Ban dựa vào 
2 ký tự đầu MÃ NV và mô tả : nếu là NS ghi là Nhân sự, nếu là KT ghi là Kế toán, 
nếu là IT ghi là Kỹ thuật, còn lại ghi Kinh Doanh. 
4. Thêm vào cột Tuổi kế cột Ngày sinh, điền dữ liệu cho cột Tuổi = Year(Today())-
Year(Ngaysinh). 
5. Thêm vào cột Số tiền chịu thuế. Tính Số tiền chịu thuế = LƯƠNG – ô dữ liệu mức 
tối thiểu. 
6. Thêm vào cột Mức giảm trừ. Điền dữ liệu cho cột này như sau :những nhân viên 
không có con thì mức giảm trừ bằng 0. Những nhân viên có số con từ 1 trở lên thì 
mức giảm trừ = số con * 4.000.000 
 0812 114 345 |  tinhocsaoviet.com Trang 42 
Trung Tâm Tin Học Sao Việt 90/47 Đồng Khởi, Tân Phong, Biên Hòa 
7. Thêm vào cột Thuế phải nộp. Nếu số tiền chịu thuế > Mức giảm trừ thì mới tính 
thuế. Thuế phải nộp = (Số tiền chịu thuế - Mức giảm trừ)*10%. 
8. Thêm vào dòng cuối bảng tính, tính tổng cộng cho cột LƯƠNG, THUẾ, trung 
bình, lớn nhất, nhỏ nhất cho cột TUỔI, LƯƠNG. 
9. Sắp xếp bảng tính theo Phòng ban tăng dần, lương giảm dần. 
10. Lọc ra những nhân viên có năm sinh 1975 
11. Lọc ra những nhân viên có số con bằng 3 
12. Lọc ra những nhân viên ở phòng nhân sự có mức lương lớn hơn 10 triệu. 
13. Thống kê có bao nhiêu nhân viên nữ, nhân viên nam 
14. Tính tổng lương theo mỗi phòng ban 
15. Thống kê mỗi phòng ban có bao nhiêu nhân viên. 
16. Thực hiện chức năng FreezePane cố định cột họ tên để xem dữ liệu các cột còn 
lại. 
 0812 114 345 |  tinhocsaoviet.com Trang 43 
Trung Tâm Tin Học Sao Việt 90/47 Đồng Khởi, Tân Phong, Biên Hòa 
 BÀI TẬP 6 
Nội dung: Sử dụng hàm IF và các hàm thao tác trên chuỗi, kết hợp với các hàm 
luận lý (AND, OR, MIN) 
 1. Thêm vào cột Khu vực dự thi kế cột ƯU TIÊN. Khu vực dự thi dựa vào 3 
 ký tự đầu của Số BD 
 2. Điền dữ liệu cho cột điểm ƯU TIÊN như sau: nếu thí sinh ở KV1 hoặc 
 KV2 và không có điểm thi nào bằng 0 thì được cộng 0.5. Nếu thí sinh ở 
 KV3 và không có điểm thi nào bằng 0 thì cộng 1. Các trường hợp khác 
 không cộng điểm. 
 3. Tổng điểm = TOÁN + LÝ + HÓA + ƯU TIÊN 
 4. Thêm vào cột KHỐI THI sau cột KHU VỰC. Điền dữ liệu cho KHỐI THI 
 dựa vào ký tự thứ 4 của SỐ BD. 
 5. Điền dữ liệu cho cột KẾT QUẢ: biết điểm chuẩn khối A là 15, khối B là 13 
 và khối C là 12. 
 0812 114 345 |  tinhocsaoviet.com Trang 44 
Trung Tâm Tin Học Sao Việt 90/47 Đồng Khởi, Tân Phong, Biên Hòa 
 BÀI TẬP 7 
Nội dung: sử dụng các hàm thao tác trên chuỗi, IF, MID, MOD và các loại địa chỉ. 
1. Loại xe: dựa vào 3 ký tự cuối của MÃ THUÊ với mô tả sau: nếu là MAX là 
máy xúc, nếu là NAH là xe nâng hàng, nếu là TNH là xe tải nhẹ, còn lại là xe tải 
nặng. 
 2. Khách hàng: là ký tự đầu của MÃ THUÊ nối với chuỗi loại xe. Ví dụ MÃ 
 THUÊ là Minh-MAX thì KHÁCH HÀNG sẽ là M_Máy xúc. 
 3. Số ngày thuê được tính từ phần dư của tổng số ngày thuê chia cho 7. ( 
 MOD(ngaytra-ngaythue,7)) 
 4. Số tuần thuê được tính từ phần nguyên của tống số 
 ngày thuê/7 (hàm INT) 
 5. ĐƠN GIÁ THUÊ được tính từ bảng kế bên(lưu ý sử 
 dụng địa chỉ tuyệt đối để ghi nhận giá trị tính 
 toán). 
 6. PHẢI TRẢ = ĐƠN GIÁ THUÊ * (NGÀY TRẢ - NGÀY THUÊ). Lưu ý nếu khách 
 hàng có SỐ TUẦN THUÊ >3 thì giảm 10% ĐƠN GIÁ THUÊ. 
 0812 114 345 |  tinhocsaoviet.com Trang 45