Merwyn từ Anh đã gửi đến vấn đề này.
Có một công thức nào trong ô B2 có thể được sao chép trên và xuống để tạo bảng cửu chương không?
Mục tiêu của Merwyn là nhập một công thức đơn trong B2 có thể dễ dàng sao chép vào tất cả 144 ô để tạo bảng tham chiếu phép nhân.
Hãy tấn công điều này với tư cách là một người mới làm quen với Excel và xem chúng ta gặp phải những cạm bẫy nào. Phản ứng ban đầu của một người có thể là có công thức trong B2 =A2*B1. Công thức này tạo ra kết quả chính xác, nhưng nó không phù hợp với nhiệm vụ của Merwyn.
Khi bạn sao chép công thức này từ ô B2 sang ô C2, các ô được tham chiếu trong công thức cũng di chuyển qua một ô. Công thức mà =A2*B1bây giờ đã trở thành =C1*B2. Đây là một tính năng được thiết kế trong Microsoft Excel và được gọi là tham chiếu công thức tương đối. Khi bạn sao chép một công thức, các tham chiếu ô trong công thức cũng di chuyển một số ô tương ứng qua và xuống.
Đôi khi bạn không muốn Excel thể hiện hành vi này và trường hợp hiện tại là một trong số đó. Để ngăn Excel thay đổi tham chiếu trong khi sao chép ô, hãy chèn "$" trước phần tham chiếu mà bạn muốn cố định. Ví dụ:
- $ A1 cho Excel biết rằng bạn luôn muốn tham chiếu đến cột A.
- B $ 1 cho Excel biết rằng bạn luôn muốn tham chiếu đến hàng 1.
- $ B $ 1 cho Excel biết rằng bạn luôn muốn tham chiếu đến ô B1.
Học mã này sẽ giảm đáng kể thời gian cần thiết để xây dựng trang tính. Thay vì phải nhập 144 công thức, Merwyn có thể sử dụng cách viết tắt này để nhập một công thức duy nhất và sao chép nó vào tất cả các ô.
Nhìn vào công thức của Merwyn =B1*A2, chúng tôi nhận thấy rằng phần "B1" của biểu thức phải luôn trỏ đến một số trong Hàng 1. Phần này phải được viết lại thành "B $ 1". Phần "A2" của công thức phải luôn trỏ đến một số trong cột A. Phần này phải được viết lại thành "$ A2". Vì vậy, công thức mới trong ô B2 là =B$1*$A2.
Sau khi nhập công thức vào B2, tôi có thể sao chép và dán nó vào phạm vi thích hợp. Excel làm theo hướng dẫn của tôi và sau khi sao chép, tôi tìm thấy các công thức sau:
- Ô M2 chứa
=M$1*$A2 - Ô B13 chứa
=B$1*$A13 - Ô M13 chứa
=M$1*$A13
Mỗi loại công thức này đều có một tên. Công thức không có bất kỳ dấu đô la nào được gọi là công thức tương đối. Công thức trong đó cả hàng và cột bị khóa bằng dấu đô la được gọi là công thức tuyệt đối. Các công thức trong đó hàng hoặc cột bị khóa bằng dấu đô la được gọi là công thức hỗn hợp.
Có một phương pháp viết tắt để nhập các ký hiệu đô la. Khi bạn đang nhập công thức và hoàn thành tham chiếu ô, bạn có thể nhấn phím để chuyển đổi giữa 4 loại tham chiếu. Giả sử bạn bắt đầu nhập một công thức và bạn đã nhập =100*G87.
- Nhấn F4 và công thức của bạn thay đổi thành
=100*$G$87 - Nhấn F4 một lần nữa và công thức của bạn thay đổi thành
=100*G$87 - Nhấn F4 một lần nữa và công thức của bạn thay đổi thành
=100*$G87 - Nhấn F4 một lần nữa và công thức của bạn trở lại ban đầu
=100*G87
Bạn có thể tạm dừng trong khi nhập công thức tại mỗi tham chiếu ô để nhấn F4 cho đến khi bạn nhận được loại tham chiếu phù hợp. Các tổ hợp phím để nhập công thức Merwyn ở trên là =B1*A1.
Một trong những cách sử dụng yêu thích của tôi về tham chiếu công thức hỗn hợp xuất hiện khi tôi có một danh sách dài các mục nhập trong cột A. Khi tôi muốn có một phương pháp nhanh chóng và tiện lợi để tìm các bản sao, đôi khi tôi nhập công thức này vào ô B4 và sau đó sao chép nó:
=VLOOKUP(A4,$A$2:$A3,1,FALSE)
Lưu ý rằng thuật ngữ thứ 2 của công thức VLOOKUP sử dụng cả tham chiếu tuyệt đối ($ A $ 2) và hỗn hợp ($ A3) để mô tả phạm vi tra cứu. Bằng tiếng Anh, tôi đang yêu cầu Excel luôn tìm kiếm từ ô $ A $ 2 đến ô trong cột A ngay phía trên ô hiện tại. Ngay khi Excel gặp một giá trị trùng lặp, kết quả của công thức này sẽ thay đổi từ # N / A! đến một giá trị.
Với việc sử dụng sáng tạo tham chiếu ô $ in, bạn có thể đảm bảo rằng một công thức duy nhất có thể được sao chép vào nhiều ô có kết quả chính xác.
