Các toán tử và công thức mảng Excel

CHÀO MỪNG BẠN ĐẾN VỚI THƯ VIỆN HỌC TẬP & ĐÀO TẠO CƠ BẢN

Các toán tử và công thức mảng Excel

Excel

Bắt đầu sử dụng Excel

Công thức và hàm

Nhập và phân tích dữ liệu

Định dạng dữ liệu

Xử lý sự cố

Excel 2016

Các công thức mảng Excel là một công cụ vô cùng hữu ích và cũng khó nhất để kiểm soát được nó. Một công thức mảng Excel đơn có thể thực hiện nhiều phép tính và thay thế hàng ngàn công thức thông thường. Tuy nhiên, ước chừng đến 90% người dùng Excel chưa bao giờ sử dụng các công thức mảng trong bảng tính, chỉ vì họ sợ phải bắt đầu học chúng từ con số 0.

1. Toán tử And và Or trong công thức mảng Excel

Một toán tử của mảng cho thấy cách bạn muốn xử lý mảng – sử dụng hàm logic AND và OR.

  • Toán tử AND là dấu hoa thị (*). Nó hướng dẫn Excel trả về TRUE nếu TẤT CẢ các điều kiện được đánh giá là TRUE.
  • Toán tử OR là dấu cộng (+). Nó trả về TRUE nếu bất kỳ điều kiện nào trong một biểu thức đã cho là TRUE.

1.1. Công thức mảng với toán tử And

Trong ví dụ này, chúng tôi cho biết tổng doanh thu tương ứng mà người bán hàng là Mike và sản phẩm là Apples:

= SUM ((A2: A9 = “Mike”) * (B2: B9 = “Apples”) * (C2: C9))

Hoặc là

= SUM (IF (((A2: A9 = “Mike”) * (B2: B9 = “Apples”)), (C2: C9)))

Toán tử And và Or trong công thức mảng Excel

Toán tử And và Or trong công thức mảng Excel

1.2. Công thức mảng Excel với toán tử Or

Công thức mảng sau với toán tử OR (+) cho phép thêm tất cả các bán hàng ứng với người bán hàng là Mike hoặc sản phẩm là Apples:

= SUM (IF (((A2: A9 = “Mike”) + (B2: B9 = “Apples”)), (C2: C9)))

Mách nhỏ: Trong các phiên bản hiện đại của Excel 2013, 2010 hoặc 2007, việc sử dụng các công thức mảng trong các tình huống như vậy thực sự không cần thiết và một công thức SUMIFS đơn giản có thể cho ra cùng một kết quả như trên. Tuy nhiên, toán tử AND và OR trong các công thức mảng có thể hữu ích trong các tình huống phức tạp hơn.

2. Toán tử đơn vị đôi điều hành trong công thức mảng Excel

Nếu bạn đã từng làm việc với các công thức mảng trong Excel, rất có thể bạn đã lướt qua một vài công thức có chứa hai dấu gạch ngang (–) và bạn có thể đã tự hỏi rằng nó được sử dụng cho những việc gì.

Hai dấu gạch ngang đôi, gọi là toán tử đơn vị đôi, được sử dụng để chuyển các giá trị Boolean không bằng số (TRUE / FALSE) bởi một số biểu thức thành 1 và 0 – thứ mà một hàm mảng có thể hiểu được.

Ví dụ sau hy vọng làm cho mọi thứ dễ hiểu hơn cho bạn. Giả sử bạn có một danh sách ngày trong cột A và bạn muốn biết có bao nhiêu ngày xảy ra vào tháng Giêng, bất kể năm nào.

Các công thức sau đây sẽ giải quyết vấn đề trên:

= SUM (–(MONTH (A2: A10) = 1))

Vì đây là công thức mảng Excel, hãy nhớ nhấn Ctrl + Shift + Enter để hoàn tất.

Nếu bạn quan tâm đến một tháng khác, hãy thay thế 1 bằng một số tương ứng. Ví dụ, 2 là Tháng 2, 3 có nghĩa là Tháng 3, v.v. Để làm cho công thức linh hoạt hơn, thì bạn có thể chỉ định số của Tháng trong một số ô, như trong hình:

Và bây giờ, chúng ta hãy cùng phân tích xem rằng công thức mảng Excel làm việc như thế nào nhé. Hàm MONTH trả về kết quả là mỗi ngày trong các ô A2 đến A10 nằm trong tháng nào, chúng sẽ được biểu thị bởi một số sê-ri, tạo ra mảng {2; 1; 4; 2; 12; 1; 2; 12; 1}.

Sau đó, mỗi phần của mảng sẽ được so sánh với giá trị trong ô D1, là số 1 trong ví dụ này. Kết quả của phép so sánh này là một mảng các giá trị Boolean (TRUE và FALSE). Như bạn nhớ, thì bạn có thể chọn một phần nhất định trong một công thức mảng và nhấn F9 để xem phần đó tương đương với:

Bạn có thể thành thạo ngay kỹ năng tin học văn phòng từ giảng viên FPT- Arena chỉ với 280,000Đ