Công thức tính tổng hợp Nhập – Xuất – Tồn
1. Tầm quan trọng: Trong quản lý kho, việc phải báo cáo tình hình Nhập – Xuất – Tồn cho cả kỳ (tuần, tháng, quý, năm hoặc thậm chí là ngày, hay bất cứ khi nào sếp yêu cầu) luôn là việc kiến người quản lý kho quan tâm. Đôi khi việc báo cáo N-X-T với bảng tính lớn, hoặc với nhiều điều kiện cùng ...
1. Tầm quan trọng:
Trong quản lý kho, việc phải báo cáo tình hình Nhập – Xuất – Tồn cho cả kỳ (tuần, tháng, quý, năm hoặc thậm chí là ngày, hay bất cứ khi nào sếp yêu cầu) luôn là việc kiến người quản lý kho quan tâm.
Đôi khi việc báo cáo N-X-T với bảng tính lớn, hoặc với nhiều điều kiện cùng lúc sẽ khiến bạn gặp nhiều khó khăn.
Hoặc đôi khi bạn đang bù đầu với việc nhập, xuất hàng, bỗng sếp phán câu xanh rờn: “Tổng hợp cho anh số lượng hàng A xuất trong tuần này, làm ngay nhé anh đang cần! ” . Ô hô, ngay là ngay thế nào, cả tuần có đến hàng trăm phiếu xuất. Cái gì mà “chỉ xuất hàng A“, cái gì mà “Chỉ trong tuần này“. Ngồi cộng tay đến sáng mai cũng chưa xong ý chứ, ngồi đấy mà có ngay.
Đấy, nếu chưa biết bài này, có khi bạn sẽ nghĩ như trên. Nhưng đọc xong bài này, bạn sẽ chỉ mất chưa đến 30s để gửi kết quả cho sếp. WTF? có thật không vậy?
2. Kết quả cần đạt được:
Đổi thời gian => ra kết quả mới
Đổi mã hàng => ra kết quả mới
Thay vì chỉ 1 dòng mã hàng, đưa tất cả các mã hàng khác vào, filldown công thức cho tất cả các mã hàng => bùm! có báo cáo tổng hợp cho cả kho hàng luôn. Quá tuyệt vời!
Công thức nào để làm việc đó? Cách sử dụng và hiểu công thức như thế nào để vận dụng? Đọc tiếp sẽ rõ.
3. Cách làm
Yêu cầu là tính tổng => sẽ liên quan tới hàm tính tổng (có chữ SUM)
Có kèm theo điều kiện => sẽ liên quan tới IF
Có nhiều điều kiện cùng lúc => IF số nhiều, là IFS
Kết hợp lại, cái ta cần biết ở đây là hàm SUMIFS
Từ phiên bản office 2007 trở lên, excel cung cấp cho chúng ta 1 công cụ rất mạnh trong việc tính toán dữ liệu với nhiều điều kiện, đó là hàm SUMIFS
Cấu trúc hàm này như sau:
=SUMIFS(sum_range,criteria_range1,criteria1,[criteria_range2,criteria2],…)
- sum_range : Vùng tính tổng / Vùng chứa kết quả cần tính
- criteria_range1 : Vùng chứa điều kiện thứ nhất
- criteria1 : Điều kiện thứ 1 (nằm trong vùng điều kiện thứ 1)
- criteria_rang2 : Vùng chứa điều kiện thứ hai (có thể có hoặc không)
- criteria2 : Điều kiện thứ 2 (nằm trong vùng điều kiện thứ 2, có thể có hoặc không. Nếu có Criteria_range2 thì buộc phải có Criteria2)
Điều kiện thứ n viết tương tự điều kiện thứ 2
=> Tính tổng dựa trên dữ liệu có nhiều điều kiện thỏa mãn đồng thời trên cùng 1 dòng
Phân tích cách sử dụng hàm
Ta gọi tên các vùng trong bảng cần tính (A2:G22) như sau: (chức năng đặt tên: Formulas/Name Manager/Define Name – công việc này giúp cho việc phân tích công thức dễ hiểu hơn)
| Nội dung | Tên sử dụng | Tọa độ |
| Cột Mã hàng | Cot_MaHang | $B$3:$B$22 |
| Cột Tên hàng | Cot_TenHang | $C$3:$C$22 |
| Cột Ngày | Cot_Ngay | $E$3:$E$22 |
| Cột Số lượng nhập | Cot_Nhap | $F$3:$F$22 |
| Cột Số lượng xuất | Cot_Xuat | $G$3:$G$22 |
Ở đây có 1 khái niệm là “Kỳ”. Đây là thuật ngữ kế toán, có hàm ý chỉ 1 khoảng thời gian xác định, có điểm đầu kỳ và điểm cuối kỳ.
Đầu kỳ: Lớn hơn hoặc bằng Từ ngày (Ô J3 trong hình bên trên)
Cuối kỳ: Nhỏ hơn hoặc bằng Đến ngày (Ô J4 trong hình bên trên)
Trong kỳ: Khoảng thời gian từ J3 đến J4. Đây được gọi là 1 kỳ.
Tổng nhập trong kỳ
=SUMIFS(Cot_Nhap,Cot_MaHang,$C$33,Cot_Ngay,”>=”&$C$31,Cot_Ngay,”<=”&$C$32)
| sum_range | Cot_Nhap | là cột chứa số lượng nhập, do kết quả cần tính là Tổng số lượng nhập | |||||
| criteria_range1 | Cot_MaHang | Điều kiện thứ 1 là mã hàng phải là mã chọn ở ô C33 | |||||
| criteria1 | ô C33 | ||||||
| criteria_range2 | Cot_Ngay | Điều kiện thứ 2 là Ngày nhập phải lớn hơn hoặc bằng Từ ngày | |||||
| criteria2 | ô C31 (Từ ngày) | ||||||
| criteria_range3 | Cot_Ngay | Điều kiện thứ 3 là Ngày nhập phải nhỏ hơn hoặc bằng Đến ngày | |||||
| criteria3 | ô C32 (Đến ngày) | ||||||
Lưu ý: Cùng là điều kiện ngày, nhưng có 2 nội dung nên phải tách ra thành 2 điều kiện. Không được ghép làm 1 sẽ không đúng
Tổng xuất trong kỳ
=SUMIFS(Cot_Xuat,Cot_MaHang,$C$33,Cot_Ngay,”>=”&$C$31,Cot_Ngay,”<=”&$C$32)
Tìm hiểu ngay: học Excel kế toán
Tồn đầu kỳ
Tồn đầu kỳ được hiểu là: Tổng nhập cho tới trước kỳ này (trước thời điểm đầu kỳ) – Tổng xuất cho tới trước kỳ này (trước thời điểm đầu kỳ)
Tổng nhập đầu kỳ =SUMIFS(Cot_Nhap,Cot_MaHang,$C$33,Cot_Ngay,”<“&$C$31)
Tổng xuất đầu kỳ =SUMIFS(Cot_Xuat,Cot_MaHang,$C$33,Cot_Ngay,”<“&$C$32)
Tồn đầu kỳ = SUMIFS(Cot_Nhap,Cot_MaHang,$C$33,Cot_Ngay,”<“&$C$31)-SUMIFS(Cot_Xuat,Cot_MaHang,$C$33,Cot_Ngay,”<“&$C$31)
Tồn cuối kỳ
Tồn cuối kỳ được hiểu là: Tồn đầu kỳ + Tổng nhập trong kỳ – Tổng xuất trong kỳ
Ở đây ta đã tính được cụ thể cả 3 chỉ tiêu trên, do đó chỉ cần lấy các vị trí ô chứa kết quả thực hiện phép tính là được.
4. Nhận xét
- Sumifs là 1 công thức hay, mạnh mẽ, dễ sử dụng. Áp dụng được trong rất nhiều trường hợp. Thậm chí có thể thay thế hoàn toàn cho hàm sumif.
- Lưu ý là SUMIFS chỉ dùng được trên phiên bản office 2007 trở lên.
- Trong công việc quản lý kho, đôi khi hàm này còn được dùng thay thế cho hàm đếm (countif / countifs)
- Hàm này thường xuyên sử dụng để tính tổng Nhập / Xuất / Tồn trong hầu hết các trường hợp.
- Hàm tương đương: SUMPRODUCT. Nhưng so về tốc độ và cách sử dụng thì SUMIFS có nhiều lợi thế hơn.
Nội dung này là một phần của khoá học IM101 – Tự tạo phần mềm quản lý kho với Excel và VBA.
Để biết thêm các kiến thức, kỹ thuật quản lý kho trong excel, bạn hãy tham khảo thêm nội dung của cả khoá học để biết được điều bổ ích hơn cho bản thân.
