Hàm mảng trong Excel là gì? Cấu trúc của hàm mảng đơn giản

Trong Excel, ngoài các hàm tính toán thông thường ra, còn có một hàm được coi là khó hiểu và khó sử dụng nhất đó là hàm mảng. Vậy hàm mảng trong Excel là gì? Tại sao nó lại là hàm khó nhất trong Excel? Nếu bạn thắc mắc, hãy cùng Mindovermetal theo dõi bài viết này.

Hàm mảng trong Excel là gì?

Mảng trong Excel được định nghĩa là một tập hợp các phần tử. Các phần tử này có thể là chuỗi, một dòng, một cột hay nhiều cột, nhiều dòng.

Công thức mảng khác với những công thức khác đó là nó sẽ đánh giá và tính toán nhiều giá trị trong một mảng thỏa mãn điều kiện trong công thức. Chính vì thế mà kết quả trả về của công thức này cũng là một mảng.

Hàm mảng trong Excel không phải là một cú pháp hay công thức xác định. Mà nó là một phương pháp để có thể làm việc với nhiều dữ liệu, thực hiện nhiều tính toán chỉ trong một công thức. Đây cũng là lý do mà hàm mảng được cho là khó hiểu và khó sử dụng nhất.

Để Excel thực hiện tính toán với mảng. Sau khi nhập công thức bắt buộc phải nhấn tổ hợp phím Ctrl + Shift + Enter thay cho phím Enter như công thức thông thường.

Ví dụ

Ta có bảng dữ liệu sau và yêu cầu đưa ra là doanh thu của từng sản phẩm.

Thông thường ta sẽ tính doanh thu của từng sản phẩm bằng cách lấy số lượng bán của từng sản phẩm nhân với đơn giá. Sau đó sao chép công thức theo hàng.

Tuy nhiên, ta sẽ làm đơn giản hơn với hàm mảng trong Excel. Đầu tiên, ta sẽ bôi đen vùng dữ liệu C2:C7. Sau đó nhập công thức sau: =A2:A7*B2:B7.

Nhấn Ctrl + Shift + Enter. Ta sẽ có kết quả là doanh thu của từng mặt hàng.

Công thức mảng đã tính toán dữ liệu theo từng hàng và trả về kết quả là một mảng theo cột. Tiếp theo ta sẽ đi tìm hiểu về các thành phần quan trọng của hàm mảng trong Excel đó là:

• Công thức mảng
• Hằng số mảng.

Công thức mảng trong Excel

Công thức mảng trong Excel được chia làm 2 trường hợp đó là công thức mảng với một ô và công thức mảng với đa ô hay nhiều ô. Việc phân biệt 2 công thức này sẽ giúp bạn linh hoạt hơn trong quá trình sử dụng hàm mảng trong Excel.

Hàm mảng với một ô trong Excel.

Hàm mảng với một ô trong Excel là những hàm chỉ trả về kết quả tại một ô trong Excel. Một số công thức tiêu biểu cho hàm mảng với một ô như: MIN, MAX, SUM,…

Thông thường để tìm giá trị nhỏ nhất của GT2 trừ đi GT1. Ta sẽ tính một cột trung gian và tình giá trị nhỏ nhất từ cột trung gian đó.

Tuy nhiên, với công thức mảng ở trên, việc tính giá trị trung gian được thực hiện luôn trong công thức. Sau đó tìm và trả về giá trị nhỏ nhất.

Giá trị trả về ở đây chỉ xuất hiện ở một ô cho dù bạn chọn nhiều ô thì những ô đó cũng cho kết quả như nhau. Chính vì thế đây là hàm mảng trong Excel cho một ô.

Công thức mảng với nhiều ô trong Excel.

Khác với công thức mảng với một ô, các kết quả ở trường hợp này sẽ được trả về nhiều ô. Có một số hàm được thiết kế riêng để trả về mảng nhiều ô như: TRANSPOSE, LINEST, TREND,…

Như ví dụ trên có thể thấy sau khi sử dụng hàm TRANSPOSE kết quả sẽ được trả về ở nhiều ô.

Lưu ý:

• Chọn dải ô mà bạn muốn kết quả trả về trước khi nhập công thức.
• Để xóa công thức, bạn phải bôi đen toàn bộ các ô chứa nó và nhấn DELETE. Hoặc có thể chọn toàn bộ công thức, nhấn DELETE và sau đó nhấn Ctrl + Shift + Enter.
• Bạn không thể xóa, di chuyển, chèn hay thay đổi một ô riêng lẻ trong công thức mảng.
• Để thu nhỏ công thức mảng, bạn phải xóa công thức hiện tại và nhập công thức mới vào.
• Để mở rộng công thức mảng, bạn phải chọn tất cả các ô chứa công thức và các ô muốn mở rộng thêm. Sau đó nhấn F2 để chỉnh sửa công thức.

Trên đây là một trong hai yếu tố cần phải quan tâm khi sử dụng hàm mảng trong Excel. Yếu tố còn lại đó hằng số mảng.

Hằng số mảng trong Excel

Hằng số mảng trong Excel là một yếu tố khác cần phải quan tâm khi sử dụng công thức mảng. Có 3 loại hằng số đó là:

• Hằng số mảng ngang
• Hằng số mảng dọc
• Mảng hai chiều.

Hằng số mảng ngang

Hằng số mảng ngang là hằng số được đặt chỉ trong một hàng. Để nhập giá trị cho hằng số mảng ngang, trước tiên phải chọn các ô trống tương ứng với số giá trị cần nhập. Sau đó nhập các giá trị, các giá trị cách nhau bởi dấu phẩy và phải được đặt trong dấu ngoặc nhọn.

Ví dụ: Nhập các giá trị 1, 2, 3, 4, 5 vào một mảng ngang ta sẽ bôi đen 5 ô theo hàng ngang. Sau đó ta nhập công thức ={1, 2, 3, 4, 4}. Cuối cùng nhấn Ctrl + Shift + Enter.

Hằng số mảng dọc

Tương tự như hằng số mảng ngang, hằng số mảng dọc trong Excel là các hằng số chỉ được đặt trong một hàng dọc.

Để nhập giá trị cho hằng số mảng dọc, ta cũng có thể thực hiện tương tự như hằng số mảng ngang. Tuy nhiên, chúng ta phải bôi đen ô trống theo hàng dọc và các giá trị được cách nhau bởi dấu chấm phẩy.

Ví dụ để nhập giá trị 1, 2, 3, 4, 5 theo mảng dọc ta bôi đen 5 ô trống theo hàng dọc. Sau đó nhập công thức ={1; 2; 3; 4; 5}. Cuối cùng nhấn Ctrl + Shift + Enter.

Hằng số mảng hai chiều

Để tạo hằng số mảng hai chiều ta cũng sẽ bôi đen hàng và cột trước. Sau đó nhập các giá trị tương tự như trên. Tuy nhiên, các mục trong một hàng sẽ cách nhau bởi dấu phẩy và các hàng sẽ cách nhau bởi dấu chấm phẩy.

Ví dụ khi nhập công thức ={1, 2, 3, 4, 5; “a”, “b”, “c”, “d”, “e”} vào Excel. Ta sẽ được kết quả là một mảng có 5 cột và 2 hàng. Trong đó, hàng trên là dãy số, hàng dưới là các chữ cái.

Ứng dụng của hàm mảng trong Excel

Hàm mảng được ứng dụng rất nhiều trong Excel. Một trong ứng dụng đơn giản và phổ biết nhất đó là tính tổng của một số các giá trị lớn nhất hay nhỏ nhất.

Ví dụ 1:

Ta có bảng số liệu sau và yêu cầu đưa ra là tính tổng 3 giá trị lớn nhất trong bảng dữ liệu đã cho.

Ta sẽ áp dụng công thức mảng để làm ví dụ này. Tại ô G3 nhập công thức =SUM(LARGE(C3:C10, {1, 2, 3})). Sau đó nhấn Ctrl + Shift + Enter để công thức mảng hoạt động.

Kết quả được trả về là 95.

Thông thường với ví dụ này, ta sẽ đi qua một bước trung gian đó là tìm những giá tri lớn nhất, sau đó mới tính tổng. Nhưng với hàm mảng thì cả 2 bước này đều được thực hiện chỉ trong một công thức.

Ngoài ra, thông thường hàm LARGE chỉ trả về một giá trị thứ k duy nhất. Trong khi đó, với công thức mảng, LARGE có thể trả về 3 thậm chí là 5 giá trị lớn nhất theo thứ tự giảm dần.

Một trường hợp khác có thể áp dụng hàm mảng đó là đếm các ô thỏa mãn nhiều điều kiện.

Ví dụ 2

Giả sử có bảng dữ liệu dưới đây:

Yêu cầu đưa ra là hãy đếm tổng số lượng mặt hàng CaCao và Gạo đã bán cho khách hàng A.

Để làm được ta sẽ dùng hàm SUM kết hợp với COUNTIFS và tính dưới dạng mảng. Công thức như sau =SUM(COUNTIFS(C2:C11, “A”,D2:D11,{“CaCao”,”Gạo”})).

Sau khi nhập công thức và nhấn tổ hợp phím Ctrl + Shift + Enter.

Kết quả trả về sẽ là 6. Hàm mảng ở đây thực hiện 3 công việc đó là đếm số lượng CaCao đã bán cho khách hàng A, số lượng gạo đã bán cho khách hàng A. Sau đó cộng tổng 2 giá trị này lại với nhau.

Hàm mảng trong Excel còn rất nhiều cách ứng dụng khác nhau tùy theo yêu cầu thực tế đặt ra. Việc hiểu và biết ứng dụng hàm này sẽ giúp bạn làm việc với dữ liệu nhanh hơn từ đó nâng cao năng suất làm việc. Nếu bạn quan tâm và muốn biết thêm kiến thức về Excel. Hãy theo dõi và đọc những bài viết khác tại Mindovermetal.