Tạo tham chiếu ngoại trong Excel để dẫn tới trang tính

CHÀO MỪNG BẠN ĐẾN VỚI THƯ VIỆN HỌC TẬP & ĐÀO TẠO CƠ BẢN

Tạo tham chiếu ngoại trong Excel để dẫn tới trang tính

Excel

Bắt đầu sử dụng Excel

Công thức và hàm

Nhập và phân tích dữ liệu

Định dạng dữ liệu

Xử lý sự cố

Excel 2016

Khi tính toán dữ liệu trong Excel, đôi khi bạn sẽ gặp phải trường hợp cần lấy dữ liệu từ một trang tính hoặc thậm chí một file Excel khác. Để làm được điều này, bạn chỉ cần tạo liên kết giữa hai trang tính (cùng một bảng tính hoặc khác bảng tính) bằng cách sử dụng ô tham chiếu ngoại trong Excel hoặc đường link.

Bài viết này, chúng tôi sẽ giúp bạn giải thích tham chiếu ngoại trong Excel một cách cơ bản và hướng dẫn cách lập tham chiếu cho một trang tính hoặc bảng tính trong công thức của bạn.

1. Tạo tham chiếu ngoại trong Excel từ một trang tính khác 

Để tham chiếu một ô trong Excel hoặc một mảng các ô từ một trang tính khác cùng nằm trong một bảng tính, đưa tên trang tính đằng sau dấu (!) nằm trước đại chỉ ô.

1.1. Công thức có dạng như sau

  • Tham chiếu tới một ô riêng lẻ

Tên_trang_tính!Địa_chỉ_ô

Ví dụ tham chiếu tới ô A1 nằm trong Sheet2, đánh Sheet2!A1.

  • Tham chiếu tới một mảng

Tên_trang_tính!Ô_đầu:Ô_cuối

Ví dụ tham chiếu tới các ô A1:A10 trong Sheet2, đánh Sheet2!A1:A10

  • Lưu ý: Nếu tên trang tính chứa dấu cách hoặc kí tự không phải chữ cái, bạn phải đặt tên trang tính trong dấu nháy. Ví dụ một tham chiếu ngoại tới ô A1 trong trang tính có tên là Thống kê, công thức như sau: ‘Thống kê’!A1.

Trong thực tế khi nhân giá trị ô A1 trong trang tính Thống kê với 10, công thức tham chiếu ngoại như sau

=’Thống kê’!A1*10

1.2. Tạo tham chiếu tới một trang tính khác trong Excel

Khi viết công thức tham chiếu tới ô nằm trong trang tính khác, bạn có thể đánh tên trang tính khác đằng sau dấu (!) và tham chiếu một ô một cách thủ công và dễ mắc lỗi. 

Cách tốt hơn là chỉ tới ô nằm trong trang tính khác mà bạn muốn công thức tham chiếu ngoại trong Excel tự sửa cú pháp. Để Excel chèn tham chiếu tới một trang tính khác, làm theo các bước sau.

  1. Đánh công thức vào vị trí ô hoặc vào thanh công thức.
  2. Khi muốn chèn tham chiếu tới trang tính khác, chuyển tới trang tính và chọn một ô hoặc một mảng ô bạn muốn tham chiếu tới.
  3. Kết thúc đánh công thức và ấn Enter để hoàn tất.

Ví dụ, nếu bạn có một danh sách Doanh số bán hàng và bạn muốn tính thuế giá trị gia tăng (19%) cho mỗi sản phẩm trong một trang tính khác tên VAT, thực hiện theo các bước sau.

  • Đánh công thức =19%* vào ô B2 nằm trong trang VAT 
  • Sang trang Doanh số, và click vào ô B2. Excel sẽ tự động chèn tham chiếu ngoại tới ô đó như hình dưới đây.
Tạo tham chiếu tới một trang tính khác trong Excel

Tạo tham chiếu tới một trang tính khác trong Excel

  • Ấn Enter để hoàn thành công thức

Tương tự như trên, bạn có thể tham chiếu một mảng các ô trong trang tính khác. Điểm khác biệt duy nhất là chọn nhiều ô từ trang tính. Ví dụ, để tìm tổng doanh số bán hàng trong các ô B2:B5 trong trang tính Doanh số , bạn nhập công thức sau:

=SUM (‘Doanh số’!B2:B5)

2. Tham chiếu ngoại trong Excel từ bảng tính khác

Trong công thức Microsoft Excel, tham chiếu ngoại từ bảng tính khách được trình bày theo hai cách dựa trên vị trí nguồn của bảng tính mở hay đóng.

2.1. Tham chiếu ngoại từ bảng tính mở

Khi nguồn bảng tính mở, tham chiếu ngoại chứa tên bảng tính đặt trong dấu ngoặc vuông (bao gồm phần mở rộng tệp) đứng trước tên trang tính, dấu (!), và ô tham chiếu hoặc mảng tham chiếu. Cú pháp như sau:

[Tên_bảng_tính]Tên_trang_tính!Địa_chỉ_ô_tham_chiếu

Ví dụ tham chiếu ngoại tới B2:B5 trong trang tính Tháng 1 nằm trong bảng tính Doanh số.xlsx:

[Tổng.xlsx]’T1’B2:B5

Nếu muốn tính tổng các ô, công thức tham chiếu bảng tính như sau:

=SUM ([Tổng. xlsx]T1!B2:B5)

2.2. Tham chiếu ngoại từ bảng tính đóng

Khi tham chiếu từ một bảng tính khác, bảng tính này không nhất thiết phải mở. Nếu nguồn của bảng tính là đóng, bạn phải chèn đường dẫn tới tham chiếu ngoại.

Ví dụ, để chèn ô B2:B5 vào trang tính Tháng 1 từ bảng tính Doanh số. xlsx nằm trong folder Báo cáo trong ổ D, công thức như sau:

=SUM (D:\BC\[Tổng. xlsx]’T1′!B2:B5)

với

  • Đường dẫn file: chỉ ra ổ và đường dẫn tới nơi lưu trữ file Excel (D:\BC\ như trong ví dụ này).
  • Tên bảng tính: gồm tệp mở rông (.xlsx, .xls, hoặc xslm) và luôn được đặt trong ngoặc vuông như [Tổng.xlsx] trong công thức trên.
  • Tên trang tính: nơi chứa ô tham chiếu. Phần này bao gồm tên trang tính và dấu chấm than theo sau (như trong ví dụ này là ‘T1’!).
  • Ô tham chiếu: chỉ ô hoặc mảng ô tham chiếu trong công thức.

Nếu bạn đã tạo tham chiếu tới một bảng tính mở và sau đó đóng nguồn của bảng tính, tham chiếu ngoại trong Excel sẽ tự động cập nhật bao gồm cả đường dẫn đầy đủ.

  • Lưu ý: Nếu tên trang tính hoặc tên bảng tính hoặc cả hai chứa kí tự trống hoặc kí tự không phải chữ cái, bạn phải đặt tên hoặc đường dẫn trong dấu nháy. Ví dụ:

=SUM(‘[Doanh số.xlsx]T1’!B2:B5)

=SUM (‘[Tổng.xlsx] Tháng 1’!B2:B5)

=SUM (‘D:Báo cáo\[Doanh số.xlsx] Tháng 1’!B2:B5)

2.3. Tạo tham chiếu tới một bảng tính khác trong excel

Giống như trường hợp tạo công thức Excel tham chiếu tới trang tính khác, bạn không cần phải đánh tham chiếu tới bảng tính một cách thủ công. Chỉ cần chuyển sang bảng tính khác khi đang nhập công thức và chọn ô hoặc mảng bạn muốn tham chiếu tới. Microsoft Excel sẽ làm phần việc còn lại.

Tạo tham chiếu tới một bảng tính khác trong excel

Tạo tham chiếu tới một bảng tính khác trong excel

  • Lưu ý: Khi tạo tham chiếu ngoại trong Excel tới trang tính khác bằng cách chọn ô, Excel luôn chèn tham chiếu tuyệt đối. Do đó, nếu bạn định copy công thức mới lập sang các ô khác, hãy chắc rằng kí hiệu dollar ($) được bỏ khỏi ô tham chiếu để đưa tham chiếu về dạng tương đối hoặc hỗn hợp dựa vào mục đích của bạn.