Bạn có một bảng doanh thu dài hàng trăm dòng và cần cộng có điều kiện? Đội ngũ bên mình từng gặp đúng tình huống này khi hướng dẫn học viên. Ban đầu ai cũng thêm cột phụ, kéo xuống rồi lo sợ sai sót. Vậy công thức mảng trong Excel là gì, và vì sao nó lại được dùng nhiều đến thế?
Hiểu đơn giản, công thức mảng là công thức tính trên nhiều giá trị cùng lúc. Thay vì xử lý từng ô một, nó nhận cả một dải ô làm đầu vào. Kết quả có thể là một giá trị duy nhất hoặc nhiều giá trị trả về cùng lúc.
Nghe có vẻ kỹ thuật, nhưng hãy hình dung thế này. Thay vì đếm từng hộp trên kệ rồi cộng lại bằng tay, bạn nhờ một người đếm toàn bộ hộp trong một lượt. Kết quả vẫn đúng, chỉ khác ở chỗ nhanh và gọn hơn nhiều.
Vì sao người làm bảng tính hay dùng công thức mảng?
Trong quá trình hỗ trợ học viên, chúng tôi thấy ba nhu cầu lặp đi lặp lại. Thứ nhất là cộng có điều kiện, chẳng hạn tổng doanh thu chỉ của một khu vực. Thứ hai là đếm theo nhiều tiêu chí cùng lúc. Thứ ba là tìm giá trị lớn nhất trong từng nhóm.
Với công thức thường, bạn phải tạo cột phụ, kéo xuống rồi mới tổng hợp. Mỗi bước thêm vào lại là một chỗ có thể nhầm lẫn. Công thức mảng gom các bước ấy về đúng một ô. Nhờ vậy bảng gọn hơn, và người khác mở file ra cũng dễ kiểm tra hơn.
Cách nhập công thức mảng theo từng phiên bản Excel
Cách nhập phụ thuộc vào phiên bản bạn đang dùng. Với Excel cũ, bạn gõ công thức xong rồi nhấn Ctrl + Shift + Enter thay vì chỉ Enter. Khi đó Excel tự bọc công thức trong cặp dấu {} để báo đây là công thức mảng.
Có một lưu ý quan trọng: đừng tự gõ dấu {} bằng tay. Excel sẽ không nhận, và công thức vẫn báo lỗi. Cặp dấu này do chính Excel tạo ra khi bạn dùng đúng tổ hợp phím.
Với Excel 365 hoặc Excel 2021, mọi thứ đơn giản hơn nhiều. Bạn chỉ cần nhấn Enter, và công thức tự tràn kết quả ra các ô liền kề nhờ tính năng mảng động. Nhiều học viên mới hiện nay không còn phải nhớ tổ hợp phím cũ.
Ví dụ thực tế: tính tổng doanh thu theo nhân viên
Giả sử bảng của bạn có cột tên nhân viên và cột doanh thu từng đơn hàng. Dữ liệu nằm rải rác trên nhiều dòng, còn bạn muốn biết tổng doanh thu của một người trong tháng.
Cách làm bằng công thức mảng là kết hợp SUM với IF. IF lọc ra đúng những dòng thuộc nhân viên đó, còn SUM cộng các giá trị đã được lọc. Toàn bộ quá trình nằm trong một ô, không cần cột phụ nào.
So với cách dùng cột phụ, sự khác biệt khá rõ. Cột phụ đòi hỏi bạn phải đánh dấu từng dòng trước, rồi mới cộng theo cột ấy. Kết quả thì giống nhau, nhưng cách mảng gọn hơn và bạn chỉ cần kiểm tra đúng một công thức.
Ví dụ thực tế: tìm mức cao nhất trong một nhóm
Bài toán thứ hai là tìm mức lương cao nhất của một phòng ban trong danh sách nhân viên. Ở đây bạn kết hợp MAX với IF, vẫn trong công thức mảng. IF chỉ giữ lại mức lương của phòng ban cần xem, còn MAX lấy giá trị lớn nhất trong số ấy.
Một mẹo kiểm tra mà chúng tôi hay dặn học viên: sau khi có kết quả, hãy lọc thử vài dòng bằng tay rồi đối chiếu. Nếu con số khớp, bạn có thể yên tâm áp dụng cho cả bảng. Thói quen này tốn chưa đầy một phút, nhưng giúp tránh rất nhiều sai sót về sau.
Ưu điểm và nhược điểm của công thức mảng
Ưu điểm rõ nhất là gọn. Một công thức xử lý được nhiều điều kiện, và bảng bớt hẳn cột phụ. Với người làm báo cáo, điều này giúp file nhẹ hơn và dễ chia sẻ hơn.
Nhược điểm nằm ở chỗ khó đọc. Người mới nhìn một công thức mảng dài thường thấy rối mắt. Nếu dữ liệu lên tới hàng chục nghìn dòng, file cũng có thể chạy chậm đi. Vì vậy, khi dữ liệu còn nhỏ và bạn muốn người khác theo dõi dễ dàng, cột phụ đôi khi lại là lựa chọn khôn ngoan hơn.
Lỗi thường gặp khi viết công thức mảng
- Quên Ctrl + Shift + Enter ở Excel cũ, khiến kết quả chỉ hiện ở một ô.
- Chọn sai kích thước vùng kết quả so với dải dữ liệu, dẫn đến thiếu hoặc thừa giá trị.
- Nhầm tham chiếu tương đối và tuyệt đối khi kéo công thức.
Lỗi cuối cùng ít người để ý hơn cả. Dấu $ quyết định vùng nào được giữ cố định khi kéo. Nếu quên dấu này, kết quả sẽ lệch dần theo từng dòng, và rất khó nhận ra nếu không đối chiếu.
Mẹo học và thực hành công thức mảng hiệu quả
Hãy bắt đầu với bài toán nhỏ, khoảng 5 đến 10 dòng dữ liệu, để bạn dễ kiểm tra bằng mắt. Khi đã quen tay, bạn mới tăng dần kích thước. Đây là cách học chậm nhưng chắc.
Phím F9 trong thanh công thức là công cụ rất hữu ích. Chọn một phần của công thức rồi nhấn F9 sẽ cho bạn thấy giá trị tạm thời của phần đó. Hãy lưu lại các mẫu công thức hay dùng vào một sheet riêng để tái sử dụng. Nếu muốn củng cố nền tảng về hàm, bạn có thể đọc thêm công thức mảng trong Excel cho người mới bắt đầu, cùng với hướng dẫn hàm VLOOKUP từ cơ bản đến nâng cao.
Công thức mảng trong công việc và mối liên hệ với các kỹ năng khác
Trong công việc văn phòng, công thức mảng thường đi cùng các kỹ năng tra cứu và tổng hợp khác. Một người làm kế toán có thể dùng công thức mảng để tổng hợp số liệu, rồi dùng hàm tra cứu để ghép thông tin từ nhiều bảng. Khi hiểu cách Excel xử lý dải ô, bạn sẽ thấy hai kỹ năng này bổ sung cho nhau rất tự nhiên.
Thói quen dùng phím tắt cũng giúp bạn làm việc nhanh hơn, nhất là khi nhập và chỉnh sửa những công thức dài. Nếu chưa quen, bạn có thể tham khảo tổng hợp phím tắt Excel giúp tăng tốc công việc. Với người muốn học sâu hơn về cơ sở dữ liệu, các câu lệnh SQL cơ bản là hướng đi tiếp theo đáng cân nhắc. Nếu doanh nghiệp bạn cần một đơn vị hỗ trợ triển khai bảng tính, báo cáo hoặc chiến lược số, bạn có thể tham khảo MONA Media để trao đổi thêm về nhu cầu cụ thể.
Câu hỏi thường gặp
Công thức mảng có khó học không?
Không khó nếu bạn học theo từng bước nhỏ. Đừng cố viết một công thức dài ngay từ đầu. Hãy bắt đầu với một điều kiện, kiểm tra thấy kết quả đúng rồi mới thêm điều kiện thứ hai.
Công thức mảng và hàm SUMPRODUCT khác nhau thế nào?
SUMPRODUCT làm được nhiều việc kiểu mảng mà không cần tổ hợp phím đặc biệt, nên nhiều người xem nó như một lối tắt. Tuy vậy, với điều kiện phức tạp, công thức mảng thường linh hoạt hơn.
Phiên bản Excel nào không cần Ctrl + Shift + Enter?
Excel 365 và Excel 2021 trở lên hỗ trợ mảng động, nên bạn chỉ cần nhấn Enter.
Công thức mảng có làm file Excel chạy chậm không?
Có thể, khi dữ liệu rất lớn và công thức được kéo xuống nhiều dòng. Nếu thấy chậm, hãy thu hẹp vùng tham chiếu chỉ còn đúng phần dữ liệu cần thiết.
Người mới nên học công thức mảng trước hay hàm VLOOKUP trước?
Theo kinh nghiệm của chúng tôi, nên học VLOOKUP trước vì nó là nền tảng cho việc tra cứu. Khi đã quen cách Excel xử lý dải ô, bạn sẽ thấy công thức mảng dễ hiểu hơn nhiều.
Việc cần làm ngay: mở một bảng nhỏ gồm 8 dòng dữ liệu bán hàng, thử tính tổng doanh thu cho một người bằng cả hai cách là cột phụ và công thức mảng, rồi so sánh kết quả. Làm xong bài này, bạn sẽ hiểu rõ hơn vì sao công thức mảng hữu ích đến vậy.