Sử dụng scrollbar trong Dashboard

Một dashboard được coi là hoàn hảo khi có đầy đủ các yếu tố sau:

• Dễ đọc (Easy to read)
• Gói gọn trong một trang (Single page)
• Thể hiện thông tin
  • Hiện tại (current status)
  • Xu hướng quá khứ (historical trend)
  • So sánh với chỉ số KPI

Để tìm hiểu sâu hơn lý thuyết cũng như các yếu tố về Dashboard, bạn đọc có thể download file powerpoint  06-1.BI.Dashboards.in.Excel(1) ở cuối bài viết. Trong bài viết hôm nay, tác giả xin được đề cập đến cách sử dụng ScrollBar nhằm mục đích thể hiện nhiều thông tin trên một không gian hẹp của Dashboards (Single page). Tác giả sẽ đề cập rõ hơn các yếu tố còn lại của Dashboard ở các bài viết tới.

Trước hết, các bạn hãy download file data  06-2.BI.Dashboards.in.Excel.Tutorial.Data(1) để thực tập. Trong trường hợp các bạn vẫn chưa rõ nội dung hướng dẫn dưới đây, các bạn có thể sử dụng file 06-2.BI.Dashboards.in.Excel.Tutorial để xem đáp án.

06-2.BI.Dashboards.in.Excel.Tutorial.Data(1) chứa các thông tin của  5 KPI chính đạt được của 100 sản phẩm. Hãy tưởng tượng, nếu như bạn có 1000 thông tin cần thể hiện (như sản phẩm, doanh thu của vùng,…) và bạn muốn thể hiện đầy đủ thông tin đó trong 1 trang dashboard. Điều đó là không thể nếu như bạn không sử dụng scrollbar.

Các bước làm như sau:

1.Tạo Scroll bar

Bước 1: Rename sheet đầu tiên là Data và tạo ra một sheet mới với tên gọi là dashboard1.
Bước 2: Tạo một sheet mới và đặt tên là Calc, điền “Scrollbar position” vào ô A1
Bước 3: Quay lại tab dashboard1, chúng ta sẽ tạo một table có 10 dòng 8 cột và bôi đen viền.
Bước 4: Trong trường hợp, bạn chưa có tab Developer, các bạn phải vào CUSTOMIZE THE RIBBON -> DEVELOPER. Khi đã có tab Developer, các bạn chọn DEVELOPER -> INSERT (Controls) -> Scrollbar. Vẽ Scrollbar vào cột thứ 3 của table.
Bước 5: Assign các giá trị scrollbar bằng cách: right-click vào scrollbar -> Format Control, set Min.Val=1, Max.Val=91 (Hãy thử giá trị 100, các bạn sẽ hiểu tại sao lại nên đặt giá trị 91) , Incr.Change=1, Page.Change=10, Cell link = Calc!$ B$ 1
Bước 6: Ở ô trên cùng bên tay trái của Dashboard, các bạn dùng lệnh OFFSET để lấy thông tin từng ô. Các bạn điền =OFFSET(top-left of data, Calc!$ B$ 1,0,1,1). Kéo toàn bộ các ô còn lại trong dashboard.
Bước 7: Format lại table. Tùy thuộc vào cảm quan của các bạn để có dashboard đẹp hơn.

Các bạn có thể áp dụng tương tự sử dụng scrollbar cho KPI!.

2. Sort data.

Tiếp tục ví dụ trên, để người đọc có thể phân tích sâu hơn các thông tin trên, chúng ta có thể thêm option để người đọc có thể sort được data theo ý mình. Ví dụ bên dưới, khi ấn vào KPI2, excel sẽ tự động sort thông tin theo KPI 2.

Nếu bạn chưa thành thạo macro, có một giải pháp khác đơn giản hơn để các bạn có thể thực hiện việc sort data.

Bước 1: Để thuận tiện, các bạn nên duplicate tab dashboard1 và thực hành trên dashboard2. Trong tab dashboard2, insert thêm một dòng ở giữa dòng KPI và thông tin sản phẩm. Insert 5 Option button bằng cách chọn DEVELOPER -> INSERT (Controls) -> Option button.
Bước 2: Tại tab Calc, ở ô A2 đánh Sort by
Bước 3: Copy cột No. and Products trong tab data vào tab Calc.
Bước 4: Trong tab Calc, thêm cột mới Active KPI và populate công thức =OFFSET(Data!B2,0,Calc!$ B$ 2) để lấy thông tin KPI

Bước 5: Thêm cột mới Unique để lấy giá trị unique của Active KPI bằng công thức =C5+A5/1000000.
Bước 6: Thêm cột mới Sorted, sử dụng hàm =LARGE($ D$ 5:$ D$ 104,A5) để lấy giá trị lớn nhất, lớn nhì…..
Bước 7: Thêm cột mới Position, sử dụng hàm =MATCH(E5,$ D$ 5:$ D$ 104,0)
Bước 8: Tạo sorted data cho từng sản phẩm như hình bên dưới với công thức OFFSET như sau: =OFFSET(Data!J$ 1,Calc!$ F12,0)

Bước 9: Quay lại dashboard2, các bạn chỉnh lại công thức của từng KPI thay vì link với tab Data, sẽ link với sorted data trong tab Calc.

3. Sử dụng thông tin về Percentile 

Giả sử bạn muốn xem top các sản phẩm vượt KPI1, bạn ấn vào sort KPI1 (Hình bên dưới)

Bạn nhận thấy Product 42 có KPI1 rất cao, và bạn nhìn lướt qua KPI3 và thấy có vẻ như sản phẩm này không tốt lắm. Để xem Product 42 có KPI3 như thế nào so với các sản phẩm khác, các bạn phải sort theo KPI3 và scroll down đến Product 42. Việc này rất phức tạp. Để có thể thể hiện được tương quan các KPI với nhau, các bạn có thể sử dụng Percentile information (Ví dụ Product A có 90% percentile có nghĩa là sản phẩm A xếp hạng cao hơn 90% các sản phẩm còn lại).

Bước 1: Thêm 2 dòng ở tab Calc để thể hiện giá trị upper and lower percentile value

Bước 2: Tại tab Calc, các bạn tính toán giá trị tuyệt đối cho percentile for green and red cho từng sản phẩm bằng hàm =PERCENTILE.INC(I$ 5:I$ 104,Data!$ C1)

Bước 2: Vào tab Dashboard2, các bạn thêm 5 cột vào giữa các cột hiện tại

Bước 3: Tại các cột mới, điền công thức để thể hiện “<+” and “<” bằng công thức =IF(Calc!$ B$ 2=Dashboard3!F$ 3,””,IF(E4>Calc!I$ 1,”<+”,IF(E4<Calc!I$ 2,”<-“,””)))
Bước 4: Thay đổi màu cho “<+” and “<”, các bạn có thể thực hiện như sau: HOME -> (Styles) -> Conditional formatting -> New formatting rule (green if “<+”) và apply nó cho tất cả cột mới.

Như vậy bây giờ các bạn đã có thể biết Product 42 có average performance đối với KPI 1 và low performance với KPI3

4. Thêm đồ thị vào dashboard

Bước 1: Các bạn cần tính toán giá trị MIN, MAX, AVG ở trong tab calc =MIN(Data!$ C$ 4:$ C$ 104), =AVERAGE(Data!$ C$ 4:$ C$ 104)

Bước 2: Insert bar chart và sử dụng data trên là nguồn (Source)
Bước 3: Chỉnh sửa lại bar chart để dashboard có thể đẹp hơn.

Nếu bạn thấy bài viết hữu ích, hãy like fanpage của chúng tôi nhé. Nếu bạn có câu hỏi hay cần hỗ trợ hãy liên lạc với chúng tôi:excelviet@datamart.vn , xin cám ơn.

(Source: Trích một phần trong giáo trình bộ môn Business Intelligence – Bournemouth University)

Download Attachments

Hãy cùng chia sẻ với bạn bè

2/5 - (4 bình chọn)

Originally posted 2017-12-20 17:23:11.

source https://www.excelketoan.net/su-dung-scrollbar-trong-dashboard