Dấu đô la trong Excel: Cách khóa tham chiếu ô

Dấu đô la trong công thức Excel (Tham chiếu ô)

Trong phần này, tôi sẽ giải thích cách dấu đô la khóa tham chiếu ô trong công thức và vì sao điều này quan trọng với công việc của bạn. Excel dùng ba loại tham chiếu: Tương đối, Tuyệt đối và Hỗn hợp; tôi sẽ đề cập lần lượt từng loại.

Tham chiếu tương đối (Không có $)

Mặc định, mọi tham chiếu trong Excel đều là tương đối. Nghĩa là khi bạn viết một công thức như = A1 + B1 ở ô C1 và sao chép nó xuống C2, Excel tự động điều chỉnh tham chiếu và công thức trở thành = A2 + B2. Mỗi tham chiếu ô dịch chuyển tương đối với vị trí mới của nó.

Chúng hữu ích vì hầu hết công thức đều tuân theo một mẫu dọc theo hàng hoặc ngang theo cột. Bạn không muốn gõ đi gõ lại cùng một công thức khi có thể sao chép một lần và Excel thông minh điều chỉnh từng tham chiếu.

Tham chiếu tuyệt đối ($A$1)

Với loại tham chiếu này, bạn khóa cả cột và hàng. Nó khác với tham chiếu tương đối. Khi bạn viết =$A$1, Excel giữ cố định tham chiếu ô đó, và nếu bạn sao chép công thức này ở bất kỳ đâu trong trang tính (C10, B20) hay sang trang khác, nó vẫn luôn tham chiếu tới A1. 

Dùng tham chiếu tuyệt đối giải quyết một vấn đề cụ thể, đó là: Giả sử bạn cần tính giảm 40% cho giá từng mặt hàng trong một bộ dữ liệu bán hàng, tỷ lệ giảm nằm ở ô D2, và bạn làm việc này cho khoảng 80 bản ghi. Bạn không muốn gõ cùng một công thức 80 lần, mà chỉ sao chép một công thức xuống.

Vấn đề khi thử làm vậy là nếu bạn viết = E3*D2 ở hàng đầu và sao chép xuống, sẽ xảy ra:

• Hàng 1: =E3*D2 (đúng — tính E3 nhân D2)

• Hàng 2: =E4*D3 (sai — D3 trống!)

• Hàng 3: =E5*D4 (sai — D4 trống!)

• Hàng 4: =E6*D5 (sai — D5 trống!)

Excel dịch chuyển cả hai tham chiếu xuống, trong khi bạn chỉ cần tham chiếu cột E dịch chuyển và D giữ nguyên.

Giải pháp là dùng =E3*$D$2.

Giờ đây, khi bạn sao chép xuống, sẽ xảy ra:

• Hàng 1: =E3*$D$2 (E3 nhân tỷ lệ giảm giá)

• Hàng 2: =E4*$D$2 (E4 nhân cùng tỷ lệ giảm giá)

• Hàng 3: =E5*$D$2 (E5 nhân cùng tỷ lệ giảm giá)

• Hàng 4: =E6*$D$2 (E6 nhân cùng tỷ lệ giảm giá)

Bạn thấy khác biệt chứ? E dịch chuyển (E3 thành E4 rồi E5) trong khi $D$2 vẫn khóa. Nó không bao giờ trở thành $D$3 hay $D$4. Kỹ thuật này rất quan trọng trong mô hình tài chính; để hiểu thêm về mô hình tài chính, bạn có thể học khóa học Financial Modeling in Excel. 

Tham chiếu hỗn hợp ($A1, A$1)

Tham chiếu hỗn hợp được dùng để khóa hoặc cột hoặc hàng, nhưng không phải cả hai. Khi hiểu cách áp dụng, đây là công cụ mạnh mẽ. Có hai cách dùng tham chiếu hỗn hợp; $A1 và A$1.

• =$A1 khóa cột (A) nhưng cho phép hàng thay đổi. Khi bạn sao chép công thức này sang phải sang cột B, nó vẫn giữ A, sao chép xuống hàng 2, nó trở thành $A2.

• =A$1 khóa hàng (1) nhưng cho phép cột thay đổi. Khi bạn sao chép công thức này sang phải sang cột B, nó thành B$1, sao chép xuống, và vẫn giữ hàng 1.

Tôi đã trình bày mỗi loại tham chiếu làm gì. Giờ cho phép tôi đưa vào một bảng tham chiếu mà tôi thấy hữu ích:

Tóm tắt quy tắc như sau:

Cách thêm dấu đô la trong Excel bằng phím F4

Để tăng năng suất, bạn có thể dùng phím F4 để luân phiên các kiểu tham chiếu khác nhau, giúp bạn đỡ phải gõ dấu đô la thủ công.

Để dùng kỹ thuật này, bấm vào một ô và bắt đầu viết công thức, ví dụ =A1+B1

Dừng gõ và bấm ngay trước hoặc bên trong phần A1 (phần bạn muốn thay đổi).

Nhấn F4

Excel sẽ luân phiên qua các kiểu tham chiếu khác nhau:

• Nhấn lần 1: A1 thành $A$1 (khóa cả hai)

• Nhấn lần 2: $A$1 thành A$1 (khóa hàng, cột tự do)

• Nhấn lần 3: A$1 thành $A1 (khóa cột, hàng tự do)

• Nhấn lần 4: $A1 thành A1 (về ban đầu)

Khi đạt đến định dạng tham chiếu phù hợp cho tình huống của bạn, hãy dừng nhấn.

Lưu ý phím tắt này hoạt động trên cả Windows và Mac; tuy nhiên, nếu F4 không hoạt động trên Mac của bạn, hãy kiểm tra xem có bật Function Lock không hoặc thử Fn+F4. Nếu F4 hoàn toàn không hoạt động, chỉ cần tự gõ dấu đô la.

Để hiểu thêm về các phím tắt trong Excel, Bảng gian lận phím tắt Excel là điểm khởi đầu tốt.

Các lỗi thường gặp với dấu đô la trong Excel

Có một số lỗi bạn dễ mắc phải khi lần đầu sử dụng dấu đô la trong Excel, bao gồm:

• Khóa quá nhiều: Bạn có thể thường khóa mọi thứ. Bạn viết kiểu =$A$1+$B$1+$C$1 trong khi =A1+B1+C1 là đủ. Dù không làm hỏng gì, nhưng khiến mọi thứ phức tạp không cần thiết. Chỉ khóa những gì cần khóa. Tôi khuyến nghị bắt đầu với tham chiếu tương đối và chỉ thêm khóa khi công thức cho kết quả sai.

• Quên khóa khi cần thiết: Lỗi ngược lại là sao chép một công thức tham chiếu tới hằng số mà không khóa tham chiếu. Tính giảm giá của bạn tham chiếu tỷ lệ giảm ở D2. Bạn sao chép công thức xuống, mong D2 giữ nguyên, nhưng bạn dùng =amount*D2 thay vì =amount*$D$2. Hàng 2 dịch sang hàng 3, hàng 4, v.v. Tính toán của bạn hỏng.

• Sao chép công thức mà không kiểm tra tham chiếu: Luôn xác minh tham chiếu trong công thức điều chỉnh đúng sau khi sao chép. Dành 30 giây để bấm ô đích và xem trên thanh công thức. Các tham chiếu đã thay đổi như kỳ vọng chưa? Nếu chưa, điều chỉnh bằng F4 hoặc sửa thủ công.

Dấu đô la trong Excel cho định dạng tiền tệ

Nếu bạn vào đây vì muốn hiển thị dấu đô la trong các ô thay vì dùng trong công thức, đây là điều bạn cần biết.

Khi bạn áp dụng định dạng tiền tệ cho một ô, Excel sẽ thêm ký hiệu $ vào phần hiển thị. Có 3 cách làm:

• Nhấp chuột phải vào ô → Format Cells → chọn Currency.
• Dùng nút $ trên thanh công cụ (tab Home, phía trên màn hình).
• Nhấn Ctrl+1 trên Windows hoặc Cmd+1 trên Mac, rồi chọn Currency.

Excel cũng cung cấp hai kiểu định dạng tiền tệ:

• Định dạng Currency: Với kiểu này, ký hiệu $ nằm sát chữ số đầu tiên của ô được định dạng.
• Định dạng Accounting: Ký hiệu $ căn về mép trái của ô bạn định dạng và số ở phía bên phải, giúp dễ đọc các cột dữ liệu vì phần thập phân thẳng hàng hoàn hảo.

Một điều bạn nên lưu ý là bất kỳ định dạng nào áp dụng cũng không ảnh hưởng tới phép tính theo bất kỳ cách nào. Ví dụ, nếu ở ô A1, bạn có $1,500.00 và ở ô A2, bạn có 2, khi thực hiện phép toán, ô A1 được nhận diện là “1500” bất kể định dạng áp dụng. Bản địa hóa quan trọng với ký hiệu, nhưng logic nền tảng vẫn như nhau.

Kết luận

Hy vọng bài viết này đã giúp bạn hiểu cách dấu đô la được dùng để khóa tham chiếu ô trong công thức, kiểm soát cách tham chiếu điều chỉnh khi công thức được sao chép sang ô khác.

Một điều quan trọng khác bạn nên rút ra là hiểu nên dùng loại tham chiếu nào trong ba loại nêu trong bài (Tương đối (A1), Tuyệt đối ($A$1) và Hỗn hợp ($A1 hoặc A$1)).

Với tất cả những điều đó, cách tốt nhất để hiểu các khái niệm như thế này là thực hành. Hãy tạo một bảng tính nhỏ với dữ liệu, sao chép công thức xuống trong khi quan sát tham chiếu dịch chuyển, nhấn F4 và luân phiên các kiểu tham chiếu. Làm vậy sẽ giúp bạn củng cố hiểu biết về khái niệm tốt hơn so với chỉ giải thích lý thuyết.