Bài viết này, học tập Excel Online để giúp đỡ bạn lý giải tham chiếu ngoại trong Excel một giải pháp cơ bạn dạng và phía dẫn biện pháp lập tham chiếu cho 1 trang tính hoặc bảng tính trong cách làm của bạn.
Khi đo lường dữ liệu vào Excel, đôi khi các bạn sẽ gặp yêu cầu trường hợp nên lấy dữ liệu xuất phát từ 1 trang tính hoặc thậm chí một tệp tin Excel khác. Để làm được điều này, bạn chỉ việc tạo links giữa nhì trang tính (cùng một bảng tính hoặc không giống bảng tính) bằng cách sử dụng ô tham chiếu nước ngoài hoặc con đường link.
Ô tham chiếu ngoại trong Excel là sự tham chiếu mang 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 tại. Tiện ích của việc dùng tham chiếu nước ngoài là bất cứ bao giờ ô tham chiếu nạm đổi, quý giá trả về vì chưng ô tham chiếu ngoại sẽ auto cập nhật.
Giữa tham chiếu ngoại trong Excel cùng ô tham chiếu vẫn có những điểm khác biệt mặc cho dù chúng tựa như nhau. Trong bài xích hướng dẫn này, họ cùng bước đầu với những bước cơ phiên bản và tạo ra một vài loại tham chiếu nước ngoài qua các ví dụ nắm thể
Xem nhanh
Tạo tham chiếu từ một trang tính khác Tham chiếu tự bảng tính khácTham chiếu tới tên trong thuộc hoặc khác bảng tính
Tạo tham chiếu xuất phát điểm từ 1 trang tính khác
Để tham chiếu một ô hoặc một mảng các ô từ 1 trang tính khác cùng phía trong một bảng tính, gửi 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 rẽ lẻTên_trang_tính!Địa_chỉ_ô
Ví dụ tham chiếu cho tới ô A1 phía bên trong Sheet2, tấn công 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 vào Sheet2, đánh Sheet2!A1:A10
Lưu ý: Nếu thương hiệu trang tính chứa dấu biện pháp hoặc kí tự không phải chữ cái, bạn phải đặt tên trang tính trong vệt nháy. Ví dụ như một tham chiếu nước ngoài tới ô A1 vào trang tính mang tên là Thống kê, công thức như sau: ‘Thống kê’!A1.Trong thực tiễn khi nhân quý hiếm ô A1 trong trang tính Thống kê với 10, phương pháp tham chiếu nước 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 phương pháp tham chiếu cho 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 (!) cùng tham chiếu một ô một cách bằng tay và dễ dàng mắc lỗi.
Cách tốt hơn là chỉ tới ô phía trong trang tính khác mà bạn muốn công thức tham chiếu tới và để Excel trường đoản cú sửa cú pháp. Để Excel chèn tham chiếu tới một trang tính khác, có tác dụng theo quá trình sau
Đánh phương pháp vào vị trí ô hoặc vào thanh phương pháp Khi mong muốn chèn tham chiếu tới trang tính khác, gửi 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 hoàn thành đánh bí quyết và ấn Enter để hoàn tấtVí dụ, nếu khách hàng có một danh sách Doanh số bán sản phẩm và bạn muốn tính thuế giá chỉ trị ngày càng tăng (19%) cho từng sản phẩm vào một trang tính không giống tên VAT, triển khai theo công việc sau
Đánh công thức =19%* vào ô B2 phía bên trong trang VAT lịch sự trang Doanh số, cùng click vào ô B2. Excel sẽ tự động hóa chèn tham chiếu nước ngoài tới ô đó như hình dưới đây Ấn Enter để xong công thức Lưu ý Khi chèn tham chiếu Excel tới một trang tính khác áp dụng phương thức trên, mang định của Excel Microsoft là thêm tham chiếu kha khá (không có kí từ bỏ $). Vậy trong lấy một ví dụ trên, chúng ta có thể copy công thức cho các ô không giống trong cột B phía bên trong trang tính VAT, ô tham chiếu rất có thể điều chỉnh cho từng hàng, và các bạn sẽ có VAT cho từng mẫu được tính toán chính xác.Tương từ như trên, chúng ta cũng có thể tham chiếu một mảng các ô trong trang tính khác. Điểm biệt lập duy tuyệt nhất là chọn những ô tự trang tính. Ví dụ, để tìm tổng doanh số bán hàng trong các ô B2:B5 vào trang tính Doanh số , chúng ta nhập công thức sau
=SUM (‘Doanh số’!B2:B5)
Tham chiếu trường đoản cú bảng tính khác
Trong công thức Microsoft Excel, tham chiếu nước ngoài từ bảng tính khách hàng được trình bày theo nhị cách dựa vào vị trí mối cung cấp của bảng tính mở hay đóng
Tham chiếu nước ngoài từ bảng tính mở
Khi mối cung cấp bảng tính mở, tham chiếu ngoại cất tên bảng tính để trong dấu ngoặc vuông (bao tất cả phần mở rộng tệp) đứng trước tên trang tính, dấu (!), với ô tham chiếu hoặc mảng tham chiếu. Cú pháp như sau:
Ví dụ tham chiếu nước ngoài tới B2:B5 trong trang tính mon 1 phía bên trong bảng tính Doanh số.xlsx:
Nếu mong tính tổng các ô, bí quyết 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 xuất phát điểm từ một bảng tính khác, bảng tính này không nhất thiết bắt buộc mở. Trường hợp nguồn của bảng tính là đóng, các 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 vào folder Báo cáo trong ổ D, cách làm như sau:
=SUM (D:BC
với
Đường dẫn file: đã cho thấy ổ và đường truyền tới nơi tàng trữ file Excel (D:BC như trong ví dụ như này) tên bảng tính: bao gồm tệp mở rông (.xlsx, .xls, hoặc xslm) và luôn luôn được đặt trong ngoặc vuông nhưNếu các bạn đã chế tạo tham chiếu tới một bảng tính mở và tiếp nối đóng nguồn của bảng tính, tham chiếu nước ngoài sẽ auto cập nhật bao hàm cả đường dẫn đầy đủ.
Lưu ý: Nếu thương hiệu trang tính hoặc thương hiệu bảng tính hoặc cả hai chứa kí trường đoản cú trống hoặc kí tự không hẳn chữ cái, bạn phải đặt tên hoặc đường truyền trong vết nháy. Ví dụ:=SUM(‘
=SUM (‘
=SUM (‘D:Báo cáo
Tạo tham chiếu cho tới một bảng tính khác trong Excel
Giống như trường phù hợp tạo phương pháp Excel tham chiếu tới trang tính khác, bạn không cần thiết phải đánh tham chiếu cho tới bảng tính một giải pháp thủ công. Chỉ việc chuyển sang bảng tính khác khi đã nhập công thức và lựa chọn ô hoặc mảng bạn có nhu cầu tham chiếu tới. Microsoft Excel sẽ làm phần câu hỏi còn lại
Lưu ý: Khi tạo nên tham chiếu cho tới trang tính khác bằng phương pháp chọn ô, Excel luôn chèn tham chiếu tuyệt đối. Vày đó, nếu như bạn định copy công thức bắt đầu lập sang các ô khác, hãy có lẽ rằng kí hiệu dollar ($) được vứt khỏi ô tham chiếu để mang tham chiếu về dạng kha khá hoặc láo hợp phụ thuộc mục đích của bạn.Tham chiếu tới thương hiệu trong thuộc hoặc không giống bảng tính
Để tạo ra tham chiếu ngoại gọn hơn, chúng ta cũng có thể tạo tên trong mối cung cấp trang tính kế tiếp tham chiếu cho tới tên kia từ trang tính khác phía bên trong cùng hoặc không giống bảng tính.
Tạo thương hiệu trong Excel
Để tạo tên vào Excel, chọn tất cả các ô bạn muốn tham chiếu. Tiếp nối 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 có nhu cầu (nhớ rằng kí trường đoản cú trống không được thực hiện cho thương hiệu Excel), và kiểm soát mảng tham chiếu được ghi trong ô Refers to.
Ví dụ đây là cách tạo tên (Doanh_số_T1) mang lại ô B2:B5 trong trang tính tháng 1
Lưu ý: Hình ảnh trên sử dụng Excel năm 2016 nên hình ảnh Define Name vẫn khác so với Excel 2013 về bên 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 chế tác lập, bạn cũng có thể sử dụng tham chiếu nước ngoài này vào Excel. Định dạng của tham chiếu này đối chọi gian hơn những so cùng với tham chiếu trang tính cùng bảng tính vừa nhắc trên đây.
Lưu ý: Tên được tạo nên mặc định với cấp độ bảng tính (ghi vào mục Scope tương đương Name in workbook). Bạn có thể lập thương hiệu ở màn chơi trang tính bằng cách chọn trang tính tương ứng từ list trong Scope (Names in workbook). Đối cùng với tham chiếu Excel, số lượng giới hạn tên khôn cùng quan trọng bởi vì nó xác định giới hạn mà thương hiệu được thực hiện khi tham chiếu.Các bạn nên tạo tên ở màn chơi bảng tính (trừ khi bạn có lý do để không sử dụng nó) bởi chúng dễ dàng và đơn giản hoá câu hỏi tạo lập tham chiếu nước ngoài hơn nhiều so với tên trang tính.
Tham chiếu tên trong trang tính không giống nằm cùng một bảng tính
Để tham chiếu tên cấp độ bảng tính trong và một bảng tính, bạn chỉ việc đánh thương hiệu vào tham số của hàm
=Function (tên)
Ví dụ để tìm tổng của các ô phía 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 xuất phát điểm từ một trang tính khác phía bên trong cùng bảng tính, bạn phải để tên trước thương hiệu 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 cất dấu biện pháp hoặc kí tự chưa phải chữ cái, hãy nhớ để chúng bên trong dấu nháy như sau
=SUM(‘Báo cáo T1’!Doanh_số_T1)
Tham chiếu thương hiệu trong bảng tính khác
Tham chiếu cho tới tên cấp độ 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ới 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 vào bảng tính khác, tên trang tính cùng dấu chấm than theo sau cũng nên nằm vào công thức, và tên bảng tính phải kê trong ngoặc vuông. Ví dụ:
=SUM (‘CDocumentsTổng.xlsx’!Doanh_số_T1)
Cách chế tạo tham chiếu tên
Nếu bạn đã tạo nhiều tên không giống nhau trong trang tính Excel, các bạn không quan trọng phải nhớ tất cả tên của chúng. Để chèn tham chiếu thương hiệu vào công thức, thực hiện theo quá trình sau
lựa chọn vị trí ô, nhâp vết (=) với đánh cách làm hoặc phép toán Khi mong muốn chèn tham chiếu tên, bạn làm như sauNếu tham chiếu tên ở màn chơi bảng tính nằm trong bảng tính khác, đưa tới bảng tính đó. Giả dụ tên phía 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 ở cấp độ trang tính, dịch chuyển tới trang tính đó (nằm trong cùng hoặc không giống bảng tính)
Ấn F3 nhằm mở vỏ hộp thoại Paste Name, chọn tên bạn muốn tham chiếu và click OK Nhập hoàn thành công thức hoặc phép tính với ấn EnterNhư vậy bạn đã hiểu phương pháp tạo tham chiếu nước ngoài trong Excel, bạn cũng có thể sử dụng chúng để lấy dữ liệu cho các phép tính dễ ợt hơn.
