Hàm
=sum[E2:E5]5 sẽ chỉ hoạt động bình thường nếu dữ liệu trong
=sum[E2:E5]6 hoặc
=sum[E2:E5]7 được sắp xếp. Sử dụng
=sum[E2:E5]87,
=sum[E2:E5]88 hoặc các chức năng liên quan khác nếu dữ liệu không được sắp xếp
Nếu không tìm thấy
=ArrayFormula[ sum[B2:B5+C2:C5+D2:D5] ]0, mục được sử dụng trong tra cứu sẽ là giá trị nhỏ hơn ngay lập tức trong phạm vi được cung cấp. Ví dụ: nếu tập dữ liệu chứa các số 1, 3, 5 và
=ArrayFormula[ sum[B2:B5+C2:C5+D2:D5] ]0 là 2 thì 1 sẽ được sử dụng để tra cứu
Trong phương pháp
=sum[E2:E5]7, hàng hoặc cột cuối cùng trong phạm vi được cung cấp được trả về. Nếu muốn một hàng hoặc cột khác, hãy sử dụng
=sum[E2:E5]87 hoặc
=sum[E2:E5]88 để thay thế
Khi sử dụng phương pháp
=sum[E2:E5]7, nếu phạm vi được cung cấp chứa nhiều cột hơn hàng, thì tìm kiếm sẽ từ trái sang phải trên hàng đầu tiên trong phạm vi. Nếu phạm vi chứa số hàng và số cột bằng nhau hoặc số hàng nhiều hơn số cột thì tìm kiếm sẽ từ trên xuống dưới đối với cột đầu tiên trong phạm vi
ví dụ
Ví dụ này cho thấy giá của một phần số 126
Mã bộ phận GiáCông thứcKết quả105$27. 50=TÌM KIẾM[126, $A$2. $A$6, $B$2. $B$6]$33. 00126$33. 00 133$41. 75 171$26. 75 188$32. 99
Ví dụ này cho thấy khi không tìm thấy khóa_tìm_kiếm [ID sinh viên 765333], kết quả khớp không chính xác có thể được trả về
Mã bộ phận GiáCông thứcResult764541B=LOOKUP[765333,$A$2. $A$5,$B$2. $B$5]C764598C 765444B- 766765AXem thêm
=sum[E2:E5]87. tra cứu dọc. Tìm kiếm một khóa ở cột đầu tiên của một dải ô và trả về giá trị của một ô đã chỉ định trong hàng được tìm thấy
=sum[E2:E5]88. tra cứu ngang. Tìm kiếm một khóa trên hàng đầu tiên của một dải ô và trả về giá trị của một ô đã chỉ định trong cột được tìm thấy
Hãy tưởng tượng tình huống bạn có 2 cột trong bảng tính Google. cột thứ nhất có giá, cột thứ 2 có số lượng mặt hàng và bạn cần nhân chúng trong cột thứ 3. Bạn thường làm gì trong trường hợp này? . Một phương pháp trường học cũ tốt hoạt động tốt.
Nhưng nếu có 1000 hàng hoặc thậm chí nhiều hơn thì sao? . Nó cũng có thể gây ra vấn đề về hiệu suất vì một loạt các công thức tương tự làm chậm toàn bộ bảng tính. Và nếu bạn cần thêm một giá trị mới và tạo một hàng riêng cho giá trị đó, Google Trang tính sẽ không tự động sao chép công thức.
OK, vậy giải pháp ở đây là gì?
Trên thực tế, có một cách năng động và hiệu quả để giải quyết các vấn đề được thảo luận và cách này được gọi là ARRAYFORMULA.
Mục lục
ARRAYFORMULA trong Google Trang tính là gì?
Nói tóm lại, ARRAYFORMULA là một hàm xuất ra một dải ô thay vì chỉ một giá trị và có thể được sử dụng với các hàm không phải mảng.
Theo tài liệu của Google Trang tính, ARRAYFORMULA cho phép
“việc hiển thị các giá trị được trả về từ một công thức mảng thành nhiều hàng và/hoặc cột và việc sử dụng các hàm không thuộc mảng với mảng”
Chà, định nghĩa sẽ giết chết bất kỳ mong muốn sử dụng chức năng nào, nhưng chờ đã, đừng vội kết luận. Nó cực kỳ hữu ích và dễ sử dụng hơn so với âm thanh trong mô tả.
Để sử dụng nó trong Google Trang tính, bạn có thể nhập trực tiếp “ARRAYFORMULA” hoặc nhấn phím tắt Ctrl+Shift+Enter [Cmd + Shift + Enter trên máy Mac], trong khi con trỏ của bạn ở trong thanh công thức để biến công thức thành công thức mảng .
Đọc thêm về phím tắt Google Trang tính
Nếu bạn thích xem hơn là đọc, hãy xem video hướng dẫn này của Railsware Product Academy về ARRAYFORMULA trong Google Sheets
Google Trang tính ARRAYFORMULA giải quyết vấn đề như thế nào?
- Vì đây là một công thức duy nhất ngay cả đối với một tập dữ liệu khổng lồ, nên bạn sẽ không phải sử dụng nhiều công thức và Google Trang tính của bạn sẽ chạy trơn tru
- ARRAYFORMULA cũng có thể mở rộng – một thay đổi ở một nơi sẽ mở rộng toàn bộ phạm vi dữ liệu
- Và nó cũng năng động. Khi một hàng mới được đưa vào tập dữ liệu, công thức sẽ tự động được áp dụng cho nó
Cú pháp ARRAYFORMULA của Google Trang tính
=ARRAYFORMULA[array_formula]=sum[E2:E5]4 là một tham số có thể được
- một phạm vi
- một biểu thức toán học sử dụng các phạm vi có cùng kích thước hoặc
- một hàm trả về kết quả lớn hơn một ô
Ví dụ về ARRAYFORMULA trên Google Trang tính
Hãy xem xét kỹ hơn cách ARRAYFORMULA hoạt động. Cách dễ nhất để hiểu điều này là thông qua một ví dụ
Giả sử chúng ta có một bộ dữ liệu hiển thị số lượng của bốn sản phẩm khác nhau được bán trong những tháng mùa hè
và chúng ta cần tính tổng số lượng sản phẩm đã bán
Chắc chắn rồi, chúng ta có thể làm điều đó bằng cách viết một công thức trong cột E có thêm B, C và D
=B2+C2+D2
Hoặc sử dụng hàm SUM.
Để tìm số lượng sản phẩm B, C, D đã bán, bạn copy công thức ở ô E2 rồi dán vào ô E3, E4, E5
và sau đó sử dụng SUM ở dưới cùng của cột E
=sum[E2:E5]
Tuy nhiên, hàm ARRAYFORMULA cho phép bạn bỏ qua tất cả các bước đó và đi thẳng đến câu trả lời bằng một công thức duy nhất, giúp bạn tiết kiệm thời gian và năng lượng nếu bạn có hơn 1000 công thức .
Trong trường hợp của chúng tôi, chúng tôi sẽ có
=ArrayFormula[ sum[B2:B5+C2:C5+D2:D5] ]
Bây giờ, tôi đang thêm một phạm vi mới cho Sản phẩm A1.
ARRAYFORMULA tính đến phạm vi mới [thay đổi B5 thành B6, C5 thành C6, D5 thành D6 trong công thức] và thực hiện phép tính, không giống như SUM, được mong đợi cho Google Trang tính. Bây giờ công thức trông như sau
=ArrayFormula[ sum[B2:B6+C2:C6+D2:D6] ]
ARRAYFORMULA với các chức năng khác nhau của Google Trang tính
Nếu một công thức đã trả về một mảng giá trị, thì không cần gói nó bằng ARRAYFORMULA. Điều này có nghĩa là việc kết hợp ARRAYFORMULA với, chẳng hạn như FILTER, SEQUENCE hoặc QUERY, sẽ không mang lại bất kỳ giá trị nào.
Đồng thời, bạn không thể hưởng lợi từ việc lồng ARRAYFORMULA với nhiều hàm không phải mảng bao gồm
- TỔNG
- SUMIFS
- ĐẾM
- ĐẾM
- ĐẾM
- NỐI
- THAM GIA
- KẾT NỐI
- vân vân
Tuy nhiên, như chúng tôi đã đề cập trước đây, ARRAYFORMULA có thể được sử dụng với các hàm không phải mảng, ví dụ: IF, SUMIF, COUNTIF, VLOOKUP, v.v.
Chúng tôi sẽ kiểm tra cách chúng hoạt động bên dưới, nhưng trước tiên, hãy nhập một số bộ dữ liệu mà chúng tôi có thể sử dụng làm ví dụ. Để thực hiện việc này, chúng tôi đã thiết lập tích hợp OneDrive với Google Sheets do Coupler cung cấp. io
khớp nối. io là giải pháp nhập dữ liệu cung cấp nhiều tích hợp Google Trang tính cho các ứng dụng như Jira, HubSpot, Airtable và nhiều ứng dụng khác. Nó cho phép bạn tự động hóa luồng dữ liệu theo lịch trình và sử dụng Excel và BigQuery làm đích ngoài Google Trang tính
ARRAYFORMULA trên Google Trang tính với hàm IF
Để nhắc bạn, hàm IF trong Google Trang tính hoạt động bằng cách thực hiện một bài kiểm tra logic chỉ có thể có một trong hai kết quả. đúng hay sai. Đọc thêm về IF và các hàm logic khác trong Google Trang tính,
Hãy xem cách sử dụng hàm IF và ARRAY trên bảng tính bán hàng. Hãy xem xét một câu lệnh IF tiêu chuẩn để kiểm tra xem có đủ [hơn 10] mặt hàng còn lại trong kho cho tháng tới không.
Trong ô G2, tôi muốn hiển thị văn bản "đặt hàng" nếu có ít hơn mười mặt hàng còn lại trong kho và "không" nếu kết quả là sai.
=if[C2 nhập=ArrayFormula[ sum[B2:B5+C2:C5+D2:D5] ]0 vào trường “=ArrayFormula[ sum[B2:B5+C2:C5+D2:D5] ]1”Đây là giao diện của tập dữ liệu Người đã nhập
Các tham số cho trình nhập Pipedrive #2
- Thực thể dữ liệu.
=ArrayFormula[ sum[B2:B5+C2:C5+D2:D5] ]
2 - Lĩnh vực.
=ArrayFormula[ sum[B2:B5+C2:C5+D2:D5] ]
3
Trường
=ArrayFormula[ sum[B2:B5+C2:C5+D2:D5] ]4 là cần thiết để tra cứu dữ liệu theo chiều dọc
Đây là giao diện của tập dữ liệu Giao dịch đã nhập
Bước 2. Áp dụng công thức VLOOKUP được lồng với ARRAYFORMULA trong Google Trang tính
Khi bạn đã lấy dữ liệu, hãy áp dụng công thức VLOOKUP sau cho ô A1 trong trang tính Người của Pipedrive
=B2+C2+D27
Không còn nghi ngờ gì nữa, sự kết hợp ARRAYFORMULA-VLOOKUP, khi được sử dụng đúng cách, là một công cụ có thể giúp bạn tiết kiệm rất nhiều thời gian và giải phóng rất nhiều công việc bận rộn.
Thử ngay trình nhập Pipedrive để tải dữ liệu của bạn vào bảng tính
FILTER và ARRAYFORMULA trong Google Trang tính
Một chức năng phổ biến và hữu ích khác tỏ ra hữu ích khi bạn cần tìm thông tin nhanh chóng là chức năng LỌC. Nó được sử dụng để lọc có điều kiện phạm vi dữ liệu đã chỉ định để lấy thông tin cần thiết. Chức năng Google Trang tính này đã được giải thích trong Hướng dẫn cách thực hiện LỌC, nhưng hãy xem xét lại việc áp dụng LỌC với ARRAYFORMULA.
Trong bảng tính bán hàng, hãy lọc ra các bản trùng lặp - trong trường hợp của chúng tôi, các số bán hàng giống hệt nhau. Vì không có chức năng trực tiếp nào trong Google Trang tính để xử lý tác vụ, nên cách giải quyết tối ưu sẽ là sử dụng LỌC với UNIQUE, ARRAYFORMULA và COUNTIF
=B2+C2+D28
Như bạn có thể thấy, công thức đáp ứng các thách thức và trả về một bản sao.
Cách dùng ARRAYFORMULA gộp cột trong Google Sheets
ARRAYFORMULA còn giúp bạn thao tác với văn bản. Bạn thực sự có thể kết hợp một văn bản với một văn bản, một văn bản với một số và một văn bản với một ngày trong Google Trang tính và áp dụng ARRAYFORMULA cho sự kết hợp đó
Ví dụ: nếu chúng tôi có một danh sách, giả sử, người quản lý bán hàng và cần kết hợp họ và tên. Để lấy tên và họ của người quản lý bán hàng đầu tiên, hãy sử dụng công thức sau
=B2+C2+D29
Tên đầy đủ xuất hiện trong một ô duy nhất, E2. Đọc thêm về Hợp nhất dữ liệu trong Google Trang tính.
Vì vậy, hãy sử dụng hàm ARRAYFORMULA để ghép tất cả các tên và họ
=sum[E2:E5]0
Chỉ được áp dụng trong E2, công thức sẽ tự động mở rộng sang các ô khác bên dưới.
Thử thách các ô trống trong đầu ra ARRAYFORMULA của Google Trang tính
Khi bạn làm việc với hàm ARRAYFORMULA, bạn phải cẩn thận với kích thước mảng. Chúng phải luôn giống nhau, ví dụ, F2. F17&G2. G17. Nếu không, Google Trang tính sẽ không thực hiện phép tính. Như một tùy chọn, để không đổ mồ hôi quá nhiều, bạn có thể sử dụng phạm vi vô hạn, như chúng tôi đã làm với SUMIF.
=sum[E2:E5]1
Tuy nhiên, trong trường hợp này, bạn có thể phải đối mặt với một thách thức khác - có thêm các ô trống trong đầu ra công thức của bạn. Trong trường hợp của tôi với ARRAYFORMULA+SUMIF trong I2, nếu có phạm vi giới hạn H2. H4, tất cả sẽ hoạt động tốt.
Mặc dù vậy, nếu bạn thay đổi nó thành H2. H, SUMIF sẽ coi phạm vi này là phạm vi chứa tiêu chí và sẽ tính tổng cột tương ứng
Chà, nó có thể làm hỏng giao diện, chứ đừng nói đến việc làm lộn xộn toàn bộ bảng tính, nhưng quan trọng nhất là khi bạn nhập bất kỳ giá trị nào vào bất kỳ ô nào trong số đó, công thức sẽ trả về lỗi #REF. Lỗi
Và điều này không chỉ xảy ra với SUMIF mà còn với các công thức khác. Vì vậy, hãy xem làm thế nào để loại bỏ chúng
Cách xóa các ô trống thừa trong đầu ra ARRAYFORMULA Google Trang tính
Vì vậy, để loại bỏ các ô trống thừa do ARRAYFORMULA được lồng với SUMIF trong Google Trang tính trả về, chúng ta có thể sử dụng hàm FILTER để lọc ra các ô trống trong tiêu chí gây ra các số 0 thừa và các ô trống tương ứng
=sum[E2:E5]2
Hoặc, cách khác, bạn có thể sử dụng IF+LEN và nó sẽ thực hiện công việc tương tự.
=sum[E2:E5]3
Tuy nhiên, không giống như tùy chọn với FILTER, IF+LEN sẽ không giúp tránh được sự cố với #REF. Lỗi và cũng sẽ trả về nếu bạn nhập bất kỳ giá trị nào vào các ô trống, vì vậy hãy cẩn thận.
Đó là bức màn?
Khó khăn. Chức năng Google Trang tính ARRAYFORMULA là một công cụ thực sự đa năng và có thể được sử dụng với nhiều kết hợp và ứng dụng khác không được đề cập ở đây. Nếu bạn có bất kỳ ý tưởng nào và muốn thảo luận về chúng, hãy bình luận bên dưới và chúng tôi sẽ giải thích
Hiện tại, chúc bạn may mắn với dữ liệu của mình và như Ben Collins, nhà phát triển Google Trang tính và người hướng dẫn phân tích dữ liệu, đã viết trên blog của mình, “Hip, Hip Array. ”