Khám phá công thức mảng Array Formulas trong Excel: Hướng dẫn và ví dụ chi tiết

Bài viết này cung cấp hướng dẫn toàn diện về công thức mảng trong Excel, kèm theo các ví dụ thực tế giúp bạn hiểu sâu và áp dụng hiệu quả vào công việc. Lưu ý: Sau khi nhập công thức, hãy nhấn tổ hợp phím Ctrl + Shift + Enter thay vì chỉ nhấn Enter.

1. Công thức mảng ứng dụng: Tính tổng và đếm theo điều kiện

Thay vì sử dụng hàm sumif truyền thống, hãy khám phá cách đơn giản hóa công thức tính tổng có điều kiện bằng công thức mảng thông qua ví dụ cụ thể dưới đây.

Ví dụ: Tính tổng số nhân viên đạt doanh số bán hàng vượt mức 38 sản phẩm và tổng lượng hàng hóa mà họ đã bán được.

Dưới đây là bảng dữ liệu minh họa:

1.1. Đếm số nhân viên có lượng sản phẩm bán ra lớn hơn 30

Thay vì dùng hàm counif, hãy khám phá cách sử dụng công thức mảng SUM để đếm. Tại ô cần tính, nhập công thức: =SUM(B4:B10>30).

Sau khi nhập công thức, nhấn tổ hợp phím Ctrl + Shift + Enter để xem kết quả:

Vì điều kiện so sánh trả về giá trị True hoặc False, bạn cần điều chỉnh công thức bằng cách thêm một bước chuyển đổi để phù hợp với hàm Sum.

Nhấn F9 để kiểm tra công thức, sau đó nhấn tổ hợp phím Ctrl + Shift + Enter để hoàn tất và nhận kết quả như hình minh họa.

1.2. Tính tổng có điều kiện bằng công thức mảng

Tính tổng lượng sản phẩm bán được của những nhân viên có doanh số vượt quá 30 sản phẩm.

Bước 1: Nhập công thức vào ô cần tính toán.

Nhấn tổ hợp phím Ctrl + Shift + Enter để hiển thị kết quả:

Trên một số máy, nếu công thức không trả về kết quả chính xác, hãy bôi đen tham số trong hàm Sum -> Nhấn F9 để kiểm tra -> Sau đó nhấn Ctrl + Shift + Enter để hoàn tất.

2. Ứng dụng công thức mảng để tính chênh lệch lớn nhất và nhỏ nhất của giá cổ phiếu qua các ngày

Ví dụ: Tính mức chênh lệch lớn nhất và nhỏ nhất của giá cổ phiếu trong các ngày giao dịch:

Thay vì tạo cột phụ để tính chênh lệch và sử dụng hàm Min, Max, bài viết này sẽ hướng dẫn bạn sử dụng công thức mảng để tính toán trực tiếp mà không cần thêm cột phụ.

Bước 1: Tại ô cần tính, nhập công thức sau: =Max(E5:E10-D5:D10).

Sau khi nhập công thức, nhấn tổ hợp phím Ctrl + Shift + Enter để xem kết quả. Bạn có thể kiểm tra bằng cách bôi đen các tham số trong hàm Max và nhấn F9 để xem chi tiết.

Hàm Max ở đây sẽ trả về giá trị lớn nhất trong mảng gồm 6 phần tử.

3. Tính giá trị lớn nhất (Max) và nhỏ nhất (Min) theo điều kiện kết hợp công thức mảng

Ví dụ: Tính doanh số cao nhất và thấp nhất của các nhân viên trong tháng. Tại ô cần tính, nhập công thức: =Max(If($B$5:$B$10=E6,C5:C10)).

Sau khi nhập công thức, nhấn tổ hợp phím Ctrl + Shift + Enter để hiển thị kết quả:

Để tính doanh số nhỏ nhất, thực hiện tương tự như tính doanh số lớn nhất, chỉ cần thay hàm MAX bằng hàm MIN.

Bạn có thể áp dụng tương tự cho các nhân viên khác bằng cách thay đổi điều kiện trong hàm If để so sánh với mã nhân viên tương ứng.

4. Xác định các mặt hàng được phân phối đến các đại lý tại 3 miền Bắc, Trung, Nam

Kết hợp hàm Index và Match với công thức mảng để tìm kiếm dữ liệu chính xác.

Tại ô cần tính, nhập công thức sau: =IFERROR(INDEX($D$5:$D$12,MATCH($F$6&$G$5,$B$5:$B$12&$C$5:$C$12,0)),"").

Sau khi nhập công thức, nhấn tổ hợp phím Ctrl + Shift + Enter để xem kết quả:

Áp dụng tương tự bằng cách kéo công thức cho các ô còn lại để nhận kết quả đầy đủ:

Hàm IFERROR được sử dụng để tránh lỗi khi mặt hàng Máy gọt hoa quả không có ở miền Bắc hoặc Trung, trả về giá trị rỗng thay vì thông báo lỗi.

5. Tính tổng chi phí dựa trên số lần xuất hiện của sản phẩm

Ví dụ: Tính tổng chi phí của các sản phẩm SP1, SP2, SP3 dựa trên bảng chi phí và số lần xuất hiện mã sản phẩm trong bảng sản phẩm đã bán.

Tại ô cần tính, nhập công thức: SUM(SUMIFS(H5:H7,G5:G7,C5:C9)).

Sau khi nhập công thức, nhấn tổ hợp phím Ctrl + Shift + Enter để hiển thị kết quả:

6. Tính tổng doanh số của những nhân viên có thành tích bán hàng cao nhất

Bạn có thể linh hoạt chọn số lượng nhân viên (n) để tính tổng doanh số, ví dụ n = 2, 3, 4… tùy theo yêu cầu.

Ví dụ: Tính tổng doanh số của 3 nhân viên bán được nhiều hàng nhất.

Tại ô cần tính, nhập công thức: =SUM(LARGE(C6:C12,{1,2,3})), trong đó {1,2,3} là mảng chứa số phần tử cần tính tổng, ở đây là 3.

Sau khi nhập công thức, nhấn tổ hợp phím Ctrl + Shift + Enter để xem kết quả:

Ví dụ: Để tính tổng doanh số của 4 nhân viên có doanh số cao nhất, chỉ cần thêm phần tử thứ 4 vào mảng: {1,2,3,4}.

7. Sử dụng công thức mảng để đánh số thứ tự, đảm bảo dữ liệu không bị ảnh hưởng khi xóa hàng hoặc cột.

Bước 1: Bôi đen vùng dữ liệu cần đánh số thứ tự -> Nhập công thức vào ô đầu tiên của cột số thứ tự.

Sau khi nhập công thức, nhấn tổ hợp phím Ctrl + Shift + Enter để hiển thị kết quả:

Với cách đánh số thứ tự bằng công thức mảng, việc thêm hoặc xóa dòng sẽ không làm thay đổi thứ tự đã được đánh. Ví dụ: Thêm một dòng mới bằng cách bôi đen dòng bất kỳ -> Chuột phải chọn Insert.

Dù thứ tự dòng 1, 2, 3, 4, 5 bị thay đổi, số thứ tự trong cột vẫn được giữ nguyên và không bị ảnh hưởng.

Trên đây là những ví dụ minh họa chi tiết về cách ứng dụng công thức mảng trong Excel, tùy theo nhu cầu công việc mà bạn có thể lựa chọn phương pháp phù hợp.

Chúc các bạn áp dụng thành công và đạt được hiệu quả cao trong công việc!