File Excel tính lãi lỗ shop online đa kênh, từng bước
Cách dựng file Excel tính lãi lỗ shop online cho Shopee, TikTok Shop và website: bố cục sheet, công thức SUMIFS, XLOOKUP, chia voucher, phí và quảng cáo.
Tác giả Nhóm DataxLand· · 9 phút đọc
Phần lớn shop bán đa kênh bắt đầu tính lãi bằng Excel, và đó là lựa chọn đúng: rẻ, ai cũng mở được, và buộc bạn hiểu từng khoản trừ. Vấn đề không nằm ở Excel mà ở cách dựng file. Một file "tổng doanh thu trừ tổng chi phí" cho ra một con số lãi duy nhất, không nói được kênh nào, sản phẩm nào đang kéo lãi xuống.
Bài này hướng dẫn dựng một file Excel tính lãi lỗ theo dòng sản phẩm của từng đơn, rồi gom lên kênh, sản phẩm và tháng. Cuối bài là các dấu hiệu cho thấy đã tới lúc ngừng làm tay.
Ví dụ số trong bài là ví dụ minh họa với giả định ghi rõ, không phải số của một shop thật. Công thức dùng hàm có trong Excel cho Microsoft 365, Excel 2021 và 2024.
Nguyên tắc: tính ở dòng, gom ở báo cáo
Mọi con số lãi đáng tin đều đi theo một chuỗi (xem bài cách tính lãi thật):
Doanh thu thuần = giá × SL − giảm giá dòng − voucher cấp đơn đã chia − tiền hoàn
Biên đóng góp = Doanh thu thuần − giá vốn − phí sàn − phí thanh toán − vận chuyển shop chịu − phí hoàn
Lãi sau phí = Biên đóng góp − quảng cáo đã phân bổTrong Excel, điều đó nghĩa là mỗi dòng của sheet đơn hàng phải mang đủ các cột trên. Báo cáo theo kênh hay theo sản phẩm chỉ là
SUMIFS trên các cột đã tính sẵn. Nếu bạn tính lãi ở sheet báo cáo bằng cách trừ các tổng, bạn sẽ không bao giờ tách được lãi
theo sản phẩm.
Bố cục sáu sheet
| Sheet | Mỗi dòng là | Cột chính | Lấy từ đâu |
|---|---|---|---|
Don_hang | Một dòng sản phẩm của một đơn | kenh, ma_don, ngay_dat, trang_thai, sku, so_luong, don_gia, giam_gia_dong | File xuất đơn của từng sàn, website |
Voucher_don | Một đơn có voucher shop | ma_don, voucher_shop | File xuất đơn (cột voucher người bán chịu) |
Phi_don | Một đơn | ma_don, phi_san, phi_thanh_toan, ship_shop_chiu, phi_hoan, da_chot | Đối soát, báo cáo thanh toán của sàn |
Gia_von | Một SKU trong một khoảng ngày | sku, tu_ngay, den_ngay, gia_von | Sổ nhập hàng của bạn |
Quang_cao | Một ngày của một kênh | ngay, kenh, chi_phi | Báo cáo của Shopee Ads, TikTok Ads, Meta |
Bao_cao | Một ô tổng hợp | kenh × tháng, sku × tháng | Tính từ các sheet trên |
Biến mỗi vùng dữ liệu thành Bảng (Ctrl + T) và đặt tên đúng như cột thứ nhất. Công thức dạng Don_hang[sku] dễ đọc hơn
B2:B5000 và tự mở rộng khi bạn dán thêm dòng.
Từng bước dựng công thức
Bước 1: chuẩn hóa trạng thái đơn
Mỗi sàn đặt tên trạng thái khác nhau. Thêm một bảng nhỏ Trang_thai với hai cột: trạng thái gốc và trạng thái chuẩn (hoàn tất,
hủy, hoàn hàng, đang giao). Ở Don_hang thêm cột:
trang_thai_chuan = XLOOKUP([@kenh] & "|" & [@trang_thai], Trang_thai[khoa], Trang_thai[chuan], "CHƯA MAP")Lọc cột này theo "CHƯA MAP" mỗi lần dán dữ liệu mới. Đơn hủy không tính doanh thu; đơn hoàn tính doanh thu rồi trừ tiền hoàn.
Bước 2: chia voucher cấp đơn về từng dòng
Voucher của shop áp cho cả đơn, nên phải chia theo tỷ lệ giá trị dòng:
gia_tri_dong = [@so_luong] * [@don_gia] - [@giam_gia_dong]
tong_don = SUMIFS([gia_tri_dong], [ma_don], [@ma_don])
voucher_chia = XLOOKUP([@ma_don], Voucher_don[ma_don], Voucher_don[voucher_shop], 0) * [@gia_tri_dong] / [@tong_don]
doanh_thu_dong = [@gia_tri_dong] - [@voucher_chia]Voucher do sàn tài trợ thì không trừ: người mua được giảm nhưng shop vẫn nhận đủ tiền.
Bước 3: giá vốn theo ngày bán
Giá nhập thay đổi theo lô, nên sheet Gia_von giữ khoảng ngày hiệu lực. Dòng đang dùng để trống den_ngay thì điền một ngày rất
xa (31/12/2099) cho công thức chạy:
gia_von_dv = SUMIFS(Gia_von[gia_von], Gia_von[sku], [@sku], Gia_von[tu_ngay], "<=" & [@ngay_dat], Gia_von[den_ngay], ">=" & [@ngay_dat])
gia_von = [@so_luong] * [@gia_von_dv]
co_gia_von = [@gia_von_dv] > 0Cột co_gia_von quan trọng hơn bạn nghĩ: SKU chưa có giá vốn sẽ cho giá vốn bằng 0 và lãi đẹp giả. Ở báo cáo, luôn hiện tỷ lệ
doanh thu có giá vốn.
Bước 4: chia phí theo đơn về từng dòng
Đối soát của sàn ghi phí theo đơn. Chia về dòng theo tỷ lệ doanh thu dòng trong đơn:
ty_trong = [@doanh_thu_dong] / SUMIFS([doanh_thu_dong], [ma_don], [@ma_don])
phi_dong = (XLOOKUP([@ma_don], Phi_don[ma_don], Phi_don[phi_san], 0)
+ XLOOKUP([@ma_don], Phi_don[ma_don], Phi_don[phi_thanh_toan], 0)
+ XLOOKUP([@ma_don], Phi_don[ma_don], Phi_don[ship_shop_chiu], 0)
+ XLOOKUP([@ma_don], Phi_don[ma_don], Phi_don[phi_hoan], 0)) * [@ty_trong]Đơn chưa có trong đối soát trả về 0 phí, tức lãi bị thổi lên. Thêm cột da_chot để biết bao nhiêu phần trăm phí đã là số thật
(bài phí sàn TMĐT 2026 giải thích vì sao phí còn đổi).
Bước 5: phân bổ quảng cáo
Quảng cáo ghi theo ngày và kênh, không theo đơn. Quy tắc đơn giản và nhất quán: chia chi phí quảng cáo của kênh trong tháng theo tỷ lệ doanh thu của dòng trong kênh tháng đó.
thang = TEXT([@ngay_dat], "yyyy-mm")
qc_thang = SUMIFS(Quang_cao[chi_phi], Quang_cao[kenh], [@kenh], Quang_cao[thang], [@thang])
dt_kenh = SUMIFS([doanh_thu_dong], [kenh], [@kenh], [thang], [@thang])
qc_phan_bo = [@qc_thang] * [@doanh_thu_dong] / [@dt_kenh]
lai_sau_phi = [@doanh_thu_dong] - [@gia_von] - [@phi_dong] - [@qc_phan_bo]Ghi quy tắc này ở đầu sheet Bao_cao. Ai đọc báo cáo cũng cần biết lãi theo SKU phụ thuộc cách chia quảng cáo.
Bước 6: sheet báo cáo
Lập bảng kênh × tháng với SUMIFS(Don_hang[lai_sau_phi], Don_hang[kenh], $A2, Don_hang[thang], B$1), làm tương tự cho doanh thu
thuần và biên đóng góp. Bảng thứ hai theo SKU, sắp xếp theo lãi tăng dần: dòng trên cùng là sản phẩm cần xem trước.
Ví dụ: một đơn hai sản phẩm
Ví dụ minh họa, giả định: đơn Shopee gồm một chai tinh dầu 300.000 ₫ (giá vốn 140.000 ₫) và một cây nến 60.000 ₫ (giá vốn 25.000 ₫), voucher shop 50.000 ₫, tổng phí của đơn trong đối soát 46.500 ₫.
| Cột | Tinh dầu | Nến | Cả đơn |
|---|---|---|---|
| Giá trị dòng | 300.000 ₫ | 60.000 ₫ | 360.000 ₫ |
| Voucher đã chia | 41.667 ₫ | 8.333 ₫ | 50.000 ₫ |
| Doanh thu dòng | 258.333 ₫ | 51.667 ₫ | 310.000 ₫ |
| Giá vốn | 140.000 ₫ | 25.000 ₫ | 165.000 ₫ |
| Phí đã chia | 38.750 ₫ | 7.750 ₫ | 46.500 ₫ |
| Biên đóng góp | 79.583 ₫ | 18.917 ₫ | 98.500 ₫ |
Nếu không chia voucher và phí, bạn sẽ thấy cây nến có biên 35.000 ₫ (60.000 − 25.000), gần gấp đôi con số thật. Sai số ở một đơn thì nhỏ, nhưng nhân lên hàng nghìn đơn mỗi tháng thì đủ để bạn đẩy quảng cáo nhầm sản phẩm.
Năm lỗi hay gặp trong file Excel tính lãi
- Dán dữ liệu mới đè lên vùng cũ, công thức tham chiếu lệch dòng. Dùng Bảng và chỉ dán thêm ở cuối.
- Trùng đơn khi xuất hai lần chồng ngày. Thêm cột đếm
COUNTIFS([ma_don],[@ma_don],[sku],[@sku])và lọc giá trị lớn hơn 1. - Mã SKU không khớp giữa file sàn và sổ giá vốn (thừa khoảng trắng, chữ hoa thường). Dùng
TRIMvàUPPERở cả hai phía. - Trừ tiền hoàn hai lần: một lần ở doanh thu, một lần nữa ở chi phí. Tiền hoàn chỉ trừ ở doanh thu thuần; phí ship chiều hoàn là khoản riêng.
- Ngày theo giờ quốc tế: một số file xuất thời điểm theo UTC, đơn lúc 6 giờ sáng ngày 1 bị tính vào ngày cuối tháng trước.
Khi nào nên ngừng làm bằng Excel
Excel vẫn ổn khi bạn có một hai kênh, vài trăm đơn mỗi tháng và một người chịu trách nhiệm file. Hãy cân nhắc chuyển khi gặp từ hai dấu hiệu trở lên:
- Mỗi tuần mất hơn nửa ngày chỉ để xuất, dán và sửa định dạng file.
- File chậm tới mức mỗi lần sửa công thức phải chờ; một sheet Excel chứa tối đa 1.048.576 dòng (Microsoft, đọc ngày 16/11/2026), và file chạm giới hạn đó trước khi chạm giới hạn của máy.
- Hai người cùng sửa và không ai chắc bản nào đúng.
- Bạn muốn biết lãi tuần này vào sáng thứ Hai, không phải cuối tháng.
Bài Excel không đủ nữa đi sâu hơn vào các dấu hiệu này. Hàm XLOOKUP dùng
trong bài có ở Excel cho Microsoft 365, Excel 2021 và 2024 (Microsoft, đọc ngày 16/11/2026);
bản cũ hơn thay bằng INDEX và MATCH.
DataxLand làm việc này thế nào
DataxLand làm đúng sáu bước trên, nhưng tự động và có kiểm tra. Bạn tải file Excel (mỗi sheet một bảng, kèm sheet
data_dictionary, xem hướng dẫn file Excel) hoặc nối thẳng Shopee, TikTok Shop. Si đề xuất cách hiểu
dữ liệu: cột nào là giá vốn, trạng thái nào là hủy hay hoàn, phí nào còn tạm tính; bạn duyệt rồi mới build. Voucher, phí và quảng
cáo được chia về dòng theo quy tắc cố định, giá vốn lấy theo ngày bán, và bộ kiểm tra dữ liệu bắt SKU không khớp hay dòng trùng
trước khi số lên dashboard.
Kết quả là dashboard lãi lỗ theo kênh, sản phẩm và tuần, kèm tỷ lệ doanh thu có giá vốn và tỷ lệ phí tạm tính. Xem trang thay Excel thủ công và bảng so sánh DataxLand với Excel, Google Sheets.
Câu hỏi thường gặp
Có file Excel tính lãi lỗ shop online mẫu không?
Bố cục sáu sheet và công thức trong bài là đủ để bạn tự dựng một file. Nếu muốn thử ngay trên số có sẵn, workspace mẫu của DataxLand chạy trên dữ liệu giả lập của một shop ba kênh, có cả các bảng đơn, phí, giá vốn và quảng cáo.
Nên chia quảng cáo theo doanh thu hay theo số đơn?
Theo doanh thu là cách phổ biến và dễ giải thích. Theo số đơn hợp với shop có giá bán các sản phẩm gần nhau. Cách nào cũng được, miễn là cố định và ghi rõ; đổi cách chia giữa các tháng sẽ làm lãi theo SKU không so sánh được.
Google Sheets có làm được như Excel không?
Được. Google Sheets có SUMIFS và XLOOKUP. Giới hạn là dung lượng: Sheets chậm đi nhanh khi số dòng và công thức tăng, nên hợp
với shop có lượng đơn vừa phải.
Bao lâu nên cập nhật file một lần?
Tối thiểu mỗi tuần cho đơn và quảng cáo, mỗi kỳ đối soát cho phí. Cập nhật thưa hơn thì bạn chỉ biết một kênh lỗ khi đã lỗ cả tháng.
Đọc tiếp: Cách tính lãi thật trên Shopee, TikTok Shop, Lazada · Excel không đủ nữa · Bắt đầu nhanh