Công thức mảng trong Excel là một tính năng mạnh mẽ, tuy có phần phức tạp nhưng lại vô cùng hữu ích cho việc xử lý dữ liệu. Bài viết này sẽ hướng dẫn bạn qua 7 ví dụ thực tế, giúp bạn hiểu rõ và áp dụng công thức mảng Excel một cách hiệu quả.
Mô tả công thức mảng trong Excel
Từ việc đếm ô theo điều kiện đến tính toán phức tạp trên nhiều dãy dữ liệu, công thức mảng mang đến giải pháp tối ưu cho nhiều bài toán trong Excel. Cùng khám phá chi tiết từng ví dụ và nâng cao kỹ năng Excel của bạn.
1. Đếm Ô Thỏa Mãn Nhiều Điều Kiện
Hàm SUMIF và COUNTIF hữu ích nhưng đôi khi không đủ đáp ứng. Công thức mảng là giải pháp hiệu quả cho các trường hợp cần so sánh nhiều dãy dữ liệu.
Ví dụ: Bạn có cột A (Kế hoạch) và cột B (Thực tế). Muốn biết có bao nhiêu giá trị ở cột B lớn hơn cột A và đồng thời lớn hơn 0, hãy dùng công thức:
=SUM((B2:B10>=A2:A10)*(B2:B10>0))Lưu ý: Nhấn Ctrl + Shift + Enter để nhập công thức mảng.
Để hiểu rõ hơn, chọn từng phần trong ngoặc đơn và nhấn F9 để xem mảng giá trị logic (TRUE/FALSE tương đương 1/0). Hàm SUM chỉ tính các hàng có giá trị TRUE (1) ở cả hai mảng.
Phân tích công thức mảng
Thêm dấu -- để chuyển đổi giá trị logic thành số: =SUM(--(B2:B10>=A2:A10)*(B2:B10>0)).
2. Kết Hợp Nhiều Hàm Trong Công Thức Mảng
Công thức mảng có thể kết hợp nhiều hàm Excel để thực hiện nhiều phép tính cùng lúc.
Ví dụ: Tính doanh số bán táo cao nhất của Mike (cột A: Tên nhân viên, cột B: Tên sản phẩm, cột C: Doanh số):
=MAX(IF(($A$2:$A$9="Mike")*($B$2:$B$9="Apples"),$C$2:$C$9,""))Tương tự, bạn có thể tính giá trị nhỏ nhất, trung bình, tổng cộng bằng cách thay MAX bằng MIN, AVERAGE, SUM.
Kết hợp hàm trong công thức mảng
3. Đếm Tổng Số Ký Tự Trong Một Vùng
Đếm số ký tự (bao gồm khoảng trắng) trong vùng A1:A10:
=SUM(LEN(A1:A10))Hàm LEN tính chiều dài chuỗi trong mỗi ô, SUM cộng các giá trị đó lại.
4. Đếm Số Lần Xuất Hiện Của Một Ký Tự
Đếm số lần xuất hiện của ký tự trong vùng:
=SUM((LEN(range)-LEN(SUBSTITUTE(range,character,"")))/LEN(character))Ví dụ: Đếm số đơn hàng loại “A” (ký tự đầu tiên) trong vùng B2:B5, mã loại ở ô E1:
=SUM((LEN(B2:B5)-LEN(SUBSTITUTE(B2:B5,E1,"")))/LEN(E1))
Đếm ký tự trong vùng
5. Tính Tổng Các Hàng Chẵn/Lẻ Hoặc Hàng Thứ N
Tính tổng các hàng chẵn trong vùng B2:B10:
=SUM(--(MOD(ROW($B2:B10),2)=0)*($B2:B10))Thay 0 bằng 1 để tính tổng hàng lẻ. Công thức tổng quát cho hàng thứ N (N nằm trong ô E1):
=SUM(--(MOD((ROW($B$2:$B$7)-ROW($B$2)),E1)=E1-1)*($B$2:$B$7))
Tính tổng hàng thứ N
6. Tính Toán Theo Khoảng Giá Trị
Tính tổng tiền dựa trên số lượng mua và bảng giá theo khoảng:
Dữ liệu gốc
Công thức mảng linh hoạt hơn so với hàm IF lồng nhau:
=SUM(B8*(B2:B6)*(--(B8>=VALUE(LEFT(A2:A6,FIND(" ",A2:A6)))))*(--(B8<VALUE(RIGHT(A2:A6,LEN(A2:A6)-FIND(" đến ",A2:A6)-LEN(" đến ")))))
Tính toán theo khoảng giá trị
Thêm điều kiện kiểm tra nằm ngoài khoảng:
=IF(AND(B8>=VALUE(LEFT(A2,FIND(" ",A2))),B8<=VALUE(RIGHT(A6,LEN(A6)-FIND(" đến ",A6)-LEN(" đến ")))),SUM(B8*(B2:B6)*(--(B8>=VALUE(LEFT(A2:A6,FIND(" ",A2:A6)))))*(--(B8<VALUE(RIGHT(A2:A6,LEN(A2:A6)-FIND(" đến ",A2:A6)-LEN(" đến ")))))),"Nằm ngoài dải")
Kiểm tra nằm ngoài khoảng
7. Sử Dụng Hàm Tự Định Nghĩa Trong Công Thức Mảng
Kết hợp hàm tự định nghĩa (VD: GetCellColor để lấy màu ô) với công thức mảng:
=SUM(--($A$2:$A$10=$F$1)*($C$2:$C$10)*(--(GetCellColor($C$2:$C$10)=GetCellColor($E$2))))
Sử dụng hàm tự định nghĩa
Công thức tính tổng doanh số của Neal với ô màu xanh và vàng (F1: tên nhân viên, E2: mẫu màu).
Hạn Chế Của Công Thức Mảng Và Giải Pháp Thay Thế
Mặc dù mạnh mẽ, công thức mảng có một số hạn chế:
- Mảng lớn làm chậm Excel: Tránh sử dụng mảng quá lớn.
- Không chọn toàn bộ cột: Không tạo mảng bao gồm toàn bộ cột.
- Giới hạn công thức tham chiếu trang tính khác: Hạn chế bởi bộ nhớ.
- Gỡ lỗi phức tạp: Sử dụng F9 để kiểm tra từng phần.
Các hàm thay thế: SUMPRODUCT, INDEX.
Hy vọng bài viết này giúp bạn hiểu rõ và sử dụng công thức mảng Excel hiệu quả hơn. Chúc bạn thành công!
Discussion about this post