PowerPivot cho Excel - Bảng tra cứu trong kho dữ liệu

Một trong những điều tôi liệt kê nhiều nhất về PowerPivot cho Excel là khả năng thêm bảng tra cứu vào tập dữ liệu của bạn. Hầu hết thời gian, dữ liệu bạn đang làm việc không có mọi lĩnh vực bạn cần để phân tích. Ví dụ: bạn có thể có trường ngày nhưng cần nhóm dữ liệu theo quý. Bạn có thể viết một công thức, nhưng sẽ dễ dàng hơn khi tạo một bảng tra cứu đơn giản trong môi trường PowerPivot.

Bạn cũng có thể sử dụng bảng tra cứu này cho một nhóm khác như tên tháng và nửa đầu / thứ hai của năm. Trong điều khoản lưu trữ dữ liệu, bạn thực sự đang tạo bảng thứ nguyên ngày. Trong bài viết này, tôi sẽ cung cấp cho bạn một vài bảng thứ nguyên ví dụ để nâng cao dự án PowerPivot cho Excel của bạn.

Bảng kích thước văn bản mới (tra cứu)

Hãy xem xét một bảng với dữ liệu đơn hàng (dữ liệu Contoso từ Microsoft bao gồm một bộ dữ liệu tương tự như thế này). Giả sử bảng có các trường cho khách hàng, ngày đặt hàng, tổng đơn đặt hàng và loại đơn đặt hàng. Chúng ta sẽ tập trung vào trường loại lệnh. Giả sử trường loại đơn hàng bao gồm các giá trị như:

• Netbook
• Máy tính để bàn
• Màn hình
• Máy chiếu
• Máy in
• Máy quét
• Máy ảnh kĩ thuật số
• Máy ảnh SLR kỹ thuật số
• Máy quay phim
• Máy quay phim
• Điện thoại văn phòng
• Điện thoại thông minh
• PDA
• Điện thoại cầm tay & Phụ kiện

Trong thực tế, bạn sẽ có mã cho những điều này nhưng để giữ ví dụ này đơn giản, giả sử đây là những giá trị thực tế trong bảng thứ tự.

Sử dụng PowerPivot cho Excel, bạn có thể dễ dàng nhóm các đơn đặt hàng của mình theo loại đơn đặt hàng. Nếu bạn muốn một nhóm khác thì sao? Ví dụ: giả sử bạn cần nhóm "danh mục" như máy tính, máy ảnh và điện thoại. Bảng thứ tự không có trường "danh mục", nhưng bạn có thể dễ dàng tạo bảng này dưới dạng bảng tra cứu trong PowerPivot dành cho Excel.

Bảng tra cứu mẫu hoàn chỉnh nằm bên dưới Bảng 1 . Dưới đây là các bước:

• Bước 1: Bạn cần danh sách riêng biệt từ trường loại cho bảng tra cứu của bạn. Đây sẽ là trường tra cứu của bạn. Từ tập dữ liệu của bạn, hãy tạo danh sách giá trị riêng biệt từ trường loại đơn đặt hàng. Nhập danh sách riêng biệt của "loại" vào sổ làm việc Excel. Gắn nhãn cho cột Type.
• Bước 2: Trong cột bên cạnh cột tra cứu của bạn (Type), thêm trường mới mà bạn muốn nhóm. Trong ví dụ của chúng tôi, thêm một cột có nhãn được gọi là Danh mục.
• Bước 3: Đối với mỗi giá trị trong danh sách giá trị riêng biệt của bạn (các loại trong ví dụ này), hãy thêm các giá trị "Danh mục" tương ứng. Trong ví dụ đơn giản của chúng tôi, nhập Máy tính, Máy ảnh hoặc Điện thoại vào cột Danh mục.
• Bước 4: Sao chép bảng dữ liệu Loại và Danh mục vào khay nhớ tạm của bạn.
• Bước 5: Mở sổ làm việc Excel với dữ liệu đơn đặt hàng trong PowerPivot dành cho Excel. Khởi chạy Cửa sổ PowerPivot. Nhấp vào Dán sẽ đưa vào bảng tra cứu mới của bạn. Đặt tên cho bảng và đảm bảo bạn chọn "Sử dụng hàng đầu tiên làm tiêu đề cột". Nhấp vào OK. Bạn đã tạo một bảng tra cứu trong PowerPivot.
• Bước 6: Tạo mối quan hệ giữa trường Loại trong bảng Thứ tự và trường Danh mục trong bảng tra cứu. Nhấp vào ribbon Thiết kế và chọn Tạo mối quan hệ. Thực hiện các lựa chọn trong hộp thoại Tạo mối quan hệ và nhấp vào Tạo.

Khi bạn tạo PivotTable trong Excel dựa trên dữ liệu PowerPivot, bạn sẽ có thể nhóm theo trường Danh mục mới của bạn. Hãy nhớ rằng PowerPivot dành cho Excel chỉ hỗ trợ Tham gia bên trong. Nếu bạn có một "loại lệnh" bị thiếu trong bảng tra cứu của bạn, tất cả các bản ghi tương ứng cho loại đó sẽ bị thiếu trong bất kỳ PivotTable nào dựa trên dữ liệu PowerPivot. Bạn sẽ cần phải kiểm tra điều này theo thời gian.

Bảng kích thước ngày (tra cứu)

Bảng tra cứu ngày sẽ rất cần thiết trong hầu hết các dự án PowerPivot cho Excel của bạn. Hầu hết các tập dữ liệu đều có một số loại trường ngày tháng. Có các hàm để tính năm và tháng.

Tuy nhiên, nếu bạn cần văn bản tháng thực tế hoặc quý, bạn cần phải viết một công thức phức tạp. Sẽ dễ dàng hơn khi bao gồm bảng Tham số ngày (tra cứu) và so khớp nó với số tháng trong tập dữ liệu chính của bạn. Bạn sẽ cần phải thêm một cột vào bảng thứ tự của bạn để biểu thị số tháng từ trường ngày đặt hàng. Công thức DAX cho "tháng" trong ví dụ của chúng tôi là "= MONTH ([Ngày đặt hàng]). Số này sẽ trả về một số từ 1 đến 12 cho mỗi bản ghi. Bảng thứ nguyên của chúng tôi sẽ cung cấp các giá trị thay thế, liên kết với số tháng. Bảng phân tích ngày đầy đủ mẫu nằm bên dưới trong Bảng 2 .

Thứ nguyên ngày hoặc bảng tra cứu sẽ bao gồm 12 bản ghi. Cột tháng sẽ có các giá trị 1 - 12. Các cột khác sẽ bao gồm văn bản tháng viết tắt, văn bản đầy đủ tháng, quý, v.v. Dưới đây là các bước:

• Bước 1: Sao chép bảng từ Bảng 2 dưới đây và dán vào PowerPivot. Bạn có thể tạo bảng này trong Excel nhưng tôi tiết kiệm thời gian cho bạn. Bạn sẽ có thể dán trực tiếp từ dữ liệu được chọn bên dưới nếu bạn đang sử dụng Internet Explorer. PowerPivot chọn định dạng bảng trong thử nghiệm của tôi. Nếu bạn đang sử dụng một trình duyệt khác, bạn có thể phải dán vào Excel trước và sao chép nó từ Excel để chọn định dạng bảng.
• Bước 2: Mở sổ làm việc Excel với dữ liệu đơn hàng trong PowerPivot dành cho Excel. Khởi chạy Cửa sổ PowerPivot. Nhấp vào Dán sẽ đưa vào bảng tra cứu của bạn được sao chép từ bảng bên dưới hoặc từ Excel. Đặt tên cho bảng và đảm bảo bạn chọn "Sử dụng hàng đầu tiên làm tiêu đề cột". Nhấp vào OK. Bạn đã tạo bảng tra cứu ngày trong PowerPivot.

• Bước 3 : Tạo mối quan hệ giữa trường Tháng trong bảng Thứ tự và trường MonthNumber trong bảng tra cứu. Nhấp vào ribbon Thiết kế và chọn Tạo mối quan hệ. Thực hiện các lựa chọn trong hộp thoại Tạo mối quan hệ và nhấp vào Tạo.

Một lần nữa, với việc bổ sung một thứ nguyên ngày, bạn sẽ có thể nhóm dữ liệu trong PivotTable của bạn bằng cách sử dụng bất kỳ giá trị khác nhau nào từ bảng tra cứu ngày. Nhóm theo quý hoặc tên của tháng sẽ là một snap.

Bảng thứ nguyên mẫu (Lookup)

Bảng 1

ban 2