Khi quản lý hóa đơn, có một nguyên tắc là phải sử dụng hóa đơn theo tuần tự Số nhỏ dùng trước, không được nhảy cách. Tuy nhiên trong thực tế việc này vẫn có thể xảy ra. Vậy làm thế nào để chúng ta có thể kiểm tra được hóa đơn nào có số lớn hơn nhưng lại sử dụng trước? Hãy cùng Học Excel Online tìm hiểu cách làm qua bài viết này nhé.

Cách kiểm tra hóa đơn có số lớn hơn nhưng sử dụng trước với hàm COUNTIFS

Giả sử chúng ta có danh sách các hóa đơn đã sử dụng tại file sau: (link tải file: http://bit.ly/2wQONsE)

Trong bảng ta có 4 cột:

Cột Số quyển: Là quyển hóa đơn. 1 quyển sẽ có nhiều tờ hóa đơn

Số hóa đơn: Mỗi tờ hóa đơn có 1 số, gồm phần ký hiệu (AB/2016, AB/2017…) và phần số (sau dấu gạch ngang)

Ngày sử dụng: Là ngày tờ hóa đơn đó được sử dụng

Yêu cầu: Phát hiện những hóa đơn Trong cùng 1 quyển có phần Số (trong cột Số hóa đơn) là số lớn hơn nhưng thời gian sử dụng lại nhỏ hơn (hay nói cách khác hóa đơn trong cùng 1 quyển có số nhỏ hơn nhưng thời gian sử dụng lại lớn hơn các số trước đó).

Cách làm như sau:

Bước 1: Tách phần số hóa đơn khỏi cột Số hóa đơn

Để có thể so sánh các giá trị số hóa đơn, chúng ta phải tách các giá trị số hóa đơn ra riêng 1 cột. Vì khi lẫn cả phần Ký hiệu với phần số thì đây là giá trị Text, không so sánh được.

Cách tách: Sử dụng hàm RIGHT kết hợp hàm VALUE như sau:

1. Hàm RIGHT: Tách phần số gồm 7 ký tự phía bên phải trong mỗi ô ở cột C
2. Hàm VALUE: Chuyển kết quả hàm RIGHT về dạng số

Tạo cột E để nhận kết quả tách số

Tại ô E2 viết công thức:

=VALUE(RIGHT(C2,7))

Kéo công thức từ ô E2 tới hết bảng, ta có kết quả:

Bước 2: Kiểm tra hóa đơn số nhỏ viết sau với hàm COUNTIFS

Tham khảo: Hướng dẫn cách sử dụng hàm countif trong excel mọi phiên bản

Tạo cột F là cột sẽ dùng công thức tính ra kết quả.

Yêu cầu này gồm 3 điều kiện đồng thời xảy ra:

• Số quyển: Phải cùng 1 quyển
• Số hóa đơn: Số kiểm tra phải nhỏ hơn các số trước đó
• Ngày sử dụng: Ngày kiểm tra phải lớn hơn các ngày trước đó

Như vậy ta có hàm COUNTIFS như sau:

F2 =COUNTIFS($B$2:B2,B2, $E$2:E2,”>”&E2, $D$2:D2,”<“&D2)

• Vùng điều kiện 1: $B$2:B2 là cột Số quyển. Vùng này cố định điểm đầu, điểm cuối không cố định để mở rộng dần vùng này ra
• Điều kiện 1: B2 là số quyển tại ô B2. Giá trị này không cố định để thay đổi khi kéo xuống dòng khác
• Vùng điều kiện 2: $E$2:E2 là cột Số hóa đơn. Vùng này cố định điểm đầu, điểm cuối không cố định để mở rộng dần vùng này ra
• Điều kiện 2: “>”&E2 là số hóa đơn lớn hơn so với số tại dòng đang kiểm tra. Giá trị này không cố định để thay đổi khi kéo xuống dòng khác
• Vùng điều kiện 3: $D$2:D2 là cột Ngày. Vùng này cố định điểm đầu, điểm cuối không cố định để mở rộng dần vùng này ra
• Điều kiện 3: “<“&D2 là ngày nhỏ hơn so với ngày tại dòng đang kiểm tra. Giá trị này không cố định để thay đổi khi kéo xuống dòng khác

Như vậy kết quả thu được là:

Khi lọc theo quyển hóa đơn để kiểm tra, chúng ta thấy:

Ví dụ với quyển số 920, ta có hóa đơn số 4597 có ngày sử dụng là 25/01/2018, trong khi đó số lớn hơn là 4600 lại có ngày sử dụng là 22/01/2018. Như vậy quyển này có hóa đơn số 4597 là số nhỏ nhưng lại viết sau, cụ thể là sau 3 tờ hóa đơn có số lớn hơn đã viết trước.

Tương tự với các quyển hóa đơn khác, các bạn có thể lọc và kiểm tra.

Như vậy chúng ta đã có thể sử dụng 1 cách viết của hàm COUNTIFS để giải quyết 1 yêu cầu công việc khá phức tạp. Công thức cũng không quá khó phải không nào. Chúc các bạn áp dụng thành công!