Sức mạnh của công thức mảng trong trích lọc dữ liệu và báo cáo

Công thức mảng là công cụ vô cùng mạnh mẽ và hữu ích nhưng cũng rất phức tạp, trừu tượng và đầy ảo diệu nên không có nhiều người biết cách sử dụng. Công thức mảng giúp chúng ta có thể giải quyết được rất nhiều bài toán mà sử công thức thông thường sẽ rất khó hoặc phải áp dụng những công thức rất phức tạp mới xử lý được.
Vậy công thức mảng là gì?
Mảng là một hàng giá trị, một cột giá trị hoặc là một kết hợp các hàng và cột giá trị. (ví dụ: {1,2,3,4})
Công thức mảng là công thức được bao bởi cặp dấu ngoặc nhọn {} do excel tự động thêm vào sau khi kết thúc nhập công thức. Công thức mảng là công thức có thể thực hiện nhiều phép tính đối với một hoặc nhiều mục trong mảng. Công thức mảng có thể trả về nhiều kết quả hoặc một kết quả duy nhất
Công thức mảng bao gồm nhiều ô được gọi là công thức đa ô và công thức mảng trong một ô duy nhất được gọi là công thức đơn ô.
Nhập công thức mảng:
Chọn ô hoặc vùng ô cần nhập công thức. Nhập công thức cần tính toán
Nhấn tổ hợp phím Ctrl + Shift + Enter
Bây giờ các bạn xem các ví dụ dưới đây để thấy rõ hơn công dụng của công thức mảng nhé
Tính tổng nhanh theo dãy
VD1: Tính thành tiền thu mua sản phẩm của từng nhân viên trong bảng sau

Thông thường tại ô G5 các bạn sẽ nhập công thức =E5*F5 enter rồi sao chép xuống các ô còn lại.
Tuy nhiên khi dùng công thức mảng, các bạn chỉ cần làm như sau:
Chọn vùng ô G5:G11 sau đó nhập công thức =E5:E11*G5:G11 rồi nhấn tổ hợp các phím
Ctrl + Shift + Enter ta nhận được kết quả như sau

Ví dụ 2: Cũng bảng dữ liệu trên, các bạn cần tính tổng thành tiền thu mua sản phẩm của tất cả các nhân viên
Thay vì các bạn phải tính thành tiền của từng nhân viên ra rồi cộng lại. Các bạn chỉ cần nhập vào ô cần hiển thị kết quả công thức như sau: =SUM(E5:E11*F5:F11)
sau đó nhấn tổ hợp phím Ctrl + Shift + Enter, ta nhận được kết quả

Tính tổng theo điều kiện, đếm theo điều kiện
Ví dụ 3: Cũng bảng dữ liệu trên, các bạn cần đếm số nhân viên thu mua sản phẩm trên 10 tạ
Thay vì dùng countif() để đếm thì bạn chỉ cần sử dụng công thức mảng như sau
=SUM(–(E5:E11>10))  nhấn tổ hợp Ctrl + Shift + Enter ta được kết quả là 3

Trong trường hợp các bạn muốn tính tổng số lượng thu mua của tất cả các nhân viên mua được sản phẩm trên 10 tạ
Các bạn nhập công thức như sau: =SUM((E5:E11>10)*(E5:E11)) nhấn tổ hợp Ctrl + Shift + Enter ta được kết quả là 40.7

Ví dụ 4: Tính tổng số lượng theo Top cao nhất hoặc thấp nhất
Cũng với bảng dữ liệu trên, yêu cầu tính tổng số lượng thu mua được của 4 nhân viên có số lượng thu mua lớn nhất
Tại ô cần tính ta nhập công thức =SUM(LARGE(E5:E11,{1,2,3,4}))
Nhấn tổ hợp phím Ctrl + Shift + enter ta nhận được kết quả là 47.7

Ví dụ 5: Cách khác để đánh số thứ tự sử dụng công thức mảng
Giả sử các bạn muốn nhập giá trị lần lượt cho cột STT trong bảng sau

Thay vì các bạn nhập 2 số đầu tiên của dãy vào rồi tự động điền theo sap chép công thức thì các bạn sử dụng công thức mảng như sau
Các bạn bôi đen dải ô B5:B11 sau đó nhập công thức
=ROW(D5:D11)-ROW(D5)+1 rồi nhấn tổ hợp phím Ctrl + Shift + enter
Ta nhận được kết quả

Trên đây là một số ví dụ về sử dụng công thức mảng. Cảm ơn các bạn đã theo dõi. Chúc các bạn thành công!