TÌM HIỂU VỀ CÁCH TẠO LIÊN KẾT ĐẾN Ô TRONG TRANG TÍNH EXCEL KHÁC

Trong bài viết này, Siêu Marketing mang đến cho bạn những hướng dẫn từ cơ bản đến chi tiết về việc tạo tham chiếu ngoại trong Excel và cách để thiết lập các liên kết đến các trang hoặc bảng tính khác trong công thức của bạn.

Lúc thực hiện việc tính toán trên Excel, bạn có thể cần phải truy cập và sử dụng thông tin từ trang tính khác hoặc thậm chí là từ một tập tin Excel khác. Điều này có thể thực hiện thông qua việc tạo ra liên kết giữa chúng, bằng cách sử dụng các tham chiếu ngoại hoặc liên kết.

Một tham chiếu ngoại trong Excel chính là sự chỉ đến một ô hoặc một dãy ô nằm ở ngoài trang tính đang được làm việc. Ưu điểm của cái này là, bất cứ khi nào ô được tham chiếu đến thay đổi, giá trị được trả về từ tham chiếu ngoại sẽ tự cập nhật theo.

Điểm khác biệt cơ bản giữa tham chiếu ngoại trong Excel so với tham chiếu thông thường mặc dù chúng khá giống nhau. Chúng ta sẽ cùng xem qua các bước để tạo tham chiếu ngoại thông qua các ví dụ minh hoạ.

Các bước để liên kết đến trang tính khác trong cùng bảng tính

Bạn có thể tham chiếu đến một ô hoặc một dãy ô từ một trang khác trong cùng một bảng tính bằng cách thêm tên trang tính trước địa chỉ của ô, và ngăn cách chúng bằng dấu (!).

Công thức liên kết có dạng sau:

  • Để tham chiếu một ô đơn:

TênTrangTính!ĐịaChỉÔ

Ví dụ, để tham chiếu đến ô A1 ở Sheet2, bạn sẽ nhập Sheet2!A1.

  • Để tham chiếu đến một dãy ô:

TênTrangTính!ÔĐầuTiên:ÔCuốiCùng

Ví dụ, liên kết đến dãy từ ô A1 đến A10 trên Sheet2, bạn sẽ gõ vào Sheet2!A1:A10.

  • Lưu ý: Nếu tên của trang tính có chứa dấu cách hoặc kí tự đặc biệt, bạn cần phải đặt tên trang tính đó trong các dấu nháy kép. Một ví dụ về tham chiếu ngoại đến ô A1 trên trang có tên là Thống kê, công thức sẽ là: ‘Thống kê’!A1.

Khi cần nhân giá trị ở ô A1 trên trang Thống kê với số 10, công thức của tham chiếu ngoại sẽ được viết như sau

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

Hướng dẫn tạo liên kết đến trang tính bên ngoài bảng tính trong Excel

Nếu bạn viết công thức tham chiếu tới một ô trong trang tính ngoài bảng hiện tại, việc nhập một cách thủ công tên trang và tham chiếu có thể dễ dàng gây ra nhầm lẫn.

Phương pháp tiện ích hơn là để Excel tự động chỉnh sửa cú pháp thay cho bạn khi bạn chọn ô ở trang tính khác bạn muốn tham chiếu. Cách để Excel tự động thêm tham chiếu đến trang tính ngoài là thực hiện theo các bước sau đây:

  1. Viết công thức vào ô hoặc thanh công thức
  2. Khi cần chèn tham chiếu đến trang tính bên ngoài, chuyển qua trang đó và chọn ô hoặc dãy ô mà bạn muốn tham chiếu
  3. Hoàn tất công thức và nhấn Enter

Ví dụ, để tính thuế giá trị gia tăng (VAT) 19% cho từng sản phẩm trong danh sách Doanh số bán hàng ở trang tính mang tên VAT, bạn thực hiện theo các bước như sau:

  • Ghi công thức=19%* vào ô B2 trên trang VAT
  • Chuyển sang trang Doanh số và nhấp vào ô B2. Excel sẽ tự động thêm tham chiếu ngoại tới ô đó như hình minh họa dưới đây

  • Kết thúc và nhấn Enter để áp dụng công thức
  • Ghi chú:Khi bạn sử dụng phương pháp này để chèn tham chiếu Excel đến trang tính ngoài, Excel sẽ mặc định chèn tham chiếu dưới dạng tương đối (không có kí tự $). Để áp dụng thuế VAT trong ví dụ trên, bạn có thể sao chép công thức đó cho các ô khác trong cột B trên trang tính VAT, và các ô tham chiếu sẽ tự điều chỉnh cho mỗi hàng tương ứng.
  • Đối với mỗi ô, việc tính toán được thực hiện với độ chính xác cao.

Giống như đã nói, bạn cũng có thể liên kết đến một dãy ô từ một trang tính khác. Chỉ có một sự khác biệt nhỏ là bạn cần chọn hàng ô từ một trang tính. Chẳng hạn, nếu muốn cộng dồn doanh thu từ ô B2 đến B5 trên trang tính Doanh số, công thức bạn cần nhập là:

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

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

Khi nói đến tham chiếu ngoại trong công thức Microsoft Excel, chúng ta phân biệt làm hai dạng dựa trên việc nguồn bảng tính đang mở hay đã đóng

  • Tham chiếu khi bảng tính nguồn đang mở

Khi bảng tính nguồn đang được mở, tham chiếu sẽ bao gồm tên bảng tính nằm trong ngoặc vuông (kèm phần phần mở rộng của tệp), theo sau là tên của trang tính, dấu (!) và địa chỉ ô tham chiếu hoặc dãy ô. Cấu trúc như sau:

[Tên_bảng_tính]Tên_trang_tính!Địa_chỉ_ô_tham_chiếu

Ví dụ, tham chiếu tới các ô B2 đến B5 trong trang tính “Tháng 1” của bảng tính “Doanh số.xlsx” sẽ như sau:

[Tổng.xlsx]’T1’B2:B5

Để thực hiện cộng tổng các ô này, công thức tham chiếu bảng tính sẽ là:

=SUM ([Tổng.xlsx]T1!B2:B5)

  • Tham chiếu khi bảng tính nguồn đã đóng

Khi bạn tham chiếu từ bảng tính khác nhưng bảng tính nguồn không mở, bạn cần đề cập đến đường dẫn của tham chiếu ngoại

Chẳng hạn, để chèn dãy ô từ B2 đến B5 từ trang “Tháng 1” thuộc bảng tính Doanh số.xlsx ở trong thư mục Báo cáo trên ổ D, công thức sẽ là:

=SUM (D:BC[Tổng.xlsx]’T1′!B2:B5)

trong đó:

  • Đường dẫn file chỉ rõ ổ đĩa và đường dẫn chứa tệp Excel (D:BC như trong ví dụ phía trên)
  • Tên bảng tính được gắn kèm với phần mở rộng tệp (.xlsx, .xls hoặc .xlsm) và luôn nằm trong ngoặc vuông, như [Tổng.xlsx] trong công thức đã nêu
  • Tên trang tính chứa ô tham chiếu. Tên trang này bao gồm tên cùng với dấu chấm than phía sau (ví dụ là ‘T1’!)
  • Ô tham chiếu chỉ định cụ thể ô hoặc dãy ô được dùng trong công thức

Nếu bạn tạo tham chiếu cho bảng tính đang mở và sau đó đóng nó lại, tham chiếu ngoại sẽ tự động cập nhật để bao gồm cả đường dẫn đầy đủ.

  • Chú ý: Nếu tên trang tính hoặc tên bảng tính hoặc cả hai chứa kí tự khoảng trắng hoặc kí tự đặc biệt, bạn phải đặt chúng trong dấu ngoặc kép. Ví dụ:

=SUM(‘[Doanh số.xlsx]T1’!B2:B5)

=SUM(‘[Tổng.xlsx] Tháng 1’!B2:B5)

=SUM(‘D:Báo cáo[Doanh số.xlsx] Tháng 1’!B2:B5)

  • Ứng dụng tham chiếu đến bảng tính khác trong Excel

Khi bạn muốn tạo công thức Excel tham chiếu đến trang tính khác, không cần phải gõ tay từng tham chiếu. Bạn chỉ cần chuyển sang trang tính mục tiêu trong khi nhập công thức và chọn dãy ô muốn tham chiếu. Phần còn lại sẽ được Microsoft Excel tự động xử lý.

  • Chú ý: Khi tạo tham chiếu bằng cách chọn ô, Excel mặc định sử dụng tham chiếu tuyệt đối. Vì vậy, nếu bạn dự định sao chép công thức ra các ô khác, hãy đảm bảo loại bỏ kí hiệu đô la ($) khỏi tham chiếu để chuyển đổi sang dạng tham chiếu tương đối hoặc hỗn hợp tùy theo nhu cầu sử dụng của bạn.

Tham chiếu đến tên đã định nghĩa trong cùng hoặc trang tính khác

Để tạo tham chiếu ngoại ngắn gọn hơn, bạn có khả năng định nghĩa tên trong bảng tính nguồn và sau đó thực hiện tham chiếu tới tên đó.

Để thiết lập tên trong Excel, hãy chọn phạm vi ô bạn muốn gắn tên tham chiếu cho. Tiếp theo, hãy truy cập đến tab Formulas>Defined names và chọn Define name hoặc nhấn phím Ctrl+F3 và sau đó chọn New.

Bạn sẽ cần điền vào tên mong muốn (chú ý không sử dụng kí tự trắng cho tên trong Excel) trong hộp thoại New Name, và xác nhận cell range tham chiếu trong ô Refers to.

Một ví dụ là việc tạo tên (Doanh_số_T1) cho dải ô từ B2 đến B5 trên sheet Tháng 1.

  • Lưu ý: Hình minh họa trên dùng phiên bản Excel 2016; vì vậy, giao diện Define Name có thể khác biệt ở các phiên bản Excel trước 2013. Refers to có thể được tìm thấy ở Select the range of cells, còn Enter a name for the data range tương ứng với phần bạn nhập tên.

Khi đã khai báo tên, bạn có khả năng sử dụng chúng để tham chiếu đến nội dung trong Excel, cách này đơn giản hơn việc sử dụng tham chiếu dài đối với sheet và workbook.

  • Lưu ý: Mặc định, tên được khởi tạo với phạm vi toàn bảng tính, được ghi chú trong mục Scope tương đương với Name in workbook. Bạn cũng có thể chỉ định phạm vi cho một sheet cụ thể bằng cách chọn từ danh sách trong Scope (Names in workbook). Phạm vi của một tên trong Excel rất quan trọng, vì nó định rõ tên được áp dụng trong phạm vi nào.

Khuyến nghị là bạn nên đặt tên với phạm vi bảng tính để dễ dàng thuce hiện tham chiếu hơn là dùng tham chiếu tới một sheet cụ thể.

  • Tham chiếu tới tên trong cùng một bảng tính

Để tham chiếu một tên có phạm vi bảng tính trong cùng một workbook, bạn chỉ cần nhập tên vào tham số trong một hàm bất kỳ.

=Function(tên)

Chẳng hạn, nếu muốn tính tổng giá trị trong tên Doanh_số_T1 mà bạn vừa tạo, hãy sử dụng công thức
=SUM(Doanh_số_T1)

Đối với việc tham chiếu tới một tên có phạm vi một sheet từ một sheet khác cùng workbook, bạn phải thêm tên sheet trước tên và một dấu chấm than sau tên sheet:

=Function(Tên_sheet!tên)

Ví dụ:

=SUM(T1!Doanh_số_T1)

Nếu tên sheet có chứa dấu cách hoặc ký tự đặc biệt, bạn cần đặt chúng trong dấu nháy kép như sau:

=SUM(‘Báo cáo T1’!Doanh_số_T1)

  • Tham chiếu tới tên trong một workbook khác

Để tham chiếu đến tên có phạm vi bảng tính từ một workbook khác, bạn cần bao gồm cả tên workbook kèm theo phần mở rộng, một dấu chấm than, và tên bạn muốn tham chiếu.

=Function(Tên_workbook!tên)

Ví dụ:

=SUM(Tổng.xlsx!Doanh_số_T1)

Đối với tham chiếu đến tên có phạm vi sheet từ một workbook khác, bạn cũng cần đưa tên sheet và dấu chấm than vào công thức; và tên workbook cần được đặt trong ngoặc vuông. Thí dụ:

=SUM(‘[CDocumentsTổng.xlsx]Doanh_số_T1’)

  • Cách chèn tham chiếu tên vào công thức

Khi bạn đã tạo sẵn các tên khác nhau trong Excel, không cần thiết phải ghi nhớ tất cả chúng. Để thêm tham chiếu tên vaò công thức, làm theo các bước sau:

  • Chọn ô bạn muốn, nhập dấu bằng (=) và bắt đầu công thức hoặc phép toán
  • Khi muốn chèn tham chiếu tên, bạn làm như sau:

Di chuyển đến sheet chứa tên có phạm vi bảng tính nếu tên khác bảng tính; bỏ qua bước này nếu tên nằm trong cùng bảng tính đang sử dụng

Nếu bạn muốn tham chiếu đến tên có phạm vi một sheet, hãy di chuyển đến sheet đó (dù nó có nằm trong cùng hay khác workbook)

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

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

Bây giờ bạn đã biết cách sử dụng tham chiếu tên trong Excel, vốn giúp việc truy xuất dữ liệu cho các phép tính trở nên dễ dàng hơn.

Trương Thành Tài

    [submission_id id-lien-he]

    0
      0
      Đơn hàng
      Đơn hàng trốngQuay lại Shop