Cách tính lượng bình quân trong Excel

Phương pháp tình bình quân gia quyền

Phương pháp bình quân áp dụng trong trường hợp những mặt hàng có đơn giá có sự thay đổi. Phương pháp này gồm 2 cách tính:

• Bình quân sau mỗi lần nhập: Sau mỗi lần nhập hàng,đơn giá lạiđược tính trung bìnhđể lần xuất sauđó tính theođơn giá mới.
• Bình quân cả kỳ: Đơn giá sẽ không tính ngay cho từng lần nhập-xuất mà phảiđợiđến cuối kỳ, sẽ tính 1đơn giá bình quân.Đơn giáđó sẽáp dụng chung cho các lần xuất hàng trong kỳ.

Quy tắc tính:

Bình quân sau mỗi lần nhập

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

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

Bình quân cả kỳ

Ứng dụng cách lập công thức bình quân trongExcel

Công thức tính bình quân sau mỗi lần nhập trongExcel

Ví dụ chúng ta có bảng tính như sau:

Ví dụ lần nhập thứ 2, ta có:

• Đơn giá bình quân lần nhập 2 = [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

Vậy thay bằng cách dùng tham chiếu tọa độtrênExcel ta có:

• Đơn giá bình quân lần nhập2 = [E3+E4]/[D3+D4] = SUM[E3:E4]/SUM[C3:C4]
• Đơn giá bình quân lần nhập 3 = [E3+E4+E5]/[D3+D4+D5] = SUM[E3:E5]/SUM[C3:C5]

Tổng quát lại ta có:

F3=SUM[$E$3:E3]/SUM[$C$3:C3]

Cốđịnhđiểm bắtđầu là cácđiểm E3, C3 vì luôn không thayđổi trong các công thức.

Filldown công thức từ F3 tới F8để thuđược kết quả:

Chúý: trong công thức này, các bạn có thể không thấy phần số lẻ trong kết quảĐG. bình quân. Nhưng thực tế là có. Nếu không muốn có số lẻ, các bạn có thể kết hợp thêm các hàm làm tròn trongExcel như hàm ROUND.

Trường hợp xen lẫn nhiều mặt hàng khi tính bình quân

Chúng ta phải dùng hàm SUMIF thay vì dùng hàm SUM, vì có liên quan tớiđiều kiện mã hàng.

Cách làm như sau:

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

Trongđó:

• 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 của mặt hàng tạiô A3

Trường hợpáp dụngđơn giá bình quân khi xuất hàng

Công thức tính như sau:

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,],]

Trongđó:

• 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,] là tổng thành tiền Nhập và Tồnđầu của mặt hàngđang xétởô A4, tínhđến trước thờiđiểm xuất [các vùng range đều chỉ xét tới A3]. Cốđịnhđiểmđầu là dòng 3, cònđiểm cuố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,] là tổng sốố lượng Nhập và Tồnđầu của mặt hàngđang xétởô A4

Như vậy tại những vị trí có Xuất hàng,đơn giá sẽđược tính bình quânđến trước thờiđiểm thực hiện việc xuấtđó, và chỉ tính theo phần Nhập, Tồnđầu kỳ.

Kết quả 2 lần xuất cóđơn giá khác nhau.

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

Có thể thấyđơn giá bình quân cả kỳ có kết quả giống nhauở cả 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ách tính của phương pháp này như sau:

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 :Nếu C4 khác rỗng thì sẽ tínhđơn giá xuất [chỉ tính khi xuất, không tính khi nhập]
• SUMIFS[$F$3:$F$11,$A$3:$A$11,A4,$B$3:$B$11,] :công thức tính thành tiền này khác với bình quân sau mỗi lần nhậpở chỗ 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,] : tương tự với công thức tính thành tiền, phần tính số lượng cũng tham chiếu cốđịnh từ dòng 3 tới dòng 11.

Hi vọng bài viết trên đây sẽ giúp các bạn hiểu rõ cách tính bình quân gia quyền trong excel. Chúc các bạn thực hiện thành công!