VietNetwork.Vn

Tiết kiệm thời gian và tham chiếu đúng ô khi tạo công thức trong Excel.

• Tham chiếu ô Excel có thể là tương đối, tuyệt đối hoặc hỗn hợp để đạt được kết quả cụ thể trong công thức.
• Tham chiếu tương đối thay đổi khi được sao chép sang ô khác, trong khi tham chiếu tuyệt đối vẫn giữ nguyên.
• Tham chiếu hỗn hợp khóa cột hoặc hàng trong công thức.

Khi xử lý nhiều dữ liệu trong Excel, bạn có thể sử dụng các tham chiếu tương đối, tuyệt đối hoặc hỗn hợp để lưu công việc thủ công lặp đi lặp lại, đảm bảo công thức của bạn hoạt động chính xác và thu thập khối lượng lớn dữ liệu chỉ bằng một vài hành động nhanh chóng. Hãy cùng khám phá cách sử dụng các loại tham chiếu Excel này.

1. Tham chiếu ô Excel là gì?

Bạn sử dụng tham chiếu ô khi bạn muốn nắm bắt thông tin có trong một ô hoặc phạm vi ô khác. Ví dụ: công thức sau tham chiếu ô A1 và A2 và sẽ cộng nội dung của chúng lại với nhau để tạo ra kết quả.

=SUM(A1+A2)
Bạn có thể sử dụng các loại tham chiếu ô sau để đạt được kết quả cụ thể:

• Theo mặc định, các tham chiếu trong Excel là tương đối, tham chiếu đến vị trí tương đối của ô. Nếu bạn đang nhập một công thức trong ô A1 có liên quan đến ô A2, bạn đang tham chiếu ô nằm bên dưới một ô nơi bạn đang nhập. Nếu bạn sao chép công thức vào một ô khác, tham chiếu sẽ tự động thực hiện hành động tương tự như ở vị trí trước đó.
• Bạn cũng có thể sử dụng tham chiếu tuyệt đối, tham chiếu này không thay đổi nếu bạn sao chép công thức sang ô khác. Nếu bạn tạo một công thức tham chiếu đến một ô bằng cách sử dụng tham chiếu tuyệt đối thì ô đó sẽ được tham chiếu đến bất kỳ nơi nào công thức được sử dụng.
• Cuối cùng, bạn có thể sử dụng tham chiếu hỗn hợp, là sự kết hợp của cả hai.

Chúng ta hãy xem xét những điều này chi tiết hơn.

2. Cách sử dụng tham chiếu tương đối trong Excel

Trong ví dụ hiển thị ở đây, bạn có thể thấy một người đã chi bao nhiêu cho mỗi mục trong mỗi tháng. Điều họ muốn làm bây giờ là tính ra tổng số tiền chi tiêu mỗi tháng.


Trước tiên, hãy nhấp vào ô nơi bạn muốn thực hiện phép tính đầu tiên (trong trường hợp này là H2) và nhập công thức để tạo phép tính này. Trong ví dụ này, bạn sẽ sử dụng công thức sau:

=SUM(E2+E12+E22)

Điều này hiện đã tham chiếu các ô E2, E12 và E22 trong phép tính, cộng chúng lại với nhau. Đây là tham chiếu tương đối—công thức trong ô H2 đã tham chiếu đến các ô có ba cột ở bên trái (E2), ba cột ở bên trái và mười hàng xuống (E12) và ba cột ở bên trái và hai mươi hàng xuống ( E22), tương ứng.

Bây giờ, nếu bạn sao chép và dán công thức đó vào nơi khác, nó sẽ nhận các giá trị ở cùng vị trí tương đối với nơi bạn dán. Bạn cũng có thể sử dụng chức năng Tự động điền của Excel để hoàn thành phần còn lại của dữ liệu với cùng tham chiếu tương đối.


Như bạn có thể thấy ở đây, công thức trong ô H11 là:

=SUM(E11+E21+E31)
Điều này là do tham chiếu tương đối được đặt chín hàng xuống so với vị trí ban đầu. Bây giờ, thay vì nhập công thức cho mỗi tháng, bạn chỉ phải nhập công thức đó một lần rồi áp dụng cùng một tham chiếu tương đối vào bất cứ nơi nào bạn muốn trên bảng tính Excel của mình.

3. Cách sử dụng tham chiếu tuyệt đối trong Excel

Trong ví dụ này, kế toán công ty muốn tính toán số tiền phải trả cho nhân viên dựa trên số giờ họ đã làm việc và mức lương cố định theo giờ.


Bắt đầu bằng cách bấm vào ô nơi bạn muốn tính tổng đầu tiên (trong trường hợp này là E2) và bắt đầu nhập công thức thích hợp. Trong ví dụ này, nhân viên kế toán muốn nhân mức tính theo giờ ở ô A2 với tổng số giờ Jacinda đã làm việc ở D2.

=SUM(A2
Tuy nhiên, nếu chúng ta sử dụng tham chiếu tương đối ở đây, khi áp dụng công thức cho tổng số của các nhân viên khác, công thức sẽ tham chiếu ô A3 cho Raul, A4 cho Lucy, v.v., nghĩa là bạn sẽ không nhận được kết quả như mong muốn.


Vì vậy, chúng ta cần đảm bảo rằng Excel tiếp tục tham chiếu đến ô chính xác, trong trường hợp này là ô A2. Để thực hiện việc này, bạn cần chèn ký hiệu đô la trước mỗi phần tham chiếu của mình:

=SUM($A$2
Điều này bây giờ cho Excel biết rằng nếu chúng ta sử dụng cùng một công thức ở nơi khác thì A2 sẽ luôn được tham chiếu.

Thay vì nhập ký hiệu đô la theo cách thủ công, sau khi nhập tham chiếu ô, hãy nhấn F4 (bạn có thể cần tìm hiểu chức năng của phím Chức năng, tùy thuộc vào bàn phím của bạn) và ký hiệu đô la sẽ tự động xuất hiện.

Bây giờ bạn có thể hoàn thành công thức của mình. Trong trường hợp này, nó sẽ như sau:

=SUM($A$2*D2)

Lưu ý phần thứ hai của công thức (D2) không sử dụng tham chiếu tuyệt đối. Điều này là do chúng tôi muốn nhân ô A2 với số giờ tương ứng của mỗi nhân viên, do đó số giờ đó cần phải tương đối với số giờ của mỗi người trong cột D.

Bây giờ bạn có thể áp dụng công thức cho các ô khác bằng cách sao chép và dán nó hoặc sử dụng chức năng Tự động điền của Excel, biết rằng ô A2 được tham chiếu tuyệt đối.


4. Cách sử dụng tham chiếu hỗn hợp trong Excel

Có thể đôi khi bạn cần khóa hàng nhưng không khóa cột trong công thức của mình hoặc ngược lại. Ví dụ, trong trường hợp này, kế toán viên muốn tính xem mỗi nhân viên phải nộp bao nhiêu thuế dựa trên thuế suất cá nhân của họ.


Do đó, chúng ta cần khóa các giá trị trong cột E (thuế suất), chứ không phải các hàng vì chúng khác nhau đối với mỗi nhân viên chứ không phải các tham chiếu đến năm vì chúng cần phải có khả năng thích ứng.

Nhấp vào ô nơi bạn muốn thực hiện phép tính đầu tiên (trong trường hợp này là ô G2) và nhập công thức của bạn. Trong ví dụ trên, kế toán viên muốn bắt đầu bằng cách tính số thuế phải nộp năm 2021 của Arjun, vì vậy họ sẽ gõ công thức sau:

=SUM(B2*E2)
Bây giờ chúng tôi cần khóa cột E. Nếu chúng tôi sử dụng tính năng Tự động điền ở bên phải mà không sửa đổi công thức, nó sẽ không tính chính xác số thuế phải nộp năm 2022 của Arjun; mặc dù nó sẽ lấy chính xác tổng thu nhập hàng năm (vì chúng tôi không khóa ô B2), công thức cũng sẽ tham chiếu đến ô F2. Để thực hiện việc này, hãy thêm ký hiệu đô la trước tham chiếu đến cột E trong công thức của bạn:

=SUM(B2*$E2)
Chúng tôi không muốn sử dụng tham chiếu tuyệt đối ở đây vì điều đó có nghĩa là khi chúng tôi tính thuế phải nộp cho các nhân viên khác, thuế suất của Arjun sẽ được sử dụng thay vì thuế suất riêng của từng nhân viên. Về bản chất, chỉ khóa cột E có nghĩa là chúng ta sẽ giữ nguyên mức thuế suất nhưng có thể điều chỉnh tương đối phù hợp với mức thuế suất khác nhau của mỗi người.


Giờ đây, chúng tôi có thể sử dụng chức năng Tự động điền để hoàn thành tổng số thuế phải nộp của Arjun, biết rằng cột E sẽ vẫn bị khóa trong các phép tính của chúng tôi, trong khi tổng số cho mỗi năm sẽ di chuyển tương ứng với vị trí đặt công thức.


Bởi vì chúng tôi đã khóa cột E nhưng vẫn để các hàng tương đối trong công thức của mình nên chúng tôi có thể tự tin hoàn thành tổng số nhân viên còn lại dựa trên thuế suất cá nhân của họ. Hãy nhớ rằng, bạn có thể nhấp vào bất kỳ ô nào trong kết quả của mình và xem công thức trong thanh công thức để kiểm tra xem các ô có được tham chiếu chính xác hay không.


5. Cách chuyển đổi giữa các loại tham chiếu ô khác nhau trong Excel

Nếu bạn muốn dễ dàng chuyển đổi giữa các tham chiếu tương đối, tuyệt đối và hỗn hợp trong trang tính Excel của mình, bạn không cần phải nhập ký hiệu đô la mỗi lần. Thay vào đó, hãy bấm vào ô bạn muốn sửa đổi, sau đó, trong thanh công thức, hãy bấm vào phần công thức bạn muốn chuyển đổi (nhấp trực tiếp trước tham chiếu, ở giữa tham chiếu hoặc ngay sau tham chiếu sẽ có tác dụng).

Trong trường hợp này, chúng tôi muốn chuyển đổi giữa các loại tham chiếu ô cho ô G11, vì vậy chúng tôi cần đảm bảo nhấp vào vị trí nào đó trên hoặc bên cạnh tham chiếu ô đó.


Sau đó, nhấn F4 và bạn sẽ thấy kiểu tham chiếu ô thay đổi.

Đầu tiên nó sẽ chuyển sang một tham chiếu tuyệt đối:

$G$11
Sau đó, một tham chiếu hỗn hợp với hàng bị khóa:

G$11
Sau đó, một tham chiếu hỗn hợp với cột bị khóa:

$G11
Và cuối cùng, nó sẽ trở lại mặc định, một tham chiếu tương đối:

G11
Nếu bạn muốn chuyển đổi nhiều tham chiếu trong cùng một công thức cùng lúc, chỉ cần đánh dấu các tham chiếu có liên quan trong công thức và làm theo các bước tương tự.

Và bạn đã hoàn tất! Ai biết rằng chỉ sự hiện diện của ký hiệu đô la cũng có thể giúp bạn sắp xếp các tham chiếu ô của mình một cách nhanh chóng và chính xác trong Excel?