Sorting và scroll bar

Trong bài Tạo scroll bar trong Excel các bạn đã tạo 1 Dashboard căn bản, có 1 table có thể cuộn lên xuống được.

Yêu cầu của xếp bạn cao hơn một chút – làm sao có thể sắp xếp dữ liệu trong bảng đó một cách dễ dàng như ví dụ minh họa ở trên.

Trước tiên bạn download file mẫu ở đây.

Tiếp theo, các bạn có thể xem file mình tạo dựa trên file mẫu ở đây.

Trong file này, 3 sheet: Data, Dashboard, Calculation là của file mẫu – không sửa chữa gì cả. Hai sheet: 01Dashboard và 01Calculation là do mình tạo dựa trên file mẫu - hướng dẫn này chủ yếu đề cập đến 2 sheet này.

Sheet Data sẽ là sheet dữ liệu dùng chung.

Sau đây là các bước thực hiện (giả dụ bạn chỉ có file mẫu – chưa có 2 sheet 01Dashboard và 01Calculation)

1. Tạo sheet 01Calculation

(Chỉ tạo đến dòng 6 mà thôi – Ô D6 để trống – không nhập giá trị)

Right click ô D6 – click Name a range – đánh vào mysort để đặt tên cho ô này là mysort.

2. Tạo scroll bar – tham khảo bài Tạo Scroll bar trong Excel

Kết quả sẽ là 1 Scroll bar như sau trong sheet mới 01Dashboard

(Chưa có các nút KPI)

3. Tạo nút KPI 1 -> KPI 5

Chọn tab Developer – click Insert – click Option Button (Form Control)

vẽ 1 Option Button

Click lên Option Button này – xóa chữ Option 01

Right click Option Button này – click Format Control – sang tab Control và cấu hình như sau:

Lưu ý phần Cell link cần cấu hình chính xác.

Bạn lặp lại các thao tác trên để có đủ 5 Option Button cho 5 KPI – sau đó di chuyển chúng vào các ô KPI.

4. Cấu hình Conditional Formatting sao cho khi click nút KPI sẽ tạo ra giá trị sắp xếp trong ô ‘01Calculatioin’!$D$6

Trong sheet 01Dashboard thực hiện các bước sau cho nút KPI 1:

Chọn (tô sáng) range D4:D14 – click Conditional Formatting – click Manage Rules… - click New Rule… - cấu hình như sau:

Click OK – OK

Thực hiện tương tự với các range E4:E14, F4:F14, G4:G14, H4:H14 cho các ô KPI 2, 3, 4, 5 (thay =mysort =1 bằng =mysort =2 và tương tự)

Kiểm tra lại – khi click lên nút KPI 1 -> ô 01Calculation!D6 phải thể hiện số 1 – và tương tự cho các nút KPI khác.

5. Tạo bảng dữ liệu sắp xếp trong sheet 01Calculation

Trong sheet 01Dashboard - tạo thêm phần từ dòng 08 đến 108.

Cột B đến cột G: tạo giá trị trung gian để sắp xếp trong sheet 01Dashboard

• Cột B và cột C: lấy dữ liệu từ sheet Data
• Cột D: lấy dữ liệu của KPI được xác định bằng ô 01Calculation!D6 (dùng OFFSET)
• Cột E: mục đích là tạo ra 1 giá trị KPI duy nhất – không trùng
• Cột F: sắp xếp các giá trị của cột E từ cao xuống thấp bằng hàm LARGE
• Cột G: xếp hạng giá trị trong cột F so với cột E bằng hàm MATCH

Cột I đến cột N: tạo giá trị dùng để sắp xếp trong 01Dashboard (dùng OFFSET)

• Cột I: lấy tên sản phẩm trong sheet Data dựa trên cột G
• Cột J,K,L,M,N: lấy giá trị KPI tương ứng trong sheet Data dựa trên cột G

Kiểm tra lại – so sánh sheet 01Calculation của bạn với sheet Calculation của file mẫu xem có giống không

6. Hoàn tất các giá trị trong sheet 01Dashboard (dùng OFFSET)

• Cột A và B: lấy giá trị Thứ tự và Tên sản phẩm trong sheet 01Calculation dựa trên ô 01Calculation!D4
• Cột D, E, F, G, H: lấy giá trị KPI tương tứng trong sheet 01Calculation dựa trên ô 01Calculation!D4

Kết quả là bạn tạo được 1 Dashboard có: scroll bar kết hợp với nút sắp xếp rất linh hoạt cho mục đích báo cáo.

Chúc bạn thành công - nếu có thắc mắc gì xin bạn comment hoặc gửi email về support@cuocsongonline.com

Hẹn gặp lại vào bài tiếp theo của loạt bài về Dashboard này nhé :).

 

Sorting và scroll bar