Phân tích dữ liệu Excel - Trực quan hóa dữ liệu

Bạn có thể hiển thị báo cáo phân tích dữ liệu của mình theo một số cách trong Excel. Tuy nhiên, nếu kết quả phân tích dữ liệu của bạn có thể được trực quan hóa dưới dạng biểu đồ làm nổi bật những điểm đáng chú ý trong dữ liệu, thì khán giả của bạn có thể nhanh chóng nắm bắt được những gì bạn muốn chiếu trong dữ liệu. Nó cũng để lại tác động tốt đến phong cách trình bày của bạn.

Trong chương này, bạn sẽ biết cách sử dụng biểu đồ Excel và các tính năng định dạng Excel trên biểu đồ cho phép bạn trình bày kết quả phân tích dữ liệu của mình một cách nhấn mạnh.

Trực quan hóa dữ liệu bằng biểu đồ

Trong Excel, biểu đồ được sử dụng để biểu diễn đồ họa của bất kỳ tập hợp dữ liệu nào. Biểu đồ là một biểu diễn trực quan của dữ liệu, trong đó dữ liệu được biểu thị bằng các ký hiệu như các thanh trong Biểu đồ Thanh hoặc các đường trong Biểu đồ Đường. Excel cung cấp cho bạn nhiều loại biểu đồ và bạn có thể chọn một loại phù hợp với dữ liệu của mình hoặc bạn có thể sử dụng tùy chọn Excel Recommended Charts để xem các biểu đồ được tùy chỉnh cho dữ liệu của bạn và chọn một trong số chúng.

Tham khảo Bảng hướng dẫn Excel để biết thêm thông tin về các loại biểu đồ.

Trong chương này, bạn sẽ hiểu các kỹ thuật khác nhau mà bạn có thể sử dụng với biểu đồ Excel để làm nổi bật kết quả phân tích dữ liệu của mình hiệu quả hơn.

Tạo biểu đồ kết hợp

Giả sử bạn có mục tiêu và lợi nhuận thực tế cho năm tài chính 2015-2016 mà bạn thu được từ các khu vực khác nhau.

Chúng tôi sẽ tạo Biểu đồ cột theo cụm cho các kết quả này.

Khi bạn quan sát, rất khó để hình dung sự so sánh nhanh chóng giữa mục tiêu và thực tế trong biểu đồ này. Nó không mang lại tác động thực sự đến kết quả của bạn.

Cách tốt hơn để phân biệt hai loại dữ liệu để so sánh các giá trị là sử dụng Biểu đồ kết hợp. Trong Excel 2013 và các phiên bản ở trên, bạn có thể sử dụng biểu đồ Tổ hợp cho cùng mục đích.

Sử dụng Cột dọc cho các giá trị mục tiêu và Dòng có Điểm đánh dấu cho các giá trị thực tế.

Bấm vào tab THIẾT KẾ bên dưới tab CÔNG CỤ BIỂU ĐỒ trên Ruy-băng.
Bấm Thay đổi Loại Biểu đồ trong nhóm Loại. Hộp thoại Thay đổi loại biểu đồ xuất hiện.

Nhấp vào Tổ hợp.
Thay đổi Loại biểu đồ cho chuỗi Thực tế thành Dòng có Điểm đánh dấu. Bản xem trước xuất hiện trong Kết hợp tùy chỉnh.
Bấm OK.

Biểu đồ kết hợp tùy chỉnh của bạn sẽ được hiển thị.

Khi bạn quan sát trong biểu đồ, Giá trị mục tiêu nằm trong Cột và Giá trị thực được đánh dấu dọc theo dòng. Việc hiển thị dữ liệu đã trở nên tốt hơn vì nó cũng cho bạn thấy xu hướng kết quả của bạn.

Tuy nhiên, kiểu biểu diễn này không hoạt động tốt khi phạm vi dữ liệu của hai giá trị dữ liệu của bạn khác nhau đáng kể.

Tạo biểu đồ kết hợp với trục phụ

Giả sử bạn có dữ liệu về số lượng đơn vị sản phẩm đã được vận chuyển và lợi nhuận thực tế cho năm tài chính 2015-2016 mà bạn thu được từ các khu vực khác nhau.

Nếu bạn sử dụng cùng một biểu đồ kết hợp như trước đây, bạn sẽ nhận được những điều sau:

Trong biểu đồ, dữ liệu của No. of Units không hiển thị vì phạm vi dữ liệu thay đổi đáng kể.

Trong những trường hợp như vậy, bạn có thể tạo biểu đồ kết hợp với trục phụ, để trục chính hiển thị một phạm vi và trục phụ hiển thị phạm vi kia.

Bấm vào tab CHÈN.
Nhấp vào Tổ hợp trong nhóm Biểu đồ.
Nhấp vào Tạo biểu đồ kết hợp tùy chỉnh từ danh sách thả xuống.

Hộp thoại Chèn Biểu đồ xuất hiện với Tổ hợp được đánh dấu.

Đối với Loại biểu đồ, hãy chọn -

Dòng với các điểm đánh dấu cho số sê-ri của đơn vị
Cột được nhóm cho lợi nhuận thực tế của chuỗi
Đánh dấu vào Ô thứ cấp Trục ở bên phải của Số sê-ri đơn vị và bấm OK.

Bản xem trước biểu đồ của bạn xuất hiện trong Kết hợp tùy chỉnh.

Biểu đồ kết hợp của bạn xuất hiện với Trục phụ.

Bạn có thể quan sát các giá trị cho Lợi nhuận thực tế trên trục chính và giá trị cho Số đơn vị trên trục phụ.

Một quan sát đáng kể trong biểu đồ trên là trong Quý 3, trong đó Số căn hộ bán được nhiều hơn, nhưng Lợi nhuận thực tế kiếm được lại ít hơn. Điều này có thể được gán cho chi phí khuyến mãi đã phát sinh để tăng doanh thu. Tình hình được cải thiện trong Quý 4, với doanh thu giảm nhẹ và Lợi nhuận thực tế tăng đáng kể.

Trục loại và chuỗi phân biệt đối xử

Giả sử bạn muốn dự báo Lợi nhuận thực tế đạt được trong các năm 2013-2016.

Tạo một cột được nhóm cho dữ liệu này.

Khi bạn quan sát, việc hiển thị dữ liệu không hiệu quả vì các năm không được hiển thị. Bạn có thể khắc phục điều này bằng cách thay đổi năm thành danh mục.

Xóa năm tiêu đề trong phạm vi dữ liệu.

Bây giờ, năm được coi là một hạng mục chứ không phải một chuỗi. Biểu đồ của bạn trông như sau:

Phần tử biểu đồ và kiểu biểu đồ

Phần tử biểu đồ cung cấp nhiều mô tả hơn cho biểu đồ của bạn, do đó giúp hiển thị dữ liệu của bạn một cách có ý nghĩa hơn.

Nhấp vào Biểu đồ

Ba nút xuất hiện bên cạnh góc trên bên phải của biểu đồ -

Thành phần biểu đồ
Kiểu biểu đồ
Bộ lọc biểu đồ

Để có giải thích chi tiết về những điều này, hãy tham khảo hướng dẫn Biểu đồ Excel.

Nhấp vào Phần tử biểu đồ.
Nhấp vào Nhãn dữ liệu.

Nhấp vào Kiểu biểu đồ
Chọn Kiểu và Màu phù hợp với dữ liệu của bạn.

Bạn có thể sử dụng Đường xu hướng để hiển thị bằng đồ thị các xu hướng trong dữ liệu. Bạn có thể mở rộng Đường xu hướng trong biểu đồ ngoài dữ liệu thực tế để dự đoán các giá trị trong tương lai.

Nhãn dữ liệu

Excel 2013 và các phiên bản mới hơn cung cấp cho bạn nhiều tùy chọn khác nhau để hiển thị Nhãn dữ liệu. Bạn có thể chọn một Nhãn Dữ liệu, định dạng nó theo ý muốn, sau đó sử dụng Nhãn Hiện tại Sao chép để sao chép định dạng sang phần còn lại của Nhãn Dữ liệu trong biểu đồ.

Các Nhãn Dữ liệu trong biểu đồ có thể có các hiệu ứng, các hình dạng và kích thước khác nhau.

Cũng có thể hiển thị nội dung của ô như một phần của Nhãn Dữ liệu với Chèn Trường Nhãn Dữ liệu.

Bố cục nhanh

Bạn có thể sử dụng Bố cục Nhanh để thay đổi bố cục tổng thể của biểu đồ một cách nhanh chóng bằng cách chọn một trong các tùy chọn bố cục được xác định trước.

Nhấp vào biểu đồ.
Nhấp vào tab THIẾT KẾ bên dưới CÔNG CỤ BIỂU ĐỒ.
Nhấp vào Bố cục Nhanh.

Các bố cục có thể khác nhau sẽ được hiển thị. Khi bạn di chuyển trên các tùy chọn bố cục, bố cục biểu đồ sẽ thay đổi thành tùy chọn cụ thể đó.

Chọn bố cục bạn thích. Biểu đồ sẽ được hiển thị với bố cục đã chọn.

Sử dụng Hình ảnh trong Biểu đồ Cột

Bạn có thể tạo điểm nhấn hơn cho bản trình bày dữ liệu của mình bằng cách sử dụng hình ảnh thay cho các cột.

Bấm vào một Cột trên Biểu đồ Cột.
Trong Chuỗi Dữ liệu Định dạng, bấm vào Điền.
Chọn Hình ảnh.
Trong Chèn ảnh từ, hãy cung cấp tên tệp hoặc tùy chọn khay nhớ tạm nếu bạn đã sao chép ảnh trước đó.

Hình ảnh bạn đã chọn sẽ xuất hiện ở vị trí của các cột trong biểu đồ.

Biểu đồ ban nhạc

Bạn có thể phải trình bày kết quả khảo sát khách hàng về một sản phẩm từ các khu vực khác nhau. Biểu đồ dải phù hợp cho mục đích này. Biểu đồ dải là Biểu đồ dạng đường có thêm vùng tô bóng để hiển thị ranh giới trên và ranh giới dưới của các nhóm dữ liệu.

Giả sử kết quả khảo sát khách hàng của bạn từ các khu vực phía đông và phía tây, tháng khôn ngoan là -

Ở đây, trong dữ liệu <50% là Thấp, 50% - 80% là Trung bình và> 80% là Cao.

Với Biểu đồ dải, bạn có thể hiển thị kết quả khảo sát của mình như sau:

Tạo Biểu đồ đường từ dữ liệu của bạn.

Thay đổi loại biểu đồ thành -

Dòng Đông và Tây thành Dòng với Điểm đánh dấu.
Thấp, trung bình và cao loạt đến cột xếp chồng.

Biểu đồ của bạn trông như sau.

Bấm vào một trong các cột.
Thay đổi độ rộng khoảng trống thành 0% trong Định dạng chuỗi dữ liệu.

Bạn sẽ nhận được Bands thay vì cột.

Để làm cho biểu đồ dễ trình bày hơn -

Thêm tiêu đề biểu đồ.
Điều chỉnh phạm vi Trục dọc.
Thay đổi màu của các dải thành Xanh lục-Vàng-Đỏ.
Thêm Nhãn vào các dải.

Kết quả cuối cùng là Biểu đồ dải với các ranh giới xác định và kết quả khảo sát được thể hiện trên các dải. Người ta có thể nhanh chóng và rõ ràng từ biểu đồ rằng trong khi kết quả khảo sát của khu vực phía Tây là khả quan, thì kết quả của khu vực phía Đông lại giảm trong quý vừa qua và cần được chú ý.

Biểu đồ nhiệt kế

Khi bạn phải biểu thị một giá trị mục tiêu và một giá trị thực tế, bạn có thể dễ dàng tạo Biểu đồ nhiệt kế trong Excel hiển thị rõ ràng các giá trị này.

Với biểu đồ Nhiệt kế, bạn có thể hiển thị dữ liệu của mình như sau:

Sắp xếp dữ liệu của bạn như hình dưới đây -

Chọn dữ liệu.
Tạo biểu đồ Cột theo cụm.

Khi bạn quan sát, cột bên phải là Mục tiêu.

Bấm vào một Cột trong biểu đồ.
Nhấp vào Chuyển đổi Hàng / Cột trên Ruy-băng.

Nhấp chuột phải vào Cột Mục tiêu.
Bấm vào Định dạng chuỗi dữ liệu.
Nhấp vào Trục phụ.

Khi bạn quan sát Trục chính và Trục phụ có các phạm vi khác nhau.

Nhấp chuột phải vào Trục chính.
Trong tùy chọn Trục định dạng, bên dưới Bounds, nhập 0 cho Tối thiểu và 1 cho Tối đa.
Lặp lại tương tự cho Trục phụ.

Cả Trục chính và Trục phụ sẽ được đặt thành 0% - 100%. Cột Mục tiêu ẩn Cột Thực tế.

Nhấp chuột phải vào cột hiển thị (Mục tiêu)
Trong Chuỗi Dữ liệu Định dạng, hãy chọn
- Không điền cho FILL
- Đường liền nét cho BORDER
- Màu xanh lam cho màu sắc

Trong Phần tử biểu đồ, bỏ chọn
- Trục → Ngang chính
- Trục → Dọc thứ cấp
- Gridlines
- Tiêu đề biểu đồ
Trong biểu đồ, nhấp chuột phải vào Trục dọc chính
Trong các tùy chọn Định dạng Trục, bấm vào TICK MARKS
Đối với loại Chính, hãy chọn Bên trong

Nhấp chuột phải vào Vùng biểu đồ.
Trong các tùy chọn Định dạng Khu vực Biểu đồ, hãy chọn
- Không điền cho FILL
- Không có dòng nào cho BORDER

Thay đổi kích thước khu vực biểu đồ, để có hình dạng của một nhiệt kế.

Bạn đã có biểu đồ nhiệt kế của mình, với giá trị thực tế so với giá trị mục tiêu được hiển thị. Bạn có thể làm cho biểu đồ nhiệt kế này ấn tượng hơn với một số định dạng.

Chèn hình chữ nhật chồng phần hình chữ nhật màu xanh lam vào biểu đồ.
Trong tùy chọn Định dạng Hình dạng, hãy chọn -
- Tô màu gradient cho FILL
- Tuyến tính cho loại
- 180 ⁰ cho Góc
Đặt các điểm dừng Gradient ở 0%, 50% và 100%.
Đối với Gradient dừng ở 0% và 100%, hãy chọn màu đen.
Đối với Gradient dừng ở 50%, chọn màu trắng.

Chèn một hình bầu dục ở dưới cùng.
Định dạng hình dạng với các tùy chọn tương tự.

Kết quả là Biểu đồ nhiệt kế mà chúng tôi đã bắt đầu.

Biểu đồ Gantt

Biểu đồ Gantt là biểu đồ trong đó một loạt các đường ngang thể hiện khối lượng công việc được thực hiện trong những khoảng thời gian nhất định liên quan đến khối lượng công việc được lên kế hoạch cho những khoảng thời gian đó.

Trong Excel, bạn có thể tạo biểu đồ Gantt bằng cách tùy chỉnh loại biểu đồ Thanh xếp chồng để nó mô tả các nhiệm vụ, thời lượng tác vụ và phân cấp. Biểu đồ Excel Gantt thường sử dụng ngày làm đơn vị thời gian dọc theo trục hoành.

Hãy xem xét dữ liệu sau đây trong đó cột -

Nhiệm vụ thể hiện các Nhiệm vụ trong dự án
Bắt đầu đại diện cho số ngày kể từ Ngày bắt đầu của dự án
Thời lượng thể hiện thời lượng của Nhiệm vụ

Lưu ý rằng Bắt đầu của bất kỳ Tác vụ nào là Bắt đầu của Tác vụ trước đó + Thời lượng. Đây là trường hợp khi Nhiệm vụ được phân cấp.

Chọn dữ liệu.
Tạo biểu đồ thanh xếp chồng.

Nhấp chuột phải vào Start Series.
Trong tùy chọn Định dạng chuỗi dữ liệu, hãy chọn Không điền.

Nhấp chuột phải vào Trục danh mục.
Trong tùy chọn Định dạng Trục, hãy chọn Danh mục theo thứ tự ngược lại.

Trong Phần tử biểu đồ, bỏ chọn
- Legend
- Gridlines
Định dạng Trục ngang thành
- Điều chỉnh phạm vi
- Đánh dấu chính vào khoảng thời gian 5 ngày
- Dấu tick nhỏ trong khoảng thời gian 1 ngày
Định dạng Chuỗi dữ liệu để làm cho nó trông ấn tượng
Đặt tiêu đề biểu đồ

Biểu đồ thác nước

Biểu đồ thác nước là một trong những công cụ trực quan hóa phổ biến nhất được sử dụng trong các doanh nghiệp lớn và nhỏ. Biểu đồ thác nước là lý tưởng để hiển thị cách bạn đạt được giá trị ròng, chẳng hạn như thu nhập ròng, bằng cách chia nhỏ tác động tích lũy của những đóng góp tích cực và tiêu cực.

Excel 2016 cung cấp kiểu Biểu đồ thác nước. Nếu bạn đang sử dụng các phiên bản Excel trước, bạn vẫn có thể tạo Biểu đồ thác nước bằng cách sử dụng Biểu đồ cột xếp chồng.

Các cột được mã hóa bằng màu sắc để bạn có thể nhanh chóng phân biệt số dương với số âm. Các cột giá trị ban đầu và cuối cùng bắt đầu trên trục hoành, trong khi các giá trị trung gian là cột nổi. Do hình thức này, Biểu đồ Thác còn được gọi là Biểu đồ Cầu.

Hãy xem xét dữ liệu sau đây.

Chuẩn bị dữ liệu cho Biểu đồ thác nước
Đảm bảo cột Dòng tiền ròng ở bên trái của Cột Tháng (Điều này là do bạn sẽ không bao gồm cột này khi tạo biểu đồ)
Thêm 2 cột - Tăng và Giảm cho các dòng tiền dương và âm tương ứng
Thêm cột Bắt đầu - cột đầu tiên trong biểu đồ có giá trị bắt đầu trong Dòng tiền ròng
Thêm cột Kết thúc - cột cuối cùng trong biểu đồ có giá trị cuối cùng trong Dòng tiền ròng
Thêm Float cột - hỗ trợ các cột trung gian
Tính toán các giá trị cho các cột này như sau

Trong cột Float, hãy chèn một hàng vào đầu và cuối. Đặt n giá trị tùy ý 50000. Điều này chỉ để có một số khoảng trống ở bên trái và bên phải của biểu đồ

Dữ liệu sẽ như sau.

Chọn các ô C2: H18 (Loại trừ cột Dòng tiền ròng)
Tạo biểu đồ cột xếp chồng

Nhấp chuột phải vào Series Float.
Bấm Định dạng Chuỗi Dữ liệu.
Trong tùy chọn Định dạng chuỗi dữ liệu, hãy chọn Không điền.

Nhấp chuột phải vào Dòng tiêu cực.
Chọn Tô màu là Đỏ.

Nhấp chuột phải vào Dòng tích cực.
Chọn Tô màu là Xanh lục.

Nhấp chuột phải vào Start Series.
Chọn Tô màu là Xám.
Nhấp chuột phải vào End Series.
Chọn Tô màu là Xám.
Xóa Chú giải.

Nhấp chuột phải vào bất kỳ Series nào
Trong tùy chọn Định dạng chuỗi dữ liệu, chọn Khoảng cách rộng là 10% trong Tùy chọn chuỗi

Đưa ra Tiêu đề Biểu đồ. Biểu đồ Thác nước sẽ được hiển thị.

Đường gấp khúc

Đường gấp khúc là các biểu đồ nhỏ được đặt trong các ô đơn lẻ, mỗi ô đại diện cho một hàng dữ liệu trong lựa chọn của bạn. Họ cung cấp một cách nhanh chóng để xem xu hướng.

Bạn có thể thêm Đường biểu diễn bằng công cụ Phân tích nhanh.

Chọn dữ liệu mà bạn muốn thêm Biểu đồ thu nhỏ.
Giữ một cột trống ở phía bên phải của dữ liệu cho Biểu đồ thu nhỏ.

Nút Phân tích nhanh

xuất hiện ở dưới cùng bên phải của dữ liệu bạn đã chọn.

Bấm vào

nút Phân tích nhanh . Thanh công cụ Phân tích Nhanh xuất hiện với các tùy chọn khác nhau.

Nhấp chuột SPARKLINES. Các tùy chọn biểu đồ được hiển thị dựa trên dữ liệu và có thể khác nhau.

Nhấp chuột Line. Biểu đồ Đường cho mỗi hàng được hiển thị trong cột bên phải dữ liệu.

PivotCharts

Biểu đồ Pivot được sử dụng để tóm tắt dữ liệu bằng đồ thị và khám phá dữ liệu phức tạp.

PivotChart hiển thị Chuỗi dữ liệu, Danh mục và Trục biểu đồ giống như cách biểu đồ chuẩn làm. Ngoài ra, nó cũng cung cấp cho bạn các điều khiển lọc tương tác ngay trên biểu đồ để bạn có thể nhanh chóng phân tích một tập hợp con dữ liệu của mình.

PivotCharts rất hữu ích khi bạn có dữ liệu trong một PivotTable khổng lồ hoặc nhiều dữ liệu trang tính phức tạp bao gồm văn bản và số. PivotChart có thể giúp bạn hiểu dữ liệu này.

Bạn có thể tạo PivotChart từ

Một PivotTable.
Bảng Dữ liệu như một bảng độc lập không có PivotTable.

PivotChart từ PivotTable

Để tạo PivotChart, hãy làm theo các bước dưới đây:

Nhấp vào PivotTable.
Nhấp vào PHÂN TÍCH bên dưới CÔNG CỤ CÓ THỂ TÍCH HỢP trên Ruy-băng.
Nhấp vào PivotChart. Hộp thoại Chèn biểu đồ xuất hiện.

Chọn Cột được Nhóm từ tùy chọn Cột.

Bấm OK. PivotChart được hiển thị.

PivotChart có ba bộ lọc - Khu vực, Nhân viên bán hàng và Tháng.

Nhấp vào tùy chọn Kiểm soát Bộ lọc Khu vực. Hộp Tìm kiếm xuất hiện với danh sách tất cả các Khu vực. Hộp kiểm xuất hiện bên cạnh Khu vực.
Chọn tùy chọn Đông và Nam.

Dữ liệu được lọc xuất hiện trên cả PivotChart và PivotTable.

PivotChart không có PivotTable

Bạn có thể tạo PivotChart độc lập mà không cần tạo PivotTable.

Bấm vào Bảng dữ liệu.
Bấm vào tab Chèn.
Bấm PivotChart trong nhóm Biểu đồ. Cửa sổ Tạo PivotChart xuất hiện.
Chọn Bảng / Phạm vi.
Chọn Vị trí bạn muốn đặt PivotChart.

Bạn có thể chọn một ô trong chính trang tính hiện có hoặc trong một trang tính mới. Bấm OK.

Một PivotChart trống và một PivotTable trống xuất hiện cùng với Danh sách Trường PivotChart để tạo PivotChart.

Chọn các trường sẽ được thêm vào PivotChart
Sắp xếp các Trường bằng cách kéo chúng vào BỘ LỌC, HUYỀN THOẠI (SERIES), TRỤC (DANH MỤC) và GIÁ TRỊ
Sử dụng Điều khiển Bộ lọc trên PivotChart để chọn Dữ liệu sẽ được đặt trên PivotChart

Excel sẽ tự động tạo một PivotTable được ghép nối.