Một số lỗi khi dùng công thức mảng trong Excel

 Công thức mảng được dùng khá phổ biến trong các phép tính nhiều điều kiện, phức tạp. Có thể chia thành 3 loại: Công thức tính toán theo mảng giá trị, Công thức trả về mảng kết kết quả và Công thức tính toán theo mảng đồng thời trả về mảng giá trị. Khi đùng công thức mảng có thể phát sinh các lỗi hoặc sự cố sau:

KHÔNG NHẤN CTRL+SHIFT+ENTER KHI CHẠY CÔNG THỨC

Đối với phiên bản MS Office không phải là Office 365 thì nhiều tình huống sử dụng công thức mảng bắt buộc phải nhấn Ctrl+Shift+Enter. Nếu nhấn Enter bình thường thì kết quả sẽ trả ra sai, thiếu hoặc lỗi. Khi đó người dùng vào lại nội dung công thức rồi nhấn tổ hợp phím Ctrl+Shift+Enter để chạy lại

Trường hợp công thức mảng phải nhấn Ctrl+Shift+Enter chủ yếu trong các trường hợp công thức mảng đó dùng các hàm như: SUM, IF, IFERROR, COUNT, COUNTA, MAX, MIN, AVERAGE. Nhưng với Office 365 thì chỉ cần nhấn Enter bình thường là được

CÔNG THỨC MẢNG TRẢ VỀ KẾT QUẢ LỖI #VALUE!, #NUM!

#VALUE! được hiểu là sai hoặc lỗi kiểu giá trị. Trường hợp này chủ yếu do 1 trong 2 nguyên nhân sau:

- Do thực hiện phép tính +, -, *, / mà dữ liệu có chứa giá trị kiểu Text. Ví dụ như hình ảnh: Cột đơn giá có 1 ô là dữ liệu Text và thực hiện phép nhân mảng thì kết quả trả về lỗi #VALUE!

- Do lệch tọa độ hoặc khác nhau số phần tử giữa 2 mảng. Ví dụ như hình ảnh: Mảng 1 từ A2:A7, mảng 2 từ B2:B8 nhiều phần tử hơn mảng 1 nên công thức trả về giá trị lỗi. Trường hợp nếu thực hiện phép tính +, -, *, / mà lệch số phần tử thì kết quả lỗi thường là #N/A

Lỗi #NUM! thường là dùng phép tính mảng có sử dụng đến các phép chia mảng, tính căn hoặc lũy thừa trong mảng. Lỗi này thường gặp khi dùng phép chia mảng trong hàm AGGREGATE với tham số 14, 15 nhưng ko có kết quả thỏa mãn

CÔNG THỨC MẢNG CHẠY CHẬM (Calculating nhích từng % và chờ rất lâu)

Nguyên nhân là do người dùng đưa vào mảng có quá nhiều phần tử cho tính toán. Đặc biệt hay gặp là đưa cả cột hoặc cả dòng Excel vào trong công thức mảng. Ví dụ: =SUM(IF(A:A>0,...

Khi gặp tình trạng này thì người dùng xử lý như sau:

- Phương án tạm thời: Giảm kích thước của mảng khi đưa vào công thức. Ví dụ: A:A thì thay bằng A1:A1000

- Phương án tối ưu: Thay bằng các công thức tương đương nhưng tốc độ tính toán nhanh hơn, VBA hoặc SQL trong Excel,...

CÔNG THỨC TRẢ VỀ MẢNG KẾT QUẢ NHƯNG LỖI #SPILL!

Lỗi này #SPILL! xuất hiện khi dùng Office 365 với các hàm hoặc công thức trả về mảng kết quả, nhưng các ô bên dưới hoặc bên cạnh công thức có chứa dữ liệu nên không đủ phạm vi cho dữ liệu trả về

Cách khắc phục: Kiểm tra dữ liệu các ô bên dưới hoặc bên cạnh công thức => thực hiện xóa hoặc chuyển dữ liệu các ô đó sang nơi khác và cập nhật lại công thức.

Liên hệ tư vấn khóa học Excel cho người đi làm & đặt hàng đào tạo tại doanh nghiệp

{Đt Zalo} - 038 696 1334