VietNetwork.Vn

Sử dụng tên bảng và cột thay vì tham chiếu ô.

Excel được biết đến rộng rãi như một chương trình cho phép bạn tạo các bảng dữ liệu phức tạp. Tuy nhiên, một số người ít quen thuộc với một loại tham chiếu đặc biệt đi kèm với các bảng này: tham chiếu có cấu trúc.

Có thể sử dụng tham chiếu có cấu trúc trong Excel 2016 trở lên, bao gồm Excel 365.


1. Tài liệu tham khảo có cấu trúc là gì?

Tham chiếu ô trực tiếp trong Excel liên kết một ô với một ô khác bằng cách sử dụng nhãn tiêu đề cột và hàng. Ví dụ, ô A1 nằm ở cột đầu tiên và hàng đầu tiên, trong khi ô C10 nằm ở cột thứ ba và hàng thứ mười.

Mặt khác, thay vì sử dụng nhãn tiêu đề cột và hàng, tham chiếu có cấu trúc sử dụng tên bảng và cột. Trong ví dụ dưới đây,

=SUM([@[Daily profit]]*7)
lấy giá trị trong mỗi ô trong cột có tên "Lợi nhuận hàng ngày" và nhân chúng với bảy.


2. Sử dụng tham chiếu có cấu trúc trong bảng

Để sử dụng tham chiếu có cấu trúc trong bảng của bạn, như trong ví dụ trên, trước tiên bạn cần thêm một số dữ liệu vào bảng tính Excel của mình, với tên cột được bao gồm ở đầu dữ liệu này. Nếu bạn không thêm tên cột, Excel sẽ mặc định là Cột 1, Cột 2, v.v., điều này kém rõ ràng hơn nhiều khi bạn muốn sử dụng tiêu đề trong các công thức sau này.


Trước khi sử dụng bất kỳ dữ liệu nào để tạo phép tính, hãy chọn một ô trong dữ liệu của bạn và nhấp vào " Định dạng dưới dạng bảng " trong tab Trang chủ trên ruy-băng. Tại đó, hãy chọn thiết kế phù hợp nhất với bạn.


Bây giờ, hãy sử dụng hộp thoại Tạo bảng để xác minh rằng tất cả dữ liệu của bạn đã được chọn, đánh dấu vào ô "Bảng của tôi có tiêu đề" và nhấp vào "OK".
Hộp thoại Tạo bảng của Excel, trong đó vị trí dữ liệu được tô sáng, hộp kiểm


Bây giờ dữ liệu của bạn đã được định dạng trong bảng Excel, bạn đã sẵn sàng sử dụng tham chiếu có cấu trúc.


Trong ví dụ của tôi, tôi sẽ chọn ô C2 (ô đầu tiên mà tôi muốn tạo phép tính) và nhập:

=SUM(

Bây giờ, tôi sẽ nhấp vào ô đầu tiên chứa dữ liệu trong cột "Lợi nhuận hàng ngày" và Excel sẽ chèn tham chiếu có cấu trúc vào cột đó.

=SUM([@[Daily profit]]

Dấu ngoặc vuông là cách Excel biểu thị rằng nó đang sử dụng tham chiếu có cấu trúc và ký hiệu @ (còn được gọi là toán tử giao nhau) có nghĩa là phép tính sẽ được áp dụng tương ứng cho từng hàng trong bảng.

Cuối cùng, tôi cần nhân các giá trị trong cột "Lợi nhuận hàng ngày" với bảy để tạo ra lợi nhuận hàng tuần và đóng dấu ngoặc tròn.

=SUM([@[Daily profit]]*7)

Khi tôi nhấn Enter, công thức sẽ tự động sao chép sang các ô còn lại trong cột "Lợi nhuận hàng tuần".


Nếu tôi xóa thủ công ký hiệu @, phép tính trong cột "Lợi nhuận hàng tuần" sẽ tính tổng toàn bộ cột "Lợi nhuận hàng ngày" và nhân tổng đó với bảy.

3. Sử dụng tham chiếu có cấu trúc bên ngoài bảng

Tham chiếu có cấu trúc cũng được sử dụng trong các công thức bên ngoài bảng Excel tham chiếu dữ liệu bảng. Trong ví dụ này, tôi sẽ sử dụng công thức XLOOKUP để tạo lợi nhuận hàng tuần trong ô E4 dựa trên trang web được nêu trong ô E2.


Bước đầu tiên là đặt tên cho bảng. Nếu không, Excel sẽ đặt tên dữ liệu của tôi là Table1, điều này có thể gây nhầm lẫn nếu có nhiều bảng trong sổ làm việc của tôi. Trên thực tế, bạn nên tập thói quen đặt tên cho các bảng bất cứ khi nào bạn tạo chúng trong Excel, ngay cả khi bạn không có ý định tham chiếu chúng ở bất kỳ nơi nào khác trong bảng tính của mình.

Để thực hiện việc này, tôi sẽ chọn bất kỳ ô nào trong bảng, mở tab "Thiết kế bảng" trên ribbon và nhập tên phù hợp vào trường Tên bảng. Trong trường hợp của tôi, tôi sẽ gọi bảng của mình là "Lợi nhuận".


Có một số quy tắc nhất định bạn phải tuân theo khi đặt tên bảng:

    Tên bảng của bạn phải bắt đầu bằng một chữ cái, dấu gạch dưới (_) hoặc dấu gạch chéo ngược (\).
    Phần còn lại của tên bảng có thể là sự kết hợp của tối đa 255 chữ cái, số, dấu chấm và dấu gạch dưới.
    Bảng của bạn không thể được gọi là "C", "c", "R" hoặc "r" vì những tên này được sử dụng cho mục đích khác trong Excel.
    Bảng của bạn cũng không thể giống với tham chiếu ô, chẳng hạn như A1 hoặc $A$1.
    Cố gắng giữ tên bảng trong một từ để dễ tham chiếu. Tuy nhiên, nếu bạn phải sử dụng nhiều hơn một từ, hãy sử dụng dấu gạch dưới (không phải dấu cách) giữa mỗi từ.
    Đảm bảo tên bảng chưa được sử dụng ở nơi khác trong bảng tính của bạn.

Bây giờ, trong ô E4, tôi có thể bắt đầu công thức XLOOKUP của mình bằng cách nhập:

=XLOOKUP(E2

Sau đó, tôi cần chọn mảng tra cứu, là các ô từ A2 đến A9. Lưu ý cách công thức của tôi tự động chuyển đổi mảng này thành tham chiếu có cấu trúc, với "Profits" là tên bảng tôi đã tạo trước đó và "Site" là tiêu đề cột.

=XLOOKUP(E2,Profits[Site],

Cuối cùng, tôi có thể chọn mảng trả về, là các ô từ C2 đến C9, và đóng dấu ngoặc tròn của tôi. Một lần nữa, Excel biến điều này thành tham chiếu có cấu trúc cho tôi.

=XLOOKUP(E2,Profits[Site],Profits[Weekly profit])

Khi tôi nhấn Enter, lợi nhuận hàng tuần đã được lấy thành công từ bảng của tôi.


4. Tại sao nên sử dụng tài liệu tham khảo có cấu trúc?

Bạn có thể đang nghĩ, "Mục đích là gì?" Vâng, có vô số lợi ích khi sử dụng tham chiếu có cấu trúc thay vì tham chiếu trực tiếp khi xử lý bảng trong Excel.

Đầu tiên, tham chiếu có cấu trúc dễ tạo và dễ đọc hơn tham chiếu trực tiếp. Do đó, tôi có thể hiểu công thức của mình có mục đích gì chỉ bằng cách liếc nhìn chúng và tôi có thể dễ dàng phân tích công thức của mình nếu Excel trả về lỗi.

Thứ hai, nếu tôi thêm một hàng vào bảng của mình bằng cách nhấp và kéo nút ở góc dưới bên phải (được gắn nhãn "A" trong ảnh chụp màn hình bên dưới), rồi điền dữ liệu bổ sung vào hàng đó, thì các tham chiếu có cấu trúc mà tôi đã sử dụng trong bảng tính của mình sẽ tự động áp dụng cho dữ liệu mới này.

Trong ví dụ này, tôi đã thêm dữ liệu cho một site bổ sung (hàng 10). Không chỉ lợi nhuận hàng tuần được tính tự động khi tôi thêm lợi nhuận hàng ngày mà tôi còn thay đổi tham chiếu site trong ô E2 thành site I và dữ liệu đã được truy xuất chính xác. Nếu tôi sử dụng tham chiếu trực tiếp, tôi sẽ phải thực hiện một số điều chỉnh thủ công để đạt được kết quả tương tự.


Thứ ba, nếu tôi thêm một cột khác vào giữa các cột "Lợi nhuận hàng ngày" và "Lợi nhuận hàng tuần", tôi có thể yên tâm rằng mọi tham chiếu đến các cột hiện tại của tôi sẽ vẫn an toàn, vì tôi đã tham chiếu đến tên cột thay vì chữ cái liên quan đến mỗi cột.

Cuối cùng, tham chiếu có cấu trúc là động. Nếu tôi thay đổi tên của một trong các cột bảng của mình, tất cả các tham chiếu có cấu trúc liên quan sẽ cập nhật tương ứng. Trong ví dụ này, tôi đã thay đổi cột "Lợi nhuận hàng tuần" của mình thành "TOTALS" và công thức XLOOKUP trong ô E4 đã áp dụng sửa đổi này.


Tóm lại, ngay khi bạn tạo bảng trong Excel, bạn nên nghĩ đến việc sử dụng tham chiếu có cấu trúc để tận dụng tối đa dữ liệu của mình.

Bên cạnh những lợi ích được liệt kê ở trên, tham chiếu có cấu trúc còn sử dụng ít bộ nhớ máy tính hơn so với tham chiếu trực tiếp—chỉ là một trong nhiều cách giúp tăng tốc bảng tính Excel của bạn.