Để tính thành tiền, ta thường sử dụng công thức Thành tiền = Số lượng * Đơn giá. Tuy nhiên, trong thực tế tình huống sẽ phức tạp hơn bởi vì có chiết khấu theo số lượng, theo loại sản phẩm, chiết khấu đơn giá, giảm giá theo mức chi tiêu,…. Dưới đây mình sẽ hướng dẫn chi tiết cách tạo dữ liệu và tính thành tiền trong Excel:
1. Chuẩn bị bảng dữ liệu để tính thành tiền
Thông thường, để tính thành tiền, ta cần phải tạo một bảng dữ liệu gồm đầy đủ các cột: Mã SP, Tên SP, Mã loại, Số lượng, Đơn giá. Ví dụ ta có một bảng dữ liệu về tính thành tiền đơn hàng như sau:
Xem ngay File tính thành tiền theo số lượng và đơn giá kèm công thức dùng sẵn
2. Cách tính thành tiền
2.1. Thành tiền = Số lượng * Đơn giá (không có chiết khấu)
Nếu thành tiền không có giảm giá hay chiết khấu, ta chỉ cần sử dụng công thức Thành tiền = Số lượng * Đơn giá. Áp dụng vào ví dụ trong bài:
- Ta tạo cột Thành tiền ở cột F
- Tại ô F2 nhập công thức
=D2*E2(trong đó D2 là số lượng, E2 là đơn giá tương ứng)
Ta kéo và áp dụng công thức ở F2 cho toàn bộ cột F để được:
2.2. Tính thành tiền khi có giảm giá theo số lượng
Nếu có chương trình khuyến mại/chiết khấu dựa trên số lượng sản phẩm/đơn đặt hàng thì ta sẽ tạo thêm cột % Giảm giá tại cột H và sử dụng hàm IF để tính số % giảm giá tương ứng với số lượng của mỗi sản phẩm.
Giả sử ta tính chiết khấu theo số lượng như sau:
- Nếu số lượng >= 10 ⇒ giảm 5%
- Nếu SoLuong > 20 ⇒ giảm 10%
- Nhỏ hơn 10 thì ⇒ 0%
Vậy tại ô H2 ta nhập công thức sau:
=IF(AND(D2>=10,D2<20),5%,IF(D2>20,10%,0))
Trong công thức trên ta sử dụng hàm IF(điều_kiện, giá_trị_đúng, giá_trị_sai):
- Nếu D2>=10 và < 20 ⇒ trả 5%
- Nếu không, kiểm tra D2>20 ⇒ trả 10%
- Còn lại 0%
Áp dụng công thức với toàn bộ cột H ta được dữ liệu % giảm giá:
Khi có % giảm giá ta sẽ tính số tiền được giảm bằng cách lấy Thành tiền * % Giảm giá . Ta tạo cột số tiền giảm tại cột H, rồi nhập vào ô H2 công thức sau:
=G2*D2*E2
Bây giờ ta tính thành tiền sau giảm giá bằng công thức Thành tiền sau giảm giá = Thành tiền - Số tiền giảm. Ta tạo cột và nhập vào ô I2 công thức:
= D2*E2 - H2
2.3. Tính thành tiền khi có giảm giá theo mã loại sản phẩm
Để tính thành tiền khi có mã giảm giá theo loại sản phẩm (ví dụ: Loại 1 giảm giá 10%, loại 2 giảm giá 15%,…) thì ta cũng sẽ sử dụng hàm IF để tính % giảm giá dựa trên mã loại sản phẩm.
Ví dụ nếu là loại 1 thì giảm giá 30% thì ta sẽ có % giảm giá được xác định bởi công thức:
=IF(C2=1,30%,0)
Sau khi đã có giảm giá thì ta cũng áp dụng công thức để tính số tiềm giảm giá và thành tiền sau khi giảm giá như mục 2.2:
2.4. Tính thành tiền khi giảm giá theo giá trị đơn hàng
Nếu thành tiền (giá trị đơn hàng) vượt các mốc giá trị thì ta giảm một lượng tiền nhất định hoặc % giảm giá. Ví dụ như:
- Nếu giá trị đơn hàng trên 500.000 thì giảm 20.000, trên 1.000.000 thì giảm 50.000 và trên 2.000.000 thì giảm 120.000.
Lúc này để tính thành tiền ta sẽ làm lần lượt như sau:
Bước 1: Ta sẽ tạo một bảng tham số tại 1 sheet khác:
- A2:A5 (Moc_Tien): 500.000; 1.000.000; 2.000.000
- B2:B5 (Giam_CoDinh): 20.000; 50.000; 120.000
Bước 2: Tính số tiền giảm cố định
Tại bảng tính thành tiền, ta vẫn tính thành tiền bằng cách lấy đơn gián * số lượng. Sau đó ta tính số tiền giảm cố định bằng cách sử dụng hàm VLOOKUP kết hợp với hàm IFERROR như sau:
=IFERROR(VLOOKUP(F2,'99_Tham_so'!$A$2:$B$5,2,TRUE),0)
Trong đó, công thức hàm VLOOKUP(giá_trị_cần_tra, bảng_tra, cột_kết_quả, [xấp_xỉ]) với:
- Ở đây dùng
TRUE(xấp xỉ): Excel sẽ chọn mốc lớn nhất ≤ Thành tiền. - Lưu ý rằng Cột mốc (A2:A5) phải tăng dần để xấp xỉ đúng.
- Ta bọc hàm
IFERROR(...,0)để trả 0 nếu không tìm được.
Lúc này ta sẽ có giá trị cột giảm tiền cố định như sau:
Sau đó ta sẽ lấy tính thành tiền sau giảm giá cố định với công thức:
= MAX(F2 - G2, 0)
Ta sử dụng hàm MAX để tránh phần tiền giảm cố định làm âm số tiền.
Trong trường hợp giảm giá theo % dựa trên giá trị đơn hàng thì ta cũng tạo bảng tham chiếu % giảm giá như trên rồi áp dụng theo các cách làm như trên.
Trên đây là hướng dẫn chi tiết và các trường hợp cụ thể để tính thành tiền theo đơn giá và số lương trong Excel! Chúc các bạn áp dụng thành công!
