Địa Chỉ Tương Đối Và Địa Chỉ Tuyệt Đối Trong Excel

Có thể bạn quan tâm: Xóa Những Ô Có Giá Trị Bằng 0 Trong Excel

Giới thiệu chung

Excel là một công cụ mạnh mẽ để xử lý dữ liệu, tính toán và trực quan hoá thông tin. Một trong những tính năngorlugu quan trọng nhất của Excel là cách mà công thức tham chiếu tới các ô trong bảng tính – địa chỉ tương đối và địa chỉ tuyệt đối. Khi hiểu rõ cách hoạt động của hai loại địa chỉ này, bạn sẽ có thể viết công thức chính xác, tự động tính toán và tránh những lỗi phổ biến khi sao chép, di chuyển hoặc mở rộng vùng dữ liệu.

Trong bài viết này, chúng ta sẽ đi sâu vào khái niệm của địa chỉ tương đối và địa chỉ tuyệt đối, cách áp dụng chúng trong thực tế, những mẹo nhỏ để tiết kiệm thời gian làm việc, cũng như một số trường hợp đặc biệt khi làm việc với nhiều trang tính, bảng dữ liệu lớn, hoặc khi cần tham chiếu động tới các vùng dữ liệu khác.

Có thể bạn quan tâm: The Power Of Language: How One Woman’s Excellent Command Of English Shaped A Community

1. Địa chỉ ô trong Excel: Cách mà Excel hiểu

Trước khi nói về tương đối hay tuyệt đối, cần hiểu cách Excel biểu diễn một ô. Mỗi ô trong Excel được xác định bởi cột và hàng:

• Cột được biểu diễn bởi chữ cái (A, B, C, …, Z, AA, AB, …).
• Hàng được biểu diễn bởi số (1, 2, 3, …).

Ví dụ, ô A1 nằm ở cột A, hàng convoy 1. Khi ta viết công thức =A1, Excel hiểu là tham chiếu tới ô A1.

Nhưng khi ta sao chép công thức từ ô này sang ô khác, cách mà tham chiếu thay đổi phụ thuộc vào loại địa chỉ mà ta đã chỉ định. Đây chính là điểm khác biệt giữa địa chỉ tương đối và địa chỉ tuyệt đối.

Có thể bạn quan tâm: Tính Phương Sai Của Danh Mục Đầu Tư Bằng Excel

2. Địa chỉ tương đối (Relative Reference)

2.1. Định nghĩa

Địa chỉ tương đối là địa chỉ mà Excel tự động điều chỉnh khi công thức được sao chép hoặc di chuyển. Khi bạn viết =A1 trong một ô, và sau đó sao chép công thức đó sang ô bên phải hoặc bên dưới, Excel sẽ tự động thay đổi tham chiếu theo vị trí mới.

2.2. Cách hoạt động

Giả sử bạn có một bảng tính với công thức =A1 trong ô B1. Khi bạn sao chép công thức này xuống ô B2, địa chỉ tương đối A1 sẽ chuyển thành A2. Nếu bạn kéo công thức sang ô C1, nó sẽ trở thành B1. Jerseys:

• Sao chép sang hàng dưới: Hàng thay đổi (đi +1, +2, …).
• Sao chép sang cột bên phải: Cột thay đổi (đi +1, +2, …).
• Sao chép sang giữa hàng và cột: Cả cột lẫn hàng thay đổi đều.

Điều này rất hữu ích khi bạn cần tính giá trị tương ứng theo một quy luật nhất định, ví dụ tính tổng, trung bình, hoặc thực hiện phép nhân chia với các giá trị nằm trong cùng một cột hoặc hàng.

2.3. Ví dụ thực tiễn

Ví dụ 1: Tính giá trị bán hàng

Giả sử ta có bảng dữ liệu bán hàng như sau:

Bạn muốn tính doanh thu cho từng dòng, tức là Giá Số lượng. Bạn nhập công thức =B2C2 vào ô D2, rồi kéo công thức xuống các hàng tiếp theo. Nhờ địa chỉ tương đối, công thức tự động cập nhật thành =B3C3, =B4C4, … mà không cần phải chỉnh sửa thủ công.

Ví dụ 2: Tính phần trăm thay đổi

Bạn có dữ liệu giá cổ phiếu ngày hôm qua và ngày hôm nay, muốn tính phần trăm thay đổi. Công thức =(B2-A2)/A2 trong ô C2 sẽ tự động thay đổi khi kéo xuống, giúp bạn nhanh chóng tính toán cho nhiều cổ phiếu một lúc.

3. Địa chỉ tuyệt đối (Absolute Reference)

3.1. Định nghĩa

Địa chỉ tuyệt đối là địa chỉ mà Excel giữ nguyên, bất kể công thức được sao chép hay di chuyển. Khi bạn viết $A$1, $A1, hoặc A$1, Excel không thay đổi phần “$” đó khi sao chép công thức.

3.2. Cách áp dụng

• $ sweeter: Cột cố định.
• A$1: Hàng cố định.
• $A$1: Cột và hàng cố định.

Ví dụ, nếu bạn có một số lãi suất cố định trong ô A1, và muốn tính lãi suất cho nhiều dòng dữ liệu, bạn sẽ sử dụng $A$1 để giữ nguyên giá trị khi sao chép công thức.

3.3. Khi nào cần dùng

1. Tham chiếu tới một ô cố định: infile, lãi suất, v.v.
2. Tính tổng cố định: =SUM($B$2:$B$10) trong nhiều ô.
3. Tham chiếu tới trang tính cố định: ='Sheet1'!$C$5.
4. Sử dụng trong các hàm phức: Khi bạn cần trích xuất dữ liệu từ một bảng tham khảo.

3.4. Ví dụ thực tiễn

Ví dụ 1: Tính lãi suất

Bạn có lãi suất cố định 5% trong ô A1. Bảng tính lãi cho nhiều khoản vay:

Khi bạn sao chép công thức =B2$A$1 xuống, $A$1 vẫn giữ nguyên tham chiếu tới ô A1, không bị thay đổi.

Ví dụ 2: Tính tổng cố định

Bạn muốn tính tổng các số trong cột B, nhưng muốn giữ tham chiếu tới ô tổng cố định khi tính toán các giá trị khác nhau. Công thức =SUM($B$2:$B$10) sẽ luôn trích xuất giá trị từ phạm vi B2:B10.

4. Cách chuyển đổi giữa địa chỉ tương đối và tuyệt đối

4.1. Sử dụng phím F4

• Khi bạn đang viết công thức trong ô, chọn tham chiếu (ví dụ A1).
• Nhấn F4: Tham chiếu sẽ chuyển thành $A$1.
• Nhấn F4 lần nữa: Tham chiếu sẽ chuyển thành $A1.
• Nhấn F4 lần nữa: Tham chiếu sẽ chuyển thành A$1.
• Nhấn F4 lần nữa: Trở lại A1.

Điều này giúp nhanh chóng chuyển đổi giữa các dạng tham chiếu mà không cần phải gõ dấu $ thủ công.

4.2. Gõ tay

Nếu bạn muốn đặt một tham chiếu cố định ngay từ đầu, Char:

• $: Đặt cố định.
• A1: Tham chiếu tương đối.
• $A$1: Tham chiếu tuyệt đối.

Bạn có thể gõ $A1, A$1, $A$1 tùy vào nhu cầu.

5. Kết hợp địa chỉ tương đối và tuyệt đối

Thực tế, nhiều công thức cần dùng cả hai loại tham chiếu cùng một lúc. Ví dụ, khi bạn làm việc với psychologically, bạn có thể muốn giữ cột cố định nhưng hàng thay đổi khi kéo công thức.

5.1. Ví dụ

Ở đây, SUM($B$2:B2) giữ nguyên phạm vi bắt đầu cố định $B$2 và mở rộng phần kết thúc B2 theo hàng khi kéo công thức xuống.

5.2. Cách viết công thức

• Cột cố định: $B2 – B có dấu $, nhưng hàng không.
• Hàng cố định: B$2 – Hàng có dấu $, nhưng cột không.
• Cả hai cố định: $B$2.

Khi bạn cần tham chiếu tới một ô cụ thể trong một bảng nội dung lớn, bạn có thể đặt cả cột và hàng cố định, ví dụ $A$1.

6. Tham chiếu tới ô trong trang tính khác

Đôi khi bạn cần tham chiếu tới một ô nằm trong một trang tính khác. Cú pháp:

• 'Tên trang tính'!C1

Nếu tên trang tính có dấu cách hoặc ký tự đặc biệt, bạn cần đặt trong dấu '. Ví dụ 'Bảng dữ liệu'!$C$5.

6.1. Địa chỉ tuyệt đối trong trang tính khác

Bạn có thể kết hợp $ trong tham chiếu tới trang tính khác, ví dụ 'Sheet2'!$C$5. Khi sao chép công thức trong trang tính hiện tại, tham chiếu tới ô C5 trong Sheet2 sẽ không thay đổi.

6.2. Ví dụ thực tế

Bạn có một bảng tổng hợp kết quả sắp xếp trên trang tính “Result”. Bạn muốn lấy giá trị từ ô C5 trong trang tính “Data”.

Công thức: ='Data'!$C$5

Khi bạn sao chép công thức này vào các ô khác trongങ്ങള്, tham chiếu tới trang tính “Data” và ô C5 luôn giữ nguyên.

Có thể bạn quan tâm: Đánh Công Thức Trong Excel Nhưng Không Ra Kết Quả

7. Tham chiếu tới vùng dữ liệu (Range)

7.1. Địa chỉ tương đối

Ví dụ: =SUM(B2:B10) – Khi bạn sao chép công thức sang hàng khác, phạm vi B2:B10 sẽ thay đổi tương ứng.

7.2. Địa chỉ tuyệt đối

Ví dụ: =SUM($B$2:$B$10) – Phạm vi luôn cố định, không thay đổi khi sao chép.

7.3. Địa chỉ mix

Bạn có thể giữ một phần cố định và phần còn lại thay đổi. Ví dụ: =SUM($B$2:B10) – Phần bắt đầu cố định, phần kết thúc thay đổi khi sao chép.

8. Hàm INDIRECT – Tham chiếu động

Trong một số trường hợp, bạn muốn tham chiếu tới một ô hoặc vùng dữ liệu mà tên của nó có thể thay đổi tuần tự (ví dụ, trang tính tên theo ngày). Hàm INDIRECT cho phép bạn tạo tham chiếu dựa trên chuỗi ký tự.

8.1. Cú pháp

INDIRECT(ref_text, a1)

• ref_text: Chuỗi ký tự biểu diễn tham chiếu.
• a1: (Tùy chọn) Boolean. Nếu TRUE hoặc bỏ qua, ref_text được hiểu theo dạng A1. Nếu FALSE, dạng R1C1.

8.2. Ví dụ

Bạn có một danh sách ngày trong cột A, và muốn lấy giá trị từ ô B1 của trang tính tương ứng với ngày đó.

Ở đây, INDIRECT("'"&A2&"'!B1") sẽ xây dựng tham chiếu tới ô B1 trên trang tính có tên giống với giá trị trong ô A2.

8.3. Lưu ý

• Hàm INDIRECT làm chậm hiệu suất khi sử dụng trong bảng tính lớn.
• Khi di chuyển hoặc đổi tên trang tính, hàm INDIRECT vẫn giữ đúng tham chiếu.

9. Tham chiếu tới Named Range

Excel cho phép bạn đặt tên cho một ô hoặc vùng dữ liệu, sau đó có thể tham chiếu tới tên đó thay vì địa chỉ ô. Ví dụ:

1. Định nghĩa Named Range: Chọn ô B2:B10 פר, vào thanh tên (trước thanh công thức), nhập Sales.
2. Sử dụng: =SUM(Sales).

Named Range luôn là một tham chiếu tuyệt đối, vì vậy khi bạn sao chép công thức, phạm vi Sales không thay đổi, ngay cả khi bạn di chuyển công thức ra các ô khác.

9.1. Khi nào nên dùng

• Khi bạn cần tham chiếu tới một vùng dữ liệu nhiều lần.
• Khi bạn làm việc với nhiều bảng tính hoặc báo cáo.
• Khi muốn tránh gõ رضا địa chỉ ô phức tạp.

10. Tham chiếu dòng/ cột cố định khi di chuyển công thức

10.1. Cột cố định

• $A1: Cột A cố định, hàng thay đổi.
• Khi kéo công thức sang cột B, tham chiếu vẫn là A2, A3, …

10.2. Hàng cố định

• A$1: Hàng 1 cố định, cột thay đổi.
• Khi kéo công thức sang hàng 2, tham chiếu vẫn là B$1, C$1, …

10.3. Ví dụ

Bạn muốn tính tổng các giá trị trên cột B và D, nhưng giữ cột cố định khi sao chép công thức sang cột E, F. Bạn viết công thức =SUM($B$1:$B$3) trong ô E1 và F1. Khi sao chép, tham chiếu vẫn giữ cố định.

11. Tham chiếu động trong PivotTables

Khi làm việc với PivotTable, bạn không phải viết công thức thủ công. Tuy nhiên, nếu bạn muốn tạo các cột tính toán tùy chỉnh, bạn có thể tham chiếu tới các ô trong PivotTable. Mặc dù PivotTable có tính năng tự động cập nhật, nhưng bạn vẫn cần chú ý đến cách tham chiếu.

• Tham chiếu tương đối: Khi bạn sao chép công thức tính tùy chỉnh, Excel sẽ điều chỉnh theo vị trí mới.
• Tham chiếu tuyệt đối: Khi bạn muốn giữ nguyên phạm vi dữ liệu nguồn, hãy dùng $.

12. Tham chiếu tới ô الجنسية trong bảng dữ liệu lớn

Khi làm việc với bảng dữ liệu lớn (nhiều hàng, nhiều cột), việc chuẩn bị công thức có thể tốn thời gian. Dưới đây là một số mẹo:

1. Sử dụng Fill Handle: Kéo bang để sao chép công thức nhanh chóng.
2. Bảo vệ tham chiếu: Dùng $ khi cần це́.
3. Sử dụng Named Range: Giảm độ phức tạp.
4. Kết hợp với Hàm INDEX: Trích xuất dữ liệu bất kỳ một cách linh hoạt hơn.

12.1. Ví dụ sử dụng INDEX

Bạn muốn logistics lấy giá trị tại hàng 5, cột 3 trong bảng dữ liệu. Thay vì viết =C5, bạn có thể viết =INDEX(A1:Z100,5,3). Khi sao chép công thức, bạn có thể giữ tham chiếu tới bảng A1:Z100 cố định bằng $, ví dụ =INDEX($A$1:$Z$100,5,3).

13. Tham chiếu tới ô trong một file khác

Nếu bạn cần tham chiếu tới một ô trong file Excel khác, cú pháp:

='TênFile.xlsxTênSheet'!CellReference

Ví dụ: ='Report.xlsxSummary'!$B$5.

13.1. Khi nào cần

• Khi bạn làm việc với nhiều file dữ liệu.
• Khi bạn muốn lấy dữ liệu tổng hợp từ các file.

13.2. Lưu ý

• Đường dẫn đầy đủ (đường dẫn đến file) cần được ghi đúng nếu file không nằm trong cùng thư mục hiện tại.
• Khi di chuyển file, tham chiếu sẽ bị lỗi.

14. Mẹo nhanh để tránh lỗi

1. Kiểm tra tham chiếu: Khi sao chép công thức, nhấn vào ô có công thức và xem thanh công thức (Formula Bar) để xác định địa chỉ tham chiếu.
2. Sử dụng tính năng “Trace Dependents”: Để xem công thức nào đang phụ thuộc vào ô đó.
3. Sử dụng “Show Formulas”: Nhấn Ctrl + để hiển thị công thức thay vì kết quả, giúp kiểm tra lỗi.
4. Sử dụng “Replace” (Ctrl + H) trong công thức để thay đổi nhanh địa chỉ (đặc biệt khi cần chuyển sang tuyệt đối).

15. Thực hành: Tạo một bảng tính ngân sách cá nhân

15.1. Cấu trúc bảng

• =$A$4: Lãi suất cố định 5%, được đặt trong ô A4. Tham chiếu tuyệt đối $ giữ nguyên khi sao chép công thức Còn lại.
• =C2$A$4: Tham chiếu tuyệt đối $A$4 giữ lại lãi suất khi sao chép công thức.

15.2. Kiểm tra

Bạn kéo công thức trong cột D và E xuống các hàng tiếp theo. Nhờ địa chỉ tuyệt đối, tham chiếu tới ô A4 giữ nguyên, trong khi các tham chiếu tới B, C, D thay đổi tương ứng.

16. Tham chiếu tới ô trong bảng dữ liệu Excel: Tóm tắt

17. Kết luận

Địa chỉ tương đối và địa chỉ tuyệt đối là những công cụ cơ bản nhưng cực kỳ quan trọng trong Excel. Khi bạn hiểu rõ cách chúng hoạt động, mission có thể:

• Tiết kiệm thời gian: Viết công thức một lần và sao chép tự động.
• Giảm lỗi: Đảm bảo tham chiếu chính xác khi dữ liệu thay đổi.
• Tăng tính linh hoạt: Dễ dàng mở rộng bảng tính, thêm hàng/ cột mà không cần chỉnh sửa công thức thủ công.
• Tạo ra các báo cáo chuyên nghiệp: Kết hợp các công thức, named ranges, và hàm INDIRECT để xây dựng mô hình dữ liệu mạnh mẽ.

Hãy áp dụng những kiến thức trên vào công việc hàng ngày của bạn và trải nghiệm sự thay đổi tích cực trong hiệu suất làm việc với Excel. Happy Exceling!

Tiến sĩ Lê Thống Nhất

Tiến sĩ Lê Thống Nhất sinh năm 1955 tại Nghệ An, là nhà giáo, nhà báo và doanh nhân trong lĩnh vực giáo dục. Ông từng giảng dạy Toán học tại Trường THPT Chuyên Phan Bội Châu và đảm nhiệm nhiều vai trò trong ngành giáo dục, từ viết sách, biên soạn tài liệu đến sáng lập các dự án hỗ trợ học tập trực tuyến.

Ông là người sáng lập BigSchool – nền tảng giáo dục trực tuyến kết nối giáo viên và học sinh cả nước. Ngoài ra, ông còn nổi tiếng với vai trò tác giả, diễn giả truyền cảm hứng và khách mời trên nhiều chương trình truyền hình, trong đó có “Ai là triệu phú” trên truyền hình VTV.

Trong suốt sự nghiệp, Tiến sĩ Lê Thống Nhất đã nhận nhiều giải thưởng giáo dục và được biết đến như một người luôn đổi mới, ứng dụng công nghệ vào dạy và học, góp phần lan tỏa tri thức và niềm say mê học tập cho thế hệ trẻ.