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

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:

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

Những điều bạn cần phải nhớ khi nhập một công thức mảng

Việc kết hợp tổ hợp phím CTRL + SHIFT + ENTER là một điều kỳ diệu để biến công thức bình thường thành công thức mảng. 

Khi bạn nhập một công thức mảng trong Excel, những điều quan trọng sau đây bạn nhất định phải nhớ: 

1. Để hoàn thành việc nhập một công thức mảng, bạn phải nhấn CTRL + SHIFT + ENTER. Sau đó, Excel sẽ tự động chèn vào 2 dấu ngoặc nhọn { } ở đầu và cuối công thức đã được nhập. Vì vậy, khi bạn chọn một ô bất kỳ trong vùng nhập công thức mảng, bạn sẽ thấy dấu ngoặc nhọn { } xuất hiện trong công thức.
2. Để hiệu chỉnh công thức mảng, bạn chỉ cần chọn 1 ô trong vùng nhận công thức mảng và hiệu chỉnh công thức mảng trên thanh công thức. Tuy nhiên, kết thúc việc hiệu chỉnh, bạn phải nhấn CTRL + SHIFT + ENTER để mọi thay đổi trên công thức được lưu lại.
3. Bạn sẽ thoát khỏi việc nhập hay hiệu chỉnh trên công thức mảng bằng phím “ESC”

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

Một công thức mảng trong Excel có thể trả về kết quả gồm một hoặc nhiều ô. Một công thức mảng được nhập trên một vùng gồm nhiều ô thì được gọi là công thức nhiều ô. Một công thức mảng chỉ trú ngụ trong một ô thì được gọi là công thức ô đơn. 

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. 

Những lưu ý khi thao tác với công thức mảng gồm nhiều ô trong Excel: 

1. Bạn phải chọn vùng (gồm nhiều ô) là nơi bạn sẽ đặt kết quả trả về rồi mới nhập công thức mảng vào

2. Xóa công thức mảng bằng cách nào?

• Cách 1: Bạn phải chọn tất cả các ô được chứa trong công thức cần xóa và nhấn DELETE
• Cách 2: Bạn chọn công thức mảng cần xóa trên thanh công thức. Sau đó, bạn nhấn phím DELETE. Tiếp tục, nhấn tổ hợp phím CTRL + SHIFT + ENTER

3. Bạn không thể hiệu chỉnh bất cứ gì hay di chuyển nội dung của một phần tử bất kỳ trong một mảng. Bạn cũng không thể thêm mới một ô hay xóa một ô trong vùng mảng. Khi bạn cố gắng làm một trong các điều vừa kể, Excel sẽ cảnh báo cho bạn biết điều này không thể “You cannot change part of an array”

Hằng mảng trong Excel

Trong Excel, một hằng mảng đơn giản là một tập hợp các giá trị tĩnh. Những giá trị này sẽ không bị thay đổi khi bạn copy công thức đến vị trí khác. 

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. 

Một số lưu ý khi làm việc với hằng mảng trong Excel

1. Các thành tố trong hằng mảng

• Một hằng mảng có thể chứa các giá trị số, chuỗi, giá trị logic (True, False), giá trị lỗi đặt cách nhau bởi dấu phẩy hoặc dấu chấm phẩy.
• Bạn có thể nhập một giá trị số như số nguyên, số thập phân hay số khoa học. Nếu bạn sử dụng kiểu chuỗi trong hằng mảng, bạn hãy nhớ đặt giá trị chuỗi của bạn trong dấu nháy kép “ “.
• Một hằng mảng thì không thể chứa một hằng mảng khác, không chứa tham chiếu đến ô, không chứa vùng, dữ liệu ngày tháng, tên như là tên vùng, công thức hay hàm. 

2. Đặt tên hằng mảng

• Tạo một hằng mảng còn dễ hơn là sử dụng nó, bạn chỉ việc đặt cho nó một cái tên.
• Trên menu bạn chọn thẻ Formulas > chọn nhóm Defined Names. Sau đó, bạn Click chọn Defined Name
• Nhập tên vào mục Name
• Trong mục Refers to, bạn nhập các phần tử của hằng mảng cần tạo trong dấu ngoặc nhọn { } và đừng quên dấu = đứng trước
• Click OK để lưu hằng mảng vừa tạo

3. Đặt hằng mảng vào Sheet

• Chọn số ô trong cùng một dòng hoặc một cột có số phần tử tương ứng với số phần tử trong hằng mảng bạn có.
• Gõ tên hằng mảng trên thanh công thức và nhớ là luôn có dấu = ở trước. \
• Nhấn tổ hợp phím CTRL + SHIFT + ENTER và bạn sẽ có hằng mảng được đặt vào sheet của mình

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

Bạn sẽ được hướng dẫn học cách sử dụng hằng mảng trong công thức bằng các ví dụ đơn giản, dễ hiểu. 

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. Chi tiết xem tại: Gitiho.com