Bạn đã bao giờ muốn tạo một biểu đồ tương tác thú vị, hấp dẫn trong Excel chưa? Đã bao giờ bạn tự hỏi Pivot table là gì? Pivot table sử dụng trong Excel như thế nào chưa?
Trong hướng dẫn này, chúng ta sẽ tìm hiểu tất cả về việc tạo biểu đồ tương tác đầu tiên Pivot table nhiều cột của bạn.
Hướng dẫn tương tác với biểu đồ trong Excel
Có một số cách để tạo một biểu đồ tương tác trong Excel. Bạn có thể sử dụng data validation, từ controls, slicers, timelines, VBA hoặc hyperlinks. Trong hướng dẫn này, chúng ta sẽ tìm hiểu cách tạo biểu đồ tương tác với data validation và slicers.
1. Biểu đồ tương tác với Data Validation
Biết rằng bạn là người quản lý sản phẩm tại bộ phận chăm sóc người tiêu dùng của Billette. Bạn đang xem dữ liệu số lượng đặt hàng lịch sử cho các sản phẩm khác nhau. Dữ liệu của bạn trông như dưới đây
Tạo một biểu đồ pivot table nâng cao với tất cả những số liệu này sẽ rất lâu và khó. Bạn muốn tạo một biểu đồ năng động hoặc tương tác để sếp của bạn có thể chọn sản phẩm nào cô ấy muốn phân tích và hiểu xu hướng đặt hàng. Bạn có thể thực hiện theo các bước sau:
Bước 1: Lập danh sách tất cả các lựa chọn
Chọn khoảng chứa tất cả tên sản phẩm và đi đến Namebox (góc trên cùng bên trái) và nhập tên là products.
Ngoài ra, bạn cũng có thể liệt kê tên sản phẩm trong một phạm vi riêng biệt và đặt tên đó. Hãy chắc chắn rằng tên sản phẩm đó phải nằm trong khoảng chọn sản phẩm mà bạn đã chọn ban đầu.
Bước 2: Thiết lập cơ chế lựa chọn
- Quyết định ô nào sẽ có lựa chọn người dùng. Ví dụ chọn ô Q5.
- Chọn ô và vào Data> Data Validation.
- Thay đổi tiêu chí xác nhận trong Allow chọn List.Nhập “products” trong Nguồn.
- Nhấp vào ok.
Bâygiờ, chúng ta có cách chọn sản phẩm trong ô Q5
Bước 3: chọn sản phẩm cho ô Q5
Nếu chúng ta muốn tên của sản phẩm được chọn, chúng ta chỉ cần sử dụng “= Q5”. Đối với phần còn lại của tính toán, chúng ta cần số lượng sản phẩm (nghĩa là vị trí của sản phẩm được chọn trong products). Đối với điều này, chúng ta có thể sử dụng công thức MATCH, như sau: Nhập công thức MATCH này vào một ô trống ví dụ ô I3= MATCH (Q5, products, 0). Sau đó sẽ trả về một số, phù hợp với sản phẩm người dùng đã chọn.
Bước 4: Tính số lượng đặt hàng để hiển thị trong biểu đồ
Bây giờ, giả sử chúng ta có số lượng sản phẩm được chọn trong ô I3. Với điều này, chúng ta có thể tính toán số lượng sản phẩm đã chọn bằng cách sử dụng công thức INDEX đơn giản=INDEX(data1[@[Soap]:[Deodorant]],$I$3). Nếu dữ liệu của bạn nằm trong phạm vi bình thường, thay vì bảng, hãy sử dụng công thức như sau: =INDEX(C6:H6,$I$3). Điền công thức vào. Bây giờ chúng ta đã tính các giá trị số lượng sản phẩm cho sản phẩm được chọn, khi bạn thay đổi I3, bạn sẽ thấy các giá trị cho sản phẩm có liên quan.
Bước 5: Tạo biểu đồ
Tất cả các công việc ban đầu sẽ được hoàn thành khi bạn thực hiện chèn một biểu đồ. Chỉ cần chọn cột sản phẩm đã chọn và chèn một cột hoặc biểu đồ đường. Chúng ta có như sau:
Đầu tiên, hãy để thêm nhãn trục:
- Nhấp chuột phải vào chart và đi đến select data.
- Chỉnh sửa nhãn ngang (Edit hoeizontal /category) và chọn cột tháng.
- Bây giờ, xóa chart title và chart border (đặt thành không có dòng). Kết quả như sau:
Bước 6: Bước hoàn tất
Chúng ta chỉ cần gộp tất cả lại với nhau và biểu đồ tương tác đầu tiên của chúng ta lại thì ta sẽ được biều đồ Pivot table nhiều cột hoàn thành.
- Chọn biểu đồ cho kiểu dữ liệu biểu trong ô Q5 (ô chọn data validation)
- Vào Insert> Shapes> Rounded rectangle và vẽ một hình chữ nhật lớn đẹp xung quanh biểu đồ và Q5.
- Vào Shape and adjust the line > Remove fill color
- Khi bạn đó với mỗi tùy chọn tại Q5 thì sẽ cho bạn một biểu đồ được cập nhật.
Biểu đồ tương tác với Pivot table và Slicer
Nếu bạn e ngại với INDEX + MATCH, bạn có thể thử phương pháp Pivot Table. Nó cũng hoạt động rất tốt và cho phép bạn tạo ra các biểu đồ tương tác tuyệt vời không kém. Hãy nhớ rằng dữ liệu của bạn cần phải phù hợp. Sắp xếp lại để nó trông như bảng sau:
Hãy nhớ rằng dữ liệu của bạn cần phải phù hợp. Sắp xếp lại để nó trông như thế này:
Bước 1: Chèn một Pivot từ dữ liệu của bạn
- Chọn dữ liệu của bạn (cột tháng, sản phẩm và số lượng) và chèn một Pivot table.
- Thêm tháng vào khu vực labels.
- Trong các phiên bản cao hơn của Excel thì sẽ xuất hiện phân cấp ngày – năm, quý và thán. Khi đó, hãy chọn quý.
- Thêm số lượng vào khu vực giá trị.
- Nhấp chuột phải vào products và thêm nó dưới dạng như một slicers.
Bước 2: Chèn 1 pivot chart
Chọn bất kỳ ô nào bên trong trục và đi tới Analyze > Pivot chart. Chọn biểu đồ đường hoặc cột.Chúng ta sẽ nhận được kết quả sau:
Bước 3: Định dạng cho pivot chart
- Chọn pivot chart và đi đến Analyze và tắt nút Field Buttons.
- Thay thế tiêu đề biểu đồ bằng Total Order Quantity trong 13 tháng qua.
- Đặt đường viền biểu đồ thành No line.
- Vị trí slicer liền kề với biểu đồ.
- Vẽ một hình chữ nhật tròn xung quanh cho tất cảBiểu đồ tương tác của chúng tôi đã sẵn sàng để hoạt động.
Gợi ý trang trí tùy chọn
Thêm một tiêu đề phụ mô tả xu hướng.Đặt giới hạn cho trục tung. Theo mặc định, Excel sẽ thay đổi giới hạn trục Y bất cứ khi nào bạn chọn sản phẩm. Đây có thể là một cách tạo ra một số biến dạng của các con số và gây nhầm lẫn cho người dùng của bạn nếu họ muốn so sánh các sản phẩm.
Bạn có thể định dạng trục và đặt giới hạn. Chọn trục, nhấn CTRL + 1 và đặt tối thiểu thành 0 và tối đa thành giá trị cao nhất có thể (chắc chắn là làm tròn rồi nhé).
Điều chỉnh độ rộng khe hở. Excel sẽ chọn một số giá trị vô lý như 219%. Điều chỉnh này thành 100% hoặc tương tự như vậy để có ít khoảng trắng hơn trên biểu đồ. Để thực hiện việc này, nhấp vào các cột, nhấn CTRL + 1 và từ Series options điều chỉnh độ rộng khoảng cách.
trích nguồn:chandoo.
