CHƯƠNG III CHƯƠNG TRÌNH XỬ LÝ BẢNG TÍNH MICROSOFT EXCEL
BÀI 5. QUẢN LÝ VÀ TỔ CHỨC DỮ LIỆU
II. TẠO CÁC BÁO CÁO
MS Excel cho phép tạo báo cáo để tổng hợp dữ liệu phục vụ cho yêu cầu của người dùng. Trong phạm vi môn học, giới thiệu
− Thêm các tổng phụ (Subtotal) và tổng gộp (Grand Total) vào bảng dữ liệu.
− Hiện dữ liệu dưới dạng các mức từ tổng quát đến cụ thể (Outline).
− Tạo báo cáo hợp nhất từ các worksheet có cùng kiểu dữ liệu (Consolidate).
− Tạo dạng báo cáo theo ý của bạn (Pivot tables).
1. Tổng phụ (Subtotal) và tổng gộp (Grand Total) trong bảng dữ liệu Ngoài tổng chung, có thể thêm các tổng nhỏ cho từng nhóm bản ghi bằng cách dùng tính năng Subtotals. Tuy nhiên, để dùng Subtotals, bạn phải đảm bảo rằng bảng dữ liệu của bạn đã được sắp xếp theo cột mà bạn định thêm tổng phụ.
1.1. Thêm tổng phụ và tổng gộp cho bảng dữ liệu
− Sắp xếp dữ liệu (Sort) theo tiêu chí định thêm tổng phụ,
− Chọn menu Data/ Subtotals, cửa sổ Subtotals xuất hiện,
− Chọn trường/ cột cần gom nhóm dữ liệu để tính toán trong hộp At each change in,
− Chọn hàm thống kê sẽ sử dụng khi thêm tổng phụ trong hộp Use Function,
− Chọn trường/ cột sẽ thêm giá trị tổng phụ trong hộp Add Subtotals to,
− Đánh dấu vào 2 mục:
Replace Current Subtotals: Tổng phụ mới sẽ thay thế tổng cũ (nếu có)
Summary Below Data: Giá trị tổng được thêm vào ngay dưới từng nhóm giá trị.
− Nếu muốn với mỗi nhóm sẽ tự động ngắt sang trang khác khi in, chọn Page break between groups,
− Chọn OK để kết thúc. MS Excel sẽ thêm tổng phụ và tổng gộp theo yêu cầu trên.
1.2. Hủy bỏ tổng phụ và tổng gộp
Giáo trình TIN VĂN PHÒNG 164 Để hủy bỏ tất cả các tổng gộp và tổng phụ hiện có trong bảng dữ liệu
− Di chuyển đến bảng dữ liệu cần bỏ tổng gộp và tổng phụ,
− Chọn menu Data/ Subtotals, cửa sổ Subtotals xuất hiện,
− Chọn nút Remove All,
− Chọn OK để kết thúc.
1.3. Ví dụ
Hãy cho biết tổng số lượng hàng bán được của mỗi mặt hàng trong tháng 1.
− Nếu dữ liệu chưa được sắp xếp thì Sắp xếp bảng dữ liệu theo Tên hàng, sau đó theo Ngày mua
− Chọn menu Data/ Subtotals,
− Trong hộp At each change in, chọn Tên hàng,
− Trong hộp Use Function, chọn hàm Sum,
− Trong hộp Add Subtotals to, đánh dấu cột Số lượng.
− Chọn OK.
Kết quả là màn hình của các bạn sẽ giống như sau:
Chú ý: phải kiểm tra để đảm bảo rằng chỉ có những cột định tính tổng phụ mới được đánh dấu, các cột khác thì không đánh dấu.
1.3. Thêm nhiều mức tổng phụ khác vào bảng dữ liệu
Có thể thêm một vài tổng phụ vào bảng dữ liệu, cách làm cũng tương tự như trên.
Các nút mức Outline
Giáo trình TIN VĂN PHÒNG 165 Tuy nhiên, cũng không nên có quá nhiều tổng phụ trong bảng dữ liệu, điều đó khiến báo cáo trở nên phức tạp.
Ví dụ: Tính số nhân viên và tổng lương cơ bản phải trả theo Bằng cấp và Phòng ban.
Mức tổng phụ yêu cầu là trên Bằng cấp và Phòng ban, do đó cần sắp xếp dữ liệu theo 2 trường này. Hàm sử dụng khi thêm tổng phụ là hàm COUNT và SUM.
− Sắp xếp bảng dữ liệu theo Bằng cấp (Sort by), sau đó theo Phòng ban (Then by), Thêm tổng phụ theo Bằng cấp
− Chọn menu Data/ Subtotals,
− Trong hộp At each change in, chọn Bằng cấp,
− Trong hộp Use Function, chọn hàm Sum,
− Trong hộp Add Subtotals to, đánh dấu cột Lương cơ bản,
− Chọn OK.
Thêm tổng phụ theo Phòng ban
− Chọn menu Data/ Subtotals,
− Trong hộp At each change in, chọn Phòng ban,
− Trong hộp Use Function, chọn hàm Sum,
− Trong hộp Add Subtotals to, đánh dấu cột Lương cơ bản,
− Bỏ tích trong hộp Replace current subtotals để việc thêm tổng phụ mới không thay thế tổng phụ đã thêm ở trên,
− Chọn OK.
Thực hiện tương tự, nếu muốn thêm các tổng phụ khác. Với mỗi tổng phụ thêm vào, trong bảng tự động thêm 1 mức outline. Kết quả
Họ và tên Ngày BĐ Bằng cấp Chức vụ Phòng ban L−ơng cơ bản
Nguyễn Bình 5/1/2001 ĐH CB Ban lãnh đạo 360,000
Ban lnh đạo Total 360,000
Trần Văn Bảo 5/1/2000 ĐH CB Kế hoạch 360,000
Huỳnh Sơn 8/19/1998 ĐH CB Kế hoạch 360,000
Kế hoạch Total 720,000
Tô Thanh Tâm 6/25/1998 ĐH PP Kế toán - Tài vụ 360,000
Nguyễn Việt Nga 4/1/1989 ĐH CB Kế toán - Tài vụ 360,000
Cao Doan Thắng 2/14/1999 ĐH CB Kế toán - Tài vụ 360,000
Kế toán - Tài vụ Total 1,080,000
Vò Minh Chi 3/8/1997 §H PP Nh©n sù 360,000
Nh©n sù Total 360,000
§H Total 2,520,000
C§ Total 1,080,000
TC Total 540,000
ThS Total 1,800,000
TS Total 1,080,000
Grand Total 7,020,000
Grand Total 7,020,000
Giáo trình TIN VĂN PHÒNG 166
2. Hiển thị dữ liệu dưới dạng các mức từ tổng quát đến chi tiết (Outline) Khi bạn dùng câu lệnh Subtotal, MS Excel tự động phân mức (Outlined) cho bảng dữ liệu của bạn. Kích chuột vào các nút mức Outline 1, 2, 3,… bạn sẽ thấy bảng dữ liệu được hiện dưới dạng các mức từ tổng quát đến cụ thể. Khi bảng dữ liệu hiện ở mức chi tiết nhất, bạn có thể kích vào các nút có dấu trừ (hoặc cộng) ở bên lề trái để chỉ hiện mức tổng quát (hoặc hiện cả các chi tiết).
Ngoài ra bạn cũng có thể phân mức dữ liệu mà không cần dùng Subtotal. Khi đó bạn phải tính tổng phụ bằng tay, các tổng phụ này sẽ quyết định dữ liệu của bạn có bao nhiêu mức, bằng cách:
− Đánh dấu (bôi đen) bảng dữ liệu không kể dòng tiêu đề, chọn menu Data/ Group and Outline/Auto Outline.
− Để bỏ chế độ phân mức: Data/ Group and Outline/ Clear Outline.
3. Báo cáo hợp nhất (Consolidate)
Báo cáo hợp nhất là kiểu báo cáo được tổng hợp dữ liệu từ nhiều bảng dữ liệu khác nhau theo yêu cầu của người dùng. Báo cáo có số dòng tương ứng với số giá trị trong cột được chọn làm tiêu chí tổng hợp.
3.1. Yêu cầu
Khi tạo báo cáo hợp nhất, các bảng dữ liệu nguồn phải có cấu trúc như nhau. Nghĩa là, tên các cột và kiểu dữ liệu trong các cột tương ứng là giống nhau. Đồng thời các cột cũng được sắp xếp theo cùng một trật tự. Để thực hiện thành công việc kết hợp dữ liệu từ dãy các bảng tính này, các bảng tính đó nên được tạo ra từ cùng một văn bản mẫu (template).
Báo cáo hợp nhất có thể được tạo ra từ nhiều worksheet trong cùng một workbook, nhưng cũng có thể tổng hợp từ các worksheet trong các workbook khác nhau. Khi lập báo cáo cần phân tích xem yêu cầu tổng hợp dữ liệu theo tiêu chí nào.
3.2. Cách thực hiện
− Di chuyển con trỏ sang worksheet sẽ tạo báo cáo,
− Chọn menu Data/ Consolidate để mở cửa sổ Consolidate,
− Chọn hàm sẽ sử dụng khi tạo báo cáo trong hộp Function,
− Chọn các vùng dữ liệu nguồn để tạo báo cáo
o Nếu dữ liệu trong các worksheet cùng file, chọn nút ở cuối hộp References để thu nhỏ cửa sổ Consolidate lại, tiến hành chọn vùng dữ liệu đầu tiên. Vùng dữ liệu sẽ có cột cần tổng hợp dữ liệu ở đầu tiên bên trái, các cột số liệu ở bên phải của cột này. Chọn lại nút cuối hộp References để mở rộng cửa sổ Consolidate trở lại. Chọn nút Add để thêm vùng dữ liệu đó vào danh sách All References.
Tiếp tục lại các bước trên khi cần chọn thêm các vùng dữ liệu khác.
Giáo trình TIN VĂN PHÒNG 167 o Nếu dữ liệu thuộc các file khác nhau, trước hết mở các file chứa dữ liệu đó. Sau
đó, chuyển sang các file đó và thực hiện chọn vùng dữ liệu tương tự theo cách trên.
− Nếu muốn bỏ một vùng dữ liệu nào đó, chọn tham chiếu vùng tương ứng trong danh sách All References, rồi chọn nút Delete,
− Nếu muốn sử dụng giá trị trong cột đầu tiên bên trái làm tiêu đề dòng trong báo cáo, chọn nút Left column,
− Nếu muốn dùng tiêu đề cột trong vùng dữ liệu nguồn làm tiêu đề cột trong báo cáo, chọn nút Top row,
− Nếu muốn báo cáo có kết nối với dữ liệu nguồn, chọn nút Creat links to source data (việc thay đổi giá trị trong bảng dữ liệu nguồn, giá trị tự động cập nhật sang báo cáo)
− Chọn OK để kết thúc.
Nếu báo cáo có liên kết với dữ liệu nguồn, trong báo cáo xuất hiện các nút Outline để người dùng có thể xem được dữ liệu chi tiết của một số liệu nào đó trong báo cáo.
3.3. Ứng dụng
Giả sử có ba worksheet là Bt2_Thang1, Bt2_Thang2, Bt2_Thang3 có cấu trúc bảng giống nhau, các worksheet lần lượt lưu thông tin bán hàng của từng tháng.
Yêu cầu: Hãy tạo báo cáo cho biết tổng số lượng mỗi mặt hàng bán được trong quý 1.
Ta thấy tiêu chí để tổng hợp dữ liệu ở đây là theo mặt hàng, cụ thể là cột Tên hàng.
− Chuyển qua worksheet sẽ tạo báo cáo, rồi chọn ô bất kỳ,
− Chọn menu Data/ Consolidate,
− Trong hộp Function, chọn hàm SUM để tính tổng giá trị trong báo cáo,
− Chọn worksheet Bt2_Thang1, chọn vùng dữ liệu bắt đầu từ cột Tên hàng (C3:E21).
Sau đó chọn nút Add để đưa vùng dữ liệu trên vào danh sách All References,
− Làm tương tự với 2 worksheer Bt2_Thang2, Bt2_Thang3,
− Trong hộp Use Label In, đánh dấu cả Top row và Left column nếu bạn muốn sử dụng cả dòng trên cùng và cột bên trái làm tiêu đề của báo cáo,
− Nếu muốn dữ liệu trong báo cáo có liên kết với dữ liệu nguồn, chọn Create links to source data,
− Chọn OK để kết thúc,
− Với các cột dữ liệu không cần cho mục đích đặt ra, có thể xóa chúng khỏi báo cáo.
Báo cáo sẽ có dạng
Giáo trình TIN VĂN PHÒNG 168
4. Báo cáo tự thiết kế theo ý của người dùng (Pivot tables)
Khi tạo một báo cáo, bạn cần hiện dữ liệu theo nhiều chiều khác nhau. Để làm được việc đó, bạn có thể sử dụng tính năng Pivot table. Trong báo cáo Pivot, bạn có thể thêm/ bớt hoặc thay thế các trường một cách dễ hay phân tích dữ liệu dưới các góc độ khác nhau mà không làm ảnh hưởng đến dữ liệu gốc.
Để có thể sử dụng tính năng Pivot table một cách dễ dàng, mỗi một cột trong bảng dữ liệu phải có một tên cột riêng.
4.1. Cách thực hiện
− Chọn menu Data/ Pivottable and Pivotchart Report để mở cửa sổ Pivottable and Pivotchart wizard,
− Tại bước 1 (Step 1 of 3), chọn vị trí của dữ liệu cần phân tích (Where is the data that you want to analyze?)
o MS Office Excel list or database: bảng dữ liệu trong MS Excel o External data source: dữ liệu từ bên ngoài
o Multiple consolidation range: từ các báo cáo hợp nhất
o Another Pivottable report or Pivotchart report: từ báo cáo Pivot khác
− Chọn kiểu báo cáo: Pivot table hay Pivotchart report, sau đó chọn nút Next,
− Tại bước 2 (Step 2 of 3): chọn vùng dữ liệu nguồn bằng cách chọn worksheet hoặc workbook chứa dữ liệu sau đó đánh dấu vùng địa chỉ của dữ liệu, chọn nút Next,
− Tại bước 3 (Step 3 of 3): chọn vị trí đặt báo cáo
o New worksheet: báo cáo được đặt trong một worksheet mới o Existing worksheet: báo cáo đặt trong wortsheet hiện hành
− Chọn nút Layout để thiết kế dạng báo cáo,
− Trong cửa sổ Layout, mỗi một trường của bảng dữ liệu nguồn được đại diện bởi một nút trong phần bên phải cửa sổ, kéo thả các nút vào phần tương ứng để tạo báo cáo
o Page (Tiêu đề trang): với mỗi một giá trị trong trường đặt tại phần này, báo cáo được đặt sang một trang mới.
o Column (Tiêu đề cột): mỗi một giá trị trong trường đặt tại phần này sẽ là một cột trong báo cáo. Có thể có nhiều trường xuất hiện trong phần tiêu đề cột.
Giáo trình TIN VĂN PHÒNG 169 o Row (Tiêu đề hàng): tương tự như tiêu đề cột.
o Data (Dữ liệu cần phân tích): số liệu cần tổng hợp theo các tiêu chí tương ứng với mỗi bộ giá trị hàng-cột. Phần này thường sử dụng các cột có kiểu dữ liệu số.
− Nếu muốn thay đổi hàm tính toán thống kê cho một cột dữ liệu của phần Data, kích đúp chuột vào tên cột tương ứng. Trong cửa sổ Pivottable field, nếu cần thay đổi tên cột số liệu thì gõ tên mới vào hộp Name. Nếu muốn dùng hàm tính toán khác, chọn tên hàm tương ứng trong phần Summarize by. Chọn OK nếu đã thay đổi xong,
− Khi kết thúc việc thiết kế dạng báo cáo, chọn OK.
− Chọn nút Finish.
Nếu chọn Pivottabe thì bạn có thể tạo được Pivotchart dựa trên Pivottable. Ngược lại, bạn không thể chỉ tạo Pivotchart riêng rẽ mà không có Pivottable.
Một trường dữ liệu có thể được phân tích dưới nhiều góc độ khác nhau (chọn nhiều lần một trường nào đó vào phần Data).
4.2. Sửa đổi Pivottable
Sau khi tạo Pivottable, báo cáo xuất hiện tại vị trí người dùng đặt ra. Đồng thời trong MS Excel xuất hiện thanh công cụ PivotTable. Với thanh công cụ này, có thể thực hiện sửa đổi lại khuôn dạng báo cáo, bằng cách
− Di chuyển con trỏ vào vùng báo cáo Pivottable cần sửa,
− Chọn nút Pivottable trên thanh công cụ PivotTable, chọn PivotTable Wizard từ menu,
− Nếu muốn bỏ một trường nào đó ra khỏi báo cáo, chỉ cần kéo thả nút đại diện trường đó ra khỏi các phần thuộc phạm vi báo cáo,
− Thực hiện thao tác thêm các trường vào báo cáo (tương tự các bước trong phần xây dựng Pivottable ở trên).
Hoặc có thể kéo thả trực tiếp trên báo cáo, tuy nhiên cách này đòi hỏi người dùng thành thạo các công cụ của Pivot.
Ngoài ra có thể thực hiện một số thao tác khác có hỗ trợ cho Pivottable bằng cách sử dụng các nút sắn có trên thanh công cụ Pivottable. Để hiện thanh công cụ này, chọn View/ Toolbars/ Pivot Table.
− Pivot Table Wizard: thêm bớt trường, thay đổi phần Layout.
− Field setting: thay đổi thuộc tính của trường, thay đổi phép toán của trường thuộc vùng Data.
− Display field: hiện ra các nút thể hiện các trường trong bảng, bạn có thể thêm, bớt, thay đổi vị trí bằng cách kéo, thả mà không cần vào phần Layout.
− Refresh data: làm tươi lại dữ liệu (thường dùng nếu dữ liệu nguồn có thay đổi).
− Chart Wizard: tạo đồ thị từ bảng Pivot Table.
4.3. Ứng dụng
Giáo trình TIN VĂN PHÒNG 170 Xây dựng báo cáo cáo cho biết Ngày công lớn nhất và Tổng lĩnh của mỗi Chức vụ trong từng Phòng ban
Với yêu cầu trên cũng có thể dùng Subtotal để thực hiện. Tuy nhiên kiểu báo cáo này không cho phép người dùng phân tích dữ liệu theo chiều dọc cũng như chiều ngang. Do đó, giải pháp đưa ra là báo cáo xây dựng bằng Pivottable.
− Di chuyển đến một ô bất kỳ ngoài bảng dữ liệu,
− Chọn menu Data/ Pivottable and Pivotchart Report,
− Tại bước 1 (Step 1 of 3), chọn MS Office Excel list or database và chọn kiểu báo cáo là Pivot table, chọn Next,
− Tại bước 2 (Step 2 of 3): chọn vùng dữ liệu (A2:A22), chọn Next,
− Tại bước 3 (Step 3 of 3): chọn vị trí đặt báo cáo là Existing worksheet,
− Chọn nút Layout,
− Trong cửa sổ Layout, kéo thả nút Phòng ban vào phần Row, nút Chức vụ vào phần Column, nút Ngày công và Tổng lĩnh vào phần Data. Với nút Sum of Ngày công, kích đúp chuột vào nút này và thay đổi hàm sử dụng thành MAX, chọn OK,
− Kết thúc việc thiết kế dạng báo cáo, chọn OK.
− Chọn nút Finish.
Báo cáo có dạng
Trong báo cáo, các ô trắng có nghĩa là không có dữ liệu nào thỏa mãn trường hợp (thỏa mãn giá trị tương ứng ở hàng và cột đó).
4.4. Xem dữ liệu trên Pivottable
Trong báo cáo, tại đầu mỗi phần xuất hiện các nút đại diện cho trường dữ liệu thuộc phần đó. Bên cạnh tên trường có nút cho phép người dùng chọn hiển thị các giá trị tương ứng với trường đó trong báo cáo, bằng cách tích vào tên các giá trị cần hiển thị trong hộp danh sách sau đó chọn nút OK.
Giáo trình TIN VĂN PHÒNG 171
5. Báo cáo dạng biểu đồ (Chart)
Ngoài việc tạo các báo cáo số liệu có thể minh hoạ báo cáo bằng các biểu đồ. Thao tác với biểu đồ trong MS Excel tương tự như trong MS Word. Cũng giống như các loại báo cáo khác, bảng dữ liệu xây dựng biểu đồ cũng cần đạt yêu cầu về cơ sở dữ liệu.
Biểu đồ có thể được xây dựng từ bảng dữ liệu chi tiết hoặc dữ liệu đã được tổng hợp dưới dạng hợp nhất thậm chí trên báo cáo tổng phụ/ tổng hợp.
5.1. Cách thực hiện
Để chèn 1 biểu đồ vào workbook, cách làm như sau:
− Di chuyển vào bảng dữ liệu để tạo biểu đồ,
− Chọn menu Insert/ Chart (hoặc chọn biểu tượng biểu đồ trên thanh Standard) để mở cửa sổ Chart Wizard,
− Tại bước 1 (Step 1 of 4), chọn kiểu biểu đồ (Chart Type) trong danh sách tương ứng o Tab Standard Types: danh sách các kiểu biểu đồ chuẩn
o Tab Custom Types: danh sách các kiểu biểu đồ khác
− Chọn nút Next,
− Tại bước 2 (Step 2 of 4), chọn nguồn dữ liệu (Chart data source) tại hộp Data range. Có thể chọn nhiều vùng dữ liệu bằng cách bấm phím CTRL trong lúc đánh dấu các vùng dữ liệu,
− Chọn dữ liệu của biểu đồ tại phần Series in là lấy giá trị theo hàng (Rows) hay theo cột (Columns). Nếu muốn thay đổi hay xây dựng dãy số liệu chọn tab Series, tại hộp dưới tiêu đề Series có hiển thị tất cả các dãy số liệu có trong biểu đồ, dùng nút Add/Remove để thêm/bớt dãy số liệu,
− Chọn Next,
− Tại bước 3 (Step 3 of 4), xây dựng các thành phần của biểu đồ (Chart option) o Tab Titles: Nhập tiêu đề của biểu đồ, tiêu đề loại (Category (X) axis), tiêu đề
cho giá trị (Value (Y) axis),
o Tab Axes: Chỉ định có hiển thị đường trục X, Y hay không, o Tab Gridlines: Thiết lập đường chỉ dẫn nền cho từng trục giá trị,
o Tab Legend: Chỉ định có hiển thị phần Chú giải hay không, nếu có thì hiển thị tại vị trí nào,
o Tab Data Labels: Chỉ định cách hiển thị nhãn số liệu, nếu không muốn hiển thị chọn None, nếu hiển thị giá trị chọn Show Values,...
o Tab Data Table: Chỉ định có hiển thị bảng số liệu không, nếu có chọn hộp Show Data table,
− Chọn Next,
− Tại bước 4 (Step 4 of 4), xác định vị trí đặt biểu đồ (Chart Location), o As New sheet: đặt biểu đồ sang một worksheet mới