Công thức mảng trong Excel

Chia sẻ bởi:api_publisher

★★★★★

Quảng cáo

Công thức mảng trong Excel và cách dùng SUMPRODUCT để tính tổng nhiều điều kiện

Công thức mảng trong Excel là kỹ thuật mạnh giúp xử lý đồng thời nhiều giá trị mà không cần thêm cột phụ. Khi hiểu đúng mảng, hàm mảng và SUMPRODUCT, bạn có thể xây dựng các công thức tính tổng, đếm, lọc, dò tìm và phân tích dữ liệu linh hoạt hơn rất nhiều.

Nội dung chính:

Công thức mảng trong Excel là gì?
Hàm mảng và công thức mảng khác nhau như thế nào?
Vì sao SUMPRODUCT là hàm xử lý mảng nhưng không cần Ctrl + Shift + Enter?
Cách dùng SUMPRODUCT để tính tổng nhiều điều kiện
So sánh SUMPRODUCT với công thức mảng dùng SUM + IF

1. Công thức mảng trong Excel là gì?

Trong Excel, mảng là một tập hợp nhiều giá trị được xử lý cùng lúc. Thay vì tính từng ô riêng lẻ, công thức mảng cho phép Excel tính toán đồng thời trên nhiều dòng hoặc nhiều cột.

Ví dụ đơn giản:

Nếu vùng A1:A2 có giá trị lần lượt là 0 và 1, biểu thức:

=A1:A2=0

sẽ trả về một mảng kết quả:

{TRUE;FALSE}

Điều này có nghĩa là Excel kiểm tra từng ô trong vùng A1:A2 với điều kiện bằng 0, sau đó trả về kết quả tương ứng cho từng phần tử.

2. Hàm mảng và công thức mảng khác nhau như thế nào?

Nhiều người thường nhầm giữa hàm mảng và công thức mảng. Có thể hiểu đơn giản như sau:

Tiêu chí	Hàm mảng	Công thức mảng
Bản chất	Bản thân hàm được thiết kế để xử lý hoặc trả về mảng.	Là công thức dùng toán tử, hàm và vùng dữ liệu để xử lý nhiều giá trị cùng lúc.
Kết quả	Có thể trả về mảng hoặc một giá trị tổng hợp.	Có thể trả về một giá trị hoặc nhiều giá trị.
Cách nhập trong Excel cũ	Thường chỉ cần nhấn Enter nếu hàm hỗ trợ xử lý mảng.	Có thể cần nhấn Ctrl + Shift + Enter trong các phiên bản Excel cũ.
Ví dụ	SUMPRODUCT, FREQUENCY, TRANSPOSE.	`=SUM(IF(C1:C3="Cam",A1:A3*B1:B3,0))`

Lưu ý:

Trong Excel 365 và các phiên bản Excel mới, nhiều công thức mảng động có thể hoạt động trực tiếp bằng phím Enter. Tuy nhiên, với Excel cũ, nhiều công thức mảng vẫn cần nhập bằng tổ hợp phím Ctrl + Shift + Enter.

3. Vì sao phím F9 quan trọng khi học công thức mảng?

Khi xây dựng công thức mảng, phím F9 rất hữu ích vì giúp kiểm tra nhanh kết quả trung gian ngay trên thanh công thức.

Cách dùng F9:

Bôi đen một phần công thức trên thanh công thức.
Nhấn F9 để xem Excel đang tính ra kết quả gì.
Nhấn Esc để thoát, tránh làm thay đổi công thức gốc.

Kỹ thuật này đặc biệt hữu ích khi kiểm tra các biểu thức dạng (C1:C3="Cam") hoặc A1:A3*B1:B3.

4. Hàm SUMPRODUCT trong Excel hoạt động như thế nào?

Hàm SUMPRODUCT dùng để nhân các phần tử tương ứng trong các mảng rồi cộng tổng kết quả lại.

=SUMPRODUCT(array1,array2,array3,...)

Ví dụ có bảng dữ liệu:

Dòng	Số lượng	Đơn giá	Loại hàng
1	2	20	Cam
2	3	10	Bưởi
3	4	25	Cam

Nếu cần tính tổng doanh thu, công thức là:

=SUMPRODUCT(A1:A3,B1:B3)

Bản chất Excel sẽ tính:

=A1*B1 + A2*B2 + A3*B3

Kết quả:

2 × 20 + 3 × 10 + 4 × 25 = 170

5. Cách dùng SUMPRODUCT để tính tổng có nhiều điều kiện

Giả sử cần tính tổng doanh thu của mặt hàng Cam, có thể dùng công thức:

=SUMPRODUCT(A1:A3*B1:B3*(C1:C3="Cam"))

Công thức này được hiểu như sau:

=A1*B1*(C1="Cam") + A2*B2*(C2="Cam") + A3*B3*(C3="Cam")

Khi điều kiện đúng, Excel hiểu TRUE = 1. Khi điều kiện sai, Excel hiểu FALSE = 0.

Dòng	Phép tính	Kết quả
Dòng 1	`220(Cam=Cam)`	40
Dòng 2	`310(Bưởi=Cam)`	0
Dòng 3	`425(Cam=Cam)`	100

Kết quả: 40 + 0 + 100 = 140

6. Phép toán logic trong công thức mảng

Khi dùng công thức mảng hoặc SUMPRODUCT, các điều kiện logic thường được chuyển đổi thành số:

TRUE tương đương 1
FALSE tương đương 0

6.1. Điều kiện AND trong công thức mảng

Dùng dấu nhân * để biểu diễn điều kiện và.

=(Điều kiện 1)*(Điều kiện 2)

Chỉ khi tất cả điều kiện đều đúng thì kết quả mới bằng 1.

6.2. Điều kiện OR trong công thức mảng

Dùng dấu cộng + để biểu diễn điều kiện hoặc.

=(Điều kiện 1)+(Điều kiện 2)

Chỉ cần một điều kiện đúng, kết quả sẽ lớn hơn 0.

Lưu ý:

Khi dùng điều kiện OR trong SUMPRODUCT, nếu có khả năng một dòng thỏa nhiều điều kiện cùng lúc, nên kiểm soát kết quả logic để tránh cộng trùng.

7. So sánh SUMPRODUCT và công thức mảng SUM + IF

Với bài toán tính tổng doanh thu mặt hàng Cam, có thể dùng nhiều cách:

Cách làm	Công thức	Cách nhập
SUMPRODUCT	`=SUMPRODUCT(A1:A3B1:B3(C1:C3="Cam"))`	Nhấn Enter
SUM + IF	`=SUM(IF(C1:C3="Cam",A1:A3*B1:B3,0))`	Excel cũ: Ctrl + Shift + Enter
SUM ép mảng	`=SUM(A1:A3B1:B3(C1:C3="Cam"))`	Excel cũ: Ctrl + Shift + Enter

Gợi ý dùng nhanh:

Nếu mục tiêu là tính tổng có điều kiện phức tạp, SUMPRODUCT thường dễ dùng, dễ kiểm tra và không cần Ctrl + Shift + Enter trong Excel cũ.

8. Khi nào nên dùng SUMPRODUCT?

SUMPRODUCT phù hợp trong các tình huống:

Tính tổng doanh thu theo một hoặc nhiều điều kiện;
Tính tổng tiền theo sản phẩm, bộ phận, nhân viên, tháng, trạng thái;
Tính tổng có điều kiện mà không muốn thêm cột phụ;
Thay thế một số tình huống dùng công thức mảng phức tạp;
Xử lý nhanh các phép tính dạng số lượng × đơn giá × điều kiện.

9. Kết luận

Công thức mảng giúp Excel xử lý nhiều giá trị cùng lúc, còn SUMPRODUCT là một trong những hàm mạnh và dễ ứng dụng nhất khi cần tính tổng có nhiều điều kiện. Nếu hiểu đúng cách Excel chuyển TRUE/FALSE thành 1/0, bạn có thể xây dựng nhiều công thức phân tích dữ liệu rất linh hoạt mà không cần tạo thêm cột phụ.

Câu hỏi thường gặp

Công thức mảng trong Excel là gì?

Công thức mảng là công thức có thể xử lý đồng thời nhiều giá trị trong một hoặc nhiều vùng dữ liệu, thay vì chỉ xử lý một giá trị tại một thời điểm.

SUMPRODUCT có phải là hàm mảng không?

SUMPRODUCT là hàm được thiết kế để xử lý các mảng và trả về một giá trị tổng hợp, nên có thể dùng trực tiếp với Enter trong nhiều trường hợp.

Vì sao SUM thường phải dùng Ctrl + Shift + Enter khi tính với mảng?

Vì hàm SUM thông thường nhận các số đơn lẻ. Khi đưa trực tiếp một biểu thức mảng vào SUM trong Excel cũ, cần Ctrl + Shift + Enter để Excel hiểu đây là công thức mảng.

Dùng dấu nhân và dấu cộng trong điều kiện mảng có ý nghĩa gì?

Dấu nhân biểu thị điều kiện AND, tức là tất cả điều kiện phải đúng. Dấu cộng biểu thị điều kiện OR, tức là chỉ cần một điều kiện đúng.

Tư vấn Doanh nghiệp
Tư vấn cá nhân
Email

Doanh nghiệp: 0969 798 944 | Cá nhân: 0984 394 338

Email: hrspring.vn@gmail.com

Địa chỉ: KĐT Vinhome Gardenia Hàm Nghi, Cầu Diễn, Nam Từ Liêm, Hà Nội

Dịch vụ:
Chiến lược nhân sự
Thiết kế tổ chức
Lương thưởng & Hiệu suất

Mentoring:
Mentoring HR
Mentoring Quản lý

Lưu ý:

Nội dung nêu trên là phần giải đáp, tư vấn của chúng tôi dành cho khách hàng của SPRINGO. Nếu quý khách còn vướng mắc, vui lòng gửi về Email hrspring.vn@gmail.com.
Nội dung bài viết chỉ mang tính chất tham khảo.
Điều khoản áp dụng theo Luật tại thời điểm viết bài.
Mọi ý kiến thắc mắc về bản quyền của bài viết vui lòng liên hệ qua địa chỉ mail hrspring.vn@gmail.com.