Bỏ qua nội dung chính

Tham chiếu tuyệt đối trong Excel (cách tạo và sử dụng)

Khi tham chiếu một ô trong công thức trong Excel, loại tham chiếu mặc định là tham chiếu tương đối. Các tham chiếu này sẽ thay đổi khi công thức được sao chép sang các ô khác dựa trên cột và hàng tương đối của chúng. Nếu bạn muốn giữ một tham chiếu không đổi, bất kể công thức được sao chép ở đâu, bạn cần sử dụng tham chiếu tuyệt đối.

Tải file mẫu miễn phí doc mẫu


Video: Tham khảo tuyệt đối

 


Tham chiếu tuyệt đối là gì

 

Tham chiếu tuyệt đối là một loại tham chiếu ô trong Excel.

So với tham chiếu tương đối sẽ thay đổi dựa trên vị trí tương đối của nó khi công thức được sao chép sang các ô khác, tham chiếu tuyệt đối sẽ không đổi bất kể công thức được sao chép hoặc di chuyển ở đâu.

Tham chiếu tuyệt đối được tạo bằng cách thêm ký hiệu đô la ($) trước tham chiếu cột và hàng trong công thức. Ví dụ: để tạo một tham chiếu tuyệt đối cho ô A1, bạn nên biểu thị nó là $A$1.

Tham chiếu tuyệt đối hữu ích khi bạn muốn tham chiếu đến một ô hoặc phạm vi cố định trong một công thức sẽ được sao chép sang nhiều ô, nhưng bạn không muốn tham chiếu thay đổi.

Ví dụ, dãy A4:C7 chứa giá các sản phẩm, bạn muốn lấy số thuế phải nộp của từng sản phẩm dựa trên thuế suất tại ô B2.

Nếu bạn sử dụng tham chiếu tương đối trong công thức như "=B5*B2", một số kết quả sai sẽ được trả về khi bạn kéo bộ điều khiển tự động điền xuống để áp dụng công thức này. Vì tham chiếu đến ô B2 sẽ thay đổi tương ứng với các ô trong công thức. Bây giờ, công thức trong ô C6 là "=B6*B3" và công thức trong ô C7 là "=B7*B4"

Nhưng nếu bạn sử dụng một tham chiếu tuyệt đối đến ô B2 với công thức "=B5*$B$2" sẽ đảm bảo rằng thuế suất giữ nguyên cho tất cả các ô khi công thức được kéo xuống bằng cách sử dụng bộ điều khiển điền tự động, thì kết quả sẽ chính xác.

Sử dụng tham chiếu tương đối   Sử dụng tham chiếu tuyệt đối
tài liệu tham khảo tuyệt đối 3 1   tài liệu tham khảo tuyệt đối 4 1

Cách tạo tham chiếu tuyệt đối

 

Để tạo tham chiếu tuyệt đối trong Excel, bạn cần thêm ký hiệu đô la ($) trước tham chiếu cột và hàng trong công thức. Có hai cách để tạo tham chiếu tuyệt đối:

Thêm ký hiệu đô la vào tham chiếu ô theo cách thủ công

Bạn có thể thêm các ký hiệu đô la ($) theo cách thủ công trước các tham chiếu cột và hàng mà bạn muốn đặt giá trị tuyệt đối trong khi nhập công thức vào một ô.

Ví dụ: nếu bạn muốn cộng các số trong ô A1 và B1 và ​​đặt cả hai số này ở dạng tuyệt đối, chỉ cần nhập công thức là "=$A$1+$B$1". Điều này sẽ đảm bảo rằng các tham chiếu ô không đổi khi công thức được sao chép hoặc di chuyển sang các ô khác.

Hoặc nếu bạn muốn thay đổi các tham chiếu trong một công thức tồn tại trong một ô thành giá trị tuyệt đối, bạn có thể chọn ô đó, sau đó chuyển đến thanh công thức để thêm các ký hiệu đô la ($).

Sử dụng phím tắt F4 để chuyển đổi tham chiếu tương đối thành tuyệt đối
  1. Nhấp đúp vào ô có công thức để vào chế độ chỉnh sửa;
  2. Đặt con trỏ tại tham chiếu ô mà bạn muốn đặt giá trị tuyệt đối;
  3. Ấn Bản F4 trên bàn phím để chuyển đổi các loại tham chiếu cho đến khi các ký hiệu đô la được thêm vào trước cả tham chiếu cột và hàng;
  4. Ấn Bản đăng ký hạng mục thi để thoát khỏi chế độ chỉnh sửa và áp dụng các thay đổi.

Phím F4 có thể chuyển đổi tham chiếu giữa tham chiếu tương đối, tham chiếu tuyệt đối và tham chiếu hỗn hợp.

A1 → $A$1 → A$1 → $A1 → A1

tham chiếu tuyệt đối f4 bật tắt 1

Nếu bạn muốn biến tất cả các tham chiếu thành tuyệt đối trong một công thức, hãy chọn toàn bộ công thức trong thanh công thức, nhấn F4 để chuyển đổi các loại tham chiếu cho đến khi các ký hiệu đô la được thêm vào trước cả tham chiếu cột và hàng.

A1+B1 → $A$1+$B$1 → A$1+B$1 → $A1+$B1 → A1+B1

tham chiếu tuyệt đối f4 bật tắt 2


Sử dụng tham chiếu tuyệt đối với các ví dụ

 

Phần này cung cấp 2 ví dụ để hiển thị thời điểm và cách sử dụng tham chiếu tuyệt đối trong công thức Excel.

Ví dụ 1 Tính tỷ lệ phần trăm của tổng

Giả sử bạn có một dải dữ liệu (A3:B7) chứa doanh số bán từng loại trái cây và ô B8 chứa tổng số tiền bán các loại trái cây này, bây giờ bạn muốn tính tỷ lệ phần trăm doanh số bán từng loại trái cây trên tổng số.

Công thức chung để tính tỷ lệ phần trăm của tổng số:

Percentage = Sale/Amount

Sử dụng tham chiếu tương đối trong công thức để lấy tỷ lệ phần trăm của quả đầu tiên như sau:

=B4/B8

Khi kéo handle tự động điền xuống để tính tỷ lệ phần trăm của các loại trái cây khác thì #DIV/0! lỗi sẽ được trả lại.

Vì khi bạn kéo bộ điều khiển tự động điền để sao chép công thức vào các ô bên dưới, tham chiếu tương đối B8 sẽ tự động được điều chỉnh thành các tham chiếu ô khác (B9, B10, B11) dựa trên vị trí tương đối của chúng. Và ô B9, B10 và B11 trống (số XNUMX), khi số chia bằng XNUMX, công thức sẽ báo lỗi.

Để sửa lỗi, trong trường hợp này, bạn cần đặt tham chiếu ô B8 là tuyệt đối ($B$8) trong công thức để ngăn nó thay đổi khi bạn di chuyển hoặc sao chép công thức đến bất kỳ đâu. Bây giờ công thức được cập nhật thành:

=B4/$B$8

Sau đó kéo thanh điều khiển tự động điền xuống để tính tỷ lệ phần trăm của các loại trái cây khác.

Ví dụ 2 Tìm một giá trị và trả về giá trị khớp tương ứng

Giả sử bạn muốn tìm danh sách tên trong D4:D5 và trả về mức lương tương ứng của họ dựa trên tên nhân viên và mức lương hàng năm tương ứng được cung cấp trong phạm vi (A4:B8).

Công thức chung để tra cứu là:

=VLOOKUP(lookup_value, table_range, column_index, logical)

Nếu bạn sử dụng tham chiếu tương đối trong công thức để tra cứu một giá trị và trả về giá trị khớp tương ứng như sau:

=VLOOKUP(D4,A4:B8,2,FALSE)

Sau đó kéo chốt tự động điền xuống để tra cứu giá trị bên dưới, sẽ trả về lỗi.

Khi bạn kéo núm điều khiển điền xuống để sao chép công thức vào ô bên dưới, các tham chiếu trong công thức sẽ tự động điều chỉnh xuống một hàng. Kết quả là, tham chiếu đến phạm vi bảng, A4:B8, trở thành A5:B9. Vì "Lisa: không thể tìm thấy trong phạm vi A5:B9, nên công thức trả về lỗi.

Để tránh lỗi, hãy sử dụng tham chiếu tuyệt đối $A$4:$B$8 thay vì tham chiếu tương đối A4:B8 trong công thức:

=VLOOKUP(D4,$A$4:$B$8,2,FALSE)

Sau đó kéo handle tự động điền xuống để lấy lương của Lisa.


 

2 lần nhấp để tạo hàng loạt tham chiếu ô tuyệt đối với Kutools

 

Cho dù bạn chọn nhập thủ công hay sử dụng phím tắt F4, bạn chỉ có thể thay đổi một công thức tại một thời điểm trong Excel. Nếu bạn muốn biến các tham chiếu ô trong hàng trăm công thức thành tuyệt đối trong Excel, thì Chuyển đổi tham chiếu công cụ của Kutools cho Excel có thể giúp bạn xử lý công việc với 2 cú nhấp chuột.

Chọn ô công thức mà bạn muốn đặt tham chiếu ô là tuyệt đối, bấm Kutools > Khác (fx) > Chuyển đổi tham chiếu. Sau đó chọn Đến tuyệt đối tùy chọn và nhấp chuột Ok or Đăng Nhập. Bây giờ tất cả các tham chiếu ô của các công thức đã chọn đã được chuyển đổi thành tuyệt đối.

Chú ý:

Tham chiếu tương đối và tham chiếu hỗn hợp

 

Ngoài tham chiếu tuyệt đối, còn có hai loại tham chiếu khác: tham chiếu tương đối và tham chiếu hỗn hợp.

Tham khảo tương đối là loại tham chiếu mặc định trong Excel, không có ký hiệu đô la ($) trước tham chiếu hàng và cột. Và khi một công thức có tham chiếu tương đối được sao chép hoặc di chuyển sang các ô khác, các tham chiếu sẽ tự động thay đổi dựa trên vị trí tương đối của chúng.

Ví dụ: khi bạn nhập công thức vào một ô chẳng hạn như "=A1+1", sau đó kéo bộ điều khiển tự động điền xuống để điền công thức này vào ô tiếp theo, công thức sẽ tự động chuyển thành "=A2+1".

tham khảo hỗn hợp bao gồm cả tham chiếu tuyệt đối và tham chiếu tương đối. Nói cách khác, tham chiếu hỗn hợp sử dụng ký hiệu đô la ($) để cố định hàng hoặc cột khi công thức được sao chép hoặc điền.

Lấy một bảng cửu chương làm ví dụ, các hàng và cột liệt kê các số từ 1 đến 9, bạn sẽ nhân các số này với nhau.

tài liệu tham khảo tuyệt đối 15 1

Để bắt đầu, bạn có thể sử dụng công thức "=B3*C2" trong ô C3 để nhân 1 trong ô B3 với số (1) trong cột đầu tiên. Tuy nhiên, khi bạn kéo núm điều khiển tự động điền sang bên phải để điền vào các ô khác, bạn sẽ nhận thấy rằng tất cả các kết quả đều không chính xác ngoại trừ kết quả đầu tiên.

tài liệu tham khảo tuyệt đối 16 1

Điều này là do khi bạn sao chép công thức sang bên phải, vị trí hàng sẽ không thay đổi nhưng vị trí cột thay đổi từ B3 sang C3, D3, v.v.. Kết quả là công thức ở các ô bên phải (D3, E3, v.v.) thay đổi thành "=C3*D2", "=D3*E2", v.v., khi bạn thực sự muốn chúng là "=B3*D2", "=B3*E2", v.v.

Trong trường hợp này, bạn cần thêm ký hiệu đô la ($) để khóa tham chiếu cột của “B3”. Sử dụng công thức như dưới đây:

=$B3*C2

Bây giờ khi bạn kéo công thức sang bên phải, kết quả sẽ chính xác.

tài liệu tham khảo tuyệt đối 17 1

Sau đó, bạn cần nhân số 1 trong ô C2 với các số trong các hàng bên dưới.

Khi bạn sao chép công thức xuống, vị trí cột của ô C2 sẽ không thay đổi, nhưng vị trí hàng thay đổi từ C2 thành C3, C4, v.v. Kết quả là công thức trong các ô bên dưới thay đổi thành "=$B4C3", "=$B5C4", v.v. sẽ tạo ra kết quả không chính xác.

tài liệu tham khảo tuyệt đối 18 1

Để giải quyết vấn đề này, hãy thay đổi “C2” thành “C$2” để giữ cho tham chiếu hàng không thay đổi khi kéo bộ điều khiển tự động điền xuống để điền công thức.

=$B3*C$2

tài liệu tham khảo tuyệt đối 19 1

Giờ đây, bạn có thể kéo núm điều khiển tự động điền sang phải hoặc xuống để nhận tất cả kết quả.

tài liệu tham khảo tuyệt đối 20 1


Những điều cần ghi nhớ

 
  • Tóm tắt các tham chiếu ô

    Kiểu Ví dụ Tổng kết
    Tham chiếu tuyệt đối $ A $ 1 Không bao giờ thay đổi khi công thức được sao chép sang các ô khác
    Tham chiếu tương đối A1 Cả tham chiếu hàng và cột đều thay đổi dựa trên vị trí tương đối khi công thức được sao chép sang các ô khác
    tham khảo hỗn hợp

    $A1/A$1

    Tham chiếu hàng thay đổi khi công thức được sao chép sang các ô khác nhưng tham chiếu cột là cố định/Tham chiếu cột thay đổi khi công thức được sao chép sang các ô khác nhưng tham chiếu hàng là cố định;
  • Nói chung, tham chiếu tuyệt đối không bao giờ thay đổi khi công thức được di chuyển. Tuy nhiên, tham chiếu tuyệt đối sẽ tự động điều chỉnh khi một hàng hoặc cột được thêm hoặc xóa khỏi đầu hoặc bên trái trong trang tính. Ví dụ: trong công thức "=$A$1+1", khi bạn chèn một hàng ở đầu trang tính, công thức sẽ tự động chuyển thành "=$A$2+1".

  • Sản phẩm F4 phím có thể chuyển đổi giữa tham chiếu tương đối, tham chiếu tuyệt đối và tham chiếu hỗn hợp.

Công cụ năng suất văn phòng tốt nhất

🤖 Trợ lý AI của Kutools: Cách mạng hóa việc phân tích dữ liệu dựa trên: Thực thi thông minh   |  Tạo mã  |  Tạo công thức tùy chỉnh  |  Phân tích dữ liệu và tạo biểu đồ  |  Gọi các hàm Kutools...
Các tính năng phổ biến: Tìm, đánh dấu hoặc xác định các bản sao   |  Xóa hàng trống   |  Kết hợp các cột hoặc ô mà không làm mất dữ liệu   |   Vòng không có công thức hữu ích. Cảm ơn !
Siêu tra cứu: Nhiều tiêu chí VLookup    VLookup Nhiều Giá Trị  |   VLookup trên nhiều trang tính   |   Tra cứu mờ ....
Danh sách thả xuống nâng cao: Tạo nhanh danh sách thả xuống   |  Danh sách thả xuống phụ thuộc   |  Danh sách thả xuống nhiều lựa chọn ....
Trình quản lý cột: Thêm một số cột cụ thể  |  Di chuyển cột  |  Chuyển đổi trạng thái hiển thị của các cột ẩn  |  So sánh dãy và cột hữu ích. Cảm ơn !
Các tính năng nổi bật: Tiêu điểm lưới   |  Chế độ xem thiết kế   |   Thanh công thức lớn    Trình quản lý sổ làm việc & trang tính   |  Thư viện tài nguyên (Văn bản tự động)   |  Bảng chọn ngày   |  Kết hợp các bảng tính   |  Mã hóa/Giải mã ô    Gửi email theo danh sách   |  Siêu lọc   |   Bộ lọc đặc biệt (lọc in đậm/nghiêng/gạch ngang...) ...
15 bộ công cụ hàng đầu12 bản văn CÔNG CỤ (thêm văn bản, Xóa ký tự,...)   |   50 + Biểu đồ Các loại (Biểu đồ Gantt,...)   |   40+ Thực tế Công thức (Tính tuổi dựa trên ngày sinh,...)   |   19 chèn CÔNG CỤ (Chèn mã QR, Chèn ảnh từ đường dẫn,...)   |   12 Chuyển đổi CÔNG CỤ (Số thành từ, Chuyển đổi tiền tệ,...)   |   7 Hợp nhất & Tách CÔNG CỤ (Các hàng kết hợp nâng cao, Chia ô,...)   |   ... và nhiều hơn nữa

Nâng cao kỹ năng Excel của bạn với Kutools for Excel và trải nghiệm hiệu quả hơn bao giờ hết. Kutools for Excel cung cấp hơn 300 tính năng nâng cao để tăng năng suất và tiết kiệm thời gian.  Bấm vào đây để có được tính năng bạn cần nhất...

Mô tả


Tab Office mang lại giao diện Tab cho Office và giúp công việc của bạn trở nên dễ dàng hơn nhiều

  • Cho phép chỉnh sửa và đọc theo thẻ trong Word, Excel, PowerPoint, Publisher, Access, Visio và Project.
  • Mở và tạo nhiều tài liệu trong các tab mới của cùng một cửa sổ, thay vì trong các cửa sổ mới.
  • Tăng 50% năng suất của bạn và giảm hàng trăm cú nhấp chuột cho bạn mỗi ngày!