Xem Nhiều 12/2022 #️ Tạo Công Thức Cho Phép Tính Trong Power Pivot / 2023 # Top 17 Trend | Trucbachconcert.com

Xem Nhiều 12/2022 # Tạo Công Thức Cho Phép Tính Trong Power Pivot / 2023 # Top 17 Trend

Cập nhật thông tin chi tiết về Tạo Công Thức Cho Phép Tính Trong Power Pivot / 2023 mới nhất trên website Trucbachconcert.com. Hy vọng nội dung bài viết sẽ đáp ứng được nhu cầu của bạn, chúng tôi sẽ thường xuyên cập nhật mới nội dung để bạn nhận được thông tin nhanh chóng và chính xác nhất.

Trong bài viết này, chúng ta sẽ xem thông tin cơ bản về việc tạo công thức tính toán cho cả cột được tính và các biện pháp trong Power Pivot. Nếu bạn mới dùng DAX, hãy đảm bảo kiểm tra bắt đầu nhanh: tìm hiểu những điều cơ bản về Dax trong 30 phút.

Thông tin cơ bản về công thức

Power Pivot cung cấp các biểu thức phân tích dữ liệu (DAX) để tạo các phép tính tùy chỉnh trong bảng Power Pivot và trong Pivottable trong Excel. DAX bao gồm một số hàm được 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 quan hệ và thực hiện các tập hợp động.

Bạn có thể tạo các công thức Power Pivot cho các cột được tính toán nhiều khi tạo công thức trong Microsoft Excel.

Hãy thực hiện các bước sau khi tạo công thức:

Mỗi công thức phải bắt đầu bằng một dấu bằng.

Bạn có thể nhập hoặc chọn tên hàm hoặc nhập một biểu thức.

Bắt đầu nhập một vài chữ cái đầu tiên của hàm hoặc tên mà bạn muốn và tự động hoàn tất Hiển thị danh sách các hàm sẵn dùng, bảng và cột. Nhấn TAB để thêm một mục từ danh sách tự động hoàn tất 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 một 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 của các bảng và cột có thể xảy ra hoặc bằng cách nhập giá trị hoặc 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 được đóng lại và các cột, bảng và giá trị được tham chiếu chính xác.

Nhấn ENTER để chấp nhận công thức.

Lưu ý: Trong một cột được tính toán ngay khi bạn chấp nhận công thức, cột sẽ được nhập vào các giá trị. Trong một thước đo, nhấn ENTER sẽ lưu định nghĩa đo.

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 điền công thức ở giữa công thức hiện có với các hàm lồng nhau. 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 đổi.

Power Pivot không thêm dấu ngoặc đơn đóng của hàm hoặc tự động khớp dấu ngoặc đơn. Bạn phải đảm bảo rằng mỗi hàm là cú pháp chính xác hoặc bạn không thể lưu hoặc sử dụng công thức. Power Pivot có dấu ngoặc đơn tô sáng, giúp kiểm tra xem chúng được đóng đúng cách.

Làm việc với bảng và cột

bảngPower Pivot trông tương tự như bảng Excel nhưng khác nhau trong cách họ làm việc với dữ liệu và với công thức:

Các công thức trong Power Pivot chỉ hoạt động với bảng và cột, chứ không phải với các ô riêng lẻ, tham chiếu phạm vi hoặc mảng.

Bạn không thể dán các 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 không đều hoặc “không bị rách”, như bạn thực hiện trong trang tính Excel. Mỗi hàng trong một 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à các bảng dữ liệu Power Pivot không phải là hoán đổi, 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 mục thêm dữ liệu trang tính vào một mô hình dữ liệu bằng cách dùng bảng được liên kết và sao chép và dán hàng vào mô hình dữ liệu trong Power Pivot.

Tham chiếu đến các 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 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ác cột từ hai bảng bằng cách sử dụng tên đủ điều kiện:

= SUM (‘ doanh số mới ‘ [amount]) + SUM (‘ doanh số quá khứ ‘ [số tiền])

Khi một công thức được đánh giá, Power Pivot lần đầu tiên kiểm tra cú pháp chung, rồi kiểm tra tên cột và bảng mà bạn cung cấp cho các cột và bảng có thể có trong ngữ cảnh hiện tại. Nếu tên đó bị xóa hoặc nếu không tìm thấy cột hoặc không, bạn sẽ gặp phải lỗi trên công thức của mình (một chuỗi #ERROR thay vì giá trị dữ liệu trong các ô mà lỗi xảy ra). Để biết thêm thông tin về 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.

Mối quan hệ bảng

Nhiều hàm DAX yêu cầu mối quan hệ tồn tại giữa các bảng hoặc trong số nhiều bảng, để xác định 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ẽ cố gắng xác định mối quan hệ; Tuy nhiên, để có kết quả tốt nhất mà bạn luôn nên tạo mối quan hệ có thể xảy ra.

Khi bạn làm việc với Pivottable, đặc biệt là bạn sẽ 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 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 sự cố lỗi trong công thức

Nếu bạn gặp phải lỗi khi xác định một cột được tính toán, công thức có thể chứa lỗi cú pháp hoặc lỗi ngữ nghĩa.

Loại lỗi còn xảy ra khi cú pháp là 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. Các lỗi ngữ nghĩa như vậy có thể do bất kỳ vấn đề nào sau đây:

Công thức tham chiếu đến một cột, bảng hoặc hàm không có hiện có.

Công thức xuất hiện chính xác, nhưng khi Power Pivot tìm kiếm dữ liệu, nó tìm thấy kiểu không khớp và tăng một lỗi.

Công thức sẽ vượt số không chính xác hoặc loại tham số vào một hàm.

Công thức tham chiếu đến một cột khác có lỗi và do đó các giá trị của nó 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, sau đó không bao giờ làm mới dữ liệu hoặc cập nhật các phép tính.

Trong bốn trường hợp đầu tiên, DAX gắn cờ toàn bộ cột có chứa công thức không hợp lệ. Trong trường hợp cuối cùng, DAX đã màu xám ra khỏi cột để cho biết cột đó nằm trong trạng thái không được xử lý.

Lỗ Hổng Trong Excel Cho Phép Hacker Tấn Công Malware Thông Qua Tính Năng Power Query / 2023

Các nhà nghiên cứu đã tìm ra cách thức lạm dụng Microsoft Excel để thực hiện các cuộc tấn công malware thông qua khai thác tính năng Microsoft Excel Power Query để thực hiện các cuộc tấn công trao đổi dữ liệu động (DDE) và phân phối các phần mềm độc hại.

Hiện tại, Microsoft vẫn chưa phát hành bản vá cho lỗ hổng này.

Lạm dụng Microsoft Excel Power Query

Các nhà nghiên cứu tại Mimecast gần đây đã báo cáo về một kỹ thuật mới có thể lạm dụng tính năng Microsoft Excel Power Query để thực hiện các cuộc tấn công malware.

Microsoft cho biết Power Query là công nghệ kết nối dữ liệu cho phép người dùng khám phá, kết nối, kết hợp và tinh chỉnh các nguồn dữ liệu để đáp ứng các nhu cầu phân tích. Với Power Query, người dùng có thể tìm kiếm các nguồn dữ liệu, tạo kết nối và sau đó định hình dữ liệu (ví dụ: xóa một cột, thay đổi loại dữ liệu hoặc hợp nhất các bảng) tùy theo nhu cầu của người dùng.

Theo các nhà nghiên cứu, các hacker tiềm năng có thể lạm dụng tính năng này để phân phối malware bằng cách nhúng mã độc vào biểu dữ liệu (datasheet). Khi mở biểu dữ liệu, mã độc sẽ chạy trên hệ thống đích và thực thi các phần mềm độc hại này.

Các cuộc tấn công dạng này thường khó phát hiện và các hacker có nhiều cơ hội để thỏa hiệp với host của nạn nhân.

Sử dụng điểm yếu tiềm ẩn trong Power Query, những kẻ tấn công có khả năng nhúng bất kỳ payload độc hại nào. Những payload này được thiết kế để không lưu bên trong (inside) tài liệu mà được tải xuống từ các trang web khi tài liệu được mở.

Trong một bài đăng trên blog, các nhà nghiên cứu cũng đã mô phỏng và chia sẻ chi tiết một khai thác DDE lạm dụng Power Query.

Vẫn chưa có bản vá cho lỗ hổng Microsoft Excel Power Query

Ngay khi phát hiện một kỹ thuật khai thác lỗ hổng thành công, Mimecast đã tìm đến Microsoft để báo cáo vấn đề. Tuy nhiên, các nhà nghiên cứu cho biết Microsft vẫn chưa tìm ra phương án sửa chữa.

Tuy nhiên, phía công ty cũng đã đề xuất một cách giải quyết để giảm thiểu các cuộc tấn công. Trong một tư vấn bảo mật gần đây, Microsoft đã giải thích cách thức người dùng có thể mở Tài liệu Microsoft chứa trường DDE (trên cả tệp Excel và Word) một cách an toàn.

Năm 2017, các nhà nghiên cứu từ Sensepost cũng đã báo cáo một phương thức tấn công tương tự lạm dụng lỗ hổng tồn tại trong Microsoft Word.

LHN

Tạo Pivottable Để Phân Tích Dữ Liệu Trang Tính / 2023

Tạo PivotTable

Chọn các ô bạn muốn dùng để tạo PivotTable.

Lưu ý: Dữ liệu của bạn không được có bất kỳ hàng hay cột trống nào. Dữ liệu chỉ được có một đầu đề hàng.

Bên dưới Chọn dữ liệu bạn muốn phân tích, chọn Chọn một bảng hoặc dải ô.

Trong Bảng/Dải ô, hãy xác minh dải ô.

Bên dưới chọn nơi bạn muốn đặt báo cáo pivottable, hãy chọn trang tính mới để đặt pivottable vào trang tính mới hoặc trang tính hiện có , rồi chọn vị trí bạn muốn pivottable xuất hiện.

Chọn OK.

Dựng PivotTable của bạn

Để thêm trường vào PivotTable của bạn, hãy chọn hộp kiểm tên trường trong ngăn Trường PivotTable .

Lưu ý: Các trường đã chọn được thêm vào khu vực mặc định: các trường không phải là số được thêm vào Hàng, các phân cấp ngày và giờ được thêm vào Cột và các trường số được thêm vào Giá trị.

Để di chuyển trường từ khu vực này sang khu vực khác, hãy kéo trường đến khu vực đích.

Trước khi bắt đầu:

Bạn nên sắp xếp dữ liệu của mình theo định dạng bảng và không chứa bất cứ hàng hoặc cột trống nào. Tốt nhất, bạn có thể sử dụng Bảng Excel giống như trong ví dụ ở trên.

Bảng là nguồn dữ liệu PivotTable tuyệt vời vì các hàng được thêm vào bảng sẽ tự động được đưa vào PivotTable khi bạn làm mới dữ liệu và bất cứ cột mới nào cũng sẽ được đưa vào Danh sách Trường PivotTable. Nếu không, bạn cần thay đổi dữ liệu nguồn cho PivotTable, hoặc sử dụng công thức phạm vi có tên động.

Kiểu dữ liệu trong cột phải giống nhau. Ví dụ: bạn không nên kết hợp ngày tháng và văn bản vào cùng một cột.

PivotTable hoạt động tức thời với dữ liệu của bạn, được gọi là bộ đệm ẩn nên dữ liệu thực tế của bạn không bị thay đổi theo bất kỳ cách nào.

Tạo PivotTable

Nếu bạn chưa có nhiều trải nghiệm với PivotTable hoặc không chắc chắn về cách bắt đầu, bạn nên sử dụng PivotTable được đề xuất. Khi bạn sử dụng tính năng này, Excel xác định một bố trí có ý nghĩa bằng cách khớp dữ liệu với các khu vực phù hợp nhất trong PivotTable. Tính năng này sẽ cho bạn một điểm khởi đầu để thử nghiệm thêm. Sau khi tạo PivotTable được đề xuất, bạn có thể khám phá các hướng khác nhau và sắp xếp lại các trường để đạt được kết quả cụ thể của bạn.

Bạn cũng có thể tải xuống hướng dẫn Tạo PivotTable đầu tiên mang tính tương tác của chúng tôi.

PivotTable được Đề xuất

Tạo PivotTable theo cách thủ công

Bấm vào một ô trong dải ô của bảng hoặc dữ liệu nguồn.

Excel sẽ phân tích dữ liệu của bạn và hiển thị cho bạn một số tùy chọn, chẳng hạn như trong ví dụ sử dụng dữ liệu chi phí hộ gia đình này.

Chọn PivotTable có vẻ phù hợp nhất với bạn và nhấn OK. Excel sẽ tạo PivotTable trên một trang tính mới và hiển thị Danh sách Trường PivotTable.

Bấm vào một ô trong dải ô của bảng hoặc dữ liệu nguồn.

Nếu bạn đang sử dụng Excel for Mac 2011 hoặc phiên bản cũ hơn, nút PivotTable sẽ nằm trên tab Dữ liệu trong nhóm Phân tích.

Excel sẽ hiển thị hộp thoại Tạo PivotTable với tên dải ô hoặc bảng được chọn. Trong trường hợp này, chúng tôi đang sử dụng bảng có tên “tbl_HouseholdExpenses”.

Trong mục Chọn nơi bạn muốn đặt báo cáo PivotTable, chọn Trang tính mới hoặc Trang tính hiện có. Đối với Trang tính hiện có, hãy chọn ô nơi bạn muốn đặt PivotTable.

Bấm vào OK và Excel sẽ tạo một PivotTable trống, đồng thời hiển thị danh sách Trường PivotTable.

Trong vùng Tên Trường ở trên cùng, chọn hộp kiểm tương ứng với trường bất kỳ mà bạn muốn thêm vào PivotTable. Theo mặc định, các trường không phải là số sẽ được thêm vào vùng Hàng, các trường ngày và giờ sẽ được thêm vào vùng Cột và các trường số sẽ được thêm vào vùng Giá trị. Bạn cũng có thể kéo và thả thủ công bất cứ mục có sẵn nào vào bất cứ trường PivotTable nào hoặc nếu bạn không muốn có mục nào đó trong PivotTable nữa, chỉ cần kéo mục đó ra khỏi danh sách Trường hoặc bỏ chọn mục đó. Khả năng sắp xếp lại các mục Trường là một trong các tính năng PivotTable giúp bạn dễ dàng thay đổi hình thức PivotTable một cách nhanh chóng.

Danh sách Trường PivotTable

Các trường tương ứng trong PivotTable

Tóm tắt theo

Theo mặc định, các trường PivotTable được đặt trong khu vực Giá trị sẽ được hiển thị dưới dạng SUM. Nếu Excel hiểu dữ liệu của bạn dưới dạng văn bản thì dữ liệu sẽ được hiển thị dưới dạng SỐ LƯỢNG. Đây là lý do tại sao bạn cần đảm bảo không kết hợp các kiểu dữ liệu cho các trường giá trị. Bạn có thể thay đổi tính toán mặc định bằng cách bấm vào mũi tên bên phải tên trường, rồi chọn tùy chọn Cài đặt trường.

Tiếp theo, thay đổi tính toán trong mục Tóm tắt theo. Lưu ý rằng khi bạn thay đổi phương pháp tính toán, Excel sẽ tự động gắn thêm phương pháp đó vào mục Tên tùy chỉnh, như “Tổng của tên_trường” nhưng bạn có thể thay đổi mục đó. Nếu bạn bấm vào nút Số…, bạn có thể thay đổi định dạng số cho toàn bộ trường.

Hiển thị dữ liệu dưới dạng

Sau khi bạn đã mở hộp thoại Cài đặt trường, bạn có thể đưa ra lựa chọn của mình từ tab Hiển thị dữ liệu dưới dạng.

Hiển thị một giá trị cả dưới dạng phép tính và phần trăm.

Chỉ cần kéo mục vào mục Giá trị hai lần, bấm chuột phải vào giá trị và chọn Cài đặt trường, rồi đặt các tùy chọn Tóm tắt theo và Hiển thị dữ liệu dưới dạng cho từng mục.

Nếu bạn đã tạo PivotTable và quyết định bạn không muốn sử dụng PivotTable đó nữa thì bạn chỉ cần chọn toàn bộ dải ô PivotTable, rồi nhấn vào Xóa. Thao tác này sẽ không có bất kỳ ảnh hưởng nào đến dữ liệu khác hoặc PivotTable hay biểu đồ xung quanh đó. Nếu PivotTable của bạn nằm trên một trang tính riêng không chứa dữ liệu nào khác mà bạn muốn giữ, việc xóa trang tính đó là cách nhanh nhất để loại bỏ PivotTable.

Ngăn chèn pivottable Hiển thị nguồn dữ liệu và đích mà pivottable sẽ được chèn và cung cấp một số pivottable được đề xuất.

Thực hiện một trong những thao tác sau:

Để tạo PivotTable theo cách thủ công, hãy chọn tạo PivotTable của riêng bạn.

Để sử dụng PivotTable đề xuất, hãy cuộn cho đến khi bạn thấy tùy chọn mình muốn, rồi chọn + Insert.

PivotTable xuất hiện ở đích và ngăn trường Pivottable sẽ xuất hiện.

Thay đổi dữ liệu nguồn hoặc ô đích

Nếu muốn, bạn có thể thay đổi nguồn cho dữ liệu pivottable và ô đích nơi bạn muốn đặt pivottable. Ô đích sẽ nằm ngoài bảng nguồn hoặc phạm vi.

Trong ngăn chèn PivotTable, hãy chọn nối kết bên cạnh nguồn hoặc nối kết bên cạnh đích. Ngăn chèn các thay đổi của PivotTable:

Thực hiện một trong những thao tác sau:

Để thay đổi nguồn, hãy chọn chọn bảng hoặc phạm vi để phân tích: và làm theo các bước xuất hiện.

Để thay đổi đích, hãy chọn trang tính mới hoặc trang tính hiện có bên dưới chọn một đích cho pivottable, rồi làm theo các bước xuất hiện.

Trong khu vực Trường PivotTable ở trên cùng, chọn hộp kiểm tương ứng với trường bất kỳ mà bạn muốn thêm vào PivotTable. Theo mặc định, các trường không phải là số sẽ được thêm vào khu vực Hàng, các trường ngày và giờ sẽ được thêm vào khu vực Cột và các trường số sẽ được thêm vào khu vực Giá trị. Bạn cũng có thể kéo và thả thủ công bất cứ mục có sẵn nào vào bất cứ trường PivotTable nào hoặc nếu bạn không muốn có mục nào đó trong PivotTable nữa, chỉ cần kéo mục đó ra khỏi danh sách Trường hoặc bỏ chọn mục đó. Khả năng sắp xếp lại các mục Trường là một trong các tính năng PivotTable giúp bạn dễ dàng thay đổi hình thức PivotTable một cách nhanh chóng.

Danh sách Trường PivotTable

Các trường tương ứng trong PivotTable

Tóm tắt Giá trị Theo

Theo mặc định, các trường PivotTable được đặt trong khu vực Giá trị sẽ được hiển thị dưới dạng TỔNG. Nếu Excel hiểu dữ liệu của bạn dưới dạng văn bản thì dữ liệu sẽ được hiển thị dưới dạng SỐ LƯỢNG. Đây là lý do tại sao việc đảm bảo bạn không kết hợp các kiểu dữ liệu cho các trường giá trị lại quan trọng. Bạn có thể thay đổi tính toán mặc định qua việc trước tiên bấm vào mũi tên bên phải tên trường, rồi chọn tùy chọn Thiết đặt Trường Giá trị.

Tiếp theo, thay đổi tính toán trong mục Tóm tắt Giá trị Theo. Lưu ý rằng khi bạn thay đổi phương pháp tính toán, Excel sẽ tự động gán phương pháp đó vào mục Tên Tùy chỉnh, như “Tổng Tên Trường” nhưng bạn có thể thay đổi mục đó. Nếu bạn bấm vào nút Định dạng Số, bạn có thể thay đổi định dạng số cho toàn bộ trường.

Hiển thị Giá trị Dưới dạng

Sau khi bạn đã mở hộp thoại Thiết đặt Trường Giá trị, bạn có thể đưa ra lựa chọn của mình từ tab Hiển thị Giá trị Dưới dạng.

Hiển thị một giá trị cả ở dưới dạng phép tính và phần trăm.

Chỉ cần kéo mục vào mục Giá trị hai lần, rồi đặt các tùy chọn Tóm tắt Giá trị Theo và Hiển thị Giá trị Dưới dạng cho từng mục.

Nếu bạn thêm dữ liệu mới vào nguồn dữ liệu PivotTable của mình, mọi PivotTable được xây dựng trên nguồn dữ liệu đó cần được làm mới. Để làm mới PivotTable, bạn có thể bấm chuột phải vào bất kỳ đâu trong dải ô PivotTable, rồi chọn Làm mới.

Nếu bạn đã tạo PivotTable và quyết định bạn không muốn sử dụng PivotTable đó nữa thì bạn chỉ cần chọn toàn bộ dải ô PivotTable, rồi nhấn vào Xóa. Thao tác này sẽ không có bất kỳ ảnh hưởng nào đến dữ liệu khác hoặc PivotTable hay biểu đồ xung quanh đó. Nếu PivotTable của bạn nằm trên một trang tính riêng không chứa dữ liệu nào khác mà bạn muốn giữ, việc xóa trang tính đó là cách nhanh nhất để loại bỏ PivotTable.

Hàm Sumproduct Trong Excel Và Một Số Ví Vụ Công Thức / 2023

Bài hướng dẫn này giải thích cách sử dụng cơ bản và nâng cao của hàm SUMPRODUCT trong Excel. Bạn sẽ thấy một vài ví dụ công thức dùng để so sánh mảng, tính tổng có điều kiện và đếm số ô có nhiều điều kiện, tính trung bình cộng có trọng lượng và hơn thế nữa.

Khi bạn nghe cái tên hàm SUMPRODUCT lần đầu tiên, nó có vẻ là một công thức vô ích – công thức tính tổng thông thường của các tích số. Nhưng định nghĩa này không cho thấy thậm chí là chỉ một phần nhỏ tính năng của hàm SUMPRODUCT.

HÀM SUMPRODUCT TRONG EXCEL – CÚ PHÁP VÀ CÔNG DỤNG:

Về mặt kỹ thuật, hàm SUMPRODUCT trong Excel nhân các con số trong mảng xác định, rồi trả về tổng của các tích số đó.

Cú pháp của hàm SUMPRODUCT rất đơn giản:

SUMPRODUCT(array1, [array2], [array3], …)

Trong đó, mảng 1, mảng 2, … là các dải ô liên tục hay là các mảng có chứa thành phần bạn muốn nhân, rồi cộng lại.

Số mảng tối thiểu là 1. Trong trường hợp này, hàm SUMPRODUCT chỉ đơn giản tính tổng các thành phần mảng rồi trả về tổng số.

Số mảng tối đa là 255 trong Excel 2016, Excel 2013, Excel 2010, và Excel 2007, và 30 trong các phiên bản trước của Excel.

Mặc dù hàm SUMPRODUCT xử lý mảng, nhưng nó không yêu cầu phím tắt mảng (Ctrl + Shift + Enter). Bạn hoàn thành một công thức SUMPRODUCT theo cách thông thường bẳng cách nhấn phím Enter.

Lưu ý:

Tất cả mảng trong công thức SUMPRODUCT phải có cùng số hàng và số cột , nếu không thì bạn sẽ nhận lỗi #VALUE!.

Nếu bất cứ câu lệnh mảng nào chứa giá trị phi số, chúng sẽ được xử lý như số 0.

Nếu mảng là phép thử lô gic, thì nó sẽ trả về giá trị TRUE và FALSE. Thông thường, bạn cần phải đổi các giá trị TRUE và FALSE này thành 1 và 0 bằng cách sử dụng toán tử đơn phân (-). Hãy xem qua ví dụ hàm SUMPRODUCT có nhiều điều kiện để nắm rõ hơn.

Hàm SUMPRODUCT không hỗ trợ ký tự đại diện.

[Đăng ký]: Khóa học Excel nâng cao Online

CÁCH DÙNG CƠ BẢN CỦA HÀM SUMPRODUCT TRONG EXCEL:

Để có cái nhìn tổng quát về cách hàm SUMPRODUCT hoạt động, hãy xem qua ví dụ sau.

Giả sử bạn có số lượng ở các ô A2:A4, và bạn muốn tính tổng. Nếu bạn đang giải một phép toán ở trường, thì bạn sẽ nhân số lượng với giá tiền của mỗi sản phẩm, rồi cộng chúng lại. Trong Microsoft Excel, bạn có thể nhận được kết quả chỉ với một công thức SUMPRODUCT:

=SUMPRODUCT(A2:A4,B2:B4)

Công thức chọn số đầu tiên trong mảng đầu tiên rồi nhân nó cho số đầu tiên trong mảng thứ hai, rồi lấy số thứ hai trong mảng đầu tiên nhân cho số thứ hai trong mảng thứ hai rồi cứ tiếp tục như thế.

Khi đã nhân tất các thành phần mảng, công thức sẽ tính tổng các tích số rồi trả về tổng số.

Nói cách khác, công thức SUMPRODUCT biểu diễn công thức toán học sau đây:

=A2*B2 + A3*B3 + A4*B4

Hãy nghĩ đến việc bạn có thể tiết kiệm bao nhiều thời gian nếu bảng của bạn không chứa 3 hàng dữ liệu, mà là 3 trăm hay 3 ngàn hàng!

CÁCH SỬ DỤNG HÀM SUMPRODUCT TRONG EXCEL – VÍ DỤ CÔNG THỨC:

HÀM SUMPRODUCT CÓ NHIỀU ĐIỀU KIỆN

Thông thường trong Microsoft Excel, luôn có nhiều hơn một cách để hoàn thành công việc. Nhưng khi nói đến việc so sánh hai hay nhiều mảng, đặc biệt là có nhiều điều kiện, nếu không nói là duy nhất, hàm SUMPRODUCT là hàm hiệu quả nhất. À thì, hàm SUMPRODUCT hay công thức mảng đều được.

Giả sử bạn có danh sách các món hàng ở cột A, doanh số bán dự kiến ở cột B, và doanh số bán thực ở cột C. Mục tiêu của bạn là tìm xem có bao nhiêu có bao nhiêu món bán ít hơn dự kiến. Đối với trường hợp này, hãy sử dụng một trong những biến phân của công thức SUMPRODUCT:

=SUMPRODUCT(-(C2:C10<B2:B10))

hay

=SUMPRODUCT((C2:C10<B2:B10)*1)

Trong đó C2:C10 là doanh số thực và B2:B10 là doanh số dự kiến.

=SUMPRODUCT(-(C2:C10<B2:B10), -(A2:A10=”táo”))

Hay, bạn có thể sử dụng cú pháp sau:

=SUMPRODUCT((C2:C10<B2:B10)*(A2:A10=”táo”))

CÁCH CÔNG THỨC SUMPRODUCT CÓ MỘT ĐIỀU KIỆN HOẠT ĐỘNG:

Đối với người mới bắt đầu, hãy chia nhỏ công thức đơn giản hơn – công thức so sánh các con số trong hai cột hàng theo hàng, và nói cho chúng ta biết số lần cột C ít hơn cột B:

=SUMPRODUCT(-(C2:C10<B2:B10))

Nếu bạn chọn phần (C2:C10<B2:B10) trong thanh công thức, và nhấn F9 để xem giá trị ẩn, thì bạn sẽ thấy mảng sau:

Hai dấu trừ (-), về mặt kỹ thuật được gọi là hai toán tử đơn phân, nó ép buộc TRUE và FALSE thành 1 và 0: {0;1;0;0;1;0;1;0;0}.

Cách khác để đổi giá trị lôgic thành giá trị số đó là nhân mảng với 1:

=SUMPRODUCT((C2:C10<B2:B10)*1)

Cách nào cũng được, vì chỉ có một mảng trong công thức SUMPRODUCT, nên nó chỉ nhân 1 trong mảng cho kết quả và chúng ta nhận được kết quả đếm mong muốn. Rất dễ, đúng không nào?

Khi công thức SUMPRODUCT trong Excel chứa hai hay nhiều mảng, nó nhân các thành phần của mảng, rồi cộng các tích số.

=SUMPRODUCT(-(C2:C10<B2:B10), -(A2:A10=”táo”))

hay

=SUMPRODUCT((C2:C10<B2:B10)*(A2:A10=”táo”))

Điểm khác biệt duy nhất về mặt kỹ thuật giữa hai công thức đó là phương pháp ép buộc TRUE và FALSE thành 1 và 0 – bằng cách sử dụng hai toán tử đơn phân hay phép nhân. Kết quả là, chúng ta nhận được hai mảng 1 và 0:

Trong Excel 2003 và các phiên bản trước thì không có hàm IFS, một trong những cách dùng thông dụng nhất của hàm SUMPRODUCT đó là tính tổng hay đếm có điều kiện các ô với nhiều tiêu chuẩn. Bắt đầu với Excel 2007, Microsoft giới thiệu một chuỗi hàm được đặc biệt thiết kế cho những nhiệm vụ này – hàm SUMIFS, hàm COUNTIFS và hàm AVERAGEIFS.

CÔNG THỨC SUMPRODUCT VỚI HÀM LÔGIC AND:

Trong các phiên bản Excel gần đây: Excel 2016, 2013, 2010 và 2007, bạn có thể dễ dàng hoàn thành nhiệm vụ bằng cách sử dụng công thức SUMIFS, COUNTIFS và AVERAGEIFS. Nếu bạn đang tìm một hướng đi khó khăn hơn, hay nếu bạn vẫn sử dụng Excel 2003 hay phiên bản cũ hơn, thì bạn vẫn đạt được kết quả mong muốn với hàm SUMPRODUCT.

Để đếm doanh số táo bán ra ở khu vực phía Bắc:

=SUMPRODUCT(-(A2:A12=”khu vực phía Bắc”), -(B2:B12=”táo”))

hay

=SUMPRODUCT((A2:A12=”khu vực phía Bắc”)*(B2:B12=”táo”))

Để tính tổng doanh số táo bán ra ở khu vực phía Bắc:

=SUMPRODUCT(-(A2:A12=”khu vực phía Bắc”), -(B2:B12=”táo”), C2:C12)

hay

=SUMPRODUCT((A2:A12=”khu vực phía Bắc”)*(B2:B12=”táo”)*C2:C12)

Để tính trung bình cộng doanh số táo bán ra ở khu vực phía Bắc:

Để tính trung bình cộng, chúng ta chỉ chia tổng cho phép đếm như thế này:

=SUMPRODUCT(-(A2:A12=”khu vực phía Bắc”), -(B2:B12=”táo”), C2:C12) / SUMPRODUCT( -(A2:A12=”khu vực phía Bắc”), -(B2:B12=”táo”))

Từ ví dụ trước, bạn đã biết cách công thức SUMPRODUCT trong Excel đếm số ô cùng với hiều điều kiện. Nếu bạn đã hiểu rõ cách đó, sẽ rất dễ dàng để bạn nắm rõ quy luật tính tổng.

=SUMPRODUCT(-(A2:A12=”khu vực phía Bắc”), -(B2:B12=”táo”), C2:C12)

Trong mảng thứ hai, 1 đại diện cho Táo, và 0 đại diện cho các món hàng khác.

Mảng thứ ba chứa chính các con số xuất hiện từ ô C2 đến C12.

Hãy nhớ rằng nhân 0 thì luôn bằng 0, và nhân 1 thì bằng chính nó, chúng ta có mảng cuối cùng chứa doanh số và những con số 0 – doanh số chỉ xuất hiện nếu hai mảng đầu tiên có số 1 ở cùng một vị trí, cụ thể là cả hai điều kiện xác định đều được đáp ứng; còn không thì sẽ hiển thị số 0:

VÍ DỤ 2. CÔNG THỨC SUMPRODUCT VỚI HÀM LÔGIC OR

Để công hay đếm có điều kiện các ô sử dụng hàm lôgic OR, hãy sử dụng dấu cộng (+) giữa các mảng.

Trong công thức Excel SUMPRODUCT, cũng như trong công thức mảng, dấu cộng đóng vai trò làm toán tử OR hướng dẫn Excel trả về giá trị TRUE nếu bất kỳ điều kiện nào trong biểu thức xác định được đáp ứng.

Ví dụ, để đếm tổng doanh số Táo và Chanh không phân biệt vùng miền, hãy sử dụng công thức này:

=SUMPRODUCT((B2:B12=”táo”)+(B2:B12=”chanh”))

Nói đơn giản, công thức trên có nghĩa là: Hãy đếm số ô nếu dải ô B2:B12=”táo” OR B2:B12=”chanh”.

Để tính tổng doanh số Táo và Chanh, hãy thêm một câu lệnh chứa dải ô Doanh số:

=SUMPRODUCT((B2:B12=”táo”)+(B2:B12=”chanh”), C2:C12)

Một trong những giải pháp khả thi đó là kết hợp hai hay nhiều hàm SUMIFS+SUMIFS hay COUNTIFS+COUNTIFS.

Cách khác đó là sử dụng hàm SUMPRODUCT trong đó:

Dấu hoa thị (*) được dùng như toán tử AND.

Dấu cộng (+) được dùng như toán tử OR.

Để đếm số lần Táo và Chanh được bán ở khu vực phía Bắc, hãy lập công thức với hàm lôgic sau:

=Count If ((Vùng miền=”miền Bắc”) AND ((Món hàng=”Táo”) OR (Món hàng=”Chanh”)))

Khi vừa sử dụng theo cú pháp hàm SUMPRODUCT, công thức sẽ có dạng như sau:

=SUMPRODUCT((A2:A12=”miền Bắc”)*((B2:B12=”táo”)+(B2:B12=”chanh”)))

Để tính tổng doanh số Táo và Chanh bán ra ở khu vục phía Bắc, hãy dùng công thức trên rồi thêm dải ô Doanh số cùng với hàm lôgic AND:

=SUMPRODUCT((A2:A12=”miền Bắc”)*((B2:B12=”táo”)+(B2:B12=”chanh”))*C2:C12)

Để công thức trông gọn hơn, bạn có thể gõ các biến vào các ô riêng biệt – Khu vực ở ô F1 và Món hàng ở ô F2 và H2 – rồi tham chiếu các ô này vào công thức:

Ở một trong những ví dụ trước, chúng ta đã bàn về công thức SUMPRODUCT dùng để tính trung bình có điều kiện. Cách dùng phổ biến khác của hàm SUMPRODUCT trong Excel đó là tính trung bình có trọng lượng trong đó mỗi giá trị có một trọng lượng nhất định.

Công thức SUMPRODUCT tính trung bình có trọng lượng tổng quát có dạng như sau:

SUMPRODUCT( values, weights) / SUM( weights)

Giả sử các giá trị nằm ở dải ô B2:B7 và trọng lượng ở dải ô C2:C7, công thức SUMPRODUCT tính trung bình có trọng lượng có dạng như sau:

=SUMPRODUCT(B2:B7,C2:C7)/SUM(C2:C7)

HÀM SUMPRODUCT ĐƯỢC DÙNG LÀM HÀM THAY THẾ CHO CÔNG THỨC MẢNG

Ngay cả khi bạn đọc bài viết này chỉ để có thêm thông tin và bạn không còn nhớ rõ các chi tiết nữa, hãy nhớ một điểm mấu chốt quan trọng – hàm SUMPRODUCT trong Excel xử lý mảng. Và vì hàm SUMPRODUCT có các khả năng của công thức mảng, nên nó có thể trở thành hàm thay thế dễ sử dụng cho công thức mảng.

Ưu điểm là gì? Về mặt cơ bản, bạn có khả năng quản lý công thức một cách dễ dàng mà không cần phải nhấp Ctrl + Shift + Enter mỗi lần bạn nhập một công thức mảng mới hay chỉnh sửa công thức mảng.

Ví dụ, chúng ta có thể sử dụng một công thức mảng đơn giản để đếm số ký tự trong dải ô xác định:

{=SUM(LEN( range))}

và biến nó thành công thức thông thường:

=SUMPRODUCT(LEN( range))

Để có thể ứng dụng tốt Excel vào trong công việc, chúng ta không chỉ nắm vững được các hàm mà còn phải sử dụng tốt cả các công cụ của Excel. Những hàm nâng cao giúp áp dụng tốt vào công việc như SUMIFS, COUNTIFS, SUMPRODUCT, INDEX + MATCH… Những công cụ thường sử dụng là Data validation, Conditional formatting, Pivot table…

Toàn bộ những kiến thức này các bạn đều có thể học được trong khóa học Excel từ cơ bản đến chuyên gia dành cho người đi làm

Bạn đang xem bài viết Tạo Công Thức Cho Phép Tính Trong Power Pivot / 2023 trên website Trucbachconcert.com. Hy vọng những thông tin mà chúng tôi đã chia sẻ là hữu ích với bạn. Nếu nội dung hay, ý nghĩa bạn hãy chia sẻ với bạn bè của mình và luôn theo dõi, ủng hộ chúng tôi để cập nhật những thông tin mới nhất. Chúc bạn một ngày tốt lành!