Nội dung
Không chỉ có thể sử dụng các Slice trong bảng pivot theo cách cổ điển - để lọc dữ liệu nguồn mà còn để chuyển đổi giữa các kiểu tính toán khác nhau trong vùng giá trị:
Thực hiện điều này rất dễ dàng - tất cả những gì bạn cần là một vài công thức và một bảng bổ trợ. Chà, chúng tôi sẽ thực hiện tất cả những điều này không phải trong bản tóm tắt thông thường, mà trong bản tóm tắt được xây dựng theo Mô hình Dữ liệu Power Pivot.
Bước 1. Kết nối bổ trợ Power Pivot
Nếu các tab của phần bổ trợ Power Pivot không hiển thị trong Excel của bạn, trước tiên bạn cần phải bật nó. Có hai lựa chọn cho việc này:
- Tab nhà phát triển - cái nút COM bổ trợ (Nhà phát triển - Phần bổ trợ COM)
- Tệp - Tùy chọn - Phần bổ trợ - Phần bổ trợ COM - Bắt đầu (Tệp - Tùy chọn - Phần bổ trợ - COM-Phần bổ trợ - Đi tới)
Nếu điều này không hữu ích, hãy thử khởi động lại Microsoft Excel.
Bước 2: Tải dữ liệu vào mô hình dữ liệu Power Pivot
Chúng ta sẽ có hai bảng làm dữ liệu ban đầu:
Đầu tiên là một bảng với doanh số bán hàng, theo đó sau này chúng tôi sẽ xây dựng một bản tóm tắt. Bảng thứ hai là bảng bổ trợ, nơi nhập tên cho các nút của lát cắt trong tương lai.
Cả hai bảng này cần được chuyển đổi thành “thông minh” (động) bằng phím tắt Ctrl+T hoặc đội Trang chủ - Định dạng dưới dạng bảng (Trang chủ - Định dạng dưới dạng Bảng) và chúng tôi mong muốn đặt cho họ những cái tên lành mạnh trên tab xây dựng (Thiết kế). Ví dụ: Bán hàng и DỊCH VỤ.
Sau đó, lần lượt từng bảng cần được tải vào Mô hình Dữ liệu - đối với điều này, chúng tôi sử dụng trên tab powerpivot nút Thêm vào mô hình dữ liệu (Thêm vào Mô hình Dữ liệu).
Các trường được tính toán trong PivotTable theo Mô hình Dữ liệu được gọi là các biện pháp. Hãy tạo một thước đo sẽ hiển thị tên của nút được nhấn trên lát cắt trong tương lai. Để thực hiện việc này, trong bất kỳ bảng nào của chúng tôi, hãy chọn bất kỳ ô trống nào trong bảng tính toán phía dưới và nhập cấu trúc sau vào thanh công thức:
Ở đây, tên của biện pháp được đặt trước (Nút nhấn), và sau dấu hai chấm và dấu bằng, một công thức để tính nó bằng cách sử dụng hàm GIÁ TRỊ DAX được tích hợp trong Power Pivot.
Nếu bạn lặp lại điều này không phải trong Power Pivot mà trong Power BI, thì dấu hai chấm không cần thiết và thay vào đó GIÁ TRỊ bạn có thể sử dụng đối tác hiện đại hơn của nó - chức năng SELECTEDVALUE.
Chúng tôi không chú ý đến lỗi ở phần dưới của cửa sổ xuất hiện sau khi nhập công thức - chúng phát sinh, bởi vì chúng tôi chưa có một bản tóm tắt và một phần trong đó một cái gì đó được nhấp vào.
Bước tiếp theo là tạo một thước đo cho các tùy chọn tính toán khác nhau tùy thuộc vào giá trị của thước đo trước đó Nút nhấn. Ở đây, công thức phức tạp hơn một chút:
Hãy chia nhỏ nó ra từng phần:
- Chức năng TẮC - một chất tương tự của IF lồng nhau - kiểm tra việc thực hiện các điều kiện được chỉ định và trả về các giá trị khác nhau tùy thuộc vào việc thực hiện một số điều kiện trong số đó.
- Chức năng THẬT() - đưa ra giá trị lôgic “true” để các điều kiện được kiểm tra sau bởi hàm SWITCH chỉ hoạt động nếu chúng được đáp ứng, tức là sự thật.
- Sau đó, chúng tôi kiểm tra giá trị của số đo được nhấn nút và tính toán kết quả cuối cùng cho ba tùy chọn khác nhau - dưới dạng tổng chi phí, kiểm tra trung bình và số lượng người dùng duy nhất. Để đếm các giá trị duy nhất, hãy sử dụng hàm DISTINCTCOUNTvà để làm tròn - ROUND.
- Nếu không có điều kiện nào trong ba điều kiện trên được đáp ứng, thì đối số cuối cùng của hàm SWITCH sẽ được hiển thị - chúng tôi đặt nó làm đối số giả bằng cách sử dụng hàm TRỐNG().
Bước 5. Xây dựng một bản tóm tắt và thêm một lát cắt
Nó vẫn là để trở lại từ Power Pivot sang Excel và xây dựng một bảng tổng hợp ở đó cho tất cả dữ liệu và thước đo của chúng tôi. Để thực hiện việc này, trong cửa sổ Power Pivot bật Chính tab chọn lệnh bảng tóm tắt (Trang chủ - Bảng tổng hợp).
Thì:
- Chúng tôi ném sân Sản phẩm từ bảng Bán hàng đến khu vực Hàng (Hàng).
- Ném một cánh đồng ở đó Kết quả từ bảng DỊCH VỤ.
- Nhấp chuột phải vào trường Kết quảvà chọn một đội Thêm dưới dạng lát (Thêm dưới dạng Slicer).
- Ném thước đo thứ hai Kết luận từ bảng DỊCH VỤ đến khu vực Các giá trị (Giá trị).
Đây, trên thực tế, là tất cả các thủ thuật. Bây giờ bạn có thể nhấp vào các nút của bộ cắt - và các tổng trong bảng tổng hợp sẽ chuyển sang chức năng bạn cần.
Sắc đẹp 🙂
- Lợi ích của Pivot theo mô hình dữ liệu
- Phân tích kế hoạch-thực tế trong bảng tổng hợp trên Power Pivot
- Tạo cơ sở dữ liệu trong Excel bằng bổ trợ Power Pivot