image

Trong bài này, chúng ta sẽ học Cách sử dụng phép tính tổng có điều kiện cho các điều kiện cụ thể.

Tình huống:

Nói một cách dễ hiểu, khi làm việc với một tập dữ liệu dài rải rác, đôi khi chúng ta cần tìm tổng các số với một số tiêu chí trên đó. Ví dụ: tìm tổng số tiền lương trong một bộ phận cụ thể hoặc có nhiều tiêu chí theo ngày, tên, phòng ban hoặc thậm chí có thể đánh số dữ liệu như lương dưới giá trị hoặc số lượng trên giá trị. Đối với điều này, bạn thường sử dụng hàm SUMPRODUCT hoặc SUMIFS. Nhưng bạn sẽ không tin rằng, bạn thực hiện cùng một hàm với hàm IF hàm cơ bản của Excel.

Làm thế nào để giải quyết vấn đề?

Bạn phải suy nghĩ làm thế nào điều này có thể thực hiện được, để thực hiện các phép toán logic trên mảng bảng bằng cách sử dụng hàm IF. Hàm IF trong excel rất hữu ích, Nó sẽ giúp bạn vượt qua một số nhiệm vụ khó khăn trong Excel hoặc bất kỳ ngôn ngữ mã hóa nào khác. Hàm IF kiểm tra các điều kiện trên mảng tương ứng với các giá trị bắt buộc và trả về kết quả là mảng tương ứng với điều kiện Đúng là 1 và Sai là 0 Đối với vấn đề này, chúng ta sẽ sử dụng các hàm sau:

Chúng tôi sẽ yêu cầu các chức năng trên và một số cảm giác cơ bản về hoạt động dữ liệu. các điều kiện logic trên mảng có thể được áp dụng bằng các toán tử logic. Các toán tử logic này hoạt động trên cả văn bản và số. Dưới đây là công thức chung. \ {} dấu ngoặc nhọn là công cụ kỳ diệu để thực hiện các công thức mảng với hàm IF.

Công thức chung:

\{ =

(logical_1) (logical_2) … (logical_n) , sum_array ) ) }*

Lưu ý: Đối với dấu ngoặc nhọn (\ {}) Sử dụng Ctrl + Shift + Enter khi làm việc với mảng hoặc phạm vi trong Excel. Điều này sẽ tạo ra các Dấu ngoặc nhọn trên công thức theo mặc định. KHÔNG cố gắng mã cứng các ký tự dấu ngoặc nhọn.

Lôgic 1: kiểm tra điều kiện 1 trên mảng 1 Lôgic 2: kiểm tra điều kiện 2 trên mảng 2 và tương tự sum_array: mảng, tổng hoạt động được thực hiện

Ví dụ:

Tất cả những điều này có thể khó hiểu. Vì vậy, hãy kiểm tra công thức này bằng cách chạy nó trên ví dụ được hiển thị bên dưới. Tại đây chúng tôi có dữ liệu về các sản phẩm được giao đến các thành phố khác nhau cùng với các trường danh mục và số lượng tương ứng. Ở đây chúng tôi có dữ liệu và chúng tôi cần tìm số lượng cookie được gửi đến Boston với số lượng lớn hơn 40.

image

Bảng dữ liệu và bảng tiêu chí được hiển thị trong hình trên. Để hiểu rõ mục đích, chúng tôi đã sử dụng các phạm vi được đặt tên cho các mảng được sử dụng. Các phạm vi được đặt tên được liệt kê bên dưới.

Đây:

Thành phố được xác định cho mảng A2: A17.

Danh mục được xác định cho mảng B2: A17.

Số lượng được xác định cho mảng C2: C17.

Bây giờ bạn đã sẵn sàng để có được kết quả mong muốn bằng cách sử dụng công thức dưới đây.

Sử dụng công thức:

\{ =

(City=”Boston”) (Category=”Cookies”) (Quantity>40) , Quantity)) }

Giải thích:

  1. City = “Boston”: kiểm tra các giá trị trong phạm vi thành phố để khớp với “Boston”.

  2. Category = “Cookies”: kiểm tra các giá trị trong phạm vi Category để khớp với “Cookies”.

  3. Số lượng> 40: kiểm tra các giá trị trong phạm vi Số lượng thành ma. Số lượng là mảng mà tổng là bắt buộc.

  4. Hàm IF kiểm tra tất cả các tiêu chí và dấu hoa thị (*) là bội số của tất cả các kết quả của mảng.

SUM (IF (\ {0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 1; 0; 0; 0}, \ {33; 87; 58; 38; 54; 51; 28; 36; 28; 44; 23; 27; 43; 42; 33; 30}))

  1. Bây giờ hàm IF chỉ trả về các đại lượng tương ứng với số 1 và phần còn lại bị bỏ qua.

  2. Hàm SUM trả về giá trị SUM.

Bây giờ số lượng tương ứng với 1 chỉ cộng lại để nhận được kết quả.

image

Như bạn có thể thấy, số lượng 43 được trả lại nhưng có ba đơn đặt hàng cookie được giao đến “Boston” có số lượng 38, 36 và 43. Chúng tôi cần tổng số lượng trong đó số lượng trên 40. Vì vậy, công thức chỉ trả về 43. Bây giờ sử dụng các tiêu chí khác để lấy Số lượng SUM cho Thành phố: “Los Angeles” & Danh mục: “Quán bar” & Số lượng nhỏ hơn 50.

Sử dụng công thức

\{ =

“Los Angeles”) (Category=”Bars”) (Quantity < 50), Quantity ) ) }

image

Như bạn có thể thấy, kết quả là công thức trả về các giá trị 86. Là tổng của 2 đơn hàng thỏa mãn điều kiện có số lượng 44 & 42.

trong Excel để giải quyết vấn đề tương tự. Chúng ta có thể kiểm tra lại kết quả bằng hàm SUMPRODUCT

Sử dụng hàm SUMPRODUCT:

Hàm SUMPRODUCT trả về tổng các giá trị tương ứng trong mảng. Vì vậy, chúng ta sẽ nhận được các mảng để trả về 1s a giá trị câu lệnh True và 0s cho giá trị câu lệnh False. Vì vậy, tổng cuối cùng sẽ tương ứng với tất cả các câu lệnh là True.

Sử dụng công thức:

=

( — (City = “Boston”) , — (Category = “Cookies”) , — (Quantity > 40)

, Quantity )

-: phép toán được sử dụng để chuyển đổi tất cả TRUE thành 1s và False thành 0.

image

Hàm SUMPRODUCT kiểm tra lại SUM của số lượng được trả về bởi hàm SUM và IF được giải thích ở trên.

Tương tự đối với ví dụ thứ hai, kết quả giống nhau.

image

Như bạn thấy, hàm SUMPRODUCT có thể thực hiện cùng một nhiệm vụ.

Dưới đây là tất cả các lưu ý quan sát liên quan đến việc sử dụng công thức.

Ghi chú:

  1. Mảng tổng trong công thức chỉ hoạt động với các số.

  2. Nếu công thức trả về lỗi #VALUE, hãy kiểm tra xem dấu ngoặc nhọn phải có trong công thức như được hiển thị trong các ví dụ trong bài viết.

  3. Ký tự phủ định (-) thay đổi các giá trị, TRUE hoặc 1s thành FALSEs hoặc 0 và FALSEs hoặc 0s thành TRUE hoặc 1s.

  4. Các phép toán như bằng (=), nhỏ hơn bằng (⇐), lớn hơn (>) hoặc không bằng (<>) có thể được thực hiện trong một công thức được áp dụng, chỉ với số.

Hy vọng bài viết này về Cách sử dụng tính tổng có điều kiện cho các điều kiện cụ thể trong Excel là giải thích. Tìm thêm các bài viết về Công thức tính tổng tại đây. Nếu bạn thích blog của chúng tôi, hãy chia sẻ nó với những người bạn thân của bạn trên Facebook. Và bạn cũng có thể theo dõi chúng tôi trên Twitter và Facebook. Chúng tôi rất muốn nghe ý kiến ​​của bạn, hãy cho chúng tôi biết cách chúng tôi có thể cải thiện, bổ sung hoặc đổi mới công việc của mình và làm cho nó tốt hơn cho bạn. Viết thư cho chúng tôi tại [email protected]

Bài viết liên quan:

Bài viết phổ biến: