Trong quá trình làm việc với dữ liệu trong excel, có những công thức bạn phải xử lý khá dài, có khi phải sử dụng cột phụ để giải quyết, và có thể công thức sử dụng bình thường sẽ không giải quyết được.

Bạn đã suy nghĩ đến việc áp dụng công thức mảng cho những trường hợp đó chưa? Nếu chưa bạn hãy thử nhé, và bạn chưa biết cách sử dụng công thức mảng này hãy cùng Trí Tuệ Việt tìm hiểu nào.

Bài viết dưới đây sẽ hướng dẫn bạn sử dụng về công thức mảng trong excel.

Công thức mảng

Công thức mảng thường gọi là công thức Ctrl + Shift + Enter hoặc CSE, để thực hiện các phép tính tạo ra một hoặc nhiều kết quả. Mảng là một hàng giá trị, một cột giá trị hoặc là một kết hợp các hàng và cột giá trị. Nghĩa là sử dụng công thức tính thay vì dùng Enter thông thường.

Có 2 dạng công thức mảng: Công thức mảng đơn ô, công thức mảng đa ô.

Ưu điểm của công thức mảng:

• Nhất quán: Nếu công thức là đa ô thì khi bấm bất kỳ ô nào trong vùng tính đều có chung công thức.
• An toàn: Không thể ghi đè hay chỉnh sửa hay xóa 1 ô trong công thức mảng dạng đa ô. Phải chọn toàn bộ phạm vi ô để sửa hay xóa công thức mảng đa ô đó.
• Dung lượng tệp sẽ nhẹ hơn: Dùng 1 công thức mảng thay vì nhiều công thức trung gian.

Nhược điểm của việc dùng công thức mảng

• Đôi khi quên nhấn Ctrl + Shift + Enter thì công thức mảng sẽ không được áp dụng.
• Gây khó khăn cho người khác khi đọc và sửa công thức trên tệp của bạn.
• Tốc độ xử lý tính toán có thể chậm nếu công thức mảng lớn.
• Chỉnh sửa hay xóa phải chọn tất cả các ô trong vùng với công thức đa ô.

Ví dụ công thức mảng

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

Tính Thành tiền = Số lượng * Đơn giá.

Sử dụng công thức mảng: Chọn vùng F5:F16 và thực hiện phép tính sau: F5 = D5:D16*E5:E16

Sau đó bấm Ctrl + Shift + Enter để thực hiện công thức.

Kết quả như hình sau:

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

Tính Tổng cộng = Tổng thành tiền , (trong đó :Thành tiền = Số lượng * Đơn giá)

Sử dụng công thức mảng: Chọn ô F18 và thực hiện phép tính sau: F18 = SUM((D5:D16)*(E5:E16))

Sau đó bấm Ctrl + Shift + Enter để thực hiện công thức.

Không cần phải thực hiện tính Thành tiền (cột phụ) tính trực tiếp từ số lượng và đơn giá

Kết quả như hình sau:

Một số ví dụ khác sử dụng công thức mảng

Ví dụ 1: Sử dụng một số công thức thống kê dữ liệu sau:

Các yêu cầu trên khi lập công thức lưu ý phải nhấn Ctrl + Shift + Enter.

Ví dụ 2: Tạo cột STT đánh tự động bằng công thức mảng

Chọn vùng A46:A57 sau đó lập công thức sau: A46= ROW(A46:A57)-ROW(A45)

Sau đó bấm Ctrl + Shift + Enter để áp dụng công thức mảng.

Kết quả như hình sau:

Ví dụ 3: Sử dụng công thức mảng để dò tìm giá trị.

Tìm Tên hàng dựa vào bảng Tên hàng

Chọn  vùng C46:C57 và lập công thức sau:

C46=INDEX($F$47:$I$49,MATCH(MID(B46:B57,2,1)*1,$E$47:$E$49,0),MATCH(LEFT(B46:B57,1),$F$46:$I$46,0)) 

Sau đó bấm Ctrl + Shift + Enter để áp dụng công thức mảng.