BÀI 4: CÔNG CỤ THỐNG KÊ MS EXCEL

Nhằm giúp cho học viên cũng như các bạn đi làm thường xuyên tiếp xúc công việc báo cáo, phân tích, thông kê dữ liệu. Trung tâm Ngoại ngữ – Tin học Phương Nam cung cấp thông tin căn bản nhất về nội dung này để các bạn có kiến thức nền tốt ứng dụng vào công việc thực tế của mình.

I. PIVOTTABLE, PIVOTCHART, SUBTOTAL

1. SUBTOTAL

B1: Sắp xếp dữ liệu theo trật tự cần tổng hợp theo nhóm

B2: Vào DATA / chọn SUBTOTAL / xuất hiện hộp thoại

B3: Thiết lập các tham số hộp thoại 

+AT EACH CHANGE IN: Chọn cột cần tổng hợp theo nhóm

+USE FUNCTION: Chọn hàm tính toán

+ADD SUBTOTAL TO: Chọn cột cần tính / Nhấn OK

Hình ảnh kết quả sau tổng hợp:

2. PIVOTTABLE

PivotTable là một công cụ tuyệt vời MS Excel cung cấp dùng để phân tích dữ liệu theo nhiều góc độ, nhiều yêu cầu khác nhau từ một danh sách hay một bảng. Từ khối dữ liệu khổng lồ ban đầu thì PivotTable có thể giúp các bạn tổng hợp dữ liệu theo nhóm, thu gọn dữ liệu theo yêu cầu và mục đích sử dụng của bạn một cách nhanh chóng.

Để sử dụng tốt Pivot Table cần nắm vững cấu trúc, thuộc tính của Pivot Table. Chúng ta phân tích ví dụ sau để hiểu hơn về Pivot Table

Ví dụ: Cho bảng dự liệu sau:

Bảng dữ liệu 1

Yêu cầu: Tạo bảng Thống Kê có dạng sau:

Bảng Thống Kê 1

Phân Tích Bảng Thông Kê 1:

– Bảng Thống Kê có 3 vùng màu đỏ, vàng , xanh

+ Màu đỏ: Thể hiện cột Mã SP từ bảng dữ liệu 1, mọi 1 Mã SP( ví dụ: CH-01 ) hiển thị như vậy gọi là dòng ( ROWS )

+ Màu xanh: Thể hiện cột Xuất Xứ từ bảng dữ liệu 1, mọi 1 Xuất Xứ( ví dụ: Anh ) hiển thị như vậy gọi là Cột ( COLUMNS )

+ Màu vàng: Thể hiện cột Tổng Chi Phí từ bảng dữ liệu 1, mọi 1 ô trong vùng màu vàng Bảng Thống Kê 1 là giao của dòng Mã SP(ROWS) và cột Xuất Xứ(COLUMNS) thể hiện tổng chi phí theo Mã SP và Xuất Xứ đây được gọi là Chỉ số tổng hợp(VALUES)

Kết Luận:

Khi sử dụng Pivot Table để thống kê về cơ bản ít nhất 2 trong 3 thuộc tính sau:

+ ROWS: Tổng hợp thông tin theo dòng

+ COLUMNS: Tổng hợp thông tin theo cột

+ VALUES: Chỉ số tổng hợp, phải chọn hàm tính toán(Sum, Count)

Ngoài ra còn thuộc tính Pivot Table: FILTERS dùng thiết lập Thuộc tính lọc để xem bảng thống kê theo một hoặc vài loại mẫu tin

Hình ảnh Layout Pivot Table

A. Cách tạo Pivot Table ( Tổng hợp dữ liệu từ 1 bảng / sheet)

B1: Đặt chuột vào ô bất kỳ của vùng dữ liệu cần tổng hợp

B2: INSERT /  PIVOT TABLE / XUẤT HIỆN HỘP THOẠI SAU

+ Tại SELECT A TABLE OR RANGE: Chọn bảng / vùng chứa dữ liệu cần tổng hợp

+ Tại USE AN EXTERNAL DATA SOURCE: Chọn nguồn từ bên ngoài khác tập tin hiện tại

+ Tại NEW WORKSHEET:  Tạo 1 sheet mới thể hiện Pivot Table

+  Tại EXISTING WORDKSHEET: Pivot Table được tạo tại vị trí con trỏ trên sheet hiện hành

– Ở đây thống nhất sử dụng mặc định / OK / mở ra sheet mới chứa Vỏ Pivot Table giống như “Hình ảnh Layout Pivot Table”

B3: Lần lượt kéo thả các cột thuộc tính vào Vỏ Pivot Table như : ROWS, COLUMNS, VALUES, FILTERS phù hợp

B4: Định dạng hiển thị cho Pivot Table: đặt chuột vào vùng Pivot Table /  Trên thanh Menu xuất hiện PIVOTTABLE TOOLS / Chọn Tab ANALYZE hoặc DESIGN để thiết lập định dạng hiển thị như mong muốn.

B. Cách tạo Pivot Table ( Tổng hợp dữ liệu từ nhiều bảng )

Khi cùng lúc có nhiều bảng dữ liệu hoặc nhiều sheet khác nhau muốn tạo một PivotTable, chúng ta giải quyết vấn đề đó thế nào? Nếu sử dụng MS Excel 2013, thì bạn có một phương pháp được tổ chức hợp lý để thực hiện việc này, đó được gọi là Mô hình Quan Hệ Dữ liệu và nó sử dụng dữ liệu quan hệ theo Mô hình cơ sở dữ liệu làm việc.

Ví dụ:

Cho 3 bảng dữ liệu gồm: KhachHang, HoaDon, ChiTietHD, trong đó bảng KhachHang quan hệ với bảng HoaDon qua MaKH, bảng HoaDon quan hệ với bảng ChiTietHD qua SoHD

Yêu Cầu: Tạo bảng thống kê theo mẫu sau(Thống kê các đơn hàng của từng khách hàng và tình trạng của từng đơn hàng.

Để làm được Ví dụ bằng Pivot Table thực hiện từng bước sau:

B1: Tạo Bảng động cho 3 bảng KhachHang, HoaDon, ChiTietHD

+ Đặt Trỏ vào vùng cần tạo bảng động

+ Vào INSERT / TABLE / HỘP THOẠI

 

+ Tại WHERE IS THE DATA FOR YOUR TABLE: Chọn vùng cần tạo bảng động

+ Tại MY TABLE HAS HEADERS: Chọn nếu muốn sử dụng tên cột có sẵn, nếu không chọn Table tự tạo tên cột

=> OK

+ Thực hiện lại để tạo đủ các bảng mong muốn

B2: Đặt con trỏ vào bảng ChiTietHD

B3: Vào INSERT / PIVOT TABLE / xuất hiện hộp thoại / Chọn Add this data to the data Mode

 

OK / xuất hiện Vỏ PIVOT TABLE( giống như “Hình ảnh Layout Pivot Table” )

B4: Tạo quan hệ cho các bảng

+ Đặt con trỏ vào bên trong vỏ Pivot Table

+ Trên thanh Menu xuất hiện PIVOTTABLE TOOLS / Chọn Tab ANALYZE / Chọn Relationship / xuất hiện hộp thoại 

+ Chọn New / Xuất hiện hộp thoại (*)

=> Tại Table: Chọn bảng KhachHang

=> Tại Related table: Chọn bảng HoaDon

=> Tại Column, Related Column : chọn MaKH 

=> OK

+ Thực hiện lại từ bước (*) để tạo quan hệ giữa bảng HoaDon và ChiTietHD

B5: Sau khi tạo quan hệ giữa các bảng chúng ta kéo thả các cột vào thuộc tính PIVOTTABLE giống như ở trên

Như vậy để tạo Pivot Table cho nhiều bảng cùng lúc

B1: Tạo bảng động cho các bảng cần tổng hợp

B2: Chèn Pivot Table

B3: Tạo quan hệ giữa các bảng

B4: Kéo thả các cột vào Vỏ Pivot Table

Xem Thêm Video

3. PIVOT chart

Tương tự như Pivot Table chỉ thêm phần đồ thị tự hình thành sau khi thực hiện Pivot

II.  CONSOLIDATE, DATA TABLE

1. consolidate

Consolidate là một công cụ vừa được sử dụng để tổng hợp, thống kê dữ liệu đồng thời dùng để ghép dữ liệu từ nhiều bảng khác nhau một cách nhanh chóng.

a. Consolidate Tổng hợp, thống kê

B1: Đặt con trỏ tại vị trí muốn hiển thị kết quả thống kê ( Vị trí này ở cùng sheet chứa thông tin cần thống kê hoặc nằm sheet khác )

Chú ý: Nếu muốn tổng hợp thống kê dữ liệu của cột nào đó cần chỉ định thì chúng ta nên tạo tiêu đề cột cho vùng thống kê trước rồi chọn vùng có chứa tiêu đề vừa tạo ( Xem trong video ví dụ phía dưới )

B2: Vào DATA (Trên thanh Ribbon hay còn gọi Menu) / CONSOLIDATE / xuất hiện hộp thoại

Tại Function: Chọn hàm tính toán

Tại Reference: Chọn vùng dữ liệu chứa thông tin cần thống kê(dòng đầu tiên của vùng này dùng để tạo dòng tiêu đề bảng thống kê, cột đầu tiên dùng để tại cột đầu tiên bảng thống kê)

Tại Use labels in: chọn Top Row(Tạo dòng tiêu đề bảng thống kê), Left column(Tạo cột đầu tiên bảng thống kê)

Ví dụ: Ở bảng thống kê sau dòng màu vàng gọi là dòng tiêu đề bảng thống kê, cột màu xanh gọi là cột đầu tiên bảng thống kê

=> Nhấn nút ADD / OK

B. Consolidate Tổng hợp, thống kê trên nhiều vùng khác nhau

Thực hiện tương tự như trên,

Tại bước B2: thực hiện lại cho đến khi chọn đủ các vùng cần thống kê mong muốn

Chú ý: Các vùng khác nhau này phải có cấu trúc tương tự mới CONSOLIDATE được

Ví dụ 1: Cho bảng dữ liệu sau

Yêu cầu: Tạo bảng thống Kê theo Mẫu sau

Hướng dẫn:

Ví dụ 2: Cho 3 bảng dữ liệu sau

Doanh thu bán hàng năm 2011

Doanh thu bán hàng năm 2012

Doanh thu bán hàng năm 2013

Yêu cầu: Tạo bảng thống Kê tổng hợp doanh thu cả 3 năm theo từng mặt hàng và tháng

Thống Kê theo Mẫu

Hướng dẫn:

2. data table

Data Table là công cụ cực kỳ hữu ích được MS Excel cung cấp để phân tích độ nhạy, đo lường ảnh hưởng của biến phụ thuộc khi một hay nhiều biến độc lập thay đổi.

Khi sử dụng Data Table chúng ta cần chú ý 3 vấn đề sau:

+ Ô truy xuất biến theo dòng

+ Ô truy xuất biến theo cột

+ Ô tham chiếu kết quả

Data Table có 2 dạng:

+ Data Table 1 chiều ( hoặc theo dòng / hoặc theo cột )

+ Data Table 2 chiều ( cả dòng và cột )

a. Data Table 1 chiều

B1: Chọn 1 ô nào đó làm ô Mẫu

B2: Tại ô tham chiếu ta viết công thức tính toán, công thức này tham chiếu đến ô mẫu.

B3: Chọn vùng Data Table

B4: Vào Data / What if Data Analysis / Data Table / Xuất hiện hộp thoại

B5: Tại Row Input Cell / Column Input Cell: Chọn ô mẫu ở bước B1

Ví dụ:

Giả sử doanh thu của doanh nghiệp là biến thay đổi từ 5->10, biến chi phí cố định là 4, tính lợi nhuận tương ứng khi biến doanh thu thay đổi

Lập bảng Data Table như sau:

Data Table Thực hiện như sau: lần lượt lấy giá trị từng ô của dòng màu vàng trong Bảng Data Table điền vào ô mẫu, trả về kết qua tương ứng bên dưới theo công thức thực hiện ở ô tham chiếu kết quả( ở đây ô tham chiếu kết quả chính là ô lợi nhuận B2-B3 )

Video hướng dẫn

B. Data Table 2 chiều

B1: Chọn 2 ô nào đó làm ô Mẫu

B2: Tại ô tham chiếu ta viết công thức tính toán, công thức này tham chiếu đến ô mẫu.

B3: Chọn vùng Data Table

B4: Vào Data / What if Data Analysis / Data Table / Xuất hiện hộp thoại

B5: Tại Row Input Cell: chọn ô mẫu đại diện biến biến đổi theo dòng

Column Input Cell: Chọn ô mẫu địa diện biến biến đổi theo cột

Ví dụ:

Giả sử doanh thu của doanh nghiệp là biến thay đổi từ 5->10, biến chi phí biến đổi từ 2 là 4, tính lợi nhuận tương ứng khi biến doanh thu và biến chi phí thay đổi.

Lập bảng Data Table như sau:

Data Table Thực hiện như sau: lần lượt lấy giá trị từng ô của dòng màu vàng trong Bảng Data Table điền vào ô mẫu màu vàng,lần lượt lấy giá trị từng ô của cột màu xanh trong Bảng Data Table điền vào ô mẫu màu xanh trả về kết quả tương ứng bên trong từng ô giao nhau của dòng và cột của Bảng Data Table theo công thức thực hiện ở ô tham chiếu kết quả( ở đây ô tham chiếu kết quả chính là ô lợi nhuận B2-B3 )

Video hướng dẫn

————————————–kết thúc bài 4————————————–