Trong thế giới kỹ thuật số ngày nay, Microsoft Excel đã trở thành công cụ không thể thiếu cho hàng triệu người dùng, từ sinh viên, nhân viên văn phòng cho đến các chuyên gia dữ liệu. Tuy nhiên, việc sử dụng các tính năng nâng cao của Excel, đặc biệt là bảng Excel (Excel Tables), có thể thay đổi hoàn toàn cách bạn tương tác và khai thác dữ liệu. Ngay cả khi bạn nghĩ mình đã nắm rõ mọi thứ về bảng trong Excel, bài viết này của tincongnghemoi.com hy vọng sẽ mang đến những mẹo và thủ thuật bổ sung, giúp bạn tối ưu hóa công việc và thống trị dữ liệu hiệu quả hơn.
Biến Dữ Liệu Thô Thành Bảng Excel Chuyên Nghiệp
Trước khi bạn chuyển đổi dữ liệu thành bảng Excel, hãy đảm bảo rằng hàng đầu tiên của bạn có các tiêu đề cột rõ ràng. Điều này sẽ giúp bạn dễ dàng quản lý và sử dụng dữ liệu sau này.
Dữ liệu thô trong Excel trước khi chuyển đổi thành bảng, làm nổi bật tiêu đề cột.
Tiếp theo, hãy chọn tất cả các ô bạn muốn chuyển đổi, bao gồm cả tiêu đề cột. Hoặc, nếu vùng dữ liệu của bạn liền kề (nghĩa là tất cả các ô nằm cạnh nhau mà không có cột hoặc hàng trống), bạn chỉ cần chọn một ô bất kỳ trong vùng dữ liệu đó. Sau đó, trên thanh Ribbon, vào thẻ Insert và nhấn vào nút “Table“, hoặc sử dụng phím tắt quen thuộc trong Microsoft Excel là Ctrl+T.
Chọn ô dữ liệu trong Excel và nhấn nút "Table" trên thẻ Insert để tạo bảng.
Lúc này, hộp thoại Create Table sẽ xuất hiện. Bạn hãy kiểm tra kỹ xem phạm vi dữ liệu đã được chọn đúng chưa, đảm bảo ô “My Table Has Headers” (Bảng của tôi có tiêu đề) đã được đánh dấu, và sau đó nhấn “OK“.
Hộp thoại Create Table trong Excel, đảm bảo chọn "My Table Has Headers" trước khi xác nhận tạo bảng.
Giờ đây, dữ liệu của bạn đã được định dạng thành một bảng Excel hoàn chỉnh, với các nút bộ lọc được thêm vào các tiêu đề cột một cách tự động và chuyên nghiệp.
Một bảng dữ liệu đã được định dạng chuẩn trong Microsoft Excel với các nút lọc.
Tùy Biến Thiết Kế Bảng Excel Để Tối Ưu Hiển Thị
Sau khi tạo bảng Excel, bước tiếp theo là điều chỉnh bố cục của nó. Khi bạn chọn bất kỳ ô nào trong bảng vừa tạo, thẻ Table Design (Thiết kế Bảng) sẽ tự động xuất hiện trên thanh Ribbon. Hãy mở thẻ này để khám phá các tùy chọn thiết kế đa dạng.
Chọn một ô trong bảng Excel để mở thẻ "Table Design" và tùy chỉnh thiết kế bảng.
Đầu tiên, bạn có thể nhấn vào mũi tên xuống trong nhóm Table Styles (Kiểu Bảng) để chọn từ các tùy chọn bố cục khác nhau. Trong ví dụ này, chúng tôi đã chọn kiểu “medium plum” để làm nổi bật bảng.
Mở rộng tùy chọn "Table Styles" trong thẻ Table Design để thay đổi giao diện bảng Excel.
Tiếp theo, hãy chuyển đến nhóm Table Style Options (Tùy chọn Kiểu Bảng) trong thẻ Table Design để xem một loạt các hộp kiểm tiện ích, giúp bạn tùy chỉnh hiển thị của bảng dữ liệu Excel.
Nhóm tùy chọn "Table Style Options" trên thẻ Table Design của Excel để bật/tắt các tính năng hiển thị.
- Header Row (Hàng Tiêu đề): Nếu bạn đã đánh dấu “My Table Has Headers” khi tạo bảng, hãy giữ nguyên tùy chọn này.
- Total Row (Hàng Tổng cộng): Đánh dấu tùy chọn này sẽ thêm một hàng ở cuối bảng, nơi bạn có thể tính tổng các cột.
- Banded Rows (Hàng Sọc): Kích hoạt tùy chọn này để dễ dàng đọc qua các hàng trong bảng. Điều này đặc biệt hữu ích nếu bạn có nhiều cột.
- First Column (Cột Đầu tiên): Áp dụng định dạng đặc biệt cho cột ngoài cùng bên trái của bảng, biến cột này thành tiêu đề hàng.
- Last Column (Cột Cuối cùng): Áp dụng định dạng cho cột ngoài cùng bên phải, rất tiện lợi nếu cột này chứa các số liệu tổng hợp hoặc tóm tắt.
- Banded Columns (Cột Sọc): Đánh dấu tùy chọn này để giúp người đọc dễ dàng theo dõi các cột theo chiều dọc. Lưu ý rằng bạn nên chọn hoặc Banded Rows hoặc Banded Columns—việc chọn cả hai có thể gây nhầm lẫn và làm bảng trông lộn xộn.
- Filter Button (Nút Lọc): Tùy chọn này thêm các mũi tên lọc vào hàng tiêu đề, cho phép bạn nhanh chóng sắp xếp và lọc dữ liệu.
Sau khi đánh dấu “Total Row”, bạn có thể quyết định cột nào sẽ được tính tổng bằng cách chọn ô liên quan trong hàng tổng, nhấn mũi tên xuống và chọn một trong các tùy chọn tổng hợp (ví dụ: Sum, Average, Count).
Chọn loại tính toán tổng hợp từ menu thả xuống của hàng Total Row trong bảng Excel.
Đặc biệt, nếu bạn áp dụng bộ lọc cho một trong các cột, chỉ những giá trị đang hiển thị trong bảng mới đóng góp vào tổng số trong hàng Total Row, mang lại cái nhìn linh hoạt về dữ liệu đã lọc.
Một điều chỉnh cuối cùng và cực kỳ quan trọng bạn nên thực hiện trong thẻ Table Design là đặt tên cho bảng Excel của mình. Theo mặc định, các bảng Excel thường được đặt tên là Table1, Table2, Table3, v.v. Tuy nhiên, việc thay đổi tên bảng sẽ giúp trang tính của bạn dễ điều hướng hơn, hỗ trợ tốt hơn cho người dùng màn hình đọc và làm cho các công thức trở nên dễ hiểu hơn nếu chúng tham chiếu đến dữ liệu trong bảng.
Để đổi tên bảng, hãy chuyển đến nhóm Properties (Thuộc tính) của thẻ Table Design và thay thế tên mặc định bằng một cái tên có ý nghĩa hơn, ví dụ như “Employee_Data”.
Bảng Excel được đổi tên thành "Employee_Data" để dễ quản lý và tham chiếu trong công thức.
Lưu ý rằng tên bảng 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. Các ký tự còn lại có thể là chữ cái, số, dấu chấm hoặc dấu gạch dưới. Tên bảng cũng phải là duy nhất, không được chứa dấu cách và không được trùng với tên tham chiếu ô hiện có (ví dụ: A1).
Lợi Ích Vượt Trội: Không Cần “Đóng Băng Hàng” Với Bảng Excel
Khi làm việc với các tập dữ liệu lớn trong Excel, bạn có thể đã quen với việc đóng băng hàng đầu để khi cuộn xuống, các tiêu đề cột vẫn hiển thị.
Tuy nhiên, một trong nhiều lợi ích tuyệt vời của việc định dạng dữ liệu dưới dạng bảng Excel là bạn không cần phải thực hiện bước này. Thay vào đó, miễn là bạn đã chọn “My Table Has Headers” trong hộp thoại Create Table và một trong các ô trong bảng được chọn, khi bạn cuộn xuống, các chữ cái đại diện cho các cột phía trên bảng tính (A, B, C, v.v.) sẽ tự động biến thành tiêu đề cột của bảng bạn. Điều này hoạt động ngay cả khi các tiêu đề cột không nằm ở hàng đầu tiên của trang tính.
Khi cuộn xuống, các tiêu đề cột của bảng Excel tự động thay thế tiêu đề chữ cái mặc định.
Đặc biệt, các tiêu đề cột này cũng hiển thị các nút bộ lọc, nghĩa là bạn không cần phải cuộn lên đầu trang tính để thay đổi cách bảng hiển thị, tiết kiệm thời gian đáng kể.
Tận Dụng Sức Mạnh Bộ Lọc Để Khai Thác Dữ Liệu Hiệu Quả
Chúng tôi đã đề cập đến các nút bộ lọc trong bảng Excel nhiều lần trong hướng dẫn này, và đó là vì chúng thực sự là một tính năng giúp tiết kiệm thời gian đáng kể. Nói tóm lại, các nút bộ lọc giúp bạn dễ dàng thu hẹp dữ liệu xuống chỉ còn những thông tin liên quan nhất, giúp bạn không phải tìm kiếm thủ công qua hàng và cột của mình.
Các nút bộ lọc cung cấp các tùy chọn khác nhau tùy thuộc vào loại dữ liệu bạn có trong mỗi cột. Ví dụ, nút bộ lọc cho cột D—chứa văn bản—cung cấp nhiều bộ lọc văn bản khác nhau, như tìm kiếm các giá trị bắt đầu hoặc kết thúc bằng các ký tự nhất định.
Tùy chọn lọc văn bản trong bộ lọc của bảng Microsoft Excel.
Mặt khác, khi tôi nhấn vào nút bộ lọc trong một cột chứa số, các bộ lọc sẽ thay đổi tương ứng, cung cấp các tùy chọn như lọc theo giá trị lớn hơn, nhỏ hơn, hoặc nằm trong một phạm vi cụ thể.
Tùy chọn lọc số trong bộ lọc của bảng Microsoft Excel.
Ngoài các tùy chọn để sắp xếp cột theo thứ tự tăng dần hoặc giảm dần, hoặc lọc theo màu sắc, còn có một trường tìm kiếm trong danh sách thả xuống của bộ lọc, giúp bạn tăng tốc quá trình lọc hơn nữa. Bạn chỉ cần gõ một phần giá trị mà mình muốn tìm và Excel sẽ hiển thị các kết quả phù hợp.
Ô tìm kiếm trong bộ lọc của bảng Excel giúp nhanh chóng tìm kiếm dữ liệu.
Nhấn “OK” sau khi bạn đã nhập truy vấn tìm kiếm bộ lọc của mình và đánh dấu hoặc bỏ đánh dấu các tùy chọn liên quan để áp dụng bộ lọc.
Một mẹo nhỏ khác là lọc một cột dựa trên một giá trị đã chọn. Trong ví dụ này, tôi chỉ muốn hiển thị nhân viên trong bộ phận điện tử. Để làm điều này, tôi có thể nhấp chuột phải vào một ô chứa từ “Electronics” và trong menu Filter (Bộ lọc), nhấn “Filter By Selected Cell’s Value” (Lọc theo Giá trị của Ô đã chọn).
Tùy chọn "Filter By Selected Cell's Value" để lọc dữ liệu dựa trên giá trị của ô được chọn trong bảng Excel.
Nâng Cao Khả Năng Lọc Với Slicers Trong Excel
Trong khi các nút bộ lọc là những cách tuyệt vời để tổ chức và sắp xếp dữ liệu, một cách thậm chí còn dễ dàng hơn để làm điều này là thêm Slicers, đặc biệt nếu một cột cụ thể có nhiều khả năng được lọc hơn các cột khác. Slicers cung cấp một giao diện trực quan và tương tác, giúp việc lọc dữ liệu trở nên nhanh chóng và thân thiện với người dùng.
Trong ví dụ này, giả sử CEO của bạn quan tâm nhất đến lợi nhuận của nhân viên trong các bộ phận khác nhau và những người nào là ứng cử viên tốt nhất để thăng chức. Bạn có thể làm cho công việc của CEO dễ dàng hơn nhiều bằng cách thêm Slicers cho ba cột này.
Với bất kỳ ô nào trong bảng đã được chọn, hãy nhấn “Insert Slicer” trong thẻ Table Design trên thanh Ribbon.
Nút "Insert Slicer" trong thẻ Table Design của Excel, dùng để thêm công cụ lọc Slicer.
Tiếp theo, đánh dấu các cột mà bạn muốn thêm Slicers, và nhấn “OK“.
Chọn các tiêu đề cột mong muốn trong hộp thoại "Insert Slicers" để tạo Slicer trong Excel.
Bây giờ, bạn có thể nhấn và kéo các Slicers để định vị lại hoặc thay đổi kích thước chúng, hoặc mở thẻ “Slicer” trên thanh Ribbon để xem thêm các tùy chọn. Bạn cũng có thể nhấp chuột phải vào một Slicer và nhấn “Slicer Settings” để tùy chỉnh thêm công cụ lọc nâng cao này, ví dụ như thay đổi tên hiển thị, số cột hay ẩn/hiện các mục trống.
Tùy chọn "Slicer Settings" khi nhấp chuột phải vào Slicer để tùy chỉnh công cụ lọc này trong Excel.
Bạn và CEO của mình giờ đây có thể sử dụng các Slicers để lọc dữ liệu trong bảng Excel một cách nhanh chóng. Trong ví dụ này, các Slicers được đặt để hiển thị nhân viên trong bộ phận làm đẹp và quần áo có tiềm năng được thăng chức lên vị trí cao hơn, và vì các lựa chọn đó được tô màu xanh, rất dễ dàng để thấy dữ liệu đang được lọc như thế nào.
Bảng Excel đã được lọc dữ liệu dựa trên các điều kiện chọn trong Slicers, giúp hiển thị thông tin rõ ràng.
Giữ phím Ctrl khi chọn các yếu tố trong Slicer để hiển thị tất cả chúng cùng một lúc, hoặc nhấn Alt+C để xóa tất cả các lựa chọn trong Slicer.
Mở Rộng Dữ Liệu Dễ Dàng: Thêm Cột Và Hàng Trong Bảng Excel
Việc thêm một cột vào bên phải hoặc một hàng vào cuối bảng Excel đã định dạng lại trở nên cực kỳ đơn giản. Trong ví dụ này, ngay sau khi tôi nhập “Score” vào ô K1 và nhấn Enter, Excel đã tự động mở rộng kích thước bảng để bao gồm cột mới. Điều này có nghĩa là tôi không cần phải áp dụng bất kỳ định dạng thủ công nào cho dữ liệu mới.
Thêm một cột mới "Score" vào bảng Excel, bảng sẽ tự động mở rộng định dạng.
Tương tự, khi tôi thêm một ID nhân viên khác vào ô A32, Excel không chỉ mở rộng bảng xuống dưới mà còn tự động sao chép bất kỳ công thức nào tôi đã tạo ở các hàng trước đó. Điều này giúp duy trì tính nhất quán của dữ liệu và tiết kiệm thời gian đáng kể.
Bảng Excel tự động mở rộng và áp dụng định dạng khi thêm một hàng dữ liệu mới vào cuối bảng.
Nếu bạn muốn chuẩn bị trước các cột và hàng mới trước khi thêm dữ liệu, bạn có thể nhấn và kéo “fill handle” của bảng sang phải hoặc xuống dưới. Nếu bạn sử dụng phương pháp này, hãy nhớ đặt tên cho mỗi cột mới bằng cách thêm một tiêu đề vào hàng đầu tiên.
Sử dụng fill handle của bảng Excel để kéo giãn và mở rộng bảng theo chiều ngang hoặc dọc.
Bạn cũng có thể thêm một hàng hoặc cột mới vào giữa dữ liệu hiện có bằng cách nhấp chuột phải vào tiêu đề hàng hoặc cột, và nhấn “Insert” (Chèn). Bạn có thể yên tâm rằng định dạng bảng sẽ tự động được áp dụng cho các điểm nhập dữ liệu mới này.
Chọn "Insert" từ menu chuột phải để chèn thêm một cột mới vào bảng Excel.
Lưu ý, khi nhấp “Insert” trên một cột sẽ thêm một cột mới vào bên trái, trong khi nhấp “Insert” trên một hàng sẽ thêm một hàng mới vào phía trên.
Tự Động Hóa Tính Toán Với Tham Chiếu Có Cấu Trúc Trong Bảng Excel
Một lợi ích khác của việc định dạng dữ liệu dưới dạng bảng Excel là việc thực hiện các phép tính giữa các cột trở nên dễ dàng và nhanh chóng hơn nhiều. Đây là lúc khái niệm tham chiếu có cấu trúc (structured references) phát huy tác dụng, giúp các công thức của bạn trở nên dễ đọc và linh hoạt hơn.
Hãy giả sử bạn có bảng như trong ảnh chụp màn hình dưới đây, và mục tiêu của bạn là tính toán lợi nhuận trên mỗi đơn vị bằng cách chia các giá trị trong cột Lợi nhuận (Profit) cho các giá trị trong cột Đơn vị Đã bán (Units Sold).
Bảng Excel với một cột trống "Profit Per Unit" sẵn sàng để nhập công thức tính toán.
Để làm điều này, trong ô G2, bạn chỉ cần gõ =, chọn ô F2, gõ dấu gạch chéo (/), và chọn ô E2. Khi bạn làm điều này, hãy chú ý cách Excel tự động chèn các tiêu đề cột—còn được gọi là tham chiếu có cấu trúc—vào công thức của bạn, thay vì các tham chiếu ô trực tiếp. Đây là lý do tại sao việc có các tiêu đề cột được đặt tên trong bảng của bạn lại quan trọng:
=[@Profit]/[@[Units sold]]
Ví dụ về tham chiếu có cấu trúc trong Excel: công thức chia cột Lợi nhuận cho cột Đơn vị Bán ra.
Các dấu ngoặc vuông là cách Excel chỉ ra rằng nó đang sử dụng một tham chiếu có cấu trúc, và ký hiệu @ (còn được gọi là toán tử giao điểm) có nghĩa là phép tính sẽ được áp dụng tương ứng cho từng hàng trong bảng.
Khi bạn nhấn Enter, các ô còn lại trong cột Lợi nhuận trên mỗi Đơn vị sẽ tự động áp dụng công thức, điền đầy đủ kết quả một cách thần kỳ.
Cột "Profit Per Unit" trong bảng Excel đã được điền đầy đủ bằng công thức sử dụng tham chiếu có cấu trúc.
Tham chiếu có cấu trúc dễ tạo, dễ đọc và dễ phân tích hơn so với các tham chiếu trực tiếp, và chúng duy trì liên kết với các cột liên quan, ngay cả khi các cột bổ sung được thêm vào. Hơn nữa, vì toàn bộ cột được tham chiếu trong công thức bằng tiêu đề của nó, phép tính sẽ tự động áp dụng cho bất kỳ hàng mới nào bạn thêm vào.
Ấn tượng hơn nữa, nếu bạn đổi tên một trong các tiêu đề cột, tham chiếu có cấu trúc sẽ tự động điều chỉnh để phù hợp với thay đổi này. Trong ví dụ này, cột “Units Sold” đã được đổi tên thành “Units”, và công thức trong cột “Profit Per Unit” phản ánh cập nhật này.
Tham chiếu có cấu trúc trong Excel tự động cập nhật khi tên cột được thay đổi, thể hiện tính linh hoạt.
Tham chiếu có cấu trúc hoạt động dù bạn đang làm việc bên trong hay bên ngoài bảng, nghĩa là bạn có thể dễ dàng tham chiếu bảng và các cột của nó từ bất kỳ đâu trong sổ làm việc.
Ở đây, tôi đã chèn thêm các hàng phía trên bảng của mình, vì tôi muốn tính tổng lợi nhuận ở đây thay vì ở dưới cùng để tránh phải cuộn xuống mỗi lần. Một lợi ích khác của việc thực hiện phép tính này bên ngoài bảng là nó bỏ qua mọi bộ lọc. Điều này có nghĩa là tôi có thể chọn chèn một hàng tổng ở cuối bảng chỉ tính những gì được hiển thị và một tổng lớn bên ngoài bảng tính toán tất cả dữ liệu.
Bảng Excel bắt đầu từ hàng 4, với ô tổng lớn nằm ngoài bảng, không bị ảnh hưởng bởi bộ lọc.
Bây giờ, trong ô F2, tôi sẽ gõ:
=SUM(Sau đó, khi tôi di chuột qua tiêu đề cột Lợi nhuận (Profit), con trỏ sẽ biến thành một mũi tên đen chỉ xuống, cho biết nó đã sẵn sàng để chọn toàn bộ cột.
Con trỏ chuột biến thành mũi tên đen khi rê qua tiêu đề cột trong bảng Excel để chọn toàn bộ cột cho công thức SUM.
Tại thời điểm này, khi tôi nhấp, Excel sẽ thêm một tham chiếu cột có cấu trúc vào công thức. Tuy nhiên, lần này, vì công thức nằm ngoài bảng, nó cũng tham chiếu tên bảng (Employee_Data):
=SUM(Employee_Data[Profit])Đây là lý do tại sao việc bạn đặt tên cho bảng của mình trong nhóm Properties của thẻ Table Design trên thanh Ribbon là rất quan trọng. Khi tôi đóng dấu ngoặc tròn và nhấn Enter, phép tính đã hoàn tất.
Tham chiếu có cấu trúc bên ngoài bảng Excel, sử dụng cả tên bảng và tên cột để tính toán.
Để tránh sử dụng chuột trong quá trình này, sau khi gõ =SUM(, hãy bắt đầu gõ tên bảng, và nhấn Tab khi nó xuất hiện trong danh sách gợi ý.
Gợi ý tên bảng trong Excel khi bạn nhập công thức, giúp dễ dàng tham chiếu.
Sau đó, với tên bảng đã được thêm vào công thức, thêm dấu ngoặc vuông để xem danh sách các cột trong bảng, sử dụng các phím mũi tên để chọn nhãn liên quan, và nhấn Tab một lần nữa để thêm nó vào công thức.
Gợi ý tên cột trong bảng Excel khi bạn nhập công thức, đơn giản hóa việc tạo tham chiếu có cấu trúc.
Cuối cùng, đóng dấu ngoặc vuông và dấu ngoặc tròn, và nhấn Enter.
Mở Khóa Tiềm Năng Dữ Liệu Với PivotTable Từ Bảng Excel
Một khi bạn đã tạo bảng Excel của mình, bạn có thể tiến thêm một bước nữa và chuyển đổi nó thành một PivotTable để điều chỉnh cách dữ liệu được trực quan hóa, tạo các bản tóm tắt dữ liệu toàn diện và thực hiện các phân tích phức tạp. Hơn thế nữa, bạn có thể tạo PivotCharts, rất tiện lợi nếu bạn đang tìm cách tạo một bảng điều khiển (dashboard) chứa tất cả các chỉ số hiệu suất chính của mình. Sự kết hợp giữa bảng Excel và PivotTable/PivotChart sẽ mang lại khả năng phân tích dữ liệu vô cùng mạnh mẽ, biến những tập dữ liệu khổng lồ trở nên dễ hiểu và dễ khai thác hơn bao giờ hết.
Việc nắm vững cách sử dụng bảng Excel không chỉ giúp bạn làm việc hiệu quả hơn mà còn mở ra cánh cửa đến các công cụ phân tích dữ liệu nâng cao khác. Hy vọng những chia sẻ trên đã cung cấp cho bạn cái nhìn sâu sắc và toàn diện về những lợi ích mà bảng Excel mang lại, từ việc đơn giản hóa việc nhập liệu cho đến tự động hóa các phép tính và tối ưu hóa khả năng lọc dữ liệu. Hãy áp dụng ngay những thủ thuật này để biến dữ liệu thô thành thông tin có giá trị và đưa kỹ năng Excel của bạn lên một tầm cao mới!
Hãy chia sẻ ý kiến của bạn về những tính năng này hoặc khám phá thêm các thủ thuật Excel khác tại tincongnghemoi.com!
