Power BI phổ biến cho các báo cáo và bảng thông tin tương tác. Nhưng là một người yêu thích Excel, tôi tự hỏi:“Excel có thể đi được bao xa nếu tôi cố gắng tạo lại báo cáo Power BI?”. Tôi đã cố gắng tạo lại một báo cáo bán hàng Power BI điển hình trong Excel bằng cách sử dụng Power Query, Mô hình dữ liệu (Power Pivot), các thước đo DAX, PivotTable/Biểu đồ, Bộ cắt/Dòng thời gian và một số cải tiến về bố cục.
Trong bài viết này, tôi sẽ chỉ ra cách tôi đã cố gắng xây dựng lại báo cáo Power BI trong Excel và nơi báo cáo bị lỗi cũng như nơi không thành công.
Báo cáo tôi đã cố gắng tạo lại
Tổng quan về doanh số tiêu chuẩn với:
- Thẻ KPI: Tổng doanh thu, Tổng lợi nhuận, Tỷ suất lợi nhuận, v.v.
- Đường xu hướng: Doanh số bán hàng theo tháng.
- Phân tích: Doanh số bán hàng theo danh mục, theo khu vực, v.v.
- Tương tác: Bộ chia theo Năm, Danh mục, Khu vực và dòng thời gian ngày.
- Đi sâu vào chi tiết: Chế độ xem chi tiết được lọc theo các lựa chọn hiện tại.
Loại bảng thông tin này thường được sử dụng; nhiều nhóm xây dựng trong Power BI. Hãy phản chiếu nó trong Excel. Mục đích là để xem liệu tôi có thể tái tạo cả chức năng và trải nghiệm người dùng trong Excel hay không.
Bước 1:Tải và làm sạch bằng Power Query (Nhận &Chuyển đổi)
- Đi tới Dữ liệu tab>> chọn Nhận dữ liệu >> chọn Từ văn bản/CSV hoặc các nguồn khác.
- Duyệt qua và chọn tệp rồi nhập tệp.
- Nhấp vào Tải hoặc Chuyển đổi dữ liệu để làm sạch tập dữ liệu.
- Trong Trình soạn thảo Power Query , đặt đúng loại (Ngày, Số nguyên, Tiền tệ).
- Làm sạch dữ liệu của bạn bằng các bản sửa lỗi nhỏ:cắt bớt văn bản, chuẩn hóa cách viết hoa, v.v.
- Nhấp vào Đóng và tải vào… để nhập dữ liệu.
- Sau đó chọn các tùy chọn cần thiết;
- Bảng hoặc Chỉ tạo kết nối .
- Thêm dữ liệu này vào Mô hình dữ liệu .
- Nhấp vào OK .
- Nhập tất cả dữ liệu theo các bước tương tự.
Trường hợp Excel thành công:
- Chức năng Power Query giống hệt với Power BI.
- Khả năng chuyển đổi dữ liệu rất mạnh mẽ.
- Nhiều kết nối nguồn dữ liệu hoạt động tốt.
Trường hợp Excel bị lỗi:
- Không có tùy chọn làm mới tăng dần.
- Giới hạn ở 1 triệu hàng trên mỗi trang tính.
- Hiệu suất chậm hơn với tập dữ liệu lớn.
- Yêu cầu làm mới thủ công (không cần lập lịch cổng).
Bước 2:Xây dựng mối quan hệ trong Mô hình dữ liệu (Power Pivot)
- Đi tới Dữ liệu tab>> chọn Quản lý mô hình dữ liệu .
- Hoặc đi tới Power Pivot tab>> chọn Quản lý .
- Thêm mối quan hệ:
- Doanh số[ProductID] → Sản phẩm[ProductID]
- Doanh số[CustomerID] → Khách hàng[CustomerID]
- Khách hàng[RegionID] → Khu vực[RegionID]
- Ẩn khóa và cột kỹ thuật khỏi công cụ máy khách để duy trì danh sách trường rõ ràng.
Power BI: Nó tự động phát hiện và tạo mối quan hệ. Để điều chỉnh thêm, bạn có thể kéo và thả để tạo mối quan hệ.
Excel: Power Pivot cung cấp mô hình mối quan hệ tương tự. Nó có vẻ giống như một giao diện phức tạp hơn để quản lý các mối quan hệ.
Bước 3: Tạo thước đo DAX
Trong Mô hình Dữ liệu, bạn có thể tạo các thước đo DAX. Bạn có thể tạo các thước đo DAX trong thanh công thức Power Pivot hoặc bạn có thể sử dụng tùy chọn tính toán.
- Truy cập Power Pivot>> từ Tính toán>> chọn Biện pháp>> chọn Biện pháp mới .
- Chèn tên thước đo và công thức.
- Nhấp vào OK .
Total Sales = SUM(Sales[SalesAmount]) Total Cost = SUM(Sales[Cost]) Profit = [Total Sales] - [Total Cost] Profit Margin % = DIVIDE([Profit], [Total Sales]) Average Order Value = DIVIDE([Total Sales], DISTINCTCOUNT(Sales[OrderID]))
- Xem trước công thức và đưa ra kết quả trong cửa sổ Power Pivot.
Power BI: Các thước đo DAX hoạt động giống nhau. Chức năng thông minh về thời gian sẽ sáng lên ngay khi bạn có bảng Lịch phù hợp liên quan đến Ngày.
Bước 4: Xây dựng lớp báo cáo bằng PivotTable/Biểu đồ
- Chèn một PivotTable được kết nối với Mô hình Dữ liệu.
- Đi tới Chèn tab>> chọn PivotTable>> chọn Mô hình dữ liệu .
- Chọn vị trí:Hiện tại hoặc Bảng tính mới .
- PivotTable được tạo trong trang tính mới.
Lưu ý: Hãy nhớ tạo các hình ảnh trực quan khác nhau thì bạn phải tạo các PivotTable riêng biệt. Một PivotTable không chứa nhiều hình ảnh trực quan khác nhau, điều này gây tốn thời gian.
Tạo hình ảnh:
Thẻ KPI:
- Từ Trường PivotTable;
- Kéo thước đo trong Giá trị .
- Định dạng phông chữ lớn; ẩn tổng cuối/tổng phụ.
Tạo biểu đồ tương tác:
- Tạo một PivotTable khác từ Mô hình Dữ liệu.
- Doanh số theo tháng:
- Kéo Ngày trên Hàng .
- Kéo Số tiền bán hàng trong Giá trị .
- Chọn PivotTable.
- Đi tới PivoTAnalyze tab>> chọn PivotChart >> chọn Biểu đồ dạng đường.
- Nhấp vào OK .
- Biểu đồ này cung cấp trải nghiệm chi tiết bằng cách sử dụng Ngày thứ bậc.
- Doanh số theo danh mục/Khu vực/Kênh :PivotCharts cột được nhóm.
- Kéo Danh mục trên Hàng .
- Kéo Số lượng bán hàng về Giá trị.
- Doanh số theo Tiểu bang/Tỉnh: Pie PivotCharts.
- Kéo Tiểu bang/Tỉnh trên Hàng .
- Kéo Số lượng bán hàng về Giá trị.
- Bản đồ: Bạn sẽ cần tạo biểu đồ Bản đồ từ dữ liệu tĩnh.
- Nhóm Quốc gia và SalesAmount của nó , sau đó sử dụng Bản đồ đã điền của Excel biểu đồ.
Power BI: Power BI cung cấp khả năng trực quan hóa ở cấp độ tiếp theo với khả năng tương tác. Nó cung cấp các hình ảnh cốt lõi giống nhau, các bộ cắt toàn cục với bố cục gọn gàng. Nó có các KPI tích hợp với đèn/biểu tượng giao thông được gắn với các ngưỡng (không cần định dạng có điều kiện phức tạp)
Trường hợp Excel thành công:
- Kiểm soát định dạng biểu đồ chi tiết hơn.
- Khả năng chú thích và văn bản tốt hơn.
- Các loại biểu đồ tùy chỉnh thông qua biểu đồ kết hợp.
- Chức năng biểu đồ thu nhỏ gốc.
Trường hợp Excel bị lỗi:
- Không có hình ảnh trực quan hóa bản đồ gốc.
- Các loại hình ảnh tương tác bị hạn chế.
- Không có chú giải biểu đồ tự động có bộ lọc.
- Yêu cầu định cỡ và định vị thủ công.
- Không có khả năng thiết kế đáp ứng.
Bước 5:Tạo tính tương tác
- Thêm Bộ cắt (Năm, Danh mục, Khu vực) và Dòng thời gian (Ngày).
- Đi tới Phân tích PivotTable tab>> chọn Chèn Bộ cắt/Dòng thời gian .
- Bộ cắt đồng bộ hóa trên tất cả các PivotTable.
- Chuyển đến Bộ cắt tab>> chọn Báo cáo kết nối .
- Hoặc nhấp chuột phải vào Bộ cắt >> chọn Báo cáo kết nối .
- Chọn tất cả PivotTable và nhấp vào OK .
Power BI: Nhấp vào bất kỳ hình ảnh nào để tự động lọc tất cả những hình ảnh khác. Ngoài ra, nó còn cung cấp các bộ cắt độc lập cùng với các tùy chọn định dạng.
Excel: Máy cắt hoạt động tốt cho việc lọc cơ bản. Nhiều bộ cắt có thể điều khiển nhiều PivotTable, cùng với các kết nối; họ có thể tạo kết nối với các PivotTable khác. Nhưng không có bộ lọc trực quan chéo (nhấp vào biểu đồ sẽ không lọc những biểu đồ khác). Khả năng xem chi tiết bị hạn chế và không có trạng thái đánh dấu trang hoặc chế độ xem.
Bước 6:Sắp xếp tất cả hình ảnh để tạo báo cáo
Trong Power BI, bạn có thể tạo tất cả hình ảnh trực quan trong trang báo cáo; sau đó, bạn chỉ cần sắp xếp lại chúng, duy trì dòng chảy chiến lược. Trong Excel, vì chúng tôi đã tạo nhiều PivotTable, Bộ cắt, biểu đồ nên bạn sẽ cần sao chép và sắp xếp những thứ này thành một hoặc nhiều trang tính.
- Chọn hình ảnh.
- Nhấp chuột phải>> chọn Sao chép .
- Chọn vị trí trong bảng báo cáo>> chọn Dán .
- Định dạng tất cả hình ảnh để phản chiếu giao diện Power BI.
- Excel cung cấp nhiều tùy chọn định dạng.
- Thử tính tương tác bằng cách chọn Bộ cắt.
Hãy nhớ rằng tất cả các PivotTable này sẽ làm chậm hiệu suất. Thời gian tải sẽ tăng lên đáng kể.
Điều gì hoạt động hiệu quả một cách đáng ngạc nhiên trong Excel
- Nhập dữ liệu :Power Query tương tự như Power BI.
- Lập mô hình &DAX :Lược đồ hình sao, thông tin thời gian và các thước đo DAX giống như Power BI.
- Hình ảnh cốt lõi :Các đường, thanh, bảng, thẻ kiểu KPI và thậm chí cả bản đồ đơn giản đều chắc chắn.
- Khám phá chi tiết :Việc bấm đúp vào một ô Pivot để “Hiển thị chi tiết” (xem chi tiết về các hàng) rất tiện lợi và một trang Chi tiết được quản lý sẽ giúp nó luôn gọn gàng.
- Hiệu suất (kích thước hợp lý) :Mô hình Dữ liệu có tốc độ nhanh đối với các bộ dữ liệu phòng ban điển hình.
Vị trí Excel kém (và tại sao nó lại quan trọng)
- Đánh dấu chéo giữa các biểu đồ:
- Power BI cho phép bạn nhấp vào một thanh trong một hình ảnh để lọc chéo và đánh dấu chéo các thanh khác.
- Bộ cắt của Excel lọc dựa trên các kết nối, nhưng các biểu đồ không làm nổi bật nhau một cách linh hoạt.
- Chú giải công cụ và thông tin chi tiết cấp trang:
- Các trang chi tiết và Chú giải công cụ trang báo cáo của Power BI cung cấp các phân tích có hướng dẫn.
- Excel không có tính năng đó, nhưng bạn có thể sử dụng siêu liên kết đến một trang tính chi tiết và các bộ cắt được dán nhãn rõ ràng, tuy nhiên việc này vẫn phải thực hiện thủ công.
- Thông số trường động (bộ chuyển đổi kích thước/đo lường):
- Các thông số trường của Power BI thật thú vị.
- Trong Excel, bạn có thể bắt chước các bảng bị ngắt kết nối + SELECTEDVALUE + SWITCH, nhưng nó cồng kềnh và khó duy trì hơn đối với người dùng cuối.
- Truy vấn trực tiếp, Mô hình tổng hợp, Làm mới tăng dần :
- Power BI có thể truy vấn các chương trình phụ trợ lớn mà không cần nhập mọi thứ, kết hợp các nguồn và làm mới dần dần.
- Mô hình dữ liệu của Excel chỉ nhập và làm mới hoàn toàn hoặc không có gì.
- Thân thiện với thiết bị di động, Dấu trang, Tường thuật:
- Các câu chuyện được đánh dấu trang, thủ thuật ngăn lựa chọn và bố cục trên thiết bị di động của Power BI không được dịch.
- Excel yêu cầu VBA/siêu liên kết cho mọi mục đích ngoài việc điều hướng đơn giản.
Hướng dẫn đưa ra quyết định nhanh chóng
Chọn Excel:
- Excel phù hợp hơn cho việc phân tích khi một nhóm nhỏ báo cáo về OneDrive/SharePoint nhưng không thay thế các ứng dụng hoặc chế độ xem trên thiết bị di động.
- Bạn cần các phép tính linh hoạt và nâng cao cùng với hình ảnh (ô giả định, LAMBDA/LET, v.v.).
Chọn Power BI:
- Nếu bạn cần chia sẻ rộng rãi, làm mới theo lịch và kiểm soát quyền truy cập theo từng đối tượng thì Dịch vụ Power BI rõ ràng là lựa chọn phù hợp.
- Nó cung cấp tính năng đánh dấu chéo, xem chi tiết, chú giải công cụ, dấu trang và bố cục trên thiết bị di động. Nó có thể chi phối việc phân phối ứng dụng và việc sử dụng các chỉ số.
Kết luận
Excel là một công cụ tuyệt vời và nó đáp ứng nhu cầu của người dùng từ cơ bản đến nâng cao. Nó có thể phản chiếu hoặc xây dựng lại một phần lớn của báo cáo Power BI điển hình, đặc biệt là ETL, lược đồ sao, DAX và hình ảnh cốt lõi. Trường hợp thất bại là kể chuyện mang tính tương tác, chia sẻ có quản lý và mô hình/làm mới ở quy mô doanh nghiệp. Nếu người dùng và các bên liên quan của bạn sống trong Excel và phạm vi được bao gồm thì Excel là một bề mặt báo cáo hoàn toàn đáng tin cậy. Nhưng nếu bạn cần tường thuật về lượt nhấp qua, phân phối an toàn hoặc kết nối trực tiếp trên quy mô lớn, hãy sử dụng Power BI.
Nhận MIỄN PHÍ Bài tập Excel nâng cao có Giải pháp!