Nội Dung Bài Viết

Công thức mảng trong Excel là công cụ mạnh mẽ giúp thực hiện nhiều phép tính phức tạp chỉ với một công thức duy nhất, thay thế hàng ngàn công thức thông thường. Tuy nhiên, nhiều người dùng Excel vẫn chưa khai thác hết tiềm năng của công cụ này. Bài viết này sẽ hướng dẫn bạn cách sử dụng công thức mảng Excel, từ những khái niệm cơ bản đến các ví dụ thực tế và mẹo hữu ích.

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

“Mảng” trong Excel là một tập hợp các mục, có thể là văn bản hoặc số, nằm trong một hoặc nhiều hàng/cột. Ví dụ, danh sách mua hàng của bạn có thể được biểu diễn dưới dạng mảng: {“Sữa”, “Trứng”, “Bơ”, “Ngũ cốc”}.

Để tạo mảng trong Excel, chọn các ô (ví dụ A1:D1), nhập mảng trên vào thanh công thức với dấu bằng (=) phía trước, rồi nhấn Ctrl + Shift + Enter.

Tạo mảng trong Excel

Vậy, “hàm mảng” khác gì so với hàm thông thường? Hàm mảng xử lý nhiều giá trị cùng lúc, thay vì chỉ một. Nó đánh giá từng giá trị trong mảng và thực hiện phép tính trên các mục thỏa mãn điều kiện trong công thức. Kết quả trả về cũng là một mảng. Công thức mảng có sẵn trong tất cả các phiên bản Excel từ 2007 trở về trước.

Ví Dụ Cơ Bản về Công Thức Mảng

Giả sử cột B chứa danh sách sản phẩm và cột C là giá của chúng. Để tính tổng doanh thu, bạn có thể tính tổng phụ từng hàng (=B2*C2) rồi cộng lại. Tuy nhiên, công thức mảng giúp tối ưu hơn bằng cách lưu trữ kết quả trung gian trong bộ nhớ, không cần cột bổ sung.

Chọn một ô trống và nhập: =SUM(B2:B6*C2:C6)
Nhấn Ctrl + Shift + Enter.

Excel sẽ tự động đặt công thức trong dấu ngoặc nhọn {}, cho biết đây là công thức mảng. Công thức này sẽ nhân giá trị từng hàng trong mảng (B2:C6), cộng các tổng phụ và trả về tổng doanh thu.

Xem thêm: Tạo QR Code và Barcode trong Excel một cách dễ dàng

Công thức mảng trong Excel

Công thức Excel thông thường

Lợi Ích của Công Thức Mảng

Công thức mảng giúp thực hiện các phép tính phức tạp một cách hiệu quả. Một công thức mảng có thể thay thế hàng trăm công thức thông thường, tiết kiệm thời gian đáng kể khi xử lý lượng dữ liệu lớn. Một số ứng dụng của công thức mảng bao gồm:

Tính tổng các số thỏa mãn điều kiện (ví dụ: tổng N giá trị lớn nhất/nhỏ nhất).
Tính tổng giá trị theo hàng/cột.
Đếm số lượng giá trị hoặc ký tự cụ thể trong một vùng dữ liệu.

Cách Nhập Công Thức Mảng (Ctrl + Shift + Enter)

Ctrl + Shift + Enter là tổ hợp phím quan trọng để tạo công thức mảng. Lưu ý:

Sau khi nhấn Ctrl + Shift + Enter, Excel sẽ tự động thêm dấu ngoặc nhọn {}.
Nhập dấu ngoặc nhọn thủ công sẽ không biến công thức thành mảng.
Mỗi lần chỉnh sửa công thức mảng, cần nhấn lại Ctrl + Shift + Enter.
Nếu quên nhấn Ctrl + Shift + Enter, công thức sẽ hoạt động như công thức thông thường và chỉ xử lý giá trị đầu tiên trong mảng.

Sử dụng Phím F9 để Đánh Giá Công Thức Mảng

Phím F9 giúp quan sát cách công thức mảng tính toán và lưu trữ các mục của mảng. Chọn một hoặc nhiều đối số trong ngoặc đơn của hàm, rồi nhấn F9. Nhấn Esc để thoát chế độ đánh giá.

Sử dụng phím F9 để xem mảng nội bộ

Công Thức Mảng Đơn Ô và Đa Ô

Công thức mảng có thể trả về kết quả trong một ô (đơn ô) hoặc nhiều ô (đa ô). Một số hàm như TRANSPOSE, TREND, FREQUENCY, LINEST được thiết kế để trả về mảng đa ô. Các hàm khác 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 Ctrl + Shift + Enter.

Ví dụ 1: Công Thức Mảng Đơn Ô

Để tìm mức tăng doanh thu lớn nhất giữa hai cột B và C (doanh số của hai tháng), bạn có thể dùng công thức mảng: =MAX(C2:C6-B2:B6) và nhấn Ctrl + Shift + Enter.

Công thức mảng MAX đơn ô

Ví dụ 2: Công Thức Mảng Đa Ô

Để tính thuế 10% cho mỗi sản phẩm trong ví dụ trên, chọn vùng D2:D6, nhập công thức =B2:B6*C2:C6*0.1 và nhấn Ctrl + Shift + Enter.

Công thức mảng đa ô

Ví dụ 3: Hàm Mảng Trả Về Mảng Đa Ô

Hàm TRANSPOSE chuyển đổi hàng thành cột và ngược lại.

Chọn vùng đích có cùng số hàng và cột với bảng gốc (nhưng đảo ngược).
Nhấn F2.
Nhập =TRANSPOSE(vùng_dữ_liệu) (ví dụ: =TRANSPOSE($A$1:$D$6)) và nhấn Ctrl + Shift + Enter.

Sử dụng hàm mảng trong Excel

Làm Việc với Công Thức Mảng Đa Ô

Khi làm việc với công thức mảng đa ô, cần lưu ý:

Chọn vùng đích trước khi nhập công thức.
Để xóa, chọn tất cả ô chứa công thức và nhấn Delete, hoặc chọn toàn bộ công thức trong thanh công thức, nhấn Delete rồi Ctrl + Shift + Enter.
Không thể chỉnh sửa hoặc di chuyển nội dung từng ô trong công thức mảng đa ô.
Để thu nhỏ/mở rộng công thức mảng, cần xóa công thức cũ và nhập lại.
Không sử dụng công thức mảng đa ô trong bảng Excel.
Vùng đích nên có cùng kích thước với mảng kết quả.

Xem thêm: Mã Hóa Chuỗi trong VBA: Tối Ưu So Sánh Chuỗi

Hằng Số Mảng

Hằng số mảng là tập hợp các giá trị cố định, không thay đổi khi sao chép công thức. Có ba loại hằng số mảng:

Ngang: Các giá trị cách nhau bởi dấu phẩy, nằm trong dấu ngoặc nhọn. Ví dụ: {1,2,3,4}.

Hằng số mảng ngang

Dọc: Các giá trị cách nhau bởi dấu chấm phẩy, nằm trong dấu ngoặc nhọn. Ví dụ: {11;22;33;44}.

Hằng số mảng dọc

Hai chiều: Các mục trong mỗi hàng cách nhau bởi dấu phẩy, các cột cách nhau bởi dấu chấm phẩy. Ví dụ: {"a","b","c";1,2,3}.

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

Làm Việc với Hằng Số Mảng

Thành phần: Hằng số mảng có thể chứa số, văn bản, giá trị Boolean (TRUE/FALSE) và giá trị lỗi. Không chứa mảng khác, tham chiếu ô, công thức hay hàm.
Đặt tên: Có thể đặt tên cho hằng số mảng để dễ sử dụng (Formulas > Defined Names > Define Name).

Tạo hằng số mảng đã đặt tên

Nhập hằng số mảng đã đặt tên

Ngăn ngừa lỗi: Kiểm tra kỹ dấu phân cách và kích thước vùng chọn.

Sử dụng Hằng Số Mảng trong Công Thức

Ví dụ 1: Tính Tổng N Giá Trị Lớn Nhất/Nhỏ Nhất

Để tính tổng 3 giá trị lớn nhất trong một vùng, sử dụng công thức =SUM(LARGE(vùng_dữ_liệu,{1,2,3})) và nhấn Ctrl + Shift + Enter. Tương tự với hàm SMALL để tính tổng các giá trị nhỏ nhất. Có thể thay SUM bằng AVERAGE để tính giá trị trung bình.

Công thức mảng tính tổng N giá trị lớn nhất

Ví dụ 2: Đếm Ô Thỏa Mãn Nhiều Điều Kiện

Sử dụng COUNTIFS kết hợp với SUM và hằng số mảng để đếm số ô thỏa mãn nhiều điều kiện. Ví dụ: =SUM(COUNTIFS(vùng_1,"điều_kiện_1",vùng_2,{"điều_kiện_1","điều_kiện_2"})).

Công thức mảng đếm ô thỏa mãn nhiều điều kiện

Toán Tử AND và OR trong Công Thức Mảng

*AND (``):** Trả về TRUE nếu TẤT CẢ điều kiện là TRUE.
OR (+): Trả về TRUE nếu BẤT KỲ điều kiện nào là TRUE.

Công thức mảng với AND:

=SUM((A2:A9="Mike")*(B2:B9="Apples")*(C2:C9))

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

Công thức mảng với OR:

=SUM(IF((A2:A9="Mike")+(B2:B9="Apples"),C2:C9))

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

Toán Tử Đơn Vị Đôi (–)

Toán tử đơn vị đôi -- chuyển đổi giá trị Boolean (TRUE/FALSE) thành 1/0. Ví dụ, để đếm số ngày trong tháng 1 (cột A): =SUM(--(MONTH(A2:A10)=1)) và nhấn Ctrl + Shift + Enter.

Sử dụng toán tử đơn vị đôi

Bài viết này đã cung cấp kiến thức tổng quan về công thức mảng trong Excel. Hy vọng bạn có thể áp dụng những kiến thức này vào công việc một cách hiệu quả.

Đánh Giá Bài Viết