Tham chiếu bảng tính động hoặc sổ làm việc Excel

Giả sử bạn có dữ liệu có cùng định dạng trên nhiều trang tính hoặc sổ làm việc và cần lấy dữ liệu từ các trang tính hoặc sổ làm việc này vào một trang tính khác. Chức năng INDIRECT có thể giúp bạn nhanh chóng hoàn thành công việc.

Tham chiếu động các ô trong một trang tính khác
Các ô tham chiếu trong sổ làm việc khác động

Tham chiếu động các ô trong một trang tính khác

Giả sử có bốn trang tính chứa doanh số bán hàng khác nhau trong các quý cho bốn nhân viên bán hàng và bạn muốn tạo một trang tính tóm tắt để kéo động doanh số bán hàng quý dựa trên nhân viên bán hàng tương ứng. Để làm cho nó hoạt động, công thức dưới đây có thể giúp bạn.

Công thức chung

=INDIRECT("'"&sheet_name&"'!Cell to return data from")

1. Như ảnh chụp màn hình bên dưới được hiển thị, trước tiên, bạn cần tạo bảng tính tóm tắt bằng cách nhập tên trang tính riêng biệt vào các ô khác nhau, sau đó chọn một ô trống, sao chép công thức bên dưới vào đó và nhấn đăng ký hạng mục thi Chìa khóa.

=INDIRECT("'"&B3&"'!C3")

Chú ý: Trong mã:

• B3 là ô chứa tên trang tính mà bạn sẽ lấy dữ liệu từ đó;
• C3 là địa chỉ ô trong trang tính cụ thể mà bạn sẽ lấy dữ liệu của nó;
• Để tránh trả về giá trị lỗi nếu B5 (ô tên trang tính) hoặc C3 (ô bạn sẽ kéo dữ liệu) trống, vui lòng kèm theo công thức INDIRECT với hàm IF hiển thị như sau:
  = IF (HOẶC (B3 = "", C3 = ""), "", INDIRECT ($ B $ 3 & "! C3"))
• Nếu không có khoảng trắng trong tên trang tính của bạn, bạn có thể sử dụng trực tiếp công thức này
  = INDIRECT (B3 & "! C3")

2. Sau đó, kéo Điền vào Xử lý xuống để áp dụng công thức cho các ô khác. Bây giờ bạn đã trả lại tất cả doanh thu của quý đầu tiên từ các bảng tính cụ thể.

3. Tiếp tục để kéo tất cả doanh số của các quý khác khi bạn cần. Và đừng quên thay đổi tham chiếu ô trong công thức.

Các ô tham chiếu trong sổ làm việc khác động

Phần này nói về tham chiếu động các ô trong sổ làm việc khác trong Excel.

Công thức chung

=INDIRECT("'[" & Book name & "]" & Sheet name & "'!" & Cell address)

Như ảnh chụp màn hình bên dưới được hiển thị, Dữ liệu bạn muốn trả về định vị trong cột E của trang tính "Tổng doanh số" trong một sổ làm việc riêng biệt có tên “Hồ sơ bán hàng”. Vui lòng làm như sau từng bước để hoàn thành.

1. Đầu tiên, hãy điền thông tin sổ làm việc (bao gồm tên sổ làm việc, tên trang tính và các ô tham chiếu), bạn sẽ kéo dữ liệu dựa trên thông tin này vào sổ làm việc hiện tại.

2. Chọn một ô trống, sao chép công thức bên dưới vào đó và nhấn đăng ký hạng mục thi Chìa khóa.

=INDIRECT("'["&$B$3&"]"&$C$3&"'!"&D3)

Chú ý:

• B3 chứa tên sổ làm việc mà bạn muốn trích xuất dữ liệu từ đó;
• C3 là tên trang tính;
• D3 là ô bạn sẽ lấy dữ liệu từ đó;
• Sản phẩm #REF! giá trị lỗi sẽ trả về nếu sổ làm việc được tham chiếu bị đóng;
• Để tránh lỗi #REF! giá trị lỗi, vui lòng đính kèm công thức INDIRECT với hàm IFERROR như sau:
  = IFERROR (INDIRECT ("'[" & $ B $ 3 & "]" & $ C $ 3 & "'!" & D3), "")

3. Sau đó kéo Fill Handle xuống để áp dụng công thức cho các ô khác.

Mẹo: Nếu bạn không muốn giá trị trả về biến thành lỗi sau khi đóng sổ làm việc được tham chiếu, bạn có thể chỉ định trực tiếp tên sổ làm việc, tên trang tính và địa chỉ ô trong công thức như sau:
=INDIRECT('[SalesFile.xlxs]Total sales'!E3,"")

Chức năng liên quan