Có thể bạn quan tâm: Hàm Thỏa Mãn 2 Điều Kiện Trong Excel
Giới thiệu
Excel, từ lâu đã trở thành công cụ không thể thiếu cho công việc phân tích dữ liệu, lập báo cáo và quản lý thông tin. Trong quá trình làm việc, chúng ta thường gặp vấn đề muốn lấy một tập hợp dữ liệu từ bảng tính, thay vì chỉ một giá trị duy nhất. Đó là lúc hàm trả về nhiều kết quả (array formulas) xuất hiện, giúp bạn xử lý dữ liệu một cách nhanh chóng, chính xác và có tính động cao. Bài viết này sẽ cung cấp một hướng dẫn chi tiết, bao gồm lý thuyết, cách áp dụng, ví dụ thực tế và mẹo tối ưu, giúp bạn thành thạo công nghệ “trả về nhiều kết quả” trong Excel.
Có thể bạn quan tâm: Hàm Số Thứ Tự Tăng Dần Trong Excel – Hướng Dẫn Chi Tiết Và Ứng Dụng Thực Tiễn
1. Tại sao cần hàm trả về nhiều kết quả?
- Phân tích dữ liệu phức tạp: Khi cần lọc, sắp xếp, tính toán cột toàn bộ dữ liệu thay vì giá trị duy nhất.
- Động và linh hoạt: Các hàm trả về nhiều kết quả tự động cập nhật khi dữ liệu nguồn thay đổi.
- Tiết kiệm công sức: Tránh viết công thức lặp đi lặp lại, giảm lỗi và tăng tính nhất quán.
- Hiệu suất tính toán: Các hàm mới (FILTER, SORT, UNIQUE…) được tối ưu hơn so với công thức mảng truyền thống.
Có thể bạn quan tâm: Hàm Sum Trong Excel Không Ra Kết Quả: Nguyên Nhân, Cách Khắc Phục Và Mẹo Nâng Cao Hiệu Suất
2. Các hàm trả về nhiều kết quả chính trong Excel
2.1. FILTER
Hàm FILTER cho phép bạn lọc một phạm vi dữ liệu dựa trên điều kiện nhất định và trả về tất cả các hàng thỏa mãn.
Cú pháp:
FILTER(array, include, if_empty)
Ví dụ: Cho bảng dữ liệu bán hàng với cột “Loại” và “Doanh thu”. Để lấy tất cả các dòng có “Loại” = “Điện tử”:
=FILTER(A2:C100, B2:B100="Điện tử", "Không có dữ liệu")
2.2. SORT
Hàm SORT sắp xếp dữ liệu theo một hoặc nhiều tiêu chí.
Cú pháp:
SORT(array, sort_index, sort_order, by_col)
Ví dụ: Sắp xếp danh sách bán hàng theo doanh thu giảm dần:
=SORT(A2:C100, 3, -1)
2.3. UNIQUE
Hàm UNIQUE trả về danh sách duy nhất gruppo các giá trị trong một phạm vi.
Cú pháp:
UNIQUE(array, by_col, exactly_once)
Ví dụ: Để lấy danh sách sản phẩm duy nhất:
=UNIQUE(A2:A-count)
2.4. SEQUENCE
Hàm SEQUENCE omogućuje generiranje niza brojeva s određenim intervalom.
Cú pháp:
SEQUENCE(rows, cols, start, step)
Ví dụ: Tạo một danh sách số từ 1 đến 10:
=SEQUENCE(10,1,1,1)
2.5. XLOOKUP (với tham số array)
XLOOKUP có thể trả về nhiều giá trị nếu kết quả được gộp vào mảng.
Ví dụ: Tìm tất cả các mã sản phẩm có giá trị tương ứng:
=XLOOKUP("Điện tử", B2:B100, A2:A100)2.6. LET và LAMBDA (định nghĩa hàm tùy chỉnh)
LET giúp đặt tên cho các phần của công thức, trong khi LAMBDA cho phép bạn tạo hàm tùy chỉnh có thể trả về mảng.
Ví dụ: Hàm LAMBDA để lấy hai cột đầu tiên của bảng:
=LAMBDA(tbl, INDEX(tbl, , SEQUENCE(1,2)))(A2:D100)
3. Cách áp dụng hàm trả về nhiều kết quả trong thực tế
3.1. Phân tích dữ liệu bán hàng
Giả sử bạn quản lý một cửa hàng, cần xem tổng doanh thu theo từng loại sản phẩm. Bạn có thể kết hợp FILTER và SUM để tính tổng doanh thu:
=SUM(FILTER(C2:C100, B2:B100="Điện tử"))
3.2. Tạo bảng tổng hợp động
Để tự động cập nhật bảng báo cáo, bạn có thể kết hợp UNIQUE, FILTER và SUMIFS:
Bước 1: Lấy danh sách duy nhất các loại sản phẩm:
=UNIQUE(B2:B100)
Bước 2: Tính doanh thu cho từng loại:
=SUMIFS(C2:C100, B2:B100, A2)
Trong đó, A2 chứa loại sản phẩm duy nhất.
3.3. Tạo danh sách động dựa trên điều kiện
Ví dụ: Bạn muốn hiển thị danh sách khách hàng đã đặt hàng trên 5 lần.
=FILTER(A2:A100, COUNTIF(B2:B100, A2:A100)>=5)
3.4. So sánh dữ liệu hai bảng
Để xác định những dòng dữ liệu có trong bảng A nhưng không có trong bảng B:
=FILTER(A2:A100, COUNTIF(B2:B100, A2:A100)=0)
4. Sử dụng công thức mảng truyền thống (Ctrl+Shift+Enter)
Trước khi Excel 365 ra mắt các hàm động, người dùng phải sử dụng công thức mảng nhập bằng Ctrl+Shift+Enter. Dưới đây là một ví dụ:
Có thể bạn quan tâm: Hàm Thống Kê Nhiều Điều Kiện Trong Excel: Cách Tối Ưu Hóa Phân Tích Dữ Liệu
Tính tổng doanh thu của các sản phẩm “Điện tử” bằng công thức mảng: 1. Chọn ô đích (định dạng sẽ trở thành {…}).
2. Nhập công thức:
=SUM((B2:B100="Điện tử")(C2:C100))
- Nhấn Ctrl+Shift+Enter.
Những lưu ý:
- Không còn cần nhập Ctrl+Shift+Enter trong Excel 365 với hàm động.
- Công thức mảng truyền thống thường khó quản lý và dễ lỗi.
- H plagic: Đối với dữ liệu lớn, công thức mảng truyền thống có thể làm chậm tính toán.
5. Tối ưu hiệu suất khi làm việc với mảng lớn
- Giới hạn phạm vi: Sử dụng phạm vi cụ thể thay vì toàn bộ cột (ví dụ: A2:A1000 thay vì A:A).
- Tránh lặp lại công thức: Dùng hàm LET để lưu trữ kết quả trung gian.
- Chọn hàm phù hợp: FILTER thường nhanh hơn khi cần lọc dữ liệu.
- Giảm tính toán tạm thời: Sử dụng hàm MINIFS, MAXIFS thay vì công thức mảng.
- Đặt tính năng tính toán tự động: Trong trường hợp dữ liệu lớn, có thể tạm thời chuyển sang tính toán thủ công để tránh tắt màn hình.
6. Thực hành: Tạo báo cáo doanh thu theo tháng
Giả sử bạn có bảng dữ liệu:
| Ngày | Loại | Doanh Thu |
|---|---|---|
| 01/01/2026 | Điện tử | 500 |
| 02/01/2026 | Thời trang | 300 |
| 15/01/2026 | Điện tử | 700 |
- Chuyển đổi ngày sang tháng: Sử dụng hàm TEXT hoặc MONTH.
- Tổng doanh thu theo tháng: <!– Tạo mảng các tháng duy nhất –>
- Ví dụ công thức:
Bước 1: Tạo danh sách tháng duy nhất:
=UNIQUE(TEXT(A2:A100, "mm-yyyy"))
Bước 2: Tính doanh thu tổng theo tháng:
=SUMIFS(C2:C100, TEXT(A2:A100, "mm-yyyy"), D2)
Trong đó, D2 chứa tháng cần tính.
7. Các trường hợp nâng cao
7.1. Sử dụng hàm CHOOSE và INDEX để trả về nhiều cột
Để lấy nhiều cột theo điều kiện, bạn có thể kết hợp CHOOSE và INDEX:
=CHOOSE({1,2}, INDEX($A$2:$C$100, , 1), INDEX($A$2:$C$100, , 3))7.2. Kết hợp hàm IF với FILTER
Ví dụ: Lọc danh sách khách hàng có doanh thu > 1000 và hiển thị tên, số điện thoại:
=FILTER(A2:C100, C2:C100>1000, "Không có")
7.3. Đọc dữ liệu từ nguồn dữ liệu bên ngoài (Power Query)
Power Query cho phép bạn lấy dữ liệu từ nhiều nguồn khác nhau, sau đó sử dụng hàm trả về nhiều kết quả trong Excel để phân tích.
8. Giao diện và cách sử dụng hàm trả về nhiều kết quả trong Excel
8.1. Tính năng “Dynamic Arrays”
Excel 365 đã giới thiệu “Dynamic Arrays”, cho phép một công thức trả về nhiều kết quả được “spilled” (phát tán) tự động vào ô kế bên.
8.2. Cách nhận biết “spilled” array
- Ô chứa công thức sẽ hiển thị dấu “#SPILL!” nếu không có đủ không gian.
- Để tránh lỗi, nên đặt công thức ở ô có đủ không gian phía bên phải và bên dưới.
8.3. Cách sử dụng hàm FILTER trong “Dynamic Arrays”
Các bước:
- Chọn ô đầu tiên.
- Nhập công thức FILTER.
- Nhấn പരിശോധ.
- Excel tự động “spill” kết quả vào ô kế bên.
9. So sánh “Dynamic Arrays” và công thức mảng truyền thống
| Tiêu chí | Dynamic Arrays | Công thức mảng truyền thống |
|---|---|---|
| Độ phức tạp | Rất đơn giản | Commercial + Ctrl+Shift+Enter |
| Hiệu suất | Cải thiện đáng kể | Chậm hơn ở dữ liệu lớn |
| Khả năng mở rộng | Rất tốt | Ràng buộc phạm vi |
| Hỗ trợ cũ | Chỉ trong Excel 365/2026 | Hỗ trợ rộng rãi |
10. Những lỗi thường gặp và cách khắc phục
- #SPILL!: Kiểm tra xem có ô trống đủ không. Xóa dữ liệu xung quanh hoặc di chuyển công thức.
- #CALC!: Dữ liệu đầu vào không phù hợp (ví dụ: nông vô điều kiện). Kiểm tra điều kiện.
- Hiệu suất thấp: Giới hạn phạm vi, dùng LET, giảm số công thức.
- Kiểu dữ liệu không đồng nhất: Convert dữ liệu sang dạng số hoặc văn bản trước khi áp dụng.
11. Kết luận
Hàm trả về nhiều kết quả trong Excel mở ra một thế giới mới cho công việc phân tích dữ liệu, giúp bạn nhanh chóng xử lý, lọc, sắp xếp và tổng hợp dữ liệu một cách hiệu quả. Bằng cách nắm vững các hàm như FILTER, SORT, UNIQUE, SEQUENCE, XLOOKUP và các công cụ hỗ trợ như LET, LAMBDA, bạn có thểოების тиждень tự động làm việc với dữ liệu phức tạp mà không cần viết công thức lặp đi lặp lại.
Đừng ngần ngại thử nghiệm các công thức trong bảng tính của mình, khám phá các trường hợp sử dụng khác nhau và tối ưu hoá hiệu suất tính toán. Khi bạn quen thuộc với “Dynamic Arrays”, công việc xử lý dữ liệu sẽ trở nên nhanh gọn, chính xác và giảm thiểu sai sót. Hãy bắt đầu ngay hôm nay để thấy được sự thay đổi lớn trong quy trình làm việc của mình!
Tiến sĩ Lê Thống Nhất sinh năm 1955 tại Nghệ An, là nhà giáo, nhà báo và doanh nhân trong lĩnh vực giáo dục. Ông từng giảng dạy Toán học tại Trường THPT Chuyên Phan Bội Châu và đảm nhiệm nhiều vai trò trong ngành giáo dục, từ viết sách, biên soạn tài liệu đến sáng lập các dự án hỗ trợ học tập trực tuyến.
Ông là người sáng lập BigSchool – nền tảng giáo dục trực tuyến kết nối giáo viên và học sinh cả nước. Ngoài ra, ông còn nổi tiếng với vai trò tác giả, diễn giả truyền cảm hứng và khách mời trên nhiều chương trình truyền hình, trong đó có “Ai là triệu phú” trên truyền hình VTV.
Trong suốt sự nghiệp, Tiến sĩ Lê Thống Nhất đã nhận nhiều giải thưởng giáo dục và được biết đến như một người luôn đổi mới, ứng dụng công nghệ vào dạy và học, góp phần lan tỏa tri thức và niềm say mê học tập cho thế hệ trẻ.