Hầu hết mọi người đều quen thuộc với việc sử dụng tham chiếu tương đối trong Excel^{(in Excel)} . Điều này là do các tham chiếu ô trong bảng tính Excel được mặc định là phương pháp tham chiếu tương đối.

Tuy nhiên, đôi khi các tài liệu tham khảo tương đối gây khó chịu. Nó sẽ thay đổi các tham chiếu ô bất kỳ khi nào bạn sao chép ô hoặc điền vào các cột và hàng. Nếu bạn không muốn các tham chiếu thay đổi, bạn sẽ cần phải sử dụng tham chiếu tuyệt đối hoặc tham chiếu hỗn hợp (bao gồm cả tham chiếu tương đối và tuyệt đối).

Trong một tham chiếu tuyệt đối, cả tham chiếu cột và hàng đều “bị khóa”, vì vậy cả hai tham chiếu đó đều không thay đổi khi bạn sao chép hoặc điền từ ô đó.

Bài viết này sẽ xem xét cách bạn có thể sử dụng tham chiếu tuyệt đối để làm cho Excel hoạt động theo cách bạn muốn với dữ liệu.

Cách tham chiếu^(References) tương đối hoạt động trong Excel

Khi bạn nhập giá trị vào bảng tính Excel , mỗi ô có một chữ cái và số cụ thể được gán cho nó. Điều này đại diện cho cột và hàng của ô đó.

Ví dụ: giá trị của “1” trong bảng tính bên dưới nằm trong cột A và hàng 2. Vì vậy, “tham chiếu” cho ô này là A2 .

Nếu bạn muốn thực hiện một phép tính trong ô tiếp theo dựa trên ô này, bằng cách thêm 1 vào ô đó, bạn sẽ viết công thức sau:

=A2+1

Công thức này sẽ chèn giá trị từ A2 vào công thức, tính toán nó và sau đó xuất kết quả vào ô có công thức này.

Khi nhấn Enter , bạn sẽ thấy kết quả.

Với tham chiếu tương đối, bạn không phải nhập công thức này vào mọi ô khác. Tất cả những gì bạn phải làm là kéo góc của ô có công thức ban đầu xuống tùy thích. 

Trong ô tiếp theo, tham chiếu đến A2 sẽ trở thành A3. Trong ô bên dưới, A3 sẽ trở thành A4. Nói cách khác, Excel biết rằng bạn muốn thêm 1 vào ô trước đó, vì vậy Excel sẽ cập nhật số (tham chiếu hàng) tương ứng khi bạn kéo xuống.

Điều này hoạt động theo cách tương tự nếu bạn kéo công thức qua các cột. Thay vì cập nhật số, Excel sẽ cập nhật phần sau của tham chiếu (cột) để luôn tham chiếu đến ô phía trên nó.

Cột bên phải chứa B2, bên phải chứa C2, v.v.

Đây là một ví dụ đơn giản về cách địa chỉ tương đối hoạt động cho cả tham chiếu cột và ô.

Cách tham chiếu^(References) tuyệt đối hoạt động trong Excel

^(Absolute)Tham chiếu tuyệt đối trong Excel cho phép bạn tham chiếu cùng một ô, thay vì để Excel tự động cập nhật^{(Excel automatically update)} tham chiếu hàng hoặc cột cho bạn. Tham chiếu "hỗn hợp" là nếu bạn chỉ khóa hàng hoặc cột và "tham chiếu tuyệt đối" là khi bạn khóa cả hai.

Hãy xem một số ví dụ.

Giả sử bảng tính của bạn có “10” ở hàng trên cùng và bạn muốn mọi hàng bên dưới nó nhân số đó với số trong ô bên trái. 

Để làm điều này, bạn phải nhập một công thức giống như sau:

=B$2*A3

Thao tác này sẽ khóa tham chiếu “2” nên nó sẽ không thay đổi tham chiếu hàng nếu bạn kéo ô có công thức này xuống các ô bên dưới nó. Vì A3 vẫn được “mở khóa” nên cả tham chiếu hàng và cột sẽ vẫn tự động thay đổi và luôn tham chiếu ô ở bên trái.

Bạn sẽ nhận thấy rằng điều này chỉ hoạt động vì bạn đang kéo xuống các ô trong cùng một cột. Vì vậy, bạn không cần phải khóa cột (B) bằng cách đặt một ký hiệu đô la ($) trước nó.

Vấn đề với điều này là nếu bạn muốn sử dụng cùng một công thức ở bên phải của công thức ban đầu, thì tham chiếu “B” sẽ thay đổi và công thức sẽ không còn tham chiếu B2 như dự định.

Hãy cùng xem cách sử dụng tham chiếu tuyệt đối thay vì tham chiếu hỗn hợp để làm cho việc điền vào cả hai hướng hoạt động hiệu quả.

Sử dụng đúng tham chiếu ^(References)tuyệt đối^(Absolute) trong Excel

Để sử dụng tham chiếu chính xác trong công thức này, bạn phải xem xét chính xác những gì bạn đang cố gắng thực hiện.

Trong trường hợp này, chúng tôi muốn các hành vi sau đây khi điền vào bên phải.

• Luôn tham chiếu giá trị trong ô B2
• Luôn tham chiếu giá trị trong cột A
• Chuyển tham chiếu của hàng cho cột A thành hàng hiện tại của công thức

Nhìn vào những hành vi này, bây giờ bạn biết những gì bạn cần “khóa” và những gì bạn không. Cả “B” và “2” cần được khóa (không thay đổi). Ngoài ra, cột A cần được khóa.

Vì vậy, công thức của bạn trong B3 cần phải có dạng như sau: =$B$2*$A3

Bây giờ khi bạn kéo cùng một ô này xuống hoặc lên, công thức sẽ hoạt động như dự định.

Việc sử dụng tham chiếu tuyệt đối một cách chính xác có thể trở nên phức tạp, vì vậy cần dành thời gian để xem xét cẩn thận cách bạn muốn Excel cập nhật công thức khi bạn điền các cột hoặc hàng^{(columns or rows)} theo một trong hai hướng.

Đạp xe qua các kiểu tham chiếu^{(Reference Types)} trong Excel

Bạn có thể tăng tốc quá trình khi nhập công thức^{(typing formulas)} có tham chiếu tuyệt đối bằng cách nhấn phím F4 , thao tác này sẽ làm cho tham chiếu ô là tuyệt đối.

Con trỏ có thể ở hai bên của tham chiếu ô (hoặc thậm chí ở giữa ô) khi bạn nhấn F4 và nó sẽ vẫn chuyển đổi tham chiếu đơn lẻ đó thành tuyệt đối.

Nếu bạn không muốn tuyệt đối (ví dụ: thay vào đó là hỗn hợp), hãy tiếp tục nhấn F4 cho đến khi tham chiếu trông theo cách bạn muốn.

Nếu bạn muốn thêm bất kỳ loại tham chiếu nào đến các ô khác trong công thức, chỉ cần đặt con trỏ của bạn ở đó và bắt đầu chuyển qua F4 một lần nữa.

Khi bạn đã định cấu hình công thức của mình, chỉ cần nhấn Enter và bắt đầu điền vào bảng tính của bạn theo bất kỳ hướng nào bạn muốn. Nếu bạn thiết lập đúng tham chiếu của mình, mọi thứ sẽ hoạt động như mong đợi.

How to Use Absolute References in Excel

Most people are familiar with using relative references in Excel. This is because cell references in Excel spreadsheets default to the relative reference method.

However, there are times when relative references get annoying. It will change cell references any time you copy cells or fill columns and rows. If you don’t want references to change, you’ll need to go with absolute references or mixed references (including relative and absolute references).

In an absolute reference, both the column and row references are “locked,” so neither of those change when you copy or fill from that cell.

This article will look at how you can use absolute references to make Excel behave the way you want with data.

How Relative References Work in Excel

When you enter values into an Excel spreadsheet, each cell has a specific letter and number assigned to it. This represents the column and row of that cell.

For example, the value of “1” in the spreadsheet below is in column A and row 2. So the “reference” to this cell is A2.

If you want to perform a calculation in the next cell based on this cell, by adding 1 to it, you will write the following formula:

=A2+1

This formula will insert the value from A2 into the formula, calculate it, and then output the result into the cell where this formula is.

When you press Enter, you’ll see the result.

With relative referencing, you don’t have to type this formula into every other cell. All you have to do is drag the corner of the cell with the original formula down as far as you’d like. 

In the next cell, the reference to A2 will become A3. In the cell below that, A3 will become A4. In other words, Excel knows that you want to add 1 to the previous cell, so Excel updates the number (the row reference) accordingly as you drag down.

This works in the same way if you drag the formula across columns. Instead of updating the number, Excel will update the latter part of the reference (the column) to always reference the cell above it.

The column to the right contains B2, to the right of that contains C2, and so on.

This is a simple example of how relative addressing works for both column and cell references.

How Absolute References Work in Excel

Absolute references in Excel let you reference the same cell, rather than letting Excel automatically update the row or column references for you. “Mixed” referencing is if you lock only the row or the column, and “absolute referencing” is when you lock both.

Let’s look at some examples.

Let’s say your spreadsheet has a “10” in a top row, and you want every row beneath it to multiply that number by the number in the cell to the left. 

To do this, you’d enter a formula that looks like this:

=B$2*A3

This locks the “2” reference so it won’t change the row reference if you drag the cell with this formula down to the cells below it. Since A3 remains “unlocked” both the row and column reference will still change automatically and always reference the cell to the left.

You will notice that this only works because you’re dragging down into cells in the same column. So you don’t have to lock column (B) by placing a dollar sign ($) in front of it.

The problem with this is that if you want to use the same formula to the right of the original formula, the “B” reference will change, and the formula will no longer reference B2 as intended.

Let’s take a look at how to use absolute references instead of mixed references to make filling in both directions work properly.

Correctly Using Absolute References in Excel

To use the correct referencing in this formula, you must consider precisely what you’re trying to do.

In this case, we want the following behaviors when filling to the right.

• Always reference the value in cell B2
• Always reference the value in column A
• Shift the reference of the row for column A to the current row of the formula

Looking at these behaviors, you now know what you need to “lock” and what you don’t. Both “B” and “2” need to be locked (unchanged). Also, column A needs to be locked.

So your formula in B3 needs to look like this: =$B$2*$A3

Now when you drag this same cell either down or up, the formula works as intended.

Correctly using absolute referencing can get tricky, so it’s essential to take the time to carefully consider how you want Excel to update the formula as you fill columns or rows in either direction.

Cycling Through Reference Types in Excel

You can speed along the process when you’re typing formulas with absolute references by pressing the F4 key, which will make the cell reference absolute.

The cursor can be on either side of the cell reference (or even in the middle of it) when you press F4, and it’ll still convert that single reference to absolute.

If you don’t want absolute (for example, mixed instead), keep tapping F4 until the reference looks the way you want.

If you want to add any kind of referencing to other cells in the formula, just place your cursor there and start cycling through F4 again.

Once you have configured your formula, just press Enter and begin filling your spreadsheet in any direction you like. If you set up your references right, everything should work just as expected.

Related posts

• Bảo tồn tham chiếu ô khi sao chép công thức trong Excel

• Cách xóa dòng trống trong Excel

• Cách sử dụng Tính năng Nói ô trong Excel

• Cách Chèn Trang tính Excel vào Tài liệu Word

• Cách sử dụng Phân tích Điều gì xảy ra trong Excel

• Cách viết công thức / câu lệnh IF trong Excel

• Cách tính điểm Z trong Excel

• Khi nào sử dụng khớp chỉ mục thay vì hàm VLOOKUP trong Excel

• Nhóm các hàng và cột trong một trang tính Excel

• Cách tạo nhiều danh sách thả xuống được liên kết trong Excel

• Cách sửa một hàng trong Excel

• Cách Chèn CSV hoặc TSV vào một trang tính Excel

• Hướng dẫn VBA nâng cao cho MS Excel

• Cách tạo Macro hoặc tập lệnh VBA trong Excel

• Cách sửa lỗi # N / A trong công thức Excel như VLOOKUP

• Cách tạo một bảng tổng hợp đơn giản trong Excel

• Kết nối Excel với MySQL

• Mảng VBA trong Excel là gì và cách lập trình một

• Cách hợp nhất dữ liệu trong nhiều tệp Excel

• Cách chia sẻ tệp Excel để cộng tác dễ dàng