Công thức mảng là một trong những tính năng khó hiểu nhất trong Excel, nhưng dường như lại là một trong những điều thú vị và hấp dẫn nhất.
1. Đếm các ô thỏa mãn các điều kiện nhất định
Việc giới thiệu các hàm SUMIF và COUNTIF cũng giống như đa phần các đối tượng khác trong Excel 2007, đều không thể luôn phù hợp với mọi trường hợp. Tuy nhiên, vẫn có những nhiệm vụ mà công thức mảng là giải pháp có hiệu quả nhất vẫn có các lựa chọn thay thế khác.
Giả sử bạn có 2 cột số, cột A (đã lên kế hoạch – planned) và cột B (thực tế). Và bạn muốn biết cột B lớn hơn gấp bao nhiêu lần so với cột A, khi một giá trị trong cột B lớn hơn 0.
Nhiệm vụ này đòi hỏi phải so sánh hai dãy và rất cần công thức mảng Excel ở đây.
Vì vậy, bạn thể hiện các điều kiện như (B2: B10> = A2: A10) và (B2: B10> 0), nối chúng bằng dấu hoa thị (*) hoạt động như hàm AND trong các công thức mảng, và đưa chúng vào trong đối số của hàm SUM:
= SUM ((B2: B10> = A2: A10) * (B2: B10> 0))
Và hãy nhớ nhấn Ctrl + Shift + Enter để nhập chính xác mảng trong Excel!
Hai đoạn tiếp theo dành cho những ai tìm hiểu sâu hơn về công nghệ. Nếu bạn không quan tâm, thì bạn có thể bỏ qua và đi tới ví dụ công thức tiếp theo.
Để hiểu rõ hơn về công thức này nói riêng và các công thức mảng Excel nói chung, hãy chọn hai biểu thức bên trong ngoặc đơn của hàm SUM trong thanh công thức, rồi nhấn F9 để xem các mảng trong thành phần của công thức.
Vì vậy, những gì chúng ta có ở đây là hai mảng các giá trị Boolean, trong đó TRUE tương đương với 1 và FALSE tương đương với 0. Do chúng ta đang sử dụng toán tử AND () trong công thức, nên hàm SUM sẽ chỉ thêm các hàng có giá trị TRUE (1) trong cả hai mảng, như trong ảnh chụp màn hình dưới đây:
Chú ý: Không phải tất cả các hàm Excel hỗ trợ các mảng có thể chuyển đổi TRUE và FALSE thành 1 và 0. Trong các công thức mảng phức tạp hơn, thì bạn có thể cần phải sử dụng dấu gạch đôi (–), được gọi là toán tử đôi, để chuyển đổi các số không phải là giá trị Boolean với số.
Bao gồm 2 dấu gạch ngang trong công thức trên sẽ không làm ảnh hưởng gì cả, mà nó sẽ chỉ giữ công thức bạn an toàn hơn: = SUM (- (B2: B10> = A2: A10) * (B2: B10> 0))
Và đây là một ví dụ công thức mảng Excel phức tạp hơn mà hoàn toàn yêu cầu sử dụng toán tử đơn vị.
2. Sử dụng một số hàm trong công thức mảng Excel
Các công thức mảng có thể làm việc với một số hàm Excel tại một thời điểm và thực hiện nhiều phép tính trong một công thức.
Ví dụ: nếu bạn có một bảng liệt kê nhiều sản phẩm bán hàng của một số nhân viên bán hàng và bạn muốn biết doanh số bán hàng tối đa của một người cụ thể cho một sản phẩm nhất định, bạn có thể viết một công thức mảng dựa trên mẫu sau:
= MAX (IF ((salesmen_range = “name”) * (product_range = “name”), sales_range, “”))
Giả sử rằng tên của nhân viên bán hàng nằm trong cột A, tên sản phẩm nằm trong cột B và doanh số bán hàng nằm trong cột C, thì để tính doanh số bán táo (apples) nhiều nhất mà Mike có thể bán, sử dụng công thức:
= MAX (IF (($ A $ 2: $ A $ 9 = “mike”) * ($ B $ 2: $ B $ 9 = “apples”), $ C $ 2: $ C $ 9, “”))
Đương nhiên, bạn có thể thay thế các tên trong công thức bằng các tham chiếu ô để người dùng của bạn chỉ cần gõ tên vào các ô nhất định mà không sửa đổi công thức mảng của bạn:
Sử dụng một số hàm trong công thức mảng Excel
Trong hình ở trên có sử dụng các công thức mảng trong Excel (và đừng quên nhấn Ctrl + Shift + Enter để nhập chính xác nhé):
Giá trị lớn nhất: =MAX(IF(($ A$ 2:$ A$ 9=$ F$ 1) * ($ B$ 2:$ B$ 9=$ F$ 2), $ C$ 2:$ C$ 9,””))
Giá trị nhỏ nhất: =MIN(IF(($ A$ 2:$ A$ 9=$ F$ 1) * ($ B$ 2:$ B$ 9=$ F$ 2), $ C$ 2:$ C$ 9,””))
Giá trị trung bình: =AVERAGE(IF(($ A$ 2:$ A$ 9=$ F$ 1) * ($ B$ 2:$ B$ 9=$ F$ 2), $ C$ 2:$ C$ 9,””))
Tổng cộng: =SUM(IF(($ A$ 2:$ A$ 9=$ F$ 1) * ($ B$ 2:$ B$ 9=$ F$ 2), $ C$ 2:$ C$ 9,””))
Chúc các bạn thành công nhé.
Bạn có thể thành thạo ngay kỹ năng tin học văn phòng từ giảng viên FPT- Arena chỉ với 280,000Đ
Bài viết liên quan
