Tạo tham chiếu ngoại trong Excel để dẫn tới trang tính/bảng tính khác

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/bảng tính khác

Tin học văn phòng

Word

Excel

Power Point

Bắt đầu với Word

Văn bản

Trang & Bố trí văn bản

Ảnh trong văn bản

Form, Nhãn và Mẫu

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

Thủ thuật

Để tạo tham chiếu ngoại trong Excel gọn hơn, bạn có thể tạo tên trong nguồn trang tính sau đó tham chiếu tới tên đó từ trang tính khác nằm trong cùng hoặc khác bảng tính.

Ô tham chiếu ngoại trong Excel là sự tham chiếu dẫn tới một ô hoặc một mảng các ô (range) không nằm trong trang tính hiện tại. Lợi ích của việc dùng tham chiếu ngoại trong Excel là bất cứ khi nào ô tham chiếu thay đổi, giá trị trả về bởi ô tham chiếu ngoại sẽ tự động cập nhật.

Giữa tham chiếu ngoại trong Excel và ô tham chiếu vẫn có những điểm khác biệt mặc dù chúng tương tự nhau. Trong bài hướng dẫn này, chúng ta cùng bắt đầu với những bước cơ bản và tạo một vài loại tham chiếu ngoại qua các ví dụ cụ thể

1. Tham chiếu ngoại trong Excel – Tạo tên trong Excel

Để tạo tên trong Excel, chọn tất cả các ô bạn muốn tham chiếu. Sau đó tới tab Formulas>Defined names và click vào nút Define name hoặc ấn Ctrl+F3 và click New

Trong hộp thoại New Name, đánh tên bạn muốn (nhớ rằng kí tự trống không được sử dụng cho tên Excel), và kiểm tra mảng tham chiếu được ghi trong ô Refers to.

Ví dụ đây là cách tạo tên (Doanh_số_T1) cho ô B2:B5 trong trang tính Tháng 1

Tham chiếu ngoại trong Excel - Tạo tên trong Excel

Tham chiếu ngoại trong Excel – Tạo tên trong Excel

  • Lưu ý: Hình ảnh trên sử dụng Excel 2016 nên giao diện Define Name sẽ khác so với Excel 2013 trở về trước với Refers to tương đương với Select the range of cells, Name tương đương Enter a name for the data range.

Sau khi tên được tạo lập, bạn có thể sử dụng tham chiếu ngoại này trong Excel. Định dạng của tham chiếu này đơn gian hơn nhiều so với tham chiếu trang tính và bảng tính vừa đề cập trên đây.

  • Lưu ý: Tên được tạo mặc định với level bảng tính (ghi trong mục Scope tương đương Name in workbook). Bạn cũng có thể lập tên ở level trang tính bằng cách chọn trang tính tương ứng từ danh sách trong Scope (Names in workbook). Đối với tham chiếu Excel, giới hạn tên vô cùng quan trọng bởi nó xác định giới hạn mà tên được sử dụng khi tham chiếu.

Các bạn nên tạo tên ở level bảng tính (trừ khi bạn có lý do để không sử dụng nó) bởi chúng đơn giản hoá việc tạo lập tham chiếu ngoại hơn nhiều so với tên trang tính.

2. Tham chiếu tên trong trang tính khác nằm cùng một bảng tính

Để tham chiếu tên level bảng tính trong cùng một bảng tính, bạn chỉ cần đánh tên vào tham số của hàm

=Function (tên)

Ví dụ để tìm tổng của các ô nằm trong tên Doanh_số_T1 mà bạn vừa lập xong, sử dụng công thức sau =SUM(Doanh_số_T1)

Để tham chiếu tên level trang tính từ một trang tính khác nằm trong cùng bảng tính, bạn phải đặt tên trước tên trang tính và dấu chấm than đằng sau:

=Function (Tên_trang_tính!tên)

Ví dụ

=SUM (T1!Doanh_số_T1)

Nếu tên trang tính chứa dấu cách hoặc kí tự không phải chữ cái, hãy nhớ đặt chúng bên trong dấu nháy như sau

=SUM(‘Báo cáo T1’!Doanh_số_T1)

3. Tham chiếu tên trong bảng tính khác

Tham chiếu tới tên level bảng tính ở khác bảng tính bao gồm tên bảng tính (gồm tệp mở rộng), dấu chấm than và tên (tên của mảng)

=Function (Tên_bảng_tính!tên)

Ví dụ

=SUM (Tổng.xlsx!Doanh_số_T1)

Để tham chiếu tên level trang tính trong bảng tính khác, tên trang tính và dấu chấm than theo sau cũng phải nằm trong công thức, và tên bảng tính phải đặt trong ngoặc vuông. Ví dụ:

=SUM (‘C\Documents\Tổng.xlsx’!Doanh_số_T1)

4. Cách tạo tham chiếu tên

Nếu bạn đã tạo nhiều tên khác nhau trong trang tính Excel, bạn không cần thiết phải nhớ tất cả tên của chúng. Để chèn tham chiếu ngoại trong Excel tên vào công thức, thực hiện theo các bước sau

  • Chọn vị trí ô, nhâp dấu (=) và đánh công thức hoặc phép toán
  • Khi muốn chèn tham chiếu tên, bạn làm như sau

Nếu tham chiếu tên ở level bảng tính nằm trong bảng tính khác, chuyển tới bảng tính đó. Nếu tên nằm trong trang tính mà cùng bảng tính, bỏ qua bước này

Nếu bạn đang lập tham chiếu ngoại trong Excel tới tên ở level trang tính, di chuyển tới trang tính đó (nằm trong cùng hoặc khác bảng tính)

  • Ấn F3 để mở hộp thoại Paste Name, chọn tên bạn muốn tham chiếu và click OK 
Tạo tham chiếu ngoại trong Excel để dẫn tới trang tính/bảng tính khác

Tạo tham chiếu ngoại trong Excel để dẫn tới trang tính/bảng tính khác

  • Nhập xong công thức hoặc phép tính và ấn Enter

Như vậy bạn đã biết cách tạo tham chiếu ngoại trong Excel, bạn có thể sử dụng chúng để lấy dữ liệu cho các phép tính dễ dàng hơn.

Chúc bạn thực hiện thành công.

Tham khảo thêm 2 khóa học hữu ích do giảng viên quốc tế trường FPT Arena giảng dạy: