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

Với công nghệ hỗ trợ Excel, công việc của kế toán viên phần nào được nhẹ nhàng hơn. Hãy cùng Gitiho tìm hiểu phương pháp bình quân gia quyền trong Excel qua bài viết này.

Quy tắc tính của phương pháp 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 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 những ký tự ghép lại bằng hàm VLOOKUP nhanh gọn

Cách tính bình quân gia quyền

Phương pháp 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 :

Phương pháp bình quân gia quyền: Bình quân cả kỳ

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

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

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

Để dễ hình dung phương pháp 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 tất cả 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 những điểm E3, C3 vì hai điểm này không đổi khác trong những công thức .Nhập công thức từ F3 tới F8 để ra hiệu 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 Ngân sách chi tiêu vốn bình quân gia quyền ( WACC )

Xen lẫn nhiều mặt hàng khi tính 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 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ẫu sản phẩm khác nhau sẽ có đơn giá khác nhau nhưng phải bảo vệ đề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 giải trình tổng hợp theo từng tháng chi tiết cụ thể nhất

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

Chúng ta sẽ cùng dựa vào bảng dữ liệu này:Chúng ta sẽ cùng dựa vào bảng tài liệu này :

Công thức tính đơn giá 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,”<>”),””)

Từ đây ta sẽ phân tích sâu hơn:Từ đây ta sẽ nghiên cứu và phân tích sâu hơn :

• 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ẫu sản phẩm đ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 gian triển khai hoạt động giải trí xuất và chỉ tính theo phần ” Nhập ” và ” Tồn thời điểm đầu kỳ “. Bên cạnh đó thì hiệu quả của 2 lần xuất sẽ có đơn giá khác nhau .Xem thêm : Hướng dẫn cụ thể 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ó tác dụng 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 giải 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 bình quâ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 xong được trách nhiệm. Nhưng bạn cũng cần quan tâm rằng việc cố định và thắt chặt trong vùng tham chiếu sẽ đổi khác giải pháp tính sau mỗi lần nhập hay sau cả kỳ .

Qua bài viết trên, các phương pháp tính bình quân gia quyền đã được hướng dẫn chi tiết và đầy đủ nhất. Ngoài ra bạn đọc cũng có thể tham khảo thêm những bài viết bổ ích sau đến từ Gitiho.com

Hướng dẫn cách dùng hàm index trong Excel chi tiết cụ thể nhất

Hướng dẫn cách copy chỉ những giá trị hiển thị sau khi lọc trong Excel

Hướng dẫn cách sắp xếp ngày trong ExcelHướng dẫn cách dùng hàm VLOOKUP qua những ví dụ từ cơ bản đến nâng cao

Hướng dẫn cách sử dụng hàm IF với nhiều điều kiện: AND, OR, hàm IF lồng nhau và hơn thế

Source: http://wp.ftn61.com
Category: Thủ Thuật