Microsoft Excel từ lâu đã được mệnh danh là “con dao đa năng Thụy Sĩ” dành cho các chuyên gia phân tích dữ liệu. Trong khi các hàm cơ bản là nền tảng vững chắc, việc khám phá và làm chủ những hàm Excel nâng cao sẽ giúp bạn khai thác tối đa sức mạnh của công cụ này. Bằng cách áp dụng các hàm này, bạn có thể tối ưu hóa quy trình làm việc, khám phá những mẫu hình ẩn trong dữ liệu và cuối cùng là nâng cao đáng kể khả năng ra quyết định của mình.
Bài viết này sẽ giới thiệu tuyển chọn 5 hàm Excel mạnh mẽ, vượt xa những hàm SUM và AVERAGE cơ bản hàng ngày, giúp bạn nâng tầm kỹ năng thao tác và phân tích dữ liệu.
1. SUMIFS và COUNTIFS: Tính toán có điều kiện linh hoạt
SUMIFS và COUNTIFS là những hàm Excel cực kỳ giá trị cho các chuyên gia phân tích dữ liệu vì chúng cho phép thực hiện các phép tính và đếm có điều kiện dựa trên nhiều tiêu chí khác nhau. Giả sử bạn có dữ liệu bán hàng của một công ty trong một bảng tính Excel và muốn phân tích doanh số dựa trên các khu vực, danh mục sản phẩm và nhân viên bán hàng khác nhau.
Bạn có cột A chứa Khu vực (Bắc, Nam, Đông, Tây), cột B chứa Danh mục sản phẩm (Điện tử, Quần áo, Đồ gia dụng), cột C chứa Nhân viên bán hàng (Alice, Bob, Charlie) và cột D chứa Số tiền bán hàng.
Để tính tổng doanh số cho khu vực Đông, danh mục sản phẩm Điện tử và nhân viên bán hàng Alice, bạn cần thêm các tham chiếu này vào các ô G1, G2 và G3 tương ứng. Bây giờ, bạn có thể sử dụng công thức sau để hoàn thành phép tính:
=SUMIFS(D:D, A:A, G1, B:B, G2, C:C, G3)Để đếm số lượng giao dịch bán hàng khớp với cùng tiêu chí, bạn có thể sử dụng công thức =COUNTIFS(A:A, G1, B:B, G2, C:C, G3).
Nếu bảng dữ liệu của bạn ngày càng lớn, việc sử dụng tham chiếu cột như A:A sẽ đảm bảo rằng tất cả dữ liệu mới đều được bao gồm trong các phép tính.
Biểu đồ minh họa cách sử dụng hàm SUMIFS trong Excel để tính toán có điều kiện
2. FORECAST.LINEAR: Dự đoán xu hướng dữ liệu tương lai
Khi xử lý phân tích dữ liệu, đặc biệt là khi liên quan đến các xu hướng, hàm FORECAST.LINEAR của Excel (phiên bản nâng cấp của FORECAST) rất hữu ích. Hàm này giúp dự đoán các giá trị tương lai dựa trên dữ liệu hiện có.
Trước khi bạn áp dụng hàm này, điều quan trọng là phải hiểu rằng chúng dựa trên các xu hướng tuyến tính. Nếu dữ liệu của bạn không tuân theo một mẫu hình tuyến tính, các dự đoán có thể không chính xác.
Ví dụ, bạn là một chuyên viên phân tích doanh số và có dữ liệu doanh số hàng tháng trong năm qua. Bạn muốn dự báo doanh số cho vài tháng tới. Cột A (A2 đến A13) chứa các tháng và cột B (B2 đến B13) chứa số liệu doanh số. Bây giờ, hãy sử dụng công thức sau để dự đoán doanh số cho tháng thứ 13:
=FORECAST.LINEAR(A14, B2:B13, A2:A13)
Ví dụ ứng dụng hàm FORECAST.LINEAR trong Excel để dự đoán xu hướng dữ liệu
3. UNIQUE: Trích xuất giá trị duy nhất nhanh chóng
Hàm UNIQUE là một công cụ tuyệt vời để trích xuất các giá trị riêng biệt từ một phạm vi hoặc mảng. Nó rất tiện lợi khi bạn phải xử lý các giá trị lặp lại trong cơ sở dữ liệu của mình. Giả sử bạn có danh sách tên khách hàng trong cơ sở dữ liệu bán hàng và muốn trích xuất một danh sách các tên khách hàng duy nhất để tránh gửi email tiếp thị trùng lặp.
Ví dụ, nếu cột A có các tên khách hàng như Parth, Heema, Aavyan, Mihir, Sanya, Aarav, Parth, Aarav, Sanya và Mihir, bạn có thể chạy hàm UNIQUE(A:A) và nó sẽ trả về một danh sách các tên khách hàng duy nhất (Parth, Heema, Aavyan, Mihir, Sanya và Aarav).
Nếu dữ liệu duy nhất của bạn được lưu trữ theo hàng thay vì cột, bạn có thể sử dụng =UNIQUE(A1:F1,TRUE) để trích xuất các giá trị duy nhất.
Nếu bạn muốn xem những khách hàng nào chỉ có một mục nhập trong danh sách, bạn có thể sử dụng =UNIQUE(A:A,FALSE,TRUE). Công thức này sẽ trả về Aavyan và Heema từ danh sách trên. Đây là một trong những hàm giúp biến người mới bắt đầu thành chuyên gia Excel.
4. AGGREGATE: Hàm tổng hợp “tất cả trong một”
AGGREGATE là một công cụ mạnh mẽ để thực hiện nhiều phép tính khác nhau trong khi bỏ qua các hàng ẩn, lỗi và các giá trị khác. Điều này làm cho nó linh hoạt hơn các hàm tiêu chuẩn như AVERAGE, SUM và COUNT. Hàm này có thể thực hiện 19 phép tính khác nhau (AVERAGE, COUNT, MAX, MIN, SUM, v.v.).
Giả sử bạn có một cơ sở dữ liệu bán hàng với các lỗi tiềm ẩn. Bây giờ, bạn có thể sử dụng =AGGREGATE(1, 6, A:A) để hiển thị số tiền bán hàng trung bình, loại trừ bất kỳ giá trị lỗi và hàng ẩn nào. Ở đây, 1 chỉ định hàm AVERAGE, 6 chỉ định bỏ qua các giá trị lỗi và A:A cho biết cột số tiền bán hàng cần tính.
Có khoảng 19 mã số hàm khác nhau. Bạn có thể kiểm tra mục đích của chúng từ nguồn chính thức.
Giao diện Excel minh họa hàm AGGREGATE với các tùy chọn tính toán nâng cao
5. XLOOKUP: Thay thế hoàn hảo cho VLOOKUP và hơn thế nữa
XLOOKUP là một trong những hàm Excel mà nhiều người ước rằng đã biết sớm hơn. Hàm này đã thay thế hiệu quả VLOOKUP, HLOOKUP và INDEX/MATCH trong nhiều quy trình làm việc. XLOOKUP có thể tìm kiếm cả theo chiều dọc và chiều ngang, đồng thời cung cấp sự linh hoạt hơn trong việc xử lý lỗi và chỉ định các loại đối sánh.
Giả sử bạn có một cơ sở dữ liệu bán hàng với thông tin sản phẩm trong các cột khác nhau. Cột A có ID sản phẩm (1001, 1002, 1003, v.v.), cột B có tên sản phẩm (Laptop, Chuột, Bàn phím, v.v.) và cột C có giá (1200, 150, 50, v.v.). Bây giờ, trong một ô khác, bạn có thể nhập ID sản phẩm bạn muốn tra cứu (ví dụ: 1002).
Bây giờ, bạn có thể sử dụng công thức dưới đây để tìm giá của sản phẩm đó:
=XLOOKUP(E1, A:A, C:C, "Không tìm thấy ID sản phẩm")Nếu tìm thấy ID sản phẩm 1002, công thức sẽ trả về giá (ví dụ: 25). Nếu không tìm thấy ID sản phẩm, nó sẽ trả về “Không tìm thấy ID sản phẩm”.
Hướng dẫn sử dụng hàm XLOOKUP trong Excel để tra cứu dữ liệu linh hoạt
Trở thành chuyên gia phân tích dữ liệu với Excel
Excel, với một loạt các hàm phong phú, là bộ công cụ thiết yếu cho bất kỳ chuyên gia phân tích nào có nhiệm vụ trích xuất dữ liệu có ý nghĩa từ các bảng tính. Bạn còn chờ gì nữa? Hãy áp dụng những hàm này vào quy trình làm việc của bạn và nâng tầm khả năng phân tích dữ liệu của bạn lên một tầm cao mới.
Nếu bạn thường xuyên làm việc với các tập dữ liệu lớn, hãy khám phá tính năng tích hợp Python trong Excel để trở thành một chiến lược gia dựa trên dữ liệu. Python trong Excel thực sự tuyệt vời và có thể mang lại nhiều giá trị bất ngờ.
