6 tính năng Excel mạnh mẽ mà hầu hết mọi người không bao giờ sử dụng

Ngay cả những bảng tính đơn giản nhất - ngân sách, danh sách, trình theo dõi, v.v... - cũng có thể được hưởng lợi từ các tính năng mạnh mẽ trong Microsoft Excel mà mọi người thường tránh vì chúng có vẻ quá phức tạp. Thực tế, chúng dễ hơn bạn nghĩ và có thể giúp tiết kiệm hàng giờ làm việc.

6. Quick Analysis

Giả sử bạn có một bảng tính chứa các bản ghi doanh số. Khi bạn highlight một số ô, công cụ Quick Analysis trong Excel sẽ ngay lập tức đề xuất thực hiện các thao tác sau cho bạn:

• Tính tổng doanh thu hoặc số lượng đơn vị đã bán
• Thêm biểu đồ, bạn có thể sử dụng để trực quan hóa tổng lợi nhuận theo khu vực
• Áp dụng thang màu để làm nổi bật các đơn hàng có lợi nhuận cao nhất
• Chuyển dữ liệu thành bảng Excel để lọc và sắp xếp dễ dàng hơn

Cách thức hoạt động như sau: Khi bạn chọn bất kỳ phạm vi ô nào trong Excel, một biểu tượng nhỏ sẽ xuất hiện ở góc dưới bên phải (trông giống như một hình vuông có ba đường thẳng và một tia chớp). Nhấp vào biểu tượng này hoặc chỉ cần nhấn Ctrl + Q, Excel sẽ ngay lập tức hiển thị một menu với 5 danh mục phân tích: Formatting, Charts, Totals, Tables và Sparklines.

Trong mỗi danh mục này, bạn sẽ tìm thấy các tùy chọn được điều chỉnh phù hợp với dữ liệu cụ thể của mình. Khi đánh dấu 15 hàng với tất cả 14 cột từ bảng tính hồ sơ bán hàng của mình, Excel đã đề xuất các biểu đồ Clustered Column, Stacked Column, Clustered Bar và Stacked Bar trong danh mục Charts.

Tuy nhiên, khi thu hẹp phạm vi xuống chỉ còn các cột Ship Date và Units Sold, nó đã thay đổi đề xuất thành biểu đồ đường, biểu đồ phân tán, biểu đồ cột cụm và biểu đồ diện tích xếp chồng.

Nếu lựa chọn của bạn nhỏ hoặc đủ dễ quản lý, bạn có thể xem trước từng tùy chọn chỉ bằng cách di chuột.

Trong khi xem trước, nếu thấy thích một biểu đồ nào đó, bạn có thể nhấp vào biểu đồ đó để tạo ngay lập tức.

5. Data Validation

Hãy tưởng tượng bạn đang thu thập thư phúc đáp cho đám cưới của mình. Bạn sẽ cần khách mời xác nhận tham dự, chọn món ăn ưa thích và có thể cho biết liệu họ có đi cùng người khác hay không. Nếu không có Data Validation, bạn chắc chắn sẽ nhận được một loạt phản hồi hỗn loạn: Yes, y, Attending, Chicken, Veggie, 1, one, hoặc thậm chí là các trường hoàn toàn trống.

Dưới đây là một ví dụ về cách bạn có thể tránh được việc phải đau đầu dọn dẹp dữ liệu như vậy:

• Bôi đen cột Meal Choice và đi đến tab Data.
• Tìm Data Validation trong Data Tools. Bạn sẽ nhận ra nó bằng biểu tượng có hai hình chữ nhật, một hình chữ nhật hiển thị dấu kiểm màu xanh lá cây và hình chữ nhật còn lại hiển thị dấu lỗi màu đỏ.
• Nhấp vào đó, chọn List trong phần Allow và nhập các giá trị được phép, phân cách bằng dấu phẩy.
• Nhấn OK > Apply, tùy theo phiên bản của bạn, thế là xong!

Giờ đây, khi ai đó cố gắng chèn một bữa ăn ngoài danh sách được phép, họ sẽ nhận được thông báo lỗi.

Bạn cũng có thể tùy chỉnh thông báo lỗi đó. Thay vì thông báo lỗi chung chung của Excel, bạn có thể hiển thị một thông báo hữu ích hơn, chẳng hạn như please enter any of these: Chicken, Fish, or Vegan.

Bạn có thể thiết lập quy tắc xác thực dữ liệu cho hầu hết mọi thứ. Ví dụ, bạn có thể giới hạn cột Status chỉ ở trạng thái Open, In Progress và Complete, hoặc đảm bảo các trường Date chỉ chấp nhận các ngày trong năm hiện tại. Điều quan trọng là phải thiết lập điều này trước khi chia sẻ bảng tính với người khác.

4. PivotTables

PivotTables trong Excel trông có vẻ phức tạp, nhưng thực ra chúng là một trong những cách dễ nhất để hiểu các tập dữ liệu khổng lồ. Hầu hết mọi người tránh chúng vì họ nghĩ rằng chỉ người dùng nâng cao mới cần đến. Nhưng không phải như vậy.

Giả sử bạn có một bảng tính doanh số 30.000 hàng và sếp của bạn muốn xem tổng lợi nhuận theo khu vực.

Thay vì dành hàng giờ để viết công thức và lọc dữ liệu, bạn có thể tạo một PivotTable trong khoảng 30 giây:

• Chọn bất kỳ ô nào trong dữ liệu.
• Vào Insert > PivotTable > From Table/Range.
• Chọn New Worksheet và nhấp vào OK.
• Kéo Region vào vùng Rows và Total Profit vào vùng Values.

Ngay lập tức, bạn sẽ thấy từng vùng và tổng lợi nhuận của vùng đó được tóm tắt gọn gàng.

Nếu muốn phân tích nhiều lớp hơn, bạn cũng có thể kéo quốc gia vào vùng Rows, sau đó thêm tổng chi phí và tổng doanh thu vào vùng Values.

PivotTables không chỉ hữu ích cho dữ liệu bán hàng. Nếu đang theo dõi kết quả học tập của học sinh và muốn xem học sinh nào có kết quả tốt nhất trong mỗi môn học, bạn cũng có thể kéo các trường xung quanh. Cùng một dữ liệu có thể trả lời hàng chục câu hỏi khác nhau mà không cần tạo nhiều bảng tính hoặc vật lộn với các công thức phức tạp.

3. Flash Fill

Tính năng này về cơ bản là một cách để Excel đọc suy nghĩ của bạn. Bạn chỉ cho Excel những gì mình muốn bằng cách nhập một hoặc hai ví dụ, và Excel sẽ tự động tìm ra quy luật và hoàn thành phần còn lại.

Nếu bạn có một cột tên đầy đủ, chẳng hạn như Jessica Doe và Sarah Lee, và cần tách phần họ trong một cột riêng biệt, bạn không cần phải nhập từng tên thủ công. Chỉ cần nhập Doe vào ô bên cạnh Jessica Doe, nhấn Enter, sau đó bắt đầu nhập Lee vào ô tiếp theo. Excel sẽ nhận ra thao tác và đề nghị hoàn thành toàn bộ cột cho bạn.

Ứng dụng của Flash Fill là vô tận: Bạn có thể tách số điện thoại thành mã vùng và số, kết hợp tên và họ từ các cột riêng biệt, trích xuất tên miền email từ địa chỉ đầy đủ, hoặc thậm chí làm sạch định dạng văn bản lộn xộn.

Mẹo duy nhất để thành thạo Flash Fill là đưa ra các ví dụ tốt. Nếu hai ví dụ đầu tiên của bạn không cung cấp đủ thông tin để Excel hiểu được quy luật, chỉ cần thêm ví dụ thứ ba.

2. Formula Auditing

Tất cả chúng ta đều đã từng gặp trường hợp này: Công thức đột nhiên tạo ra một lỗi kỳ lạ và bạn băn khoăn tự hỏi liệu mình có gõ sai ở đâu đó trong hàng 247 hay không. May mắn thay, công cụ Formula Auditing của Excel có thể chỉ ra chính xác lỗi sai chỉ trong vài giây.

Giả sử bạn đang tính toán biên lợi nhuận trên hàng trăm hàng. Bạn có thể sẽ có một cột Total Margin (chia Total Profit cho Total Revenue) và một cột Adjusted Margin (nhân Total Margin với 0,9).

Mọi thứ trông có vẻ hoàn hảo cho đến khi bạn cuộn xuống và thấy các thông báo lỗi nằm rải rác trong dữ liệu. Đó chính là lúc Formula Auditing phát huy tác dụng.

Bôi đen bất kỳ ô nào hiển thị lỗi và chuyển đến tab Formulas. Nhấp vào Trace Precedents, Excel sẽ vẽ các mũi tên chỉ đến chính xác những ô nhập vào công thức bị lỗi. Theo kinh nghiệm, bạn thường sẽ phát hiện ra vấn đề ngay lập tức - có thể tổng doanh thu bằng 0, bạn đang tham chiếu đến một ô trống, hoặc ô chứa văn bản thay vì số.

Trace Dependents hoạt động ngược lại và thậm chí có thể hữu ích hơn. Bạn có thể highlight bất kỳ ô nào để kiểm tra xem các công thức nào khác dựa trên ô đó. Điều này cực kỳ hữu ích khi bạn sắp xóa hoặc sửa đổi một ô và muốn biết điều gì khác có thể bị lỗi.

Công cụ thực sự tạo nên sự khác biệt là công cụ Error Checking. Công cụ này tự động báo lỗi như chia cho 0, tham chiếu đến các ô trống hoặc công thức không khớp với mẫu trong cột của chúng.

Những công cụ này biến việc gỡ lỗi công thức Excel từ việc đoán mò khó chịu thành một quy trình liền mạch.

1. Conditional Formatting

Bảng tính chỉ là những con số vô tri cho đến khi bạn đặt chúng vào ngữ cảnh. Lấy ví dụ về bảng tính ngân sách. Bạn sẽ phải xem xét nó một lúc trước khi có thể hiểu được mình thực sự đã chi tiêu bao nhiêu trong suốt cả tháng.

Thay vì tự tay so sánh chi tiêu thực tế với ngân sách trên hai mươi hạng mục khác nhau, bạn có thể sử dụng Conditional Formatting để làm cho bảng tính của mình trở nên sống động, giúp bạn có thể ngay lập tức biết mình đã chi tiêu quá mức ở đâu và tiết kiệm được bao nhiêu. Trong ngân sách hàng tháng, có một cột Variance hiển thị sự khác biệt giữa số tiền đã lập ngân sách và số tiền thực sự đã chi tiêu. Sau đó, hãy để Conditional Formatting làm phần việc khó khăn.

Trước tiên, chọn tất cả các ô trong cột Variance và nhấp vào Conditional Formatting trên tab Home trước khi thực hiện thao tác này:

• Đối với chi tiêu vượt mức: Nhấp vào Highlight Cells Rules > Greater Than, nhập 0 vào ô và chọn màu highlight (có thể là tô màu đỏ nhạt với văn bản đỏ đậm).
• Đối với chi tiêu dưới mức: Quay lại Conditional Formatting > Highlight Cells Rules > Less Than, nhập 0 một lần nữa và chọn một màu khác.
• Đối với chi tiêu chính xác: Sử dụng Conditional Formatting > Highlight Cells Rules > Equal To, nhập 0 một lần nữa và chọn một màu khác.

Ngay lập tức, các số dương (chi tiêu vượt mức) chuyển sang màu xanh lá cây, những số âm (tiết kiệm được) chuyển sang màu đỏ và các số trùng khớp chính xác vẫn giữ nguyên màu vàng. Chỉ cần nhìn lướt qua là bạn có thể biết mọi thứ cần biết về chi tiêu hàng tháng của mình.

Data Bars cũng vô cùng hữu ích để bạn dễ dàng nhìn thấy xu hướng chi tiêu của mình. Bạn có thể áp dụng chúng vào cột Actual Spend, và mỗi ô sẽ trở thành một biểu đồ cột nhỏ. Bạn sẽ ngay lập tức thấy rằng mình chi tiêu cho thực phẩm nhiều hơn so với tiền điện nước mà không cần phải so sánh các con số.

Icon Sets cũng rất tuyệt vời để theo dõi xu hướng. Thêm chúng vào cột Change from Last Month, và bạn sẽ thấy mũi tên hướng lên khi chi tiêu tăng đáng kể, mũi tên hướng ngang khi tăng nhẹ và mũi tên hướng xuống khi giảm.

Sai lầm lớn nhất mà người mới sử dụng Định dạng có điều kiện trong Excel thường mắc phải là lạm dụng nó. Mục tiêu của bạn là làm nổi bật thông tin quan trọng, chứ không phải biến bảng tính của mình thành một cây thông Noel. Vì vậy, chỉ nên làm nổi bật những gì cần chú ý.

Thành thạo ngay cả một vài tính năng này cũng có thể thay đổi cách bạn làm việc trong Excel. Chúng không chỉ là dụng cụ điện, mà còn giúp bạn tiết kiệm thời gian và làm việc như một chuyên gia.