Tìm hiểu tất tần tật chủ đề Tables trong Excel
Trong bài viết này chúng ta sẽ tìm hiểu tất tần tật chủ đề Tables trong Excel nhé!
Note: Bạn có thể kéo xuống dưới cùng để download tài liệu của bài viết này.
Tables
Chúng ta sẽ cùng tìm hiểu về cách chèn (insert), sắp xếp (sort), lọc (filter) cũng như cách hiển thị tổng hàng (total row) ở cuối mỗi tables.
Insert a Table
Để insert một tables, bạn hãy thực hiện các bước sau:
- Click vào ô đơn bất kỳ nào bên trong tập dữ liệu.
- Trên tab Insert, trong nhóm Tables, click Table.
- Excel tự động chọn dữ liệu cho bạn. Kiểm tra "My table has headers" và click OK.
Kết quả: Excel đã tạo ra cho bạn một tables khá là độc đáo. Tuy nhiên, bạn nhìn vào sẽ thấy nó vẫn là một phạm vi dữ liệu bình thường, nhưng chỉ với một cú click chuột, bạn sẽ thấy được nhiều tính năng hay và mạnh mẽ đấy.
Sort a Table
Để sắp xếp theo Last Name đầu tiên và Sales thứ hai, bạn hãy thực hiện các bước sau.
- Click vào mũi tên bên cạnh Sales và click Sort Smallest to Largest.
- Click vào mũi tên bên cạnh Last Name và click Sort A to Z.
Kết quả:
Filter a Table
Để filter a table, bạn hãy thực hiện các bước sau.
- Click vào mũi tên bên cạnh Country và chỉ kiểm tra USA.
Kết quả:
Total Row
Để hiển thị total row ở cuối table, bạn hãy thực hiện các bước sau.
- Đầu tiên, chọn một ô bên trong table. Tiếp theo, trên tab Table Design, trong nhóm Table Style Options, hãy kiểm tra Total Row.
Kết quả:
- Click vào ô bất kỳ nào trong hàng cuối cùng để tính Total (Average, Count, Max, Min, Sum,...) của một column. Ví dụ: tính tổng của Sales column.
Lưu ý: trong formula bar, bạn hãy xem cách Excel sử dụng hàm SUBTOTAL để tính tổng. 109 là đối số cho Sum nếu bạn sử dụng hàm SUBTOTAL. Excel sử dụng hàm này (chứ không phải hàm SUM tiêu chuẩn) để tính toán chính xác tổng bảng của các filtered tables.
Structured References
Khi làm việc với các tables trong Excel, bạn có thể sử dụng các structured references để làm các công thức của bạn trở nên dễ hiểu hơn.
Ví dụ, chúng ta có table sau.
- Chọn ô E1, nhập Bonus và nhấn Enter. Excel sẽ tự động định dạng column cho bạn.
- Chọn ô E2 và gõ
= 0.02 * [
- Danh sách các structured references (the columns) sẽ xuất hiện. Chọn Sales từ danh sách.
- Đóng lại bằng dấu ngoặc vuông và nhấn Enter.
Kết quả: Excel sẽ tự động sao chép công thức xuống column cho bạn.
Lưu ý: click AutoCorrect Options và click Undo Calculated Column để insert công thức vào ô E2.
Để tham chiếu đến table này trong một công thức, bạn hãy thực hiện các bước sau.
- Đầu tiên, bạn hãy chọn một ô bên trong bảng. Tiếp theo, trên tab Table Design, trong nhóm Properties, chúng ta có thể thấy Table1 là tên của table này.
- Chọn ô E18 và nhập công thức hiển thị bên dưới.
Giải thích: công thức này sẽ tính tổng của Sales column. Enter =SUM(Table1[ và Excel sẽ cung cấp cho bạn danh sách các structured references mà bạn có thể sử dụng. Công thức này tự động cập nhật khi bạn thêm hoặc xóa bản ghi.
Table Styles
Định dạng nhanh một loạt các ô bằng cách chọn Table Styles. Bạn cũng có thể tạo Table Styles của riêng mình. Định dạng nhanh một ô bằng cách chọn cell style.
- Nhấp vào ô đơn bất kỳ nào bên trong tập dữ liệu.
- Trên tab Home, trong nhóm Styles, click Format as Table.
- Chọn table style.
Lưu ý: click New Table Style để tạo bảng của riêng bạn hoặc click chuột phải vào table style và click Duplicate để tạo table style mới tương tự table style hiện có. Việc sửa đổi table style tùy chỉnh ảnh hưởng đến tất cả các bảng trong sổ làm việc sử dụng table style đó. Điều này sẽ giúp bạn tiết kiệm được rất nhiều thời gian.
- Excel tự động chọn dữ liệu cho bạn. Kiểm tra "My table has headers" và click OK.
Kết quả:
Để chuyển đổi bảng này trở lại một phạm vi ô bình thường (và giữ nguyên định dạng), bạn hãy thực hiện các bước sau.
- Đầu tiên, bạn hãy chọn một ô bên trong bảng. Tiếp theo, trên tab Table Design, trong nhóm Tools, click Convert to Range.
Kết quả:
Lưu ý: để loại bỏ table style, hãy chọn phạm vi ô, trên tab Home, trong nhóm Styles group, click Normal.
Quick Analysis
Sử dụng công cụ Quick Analysis trong Excel để giúp phân tích nhanh dữ liệu của bạn. Nó sẽ nhanh chóng tính tổng, insert tables và nhanh chóng áp dụng định dạng có điều kiện,...
Totals
Thay vì hiển thị một total row ở cuối Excel table, bạn hãy sử dụng công cụ Quick Analysis để nhanh chóng tính tổng bằng cách:
- Chọn một range of cells và click nút Quick Analysis.
- Ví dụ: click Totals và click Sum để tính tổng các số trong mỗi cột.
Kết quả:
- Chọn phạm vi A1: D7 và thêm một cột có running total.
Lưu ý: total rows có màu xanh lam và total columns có màu vàng cam.
Tables
Sử dụng tables trong Excel để sort, filter và summarize dữ liệu. Pivot table trong Excel cho phép bạn trích xuất ý nghĩa từ một tập dữ liệu lớn và chi tiết.
- Chọn một range of cells và click nút Quick Analysis.
- Để quickly insert một table, click Tables và click Table.
- Download tệp Excel (phía bên phải của trang này) và mở sheet thứ hai.
- Click vào ô đơn bất kỳ nào bên trong tập dữ liệu.
- Nhấn CTRL + q. Phím tắt này chọn toàn bộ tập dữ liệu và mở công cụ Quick Analysis.
- Để nhanh chóng insert một pivot table, click Tables và click một trong các ví dụ về pivot table.
Formatting
Data bars, color scales và bộ biểu tượng trong Excel sẽ giúp bạn dễ dàng hình dung các giá trị trong phạm vi một ô.
- Chọn một range of cells và click nút Quick Analysis.
- Để nhanh chóng thêm data bars, click Data Bars.
Lưu ý: thanh dài hơn sẽ thể hiện giá trị cao hơn.
- Để nhanh chóng thêm color scale, click Color Scale.
Lưu ý: bóng của màu đại diện cho giá trị trong ô.
- Để nhanh chóng thêm một icon set, click Icon Set.
Lưu ý: mỗi biểu tượng sẽ đại diện cho một phạm vi giá trị.
- Để nhanh chóng đánh dấu các ô greater than a value, click Greater Than.
- Nhập giá trị 100 và chọn formatting style.
- Click OK.
Kết quả: Excel sẽ đánh dấu các ô greater than 100.
Charts
Bạn có thể sử dụng công cụ Quick Analysis để tạo nhanh biểu đồ. Tính năng Recommended Charts phân tích dữ liệu sẽ đề xuất cho bạn các biểu đồ hữu ích.
- Chọn một range of cells và click nút Quick Analysis.
- Ví dụ: click Charts và click Clustered Column để tạo clustered column chart.
Lưu ý: click More để xem thêm các biểu đồ được đề xuất.
Sparklines
Sparklines trong Excel là đồ thị sẽ nằm gọn trong một ô. Các Sparklines rất hay vì chúng sẽ hiển thị các xu hướng.
- Download tệp Excel (phía bên phải của trang này) và mở sheet thứ ba.
- Chọn phạm vi A1: F4 và click vào nút Quick Analysis.
- Ví dụ: click Sparklines và click Line để insert sparklines.
Kết quả tùy chỉnh:
Merge Tables
Bạn có thể sử dụng bảng và hàm Vlookup trong Excel để nhanh chóng hợp nhất hai bảng. Thủ thuật của Excel sẽ giúp bạn lên một tầm cao mới.
Mục tiêu của chúng ta là tạo một bảng bao gồm danh sách các thành phố, quốc gia và mã quốc gia.
Đầu tiên, insert two tables.
- Chọn một ô trong phạm vi đầu tiên.
- Trên tab Insert, trong nhóm Tables, click Table.
- Excel tự động chọn dữ liệu cho bạn. Click OK.
- Lặp lại các bước này cho phạm vi thứ hai.
Sẽ cho ra kết quả:
Bây giờ, chúng ta sẽ đến với phần quan trọng nhất.
- Chọn ô C2 và nhập
= VLOOKUP ( - Nhập argument đầu tiên bằng cách chọn ô B2. Excel sẽ thay thế reference ô này bằng [@Country] vì chúng ta đang làm việc với các bảng ở đây. Đây được gọi là structured reference.
- Nhập argument thứ hai bằng cách chọn phạm vi E2: F6. Excel thay thế tham chiếu phạm vi này bằng Table2.
- Sử dụng 2 cho argument thứ ba vì các codes nằm trong column thứ hai của bảng màu đỏ.
- Sử dụng FALSE cho argument thứ tư để tìm ra một kết quả chính xác.
- Nhấn Enter.
Lưu ý: Excel đã tự động sao chép hàm VLOOKUP sang các ô khác. Điều đó sẽ giúp bạn tiết kiệm được thời gian.
- Nhập một thành phố và quốc gia mới. Excel sẽ tự động tra cứu mã quốc gia tương ứng cho bạn.
Kết luận: Tables có thể làm cho các công thức VLOOKUP trở nên dễ dàng hơn rất nhiều. Bạn hãy cùng thử nghiệm bằng cách tải tệp Excel xuống và nhập một thành phố và quốc gia mới.
Tài liệu của series
Để chuẩn bị cho việc thực hành, bạn tải tài liệu của series bằng cách điền vào form bên dưới nhé!
Chúc bạn có trải nghiệm học tập vui vẻ tại 200Lab!