Hiểu biết về tham chiếu ô Excel tương đối và tuyệt đối và tìm hiểu lý do tại sao sử dụng ký hiệu đô la ($) trong công thức Excel

  • rdom September 25, 2020

Tham chiếu ô tuyệt đối trong Excel (dấu $)
Tham chiếu tuyệt đối trong Excel là địa chỉ của ô có dấu đô la ($) trong tọa độ hàng hoặc cột, ví dụ: $ A $ 1.

Ký hiệu đô la sửa chữa tham chiếu đến ô đã cho để nó giữ nguyên bất kể công thức di chuyển đến đâu. Nói cách khác, sử dụng $ trong tham chiếu ô cho phép bạn sao chép công thức vào Excel mà không thay đổi tham chiếu.

Tất tần tật về quản lý xuất nhập tồn bằng excel miễn phí

Tham chiếu ô tuyệt đối trong Excel

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 trả về 15, bất kể ô nào khác mà 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 tham chiếu ô tương đối (A1) và sau đó sao chép nó sang các ô khác trong cột, một 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:
Tham chiếu ô tuyệt đối so với tham chiếu tương đối

Ghi chú. Mặc dù chúng tôi đã nói rằng một tham chiếu tuyệt đối không bao giờ thay đổi trong Excel, nhưng nó thực sự thay đổi khi bạn thêm hoặc xóa các hàng và / hoặc cột trong trang tính của mình và điều này thay đổi vị trí của 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 thay đổi này:
Tham chiếu tuyệt đối thay đổi khi bạn thêm hoặc xóa các hàng hoặc cột trong trang tính.
Trong trang tính thực, rất hiếm khi chỉ các tham chiếu tuyệt đối được sử dụng trong công thức Excel. Tuy nhiên, có nhiều nhiệm vụ yêu cầu sử dụng cả tham chiếu tuyệt đối và tương đối, như được minh họa trong các ví dụ sau.

>>> Tìm hiểu chi tiết: Top 20 bài viết chia sẻ kiến thức công nghệ thủ thuật hay nhất – digitalfuture

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

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

Ví dụ 1. Tham chiếu ô tương đối và tuyệt đối để tính toán các số
Trong ví dụ trước của chúng tôi với giá USD và EUR, bạn không thể thanh toán cho tỷ giá hối đoái được mã hóa cứng trong công thức. Thay vào đó, bạn có thể nhập số này vào một số ô, chẳng hạn C1 và sửa tham chiếu ô này trong công thức bằng cách sử dụng ký hiệu đô la ($), như được hiển thị trong ảnh chụp màn hình sau:
Sử dụng tham chiếu ô tương đối và tuyệt đối trong một công thức

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

B4 là tham chiếu ô tương đối được điều chỉnh cho mỗi hàng và
$ C $ 1 là tham chiếu ô tuyệt đối không bao giờ thay đổi cho dù công thức được sao chép ở đâu.
Ưu điểm của phương pháp này là người dùng có thể tính giá EUR dựa trên tỷ giá hối đoái có thể thay đổi mà không cần thay đổi công thức. Khi tỷ lệ chuyển đổ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. Tham chiếu ô tương đối và tuyệt đối để tính toán ngày tháng
Một cách sử dụng phổ biến khác của tham chiếu ô tương đối và tuyệt đối trong một công thức là tính toán ngày trong Excel dựa trên ngày hôm nay.

Giả sử bạn có một danh sách ngày giao hàng 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 (). Điều bạn muốn biết là mỗi mặt hàng sẽ được vận chuyển trong bao nhiêu ngày và bạn có thể tính toán điều này bằng công thức sau: “B4- $ C $ 1
Sử dụng tham chiếu ô tương đối và tuyệt đối để tính toán ngày tháng

Một lần nữa, chúng tôi sử dụng hai loại liên kết trong công thức:

Tương đối của ô có ngày phân phối đầu tiên (B4) vì bạn muốn tham chiếu ô này thay đổi tùy thuộc vào hàng nơi chứa công thức.
Tuyệt đối cho ô có ngày hôm nay ($ C $ 1) vì bạn muốn tham chiếu ô này không đổi.
Tóm lại, bất cứ khi nào bạn muốn Excel tạo tham chiếu ô tĩnh luôn tham chiếu đến cùng một ô, hãy đảm bảo bao gồm ký hiệu đô la ($) trong công thức của bạn để tạo tham chiếu tuyệt đối trong Excel.

>>> Tìm hiểu chi tiết: https://digitalfuture.vn/tong-hop-15-bai-viet-ham-excel-thong-dung-nhat-cho-dan-van-phong

Tham chiếu ô hỗn hợp trong Excel

Tham chiếu ô hỗn hợp trong Excel là tham chiếu trong đó ký tự của cột hoặc số hàng được cố định. Ví dụ: $ A1 và A $ 1 là các liên kết hỗn hợp. Nhưng mỗi chúng có nghĩa là gì? Nó rất đơn giản.

Như bạn nhớ, một tham chiếu tuyệt đối trong Excel chứa 2 ký hiệu đô la ($) chặn cả cột và hàng. Trong tham chiếu ô hỗn hợp, chỉ một tọa độ (tuyệt đối) được cố định và tọa độ kia (tương đối) sẽ thay đổi tùy thuộc vào vị trí tương đối của hàng hoặc cột:

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

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 – ví dụ về công thức
Trong ví dụ này, chúng ta sẽ sử dụng lại bảng chuyển đổi tiền tệ. Nhưng lần này chúng tôi sẽ không chỉ giới hạn trong việc chuyển đổi USD – EUR. Những gì chúng ta sẽ làm là chuyển đổi giá đô la sang một loạt các loại tiền tệ khác, tất cả đều có cùng một công thức!

Để bắt đầu, hãy nhập tỷ lệ chuyển đổi trên một số dòng, chẳng hạn như dòng 2, như thể hiện trong ảnh chụp màn hình bên dưới. Và sau đó bạn chỉ viết một công thức ô 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á của một đô la trong cùng một hàng và C $ 2 là tỷ giá chuyển đổi USD – EUR.

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

Bây giờ sao chép công thức xuống các ô khác trong cột C, rồi tự động điền vào các cột khác có cùng công thức bằng cách kéo chốt điền. Điều này sẽ dẫn đến 3 cột giá khác nhau trong một cột, được tính toán chính xác dựa trên tỷ giá hối đoái tương ứng trong hàng 2. Để kiểm tra đ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). Những gì chúng ta thấy ở đây là công thức “$ B7” D $ 2 lấy giá USD trong B7 và nhân nó với giá trị trong D2, là tỷ lệ chuyển đổi USD-GBP, chính xác như những gì bác sĩ đã yêu cầu 🙂

Tham chiếu ô hỗn hợp được điều chỉnh chính xác cho từng ô.

Bây giờ chúng ta hãy hiểu điều này xảy ra như thế nào, Excel biết chính xác giá cần lấy và tỷ giá hối đoái nào để nhân nó. Như bạn có thể đoán, đây là các tham chiếu ô hỗn hợp thực hiện thủ thuật ($ B5’C $ 2).

$ B5 là một cột tuyệt đối và một hàng tương đối. Ở đây, bạn thêm một ký hiệu đô la ($) ngay trước ký tự cột để cắt tham chiếu đến cột A, vì vậy Excel luôn sử dụng giá USD gốc cho tất cả các chuyển đổi. Tham chiếu hàng (chưa ký $) không bị chặn vì bạn muốn tính giá riêng cho từng hàng.
C $ 2 – cột tương đối và hàng tuyệt đối. Vì tất cả tỷ giá hối đoái đều ở dòng 2, bạn khóa tham chiếu dòng bằng cách đặt một ký hiệu đô la ($) trước số dòng. Và bây giờ, bất kể bạn sao chép công thức ở hàng nào, Excel sẽ luôn tìm kiếm tỷ giá hối đoái ở hàng 2. Và vì tham chiếu cột là tương đối (không có dấu $), nó sẽ được điều chỉnh cho cột mà công thức đã được sao chép.

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

Liên kết đến một cột vững chắc
Cùng với 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 như $ A: $ A
Tham chiếu cột tương đối như A: A
Và một lần nữa, bạn sử dụng ký hiệu đô la ($) trong tham chiếu cột tuyệt đối để khóa nó vào một cột cụ thể, đối với toàn bộ cột, các tham chiếu không thay đổi khi bạn sao chép công thức sang các ô khác.

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

Liên kết đến toàn bộ chuỗi
Toàn bộ hàng sử dụng cùng một cách tiếp cận, ngoại trừ số hàng thay vì một cột:

Tham chiếu chuỗi tuyệt đối như $ 1: $ 1
Tham chiếu chuỗi tương đối như 1: 1
Về lý thuyết, bạn cũng có thể tạo tham chiếu toàn bộ cột hỗn hợp hoặc tham chiếu toàn hàng hỗn hợp, chẳng hạn như $ A: A hoặc $ 1: 1, tương ứng. Tôi nói “trên lý thuyết” bởi vì tôi không thể nghĩ ra bất kỳ ứng dụng thực tế nào cho các tham chiếu như vậy, mặc dù Ví dụ 4 chứng minh rằng các công thức có tham chiếu như vậy hoạt động chính xác như chúng cần.

Ví dụ 1. Liên kết đến toàn bộ cột Excel (tuyệt đối và tương đối)
Giả sử bạn có một số số trong cột B và muốn biết tổng 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, vì vậy việc viết công thức thông thường SUM () hoặc AVERAGE () cho một phạm vi ô cố định không phải là cách tốt. Thay vào đó, bạn có thể tham khảo toàn bộ cột B:

SUM ($ B: $ B) – Sử dụng ký hiệu đô la ($) để tạo tham chiếu tuyệt đối đến toàn bộ cột chặn công thức cột B.

SUM (B: B) – Viết công thức không có $ để tạo tham chiếu cột tương đối. Điều này sẽ thay đổi khi bạn sao chép công thức sang các cột khác.

Hội đồng. Khi viết công thức, hãy bấm vào ký tự cột để thêm toàn bộ liên kết cột vào công thức. Như với tham chiếu ô, Excel sẽ chèn một tham chiếu tương đối (không dấu $) theo mặc định:
Viết công thức để tham chiếu đến toàn bộ cột

Theo cách tương tự, chúng ta viết công thức tính giá trung bình trong toàn bộ cột B:

TRUNG BÌNH (B: B)

Trong ví dụ này, chúng tôi đang sử dụng một tham chiếu tương đối cho toàn bộ cột, vì vậy công thức của chúng tôi sẽ điều chỉnh chính xác khi được sao chép sang các cột khác:
Sử dụng tham chiếu tương đối toàn bộ cột để công thức điều chỉnh khi sao chép sang các cột khác.

Ghi chú. Khi sử dụng tham chiếu cột trong công thức Excel, đừng bao giờ nhập công thức vào cùng một cột. Ví dụ: có vẻ như bạn nên nhập công thức “SUM (B: B” vào một trong các ô trống trong cột B để có tổng ở cuối cùng cột đó. Đừng! Điều này sẽ tạo ra cái gọi là tham chiếu vòng tròn và công thức sẽ trả về 0.
Ví dụ 2. Trợ giúp Excel của toàn bộ chuỗi (tuyệt đối và tương đối)
Nếu dữ liệu trong trang tính Excel được tổ chức theo hàng chứ không phải cột, thì bạn có thể tham chiếu đến toàn bộ hàng trong công thức. Ví dụ: theo cách này, chúng ta có thể tính giá trung bình trong hàng 2:

AVERAGE ($ 2: $ 2) – Tham chiếu tuyệt đối đến toàn bộ hàng được khóa thành một hàng cụ thể có ký hiệu đô la ($).

AVERAGE (2: 2) – Tham chiếu tương đối đến toàn bộ hàng sẽ thay đổi khi công thức được sao chép sang các hàng khác.

Trong ví dụ này, chúng tôi cần một tham chiếu tương đối cho toàn bộ hàng vì chúng tôi có 3 hàng dữ liệu và chúng tôi muốn tính giá trị trung bình trong mỗi hàng bằng cách sao chép cùng một công thức:
Sử dụng tham chiếu đến toàn bộ chuỗi trong Excel

Ví dụ 3. Cách tham chiếu toàn bộ cột trừ một vài hàng đầu tiên
Đây là một vấn đề rất có liên quan vì thường thì vài dòng đầu tiên của trang tính chứa một số thông tin giới thiệu hoặc giải thích và bạn không muốn đưa chúng vào tính toán của mình. Thật không may, Excel không cho phép các tham chiếu như B5: B bao gồm tất cả các hàng trong cột B, bắt đầu bằng hàng 5. Nếu bạn cố gắng thêm một tham chiếu như vậy, công thức của bạn rất có thể sẽ trả về lỗi #NAME.

Thay vào đó, bạn có thể chỉ định một hàng tối đa để liên kết của bạn bao gồm tất cả các hàng có thể có trong một cột nhất định. Trong Excel 2016, 2013, 2010 và 2007, tối đa là 1.048.576 hàng và 16.384 cột. Các phiên bản Excel trước có tối đa 65.536 hàng và một cột có tối đa 256.+

Leave a Reply

Your email address will not be published. Required fields are marked *