Excel có những tính năng mạnh mẽ nhưng thường bị bỏ qua, hoạt động như một viên ngọc quý. Nó có thể tự động hóa công việc lặp đi lặp lại, cải thiện độ chính xác và giúp quản lý báo cáo của bạn dễ dàng hơn. Khi chúng tôi tập trung cao độ vào việc xây dựng báo cáo, ngay cả những mức tăng hiệu suất nhỏ cũng có thể giúp bạn tiết kiệm hàng giờ mỗi tuần.
Trong hướng dẫn này, chúng tôi sẽ hiển thị 7 tính năng ẩn của Excel giúp tiết kiệm hàng giờ làm báo cáo.
1. Flash Fill – Định dạng lại dữ liệu ngay lập tức
Flash Fill là một công cụ nhận dạng mẫu được hỗ trợ bởi AI. Công cụ này phát hiện các mẫu trong cách bạn nhập và tự động điền vào phần còn lại của cột.
Bạn có thể sử dụng tính năng này để làm sạch hoặc chuyển đổi dữ liệu mà không cần sử dụng công thức.
- Tách họ và tên.
- Định dạng số điện thoại.
- Trích xuất mã từ văn bản.
Các bước sử dụng:
- Bắt đầu nhập mẫu bạn muốn vào cột bên cạnh dữ liệu của bạn.
- Nhập một vài ví dụ (hai ô trở lên) về phép biến đổi.
- Trong Excel 365, nó tự động phát hiện mẫu và điền vào phần còn lại của cột.
- Đối với các phiên bản cũ hơn,
- Đi tới Dữ liệu tab>> chọn Flash Fill hoặc nhấn Ctrl + E .
- Excel tự động điền toàn bộ cột.
Hãy định dạng lại tên Nhân viên bán hàng:
- Gõ Adams, John , ở hàng đầu tiên.
- Sau khi nhập hai ô trở lên, Excel sẽ tự động phát hiện mẫu và đề xuất phần còn lại của cột.
- Excel điền phần còn lại dựa trên mẫu.
Thời gian đã lưu: Báo cáo thường yêu cầu các loại định dạng như vậy và không cần bất kỳ công thức nào, báo cáo sẽ hoạt động ngay lập tức đối với các chuyển đổi một lần.
2. Định dạng số tùy chỉnh – Định dạng dữ liệu mà không thay đổi giá trị
Định dạng số tùy chỉnh có thể tạo các báo cáo có giao diện chuyên nghiệp hiển thị chính xác những gì bạn cần mà không làm thay đổi dữ liệu cơ bản.
Cú pháp cơ bản:
Positive;Negative;Zero;Text
Các bước sử dụng:
- Truy cập Trang chủ tab>> nhấp vào Biểu tượng định dạng số .
- Hoặc nhấp chuột phải>> chọn Định dạng ô .
- Bạn cũng có thể nhấn Ctrl+1 để mở Định dạng ô hộp thoại.
- Chọn Số tab>> chọn Tùy chỉnh .
- Nhập định dạng tùy chỉnh của bạn.
Hãy hiển thị lãi lỗ bằng màu sắc:
- Chọn cột Lợi nhuận.
- Mở Định dạng ô hộp thoại>> chọn Số>> chọn Tùy chỉnh .
- Chèn định dạng sau.
- Nhấp vào OK .
[Green]$#,##0;[Red]($#,##0);[Blue]$0;"N/A"
Bây giờ lợi nhuận xuất hiện màu xanh lá cây và thua lỗ xuất hiện màu đỏ, đồng thời điểm hòa vốn sẽ hiển thị màu xanh lam trong báo cáo. Định dạng này có thể thay thế nhiều cột công thức TEXT và tạo kiểu thủ công.
Ví dụ phổ biến mạnh mẽ:
- Tỷ lệ có văn bản mô tả:
0.0% "Growth";[Red]-0.0% "Decline";0% "Flat"
Định dạng này thêm văn bản mô tả cùng với màu sắc và tỷ lệ phần trăm.
- Ngày viết tắt:
Định dạng này hiển thị “24 tháng 1” thay vì “1/1/2024”
- Số lớn được đơn giản hóa:
Định dạng này hiển thị 1.500.000 là “2M”
- Văn bản có điều kiện dựa trên giá trị:
[>100]"Exceeded Target";[>50]"On Track";"Needs Attention"
Thời gian đã lưu: Bạn sẽ không cần bất kỳ công thức, màu thủ công hay thậm chí cột trợ giúp nào cho mục đích hiển thị.
3. Power Query – Tự động làm sạch dữ liệu
Power Query nhập và kết nối nhiều nguồn dữ liệu khác nhau. Mặc dù người dùng nghĩ rằng đây là một công cụ kết nối hoặc nhập dữ liệu nhưng nó cung cấp nhiều tính năng để chuyển đổi dữ liệu với giao diện thân thiện với người dùng, không cần mã hóa.
Các bước sử dụng:
- Đi tới Dữ liệu tab>> chọn Nhận dữ liệu .
- Chọn nguồn của bạn (tệp Excel, cơ sở dữ liệu, trang web, v.v.)
- Chọn nguồn dữ liệu của bạn>> nhấp vào Chuyển đổi dữ liệu .
- Sử dụng Trình soạn thảo Power Query để xóa khoảng trắng thừa, thay đổi kiểu chữ hoặc chia cột.
- Nhấp vào Đóng và tải để tải dữ liệu trong Excel và làm mới bất cứ lúc nào chỉ bằng một cú nhấp chuột.
Chuyển đổi và làm sạch thông thường:
- Đi tới Trang chủ tab>> chọn Xóa hàng >> chọn Xóa hàng trống .
- Đi tới Trang chủ tab>> chọn Tách cột >> chọnTheo dấu phân cách .
- Đi tới Chuyển đổi tab>> chọn Sử dụng hàng đầu tiên làm tiêu đề .
- Đi tới Chuyển đổi tab>> chọn Định dạng>> chọn Viết hoa từng từ .
- Đi tới Chuyển đổi tab>> chọn Loại dữ liệu (đặt Ngày, Số nguyên, Tiền tệ).
Kết hợp nhiều tệp:
Tự động hợp nhất hàng chục tệp Excel tương tự vào một báo cáo chính.
- Đi tới Dữ liệu tab>> chọn Nhận dữ liệu>> chọn Từ tệp>> chọn Từ thư mục .
- Duyệt qua Thư mục >> chọn Kết hợp các tệp>> chọn Chuyển đổi dữ liệu khi cần thiết.
Thời gian đã lưu: Loại bỏ việc dọn dẹp thủ công lặp đi lặp lại hàng tháng. Giảm 2-3 giờ hợp nhất dữ liệu hàng tháng xuống còn 5 phút thiết lập chạy tự động.
4. Công cụ phân tích nhanh – Biểu đồ, tổng số và định dạng nhanh
Công cụ Phân tích Nhanh là một công cụ hiển thị nhưng ẩn trong Excel. Nó tự động đề xuất biểu đồ, bảng và định dạng ngay lập tức dựa trên lựa chọn.
Các bước sử dụng:
- Chọn dữ liệu của bạn.
- Nhấp vào biểu tượng Phân tích nhanh (dưới cùng bên phải).
- Chọn Định dạng , Biểu đồ , Tổng số , Bảng , Đường lấp lánh .
- Khám phá tất cả các tính năng để chèn chúng vào báo cáo chỉ bằng một cú nhấp chuột.
Hãy nêu bật doanh số 10% hàng đầu:
- Chọn cột Số tiền bán hàng (ví dụ:H2:H101).
- Nhấp vào biểu tượng Phân tích nhanh .
- Chọn Định dạng>> chọn 10% hàng đầu .
Thời gian đã lưu: Nó có thể biến dữ liệu thô thành thông tin chi tiết trong vài giây mà không cần tìm kiếm lệnh. Cung cấp nhiều tính năng và tùy chọn.
5. Mảng động – Công thức tràn tức thời
Tự động điền nhiều ô bằng một công thức. Tự động cập nhật bất cứ khi nào có bất kỳ thay đổi nào xảy ra. Các hàm mảng động hiệu quả và hữu ích là UNIQUE, XLOOKUP, FILTER, SORT, TEXTSPLIT, v.v.
DUY NHẤT:
- Có được nhân viên bán hàng duy nhất ngay lập tức.
Danh sách tự động cập nhật khi có tên mới được thêm vào.
XLOOKUP cho nhiều tra cứu:
- Nhận dữ liệu bán hàng cho một sản phẩm cụ thể từ một khu vực cụ thể.
=XLOOKUP(1,(B2:B101="East")*(C2:C101="Laptop Pro 15")*(D2:D101="Computers"), I2:I101,"No match")
Công thức này nhân các mảng TRUE/FALSE để tạo ra các số 1 trong đó tất cả các tiêu chí đều khớp. Sau đó trả về đơn giá khi khu vực, sản phẩm và danh mục khớp nhau.
XLOOKUP là người anh em linh hoạt, mạnh mẽ hơn của VLOOKUP, hoạt động theo mọi hướng và xử lý nhiều tiêu chí một cách dễ dàng.
Thời gian đã lưu: Không cần phải sao chép-dán các giá trị đã lọc theo cách thủ công. Những cột này thay thế các cột trợ giúp bằng một phạm vi trực tiếp (#). Tự động cập nhật nếu có thay đổi.
6. Phân tích dữ liệu – Nhận thông tin chi tiết tức thì do AI tạo ra
Tính năng Phân tích dữ liệu được giấu trong tab Trang chủ, ngoài cùng bên phải. Tính năng này quét tập dữ liệu và tự động đề xuất biểu đồ, tóm tắt và thông tin chi tiết chính. Nó được hỗ trợ bởi công cụ AI của Excel.
Bạn có thể sử dụng nó;
- Nếu bạn có một tập dữ liệu nhưng không chắc nó kể câu chuyện gì.
- Bạn cần báo cáo nhanh mà không cần xây dựng PivotTable.
- Bạn muốn có biểu đồ sẵn sàng để sử dụng cho bản trình bày.
Các bước sử dụng:
- Nhấp vào bên trong tập dữ liệu.
- Đi tới Trang chủ tab>> chọn Phân tích dữ liệu .
- Excel gợi ý “Tổng doanh số theo khu vực” hoặc “Xu hướng bán hàng theo sản phẩm”.
- Nhấp vào Chèn để thêm vào báo cáo của bạn ngay lập tức.
Hãy phân tích dữ liệu hiện có của chúng tôi:
- Nhấp vào bất kỳ đâu trong tập dữ liệu.
- Đi tới Trang chủ tab>> chọn Phân tích dữ liệu (phía bên phải của Ribbon).
- Excel sẽ mở một khung bên với:
- PivotTable được đề xuất (ví dụ:Lợi nhuận theo Tên sản phẩm và Nhân viên bán hàng).
- Được đề xuất Biểu đồ (ví dụ:đơn giá theo ngày).
- Dựa trên dữ liệu, nó hiển thị hơn 37 bản phân tích, bao gồm hầu hết tất cả các loại phân tích.
- Nhấp vào Chèn PivotTable/Biểu đồ bên cạnh gợi ý bạn thích.
- Bạn có thể nhập bất kỳ câu hỏi nào liên quan đến báo cáo, chẳng hạn như “Tạo biểu đồ hình tròn cho nhân viên bán hàng”.
- Excel tạo ra câu trả lời ngay lập tức.
- Khám phá các câu hỏi được đề xuất.
- Excel tự động tạo báo cáo chỉ bằng một cú nhấp chuột.
Thời gian đã lưu: Ngay lập tức phát hiện các xu hướng và đề xuất phân tích mà bạn có thể không nghĩ tới. Yêu cầu không có công thức hoặc kiến thức về PivotTable. Bạn có thể nhập câu hỏi bằng ngôn ngữ tự nhiên.
7. Công cụ máy ảnh – Ảnh chụp nhanh trực tiếp các số liệu chính
Công cụ này không có trên Ribbon; bạn phải thêm nó theo cách thủ công vào Thanh công cụ truy cập nhanh. Nó có thể tạo ra một hình ảnh của một phạm vi được cập nhật tự động. Bạn có thể đặt các phần cập nhật trực tiếp của báo cáo trên trang tổng quan mà không cần liên kết phức tạp.
Bạn có thể sử dụng nó;
- Xây dựng trang tổng quan bằng biểu đồ trực tiếp.
- Hiển thị KPI từ các trang tính khác nhau trong một chế độ xem.
Các bước thêm và sử dụng:
Để thêm Máy ảnh công cụ;
- Nhấp chuột phải vào thanh công cụ>> chọn Tùy chỉnh .
- Chọn Tùy chỉnh dải băng>> chọn Máy ảnh>> nhấp vào Thêm và chèn nó vào Tab mới .
- Nhấp vào OK .
Để sử dụng công cụ;
- Chọn một dải ô.
- Chuyển đến Tab mới>> nhấp vào Máy ảnh .
- Đặt biểu tượng + khi cần thiết.
- Đã đặt biểu tượng + trong một trang tính mới.
- Mọi thay đổi về nguồn sẽ cập nhật ảnh chụp nhanh ngay lập tức.
Tiết kiệm thời gian: Cho phép bạn tập hợp các số liệu trực tiếp mà không cần liên kết phức tạp. Mang đến cho trang tổng quan một giao diện đẹp mắt vì chỉnh sửa hình ảnh dễ dàng hơn chỉnh sửa ô.
Tính năng ẩn thưởng
Nhóm và dàn ý – Mở rộng/Thu gọn các phần báo cáo:
Nó giúp lưu giữ thông tin chi tiết hàng ngày nhưng hiển thị tóm tắt theo mặc định.
- Số liệu chia nhỏ hàng tháng tính đến tổng số hàng quý.
- Ẩn các hàng chi tiết trong báo cáo quản lý.
Các bước sử dụng:
- Chọn các hàng bạn muốn nhóm.
- Đi tới Dữ liệu tab>> chọn Nhóm .
- Sử dụng +/- các nút để mở rộng hoặc thu gọn.
Kết thúc
Excel có rất nhiều tính năng; trong số tất cả các tính năng đó, 7 tính năng này sẽ tiết kiệm hàng giờ cho việc báo cáo. Bằng cách triển khai bảy tính năng Excel ẩn này, bạn sẽ chuyển đổi từ người xử lý dữ liệu thành nhà phân tích dữ liệu, tạo báo cáo không chỉ tiết kiệm thời gian mà còn cung cấp thông tin chi tiết sâu hơn và trải nghiệm người dùng tốt hơn. Riêng lẻ, có vẻ như các tính năng này sẽ giảm bớt một vài phút nhưng khi được kết hợp vào quy trình báo cáo của bạn, chúng có thể cắt giảm thời gian báo cáo từ 50% trở lên. Điều kỳ diệu thực sự nằm ở việc xác định các tác vụ lặp đi lặp lại và để Excel xử lý chúng cho bạn.
Nhận MIỄN PHÍ Bài tập Excel nâng cao có Giải pháp!