Trong bài viết này, chúng ta sẽ xem xét những điều cơ bản về việc tạo công thức tính toán cho cả cột và số đo được tính trong Power Pivot. Nếu bạn mới dùng DAX, hãy chắc chắn bạn xem Khởi động Nhanh: Tìm hiểu Kiến thức cơ bản về DAX trong 30 Phút.
Kiến thức cơ bản về công thức
Power Pivot cung cấp Biểu thức Phân tích Dữ liệu (DAX) để tạo phép tính tùy chỉnh trong bảng Power Pivot và trong PivotTable Excel. DAX bao gồm một số hàm được sử dụng trong công thức Excel và các hàm bổ sung được thiết kế để làm việc với dữ liệu có quan hệ và thực hiện tổng hợp động.
Dưới đây là một số công thức cơ bản có thể dùng trong cột được tính:
| Công thức | Mô tả |
|---|---|
| =TODAY() | Chèn ngày hôm nay vào mọi hàng của cột. |
| =3 | Chèn giá trị 3 vào mọi hàng của cột. |
| =[Column1] + [Column2] | Cộng các giá trị trong cùng hàng của [Column1] và [Column2] và đặt kết quả vào cùng hàng của cột được tính. |
Bạn có thể tạo công thức Power Pivot cho các cột được tính toán giống như bạn tạo công thức trong Microsoft Excel.
Sử dụng các bước sau khi bạn tạo công thức:
- Mỗi công thức phải bắt đầu bằng dấu bằng.
- Bạn có thể nhập hoặc chọn tên hàm hoặc nhập biểu thức.
- Bắt đầu nhập vài chữ cái đầu tiên của hàm hoặc tên bạn muốn và Tự động Hoàn tất sẽ hiển thị danh sách các hàm, bảng và cột sẵn dùng. Nhấn TAB để thêm một mục từ danh sách Tự động Hoàn thành vào công thức.
- Bấm vào nút Fx để hiển thị danh sách các hàm sẵn dùng. Để chọn hàm từ danh sách thả xuống, hãy sử dụng các phím mũi tên để tô sáng mục, rồi bấm Ok để thêm hàm vào công thức.
- Cung cấp các đối số cho hàm bằng cách chọn chúng từ danh sách thả xuống gồm các bảng và cột khả thi, hoặc bằng cách nhập các giá trị hoặc một hàm khác.
- Kiểm tra lỗi cú pháp: đảm bảo rằng tất cả các dấu ngoặc đơn đều đóng, đồng thời tham chiếu chính xác cột, bảng và giá trị.
- Nhấn ENTER để chấp nhận công thức.
Lưu ý
Trong cột được tính, ngay khi bạn chấp nhận công thức, cột sẽ được điền các giá trị. Trong giá trị đo, thao tác nhấn ENTER sẽ lưu định nghĩa số đo.
Tạo một công thức đơn giản
| Để tạo cột được tính bằng công thức đơn giản Ngày Bán hàngThể loại phụSản phẩmBán hàngSố lượng1/5/2009Phụ kiệnBao đực254995681/5/2009Phụ kiệnBộ sạc pin mini1099.56441/5/2009DigitalSlim Digital6512441/6/2009Phụ kiệnỐng kính chuyển đổi tele1662.5181/6/2009Phụ kiệnChân máy938.34181/6/2009Phụ kiệnCáp USB1230.2526
|
|---|
Mẹo sử dụng tính năng Tự động Hoàn tất
- Bạn có thể sử dụng tính năng Tự động Hoàn tất Công thức ở giữa công thức hiện có với các hàm được lồng vào. Văn bản ngay trước điểm chèn được dùng để hiển thị các giá trị trong danh sách thả xuống và tất cả văn bản sau điểm chèn vẫn không thay đổi.
- Power Pivot không thêm dấu đóng ngoặc đơn của hàm hoặc tự động khớp các dấu ngoặc đơn. Bạn phải đảm bảo rằng từng hàm đều đúng về mặt cú pháp, nếu không bạn sẽ không thể lưu hay sử dụng công thức. Power Pivot sẽ tô sáng các dấu ngoặc đơn, giúp bạn dễ dàng kiểm tra xem chúng đã được đóng đúng cách hay chưa.
Làm việc với Bảng và Cột
Bảng Power Pivot trông giống như bảng Excel, nhưng khác nhau về cách chúng hoạt động với dữ liệu và với công thức:
- Công thức trong Power Pivot chỉ hoạt động với bảng và cột, không hoạt động với ô riêng lẻ, tham chiếu phạm vi hoặc mảng.
- Công thức có thể sử dụng mối quan hệ để lấy giá trị từ các bảng liên quan. Các giá trị được truy xuất luôn liên quan đến giá trị hàng hiện tại.
- Bạn không thể dán công thức Power Pivot vào trang tính Excel và ngược lại.
- Bạn không thể có dữ liệu bất thường hoặc "rách rưới" như trong một trang tính Excel. Mỗi hàng trong bảng phải chứa cùng số cột. Tuy nhiên, bạn có thể có các giá trị trống trong một số cột. Bảng dữ liệu Excel và bảng dữ liệu Power Pivot không thể hoán đổi cho nhau, nhưng bạn có thể nối kết đến bảng Excel từ Power Pivot và dán dữ liệu Excel vào Power Pivot. Để biết thêm thông tin, hãy xem Thêm dữ liệu trang tính vào Mô hình Dữ liệu bằng cách dùng bảng đã nối kết và Sao chép và dán các hàng vào Mô hình Dữ liệu trong Power Pivot.
Tham chiếu đến bảng và cột trong công thức và biểu thức
Bạn có thể tham chiếu đến bất kỳ bảng và cột nào bằng cách sử dụng tên của nó. Ví dụ: công thức sau đây minh họa cách tham chiếu đến cột từ hai bảng bằng cách sử dụng tên đầy đủ tiêu chuẩn:
=SUM('Doanh số Mới'[Số lượng]) + SUM('Doanh số Trong quá khứ'[Số lượng])
Khi một công thức được đánh giá, trước tiên Power Pivot sẽ kiểm tra cú pháp chung, sau đó kiểm tra tên của các cột và bảng mà bạn cung cấp so với các cột và bảng khả thi trong ngữ cảnh hiện tại. Nếu tên không rõ ràng hoặc nếu không tìm thấy cột hoặc bảng, bạn sẽ nhận được lỗi trên công thức (một chuỗi #ERROR thay vì một giá trị dữ liệu trong các ô xảy ra lỗi). Để biết thêm thông tin về các yêu cầu đặt tên cho bảng, cột và các đối tượng khác, hãy xem "Các yêu cầu Đặt tên trong Đặc tả Cú pháp DAX cho Power Pivot.
Lưu ý
Ngữ cảnh là một tính năng quan trọng của mô hình dữ liệu Power Pivot cho phép bạn tạo công thức động. Ngữ cảnh được xác định bởi các bảng trong mô hình dữ liệu, mối quan hệ giữa các bảng và bất kỳ bộ lọc nào đã được áp dụng. Để biết thêm thông tin, hãy xem Ngữ cảnh trong Công thức DAX.
Mối quan hệ Bảng
Các bảng có thể liên quan đến các bảng khác. Bằng cách tạo các mối quan hệ, bạn có thể tra cứu dữ liệu trong một bảng khác và dùng các giá trị liên quan để thực hiện các tính toán phức tạp. Ví dụ: bạn có thể sử dụng cột được tính để tra cứu tất cả các bản ghi vận chuyển liên quan đến nhà bán lẻ hiện tại, rồi tính tổng chi phí vận chuyển cho từng bản ghi. Hiệu ứng giống như một truy vấn được tham số hóa: bạn có thể tính một tổng khác nhau cho mỗi hàng trong bảng hiện tại.
Nhiều hàm DAX yêu cầu tồn tại mối quan hệ giữa các bảng hoặc giữa nhiều bảng để định vị các cột mà bạn đã tham chiếu và trả về kết quả có ý nghĩa. Các hàm khác sẽ tìm cách xác định mối quan hệ; Tuy nhiên, để có được kết quả tốt nhất, bạn nên luôn luôn tạo mối quan hệ khi có thể.
Khi bạn làm việc với PivotTable, điều đặc biệt quan trọng là bạn phải kết nối tất cả các bảng được dùng trong PivotTable để dữ liệu tóm tắt có thể được tính toán chính xác. Để biết thêm thông tin, hãy xem Làm việc với Mối quan hệ trong PivotTable.
Khắc phục lỗi trong công thức
Nếu bạn gặp lỗi khi xác định cột được tính, thì công thức đó có thể chứa lỗi cú pháp hoặc lỗi ngữ nghĩa.
Lỗi cú pháp là cách dễ giải quyết nhất. Chúng thường bao gồm thiếu dấu ngoặc đơn hoặc dấu phẩy. Để được trợ giúp về cú pháp của các hàm riêng lẻ, xem Tham khảo Hàm DAX.
Loại lỗi khác xảy ra khi cú pháp chính xác, nhưng giá trị hoặc cột được tham chiếu không có ý nghĩa trong ngữ cảnh của công thức. Những lỗi ngữ nghĩa này có thể do bất kỳ vấn đề nào sau đây gây ra:
- Công thức tham chiếu đến một cột, bảng hoặc hàm không tồn tại.
- Công thức có vẻ chính xác, nhưng khi Power Pivot tải dữ liệu, nó phát hiện ra một kiểu không khớp và nêu ra lỗi.
- Công thức chuyển một số lượng hoặc kiểu tham số không chính xác đến một hàm.
- Công thức tham chiếu đến một cột khác có lỗi, do đó giá trị của công thức không hợp lệ.
- Công thức tham chiếu đến một cột chưa được xử lý. Điều này có thể xảy ra nếu bạn thay đổi sổ làm việc sang chế độ thủ công, thực hiện thay đổi và sau đó không bao giờ làm mới dữ liệu hoặc cập nhật tính toán.
Trong bốn trường hợp đầu tiên, DAX gắn cờ cho toàn bộ cột chứa công thức không hợp lệ. Trong trường hợp cuối cùng, DAX làm xám cột để biểu thị cột đang ở trạng thái chưa xử lý.