Cách dùng công thức mảng Array Formulas trong Excel và ví dụ minh họa

Bạn có bao giờ muốn tìm hiểu về cách sử dụng công thức mảng Array Formulas trong Excel nhưng không biết bắt đầu từ đâu? Đừng lo, trong bài viết này, tôi đã tổng hợp kiến thức và chia sẻ ví dụ cụ thể để giúp bạn hiểu rõ hơn về cách thức và ứng dụng của công thức mảng trong Excel. Hãy cùng tìm hiểu nhé!

Công thức mảng để tính tổng theo điều kiện, đếm theo điều kiện 

Khi muốn tính tổng có điều kiện, chúng ta thường sử dụng hàm sumif. Tuy nhiên, để đơn giản hóa công thức, chúng ta có thể thử sử dụng công thức mảng.

Ví dụ: Hãy tính tổng số nhân viên bán được với số lượng lớn hơn 38. Và tính tổng số lượng hàng bán được của những nhân viên đó.

Dữ liệu ban đầu:

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

Trong trường hợp này, bạn có thể sử dụng hàm counif. Tuy nhiên, chúng ta có thể giới thiệu cách sử dụng công thức mảng Sum. Tại ô cần tính, hãy thử nhập công thức sau: =SUM(B4:B10>30).

Nhấn tổ hợp phím Ctrl + Shift + Enter. Kết quả:

Vì điều kiện so sánh trả về giá trị True hoặc False và chúng ta sử dụng hàm Sum, bạn có thể bổ sung công thức như sau:

Nhấn F9 -> 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 

Hãy tính tổng số lượng sản phẩm đã bán được của những người đã bán trên 30 sản phẩm.

Bước 1: Tại ô cần tính, hãy nhập công thức.

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

Đối với một số máy tính, nếu bạn nhập công thức như vậy mà không nhận đượ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ả -> nhấn Ctrl + Shift + Enter.

Công thức mảng sử dụng trong việc tính chênh lệch lớn nhất của mã số cổ phiếu qua các ngày 

Ví dụ: Hãy tính giá 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ường, chúng ta thường tạo ra một cột phụ để tìm giá chênh lệch, sau đó 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 ra một cột phụ về giá chênh lệch.

Bước 1: Tại ô cần tính giá chênh lệch, hãy nhập công thức sau: =Max(E5:E10-D5:D10).

Sau khi nhập xong công thức, hãy 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 để kiểm tra:

Ở đây, Max sẽ lấy giá trị từ mảng gồm 6 phần tử.

Tính Max, Min theo điều kiện kết hợp công thức mảng 

Ví dụ: Hãy 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. Tại ô cần tính doanh số lớn nhất, hãy nhập công thức: =Max(If($B$5:$B$10=E6,C5:C10)).

Sau khi nhập công thức, hãy 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ỉ cần thay hàm Max bằng hàm Min:

Bạn có thể 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.

Tính tổng chi phí 

Ví dụ: Hãy tính tổng chi phí của các mặt hàng SP1, SP2, SP3 dựa vào 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, hãy nhập công thức: SUM(SUMIFS(H5:H7,G5:G7,C5:C9)).

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

FAQ – Những câu hỏi liên quan

Làm thế nào để tính tổng số nhân viên bán được với số lượng lớn hơn 38 và tính tổng số lượng hàng bán được của những nhân viên đó?

Để tính tổng số nhân viên bán được với số lượng lớn hơn 38, bạn có thể sử dụng công thức mảng: =SUM(B4:B10>38).

Để tính tổng số lượng hàng bán được của những nhân viên đó, bạn có thể sử dụng công thức mảng: =SUM((B4:B10>38)*C4:C10).

Làm thế nào để tính giá chênh lệch lớn nhất và nhỏ nhất của mã cổ phiếu qua các ngày?

Để tính giá chênh lệch lớn nhất của mã cổ phiếu qua các ngày, bạn có thể sử dụng công thức mảng: =MAX(E5:E10-D5:D10).

Để tính giá chênh lệch nhỏ nhất của mã cổ phiếu qua các ngày, bạn có thể sử dụng công thức mảng: =MIN(E5:E10-D5:D10).

Làm thế nào để tính doanh số lớn nhất và nhỏ nhất cho các nhân viên đã bán được trong tháng?

Để tính doanh số lớn nhất của các nhân viên đã bán được trong tháng, bạn có thể sử dụng công thức mảng: =MAX(IF(B5:B10-E6,C5:C10))

Để tính doanh số nhỏ nhất của các nhân viên đã bán được trong tháng, bạn có thể sử dụng công thức mảng: =MIN(IF(B5:B10-E6,C5:C10))

Hãy chia sẻ bài viết này để mọi người cùng tìm hiểu về cách sử dụng công thức mảng trong Excel. Nếu bạn có bất kỳ câu hỏi nào hoặc muốn đưa ra ý kiến của mình, hãy để lại bình luận ở dưới bài viết này. Chúng tôi sẽ trả lời và giúp bạn sớm nhất có thể. Chúng ta hãy cùng học hỏi và nâng cao kỹ năng với Excel nhé!