Muốn biết giá một món vào ngày 15 tháng 3, hay một đơn hàng đã nằm ở trạng thái "chờ giao" bao lâu, bảng phải lưu lịch sử thay vì ghi đè: mỗi lần giá hay trạng thái thay đổi là một dòng mới có ngày bắt đầu hiệu lực, còn giá trị hiện tại chỉ là dòng mới nhất. Ghi đè thì nhanh, nhưng mỗi lần ghi đè là một mẩu quá khứ bị xoá vĩnh viễn.
Bài này dùng ví dụ giả định của một quán cà phê nhỏ ở Lâm Đồng điều chỉnh giá đồ uống vài lần mỗi năm và nhận đơn mang đi. Bạn sẽ thấy ba kiểu bảng lưu lịch sử, cách tra giá tại một ngày bằng công thức bảng tính, cách lưu chuỗi trạng thái của đơn hàng, các quy tắc để lịch sử không tự mâu thuẫn, và cách nhờ AI kiểm khoảng hiệu lực. Nhật ký "ai sửa ô nào" của bảng tính dùng chung là chuyện khác, có bài riêng ở danh mục tự động hoá của trang.
Ghi đè làm mất câu trả lời cho những câu hỏi nào
Bảng thực đơn của quán có cột Giá. Tháng 4 cà phê sữa tăng giá, người quản lý sửa ô đó. Mọi thứ vẫn chạy, cho đến khi cần trả lời những câu như: doanh thu tháng 3 tính theo giá nào, vì sao tổng tiền của một đơn cũ không khớp với giá hiện tại nhân số ly, đợt khuyến mãi tháng trước áp trên giá cũ hay giá mới. Ô Giá chỉ còn biết hiện tại.
Đơn hàng cũng vậy. Bảng đơn có cột Trạng thái, nhân viên đổi từ "mới" sang "đang pha" rồi "đã giao". Đến cuối ngày, cột đó cho biết đơn đã giao, nhưng không cho biết đơn chờ pha mất bao lâu, đơn nào từng bị chuyển sang "huỷ" rồi được khôi phục, hay ai đã đổi trạng thái. Những câu đó lại chính là câu người quản lý muốn hỏi khi khách phàn nàn giao chậm.
Nguyên tắc chung: thông tin nào có thể thay đổi và bạn có lúc cần biết giá trị của nó ở một thời điểm trong quá khứ, thông tin đó cần lịch sử. Thông tin nào chỉ cần giá trị hiện tại, như cách viết tên món cho đẹp, thì ghi đè là đủ. Việc phân loại này nên làm khi thiết kế, vì lịch sử đã mất thì không lấy lại được.
Ghi đè và ba kiểu bảng lưu lịch sử
Có ba cách phổ biến để giữ lịch sử của một thông tin hay thay đổi. Ví dụ dưới là bảng giá đồ uống giả định.
| Kiểu | Cấu trúc | Ưu điểm | Nhược điểm |
|---|---|---|---|
| Ghi đè | Bảng món có cột gia | Đơn giản nhất | Không biết giá trong quá khứ |
| Bảng sự kiện: chỉ ngày bắt đầu | Bảng gia_ban gồm ma_mon, gia, ap_dung_tu; mỗi lần đổi giá thêm một dòng | Không thể chồng khoảng; thêm giá mới chỉ thêm một dòng | Tra giá tại một ngày cần tìm dòng gần nhất trước ngày đó |
| Bảng khoảng: ngày bắt đầu và kết thúc | Như trên, thêm cột ap_dung_den; dòng đang áp dụng để trống | Tra theo ngày bằng điều kiện nằm trong khoảng, dễ đọc | Mỗi lần đổi giá phải sửa ngày kết thúc dòng cũ; dễ sinh khoảng chồng hoặc khoảng hở |
| Bản chụp trong giao dịch | Chi tiết đơn lưu don_gia tại lúc bán | Đơn cũ luôn đúng số tiền đã thu | Không thay được bảng giá; chỉ cho biết giá của những lần đã bán |
Với doanh nghiệp nhỏ làm trên bảng tính, kiểu bảng sự kiện thường an toàn hơn: không có ngày kết thúc thì không có lỗi quên đóng khoảng. Bản chụp giá trong chi tiết đơn nên có trong mọi trường hợp; nó trả lời "khách đã trả bao nhiêu", còn bảng giá trả lời "giá niêm yết lúc đó là bao nhiêu" — hai câu khác nhau, có khi khác số vì giảm giá.
Tra giá tại một ngày bằng công thức
Với bảng sự kiện gia_ban (cột A mã món, cột B giá, cột C áp dụng từ), giá của món ở ô E2 vào ngày ở ô F2 là giá của dòng có ngày áp dụng gần nhất nhưng không sau F2. Làm hai bước. Bước một tìm ngày đó: =MAXIFS(gia_ban!C:C,gia_ban!A:A,E2,gia_ban!C:C,"<="&F2). Bước hai, với ngày tìm được ở ô G2, lấy giá: =SUMIFS(gia_ban!B:B,gia_ban!A:A,E2,gia_ban!C:C,G2). Dùng SUMIFS ở bước hai được vì mỗi cặp món và ngày chỉ có một dòng; nếu có dòng trùng, tổng sẽ gấp đôi và lộ ra ngay.
MAXIFS có trong Excel 2019 trở đi, Microsoft 365 và Google Sheets; bản Excel cũ hơn phải dùng công thức mảng hoặc sắp xếp bảng rồi dò tìm gần đúng. Máy cài vùng Việt Nam có thể cần dấu chấm phẩy thay dấu phẩy. Nếu bước một trả về 0, nghĩa là món chưa có giá nào trước ngày đó — thường do ngày bán sai hoặc thiếu dòng giá đầu tiên.
Trong cơ sở dữ liệu, cùng ý đó viết bằng một câu truy vấn con: chọn giá của dòng có mã món khớp và ngày áp dụng lớn nhất trong những ngày không sau ngày cần tra. Khi nhờ AI viết, mô tả rõ bảng là kiểu chỉ có ngày bắt đầu, nếu không nó hay mặc định có cột ngày kết thúc.
Lịch sử trạng thái đơn hàng
Cách lưu chuỗi trạng thái là một bảng riêng, mỗi lần đơn đổi trạng thái thêm một dòng: mã đơn, trạng thái mới, thời điểm đổi (ngày và giờ), người đổi, ghi chú ngắn. Đơn đi qua năm bước sẽ có năm dòng. Trạng thái hiện tại là dòng có thời điểm muộn nhất của đơn đó.
Nhiều nơi vẫn giữ thêm cột trạng thái hiện tại trong bảng đơn để lọc nhanh. Đó là lưu lặp có chủ ý, với một luật đi kèm: mỗi lần đổi trạng thái phải vừa thêm dòng lịch sử vừa sửa cột hiện tại, không bao giờ chỉ làm một việc. Trong bảng tính, cách đỡ lỗi là để cột hiện tại tính bằng công thức từ bảng lịch sử thay vì gõ tay; trong cơ sở dữ liệu hay phần mềm có quy trình, việc này thường được làm tự động.
Có lịch sử trạng thái rồi, những câu trước đây không trả lời được trở thành phép trừ thời gian: từ "mới" sang "đang pha" mất bao nhiêu phút, đơn nào bị huỷ rồi khôi phục. Đọc ý nghĩa của những con số đó — giờ nào hay chậm, khâu nào nghẽn — là việc phân tích, thuộc về trang AI Phân Tích. Ở đây chỉ cần chắc dữ liệu được ghi đúng để câu hỏi có thể được đặt ra.
Quy tắc để lịch sử không tự mâu thuẫn
Chỉ thêm, không sửa dòng cũ. Giá nhập nhầm thì thêm dòng đính chính có ghi chú, hoặc nếu nhầm ngay trong ngày thì sửa kèm ghi lại người sửa. Lịch sử bị sửa tuỳ tiện không còn là lịch sử.
Mỗi món, mỗi ngày bắt đầu chỉ một dòng. Hai dòng cùng mã món, cùng ngày áp dụng là mâu thuẫn. Đếm bằng COUNTIFS theo cặp mã món và ngày; kết quả lớn hơn 1 là lỗi.
Với bảng khoảng: không chồng, không hở. Ngày kết thúc của dòng cũ phải liền trước ngày bắt đầu của dòng mới. Quy ước rõ ngày kết thúc có tính cả ngày đó hay không, ghi vào từ điển dữ liệu.
Thống nhất cách ghi thời điểm. Giá đổi theo ngày thì chỉ cần ngày; trạng thái đơn cần cả giờ phút. Không trộn ngày trần với ngày giờ trong cùng một cột.
Cho phép ngày hiệu lực trong tương lai. Quán có thể nhập trước bảng giá mới áp dụng từ đầu tháng sau. Công thức tra theo ngày tự lấy giá cũ cho tới đúng ngày đó, không ai phải nhớ sửa vào nửa đêm.
Nhờ AI kiểm khoảng hiệu lực và viết công thức tra cứu
Lỗi trong bảng lịch sử thường không làm công thức báo lỗi, chỉ trả về giá sai. Kiểm bằng dữ liệu thử mà bạn đã biết đáp án là cách chắc nhất.
- Mô tả cấu trúc bảng lịch sử (tên cột, kiểu, quy ước ngày) cho AI, kèm năm đến mười dòng giả định; không cần dữ liệu doanh thu thật.
- Gửi câu lệnh mẫu: "Bảng gia_ban trong Google Sheets gồm ma_mon, gia, ap_dung_tu (chỉ có ngày bắt đầu). Viết công thức trả về giá của một món tại một ngày cho trước, giải thích cách nó xử lý ngày trước giá đầu tiên và hai dòng trùng ngày. Sau đó viết công thức đánh dấu mọi cặp ma_mon và ap_dung_tu bị trùng. Tạo thêm tám trường hợp thử giả định kèm đáp án đúng."
- Tự kiểm ít nhất ba trường hợp thử bằng tay: ngày đúng bằng ngày đổi giá, ngày ngay trước đó, ngày trước giá đầu tiên.
- Áp công thức vào bảng thật trên bản sao, chọn vài đơn cũ mà bạn còn nhớ giá lúc bán và so.
- Ghi quy ước ngày và công thức tra cứu vào từ điển dữ liệu, để người sau không viết lại theo cách khác.
AI hay viết công thức dò tìm gần đúng đòi bảng phải được sắp xếp theo ngày mà không nói rõ điều kiện đó. Hỏi thẳng nó "công thức này có cần bảng đã sắp xếp không" trước khi dùng.
Lịch sử nào đáng giữ, lịch sử nào không
Giá bán, giá nhập, danh mục có hiệu lực theo thời gian như địa giới hành chính, trạng thái đơn và phiếu là những thứ gần như luôn đáng giữ lịch sử: chúng ảnh hưởng tới tiền và tới việc giải thích số liệu cũ. Thông tin mô tả như tên hiển thị, ảnh món thì thường ghi đè được.
Cần cân nhắc kỹ với dữ liệu cá nhân. Lịch sử địa chỉ, số điện thoại, chức vụ, mức lương của nhân viên đều là thông tin nhạy cảm; giữ lịch sử của chúng nghĩa là giữ nhiều dữ liệu cá nhân hơn, lâu hơn. Chỉ giữ khi có lý do nghiệp vụ hoặc nghĩa vụ rõ ràng, giới hạn người được xem, và có thời hạn xoá. Nguyên tắc thu thập tối thiểu và nghĩa vụ pháp lý về dữ liệu cá nhân có hướng dẫn ở trang AI Bảo Mật.
Khi đưa bảng lịch sử vào AI để nhờ viết công thức hay kiểm lỗi, chỉ đưa cấu trúc và dòng giả định. Bảng lịch sử thường dài và chi tiết hơn bảng hiện tại, nên lộ ra cũng nhiều hơn.
Câu hỏi thường gặp
Bảng tính có lịch sử phiên bản rồi thì có cần bảng lịch sử giá không?
Có. Lịch sử phiên bản giúp khôi phục tệp về một thời điểm, nhưng không dùng được trong công thức hay báo cáo. Muốn tính doanh thu theo giá từng thời kỳ, giá phải nằm trong một bảng có cột ngày hiệu lực.
Nên chọn bảng chỉ có ngày bắt đầu hay có cả ngày kết thúc?
Trên bảng tính, bảng chỉ có ngày bắt đầu ít lỗi hơn vì không phải nhớ đóng khoảng cũ. Bảng có ngày kết thúc dễ đọc hơn và một số công cụ truy vấn thuận hơn; nếu dùng, phải có phép kiểm chồng khoảng.
Đơn hàng đã lưu đơn giá lúc bán thì còn cần bảng lịch sử giá không?
Vẫn nên có. Đơn giá trong đơn cho biết khách đã trả bao nhiêu, kể cả sau giảm giá; bảng lịch sử giá cho biết giá niêm yết từng thời kỳ, cần khi tính mức giảm thực tế hoặc trả lời khách hỏi giá cũ.
Lịch sử trạng thái có làm bảng phình to quá không?
Mỗi đơn thêm vài dòng, với quán nhỏ là vài trăm dòng mỗi ngày — bảng tính vẫn chịu được nhiều năm. Khi quá lớn, cất phần cũ sang tệp lưu trữ theo năm và giữ bảng đang dùng gọn.
Giá theo chi nhánh khác nhau thì thiết kế thế nào?
Thêm cột mã chi nhánh vào bảng giá, mỗi dòng là giá của một món tại một chi nhánh từ một ngày. Công thức tra cứu thêm một điều kiện chi nhánh; quy tắc chống trùng tính theo bộ ba món, chi nhánh, ngày.