Bài giảng Phần mềm bảng tính điện tử - Microsoft excel

MIN(A4:E15)® Cho kết quả là số nhỏ nhất trong giá trị các ô từ A4 đến E15 AVERAGE(number1, number2, ...) Trả về giá trị trung bình cộng của các số trong danh sách tham số. =AVERAGE(1, 2, 3, 5) ®2.75 COUNT(value1, value2, ...) Đếm số các giá trị số trong danh sách tham số. =COUNT(2, “hai”, 4, -6) ® 3 COUNTA(value1, value2, ...) Đếm số các ô không rỗng trong danh sách tham số. =COUNT(2, “hai”, 4, -6) ® 4 COUNTBLANK(range) Đếm số các rỗng trong vùng range. =COUNTBLANK(B4:B12) COUNTIF(range, criteria) Đếm các ô thỏa mãn điều kiện criteria trong vùng range. - range: là vùng mà điều kiện sẽ được so sánh. - criteria: là chuỗi mô tả điều kiện. Ví dụ: "10", ">15", "<20". =COUNTIF(B4:B12, “>=6”) RANK(number, ref [, order]) Trả về thứ hạng của number trong ref, với order là cách xếp hạng. Nếu order = 0 hoặc được bỏ qua thì ref được hiểu là có thứ tự giảm. Nếu order 0 thì ref được hiểu là có thứ tự tăng. =RANK(F4, $F$4:$F$12, 0) =RANK(G4, $G$4:$G$12, 1) 3.2.3. Các hàm logic Cú pháp Ý nghĩa và ví dụ AND(logical1, logical2, ) Trả về giá trị TRUE nếu tất cả các điều kiện đều là TRUE. =AND(3>2, 5-12) ®TRUE OR(logical1, logical2, ) Trả về giá trị TRUE nếu có ít nhất một điều kiện là TRUE. =OR(2>3, 123) ® TRUE =OR(2>3, 123)® FALSE NOT(logical) Lấy phủ định của giá trị logical. =NOT(2>3) ® TRUE IF(logical_test, value_if_true, value_if_false) Trả về giá trị thứ nhất value_if_true nếu điều kiện logical_test là TRUE, ngược lại sẽ trả về giá trị thứ hai value_if_false. =IF(A1 >=5, “Đậu”,”Rớt”): => Kết quả Nếu giá trị tại A1 >= 5 thì kết quả của hàm là Đậu. Ngược lại nếu giá trị ở ô A1 < 5 thì kết quả là Rớt. 3.2.4. Các hàm xử lý chuỗi Cú pháp Ý nghĩa và ví dụ LOWER(text) Chuyển chuỗi text thành chữ thường. =LOWER(“Dai hoc HOA LU”) ®dai hoc hoa lu UPPER(text) Chuyển chuỗi text thành chữ in hoa. =UPPER(“Dai hoc HOA Lu”)®DAI HOC HOA LU PROPER(text) Đổi các ký tự đầu của mỗi từ trong chuỗi text thành chữ in hoa, còn lại đều là chữ thường. =PROPER(“Dai hoc HOA lu”) → Dai Hoc Hoa Lu TRIM(text) Cắt bỏ các ký tự trống vô ích trong chuỗi text. =TRIM(“ Hoa Lu ”)® Hoa Lu LEN(text) Trả về độ dài của chuỗi text (số ký tự trong chuỗi text). =LEN(“Dai hoc Hoa Lu”)® 14 LEFT(text, num_chars) Trả về num_char ký tự bên trái chuỗi text. =LEFT(“Dai hoc HOA lu”, 7) ® Dai hoc RIGHT(text, num_chars) Trả về num_char ký tự bên phải chuỗi text. =RIGHT(“Dai hoc HOA Lu”, 6)® HOA Lu MID(text, start_num, num_chars) Trả về chuỗi ký tự có độ dài num_chars bắt đầu từ vị trí start_num của chuỗi text. =MID(“Dai hoc HOA Lu”, 5, 3)® hoc VALUE(text) Chuyển chuỗi có dạng số thành giá trị số. VALUE("123") + 2 ®125 FIND(find_text, within_text[, start_num]) Trả về vị trí xuất hiện (nếu có) của find_text trong within_text (bắt đầu tìm từ vị trí start_num). Chú ý: - Nếu không có start_num thì vị trí bắt đầu tìm từ đầu chuỗi. - Hàm FIND phân biệt chữ in hoa và chữ thường. - Nếu không tìm thấy find_text thì sẽ trả về lỗi #VALUE! =FIND(“Excel”, “Microsoft Excel”) ® 11 =FIND(“Excel”, “Microsoft Excel”, 6) ®11 =FIND(“excel”, “Microsoft Excel”, 6) ®#VALUE! SEARCH(find_text, within_text[, start_num]) Tương tự như hàm FIND nhưng không phân biệt chữ in hoa hay thường. =SEARCH(“Excel”, “Microsoft Excel”) ® 11 =SEARCH(“excel”, “Microsoft Excel”) ®11 REPLACE(old_text, num_start, num_chars, new_text) Thay thế num_chars ký tự trong old_text bằng new_text bắt đầu từ vị trí num_start. =REPLACE(“Ngon ngu lap trinh”, 10, 3, “chuong”) ® Ngon ngu chuong trinh CONCATENATE(text1,text2,) Nối các chuỗi text1, text2 thành một chuỗi 3.2.5. Các hàm ngày và giờ (Date và Time) Giả sử ô A1 chứa ngày 28/09/2004 (Thứ ba). Cú pháp Ý nghĩa và ví dụ TODAY( ) Trả về ngày hiện hành của hệ thống. =TODAY( ) ®Tuỳ vào ngày hiện hành của hệ thống. NOW( ) Trả về ngày và giờ hiện hành của hệ thống. =NOW( )® Tuỳ vào ngày và giờ hiện hành của hệ thống. DAY(date) Trả về giá trị ngày trong tháng của biểu thức ngày date. =DAY(A1)® 28 MONTH(date) Trả về giá trị tháng trong năm của biểu thức ngày date. =MONTH(A1)® 9 YEAR(date) Trả về giá trị năm của biểu thức ngày date. =YEAR(A1) ® 2004 WEEKDAY(date) Trả về số thứ tự ngày trong tuần của biểu thức date. Giá trị 1: Sunday, 2:Monday, ..., 7: Saturday. =WEEKDAY(A1)® 3 DATEVALUE(date_text) Đổi chuỗi ngày date_text (theo qui ước nhập ngày) thành trị số ngày. Ghi chú: ta có thể định dạng kết quả trên thành dạng Date bằng cách sử dụng menu Format/Cells. = DATEVALUE("22/8/55")® 20323® 22/8/55 DATE(year, month, day) Trả về giá trị dạng Date theo quy định của hệ thống. =DATE(2004,09,28)®28/09/2004 =DATE(04,9,28)®28/09/2004 TIME(hour, minute, second) Trả về giá trị dạng Time. =TIME(8,25,28) ®8:25:28 AM =TIME(17,2,46) ® 5:2:46 PM 3.2.6. Các hàm tìm kiếm a. Hàm VLOOKUP - Chức năng: Tính toán dựa vào một vùng bảng đối chiếu - Công thức: VLOOKUP(lookup_value, table_array, col_index_num, range_lookup) Tìm giá trị lookup_value trong cột trái nhất của bảng table_array theo chuẩn dò tìm range_lookup, trả về trị tương ứng trong cột thứ col_index_num (nếu tìm thấy). range_lookup = 1 (mặc nhiên): Tìm tương đối, danh sách các giá trị dò tìm của bảng table_array phải sắp xếp theo thứ tự tăng dần Nếu tìm không thấy sẽ trả về vị trí của giá trị lớn nhất nhưng nhỏ hơn lookup_value. range_lookup = 0: Tìm chính xác, danh sách các giá trị dò tìm của bảng table_array không cần sắp xếp thứ tự. Nếu tìm không thấy sẽ trả về lỗi #N/A. b. Hàm HLOOKUP - Công thức: HLOOKUP(lookup_value, table_array, row_index_num, range_lookup) Tương tự như hàm VLOOKUP nhưng tìm giá trị lookup_value trong dòng trên cùng của bảng table_array theo chuẩn dò tìm range_lookup, trả về trị tương ứng trong dòng thứ row_index_num (nếu tìm thấy) Ví dụ: Cho bảng tính với số liệu như sau: A B C D E F 1 A01 5 12 16 10 2 C02 6 15 20 24 3 B75 8 25 22 18 4 5 A02 10 A01 B75 D25 6 B555 12 CẦN THƠ GẠO 7 D25 15 TRẮNG NƯỚC TRONG =VLOOKUP("B75", A1:B3, 2, 0)® 8 = HLOOKUP(16, D1:F3, 3, 0) ® 22 =VLOOKUP("B8",A1:B3,2,0)® #N/A =HLOOKUP(15, D1:F3, 3, 0)®#N/A =VLOOKUP("B85", A1:B3, 2, 1)®5 = HLOOKUP(15, D1:F3, 3, 1)®25 =VLOOKUP("B85", A1:B3, 2)® 5 = HLOOKUP(15, D1:F3, 3)®25 =VLOOKUP(A6, A5:B7, 2, 0) ®12 =HLOOKUP(F5, D5:F7, 2, 0)®GẠO =VLOOKUP("B555", A5:B7, 2, 0)®12 =HLOOKUP(“B75”,D5:F7,3,1)®NƯỚC =VLOOKUP("B85", A5:B7, 2, 1)®12 =HLOOKUP(“E95”,D5:F7,2, 0)®#N/A =VLOOKUP("E05", A5:B7, 2)®15 =HLOOKUP(“E95”,D5:F7,3)® TRONG 3.2.7. Một số ví dụ về cách sử dụng hàm a. Hàm IF Hàm IF(logical_test, value_if_true, value_if_false) Ví dụ 1: =IF(B1 >= 5, “Đậu”, “Rớt”) Excel sẽ kiểm tra biểu thức B1 >= 5, nếu biểu thức đúng (giá trị tại ô B1 là >= 5) thì sẽ in ra “Đậu” và kết thúc hàm, ngược lại sẽ in ra “Rớt” và kết thúc hàm. Ví dụ 2: =IF(B1 > 0, “Số dương”, IF(B1 = 0, “Số không”, “Số âm”)) - Excel sẽ kiểm tra biểu thức B1 > 0, nếu biểu thức đúng thì sẽ in ra “Số dương” và kết thúc hàm, ngược lại sẽ xét tiếp biểu thức B1 = 0. - Nếu biểu thức B1 = 0 là đúng thì sẽ in ra “Số không” và kết thúc hàm, ngược lại sẽ in ra “Số âm” và kết thúc hàm. Ví dụ 3: giả sử yêu cầu xếp loại học tập dựa vào Diem TB trong bảng điểm cho trước và cách xếp loại như sau: Nếu Diem TB>= 9 ®XS Nếu 8 <= Diem TB < 9 ® Giỏi Nếu 7 <= Diem TB < 8 ® Khá Nếu 5 <= Diem TB < 7® TB Nếu 3.5 <= Diem TB < 5 ® Yếu Nếu Diem TB < 3.5 ® Kém A B C D 1 STT Ten Diem TB Xep loai 2 Cần 6.7 3 Kiệm 9.2 4 Liêm 5.8 5 Chính 2.4 6 Chí 7.7 7 Công Công thức tại ô D2: =IF(C2 >= 9, “XS”, IF(C2 >= 8, “Giỏi”, IF(C2 >= 7, “Khá”, IF(C2 >= 5, “TB”, IF(C2 >= 3.5, “Yếu”, “Kém”))))) - Sao chép công thức tại ô D2 đến vùng D3:D7 Tổng quát: nếu có n trưòng hợp thì ta phải sử dụng n-1 hàm IF lồng nhau. b. Hàm VLOOKUP(lookup_value, table_array, row_index_num, range_lookup) Ví dụ 1: Cho bảng lương như sau: A B C D E F G H 1 TT Họ và Tên Chức vụ Hệ số Ngày công Lương Phụ câp CV Tăng giờ Tổng cộng 2 1 Trần Hà NV 2,34 28 3 2 Đinh Hạnh NV 3,0 29 4 3 Triệu Tú TP 3,34 25 5 4 Hà Hà GD 4,0 28 6 5 Huỳnh Gia PGD 3,5 26 7 PHỤ CẤP CHỨC VỤ 8 Chức vụ Tiền 9 GD 5000000 10 PGD 400000 11 TP 2000000 12 VN 0 Yêu cầu: Tính phụ cấp chức vụ (Phụ cấp CV) dựa vào bảng PHỤ CẤP CHỨC VỤ đã cho. Giải: Tại ô G2 ta gõ như sau: = VLOOKUP(C2,$B$9:$C$12,2,0) Sử dụng copy công thức để tìm kết quả cho các ô còn lại Ví dụ 2: Cho dữ liệu như bảng dưới đây: A B C D E F G 1 BẢNG HỌC BỔNG BẢNG TRỢ CẤP 2 Xếp loại Học bổng Mã TC Tỉ lệ % 3 01 100000 A 50% 4 02 70000 B 100% 5 03 50000 C 0% 6 04 0 7 8 DANH SÁCH NHẬN HỌC BỔNG 9 10 TT Họ tên Xếp loại Học bổng Mã TC Trợ cấp Tổng cộng 11 Trường 02 70,000 A 35,000 12 Kỳ 01 B 13 Kháng 02 C 14 Chiến 04 B 15 Nhất 01 C 16 Định 03 B 17 Thắng 04 A 18 Lợi 02 A Yêu cầu: 1) Tính cột Học bổng dựa vào cột Xếp loại và BẢNG HỌC BỔNG. 2) Tính cột Trợ cấp = Học bổng * Tỉ lệ Trong đó Tỉ lệ được tính nhờ vào cột Mã TC và BẢNG TRỢ CẤP. 3) Tính cột Tổng cộng = Học bổng + Trợ cấp Giải: 1) Tính cột Học bổng + Trước hết ta viết công thức cho ô D11: Lấy giá trị trong ô C11 (lookup_value) để dò trong vùng $C$3:$D$6 (table_array), trong bảng này ta muốn lấy cột Học bổng tức là cột thứ 2 (col_index_num), do trong BẢNG HỌC BỔNG cột Xếp loại đã sắp xếp theo thứ tự tăng dần nên ta có thể dò tìm tương đối (range_lookup là 1 hoặc có thể bỏ qua). Vì vùng C3:D6 sử dụng chung để dò tìm nên phải lấy địa chỉ tuyệt đối. Ta được công thức cho ô D11 như sau: =VLOOKUP(C11,$C$3:$D$6,2,1) hoặc =VLOOKUP(C11,$C$3:$D$6,2) + Sao chép công thức tại ô D11 đến vùng D12:D18. 2) Tính cột Trợ cấp + Trước hết ta viết công thức cho ô F11: Để tính Tỉ lệ ta lấy giá trị trong ô E11 (lookup_value) để dò trong vùng $F$3:$G$5 (table_array), trong bảng này ta muốn lấy cột Tỉ lệ tức là cột thứ 2 (col_index_num), do trong BẢNG TRỢ CẤP cột Mã TC chưa được sắp xếp nên ta phải dò tìm tuyệt đối (range_lookup là 0). Ta được công thức cho ô F11 như sau: =D11 * VLOOKUP(E11,$F$3:$G$5,2,0) + Sao chép công thức tại ô F11 đến vùng F12:F18. 3) Tính cột Tổng cộng + Trước hết ta viết công thức cho ô G11: =D11 + F11 + Sao chép công thức tại ô G11 đến vùng G12:G18. 3. Hàm HLOOKUP(lookup_value, table_array, row_index_num, range_lookup) Ví dụ 1: Cho bảng lương như sau: A B C D E F G H I 1 TT Họ và Tên Chức vụ Hệ số Ngày công Lương Phụ câp CV Tăng giờ Tổng cộng 2 1 Trần Hà KT 2,34 28 3 2 Đinh Hạnh NV 3,0 29 4 3 Triệu Tú TP 3,34 25 5 4 Hà Hà GD 4,0 28 6 5 Huỳnh Gia PGD 3,5 26 7 PHỤ CẤP CHỨC VỤ 8 Chức vụ GD PGD TP NV 9 Tiền 500000 400000 200000 0 10 Yêu cầu: Tính phụ cấp chức vụ (Phụ cấp CV) dựa vào bảng PHỤ CẤP CHỨC VỤ đã cho. Giải: Tại ô G2 ta gõ như sau: = HLOOKUP(C2,$B$8:$E$9,2,0) Sử dụng copy công thức để tìm kết quả cho các ô còn lại Ví dụ 2: Xét lại ví dụ 2 của hàm Vlookup ở trên nhưng BẢNG HỌC BỔNG và BẢNG TRỢ CẤP được cho như sau: A B C D E F G 1 BẢNG HỌC BỔNG 2 Xếp loại 01 02 03 04 3 Học bổng 100000 70000 50000 0 4 5 BẢNG TRỢ CẤP 6 Mã TC A B C 7 Tỉ lệ % 50% 100% 0% 8 DANH SÁCH NHẬN HỌC BỔNG 9 10 TT Họ tên Xếp loại Học bổng Mã TC Trợ cấp Tổng cộng 11 Trường 02 70,000 A 35,000 12 Kỳ 01 B 13 Kháng 02 C 14 Chiến 04 B 15 Nhất 01 C 16 Định 03 B 17 Thắng 04 A 18 Lợi 02 A Giải: 1) Tính cột Học bổng Công thức cho ô D11 như sau: =HLOOKUP(C11,$C$2:$F$3,2,1) hoặc =HLOOKUP(C11,$C$2:$F$3,2) 2) Tính cột Trợ cấp Công thức cho ô F11 như sau: =D11 * HLOOKUP(E11,$C$6:$E$7,2,0) 3) Tính cột Tổng cộng Công thức cho ô G11 như sau: =D11 + F11 THỰC HÀNH Bài 1: Cho bảng dữ liệu bảng tổng hợp điểm và bảng phụ chứa điểm và học bổng như sau: Điểm Học Bổng 0 0 7 180000 8 240000 9 360000 Bảng Tổng Hợp Điểm STT Họ và tên Môn 1 Môn 2 Môn 3 Tổng Trung bình xếp hạng học bổng học bổng Vlookup 1 Trần Thanh Tùng 6.7 7.8 8.6 2 Phạm Thu Hương 6.3 7.5 6.8 3 Phạm Ngọc Thạch 8.2 8.4 8 4 Phạm Hồng Thái 7.3 7.5 7.2 5 Nguyễn Thị Mai 5.6 6.5 7.1 6 Nguyên Thị Lan 7.6 8.5 7.8 7 Lê Văn Hân 7.1 6.5 6.3 8 Lê Thu Hong 6.8 6.3 6.2 9 Đinh Văn Thành 8.6 8.5 8.2 10 Đinh Tiên Hoàng 8.5 8.3 8.2 Yêu cầu: 1. Tính tổng 3 môn 2. Tính trung bình 3 môn 3. Xếp hạng cho thí sinh theo điểm từ cao xuống thấp bằng hàm RANK 4. Tính học bổng biết: Nếu Trung bình>=9 thì học bổng = 360000, trung bình >=8 và =7 và <8 thì 180000 còn lại học bổng = 0 5. Dùng VLOOKUP để tính học bổng theo bảng phụ chứa điểm và học bổng 6. Dùng hàm ROUND để làm tròn cột bình quân, chỉ lấy 1 số sau dấu phẩy Bài 2: Cho 2 bảng dữ liệu sau: THỐNG KÊ BÁN HÀNG THÁNG 4 NĂM 2010 STT Ngày Mã hàng Tên hàng Số lượng Giá Tiền 1 05-10-04 TV 15 2 05-10-04 XM 10 3 05-10-04 TL 20 4 05-10-04 XM 7 5 06-10-04 TL 15 6 06-10-04 TV 9 7 07-10-04 TL 24 8 07-10-04 TV 12 BẢNG HÀNG HÓA VÀ GIÁ Mã hàng Tên hàng giá tổng tiền TL Tủ lạnh 40000000 TV Ti vi 60000000 XM Xe máy 30000000 Yêu cầu: 1. Điền cột tên hàng của bảng đầu dựa vào cột Mã hàng trong bảng thứ 2 2. Điền cột giá của bảng đầu dựa vào cột giá của bảng 2 3. Tính cột tiền của bảng đầu theo công thức tiền = số lượng * đơn giá, nếu mua với số lượng lớn hơn 10 thì số tiền phải trả giảm đi 10% 4. Tính cột tổng tiền theo từng mặt hàng của bảng dưới (dùng sumif) Bài 3: Cho bảng như sau: Mã số Họ Tên Ngày sinh Phái Điểm thêm Điểm KK Điểm thi Tổng Điểm Kết Quả Khối Giỏi TDTT VHNT Nghề Cộng Toán Văn Lý Hóa Anh Sử A B C 100TB Lâm Đức Trí 02/03/91 Nam 6 7 8 5 5 5 220KH Nguyễn Thị Nghĩa 01/07/93 Nữ 10 8 9 10 9 9 301GI Trần Hải Thanh 04/12/93 Nam 9 8 10 9 7 4 201GI Nguyễn Thị Châu 03/03/92 Nữ 7 6 8 5 4 6 003KH Phan Thành Long 06/09/93 Nam 9 9 8 8 6 6 000TB Võ Trường Hải 06/05/91 Nam 6 4 7 5 8 8 000XX Nguyễn Ngọc Bích 12/15/91 Nữ 8 9 6 9 10 8 011XX Thái Minh Trí 03/08/92 Nam 3 4 8 5 9 10 123KH Lê Thu Trang 10/22/91 Nữ 5 4 7 9 8 10 030TB Trần Chí Hải 11/11/91 Nam 10 7 8 8 10 7 1. Tính phần ĐIỂM THÊM GIỎI : Dựa vào ký tự thứ 1 của mã số : nếu là 1 là 2 - nếu là 2 là 1.5 - nếu là 3 là 1 TDTT :Dựa vào ký tự thứ 2 của mã số : nếu là 1 là 2 - nếu là 2 là 1.5 - nếu là 3 là 1 VHNT : Dựa vào ký tự thứ 3 của mã số : nếu là 1 là 2 - nếu là 2 là 1.5 - nếu là 3 là 1 NGHỀ : Dựa vào ký tự thứ 4 và 5 của mã số : nếu là GI là 2 - nếu là KH là 1.5 - nếu là TB là 1 CỘNG : là tổng điểm thêm cho HS Giỏi có giải TDTT, VHNT và nghề nhưng không quá 5 2. Tính Điểm KK : Nếu là học sinh nữ và có tuổi nhỏ hơn 15 , Điểm KK là 1 các trường hợp còn lại là 0 3. Tính Tổng Điểm = Điểm thêm(cộng) + Điểm KK + Điểm của các môn thi 4. Căn cứ vào Tổng Điểm để xếp loại Kết quả GIỎI : Nếu Tổng Điểm từ 55 trở lên và không có môn thi dưới 7 KHÁ : Nếu 45<=Tổng Điểm <55 và không có môn thi dưới 5 TBÌNH : Nếu 30<=Tổng Điểm <45 và không có môn thi dưới 3 Còn lại là Hỏng 5. Đánh dấu X chọn HS các khối A, B, C theo yêu cầu Khối A : Nếu Kết quả là Giỏi và các môn thi Toan, Lý, Hóa trên 8, Anh trên 5 Khối B : Nếu Kết quả là Giỏi hoặc Khá và có ít nhất 1 trong 2 môn Văn, Sử trên 7 các môn còn lại trên 5 (Chú ý : Trường hợp xếp vào khối A thì không xếp vào khối B) Khối C : Các học sinh tốt nghiệp còn lại 6. Trang trí bảng tính và định dạng các ô chứa trị dạng số theo đúng mẫu trên. 7. Lập bảng KHỐI A B C TỔNG CỘNG TỔNG SỐ HỌC SINH TỶ LỆ (%) BÀI 4: THAO TÁC TRÊN CƠ SỞ DỮ LIỆU 4.1. Khái niệm về cơ sở dữ liệu 4.1.1. Khái niệm cơ bản về cơ sở dữ liệu Khi quản lý thông tin về một đối tượng nào đó, như quản lý nhân viên chẳng hạn, ta phải quản lý nhiều thuộc tính liên quan đến nhân viên đó như họ tên, mã nhân viên, phái, năm sinh, nơi sinh, địa chỉ, mã ngạch, bậc, hệ số, lương, phụ cấp, chức vụ,... Đó là các thuộc tính phản ánh nội dung của một đối tượng cần quản lý. Các thuộc tính đó thường được biểu diễn dưới dạng các kiểu dữ liệu khác nhau (là chuỗi, số, ngày tháng, ) và được hợp nhất thành một đơn vị thông tin duy nhất gọi là mẩu tin (record). Các mẩu tin cùng “dạng” (cùng cấu trúc) hợp lại thành một cơ sở dữ liệu. Trong Excel, cơ sở dữ liệu có dạng như một danh sách, ví dụ như danh sách nhân viên, danh sách hàng hóa,... Mỗi danh sách có thể gồm có một hay nhiều cột, mỗi cột như vậy được gọi là một trường (field) của cơ sở dữ liệu, tên của cột sẽ được gọi là tên trường. Hàng đầu tiên trong danh sách (cơ sở dữ liệu) chứa các tên trường được gọi là hàng tiêu đề (Header row), các hàng tiếp theo mỗi hàng là một mẫu tin (record) cho biết thông tin về đối tượng mà ta quản lý. Ví dụ: Xét cơ sở dữ liệu BẢNG LƯƠNG CHI TIẾT của các nhân viên trong một cơ quan như sau: A B C D E F G H 1 BẢNG LƯƠNG CHI TIẾT Tháng 07/2001 2 3 STT HOTEN MANG BAC HESO NG_BD LUONG PHUCAP 4 1 Trần Thanh Bình 01.003 4 2.58 25/01/97 541,800 108,360 5 2 Phan Thanh Bình 01.003 3 2.34 30/01/98 491,400 98,280 6 3 Nguyễn Xuân Huy 01.009 1 1.00 01/01/99 210,000 105,000 7 4 Trần Văn Hùng 01.009 2 1.09 15/01/99 228,900 114,450 8 5 Nguyễn Anh Dũng 01.003 2 1.86 01/10/97 390,600 78,120 9 6 Châu Thanh Khiết 01.009 1 1.00 01/05/98 210,000 105,000 10 7 Lê Minh Lợi 01.009 3 1.18 01/08.98 247,800 123,900 11 Tổng cộng: 2,320,500 733,110 + Mỗi cột gọi là một trường (field): trường HO TEN, trường MANG, trường BAC, trường HE SO, + Hàng thứ ba được gọi là hàng tiêu đề (Header row). + Từ hàng thứ tư đến hàng thứ mười, mỗi hàng là một mẫu tin (record). Một số công việc thường gặp khi làm việc trên cơ sở dữ liệu (bảng tính) như: Sắp xếp (Sort) các mẫu tin trong cơ sở dữ liệu theo thứ tự tăng/ giảm của một trường (gọi là trường khoá), trích lọc (Filter) các mẫu tin thoả mãn điều kiện, thống kê, tổng hợp các mẫu tin theo nhóm (Subtotals), ... 4.1.2. Hàng tiêu đề (Header row) Là hàng đầu tiên trong danh sách (cơ sở dữ liệu) chứa các tên trường. Tuy nhiên một số cơ sở dữ liệu có phần tiêu đề nhiều hơn một hàng, khi đó các thao tác thực hiện trên cơ sở dữ liệu sẽ bị lỗi hoặc không thực hiện được, ta phải thêm vào một hàng tiêu đề phụ cho cơ sở dữ liệu, và sử dụng hàng tiêu đề phụ cho các thao tác trên cơ sở dữ liệu. Tiêu đề nhiều hơn 1 hàng Thêm tiêu đề phụ cho CSDL 4.1.3. Vùng tiêu chuẩn (Criteria range) Là vùng chứa điều kiện theo chỉ định (trích lọc, thống kê, ), vùng này có tối thiểu 2 hàng. Hàng đầu chứa các tên trường đặt điều kiện (được sao chép từ tên trường trong bảng CDSL), các hàng khác dùng để mô tả điều kiện. Giả sử cần tạo vùng tiêu chuẩn với điều kiện các mẫu tin phải thoả mãn: a) MANG = ”01.009” và BAC = 1. b) MANG = ”01.009” hoặc MANG = ”01.003” và BAC = 4. Cách tạo vùng tiêu chuẩn: - Chọn các ô trống trong bảng tính để làm vùng tiêu chuẩn - Sao chép tên trường dùng làm điều kiện đến hàng đầu của vùng tiêu chuẩn. - Nhập trực tiếp các điều kiện vào ô dưới tên trường tương ứng. Các điều kiện ghi trên cùng một hàng là các điều kiện thỏa mãn đồng thời (điều kiện AND), còn những điều kiện ghi trên các hàng khác nhau là những điều kiện thỏa mãn không đồng thời (điều kiện OR). Ta có vùng tiêu chuẩn cho điều kiện trên như sau: MANG BAC MANG BAC 01.009 1 01.009 (a) 01.003 4 (b) 4.2. Trích lọc dữ liệu Trích lọc dữ liệu là chức năng cho phép lọc ra các mẫu tin thỏa mãn những tiêu chuẩn nào đó từ cơ sở dữ liệu ban đầu. Có hai phương pháp lọc dữ liệu: lọc tự động (AutoFilter) và lọc nâng cao (Advanced Filter). 4.2.1. Lọc dữ liệu tự động (AutoFilter). Lệnh Data/Filters/AutoFilter dùng để lọc các mẫu tin thỏa mãn những tiêu chuẩn nào đó từ cơ sở dữ liệu ban đầu. Chỉ những mẫu tin nào thỏa mãn tiêu chuẩn thì mới được hiển thị còn những mẫu tin khác sẽ tạm thời bị che không nhìn thấy. Cách thực hiện - Chọn vùng CSDL với tiêu đề là một hàng. - Vào menu Data/Filters/AutoFilter, Excel sẽ tự động thêm các nút thả cạnh tên trường cho phép bạn chọn tiêu chuẩn lọc tương ứng với các trường đó. Chọn điều kiện trong hộp liệt kê thả của từng trường tương ứng. All: cho hiển thị tất cả các mẫu tin. Top 10: cho phép chọn lọc lấy một số mẩu tin có giá trị cao nhất (Top) hay thấp nhất (Bottom). Custom: cho phép đặt các điều kiện so sánh khác ( >, >=, ...) Các giá trị: chỉ hiển thị những mẫu tin đúng bằng giá trị đó. + Mặc nhiên Excel sẽ hiểu tên trường bằng với giá trị được chọn trong hộp liệt kê thả. Các điều kiện trong các trường khác nhau có tính chất đồng thời với nhau (AND). Ví dụ: Lọc những mẫu tin thỏa tiêu chuẩn là MANG = ”01.009” và BAC = 1 + Nếu chọn mục Custom thì sẽ xuất hiện hộp thoại cho phép đặt điều kiện theo tiêu chuẩn khác. Lựa chọn các tiêu chuẩn Giá trị cụ thể của tiêu chuẩn đã chọn Ghi chú: Muốn hiển thị lại tất cả bạn chọn lệnh Data/ Filter/ Show All. Muốn bỏ chế độ lọc dữ liệu tự động (bỏ các nút thả) trở về trạng thái bình thường, bạn chọn lại lệnh Data/ Filter/ AutoFilter. Khi in thì chỉ những bản ghi được lọc mới được in ra giấy. 4.2.2. Lọc dữ liệu nâng cao (Advanced Filter) Lệnh Data/Filter/Advanced Filter dùng để trích lọc ra các mẫu tin theo các điều kiện chỉ định trong vùng tiêu chuẩn do bạn thiết lập trên Sheet. Cách thực hiện - Tạo vùng tiêu chuẩn lọc (sử dụng một trong hai cách nêu trên). - Vào menu Data/ Filter/ Advanced Filter, xuất hiện hộp thoại sau: Chọn địa chỉ vùng CSDL Chọn địa chỉ vùng tiêu chuẩn Chọn địa chỉ của ô đầu tiên trong vùng kết quả (phải chọn mục Copy to another location). Action: + Filter the list, in-place: kết quả hiển thị trực tiếp trên vùng CSDL. + Copy to another location: kết quả được đặt tại một vị trí khác. þ Unique records only: nếu có nhiều mẫu tin giống nhau thì chỉ lấy duy nhất một mẫu tin đại diện, ngược lại thì lấy hết các mẫu tin thỏa điều kiện của vùng tiêu chuẩn (dù giống nhau). 4.3. Các hàm cơ sở dữ liệu và sắp xếp dữ liệu 4.3.1. Các hàm cơ sở dữ liệu Các hàm cơ sở dữ liệu mang tính chất thống kê những mẫu tin trong CSDL có trường thỏa điều kiện của vùng tiêu chuẩn đã được thiết lập trước. Cú pháp chung: =Tên hàm(database, field, criteria) Trong đó: - database: địa chỉ vùng CSDL (nên chọn là địa chỉ tuyệt đối). - field: cột cần tính toán, field có thể là tên trường, địa chỉ của ô tên trường hoặc số thứ tự của trường đó (cột thứ nhất của vùng CSDL đã chọn tính là 1 và tăng dần sang trái). - criteria: địa chỉ vùng tiêu chuẩn. Xét cơ sở dữ liệu BẢNG LƯƠNG CHI TIẾT với vùng tiêu chuẩn được tạo trước. A B C D E F G H 1 BẢNG LƯƠNG CHI TIẾT Tháng 07/ 2001 2 3 STT HO TEN MANG BAC HE SO NG_BD LUONG PHU CAP 4 1 Trần Thanh Bình 01.003 4 2.58 25/01/97 541,800 108,360 5 2 Phan Thanh Bình 01.003 3 2.34 30/01/98 491,400 98,280 6 3 Nguyễn Xuân Huy 01.009 1 1.00 01/01/99 210,000 105,000 7 4 Trần Văn Hùng 01.009 2 1.09 15/01/99 228,900 114,450 8 5 Nguyễn Anh Dũng 01.003 1 1.86 01/10/97 390,600 78,120 9 6 Châu Thanh Khiết 01.009 1 1.00 01/05/98 210,000 105,000 10 7 Lê Minh Lợi 01.009 3 1.18 01/08/98 247,800 123,900 11 Tổng cộng: 2,320,500 733,110 12 13 Vùng tiêu chuẩn 1 MANG Vùng tiêu chuẩn 2 MANG BAC 14 01.009 01.003 4 Danh sách các hàm Tên hàm Ý nghĩa và ví dụ DSUM(database, field, criteria) Tính tổng các giá trị trong cột field của các mẫu tin thỏa điều kiện criteria. Ví dụ: Tính tổng lương của những người có MANG là 01.009 =DSUM($A$3:$H$10, 7, C13:C14) =DSUM($A$3:$H$10, “LUONG”, C13:C14) =DSUM($A$3:$H$10, $G$3, C13:C14) DAVERAGE(database, field, criteria) Tính trung bình cộng các giá trị trong cột field của các mẫu tin thỏa điều kiện criteria. Ví dụ: Tính lương trung bình của những người có MANG là 01.009 =DAVERAGE($A$3:$H$10, 7, C13:C14) =DAVERAGE($A$3:$H$10, $G$3, G13:H14) DMAX(database, field, criteria) Tìm trị lớn nhất trong cột field của các mẫu tin thỏa điều kiện criteria. Ví dụ: Tìm bậc lương lớn nhất của những người có MANG là 01.009 =DMAX($A$3:$H$10, “BAC”, C13:C14) =DMAX($A$3:$H$10, 5, G13:H14) DMIN(database, field, criteria) Tìm trị nhỏ nhất trong cột field của các mẫu tin thỏa điều kiện criteria. =DMIN($A$3:$H$10, $D$3, C13:C14) =DMIN($A$3:$H$10, 5, C13:C14) DCOUNT(database, field, criteria) Đếm các ô kiểu số trong cột field của các mẫu tin thỏa điều kiện criteria. =DCOUNT($A$3:$H$10, 4, C13:C14) =DCOUNT($A$3:$H$10, 4, G13:H14) DCOUNTA(database, field, criteria) Đếm các ô khác rỗng trong cột field của các mẫu tin thỏa điều kiện criteria. =DCOUNTA($A$3:$H$10, 2, C13:C14) =DCOUNTA($A$3:$H$10, 2, G13:H14) 4.3.2. Sắp xếp dữ liệu Tương tự như chức năng Table/ Sort của Word, lệnh Data/Sort cho phép sắp xếp các hàng hoặc các cột trong vùng được chọn theo thứ tự tăng dần (thứ tự ABC đối với chuỗi, hoặc số tăng dần) hay giảm dần (thứ tự ZYX đối với chuỗi, hoặc số giảm dần) tương ứng khoá sắp xếp được chỉ định, vùng sắp xếp phải chọn tất cả các ô có liên hệ với nhau, nếu không sẽ xảy ra tình trạng “râu ông này cắm cằm bà kia”. Cách thực hiện: Giả sử cần sắp xếp cơ sở dữ liệu BẢNG LƯƠNG CHI TIẾT ở trên theo MANG tăng dần, nếu cùng MANG thì sắp theo BAC giảm dần. − Chọn vùng dữ liệu cần sắp xếp. − Vào menu Data/ Sort, xuất hiện hộp thoại sau: Khóa sắp xếp chính Khóa sắp xếp thứ hai Khóa sắp xếp thứ ba Vùng CDSL đã chọn có dòng tiêu đề hay không Sắp xếp theo thứ tự tăng dần Sắp xếp theo thứ tự giảm dần Chọn có/không có dòng tiêu đề Chọn các khoá sắp xếp, kích chọn OK để sắp xếp. Chú ý: - Nếu muốn sắp xếp theo hàng thì chọn Options xuất hiện hộp thoại: Sắp xếp theo hàng Sắp xếp theo cột Ta có thể sắp xếp nhanh theo cột nào đó bằng cách đặt trỏ vào ô bất kỳ của cột đó và chọn các biểu tượng 4.4. Tổng hợp theo từng nhóm (Subtotals) Tổng tiền lương (LUONG) theo từng nhóm ngạch lương (MANG), hay tổng hợp số nhân viên theo bậc (BAC), Lệnh Data/ Subtotals sẽ giúp chúng ta thực hiện được những công việc trên. 4.4.1. Cách thực hiện Giả sử cần tổng hợp và tính tổng tiền lương (LUONG) theo từng nhóm ngạch lương (MANG) trong CSDL BẢNG LƯƠNG CHI TIẾT ở trên. − Dùng lệnh Data/Sort để sắp xếp các mẫu tin theo MANG, mục đích để các mẫu tin có cùng MANG thì nằm liền kề nhau. − Chọn vùng CSDL cần tổng hợp với tiêu đề là một hàng. − Vào menu Data/ Subtotals, xuất hiện hộp thoại sau: Chọn tên trường cần tổng hợp nhóm Chọn hàm sử dụng tính toán hay thống kê Chọn trường để tính toán hay thống kê Thay thế các dòng tổng hợp Tạo ngắt trang giữa các nhóm Thêm dòng tổng hợp sau mỗi nhóm Một số hàm trong hộp Use funtion: Hàm Mô tả Sum Tính tổng các số trong nhóm. Count Đếm số ô không rỗng trong nhóm. Average Tính giá trị trung bình các số trong nhóm. Max Tìm giá trị lớn nhất trong nhóm. Min Tìm giá trị nhỏ nhất trong nhóm. Product Tính tích các số trong nhóm. Count Nums Đếm số ô kiểu số trong nhóm. 4.4.2. Làm việc với màn hình kết quả sau khi tổng hợp nhóm - Click vào các nút để chọn các mức dữ liệu bạn muốn xem. Chỉ hiển thị tổng chính (Grand Total Only). Hiển thị tổng chính và tổng phụ (Grand Total And Subtotal). Hiển thị chi tiết tất cả các mẫu tin cùng các tổng hợp (All Record). - Click vào để hiển thị hoặc để che dấu các mẫu tin trong nhóm con. Chú ý: để loại bỏ tổng hợp nhóm, bạn chọn Data/ Subtotals, sau đó chọn nút lệnh Remove All. THỰC HÀNH Bài 1: KHÁCH SẠN KHÁNH LINH- BẢNG THANH TOÁN TIỀN THUÊ PHÒNG Tháng 3 năm 2011 STT Tên khách hàng Ngày thuê Ngày trả Phòng Thời gian Đơn giá phòng Tiền KM Thành tiền Loại Tầng 1 Vũ Văn Hạnh 01/06/11 05/06/11 A 1 2 Đinh Tuấn Anh 05/07/11 17/05/11 B 2 3 Phạm Thị Tuất 25/08/11 02/09/11 C 3 4 Bùi Đức Trung 08/09/11 16/09/11 A 4 5 Tống Duy Hiến 10/10/11 27/10/11 B 5 Yêu cầu: Nhập và định dạng bảng như trên Tính thời gian thuê phòng của khách: Ngày trả - Ngày thuê + 1 Tính Đơn giá phòng căn cứ vào bảng sau BẢNG GIÁ PHÒNG VÀ HỆ SỐ TẦNG BẢNG GIÁ PHÒNG TẦNG - HỆ SỐ Loại Giá Tầng Hệ số % A 300000 1 1 B 200000 2 0.95 C 100000 3 0.9 4 0.85 5 0.8 Tính tiền khuyến mại (Tiền KM) cho khách biết nếu khách ở trên trên 10 ngày thì được khuyến mại trong cả đợt là 150.000, nếu khách ở trên 5 ngày thì được 50000 tiền khuyến mại còn lại không được khuyến mại. Tính tiền khách hàng phải trả biết Thành tiền = Thời gian * Đơn giá phòng - Tiền KM Sắp xếp dữ liệu theo tiêu chí 1: thời gian thuê tăng dần, Tiêu chí 2: đơn giá tăng dần. Tính tổng thành tiền theo từng loại phòng Lọc ra danh sách những khách hàng ở phòng loại A ra hàng thứ 25 trong bảng. Bài 2: BẢNG LƯƠNG NHÂN VIÊN THÁNG 10/1996 STT TÊN CVỤ LCB NC LƯƠNG PHỤ CẤP THƯỞNG TẠM ỨNG CÒN LẠI TỔ BÁN HÀNG BH 1 HẠNH TT 160 24 100000 2 H.ANH TP 165 22 100000 3 THỦY NV 160 24 4 HỒNG NV 170 23 100000 5 A.ĐÀO NV 150 24 TỔ TIẾP THỊ TT 1 ĐỨC TT 190 26 100000 2 HIỆP TP 190 27 100000 3 LÂM NV 200 25 4 CHI NV 210 26 100000 5 THẢO NV 190 24 100000 6 HÀO NV 185 24 100000 7 KIỆT NV 190 25 8 TIẾN NV 200 26 9 HOÀNG NV 210 28 10 TRÍ NV 220 28 100000 TỔ THƯ KÝ TK 1 VIÊN NV 175 24 2 DUYÊN NV 170 24 3 TRANG NV 170 23 TỔNG CỘNG QUỸ LƯƠNG 10.000.000 BẢNG HỆ SỐ TT/TP NV TK 30 27 BH 25 23 TT 40 37 Yêu cầu: 1. LƯƠNG = LCB * NC 2. PHỤ CẤP = 5000*HESO. Hệ số được xác định từ bảng hệ số. 3. Nếu số ngày công (NC) lớn hơn 24, thì mỗi ngày được thưởng 15000. 4. Trình bày bài theo mẫu trên. 5. Tính tổng LƯƠNG, THƯỞNG theo từng tổ. LƯƠNG THƯỞNG BH TT TK 6. Lưu nội dung tập tin với tên BAITAP3.XLS BÀI 5: BIỂU ĐỒ VÀ HOÀN THIỆN TRANG BẢNG TÍNH 5.1. Tạo biểu đồ trong MS Excel Biểu đồ cho phép biểu diễn sự tương quan của dữ liệu trong bảng tính trên phương diện đồ họa, biến đổi các hàng, cột thông tin thành những hình ảnh có ý nghĩa. Qua biểu đồ chúng ta có thể so sánh số liệu trong bảng tính một cách trực quan, tránh việc phải đọc các số liệu chi chít trên bảng, tiên đoán được sự phát triển của dữ liệu mô tả trong bảng, làm cho dữ liệu trở nên sinh động và thuyết phục hơn. 5.1.1. Các loại biểu đồ Có 3 loại biểu đồ thường gặp là biểu đồ dạng cột (Column), dạng đường thẳng (Line) và dạng hình tròn (Pie). Từ 3 dạng này Excel triển khai thành 14 loại biểu đồ chuẩn (Standard types) và 20 kiểu biểu đồ tuỳ chọn (Customize types) có thể dùng để biểu diễn số liệu trong bảng tính thành nhiều cách nhìn khác nhau tùy theo yêu cầu của người dùng. Các loại biểu đồ chuẩn trong Excel và công dụng cơ bản của mỗi loại Loại biểu đồ Biểu tượng Chức năng Column So sánh các loại dữ liệu với nhau theo chiều dọc. Bar So sánh các loại dữ liệu với nhau theo chiều ngang. Line Cho xem sự thay đổi dữ liệu trong một giai đoạn. Pie So sánh tỷ lệ của các thành phần trong một tổng thể. XY (Scatter) Mô tả quan hệ giữa hai loại dữ liệu liên quan. Area Nhấn mạnh tầm quan trọng tương đối của các giá trị qua một giai đoạn. Doughnut So sánh các phần với tổng thể trong một hoặc nhiều phạm trù dữ liệu (Biểu đồ Pie có một lỗ ở giữa). Rada Chỉ ra các thay đổi trong dữ liệu hoặc tần số dữ liệu tương đối với tâm điểm. Surface Tạo vết các thay đổi trong hai biến số khi biến số thứ ba (như thời gian) thay đổi, là một đồ họa 3 chiều. Buble Hiện sáng các chùm giá trị, tương tự như đồ họa Scatter. Stock Kết hợp đồ họa Line và đồ họa Column. Được thiết kế đặc biệt để tạo vết giá cổ phiếu. Cylinder Sử dụng một hình trụ để trình bày các dữ liệu đồ họa Bar hay đồ họa Column. Cone Nhấn mạnh các đỉnh của dữ liệu, là đồ họa Bar hay đồ họa Column. Pyramid Nhấn mạnh các đỉnh của dữ liệu trong các đồ họa Bar hay Column, tương tự đồ họa Cone. 5.1.2. Tạo các biểu đồ, đồ thị Các bước thực hiện : Chọn vùng dữ liệu. Vào Insert\Chart hoặc nhắp chọn biểu tượng chart trên thanh công cụ khi đó xuất hiện hộp thoại Chart Wizard ta làm theo các bước. Loại biểu đồ Các hình dạng cụ thể của từng loại Bước 1: Chọn loại biểu đồ hình dạng tương ứng và chọn Next. Bước 2: Xác định vùng dữ liệu (là vùng đã được chọn, nếu có sai lệch ta sửa tại (vùng 1). Sau đó nhấn Next để chuyển sang bước 3 Vùng 1 Muốn có chú thích thì ấn Series xuất hiện hộp thoại Tên chú thích Bước 3: Thêm tiêu đề ngang, tiêu đề trục x, tiêu đề trục y xong nhấn Next để chuyển sang bước 4 Tiêu đề của đồ thị Tiêu đề trục ngang X Tiêu đề trục đứng Y Bước 4: Xác định vị trí đặt biểu đồ và bấm Finish để kết thúc. Vị trí đặt biểu đồ VD Chọn Sheet 2 là nơi đặt biểu đồ 5.1.3. Biên tập sửa đổi biểu đồ, đồ thị a. Biên tập sửa đổi a.1. Thêm tiêu đề, chú thích ý nghĩa các trục đồ thị Nhắp chọn đồ thị. Vào Chart\Chart Option hoặc nhấn chuột phải chọn Chart Option Trong hộp thoại chọn Tittle sau đó chỉnh sửa tiêu đề. Nhấn OK để kết thúc. a.2. Xoá bỏ tiêu đề Nhắp chọn đồ thị. Nhắp đúp chuột vào vùng tiêu đề hay chú thích, ấn Delete a.3. Hiển thị hoặc không hiển thị các đường kẻ ô lưới Chọn đồ thị. Vào Chart\Chart Option hoặc nhấn chuột phải vào biểu đồ cần, chọn Chart Option Trong hộp thoại chọn thẻ Gridline sau đó chỉnh sửa tiêu đề. Các đường biểu diễn giá trị chính Các đường biểu diễn độ chia nhỏ hơn Chú ý: Trục Y cũng có thuộc tính như trên. a.4. Hiển thị dữ liệu kèm theo đồ thị Chọn biểu đồ. Vào Chart\Chart Option hoặc nhấn chuột phải chọn Chart Option Trong hộp thoại chọn Data Table đánh dấu chọn ô Show data Table. b Thay đổi hình dạng biểu đồ Nhấn để mở danh sách dạng biểu đồ, chọn dạng biểu đồ muốn áp dụng Chọn dạng biểu đồ khi đó thanh công cụ Chart tự động được hiển thị trên cửa sổ chương trình. c Thay đổi kích thước và xoá biểu đồ c.1. Thay đổi kích thước Chọn biểu đồ. Đặt con trỏ vào một trong các nút hình vuông khi con trỏ chuyển thành hình mũi tên hai chiều, thì kéo di để thay đổi kích thước. Chú ý: Muốn thay đổi từng thành phần: Tiêu đề, cột biểu diễn.... ta làm tương tự như trên. c.2. Xoá biểu đồ. Chọn biểu đồ cần xoá. Ấn phím Delete hoặc vào Edit/Clear/All d. Di chuyển, sao chép biểu đồ d.1. Sao chép biểu đồ: Thực hiện như đối với văn bản. d.2. Di chuyển biểu đồ Chọn biểu đồ cần di chuyển. Ấn trỏ vào bên trong biểu đồ khi xuất hiện mũi tên 4 chiều thì ấn và di đến vị trí mới. e. Thay đổi mầu nền của biểu đồ, đồ thị Nhấn chuột vào thành phần cần thay đổi (Tiêu đề, vùng chú thích, vùng dữ liệu, các đối tượng cột đứng...) Nhấn vào đây để mở và chọn mẫu tương ứng THỰC HÀNH Cho dữ liệu về Báo cáo thu mua lương thực năm 2011 như sau: Đơn vị: Triệu tấn Quí 1 Quí 2 Quí 3 Kho 1 80 180 150 Kho 2 70 50 40 Kho 3 70 60 60 Vẽ biểu đồ hình cột so sánh giữa các kho trong mỗi quý. Biểu đồ có đầy đủ tên, chú thích. Đơn vị: Triệu tấn 5.2. Hoàn thiện trang bảng tính và in ấn 5.2.1. Trình bày trang in a. Thay đổi lề trang in lề trái lề Phải Căn bảng luôn ở giữa trang (Chiều ngang) Bảng luôn ở giữa trang (Chiều dọc) Vùng tiêu đề cuối trang Vùng tiêu đề đầu trang Vào File\ Page Setup xuất hiện hộp thoại chọn Margins - Chọn các thông số phù hợp và nhấn OK để kết thúc b. Chọn hướng giấy Giấy dọc Giấy ngang Vào File\ Page Setup xuất hiện hộp thoại chọn Page. Chọn hướng in ngang hay dọc và chọn OK. c. Thêm tiêu đề đầu trang và cuối trang Vào File\Page Setup xuất hiện hộp thoại chọn thẻ Header/Footer xuất hiện hộp thoại Nhấn vào đây để thêm thông tin cho cuối trang Danh sách các kiểu tiêu đề Nhấn vào đây để thêm thông tin cho đầu trang Thêm dữ liệu vào đầu trang, cuối trang và chọn OK 5.2.2. Hoàn tất các trang in a. Xem trang bảng tính trước khi in Vào File\Print Preview hoặc nhắp chọn biểu tượng trên thanh công cụ khi đó ta có: Trang tiếp Trang trứơc In bảng tính Thay đổi các thông số trong Page Setup Thay đổi lề nhanh Trở về chế độ soạn thảo b. Thiết lập các chế độ in tại dòng tiêu đề khi sang trang mới Vào File\Page Setup xuất hiện hộp thoại chọn Sheet: Chỉ định dòng (các dòng DL dùng làm dòng đầu tiên cho mọi trang in ra Chỉ định cột (các cột) DL dùng làm cột đầu tiên cho mọi trang in ra 5.2.3. In ấn Cách 1: Bấm vào biểu tượng: để in từng trang trong bảng tính. Cách 2: Vào File\Print hoặc ấn Ctrl+P khi đó xuất hiện hộp thoại: In tất cả In từ trang From...To.. Số bản được in Chọn máy in Chọn các cách in: a. In toàn bộ bảng tính: Chọn vùng 1 Vùng 1 b. In toàn bộ trang bảng tính hiện hành: chọn vùng 1 và vùng 2 Vùng 1 Vùng 2 c. In một vùng ô định trước Chọn vùng (sử dụng hỗ trợ phím Shift và Ctrl) Vào File\Print hoặc ấn Ctrl+P khi đó xuất hiện hộp thoại: Chọn vùng 1 Vùng 1 d. In một phần trang bảng tính Chọn vùng 1 gõ vào trang đầu, trang cuối Trang đầu Trang cuối Vùng 1 e. In riêng biểu đồ, đồ thị Chọn biểu đồ cần in. Vào File\Print hoặc ấn Ctrl+P khi đó xuất hiện hộp thoại: Excel tự động chọn Selected Chart, ta chọn nút OK THỰC HÀNH Bài 1: Lập bảng và định dạng theo mẫu sau: Mã NV Họ Tên Phái Năm sinh Nơi sinh Ngày vào làm việc Ngày Công A250-1 Lâm Đức Trí 1973 Huế 01-02-97 20 B356-2 Nguyễn Thị Nghĩa x 1974 Đà Lạt 20-05-98 25 C452-1 Trần Hải Thanh 1975 Sông Bé 25-08-98 24 B897-3 Nguyễn Ngọc Châu x 1969 Hà Nội 30-07-94 24 B696-1 Phan Thành Long 1972 TP. HCM 26-06-98 25 C897-1 Võ Trường Hải 1964 Huế 10-01-94 26 D456-2 Nguyễn Ngọc Bích x 1977 Đà Lạt 15-09-95 20 A123-3 Thái Minh Trí 1973 Tiền Giang 05-07-96 22 C556-2 Lê Thu Trang x 1975 Nha Trang 06-10-97 24 D658-3 Trần Chí Hải 1970 Đà Nẵng 25-12-97 24 C478-1 Nguyễn Bích Thủy x 1966 TP. HCM 22-06-96 25 B285-2 Lê Ngọc Quỳnh x 1960 Vũng Tàu 10-04-93 24 A396-1 Ngô Thanh Tâm x 1962 Huế 16-06-94 25 B963-2 Trần Như Quỳnh 1970 Đà Lạt 14-10-95 26 D987-3 Nguyễn Bích Thủy x 1972 Minh Hải 19-11-97 28 1. Chèn thêm cột STT vào bên trái cột Mã NV và điền số thứ tự cho cột này có dạng 01,02,03,, Chèn thêm cột Bậc lương, Phòng ban, Thâm niên, Phụ cấp thâm niên, Lương, Tạm ứng, Thực lĩnh lần lượt sau cột Ngày công. 2. Lập công thức cho cột Bậc Luơng dựa vào ký tự đầu bên trái của Mã NV, Nếu là A bậc lương là 330, nếu là B bậc lương là 310, nếu là C bậc lương là 290 và các trướng hợp còn llại là 275 3. Lập công thức cho cột Phòng ban dựa vào ký tự đầu bên phải của Mã NV Nếu là 1 phòng ban là Kỹ thuật, nếu là 2 phòng ban là Kế Toán, các trường hợp còn lại phòng ban là Kế hoạch 4. Thâm niên là số năm làm việc. Thâm niên = năm hiện tại - năm của ngày vào làm. 5. Lập công thức cho cột Phụ cấp thâm niên. Phụ cấp thâm niên = Thâm niên * 20000 6. Lập công thức tính Lương . Lương được tính như sau Nếu ngày công <=ngày công qui định thì lấy ngày công x với bậc lương x 1000 Nếu ngày công > ngày công qui định thì số ngày dôi ra được nhân đôi và cộng vào với ngày công qui định, sau đó đem nhân với bậc lương x 1000 7. Lập công thức tính Tạm ứng. Tạm ứng bằng 1/3 Lương nhưng chỉ lấy phần nguyên 8. Lập công thức cho cột thực lãnh. Thực lãnh = Lương + Phụ cấp thâm niên - Tạm ứng 9. Sắp xếp danh sách tăng dần theo Tên, nếu trùng tên sắp xếp tăng dần theo Họ nếu trùng Họ sắp xếp tăng dần theo phòng ban 10. Trang trí bảng tính và định dạng các ô chứa trị dạng số theo đúng mẫu trên 11. Trích ra danh sách nhân viên thuộc phòng Kỹ Thuật 12. Trích ra những nhân viên có 4 triệu <= Thực lãnh <= 5 triệu Bài 2: Lập bảng và định dạng theo mẫu sau: SBD Họ Tên Phái Năm sinh Nơi sinh Toán Sinh Lý Hóa A250-1 Lâm Đức Trí 1973 Huế 5 3 4 8 B356-2 Nguyễn Thị Nghĩa x 1974 Đà Lạt 6 8 5 10 C452-1 Trần Hải Thanh 1975 Sông Bé 4 4 5 5 B897-3 Nguyễn Ngọc Châu x 1969 Hà Nội 6 3 3 7 B696-1 Phan Thành Long 1972 TP. HCM 5 8 1 9 C897-1 Võ Trường Hải 1980 Huế 8 5 2 5 D456-2 Nguyễn Ngọc Bích x 1977 Đà Lạt 9 10 9 9 A123-3 Thái Minh Trí 1973 Tiền Giang 10 9 7 4 C556-2 Lê Thu Trang x 1975 Nha Trang 8 5 4 6 D658-3 Trần Chí Hải 1970 Đà Nẵng 8 8 6 6 C478-1 Nguyễn Bích Thủy x 1986 TP. HCM 7 5 8 8 B285-2 Lê Ngọc Quỳnh x 1982 Vũng Tàu 6 9 10 8 A396-1 Ngô Thanh Tâm x 1985 Huế 8 5 9 10 B963-2 Trần Như Quỳnh 1984 Đà Lạt 7 9 8 10 D987-3 Nguyễn Bích Thủy x 1982 Minh Hải 8 8 10 7 B465-3 Trần Văn Linh 1986 Nha Trang 6 4 5 7 C324-1 Vũ Ngọc Sơn 1985 Đà Nẵng 7 4 7 7 A782-2 Phan Thái Trung 1986 TP. HCM 6 10 9 8 A659-2 Nguyễn Mạnh Dũng 1985 Hà Nội 8 9 8 6 A333-1 Lân Ngọc Châu x 1984 Kiên Giang 5 8 9 7 1. Chèn thêm cột Stt trước cột SBD và điền vào cột này theo dạng 01,02,03 Chèn thêm cột Tổng điểm, Ưu tiên, Điểm kết quả, Ngành Thi, Điểm chuẩn, Kết quả, Kết quả 1, Kết quả 2 liên tiếp sau cột điểm Hóa. 2. Lập công thức cho cột Tổng điểm biết các môn không có hệ số 3. Lập công thức cho cột Ưu tiên, dựa vào ký tự đầu bên phải của SBD, Nếu là 1, Ưu tiên là 2, nếu là 2, Ưu tiên là 1.5 các trường hợp còn lại Ưu tiên là 1 4. Lập công thức cho cột ĐKQ, biết ĐKQ = Tổng cộng + Ưu tiên 5. Lập công thức cho cột Ngành thi, dựa vào ký tự đầu bên trái của SBD, Nếu là A ngành thi là Toán, nếu là B ngành thi là Lý, nếu là C ngành thi là Hóa, là D ngành thi là Sinh, 6. Lập công thức cho cột Điểm chuẩn, biết ngành toán là 30, lý là 28, hóa là 26, sinh là 24 7. Lập công thức cho cột Kết Quả nếu Tổng cộng >= Điểm chuẩn thì kết quả là "Đạt" ngược lại để trống, 8. Lập công thức cho cột KQ1, nếu điểm Toán >=9 và một trong hai môn Hóa hoặc Lý từ 8 trở lên,thì đánh dấu x 9. Lập công thức cho cột KQ2, nếu điểm Toán >=9 và một trong hai môn Hóa hoặc Sinh từ 8 trở lên thì đánh dấu x 10. Sắp xếp danh sách tăng dần theo Tên , nếu trùng tên thì sắp xếp giảm dần theo Tổng cộng, 11. Trang trí bảng tính và định dạng các ô chứa trị dạng số theo đúng mẫu trên. 12. Tạo một danh sách mới gồm các học sinh có kết quả đạt. 13. Lập bảng thống kê: KẾT QUẢ ĐẠT KHÔNG ĐẠT TỔNG CỘNG TỔNG SỐ HỌC SINH TỶ LỆ (%) 14. Vẽ biểu đồ (PIE) phân tích tỷ lệ học sinh đạt và không đạt (có tiêu đề và trang trí cần thiết). BÀI THỰC HÀNH TỔNG HỢP Bài 1: Cho bảng tính Yêu cầu: Cột STT nhập tự động Sắp xếp bảng tính tăng dần theo Mã hàng, nếu trùng thì giảm dần theo số lượng. Cột mã hàng có chú thích là: 3 Ký tự đầu cho biết mã thương hiệu Ký tự 4,5 của Mã hàng cho biết kích thước Ký tự cuối mã hàng cho biết Tivi thuộc loại Mono (M) hay Hifi (H) Điền dữ liệu cho cột tên hàng dựa vào mã hàng tra trong Bảng Phụ và ký tự cuối để thể hiện loại Mono hay Hifi. Điền số liệu cho cột đơn giá dựa vào Mã thương hiệu và hai ký tự 4,5 để biết loại 14' hay 21' và tra trên bảng phụ để lấy đơn giá. Nếu Tivi được bán trước ngày 10/10/2001 thì giảm 5% đơn giá ngược laị thì không giảm giá. Thành tiền : (Đơn giá - Giảm giá ) * Số lượng . Định dạng cột thành tiền có đơn vị tính USD Tính giá trị cho các ô đánh dấu ? Định dạng bảng tính theo mẫu. Rút trích các mặt hàng là Tvi Sony 14' và Panasonic 14' Bài 2: Cho bảng tính: Mô tả: Ký tự thứ 2 của Mã số là Loại Phòng - Ký tự cuối của Mã số là Khẩu phần ăn Yêu cầu: Thêm 2 cột Tiền phòng và Tiền ăn sâu cột Số ngày ở. Tính toán và định dạng cột Tiền phòng và Tiền ăn là USD, biết rằng: Tiền phòng: Số ngày ở * Giá phòng (Dựa vào Loại phòng tra trong BẢNG ĐƠN GIÁ) Tiền ăn: Số ngày ở * Giá khẩu phần ăn (Dựa vào khẩu phần ăn tra trong BẢNG ĐƠN GIÁ) Tiền phải trả: Tiền phòng + Tiền ăn. Tuy nhiên: - Giảm 5% Tiền phòng cho khách ở từ 10 -> 15 ngày - Giảm 10% Tiền phòng cho khách ở trên 15 ngày, các trường hợp khác thì không giảm. - Quy đổi ra tiền Việt Nam với tỷ giá quy đổi là 15,869 và định dạng theo 1,000 đồng. 3. Cho biết Số tiền phải trả nhiều nhất. 4. Rút trích thông tin khách thuê phòng Loại 1 hoặc Loại 2 5. Lập BẢNG THỐNG KÊ TỔNG TIỀN PHẢI TRẢ theo mẫu trên. Bài 3: Cho bảng tính Yêu cầu: Sắp xếp bảng tính tăng dần theo tên hàng, nếu trùng thì giảm dần theo số lượng . Cột Số TT nhập tự động, cột mã số có chú thích là Ký tự đầu cho biết Mã sản phẩm Ký tự 2,3 cho biết ngày bán hàng Ký tự cuối cho biết loại hàng 3.Dựa vào Mã Sp tra trong bảng phụ để lấy tên hàng. Thể hiện thêm chuỗi Loại hàng ở cuối : VD : G01A --> Gạo Loại A. 4. Dựa vào ký tự 2,3 để lấy ngày và thể hiện Ngày/ tháng / năm: VD 01/11/2005 5. Dựa vào Mã SP và loại hàng tra trong bảng phụ để lấy đơn giá . 6. Những mặt hàng bán trước ngày 15 /11 thỉ giảm 10 % ngược laị để trống 7. Thành tiền = Số lượng * Đơn giá - Giảm giá 8. Rút trích những mặt hàng Gạo loại A bán trước ngày 15. 9. Điền số lượng vào bảng thống kê số lượng bán Bài 4: Cho bảng tính: Sắp xếp bảng tính tăng dần theo tên hàng. Nếu trùng thì giảm dần theo số lượng. Cột Mã hàng tạo chú thích có nội dung: Ký tự đầu cho biết Mã số hàng Ký tự 2,3 cho biết tháng bán hàng Ký tự cuối cho biết hình thức : N : Hàng Nhập , X : Hàng Xuất 3. Tên hàng dựa vào ký tự đầu của mã số được tra trên bảng danh mục 4. Thành tiền: Thành tiền = Số lượng * Đơn giá Nếu là hàng Xuất thì tăng đơn giá lên 3% Nếu số lượng >= 200 thỉ giảm 5 % thành tiền 5.Thuế: Thuế = Tỉ lệ thuế * Thành tiền - Tỷ lệ thuế dựa vào Mã số tra trong bảng Tỉ lệ - Rút trích các mặt hàng Vải Kate, Vải Silk bán trong tháng 3 và thể hiện 4 cột Mã hàng, tên hàng, số lượng, thành tiền 6. Trả trước: Nếu thành tiền > 5000000 thì trả trước 75 % ngược lại trả trước 50 % 7. Còn lại: Còn lại = Thành tiền - trả trước Bài 5: Cho bảng tính Yêu cầu: Sắp xếp bảng tính tăng dần theo ngaỳ bán, nếu trùng giảm dầm theo số lượng. Mã hàng tạo chú thích có nội dung: Ký tự đầu cho biết Mã nhản hiệu S : Samsung, N : Nokia Ký tự cuối cho biết loại hàng : C : Chính hãng , X : Xách tay Nhãn hiệu: Dựa vào mã hàng để điền các thông tin thích hợp theo như mô tả Model: Các ký tự 2,3,4,5 của mã hàng là loại model Dựa vào ký tự cuối để thể hiện loại hàng theo như mộ tả Thành tiền = Số lượng * Đơn giá Nếu hàng được ban1 từ ngaỳ 5/2/2003 đến ngaỳ 8/3/2003 thì thành tiền giảm 10% 7. Bảo hành: Chính hãng Samsung --> Bảo hành 24 tháng Chính hãng Noikia --> Bảo hành 12 tháng Ngược lại bảo hành 6 tháng 8. Rút trích các mặt hàng chính hãng của Nokia 9. Điền dữ liệu cho bảng thống kê các mặt hàng và loại hàng. Bài 6: Cho bảng tính: Mô tả 3 ký tự đầu của phiếu hàng là loại cửa 2 ký tự 4,5 của phiếu hàng là mã công ty Các ký tự cuối là số lượng Yêu cầu Diễn giải: Dựa vào loại cửa tra trong bàng đơn giá Ngày giao = ngày đặt hàng + Số ngày giao.Biết rằng số ngày giao dựa vào mã công ty tra trong bảng danh mục . Hiển thị theo dd/mm Đơn giá:dựa vào loại cửa tra trong bảng đơn giá Thành tiền = số lượng * đơn giá.Biết rằng số lượng dựa vào mô tả và nếu mặt hàng là cửa 1 cánh nhựa hoặc cửa 1 cánh trượ th2 thành tiền giảm 2% Lập bảng thống kê theo mẫu sau: Thống kê/công ty thành tiền Cửa 1 cánh Cửa xoay Bài 7: Cho bảng tính Mô tả 2 Ký tự đầu của Mã đặt báo cho biết Loại KH là Cá nhân (CN) hay Cơ quan (CQ) 2 Ký tự cuối của Mã đặt báo cho biết Mã KH Yêu cầu Sắp xếp bảng tính tăng dần theo cột Ngày đặt, nếu trùng giảm dần theo Mã đặt báo Tên KH: Nếu Loại KH là "CQ" thì dựa vào Mã KH tra trong BẢNG 1, ngược lại thì để trống Loại KH: dựa vào mô tả trên để điền giá trị thích hợp ("Cá nhân" hoặc "Cơ quan") Thành tiền = Tổng Số lượng các loại báo * Đơn giá. Biết rằng: + Nếu Loại KH là Cơ quan thì Đơn giá = 1200, ngược lại Đơn giá = 1500 + Tổng Số Lượng các loại báo là tổng Số Lượng đặt cho báo Tiếp Thị, Phụ Nữ, Thể Thao Giảm Giá: Nếu Loại KH là Cơ quan và Tổng Số Lượng các loại báo >= 100 thì Giảm giá = 10% * Thành tiền, ngược lại thì không giảm giá. Khuyến mãi: Nếu đặt báo vào ngày 3/2 hoặc 26/3 thì điền giá trị "Tặng vé xem ca nhạc", ngược lại thì để trống. Định dạng cột Thành Tiền, Giảm giá theo dạng: 1,000 đồng Rút trích các loại khách hàng là "Cá nhân" và có Số lượng đặt báo Tiếp Thị >= 30 Hãy lập BẢNG THỐNG KÊ SỐ LƯỢNG theo mẫu trên Bài 8: Cho bảng tính: Mô tả Ký tự đầu cho biết Mã hàng 2 ký tự 2.3 cho biết số lượng Ký tự thứ 4 cho biết nơi vận chuyển T: Nội thành, N : Ngoại thành Nếu vận chuyển ra ngoại thành thì ký tự thứ 5 cho biết Km vận chuyển Yêu cầu: Dựa vào Mã hàng tra trong bảng Đơn giá Dựa vào mã hàng để lấy số lượng và định dạng cột số lương có đơn vị tính Cái Nếu số lượng > 10 thì cú 10 cái qui ra thành 1 bó Số lượng còn lại không qui ra thành bó được Nếu vận chuyển trong nội thành thì cước vận chuyển không tính. Nếu vận chuyển ngoại thành thì Cước vận chuyển = Số Km vận chuyển * 5000 Thành tiền = SL lẻ * Dg LẺ + SL bó * Dg Bó. Biết rằng Đơn giá Bó hay lẻ dựa vào Mã hàng tra trong bảng đơn giá Tính số liệu của bảng thống kê thành tiền Sắp xếp : Bảng tính tăng dần theo cột Tên hàng , nếu trùng tên hàng thì sắp giảm dần theo số lượng Rút trích những mặt hàng được vận chuyển ra ngoại thành có số lượng >= 20 Bài 9: Cho bảng tính: Mô tả 2 ký tự đầu của phiếu hàng là ngày bán 2 ký tự 3 ,4 của phiếu hàng là mã hàng Ký tự thứ 5 của phiếu hàng là qui cách Ký tự cuối của phiếu hàng là loại hàng (A: loại A, B : loại B) Yêu cầu Tên hàng : dựa vào mã hàng tra trong bảng đơn giá . Hiển thị theo dạng Tên hàng - Qui cách chi tiết Trong đó , chi tiết dựa vào phiếu hàng tra trong bảng đơn giá Ví dụ : phiếu hàng là 10BH4A thì tên hàng là Bàn họp cao cấp - 4 mét Đơn giá : dựa vào mã hàng tra trong bảng đơn giá . Tuy nhiên , nếu là mặt hàng loại B thì giảm 10% đơn giá loại A Thành tiền = số lượng * đơn giá. Nếu mặt hàng Bàn họp cao cấp hoặc ghế cố định loại A thì giảm 10% Rút trích các mặt hàng Bàn họp cao cấp với số lượng lớn hơn 50 Lập thống kê theo mẫu sau Loại Tổng số lượng Tổng thành tiền Loại A Loại B