Công thức mảng trong Excel

Trong bài viết tiếp theo của tutorial Công thức và Hàm Excel này Học TV sẽ hướng dẫn công thức mảng Excel, cách để nhập nó chính xác trong bảng tính của bạn và cách sử dụng các công thức mảng trong Excel bạn nhé.

Các công thức mảng trong Excel là một công cụ vô cùng hữu ích và đây cũng là cái khó để kiểm soát nhất trong Excel. Một công thức mảng Excel đơn giản có thể thực hiện nhiều phép tính và thay thế hàng ngàn công thức thông thường. Tuy nhiên, ước chừng đến 96% người dùng Excel chưa bao giờ sử dụng tới các công thức mảng trong bảng tính, chỉ vì họ sợ phải bắt đầu học chúng từ con số 0.

Mảng trong Excel là gì?

Trước khi chúng ta bắt đầu tìm hiểu về các công thức mảng Excel, thì chúng ta cần tìm hiểu thế nào là thuật ngữ “mảng” đã. Về cơ bản, một mảng trong Excel là một tập hợp các mục. Các mục này có thể là văn bản hoặc số và chúng có thể nằm trong một hàng/cột, hoặc trong nhiều hàng và cột khác nhau.

Ví dụ: nếu bạn nhập danh sách mua hàng hàng tuần của bạn vào một định dạng mảng Excel, thì nó sẽ kiểu như:

{"Thịt", "Cá","Trứng","Sữa"}

Cách nhập mảng vào Excel như sau:

1. Chọn dải ô từ A1 đến D1
2. Nhập công thức sau trên thanh công thức ={"Thịt", "Cá","Trứng","Sữa"}
3.  Sau đó nhấn CTRL + SHIFT + ENTER, và bạn sẽ nhận được kết quả như sau:

Những gì bạn vừa làm là tạo một mảng một chiều theo chiều ngang. Nãy giờ mọi thứ vẫn rất đơn giản, phải không nào? Bây giờ chúng ta sẽ tạo một mảng theo chiều dọc nhé

1. Chọn ô Từ A3 đến A7
2. Nhập công thức sau trên thanh công thức ={"Kim";"Mộc";"Thổ";"Thủy";"Hỏa"}
   
3. Sau đó nhấn CTRL + SHIFT + ENTER, và bạn sẽ nhận được kết quả như sau:

Trong Excel sử dụng dấu phẩy "," để tạo mảng theo hàng, dấu chấm phẩy ";" để tạo mảng theo chiều dọc

Công thức mảng Excel là gì?

Sự khác biệt giữa một công thức mảng và các công thức Excel thông thường khác là công thức mảng sẽ xử lý nhiều thay vì chỉ một giá trị mà thôi. Nói cách khác, một công thức mảng trong Excel sẽ đánh giá tất cả các giá trị riêng lẻ trong một mảng, và thực hiện nhiều phép tính trên một hoặc nhiều mục mà có thỏa các điều kiện được biểu thị trong công thức.

Một công thức mảng không chỉ xử lý đồng thời một số giá trị, mà nó cũng có thể cùng 1 lúc trả lại một số các giá trị. Vì thế, các kết quả trả lại bởi công thức mảng cũng là một mảng.

Công thức mảng Excel không chỉ khả dụng với Office 2019, Office 365 mà có sẵn trong tất cả các phiên bản của Excel 2016,Excel 2013, Excel 2010, Excel 2007 và trước đó nữa.

Và bây giờ, có vẻ như đã đến lúc chúng ta cần bắt tay tạo công thức mảng đầu tiên cho bạn.

Ví dụ tạo công thức mảng đơn giản 

Giả sử bạn có một số mục trong cột B, số lượng trong cột C và cột D biểu thị giá của chúng, bài toán của bạn là tính tổng doanh thu.

Dĩ nhiên chúng ta có thể tính tổng của mỗi món hàng tại cột E theo công thức =C2*D2 sau đó cộng các giá trị thu được lại với nhau hoặc dùng hàm =SUM(E2:E8) để tính ra kết quả.

Tuy nhiên, sử dụng một công thức mảng có thể mang đến cho bạn những điểm đột phá quan trọng, từ khi nó được Excel dùng trong lưu trữ kết quả trung gian trong bộ nhớ, thay vì phải tạo thêm cột bổ sung. Vì vậy, một công thức mảng đơn chỉ cần thực hiện 2 bước nhanh chóng nho nhỏ như sau để giải quyết bài toán trên:

1. Chọn một ô trống và nhập vào công thức sau:
2. =SUM(C2:C8*D2:D8)
   
3. Nhấn phím tắt CTRL + SHIFT + ENTER để hoàn tất công thức mảng. Khi nhấn tổ hợp phím trên, Microsoft Excel đặt công thức trên trong {dấu ngoặc nhọn}, hay được gọi một dấu hiệu dễ nhìn thấy cho biết đó là một công thức mảng.

Công thức sẽ nhân giá trị trong mỗi hàng riêng của mảng được chỉ định (các ô từ C2 đến C8), có thêm các tổng (phụ), và kết quả cuối cùng là tổng doanh thu.

Ví dụ đơn giản trên cho thấy sức mạnh của công thức mảng trong Excel. Khi bạn làm việc với hàng trăm hoặc hàng ngàn hàng ô chứa dữ liệu, thì bạn có thể tự tính toán nó sẽ tiết kiệm bao nhiêu thời gian với một công thức mảng trong mỗi ô.

Tại sao phải sử dụng công thức mảng trong Excel

Công thức mảng Excel là công cụ tiện dụng nhất để thực hiện các phép tính tinh vi và các tác vụ phức tạp. Một công thức mảng đơn có thể thay thế hàng trăm công thức thông thường. Công thức mảng có thể sử dụng cho các công việc như:

• Tính tổng các số thỏa các điều kiện đã cho, ví dụ tính tổng N giá trị lớn nhất hoặc nhỏ nhất trong một dải.
• Tính tổng các giá trị các hàng, hoặc trong hàng/cột thứ N.
• Đếm các giá trị của tất cả hoặc đếm những ký tự nhất định trong một dải xác định.

Cách nhập công thức mảng trong Excel 

Sử dụng tổ hợp phím CTRL + SHIFT + ENTER là cách để biến một công thức thông thường thành một công thức mảng.

Khi nhập một công thức mảng trong Excel, có những điều quan trọng mà bạn cần ghi nhớ:

• Một khi bạn đã hoàn thành gõ công thức và đồng thời nhấn các phím CTRL + SHIFT+ ENTER, thì Excel sẽ tự động đặt công thức đó trong {dấu ngoặc nhọn}. Khi bạn chọn một hoặc các ô như vậy, bạn có thể thấy các dấu ngoặc nhọn trong thanh công thức. Dấu ngoặc nhọn trong công thức cho bạn biết rằng có một công thức mảng trong đó.
• Việc nhập các dấu ngoặc nhọn bao quanh công thức bằng tay sẽ không có tác dụng chuyển công thức trên sang định dạng mảng mà Excel sẽ hiểu đó là dạng text thông thường. Bạn phải nhấn tổ hợp phím Ctrl + Shift + Enter để hoàn thành công thức mảng.
• Mỗi lần bạn chỉnh sửa một công thức mảng, các dấu ngoặc nhọn sẽ biến mất và bạn phải nhấn Ctrl + Shift + Enter để lưu các thay đổi trong công thức của bạn.
• Nếu bạn quên nhấn Ctrl + Shift + Enter, thì công thức của bạn sẽ hoạt động như một công thức Excel thông thường và chỉ xử lý các giá trị đầu tiên trong mảng được chỉ định.
• Bởi vì tất cả các công thức mảng trong Excel đều đòi hỏi phải nhấn tổ hợp phím Ctrl + Shift + Enter, đôi khi được gọi là công thức CSE.
• 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 cũng 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.

Sử dụng F9 để xem các phần trong 1 công thức mảng

Khi làm việc với các công thức mảng trong Excel, bạn có thể quan sát cách chúng tính toán và lưu trữ các mục của mảng (mảng nội bộ) để hiển thị kết quả cuối cùng trong một ô. Để làm điều này, hãy click chuột chọn một hoặc nhiều đối số trong công thức mảng sau đó nhấn F9 , nhấn Esc để tắt chế độ đánh giá công thức.

Ví dụ click vào  trong công thức mảng trên 

{=SUM(C2:C8*D2:D8)}

Nhấn chọn C2:C8 hoặc D2:D8 

rồi nhấn F9 

Lưu ý: Hãy chắc chắn rằng bạn đã chọn một số phần của công thức trước khi nhấn phím F9, nếu không khi nhấn F9 thì nó sẽ chỉ đơn giản thay thế công thức mảng của bạn bằng (các) giá trị tính toán mà thôi.

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

1. Chọn vùng chứa công thức mảng: Nhấn đúp vào ô chứa công thức mảng
2. 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.
3. Mở rộng hoặc thu hẹp công thức mảng: Chọn vùng chứa công thức mảng sau đó 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 Ctrl + Shift + Enter để kế thúc chỉnh sửa.

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

Công thức mảng có thể trả lại kết quả trong một ô hoặc trong nhiều ô. 

• Công thức mảng nằm trong một ô duy nhất được gọi là công thức ô đơn.
• Một công thức mảng được nhập vào trong một dải ô được gọi là công thức đa ô. 

Các hàm  như SUM, AVERAGE, AGGREGATE, MAX, MIN, có thể tính toán biểu thức mảng khi nhập vào một ô duy nhất bằng tổ hợp phím Ctrl + Shift + Enter. Đồng thời chúng cũng có thể trả về đa ô nếu được nhập vào trong 1 dải ô

Có một vài hàm mảng được thiết kế để trả về các mảng đa ô, ví dụ như TRANSPOSE, TREND, FREQUENCY, LINEST, v.v.

Các ví dụ dưới đây sẽ minh hoạ cách sử dụng một công thức mảng một ô và nhiều ô trong Excel.

Ví dụ 1: Công thức mảng với ô đơn

Giả sử bạn có bảng tính đi siêu thị như sau. Bạn muốn tìm ra sản phẩm tốn nhiều tiền nhất khi đi siêu thị của mình. 

Thông thường các bán sẽ tính gia từng sản phẩm 1 sau đó tìm giá trị lớn nhất.

Tuy nhiên, một công thức mảng Excel không cần một cột bổ sung vì nó lưu trữ hoàn hảo tất cả các kết quả trung gian trong bộ nhớ. Vì vậy, bạn chỉ cần nhập công thức sau 

=MAX(C2:C8*D2:D8)

và nhấn Ctrl + Shift + Enter mà thôi. Kết quả sẽ ra ngay và luôn như sau

Ví dụ 2: Công thức mảng đa ô trong Excel

Trong ví dụ trước của hàm SUM, giả sử bạn phải trả thêm 10% thuế cho mỗi mặt hàng và bạn muốn tính số tiền trả thuế cho mỗi sản phẩm chỉ với một công thức.

1. Trước tiên, bạn cần chọn dải ô trong một cột trống, ví như E2:E7
2. Nhập công thức sau vào thanh công thức: =C2:C8*D2:D8*0.1
3. Nhấn Ctrl + Shift + Enter, sau đó Excel sẽ tạo công thức mảng và đặt công thức mảng của bạn trong mỗi ô trong dải đã chọn
4. Bạn sẽ nhận được kết quả trả về như sau: 

Sử dụng Hàm mảng Excel để trả về mảng nhiều ô

Như đã đề cập, thì Microsoft Excel cung cấp một vài cái gọi là “các hàm mảng” được thiết kế đặc biệt để làm việc với các mảng đa ô. Hàm TRANSPOSE là một trong những hàm như vậy và chúng mình sẽ sử dụng nó để chuyển đổi vị trí trong bảng ở trên, tức là chuyển hàng sang cột và ngược lại.

Ví dụ sử dụng hàm mảng Excel trả về mảng nhiều ô

Chọn một dải các ô trống mà bạn muốn xuất ra bảng sau khi chuyển đổi. Vì chúng ta đang chuyển đổi các hàng thành các cột, nên hãy chắc chắn rằng bạn chọn cùng một số hàng và cột như số các cột và các hàng tương ứng trong bảng nguồn (bảng gốc) bạn có. 

1. Trong ví dụ này, chúng tôi đang lựa chọn 8 cột và 5 hàng và đánh dấu cho các bạn thấy
2. Nhấn F2 để vào chế độ chỉnh sửa.
3. Nhập công thức mảng: =TRANSPOSE($A$1:$E$8) và nhấn Ctrl + Shift + Enter 
4. Kết quả sẽ như sau:

Lưu ý khi làm việc với công thức mảng nhiều ô

Khi làm việc với các công thức mảng đa ô trong Excel, hãy đảm bảo tuân theo các quy tắc dưới đây để có được kết quả chính xác:

• Chọn dải ô mà bạn muốn xuất ra kết quả trước khi nhập công thức.
• Để xóa một công thức mảng đa ô, hoặc là bạn sẽ chọn tất cả các ô chứa nó và nhấn DELETE, hoặc bạn phải chọn toàn bộ công thức trong thanh công thức, nhấn DELETE, và sau đó nhấn Ctrl + Shift + Enter.
• Bạn không thể chỉnh sửa hoặc di chuyển nội dung của một ô riêng lẻ trong một công thức mảng của Excel, và bạn cũng không thể chèn các ô mới vào hoặc xóa các ô hiện tại từ một công thức mảng đa ô. Bất cứ khi nào bạn thử làm điều này, thì Microsoft Excel sẽ đều đưa ra cảnh báo You cannot change part of an array (“Bạn không thể thay đổi một phần của mảng”).
• Bạn nên nhập một công thức mảng đa ô trong một dải ô có cùng kích cỡ với mảng có kết quả được trả về. Nếu công thức mảng trong Excel của bạn chứa một mảng mà lớn hơn dải bạn đã chọn, thì những giá trị vượt quá sẽ không xuất hiện trên bảng tính. Nếu một mảng trả về bởi công thức nhỏ mà hơn dải đã chọn, lỗi #N/A sẽ xuất hiện trong các ô bổ sung.
• Nếu công thức của bạn có thể trả về một mảng với một số lượng các phần tử có thể thay đổi, thì hãy nhập nó vào một dải bằng hoặc lớn hơn mảng lớn nhất mà được bởi công thức trả về và hãy lồng công thức của bạn trong hàm IFERROR.
• Để thu nhỏ công thức mảng, tức là áp dụng nó cho ít ô hơn, bạn cần phải xoá công thức hiện tại trước rồi mới nhập một công thức mới vào.
• Để mở rộng công thức mảng, ví dụ áp dụng nó cho nhiều ô hơn, bạn cần chọn tất cả các ô có chứa công thức hiện tại cộng với các ô rỗng mà bạn muốn có công thức trong đó, rồi nhấn F2 để chuyển sang chế độ chỉnh sửa, để điều chỉnh các tham chiếu trong công thức và nhấn Ctrl + Shift + Enter để cập nhật nó.
• Bạn không thể sử dụng công thức mảng đa ô trong định dạng bảng Excel.

Công thức mảng Excel với toán tử AND và OR

Toán tử AND là dấu hoa thị (*). Nó có tác dụng hướng dẫn Excel trả về TRUE nếu TẤT CẢ các điều kiện được đánh giá là TRUE.

Toán tử OR là dấu cộng (+). Nó trả về TRUE nếu bất kỳ điều kiện nào trong một biểu thức đã cho là TRUE.

Công thức mảng với toán tử AND

Trong ví dụ này, chúng mình sẽ cho biết tổng số lượng mà người bán hàng là AN và sản phẩm là Táo:

=SUM((A2:A12="An")*(B2:B12 ="Táo")* (C2:C12))

Hoặc là

= SUM(IF(((A2:A12 ="An")*(B2:B12 ="Táo")),(C2:C12)))

Công thức mảng với toán tử OR

Công thức mảng sau với toán tử OR (+) cho phép thêm tất cả các bán hàng ứng với người bán hàng là An hoặc sản phẩm là Quýt 

=SUM(IF(((A2:A12 = "An")+(B2:B12="Quýt")),(C2:C12)))

Trong các phiên bản hiện đại của Excel 2013, 2010 hoặc 2007, việc sử dụng các công thức mảng trong các tình huống như vậy thực sự không cần thiết và một công thức SUMIFS đơn giản có thể cho ra cùng một kết quả như trên. Tuy nhiên, toán tử AND và OR trong các công thức mảng có thể hữu ích trong các tình huống phức tạp hơn, điều này liên quan đến việc luyện tập các ý tưởng cho não của bạn.

Toán tử Đơn Vị Đôi Điều Hành trong công thức mảng của Excel

Hai dấu gạch ngang gọi là toán tử đơn vị đôi, được sử dụng để chuyển các giá trị Boolean không bằng số (TRUE / FALSE) của 1 số số biểu thức thành 1 và 0  thứ mà một hàm mảng có thể hiểu được. Trong đó các toán tử đơn vị đầu tiên (Dấu trừ - thứ nhất) sẽ làm TRUE / FALSE thành -1/0, tương ứng. Dấu trừ thứ hai thì sẽ phủ định giá trị, tức là đảo ngược dấu, biến chúng thành +1 và 0, để mà hầu hết các hàm của Excel có thể hiểu và làm việc với nó. 

Ví dụ =--TRUE sẽ cho kết quả là 1 và =--FALSE cho kết quả 0

Ví dụ sau hy vọng làm cho mọi thứ dễ hiểu hơn cho bạn. Giả sử bạn có một danh sách ngày trong cột A và bạn muốn biết có bao nhiêu ngày xảy ra vào tháng Giêng, bất kể năm nào.

Các công thức sau đây sẽ giải quyết vấn đề trên:

=SUM(--(MONTH(A1:A11)=1))

Vì đây là công thức mảng Excel, hãy nhớ nhấn Ctrl + Shift + Enter để hoàn tất.

Nếu bạn quan tâm đến một tháng khác, hãy thay thế 1 bằng một số tương ứng. Ví dụ, 2 là Tháng 2, 3 có nghĩa là Tháng 3, v.v. Để làm cho công thức linh hoạt hơn, thì bạn có thể chỉ định số của Tháng bằng tham chiếu ô

Và bây giờ, chúng ta hãy cùng phân tích xem rằng công thức mảng Excel làm việc như thế nào nhé. Hàm MONTH trả về kết quả là mỗi ngày trong các ô A2 đến A11 nằm trong tháng nào.

Chúng sẽ được biểu thị bởi một số sê-ri, tạo ra mảng {1;3;3;3;1;1;3;3;3;3;3}

Sau đó, mỗi phần của mảng sẽ được so sánh với giá trị trong ô D1, là số 1 trong ví dụ này. Kết quả của phép so sánh này là một mảng các giá trị Boolean (TRUE và FALSE). Như bạn nhớ, thì bạn có thể chọn một phần nhất định trong một công thức mảng và nhấn F9 để xem phần đó tương đương với:

Cuối cùng, toán tử đôi -- sẽ biến đổi các giá trị Boolean này thành 1 và 0 để hàm SUM có thể hiểu được.  Nếu bạn loại bỏ toán tử đơn vị đôi khỏi công thức trên, thì nó sẽ không hoạt động.

Trên đây là toàn bộ kiến thức cơ bản về công thức mảng trong Excel và cách sử dụng nó trong tính toán thực tế. Nếu chưa rõ phần nào các bạn hãy đặt comment bên dưới nhé. Cảm ơn các bạn đã theo dõi!