Hướng dẫn sử dụng công thức mảng trong Excel qua ví dụ cụ thể

XEM NHANH BÀI VIẾT

1 Những điều cần biết về công thức mảng trong Excel

1.1 Mảng trong Excel là gì
1.2 Thế nào là một công thức mảng?
1.3 Nhập công thức mảng bằng cách nào?
1.4 Những điều bạn cần phải nhớ khi nhập một công thức mảng

2 Công thức mảng với 1 ô và công thức mảng gồm nhiều ô trong Excel

2.1 Công thức mảng với ô đơn
2.2 Công thức mảng cho nhiều ô trong Excel
2.3 Sử dụng hàm mảng trong Excel và trả kết quả cho một mảng gồm nhiều ô
2.4 Những lưu ý khi thao tác với công thức mảng gồm nhiều ô trong Excel:

3 Hằng mảng trong Excel

3.1 Hằng mảng ngang
3.2 Hằng mảng dọc
3.3 Hằng mảng theo 2 hướng
3.4 Một số lưu ý khi làm việc với hằng mảng trong Excel

4 Ví dụ về hàm mảng trong công thức Excel

4.1 Tìm tổng các số lớn nhất trong một vùng.
4.2 Tìm tổng số tiền bán hàng cho từng loại sản phẩm

Công thức mảng trong Excel là một công cụ thật sự mang lại sức mạnh cho người dùng trong việc giải quyết những vấn đề khó. Một công thức mảng đơn giản có thể biểu diễn nhiều phép tính cùng lúc và vì thế nó có thể thay thế cho rất nhiều công thức thường dùng. Thật lạ là hơn 90% người dùng chưa bao giờ sử dụng công thức mảng trong bảng tính của họ bởi vì họ e ngại cách bắt đầu với một công thức mảng.

Vì vậy, công thức mảng là một trong số những điều mơ hồ nhất trong Excel đối với người dùng và người học. Bài viết này sẽ giúp bạn hiểu rõ về công thức mảng theo cách dễ dàng nhất.

Những điều cần biết về công thức mảng trong Excel

Mảng trong Excel là gì

Mảng trong Excel là tập hợp các phần tử, mỗi phần tử có thể là chuỗi, số, một dòng, một cột hay nhiều dòng, nhiều cột.

Ví dụ, bạn có danh sách các món ăn và bạn định đặt chúng vào một mảng trong Excel, danh sách các món ăn gồm ("Milk", "Eggs", "Butter", "Corn flakes")

Thực hiện

Giả sử, bạn thực hiện chọn các ô để nhập vào danh sách này. Ở ví dụ này, tôi chọn các ô từ A1 đến D1.
Bạn đưa trỏ chuột đến thanh công thức và nhập: = {“Milk”, “Eggs”,”Butter”,”Corn flakes”}
Nhấn CTRL + SHIFT + ENTER

Kết quả là bạn sẽ có một mảng như sau:

Hướng dẫn sử dụng công thức mảng trong Excel qua ví dụ cụ thể

Thế nào là một công thức mảng?

Điều tạo nên sự khác biệt giữa công thức mảng và công thức thường là một công thức mảng có thể xử lí nhiều giá trị kết quả thay vì chỉ một như công thức thường. Hay nói cách khác, một công thức mảng trong Excel sẽ xác định được các giá trị riêng của các phần tử trong mảng. Nó có thể trả về nhiều giá trị cùng lúc. Vì thế, kết quả trả về cho một công thức mảng sẽ là một mảng giá trị.

Công thức mảng được hỗ trợ trong các phiên bản Excel 2019, Excel 2016, Excel 2013, Excel 2010, Excel 2007 và các Phiên bản thấp hơn nữa.

Nhập công thức mảng bằng cách nào?

Chúng ta có dữ liệu mẫu cho ví dụ như sau:

Chúng ta sẽ dùng công thức để tính tổng số tiền bán được cho mỗi sản phẩm ở cột D và tổng thu ở ô D7.

Thông thường, bạn sẽ tính tổng tiền thu được cho mỗi sản phẩm ở cột D bằng công thức “= B2 * C2“, “= B3 * C3”, … Sau đó tính tổng thu ở ô D7:= SUM (D2:D6)

Điều khác biệt gì xảy ra khi tôi sử dụng công thức mảng?

Tổng số tiền thu được ở mỗi sản phẩm từ ô D2 đến ô D6 sẽ được tính bằng thật nhanh và đơn giản như sau:

Bước 1: Chọn vùng D2:D6

Bước 2: Nhập “ = B2:B6 * C2:C6 “

Bước 3: Nhấn CTRL + SHIFT + ENTER

Bạn sẽ có kết quả cho tổng thu của mỗi sản phẩm ở các ô tương ứng D2:D6

Việc tính tổng thu cho tất cả các sản phẩm có thể thực hiện bằng một trong 2 cách sau:

- Cách thông thường: D7:= SUM (D2:D6)

- Công thức mảng:

Chọn ô D7
Nhập “ = SUM (B2:B6 * C2:C6)
Nhấn CTRL + SHIFT + ENTER

Khi sử dụng công thức mảng để tính tổng thu cho tất cả các sản phẩm, thậm chí bạn không cần tính tổng số tiền thu được của mỗi sản phẩm ở các ô D2:D6

Công thức mảng với ô đơn

Dữ liệu mẫu liệt kê các sản phẩm được bán trong tháng 1 và tháng 2. Việc bạn cần làm là tìm xem số lượng bán ra chênh lệch cao nhất giữa tháng 1 với tháng 2 của các sản phẩm là bao nhiêu?

Thông thường, để tìm được kết quả này, bạn sẽ chèn thêm cột D vào bên phải của bảng dữ liệu. Sau đó, bạn tính chênh lệch ở từng sản phẩm bằng công thức như D2:= C2 – B2. Cuối cùng F1:= MAX (D2:D6)

Sử dụng công thức mảng, bạn không cần phải chèn thêm cột D vào bảng dữ liệu và sử dụng công thức để tính chênh lệch cho từng sản phẩm. Việc bạn cần làm là nhập công thức vào ô F1 như sau: = MAX (C2:C6 – B2:B6) rồi nhấn CTRL + SHIFT + ENTER, bạn sẽ nhận được kết quả ngay lập tức.

Công thức mảng cho nhiều ô trong Excel

Chúng ta có bảng dữ liệu sau:

Ở ví dụ này, chúng ta sẽ sử dụng công thức mảng cho nhiều ô để tính thuế cho các sản phẩm bán được. Thuế được tính 10% tổng giá trị sản phẩm bán được. Chúng ta thực hiện theo các bước sau:

Bước 1: Chọn vùng D2:D6

Bước 2: Nhập B2:B6 * C2:C6 * 0.1 vào thanh công thức

Bước 3: Nhấn tổ hợp phím CTRL + SHIFT + ENTER

Sử dụng hàm mảng trong Excel và trả kết quả cho một mảng gồm nhiều ô

Chúng ta có bảng dữ liệu sau:

Trong Excel, có một số hàm đã được thiết kế dùng cho mảng gồm nhiều ô gọi là “hàm mảng”. TRANSPOSE là một trong số các hàm mảng đó, hàm TRANSPOSE dùng để chuyển đổi dữ liệu của bảng từ cột thành dòng. Chúng ta thực hiện như sau:

Bước 1: Chọn một vùng gồm nhiều ô, nơi bạn muốn chuyển dữ liệu của bảng sang. Khi chúng ta muốn chuyển từ dòng sang cột hoặc ngược lại thì phải chắc chắn rằng số dòng, số cột ở bảng nguồn phải tương ứng với số cột, số dòng được chọn ở bảng đích.

Trong ví dụ này, bảng nguồn gồm 6 dòng, 4 cột. Như vậy vùng trống cần chọn cho bảng đích gồm 6 cột, 4 dòng.

Bước 2: Nhấn F2 để vào chế độ chỉnh sửa

Bước 3: Nhập hàm mảng = TRANSPOSE ($A$1:$D$6)

Bước 4: Nhấn tổ hợp phím CTRL + SHIFT + ENTER. Bạn có kết quả bảng dữ liệu đã được chuyển đổi như hình trên.

Hằng mảng ngang

Một hằng mảng ngang sẽ nằm trên một dòng (row).

Bước 1: Bạn chọn số ô tương ứng cần tạo hằng mảng ngang trên một dòng. Trong ví dụ này, bạn chọn các ô A1:D1

Bước 2: Bạn nhập = {1, 2, 3, 4} trên thanh công thức

Bước 3: Nhấn tổ hợp phím CTRL + SHIFT + ENTER

Kết quả bạn sẽ có một hằng mảng được Excel bao bọc bởi một dấu ngoặc nhọn { } khác.

Hằng mảng dọc

Một hằng mảng dọc sẽ nằm trên một cột

Bước 1: Chọn số ô tương ứng cần tạo hằng mảng trên một cột.

Bước 2: Nhập = {1, 2, 3, 4} trên thanh công thức

Bước 3: Nhấn tổ hợp phím CTRL + SHIFT + ENTER

Kết quả bạn sẽ có một hằng mảng được Excel bao bọc bởi một dấu ngoặc nhọn { }

Hằng mảng theo 2 hướng

Tạo hằng mảng theo 2 hướng cũng tương tự như việc tạo hằng mảng ngang hay hằng mảng dọc. Tuy nhiên, bạn sẽ dùng dấu “ , “ để phân cách các phần tử giữa các cột, dùng dấu “ ; “ để phân cách phần tử giữa các dòng.

Tìm tổng các số lớn nhất trong một vùng.

Bây giờ, bạn hãy tạo một hằng mảng dọc chứa nhiều số tự nhiên. Trong ví dụ này, bạn muốn tính tổng của 3 số tự nhiên lớn nhất trong vùng mảng của bạn.

Bạn tạo hằng mảng có tên là “range” và đặt dữ liệu của hằng mảng này trong vùng A1:A7 gồm các phần tử {1, 20, 15, 35, 4, 40, 10}
Để xác định các số tự nhiên lớn nhất trong vùng mảng, sử dụng công thức mảng với hàm LARGE là cách giúp bạn có kết quả nhanh chóng hơn rất nhiều so với cách làm thông thường. Sau đó, bạn dùng hàm SUM để tính tổng các số lớn nhất này. Bạn nhập công thức = SUM (LARGE (A1:A7, {1,2,3}))
Nhấn tổ hợp phím CTRL + SHIFT + ENTER. Bạn sẽ có kết quả là 95. Tổng 3 số tự nhiên lớn nhất trong vùng theo thứ tự 1, 2, 3 là 40, 35, 20.

Tìm tổng số tiền bán hàng cho từng loại sản phẩm

Trong ví dụ này, bạn sẽ tính tổng số tiền mà nhân viên Mike đã bán các sản phẩm Apples.

Như vậy, với điều kiện là Seller = “Mike” và Product = “Apples”, số tiền bán được chỉ có 2 lượt là $10 và $30 như dữ liệu hình bên trên. Theo cách thực hiện thông thường, bạn sẽ gặp khó khăn một chút và trước tiên bạn sẽ phải lọc ra dữ liệu mà Mike bán các sản phẩm Apples. Sau đó mới tính tổng số tiền bán được của các mặt hàng này. Bằng cách sử dụng công thức mảng, yêu cầu này trở nên đơn giản hơn bao giờ hết.

Nhập công thức mảng tại ô F3 như sau:

Nhập = SUM ((A2:A9 = “Mike”) * (B2:B9 = “Apples”) * (C2:C9))
Nhấn tổ hợp phím CTRL + SHIFT + ENTER

Giải thích kết quả công thức mảng có được:

{= SUM ((A2:A9 = “Mike”) * (B2:B9 = “Apples”) * (C2:C9))}

(A2:A9 = “Mike”) cho kết quả {1, 0, 0, 1, 0, 0, 1, 1} với 1 là TRUE và 0 là FALSE
(B2:B9 = “Apples”) cho kết quả {1, 0, 0, 1, 0, 1, 0, 0} với 1 là TRUE và 0 là FALSE
(C2:C9) chính là vùng dữ liệu {10, 30, 20, 30, 40, 10, 10, 20}
Kết quả {1, 0, 0, 1, 0, 0, 1, 1} * {1, 0, 0, 1, 0, 1, 0, 0} * {10, 30, 20, 30, 40, 10, 10, 20} ta được: {10, 0, 0, 30, 0, 0, 0, 0}
Cuối cùng {SUM (10, 0, 0, 30, 0, 0, 0, 0)} ta được 40

Hi vọng, bài viết này thật sự hữu ích cho bạn trong việc học cách sử dụng công thức mảng trong Excel. Đừng quên theo dõi Gitiho ngay hôm nay nhé, chúng tôi còn nhiều mẹo và thủ thuật tin học văn phòng thú vị khác chờ bạn khám phá.

Để nâng cao kiến thức Excel, bạn hãy tham gia ngay khóa học Tuyệt đỉnh Excel, trở thành bậc thầy Excel trong 16 giờ của Gitiho.com. Trong khóa học này bạn sẽ được tìm hiểu cách sử dụng các công cụ: conditional formatting, data validation, pivot table… để ứng dụng vào trong công việc. Ngoài ra bạn còn biết cách sử dụng các hàm trong excel, từ các hàm cơ bản cho tới các hàm nâng cao, phức tạp. Hiện nay hệ thống đang có ưu đãi rất lớn cho bạn khi đăng ký tham gia khóa học.