Excel: Tạo tham chiếu đến một bảng tính khác

Người dùng Microsoft Excel đánh giá cao tùy chọn tạo tham chiếu đến các bảng tính khác trong thư mục hiện hoạt. Đặc biệt với những bảng tính lớn có nhiều công thức, dữ liệu và hàm, việc tạo tham chiếu để có thể làm việc hiệu quả với mọi dữ liệu là rất hữu ích. Điều này giúp tiết kiệm thao tác nhập kép hoặc sao chép và dán thủ công tẻ nhạt, vốn tốn thời gian và dễ xảy ra lỗi.

Trong bài viết này, bạn sẽ tìm hiểu cách có thể liên kết các bảng tính Excel với nhau chỉ bằng một vài cú nhấp chuột và do đó thực hiện các phép tính trên nhiều bảng tính.

Làm thế nào để bạn tạo một tham chiếu đến một bảng tính khác?

Bạn tạo liên kết giữa hai bảng tính Excel bằng công thức và tham chiếu bên ngoài trong bảng tính đích.

Trong ví dụ của chúng tôi, Bảng 1 cho biết một công ty có bao nhiêu nhân viên tại các địa điểm khác nhau ở Đức và các quốc gia Châu Âu khác. Trong bảng thứ hai, chỉ nên xuất số lượng nhân viên của chi nhánh ở Passau. Liên kết giữa hai bảng đảm bảo nếu dữ liệu ở bảng 1 thay đổi thì thông tin ở bảng 2 được tự động cập nhật.

Để tạo tham chiếu đến một bảng tính khác, trước tiên hãy mở cả hai bảng tính trong Microsoft Excel. Sau đó, trong bảng đích, bấm vào ô đích mà bạn muốn hiển thị dữ liệu từ tệp nguồn. Trong ví dụ thực tế của chúng tôi, đây là ô mục tiêu B1.Công thức cho liên kết luôn bắt đầu bằng dấu bằng. Nhập "="vào ô đã chọn trước đó.

Trong bước tiếp theo, hãy chuyển sang bảng có dữ liệu nhân viên và đánh dấu số 55 trong ô C9. Đây là số lượng nhân viên tại chi nhánh ở Passau.

Nhấn Enter để kết thúc phím tắt. Số lượng 55 nhân viên hiện xuất hiện trong Bảng 2, vì bạn đã liên kết hiệu quả các ô của cả hai trang tính với nhau.

Nếu bạn muốn tạo tham chiếu thủ công bằng bàn phím, hãy sử dụng cú pháp sau:

=tên tờ!ô

Đối với tên trang tính, hãy nhập tên của trang tính có ô bạn muốn tham chiếu. Trong ví dụ của chúng tôi, tên trang tính là "Trang tính 1." Nhãn ô chỉ định tham chiếu đến (các) ô trong Trang tính 1 mà bạn muốn tham chiếu đến.

Làm cách nào để thực hiện các phép tính trên nhiều bảng tính Excel?

Việc tính toán phải được thực hiện trên nhiều bảng tính thường xảy ra. Trong sổ làm việc mẫu của chúng tôi, có một số bảng tính có cấu trúc giống hệt nhau. Bảng mô tả chi phí điện của các nhà máy khác nhau của một công ty trong một năm, chia theo tháng. Sổ làm việc chứa dữ liệu cho các năm 2016 đến 2021. Tổng số và giá trị tối đa sẽ được tính trong một trang tính tóm tắt.

Người dùng làm việc không thường xuyên với Excel biết cách thực hiện các phép tính trên nhiều trang tính trong sổ làm việc. Ít người biết rằng khi tính toán trên nhiều bảng tính, các khu vực có thể được chuyển đổi giúp rút ngắn đáng kể các công thức và làm cho chúng rõ ràng hơn.

Quan trọng: Điều quan trọng là tất cả các bảng tính mà bạn đưa vào phép tính của mình đều được cấu trúc giống hệt nhau theo cùng một mẫu. Chỉ trong trường hợp này, các công thức sẽ cung cấp kết quả chính xác. Đặc biệt với các bảng tính mở rộng có nhiều dữ liệu, kết quả không nhất thiết phải đáng chú ý nếu vùng dữ liệu trong một trong các bảng tính bị dịch chuyển theo hàng hoặc cột. Đồng thời, kết quả là sai.

Trong ví dụ sau, chi phí điện của vài năm qua sẽ được cộng lại. Hình minh họa cho thấy cấu trúc của các trang riêng lẻ:

Ngoài cấu trúc thống nhất của các bảng, việc sắp xếp các trang tính trong sổ làm việc rất quan trọng. Đảm bảo các trang tính bạn cần nằm cạnh nhau trong sổ làm việc.

Hình dưới đây cho thấy cách sắp xếp các trang tính trong sổ làm việc mẫu:

Sau khi chuẩn bị xong, bạn có thể bắt đầu nhập công thức.

Làm cách nào để tính tổng của nhiều trang tính trong Excel?

Trong bảng ví dụ của chúng tôi, kết quả chung của tất cả các tổng hàng năm sẽ được xác định trong một bảng tóm tắt. Tổng của các năm riêng lẻ nằm trong bảng tương ứng ở ô H16.

Công thức tính tổng có cấu trúc như sau:

=SUM('Trang tính đầu tiên:Trang tính cuối cùng'!Phạm vi)

" Với FirstSheet, bạn chuyển tên của trang tính đầu tiên sẽ được đưa vào phép tính. Đối số trang tính cuối cùng xác định tên của trang tính cuối cùng để tính toán. Tất cả các sheet nằm giữa hai sheet này cũng được xem xét.Khu vực xác định khu vực bạn muốn tính tổng."

Sử dụng công thức sau để tính tổng các giá trị trong các ô H16 của trang tính từ 2016 đến 2021:

=SUM('2016:2021'!H16)

Hình dưới đây cho thấy việc sử dụng công thức này trong bảng mẫu:

Bằng cách điều chỉnh công thức đơn giản này, bạn cũng có thể truy vấn các ô khác. Ví dụ: nếu bạn muốn biết lượng điện năng tiêu thụ tại nhà máy Frankfurt vào tháng 4 từ năm 2016 đến năm 2021, hãy thay thế tham chiếu đến ô H16 bằng ô F7 trong công thức.

Khi tính toán trên nhiều bảng tính, bạn không bị giới hạn ở từng ô riêng lẻ trong bảng tính. Bạn có thể chuyển toàn bộ phạm vi cho một hàm bất kỳ lúc nào.

Làm cách nào để tính giá trị lớn nhất của nhiều trang tính trong Excel?

Trong bảng ví dụ của chúng tôi, trong đó chi phí điện của các nhà máy khác nhau được so sánh trong các năm từ 2016 đến 2021, giá trị tối đa tuyệt đối của tất cả các giá trị riêng lẻ của tất cả các năm sẽ được xác định. Các giá trị riêng lẻ nằm trong bảng 2016 đến 2021 trong các ô B4:G15.

Sử dụng công thức sau để xác định giá trị lớn nhất của tất cả các giá trị riêng lẻ trong năm bảng tính của các năm tương ứng:

=MAX('2016:2021'!B4:G15)

Hình dưới đây cho thấy việc sử dụng công thức này trong bảng mẫu:

Làm thế nào để bạn tính toán giá trị trung bình trên các bảng tính khác nhau?

Ngoài giá trị tối đa, giá trị trung bình trên nhiều bảng tính cũng có thể được tính bằng một công thức duy nhất. Để thực hiện việc này, hãy sử dụng cú pháp sau.

=AVERAGE('trang tính bắt đầu:trang tính kết thúc'!phạm vi ô)

Với công thức bạn chuyển tổng cộng ba đối số:

• Với trang tính bắt đầu, bạn chuyển tên của trang tính đầu tiên sẽ được đưa vào phép tính.
• Tên của trang tính cuối cùng được sử dụng để xác định tên của trang tính cuối cùng cho phép tính. Tất cả các sheet nằm giữa hai sheet này đều được đưa vào tính toán.
• CellRange được sử dụng để xác định phạm vi ô cho phép tính.

Đối với bảng mẫu của chúng tôi, công thức và kết quả sẽ như sau:

=AVERAGE('2016:2021'!B4:G15)

Làm thế nào các giá trị từ các tài liệu khác có thể được liên kết?

Nếu thường xuyên làm việc với Excel, đôi khi bạn cần liên kết các tham chiếu ô từ một bảng tính không nằm trong cùng một tài liệu.Điều này hoạt động mà không có bất kỳ sự cố nào bằng cách thêm tên của tài liệu vào tham chiếu ô được mô tả ở trên. Điều này được đánh dấu bằng dấu ngoặc vuông (" [" hoặc "]" ).

Ví dụ:=[Document1]Origin!G9

Sử dụng biến thể này nếu thư mục khác có tên “Document1” đang mở. Mặt khác, nếu tài liệu không mở, hãy sử dụng đường dẫn nơi tài liệu được lưu.

Ví dụ:=C:\Files\FolderName\[Document1]Origin!G9

Làm cách nào để liên kết tham chiếu ô bằng chuột?

Các tham chiếu ô đến các tài liệu khác không nhất thiết phải được nhập thủ công, vì bạn có thể dễ dàng liên kết các ô chỉ bằng một vài cú nhấp chuột. (CHÈN ẢNH)

Làm cách nào để khắc phục lỗi tham chiếu ô?

Khi liên kết bảng tính và sử dụng tham chiếu, Microsoft Excel có thể hiển thị lỗi trong tham chiếu ô. Nếu trường hợp này xảy ra, công thức có lỗi hoặc đã xảy ra lỗi khi chọn khu vực bằng chuột

Lỗi tham chiếu cũng xảy ra nếu một tham chiếu ô được nhập vào một trang tính khác đã bị xóa hoặc đổi tên. Lỗi này có thể được so sánh với việc gửi một bức thư đến một địa chỉ cụ thể. Ngay sau khi mã zip được thay đổi, bức thư không thể gửi được nữa vì địa chỉ không chính xác.

Tóm lại, bạn nên làm việc với các tham chiếu trong Excel. Lợi ích chính của việc làm việc với các liên kết là bạn có thể thực hiện công việc của mình hiệu quả và nhanh chóng hơn.

Liên kết cho phép bạn truy cập dữ liệu hiện có mà không cần phải nhập lại. Điều này tiết kiệm thời gian và ngăn ngừa lỗi truyền. Trong Excel, tham chiếu cũng có thể được sử dụng để thực hiện các phép tính. Tham chiếu có thể được đặt cho các ô khác trong một sổ làm việc hoặc các ô trong một sổ làm việc khác. Chúng rất hữu ích cho việc tổ chức dữ liệu và thực hiện các phép tính.Liên kết cũng có thể được sử dụng để lọc và sắp xếp thông tin. Do đó, tham chiếu là một công cụ mạnh mẽ trong Excel giúp bạn hoàn thành công việc hiệu quả hơn.