Tạo cơ sở dữ liệu quan hệ trong Excel 2013

Các công cụ về CSDL quan hệ của Excel 2013 sau đây sẽ giúp bạn dễ dàng liên kết đồ thị với các ô dữ liệu, thực hiện tìm kiếm và tạo các báo cáo động.

Cơ sở dữ liệu quan hệ là cơ sở dữ liệu có cấu trúc để nhận ra mối quan hệ giữa các thông tin được lưu trữ trong đó, rất cần thiết khi làm việc với số lượng lớn cho dữ liệu doanh nghiệp. Chúng cho phép bạn nhanh chóng tìm kiếm và lấy thông tin cụ thể, xem cùng một dữ liệu thiết lập theo nhiều cách, giảm những lỗi về dữ liệu và dự phòng. Hãy thử làm điều đó với một bảng tính Excel.

Để hiểu về cách Excel tạo cơ sở dữ liệu dễ dàng hơn, chúng ta sẽ tạo ra hai bảng: bảng tổng thể (master table) và bảng chi tiết (detail table). Bảng master là bảng chính, thường chứa những thông tin bản ghi (record) duy nhất, chẳng hạn như tên, địa chỉ, thành phố... Bảng này hiếm khi thay đổi ngoại trừ việc thêm hoặc xóa các thông tin cá nhân.

Đối với mỗi record trong bảng master, có thể có nhiều record trong các bảng detail (còn gọi là slave hay child table) liên kết trở lại bảng master. Điều này được gọi là một mối quan hệ một-nhiều. Dữ liệu trong các bảng, chẳng hạn như doanh số bán hàng hàng ngày, giá sản phẩm, số lượng, thường là thay đổi liên tục.

Để tránh lặp lại tất cả các thông tin tổng thể trong tất cả các bảng chi tiết, bạn cần tạo ra các mối quan hệ bằng cách sử dụng một trường duy nhất, chẳng hạn như Sales ID, sau đó để cho Excel làm phần còn lại. Ví dụ, doanh nghiệp của bạn có 10 nhân viên bán hàng đều có thông tin cá nhân duy nhất trong bảng master. Mỗi người bán hàng có 200 sản phẩm mà anh ấy/cô ấy bán được trong bảng detail. Vào cuối mỗi năm, bạn cần có một báo cáo cung cấp tổng doanh thu hàng năm của mỗi người, nhưng bạn cũng cần một báo cáo cung cấp tổng doanh thu của cả thành phố.

Trong khuôn khổ bài viết hướng dẫn này, chúng ta sẽ tạo ra một bảng master với thông tin của các nhân viên bán hàng và một bảng thứ hai cung cấp chi tiết tổng số bán hàng của họ theo quý trong năm hiện tại. Sales ID là trường quan hệ kết nối các bảng. Sau đó, chúng ta sẽ tạo ra một báo cáo (report hay còn gọi là bảng pivot) cho thấy những thành phố có doanh thu cao nhất. Để thực hiện, hãy mở Excel lên và chọn một bảng tính mới và hoàn toàn trống.

Tạo bảng master

1. Trước hết, nhấn đúp vào thẻ ở dưới bên dưới đáy màn hình và đổi tên Sheet1 thành.

2. Trong ô A1 nhập vào “Master”. Trong ô từ A3 đến F3 nhập vào lần lượt tiêu đề các cột là Sales ID, Sales Person, Address, City, State, Zip Code.

Tạo bảng Master.
 

3. Trong ô từ A4 đến A13 nhập vào số Sales ID của từng nhân viên, trong trường hợp này là từ 101 đến 110. Sales ID sẽ là giá trị dữ liệu duy nhất được dùng để tạo mối quan hệ giữa hai bảng.

4. Lần lượt nhập nội dung tên, địa chỉ, thành phố, bang và mã vùng vào các ô còn lại. Bạn có thể sao chép thông tin từ bảng mẫu trong bài viết này hay tự tạo dữ liệu của riêng mình. Do chúng ta cần tìm kiếm doanh số cao nhất theo từng thành phố nên hãy chắc chắn là bạn đã tạo ra nhiều thành phố khác nhau trong bảng này.

5. Sau khi đã nhập dữ liệu, hãy đánh dấu khối các ô từ A3 đến F13, gồm cả tiêu đề của các cột. Trong trình đơn Styles, hãy chọn “Format as Table”. Từ danh sách xổ xuống, hãy chọn màu và định dạng mà bạn thích. Hộp thoại “Format as Table sẽ xuất hiện với vùng dữ liệu hiển thị trong hộp trắng. Hãy đảm bảo là ô “My Table Has Headers” được đánh dấu chọn và sau đó nhấn OK.

Đánh dấu và đặt tên bảng Master.

6. Khi bảng vẫn đang còn được đánh dấu, bạn hãy chọn thẻ Design ngay bên dưới Table Tools (tùy chọn này sẽ chỉ hiển thị khi bảng đang được đánh dấu). Trong trình đơn Properties, hãy nhập Master vào hộp Table Name.
 

Tạo bảng detail

1. Ở bên dưới cạnh thẻ Master, hãy nhấn dấu “+” để chèn thêm một bảng tính mới. Nhấn đúp vào thẻ và đổi tên từ Sheet2 thành “Sales”.

Tạo bảng Detail (Sales).

2. Trong ô A1, nhập vào nội dung “Total Sales for 2013”. Trong ô từ A3 đến E3, nhập vào tiêu đề các cột Sales ID, Quarter1, Quarter2, Quarter3 và Quarter4.

3. Trong ô từ A4 đến A13, nhập vào Sales ID từ 101 đến 110.

4. Trong ô từ B4 đến E13, nhập 40 con số ngẫu nhiên để đại diện cho doanh số (tính bằng USD) hoặc sao chép dữ liệu từ bài viết này.

Đánh dấu và đặt tên bảng Detail.

5. Sau khi nhập xong dữ liệu, đánh dấu các ô từ A3 đến E13. Trên trình đơn Styles, chọn “Format as Table”. Trong danh sách xổ xuống, chọn màu và định dạng. Khi hộp thoại “Format as Table” xuất hiện cùng với vùng dữ liệu hiển thị trong hộp trắng, hãy đảm bảo ô “My Table Has Headers” đã được đánh dấu, sau đó nhấn OK.

6. Trong khi bảng đang còn được đánh dấu, chọn thẻ Design ngay dưới Table Tools (tùy chọn này sẽ chỉ hiển thị khi bảng đang được đánh dấu). Trong trình đơn Properties, hãy nhập Sales vào hộp Table Name.

Thiết lập quan hệ

Nguyên tắc đầu tiên mà bạn cần lưu ý khi tạo bản báo cáo pivot là cần phải định nghĩa quan hệ giữa các bảng trong phần báo cáo Pivot Table. Không được cố gắng tạo các kết nối quan hệ trước vì Excel sẽ không nhận ra chúng từ phần báo cáo Pivot Table. Cũng nên đảm bảo là đã chọn bảng detail (Sales) trong phần “analyze data”, nếu không nó sẽ không hoạt động.

1. Đến bảng Sales và đánh dấu các ô từ A1 đến E11. Nhấn vào thẻ Insert sau đó nhấn nút “Pivot Table”.

Trong hộp thoại “Create Pivot Table hãy đảm bảo là trường “Table Range” trong phần “Select a Table or Range” có giá trị là “Sales”. Nếu muốn nhập một bảng hay cơ sở dữ liệu từ chương trình khác như Word hay Access, hãy nhấn tùy chọn thứ hai “Use an External Data Source”.

Chèn và tạo báo cáo Pivot Table.
 

3. Trong mục thứ hai “Choose Where You Want the Pivot Report placed”, nhấn “New Worksheet” nếu bạn muốn tạo bảng trong một bảng tính riêng, hay nhấn “Existing Worksheet” nếu bạn muốn báo cáo nằm bên cạnh bảng Sales.

4. Trong mục cuối cùng “Choose Whether You Want to Analyzy Multiple Tables”, đánh dấu vào tùy chọn “Add this Data to the Data Model” rồi nhấn OK.

Trình đơn Pivot Table sẽ xuất hiện cùng với một hộp Help nằm bên trái với nội dung “To build a report, choose fields from the Pivot Table field list”.

1. Ngay bên dưới phần “PivotTable Fields”, nút Active đang được chọn bởi vì chỉ có một bảng hiện được kích hoạt. Hãy nhấn chọn vào các hộp Quarter1, Quarter2, Quarter3 và Quarter4 thì số liệu sẽ xuất hiện ở các ô lưới bên trái.

Chọn các trường từ bảng Master và Detail rồi tạo mối quan hệ.
 

2. Nhấn vào nút All, rồi sau đó nhấn vào liên kết bảng Master. Các trường từ bảng Master sẽ xuất hiện. Nhấn vào hộp đánh dấu bên cạnh tùy chọn “City”. Một hộp màu vàng sẽ xuất hiện với nội dung “Relationships between tables may be needed”.

3. Đây là nơi bạn sẽ định nghĩa mối quan hệ giữa hai bảng. Nhấn nút Create để làm xuất hiện hộp thoại “Create Relationship”. Trong mục “Table” của hộp thoại này, nhấn mũi tên xổ xuống rồi chọn “Sales” trong danh sách các bảng hiện có. Trong mục “Column (Foreign)”, nhấn chọn “Sales ID” trong danh sách các trường.

Báo cáo tổng doanh số tính theo từng thành phố. 
 

4. Lưu ý là “Sales ID” là trường duy nhất trong cả hai bảng. Trong mục “Related Table”, chọn “Master” và trong mục “Related Column (Primary)”, chọn “Sales ID” rồi sau đó nhấn nút OK.

Excel sẽ tạo kết nối, sau đó hiển thị báo cáo “Total Sales by City” trên màn hình để cho biết tổng doanh số tính theo từng thành phố. Bạn có thể thay đổi tiêu đề của báo cáo trong ô A1 để hoàn thành bước tạo báo cáo.

Sắp xếp, tạo bộ lọc và chọn dữ liệu

Bộ lọc trong Excel được sử dụng để chọn một số trường dữ liệu cụ thể nào đó. Đối với bài viết này, để lọc dữ liệu theo thành phố, hãy nhấn vào bất kỳ ô nào trong bảng sau đó nhấn vào trường City. Lưu ý đến mũi tên nhỏ bên phải trường này.

1. Nhấn vào mũi tên vừa nêu và hộp thoại “Sort-Filter Options” sẽ xuất hiện với các lựa chọn Filters và Sorting. Nếu muốn sắp xếp, bạn có thể nhấn tùy chọn “Sort A to Z” để xếp theo thứ tự tăng dần hoặc “Sort Z to A” để xếp theo thứ tự giảm dần. Ngoài ra, bạn cũng có thể có nhiều tùy chọn hơn bằng cách nhấn “Sort More Options”.

Sắp xếp và lọc kết quả báo cáo theo City.

Các tùy chọn Filter gồm có “Label Filters”, “Value Filters” và “Search” (hoặc chọn các bản ghi cụ thể trong trường tìm kiếm hiện tại). Nếu có một cơ sở dữ liệu khổng lồ với hàng trăm bản ghi, bạn có thể nhập vào tên một thành phố trong hộp Search, sau đó nhấn biểu tượng kính lúp để định vị bản ghi hay thành phố đó. Excel sẽ hiển thị thành phố trong danh sách bên dưới hộp Search.

2. Nếu cơ sở dữ liệu của bạn tương đối nhỏ, trước hết hãy bỏ đánh dấu nút “Select All” sau đó cuộn xuống thành phố mà bạn muốn chọn, nhấn vào ô đánh dấu trước nó rồi nhấn OK. Báo cáo sẽ chỉ hiển thị tổng doanh số cho từng quý của riêng thành phố đó.

Các tùy chọn bộ lọc “Label Filters” và “Value Filters” sẽ giúp bạn xác định tìm kiếm. Chẳng hạn, trong “Label Filters”, nếu chọn tất cả các thành phố bắt đầu bằng ký tự “S” (Begin With “S”), kết quả sẽ chỉ hiển thị các thành phố San Diego và San Francisco. Nếu chọn tất cả các thành phố có tên bắt đầu nhỏ hơn ký tự “S” (Less Than “S”), bạn sẽ nhận được kết quả là các thành phố Hollywood và Los Angeles. Các trường có giá trị bằng số cũng sẽ được lọc cùng với phương pháp trên, sử dụng các điều kiện Less Than, Greater Than, Equals, Between…

3. Bạn cũng có thể chọn một trường khác và nhanh chóng tạo một báo cáo mới. Chẳng hạn, nếu bạn muốn xem tổng doanh số hàng quý theo từng nhân viên, hãy bỏ chọn “City” và chuyển sang chọn “Sales Person”. Báo cáo sẽ được hiển thị ngay sau đó.

Báo cáo tổng doanh số theo từng nhân viên, sau đó lọc theo những nhân viên được chọn.
 

4. Tiếp theo, hãy nhấn vào mũi tên bên cạnh “Sales Person”, bỏ chọn “Select All” trong hộp thoại “Sort-Filter Options”. Nhấn chọn 4 người nhân viên trong danh sách, nhấn OK và báo cáo được lọc sẽ hiển thị.

Các tùy chọn báo cáo Pivot Table rất phong phú. Có nhiều cách để phân tích dữ liệu, tạo và quản lý các nhóm, trường, nhập và xuất dữ liệu cũng như thiết kế báo cáo theo nhiều định dạng và phong cách khác nhau, tạo đồ thị nhiều màu sắc và sau đó in chúng ra giấy.

Thêm sự tinh tế cho bảng báo cáo bằng đồ thị.
 

Các kiểu đồ thị

Để thêm chút “gia vị” cho bảng dữ liệu trước khi in ra, bạn có thể thêm đồ thị với nhiều màu sắc và phong cách khác nhau vào đó.
Để thêm đồ thị, hãy đánh dấu bảng, chọn Pivot Table Tools > Analyze > Tools > Pivot Chart, rồi chọn một kiểu đồ thị có sẵn trong bộ sưu tập và nhấn OK.

Để thêm màu và phong cách cho đồ thị, chọn Pivot Table Tools > Design > Pivot Table Styles và chọn một kiểu thiết kế bảng từ bộ sưu tập Styles của Excel. Nhấn “Banded Rows” ngay dưới nhóm “Pivot Table Style Options” để thay thế màu và bóng đổ để làm nổi bật các hàng dữ liệu.

Với tính năng quan hệ cơ sở dữ liệu mới này, quá trình thiết lập sẽ trở nên dễ dàng hơn trong Excel 2013, bạn có thể trích xuất dữ liệu cụ thể và tạo ra hàng chục báo cáo chỉ trong vài phút.

Theo pcworld.com.vn