Dò đơn giá theo NGÀY HIỆU LỰC khi bảng giá đổi mấy lần trong năm
Đầu bài
Cùng một mã hàng có nhiều dòng trong bảng giá, mỗi dòng một ngày áp dụng. Hoá đơn ngày nào phải ăn đúng giá có hiệu lực tại ngày đó — VLOOKUP thường chỉ lấy dòng nó gặp trước.
Các bước làm
- 1
Sắp bảng giá theo mã rồi tới ngày
Alt › A › S › S mở hộp Sort hai tầng: cấp 1 là Mã hàng, cấp 2 là Ngày áp dụng tăng dần. Mẹo LOOKUP ở bước 4 sống chết dựa vào thứ tự này.
- 2
Tìm mốc hiệu lực gần nhất không vượt quá ngày hoá đơn
=MAXIFS(giá[Ngày], giá[Mã], mã_ô, giá[Ngày], "<="&ngày_ô) — ra đúng ngày bảng giá đang có hiệu lực tại thời điểm bán.
- 3
Từ mốc đó lấy ra đơn giá
=SUMIFS(giá[Giá], giá[Mã], mã_ô, giá[Ngày], mốc_ô). Dùng SUMIFS vì đã khoá đủ hai điều kiện, khỏi dựng cột phụ ghép khoá.
- 4
Gói lại thành MỘT công thức nếu không muốn cột phụ
=LOOKUP(2, 1/((giá[Mã]=mã_ô)*(giá[Ngày]<=ngày_ô)), giá[Giá]) — mẫu LOOKUP(2,1/…) luôn lấy dòng khớp CUỐI CÙNG, bảng đã sắp xếp thì đó chính là giá mới nhất còn hiệu lực.
- 5
Khoá vùng rồi đổ xuống cả cột
Bôi vùng trong công thức rồi F4 cho thành $; Ctrl+Shift+↓ chọn hết cột kết quả rồi Ctrl+Enter điền một lượt, khỏi kéo chuột nghìn dòng.
- 6
Bắt hoá đơn rơi vào khoảng chưa khai giá
=IF(COUNTIFS(giá[Mã], mã_ô, giá[Ngày], "<="&ngày_ô)=0, "Chưa có giá tại ngày này", công_thức) — hàng mới bán trước khi kịp khai giá là ca hay bị bỏ sót nhất.
- 7
Kiểm chéo bằng mắt cho ba mã
Ctrl+Shift+L lọc bảng giá đúng một mã, nhìn xem giá công thức trả về có nằm đúng khoảng ngày không. Soát ba mã là đủ tin cả cột.
Phím dùng trong bài
Bẫy hay dính
Bảng giá chưa sắp xếp thì mẫu LOOKUP(2,1/…) trả về dòng cuối cùng của BẢNG chứ không phải giá mới nhất — sai âm thầm, không lỗi nào hiện ra. Cặp MAXIFS+SUMIFS không phụ thuộc thứ tự nên an toàn hơn khi bảng do người khác giữ.
Cùng nhóm dò tìm, đối chiếu & bắt lệch
- VLOOKUP báo #N/A dù hai bên nhìn y hệt nhauDò mã khách từ bảng này sang bảng kia, mắt nhìn hai ô giống hệt nhau nhưng công thức vẫn trả về #N/A. Đây là câu hỏi số một của học viên mới.
- Đối chiếu hai danh sách: bên nào thiếu, bên nào lệch tiềnSao kê ngân hàng một bên, sổ kế toán một bên. Cần biết chứng từ nào chỉ có ở một bên, và chứng từ nào có cả hai bên nhưng lệch số tiền.
- Dò theo hai điều kiện trở lên không cần cột phụCần lấy giá theo cả mã hàng VÀ khu vực, hoặc doanh số theo nhân viên VÀ tháng. VLOOKUP thường chỉ dò được một khóa.
- Đếm giá trị không trùng và truy ra bản ghi trùngCần biết có bao nhiêu khách hàng KHÁC NHAU (không phải bao nhiêu dòng), và chỉ ra chứng từ nào bị nhập trùng theo nhiều khóa cùng lúc.