Tìm các cột còn thiếu dữ liệu hoặc chưa thoả mãn điều kiệu trong Excel

Chắc có lúc bạn sẽ gặp phải trường hợp cần liệt kê ra những cột chưa có đầy đủ dữ liệu hoặc các dữ liệu trong các cột đó chưa thoả mãn một điều kiện được đặt ra. Ví dụ như trong trường hợp sau đây, chúng ta có 1 danh sách các học sinh và có các cột tiền học của các môn Toán, Văn,Anh, Thể Dục. Trong các cột tiền học, cột nào có giá trị 0 tức là học sinh tương ứng chưa đóng tiền môn đó, làm sao để chúng ta có thể liệt kê ra những môn này? Hãy theo dõi ví dụ sau đây. File kèm theo ví dụ này có thể được download ở đây

Để thực hiện được yêu cầu này một cách đơn giản nhất, chúng ta có thể nghĩ đến hàm IF trong Excel. So sánh giá trị của từng ô tiền học trong các cột với số 0, nếu ô nào thoả mãn điều kiện giá_trị_trong_ô = 0 thì trả về tên của cột đó và nối với dấu phẩy; nếu không thì chúng ta trả lại kí tự rỗng. Chúng ta có 4 cột cho 4 môn, chỉ cần nối 4 cột này lại với dấu phẩy , là ngon phải không? Nghe có vẻ hoàn hảo. Sau một hồi loay hoay, chúng ta dùng công thức sau đây:

=IF(D13=0,D$5&", ","")&IF(E13=0,E$5&", ","")&IF(F13=0,F$5&", ","")&IF(G13=0,G$5&", ","")

Sau khi nhập công thức vào cột I và kéo công thức cho toàn bộ các dòng, những cột thiếu dữ liệu đã được liệt kê. NHƯNG phía cuối mỗi dòng kết quả ở cột I thừa ra 1 dấu ,. Làm thế nào để loại bỏ dấu phẩy này? Chúng ta để ý là không chỉ có 1 dấu phẩy ở cuối cùng, mà thực ra 1 dấu cách cũng ở đó, tóm lại, có 2 kí tự thừa ở bên phải của kết quả. Đến đây, nhiều bạn đã nghĩ đến sử dụng hàm LEFT để lấy hết phần bên trái của chuỗi kết quả.

=LEFT(IF(D13=0,D$5&", ","")&IF(E13=0,E$5&", ","")&IF(F13=0,F$5&", ","")&IF(G13=0,G$5&", ",""),LEN(=IF(D13=0,D$5&", ","")&IF(E13=0,E$5&", ","")&IF(F13=0,F$5&", ","")&IF(G13=0,G$5&", ",""))-2

Phức tạp không? Công thức dài không? Chắc chắn là có! Vậy chúng ta sẽ làm cho công thức đơn giản hơn.

Thay vì đưa dấu , ở công thức IF ở phía sau mỗi môn học, chúng ta sẽ đưa ra phía trước:

=IF(D6=0,", "&D$5,"")&IF(E6=0,", "&E$5,"")&IF(F6=0,", "&F$5,"")&IF(G6=0,", "&G$5,"")

Với hàm IF như vậy, chúng ta sẽ được 1 dãy kết quả thừa dấu , bên phải. Và khi dấu phẩy bên tay phải, chúng ta có thể dùng Hàm MID để xử lý như sau:

=MID(IF(D6=0,", "&D$5,"")&IF(E6=0,", "&E$5,"")&IF(F6=0,", "&F$5,"")&IF(G6=0,", "&G$5,""),2,999)

Với công thức trên, chúng ta lấy đi 2 giá trị tính từ bên trái sang, tức là phần , đã được loại bỏ, số 999 là một số bạn có thể tự chọn và cần phải đủ dài để lấy được toàn bộ chuỗi cần lấy.