Công thức công thức mảng trong google sheet

Theo cách tiêu chuẩn, không thể sử dụng IF, AND, OR trong Mảng trong Google Trang tính. Nhưng chúng ta có hai cách giải quyết

Các giải pháp thay thế đang sử dụng toán tử (_______6 [bổ sung] và * [phép nhân]) hoặc hàm trợ giúp lambda

Tôi đang nói về cách sử dụng các hàm logic IF, AND, OR cùng nhau trong một Mảng

Mình đã có bài hướng dẫn sử dụng các hàm logic OR, AND trong mảng ở 2 bài viết riêng. Nhưng đó là những mảng riêng lẻ sử dụng. Đây là họ

1. Sử dụng kiểm tra logic AND trong mảng trong Google Sheets
2. Sử dụng Kiểm tra logic OR trong Mảng trong Google Trang tính

Cách sử dụng IF, AND, OR trong mảng trong Google Sheets

Công thức không phải mảng

Công thức trong ảnh chụp màn hình bên dưới cho thấy việc sử dụng các hàm logic IF, AND, OR không theo mảng ở dạng kết hợp

Nó được gọi là sử dụng kết hợp logic IF, AND, OR

Trong ô F2, tôi có công thức kiểm tra logic IF, AND, OR kết hợp sau đây, trả về tên của nhân viên có bộ phận là "Lập kế hoạch" và có phân bổ là "Dự án 1" hoặc "Dự án 2"

=if(and(A2=$E$2,OR(C2=$E$3,C2=$E$4)),B2,"")

Tôi đã sao chép nó xuống

Tôi muốn một công thức duy nhất trong ô F2 mở rộng kết quả xuống

Để tạo được công thức mảng như vậy, bạn nên biết cách sử dụng IF, AND, OR trong Công thức mảng trong Google Sheets. Nhưng trước đó, hãy để tôi giải thích công thức phi mảng ở trên cho người mới

Công thức trên là như thế này. Nếu “Bộ phận” là Kế hoạch (A2=$E$2) và “Phân bổ” là Dự án 1 hoặc Dự án 2 (OR(C2=$E$3, C2=$E$4)), tôi muốn công thức trả về tên của nhân viên đó trong B2

Công thức trong ảnh chụp màn hình ở trên hoạt động hoàn hảo. Nhưng ở dạng mảng thì không được. Chúng ta không thể sử dụng các hàm logic IF, AND, OR ở trên ở dạng mảng như bên dưới

=ArrayFormula(if(and(A2:A7=$E$2,OR(C2:C7=$E$3,C2:C7=$E$4)),B2:B7,""))

Dưới đây bạn có thể tìm thấy hai giải pháp làm việc. Một sử dụng các toán tử cộng (OR) và nhân (OR) và một sử dụng hàm lambda MAP

IF, AND, OR trong mảng sử dụng phép toán cộng (OR) và phép nhân (AND)

Có một số lợi ích khi sử dụng công thức Mảng

Nếu bạn sao chép một công thức trong nhiều ô, khi bạn muốn thay đổi nó, việc chỉnh sửa có thể khiến bạn đau đầu

Dưới đây là một công thức mảng mở rộng kết quả

=ArrayFormula(if((A2:A7=$E$2)*((C2:C7=$E$3)+(C2:C7=$E$4))>0,B2:B7,))

Như tôi đã đề cập ở phần đầu của Hướng dẫn Google Trang tính này, bạn không thể sử dụng logic IF, AND, OR cùng nhau trong một mảng theo cách tiêu chuẩn ngoại trừ sử dụng lambda

Công thức mảng này là một ví dụ về cách sử dụng IF, AND, OR trong Mảng trong Google Trang tính

Nó nhận các dấu nhân và dấu cộng thay vì AND, OR trong IF

Tôi biết tôi nên làm sáng tỏ công thức này để bạn có thể sử dụng nó

Giải phẫu của các công thức

Xem phần trích của công thức công thức mảng IF, AND, OR trong Google Sheets

=(A2:A7=$E$2)*((C2:C7=$E$3)+(C2:C7=$E$4))>0

Ghi chú. Nếu bạn sử dụng công thức được trích xuất độc lập để thử nghiệm, hãy sử dụng nó như bên dưới

=ArrayFormula((A2:A7=$E$2)*((C2:C7=$E$3)+(C2:C7=$E$4))>0)

Nó là cốt lõi của công thức mảng logic kết hợp IF, AND, OR ở trên

Phần màu lục lam sống động của công thức trên trả về TRUE hoặc FALSE dựa trên kết quả khớp

Giá trị của Boolean TRUE là 1 và 0 là FALSE. Nó kiểm tra trường "Bộ phận" với các tiêu chí trong E2

Nếu khoa khớp thì phần này sẽ trả về TRUE, và giá trị là 1. Chúng tôi đang mong đợi ở đây giá trị 1

Bây giờ về phần màu đỏ sống động trong công thức. Nó kiểm tra trường "Phân bổ" với hai tiêu chí. Dự án 1 và Dự án 2

Kết quả có thể là TRUE+FALSE, FALSE+TRUE hoặc FALSE+FALSE, có nghĩa là 1+0, 0+1 hoặc 0+0

Ở đây một lần nữa, chúng tôi đang mong đợi giá trị 1. Điều đó có nghĩa là ít nhất một dự án phải phù hợp với thử nghiệm

Vì vậy, công thức cuối cùng của chúng tôi nhân hai phần hợp lý này (màu xanh lục lam rực rỡ và màu đỏ rực rỡ). Như đã nói, chúng tôi muốn có kết quả

=ArrayFormula(if(and(A2:A7=$E$2,OR(C2:C7=$E$3,C2:C7=$E$4)),B2:B7,""))

Phần logic IF kiểm tra xem đầu ra ở trên có> 0 không

Nếu > 0, công thức trả về tên nhân viên, ngược lại để trống

Bằng cách này, chúng ta có thể sử dụng logic IF, AND, OR trong Mảng trong Google Trang tính. Vui thích

MAP Lambda để mở rộng Kết quả công thức IF, AND, OR trong Google Trang tính

Hàm trợ giúp lambda MAP sẽ là lựa chọn hoàn hảo của chúng tôi ở đây, không phải BYROW, vì chúng tôi phải ánh xạ nhiều cột trong bảng

Công thức mảng có một danh tiếng đáng sợ trong thế giới bảng tính (nếu bạn thậm chí đã nghe nói về chúng)

Công thức mảng cho phép bạn xuất ra một dải ô, thay vì một giá trị đơn lẻ. Chúng cũng cho phép bạn sử dụng các hàm không phải mảng với mảng dữ liệu (nghĩ rằng phạm vi)

Trong bài đăng này, tôi sẽ tìm hiểu những kiến ​​thức cơ bản về cách sử dụng công thức mảng và bạn sẽ thấy chúng thực sự không đáng sợ đến thế

Mảng hông, hông

Công thức mảng trong Google Trang tính là gì?

Trước hết, chúng là gì?

Đối với những người không quen biết, họ thật bí ẩn. khó nắm bắt. Khó để hiểu. Vâng, vâng, và vâng, nhưng chúng cực kỳ hữu ích trong những tình huống phù hợp

Theo định nghĩa chính thức, công thức mảng cho phép hiển thị các giá trị được trả về thành nhiều hàng và/hoặc cột và sử dụng các hàm không phải mảng với mảng

Tóm lại. trong khi một công thức bình thường đưa ra một giá trị duy nhất, công thức mảng đưa ra một phạm vi ô

Cách dễ nhất để hiểu điều này là thông qua một ví dụ

Hãy tưởng tượng chúng ta có bộ dữ liệu này, hiển thị số lượng và chi phí mặt hàng cho bốn sản phẩm

và chúng tôi muốn tính tổng chi phí của cả bốn sản phẩm

Chúng ta có thể dễ dàng làm điều này bằng cách thêm một công thức vào cột D nhân B và C, sau đó cộng một tổng ở cuối cột D

Tuy nhiên, với công thức mảng, chúng ta hãy bỏ qua bước đó và đi thẳng vào câu trả lời với một công thức duy nhất

Công thức là gì?

=ArrayFormula(if(and(A2:A7=$E$2,OR(C2:C7=$E$3,C2:C7=$E$4)),B2:B7,""))

Công thức này hoạt động như thế nào?

Thông thường, khi chúng ta sử dụng toán tử nhân (*) trong Sheet, chúng ta sẽ gán cho nó hai số hoặc hai ô để nhân với nhau

Tuy nhiên, trong trường hợp này, chúng tôi cung cấp cho nó hai phạm vi hoặc hai mảng dữ liệu

=ArrayFormula(if(and(A2:A7=$E$2,OR(C2:C7=$E$3,C2:C7=$E$4)),B2:B7,""))

Tuy nhiên, khi chúng tôi nhấn Enter, điều này sẽ cho chúng tôi như được hiển thị ở đây

Chúng tôi cần thông báo cho Google Trang tính rằng chúng tôi muốn đây là Công thức mảng. Chúng tôi làm điều này theo hai cách

Nhập từ

=ArrayFormula(if(and(A2:A7=$E$2,OR(C2:C7=$E$3,C2:C7=$E$4)),B2:B7,""))

=ArrayFormula(if(and(A2:A7=$E$2,OR(C2:C7=$E$3,C2:C7=$E$4)),B2:B7,""))

Bây giờ nó đã hoạt động và Google Trang tính sẽ xuất ra một mảng với mỗi ô tương ứng với một hàng trong các mảng ban đầu, như minh họa trong hình dưới đây

Thực tế, điều đang xảy ra là đối với mỗi hàng, Google thực hiện phép tính và đưa kết quả đó vào mảng đầu ra của chúng tôi (ở đây hiển thị các công thức tương đương)

và một chế độ xem khác, hiển thị cách tính toán được thực hiện (chỉ dành cho hàng đầu tiên và hàng cuối cùng)

Ghi chú. công thức mảng chỉ hoạt động nếu kích thước của hai mảng khớp nhau, trong trường hợp này, mỗi mảng có 4 số, vì vậy phép nhân mỗi hàng có thể xảy ra

Cuối cùng, chúng ta chỉ cần thêm hàm SUM để cộng bốn số

=ArrayFormula(if(and(A2:A7=$E$2,OR(C2:C7=$E$3,C2:C7=$E$4)),B2:B7,""))

như sau

Ngoài lề

Phép tính này cũng có thể được thực hiện với công thức TỔNG HỢP, lấy các giá trị mảng làm đầu vào, nhân chúng và cộng chúng lại với nhau

=ArrayFormula(if(and(A2:A7=$E$2,OR(C2:C7=$E$3,C2:C7=$E$4)),B2:B7,""))

Một ví dụ nhân công thức mảng khác

Trong ví dụ này, chúng ta nhập một công thức duy nhất để nhân mảng tiêu đề hàng với mảng tiêu đề cột

Điều này chỉ hoạt động vì kích thước của các mảng tương thích với nhau, vì số lượng cột trong ma trận thứ nhất bằng với số lượng hàng trong ma trận thứ hai

Công thức mảng với hàm IF

Đây là một ví dụ về hàm không phải mảng được sử dụng với mảng (phạm vi). Nó hoạt động vì chúng tôi đã thiết kế công thức IF dưới dạng Công thức mảng

Hãy xem xét một câu lệnh IF tiêu chuẩn để kiểm tra xem một giá trị trong cột A có lớn hơn 2.000 đô la hay không

=ArrayFormula(if(and(A2:A7=$E$2,OR(C2:C7=$E$3,C2:C7=$E$4)),B2:B7,""))

Sau đó, công thức này sẽ được sao chép vào từng hàng mà chúng tôi muốn chạy thử nghiệm

Chúng ta có thể thay đổi điều này thành một Công thức mảng duy nhất ở đầu cột và chạy câu lệnh IF trên tất cả các hàng cùng một lúc. Ví dụ: giả sử chúng ta có các giá trị từ hàng 2 đến hàng 10 thì chúng ta tạo một Công thức mảng như thế này

=ArrayFormula(if(and(A2:A7=$E$2,OR(C2:C7=$E$3,C2:C7=$E$4)),B2:B7,""))

Công thức duy nhất này, trên hàng 2, sẽ tạo một mảng đầu ra lấp đầy các hàng từ 2 đến 10 với các câu trả lời “Có”/”Không”, như thể hiện trong hình ảnh sau

Công thức mảng để làm gì?

Công thức Google Sheet ở đâu?

Làm cách nào để tìm giá trị trung bình trong Google Trang tính?