Cách sử dụng hàm SUMIF – từ Cơ bản – Nâng cao (PRO)

Hàm sumif được dùng để tính tổng có điều kiện trong excel. (Áp dụng: Excel 2003 2007 2010 2013) Dành cho: Kế toán, Nhân sự, Giáo viên, Chủ cửa hàng, doanh nghiệp,… Lưu ý: Bài viết bao phủ toàn bộ vấn đề liên quan tới hàm sumif nên khá dài. Bạn nên lưu lại bài viết vào trình duyệt chrome của bạn bằng cách nhấn Ctrl + D. Hoặc copy link bài viết và dán vào tường Facebook. Khi cần dùng thì mở lại để xem nhé, đỡ phải mất công tìm lại. Các nội dung chính bạn sẽ được học trong bài này gồm: Định nghĩa, ví dụ và cú pháp Tại sao phải thêm dấu $ trong công thức đối với vùng điều kiện và vùng tính tổng của hàm sumif Cách tính tổng với điều kiện lớn hơn, nhỏ hơn hoặc bằng một giá trị cho trước Cách sử dụng hàm để tính tổng các giá trị khác với 1 giá trị nào đó. Ứng dụng hàm trong công việc thực tế: Kế toán Ứng dụng hàm trong công việc thực tế: Nhân sự Link Download mẫu bài tập hàm đơn giản, điều kiện nhỏ hơn, khác,… của hàm này   1. Định nghĩa, ví dụ và cú pháp về hàm sumif 1.1. Định nghĩa: Là hàm excel được sử dụng để tính tổng có 01 điều kiện trong excel. Đơn giản như bạn có một bảng kê doanh thu bán hàng của 100 mặt hàng với 500 dòng dữ liệu. Làm thế nào để trong 2 giây bạn có được doanh số của mặt hàng bất kỳ. Hàm sumif chính là giải pháp cho bạn. Lưu ý: Cách sử dụng hàm sumif trong excel 2003 giống excel 2007 2010 2013. Do đó bạn chỉ cần biết cách sử dụng trong ví dụ sau là có thể làm được trong các phiên bản khác. Tuy nhiên hàm sumifs (hàm tính tổng nhiều điều kiện trong excel) thì không thể sử dụng trong excel 2003 được nhé. Xem thêm: Cách sử dụng Hàm SUMIFS (Tính tổng nhiều điều kiện)   1.2. Ví dụ hàm sumif: Yêu cầu: Tính tổng lương theo từng cấp bậc Với danh sách 10 người bạn có thể cộng bằng máy tính cho từng cấp bậc. Tuy nhiên, nếu danh sách có +10.000 người thì không thể cộng tay như thế được, bởi: Rất mất thời gian Rủi ro cộng sai là cực lớn Giải pháp tính tổng lương theo cấp bậc: Sử dụng Hàm sumif. Trường sẽ hướng dẫn các bạn các dùng hàm tính tổng để xử lý yêu cầu của bài tập trên ở phần 1.3. 1.3. Cú pháp hàm sumif: = Sumif (Vùng điều kiện, Điều kiện, Vùng tính tổng) Vùng điều kiện: Cột cấp bậc Điều kiện: Cấp bậc (Ví dụ: Nếu tính tổng lương cho Trưởng phòng thì điều kiện là “Trưởng Phòng”) Vùng tính tồng: Cột Tổng thực lĩnh => Hàm sumif sẽ được viết lại như sau khi muốn tính tổng lương cho từng cấp bậc. Vùng Điều kiện: E5:E14 – chính là cột chứa danh sách chức vụ của từng nhân viên Điều kiện: “Phó giám đốc” hoặc “Trưởng phòng” hoặc “Nhân viên” Vùng Tính tổng: G5:G14 – chính là cột chứa lương của từng nhân viên Bạn để ý thấy trong công thức hàm sumif ở trong ảnh trên, ad có thêm các dấu $ vào hàm. Vậy ý nghĩa của dấu $ này là gì. Bạn đọc ở phần dưới nhé. Nó rất quan trọng đấy. Và bạn cũng đừng quên, xuống cuối bài viết để download bài tập mẫu về thực hành. Sau khi đã nghiên cứu kỹ các điểm chính về hàm này được ad nêu sau đây. 1.4. Có 2 cách viết hàm sumif Trong ví dụ này, ta có hai cách viết công thức hàm sumif để tính tổng lương cho các cấp bậc trong công ty. Hai cách viết này chỉ khác nhau ở ĐIỀU KIỆN tính tổng. Cách 1: Điều kiện tính tổng trong trường hợp này là chuỗi text thể hiện tên của từng loại cấp bậc: Phó giám đốc, Trưởng phòng hoặc nhân viên. Lưu ý khi điền một chuỗi text bất kỳ trong một công thức Excel thì luôn phải để trong dấu ” “. Cách 2: Trong bảng tổng hợp tiền lương theo cấp bậc ta đã có cột cấp bậc là K, ví dụ Trưởng phòng có địa chỉ ô là K11, khi đó thay vì phải viết một chuỗi text dài loằng ngoằng ta chỉ cần viết địa chỉ chứa giá trị của chuỗi text đó. Mấu chốt: Hiểu một cách đơn giản: K11 = “Trưởng Phòng” Ad khuyên các bạn nên làm quen và thật thành thạo cách số 2 bởi bạn sẽ tiết kiệm được hàng tá thời gian. Bằng cách tư duy hệ thống, khoa học sẽ giúp bạn giải quyết được nhiều vấn đề khác trong công việc và cuộc sống. 2. Một số lưu ý quan trọng khi sử dụng hàm sumif trong excel 2.1. Tại sao phải thêm dấu $ trong công thức đối với vùng điều kiện và vùng tính tổng. Dấu $ được thêm vào tên dòng để cố định dòng, thêm vào tên cột sẽ cố định cột. Và trong excel ta dùng dấu $ để cố định dòng cột của công thức. Như vậy ta chỉ cần viết công thức cho ô đầu tiên rồi copy cho tất cả các ô còn lại. Khi đó bạn sẽ viết được công thức cho hàng triệu dòng chỉ trong nháy mắt. Đặc biệt khi sử dụng cách 2 để tính tổng tiền lương theo từng cấp bậc. Ta chỉ cần nhập công thức cho dòng đầu tiên (tức là L10 trong ví dụ trên). Sau đó chỉ cần copy công thức xuống cho các dòng còn lại khi đó điều kiện tính tổng của hàm sumif sẽ thay đổi theo thành L11 hoặc L12. Khi copy công thức, việc không cố định vùng tính tổng và vùng điều kiện thì sao? Khi đó địa chỉ vùng sẽ đổi mỗi khi copy sang dòng hoặc cột khác. Điều này dẫn đến kết quả không chính xác. Xem bài viết: Ý nghĩa của dấu $ – cố định vùng dữ liệu trong excel 2.2. Cách tính tổng với điều kiện lớn hơn, nhỏ hơn hoặc bằng một giá trị cho trước Trong ví dụ trên, nếu bạn muốn tính lương của tất cả những ai có lương nhỏ hơn 5.000.000 đ ta sẽ làm như thế nào?. Đa phần người mới sử dụng excel không biết làm thế nào để excel hiểu được ý của mình trong trường hợp này. Ad sẽ giúp bạn giải quyết khó khăn. Mấu chốt của hàm sumif trong trường hợp này là điều kiện “nhỏ hơn 5 triệu đồng” viết như thế nào để excel hiểu được. Trong excel thì toán tử >, <, =,… được sử dụng rất nhiều để so sánh 2 hay nhiều giá trị với nhau. Trong trường hợp này thì toán tử < sẽ giúp các bạn giải quyết được khúc mắc. Cách sử dụng hàm sumif để tính tổng giá trị với điều kiện lớn hơn, nhỏ hơn như sau: = Sumif (G5:G14, “<“&5000000,”G5:G14) = 10.800.000 đ. Lưu ý: Nếu bạn chỉ viết <5000000 trong phần điều kiện của hàm sumif thì excel sẽ không hiểu được bạn muốn gì đâu bạn nhé. 2.3. Cách sử dụng hàm sumif để tính tổng các giá trị khác với 1 giá trị nào đó. Yêu cầu: Tính tổng lương của cả công ty trừ cấp Trưởng phòng. Ban căng đây, khó phết đấy. Nhưng bạn đừng lo, thủ thuật dùng hàm excel sau đây bạn sẽ giải quyết được khúc xương này. Khó ở đấy là: Làm thế nào để excel hiểu ta muốn nó tính tổng với điều kiện “KHÁC” Khác trong excel được viết như sau: <> Như vậy hàm sumif được viết như thế nào? = SUMIF (E5:E14, “<>”&”TP”,G5:G14) = 48.200.000 đ 2.4. Tính tổng nâng cao với dấu (*) Trong ví dụ trên, Trường muốn tính tổng lương thực lĩnh của những người mang họ Phạm chẳng hạn. Thì chúng ta phải viết công thức như thế nào. Các bạn nhìn hình ở dưới sẽ thấy, nếu chỉ dùng hàm sumif thông thường sẽ rất khó để tính tổng với điều kiện trên. Bởi có nhiều người có họ Phạm như: Phạm Đức Vũ, Phạm Huỳnh Thảo,… Giải pháp: Điều kiện tìm kiếm ta sẽ kết hợp với dấu * ngay sau chữ Phạm là được. Công thức như sau: = Sumif (D5:D14, “Phạm*”, G5:G14) => Kết quả trả về: 20.500.000 đ Tương tự bạn có thể dùng công thức trên để tính tổng lương thực lĩnh của những ai là Nhân viên khi vùng điều kiện là cột Mã nhân viên. Tức là ta không cần dùng tới cột Cấp bậc vẫn thống kê được bình thường. Công thức như sau: Tổng lương nhân viên = Sumif (C5:C14, “NV*”, G5:G14) 3. Vận dụng hàm vào công việc thực tế Ad mong muốn chia sẻ với các bạn không chỉ kiến thức cơ bản về hàm excel. Bạn còn phải biết làm sao để vận dụng nó vào công việc giúp cải thiện hiệu suất làm việc. Và với hàm sumif cũng không phải ngoại lệ. Ad sẽ bổ sung dần dần nội dụng ứng dụng thực tế của hàm này mỗi khi ad có thêm kiến thức mới. Dưới đây là các nội dung cập nhật nhất. 3.1. Ứng dụng hàm trong công việc của người làm Kế toán. Nếu là người học và làm kế toán thì phải biết sử dụng excel để phục vụ công việc của mình. Trong các file kế toán excel, có một phần rất quan trọng mà hàm sumif chính là linh hồn của phần đó. Đó chính là bảng cân đối tài khoản hay còn gọi là bảng cân đối số phát sinh. Đây là báo cáo giúp tính tổng giá trị phát sinh trong kỳ của một tài khoản. Tổng số phát sinh nợ tài khoản 1111 = Sumif (Cột số tài khoản hạch toán, 1111, Cột số tiền) Như trong hình dưới đây, Ad đang chọn ô G11 – công thức hiện trên thanh công thức (formula) như sau: = Sumif (Phatsinh!$F$10:$F33, C11, Phatsinh!$J$10:$J$33) Đây là bảng cân đối số phát sinh của Webkynang.vn trong kỳ kế toán 01/2008. Nhiệm vụ của chúng ta là phải tổng hợp được tổng số tiền phát sinh của từng tài khoản trong kỳ kế toán này. Và đây là ứng dụng tiêu biểu của hàm sumif trong kế toán excel Phân tích hàm sumif trong ví dụ này như sau: – Vùng điều kiện: Phatsinh!$F$10:$F33 Cột F bên sheet Phatsinh là cột cập nhật số tài khoản phát sinh bên nợ của nghiệp vụ kinh tế phát sinh F10 -> F33: toàn bộ nghiệp vụ phát sinh trong tháng 01/2008. – Điều kiện: C11 Điều kiện tìm kiếm ở đây là số tài khoản muốn tính tổng giá trị tiền phát sinh trong kỳ kế toán – Vùng tính tổng: Phatsinh!$J$10:$J$33 Cột J là cột số tiền bên sheet Phatsinh. Như vậy, với việc dùng hàm sumif trong bảng cân đối số phát sinh ta đã có được số liệu phát sinh của từng tài khoản trong kỳ kế toán 01/2008. Đây là ứng dụng thiết thực nhất của hàm sumif với người làm kế toán. Ngoài ra, khi bạn đã hiểu bản chất và biết cách vận dụng của các trường hợp tiêu biểu, bạn sẽ còn biết cách vận dụng vào nhiều trường hợp khác nữa. Đừng tự giới hạn bản thân nhé bạn của tôi. 3.2. Ứng dụng vào công việc của người làm Nhân sự Đối với người làm nhân sự, bạn thường xuyên nhận được yêu cầu của cấp trên ví dụ như: Báo cáo cho anh tổng số nhân sự hiện nay là bao nhiêu? Số lượng nhân sự của bộ phận kinh doanh tháng này so với tháng trước tăng như thế nào Cho anh biết chi phí lương của Phân xưởng A, Phân xưởng B tháng này là bao nhiêu. và rất nhiều yêu cầu khác. Rõ ràng bạn phải có một danh sách bảng lương chi tiết của toàn công ty, sau đó dựa vào từng điều kiện (phòng, phân xưởng,…) để bạn tính số lượng nhân sự, chi phí lương phải không nào. Khi đó bạn dùng hàm sumif để tính tổng theo điều kiện được yêu cầu. Cú pháp chung như sau: Tổng lương phòng A = Sumif (Cột phòng ban, “Phòng A”, Cột lương thực lĩnh) Tổng lương Phân xưởng B = Sumif (Cột phân xưởng, “Phân xưởng B”, Cột lương thực lĩnh) Số lượng nhân sự Công đoạn Dệt = Sumif (Cột công đoạn, “Dệt”, Cột số lượng nhân sự) 3.3. Ứng dụng cho việc tổng hợp báo cáo nhập xuất tồn hàng hóa Trong kỳ ta nhập xuất rất nhiều lần và cũng rất nhiều loại hàng hóa. Và ta sẽ dùng hàm tính tổng có điều kiện để thống kê số lượng và doanh thu của từng mặt hàng. Cú pháp hàm như sau: Tổng số lượng mặt hàng A = Sumif (Cột mã hàng, mã hàng A, Cột số lượng) Tổng Doanh thu ngày 15 = Sumif (Cột ngày tháng, Ngày 15, Cột Doanh thu) Với những ai đang kinh doanh nhỏ muốn theo dõi bán hàng hoặc tồn kho thì phải biết hàm này. Bên cạnh đó là biết cách dùng hàm Sumifs nữa bạn nhé. Bởi lẽ hàm sumifs cho phép tính tổng nhiều điều kiện, ví dụ như tổng doanh thu theo: Mặt hàng A Từ ngày … tới ngày… Của cửa hàng B Xem thêm: Phần mềm quản lý kho – bán hàng – công nợ chuyên nghiệp   4. Download mẫu bài tập hàm sumif đơn giản, điều kiện nhỏ hơn, khác,… Học mà không hành bạn sẽ không đạt được nhiều thành công. Tương tự trong excel, dù ad có chia sẻ bao nhiêu bí kíp hay đi chăng nữa mà bạn không thực hành và vận dụng vào công việc hàng ngày của bạn thì chỉ sau một hai ngày bạn sẽ quên ngay và luôn thôi. Vậy thì còn chờ gì nữa, hãy download bài tập thực hành ở link dưới và đừng quên nhấn nút like ở bên trái bài viết để chia sẻ các bí kíp hay này cho bạn bè nhé. Ad xin cảm ơn bạn đã dành thời gian đọc và theo dõi những chia sẻ của ad. Nếu có điểm nào chưa rõ hoặc chưa đúng bạn hãy gửi góp ý về hòm mail: webkynang.vn@gmail.com Link download bài tập thực hành.  Pass giải nén: webkynang.vn Ad sẽ thường xuyên update các bài viết trên website để đem tới nhiều thủ thuật hay tới các bạn. Nên hãy theo dõi thường xuyên nhé. Hi vọng bài viết chia sẻ vừa rồi sẽ giúp các bạn cải thiện kỹ năng excel của mình. Hãy luôn ủng hộ chuyên trang về excel của webkynang.vn nhé các bạn.   Để luôn cập nhật kiến thức về Excel – Bạn nên đăng ký nhận bài viết mới qua email nhé: Click để đăng ký Lưu ý: Sau khi đăng ký, bạn hãy vào email vừa đăng ký để xác nhận đăng ký (Có thể thư kích hoạt bị rơi vào hòm thư spam – bạn hãy vào hòm thư spam để kiểm tra nhé)