›
›

Giáo trình Tin học ứng dụng trong kinh doanh (Phần 1)

pdf 60 trang phuongnguyen 5950
Download
Bạn đang xem 20 trang mẫu của tài liệu "Giáo trình Tin học ứng dụng trong kinh doanh (Phần 1)", để 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:

  • pdfgiao_trinh_tin_hoc_ung_dung_trong_kinh_doanh_phan_1.pdf

Nội dung text: Giáo trình Tin học ứng dụng trong kinh doanh (Phần 1)

  1. 1 BỘ XÂY DỰNG TRƯỜNG ĐẠI HỌC XÂY DỰNG MIỀN TRUNG GIÁO TRÌNH TIN HỌC ỨNG DỤNG TRONG KINH DOANH (Dùng cho sv cao đẳng Ngành quản trị kinh doanh) Biên soạn: ThS. Nguyễn văn Chế Lưu hành nội bộ
  2. 2 LỜI NÓI ĐẦU Nhằm giúp cho sinh viên có tài liệu để học tập, nghiên cứu việc ứng dụng tin học trong lĩnh vực kinh doanh, năm học 2010-2011 tôi đã biên soạn giáo trình Tin học ứng dụng trong kinh doanh. Đây là tài liệu được biên soạn trên cơ sở đề cương học phần Tin học ứng dụng dành cho bậc Cao đẳng ngành Quản trị kinh doanh đang áp dụng tại trường Đại học xây dựng Miền Trung. Nội dung giáo trình gồm 4 chương được phân bố như sau: Chương 1: Một số hàm thông dụug Nội dung chương 1 trang bị cho sinh viên một số hàm thông dụng của EXCEL được sử dụng trong kinh doanh. Chương 2: Cơ sở dữ liệu trên excel Nội dung chương 2 trang bị cho sinh viên kiến thức cơ bản về cách tổ chức cơ sở dữ liệu trên EXCEL để từ đó thực hiện được các thao tác sắp xếp, lọc, trích ghi dữ liệu và sử dụng các hàm tính toán với điều kiện phức tạp thường gặp trong khi giải các bài toán ứng dụng. Chương 3: Tổng hợp, phân tích và thống kê số liệu Nội dung chương 3 trang bị cho sinh viên kiến thức cơ bản về cách tổng hợp dữ liệu từ nhiều sheet, nhiều tập tin khác nhau trong đó có thực hiện một số phép toán: tính tổng, đếm, tìm giá trị lớn nhất, nhỏ nhất và kỹ năng phân tích số liệu 3 chiều. Chương 4: Các hàm tài chính Nội dung chương 4 trang bị cho sinh viên một số hàm sử dụng trong lĩnh vực tài chính giúp sinh viên có thể tính toán và lựa chọn các giải pháp tối ưu trong lĩnh vực này. Chương 5: Các bài toán ứng dụng trong kinh doanh Nội dung chương 5 giới thiệu một số bài toán cơ bản trong lĩnh vực kinh doanh từ việc tổ chức dữ liệu, lựa chọn hàm tính toán và dựa vào kết quả tính toán biết phân tích và rút ra kết luận chính xác. Mặc dù đã rất cố gắng song không thể tránh khỏi những thiếu sót nhất định, tác giả mong nhận được những góp ý cả về nội dung lẫn hình thức của Hội đồng khoa học nhà trường, tập thể giáo viên bộ môn và bạn đọc để tài liệu ngày càng hoàn thiện hơn Người biên soạn Nguyến Văn Chế
  3. 3 MỤC LỤC Nội dung Trang CHƯƠNG 1: MỘT SỐ HÀM THÔNG DỤNG 3 1.1 Giới thiệu 3 1.2 Hàm toán học và lượng giác 3 1.3 Hàm Logic 7 1.4 Hàm thống kê 7 1.5 Hàm xử lý dữ liệu kiểu chuỗi 11 1.6 Hàm tìm kiếm và tham chiếu 13 1.7 Hàm xử lý dữ liệu kiểu ngày 15 CHƯƠNG 2: CƠ SỞ DỮ LIỆU TRÊN EXCEL 20 2.1 Giới thiệu 20 2.2 Sắp xếp dữ liệu 20 2.3 Lọc dữ liệu 21 2.4 Các hàm trên cơ sở dữ liệu 25 2.5 Kiểm tra dữ liệu khi nhập 27 CHƯƠNG 3: TỔNG HỢP, PHÂN TÍCH VÀ THỐNG KÊ SỐ LIỆU 31 3.1 Chức năng Subtotal 31 3.2 Chức năng Consolidate 33 3.3 Tổng hợp, thống kê và phân tích số liệu với Pivotable 39 CHƯƠNG 4: CÁC HÀM TÀI CHÍNH 47 4.1 Khái niệm 47 4.2 Các hàm tài chính 47 CHƯƠNG 5: CÁC BÀI TOÁN ỨNG DỤNG TRONG KINH DOANH 53 5.1 Bài toán dự báo kinh tế 53 5.2 Bài toán tìm mục tiêu 60 5.3 Bài toán qui hoạch tuyến tính 63 5.4 Bài toán phân tích tình huống 66 5.5 Bài toán phân tích độ nhạy 70 5.6 Bài toán tìm giao điểm của đường cung và đường cầu 73 5.7 Bài toán điểm hòa vốn 75 5.8 Tương quan và hồi qui tuyến tính 77 MỤC LỤC 83 TÀI LIỆU THAM KHẢO 85
  4. 4 DANH MỤC CHỮ VIẾT TẮT CSDL Cơ sở dữ liệu QHTT Qui hoạch tuyến tính DA Dự án
  5. 5 CHƯƠNG 1 MỘT SỐ HÀM THÔNG DỤNG TRONG EXCEL Mục đích, yêu cầu Mục đích: - Trang bị cho sinh viên một số hàm thông dụng của EXCEL được sử dụng trong kinh doanh Yêu cầu: - Sinh viên phải hiểu rõ cú pháp của hàm, biết lựa chọn hàm thích hợp để giải quyết các bài toán trong kinh doanh - Giải quyết tốt các yêu cầu bài tập ở cuối chương 1.1 Giới thiệu Hàm là công cụ tính toán lập sẵn trong EXCEL hỗ trợ cho người sử dụng thực hiện các phép toán thường dùng trong xử lý số liệu Cú pháp tổng quát: TÊN_HÀM(danh sách các đối số) - Tên hàm là một từ tiếng Anh viết đầy đủ hoặc rút gọn nhưng mang tính gợi nhớ - Các đối số của hàm có thể là: + Giá trị cụ thể + Địa chỉ ô + Địa chỉ khối ô + Tên khối ô + Hàm tính toán Các đối số của hàm cách nhau bỡi dấu được khai báo trong mục List separator Nếu hàm không đối số ta vẫn phải nhập cặp () sau tên hàm Cách nhập hàm như sau : - Đưa con trỏ ô đến ô cần nhập - Nhập =Công thức tính toán - Nhấn phím Enter hoặc phím di chuyển để kết thúc 1.2 Hàm toán học và lượng giác a. Hàm ABS(): Trả về trị tuyệt đối trị số của number Cú pháp:
  6. 6 ABS(number) Ví dụ = ABS(5-150) 145 = ABS(2*(-50)) 100 b. Hàm INT(): Trả về trị số nguyên gần nhất nhỏ hơn number Cú pháp: INT(number) Ví dụ = INT(123.45) 123 = INT(-3.2) -4 c. Hàm ODD(): Trả về Số nguyên lẻ nhỏ nhất lớn hơn hay bằng number Cú pháp: ODD(number) Ví dụ = ODD(3.7) 5 d. Hàm MOD(): Trả về số dư của phép chia nguyên Cú pháp: MOD(number, divisor) Ví dụ: = MOD(30,7) 2 e. Hàm PRODUCT(): Trả về tích của các đối số Cú pháp: PRODUCT(number1, number2, ) Ví dụ: = PRODUCT(6,5,20) 600 f. Hàm RAND(): Trả về số ngẫu nhiên lớn hơn 0 và nhỏ hơn 1 Cú pháp: RAND() Số ngẫu nhiên giữa 0 và 1 g. Hàm RANDBETWEEN(): Trả về số ngẫu nhiên trong khoảng chỉ định Cú pháp: RANDBETWEEN(bottom,top) Ví dụ:
  7. 7 = RANDBETWEEN(18,45) Số ngẫu nhiên giữa 18 và 45 h. Hàm ROUND(): Làm tròn đến cột số lẻ chỉ định Cú pháp: ROUND(number, number digits) Ví dụ: = ROUND(12345.678,2) 12345.68 = ROUND (12345.678,-3) 12000 i. Hàm SQRT(): Căn bậc 2 của số dương Cú pháp: SQRT(number) = SQRT(25) 5 j. Hàm SUM(): Tổng các trị số trong danh sách Cú pháp: SUM(number1, number2, ) Ví dụ: = SUM(5,10,15,20) 50 k. Hàm SUMIF(): Tính tổng các ô thoả điều kiện Cú pháp: SUMIF(range1, criteria, range2) Ví dụ: Có số liệu như bảng 1.1 Bảng 1.1 = SUMIF(B2:B7,“ >500 “, A2:A7) 20 l. Hàm SUMPRODUCT(): Tính tổng của các tích Cú pháp: SUMPRODUCT(Array1,Array2, ) Ví dụ: Theo số liệu trong bảng 1.1 =SUMPRODUCT(A2:A7,B2:B7) 20,000
  8. 8 m. Hàm CEILING(): Làm tròn số đến bội số gần nhất (lớn hơn hoặc bằng số đó) của một số được chỉ định Cú pháp: CEILING(number,significance) - number: Số cần làm tròn - significance: Con số mà bạn cần làm tròn number đến bội số của nó + Nếu number và significance khác dấu, hàm sẽ báo lỗi #NUM! + Nếu number là bội số của significance, kết quả là chính số đó Ví dụ: =CEILING(5,2) 6 n. Hàm FLOOR():Làm tròn số đến bội số gần nhất (nhỏ hơn hoặc bằng số đó) của một số được chỉ định Cú pháp: FLOOR(number, significance). Ví dụ: =FLOOR(2.5,2) 2 o. Hàm SIN(): Trả về giá trị sin của đối số Cú pháp: SIN(number) Ví dụ: =SIN(1.5708) 1 p. Hàm COS(): Trả về giá trị cos của đối số Cú pháp: COS(number) Ví dụ: =COS(3.1416) -1 q. Hàm ASIN(): Trả về số đo của góc có giá trị sin bằng đối số Cú pháp: ASIN(number) Ví dụ: =ASIN(1) 1.5708 r. Hàm ACOS(): Trả về giá trị cos của đối số Cú pháp:
  9. 9 ACOS(number) Ví dụ: =ACOS(-1) -13.1416 s. Hàm ATAN(): Trả về giá trị tan của đối số Cú pháp: ATAN(number) Ví dụ: =ATAN(1) 0.7854 1.3 Hàm logic a. Hàm AND(): Trả về TRUE nếu tất cả các đối số là TRUE, trả về FALSE nếu một hay nhiều đối số là FALSE Cú pháp: AND(logical1, logical2, ) Ví dụ: Theo số liệu trong bảng 1.2 thì Bảng 1.2 =AND(D2>=7,D2<9) False b. Hàm NOT(): Đảo ngược giá trị của đối số Cú pháp: NOT(Logical) Ví dụ: = NOT(9<5) True c. Hàm OR(): Trả về TRUE nếu một hay nhiều đối số là TRUE, trả về FALSE nếu tất cả các đối số là FALSE Cú pháp: OR(logical1, logical2, )
  10. 10 Ví dụ: Theo số liệu trong bảng 1.2 thì =OR(D2 8) False d. Hàm IF(): Trả về 1 trong 2 giá trị tuỳ thuộc vào giá trị của biểu thức logic Cú pháp: IF(logical_test, value_if_true, value_if_false): Ví dụ: Theo số liệu trong bảng 1.2 thì = IF(D2>=5,"DAT","THI LAI") "DAT" 1.4 Hàm thống kê a. Hàm AVERAGE(): Tính trị trung bình Cú pháp: AVERAGE(number1,number2, ) Ví dụ: =AVERAGE(5,3,4,8,5,6) 5.167 b. Hàm CORREL(): Hệ số tương quan giữa hai chuỗi số liệu Cú pháp: CORREL(array1,array2) Ví dụ: Có số liệu như bảng 1.3 Bảng 1.3 =CORREL(A1:A5,B1:B5) -0.969 (Hai dãy số có sự tương quan nghịch) c. Hàm COUNT(): Đếm số lượng các ô có giá trị Cú pháp: COUNT(value1,value2) Ví dụ: Có số liệu như bảng 1.3 =COUNT(B1:B5) 5 d. Hàm COVAR(): Đồng phương sai, trung bình của tích các cặp sai lệch. Cú pháp: COVAR(array1,array2)
  11. 11 Ví dụ: Có số liệu như bảng 1.3 =COVAR(A1:A5,B1:B5) -6,8 e. Hàm INTERCEPT(): Tung độ gốc của một đường hồi qui tuyến tính Cú pháp: =INTERCEPT(Known_y’s,known_x’s) Ví dụ: Có số liệu như bảng 1.3 =INTERCEPT(A1:A5,B1:B5) 9.2 f. Hàm SLOPE(): Hệ số gốc của một đường hồi qui tuyến tính Cú pháp: SLOPE(known_y’s,known_x’s) Ví dụ: Có số liệu như bảng 1.3 =SLOPE(A1:A5,B1:B5) -1.1 g. Hàm TREND():nst) : Trả về giá trị theo xu thế tuyến tính từ 2 chuỗi giá trị đã có Cú pháp: TREND(known_y's, known_x's, new_x's, const) Ví dụ: Có số liệu như bảng 1.4 Bảng 1.4 =TREND(B2:B9,A2:A9,A10,1) 288172.78 h. Hàm FORECAST(): Dự đoán một giá trị tương lai bằng cách sử dụng các giá trị hiện có theo phương pháp hồi quy tuyến tính Cú pháp: FORECAST(x, known_y's, known_x's) :
  12. 12 Ví dụ: Có số liệu như bảng 1.4 =FORECAST(A10,B2:B9,A2:A9) 288172.78 i. Hàm LINEST():Tính thống kê cho một đường bằng cách dùng phương pháp bình phương tối thiểu để tính đường thẳng (hệ số góc và tung độ gốc) thích hợp nhất với dữ liệu, LINEST(known_y's, known_x's, const, stats): Ví dụ: Có số liệu như bảng 1.4 = LINEST(B2:B9,A2:A9,1,1) {191,90, 211414.37} j. Hàm MAX(): Giá trị lớn nhất của một mảng dữ liệu Cú pháp: MAX(number1,number2, ) Ví dụ: Theo số liệu trong bảng 1.5 Bảng 1.5 =MAX(B2:B10) 27,704 k. Hàm MEDIAN(): Giá trị tại đó chuỗi số liệu được chia đôi sau khi sắp xếp. Nếu số lượng phần tử (n) trong chuỗi số liệu là lẻ thì giá trị trả về bằng giá trị phần tử đứng giữa chuỗi (mod(n,2)+1), ngược lại thì giá trị trả về bằng trung bình cộng của 2 phần tử n/2 và n/2+1 Cú pháp: MEDIAN(number1,number2, ) Ví dụ: Theo số liệu trong bảng 1.5 =MEDIAN(B2:B10) 26,804 l. Hàm MODE(): Số yếu vị; là số có số lần lặp lại nhiều nhất Nếu chuỗi số liệu không tồn tại số liệu lặp lại hàm sẽ trả về thông báo lỗi #N/A Cú pháp:
  13. 13 MODE(number1,number2, ) Ví dụ 1: =MODE(1,3,6,3,8,6) 3 (có 2 giá trị lặp 3 và 6 nhưng 3 xuất hiện trước) Ví dụ 2: =MODE(6,3,6,3,8,1) 6 (có 2 giá trị lặp 3 và 6 nhưng 6 xuất hiện trước) Ví dụ 3: Theo số liệu trong bảng 1.5 thì =MODE(B2:B10) #N/A (vì không có giá trị nào lặp lại) m. Hàm MIN(): Giá trị nhỏ nhất của một mảng dữ liệu Cú pháp: MIN(number1,number2, ) Ví dụ: Theo số liệu trong bảng 1.5 thì =MIN(B2:B10) 24,431 n. Hàm RANK(): Thứ hạng (từ lớn đến nhỏ) của một giá trị Cú pháp: RANK(number,ref,order) Ví dụ: Theo số liệu trong bảng 1.6 thì Bảng 1.6 =RANK(B2,B2:B13,0) 12 (Giá trị xếp hạng thấp nhận giá trị cao) =RANK(B6,B3:B9,1) 1(Giá trị xếp hạng thấp nhận giá trị thấp) o. Hàm STDEV(): Ước tính độ lệch chuẩn của một mẫu Cú pháp: STDEV(number1,number2, )
  14. 14 Ví dụ: Theo số liệu trong bảng 1.6 thì =STDEV() 307.0409 p. Hàm STDEVP(): Ước tính độ lệch chuẩn của một tổng thể Cú pháp: STDEVP(number1,number2, ) Ví dụ: Theo số liệu trong bảng 1.6 thì =STDEVP(B2:B13) 293.9692 q. Hàm VAR(): Ước tính phương sai của một mẫu Cú pháp: VAR(number1,number2, ) Ví dụ: Theo số liệu trong bảng 1.6 thì =VAR() 94,274.09 r. Hàm VARP(): Ước tính phương sai của một tổng thể Cú pháp: VARP(number1,number2, ) Ví dụ: Theo số liệu trong bảng 1.6 thì =VARP(B2:B13) 86,417.91 s. Hàm COUNTIF(): Đếm số ô không rỗng trong vùng thoả mãn 1 điều kiện cho trước Cú pháp: COUNTIF(range,criteria) Ví dụ: Theo số liệu trong bảng 1.6 thì =COUNTIF(B2:B13,>= "1,500") 4 1.5 Hàm xử lý dữ liệu kiểuchuỗi a. Hàm FIND(): Trả về vị trí xuất hiện của chuỗi con trong chuỗi lớn Cú pháp: FIND(findtext, within-text, startnum) start-num:Vị trí bắt đầu tìm(mặc nhiên là1-đầu chuỗi) Ví dụ: =FIND("Excel","Microsoft Excel", 1) 11 b. Hàm LEFT(): Cắt lấy bên trái của chuỗi một số ký tự Cú pháp:
  15. 15 LEFT(text, num-chars) Ví dụ: = LEFT("C11A010076", 4) “C” c. Hàm LEN(): Trả về số chỉ chiều dài của chuỗi. Cú pháp: LEN(text) Ví dụ: = LEN("C11A010076") 10 d. Hàm LOWER(): Đổi chuỗi thành chữ thường. Cú pháp: LOWER(Text) Ví dụ: = LOWER(“THÔNG TIN”) “thông tin” e. Hàm MID(): Cắt lấy một số ký tự bên trong của chuỗi Cú pháp: MID(text, start-num, num-chars) Ví dụ: = MID("C11A010076" ,4,1 ) "A" f. Hàm PROPER(): Viết hoa ký tự đầu từ Cú pháp: PROPER(text) Ví dụ: = PROPER("tran van an") Tran Van An g. Hàm REPLACE(): Thay một phần trong chuỗi bằng chuỗi khác Cú pháp: REPLACE(old-text, start-num, numchars, new-text) Ví dụ: = REPLACE("MS Excel 2003",13,1,"7") ("MS Excel 2007" h. Hàm RIGHT(): Cắt lấy bên phải của chuỗi một số ký tự. Cú pháp: RIGHT(text, numchars) Ví dụ:
  16. 16 = RIGHT(("C11A010076",4) "0076" i. Hàm TEXT(): Đổi số qua chuỗi theo dạng chỉ định. Cú pháp: TEXT(value, format text) Ví dụ: = TEXT(1234.56,"##,###.###") "1,234.56" j. Hàm TRIM(): Cắt bỏ các ký tự trống vô ích trong chuỗi. Cú pháp: TRIM(text) Ví dụ: = TRIM(" MS Excel 2007 ") "MS Excel 2007" k. Hàm UPPER(): Đổi chuỗi thành chữ hoa Cú pháp: UPPER(text) Ví dụ: =UPPER("xay dung") "XAY DUNG" l. Hàm VALUE(): Đổi chuỗi có dạng sốthành trị số Cú pháp: VALUE(text) Ví dụ: = VALUE("0076") 76 1.6 Hàm tìm kiếm và tham chiếu a. Hàm COLUMN(): Số thứ tự cột bên trái của một khối ô. Cú pháp: COLUMN(reference) Ví dụ: Theo số liệu trong bảng 1.7 thì
  17. 17 Bảng 1.7 = COLUMN() Cột D là cột thứ 4 b. Hàm COLUMNS(): Số lượng cột có trong một khối ô Cú pháp: COLUMNS(array) Ví dụ: Theo số liệu trong bảng 1.7 thì =COLUMNS(D2:F12) Từ cột D đến cột F là 3 c. Hàm ROW(): Số thứ tự hàng trên cùng của một khối ô Cú pháp: ROW(reference) Ví dụ: Theo số liệu trong bảng 1.7 thì = ROW(D2:F12) 2 d. Hàm ROWS(): Số lượng hàng có trong một khối ô Cú pháp: ROWS(array) Ví dụ: Theo số liệu trong bảng 1.7 thì = ROWS(D2:F12) 11 e. Hàm VLOOKUP(): Dò tìm Lookup_value bên trái của Table_Array và tham chiếu trị tương ứng ở cột Col_index_num. Cú pháp: VLOOKUP(Lookup_value, Table_array,Col_index_num,{range_lookup}) Lưu ý: - Nếu danh sách xếp tăng dần: Range_lookup= 1 hoặc True - Nếu danh sách không xếp thứ tự: Range_lookup= 0 hoặc False: Ví dụ 1: Theo số liệu trong bảng 1.8 thì để tính giá dịch vụ tại ô I2 ta dùng hàm
  18. 18 Bảng 1.8 = VLOOKUP(G2,$G$15:$I$18,3,0 ) 3 Ví dụ 2: Theo số liệu trong bảng 1.9 thì Bảng 1.9 = VLOOKUP(6,A1:B4,2,1) C f. Hàm HLOOKUP(): Dò tìm lookup_value trên hàng đầu tiên của Table_Array và tham chiếu trị tương ứng ở hàng row_index_num. Cú pháp: HLOOKUP(Lookup_value, Table_array, Row_index_num, {range_lookup}) Ví dụ: Theo số liệu trong bảng 1.8 thì để tính giá phòng tại ô J2 ta dùng hàm = HLOOKUP(C2,$A$14:$E$15,2,0) 60 g. Hàm INDEX(): Chọn một trị trong mảng thông qua chỉ số hàng cột. Cú pháp: INDEX(Array, Rownum, Colnum) Ví dụ: Theo số liệu trong bảng 1.20 thì
  19. 19 Bảng 1.10 = INDEX(A1:B3,3,2) 36 1.7 Hàm xử lý dữ liệu kiểu ngày a. Hàm DATE(): Đổi trị gồm năm, tháng, ngày thành một ngày Cú pháp: DATE(year, month, day) Ví dụ: =DATE(12,1,25) 01/25/12 b. Hàm DATEVALUE(): Đổi chuỗi dạng ngày(mm/dd/yy) thành trị ngày tháng Cú pháp DATEVALUE (date_Text) Ví dụ: = DATEVALUE ("01/ 25/12") 40,933 c. Hàm NOW(): Trả về ngày và giờ hiện hành của máy tính. Cú pháp NOW() Ví dụ: = NOW() 25/12/2011 8:30 d. Hàm TODAY(): Trả về ngày hiện hành của máy Cú pháp TODAY() Ví dụ: =TODAY() 25/12/2011 e. Hàm DAY(): Ngày trong tháng của một biểu thức ngày (biểu thức ngày có thể là chuỗi dạng ngày hoặc địa chỉ ô chứa dữ liệu kiểu ngày) Cú pháp DAY(serial_Number) Ví dụ: = DAY("04/30/75") 30
  20. 20 f. Hàm DAYS360(): Trả về số ngày giữa hai ngày dựa trên cơ sở một năm có 360 ngày (12 tháng, mỗi tháng có 30 ngày) để dùng cho các tính toán tài chính Cú pháp DAYS360(start_date, end_date, method) Ví dụ: =DAYS360("12/25/10","01/28/11") 33 g. Hàm MONTH(): Trả về tháng của một biểu thức ngày Cú pháp MONTH (serial_number) Ví dụ: = MONTH("04/30/75") 4 h. Hàm YEAR(): Trả về năm của một biểu thức ngày Cú pháp YEAR(serial_number) Ví dụ = YEAR("04/30/75") 1975 i. Hàm WEEKDAY(): Trả về ngày trong tuần từ 1 7(Chủ nhật:1, Thứ bảy: 7) Cú pháp: WEEKDAY(serial_number) Ví dụ = WEEKDAY("12/30/95") 7 CÂU HỎI ÔN TẬP CHƯƠNG 1 1. Nêu các loại đối số của hàm. Cách biểu diễn hằng kiểu ngày trong đối số của hàm 2. Hàm IF có thể lồng nhau mấy cấp? 3. Khi nào tham chiếu dữ liệu nên dùng hàm VLOOKUP, khi nào nên dùng hàm HLOOKUP, khi nào nên dùng hàm INDEX
  21. 21 BÀI TẬP CHƯƠNG 1 Bài 1. Nhập dữ liệu sau đây vào bảng tính STT Khách Số phòng Ngày đến Ngày đi Tiền thuê Tiền giảm Tiền phải trả 1 Nguyễn Thành Nam A01 01/01/11 10/01/11 2 Trần Thanh Hùng B01 15/01/11 15/01/11 3 Nguyễn Văn Long A01 20/01/11 25/01/11 4 Trịnh Quốc Minh C02 15/01/11 20/01/11 5 Nguyễn Văn Thanh B02 25/01/11 30/01/11 6 Bùi Quang Dũng A02 01/01/11 30/01/11 Bảng giá thuê phòng Loại Giá/ngày A 500,000 B 300,000 C 200,000 Sau đó thực hiện các yêu cầu sau : - Nhập hàm để tính toán những cột còn trống Trong đó + Tiền thuê=(số ngày đi-số ngày đến+1 )*giá + Tiền giảm: Nếu ngày đến=1/15/07 được giảm 50% + Tiền phải trả=Tiền thuê-tiền giảm - Tính tổng số người đến thuê trong ngày 1/15/07 - Tính tổng số tiền thu được từ ngày 1 đến ngày 15/01/07 Bài 2. Nhập dữ liệu sau đây vào bảng tính BẢNG TỔNG KẾT CUỐI NGÀY STT Mã mặt hàng Tên mặt hàng Đơn vị tính Số lượng Đơn giá Thành tiên 1 A01 1,000 2 B01 80 3 A02 1,200 4 C01 20 5 C02 80 6 A01 500 Tổng Bảng phụ 1 Bảng phụ 2 Kí tự thứ nhất Loại VLXD Đơn vị tính Kí tự thứ 2 và 3 Chất lượng A Gạch men Viên 01 Loại 1 B Tôn Tấm 02 Loại 2 C Xi măng Bao Bảng đơn giá Kí tự thứ nhất Loại 1 Loại 2 A 15,000 13,000 B 80,000 70,000 C 85,000 75,000 Sau đó thực hiện các yêu cầu sau : - Nhập hàm để tính toán những cột còn trống
  22. 22 Trong đó : Thành tiền =(số lượng*đơn giá) nhưng nếu thành tiền >1000000 thì được giảm 2% - Tính tổng tiền thu được khi giá xi măng tăng 5% Bài 3. Nhập dãy số liệu sau: 3,5,9,5,4,6,2 vào bảng tính, sau đó tính giá trị của các hàm sau đây trên dãy số đó: - Average - Max - Min - Mode - Median - Stdev - Var - Varp Bài 4. Nhập bảng dữ liệu sau đây vào bảng tính Năm Doanh số Lợi nhuận 1991 37,197 888 1992 50,444 1,270 1993 71,609 1,617 1994 92,863 2,067 1995 130,679 3,572 1996 174,088 4,861 1997 220,181 5,789 1998 300,943 9,481 1999 417,924 13,171 2000 546,580 15,287 2001 666,848 19,317 2002 947,074 21,855 2003 1,172,459 27,718 2004 1,469,564 37,305 2005 1,865,632 47,585 2006 2,406,582 61,823 2007 2,953,807 85,802 2008 3,815,426 112,541 2009 4,717,066 139,775 2010 5,584,410 172,571 2005 6,438,507 205,171 2006 7,195,923 178,005 Sau đó thực hiện các yêu cầu sau:
  23. 23 - Sử dụng hàm dự báo để dự báo doanh số năm 2007 - Viết phương trình thể hiện tương quan gữa thu nhập và doanh số - Dự báo lợi nhuận tương ứng với doanh số năm 2007 Bài 4. Công ty đầu tư tài chính DIFC đang phân tích biến động cổ phiếu của ngành sản xuất ôtô. Công ty đã thu thập các dữ liệu về cổ phiếu của các công ty SX ôtô trong 3 năm như sau: Giá cổ phiếu các năm của các công ty sản xuất ô tô (ĐVT ngàn đồng): B C D E F G H I 4 CT Cơ Ô tô TCT CT Ô tô Nhà máy CT Ô tô khí Năm Quý Trường CKGT Cửu Ô tô SG Ô tô Hải Tp.HCM Long Cần Thơ SAGACO VN 5 2008 1 121 106 128 102 108 112 6 2 126 125 134 100 120 114 7 3 120 103 132 105 107 114 8 4 128 95 136 105 107 110 9 2009 1 142 86 136 112 109 115 10 2 137 91 137 115 109 121 11 3 139 84 140 110 107 118 12 4 134 92 135 106 108 122 13 2010 1 128 103 138 114 105 124 14 2 125 106 127 105 105 124 15 3 123 104 128 105 102 125 16 4 120 112 125 107 96 123 Hãy tính phương sai và độ lệch chuẩn của từng công ty và của toàn ngành SX ôtô.
  24. 24 CHƯƠNG 2 CƠ SỞ DỮ LIỆU TRÊN EXCEL Mục đích, yêu cầu Mục đích: - Trang bị cho sinh viên kiến thức cơ bản về cách tổ chức cơ sở dữ liệu trên EXCEL để từ đó thực hiện được các thao tác sắp xếp, lọc, trích ghi dữ liệu - Trang bị một số hàm tính toán với điều kiện phức tạp thường gặp trong khi giải các bài toán ứng dụng Yêu cầu: - Sinh viên phải hiểu rõ thành phần của cơ sở dữ liệu, các thao tác trên cơ sở dữ liệu, cú pháp tổng quát của hàm trên cơ sở dữ liệu - Biết cách tổ chức vùng tiêu chuẩn để thực hiện các thao tác và hàm - Giải các bài tập cuối chương và biết vận dụng các kiến thức đã học để giải quyết các bài toán ứng dụng trong thực tế 2.1 Giới thiệu về cơ sở dữ liệu (database) Cơ sở dữ liệu (CSDL) là tập hợp các thông tin, dữ liệu đựơc tổ chức theo cấu trúc hàng, cột để khai thác, truy cập nhanh chóng. - Dòng đầu tiên dùng để chứa tên vùng tin hay còn gọi là trường (Field) của CSDL. Tên vùng tin phải là kiểu chuỗi và duy nhất. - Những dòng kế tiếp dùng để chứa nội dung CSDL. Mỗi dòng được gọi là mẩu tin hay bản ghi (Record) 2.2 Sắp xếp dữ liệu 2.2.1 Sắp xếp dựa vào một cột - Đưa con trỏ ô vào tên cột làm căn cứ sắp xếp - Chọn lệnh Data Xuất hiện nhóm cộng cụ Sort & Filter như hình 2.1 Hình 2.1 - Chọn công cụ để xếp tăng dần Hoặc chọn công cụ để xếp giảm dần 2.2.2 Sắp xếp dựa vào nhiều cột
  25. 25 - Chọn khối dữ liệu cần sắp xếp - Chọn lệnh Data Xuất hiện nhóm cộng cụ Sort & Filter như hình 2.1 - Chọn công cụ Xuất hiện hộp thoại như hình 2.2 Hình 2.2 - Chọn cột ( Field ) làm tiêu chí chính để sắp xếp tại mục Sort by - Chọn kiểu sắp xếp tại khung order: + A to Z: Sắp xếp theo thứ tự tăng dần. + Z to A: Sắp xếp theo thứ tự giảm giần. - Kích chuột vào Add level Xuất hiện hộp thoại như hình 2.3 Hình 2.3 - Chọn cột làm tiêu chí sắp xếp phụ tại mục Then by ( Excel sẽ sắp xếp dựa vào tiêu chí phụ này khi tiêu chí chính trong mục Sort by bị trùng ). - Chọn kiểu sắp xếp cho cột làm tiêu chí sắp xếp phụ tại khung order Lưu ý : Có thể chọn nhiều cột làm tiêu chí sắp xếp phụ - Kích chuột vào hộp kiểm My data has headers (chọn mục này khi trong bảng dữ đang chọn có dòng tiêu đề) - Kích chuột vào nút lệnh OK 2.3 Lọc dữ liệu:
  26. 26 Chức năng này dùng để trích ra những bản ghi trong cơ sở dữ liệu thoả mãn những yêu cầu đặt ra. Có 2 cách lọc: Lọc tự động và lọc theo bảng điều kiện tạo trước. 2.3.1 Lọc tự động (AutoFilter): Là cách lọc làm cho bảng dữ liệu chỉ hiển thị các bản ghi thỏa mãn điều kiện đưa ra, (các bản ghi không thỏa mãn điều kiện sẽ bị ẩn). Thao tác : - Chọn CSDL muốn lọc (chọn cả dòng đầu tiên của CSDL) - Chọn lệnh Data Xuất hiện nhóm cộng cụ Sort & Filter như hình 2.1 - Chọn công cụ Khi đó trên tiêu đề mỗi cột sẽ xuất hiện biểu tượng lọc là các Menu DropDown như bảng 2.1 Bảng 2.1 - Muốn lọc theo điều kiện ở cột nào thì kích chuột vào biểu tượng lọc của cột đó như bảng 2.2 Bảng 2.2
  27. 27 - Chọn giá trị cần lọc: - Nếu điều kiện lọc là một biểu thức ta chọn mục lệnh text filters custom filter Xuất hiện hộp thoại như hình 2.4 Hình 2.4 - Chọn phép so sánh: + equals: bằng + does not equal: không bằng (khác) + is greater than: lớn hơn + is greater than or equal to: lớn hơn hoặc bằng + is less than: nhỏ hơn + is less than or equal to: nhỏ hơn hoặc bằng + begins with: bắt đầu bằng + does not begin with: không bắt đầu bằng - ends with: kết thúc bằng + does not end with: không kết thúcbằng + contains: chứa + does not contain: không chứa - Nhập (hoặc chọn) giá trị làm điều kiện trong hộp danh sách bên phải Lưu ý : Có thể kết hợp thêm một điều kiện lọc nữa bằng cách chọn tương tự trong hai hộp danh sách phía dưới nhưng phải thông qua hai phép toán And (và) hoặc Or (hoặc) 2.3.2 Lọc theo bảng tiêu chuẩn tạo trước : Dùng để lọc dữ liệu có tiêu chuẩn xác định trước hoặc rút trích dữ liệu đến nơi khác. Với cách lọc nàyta phải nhập vào một bảng tiêu chuẩn trước khi gọi lệnh lọc a. Cách tạo bảng tiêu chuẩn - Bảng tiêu chuẩn là 1 khối có ít nhất 2 ô (trên 2 dòng) , ô trên chứa tên
  28. 28 trường (Field), ô dưới chứa tiêu chuẩn lọc. Tên trường làm tiêu chuẩn phải giống hệt tên trường của vùng CSDL tốt nhất là sao chép từ tên trường CSDL - Các dòng phía dưới ghi điều kiện: các giá trị tiêu chuẩn cùng dòng là các toán hạng của phép AND, khác dòng là phép OR Ví dụ 1: Bảng 2.3 là tiêu chuẩn để lọc các bản ghi có số SP bán ra trong tháng 1 =400 Bảng 2.3 Ví dụ 2: Bảng 2.4 là tiêu chuẩn để lọc các bản ghi có số SP bán ra trong tháng 1 lớn hơn 50 Bảng 2.4 Ví dụ 3: Bảng 2.5 là tiêu chuẩn để lọc các bản ghi có số SP bán ra trong tháng 1 với số lượng >150 và 150 hoặc trong tháng 2 ≥200 Bảng 2.6 b. Thao tác lọc - Tạo bảng tiêu chuẩn (như đã trình bày ở trên) - Chọn lệnh Data - Xuất hiện nhóm cộng cụ Sort & Filter như hình 2.1 - Chọn công cụ Advanced. Xuất hiện hộp thoại như hình 2.5
  29. 29 Hình 2.5 - Chọn 1 trong 2 hành động sau: + Filter the list, in-place: Kết quả lọc xuất hiện ngay trên CSDL gốc. Các dòng không thỏa mãn điều kiện sẽ bị ẩn + Copy to another location: Kết quả lọc sẽ được trích sang một vùng khác. - List range: Địa chỉ của bảng dữ liệu cần lọc. Xác định bằng cách gõ trực tiếp hoặc đặt con trỏ vào mục này rồi đưa chuột ra ngoài để quét. - Criteria range: Địa chỉ bảng tiêu chuẩn đã tạo trước đó (cách xác định tương tự List range) - Copy to: Mục này chỉ xuất hiện khi ở mục Action chọn “Copy to another location”. Xác định địa chỉ của một ô bất kỳ ngoài vùng trống dự kiến sẽ chứa kết quả lọc 2.4 Các hàm trên cơ sở dữ liệu Microsoft Excel cung cấp nhiều hàm dùng cho việc phân tích dữ liệu trong cơ sở dữ liệu. Mỗi hàm đều sử dụng 3 đối số là database, field và criteria. Cú pháp tổng quát: TÊN_HÀM(database, field,criteria) - database: Một cơ sở dữ liệu là một danh sách dữ liệu gồm các cột (trường – field) và các dòng (mẫu tin – record). - field: Tên cột, có thể được cho ở dạng text với tên cột được để trong cặp dấu ngoặc kép (“mã hàng”) hay là số đại diện cho vị trí của cột (1, 2, ), hoặc địa chỉ ô chứa tên cột - criteria: Là một dãy các ô chứa điều kiện tương tự như vùng điều kiện trong thao tác lọc Một số hàm thông dụng trên cơ sở dữ liệu
  30. 30 Cho bảng số liệu và vùng tiêu chuẩn như bảng 2.7 Bảng 2.7 a. DAVERAGE():Tính trung bình các giá trị trong một cột của danh sách hay các trị của một cơ sở dữ liệu, theo một điều kiện được chỉ định Cú pháp: DAVERAGE(database, field, criteria) Ví dụ: Tính thành tiền bình quân thỏa điều kiện trong khối D17:E18 = DAVERAGE(A2:I14, I2, D17:E18) b. DCOUNT(): Đếm các ô chứa số liệu trong một cột của danh sách hay các trị của một cơ sở dữ liệu, theo một điều kiện được chỉ định. Cú pháp: DCOUNT (database, field, criteria) Ví dụ: Tính số lần nhập thỏa điều kiện trong khối D17:E18 = DCOUNT(A2:I14, I2, D17:E18) c. DMAX(): Trả về trị lớn nhất trong một cột của một danh sách hay của một cơ sở dữ liệu, theo một điều kiện được chỉ định. Cú pháp: DMAX(database, field, criteria) Ví dụ: Tính số lượng cao nhất thỏa điều kiện trong khối D17:E18 = DMAX(A2:I14, F2, D17:E18) d. DMIN(): Trả về trị nhỏ nhất trong một cột của một danh sách hay của một cơ sở dữ liệu, theo một điều kiện được chỉ định. Cú pháp:
  31. 31 DMIN(database, field, criteria) Ví dụ: Tính số lượng cao nhất thỏa điều kiện trong khối D17:E18 = DMIN(A2:I14, F2, D17:E18) e. DSUM(): Cộng các số trong một cột của một danh sách hay của một cơ sở dữ liệu, theo một điều kiện được chỉ định. Cú pháp: DSUM (database, field, criteria) Ví dụ: Tính tổng thành tiền thỏa điều kiện trong khối D17:E18 = DSUM(A2:I14, I2, D17:E18) 2.5 Kiểm tra dữ liệu khi nhập Khi lập một bảng tính Excel, có những ô chỉ có thể chứa một loại dữ liệu nào đó mà thôi. Nếu nhập sai kiểu dữ liệu thì các công thức có thể tính toán sai hoặc báo lỗi. Để tránh trường hợp người dùng nhập sai kiểu dữ liệu, Excel cung cấp công cụ Data Validaton để khống chế dữ liệu nhập vào. Cách thực hiện như sau : - Chọn vùng bảng tính muốn thực hiện kiểm tra. - Vào menu Data Data Validation. Xuất hiện hộp thoại như hình 2.6 Hình 2.6 - Trong hộp thoại Data Validation, chọn thẻ Settings. - Chọn kiểu dữ liệu trong mục Allow - Quy định các giới hạn trong kiểu dữ liệu đó ở mục Data Ví dụ : khi lập bảng điểm của HS, ở vùng nhập điểm số, ta muốn chỉ được nhập số trong khoảng từ 0 10, ta sẽ khai báo như trong hình 2.6 - Chọn thẻ Input Message để nhập thông báo hướng dẫn (như hình 2.7) khi
  32. 32 người dùng chọn ô đó. Hình 2.7 - Chọn thẻ Error Alert để nhập thông báo lỗi (như hình 2.8) khi người dùng nhập sai. Hình 2.8 CÂU HỎI ÔN TẬP CHƯƠNG 2 1. Các thành phần của một cơ sở dữ liệu trên EXCEL 2. Các thao tác sắp xếp cơ sở dữ liệu dự trên nhiều cột 3. Mô tả các thành phần của vũng tiêu chuẩn 4. Các thao tác lọc, trích ghi dữ liệu 5. Cú pháp tổng quát của các hàm trên cơ sở dữ liệu?
  33. 33 BÀI TẬP CHƯƠNG 2 Bài 1. Nhập dữ liệu sau đây vào bảng tính Sau đó thực hiện các yêu cầu sau : - Nhập hàm để tính toán những cột còn trống - Sắp xếp bảng dữ liệu tăng dần theo ngày - Tính tổng số tiền bán được của các mặt hàng đĩa mềm, đĩa cứng, đĩa CD - Trích ghi ra danh sách mặt hàng đĩa cứng có số lượng bán lớn hơn 5 - Tô đỏ những dòng có số lượng lơn hơn 10 Bài 2. Nhập dữ liệu sau đây vào bảng tính Sau đó thực hiện các yêu cầu sau : - Nhập hàm để tính toán những cột còn trống Trong đó: + Ký tự đầu của mã chỉ tên hàng (T Tea, C Coffee)
  34. 34 + 2 ký tự cuối của mã chỉ loại hàng (TH Thượng hạng, DB đặc biệt, TB Trung bình) + Giá của loại hđ N(Nhập) thấp hơn giá loại hđ X(Xuất) 15% + Thành tiền =Số lượng*đơn giá Giá của loại hđ N thấp hơn giá loại hđ X 15% - Tính tổng thành tiền của các mặt hàng trong bảng thống kê - Tô màu những dòng xuất của tên hàng Tea - Xây dựng vùng tiêu chuẩn để chỉ mặt hàng Tea bán ra trong tháng 10 - Tính tổng số lượng thỏa các giá trị được xây dựng trong vùng tiêu chuẩn Bài 3. Nhập dữ liệu sau đây vào bảng tính Sau đó thực hiện các yêu cầu sau : - Nhập hàm để tính toán những cột còn trống Trong đó: + Tiền ăn =(Ngày đi-Ngày về +1)*tiền ăn/ng + Tiền khách sạn =(Ngày đi-Ngày về )*tiền khách sạn/ng + Tổng cộng =Tiền ăn + Tiền khách sạn+Tiền xe - Tính các giá trị trong bảng thống kê - Tô màu những các chuyến đi đến Đà Lạt - Trích ghi ra danh sách các chuyến đi đến Phú Quốc hoặc Huế - Tính giá trị lớn nhất trong cột tổng cộng của các chuyến đi đến Phú Quốc hoặc Huế Bài 4. Nhập dữ liệu sau đây vào bảng tính.
  35. 35 Sau đó thực hiện các yêu cầu sau : - Nhập hàm để tính toán những cột còn trống Trong đó: + Đơn giá tra Bảng giá sản phẩm + Thành tiền = Đơn giá * Số lượng + Thuế : Nếu Mã sản phẩm có ký tự bên trái là B thì thuế 5% Thành tiền ngược lại thuế 3% thành tiền + Tổng cộng = Thành tiền + thuế - Tính các giá trị trong bảng thống kê - Xây dựng vùng tiêu chuẩn để lọc những sản phẩm bán ra từ ngày 14/10/01 đến 17/10/01 - Xây dựng vùng tiêu chuẩn và tính tổng số lượng bán ra với Mã B002 trước ngày 17/10/01 - Sắp xếp dữ liệu theo Mã sản phẩm và theo số lượng giảm dần
  36. 36 Bài 5. Nhập dữ liệu sau đây vào bảng tính. Sau đó thực hiện các yêu cầu sau : - Nhập hàm để tính toán những cột còn trống Trong đó: + Loại hộ : là ký tự bên phải của Mã hộ + Tiêu thụ = Số mới - Số cũ + Định mức: tra bảng + Vượt mực: Nếu tiêu thụ >Định mức thì Vượt mức = tiêu thụ - định mức ngược lại Vượt mức =0 - Tính các giá trị trong bảng thống kê - Xây dựng vùng tiêu chuẩn chỉ các loại hộ A,B và có vượt mức - Tính tổng tiêu thụ thỏa các giá trị được xây dựng trong vùng tiêu chuẩn
  37. 37 CHƯƠNG 3 TỔNG HỢP VÀ PHÂN TÍCH SỐ LIỆU Mục đích, yêu cầu Mục đích: - Trang bị cho sinh viên kiến thức cơ bản về cách tổng hợp dữ liệu từ nhiều sheet, nhiều tập tin khác nhau trong đó có thực hiện một số phép toán: tính tổng, đếm, tìm giá trị lớn nhất, nhỏ nhất - Trang bị kỹ năng phân tích số liệu 3 chiều Yêu cầu: - Sinh viên phải hiểu rõ các thao tác khi thực hiện tổng hợp số liệu trong 2 trường hợp: các bảng dữ liệu có cấu trúc gống nhau, các bảng dữ liệu có cấu trúc khác nhau - Biết cách tính tổng của mỗi bộ phận trong bảng cơ sở dữ liệu - Biết các thành phần của bảng phân tích số liệu 3 chiều và cách tạo bảng phân tích số liệu 3 chiều - Giải các bài tập cuối chương và biết vận dụng các kiến thức đã học để giải quyết các bài toán ứng dụng trong thực tế 3.1 Chức năng Subtotals (Tổng bộ phận): Chức năng này dùng để nhóm dữ liệu theo từng nhóm đồng thời chèn vào cuối mỗi nhóm những dòng thống kê tính toán (gọi là các bộ phận - Subtotals ) và một dòng tổng kết ở cuối phạm vi (gọi là toàn bộ - GrandTotal). Thao tác như sau: - Sắp xếp CSDL theo cột làm khoá (muốn nhóm theo cột nào thì cột đó gọi là cột làm khoá) - Đặt con trỏ ô vào vùng CSDL - Chọn lệnh Data Xuất hiện nhóm công cụ Outline Hình 3.1 - Trong nhóm công cụ out line (hình 3.1) chọn công cụ Subtotals Xuất hiện hộp thoại như hình 3.2
  38. 38 Hình 3.2 + At each change in: Chọn trường làm khoá để sắp xếp + Use Function: Chọn hàm sử dụng để thống kê + Add SubTotal to: Đánh dấu vào những cột cần thống kê giá trị + Replace current Subtotals: Thay các hàng Subtotal tạo trước đó bằng các hàng Subtotal mới. + Page Break Between Groups: Tự động động tạo dấu ngắt trang giữa các nhómdữ liệu. + Sumary Below data: Tạo các dòng thống kê phía dưới các nhóm dữ liệu. - Chọn xong ấn OK. Ví dụ: Có số liệu về bảng doanh thu bán hàng tháng 7/2010 như sau Hãy tính tổng thành tiền theo tên hàng
  39. 39 Giải : B1: Sắp xếp CSDL theo tên hàng Kết quả như sau B2: Đưa con trỏ vào vùng CSDL B3: Chọn lệnh Data , tại nhóm Outline chọn Subtotal + Tại At Each Change In, Tên hàng + Tại Use Function, chọn hàm Sum + Tại Add Subtotal To chọn thành tiền B4: Chọn OK Kết quả như sau
  40. 40 3.2 Chức năng Consolidate (Tổng hợp từ các cơ sở dữ liệu chi tiết): Chức năng Consolidate được sử dụng để tạo CSDL tổng hợp từ những CSDL chi tiết (được chọn lựa trên cùng một hoặc trên nhiều tập tin bảng tính khác nhau) 3.2.1.Tổng hợp theo vị trí. Được sử dụng khi dữ liệu bảng tính giống hệt nhau về Cấu trúc, bao gồm cả Số hàng, Số cột. Để thực hiện tổng hợp dữ liệu, chúng ta cần tạo ra một Sheet trống, với cấu trúc tương tự như các Sheet khác. B1: Chọn vùng mà chúng ta muốn tổng hợp dữ liệu. B2: Chọn lệnh Data Xuất hiện Ribbon như hình 3.3 Hình 3.3 B3: Trong nhóm công cụ data tools chọn Consolidate Xuất hiện hộp thoại như hình 3.4
  41. 41 Hình 3.4 B4: Lần lượt chọn hàm, nhập vùng dữ liệu cần tổng hợp vào hộp thoại - Function: Chọn hàm cần dùng để tổng hợp - Reference: Nhập địa chỉ vùng dữ liệu. Địa chỉ này bao gồm: 'tên ổ đĩa\[tên tập tin]tên Sheet'!địa chỉ vùng dữ liệu (Nếu dữ liệu ở trong cùng tập tin với bảng tổng hợp thì không cần nhập tên ổ đĩa, tên tập tin - Top Row: Tạo dòng tiêu đề cho bảng tổng hợp. - Left Column: Tạo tiêu đề cột đầu tiên cho bảng tổng hợp. - Create link to source data: Tạo mối liên kết từ bảng tổng hợp đến các bảng chi tiết nhằm mục đích nếu có sự thay đổi trong các bảng dữ liệu chi tiết thì các dữ liệu liên quan trong bảng tổng hợp cũng tự thay đổi theo. - Kích chuột vào nút Add - Tiếp tục khai báo cho các vùng dữ liệu tiếp theo - Sau khi khai báo xong các vùng dữ liệu cần tổng hợp kích chuột vào nút OK. Ví dụ: Có số liệu chi tiết về hàng bán của 3 năm như sau
  42. 42 Yêu cầu: Tổng hợp hàng bán sau 3 năm theo mẫu: Giải: B1: Tạo sheet tổng hợp theo mẫu B2: Chọn khối ô B4:E7 B3: Chọn lệnh Data B4: Trong nhóm công cụ data tools chọn Consolidate Xuất hiện hộp thoại (như hình 3.4) B5: Tại ô Reference nhập địa chỉ : nam2008!$B$4:$E$7, rồi chọn Add B6: Tại ô Reference nhập tiếp địa chỉ : nam2009!$B$4:$E$7, rồi chọn Add B7: Tại ô Reference nhập địa chỉ : nam2010!$B$4:$E$7, rồi chọn Add
  43. 43 Cuối B7 ta có hộp thoại như hình 3.5 Hình 3.5 B8: Chọn OK sẽ được kết quả tổng hợp bảng 3.1 Bảng 3.1 3.2.2.Tổng hợp theo hàng và cột. Được sử dụng khi cấu trúc dữ liệu khác nhau. Excel dựa trên Hàng và Cột mà tổng hợp dữ liệu. Thao tác : - Đặt con trỏ ô ở sheet cần tổng hợp - Chọn lệnh Data - Trong nhóm công cụ data tools chọn Consolidate Xuất hiện hộp thoại như hình 3.3 - Lần lượt chọn những vùng dữ liệu cần tổng hợp trên các sheet (chọn cả tiêu đề dòng, cột), đánh dấu vào mục Top Row & Left Column rồi nhấn nút Add - Chọn Creat Link to Source Data (nếu muốn kết quả tổng hợp thay đổi theo khi dữ liệu nguồn thay đối). - Chọn OK Lưu ý . Nếu CSDL chi tiết có cùng cấu trúc (có cùng số lượng trường, tên trường
  44. 44 và kiểu dữ liệu từng trường hoàn toàn như nhau) thì CSDL tổng hợp sẽ có cấu trúc tương tự như các CSDL chi tiết và mỗi bản ghi của CSDL tổng hợp sẽ là dữ liệu tổng hợp từ các bản ghi trong các CSDL chi tiết. . Nếu các CSDL chi tiết không có cùng cấu trúc thì nhất thiết phải có chung ít nhất trường đầu tiên bên trái cùng kiểu dữ liệu để làm khoá. Lúc đó CSDL tổng hợp sẽ có dạng gộp các CSDL chi tiết theo qui tắc: + Các trường trùng tên sẽ được tổng hợp + Các trường không trùng tên sẽ được ghép nối Ví dụ: Cho bảng số liệu chi tiết về hàng bán của 3 năm (2008 – 2010) như sau: Yêu cầu: Tổng hợp hàng bán sau 3 năm cho các đại lý
  45. 45 Giải: Nhận xét: Các bảng dữ liệu có cấu trúc không giống nhau, số lượng hàng của mỗi bảng cũng không giống nhau, số cột nhiều nhất là 5; số hàng nhiều nhất là 5 B1: Tạo sheet tổng hợp B2: Đưa con trỏ ô đến vị trí cần tạo bảng tổng hợp B3: Chọn lệnh Data, Trong nhóm công cụ data tools chọn Consolidate Xuất hiện hộp thoại như hình 3.3 B4: Tại ô Reference nhập địa chỉ : nam2008!$A$3:$D$6 , chọn Top row và left column sau đó chọn Add B5: Tại ô Reference nhập địa chỉ : nam2009!$A$3:$C$7, chọn Top row và left column sau đó chọn Add B6: Tại ô Reference nhập địa chỉ : nam2010!$A$3:$E$7, chọn Top row và left column sau đó chọn Add Cuối B6 ta có hộp thoại như hình 3.6 Hình 3.6 B7: Chọn OK sẽ được kết quả tổng hợp như bảng 3.2 Bảng 3.2 3.3 Tổng hợp, thống kê và phân tích số liệu với Pivotable
  46. 46 Pivot table là công cụ để tổng hợp và phân tích nhanh chóng số lượng lớn dữ liệu. Sử dụng báo biểu PivotTable để: - Truy vấn một lượng lớn dữ liệu, trình bày gọn, dễ hiểu; - Dữ liệu được gom nhóm và tính toán trên các nhóm; - Trình bày nhiều dạng tổng hợp dữ liệu khác nhau (chuyển cột thành dòng, dòng thành cột); 3.3.1 Cách tạo Pivot Table - Chọn vùng dữ liệu nguồn cho PivotTable - Chọn lệnh Insert Chọn Pivot Table Xuất hiện công cụ như hình 3.7 Hình 3.7 - Chọn PivotTable Xuất hiện hộp thoại như hình 3.8 Hình 3.8 - Chọn vị trí đặt Pivot table  New Worksheet (Trên worksheet mới)  Hoặc Existing Worksheet (Trên worksheet hiện tại) Nếu chọn Existing Worksheet thì nhập thêm địa chỉ ô đặt Pivot table - Chọn OK Xuất hiện hộp thoại như hình 3.9
  47. 47 Hình 3.9 - Chọn các vùng dữ liệu liên kết trên Pivottable bằng cách rê các Field thả vào các vị trí tương ứng gồm: + Report Fielter: Cấp lọc dữ liệu cao nhất + Row Labels: hiển thị đầu dòng + Comumn Labels: hiển thị đầu cột + Value: Field tính toán - Để chọn hàm tính toán: kích phải chuột vào ô Values chọn Value Field settings chọn lại tên hàm Ví dụ: Có bảng kê chi tiết bán hàng như bảng 3.3 A B C D E 1 SHD TENKHACHHANG NGAY TENSP SOLUONG 2 47 Tan Hiep 04/05/11 Gach thedac190 6,700 3 48 Anh Vu 04/05/11 Gach 4lo90 1,400 4 49 Dong Tien 04/05/11 Gach 4lo90 6,000 5 50 Hai Thach 04/05/11 Gach TC4l080 5,000 6 51 Tan Hiep 04/05/11 Gach 4lo90 4,000 7 52 Hai Thach 04/05/11 Gach TC4l080 5,000 8 53 Anh Tu 04/05/11 Gach 4l80 4,000 9 54 Tan Hiep 04/05/11 Gach thedac180 2,000 10 55 Hai Thach 04/05/11 Gach thedac190 5,000 11 56 Hoan My 04/05/11 Gach 4l80 4,000 12 57 Thanh Cong 05/05/11 Gach 4lo 75 5,000
  48. 48 13 58 Quang Son 05/05/11 Gach 4lo90 3,000 14 59 Tan Hiep 05/05/11 Gach 4l80 2,000 15 60 Phu Thuan 05/05/11 Gach chongnong 2,000 16 616 Anh Hai 03/06/11 Gach thedac180 2,000 17 617 Chuc Bao 03/06/11 Gach 4lo 75 5,000 18 618 Cty XD Hop Thang 03/06/11 Gach 4lo 75 5,000 19 619 Phu Thuan 03/06/11 Gach chongnong 2,000 20 620 Dang Phuong 03/06/11 Gach thedac190 6,000 21 621 Tan Hiep 03/06/11 Gach 4lo90 4,500 22 622 Thanh son 03/06/11 Gach 4lo90 2,500 23 623 Truong Ba Ngac 03/06/11 Gach 4lo90 3,000 24 625 Nguyen Duc Chanh 03/06/11 Gach 4lo90 3,000 25 626 Tan Hiep 03/06/11 Gach thedac190 5,000 Bảng 3.3 Yêu cầu: Lập báo cáo dưới góc độ từng khách hàng như sau: TENKHACHHANG NGAY TENSP Tổng SOLUONG Bài giải : B1: Đưa con trỏ ô vào bảng dữ liệu B2: Chọn lệnh Insert Pivot Table Pivot Table B3: Chọn OK B4: Rê Field TENKH thả vào ô Report Filter B5: Rê Field TENSP thả vào ô Row Labels B6: Rê Field NGAY thả vào ô Column Labels B7: Rê Field SOLUONG thả vào ô Values Kết quả như bảng 3.4
  49. 49 Bảng 3.4 - Để xem khách hàng nào ta kích chuột vào biểu tượng rồi chọn tên khách hàng - Để xem theo ngày ta kích chuột vào biểu tượng rồi chọn ngày cần xem - Để xem sản phẩm nào ta kích chuột vào biểu tượng rồi chọn tên sản phẩm cần xem 3.3.2 Hiệu chỉnh Pivot Table - Thêm / xoá trường Để thêm/xoá trường trong PivotTable + Kích chuột phải vào bảng PivotTable Chọn lệnh Show Field list Xuất hiện lại hộp thoại Pivot Table Field list như hình 3.8 + Rê tên trường thả vào vùng cần hiển thị của PivotTable để thêm (hoặc bỏ chọn tên trường trong hộp thoại Pivot Table Field list để xóa trường ra khỏi PivotTable) - Thay đổi hàm tính toán cho trường dữ liệu tính toán + Kích phải chuột vào vùng ô chưa tên hàm + Chọn Value Field settings Xuất hiện hộp thoại như hình 3.10 Hình 3.10
  50. 50 + Chọn lại tên hàm + Chọn OK 3.4 Tạo biểu đồ trong Excel Trong Excel 2007, chúng ta có thể tạo ra biểu đồ một cách nhanh chóng. Sau khi tạo biểu đồ chúng ta có thể dễ dàng thêm các yếu tố mới vào nó như tiêu đề hoặc thay đổi cách trình bày của biểu đồ. 3.4.1 Các dữ liệu worksheet xuất hiện trong biểu đồ Ví dụ: Có biểu đồ được tạo từ bảng 3.5 như sau Bảng 3.5 - Trong biểu đồ trên, dữ liệu từ mỗi ô là một cột. Cột đầu tiên (tên sản phẩm) sẽ là bên phải của biểu đồ, và dòng đầu tiên (các quí) sẽ xuất hiện ở dưới cùng của biểu đồ. - Dữ liệu cho mỗi sản phẩm xuất hiện trong bốn cột riêng biệt, mỗi quí một cột. Chiều cao của mỗi biểu đồ tương ứng đến các giá trị trong ô mà nó đại diện. - Mỗi hàng dữ liệu của sản phẩm có một màu khác nhau trong biểu đồ. Biểu đồ ghi chú tạo ra từ cột đầu tiên trong bảng dữ liệu (tên những sản phẩm), màu sắc đại diện cho các dữ liệu của mỗi sản phẩm - Dòng tiêu đề trtừ worksheet, tháng Giêng, trong bảng dữ liệu Quí 1, Quí 2, Quí3, Quí4 nằm dưới cùng của biểu đồ. Ở phía bên trái của biểu đồ, Excel đã tạo ra một tỷ lệ của số để giúp chúng ta giải thích chiều cao cột. - Bất kỳ thay đổi dữ liệu trong các worksheet sau khi biểu đồ được tạo ra, ngay lập tức biểu đồ cũng thay đổi. 3.4.2 Cách tạo biểu đồ B1: Đưa con trỏ ô vào bảng dữ liệu
  51. 51 Ví dụ: Tạo biểu đồ cho dữ liệu ở bảng 3.6 như sau Bảng 3.6 B2: Kích chuột vào menu Insert (hình 3.11) Hình 3.11 B3: Kích chuột vào vào một trong các loại biểu đồ nhóm Charts Ví dụ: ta chọn loại Column xuất hiện công cụ như hình 3.12 hình 3.12 B4: Kích chuột vào vào một trong dạng biểu đồ Ví dụ ta chọn dạng sau Xuất hiện màn hình biểu đồ như hình 3.13
  52. 52 hình 3.13 3.4.3 Cách thay đổi dạng, loại biểu đồ - Thay đổi dạng B1: Kích chuột vào bên trong biểu đồ. B2: Kích chuột vào dạng cần thay đổi trong mục Chart style. Hình 3.14 - Thay đổi loại biểu đồ B1: Kích chuột vào bên trong biểu đồ. B2: Kích chuột vào Change Chart Type trong nhóm Type Hình 3.15 Xuất hiện màn hình liệt kê các loại đồ thị như hình 3.16 Hình 3.16
  53. 53 B3: Kích chuột vào loại biểu đồ khác Ví dụ ta chọn dạng line Xuất hiện màn hình như hình 3.17 Hình 3.17 B4: Kích chuột vào một trong các dạng biểu đồ của loại line Ví dụ ta chọn dạng OK Xuất hiện màn hình như hình 3.18 Hình 3.18 3.4.4 Thêm tiêu đề cho biểu đồ - Tạo tiêu đề cho biểu đồ B1: Kích chuột vào biểu đồ Xuất hiện hệ thống menu như hình 3.19 Hình 3.19 B2: Kích chuột vào Layout Xuất hiện công cụ như hình 3.20 Hình 3.20
  54. 54 B3: Kích chuột vào Chart Title Xuất hiện công cụ như hình 3.21 Hình 3.21 B4: Chọn vị trí đặt Chart Title Ví dụ ta chọn Above Chart (nằm phí trên biểu đồ) Xuất hiện một text box Chart Title như hình 3.22 Hình 3.22 B5: Kích chuột vào Chart Title B6: Nhập tên cho biểu đồ - Tạo tiêu đề cho các trục (Trục hoành, trục tung) B1: Kích chuột vào biểu đồ Xuất hiện hệ thống menu như hình 3.23 Hình 3.23 B2: Kích chuột vào Layout Xuất hiện công cụ như hình 3.24 Hình 3.24 B3: Kích chuột vào Axis Titles Xuất hiện công cụ như hình 3.25
  55. 55 Hình 3.25 B4: Chọn primary Horizontal axis title Title below Axis để tạo tiêu đề nằm dưới trục hoành Xuất hiện text box Title như hình 3.26 Hình 3.26 B5: Kích chuột vào Axis Titles B6: Nhập tên cho trục hoành Thực hiện tương tự như trên và chọn mục Chọn primary Vertical axis title Rotated title (như hình 3.27) để tạo tiên đề cho trục tung Hình 3.27 CÂU HỎI ÔN TẬP CHƯƠNG 3 1. Trình bày các thao tác thực hiện tính tổng bộ phận trên cơ sở dữ liệu 2. Trình bày các thao tác tổng hợp dữ liệu có cấu trúc giống nhau từ nhiều tập tin khác nhau
  56. 56 3. Trình bày các thao tác tổng hợp dữ liệu có cấu trúc khác nhau từ nhiều tập tin khác nhau 4. Nêu các thành phần của bảng phân tích dữ liệu 3 và trình bày các thao tác để tạo các thành phần đó BÀI TẬP CHƯƠNG 3 Bài 1: Nhập bảng số liệu chi tiết hàng xuất như sau: Hãy tính tổng số lượng theo Tên SP Bài 2: Tạo tập tin tên nam2010.xlsx gồm 5 sheet. Lần lượt nhập các bảng dữ liệu sau đây vào các sheet:
  57. 57 Yêu cầu : Sử dụng công cụ Consolidate để tổng hợp số ngày thuê trong năm theo mẫu sau (kết quả lưu vào sheet5) Bài 3: Tạo các tập tin sau: nam2009.xlsx, nam2010.xlsx, nam2011.xlsx sau đó lần nhập : - Bảng số liệu sau vào sheet1 của tập tin nam2009.xlsx THỐNG KÊ LƯỢNG HÀNG NHẬP Mã sản phẩm Năm 2009 BC 735 SH 450 KG 380 MH 200 XL 500 - Bảng số liệu sau vào sheet2 của tập tin nam2010.xlsx
  58. 58 THỐNG KÊ LƯỢNG HÀNG NHẬP Mã sản phẩm Năm 2010 BP 550 CH 750 MB 300 MH 550 XL 850 - Bảng số liệu sau vào sheet3 của tập tin nam2011.xlsx THỐNG KÊ LƯỢNG HÀNG NHẬP Mã sản phẩm Năm 2010 SP 150 CK 950 BN 600 CH 350 XL 250 Yêu cầu : Sử dụng công cụ Consolidate để tổng hợp lượng hàng nhập trong 3 năm theo mẫu: THỐNG KÊ LƯỢNG HÀNG NHẬP Mã sản phẩm Năm 2009 Năm 2010 Năm 2011 Kết quả lưu vào tập tin tonghop3nam.xlsx Bài 4: Nhập bảng số liệu bán hàng tháng 7/2009 sau: NGÀY SoHD MSKH Mshang Slban TT bán 1/7/09 1 KH4 SG33 5 670,000 1/7/09 1 KH4 POPC 4 420,000 1/7/09 1 KH4 COFL 18 1,620,000 1/7/909 2 KH5 CDSC 6 192,000 1/7/09 2 KH5 POBC 9 765,000 1/7/09 2 KH5 POPL 4 540,000 1/7/09 3 KH2 PEPC 3 123,000
  59. 59 2/7/09 4 KH9 COFC 5 190,000 2/7/09 5 KH6 RB 9 558,000 Sử dụng công cụ Pivot Table để lịêt kê từng mặt hàng, mỗi mặt hàng liệt kê từng khách hàng với số lượng và trị giá mua có dạng sau Mỗi lần chọn một Mshang khác thì số liệu báo cáo thay đổi tương ứng giúp chúng ta xem được số liệu tổng hợp của từng mặt hàng bán trong tháng 7/2009. Bài 5: Nhập bảng số liệu về khách hàng vay tiền như sau: Sử dụng công cụ Pivot Table để lịêt kê từng ngày, mỗi ngày hàng liệt kê họ tên khách khách hàng với thời hạn vay và số tiền vay có dạng sau