File Excel quản lý bán hàng: 6 công thức đủ dùng và bẫy dấu phân cách

Bạn chỉ cần một file excel quản lý bán hàng nếu công việc hiện tại là ghi đơn, tính thành tiền và xem vài tổng cơ bản. Bài này hướng dẫn dựng một sheet từ đầu, chép công thức theo đúng dấu phân cách trên máy, đồng thời kiểm đủ sáu chỉ số thay vì gọi là “sáu công thức” rồi bỏ quên tổng số lượng đã giao.

Đừng tải mẫu vội: việc một sheet này làm được và không làm được

Một sheet phù hợp khi mỗi dòng có thể đại diện cho một đơn hàng đơn giản, gồm mã đơn, ngày, mã sản phẩm, số lượng, đơn giá và trạng thái. Từ đó bạn tính thành tiền từng dòng, cộng doanh thu theo trạng thái, đếm số đơn đã giao, tính giá trị đơn trung bình và cộng số lượng đã giao. Nếu đang tìm “file excel quan ly ban hang mien phi”, đừng chọn mẫu chỉ vì có nhiều màu hoặc nhiều trang; hãy mở phần tiêu đề cột và kiểm tra xem nó có chứa đúng dữ liệu bạn đang nhập hằng ngày hay không.

Mô hình này bắt đầu chật khi một đơn có nhiều sản phẩm nhưng bạn vẫn muốn đếm mỗi đơn một lần, khi hàng có nhiều lô và giá vốn khác nhau, hoặc khi cần theo dõi tiền khách còn nợ, lịch trả nợ, nhập hàng và xuất hàng. Khi đó, một dòng không còn dễ dàng đại diện cho một đơn. Bạn vẫn có thể mở rộng sheet, nhưng trước hết hãy xác định việc cần giải quyết hôm nay là ghi và tổng hợp đơn, hay là kiểm soát cả quy trình bán hàng, kho và công nợ.

Tạo file Excel quản lý bán hàng: cột đủ để công thức chạy

Mở một sheet mới và đặt hàng đầu tiên làm tiêu đề. Dùng cấu trúc đơn giản sau: A là Ngày, B là Mã đơn, C là Mã sản phẩm, D là Tên sản phẩm, E là Số lượng, F là Đơn giá, G là Trạng thái, H là Thành tiền. Quy ước mỗi dòng là một đơn chỉ phù hợp khi một đơn có một sản phẩm hoặc bạn chấp nhận gộp thông tin sản phẩm theo cách của mình. Nếu một đơn có ba sản phẩm và cần lưu từng sản phẩm, hãy dùng ba dòng có cùng mã đơn; khi đó công thức đếm số đơn phải thay đổi, không được đếm số dòng một cách máy móc.

Đừng làm phần báo cáo ngay. Nhập vài dòng thử, gồm ít nhất một đơn đã giao, một đơn đang xử lý và một đơn đã huỷ. Có thể làm theo thứ tự sau:

1. 1Nhập tiêu đề vào các cột A đến H.
2. 2Nhập ngày, mã đơn, mã sản phẩm, tên sản phẩm, số lượng, đơn giá và trạng thái vào một dòng.
3. 3Nhập thêm các dòng có trạng thái khác nhau.
4. 4Để trống cột H trong lúc nhập dữ liệu thử.
5. 5Lưu tệp với một tên dễ nhận biết.

Dấu hiệu đã xong là bạn nhìn thấy các dòng dữ liệu có cùng cấu trúc, số lượng và đơn giá đang ở dạng số, còn trạng thái được viết thống nhất, chẳng hạn chỉ dùng Đã giao, Đang xử lý và Đã huỷ. Nếu ngày hoặc tiền bị căn như văn bản, hãy sửa định dạng ô trước khi chép công thức; công thức có thể vẫn chạy nhưng cho kết quả không đáng tin.

Chép công thức báo cáo: sáu công thức được gọi tên nhưng có sáu chỉ số cần tính

Trước hết, tính thành tiền ở cột H. Chọn ô H2, nhập công thức rồi sao chép xuống các dòng bên dưới. Công thức này là phép nhân số lượng với đơn giá, không phải một chỉ số tổng hợp. Nếu H2 đã hiện đúng số tiền của dòng đầu tiên, kéo chốt điền xuống hoặc sao chép H2 vào các dòng còn lại; dấu hiệu hoàn tất là mỗi dòng có thành tiền tương ứng với E nhân F.

=E2*F2

Đặt khu vực báo cáo ở bên phải bảng, chẳng hạn từ J1 đến K7. Với ba tổng giá trị theo trạng thái, bạn dùng cùng một mẫu SUMIF: cộng cột H khi cột G khớp trạng thái. Ba ô này là ba kết quả riêng, dù chỉ có một kiểu công thức. Bạn có thể đặt nhãn ở J2, J3 và J4 lần lượt là Tổng đang xử lý, Tổng đã giao và Tổng đã huỷ, rồi dùng các công thức sau ở cột K.

=SUMIF($G:$G;"Đang xử lý";$H:$H)

=SUMIF($G:$G;"Đã giao";$H:$H)

=SUMIF($G:$G;"Đã huỷ";$H:$H)

Dấu $ giúp phạm vi không đổi nếu bạn sao chép công thức sang vị trí khác. Sau khi nhập, kiểm tra bằng cách lấy riêng một trạng thái và cộng thủ công vài dòng để đối chiếu. Tiếp theo, đếm số đơn đã giao bằng COUNTIF, tính giá trị đơn trung bình của các dòng đã giao bằng AVERAGEIF, và tính tổng số lượng đã giao bằng SUMIF. Ba công thức này lần lượt trả về ba loại dữ liệu khác nhau: số đơn, tiền trung bình và số sản phẩm.

=COUNTIF($G:$G;"Đã giao")

=AVERAGEIF($G:$G;"Đã giao";$H:$H)

Hai điều cần biết trước khi tin vào ba con số cuối, cả hai đều đã đo trên bảng thật.

`COUNTIF` đếm DÒNG, không đếm đơn. Nó chỉ đúng khi bảng của bạn theo quy ước một dòng là một đơn. Nếu bạn cho một đơn nhiều sản phẩm nằm trên nhiều dòng, cùng một mã đơn sẽ được đếm nhiều lần và con số "số đơn đã giao" trở thành số dòng đã giao. Giữ một dòng một đơn cho tới khi thật sự cần khác đi; lúc đó bài toán đã sang chuyện khác, không giải bằng COUNTIF nữa.

`AVERAGEIF` báo lỗi khi chưa có dòng nào khớp. Thử trên một bảng không có trạng thái "Đã giao" nào: SUMIF trả 0, COUNTIF trả 0, nhưng AVERAGEIF trả #DIV/0! — vì nó chia cho số dòng khớp, và số đó bằng không. Shop mới mở bảng, hoặc ngày chưa giao đơn nào, sẽ thấy đúng ô này đỏ lên. Đó không phải công thức hỏng, chỉ là chưa có dữ liệu để tính trung bình.

=SUMIF($G:$G;"Đã giao";$E:$E)

Nếu bảng của bạn bắt đầu từ hàng 2 và kết thúc ở một hàng xác định, có thể thay phạm vi cả cột bằng phạm vi cụ thể như $G$2:$G$500. Tôi chưa đo cấu hình tệp của bạn nên không khẳng định phạm vi cả cột sẽ luôn phù hợp với mọi bản Excel hoặc LibreOffice; khi file lớn, phạm vi giới hạn thường dễ kiểm tra hơn. Bộ đầu ra đầy đủ là sáu ô công thức tổng kết: một ô thành tiền cho mỗi dòng không tính như một ô báo cáo, ba ô tổng giá trị theo trạng thái, một ô số đơn đã giao, một ô giá trị đơn trung bình và một ô tổng số lượng đã giao. Vì ba tổng trạng thái dùng chung một mẫu, người ta có thể gọi đây là sáu công thức; nhưng nếu yêu cầu thực tế có cả số lượng đã giao thì danh sách kết quả vẫn phải giữ đủ bảy ô.

Thử dấu chấm phẩy trước khi sửa công thức lỗi

Khi công thức báo lỗi, đừng sửa nội dung điều kiện ngay. Mở hai ô trống, nhập mỗi bản một lần. Trên một số thiết lập, bản dùng dấu chấm phẩy sẽ chạy và bản dùng dấu phẩy sẽ báo lỗi; trên thiết lập khác thì ngược lại.

=SUM(1;2)

=SUM(1,2)

Dấu hiệu đã tìm được dấu phân cách là một trong hai ô trả về số 3. Nếu cả hai đều lỗi, kiểm tra bạn có gõ đúng dấu ngoặc và tên hàm không; đừng kết luận ngay do dấu phân cách.

Trong LibreOffice Calc, vào phần tùy chọn của Calc, mở mục Formula và xem khu vực Separators; tên menu có thể khác đôi chút tùy bản cài đặt hoặc ngôn ngữ giao diện. Nếu bản đang dùng hiển thị dấu phân cách đối số là dấu chấm phẩy, dùng các công thức ở trên; nếu là dấu phẩy, đổi toàn bộ dấu ngăn cách đối số sang dấu phẩy. Err:508 có thể liên quan đến dấu ngoặc hoặc cấu trúc biểu thức, nên phép thử ngắn này chỉ giúp loại trừ một nguyên nhân, không phải cách sửa cho mọi lỗi.

Đơn huỷ không bị xoá: dùng trạng thái để đối soát và để tổng tiền không bị lẫn

Giả sử buổi sáng có một đơn trị giá 400.000 đồng, sau đó khách huỷ. Nếu xoá dòng này, tổng doanh thu có thể vẫn đúng theo cách bạn đang tính, nhưng bạn mất dấu vết để đối chiếu với tin nhắn, lịch sử giao hàng hoặc lý do huỷ. Nếu giữ dòng và đổi trạng thái thành Đã huỷ, công thức tổng Đã giao sẽ không cộng dòng đó, còn tổng Đã huỷ vẫn cho biết giá trị đơn đã rời khỏi luồng bán.

Với cột G là trạng thái và cột H là thành tiền, công thức doanh thu đã giao nên lọc rõ trạng thái thay vì cộng cả cột H. Công thức SUMIF ở trên đã làm việc đó. Nếu cần loại nhiều điều kiện hơn, chẳng hạn chỉ tính đơn đã giao trong một khoảng ngày, dùng SUMIFS và thêm cột ngày A:

=SUMIFS($H:$H;$G:$G;"Đã giao";$A:$A;">="&J10;$A:$A;"<="&K10)

Ở đây J10 và K10 phải chứa ngày bắt đầu và ngày kết thúc hợp lệ. Dấu hiệu đã xong là một đơn huỷ không làm tăng tổng đã giao, nhưng vẫn xuất hiện trong danh sách để bạn truy ngược khi đối soát.

Khi cần dùng chung, theo dõi tồn hoặc công nợ: dừng mở rộng sheet này ở đâu

Excel và Google Sheets đều có thể dùng cho một bảng đơn giản. Khi nhiều người cùng nhập, Google Sheets thường thuận tiện vì mọi người mở cùng một tệp trên trình duyệt; Excel cũng có tính năng cùng chỉnh sửa trong những cấu hình hỗ trợ lưu trữ và tài khoản phù hợp. Bản Excel đang dùng, cách lưu file và quyền chia sẻ có thể làm trải nghiệm khác đi, nên hãy thử với đúng tài khoản và thiết bị của nhóm trước khi chuyển toàn bộ dữ liệu. Nếu câu hỏi của bạn là “file excel quan ly ban hang online”, việc cần kiểm tra đầu tiên là ai được sửa cột trạng thái và ai chịu trách nhiệm xử lý hai người sửa cùng một đơn.

Một sheet cũng không tự biết tồn kho thực tế nếu bạn chỉ ghi số lượng bán. Muốn có file excel quan ly xuat nhap ton, bạn cần dữ liệu nhập, xuất, tồn đầu kỳ, mã sản phẩm thống nhất và cách xử lý trả hàng; muốn theo dõi file excel quan ly ban hang cong no, cần thêm khách hàng, số đã thu, ngày hẹn và lịch sử thanh toán. Cứ giữ cách quản lý bằng bảng tính chừng nào người nhập vẫn kiểm tra được từng dòng và biết số liệu lấy từ đâu. Hãy dừng mở rộng khi dữ liệu thường xuyên bị ghi đè, tồn trên sheet không khớp hàng thật, công nợ phải tra từ nhiều tin nhắn, hoặc người dùng không còn biết ô nào được phép sửa. Khi đó, bước tiếp theo không phải thêm thật nhiều công thức, mà là thiết kế lại quy trình và phân quyền nhập liệu.