Khi bạn tạo bảng Excel, Excel gán tên cho bảng và cho từng tiêu đề cột trong bảng. Khi bạn thêm công thức vào bảng Excel, những tên đó có thể tự động xuất hiện khi bạn nhập công thức và bạn không cần nhập tham chiếu ô rõ ràng. Đây là ví dụ về những gì Excel làm:
| Thay vì dùng tham chiếu ô rõ ràng | Excel dùng tên cột và bảng |
|---|---|
| =Sum(C2:C7) | =SUM(DeptSales[Doanh Số]) |
Sự kết hợp của bảng và tên cột được gọi là một tham chiếu có cấu trúc. Tên trong các tham chiếu có cấu trúc sẽ điều chỉnh bất cứ khi nào bạn thêm hoặc loại bỏ dữ liệu ra khỏi bảng.
Tham chiếu có cấu trúc cũng xuất hiện khi bạn tạo một công thức bên ngoài bảng Excel mà tham chiếu đến dữ liệu bảng. Các tham chiếu này có thể giúp bạn dễ dàng định vị các bảng trong một sổ làm việc lớn.
Để bao gồm các tham chiếu có cấu trúc trong công thức của bạn, hãy chọn ô bảng bạn muốn tham chiếu thay vì nhập tham chiếu ô của chúng trong công thức. Chúng ta hãy dùng dữ liệu ví dụ sau đây để nhập một công thức tự động sử dụng các tham chiếu có cấu trúc để tính toán lượng tiền hoa hồng bán hàng.
| Người Bán hàng | Khu vực | Doanh Số | % Tiền hoa hồng | Số tiền Hoa hồng |
|---|---|---|---|---|
| Joe | Phía Bắc | 260 | 10% | |
| Robert | Phía Nam | 660 | 15% | |
| Michelle | Phía Đông | 940 | 15% | |
| Erich | Phía Tây | 410 | 12% | |
| Dafna | Phía Bắc | 800 | 15% | |
| Rob | Phía Nam | 900 | 15% |
- Sao chép dữ liệu mẫu trong bảng trên, bao gồm các đầu đề cột và dán nó vào ô A1 của một trang tính Excel mới.
- Để tạo bảng, hãy chọn ô bất kỳ trong dải dữ liệu, rồi nhấn Ctrl+T.
- Đảm bảo chọn hộp Bảng của tôi có tiêu đề và chọn OK.
- Trong ô E2, nhập dấu bằng (=) và chọn ô C2.
Trong thanh công thức, các tham chiếu có cấu trúc [@ [Doanh Số]] xuất hiện sau dấu bằng. - Nhập dấu hoa thị (*) ngay sau dấu ngoặc đóng, rồi chọn ô D2.
Trong thanh công thức, các tham chiếu có cấu trúc [@ [% Tiền hoa hồng]] xuất hiện sau dấu hoa thị. - Nhấn Enter.
Excel sẽ tự động sẽ tạo một cột được tính toán và sao chép công thức xuống toàn bộ cột cho bạn, điều chỉnh công thức cho mỗi hàng.
Điều gì xảy ra khi tôi dùng tham chiếu ô rõ ràng?
Nếu bạn nhập tham chiếu ô rõ ràng trong cột tính toán thì có thể khó xem những gì công thức đang tính toán hơn.
- Trong trang tính mẫu của bạn, hãy chọn ô E2
- Trong thanh công thức, nhập =C2*D2 và nhấn Enter.
Lưu ý là trong khi Excel sao chép công thức của bạn xuống cột, nó không dùng tham chiếu có cấu trúc. Ví dụ: nếu bạn thêm một cột vào giữa cột C và D hiện có, bạn phải xem lại công thức của mình.
Làm thế nào để tôi thay đổi tên bảng?
Bất kỳ khi nào bạn tạo bảng Excel, Excel sẽ tạo tên bảng mặc định (Table1, Table2, v.v.), nhưng bạn có thể thay đổi tên bảng để giúp nó có ý nghĩa hơn.
- Chọn ô bất kỳ trong bảng để hiển thị tab Thiết kế Bảng trên dải băng.
- Nhập tên bạn muốn vào hộp Tên Bảng , rồi nhấn Enter.
Trong dữ liệu ví dụ của chúng tôi, chúng ta đã dùng tên DeptSales.
Sử dụng các quy tắc sau đây cho tên bảng:
- Sử dụng các ký tự hợp lệ Luôn bắt đầu một tên bằng một chữ cái, ký tự dấu gạch dưới (_), hay dấu gạch chéo ngược ().\ Sử dụng các chữ, số, dấu chấm, và ký tự gạch dưới với phần còn lại của tên. Bạn không thể dùng "C", "c", "R" hoặc "r" cho tên, vì chúng đã được chỉ định là một lối tắt cho việc lựa chọn các cột hoặc hàng cho ô hiện hoạt khi bạn nhập chúng vào hộp Tên hoặc Đi Tới .
- Không sử dụng tham chiếu ô Tên không được giống như tham chiếu ô, chẳng hạn như Z$100 hoặc R1C1.
- Không sử dụng dấu cách để phân tách các từ Dấu cách không thể được dùng trong tên. Bạn có thể dùng ký tự dấu gạch dưới (_) và dấu chấm (.) làm dấu phân cách từ. Ví dụ, DeptSales, Sales_Tax hoặc First.Quarter.
- Không sử dụng hơn 255 ký tự Tên bảng có thể chứa tối đa 255 ký tự.
- Sử dụng tên bảng duy nhất Không được phép dùng tên giống nhau. Excel không phân biệt ký tự in hoa và chữ thường trong tên vì vậy nếu bạn nhập "Doanh thu" nhưng đã có một tên khác được gọi là "DOANH THU" trong cùng một sổ làm việc, bạn sẽ được nhắc để chọn một tên duy nhất.
- Sử dụng mã định danh đối tượng Nếu bạn dự định tạo kết hợp các bảng, PivotTable và biểu đồ, bạn nên thêm kiểu đối tượng vào tiền tố trước tên của mình. Ví dụ: tbl_Sales cho bảng doanh số, pt_Sales cho PivotTable bán hàng và chrt_Sales cho biểu đồ bán hàng hoặc ptchrt_Sales cho PivotChart doanh số. Thao tác này sẽ giữ tất cả tên của bạn trong một danh sách theo thứ tự trong Trình quản lý Tên.
Quy tắc cú pháp tham chiếu có cấu trúc
Bạn cũng có thể nhập hoặc thay đổi các tham chiếu có cấu trúc theo cách thủ công trong công thức nhưng để thực hiện việc này, phải hiểu cú pháp tham chiếu có cấu trúc. Chúng ta hãy xem ví dụ công thức sau đây:
=SUM(DeptSales[ [ #Tổng] , [Số tiền doanh thu]] ,DeptSales[[#Dữ liệu] , [Số Tiền Hoa hồng]])
Công thức này có các thành phần tham chiếu có cấu trúc sau đây:
- **Tên bảng:**DeptSales là tên bảng tùy chỉnh. Nó tham chiếu dữ liệu bảng, mà không có bất kỳ tiêu đề hoặc hàng tổng nào. Bạn có thể sử dụng tên bảng mặc định, chẳng hạn như Table1, hoặc thay đổi nó để dùng một tên tùy chỉnh.
- Mã xác định cột:[Doanh Số] và [Số tiền Hoa hồng] là các mã xác định cột sử dụng tên của cột mà chúng đại diện. Chúng tham khảo dữ liệu cột, mà không có bất kỳ tiêu đề cột hoặc hàng tổng nào. Luôn đặt mã xác định trong dấu ngoặc như được minh họa.
- Mã xác định mục:[#Totals] và [#Data] là các mã xác định mục đặc biệt tham chiếu đến các phần cụ thể của bảng, chẳng hạn như hàng tổng.
- Mã xác định bảng:[[#Tổng] , [Doanh Số]] và [[#Dữ liệu] , [Số tiền Hoa hồng]] là các mã xác định bảng đại diện cho các phần bên ngoài của tham chiếu có cấu trúc. Tham chiếu bên ngoài sẽ theo sau tên bảng và bạn đặt chúng trong dấu ngoặc vuông.
- Tham chiếu có cấu trúc:(DeptSales[[#Totals], [Doanh Số]] và DeptSales[[#Data], [Số tiền Hoa hồng]] là các tham chiếu có cấu trúc, được đại diện bởi một chuỗi bắt đầu với tên bảng và kết thúc với mã xác định cột.
Khi bạn tạo hay sửa tham chiếu có cấu trúc theo cách thủ công, hãy dùng quy tắc cú pháp sau:
- Dùng ngoặc vuông các mã xác định Tất cả các mã xác định bảng, cột và mục đặc biệt phải được đóng trong dấu ngoặc phù hợp ([ ]). Một mã xác định chứa các mã xác định khác sẽ cần dấu ngoặc phù hợp bên ngoài để đóng dấu ngoặc phù hợp bên trong của mã xác định khác. Ví dụ: =DeptSales[[Người Bán hàng]:[Khu vực]]
- Tất cả đầu đề cột là chuỗi văn bản Nhưng chúng không yêu cầu dấu ngoặc kép khi chúng được sử dụng trong một tham chiếu có cấu trúc. Số hoặc ngày, chẳng hạn như 2014 hoặc 1/1/2014, cũng được coi là chuỗi văn bản. Bạn không thể sử dụng biểu thức với tiêu đề cột. Ví dụ, biểu thức DeptSalesFYSummary[[2014]:[2012]] sẽ không hoạt động.
Sử dụng ngoặc vuông bao quanh tiêu đề cột với các ký tự đặc biệt Nếu có ký tự đặc biệt, toàn bộ tiêu đề cột cần được đặt trong dấu ngoặc vuông, điều này có nghĩa là dấu ngoặc vuông kép được yêu cầu trong một mã xác định cột. Ví dụ: =DeptSalesFYSummary[[Tổng Số tiền $]]
Đây là danh sách các ký tự đặc biệt cần dấu ngoặc bổ sung trong công thức:
- Tab
- Xuống dòng
- Trở về đầu dòng
- Dấu phẩy (,)
- Dấu hai chấm (:)
- Dấu chấm (.)
- Dấu ngoặc vuông mở ([)
- Dấu ngoặc vuông đóng (])
- Dấu thăng (#)
- Dấu nháy đơn (')
- Dấu ngoặc kép (")
- Dấu ngoặc nhọn mở ({)
- Dấu ngoặc nhọn đóng (})
- Dấu dollar ($)
- Dấu mũ (^)
- Dấu và (&)
- Dấu sao (*)
- Dấu cộng (+)
- Dấu Bằng (=)
- Dấu trừ (-)
- Ký hiệu lớn hơn (>)
- Ký hiệu nhỏ hơn (<)
- Dấu chia (/)
- Dấu a còng (@)
- Xuyệt ngược (\)
- Chấm than (!)
- Dấu ngoặc đơn mở (()
- Dấu ngoặc đơn phải ())
- Dấu phần trăm (%)
- Dấu chấm hỏi (?)
- Bấm lùi (')
- Dấu chấm phẩy (;)
- Dấu ngã (~)
- Dấu gạch dưới (_)
- Dùng ký tự thoát cho một số ký tự đặc biệt trong tiêu đề cột Một số ký tự có ý nghĩa đặc biệt và cần dùng một dấu nháy đơn (') làm ký tự thoát. Ví dụ: =DeptSalesFYSummary['#OfItems]
Dưới đây là danh sách các ký tự đặc biệt cần ký tự thoát (') trong công thức:
- Dấu ngoặc vuông mở ([)
- Dấu ngoặc vuông đóng (])
- Dấu thăng (#)
- Dấu nháy đơn (')
- Dấu a còng (@)
Dùng ký tự dấu cách để nâng cao khả năng đọc trong tham chiếu có cấu trúc Bạn có thể dùng ký tự dấu cách để cải thiện khả năng đọc tham chiếu có cấu trúc. Ví dụ: =DeptSales[ [Người Bán hàng]:[Khu vực] ] hoặc =DeptSales[[#Tiêu đề], [#Dữ liệu], [% Tiền hoa hồng]]
Tôi khuyên bạn sử dụng một dấu cách:
- Sau dấu ngoặc vuông mở đầu tiên ([)
- Trước dấu ngoặc vuông đóng cuối cùng (]).
- Sau một dấu phẩy.
Toán tử tham chiếu
Để linh hoạt hơn trong việc chỉ định phạm vi ô, bạn có thể sử dụng các toán tử tham chiếu sau đây để kết hợp các mã xác định cột.
| Tham chiếu có cấu trúc này: | Tham chiếu đến: | Bằng cách sử dụng: | Đó là phạm vi ô: |
|---|---|---|---|
| =DeptSales[[Người Bán hàng]:[Khu vực]] | Tất cả các ô trong hai hoặc nhiều cột liền kề | : Toán tử phạm vi (dấu hai chấm) | Câu trả lời 2:B7 |
| =DeptSales[Doanh Số],DeptSales[Số Tiền Hoa hồng] | Tổ hợp hai hoặc nhiều cột | , (dấu phẩy) toán tử liên kết | C2:C7, E2:E7 |
| =DeptSales[[Người Bán hàng]:[Doanh Số]] DeptSales[[Khu vực]:[% Tiền hoa hồng]] | Giao điểm của hai hoặc nhiều cột | toán tử giao (khoảng cách) | B2:C7 |
Mã xác định mục đặc biệt
Để tham chiếu đến các phần cụ thể của bảng, chẳng hạn như chỉ hàng tổng, bạn có thể sử dụng bất kỳ mã xác định mục đặc biệt nào sau đây trong các tham chiếu có cấu trúc của mình.
| Mã xác định mục đặc biệt này: | Tham chiếu đến: |
|---|---|
| #All | Toàn bộ bảng, bao gồm tiêu đề cột, dữ liệu và tổng (nếu có). |
| #Data | Chỉ các hàng dữ liệu. |
| #Headers | Chỉ hàng tiêu đề. |
| #Totals | Chỉ hàng tổng. Nếu không tồn tại sổ nào, nó trả về null. |
| Hàng #This hoặc @ hoặc @[Tên Cột] |
Chỉ các ô trong cùng hàng với công thức. Không thể kết hợp các mã xác định này với bất kỳ mã xác định mục đặc biệt nào khác. Sử dụng chúng để bắt buộc hành vi giao cắt ngầm cho tham chiếu hoặc ghi đè hành vi giao cắt ngầm và tham chiếu đến các giá trị đơn lẻ từ một cột. Excel tự động thay đổi #This xác định Hàng thành mã xác định @ ngắn hơn trong bảng có nhiều hàng dữ liệu. Nhưng nếu bảng của bạn chỉ có một hàng, Excel sẽ không thay thế mã xác định Hàng #This, điều này có thể dẫn đến kết quả tính toán không mong muốn khi bạn thêm nhiều hàng. Để tránh vấn đề trong tính toán, hãy đảm bảo bạn nhập nhiều hàng vào bảng trước khi nhập bất kỳ công thức tham chiếu có cấu trúc nào. |
Xác thực đủ điều kiện tham chiếu có cấu trúc trong cột tính toán
Khi tạo cột được tính, bạn thường dùng tham chiếu có cấu trúc để tạo công thức. Tham chiếu có cấu trúc này có thể không đủ tiêu chuẩn hoặc đầy đủ tiêu chuẩn. Ví dụ: để tạo cột được tính, có tên là Số tiền Hoa hồng, để tính số tiền hoa hồng tính bằng đô-la, bạn có thể sử dụng công thức sau đây:
| Loại tham chiếu có cấu trúc | Ví dụ | Chú thích |
|---|---|---|
| Không đủ tiêu chuẩn | =[Doanh Số]*[% Tiền hoa hồng] | Nhân các giá trị tương ứng từ hàng hiện tại. |
| Đủ điều kiện | =DeptSales[Doanh Số]*DeptSales[% Tiền hoa hồng] | Nhân các giá trị tương ứng cho mỗi hàng cho cả hai cột. |
Quy tắc chung cần thực hiện là: Nếu bạn đang dùng tham chiếu có cấu trúc trong bảng, chẳng hạn như khi bạn tạo cột được tính, bạn có thể dùng tham chiếu có cấu trúc không tiêu chuẩn, nhưng nếu bạn dùng tham chiếu có cấu trúc bên ngoài bảng, bạn cần dùng tham chiếu có cấu trúc đầy đủ tiêu chuẩn.
Ví dụ về dùng tham chiếu có cấu trúc
Dưới đây là một số cách dùng tham chiếu có cấu trúc.
| Tham chiếu có cấu trúc này: | Tham chiếu đến: | Đó là phạm vi ô: |
|---|---|---|
| =DeptSales[[#All],[Doanh Số]] | Tất cả các ô trong cột Số lượng Doanh số. | C1:C8 |
| =DeptSales[[#Headers],[% Tiền hoa hồng]] | Tiêu đề cột % Tiền hoa hồng. | D1 |
| =DeptSales[[#Totals],[Khu vực]] | Tổng của cột Khu vực. Nếu không có hàng Tổng thì nó trả về null. | B8 |
| =DeptSales[[#All],[Doanh Số]:[% Tiền hoa hồng]] | Tất cả các ô trong Doanh số Amount và % Tiền hoa hồng. | C1:D8 |
| =DeptSales[[#Data],[% Commission]:[Commission Amount]] | Chỉ dữ liệu của các cột % Tiền hoa hồng và Số tiền Hoa hồng. | D2:E7 |
| =DeptSales[[#Headers],[Region]:[Commission Amount]] | Chỉ các tiêu đề của cột giữa Khu vực và Số tiền Hoa hồng. | B1:E1 |
| =DeptSales[[#Totals],[Doanh Số]:[Số Tiền Hoa hồng]] | Tổng của cột Doanh số đến Số tiền Hoa hồng. Nếu không có hàng Tổng thì nó trả về null. | C8:E8 |
| =DeptSales[[#Headers],[#Data],[% Tiền hoa hồng]] | Chỉ tiêu đề và dữ liệu % Hoa hồng. | D1:D7 |
| =DeptSales[[#This hàng], [Số tiền Hoa hồng]] hoặc =DeptSales[@Commission Amount] |
Ô ở giao điểm của hàng hiện tại và cột Số tiền Hoa hồng. Nếu được sử dụng trong cùng hàng với tiêu đề hoặc hàng tổng, hàm này sẽ trả về lỗi #VALUE! . Nếu bạn nhập dạng dài hơn của tham chiếu có cấu trúc này (Hàng #This) vào bảng có nhiều hàng dữ liệu, Excel sẽ tự động thay thế bằng dạng ngắn hơn (@). Cả hai đều hoạt động như nhau. |
E5 (nếu hàng hiện tại là 5) |
Chiến lược để làm việc với các tham chiếu có cấu trúc
Hãy cân nhắc những điều sau khi bạn làm việc với các tham chiếu có cấu trúc.
Dùng Tự động Điền Công thức Bạn có thể thấy rằng việc sử dụng tính năng Tự động Hoàn tất Công thức rất hữu ích khi bạn nhập các tham chiếu có cấu trúc và để đảm bảo dùng đúng cú pháp. Để biết thêm thông tin, hãy xem mục Sử dụng Tự động Điền Công thức.
Quyết định có tạo các tham chiếu có cấu trúc cho bảng trong bán lựa chọn hay không Theo mặc định, khi bạn tạo công thức, việc chọn phạm vi ô trong bảng sẽ chọn một nửa các ô và tự động nhập tham chiếu có cấu trúc thay vì phạm vi ô trong công thức. Hành vi bán chọn này giúp việc nhập tham chiếu có cấu trúc dễ dàng hơn nhiều. Bạn có thể bật hoặc tắt hành vi này bằng cách chọn hoặc xóa hộp kiểm Sử dụng tên bảng trong công thức trong hộp thoạiTùy chọn>Tệp>Công thức>Làm việc với công thức.
Dùng sổ làm việc có nối kết ngoài đến bảng Excel trong các sổ làm việc khác Nếu một sổ làm việc chứa một nối kết bên ngoài đến một bảng Excel trong một sổ làm việc khác, thì sổ làm việc nguồn được nối kết đó phải được mở trong Excel để tránh lỗi #REF! trong sổ làm việc đích có chứa nối kết. Nếu bạn mở sổ làm việc đích trước và lỗi #REF! xuất hiện, chúng sẽ được giải quyết nếu sau đó bạn mở sổ làm việc nguồn. Nếu bạn mở sổ làm việc nguồn trước, bạn sẽ không thấy mã lỗi nào.
Chuyển đổi một phạm vi thành bảng và một bảng thành một phạm vi Khi bạn chuyển đổi bảng thành một phạm vi, tất cả các tham chiếu ô sẽ thay đổi thành tham chiếu kiểu A1 tuyệt đối tương đương của chúng. Khi bạn chuyển đổi một phạm vi thành bảng, Excel sẽ không tự động thay đổi bất kỳ tham chiếu ô nào của phạm vi này thành các tham chiếu có cấu trúc tương đương của chúng.
Tắt tiêu đề cột Bạn có thể bật hoặc tắt tiêu đề cột bảng từ tab >Thiết kế Bảng, Hàng Tiêu đề. Nếu bạn tắt tiêu đề cột bảng, tham chiếu có cấu trúc dùng tên cột sẽ không bị ảnh hưởng và bạn vẫn có thể dùng chúng trong công thức. Các tham chiếu có cấu trúc tham chiếu trực tiếp đến tiêu đề bảng (ví dụ: =DeptSales[[#Headers],[%Commission]]) sẽ dẫn đến #REF.
Thêm hoặc xóa cột và hàng vào bảng Vì phạm vi dữ liệu bảng thường thay đổi, tham chiếu ô cho tham chiếu có cấu trúc sẽ tự động điều chỉnh. Ví dụ: nếu bạn sử dụng tên bảng trong công thức để đếm tất cả các ô dữ liệu trong bảng, sau đó thêm một hàng dữ liệu, tham chiếu ô sẽ tự động điều chỉnh.
Đổi tên bảng hoặc cột Nếu bạn đổi tên cột hoặc bảng, Excel sẽ tự động thay đổi cách sử dụng tiêu đề bảng và cột đó trong tất cả các tham chiếu có cấu trúc được sử dụng trong sổ làm việc.
Di chuyển, sao chép và điền các tham chiếu có cấu trúc Tất cả các tham chiếu có cấu trúc vẫn giữ nguyên khi bạn sao chép hoặc di chuyển một công thức sử dụng một tham chiếu có cấu trúc.
Lưu ý
Sao chép tham chiếu có cấu trúc và điền tham chiếu có cấu trúc không giống nhau. Khi sao chép, tất cả các tham chiếu có cấu trúc vẫn giữ nguyên, trong khi bạn điền một công thức, các tham chiếu có cấu trúc đủ điều kiện điều chỉnh mã xác định cột như một chuỗi như đã tóm tắt trong bảng sau.
| Nếu hướng tô là: | Và trong khi đổ đầy, bạn nhấn: | Sau đó: |
|---|---|---|
| Lên hoặc xuống | Không gì cả | Không có điều chỉnh mã xác định cột. |
| Lên hoặc xuống | Ctrl | Mã xác định cột điều chỉnh như một chuỗi. |
| Bên phải hoặc trái | Không có | Mã xác định cột điều chỉnh như một chuỗi. |
| Lên, xuống, phải hoặc trái | Shift | Thay vì ghi đè giá trị trong các ô hiện tại, các giá trị ô hiện tại được di chuyển và các mã xác định cột được chèn vào. |
Bạn cần thêm trợ giúp?
Bạn luôn có thể hỏi chuyên gia trong Cộng đồng kỹ thuật Excel hoặc nhận hỗ trợ trong Cộng đồng.
Chủ đề liên quan
Tổng quan về các bảng Excel
Tạo và định dạng bảng
Tính tổng dữ liệu trong bảng Excel
Định dạng bảng Excel
Đổi kích cỡ cho bảng bằng cách thêm hoặc loại bỏ hàng và cột
Lọc dữ liệu trong một dải ô hoặc bảng
Chuyển đổi bảng thành phạm vi
Các vấn đề tương thích liên quan đến bảng Excel
Xuất bảng Excel sang SharePoint
Tổng quan về các công thức trong Excel