Thời gian đọc ước tính: 12 phút
Những điểm chính
- Cấu trúc tham chiếu cơ bản: Hiểu rõ cú pháp
TênSheet!Địa_chỉ_ôđể lấy dữ liệu từ trang tính khác. - Xử lý tên Sheet đặc biệt: Quy tắc bắt buộc sử dụng dấu nháy đơn
' 'khi tên trang tính chứa khoảng trắng hoặc ký tự đặc biệt. - Liên kết External Workbook: Phân biệt sự khác nhau trong cú pháp khi file Excel nguồn đang mở (chỉ tên file) và khi file đóng (đường dẫn đầy đủ).
- Tự động hóa quy trình: Kỹ thuật dùng chuột chọn vùng dữ liệu để Excel tự động viết công thức chính xác, tránh lỗi cú pháp.
- Named Ranges (Vùng đặt tên): Sử dụng tên vùng định nghĩa sẵn để đơn giản hóa công thức và quản lý dữ liệu hiệu quả hơn giữa các file.
Khả năng tham chiếu các ô và vùng dữ liệu là yếu tố cốt lõi giúp bảng tính Excel hoạt động hiệu quả. Nếu không có tính năng này, Excel chỉ đơn thuần là một công cụ lưu trữ dữ liệu tĩnh.
Trong hướng dẫn này, chúng tôi sẽ phân tích chi tiết mọi khía cạnh kỹ thuật về cách tham chiếu ô và vùng trong Excel. Bên cạnh việc tham chiếu cơ bản trên cùng một trang tính (sheet), trọng tâm chính của bài viết sẽ tập trung vào cách tham chiếu đến một sheet khác hoặc một workbook (file Excel) hoàn toàn khác.
Mặc dù nguyên lý hoạt động cơ bản là tương tự nhau, nhưng định dạng cú pháp sẽ có những thay đổi nhỏ khi bạn chuyển từ tham chiếu nội bộ sang tham chiếu file bên ngoài. Ngoài ra, có những nguyên tắc quản lý dữ liệu quan trọng bạn cần nắm vững để tránh lỗi liên kết (broken links) khi làm việc với các file external.
Hãy cùng đi qua từng bước để làm chủ kỹ thuật này.
Tham chiếu đến một ô trong cùng trang tính (Worksheet)
Đây là cấp độ cơ bản nhất của việc tham chiếu dữ liệu. Khi bạn muốn sử dụng giá trị của một ô khác trên cùng một sheet hiện hành.
Ví dụ: Nếu bạn đang ở ô B1 và muốn lấy dữ liệu từ ô A1, công thức đơn giản là:
=A1
Tham chiếu đến một ô trong cùng một trang tính
Khi thực hiện thao tác này, giá trị tại ô B1 sẽ luôn đồng bộ với ô A1. Bất kỳ thay đổi nào tại A1 sẽ được cập nhật ngay lập tức tại nơi bạn đặt tham chiếu.
Tham chiếu đến một ô ở trang tính (Sheet) khác
Khi dữ liệu nằm phân tán trên nhiều tab khác nhau trong cùng một file Excel, bạn cần sử dụng cú pháp mở rộng để định vị chính xác nguồn dữ liệu. Định dạng tiêu chuẩn như sau:
Tên_Sheet!Địa_chỉ_ôThành phần bắt buộc bao gồm tên của trang tính, theo sau là dấu chấm than (!) để ngăn cách, và cuối cùng là địa chỉ ô hoặc vùng dữ liệu.
Ví dụ, để tham chiếu đến ô A1 nằm trong Sheet1, bạn sử dụng công thức:
=Sheet1!A1Nếu bạn muốn tham chiếu đến một vùng dữ liệu (range) từ A1 đến C10 trên Sheet1, cú pháp sẽ là:
=Sheet1!A1:C10Lưu ý quan trọng: Cấu trúc này áp dụng cho mọi hàm trong Excel. Cho dù bạn dùng hàm SUM, VLOOKUP hay INDEX, phần tham chiếu địa chỉ vẫn giữ nguyên nguyên tắc này.
Xử lý tên Sheet có khoảng trắng hoặc ký tự đặc biệt
Trong thực tế doanh nghiệp, các trang tính thường được đặt tên cụ thể như “Dữ Liệu Bán Hàng” hoặc “Báo-Cáo-2026”. Khi tên sheet chứa khoảng trắng (space) hoặc các ký tự không phải chữ cái (như -, #, @…), Excel yêu cầu bạn phải bao quanh tên sheet bằng cặp dấu nháy đơn (' ').
Ví dụ, để tham chiếu ô A1 trong sheet tên là Sales Data, công thức bắt buộc phải là:
='Sales Data'!A1Nếu tên sheet là Sales-Data, công thức sẽ là:
='Sales-Data'!A1Một ưu điểm của Excel là tính năng tự động cập nhật. Nếu bạn thay đổi tên sheet sau khi đã thiết lập công thức, Excel sẽ tự động sửa lại tất cả các tham chiếu liên quan trong workbook để khớp với tên mới mà không làm gãy công thức.
Tuy nhiên, việc gõ thủ công các địa chỉ này rất dễ dẫn đến sai sót cú pháp, đặc biệt là thiếu dấu nháy đơn hoặc dấu chấm than. Phần tiếp theo sẽ hướng dẫn bạn cách thực hiện điều này chính xác tuyệt đối mà không cần gõ phím.
Tự động tạo tham chiếu đến Sheet khác trong cùng Workbook
Phương pháp hiệu quả và chính xác nhất để tạo tham chiếu là sử dụng thao tác “trỏ và chọn” (point and click). Bạn chỉ cần chỉ cho Excel biết đâu là dữ liệu nguồn, phần mềm sẽ tự động điền cú pháp chính xác.
Quy trình thực hiện như sau:
- Chọn ô tại sheet hiện hành nơi bạn muốn hiển thị kết quả.
- Gõ dấu bằng (
=) hoặc bắt đầu công thức (ví dụ=SUM(). - Dùng chuột nhấp vào tab (thẻ) của sheet chứa dữ liệu nguồn.
- Quét chọn ô hoặc vùng dữ liệu bạn cần tham chiếu.
- Nhấn Enter.
Thao tác này giúp loại bỏ hoàn toàn rủi ro sai lỗi chính tả hoặc thiếu ký tự đặc biệt trong tên sheet.
Ví dụ thực tế: Bạn muốn tính tổng vùng A1:A10 từ sheet Sales Data và trả kết quả về sheet hiện tại.
Bước 1: Nhập công thức tại sheet hiện tại: =SUM(
Nhập công thức đến điểm cần tham chiếu
Bước 2: Chuyển sang sheet Sales Data.
Bước 3: Quét chọn vùng A1:A10. Quan sát thanh công thức, bạn sẽ thấy Excel tự động viết: =SUM('Sales Data'!A1:A10
Chọn vùng dữ liệu thủ công để Excel tự tạo tham chiếu
Bước 4: Nhấn Enter để hoàn tất.
Lưu ý rằng khi bạn dùng chuột để chọn, Excel mặc định tạo ra tham chiếu tương đối (ví dụ: A1). Điều này có nghĩa là nếu bạn copy công thức này sang ô khác, địa chỉ tham chiếu sẽ thay đổi theo.
Ví dụ: Tại ô A1 bạn có =SalesData!A1. Nếu copy công thức này xuống ô A2, nó sẽ tự động biến thành =SalesData!A2.
Để cố định vùng dữ liệu (đặc biệt quan trọng khi dùng VLOOKUP hoặc các bảng tra cứu), bạn cần chuyển sang tham chiếu tuyệt đối bằng cách thêm dấu $ (nhấn phím F4). Công thức sẽ trở thành:
=SalesData!$A$1Việc hiểu rõ tham chiếu tương đối và tuyệt đối là nền tảng để tránh các lỗi sai lệch dữ liệu nghiêm trọng khi sao chép công thức.
Cách tham chiếu đến Workbook (File Excel) khác
Làm việc với dữ liệu trải dài trên nhiều file Excel (External Reference) phức tạp hơn một chút so với tham chiếu nội bộ. Định dạng tham chiếu sẽ phụ thuộc vào việc file nguồn đang Mở hay đang Đóng.
Tham chiếu đến Workbook đang mở
Khi file Excel nguồn đang mở trên máy tính, cú pháp tham chiếu sẽ bao gồm: Tên File (trong ngoặc vuông), Tên Sheet và Địa chỉ ô.
Cấu trúc:
=[TênFile.xlsx]TênSheet!Địa_chỉ_ôVí dụ: Bạn có file ExampleFile.xlsx đang mở và muốn lấy dữ liệu từ ô A1 của Sheet1. Công thức là:
=[ExampleFile.xlsx]Sheet1!A1Nếu tên file hoặc tên sheet có chứa khoảng trắng, toàn bộ cụm [TênFile]TênSheet phải được đặt trong dấu nháy đơn.
Ví dụ với file Example File.xlsx và sheet Sales Data:
='[Example File.xlsx]Sales Data'!A1Cách tạo tham chiếu đến Workbook khác (Tự động)
Tương tự như tham chiếu nội bộ, bạn không nên gõ tay các đường dẫn này. Hãy để Excel tự làm việc đó.
- Mở cả hai file Excel (file đích và file nguồn).
- Tại file đích, gõ dấu
=hoặc công thức=SUM(. - Dùng tổ hợp phím
Alt + Tabhoặc chuột để chuyển sang cửa sổ file nguồn. - Chọn sheet và vùng dữ liệu mong muốn.
- Nhấn Enter.
Tạo tham chiếu đến workbook khác trong Excel
Điểm khác biệt quan trọng: Khi tham chiếu đến một file khác, Excel mặc định sẽ luôn tạo tham chiếu tuyệt đối (có dấu $, ví dụ $A$1:$A$10). Nếu bạn muốn copy công thức cho các dòng tiếp theo, bạn phải thủ công xóa dấu $ để chuyển về tham chiếu tương đối.
Tham chiếu đến Workbook đang đóng
Điều gì xảy ra khi bạn đóng file nguồn lại? Excel không còn truy cập trực tiếp được vào bộ nhớ đệm của file đó nữa, do đó nó cần một đường dẫn đầy đủ (Full Path) đến vị trí lưu file trên ổ cứng.
Khi file nguồn đóng, Excel sẽ tự động chuyển đổi công thức từ dạng ngắn gọn sang dạng đầy đủ như sau:
='C:UsersTen_Nguoi_DungDocuments[Example File.xlsx]Sheet1'!$A$1Cấu trúc bao gồm:
- Đường dẫn (Path): Ổ đĩa và thư mục chứa file.
- Tên File: Tên workbook kèm đuôi mở rộng (.xlsx, .xlsm…).
- Tên Sheet: Sheet chứa dữ liệu.
- Địa chỉ ô: Vùng dữ liệu cụ thể.
Bạn không cần phải tự viết đường dẫn này. Hãy tạo công thức khi cả hai file cùng mở, và khi bạn đóng file nguồn, Excel sẽ tự động thêm đường dẫn vào cho bạn.
Tác động của việc thay đổi vị trí file
Liên kết đến file bên ngoài rất dễ bị hỏng (broken link). Nếu bạn di chuyển file nguồn sang thư mục khác, đổi tên file, hoặc xóa file đó, Excel sẽ không thể tìm thấy dữ liệu.
Khi mở file đích, nếu liên kết bị hỏng, Excel sẽ hiện thông báo yêu cầu cập nhật:
Thông báo liên kết bên ngoài bị hỏng
Để sửa lỗi, bạn chọn Update… hoặc vào thẻ Data > Edit Links. Hộp thoại Edit Links sẽ xuất hiện cho phép bạn trỏ lại đường dẫn mới cho file nguồn (Change Source).
Chỉnh sửa liên kết để sửa tham chiếu bị hỏng
Sử dụng Named Range (Vùng được đặt tên) để tham chiếu
Một kỹ thuật nâng cao giúp công thức của bạn dễ đọc và dễ quản lý hơn là sử dụng Named Range (Vùng được đặt tên). Thay vì nhớ các địa chỉ ô khô khan như Sheet1!$A$1:$C$50, bạn có thể đặt tên cho vùng đó là DoanhThuQuy1.
Lợi ích là bạn chỉ cần gọi tên DoanhThuQuy1 trong công thức, bất kể bạn đang ở sheet nào hay file nào.
Cách tạo Named Range
- Quét chọn vùng dữ liệu cần đặt tên.
- Vào thẻ Formulas (Công thức).
Nhấp vào tab Formulas
- Chọn Define Name (Định nghĩa tên).
Nhấp vào tùy chọn Define Name
- Trong hộp thoại New Name, đặt tên cho vùng (Ví dụ: SalesData). Lưu ý: Tên không được chứa khoảng trắng.
Tạo vùng đặt tên mới bằng cách nhập tên
- Tại mục Scope (Phạm vi), chọn Workbook. Điều này cực kỳ quan trọng để tên này có thể được gọi từ bất kỳ đâu trong file.
Giữ phạm vi là Workbook
- Kiểm tra lại vùng tham chiếu tại mục Refers to và nhấn OK.
Xác định địa chỉ cho vùng đặt tên
Bây giờ, thay vì viết =SUM(Sheet1!A1:A10), bạn chỉ cần viết:
=SUM(SalesData)Tham chiếu Named Range từ Workbook khác
Bạn hoàn toàn có thể sử dụng Named Range của một file Excel khác.
Nếu Named Range có phạm vi là Workbook (Workbook Level), cú pháp rất gọn gàng:
=SUM('Example File.xlsx'!SalesData)Excel sẽ hiểu SalesData là một vùng dữ liệu đã được định nghĩa trong file Example File.xlsx mà không cần bạn phải nhớ nó nằm ở sheet nào hay dòng bao nhiêu. Đây là cách quản lý dữ liệu chuyên nghiệp, giúp giảm thiểu lỗi khi cấu trúc file nguồn thay đổi (ví dụ chèn thêm dòng/cột vào vùng dữ liệu nguồn, Named Range sẽ tự co giãn theo).
Nếu file nguồn đang đóng, cú pháp sẽ tự động thêm đường dẫn đầy đủ:
=SUM('C:DesktopExample File.xlsx'!SalesData)Mẹo sử dụng phím tắt F3
Khi làm việc với nhiều Named Range, bạn không thể nhớ hết tên của chúng. Excel cung cấp phím tắt F3 để hỗ trợ việc này.
- Đặt con trỏ tại vị trí đang nhập công thức.
- Nhấn phím F3 trên bàn phím.
- Hộp thoại Paste Name sẽ hiện ra danh sách tất cả các tên đã tạo.
Hộp thoại Paste Name
- Nhấp đúp vào tên bạn cần dùng.
Lưu ý: Phím F3 chỉ liệt kê các tên có sẵn trong Workbook hiện tại. Để dùng tên từ file khác, bạn vẫn cần mở file đó và thực hiện thao tác chọn thủ công lần đầu.
FAQ: Câu hỏi thường gặp
1. Làm thế nào để sửa lỗi #REF! khi tham chiếu sheet khác?
Lỗi #REF! xuất hiện khi tham chiếu không còn hợp lệ. Nguyên nhân thường gặp nhất là bạn đã xóa sheet hoặc xóa các ô chứa dữ liệu nguồn. Để khắc phục, bạn cần kiểm tra lại công thức và trỏ lại vào vùng dữ liệu mới còn tồn tại.
2. Tại sao công thức của tôi hiển thị đường dẫn dài khi đóng file nguồn?
Đây là tính năng mặc định của Excel. Khi file nguồn đóng, Excel cần đường dẫn tuyệt đối (Full path) để định vị dữ liệu. Bạn không nên xóa đường dẫn này vì nó sẽ làm hỏng liên kết. Khi bạn mở lại file nguồn, đường dẫn sẽ tự động thu gọn lại.
3. Tôi có thể tham chiếu dữ liệu từ Google Sheets sang Excel không?
Trực tiếp bằng công thức thông thường thì không. Bạn cần sử dụng tính năng Get Data (Power Query) hoặc các add-in của bên thứ ba để kết nối dữ liệu giữa hai nền tảng đám mây và máy tính này.
4. Dấu $ trong công thức tham chiếu có ý nghĩa gì?
Dấu $ biểu thị tham chiếu tuyệt đối. Ví dụ $A$1 nghĩa là cố định cột A và dòng 1. Khi bạn copy công thức sang ô khác, vị trí tham chiếu này không thay đổi. Khi tham chiếu đến file khác, Excel mặc định thêm dấu $ để đảm bảo tính chính xác của dữ liệu.
