Giáo trình Microsoft Excel 2000 - Bài 5: Cơ sở dữ liệu (Database)

Giáo trình Microsoft Excel 2000 1/24 Lª ViÖt Anh – S−u tÇm vμ biªn so¹n BÀI 5: CƠ SỞ DỮ LIỆU ( DATABASE ) Một trong các ứng dụng căn bản của một chương trình bảng tính là phân tích dữ liệu. Bên cạnh các công cụ dễ sử dụng để giúp người mới dùng có thể đơn giản phân tích số liệu Excel còn cung cấp nhiều công cụ mạnh khác cho kỹ thuật, khoa học và phân tích tài chình. Bạn có thể dùng Excel để tạo lập cơ sở dữ liệu hay danh mục thông tin. Các tính năng như Data Form giúp bạn nhanh chóng thêm, bớt và tìm kiếm các bản ghi. Bạn cũng có thể nhanh chóng sắp xếp và lọc dữ liệu trong cơ sở dữ liệu. Sử dụng các tính năng như outlining hay pivot table, bạn có thế xử lý danh mục để tổng hợp số liệu theo nhiều góc độ khác nhau. Bạn có thể dùng Goal Seek và Solver để tìm lời giải tối ưu cho các vấn đề tài chính. Các tính năng khác của Excel như AutoFill, AutoComplete, và Pick From List cho phép bạn tăng tốc nhập liệu. Bạn có thể sử dụng Template Wizard để tạo nên những mẫu nhập liệu có kiểu dạng chuyên nghiệp. Biểu mẫu có thể chứa các hộp điều khiển như hộp kiểm tra, thanh trượt. 5.1. Tạo một CSDL Một trong những ứng dụng căn bản của Excel là quản trị dữ liệu. Bạn có thể dùng các tính năng của Excel để làm việc với một danh sách đơn giản ví dụ mục các việc cần làm cho đến các cơ sở dữ liệu phức tạp hơn. Trong Excel, khái niệm cơ sở dữ liệu và danh sách được sử dụng thay nhau. Chúng ta sử dụng khái nhiệm cơ sở dữ liệu để ám chỉ cả hai: danh sách đơn và cơ sở dữ liệu phức tạp. Giáo trình Microsoft Excel 2000 2/24 Lª ViÖt Anh – S−u tÇm vμ biªn so¹n Một CSDL chứa các bộ dữ liệu giống nhau như cơ sở dữ liệu khách hàng. Một hàng thông tin trong CSDL được gọi là bản ghi record. Các mục tin được ghi trong các trường, mỗi cột là một trường. Ví dụ tên khách hàng được lưu trong trường tên. Nếu tổ chức dữ liệu dới dạng cơ sở dữ liệu, bạn có thể nhanh chóng sắp xếp, lọc và tổng hợp thông tin. Các bước 1. Nhập tên trường trên một hàng của bảng tính. Các tên trờng này (ví dụ Họ Tên, Địa Chỉ, Điện Thoại, ... ) là các tiêu đề cột của cơ sở dữ liệu. 2. Chọn vùng tên trường và định dạng ví dụ chọn chữ đậm, nghiêng hay chọn Font chữ to hơn để làm nổi bật trường với các bản ghi. 3. Nhập các bản ghi (như thông tin từng khách hàng) ngay dưới tên trường, mỗi bản ghi trên một hàng. Không để các hàng trắng sau tên trường. Chú ý: Cơ sở dữ liệu của Excel sự thực chỉ là một dạng bảng tính đặc biệt. Chúng ta vẫn có thể dùng các lệnh bảng tính bình thờng để hiệu chỉnh và định dạng cơ sở dữ liệu. 5.2. Chèn một bản ghi Tính năng data form cung cấp cách thuận lợi để chèn, xoá và tìm bản ghi. Bạn cũng có thể chèn, xoá trực tiếp trên bảng tính. Các bước 1. Chọn một ô bất kỳ trong CSDL; sau đó gọi Data, Form. 2. Nhấn nút New ; sau đó nhập bản ghi mới vào các hộp văn bản. 3. Lặp lại bước 2 cho mỗi bản ghi mới bạn muốn thêm. Giáo trình Microsoft Excel 2000 3/24 Lª ViÖt Anh – S−u tÇm vμ biªn so¹n 4. Nhấn Close khi kết thúc. Các bản ghi mới được thêm nối đuôi vào CSDL. Bạn có thể sắp xếp lại các bản ghi theo một trật tự khác. TIP: Để không ghi bản ghi vừa nhập, nhấn nút Restore. Để chèn trực tiếp bản ghi vào bảng tính, nhấn chuột phải lên tiêu đề hàng nơi bạn muốn hàng mới xuất hiện. Sau đó chọnh Insert từ menu tắt để chèn một hàng trắng vào CSDL. 5.3. Xoá bản ghi Bạn có thể xoá trực tiếp ngay trên bảng tính hoặc sử dụng data form để tìm đến và xoá một bản ghi cụ thể. Các bước 1. Để xoá một bản ghi sử dụng data form, chọn một ô bất kỳ trong CSDL; sau đó gọi Data, Form. 2. Nhấn nút Find Next hay Find Prev cho đến khi tới bản ghi cần xoá, nhấn Delete. 3. Khi một cửa sổ cảnh báo xuất hiện, nhấn OK để xoá bản ghi. (Nhớ rằng bạn không thể Undo một bản ghi bị xoá). Nhấn nút Close để quay lại bảng tính. TIP: Để xoá trực tiếp một bản ghi từ bảng tính, nhấn chuột phải lên tiêu đề hàng có chứa bản ghi cần xoá. Sau đó chọn Delete từ menu tắt. Với cách này bạn có thể phục hồi lại bản ghi vừa xoá sử dụng Edit, Undo. 5.4. Tìm kiếm bản ghi Khi cần tìm các bản ghi cụ thể, bạn có thể dùng data form để tìm. Chỉ cần nhập tiêu chuẩn tìm kiếm và Excel sẽ tìm các bản ghi thoả mãn tiêu chuẩn. Các bước Giáo trình Microsoft Excel 2000 4/24 Lª ViÖt Anh – S−u tÇm vμ biªn so¹n 1. Chọn một ô bất kỳ trong CSDL; sau đó gọi Data, Form. 2. Nhấn nút Criteria; sau đó nhập tiêu chuẩn cần tìm trong một hay nhièu hộp văn bản. Excel tìm các bản ghi thoả mãn tiêu chuẩn. 3. Nhấn Find Next cho đến khi gặp bản ghi cần tìm, để thoát khỏi Form nhấn nút Close. TIP: Trong phần tiêu chuẩn bạn có thể dùng các ký tự thay thế (* và ?) cho tr- ường văn bản và các toá tử so sánh đối với trường số. 5.5. Sắp xếp một cơ sở dữ liệu Bạn có thể sắp xếp bất kỳ dữ liệu nào trên bảng tính, tuy nhiên tổ chức dữ liệu dưới dạng CSDL cho phép sắp xếp dữ liệu nhanh nhất. Bạn có thể sắp xếp tăng hay giảm. Các bước 1. Chọn một ô bất kỳ trong CSDL; sau đó gọi Data, Sort. 2. TRong hộp Sort By, chọn trường khoá sắp xếp chính, sau đó chọn trật tự tăng (Ascending) hay giảm (Descending). 3. Nếu bạn muốn chỉ định khoá xắp xếp phụ, chọn chúng trong hộp Then By, và chỉ định trật tự cho mỗi khoá phụ. Nhấn OK để thực hiện sắp xếp. TIP: Để nhanh chóng sắp xếp cơ sở dữ liệu theo một trường đơn, chọn một ô bất kỳ trong trờng đó. Sau đó nhấn nút Sort Ascending hay Sort Descending trên thanh công cụ chuẩn. Chú ý: Ngoài trật tự tăng giảm ngầm định bạn cũng có thể yêu cầu Excel sắp xếp CSDL theo một trật tự do bạn chỉ định. Trước hết bạn phải tạo ra trật tự này gọi là Giáo trình Microsoft Excel 2000 5/24 Lª ViÖt Anh – S−u tÇm vμ biªn so¹n Custom List. Sau đó gọi Data, Sort, nhấn mục Option, dới mục First key sort order chọn mục custom list. Tính năng này hữu ích khi bạn ví dụ nh muốn sắp xếp trật tự tiếng Việt 5.6. Tạo một Custom List Excel cho phép tạo và chèn một danh sách người dùng vào một bảng tính. Giả thiết bạn phải thường xuyên nhập tên các công ty hay các ngày trong tuần bằng tiếng Việt. Bạn có thể tạo một custom list. Các bước 1. Lên Tools, Options; chọn phiếu Custom Lists. 2. Trong hộp List Entries, nhập các mục cho custom list. Nhấn Enter sau mỗi mục. 3. Nhấn Add. Danh sách nhập vào xuất hiện trong Custom Lists box. 4. Nếu bạn muốn tạo thêm các custom list mới, nhấn NEW LIST trong cửa sổ Custom 5. Lists, lặp lại các bước 2 và 3 trên. 6. Nhấn OK để đóng cửa sổ. TIP: Nếu bạn muốn tạo một custom list dựa trên các ô sẵn có trong bảng tính thì hãy nhập địa chỉ vùng ô trong Import List From Cells. Sau đó nhấn nút Import để nhập danh sách. Giáo trình Microsoft Excel 2000 6/24 Lª ViÖt Anh – S−u tÇm vμ biªn so¹n 5.7. Lọc tự động với AutoFilter Tính năng AutoFilter cho phép bạn lọc và giữ lại một tập con các bản ghi của một danh sách mà không làm ảnh hưởng đến danh sách gốc. Cạnh mỗi trường sẽ xuất hiện một mũi tên sổ xuống để bạn chọn tiêu chuẩn lọc. Các mục chọn trong AutoFilter Các mục chọn để đặt tiêu chuẩn lọc đợc liệt kê trong bảng sau: Mục Diễn giải (All) Hiển thị toàn bộ bản ghi cho trờng này(mục ngầm định). (Top 10) Cho phép lọc lại một số chỉ định các bản ghi có trị đứng đầu hoặc đứng cuối danh sách. (Custom) Hiển thị cửa sổ Custom AutoFilter để bạn tạo tiêu chuẩn AND hoặc OR. (Trị cụ thể) Chỉ hiển thị bản ghi có trị bằng chính xác trị bạn chọn cho trư- ờng này. (Blanks) Hiển thị tất cả bản ghi trắng trong trường này. (NonBlanks) Hiển thị tất cả các bản ghi có dữ liệu trên trường này. Các bước 1. Chọn một ô bất kỳ trong danh sách; sau đó chọn Data, Filter, AutoFilter. 2. Nhấn lên mũi tên bên cạnh trường chứa dữ liệu bạn định lọc. 3. Chọn tiêu chuẩn lọc cho trường theo các mục chọn trên. Khi lọc, các hàng không thoả mãn tiêu chuẩn lọc sẽ bị ẩn đi. Giáo trình Microsoft Excel 2000 7/24 Lª ViÖt Anh – S−u tÇm vμ biªn so¹n 4. Để bỏ chế độ lọc AutoFilter, gọi Data, Filter, AutoFilter. Các mũi tên sẽ biến mất. 5.8. Lọc dữ liệu sử dụng lọc nâng cao Nếu bạn phải lọc dữ liệu với tiêu chuẩn lọc phức tạp thì bạn nên sử dụng Advanced Filter. Kết quả cũng nh lọc với AutoFilter tuy nhiên không có các mũi tên bên cạnh trờng. Để sử dụng Advanced Filter bạn phải tạo một vùng tiêu chuẩn lọc. Tốt nhất bạn nên đặt vùng tiêu chuẩn lọc lên trên đỉnh hay dưới đáy danh sách. Tiêu chuẩn lọc phức tạp có hai loại:Tiêu chuẩn so sánh và Tiêu chuẩn tính toán y Tiêu chuẩn lọc so sánh cho phép lọc lại những bản ghi nằm trong một giới hạn nào đó. Tiêu chuẩn lọc có thể là trị chuỗi, số logíc hay biểu thức so sánh. Ví dụ: Cho biết danh sách các sinh viên có điểm văn > 6. y Tiêu chuẩn tính toán là tiêu chuẩn lọc mà trị so sánh phải được tính toán hoặc lọc trên cột ảo được tính từ các cột khác. Trích lọc danh sách các sinh viên có tổng điểm > 15. y Liên kết tiêu chuẩn: Có thể lọc, xóa hay rút trích các mẩu tin trong vùng dữ liệu bằng phép giao (AND) hay hội (OR) của nhiều điều kiện khác nhau Phép AND: Nếu các ô điều kiện khác cột. Phép OR: Nếu các ô điều kiện khác dòng. Ví dụ: Trích lọc danh sách sinh viên có điểm văn > 5 và điểm toán > 5. Tạo vùng tiêu chuẩn lọc y Trên hàng đầu nhập tên trường giống hệt nh tên trường cần lọc trên cơ sở dữ liệu. Giáo trình Microsoft Excel 2000 8/24 Lª ViÖt Anh – S−u tÇm vμ biªn so¹n Vùng tiêu chuẩn tính toán có tên trường khác với mọi tên trờng của cơ sở dữ liệu. y Nhập các tiêu chuẩn lọc trên các hàng bên dưới chứa trị hay phép so sánh với một trị. Các tiêu chuẩn nằm cùng hàng nếu bạn muốn chúng cùng được thoả mãn (AND). Các tiêu chuẩn nằm khác hàng nếu chỉ cần một trong số đó thoả mãn (OR) y Tiêu chuẩn tính toán bắt đầu bằng dấu bằng, sau đó là biểu thức logíc. Tip: Để có tên trường của vùng tiêu chuẩn giống hệt như của vùng CSDL, bạn nên copy tên trường. Chú ý: Vùng tiêu chuẩn và vùng cơ sở dữ liệu phải cách nhau ít nhất một hàng trắng (hay cột trắng) Các bước 1. Nếu cha có vùng tiêu chuẩn lọc, hãy tạo nó theo nguyên tắc nêu trên 2. Chọn một ô bất kỳ trong cơ sở dữ liệu. Lên Data, Filter, gọi Advanced Filter. 3. Trong cửa sổ Advanced Filter, khai báo các thông số cần thiết. Các thông số trong cửa sổ Advanced Filter Filter the List In Place Chọn mục này nếu bạn muốn lọc tại chỗ Copy to another Location Chọn mục này nếu bạn muốn copy kết quả ra chỗ khác trên cùng bảng tính. Nếu bạn chọn mục này thì mục Copy To sẽ tự động bật sáng Giáo trình Microsoft Excel 2000 9/24 List Range Khai báo địa chỉ vùng CSDL (cả tên trờng) Criteria Range Khai báo địa chỉ vùng tiêu chuẩn lọc (cả tên trờng) Copy To Chỉ định ô đầu tiên của vùng bạn định đa kết quả ra đó Unique Record Only Nếu có nhiều bản ghi giống nhau thì chỉ giữ lại một bản 5.9. Sử dụng hàm Database. Các hàm Database có tính chất thông kê dữ liệu các mẩu tin trong vùng dữ liệu (Database) thỏa điểu kiện của vùng tiêu chuẩn (Criteria). Cú pháp chung của các hàm Database: Tên_hàm(, , ). Trong đó: y Database: Là vùng dữ liệu. y Field: Tên tiêu đề cột hay địa chỉ ô chúa tiêu đề cột hoặc thứ tự cột ( bắt đầu từ 1) của vùng dữ liệu. y Criteria: Vùng tiêu chuẩn (điều kiện). Hình 5.1 Sử dụng hàm Lª ViÖt Anh – S−u tÇm vμ biªn so¹n Giáo trình Microsoft Excel 2000 10/24 Lª ViÖt Anh – S−u tÇm vμ biªn so¹n 1. Hàm DSUM: Hàm tính tổng trên Field (cột) của vùng dữ liệu của vùng dữ liệu tại những mẩu tin thỏa mãn điều kiện của vùng tiêu chuẩn. Ví dụ: Tính tổng lương của các nhân viên thuộc phòng TC = DSUM(A2:D9, “Luong”, C11:C12) = DSUM(A2:D9, D2, C11:C12) = DSUM(A2:D9, 5, C11:C12) 2. Hàm DMAX: Hàm này tính giá trị lớn nhất trên cột (Field) của vùng dữ liệu tại những dòng thỏa mãn điều kiện của vùng tiêu chuẩn Ví dụ: Cho biết NV lớn tưổi nhất của phòng TV = DMAX(A2:D9, “Tuoi”, C11:C12) = DMAX(A2:D9, B2, C11:C12) = DMAX(A2:D9, 2, C11:C12) 3. Hàm DMIN: Hàm này tính giá trị nhỏ nhất trên cột (Field) của vùng dữ liệu tại những dòng thỏa mãn điều kiện của vùng tiêu chuẩn Ví dụ: Cho biết NV nhỏ tưổi nhất của phòng TV = DMIN(A2:D9, “Tuoi”, C11:C12) = DMIN(A2:D9, B2, C11:C12) = DMIN(A2:D9, 2, C11:C12) 4. Hàm DCOUNT: Hàm này tính số phần tử kiểu số trên cột (Field) của vùng dữ liệu tại những dòng thỏa mãn điều kiện của vùng tiêu chuẩn Ví dụ: Cho biết số NV có tưổi 26 = DCOUNT(A2:D9, “Tuoi”, D11:D12) Giáo trình Microsoft Excel 2000 11/24 Lª ViÖt Anh – S−u tÇm vμ biªn so¹n = DCOUNT(A2:D9, B2, B11:D12) = DCOUNT(A2:D9, 2, D11:D12) 5. Hàm DCOUNTA: Hàm này tính số phần tử khác trống trên cột (Field) của vùng dữ liệu tại những dòng thỏa mãn điều kiện của vùng tiêu chuẩn 6. Hàm DAVERAGE: Hàm này tính trung bình cộng trên cột (Field) của vùng dữ liệu tại những dòng thỏa mãn điều kiện của vùng tiêu chuẩn Ví dụ: Tính lương bình quân của nhân viên thuộc phòng TV = DAVERAGE(A2:D9, “Luong”, D11:D12) Giáo trình Microsoft Excel 2000 12/24 5.10. Data table: Đây là phương pháp tính giá trị một vùng có cùng công thức giống nhau. 1. Phương pháp 1(Table 1): Các ô trong vùng table sẽ được tính giá trị theo công thức được thiết lập ở các ô đầu tiên trên cột tương ứng của vùng Table. Hình 5.2 Sử dụng Data table Phương pháp này cần: y Cột đầu của vùng Table phải có giá trị. y Ô input (tam) chứa giá trị của các ô cột đầu.trong vùng Table. Công thức của dòng đầu phụ thuộc vào giá trị ô input y Xác lập công thức cho các ô đầu của các cột còn lại trong vùng Table. Ví dụ: Công thức của các ô: B2 = E1*2 C2 = E1*3 Cách thực hiện y Mở vùng Table (A2:C5) y Mở trình đơn Data\Table. Lª ViÖt Anh – S−u tÇm vμ biªn so¹n Giáo trình Microsoft Excel 2000 13/24 Lª ViÖt Anh – S−u tÇm vμ biªn so¹n y Trong hộp thoại Table nhập địa chỉ ô Input (E1) vào khung Column Input Cell. y Click OK Để thực hiện Chú ý: Khi đó giá trị của các ô của vùng B3:C5 sẽ được tính theo qui tắc: Lấy giá trị trong ô cột đầu cùng dòng cho vào ô input và trả về giá trị theo công thức trong ô dòng đầu cùng cột. Ví dụ: Ô C5 được tính như sau: lấy giá trị 3 trong ô A5 (ô cột đầu, cùng dòng ô C5) cho vào ô input (E1) và trả về giá trị theo công thức trong ô C2 (ô dòng đầu cùng cột với C5). Giá trị ô C5 theo công thức C2 là 9 vì E1*3 là 3*3 = 9 2. Phương pháp 2(Table 2): Các ô trong vùng Table sẽ được tính giá trị theo công thức đươc thiết lập ở ô góc trên bên trái của vùng Table. Phương pháp này cần: y Cột đầu của vùng Table có giá trị y Dòng đầu của vùng Table có giá trị y Ô input dòng chứa giá trị của các ô cột đầu trong vùng Table y Ô input cột chứa giá trị của các ô cột đầu trên cột tính giá trị trong vùng Table y Xác định công thức cho ô góc trên bên trái của vùng Table Ví dụ: Công thức của ô A2 = D1*2 + E3 Cách thực hiện: y Chọn vùng Table (chọn A2:C5) y Mở trình đơn Data \ Table. Giáo trình Microsoft Excel 2000 14/24 Lª ViÖt Anh – S−u tÇm vμ biªn so¹n y Trong hộp thoại Table, nhập địa chỉ ô input dòng (E2) vào khung row input cell và ô input cột D1 vào khung Column input cell y Click OK để thực hiện. Chú ý: Khi đó giá trị của các ô của vùng B3:C5 sẽ được tính theo qui tắc: Lấy giá trị trong ô cột đầu cùng dòng và cho vào ô input cột và giá trị trong ô cột đầu cùng cho vào ô input dòng trả về giá trị theo công thức trong ô góc trên bên trái. Ví dụ: Ô C5 được tính như sau: lấy giá trị 3 trong ô A5 (ô cột đầu, cùng dòng ô C5) cho vào ô input (E1) và trả về giá trị theo công thức trong ô C2 (ô dòng đầu cùng cột với C5). Giá trị ô C5 theo công thức C2 là 9 vì E1*3 là 3*3 = 9 5.11. Pivot Table Pivot table là bảng tổng hợp, phân tích và xử lý dữ liệu từ một danh sách hay một bảng. Với Pivot Table ta có thể tổng hợp dữ liệu theo nhóm. Sau khi có Pivot Table bạn có thể vẽ đồ thị từ dữ liệu tổng hợp này. a) Tạo một Pivot Table Để tạo một Pivot Table sử dụng PivotTable Wizard bạn cần cho Excel biết tổng hợp trờng nào theo cột và trờng nào theo hàng. Bạn cũng có thể chỉ định trư- ờng trang để xếp các nhóm theo trang. Bạn có thể đảo lại vị trí của các trờng để có một bảng tổng hợp khác. Bạn có thể tạo một pivot table từ nhiều nguồn dữ liệu khác nhau. Thường nguồn dữ liệu là các bảng của Excel. Bạn cũng có thể tạo Pivot Table từ nguồn dữ liệu ngoại, từ nhiều vùng hợp lại, hay từ chính các Pivot Table khác. Các bước Giáo trình Microsoft Excel 2000 15/24 Lª ViÖt Anh – S−u tÇm vμ biªn so¹n 1. Chọn một ô bất kỳ trong danh sách cần tổng hợp; sau đó chọn Data, PivotTable Report. 2. Trong bước 1 của PivotTable Wizard, chọn nguồn dữ liệu ( Microsoft Excel List hay từ các cơ sở dữ liệu khác); sau đó nhấn nút Next. 3. Trong bước 2 của PivotTable Wizard, vùng địa chỉ của danh sách xuất hiện trong hộp Range. Nếu địa chỉ sai hãy chỉnh lại. Sau đó nhấn Next. 4. Trong bước 3 của PivotTable Wizard, bạn trình bày bố cục của pivot table. Các trường trở thành các nút nằm bên phải của cửa sổ. Kéo nút ứng với trường dữ liệu vào vùng DATA. Để sắp xếp một trờng theo cột, kéo nút của trờng đó len vùng COLUMN. Để sắp xếp một trờng theo hàng, kéo đến vùng ROW. Nhấn nút Next. 5. Trong bước 4 của PivotTable Wizard, bạn chỉ định ô để đặt pivot table. Nếu muốn đặt thêm các tuỳ chọn khác cho pivot table, nhấn nút Options, đặt các tuỳ chọn khác, và nhấn OK. sau đó nhấn Finish. b) Hiệu chỉnh một Pivot Table Vì pivot tables chỉ là bảng hiển thị thông tin nên bạn không thể thay đổi thông tin trên bảng. Bạn chỉ có thể thay tên của các trường. Các bước 1. Để hiệu chỉnh tên trường, chọn trường 2. Nhập tên mới. TIP: Để thay đổi các tuỳ chọn khác cho một trường, nhấn kép lên nút của trường đó. Chọn các tuỳ chọn; sau đó nhấn OK. c) Thêm trường dữ liệu Giáo trình Microsoft Excel 2000 16/24 Lª ViÖt Anh – S−u tÇm vμ biªn so¹n Các bước 1. Để thêm trường dữ liệu, chọn một ô trong bảng pivot table. 2. Nhấn nút PivotTable Wizard trên thanh PivotTable. (Nhấn chuột phải lên vùng thanh công cụ, chọn PivotTable để hiển thị thanh này nếu cha hiện) 3. Trong bớc 3 của PivotTable Wizard, kéo thêm nút của trường bạn muốn thêm vào vùng DATA; nhấn nút Finish. d) Thêm trường Rows, Columns, hay Pages Nếu bạn muốn thêm chi tiết vào một bảng pivot table, bạn có thể thêm các trường. Các bước 1. Để thêm trường hàng, cột hay trang, chọn một ô bất kỳ trong pivot table. 2. Nhấn nút PivotTable Wizard trên thanh PivotTable. 3. Trong bước 3 của PivotTable Wizard, kéo các nút cần thiết vào vùng ROW, COLUMN, hay PAGE; sau đó nhấn Finish. TIP: Để bỏ một trường hàng, cột hay trang, kéo nút trường ra khỏi pivot table. e) Định dạng Pivot Table Bạn có thể sử dụng tính năng AutoFormat để định dạng Các bước 1. Chọn một ô trong bảng pivot table. Chọn Format, AutoFormat. 2. Trên Table Format , chọn định dạng mong muốn, nhấn OK. Chú ý: Để định dạng không bị mất khi ta thay đổi hay cập nhật lại bảng PivotTable cần đảm bảo mục Enable Selection trên PivotTable menu của thanh PivotTable đợc chọn. f) Cập nhật Pivot Table Giáo trình Microsoft Excel 2000 17/24 Lª ViÖt Anh – S−u tÇm vμ biªn so¹n Một pivot table không tự cập nhật khi bạn thay đổi dữ liệu nguồn. Các bước 1. Để cập nhật pivot table, chọn một ô bất kỳ trong pivot table. 2. Nhấn nút Refresh Data trên thanh PivotTable. 5.12. Bài tập 1 1. Cho bảng nhân sự A B C D E F G H I J K 5 MaNV Ho Ten GT NgaySinh QueQuan LapGiaDinh NgayVaoLam SoCMND AnhVan Luong 6 1002 Le Thi Hanh Nu 10-01-74 Ha Noi Roi 09-05-97 11669680 4 160 7 1004 Nguyen Van Hung Nu 03-09-73 Ha Tay Chua 06-02-98 11143579 4 201.8 8 1008 Dao Thi Lan Nu 13-10-56 Ha Noi Roi 15-02-98 11190274 1 125.2 9 1010 Chu Thi Mai Nu 25-02-68 Ha Noi Roi 02-02-98 11739644 4 240 10 1011 Le Anh Nam Nu 29-05-68 Ha Dong Roi 16-02-98 11318062 4 230 11 1012 Nguyen Thi Van Nu 18-08-73 Ha Noi Chua 10-02-98 11897544 3 190.4 12 2003 Hoang Thu Ha Nu 24-02-59 Ha Noi Chua 01-11-97 22758996 0 100 13 2004 Nguyen Thuy Dung Nu 05-02-73 Ha Noi Chua 16-02-98 11651990 4 189 14 3003 Ngo Thi Ngoc Nu 15-09-76 Ha Noi Chua 15-01-98 11796805 0 110.7 15 3004 Dang Hue Nu 15-11-73 Ha Tay Chua 16-02-98 11829098 0 117 Giáo trình Microsoft Excel 2000 18/24 Lª ViÖt Anh – S−u tÇm vμ biªn so¹n Thi 16 4002 Bui Thanh Thuy Nu 29-03-75 Ha Noi Chua 01-09-97 11949123 2 153.2 17 6003 Nguyen Thi Cuc Nu 11-04-60 Thai Binh Roi 02-12-97 11310613 0 100 18 6004 Dinh Van Hoa Nam 11-07-73 Ha Noi Chua 25-03-98 11620192 4 230 19 6005 Le Thi Hong Nu 19-09-75 Ha Noi Chua 16-02-98 11620496 4 201.8 20 6006 Do Hoang Kien Nam 01-09-65 Thai Binh Roi 15-02-98 11351091 2 137 21 7001 Nguyen Thi Thang Nam 22-07-62 Ha Noi Roi 15-12-97 10110138 4 155 22 7002 Hoang Thi Hanh Nu 13-01-62 Ha Noi Roi 01-01-98 10159713 1 150 23 7003 Le Xuan Anh Nu 11-01-75 Ha Noi Chua 02-03-98 11739836 3 160 1. Sắp xếp bảng trên theo a)Một trờng bất kỳ; b) theo GT, Que, LapGiaDinh và Lương 2. Hãy lọc lại y những ngời có sinh nhật trong tháng này y những ngời đã công tác trên 3 năm y những ngời đã công tác trên 2 năm và luơng trên 200 2. Cho bảng sinh viên STT Hä vμ Tªn Ph¸i N¨m Sinh Quª Mon 1 Mon 2 Mon 3 TB 1 TrÇn Ngäc §«ng Nam 03-11-76 Hμ Néi 9 7 8 8.0 Giáo trình Microsoft Excel 2000 19/24 Lª ViÖt Anh – S−u tÇm vμ biªn so¹n 2 TrÇn Vò Anh Nam 15-10-74 Thanh Ho¸ 9 4 8 7.0 3 TriÖu §¹i Ngäc Nam 17-07-75 Hμ Néi 7 2 4 4.3 4 Lª ThÞ Minh Hoa N÷ 30-09-69 Thanh Ho¸ 10 8 9 9.0 5 Ng« C«ng Tó Nam 08-04-76 Hng Yªn 6 9 5 6.7 6 Lý Ngäc H©n N÷ 29-03-70 Thanh Ho¸ 7 7 7 7.0 7 Vò Thanh T©m N÷ 10-10-75 Hμ B¾c 5 5 5 5.0 8 D¬ng TiÕn Minh Nam 30-07-76 Hμ Néi 4 6 6 5.3 9 NguyÔn C«ng Hoa N÷ 15-04-75 VÜnh Phó 7 5 5 5.7 10 TrÇn B¶o Ch©u Nam 07-06-75 Vinh 6.5 6 7 6.5 11 Ph¹m ThÞ NguyÖt Minh N÷ 01-04-74 Hμ Néi 7 7 7 7.0 12 Hoμng ThÞ Tó Anh N÷ 16-06-71 Vinh 5.5 6 6 5.8 13 TrÞnh TuyÕt Mai N÷ 03-03-70 Hng Yªn 8 8 8 8.0 14 Lª ThÞ Kim Chi N÷ 13-04-75 Hμ Néi 6 7 5 6.0 15 Hoμng Ngäc Ch©u Nam 07-04-76 VÜnh Phó 5 5 5 5.0 16 TrÇn TuÊn Vò Nam 06-12-76 Hng Yªn 4 3 8 5.0 17 Lª Quang Nam 23-09-76 Hμ B¾c 2 3 10 5.0 18 TrÞnh Hoμi V©n N÷ 24-08-71 Hμ Néi 6 7 8 7.0 19 D¬ng Hoμng Lan N÷ 12-06-77 HuÕ 9 8 10 9.0 20 NguyÔn HoÌ Nam 12-07-76 Nam Hμ 4 10 10 8.0 y Sắp xếp theo tên, giới tính và năm sinh, quê. y In danh sách sv học khá, giỏi (các môn đều đạt và điểm trung bình từ 8 trở lên) y Lọc tìm một sinh viên có tên là Vân y Lọc các sinh viên quê không thuộc Hà Nội, học khá y Lọc những sv có môn trượt Giáo trình Microsoft Excel 2000 20/24 Lª ViÖt Anh – S−u tÇm vμ biªn so¹n y Lọc những sv trượt 2 môn y Lọc những sinh viên có điểm hai môn bằng nhau y Đếm số học sinh nữ học khá 5.13. Bài tập 2 1. Dùng công thức và Pivot Table để tổng hợp doanh số bán hàng theo Vùng, tháng, nhân viên và sản phẩm Salesperson MonthRegion Product Sales Jones March Central Health $ 11,701.01 Jones March Central Life $ 139,858.93 Carlson March Central Life $ 160,227.81 Thomas March East Life $ 489,507.48 Jones March West Life $ 139,858.93 Green March West Auto $ 124,314.83 Bell March West Health $ 194,228.56 Thomas March East Health $ 492,831.17 Jones March Central Auto $ Giáo trình Microsoft Excel 2000 21/24 Lª ViÖt Anh – S−u tÇm vμ biªn so¹n 301,484.10 Davis April East Auto $ 347,271.66 Smith April Central Health $ 202,973.84 Green April West Health $ 115,017.67 Smith April Central Auto $ 21,300.87 Davis April East Health $ 58,638.34 Davis April East Life $ 139,688.81 Bell April West Auto $ 1,632.63 Smith April Central Life $ 160,679.58 Green April West Life $ 155,229.54 Thomas April East Auto $ 200,615.17 2. Dùng Pivot Table để tổng hợp bảng theo dõi thu chi ngoại tệ TT Loại HĐ Loại NT Số tiền Tỷ giá Quy ra VNĐ 1 Chi USD 100 ? ? Tỷ giá USD=13900 đ, DM=6900 đ, FR=5000đ Loại NTLoại HDSố tiền Quy ra VNĐ USD Thu ? ? Giáo trình Microsoft Excel 2000 22/24 Lª ViÖt Anh – S−u tÇm vμ biªn so¹n 2 Thu DM 1500 ? ? 3 Thu FR 3000 ? ? 4 Chi DM 1000 ? ? 5 Chi FR 4000 ? ? 6 Thu USD 500 ? ? 7 Chi DM 500 ? ? Chi ? ? FR Thu ? ? Chi ? ? DM Thu ? ? Chi ? ? 3. Kiểm tra sự hợp lý của lịch học, ví dụ không trùng phòng, trùng buổi,.. Thứ Buổi Lớp Giáo Viên Môn Phòng T6 Sáng 6C C. n 1Ha h Ly P T2 Sáng 5A C. n 1La Dia P T4 Tối 7C C. n oa 1La T n P T7 Tối 7D C. n 1La Su P T2 Chiều 5A C en 3. Li Toan P T7 Sáng 5A C. en 3Li Toan P T3 Sáng 5B T. on a 1Ph g Ho P T4 Sáng 5C T. on a 1Ph g Ho P T7 Sáng 6B T. on a 1Ph g Ho P T6 Chiều 5A T on 1. Ph g Hoa P TCN Chiều 5A T 2. Phong Dia P TCN Chiều 7A T h 3. TAnh Sin P Giáo trình Microsoft Excel 2000 23/24 Lª ViÖt Anh – S−u tÇm vμ biªn so¹n T5 Sáng 7A T. n h 2TA h Sin P T4 Sáng 7C T. n h 2TA h Sin P T4 Chiều 6A T n 3. TA h Su P T7 Chiều 6E T n h 3. TA h Sin P T3 Chiều 5A T n h 2. TA h Sin P T6 Sáng 7C T. ai 2Th Van P T7 Sáng 7D T. ai 2Th Van P T3 Sáng 6D T. ng 3Tu Ly P TCN Sáng 7B T 3. Tung Su P T5 Chiều 6B T ng 3. Tu Su P T2 Sáng 6C T. ng 3Tu Su P T6 Chiều 6D T ng h 3. Tu Sin P T2 Chiều 6A T a 1. Xu n Hoa P T6 Sáng 7A T. a a 1Xu n Ho P T4 Chiều 7B T a 1. Xu n Dia P T3 Chiều 7C T a 1. Xu n Hoa P T5 Chiều 7D T a 1. Xu n Hoa P T5 Sáng 5B T. a 1Xu n Dia P T2 Sáng 7B T. a 2Xu n Dia P T6 Chiều 7D T a 2. Xu n Dia P TCN Sáng 5C T 1.Tung Ly P Giáo trình Microsoft Excel 2000 24/24 Lª ViÖt Anh – S−u tÇm vμ biªn so¹n