Cách dùng công thức mảng trong Excel [Kèm ví dụ minh hoạ]

Một trong những tính năng độc đáo và mạnh mẽ nhất của Excel chính là khả năng tính toán với công thức mảng. Khi nắm rõ bản chất và cách sử dung công thức mảng trong Excel, các bạn sẽ tạo ra được các công thức để thực hiên các phép tính khó cho dù Excel không hổ trợ hoặc các hàm có sẵn trong Excel không thể thực hiện được.

Trong bài viết này, chúng tôi sẽ giới thiệu với các bạn khái niệm về công thức mảng và các ví dụ cụ thể để các bạn nắm được về tính năng khá mạnh mẽ này của Excel.

Xem thêm bài viết về Các hàm cơ bản trong Excel mình đã chia sẻ trong thời gian gần đây.

Cách sử dụng công thức mảng trong Excel

Tìm hiểu công thức mảng

Một mảng là một tập hợp các phần tử có quan hệ hay độc lập với nhau. Ví dụ, {1; 2; “ba”; “four”}.

Trong Excel, một mảng có một một chiều hoặc hai chiều (chính là các dòng, các cột trong Excel). Ví dụ, một vùng dữ liệu nằm trên một dòng hoặc một cột (A1:A5; B2:E2) là các mảng một chiều; nếu một vùng dữ liệu nằm trên ít nhất 2 dòng, 2 cột là mảng 2 chiều (A1:D4).

Công thức mảng không cần lưu trữ các số liệu trong quá trình tính toán tại các ô mà thực hiện tính toán và lưu trữ trong bộ nhớ máy tính. Khi tính toán xong, Excel sẽ trả về kết quả là một ô hoặc một vùng dữ liệu.

Lên đầu trang ↑

Đặc điểm của công thức mảng

– Để kết thúc một công thức mảng, các bạn cần sử dụng tổ hợp phím Ctrl + Shift + Enter (thay vì Enter). Khi đó, cặp ngoặc {} bao quanh công thức để thể hiện đó là công thức mảng. Ví dụ:

{=SUM(LEN($E$12:$H$20))}

– Bạn không thể chỉnh sửa nội dung của 1 ô trong vùng công thức mảng. Bên cạnh đó, bạn không thể tạo thêm cột, dòng mới xen giữa vùng có công thức mảng.

Excel báo lỗi nếu bạn thêm mới dòng, cột trong vùng dữ liệu chứa công thức mảng. Để chuyển tất cả các ô trong công thức mảng, các bạn chọn cả vùng, nhấn F2 để chỉnh sửa và nhấn tổ hợp phím Ctrl + Enter.

Lên đầu trang ↑

Các thao tác với công thức mảng

a. Chọn vùng chứa công thức mảng

Nếu các bạn đã biết vùng chứa công thức mảng của mình, các bạn có thể quét và chọn vùng chứa công thức mảng bằng tay.

Ngoài ra, đơn giản hơn, các bạn có thể chọn 1 ô bất kỳ trong công thức mảng sau đó nhấn F5 hoặc Ctrl + G > Special…. > Current Array.

b. Chỉnh sửa một công thức mảng

Để chỉnh sửa một công thức mảng, các bạn cần chọn tất cả các ô trong công thức mảng, sau đó nhấn phím F2 hoặc click vào thanh công thức và chỉnh sửa như thông thường. Sau khi chỉnh sửa xong, các  bạn  phải  nhấn tổ hợp Ctrl + Shift + Enter để kết thúc.

c. Mở rộng hoặc thu hẹp công thức mảng

Để thay đổi vùng áp dụng công thức mảng, các bạn thực hiện như sau:

• Chọn vùng chứa công thức mảng
• Nhấn F2 để hiệu chỉnh công thức
• Nhấn Ctrl + Enter để chuyển về dạng công thức thường cho vùng chọn.
• Chọn lại vùng áp dụng công thức mảng (mở rộng hoặc thu hẹp vùng chọn so với ban đầu)
• Nhấn F2 để hiệu chỉnh công thức
• Nhấn Ctrl + Shift + Enter để kế thúc chỉnh sửa

Lên đầu trang ↑

Ví dụ với công thức mảng trả về kết quả tại một ô

a. Đếm số ký tự trong một vùng dữ liệu

Như đã giới thiệu tại bài viết này, nếu chúng ta chỉ sử dụng hàm LEN thì chúng ta phải tính số lý tự cho từng ô trong vùng dữ liệu và tổng cộng lại để ra tổng số ký tự trong vùng

Tuy nhiên,  ta  dùng  công  thức  mảng kết hợp hàm LEN và hàm SUM. Công thức mảng sử dụng hàm LEN để tạo ra một mảng trong bộ nhớ gồm số ký tự trong mỗi ô của dãy rồi sau đó, tính tổng của dãy.

b. Đếm/Tính tổng theo điều kiện

Trong Excel, chúng ta có thể sử dụng các hàm SUMIF, SUMIFS, COUNTIF, COUNTIFS để tính tổng, đếm theo một điều kiện nào đó.

Trong ví dụ trên, chúng ta cần đếm xem có bao nhiêu bạn học sinh mà tên chứa 4 ký tự.

Tại ô C14, chúng ta sử dụng công thức mảng:

 {=SUM((LEN(B3:B12)=4)*1)}

Trong đó:

+ Hàm (LEN(B3:B12)=4) sẽ tạo ra 1 mảng chứa các giá trị True (nếu tên có 4 ký tự) và False (nếu tên có độ dài khác 4 ký tự). Với điều kiện B3:B12, hàm (LEN(B3:B12)=4) sẽ trả về mảng:

 {FALSE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}

+ Phép tính *1 để nhân với từng giá trị trong mảng trên, nếu True * 1 = 1 và False * 1 = 0. Sau phép tính *1, chúng ta nhận được mảng {0;1;1;1;0;0;0;0;0;0}

+ Hàm Sum có tác dụng cộng các giá trị trong mảng vừa được tạo ra và chúng ta nhận được kết quả là 3 tương ứng với các trường hợp tên là “Bích”, “Bình”, “Bình”.

c. Tính tổng/ trung bình cộng của n giá trị lớn nhất/ nhỏ nhất

Trong ví dụ sau, chúng ta sẽ tính điểm trung bình của 03 học sinh có điểm toán cao nhất. Đó là các học sinh có số thứ tự 3, 8, 9 với điểm thi lần lượt là 9.5; 9.9; 8.8.

Tại ô C14, các bạn sử dụng công thức mảng:

 {=AVERAGE(LARGE(C3:C12;ROW(INDIRECT("1:3"))))}

và nhấn Ctrl + Shift + Enter để kết thúc công thức. Trong đó:

+ Hàm ROW(INDIRECT(“1:3”)) sẽ tạo ra một mảng gồm các số nguyên từ 1-3 như sau {1;2;3} trong bộ nhớ máy.

+ Hàm LARGE sẽ trả về giá trị lớn thứ k trong vùng dữ liệu được chọn. Ở đây, hàm LARGE sẽ trả về giá trị lớn thứ 1, 2 và 3 (tương ứng với các giá trị trong mảng số nguyên vừa được tạo ra) trong vùng C3:C12

+ Hàm AVERAGE sẽ trả về trung bình cộng của 03 điểm toán lớn nhất.

Lưu ý: Trong ví dụ trên, các bạn sử dụng hàm SMALL thay cho LARGE để tính cho các học sinh có điểm toán thấp nhất.

d. Xếp hạng liên tục

Trong Excel, chúng ta sử dụng hàm RANK để tính thứ tự xếp hạng của một phần tử trong dãy. Ví dụ sau sẽ sắp xếp các học sinh theo thứ tự Điểm tổng kết.

Tuy nhiên, hàm RANK không tạo ra một thứ hạng liên tục. Nếu các bạn để ý sẽ thấy, học sinh thứ 1 và thứ 4 có cùng điểm tổng kết là 8 và sẽ cùng xếp ở vị trí thứ 3 theo lý thuyết. Tuy nhiên, hàm RANK lại sắp xếp 2 học sinh này ở vị trí thứ 4 mà bỏ qua vị trí thứ 3 (nguyên nhân do vị trí thứ 2 có 2 bạn cùng được xếp).

Để giải quyết tình trạng này, chúng ta sẽ sử dụng công thức mảng như sau:

Tại ô F3, các bạn nhập công thức mảng:

 {=SUM(IF(D3<$D$3:$D$7;1/COUNTIF($D$3:$D$7;$D$3:$D$7)))+1}

và sao chép cho các ô còn lại. Trong công thức này, điều kiện D3<$D$3:$D$7 sẽ tạo nên thứ tự sắp xếp giảm dần dựa vào Điểm tổng kết. Trong trường hợp muốn sắp xếp tăng dần, các bạn sử dụng điều kiện D3>$D$3:$D$7.

Lên đầu trang ↑

Ví dụ với công thức mảng trả về kết quả tại vùng dữ liệu

Một trong những áp dụng lớn nhất của công thức mảng là tạo dương lịch cho năm bất kỳ. Trình tự các bước thực hiện như sau:

– Bước 1: Điền chuỗi n_[i] (i  = 1..a*b) vào bảng Row_[1..a] Columns_[1..b]

– Bước 2: Điền ngày tháng vào (do ngày tháng có công thức cộng trừ luôn)

– Bước 3: So sánh với tháng hiện tại để hiện thông tin phù hợp

File ví dụ, các bạn có thể tải về trong các liên kết dưới đây.

1. Box.com
2. Mega

Lên đầu trang ↑

Lời kết

Công thức mảng là một trong những nội dung khó và có nhiều áp dụng trong công việc khi phải xử lý các yêu cầu phức tạp trong Excel. Hi vọng rằng với bài viết này, các bạn có thể hiểu và dễ dàng sử dụng khi cần thiết trong công việc và học tập.

Nếu như có bất cứ vấn đề nào khác trong khi sử dụng và thực hiện như hướng dẫn trên thì bạn có thể để lại bình luận trong phần dưới đây.

Chúc bạn thành công!