Chương 7 : Cơ sở dữ liệu ( DATABASE)

Cơ sở dữ liệu là tập hợp thông tin, dữ liệu được tổ chức theo cấu trúc dòng và cột để có thể

liệt kê, truy tìm, xoá, rút trích những dòng dữ liệu thoả mãn một tiêu chuẩn nào đó nhanh chóng. Để thực hiện các thao tác này ta phải tạo ra các vùng Database, Criteria và Extract.

Vùng Database là vùng cơ sở dữ liệu gồm ít nhất hai dòng. Dòng đầu tiên chứa các tiêu đề cột, gọi là tên vùng tin ( Field Name ) của CSDL. Tên các vùng tin phải là dữ liệu kiểu chuỗi và không được trùng lặp. Các dòng còn lại lứa dữ liệu, mỗi dòng gọi là mẫu tin CSDL (Record).

Vùng Criteria là vùng tiêu chuẩn chứa điều kiện để tìm kiếm, xoá, rút trích, ... Vùng này gồm ít nhất hai dòng. Dòng đầu chứa tiêu đề, các dòng còn lại chứa điều kiện .

Vùng Extract là vùng trích dữ liệu chứa các mẫu tin của vùng Database thoả mãn điều kiện của vùng tiêu chuẩn. Vùng Extract cũng có dòng đầu tiên chứa các tiêu đềmuốn trích rút. Chỉ cần vùng này trong trường hợp thực hiện thao tác rút trích, còn thao tác tìm, kiếm, xoá, ... không cần đến vùng này.

Để giúp bạn dễ dàng quản lý dữ liệu trong danh sách, Excel cho phép bạn thêm, bớt và tìm kiếm các bản ghi bằng cách sử dụng Form trong lệnh đơn Data. Khi chọn Form, một hộp thoại xuất hiện trình bày các trường trong danh sách và một số nút nhấn lệnh quản lý danh sách. Tên của WorkSheet hiện hành sẽ xuất hiện trên thanh tiêu đề của hộp hội thoại. Theo ngầm định bản ghi đầu tiên trong danh sách sẽ xuất hiện, và bạn có thể cuộn đến các bản ghi khác bằng cách sử dụng thanh cuộn dọc. Excel sẽ thêm các bản ghi mới vào cuối danh sách. Để hiện thị bản ghi trống, bạn có thể cuộn đến cuối danh sách rồi nhắp lên nút nhấn New. Mặc dù bạn có thể thêm các bản ghi bằng cách gõ trực tiếp vào WorkSheet, nhưng sử dụng lệnh Form sẽ dễ dàng hơn, và trong một số trường hợp sẽ nhanh hơn. Muốn sử dụng hộp thoại Data Form, bạn sẽ tiến hành các bước sau:

 Đặt con trỏ ô nằm trong phạm vi vùng Database.

 Thực hiện lệnh: Data/Form. Một hộp thoại xuất hiện. Trong đó hiện nội dung CSDL theo từng mẫu tin. Có thể sử dụng các phím sau:

  : đến mẫu tin trước/sau mẫu tin hiện hành.

 Enter : đến mẫu tin sau mẫu tin hiện hành

 PageUP/PgDn : đến mẫu tin đầu/cuối

 Tab/Shift-Tab: di chuyển qua lại giữa các khung dữ liệu

Công dụng của những nút trong hộp thoại :

New: nhập mẫu tin mới

Delete: xoá mẫu tin hiện hành

Restore : khôi phục dữ liệu vừa nhập/xoá

Find Prev : đến mẫu tin trước mẫu tin hiện hành

Find Next: đến mẫu tin sau mẫu tin hiện hành

Criteria : nhập điều kiện truy tìm

Close : đóng cửa sổ Data Form

ở đây: vùng A1:E6 là vùng Database, vùng G2:G4 là vùng Criteria, vùng A9:C10 là vùng Extract.

a. Tiêu chuẩn số : ô điều kiện có kiểu số. Ví dụ: tiêu chuẩn Số con bằng 2

b. Tiêu chuẩn chuỗi: ô điều kiện có kiểu chuỗi. Ví dụ: tiêu chuẩn Chức vụ là "GD": trong ô điều kiện có thể chứa ký tự đại diện ? hoặc *. Ví dụ : tiêu chuẩn Tên bắt đầu bằng "H".

c. Tiêu chuẩn so sánh: ô điều kiện chứa toán tử so sánh kèm với giá trị so sánh. Ví dụ: tiêu chuẩn Số con lớn hơn 2.

d. Tiêu chuẩn công thức: ô điều kiện có kiểu công thức. Trong trường hợp này cần chú ý:

- Ô tiêu đề của vùng tiêu chuẩn phải là một tiêu đề khác với tất cả các tiêu đề của vùng Database.

- Trong ô điều kiện phải lấy địa chỉ của ô trong mẫu tin đầu tiên để so sánh. Ví dụ: tiêu chuẩn ký tự đầu tiên của tên khác "H". Công thức trong ô điều kiện là: =LEFT(C2,1)<>"H".

e. Liên kết tiêu chuẩn: Có thể tìm, kiếm, xoá hay rút trích các mẫu tin trong vùng Database bằng cách giao (AND) hay hội (OR) của nhiều điều kiện khác nhau.

- Ví dụ: Số con bằng 1 và Chức vụ là NV hay Số con lớn hơn 2 và Chức vụ là GD.

1. Tìm kiếm:thao tác này chỉ cần tạo trước vùng Database và vùng Criteria, không cần tạo vùng Extract.

- Di chuyển con trỏ ô vào vùng Database.

- Thực hiện lệnh : Data/Filter/Advanced Filter. Một hộp thoại xuất hiện

- Chọn: Filter the list, in place ở mục Action (hiển thị những mẫu tin thoả mãn điều kiện ngay trên vùng CSDL)

- Xác định địa chỉ vùng Database vào hộp : List Range

- Xác định địa chỉ vùng Criteria vào hộp: Criteria Range

- Gõ Enter hay chọn OK, các mẫu tin không thoả mãn điều kiện trong vùng Criteria sẽ bị dấu đi, chỉ trình bày các mẫu tin thoả mãn điều kiện.

Chú ý: sau đó muốn liệt kê lại toàn bộ nội dung các mẫu tin CSDL, dùng lệnh: Data/Filter/Show All.

2. Rút trích

- Trong thao tác này cần tạo trước vùng Database, vùng Criteria và vùng Extract.

- Di chuyển con trỏ ô vào vùng Database

- Thực hiện lệnh: Data/Filter/Advanced Filter. Hộp thoại như chức năng tìm kiếm hiện ra.

- Chọn Copy to Another Location ở mục Action.

- Xác định địa chỉ vùng Database vào hộp List Range

- Xác định địa chỉ vùng Criteria vào hộp: Criteria Range

- Xác định địa chỉ các tiêu đề của vùng Extract vào hộp: Copy to

- Gõ Enter hay chọn OK, các mẫu tin thoả mãn điều kiện trong vùng Criteria sẽ được chép vào vùng Extract

 Nếu chọn Unique Records Only: các mẫu tin giống nhau trong vùng trích chỉ tồn tại một mẫu tin

 Nếu không chọn Unique Records Only: trích đầy đủ các mẫu tin thoả mãn điều kiện của vùng tiêu chuẩn.

3. Xoá: thực hiện các bước như thao tác tìm kiếm

- Sau khi trong vùng CSDL liệt kê các mẫu tin thoả mãn điều kiện trong vùng Criteria, chọn các dòng này và thực hiện lệnh Edit/Delete Row

- Thực hiện lệnh Data/Filter/Show All để trình bày trở lại các mẫu tin bị dấu.

Hàm DSUM, DAVERAGE, DCOUNT, DCOUNTA, DMAX, DMIN.

+ Cộng dụng:

= DSUM: Tính tổng số của những mẫu tin thoả điều kiện trên cột khai thác trong CSDL.

=DAVERAGE: Tính trung bình cộng của những mẫu tin thoả điều kiện trên cột khai thác trong CSDL.

= DCOUNT: Đếm tổng số những ô chứa dữ liệu số của những mẫu tin thoả điều kiện trên cột khai thác trong CSDL.

= DCOUNTA: Đếm tổng số những ô chứa dữ liệu của những mẫu tin thoả điều kiện trên cột khai thác trong CSDL.

= DMAX: Tìm trị số cao nhất của những mẫu tin thoả điều kiện trên cột khai thác trong CSDL.

= DMIN: Tìm trị số nhỏ nhất của những mẫu tin thoả điều kiện trên cột khai thác trong CSDL.

+ Cú pháp:

= DSUM (Vùng CSDL, cột n, vùng tiêu chuẩn).

= DAVEGE(Vùng CSDL, cột n, vùng tiêu chuẩn)

= DCOUNT (Vùng CSDL, cột n, vùng tiêu chuẩn).

= DCOUNTA(Vùng CSDL, cột n, vùng tiêu chuẩn).

= DMAX (Vùng CSDL, cột n, vùng tiêu chuẩn).

= DMIN (Vùng CSDL, cột n, vùng tiêu chuẩn).

Lưu ý:

* Số cột khai thác phải là trị số lớn hơn hay bằng 1, và không được lớn hơn số cột có trong khối khai thác.

* Xem cách khai báo phạm vi và ghi điều kiện trong chương CSDL.

* Khi trong CSDL không có trị thoả điều kiện thì các hàm trên sẽ cho trị 0.

 Kiểm tra dữ liệu ngay khi nhập. Nếu có nhiều người dùng đồng thời sử dụng một danh sách, thì bạn có thể kiểm tra thông tin được học nhập vào xem có sai sót gì không. Để sử dụng tính năng kiểm tra dữ liệu, hãy làm như sau:

1. Chọn các ô trong cột muốn bảo vệ dữ liệu theo kiểu kiểm tra. Các ô đó có thể là ô đã chứa dữ liệu hay ô trống, là nơi bạn sẽ thêm mới các bản ghi.

2. Chọn Validation từ lệnh đơn Data, một hộp thoại xuất hiện và mở khối settings ra.

3. Trong danh sách đổ xuống Allow, chỉ định dạng bạn muốn cho dữ liệu sẽ nhập vào. Các tuỳ chọn của bạn có thể là Any value, Whole Number, Date ...

Khi chọn xong danh sách đổ xuống Allow, một số ô nhập khác sẽ xuất hiện để bạn chỉ định điều kiện, chẳng hạn như các số nhỏ và số lớn nhất để Excel có thể chấp nhận.

4. Mở khối Input Message ra rồi chọn Show Input Message When Cell is Selected để chấp nhận vào thông tin hướng dẫn sẽ xuất hiện khi ô được chọn.

5. Trong mục Input Message, nhập vào các thông tin hướng dẫn nhập (bước này là tuỳ chọn).

6. Mở khối Error Alert ra rồi chọn Show Error Alert After Invalid Data Is Entered để Excel sẽ thông báo lỗi khi người dùng nhập liệu sai.

7. Trong danh sách đổ xuống Style, chọn một trong những tuỳ chọn sau: Stop (để ngưng nhập liệu), Warning (để nhắc nhở người dùng nhưng vẫn cho nhập liệu) và Information để thông báo cho người dùng).

8. Trong mục Error Message, gõ vào nội dung nhập thông báo lỗi khi dữ liệu bị nhập sai.

9. Chọn OK để hoàn tất hộp hội thoại Data Validation. Nếu bạn đã chọn lựa trong bước 7, bạn có thể nhận được thông báo lỗi khi nhập dữ liệu sai.

Công dụng: Tạo mỗi quan hệ nối kết giữa các dữ liệu dạng số của những bảng tính lựa chọn vào

trong một bảng tính tổng hợp. Bạn có thể kết hợp số liệu của các bảng tính lựa chọn trong cùng 1 tập tin Workbook hoặc của những tập tin Workbook khác với nhau.

1. Đọc lại tập tin Workbook chứa những bảng tính cần kết hợp và hiển thị các cửa sổ bảng tính trên cùng một màn hình(để dễ dàng xác định phạm vi cần thích hợp).

2. Lựa chọn bảng tính và phạm vi dùng để chứa những dữ liệu kết hợp (chỉ cần chọn toạ độ ô đầu trong một bảng tính bất kỳ).

3. Chọn lệnh [Menu] Data > Consolidate.

Xuất hiện hộp đối thoại Consolidate.

4. Lựa chọn phạm vi chứa dữ liệu trong bảng tính cần kết hợp:

Chọn một trong hai cách thực hiện sau:

* Di chuyển vào bảng tính cần thực hiện và Drag trên phạm vi lựa chọn.

* Nhập toạ độ ô trên khung Reference (xem dạng thức trên phần lưu ý).

5. Chọn nút Add.

Xuất hiện phạm vi vừa khai báo trong khung All References.

6. Lập lại các bước 4,5 cho những bảng tính cần kết hợp khác.

7. Chọn những thông số trên khung Use Labels In:

* Left Column: Khi cần dùng cột dữ liệu đầu tiên ở bên trái những cột số liệu trong phạm vi lựa chọn làm cột tiêu đề.

* Top Row: Khi cần dùng dòng dữ liệu đầu tiên ở phía trên những dong số liệu trong phạm vi lựa chọn làm cột tiêu đề.

8. Thay đổi loại hàm cần tính toán trong việc kết hợp trên khung Function khi cần thiết (định dạng mặc nhiên của Excel là hàm Sum).

9. Chọn nút OK.

Excel sẽ gới kết xuất vào phạm vi lựa chọn trong bước 2.

Lưu ý:

* Phạm vi vùng chứa dữ liệu cần kết hợp có thể là:

- Những vùng khác nhau trong cùng một bảng tính.

- Giữa những bảng tính trong cùng tập tin Workbook.

- Giữa những tập tin Workbook đang hiển thị trên màn hình hoặc trong đĩa.

* Có thể kết hợp tối đa 255 khu vực.

* Vùng chứa dữ liệu cần kết hợp có thể chứa cột dòng tiêu đề hoặc không.

* Nhập phạm vi vùng chứa dữ liệu kết hợp trên khung Reference:

- Tên hoặc toạ độ của vùng: khi thực hiện việc kết hợp trong cùng một bảng tính.

- Tên bảng tính! Tên hoặc toạ độ của vùng: khi thực hiện việc kết hợp giữa các bảng tính trong cùng một tập tin Workbook.

- [Tên tập tin]tên bảng tính tên hoặc toạ độ của vùng khi thực hiện việc kết hợp giữa các tập tin trong thư mục.

- Tên đường dẫn [Tên tập tin] tên bảng tính hoặc toạ độ của vùng khi thực hiện việc kết hợp giữa các tập tin Workbook khác thư mục.

* Chỉ có dữ liệu loại giá trị mới được tổng hợp (chuỗi dạng thức không có giá trị thực hiện).

* Có 11 hàm tương thích với tính năng này là : Sum; Average; Count; Max; Min...

* Chọn nút Browse khi cần lựa chọn các tạp tin Workbook chứa bảng tính cần kết hợp khác trê đĩa.

* Bạn có thể sử dụng các kí tự đại diện (*,?) trong phần tên tập tin khi khai báo trên khung Reference.

* Chọn thông số Create Links to Source Data khi cần tạo thêm sự liên két với những dữ liệu nguồn trên các bảng tính.

CSDL là một tập hợp của những mẫu tin CSDL được trình bày theo cấu trúc cột/dòng (mỗi vùng tin tương ứng với một cột và mỗi mẫu tin tương ứng với 1 dòng của bảng tính). Tuy nhiên với cấu trúc này, bạn sẽ gặp khó khăn khi cần phân tích hay tổng hợp dữ liệu theo từng đề mục. Nay với tính năng Pivot Table của Excel, bạn có thể dễ dàng sắp xếp và trình bày những vùng tin trong CSDL, để có thể nhanh chóng phân tích hay thống kê dữ liệu theo nhiều hướng khác nhau.

* Mỗi Pivot Table gồm có bốn thành phần như sau:

- Page: hiển thị một mục tin trong vùng tin lựa chọn làm đề mục chính cho Pivot Table ( có thể có hoặc không).

- Row: hiển thị những mục tin trong vùng tin lựa chọn như những dòng tiêu đề (có thể có hoặc không).

- Column: hiển thị những mục tin trong vùng tin lựa chọn như những cột tiêu đề (có thể có hoặc không).

- Data : vùng dữ liệu cần tính toán hay thống kê tương ứng với cột/dòng tiêu đề (bắt buộc phải có).

Làm việc với những vùng tin trong Pivot Table

1. Chọn một trong hai cách thực hiện sau:

* Di chuyển ô hiện hành vào tên vùng cần thực hiện trong Pivot Table và chọn lệnh [Menu] Data > Pivot Table Field.

* Double Click trên tên vùng tin cần thực hiện

Excel xuất hiện hộp thoại Pivot Table Field

2. Lựa chọn những thông số cần thực hiện

* Nhập tên vùng tin cần thay đổi trên khung Name

* Thay đổi thành phần của vùng tin trên Pivot Table :

- Click vào các nút: Row, Column, Page trên khung Orientation.

* Lựa chọn việc thống kê, tính toán của vùng tin trên khung Subtotals

* Chọn nút Delete để xoá vùng tin trên Pivot Table

* Bật/tắt việc hiển thị các mục tin có trong vùng tin trên khung Hide Items

3. Chọn nút OK

Ví dụ về tạo Pivot Table

Month	Region	Category	Amount	1/	Di chuyển ô hiện hành vào ô bất kỳ
Jan	North	Hardware	18500		Trong phạm vi: A1:D28
Jan	North	Software	975	2/	Chọn lệnh [Menu] Data>Pivot Table
Jan	North	Other	530	3/	Chọn nút Next trên HĐT Pivot Table
Jan	South	Hardware	21554		Wizard Step 1 of 4
Jan	South	Software	1125	4/	Chọn nút Next trên HĐT Pivot Table
Jan	South	Other	850		Wizard Step 2 of 4
Jan	West	Hardware	29750	5/	Khai báo cấu trúc cho Pivot Table :
Jan	West	Software	1290		- Drag vùng tin Month vào khung Page
Jan	West	Other	1500		- Drag vùng tin Categary vào khung Row
Feb	North	Hardware	15350		- Drag vùng tin Region vào khung Column
Feb	North	Software	500		- Drag vùng tin Amount vào khung Data
Feb	North	Other	1100		- Chọn nút Next
Feb	South	Hardware	15690	6/	- Chọn nút Finish
Feb	South	Software	1800
Feb	South	Other	1400
Feb	West	Hardware	15400
Feb	West	Software	900
Feb	West	Other	150
Mar	North	Hardware	23500
Mar	North	Software	1190
Mar	North	Other	1356
Mar	South	Hardware	22500
Mar	South	Software	2500
Mar	South	Other	590
Mar	West	Hardware	18650
Mar	West	Software	1050
Mar	West	Other	275