Bài viết: Tiết kiệm thời gian với Excel Table (2 người xem)

[kyo]

Tiết kiệm thời gian với Excel Table



Một tính năng được cải thiện từ Excel 2007 về sau chính là Table (các phiên bản Excel trước 2007 được gọi là List). Mục đích của Excel Table là để hỗ trợ cho việc quản lý và phân tích dữ liệu. Bằng cách lưu trữ thông tin trong những Table được định dạng nhất quán, bạn có thể dễ dàng phân loại, chọn lọc, định dạng và diễn giải hàng ngày, hàng tuần, hàng tháng sau khi tạo ra chúng.

Table có những chức năng rất kì diệu. Bạn tin không? Cùng đọc bài dưới đây để xem lý do vì sao bạn nên sử dụng và Table có thể tiết kiệm thời gian và khiến công việc của bạn trôi chảy như thế nào nhé.

Tạo Excel Table

Table thường chỉ có tiêu đề cột và không có cột trống. Để tạo Table, bạn chọn 1 ô bất kỳ trong vùng dữ liệu bạn muốn chuyển đổi vào Table, và bấm Ctrl + T.

Hãy nhìn xem, Table được bắt đầu với các tiêu đề cột từ dòng 3. Dòng 1 và 2 không phải là 1 phần của Table.

Điều kỳ diệu 1

Mỗi khi Table được thiết lập, bạn sẽ thấy định dạng được áp dụng tự động và 1 thẻ mới sẽ xuất hiện trên thanh Ribbon, gọi là "Design". Tại đây, bạn có thể chọn từ 61 kiểu khác nhau để thay đổi định dạng như hình trên, hoặc tạo kiểu khác cho riêng bạn để phù hợp với thương hiệu của chính mình.

Nếu bạn không muốn làm việc với các dữ liệu trong 1 Table, nhưng bạn lại thích các kiểu định dạng có sẵn như trên, bạn có thể chuyển đổi Table thành 1 vùng dữ liệu thông thường trong khi vẫn giữ kiểu định dạng đã áp dụng. Bạn sẽ tìm thấy tùy chọn này trên thẻ Design của Ribbon, hoặc đơn giản là click chuột phải vào bất cứ nơi nào trong Table và chọn Table -> Convert to Range.

Điều kỳ diệu 2

Bây giờ, bạn thử chèn 1 Total Row và cùng xem sự phi thường của Total Row nào.

Trông có vẻ khá bình thường, nhưng hãy nhìn vào hình dưới đây nhé. Đơn giản bạn chỉ cần chọn 1 chức năng khác nhau từ danh sách thả xuống, hoặc chọn More Functions để vào những cái khác.

- Hàm Total không cần phải tham chiếu đến Table.
- Bạn có thể chuyển đổi tắt/bật Total Row và Excel sẽ nhớ các hàm đã tồn tại trước đó.
- Khi bạn thêm dòng/cột, Total Row sẽ tự động cập nhật, bao gồm các dữ liệu mới.
- Hàm Total được sử dụng như hàm SUBTOTAL, do đó khi bạn lọc (Filter) Table của bạn, hàm Total sẽ chỉ cộng các ô có thể nhìn thấy.

Điều kỳ diệu 3: Chèn hàm vào cột mới

1/ Gõ tiêu đề cho cột mới của bạn ngay lập tức, cột mới được chèn vào. Cột mới là cột kế bên cột cuối cùng và Table sẽ tự động kết hợp cột mới vào vùng Table và áp dụng định dạng.
2/ Mẹo: Định dạng các ô trước khi bạn nhập hàm vào.
3/ Nhập hàm và bạn sẽ thấy Table tự động Copy các hàm mới và định dạng lại toàn bộ cột cho đến cuối Table. Điều này được hiểu như là AutoComplete.
4/ Thêm dòng và hàm của bạn sẽ AutoComplete để điền vào các dòng mới.
5/ Bất kỳ sự thay đổi nào đối với hàm cũng sẽ tự động truyền tời tất cả các dòng.
6/ Chú ý: hàm trong Formula Bar dưới đây không đề cập đến các ô tham chiếu. Thay vào đó sử dụng "Structured Referencing” (Tham chiếu cấu trúc), tương tự như Name vùng. Đọc Điều kỳ diệu 4 để hiểu thêm về điều này.

Cú pháp 2007:

Cú pháp 2010+:

Điều kỳ diệu 4

LÀM VIỆC VỚI HÀM TRONG TABLE

Khi bạn tạo 1 Table, Excel sẽ tự động chèn Table và mỗi cột sẽ là 1 Tham chiếu cấu trúc (Structured Reference), tương tự như 1 Name vùng
Tham chiếu cấu trúc sẽ tự động cập nhật khi bạn thêm hoặc xóa dữ liệu.
Các tên cho Table thường sẽ là "Table1", "Table2", … và các cột sẽ có tên dựa vào tiêu đề cột trong Table. Nếu bạn không có bất kỳ tiêu đề cột, Excel sẽ tự động đặt tên cột như "Column1", "Column2",…
Cũng vậy, khi bạn thêm hoặc xóa cột, Tham chiếu cấu trúc sẽ tự động cập nhật theo.

ĐỔI TÊN THAM CHIẾU CẤU TRÚC

Bạn có thể đổi tên mặc định "Table1" trên thẻ Design nằm trên thanh Ribbon hoặc trong phần Properties.

Muốn đổi tên các cột, chỉ cần gõ tên mới vào các tiêu đề cột.

CÁC CÚ PHÁP HÀM DÀNH CHO TABLE:

- Để tham chiếu toàn bộ Table : =Table1[#All]
- Để tham chiếu các tiêu đề : =Table1[#Headers]
- Để tham chiếu toàn bộ cột : =Table1[[#All],[Column1]]
- Để tham chiếu tất cả dữ liệu (không bao gồm các tiêu đề) trong Table : =Table1[#Data]
- Để tham chiếu các giá trị tiêu đề của 1 cột : =Table1[[#Headers],[Column1]]
- Để tham chiếu dòng hiện tại:
+ Excel 2007 : =Table1[[#This Row],[Column1]]
+ Excel 2010 trở đi, chỉ đơn giản : =@Column1
- Các Tham chiếu cấu trúc (Table1, #Headers, #Column1 …) cũng có thể kết hợp được với nhau, ví dụ:
=Table1[[#Headers],[#Data],[Column1]]
- ….
Chú ý: Nếu bạn đã đổi tên cho Table, bạn sẽ thay thế "Table1" trong hàm bằng tên Table của bạn. Tương tự như vậy, bạn sẽ sử dụng tên cột của bạn nếu đó không phải là mặc định Column1 …

SỬ DỤNG THAM CHIẾU CẤU TRÚC TRONG HÀM

- Cộng tất cả cột Lương : =SUM(Table1[Salary])
- Trung bình Lương : =AVERAGE(Table1[Salary])
- Cộng toàn bộ Table : =SUM(Table1)
Chú ý : các hàm trên đã loại bỏ các ô tiêu đề.
- Trích BH của NV : 10,5%
+ Excel 2007 : =Table1[[#This Row],[Salary]]*10.5%
+ Excel 2010 : =[@Salary]*0.1%
Chú ý : [#This Row] cho Excel 2007 hay [@row] cho Excel 2010 trở đi chứng tỏ rằng các hàm được áp dụng cho mỗi dòng riêng lẻ như trong ví dụ dưới đây:

Cú pháp 2007:

Cú pháp 2010+:

Muốn làm quen và nắm rõ Tham chiếu cấu trúc, bạn cần phải thực hành nhiều hơn, hơi mất thời gian 1 tí. Nhưng hãy tưởng tượng xem bạn có thể tiết kiệm được bao nhiêu thời gian 1 khi đã thuần thục và có thể viết hàm từ bất cứ nơi nào trong bảng tính, mà không cần phải qua qua lại lại giữa các sheet với nhau. Kì điệu quá, đúng không?

Điều quan trọng cần biết về Tham chiếu cấu trúc:
- Tên của Table là duy nhất cho cả bảng tính. Nếu bạn có 5 Table trong 1 bảng tính trên nhiều sheet, mỗi Table phải có tên riêng để bạn có thể tham chiếu từ bất kỳ sheet nào.
- Bạn không thể có Name vùng trùng với tên Table hoặc tên cột được.
- Bạn sẽ tìm thấy tên Table của bạn trong mục Name Manager tại thẻ Formulas. Trong đó có các Name vùng bạn đã đặt trước đó.

- Nếu tên Table hoặc tên cột được thay đổi, các hàm sẽ tự động cập nhật với các tên mới
- Tham chiếu cấu trúc rất tiện lợi khi làm việc với Data Validation (1 danh sách thả xuống), báo cáo PivotTable, và bất cứ điều gì khác phụ thuộc vào vùng Table, ví dụ như hàm, ….

Điều kỳ diệu 5 : Tự động chèn bộ lọc (Filter)

Chúng ta sẽ không bàn tới cách sử dụng bộ lọc (Filter) ở đây, nhưng đối với bạn và những người biết cách sử dụng, bạn sẽ hài lòng khi biết rằng, tất cả các hàm được sử dụng trong TOTAL ROW sẽ tự động loại trừ bất kỳ dòng nào bị ẩn bởi bộ lọc
Và bất kì dòng mới nào được thêm vào sẽ tự động được TOTAL ROW tính vào.

Hy vọng rằng bài viết trên sẽ giúp bạn có cái nhìn mới mẻ hơn về tính năng Table của Excel. Chúc bạn thành công khi tìm hiểu về nó.

Nguyễn Bảo Khanh.

Một số bài viết có liên quan:
1/ PivotTable & PivotChart 2007 - Từ căn bản đến nâng cao (phần 7)
2/ PivotTable & PivotChart 2007 - Từ căn bản đến nâng cao (phần 6)
3/ PivotTable & PivotChart - Từ căn bản đến nâng cao (phần 5)
4/ PivotTable & PivotChart - Từ căn bản đến nâng cao (phần 4)
5/ PivotTable & PivotChart - Từ căn bản đến nâng cao (phần 3)
6/ PivotTable & PivotChart - Từ căn bản đến nâng cao (phần 2)
7/ PivotTable & PivotChart - Từ căn bản đến nâng cao (phần 1)
8/ 29 cách tiết kiệm thời gian với các công thức Excel (phần 3)
9/ 29 cách tiết kiệm thời gian với các công thức Excel (phần 2)
10/ 29 cách tiết kiệm thời gian với các công thức Excel (phần 1)