Cách tạo dashboard trong excel – Hướng dẫn các bước chi tiết

Dashboard Excel hay còn gọi là bảng điều khiển Excel. Là một dạng báo cáo được trình bày trên Excel với các thông tin sinh động và trực quan hơn là các con số khô khan. Do đó cách tạo Dashboard trong Excel là một kỹ năng nâng cao quan trọng mà các nhân viên văn phòng cần thành thạo. Nó sẽ giúp ích rất nhiều trong công việc của bạn. Trong hướng dẫn này của chúng tôi sẽ giúp bạn hiểu về cách sử dụng Dashboard trong Excel.

Dashboard Excel là gì và nó khác với báo cáo như thế nào?

Trước tiên, hãy hiểu bảng điều khiển Excel là gì.

Bảng điều khiển Excel là một bảng trực quan (hầu hết, nhưng không phải lúc nào cũng cần thiết) giúp người quản lý và lãnh đạo doanh nghiệp theo dõi KPI hoặc số liệu chính và đưa ra quyết định dựa trên nó. Nó chứa các biểu đồ / bảng / khung nhìn được hỗ trợ bởi dữ liệu.

Bảng điều khiển thường được gọi là báo cáo, tuy nhiên, không phải tất cả các báo cáo đều là bảng điều khiển.

Đây là sự khác biệt:

Một báo cáo sẽ chỉ thu thập và hiển thị dữ liệu ở một nơi duy nhất.

Ví dụ: nếu người quản lý muốn biết doanh số đã tăng trưởng như thế nào trong giai đoạn vừa qua và khu vực nào có lợi nhuận cao nhất, một báo cáo sẽ không thể trả lời. Nó chỉ đơn giản là báo cáo tất cả các dữ liệu bán hàng có liên quan. Các báo cáo này sau đó được sử dụng để tạo bảng điều khiển liên kết biều đồ từ Excel sang PowerPoint (trong Excel hoặc PowerPoint) sẽ hỗ trợ cho việc ra quyết định.

Mặt khác, bảng điều khiển sẽ trả lời ngay các câu hỏi quan trọng như khu vực nào đang hoạt động tốt hơn và quản lý nên tập trung vào sản phẩm nào. Các bảng điều khiển này có thể là tĩnh hoặc tương tác (nơi người dùng có thể thực hiện các lựa chọn và thay đổi chế độ xem và dữ liệu sẽ tự động cập nhật).

Bây giờ chúng ta đã hiểu về bảng điều khiển là gì, hãy để Lặn lặn và tìm hiểu cách tạo bảng điều khiển trong Excel.

Làm cách nào để tạo Bảng điều khiển Excel?

Tạo Bảng điều khiển Excel là một quy trình gồm nhiều bước và có một số điều quan trọng bạn cần ghi nhớ khi tạo nó.

Ngay cả trước khi bạn khởi chạy Excel, bạn cần phải rõ ràng về các mục tiêu của bảng điều khiển.

Ví dụ: nếu bạn đang tạo một bảng điều khiển KPI để theo dõi KPI tài chính của một công ty, mục tiêu của bạn sẽ là hiển thị so sánh giai đoạn hiện tại với (các) giai đoạn trước.

Tương tự, nếu bạn tạo ra một bảng điều khiển cho bộ phận nhân sự để theo dõi đào tạo nhân viên, thì mục tiêu sẽ là cho thấy có bao nhiêu nhân viên đã được đào tạo và có bao nhiêu nhu cầu được đào tạo để đạt được mục tiêu.

Những việc cần làm trước khi bạn bắt đầu tạo Bảng điều khiển Excel

Rất nhiều người bắt đầu làm việc trên bảng điều khiển ngay khi họ có được dữ liệu.

Và trong hầu hết các trường hợp, họ mang đến cho họ sự khốn khổ khi làm lại trên bảng điều khiển vì các mục tiêu của khách hàng / các bên liên quan không được đáp ứng.

Dưới đây là một số câu hỏi bạn phải trả lời trước khi bắt đầu xây dựng Bảng điều khiển Excel:

Mục đích của Bảng điều khiển là gì?

Điều đầu tiên cần làm ngay khi bạn nhận được dữ liệu (hoặc thậm chí trước khi lấy dữ liệu), là để có được sự rõ ràng về những gì các bên liên quan của bạn muốn. Hãy rõ ràng về mục đích mà bảng điều khiển cần phục vụ.

Là để theo dõi KPI chỉ một lần, hoặc trên cơ sở thường xuyên? Có cần theo dõi KPI cho toàn bộ công ty hoặc phân chia khôn ngoan không?. Đặt câu hỏi đúng sẽ giúp bạn hiểu dữ liệu nào bạn cần và cách thiết kế bảng điều khiển.

Các nguồn dữ liệu là gì?

Luôn biết dữ liệu đến từ đâu và ở định dạng nào. Trong một trong các dự án của tôi, dữ liệu được cung cấp dưới dạng tệp PDF bằng tiếng Tây Ban Nha. Điều này đã thay đổi hoàn toàn phạm vi và hầu hết thời gian của chúng tôi bị hút vào việc hủy dữ liệu thủ công. Dưới đây là những câu hỏi bạn nên đặt ra: Ai sở hữu dữ liệu? Trong định dạng nào bạn sẽ nhận được dữ liệu? Làm thế nào thường xuyên cập nhật dữ liệu?

Ai sẽ sử dụng Bảng điều khiển Excel này?

Người quản lý có thể chỉ quan tâm đến những hiểu biết mà bảng điều khiển của bạn cung cấp, tuy nhiên, một số nhà phân tích dữ liệu trong nhóm của anh ta có thể cần một cái nhìn chi tiết hơn. Dựa vào người sử dụng bảng điều khiển của bạn, bạn cần cấu trúc dữ liệu và đầu ra cuối cùng.

Bảng điều khiển Excel cần được cập nhật thường xuyên như thế nào?

Nếu bảng điều khiển của bạn được cập nhật hàng tuần hoặc hàng tháng, tốt hơn hết bạn nên tạo mô hình plug-and-play (nơi bạn chỉ cần sao chép dán dữ liệu và nó sẽ tự động cập nhật). Nếu nó chỉ là một bài tập một lần duy nhất, bạn có thể bỏ qua một số tự động hóa và làm điều đó bằng tay.

Khách hàng / các bên liên quan sử dụng phiên bản nào của Office?

Tốt hơn hết là đừng cho rằng khách hàng / các bên liên quan có phiên bản MS Office mới nhất. Tôi đã từng tạo một bảng điều khiển chỉ để biết rằng các bên liên quan của tôi đã sử dụng Excel 2003. Điều này dẫn đến một số thao tác lại khi hàm IFERROR không hoạt động trong phiên bản Excel 2003 (mà tôi đã sử dụng rộng rãi khi tạo bảng điều khiển).

Lấy dữ liệu trong Excel

Khi bạn có một ý tưởng tốt về những gì bạn cần tạo, các bước tiếp theo là bắt tay vào dữ liệu và lấy nó trong Excel.

Cuộc sống của bạn thật dễ dàng khi khách hàng của bạn cung cấp cho bạn Dữ liệu trong Excel, tuy nhiên, nếu đó không phải là trường hợp, bạn cần tìm ra một cách hiệu quả để có được nó trong Excel.

Nếu bạn cung cấp các tệp CSV hoặc tệp Văn bản, bạn có thể dễ dàng chuyển đổi các tệp này trong Excel. Nếu bạn có quyền truy cập vào cơ sở dữ liệu lưu trữ dữ liệu, bạn có thể tạo kết nối và cập nhật gián tiếp.

Làm sạch nó và chuẩn hóa nó.

Ví dụ, bạn có thể cần phải loại bỏ các khoảng trắng hàng đầu, dấu hoặc kép, tìm và xóa các bản sao, xóa khoảng trống và lỗi, v.v.

Trong một số trường hợp, bạn thậm chí có thể cần cơ cấu lại dữ liệu (ví dụ: bạn cần tạo bảng Pivot).

Các bước này sẽ phụ thuộc vào dự án và dữ liệu của bạn trông như thế nào trong Excel.

Phác thảo cấu trúc của Bảng điều khiển

Khi bạn có dữ liệu trong Excel, bạn sẽ biết chính xác những gì bạn có thể và không thể sử dụng trong bảng điều khiển Excel của mình.

Ở giai đoạn này, một ý tưởng hay là khoanh tròn lại với các bên liên quan của bạn với một phác thảo về bảng điều khiển Excel.

Như một cách thực hành tốt nhất, tôi tạo một phác thảo đơn giản trong PowerPoint cùng với các ghi chú bổ sung. Mục đích của bước này là để đảm bảo các bên liên quan của bạn hiểu loại bảng điều khiển nào anh ấy / cô ấy có thể mong đợi với dữ liệu có sẵn.

Nó cũng có ích khi các bên liên quan có thể đề xuất các thay đổi sẽ tăng thêm giá trị cho anh ta.

Dưới đây là một ví dụ về phác thảo mẫu tôi đã tạo cho một trong các bảng điều khiển KPI:

Bắt đầu tạo bảng điều khiển Excel.

Cách tốt nhất là chia sổ làm việc Excel của bạn thành ba phần (đây là các bảng tính mà tôi tạo có cùng tên):

• Dữ liệu – Đây có thể là một hoặc nhiều bảng tính chứa dữ liệu thô.
• Tính toán – Đây là nơi bạn thực hiện tất cả các tính toán. Một lần nữa, bạn có thể có một hoặc nhiều hơn một tờ để tính toán.
• Dashboard (Bảng điều khiển) – Đây là bảng có bảng điều khiển. Trong hầu hết các trường hợp, đó là một chế độ xem trang duy nhất hiển thị phân tích / thông tin chi tiết được hỗ trợ bởi dữ liệu.

Cách vẽ biểu đồ đo lường (Gauge)

Bảng Excel – Sốt bí mật của Bảng điều khiển Excel hiệu quả

Điều đầu tiên tôi làm với dữ liệu thô là chuyển đổi nó thành Bảng Excel. Bảng Excel cung cấp nhiều lợi thế rất quan trọng trong khi tạo bảng điều khiển Excel.

Để chuyển đổi dữ liệu dạng bảng thành bảng Excel, chọn dữ liệu và chuyển đến tab Chèn và nhấp vào biểu tượng Bảng.

Dưới đây là những lợi ích của việc sử dụng Bảng Excel cho bảng điều khiển của bạn:

• Khi bạn chuyển đổi một tập dữ liệu dạng bảng thành một bảng Excel, bạn không cần phải lo lắng về việc dữ liệu sẽ bị thay đổi ở giai đoạn sau. Ngay cả khi bạn nhận được dữ liệu bổ sung, bạn chỉ cần thêm nó vào bảng mà không phải lo lắng về các công thức bị sai sót. Điều này thực sự hữu ích khi tôi tạo bảng điều khiển plug-and-play.
• Với Bảng Excel, bạn có thể sử dụng tên của các cột thay vì tham chiếu. Ví dụ: thay vì là C2: C1000, bạn có thể sử dụng “Doanh số”.

Các hàm Excel quan trọng cho Bảng điều khiển

Bạn có thể tạo rất nhiều bảng điều khiển Excel tương tác tốt chỉ bằng cách sử dụng các công thức Excel.

Khi bạn thực hiện lựa chọn từ danh sách thả xuống hoặc sử dụng thanh cuộn hoặc chọn hộp kiểm, có các công thức cập nhật dựa trên kết quả và cung cấp cho bạn dữ liệu / chế độ xem được cập nhật trong bảng điều khiển.

Chức năng Excel hàng đầu cho bảng Dashboard Excel:

Hàm SUMPRODVEL

Nó khởi động chức năng yêu thích của tôi trong khi tạo bảng điều khiển Excel tương tác. Nó cho phép tôi thực hiện các phép tính phức tạp khi có nhiều biến. Ví dụ: giả sử tôi có bảng điều khiển bán hàng và tôi muốn biết doanh số bán hàng của đại diện Bob trong quý thứ ba ở khu vực phía Đông. Tôi chỉ đơn giản có thể tạo công thức SUMPRODVEL cho việc này.

Hàm INDEX / MATCH

Tôi là người đề xuất sử dụng kết hợp công thức INDEX và MATCH để tìm kiếm dữ liệu trong Bảng điều khiển Excel. Bạn cũng có thể sử dụng chức năng VLOOKUP, nhưng tôi thấy INDEX / MATCH là lựa chọn tốt hơn.

Hàm IFERROR

Khi thực hiện tính toán trên dữ liệu thô, bạn sẽ thường gặp lỗi. Tôi sử dụng IFERROR rộng rãi để ẩn lỗi trong bảng điều khiển (và nhiều lần trong dữ liệu thô).

Chức năng TEXT

Nếu bạn muốn tạo tiêu đề động hoặc tiêu đề, bạn cần sử dụng chức năng TEXT cho nó.

Hàm ROWS / COLUMNS

Tôi thường sử dụng những công cụ này khi tôi phải sao chép một công thức và một trong các đối số cần tăng khi chúng ta đi xuống / bên phải của ô

Những điều cần lưu ý khi làm việc với các công thức trong Bảng điều khiển Excel:

• Tránh sử dụng các công thức dễ bay hơi (như OFFSET, NOW, TODAY, v.v.). Những điều này sẽ làm chậm sổ làm việc của bạn.
• Loại bỏ các công thức không cần thiết. Nếu bạn có một số công thức bổ sung trong bảng tính toán, hãy xóa chúng trong khi hoàn thiện bảng điều khiển.
• Sử dụng các cột trợ giúp vì nó có thể giúp bạn tránh các công thức mảng dài.
• Công cụ tương tác để làm cho bảng điều khiển Excel của bạn tuyệt vời

Những công cụ tương tác mà bạn có thể sử dụng để làm cho Dashboard Excel của bạn năng động và thân thiện với người dùng.

Dưới đây là một số trong số này tôi sử dụng thường xuyên:

1. Thanh cuộn

Sử dụng thanh cuộn để lưu bất động sản sổ làm việc của bạn. Ví dụ: nếu bạn có 100 hàng dữ liệu, bạn có thể sử dụng thanh cuộn chỉ có 10 hàng trong bảng điều khiển. Người dùng có thể sử dụng thanh cuộn nếu anh ấy / cô ấy muốn xem toàn bộ tập dữ liệu.

2. Hộp kiểm

Hộp kiểm cho phép bạn thực hiện các lựa chọn và cập nhật bảng điều khiển. Ví dụ: giả sử tôi có bảng điều khiển đào tạo và tôi là CEO của công ty, tôi muốn xem xét bảng điều khiển chung của công ty. Nhưng nếu tôi là trưởng phòng kinh doanh, tôi sẽ chỉ muốn nhìn vào hiệu suất của bộ phận của tôi. Trong trường hợp như vậy, bạn có thể tạo bảng điều khiển với các hộp kiểm cho các bộ phận khác nhau của công ty.

3. Danh sách thả xuống

Danh sách thả xuống cho phép người dùng thực hiện lựa chọn độc quyền. Bạn có thể sử dụng các lựa chọn thả xuống này để cập nhật bảng điều khiển. Ví dụ: nếu bạn đang hiển thị dữ liệu theo bộ phận, bạn có thể có tên bộ phận trong danh sách thả xuống

Sử dụng Biểu đồ Excel để trực quan hóa dữ liệu trong Bảng điều khiển Excel

Biểu đồ không chỉ làm cho bảng điều khiển Excel của bạn hấp dẫn trực quan mà còn giúp bạn dễ dàng sử dụng và diễn giải.

Một số mẹo trong khi sử dụng biểu đồ trong Bảng điều khiển Excel:

1. Chọn biểu đồ cơ bản

Excel cung cấp cho bạn rất nhiều tùy chọn biểu đồ và bạn cần sử dụng biểu đồ phù hợp.

Ví dụ: nếu bạn phải hiển thị xu hướng, bạn cần sử dụng biểu đồ đường, nhưng nếu bạn muốn làm nổi bật các giá trị thực tế, biểu đồ thanh / cột có thể là một lựa chọn đúng đắn.

Trong khi rất nhiều chuyên gia khuyên không nên sử dụng biểu đồ hình tròn, tôi sẽ đề nghị bạn sử dụng theo ý của mình. Nếu đối tượng của bạn đã quen nhìn thấy các biểu đồ hình tròn, bạn cũng có thể sử dụng các biểu đồ này.

2. Sử dụng biểu đồ kết hợp

Tôi đặc biệt khuyên bạn nên sử dụng biểu đồ kết hợp vì chúng cho phép người dùng so sánh các giá trị và rút ra những hiểu biết ý nghĩa. Ví dụ: bạn có thể hiển thị số liệu bán hàng dưới dạng biểu đồ cột và tăng trưởng dưới dạng biểu đồ đường.

3. Sử dụng biểu đồ động

Nếu bạn muốn cho phép người dùng thực hiện các lựa chọn và muốn biểu đồ cập nhật với nó, hãy sử dụng biểu đồ động. Bây giờ một biểu đồ động không là gì ngoài một biểu đồ thông thường có dữ liệu cập nhật ở mặt sau khi bạn thực hiện các lựa chọn.

1. Sử dụng Sparklines để làm cho dữ liệu của bạn có ý nghĩa hơn

Nếu bạn có nhiều dữ liệu trong bảng điều khiển / báo cáo của mình, bạn có thể xem xét sử dụng Sparklines để làm cho nó trực quan. Biểu đồ thu nhỏ là một biểu đồ nhỏ nằm trong một ô và có thể được tạo bằng bộ dữ liệu. Đây là hữu ích khi bạn muốn hiển thị một xu hướng theo thời gian và đồng thời tiết kiệm không gian trên bảng điều khiển của bạn. Dưới đây là hướng dẫn chi tiết về cách tạo và sử dụng Sparklines trong Excel:

2. Sử dụng màu tương phản để làm nổi bật dữ liệu

Đây là một mẹo biểu đồ chung, trong đó bạn nên làm nổi bật dữ liệu trong biểu đồ để nó dễ hiểu. Ví dụ: nếu bạn có dữ liệu bán hàng, bạn có thể đánh dấu năm có giá trị bán hàng thấp nhất bằng màu đỏ. Bạn có thể duyệt qua một số hướng dẫn biểu đồ của tôi ở đây.

Dashboard những điều nên và không nên (Do & Don’t)

Hãy để đầu tiên bắt đầu với từ Dont!

Dưới đây là một số điều tôi khuyên bạn nên tránh khi tạo bảng điều khiển Excel. Một lần nữa những điều này sẽ thay đổi dựa trên dự án và các bên liên quan của bạn nhưng có giá trị trong hầu hết các trường hợp.

1. Dont Patrick Làm lộn xộn Bảng điều khiển của bạn

Chỉ vì bạn có dữ liệu và biểu đồ không có nghĩa là nó nên đi trong bảng điều khiển của bạn. Hãy nhớ mục tiêu của bảng điều khiển là giúp xác định một vấn đề hoặc hỗ trợ trong việc đưa ra quyết định. Vì vậy, giữ cho nó có liên quan và loại bỏ tất cả mọi thứ không thuộc về nó. Tôi thường tự hỏi nếu một cái gì đó tốt chỉ cần phải hoàn toàn phải có. Tôi đi trước và loại bỏ tất cả những người tốt.

2. Dont Tiết sử dụng các công thức dễ bay hơi

Vì nó sẽ làm chậm các tính toán của bạn.

3. Don Patrick giữ thêm dữ liệu trong sổ làm việc của bạn

Nếu bạn cần dữ liệu đó, hãy tạo một bản sao của bảng điều khiển và giữ nó làm bản sao lưu.

Những cái nhìn về một Do(hoặc thực hành tốt nhất)

1. Đánh số biểu đồ / Phần của bạn

Bảng điều khiển của bạn không chỉ là một tập hợp ngẫu nhiên các biểu đồ và điểm dữ liệu. Thay vào đó, nó là một câu chuyện trong đó một điều dẫn đến điều khác. Bạn cần đảm bảo khán giả của mình thực hiện theo các bước theo đúng thứ tự và do đó, tốt nhất là đánh số này. Mặc dù bạn có thể hướng dẫn họ khi bạn trình bày trực tiếp, nhưng đó là một sự trợ giúp tuyệt vời khi ai đó sử dụng bảng điều khiển của bạn ở giai đoạn sau hoặc in ra từ đó.

2. Hạn chế di chuyển trong khu vực bảng điều khiển

Ẩn tất cả các hàng / cột để đảm bảo người dùng không vô tình cuộn đi.

3. Đóng băng các hàng / cột quan trọng

Sử dụng các ô đóng băng khi bạn muốn một số hàng / cột luôn hiển thị trên bảng điều khiển.

4. Tạo hình dạng / Biểu đồ dính

Đảm bảo hình dạng / biểu đồ hoặc điều khiển tương tác của bạn không che giấu hoặc thay đổi kích thước khi ai đó ẩn / thay đổi kích thước các ô. Bạn cũng có thể sử dụng công cụ Máy ảnh Excel để chụp nhanh các biểu đồ / bảng và đặt nó vào bảng điều khiển Excel (những hình ảnh này là động và cập nhật khi cập nhật biểu đồ / bảng mặt sau).

5. Cung cấp Hướng dẫn sử dụng

Nếu bạn có bảng điều khiển phức tạp, thì đó là một ý tưởng hay để tạo một bảng tính riêng và làm nổi bật các bước. Nó sẽ giúp mọi người sử dụng bảng điều khiển của bạn ngay cả khi bạn không ở đó.

6. Tiết kiệm không gian với biểu đồ kết hợp

Sử dụng biểu đồ kết hợp (như biểu đồ dấu đầu dòng, biểu đồ nhiệt kế và biểu đồ thực tế so với biểu đồ đích) để tiết kiệm không gian trang tính của bạn.

7. Sử dụng Biểu tượng & Định dạng có điều kiện

Sử dụng biểu tượng và định dạng có điều kiện để thêm một lớp phân tích vào bảng điều khiển của bạn (nhưng không nên làm điều đó).

Theo Trump Excel