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.

• Tham chiếu tuyệt đối là gì
• Cách tạo tham chiếu tuyệt đối
• Sử dụng tham chiếu tuyệt đối với các ví dụ
• Tính tỷ lệ phần trăm của tổng số
• Tìm kiếm một giá trị và trở về giá trị khớp tương ứng
• 2 clicks to batch make cell references absolute with Kutools
• Tham chiếu tương đối và tham chiếu hỗn hợp
• Những điều cần ghi nhớ

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

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

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

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

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 clicks to batch make cell references absolute with 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 for 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.

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.

Để 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.

Đ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.

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.

Để 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

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ả.

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".

  

• Mô hình 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.