Hàm SUMIF trong Excel từ Cơ bản – Nâng cao [ Học viện Excel VN ]

Hàm SUMIF là gì và cách sử dụng hàm như thế nào cho HIỆU QUẢ trong công việc. Học viện Excel VN sẽ giúp bạn hiểu hàm Sumif cơ bản, nâng cao, kết hợp với các hàm khác, bẫy lỗi…

Bài viết có chất lượng kiến thức rất cao.

Bạn hãy chăm chỉ đọc từ từ từng phần một để hiểu sâu, rõ và vận dụng tốt vào công việc nhé.

1 – Hàm sumif cơ bản

1.1. Định nghĩa và ứng dụng (ý nghĩa, mục đích sử dụng)

Nói đến hàm sumif, ta có thể liên tưởng ngay tới việc tính tổng (sum) và có kèm theo điều kiện (IF).

Đây là một hàm có ứng dụng nhiểu trong văn phòng.

Ứng dụng trong kế toán:

  • Khi cần lập bảng cân đối phát sinh dựa trên sổ nhật ký chung
  • Hay báo cáo lưu chuyển tiền tệ dựa trên sổ tổng hợp tài khoản 111,112

Ứng dụng trong bán hàng:

  • Khi cần tính tổng doanh số của từng khách hàng

Hay ứng dụng trong kế toán công nợ

  • Khi tính tổng nợ của 1 khách hàng hoặc 1 nhóm khách hàng thỏa mãn điều kiện nào đó

Úng dụng lập báo cáo nhập xuất tồn, ứng dụng theo dõi tạm ứng ….

1.2. Công thức/ Cú pháp hàm Sumif:

=Sumif(range,criteria,[sum_range])

Diễn giải:

=SUMIF(vùng điều kiện A, điều kiện tính tổng B, vùng tính tổng C)

Công thức của hàm này có thể được hiểu đơn giản như sau:

Tính tổng tất cả các phần tử thuộc vùng tính tổng C mà tương ứng với mỗi giá trị tại vùng C.

Ta có thể tìm thấy 1 giá trị ở vùng điều kiện A và giá trị này thỏa mãn điều kiện B.

Chi tiết các thành phần của hàm SUMIF:

  • Vùng điều kiện A : là vùng mang các giá trị dùng làm căn cứ tính tổng (điều kiện tính tổng) ví dụ tính tổng theo tên người, theo chức danh, theo tên hàng, theo tên đơn vị … thì trong bảng tính, vùng chứa tên đó là vùng điều kiện.
  • Điều kiện tính tổng B: là yêu cầu của bài toán, tính tổng theo tiêu thức nào? Theo điều kiện nào? Điều kiện tính tổng có thể là một địa chỉ ô chứa giá trị, một hoặc vài đoạn ký tự hay một số.
  • Vùng tính tổng C: là vùng chứa các giá trị cần tính tổng. Mỗi ô tại vùng này là một giá trị tương ứng với một ô tại vùng điều kiện.

Lưu ý:

Để hàm cho ra kết quả chính xác:

  • Vùng điều kiện phải có độ lớn bằng vùng tính tổng
  • Và mỗi ô (địa chỉ) tại vùng điều kiện, sẽ có một giá trị tương ứng tại vùng tính tổng.

2. Ví dụ bài tập thực hành hàm SUMIF

Chúng ta cùng xem và phân tích ví dụ đơn giản sau đây để hiểu kỹ hơn về cách dùng của hàm này.

sumif-1 cách sử dụng hàm sumif nâng cao nhiều điều kiện trong excel

Như chúng ta thấy, bài toán này là bài toán tính tổng với 1 điều kiện, do đó ta sử dụng hàm sumif. Ta đi tìm các thông số của hàm sumif bao gồm: vùng điều kiện, điều kiện, vùng tính tổng.

Phân tích đề bài tính tổng có điều kiện SUMIF

Như chúng ta thấy ở đây, yêu cầu bài toán đặt ra rất rõ ràng là tổng tính doanh số của 1 nhân viên kinh doanh.

Vậy cái chúng ta cần tính tổng là doanh số.

Do vậy chúng ta xác định được

  • Vùng tính tổng C [sum_range] chính là vùng chứa doanh số= vùng E3:E16.
  • Điều kiện tính tổng B [criteria] chính là tên nhân viên.
  • Vùng điều kiện A (range) chính là cột tên NVKD trên bảng tính doanh số tháng 8 = vùng từ C3:C16.

Lưu ý:

Ở đây lưu ý là vùng điều kiện (C3:C16) phải có độ lớn bằng vùng tính tổng (E3:E16) và tương ứng với mỗi một ô tại vùng điều kiện, ta đều tìm thấy được 1 giá trị tại vùng tính tổng. (E3 tương ứng C3, E4 tương ứng C4 …)

Ta đã xác định xong các thông số của hàm. Ta đi xem công thức tại ô J3 và thấy như sau:

=Sumif($C$3:$C$16;I3;$E$3:$E$16)

Giải thích ký tự $.

Do khi kéo công thức xuống dưới, các địa chỉ vùng, ô điều kiện, vùng tính tổng của chúng ta sẽ bị thay đổi. Vì vậy chúng ta cần sử dụng địa chỉ tuyệt đối, hay địa chỉ cố định với các vùng tính tổng và vùng điều kiện để đảm bảo kết quả luôn đúng. Biểu tượng dấu “$” chính là thể hiện địa chỉ tuyệt đối hay cố định địa chỉ.

Tất nhiên, trong excel, chúng ta luôn phải bắt đầu 1 công thức bằng 1 toán tử (“=” hoặc “+” hoặc “-”)

3. Cách sử dụng Sumif nâng cao: Bất đẳng thức

3.1. Các bất đẳng thức trong hàm excel

Phần này chúng ta sẽ học Cách sử dụng Sumif để tính tổng với những điều kiện là bất đẳng thức:

  • > : Lớn hơn,
  • >= : Lớn hơn hoặc bằng
  • < : Nhỏ hơn,
  • <= : Nhỏ hơn hoặc bằng,
  • <> : Khác

Ở phần 1, chúng ta đã tìm hiểu cách sử dụng hàm sumif với 1 điều kiện theo cách phổ biến đó là tính tổng với điều kiện bằng b.

Trong trường hợp mà ta muốn tính tổng với điều kiện nhỏ hơn, hoặc lớn hơn, hoặc nhỏ hơn hoặc bằng, hay lớn hơn hoặc bằng hay B khác X nào đó.

Trong trường hợp đó ta sử dụng công thức sau:

=Sumif(vùng điều kiện, “Ký hiệu kất đẳng thức” & giá trị của bất đẳng thức, vùng tính tổng)

Ký hiệu bất đẳng thức ở đây bao gồm:

Chúng ta cũng xem ví dụ bên dưới để thấy rõ hơn cách dùng trong trường hợp này.

3.2. Ví dụ thực hành hàm sumif nâng cao

sumif-2

Trên đây là một ứng dụng của excel trong kế toán quản trị dùng để tính toán tỷ lệ giữa các nhóm đối tượng trên tổng thể.

Phân tích lại yêu cầu bài toán:

Tính tổng doanh số (sum) của các khách hàng thỏa mãn điều kiện (if) có dư nợ cuối kỳ lớn hơn 120 triệu.

Như vậy,

  • Doanh số là cái dùng để tính tổng, vùng tính tổng vẫn là E3:E16,
  • Vùng điều kiện sẽ khác so với ví dụ trên, sẽ là vùng số số dư nợ từ G3:G16.
  • Điều kiện tính tổng ở đây là số dư nợ của khách lớn hơn 120 triệu. Ta sẽ diễn giải điều kiện này dưới dạng công thức như sau: “>”&120000000.

Và cuối cùng, tại ô J14 ta có công thức:

=Sumif($G$3:$G$16,“>”&120000000,$E$3:$E$16)

Tại sao ở đây chúng ta lại dùng dấu “ ”  và dấu “&”.

Vì hàm Sumif chỉ hiểu điều kiện của nó là một chuỗi hoặc 1 giá trị.

  • Nếu là giá trị, ta không cần dấu “ ”.
  • Nhưng, giống với hầu hết các hàm excel khác, nếu trong công thức là chuỗi text. Chúng ta cần đặt chuỗi đó trong dấu “ ”.

Nếu ta sử dụng trực tiếp là >120000000 thì hàm không hiểu được.

Trên đây, chúng ta đã tìm hiểu về các sử dụng hàm sumif trong việc tính tổng với 1 điều kiện cho trước.

Hi vọng sau khi đọc xong bài viết này, các bạn có thể hiểu và ứng dụng hàm sumif trong công việc của mình, giúp cho công việc trở nên dễ dàng hơn.

Thân,

Admin Học viện Excel Việt Nam

  • Add Your Comment

    Time limit is exhausted. Please reload the CAPTCHA.