Công thức mảng excel nghe có vẻ cao siêu, nhưng ý tưởng nền tảng khá đơn giản. Thay vì tính từng ô một, bạn ra lệnh cho Excel xử lý cả một dải ô trong một công thức. Chúng tôi nhận ra sức mạnh của nó khi phải tính tổng điểm có điều kiện cho cả khối lớp mà không muốn thêm cột phụ nào. Bài viết này dẫn bạn đi từ khái niệm đến ví dụ chạy được ngay.
Mảng là gì và vì sao cần công thức mảng excel
Mảng là một nhóm giá trị được xử lý như một khối. Một dải ô A2:A11 là một mảng gồm mười giá trị. Một công thức thông thường chỉ làm việc với một giá trị mỗi lần. Công thức mảng excel làm việc với cả nhóm.
Hãy nghĩ đến tình huống chấm điểm. Bạn có hai cột: điểm Toán và điểm Văn của mười học sinh. Muốn biết tổng điểm hai môn của cả lớp, bạn có thể thêm một cột phụ cộng từng dòng rồi cộng cột đó lại. Với công thức mảng excel, một ô duy nhất là đủ, không cần cột phụ.
Lợi ích rõ nhất là bảng tính gọn hơn. Ít cột phụ nghĩa là ít chỗ cho người khác vô tình xoá nhầm. Cũng ít chỗ để bạn quên cập nhật.
Cách nhập công thức mảng excel
Cách nhập phụ thuộc vào phiên bản Excel bạn dùng. Đây là chỗ nhiều người bối rối nhất.
Với Excel 2019 trở về trước, bạn gõ công thức rồi nhấn Ctrl + Shift + Enter thay vì Enter thường. Excel sẽ bao công thức trong cặp ngoặc nhọn, ví dụ {=SUM(A2:A11*B2:B11)}. Bạn không tự gõ ngoặc nhọn được, vì chúng chỉ xuất hiện khi Excel nhận ra đó là công thức mảng.
Với Microsoft 365 và Excel 2021, Excel có cơ chế mảng động. Bạn chỉ cần nhấn Enter. Nếu công thức cho ra nhiều kết quả, chúng tự tràn sang các ô bên cạnh. Vùng kết quả tràn được đánh dấu bằng viền xanh nhạt.
Nếu không chắc phiên bản của mình, hãy thử gõ công thức rồi nhấn Enter. Bạn thấy kết quả sai hoặc lỗi #VALUE! thì quay lại dùng Ctrl + Shift + Enter.
Ví dụ 1: tính tổng tích hai cột
Giả sử cột A là số lượng sách và cột B là đơn giá, từ dòng 2 đến dòng 11. Bạn muốn biết tổng giá trị của toàn bộ mà không thêm cột thành tiền.
Nhập công thức =SUM(A2:A11*B2:B11). Excel nhân từng cặp ô cùng dòng, tạo ra một mảng mười kết quả, rồi SUM cộng chúng lại. Dùng đúng phím theo phiên bản như ở phần trên.
Hàm SUMPRODUCT cho kết quả tương tự và không đòi Ctrl + Shift + Enter. Khi mới học, bạn có thể dùng thử: =SUMPRODUCT(A2:A11, B2:B11).
Ví dụ 2: tổng có điều kiện
Cột A ghi tên lớp, cột B ghi điểm. Bạn muốn tổng điểm của lớp “10A”. Công thức mảng excel là =SUM((A2:A31=”10A”)*B2:B31).
Phần (A2:A31=”10A”) tạo ra mảng gồm TRUE và FALSE. Khi nhân với điểm, TRUE thành 1 và FALSE thành 0. Nhờ vậy chỉ các dòng của lớp 10A được giữ lại. SUM cộng các giá trị còn lại.
Muốn thêm điều kiện thứ hai, ví dụ chỉ tính học sinh có điểm từ 5 trở lên, bạn nhân thêm một vế: =SUM((A2:A31=”10A”)*(B2:B31>=5)*B2:B31). Mỗi vế trong ngoặc là một điều kiện, và dấu nhân đóng vai trò “và”.
Với bài toán đơn giản như thế này, hàm SUMIFS cũng làm được. Công thức mảng excel thể hiện ưu thế khi điều kiện phức tạp, chẳng hạn cần so sánh giữa hai cột với nhau.
Ví dụ 3: đếm giá trị khác nhau
Một bài toán hay gặp là đếm có bao nhiêu giá trị không trùng nhau trong danh sách tên lớp. Công thức kinh điển là =SUM(1/COUNTIF(A2:A31, A2:A31)).
Cách hoạt động như sau. COUNTIF đếm số lần mỗi giá trị xuất hiện. Lấy 1 chia cho số lần đó, mỗi giá trị trùng sẽ đóng góp phần lẻ. Tổng các phần lẻ của cùng một giá trị luôn bằng 1. Kết quả cuối là số giá trị khác nhau.
Lưu ý là ô trống trong vùng sẽ gây lỗi #DIV/0!. Hãy chắc rằng vùng bạn chọn không có ô trống, hoặc dùng thêm điều kiện loại ô trống.
Lỗi thường gặp và cách xử lý
Lỗi #VALUE! thường do độ dài các mảng không khớp. Nếu A2:A11 có mười ô còn B2:B12 có mười một ô, phép nhân giữa chúng sẽ lỗi. Hãy kiểm tra các vùng có cùng số dòng.
Công thức trả về một kết quả duy nhất dù bạn mong nhiều kết quả thường là do quên Ctrl + Shift + Enter ở bản cũ. Ngược lại, ở bản mới, lỗi #SPILL! xuất hiện khi có thứ gì đó chắn đường tràn của kết quả. Xoá các ô bị chắn là đủ.
Công thức mảng trên vùng rất lớn có thể làm bảng tính chậm đi. Nếu thấy Excel đứng hình, hãy thu nhỏ vùng tính toán đúng phạm vi dữ liệu thay vì chọn cả cột.
Nên học tiếp gì
Khi đã quen công thức mảng excel, bạn sẽ thấy các hàm tra cứu mạnh hơn. Bài hướng dẫn hàm VLOOKUP cho bạn nền tảng tra cứu cơ bản. Còn bài hàm INDEX MATCH cho thấy công thức mảng giúp tra cứu hai điều kiện ra sao.
Bạn có thể đối chiếu thêm với hướng dẫn chính thức của Microsoft về công thức mảng. Nếu cần danh sách hàm theo nhóm, trang hỗ trợ Excel có sẵn mục tra cứu.
Việc nhỏ để bạn tự làm
Hãy lập bảng mười dòng gồm lớp, môn, điểm. Viết công thức mảng excel tính tổng điểm môn Toán của một lớp bất kỳ, sau đó viết lại bằng SUMIFS và so hai kết quả. Khi hai ô cho cùng một số, bạn đã hiểu cơ chế điều kiện của công thức mảng. Thử đổi sang tổ hợp hai điều kiện để thấy sức mạnh thật sự của nó.
