Graham sử dụng một công thức tham chiếu đến hai ô, B1 và ​​C1. Tất cả những gì công thức làm là trả về giá trị của một trong các ô, như trong = IF (A1 = 1, IF (B1> C1, B1, C1)). Cả B1 và ​​C1 thường chứa ngày tháng, nhưng đôi khi một trong hai hoặc cả hai có thể trống. Nếu cả hai đều trống, giá trị trả về (bằng 0 vì chúng trống) sẽ hiển thị là 1/0/1900. Khi cả hai ô đều trống, Graham muốn công thức trả về giá trị trống, không phải là 0.

Có một số cách để tiếp cận nhu cầu này. Tuy nhiên, trước tiên, hãy bắt đầu với công thức ví dụ của Graham:

=IF(A1=1, IF(B1>C1, B1, C1))

Công thức có một chút “không đầy đủ” vì nó không cung cấp những gì sẽ được trả lại nếu A1 không chứa giá trị 1. Như đã viết, nếu A1 chứa 2 (hoặc bất kỳ giá trị nào khác ngoại trừ 1), thì công thức trả về “FALSE “. Vì vậy, hãy sửa đổi công thức một chút để nếu A1 không phải là 1 thì nó trả về giá trị “trống”, như thế này:

=IF(A1=1, IF(B1>C1, B1, C1), "")

Tiếp theo, điều quan trọng là phải hiểu những gì đang xảy ra trong “B1> C1”

sự so sánh. Tất nhiên, ngày tháng được lưu trữ bên trong dưới dạng giá trị số — dưới dạng số sê-ri. Nếu B1 chứa một ngày gần đây hơn C1, thì số sê-ri trong B1 sẽ lớn hơn số sê-ri trong C1.

Ngoài ra, các đặc điểm của dữ liệu trong B1 và ​​C1 xác định cách thức hoạt động của phép so sánh (>).

  1. Nếu cả hai ô đều chứa giá trị số (bao gồm cả ngày tháng) thì so sánh sẽ hoạt động như mong đợi.

  2. Nếu một trong hai ô chứa giá trị văn bản, thì cả hai ô được coi như thể chúng chứa giá trị văn bản, ngay cả khi một trong số chúng chứa giá trị số.

  3. Nếu một trong các ô trống, thì ô đó được coi là ô chứa giá trị 0.

Dựa trên điều này, nếu một ô chứa ngày và ô còn lại trống thì tương đương với việc so sánh số sê-ri (ngày) với số 0 (ô trống), vì vậy số sê-ri sẽ luôn lớn hơn ô trống. Nếu cả hai ô đều trống, cả hai ô đều được coi là chứa 0 và do đó cả hai đều bằng nhau.

Vì vậy, giả sử rằng trong công thức của Graham, ô A1 chứa giá trị 1, ô B1 trống và ô C1 trống. Đây là cách công thức được “dịch” bởi Excel:

=IF(A1=1, IF(B1>C1, B1, C1), "")

=IF(1=1, IF(B1>C1, B1, C1), "")

=IF(B1>C1, B1, C1)

=IF(0>0, B1, C1)

=C1 =0

Đây là lý do tại sao Graham thấy 0 được trả về bởi công thức và khi 0 được coi là một số sê-ri ngày, nó được hiển thị là 1/0/00 (hoặc 1/0/1900).

Để tránh điều này, bạn cần kiểm tra xem cả B1 và ​​C1 đều trống không.

Có rất nhiều cách có thể được thực hiện. Hãy xem xét biến thể này trong công thức của anh ấy:

=IF(A1=1, IF((B1+C1=0), "", IF(B1>C1, B1, C1)), "")

Biến thể này thêm một câu lệnh IF để xem liệu B1 được thêm vào C1 có bằng 0 hay không, nếu cả hai đều trống vì Excel coi các khoảng trống và 0 là tương đương trong ngữ cảnh này. Hạn chế là nếu B1 hoặc C1 chứa giá trị văn bản, thì lỗi #VALUE được trả về bởi công thức.

Một biến thể tốt hơn có thể là như sau:

=IF(A1=1, IF(AND(B1=0,C1=0), "", IF(B1>C1, B1, C1)), "")

Trong phiên bản này, câu lệnh IF sử dụng hàm AND để xác định xem cả B1 và ​​C1 có bằng 0. Điều này cũng giải quyết vấn đề lỗi #VALUE tiềm ẩn.

Nếu bạn thực sự muốn kiểm tra xem cả B1 và ​​C1 đều trống hay không, thì bạn sẽ cần phải dựa vào một cách tiếp cận khác. Một cách là sử dụng hàm COUNTA:

=IF(A1=1, IF(COUNTA(B1:C1)=0, "", IF(B1>C1, B1, C1)), "")

Nếu bạn biết thực tế là cả B1 và ​​C1 sẽ không bao giờ chứa các giá trị văn bản cùng một lúc, bạn cũng có thể sử dụng hàm COUNT thay cho hàm COUNTA trong công thức ở trên.

Một cách tiếp cận khác là sử dụng hàm COUNTBLANK theo cách tương tự; nó trả về số lượng ô trong một phạm vi trống:

=IF(A1=1, IF(COUNTBLANK(B1:C1)=2, "", IF(B1>C1, B1, C1)), "")

Một biến thể tương tự là sử dụng hàm ISBLANK (trả về TRUE nếu ô trống) cùng với hàm AND:

=IF(A1=1, IF(AND(ISBLANK(B1), ISBLANK(C1)), "", IF(B1>C1, B1, C1)), "")

Bạn cũng có thể để Excel đánh giá B1 và ​​C1 như thể chúng chứa văn bản, như được thực hiện ở đây:

=IF(A1=1, IF(B1&C1="", "", IF(B1>C1, B1, C1)), "")

Công thức này sẽ không hoạt động như mong đợi nếu B1 hoặc C1 chứa một khoảng trắng, vì vậy bạn có thể muốn thêm hàm TRIM vào hỗn hợp:

=IF(A1=1, IF(TRIM(B1&C1)="", "", IF(B1>C1, B1, C1)), "")

Trong bất kỳ công thức nào được thảo luận cho đến nay, bạn cũng có thể muốn thay đổi câu lệnh IF trả về B1 hoặc C1 bằng hàm MAX, theo cách sau:

=IF(A1=1, IF(TRIM(B1&C1)="", "", MAX(B1:C1)), "")

Tuy nhiên, có một cảnh báo nếu bạn quyết định làm điều này: Hàm MAX coi tất cả các giá trị văn bản là tương đương với 0. Do đó, nếu ô B1 chứa “abc” và ô C1 chứa 1, thì hãy sử dụng toán hạng lớn hơn so sánh (> ) coi “abc” lớn hơn 1, nhưng MAX coi 1 lớn hơn “abc”.

Cũng có một cách mà bạn có thể quay lại và sử dụng công thức ban đầu của Graham (công thức không kiểm tra hai ô trống) và chỉ cần dựa vào định dạng ô chứa công thức. Chỉ cần tạo một định dạng tùy chỉnh như sau:

m/d/yyyy;;

Lưu ý hai dấu chấm phẩy ở cuối định dạng. Những điều này cho biết Excel không hiển thị bất kỳ thứ gì nếu giá trị trong ô là âm hoặc giá trị bằng không. Sử dụng định dạng này, bạn sẽ không bao giờ thấy ngày 1/0/1900 xuất hiện; ô sẽ hiển thị là trống.

Tất nhiên, bạn có thể sửa đổi Excel để nó ẩn tất cả các giá trị 0 trong trang tính — như đã được đề cập trong ExcelTips khác — nhưng làm như vậy có thể không phù hợp với mục đích của bạn nếu bạn cần hiển thị kết quả bằng không từ các công thức khác.

ExcelTips là nguồn của bạn để đào tạo Microsoft Excel hiệu quả về chi phí.

Mẹo này (10386) áp dụng cho Microsoft Excel 2007, 2010, 2013, 2016, 2019 và Excel trong Office 365.