Mọi người đều biết bạn có thể nhập công thức trong Excel. (Rốt cuộc, một bảng tính sẽ ra sao nếu không có công thức?) Nếu bạn sử dụng tham chiếu địa chỉ trong một công thức, những tham chiếu đó sẽ tự động được cập nhật nếu bạn chèn hoặc xóa ô, hàng hoặc cột và những thay đổi đó ảnh hưởng đến tham chiếu địa chỉ theo một cách nào đó. Ví dụ, hãy xem xét công thức đơn giản sau:

=IF(A7=B7,"YES","NO")

Nếu bạn chèn một ô phía trên B7, thì công thức sẽ được Excel tự động điều chỉnh để nó xuất hiện như sau:

=IF(A7=B8,"YES","NO")

Tuy nhiên, điều gì sẽ xảy ra nếu bạn không muốn Excel điều chỉnh công thức? Bạn có thể thử thêm một số ký hiệu đô la vào địa chỉ, nhưng điều này chỉ ảnh hưởng đến địa chỉ trong công thức được sao chép sau đó; nó không ảnh hưởng đến chính công thức nếu bạn chèn hoặc xóa các ô ảnh hưởng đến công thức.

Cách tốt nhất để tạo tham chiếu công thức? Không điều chỉnh? là sửa đổi chính công thức để sử dụng các hàm trang tính khác nhau. Ví dụ, bạn có thể sử dụng công thức này trong ô C7:

=IF(INDIRECT("A"&ROW(C7))=INDIRECT("B"&ROW(C7)),"YES","NO")

Công thức này thực hiện là tạo một địa chỉ dựa trên bất kỳ ô nào mà công thức xuất hiện. Hàm ROW trả về số hàng của ô (trong trường hợp này là C7, vì vậy giá trị 7 được trả về) và sau đó hàm INDIRECT được sử dụng để tham chiếu địa chỉ đã xây dựng, chẳng hạn như A7 và B7. Nếu bạn chèn (hoặc xóa) các ô phía trên A7 hoặc B7, tham chiếu trong ô C7 sẽ không bị xáo trộn, vì nó chỉ tạo ra một địa chỉ hoàn toàn mới.

Một cách tiếp cận khác là sử dụng hàm OFFSET để tạo một loại tham chiếu tương tự:

=IF(OFFSET($A$1,ROW()-1,0)=OFFSET($B$1,ROW()-1,0),"YES","NO")

Công thức này chỉ đơn giản là xem vị trí của nó (trong cột C) và so sánh các giá trị trong các ô ở bên trái của nó. Công thức này tương tự như vậy không bị xáo trộn nếu bạn vô tình chèn hoặc xóa các ô trong cột A hoặc B.

Cách tiếp cận cuối cùng (và có lẽ là cách tốt nhất) là sử dụng các công thức được đặt tên. Đây là một tính năng của khả năng đặt tên của Excel hiếm khi được hầu hết mọi người sử dụng. Làm theo các bước sau:

  1. Chọn ô C2.

  2. Chọn Tên từ menu Chèn, sau đó chọn Xác định từ menu con.

Excel sẽ hiển thị hộp thoại Xác định Tên. (Xem Hình 1.)

  1. Trong hộp Names in Workbook, nhập tên CompareMe. (Bạn có thể sử dụng một tên khác, nếu bạn muốn.)

  2. Xóa bất cứ thứ gì có trong hộp Tham khảo, thay thế nó bằng công thức sau:

  3. Bấm OK.

Tại thời điểm này, bạn đã tạo ra công thức được đặt tên của bạn. Bây giờ bạn có thể sử dụng nó trong bất kỳ ô nào trong cột C theo cách sau:

=CompareMe

Nó so sánh bất cứ thứ gì có trong hai ô bên trái, giống như công thức ban đầu của bạn được thiết kế để làm. Vẫn tốt hơn, công thức không được tự động điều chỉnh khi bạn chèn hoặc xóa ô.

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

Mẹo này (2876) áp dụng cho Microsoft Excel 97, 2000, 2002 và 2003. Bạn có thể tìm thấy phiên bản của mẹo này cho giao diện ribbon của Excel (Excel 2007 trở lên) tại đây: