Bài viết này, Học Excel Online 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.
Bạn đang xem: Dấu chấm than trong excel
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 hoặc đường link.
Ô 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 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ể
Tạo tham chiếu từ một trang tính khác
Để tham chiếu một ô 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ỉ ô.
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ảngTê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
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 tới và để 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
Đánh công thức vào vị trí ô hoặc vào thanh công thứcKhi 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ớiKết thúc đánh công thức và ấn Enter để hoàn tấtVí 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Ấn Enter để hoàn thành công thứcLưu ý Khi chèn tham chiếu Excel tới một trang tính khác sử dụng phương thức trên, mặc định của Excel Microsoft là thêm tham chiếu tương đối (không có kí tự $). Vậy trong ví dụ trên, bạn có thể copy công thức cho các ô khác trong cột B nằm trong trang tính VAT, ô tham chiếu có thể điều chỉnh cho mỗi hàng, và bạn sẽ có VAT cho mỗi mẫu được tính toán chính xá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)
Tham chiếu 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
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:
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:
Nếu muốn tính tổng các ô, công thức tham chiếu bảng tính như sau:
=SUM (
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\
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ư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 sẽ tự động cập nhật bao gồm cả đường dẫn đầy đủ.
Xem thêm: File Esd Là Gì - Apa Itu File Esd
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(‘
=SUM (‘
=SUM (‘D:Báo cáo\
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
Lưu ý: Khi tạo tham chiếu 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.Tham chiếu tới tên trong cùng hoặc khác bảng tính
Để tạo tham chiếu ngoại 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.
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
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.
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)
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)
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 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ánKhi muốn chèn tham chiếu tên, bạn làm như sauNế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 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 Nhập xong công thức hoặc phép tính và ấn EnterNhư 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.