8 thủ thuật cho người dùng Excel cao cấp

Loạt giải pháp sau đây sẽ giúp bạn áp dụng những tính năng nâng cao để quản lý hay thực hiện các thao tác tìm kiếm dữ liệu trong bảng tính một cách hiệu quả hơn.

1. Sử dụng phím tắt để tạo bảng

Bảng (Table) là một trong những tính năng hữu ích nhất trong Excel nhằm quản lý dữ liệu theo dạng cột (Column) và hàng (Row). Bảng giúp người dùng dễ dàng sắp xếp, lọc và theo dõi dữ liệu, thêm hàng hay cột mới với định dạng tương tự như các hàng hay cột đã có. Ngoài ra, nếu thực hiện việc tạo biểu đồ từ dữ liệu trong các ô, bảng sẽ giúp biểu đồ tự động cập nhật nếu thêm hàng hay cột mới.

Nếu muốn tạo bảng dữ liệu bằng cách truyền thống là dùng thanh trình đơn ribbon của Excel, hãy nhấn thẻ Insert rồi sau đó chọn Table. Ngoài ra, còn có một cách khác đơn giản hơn là dùng phím tắt. Đó là sau khi lần đầu tiên chọn tất cả dữ liệu bằng tổ hợp phím Ctrl-A, hãy biến dữ liệu đã chọn thành dạng bảng bằng tổ hợp phím Ctrl-T.

2. Thêm tóm tắt nội dung hàng của bảng

Bạn có thể thêm một hàng tóm tắt vào bảng trong trình đơn ribbon Design của Excel bằng cách đánh dấu vào mục “Total Row”. Mặc dù được gọi là Total Row, bạn có thể chọn từ một loạt các thống kê tóm tắt, không chỉ cho biết số tổng của hàng, đếm số lượng, số chênh lệch chuẩn, số trung bình và nhiều thứ nữa.

Bạn có thể tự mình chèn thông tin này vào bảng tính bằng công thức tương ứng, thông tin Total Row sẽ được “đính kèm” vào bảng nhưng nằm ở vị trí hàng dưới cùng bất kể sau đó bạn chọn sắp xếp dữ liệu bảng của mình như thế nào. Điều này có thể khá tiện dụng nếu sau đó bạn muốn thực hiện nhiều thao tác kiểm tra dữ liệu.

Lưu ý rằng sẽ cần phải tạo ra một hàng tổng cho mỗi cột riêng biệt, đồng thời việc tạo ra con số tổng cho một cột sẽ không tự động tạo ra số tổng cho phần còn lại của bảng (vì không phải tất cả các cột đều có cùng một kiểu dữ liệu).

3. Chọn cột hay hàng dễ dàng hơn

Nếu dữ liệu nằm trong một bảng và bạn cần phải tham khảo nội dung của toàn bộ một cột bằng công thức mới, hãy nhấn chuột vào tên cột. Thao tác này sẽ giúp liên kết đến tên của toàn bộ cột được chọn. Việc này cũng sẽ hữu ích nếu sau đó bạn muốn thêm nhiều cột khác vào bảng.

Điều quan trọng là phải đảm bảo rằng con trỏ chuột có hình dạng mũi tên chĩa xuống màu đen trước khi bạn nhấn vào tên cột. Nếu con trỏ chuột trông giống như hình dấu thập thì bạn chỉ có thể chọn nội dung một ô duy nhất, không phải toàn bộ cột.

Cho dù có hay không dữ liệu trong một bảng, bạn có thể thực hiện hai thao tác chọn nhanh bằng phím tắt: Shift-Spacebar để chọn nguyên cả hàng và Ctrl-Spacebar để chọn nguyên cả cột. Lưu ý là nếu không có dữ liệu trong bảng, phần chọn sẽ đi ra ngoài phần có dữ liệu và bao gồm cả các ô trống. Với bảng dữ liệu, phần chọn sẽ chỉ trong giới hạn bảng.

Nếu muốn chọn toàn bộ cột không phải trong cùng một bảng và chỉ chọn các ô có dữ liệu trong đó, hãy đặt con trỏ chuột vào một cột bên cạnh ô có dữ liệu bất kỳ cần chọn, nhấn Ctrl-mũi tên xuống, sử dụng phím mũi tên trái hoặc phải để di chuyển đến cột mong muốn, sau đó nhấn Ctrl-Shift-mũi tên lên. Điều này có thể có ích nếu cột dữ liệu của bạn khá dài.

4. Lọc dữ liệu bảng bằng tính năng Slicer

Bảng trong Excel cung cấp trình đơn dạng mũi tên thả xuống bên cạnh mỗi tiêu đề cột để dễ dàng phân loại, tìm kiếm và lọc nội dung. Tuy nhiên, việc lọc dữ liệu với tùy chọn này khi có một lượng lớn dữ liệu có thể hơi lâu và khó khăn. Thay vào đó, bạn có thể sử dụng Slicer vốn là một tính năng được thiết kế để lọc dữ liệu dễ dàng hơn.

Ban đầu, Slicer được phát triển chỉ để sử dụng trong các bảng Pivot Table (vốn được dùng để chứa các báo cáo). Tuy nhiên, kể từ phiên bản Excel 2013, tính năng này cũng có thể làm việc trên bảng thông thường.

Để thêm một Slicer vào bảng, hãy vào thẻ Design trên thanh công cụ của Excel, chọn Insert Slicer và sau đó chọn cột mà bạn muốn lọc. Slicer sẽ hiển thị trong bảng tính, xuất hiện trong một cột rộng chỉ chứa vài mục hiển thị.

Nhưng nếu bảng tính dài và hẹp với nhiều không gian bên phải, bạn có thể thay đổi kích thước để Slicer rộng hơn đáng kể so với mặc định. Bạn có thể thêm các cột vào Slicer với các tùy chọn Slicer trên thanh công cụ.

Nếu muốn lọc nhiều mục trong Slicer, bạn có thể sử dụng tổ hợp phím Ctrl-nút chuột trái để chọn. Để xóa tất cả các bộ lọc, hãy nhấn nút Clear ở phía trên bên phải của Slicer.

5. Tạo tóm tắt cho ô có thể thay đổi khi lọc nội dung bảng

Nếu muốn tạo một ô bên ngoài bảng để tóm tắt dữ liệu trong bảng, chẳng hạn như tổng của một cột, đồng thời nếu bạn muốn rằng ô này sẽ hiển thị số tổng được tự cập nhật khi thực hiện lọc bảng thì đây vốn là vấn đề mà công thức SUM cơ bản sẽ không làm được.

Thay vào dùng hàm SUM trong ô này, hã dùng hàm AGGREGATE và sau đó ô sẽ liên kết đến các bộ lọc trong bảng. Về cơ bản, hàm AGGREGATE trả về một giá trị tổng hợp trong một danh sách hoặc cơ sở dữ liệu. Hàm AGGREGATE trong Excel đòi hỏi 3 thông số, trong đó hai thông số đầu phải ở dạng số (Number) còn thông số còn lại là dải ô mà bạn muốn lọc.

6. Sắp xếp dữ liệu trong bảng Pivot Table

Đôi khi, bạn muốn sắp xếp dữ liệu của một cột cụ thể trong bảng Pivot Table cũng như trong bảng thông thường. Nhưng không giống như bảng thông thường, bảng Pivot Table không có các trình đơn thả xuống trên mỗi cột cung cấp khả năng sắp xếp. Tuy nhiên, nếu bạn chọn mũi tên xổ xuống trên cột đầu tiên, bạn sẽ nhận được một trình đơn cho phép sắp xếp dữ liệu theo cột.

Khi sắp xếp dữ liệu trong PivotTable, hãy lưu ý rằng dữ liệu có khoảng trống ở đầu sẽ ảnh hưởng tới kết quả sắp xếp. Để có kết quả tối ưu, hãy loại bỏ mọi khoảng trống ở đầu trước khi sắp xếp dữ liệu. Bạn sẽ không sắp xếp được mục nhập văn bản có phân biệt chữ hoa chữ thường. Ngoài ra, bạn sẽ không sắp xếp được dữ liệu theo một định dạng cụ thể, chẳng hạn như màu phông của ô, theo các chỉ báo định dạng có điều kiện chẳng hạn như bộ biểu tượng.

7. “Unpivot” dữ liệu

Một số người gọi đây là định hình lại dữ liệu từ dạng “rộng” sang dạng “dài”. Trong thế giới cơ sở dữ liệu, thao tác này được gọi là “gấp”, tức là lấy dữ liệu từ các cột riêng biệt và di chuyển chúng thành các hàng. Về cơ bản, việc này ngược lại so với thao tác “Pivot” để tạo ra bảng Pivot Table. Trong một bảng Pivot Table, bạn chuyển các định dạng Categories trong một cột thành các cột riêng biệt.

Để “Unpivot” các cột, bạn cần phải sử dụng trình Query Editor trong Excel 2016. Hãy truy xuất Query Editor trong thanh ribbon Data của Excel, sau đó trong phần “Get & Transform” hãy chọn From Table. Khi trình Query Editor xuất hiện, hãy chọn các cột mà bạn muốn thao tác, nhấn vào thẻ Transform và chọn Unpivot Columns.

Sau khi thực hiện, để lưu lại các thay đổi, hãy chọn File > Close & Load (nếu muốn lưu vào vị trí mặc định trong Excel) hay chọn File > Close & Load To (nếu muốn lưu vào vị trí khác).

8. Nhập dữ liệu từ web vào Excel

Đôi khi bạn tình cờ thấy một vài bảng dữ liệu HTML được định dạng khá đẹp trên trang web và muốn sử dụng nó trong bảng tính Excel của mình. Lúc đó, bạn sẽ cần phải thực hiện một vài chỉnh sửa bổ sung để đưa dữ liệu vào biểu mẫu nhằm có thể phân tích.

Để thực hiện, hãy vào thẻ Data trong trình đơn ribbon của Excel rồi chọn New Query > From Other Sources > From Web. Tiếp theo hãy nhập vào địa chỉ URL của trang web tương ứng. Excel sẽ tìm kiếm và liệt kê những bảng HTML tương thích trong trang web đó. Bạn chỉ cần nhấn vào một bảng bất kỳ để xem trước và nhấn Load nếu muốn sử dụng bảng đó.

Sẽ có nhiều người đặt câu hỏi tại sao không sao chép và dán bảng HTML trực tiếp trong Excel. Câu trả lời là nếu dữ liệu trong bảng luôn được cập nhật, bạn có thể dễ dàng làm tươi bằng cách nhấn chuột phải vào bảng rồi chọn Refresh thay vì phải thực hiện lại các thao tác sao chép và dán dữ liệu mới.

Huy Thắng
(theo PCWorldVN)