Bài giảng Tin học ứng dụng - Chương 2: Cơ sở dữ liệu trong Excel

 Trường Đại học Vinh 
 Khoa Công nghệ Thông tin 
 ----------o0o------------ 
 Chương 2 
CƠ SỞ DỮ LIỆU TRONG EXCEL 
 1 
2.1 Khái niệm cơ sở dữ liệu 
2.2 Tạo lập cơ sở dữ liệu 
2.3 Sắp xếp dữ liệu 
2.4 Trích lọc dữ liệu 
2.5 Kiểm tra dữ liệu khi nhập 
2.6 Tạo lập báo cáo động với Pivot Table 
 2/
 01/10/2015 38 
 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). 
 3/
 01/10/2015 38 
 Các bước 
 ◦ 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. Không để 
 các hàng trắng sau tên trường. 
 ◦ 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. 
 ◦ 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. 
 Chú ý: 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. 
 4/
 01/10/2015 38 
 Bật thanh công cụ Form: Vào File/ Options, xuất hiện hộp 
 thoại: 
 5/
 01/10/2015 38 
 Chọn mục Quick Access Toolbar / All commands 
 Sau đó chọn mục Form.. và Add sang phía bên kia 
 Chọn Ok 
 Lúc này trên thanh Quick Access đã xuất hiện biểu tượng Form 
 Trong file Exel ta có sẵn mẫu các trường trong bảng như sau: 
 STT SBD Họ tên Khoa 
 Bôi đen dòng tiêu đề bảng 
 Click biểu tượng Form trên thanh công cụ để nhập dữ liệu 
 6/
 01/10/2015 38 
Hộp thoại nhập dữ liệu: 
+ Find Prev: tìm dữ liệu trước đó 
+ Find Next: Tìm dữ liệu tiếp theo 
+ Criteria: Điều kiện 
 7/
 01/10/2015 38 
 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 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. 
 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. 
 Chú ý: Để không ghi bản ghi vừa nhập, nhấn nút Restore. 
 8/
 01/10/2015 38 
 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. 
 Chú ý: Để 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á và chọn Delete. 
 9/
 01/10/2015 38 
 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 
 1. Chọn một ô bất kỳ trong CSDL; sau đó gọi 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. 
 10
 /3
 01/10/2015 8 
 Dùng để sắp xếp dữ liệu tăng dần hoặc giảm dần 
 Sắp xếp 1 cột dữ liệu 
 ◦ Chọn vùng dữ liệu cần sắp xếp 
 ◦ Chọn tab Home Sort & Filter 
 ◦ Sắp xếp tăng dần: Chọn “Sort A to Z” hoặc “Sort Smallest to 
 Largest” 
 ◦ Sắp xếp giảm dần: Chọn “Sort Z to A” hoặc “Sort Largest to 
 Smallest” 
 11
 /3
 01/10/2015 8 
 Sắp xếp theo 1 cột 
 12
 /3
 01/10/2015 8 
 Tùy chọn sẽ hiển thị, bạn có thể chọn bất kỳ cột nào để sắp 
 xếp: A to Z là theo thứ tự abc, còn Z to A là ngược lại và OK 
 13
 /3
 01/10/2015 8 
 Sắp xếp dựa theo nhiều cột 
 14
 /3
 01/10/2015 8 
 Lọc dữ liệu tự động (Filter): Dùng để lọc dữ liệu theo các tiêu chuẩn nào 
 đó 
 1. Chọn các cột dữ liệu cần lọc 
 2. Chọn tab Home Sort & Filter Filter 
 3. Chọn mũi tên ở các cột dữ liệu để lọc 
 4. Kích chuột vào đó, có danh sách thả xuống: 
 Dữ liệu số 
 15
 /3
 01/10/2015 8 
 Dữ liệu ký tự 
 Thứ tự ưu tiên lọc theo các cột từ trái phải 
 Hủy bỏ lọc đã thiết lập 
 ◦ Chọn tab Home Sort & Filter Filter Clear 
 16
 /3
 01/10/2015 8 
o Lọc dữ liệu nâng cao 
 Trích ra các mẩu tin theo các điều kiện chỉ định trong vùng điều 
 kiện được tạo trước 
 Thực hiện: 
  Bước 1: Tạo điều kiện gồm ít nhất 2 hàng 
  1: Tiêu đề cột điều kiện 
 Kết quả được đặt 
  2: Giá trị điều kiện ở vị trí khác 
  Bước 2: Vào Data -> advanced 
  Khi đó xuất hiện hộp thoại 
 Vị trí copy 
 tới 
 Chọn địa chỉ 
 vùng CSDL 
 Chọn địa chỉ 
 vùng điều kiện 17
 /3
 01/10/2015 8 
o Trích ra danh sách của người có địa chỉ ở Hà Nội 
 1 
 2 
 3 
 18
 /3
 01/10/2015 8 
- Mục đích: Kiểm tra dữ liệu nhập vào một ô thỏa mãn điều kiện theo 
 danh sách. 
1. Tạo bảng điều kiện và bảng cần nhập dữ liệu 
2. Đặt tên cho các trường ở bảng điều kiện 
3. Vào Data/ Data Validation, chọn Data Validation 
 19
 /3
 01/10/2015 8 
- Xuất hiện hộp thoại: 
- Allow: Chọn mục List 
- Source: Nhập điều kiện 
 20
 /3
 01/10/2015 8 
 Trong mục Settings: 
 ◦ Any value: nhập dữ liệu bất kỳ 
 ◦ Whole number: số nguyên 
 ◦ Decimal: Số thập phân 
 ◦ List: dạng danh sách 
 ◦ Date: Ngày tháng 
 Input Message: Thay đổi nội dung thông báo 
 Error Alert: Thay đổi nội dung báo lỗi nhập 
 21
 /3
 01/10/2015 8 
 Yêu cầu nhập thông tin vào bảng 1 dựa vào các điều kiện 
 bảng 2, sử dụng kiểm tra dữ liệu nhập vào: 
 22
 /3
 01/10/2015 8 
B1: Bôi đen ô F6 và F7, sau đó đặt tên cho 2 ô trên thanh địa chỉ, ví 
dụ: ngành 
B2: Bôi đen ô từ G6 đến G8, đặt tên cho ô trên thanh địa chỉ, ví dụ: 
TCNH 
B3: Bôi đen ô từ H6 đến H9, đặt tên cho ô trên thanh địa chỉ, ví dụ: 
QTKD 
B4: Bôi đen vùng cần điền từ B6 đến B9 (cột Ngành), vào Data/ 
Data Validation, xuất hiện hộp thoại: 
 23
 /3
 01/10/2015 8 
 Allow: Chọn List 
 Source: gõ dấu =, sau đó bôi đen vùng từ F6 đến F7 (hoặc 
 gõ vào chữ ngành) 
 24
 /3
 01/10/2015 8 
 B5: Bôi đen vùng cần điền từ C6 đến C9 (cột Chuyên 
 ngành), vào Data/ Data Validation, xuất hiện hộp thoại: 
 Trong Allow, chọn List 
 Gõ vào trong phần Source 
 =if(b6=“TCNH”,indirect($f$6),indirect($f$7)) 
 25
 /3
 01/10/2015 8 
 Kiểm tra dữ liệu nhập vào ô A3 thỏa mãn điều kiện là 1 số 
 nguyên trong khoảng từ 30 đến 60. 
 Kiểm tra dữ liệu nhập vào ô B3 thỏa mãn điều kiện là 1 số 
 nguyên ngoài khoảng khoảng từ 30 đến 60. 
 26
 /3
 01/10/2015 8 
Excel cho phép tạo báo cáo thống kê bằng công cụ Pivot Table 
Ví dụ: Cho bảng dữ liệu sau 
 27
 /3
 01/10/2015 8 
 Dữ liệu trên thể hiện danh sách chi tiết bán hàng của một công ty 
 bán về sản phẩm hàng rau củ quả, thông tin chi tiết của đơn hàng 
 được mô tả ngắn ngọn như sau: một dòng thể hiện thông tin bán một 
 sản phẩm, ví dụ Banana (chuối), sản phẩm banana thuộc một loại 
 hàng hóa nhất định đó là vegetables (đồ rau củ quả), có một số tiền 
 bán được nhất định (amount) cho đơn hàng này và bán vào ngày 
 (date) ở một đất nước (country). 
 28
 /3
 01/10/2015 8 
 Hãy bảo đảm dữ liệu của bạn có tiêu đề cột hoặc tiêu đề bảng và 
 không có hàng trống nào. 
 Hãy bấm vào ô bất kỳ trong phạm vi ô hoặc bảng. 
 Nhấn vào biểu tượng PivotTable ở menu Insert 
 29
 /3
 01/10/2015 8 
 Một cửa sổ mới hiện lên, bạn nhấn chọn OK để thiết lập tạo một 
 pivot table theo chế độ mặc định của excel. 
 30
 /3
 01/10/2015 8 
 Bảng PivotTable hiện ra. Đầu tiên để tính tổng số tiền bán được của 
 mỗi một sản phẩm, thì bạn làm theo các bước 
 ◦ Kéo cột Product vào vùng Row Labels 
 ◦ Kéo cột Amount vào vùng Values area 
 ◦ Kéo cột Country vào vùng Report 
 31
 /3
 01/10/2015 8 
 32
 /3
01/10/2015 8 
 Ngoài chức năng là tính tổng của một dòng theo một nhóm 
 nào đó, như kết quả ở trên là tính tổng số tiền theo mỗi một 
 sản phẩm, thì bạn có thể tùy chỉnh thành đếm số lượng hóa 
 đơn có bán sản phẩm đó như bên dưới. 
 Đếm số lượng hóa đơn có chứa theo mỗi sản phẩm 
 Nhấn chuột chọn vào giá trị đang được tính tổng Sum of 
 Amout chọn Value Field Setting 
 33
 /3
 01/10/2015 8 
 34
 /3
01/10/2015 8 
 35
 /3
01/10/2015 8 
 Sau đó nhấn OK, kết quả hiện ra bên dưới sản phẩm Apple 
 (táo) có 16 hóa đơn trên tổng số 28 hóa đơn 
 36
 /3
 01/10/2015 8