Tham chiếu dữ liệu giữa các sheet trong excel

      26

Bài viết này, học tập Excel Online để giúp đỡ bạn phân tích và lý giải tham chiếu nước ngoài trong Excel một cách cơ bạn dạng và hướng dẫn bí quyết 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: Tham chiếu dữ liệu giữa các sheet trong excel

Khi giám sát và đo lường dữ liệu vào Excel, song khi bạn sẽ gặp cần trường hợp đề xuất 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 cho được điều này, bạn chỉ việc tạo link giữa nhị 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à việc tham chiếu mang đến một ô hoặc một mảng những ô (range) không phía bên trong trang tính hiện tại tại. Tác dụng của việc dùng tham chiếu nước ngoài là bất cứ lúc nào ô tham chiếu núm đổi, quý hiếm trả về do ô 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 biệt lập mặc cho dù chúng giống như nhau. Trong bài hướng dẫn này, họ cùng bước đầu với những cách cơ phiên bản và tạo ra một vài các loại tham chiếu ngoại qua các ví dụ cố kỉnh 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ông giống 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 những ô từ 1 trang tính khác cùng bên trong một bảng tính, gửi tên trang tính ẩn dưới dấu (!) ở trước đại chỉ ô.

Công thức bao gồm dạng như sau

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

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

Ví dụ tham chiếu cho tới ô A1 phía bên 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 vào Sheet2, đánh Sheet2!A1:A10

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

Trong thực tế khi nhân cực hiếm ô A1 vào trang tính Thống kê với 10, cách làm tham chiếu ngoại như sau

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

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

Khi viết cách làm tham chiếu tới ô bên trong trang tính khác, chúng ta có thể đánh thương hiệu trang tính khác phía sau dấu (!) và tham chiếu một ô một cách bằng tay và dễ mắc lỗi. 

Cách xuất sắc 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 cùng để Excel tự 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 công việc sau

Đánh cách làm 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, gửi tới trang tính và lựa 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ất

Ví dụ, nếu bạn có một list Doanh số bán sản phẩm và bạn muốn tính thuế giá chỉ trị tăng thêm (19%) cho mỗi sản phẩm vào một trang tính không giống tên VAT, triển khai theo quá trình sau

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

Ấn Enter để chấm dứt công thứcLưu ý Khi chèn tham chiếu Excel tới một trang tính khác thực hiện phương thức trên, mặc định của Excel Microsoft là thêm tham chiếu tương đối (không tất cả kí tự $). Vậy trong lấy ví dụ như trên, bạn cũng có thể copy công thức cho những ô không giống trong cột B phía trong trang tính VAT, ô tham chiếu hoàn toàn có thể điều chỉnh cho từng hàng, và bạn sẽ có VAT cho mỗi mẫu được đo lường chính xác.

Tương từ như trên, bạn cũng có thể tham chiếu một mảng những ô vào 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 trong trang tính Doanh số , các bạn nhập cách làm sau

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

Tham chiếu từ bảng tính khác

Trong cách làm Microsoft Excel, tham chiếu ngoại từ bảng tính khách hàng được trình diễn theo hai cách dựa vào vị trí mối cung cấp của bảng tính mở xuất xắc đóng

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

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

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

Ví dụ tham chiếu nước ngoài tới B2:B5 vào trang tính mon 1 phía trong bảng tính Doanh số.xlsx:

’T1’B2:B5

Nếu ý muốn tính tổng các ô, phương pháp tham chiếu bảng tính như sau:

=SUM (T1!B2:B5)

Tham chiếu nước 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 phải mở. Giả dụ 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 vào folder Báo cáo trong ổ D, phương pháp như sau:

=SUM (D:BC’T1′!B2:B5)

với

Đường dẫn file: đã cho thấy ổ và băng thông tới nơi lưu trữ file Excel (D:BC như trong lấy một ví dụ 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ư  trong cách làm trênTên trang tính: nơi cất ô tham chiếu. Phần này bao hàm tên trang tính với dấu chấm than theo sau (như trong lấy ví dụ như này là ‘T1’!)Ô tham chiếu: chỉ ô hoặc mảng ô tham chiếu vào công thức

Nếu chúng ta đã tạo tham chiếu cho tới một bảng tính mở và kế tiếp đóng nguồn của bảng tính, tham chiếu nước ngoài sẽ tự động hóa cập nhật bao hàm cả đường truyền đầy đủ.

Xem thêm: Top Hình Ảnh Chúc Mừng Năm Mới 2021 Đẹp Gửi Tặng Bạn Bè, Người Thân

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ừ bỏ trống hoặc kí tự chưa phải chữ cái, bạn phải để tên hoặc đường truyền trong lốt nháy. Ví dụ:

=SUM(‘T1’!B2:B5)

=SUM (‘ mon 1’!B2:B5)

=SUM (‘D:Báo cáo mon 1’!B2:B5)

Tạo tham chiếu tới một bảng tính không giống trong Excel

Giống như trường đúng theo 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 bí quyết thủ công. Chỉ việc chuyển sang trọng bảng tính khác khi đã nhập cách làm và lựa chọn ô hoặc mảng bạn muốn tham chiếu tới. Microsoft Excel sẽ làm cho phần việc còn lại

Lưu ý: Khi tạo ra tham chiếu tới trang tính khác bằng phương pháp chọn ô, Excel luôn chèn tham chiếu hay đối. Do đó, nếu như khách hàng định copy công thức mới lập sang những ô 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 tương đối hoặc lếu láo hợp phụ thuộc mục đích của bạn.

Tham chiếu tới tên trong thuộc hoặc khác bảng tính

Để tạo nên tham chiếu ngoại gọn gàng hơn, bạn cũng có thể tạo thương hiệu trong nguồn trang tính tiếp nối tham chiếu tới tên kia từ trang tính khác phía trong cùng hoặc không giống bảng tính.

Tạo tên trong Excel

Để chế tác tên vào Excel, chọn toàn bộ các ô bạn có nhu cầu 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 vỏ hộp thoại New Name, đánh tên bạn muốn (nhớ rằng kí từ trống ko được áp dụng cho tên Excel), và soát sổ mảng tham chiếu được ghi vào ô Refers to.

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

Lưu ý: Hình hình ảnh trên sử dụng Excel năm nhâm thìn nên giao diện Define Name đã khác so với Excel 2013 quay trở lại 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 lúc tên được sản xuất lập, bạn có thể sử dụng tham chiếu ngoại này vào Excel. Định dạng của tham chiếu này đơn gian hơn nhiều so cùng với tham chiếu trang tính với bảng tính vừa đề cập trên đây.

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

Các bạn nên tạo thương hiệu ở cấp độ bảng tính (trừ khi bạn có nguyên nhân để không sử dụng nó) bởi vì chúng đơn giản và dễ dàng hoá việc tạo lập tham chiếu nước ngoài hơn các so với thương hiệu trang tính.

Tham chiếu tên trong trang tính không giống nằm và một bảng tính

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

=Function (tên)

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

Để tham chiếu tên cấp độ trang tính xuất phát từ 1 trang tính khác bên trong cùng bảng tính, bạn phải kê tên trước tên trang tính với 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 phương pháp hoặc kí tự không phải chữ cái, hãy nhớ để chúng bên phía 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ông giống bảng tính bao hàm tên bảng tính (gồm tệp mở rộng), lốt chấm than cùng 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 cấp độ trang tính trong bảng tính khác, thương hiệu trang tính và dấu chấm than theo sau cũng đề xuất nằm trong công thức, với 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 những tên khác biệt trong trang tính Excel, bạn không cần thiết phải nhớ toàn bộ 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 công việc sau

Chọn địa chỉ ô, nhâp vết (=) và đánh công thức hoặc phép toánKhi muốn chèn tham chiếu tên, chúng ta làm như sau

Nếu tham chiếu tên ở cấp độ bảng tính phía bên trong bảng tính khác, gửi tới bảng tính đó. Trường hợp tên nằm trong trang tính mà lại cùng bảng tính, bỏ lỡ bước này

Nếu ai đang lập tham chiếu tới thương hiệu ở cấp độ trang tính, dịch rời tới trang tính kia (nằm trong cùng hoặc khác bảng tính)

Ấn F3 để mở vỏ hộp thoại Paste Name, chọn tên bạn muốn tham chiếu và click OK 

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

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