Lợi ích của Pivot theo mô hình dữ liệu

Khi xây dựng bảng tổng hợp trong Excel, trong hộp thoại đầu tiên, nơi chúng tôi được yêu cầu đặt phạm vi ban đầu và chọn vị trí để chèn bảng tổng hợp, có một hộp kiểm không dễ thấy nhưng rất quan trọng bên dưới: Thêm dữ liệu này vào Mô hình dữ liệu (Thêm dữ liệu này sang Mô hình Dữ liệu) và cao hơn một chút, công tắc Sử dụng mô hình dữ liệu của cuốn sách này (Sử dụng Mô hình Dữ liệu của sổ làm việc này):

Thật không may, nhiều người dùng đã quen thuộc với bảng pivot trong một thời gian dài và sử dụng thành công chúng trong công việc của họ đôi khi không thực sự hiểu ý nghĩa của các tùy chọn này và không bao giờ sử dụng chúng. Và vô ích. Rốt cuộc, việc tạo bảng tổng hợp cho Mô hình dữ liệu mang lại cho chúng ta một số lợi thế rất quan trọng so với bảng tổng hợp Excel cổ điển.

Tuy nhiên, trước khi xem xét kỹ những “cái bánh” này, trước tiên chúng ta hãy hiểu trên thực tế, Mô hình Dữ liệu này là gì?

Mô hình dữ liệu là gì

Mô hình dữ liệu (viết tắt là MD hoặc DM = Data Model) là một vùng đặc biệt bên trong tệp Excel nơi bạn có thể lưu trữ dữ liệu dạng bảng - một hoặc nhiều bảng được liên kết với nhau, nếu muốn, nếu muốn. Trên thực tế, đây là một cơ sở dữ liệu nhỏ (khối OLAP) được nhúng bên trong sổ làm việc Excel. So với việc lưu trữ dữ liệu cổ điển dưới dạng bảng thông thường (hoặc thông minh) trên chính trang tính của Excel, thì Mô hình Dữ liệu có một số ưu điểm đáng kể:

• Bàn có thể lên đến 2 tỷ dòngvà một trang tính Excel có thể chứa nhiều hơn 1 triệu.
• Mặc dù kích thước khổng lồ, việc xử lý các bảng như vậy (lọc, sắp xếp, tính toán trên chúng, tóm tắt xây dựng, v.v.) được thực hiện rất nhanh Nhanh hơn nhiều so với chính Excel.
• Với dữ liệu trong Mô hình, bạn có thể thực hiện các phép tính bổ sung (nếu muốn, rất phức tạp) bằng cách sử dụng ngôn ngữ DAX tích hợp.
  
• Tất cả thông tin được tải vào Mô hình Dữ liệu rất nén mạnh bằng cách sử dụng một trình lưu trữ tích hợp sẵn đặc biệt và tăng kích thước của tệp Excel gốc một cách vừa phải.
  

Mô hình được quản lý và tính toán bởi một phần bổ trợ đặc biệt được tích hợp trong Microsoft Excel - powerpivotvề cái mà tôi đã viết. Để kích hoạt nó, trên tab nhà phát triển Nhấp chuột COM bổ trợ (Nhà phát triển - Phần bổ trợ COM) và chọn hộp thích hợp:

Nếu các tab nhà phát triển (Nhà phát triển)bạn không thể nhìn thấy nó trên ruy-băng, bạn có thể bật nó lên Tệp - Tùy chọn - Thiết lập ruy-băng (Tệp - Tùy chọn - Tùy chỉnh ruy-băng). Nếu trong cửa sổ hiển thị ở trên trong danh sách bổ trợ COM bạn không có Power Pivot, thì nó không được bao gồm trong phiên bản Microsoft Office của bạn 🙁

Trên tab Power Pivot xuất hiện, sẽ có một nút lớn màu xanh lục nhạt Quản lý (Quản lý), nhấp vào đó sẽ mở cửa sổ Power Pivot ở trên cùng của Excel, nơi chúng ta sẽ thấy nội dung của Mô hình Dữ liệu của cuốn sách hiện tại:

Một lưu ý quan trọng trong quá trình thực hiện: sổ làm việc Excel chỉ có thể chứa một Mô hình Dữ liệu.

Tải các bảng vào Mô hình Dữ liệu

Để tải dữ liệu vào Mô hình, trước tiên, chúng tôi biến bảng thành một phím tắt động "thông minh" Ctrl+T và đặt cho nó một cái tên thân thiện trên tab xây dựng (Thiết kế). Đây là bước bắt buộc.

Sau đó, bạn có thể sử dụng bất kỳ phương pháp nào trong ba phương pháp để chọn:

• Nhấn nút Thêm vào mô hình (Thêm vào Mô hình Dữ liệu) chuyển hướng powerpivot chuyển hướng Trang Chủ (Trang Chủ).
  
• Chọn đội Chèn - PivotTable (Chèn - Bảng tổng hợp) và bật hộp kiểm Thêm dữ liệu này vào Mô hình dữ liệu (Thêm dữ liệu này vào Mô hình Dữ liệu). Trong trường hợp này, theo dữ liệu được tải vào Mô hình, một bảng tổng hợp cũng được xây dựng ngay lập tức.
  
• Trên tab Nâng cao Ngày (Ngày) nhấn nút Từ Bảng / Phạm vi (Từ Bảng / Phạm vi)để tải bảng của chúng tôi vào trình soạn thảo Power Query. Đường dẫn này dài nhất, nhưng nếu muốn, tại đây bạn có thể thực hiện thêm việc dọn dẹp dữ liệu, chỉnh sửa và tất cả các loại chuyển đổi, trong đó Power Query rất mạnh.

  Sau đó, dữ liệu đã lược được tải lên Model bằng lệnh Trang chủ - Đóng và tải - Đóng và tải vào… (Trang chủ - Đóng & Tải - Đóng & Tải vào…). Trong cửa sổ mở ra, hãy chọn tùy chọn Chỉ cần tạo kết nối (Chỉ tạo kết nối) và quan trọng nhất, hãy đánh dấu Thêm dữ liệu này vào Mô hình dữ liệu (Thêm dữ liệu này vào Mô hình Dữ liệu).
  

Chúng tôi xây dựng bản tóm tắt về Mô hình dữ liệu

Để xây dựng Mô hình Dữ liệu tóm tắt, bạn có thể sử dụng bất kỳ phương pháp nào trong ba cách tiếp cận:

• nhấn nút bảng tóm tắt (Bảng tổng hợp) trong cửa sổ Power Pivot.
• Chọn lệnh trong Excel Chèn - PivotTable và chuyển sang chế độ Sử dụng mô hình dữ liệu của cuốn sách này (Chèn - Bảng tổng hợp - Sử dụng Mô hình Dữ liệu của sổ làm việc này).
• Chọn đội Chèn - PivotTable (Chèn - Bảng tổng hợp) và bật hộp kiểm Thêm dữ liệu này vào Mô hình dữ liệu (Thêm dữ liệu này vào Mô hình Dữ liệu). Bảng “thông minh” hiện tại sẽ được tải vào Mô hình và một bảng tóm tắt sẽ được xây dựng cho toàn bộ Mô hình.

Bây giờ chúng ta đã tìm ra cách tải dữ liệu vào Mô hình dữ liệu và xây dựng bản tóm tắt về nó, hãy cùng khám phá những lợi ích và lợi thế mà điều này mang lại cho chúng ta.

Lợi ích 1: Mối quan hệ giữa các bảng mà không cần sử dụng công thức

Một bản tóm tắt thông thường chỉ có thể được tạo bằng cách sử dụng dữ liệu từ một bảng nguồn. Nếu bạn có một vài trong số đó, chẳng hạn như bán hàng, bảng giá, danh bạ khách hàng, sổ đăng ký hợp đồng, v.v., thì trước tiên bạn sẽ phải thu thập dữ liệu từ tất cả các bảng thành một bằng cách sử dụng các hàm như VLOOKUP (Vlookup), MỤC LỤC (MỤC LỤC), ĐÃ ĐƯỢC XEM THÊM (CUỘC THI ĐẤU), TÓM TẮT (SUMIFS) và những thứ tương tự. Điều này kéo dài, tẻ nhạt và khiến Excel của bạn rơi vào tình trạng “suy nghĩ” với một lượng lớn dữ liệu.

Trong trường hợp tóm tắt Mô hình dữ liệu, mọi thứ đơn giản hơn nhiều. Chỉ cần thiết lập mối quan hệ giữa các bảng một lần trong cửa sổ Power Pivot là đủ - và nó đã hoàn tất. Để làm điều này, trên tab powerpivot Nhấn nút Quản lý (Quản lý) và sau đó trong cửa sổ xuất hiện - nút Xem biểu đồ (Xem sơ đồ). Nó vẫn là kéo các tên cột (khóa) chung (trường) giữa các bảng để tạo liên kết:

Sau đó, trong phần tóm tắt cho Mô hình Dữ liệu, bạn có thể đưa vào vùng tóm tắt (hàng, cột, bộ lọc, giá trị) bất kỳ trường nào từ bất kỳ bảng nào có liên quan - mọi thứ sẽ được liên kết và tính toán tự động:

Lợi ích 2: Đếm các giá trị duy nhất

Một bảng tổng hợp thông thường cho chúng ta cơ hội chọn một trong một số hàm tính toán được tích hợp sẵn: tổng, trung bình, đếm, tối thiểu, tối đa, v.v. Trong phần tóm tắt Mô hình Dữ liệu, một hàm rất hữu ích được thêm vào danh sách chuẩn này để đếm số lượng giá trị duy nhất (không lặp lại). Ví dụ: với sự trợ giúp của nó, bạn có thể dễ dàng đếm số lượng mặt hàng (phạm vi) hàng hóa duy nhất mà chúng tôi bán ở mỗi thành phố.

Nhấp chuột phải vào trường - lệnh Tùy chọn trường giá trị và trên tab hoạt động Chọn Số phần tử khác nhau (Số lượng riêng biệt):

Lợi ích 3: Công thức DAX tùy chỉnh

Đôi khi bạn phải thực hiện nhiều phép tính bổ sung khác nhau trong bảng tổng hợp. Trong tóm tắt thông thường, điều này được thực hiện bằng cách sử dụng các trường và đối tượng được tính toán, trong khi tóm tắt mô hình dữ liệu sử dụng các thước đo bằng ngôn ngữ DAX đặc biệt (DAX = Biểu thức phân tích dữ liệu).

Để tạo thước đo, hãy chọn trên tab powerpivot Lệnh Biện pháp - Tạo thước đo (Biện pháp - Biện pháp mới) hoặc chỉ cần bấm chuột phải vào bảng trong danh sách Trường Pivot và chọn Thêm số đo (Thêm số đo) trong menu ngữ cảnh:

Trong cửa sổ mở ra, hãy đặt:

• Tên bảngnơi mà thước đo đã tạo sẽ được lưu trữ.
• Đo tên - bất kỳ tên nào bạn hiểu cho lĩnh vực mới.
• Mô tả - không bắt buộc.
• Công thức - điều quan trọng nhất, vì ở đây chúng tôi nhập thủ công hoặc nhấp vào nút fx và chọn một hàm DAX từ danh sách, hàm này sẽ tính toán kết quả khi chúng ta đưa thước đo của mình vào vùng Giá trị.
• Ở phần dưới của cửa sổ, bạn có thể đặt ngay định dạng số cho số đo trong danh sách Phân loại.
  

Ngôn ngữ DAX không phải lúc nào cũng dễ hiểu vì hoạt động không phải với các giá trị riêng lẻ mà với toàn bộ các cột và bảng, tức là đòi hỏi một số cơ cấu lại tư duy sau các công thức Excel cổ điển. Tuy nhiên, nó rất đáng giá vì sức mạnh của khả năng xử lý một lượng lớn dữ liệu rất khó để đánh giá quá cao.

Lợi ích 4: Phân cấp trường tùy chỉnh

Thông thường, khi tạo báo cáo chuẩn, bạn phải ném các kết hợp trường giống nhau vào bảng tổng hợp theo một trình tự nhất định, chẳng hạn Năm-Quý-Tháng-Ngày, hoặc là Danh mục-Sản phẩm, hoặc là Quốc gia-Thành phố-Khách hàng v.v ... Trong phần tóm tắt Mô hình Dữ liệu, vấn đề này có thể dễ dàng giải quyết bằng cách tạo hệ thống phân cấp - bộ trường tùy chỉnh.

Trong cửa sổ Power Pivot, chuyển sang chế độ biểu đồ bằng nút Xem biểu đồ chuyển hướng Trang Chủ (Trang chủ - Xem sơ đồ), chọn với Ctrl các trường mong muốn và nhấp chuột phải vào chúng. Menu ngữ cảnh sẽ chứa lệnh Tạo cấu trúc phân cấp (Tạo hệ thống phân cấp):

Cấu trúc phân cấp đã tạo có thể được đổi tên và kéo vào đó bằng chuột các trường bắt buộc, để sau đó trong một chuyển động, chúng có thể được đưa vào tóm tắt:

Lợi ích 5: Giấy nến tùy chỉnh

Tiếp tục ý tưởng của đoạn trước, trong phần tóm tắt của Mô hình Dữ liệu, bạn cũng có thể tạo các tập hợp phần tử của riêng mình cho từng trường. Ví dụ: từ toàn bộ danh sách các thành phố, bạn có thể dễ dàng tạo một tập hợp chỉ những thành phố thuộc khu vực bạn phụ trách. Hoặc chỉ tập hợp khách hàng của bạn, hàng hóa của bạn, v.v. vào một tập hợp đặc biệt.

Để làm điều này, trên tab Phân tích bảng tổng hợp trong danh sách thả xuống Trường, Mục và Bộ có các lệnh tương ứng (Phân tích - Lĩnh vực, tôitems & Sets - Tạo tập hợp dựa trên các mục hàng / cột):

Trong cửa sổ mở ra, bạn có thể chọn lọc loại bỏ, thêm hoặc thay đổi vị trí của bất kỳ phần tử nào và lưu tập hợp kết quả dưới một tên mới:

Tất cả các tập hợp đã tạo sẽ được hiển thị trong bảng điều khiển Trường PivotTable trong một thư mục riêng biệt, từ đó chúng có thể được kéo tự do đến các vùng hàng và cột của bất kỳ PivotTable mới nào:

Lợi ích 6: Ẩn có chọn lọc các bảng và cột

Mặc dù đây là một lợi thế nhỏ, nhưng rất dễ chịu trong một số trường hợp. Bằng cách bấm chuột phải vào tên trường hoặc trên tab bảng trong cửa sổ Power Pivot, bạn có thể chọn lệnh Ẩn khỏi Bộ công cụ dành cho khách hàng (Ẩn khỏi Công cụ Máy khách):

Cột hoặc bảng ẩn sẽ biến mất khỏi ngăn Danh sách Trường PivotTable. Sẽ rất thuận tiện nếu bạn cần ẩn với người dùng một số cột phụ trợ (ví dụ: cột được tính toán hoặc cột có giá trị chính để tạo mối quan hệ) hoặc thậm chí toàn bộ bảng.

Lợi ích 7. Chi tiết nâng cao

If you double-click on any cell in the value area in a regular pivot table, then Excel displays on a separate sheet a copy of the source data fragment that was involved in the calculation of this cell. This is a very handy thing, officially called Drill-down (in they usually say “fail”).

Trong phần tóm tắt Mô hình Dữ liệu, công cụ tiện dụng này hoạt động tinh vi hơn. Bằng cách đứng trên bất kỳ ô nào có kết quả mà chúng tôi quan tâm, bạn có thể nhấp vào biểu tượng có hình kính lúp bật lên bên cạnh (nó được gọi là Xu hướng nhanh) và sau đó chọn bất kỳ trường nào bạn quan tâm trong bất kỳ bảng liên quan nào:

Sau đó, giá trị hiện tại (Model = Explorer) sẽ đi vào vùng lọc và bản tóm tắt sẽ được các văn phòng xây dựng:

Tất nhiên, quy trình như vậy có thể được lặp lại nhiều lần, liên tục đào sâu vào dữ liệu của bạn theo hướng bạn quan tâm.

Lợi ích 8: Chuyển đổi các hàm Pivot thành Cube

Nếu bạn chọn bất kỳ ô nào trong phần tóm tắt cho Mô hình Dữ liệu và sau đó chọn trên tab Phân tích bảng tổng hợp Lệnh Công cụ OLAP - Chuyển đổi sang công thức (Phân tích - Công cụ OLAP - Chuyển đổi thành công thức), sau đó toàn bộ tóm tắt sẽ được tự động chuyển đổi thành công thức. Giờ đây, các giá trị trường trong vùng hàng-cột và kết quả trong vùng giá trị sẽ được truy xuất từ ​​Mô hình Dữ liệu bằng cách sử dụng các hàm khối lập phương đặc biệt: CUBEVALUE và CUBEMEMBER:

Về mặt kỹ thuật, điều này có nghĩa là bây giờ chúng tôi không xử lý tóm tắt, nhưng với một số ô có công thức, tức là chúng tôi có thể dễ dàng thực hiện bất kỳ chuyển đổi nào với báo cáo của chúng tôi mà báo cáo không có sẵn trong tóm tắt, ví dụ: chèn hàng hoặc cột mới vào giữa của báo cáo, thực hiện bất kỳ tính toán bổ sung nào bên trong bản tóm tắt, sắp xếp chúng theo bất kỳ cách nào mong muốn, v.v.

Đồng thời, kết nối với dữ liệu nguồn, tất nhiên, vẫn còn và trong tương lai, các công thức này sẽ được cập nhật khi các nguồn thay đổi. Vẻ đẹp!

• Phân tích thực tế kế hoạch trong bảng tổng hợp với Power Pivot và Power Query
• Bảng tổng hợp có tiêu đề nhiều dòng
• Tạo cơ sở dữ liệu trong Excel bằng Power Pivot