Buzz
Trong bài viết này, chúng ta sẽ chi tiết về công thức mảng và xem những ví dụ thực tế giúp bạn nắm bắt và áp dụng chúng hiệu quả trong công việc hàng ngày. Nhớ nhấn tổ hợp phím Ctrl + Shift + Enter sau khi nhập công thức để kích hoạt công thức mảng thay vì sử dụng phím Enter.
1. Sử dụng công thức mảng để tính tổng và đếm theo điều kiện
Để tính tổng có điều kiện, thường ta dùng hàm sumif. Tuy nhiên, để giản đơn, hãy xem ví dụ về cách tính tổng có điều kiện sử dụng công thức mảng.
Ví dụ: Tính tổng số nhân viên có doanh số bán hàng vượt quá 38. Và tính tổng số lượng sản phẩm đã bán của những nhân viên đó.
Dữ liệu cho trước như sau:
1.1. Đếm số nhân viên có doanh số bán hàng lớn hơn 30
Trong trường hợp này, bạn có thể sử dụng hàm counif, nhưng ở đây, mình sẽ hướng dẫn cách sử dụng công thức mảng Sum. Tại ô cần tính, nhập công thức sau: =SUM(B4:B10>30).
Nhấn tổ hợp phím Ctrl + Shift + Enter. Đây là kết quả:
Vì điều kiện so sánh trả về giá trị True hoặc False khi sử dụng hàm Sum, bạn cần bổ sung vào công thức như sau:
Nhấn F9 -> Sau đó, nhấn tổ hợp phím Ctrl + Shift + Enter để xem kết quả như hình dưới:
1.2. Tính tổng có điều kiện sử dụng công thức mảng
Tính tổng số lượng sản phẩm đã bán của những người bán hàng trên 30 sản phẩm.
Bước 1: Tại ô cần tính, nhập công thức.
Nhấn tổ hợp phím Ctrl + Shift + Enter. Kết quả sẽ hiển thị:
Đối với một số máy, nếu bạn nhập công thức như trên mà không có kết quả đúng, hãy bôi đen tham số trong hàm Sum -> Nhấn F9 kiểm tra lại kết quả -> sau đó nhấn Ctrl + Shift + Enter.
2. Sử dụng công thức mảng để tính chênh lệch lớn nhất của mã cổ phiếu qua các ngày.
Ví dụ: Tính giá trị chênh lệch lớn nhất và nhỏ nhất của mã cổ phiếu qua các ngày:
Thường thì bạn sẽ tạo một cột phụ để tính giá trị chênh lệch và sử dụng hàm Min, Max. Tuy nhiên, trong bài viết này, chúng ta sẽ sử dụng công thức mảng để tính toán mà không cần tạo một cột phụ cho giá trị chênh lệch.
Bước 1: Tại ô cần tính giá chênh lệch, nhập công thức sau: =Max(E5:E10-D5:D10).
Sau khi nhập xong công thức, nhấn 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 để kiểm tra:
Ở đây, giá trị Max được tính trong mảng gồm 6 phần tử.
3. Tính Max, Min theo điều kiện kết hợp công thức mảng
Ví dụ, để tính doanh số lớn nhất và nhỏ nhất của các nhân viên đã bán được trong tháng, nhập công thức tại ô cần tính: = 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 để xem kết quả:
Tính doanh số nhỏ nhất tương tự như doanh số lớn nhất, chỉ thay đổi bằng hàm MIN:
Tính cho các nhân viên khác bằng cách thay đổi điều kiện so sánh trong hàm If với mã nhân viên tương ứng.
4. Tìm những mặt hàng đã được giao cho các đại lý ở 3 miền Bắc, Trung, Nam
Sử dụng hàm Index, match kết hợp với công thức mảng.
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)),'').
Nhấn tổ hợp phím Ctrl + Shift + Enter sau khi nhập xong công thức để xem kết quả:
Kéo công thức cho các ô còn lại để nhận được kết quả.
Sử dụng hàm IFERROR để tránh lỗi khi ở miền Bắc, Trung mặt hàng Máy gọt hoa quả không có, trả về giá trị rỗng.
Tính tổng chi phí.
Ví dụ: Tính tổng chi phí của các mặt hàng SP1, SP2, SP3 dựa trên bảng chi phí và số lần xuất hiện mã sp 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 để xem kết quả:
Tính tổng doanh số của những nhân viên bán được nhiều nhất.
Bạn có thể chọn tính tổng của n nhân viên bán được nhiều nhất với n=2, 3, 4…
Ví dụ tính tổng doanh số bán của 3 nhân viên bán được nhiều 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ố lớn nhất, chỉ cần thêm phần tử 4 vào mảng.
Sử dụng công thức mảng để đánh số thứ tự đảm bảo thành phần dữ liệu không bị xóa.
Bước 1: Bôi đen vùng dữ liệu muốn tạo số thứ tự -> quay trở lên trên và nhập công thức.
Sau khi nhập công thức, nhấn tổ hợp phím Ctrl + Shift + Enter để xem kết quả:
Với cách nhập số thứ tự từ công thức mảng, thứ tự cột không bị thay đổi khi thêm dòng mới. Ví dụ thêm 1 dòng: Bôi đen 1 dòng bất kỳ -> Chuột phải chọn Insert.
Số thứ tự dòng 1, 2, 3, 4, 5 bị thay đổi, nhưng số thứ tự trong cột không thay đổi.
Dưới đây là một số ví dụ về cách áp dụng công thức mảng cho công việc của bạn.
Chúc các bạn đạt được thành công lớn!
