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 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 - 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 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.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 - 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 Sắp xếp dựa vào nhiều cột 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 - 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 - 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 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.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 - 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 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 filterscustom filter. Xuất hiện hộp thoại như 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. + does not contain: không chứa.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 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.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.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à <= 500 Bảng 2.6 là tiêu chuẩn để lọc các bản ghi có số lượng bán ra trong tháng 1 >150 hoặc trong tháng 2 ≥200 Bảng 2. 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 - 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,.