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 cách tính bình quân trong Excel với 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 mà đơ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ỳ
• Bình quân sau mỗi lần nhập

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 tính bình quân gia quyền trong Excel

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

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ỳ.

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

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

Cách tính bình quân cả kỳ

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.

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

Ví dụ cách tính bình quân gia quyền trong Excel

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 trong Excel này, chúng ta có bảng tính sản phẩm như sau:

Trong bảng trên, ta có thể thấy Đơ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

Trong Excel, chúng ta có thể tính toán bằng cách dùng tham chiếu tọa độ như sau:

• Đơn giá bình quân lúc nhập lần 2:  

=(E3+E4)/(D3+D4)

hoặc:

=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)

hoặc

=SUM(E3:E5)/SUM(C3:C5)

Từ đây ta có công thức tổng quát ở ô F3 như sau:

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

Note: 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)

Cách tính bình quân gia quyền với nhiều mặt hàng

Vì có nhiều 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 tính bình quân trong trường hợp này:

Đ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.

Ta nhập hàm bình quân gia quyền trong Excel vào ô F3 như sau:

=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ột E) của mặt hàng tại ô A3 khi giá trị trong cột A trùng với giá trị hiện tại
• SUMIF($A$3:A3,A3,$C$3:C3) là tổng số lượng mặt hàng tại ô A3

Tính đơn giá công thức bình quân gia quyền khi xuất hàng

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

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,"<>"),"")

Từ đây ta sẽ phân tích sâu hơn:

IF(C4<>”” : Nếu C4 không phải giá trị rỗng thì sẽ tính đơn giá xuất

SUMIFS($F$3:F3, $A$3:A3, A4, $B$3:B3, "<>"): Tính tổng các giá trị trong cột F (Thành tiền) theo các tiêu chí:

• Giá trị trong cột A phải khớp với giá trị trong ô A4 (Cam).
• Giá trị trong cột B phải khác rỗng.

/SUMIFS($D$3:D3, $A$3:A3, A4, $B$3:B3, "<>"): Đây là phần mẫu số của công thức. Nó tính tổng các giá trị trong cột D (ví dụ: tổng số lượng) theo các tiêu chí tương tự như trên.

Xem thêm: Cách tính đơn giá trong Excel bằng hàm VLOOKUP chuẩn

Tính đơ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.

Nhập hàm tính bình quân gia quyền trong excel vào ô 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,”<>”): tính tổng thành tiền 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,”<>”): tính tổng 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ỳ.

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ách tính 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ả.