Cách thực hiện công thức bình quân gia quyền trên Excel

Hướng dẫn kế toán sử dụng Excel để thực hiện công thức bình quân gia quyền cực nhanh, đảm bảo chính xác. Hãy xem hướng dẫn chi tiết của Gitiho ngay nhé.

Quy tắc tính của công thức bình quân gia quyền

Thường người ta sử dụng phương pháp tính này để tính những mặt hàng có đơn giá có sự thay đổi. Sẽ có 2 cách tính thường thấy cho công thức bình quân gia quyền sau đây:

• Bình quân cả kỳ: Cách tính này sẽ đợi đến cuối kỳ để tính một đơn giá bình quân. Đơn giá này cũng sẽ được áp dụng cho các lần xuất hàng khác trong kỳ.
• Bình quân sau mỗi lần nhập: Trái ngược với phương pháp đầu tiên, phương pháp thứ 2 này sẽ được tính trung bình sau mỗi lần nhập hàng để lần xuất sau sẽ tính theo đơn giá mới.

Xem thêm: Cách tìm đơn giá của mã hàng được tạo bởi các ký tự ghép lại bằng hàm VLOOKUP nhanh chóng

2 công thức bình quân gia quyền kế toán cần biết

Công thức bình quân gia quyền: Bình quân sau mỗi lần nhập

Ví dụ ta tính đến lần nhập thứ 3:

Công thức tổng quát:

Xem thêm: Cách viết công thức Excel trong Conditional Formatting và Data Validation

Công thức bình quân gia quyền: Bình quân cả kỳ

Công thức tổng quát:

Xem thêm: Cách đếm số lượng Sheet bằng công thức Excel

Sử dụng công thức bình quân gia quyền trong Excel

Cách tính công thức bình quân gia quyền sau mỗi lần nhập

Để dễ hình dung công thức tính bình quân gia quyền này, chúng ta có bảng tính sản phẩm như sau:

Đơn giá khác nhau đến từ số lần nhập khác nhau. Nhiệm vụ của chúng ta là tính đơn giá bình quân sau mỗi lần nhập.

Ở lần nhập thứ 2 ta có:

• Đơn giá bình quân lần nhập 2 sẽ là: =(1.600.000 + 720.000) / (50 + 20) = 33.142,85714
• Đơn giá bình quân lần nhập 3: =(1.600.000 + 720.000 + 3.000.000) / (50 + 20 + 100) = 31.294,11765

Thay vì cách dùng tham chiếu tọa độ thì ta sẽ có:

• Đơn giá bình quân lúc nhập lần 2:  =(E3+E4)/(D3+D4) = SUM(E3:E4)/SUM(C3:C4)
• Đơn giá bình quân lúc nhập lần 3: =(E3+E4+E5)/(D3+D4+D5) = SUM(E3:E5)/SUM(C3:C5)

Từ đây ta có công thức tổng quát:

F3=SUM($E$3:E3)/SUM($C$3:C3)

Cố định các điểm E3, C3 vì hai điểm này không thay đổi trong các công thức.

Nhập công thức từ F3 tới F8 để ra kết quả:

Lưu ý: Ở công thức này có thể sẽ không thể thấy phần số lẻ trong kết quả đơn giá bình quân  (ĐG. Bình quân). Trên thực tế, nếu không muốn thấy số lẻ thì có thể kết hợp thêm các hàm làm tròn như ROUND trong Excel.

Xem thêm: Hướng dẫn cách tính Chi phí vốn bình quân gia quyền (WACC)

Xen lẫn nhiều mặt hàng khi tính công thức bình quân gia quyền

Vì có điều kiện mã hàng nên ở đây chúng ta phải dùng hàm SUMIF để tính công thức bình quân gia quyền cho nhiều mặt hàng. Và sau đây là cách làm:

Điều kiện để tính ở đây là "Tên hàng" trong cột A, vậy nên mỗi mặt hàng khác nhau sẽ có đơn giá khác nhau nhưng phải đảm bảo đều là đơn giá trung bình sau mỗi lần nhập.

F3=SUMIF($A$3:A3,A3,$E$3:E3)/SUMIF($A$3:A3,A3,$C$3:C3)

Cụ thể:

• SUMIF($A$3:A3,A3,$E$3:E3) là tổng thành tiền của mặt hàng tại ô A3
• SUMIF($A$3:A3,A3,$C$3:C3) là tổng số lượng mặt hàng tại ô A3

Xem thêm: Hướng dẫn cách dùng hàm SUMIFS lập báo cáo tổng hợp theo từng tháng chi tiết nhất

Hướng dẫn sử dụng đơn giá công thức bình quân gia quyền khi xuất hàng

Công thức bình quân gia quyền ở đây sẽ là: 

G4=IF(C4<>””,SUMIFS($F$3:F3,$A$3:A3,A4,$B$3:B3,”<>”)/SUMIFS($D$3:D3,$A$3:A3,A4,$B$3:B3,”<>”),””)

• IF(C4<>”” : Nếu C4 khác rỗng thì sẽ tính đơn giá xuất
• SUMIFS($F$3:F3,$A$3:A3,A4,$B$3:B3,”<>”): Đây là tổng tiền của hạng mục "Nhập" và "Tồn đầu" của mặt hàng đang xét tại ô A4 tính đến trước thời điểm xuất hàng (các vùng range đều chỉ xét tời A3). Cố định điểm đầu là dòng 3, thế nhưng điểm cuối lại không cố định và tăng dần khi xét những lần xuất tiếp theo.
• SUMIFS($D$3:D3,$A$3:A3,A4,$B$3:B3,”<>”): Tổng số lượng "Nhâp" và "Tồn đầu" của mặt hàng đang xét trên ô A4,

Những vị trí có "Xuất hàng" thì đơn giá sẽ được tính bình quân trước thời điểm thực hiện hoạt động xuất và chỉ tính theo phần "Nhập" và "Tồn đầu kỳ". Bên cạnh đó thì kết quả của 2 lần xuất sẽ có đơn giá khác nhau.

Xem thêm: Hướng dẫn chi tiết cách tính bình quân gia quyền trong Excel

Đơn giá bình quân gia quyền cả kỳ

Ta thấy rõ ràng rằng đơn giá bình quân gia quyền của cả kỳ có kết quả giống nhau ở 2 lần xuất hàng và cũng khác so với đơn giá bình quân sau mỗi lần nhập.

Công thức để tính phương pháp này:

G4=IF(C4<>””,SUMIFS($F$3:$F$11,$A$3:$A$11,A4,$B$3:$B$11,”<>”)/SUMIFS($D$3:$D$11,$A$3:$A$11,A4,$B$3:$B$11,”<>”),””)

Trong đó:

• IF(C4<>”” : Chẳng hạn C4 khác rỗng thì sẽ tính đơn giá xuất (không tính nhập).
• SUMIFS($F$3:$F$11,$A$3:$A$11,A4,$B$3:$B$11,”<>”): cùng là công thức tính thành tiền nhưng công thức này khác với công thức tính bình quân gia quyền sau mỗi lần nhập ở vùng tham chiếu không phải chỉ cố định 1 điểm đầu rồi tăng dần mà cố định sẵn cho cả kỳ từ dòng 3 đến dòng 11.
• SUMIFS($D$3:$D$11,$A$3:$A$11,A4,$B$3:$B$11,”<>”) : Đây cũng là một công thức tính thành tiền khác mà phần tính số lượng cũng tham chiếu cố định từ dòng 3 -> dòng 11.

Qua 2 cách tính trên ta chỉ cần dùng duy nhất hàm SUMIFS là hoàn thành được nhiệm vụ. Nhưng bạn cũng cần lưu ý rằng việc cố định trong vùng tham chiếu sẽ thay đổi phương pháp tính sau mỗi lần nhập hay sau cả kỳ.\

Xem thêm: Hướng dẫn tổng quan về các công thức trong Excel

Kết luận

Hi vọng thông qua bài viết này, các bạn kế toán đã nắm được công thức bình quân gia quyền và biết cách thực hiện nó trên Excel. Nếu các bạn muốn được học kiến thức Excel phù hợp với ngành kế toán thì hãy tham gia vào khóa học dưới đây:

Excel cho Tài chính, Kế toán và Phân tích tài chính

Khóa học được giảng dạy bởi chuyên gia Excel đã có kinh nghiệm đào tạo cho nhân viên của các tập đoàn lớn như Vietinbank, VP Bank, FPT Software, Mobifone,... Tham gia vào khóa học, bạn sẽ biết cách sử dụng Excel để nâng cao tốc độ làm việc, biết cách làm việc chủ động với Excel cho các công việc của ngành kế toán, tài chính và phân tích tài chính. Trong quá trình học, nếu gặp vấn đề khó hiểu thì các bạn có thể hỏi giảng viên trong mục Hỏi - Đáp ngay dưới video bài giảng. Gitiho cam kết rằng câu hỏi của bạn sẽ được chuyên gia Excel giải đáp trong vòng 24h. Chúc các bạn học tập hiệu quả.