Tại sao phải sử dụng ký hiệu dollar ($) trong công thức Excel – các tham chiếu ô tuyệt đối và tương đối

 Trong bài viết này, Học Excel Online cung cấp cho bạn đầy đủ chi tiết về tính năng của dấu $.

Tầm quan trọng của tham chiếu ô không thể xem thường. Hiểu sự khác biệt giữa tham chiếu tuyệt đối, tương đối và hỗn hợp là bạn có thể nắm tính linh hoạt của các công thức và hàm trong Excel.

Tất cả các bạn có lẽ đã thấy dấu dollar ($) trong công thức Excel và tự hỏi rốt cuộc chúng là gì. Thực tế, bạn có thể tham chiếu một và cùng một ô theo bốn cách khác nhau, ví dụ A1, $A$1, $A1, và A$1.

Dấu dollar trong một tham chiếu ô ảnh hưởng đến một điều duy nhất – nó hướng dẫn Excel cách đối xử lý với dữ liệu tham chiếu khi công thức được di chuyển hoặc sao chép sang các ô khác. Tóm lại, sử dụng dấu $ trước hàng và cột tạo ra một tham chiếu ô tuyệt đối, nó sẽ không thay đổi. Nếu không có dấu dollar, tham chiếu là tương đối và nó sẽ thay đổi.

Nếu bạn đang viết một công thức vào một ô đơn, bạn có thể dùng bất kỳ loại tham chiếu nào và đều có được công thức đúng. Nhưng nếu bạn dự định sao chép công thức của bạn vào các ô khác, việc chọn kiểu tham chiếu ô thích hợp là rất quan trọng. Nếu bạn muốn nghiên cứu nghiêm túc, hãy đầu tư một vài phút học tất cả kiến thức của các tham chiếu ô tuyệt đối và tương đối trong Excel, và khi nào sử dụng nó.

Tham chiếu ô Excel là gì?

Một cách đơn giản thì tham chiếu ô trong Excel là một địa chỉ ô. Nó cho Microsoft Excel biết nơi để tìm kiếm giá trị mà bạn muốn sử dụng trong công thức.

Ví dụ, nếu bạn nhập một công thức đơn giản =A1 trong ô C1, Excel sẽ kéo một giá trị từ ô A1 vào C1:

Như đã đề cập, miễn là bạn viết một công thức cho một ô , bạn có thể sử dụng bất kỳ kiểu tham chiếu nào, có hoặc không có dấu dollar ($), kết quả vẫn sẽ giống nhau:

Nhưng nếu bạn muốn di chuyển hoặc sao chép công thức trên worksheet, điều quan trọng là bạn phải chọn đúng loại tham chiếu cho công thức để sao chép chính xác đến các ô khác. .

Chú ý. Ngoài kiểu tham chiếu A1, các cột được xác định theo chữ cái và các dòng theo số, cũng tồn tại kiểu tài liệu tham chiếu R1C1 khi cả hàng và cột được xác định bằng số (R1C1 chỉ định hàng 1, cột 1).

Bởi vì A1 là kiểu tham chiếu mặc định trong Excel và nó hầu như được sử dụng mọi lúc, nên chúng ta chỉ thảo luận về các tham chiếu loại A1 trong hướng dẫn này. Nếu ai hiện đang sử dụng kiểu R1C1, thì có thể tắt nó bằng cách nhấp vào File > Options > Formulas và bỏ chọn hộp R1C1 reference style.

Tham chiếu ô tương đối (không có ký hiệu $)

Một tham chiếu tương đối trong Excel là một tham chiếu ô không có dấu dollar, như A1 .

Sử dụng tham chiếu ô tương đối trong công thức để điều chỉnh tham chiếu đó tùy thuộc vào nơi bạn sao chép công thức. Chính xác hơn, một tham chiếu tương đối thay đổi dựa trên vị trí tương đối của các hàng và cột.

Giả sử bạn có công thức sau trong ô B1:

=A1*10

Nếu bạn sao chép công thức này sang một hàng khác trong cùng một cột, chẳng hạn ô B2, công thức sẽ điều chỉnh cho dòng 2 (A2 * 10) vì Excel giả định bạn đang muốn nhân một giá trị trong mỗi hàng cột A với 10.

Nếu bạn sao chép công thức chứa tham chiếu ô tương đối đến một cột khác trong cùng một hàng, Excel sẽ thay đổi tham chiếu cột cho phù hợp:

Và nếu bạn sao chép hoặc di chuyển một công thức Excel chứa tham chiếu ô tương đối đến một hàng khác và một cột khác, cả hai tham chiếu cột và hàng sẽ thay đổi:

Như bạn thấy, việc sử dụng tham chiếu ô tương đối trong công thức Excel là một cách tiện lợi để thực hiện các tính toán giống nhau trên toàn bộ bảng tính. Để rõ hơn điều này, hãy xem một ví dụ thực tế.

Sử dụng tham chiếu tương đối – công thức ví dụ

Giả sử bạn có một cột giá USD (cột B) trong bảng tính, và bạn muốn chuyển chúng sang EUR. Biết tỷ lệ chuyển đổi USD – EUR (thời điểm này là 0.93), công thức cho hàng 2 đơn giản là =B2*0.93. Chú ý rằng chúng ta đang sử dụng một tham chiếu ô tương đối, không có dấu dollar.

Nhấn phím Enter, kết quả sẽ xuất hiện ngay lập tức trong ô.

Mẹo. Theo mặc định, tất cả các tham chiếu ô trong Excel là các tham chiếu tương đối. Vì vậy, khi viết một công thức, bạn có thể thêm một tham chiếu tương đối bằng cách nhấp vào ô tương ứng trên bảng tính thay vì nhập một tham chiếu ô theo cách thủ công.

Để sao chép công thức vào các ô bên cạnh , hãy di chuột qua thanh điều khiển fill handle  (một hình vuông nhỏ ở góc dưới cùng bên phải của ô được chọn). Khi bạn làm điều này, con trỏ sẽ chuyển thành một chữ thập màu đen mỏng, bạn giữ và kéo nó qua các ô bạn muốn tự động sao chép công thức.

Để sao chép công thức vào các ô không liền kề , hãy nhấp vào ô chứa công thức, nhấn Ctrl + C để sao chép, chọn một ô khác hoặc một dải ô, và nhấn Ctrl + V để dán công thức.

Công thức được sao chép vào các ô khác với tham chiếu tương đối được điều chỉnh phù hợp cho mỗi ô riêng lẻ. Để đảm bảo giá trị trong mỗi ô được tính chính xác, chọn bất kỳ ô nào và xem công thức trong thanh công thức. Trong ví dụ này, tôi chọn ô C4 và thấy rằng tham chiếu ô trong công thức liên quan đến hàng 4, vậy là chính xác!:

Phím tắt để sao chép công thức vào các ô khác

Ctrl + D –  lệnh sao chép dữ liệu từ các ô ở trên. Ví dụ, nếu bạn có một công thức trong ô A1 và bạn muốn sao chép nó vào ô A2, chọn A2 và nhấn tổ hợp phím Ctrl + D .

Ctrl + R – để sao chép dữ liệu từ ô bên trái. Ví dụ, nếu bạn có một công thức trong ô A1 và bạn muốn sao chép nó vào ô B1, chọn B1 và nhấn Ctrl + R .

Các phím tắt này có thể được sử dụng để sao chép các công thức cho nhiều ô. Thủ thuật là chọn cả ô nguồn và ô đích trước khi nhấn phím tắt. Ví dụ, nếu bạn muốn sao chép công thức từ A1 đến 9 hàng tiếp theo, chọn các ô A1: A10 và nhấn tổ hợp phím Ctrl + D .

Tham chiếu ô tuyệt đối trong Excel (với dấu $)

Một tham chiếu tuyệt đối trong Excel là một tham chiếu ô với dấu $, như $A$1 .

Dấu dollar cố định tham chiếu đến một ô nhất định, sao cho nó vẫn tĩnh bất kể vị trí của công thức. Nói cách khác, bằng cách sử dụng $ trong tham chiếu ô cho phép bạn sao chép công thức trong Excel mà không thay đổi tham chiếu.

Xin lưu ý rằng trong tham chiếu tuyệt đối của Excel, có một dấu dollar ($) trước hàng và cột.

Ví dụ: nếu bạn có 10 trong ô A1 và bạn sử dụng tham chiếu ô tuyệt đối ($A$1), công thức =$A$1+5 sẽ luôn luôn trả về 15, cho dù các ô khác có công thức được sao chép. Mặt khác, nếu bạn viết cùng một công thức với một tham chiếu ô tương đối (A ), và sau đó sao chép nó xuống các ô khác trong cột, mỗi giá trị khác sẽ được tính cho mỗi hàng. Hình ảnh sau đây thể hiện sự khác biệt:

Chú ý. Mặc dù chúng ta đã nói rằng một dữ liệu tham chiếu tuyệt đối trong Excel không bao giờ thay đổi, trên thực tế nó thay đổi khi bạn thêm hoặc xoá các hàng và / hoặc các cột trong bảng tính của bạn và điều này thay đổi vị trí của ô được tham chiếu. Trong ví dụ trên, nếu chúng ta chèn một hàng mới ở đầu trang tính, Excel đủ thông minh để điều chỉnh công thức để phản ánh sự thay đổi đó:

Trong bảng tính thực, đây là một trường hợp rất hiếm khi bạn chỉ cần sử dụng các tham chiếu tuyệt đối trong công thức Excel của mình. Tuy nhiên, có rất nhiều nhu cầu thực tế đòi hỏi phải sử dụng cả dữ liệu tham chiếu tuyệt đối và tương đối, như thể hiện trong các ví dụ sau đây.

Sử dụng tham chiếu ô tương đối và tuyệt đối trong một công thức

Bạn có thể cần một công thức mà một số tham chiếu ô được điều chỉnh cho các cột và các hàng mà công thức được sao chép, trong khi các ô khác vẫn cố định. Nói cách khác, bạn phải sử dụng tham chiếu ô tương đối và tuyệt đối trong một công thức.

Ví dụ 1. Liên kết ô tương đối và tuyệt đối để tính toán số

Trong ví dụ trước đây của chúng ta với tỷ giá USD và EUR, bạn có thể không muốn nhập tỷ giá hối đoái trong công thức. Thay vào đó, bạn có thể nhập số đó vào một số ô, ví dụ C1 và cố định tham chiếu ô đó như sau:

Trong công thức này (B4 * $C$1), có hai loại tham chiếu ô:

• B4 – tham chiếu ô tương đối thay đổi theo mỗi hàng, và
• $C$1 – tham chiếu ô tuyệt đối  không bao giờ thay đổi bất kể công thức được sao chép đi đâu.

Lợi thế của cách này là người dùng có thể tính giá EUR dựa trên tỷ giá hối đoái thay đổi mà không thay đổi công thức. Khi tỷ giá trao đổi thay đổi, tất cả những gì bạn phải làm là cập nhật giá trị trong ô C1.

Ví dụ 2. Liên kết ô tương đối và tuyệt đối để tính ngày tháng

Một ứng dụng phổ biến khác của tham chiếu ô tuyệt đối và tương đối trong công thức đơn là tính ngày trong Excel dựa trên ngày hiện tại.

Giả sử bạn có một danh sách ngày phân phối trong cột B, và bạn nhập ngày hiện tại vào C1 bằng cách sử dụng hàm TODAY (). Những gì bạn muốn biết là mỗi chuyến hàng tốn bao nhiêu ngày và bạn có thể tính toán bằng cách sử dụng công thức sau: =B4-$C$1

Chúng ta sử dụng hai loại tham chiếu trong công thức:

• Tương đối cho ô có ngày giao hàng đầu tiên (B4), bởi vì bạn muốn tham chiếu ô này thay đổi tùy thuộc vào hàng mà công thức xuất hiện.
• Tuyệt đối với ô với ngày hiện tại ($C$1), bởi vì bạn muốn tham chiếu ô này không đổi.

Nói chung là bất cứ khi nào bạn muốn tạo một tham chiếu tĩnh, ghi thêm dấu dollar ($) trong công thức để tạo một dữ liệu tham chiếu tuyệt đối trong Excel.

Tham chiếu ô hỗn hợp

Một tham chiếu ô hỗn hợp trong Excel là tham chiếu mà một cột hoặc một hàng được cố định. Ví dụ: $A1 và A$1 là tham chiếu hỗn hợp.

Tham chiếu tuyệt đối của Excel chứa 2 dấu dollar ($) khóa cả cột và hàng. Trong một tham chiếu ô hỗn hợp, chỉ có một tọa độ cố định (tuyệt đối) và phần khác (tương đối) sẽ thay đổi dựa trên vị trí tương đối của hàng hoặc cột:

• Cột tuyệt đối và hàng tương đối, $A1. Khi một công thức với kiểu tham chiếu này được sao chép vào các ô khác, ký hiệu $ ở phía trước cột chữ cái sẽ khóa tham chiếu đến cột được chỉ định để nó không bao giờ thay đổi. Tham chiếu hàng tương đối, không có ký hiệu dollar, thay đổi tùy thuộc vào hàng mà công thức được sao chép.
• Cột tương đối và hàng tuyệt đối , A$1. Trong loại tham chiếu này, tham chiếu của hàng sẽ không thay đổi, còn tham chiếu của cột sẽ thay đổi.

Dưới đây bạn sẽ tìm thấy một ví dụ về việc sử dụng cả hai loại tham chiếu ô hỗn hợp, hy vọng sẽ làm cho mọi thứ dễ hiểu hơn.

Sử dụng tham chiếu hỗn hợp trong Excel – công thức ví dụ

Đối với ví dụ này, chúng ta sẽ sử dụng lại bảng chuyển đổi tiền tệ ở trên. Nhưng lần này, chúng ta sẽ không chỉ giới hạn việc chuyển đổi USD – EUR. Những gì chúng ta sẽ làm là chuyển đổi giá dollar sang một số loại tiền tệ khác, tất cả chỉ bằng một công thức duy nhất!

Để bắt đầu, hãy nhập tỷ giá ở một hàng, chẳng hạn hàng 2, như thể hiện trong ảnh chụp màn hình dưới đây. Và sau đó, bạn chỉ viết một công thức cho ô trên cùng bên trái (C5 trong ví dụ này) để tính giá EUR:

=$B5*C$2

Trong đó $B5 là giá dollar trong cùng một hàng và C$2 là tỷ lệ chuyển đổi USD – EUR.

Và bây giờ, sao chép công thức xuống các ô khác trong cột C, sau đó tự động điền các cột khác có cùng công thức bằng cách kéo chốt. Kết quả là bạn sẽ có 3 cột giá khác nhau được tính chính xác dựa trên tỷ giá tương ứng trong hàng 2 trong cùng một cột. Để xác minh điều này, hãy chọn bất kỳ ô nào trong bảng và xem công thức trong thanh công thức.

Ví dụ, hãy chọn ô D7 (trong cột GBP).  Ta thấy ở đây là công thức =$B7*D$2 tính giá USD ở B7 và nhân nó bằng giá trị trong D2, đó là tỷ lệ chuyển đổi USD-GBP.

Để hiểu làm thế nào mà Excel biết được cái nào cần phải có và tỷ giá hối đoái nào sẽ được lấy đem nhân. Đó là vì các tham chiếu ô hỗn hợp ra lệnh ($B5 * C$2).

• $B5 – cột tuyệt đối và hàng tương đối . Ở đây, bạn chỉ thêm ký hiệu dollar ($) trước cột chữ cái neo tham chiếu đến cột A, do đó, Excel luôn sử dụng giá gốc cho tất cả các chuyển đổi. Tham chiếu hàng (không có dấu dollar) không bị khóa vì bạn muốn tính giá cho mỗi hàng riêng lẻ.
• C$2 – cột tương đối và hàng tuyệt đối . Bởi vì tất cả các tỷ giá hối đoái nằm trong hàng 2, bạn sẽ khóa tham chiếu hàng bằng cách đặt dấu dollar ($) ở phía trước số hàng. Và bây giờ, cho dù bạn sao chép công thức, Excel sẽ luôn luôn tìm kiếm tỷ giá hối đoái trong hàng 2. Và vì tham chiếu cột là tương đối (không có dấu dollar), nó sẽ điều chỉnh cột ở công thức Sao chép.

Làm thế nào để tham chiếu toàn bộ cột hoặc hàng trong Excel

Khi bạn đang làm việc với một bảng tính Excel có một số các hàng thay đổi, bạn có thể muốn tham chiếu tất cả các ô trong một cột cụ thể. Để tham khảo toàn bộ cột, chỉ cần gõ hai lần ký cột với một dấu hai chấm ở giữa, ví dụ A:A .

Tham chiếu toàn bộ cột

Cũng như tham chiếu ô, toàn bộ tham chiếu cột có thể là tuyệt đối và tương đối, ví dụ:

• Tham chiếu cột tuyệt đối ,  ví dụ $A:$A
• Tham khảo cột tương đối, ví dụ A:A

Và một lần nữa, bạn sử dụng ký hiệu dollar ($) trong một tham chiếu cột tuyệt đối để khóa nó vào một cột nhất định, vì tham chiếu toàn bộ cột không thay đổi khi bạn sao chép một công thức vào các ô khác.

Một tham chiếu cột tương đối sẽ thay đổi khi công thức được sao chép hoặc di chuyển các cột khác và sẽ vẫn còn nguyên vẹn khi bạn sao chép công thức đến các ô khác trong cùng một cột.

Tham chiếu toàn bộ hàng

Để tham chiếu đến toàn bộ hàng, bạn sử dụng phương pháp tương tự, bạn nhập số hàng thay vì các ký tự cột:

• Tham chiếu hàng tuyệt đối,  ví dụ $1:$1
• Liên kết hàng tương đối, ví dụ 1:1

Về lý thuyết, bạn cũng có thể tạo ra một tham chiếu hỗn hợp toàn bộ cột hoặc hỗn hợp toàn bộ hàng, như $A:A hoặc $1:1, tương ứng. Nhưng chỉ “trên lý thuyết”, bởi vì không thấy có bất kỳ ứng dụng thực tế nào của các tham chiếu như vậy, mặc dù ví dụ 4 chứng minh rằng công thức với các tham chiếu đó có hoạt động.

Ví dụ 1. Tham chiếu toàn bộ cột của Excel (tuyệt đối và tương đối)

Giả sử bạn có một số con số trong cột B và bạn muốn biết tổng số và trung bình của chúng. Vấn đề là các hàng mới được thêm vào bảng mỗi tuần, do đó, viết một công thức thông thường SUM () hoặc AVERAGE () cho một phạm vi cố định của các ô không phải là cách tốt. Thay vào đó, bạn có thể tham chiếu toàn bộ cột B:

=SUM($B:$B)– sử dụng ký hiệu dollar ($) để tạo một tham chiếu toàn bộ cột tuyệt đối để khóa công thức vào cột B.

=SUM(B:B)– viết công thức không có $ để tạo một tham chiếu toàn bộ cột tương đối sẽ được thay đổi khi bạn sao chép công thức vào các cột khác.

Mẹo. Khi viết công thức, hãy nhấp vào ký tự cột để tham chiếu toàn bộ cột vào công thức. Cũng như các tham chiếu ô, theo mặc định Excel sẽ chèn tham chiếu tương đối (không có dấu dollar):

Cũng như thế, chúng ta viết một công thức để tính giá trung bình trong toàn bộ cột B:

=AVERAGE(B:B)

Trong ví dụ này, chúng ta đang sử dụng tham chiếu toàn bộ cột tương đối, vì vậy công thức của chúng ta được điều chỉnh đúng khi chúng ta sao chép nó vào các cột