Công thức mảng google sheet vlookup
|
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 Show
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
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
Google Trang tính ARRAYFORMULA giải quyết vấn đề như thế nào?
Cú pháp ARRAYFORMULA của Google Trang tính=sum(E2:E5)4 là một tham số có thể được
Ví dụ về ARRAYFORMULA trên Google Trang tínhHã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ínhNế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
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
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<10, "order", "no") Hàm IF thực hiện phép tính của nó và đối với mặt hàng đầu tiên này, vì chỉ còn tám chiếc trong kho, văn bản "đặt hàng" được hiển thị. Bây giờ, hãy chạy thử nghiệm cho từng mục và đây là lúc một ARRAYFORMULA duy nhất có ích. Nhập ARRAYFORMULA trước IF và nó sẽ chạy câu lệnh IF trên tất cả các hàng cùng một lúc. Mát mẻ, phải không? =arrayformula( if(C2:C17<10, "order", "no") ) SUMIF & SUMIFS với ARRAYFORMULA trong Google Trang tínhDựa trên ví dụ trước, chúng ta hãy xem cách ARRAYFORMULA có thể được sử dụng với các hàm SUMIF và SUMIFS trên Google Trang tính SUMIF và SUMIFS là hai hàm độc lập trong Google Trang tính. SUMIF được sử dụng để cộng các giá trị dựa trên một điều kiện và mục đích của SUMIFS là tính tổng các giá trị trong một phạm vi, dựa trên nhiều điều kiện SUMIF + CÔNG THỨC Mảng trong Google Trang tínhVì vậy, hãy viết mã một công thức mảng cho SUMIF. Giả sử bạn cần tìm xem có bao nhiêu mặt hàng văn phòng phẩm đã được đặt và bạn áp dụng hàm SUMIF, hàm này trả về cho bạn 32 trong ô I2 =sumif(F2:F,H2:H,D2:D) Và nếu bạn muốn biết số lượng mặt hàng đã được đặt cho từng danh mục, cách tốt nhất là áp dụng ARRAYFORMULA, một lần nữa, cách này sẽ cực kỳ hữu ích nếu bạn cũng có cách. . =arrayformula( sumif(F2:F,H2:H,D2:D) ) SUMIFS + ARRAYFORMULA Google Trang tính không mở rộngVới SUMIFS, mọi thứ phức tạp hơn một chút. Cú pháp của nó là =sum(E2:E5)0 Không giống như SUMIF, hàm SUMIFS không mở rộng kết quả ngay cả khi bạn sử dụng ARRAYFORMULA với nó. Logic rất đơn giản, vì SUMIFS trong Google Trang tính trả về tổng của một mảng theo điều kiện, do đó, nó chỉ có thể là một kết quả duy nhất. Hãy cùng kiểm tra nào =B2+C2+D20 SUMIFS trong ví dụ trên tổng hợp số lượng mặt hàng “Văn phòng phẩm” cần đặt hàng. Và, ngay cả khi chúng ta lồng SUMIFS với ARRAYFORMULA, nó sẽ không mở rộng mà sẽ trả về một kết quả theo cách này hay cách khác. =B2+C2+D21 Để giải quyết vấn đề mở rộng này, người ta nên sử dụng các công thức thay thế. Có một số tùy chọn về cách giải quyết các sự cố mở rộng SUMIFS-ARRAYFORMULA và cách dễ nhất là với sự trợ giúp của SUMIF. Thay thế số 1. Google Trang tính ARRAYFORMULA và SUMIFTrên thực tế, hàm SUMIF có thể xử lý nhiều tiêu chí để mở rộng kết quả, mặc dù theo một cách hơi phức tạp. Mẹo chính ở đây là kết hợp các phạm vi và tiêu chí tương ứng bằng cách sử dụng AMPERSAND ( =sum(E2:E5)5). =B2+C2+D22 Và, như bạn có thể thấy, nó hoàn toàn có thể mở rộng. Phương án số 2. Google Trang tính ARRAYFORMULA, IF, LEN, VLOOKUP, QUERYMột cách giải quyết khác là sử dụng kết hợp các hàm ARRAYFORMULA, IF, LEN, VLOOKUP và QUERY. Có vẻ phức tạp? . Nhưng công thức hoạt động hoàn hảo, không còn nghi ngờ gì nữa =B2+C2+D23 Tôi có thể sử dụng QUERY thay thế cho SUMIFS+ARRAYFORMULA trong Google Trang tính không?Bạn có thể nảy ra ý tưởng cố gắng sử dụng thứ gì đó đơn giản hơn, chẳng hạn như QUERY solo, như một số tài nguyên internet khác gợi ý. Chà, chúng tôi đã thử áp dụng công thức sau =B2+C2+D24 Và về mặt logic, nó phải hoạt động, nhưng nó không hoạt động vì nó trả về kết quả theo thứ tự ngẫu nhiên, ít nhất là không thân thiện với người dùng chút nào. Vì vậy, nếu bạn không có thành kiến hoặc hạn chế nào về việc sử dụng SUMIF, tốt hơn hãy xem Phương án #1. Đọc thêm về sức mạnh của chức năng QUERY trong Google Trang tính VLOOKUP và ARRAYFORMULA trong Google Trang tínhĐối với nhiều người dùng Google trang tính, thành thạo VLOOKUP là một bước ngoặt. Đó là khi họ thực sự cảm thấy thoải mái với nhiều chức năng và ứng dụng của chúng Và, nếu bạn vẫn chưa thành thạo nó, hãy mở VLOOKUP bằng cách đọc bài đăng chuyên dụng của chúng tôi Giải thích về VLOOKUP. Cách tìm kiếm dữ liệu theo chiều dọc trong bảng tính Như bạn có thể đã biết, vấn đề hạn chế chính với VLOOKUP là nó chỉ cho phép bạn tìm kiếm một giá trị duy nhất. Tuy nhiên, thế giới thực thường yêu cầu bạn sử dụng hai tiêu chí trở lên khi tra cứu dữ liệu từ cơ sở dữ liệu. Để tra cứu theo chiều dọc nhiều tiêu chí, hãy lồng VLOOKUP với ARRAYFORMULA =ARRAYFORMULA(VLOOKUP({search-key#1;search-key#2;…}, range, column-index,[sorted/not-sorted]) =B2+C2+D25 Hãy xem bảng đã quen thuộc, giả sử chúng ta muốn tìm kiếm SKU# của Mặt hàng và trả về Số tiền đã bán, Giá và Danh mục Vì chúng tôi cần hàm VLOOKUP để trả về nhiều cột, nên hãy sử dụng dấu ngoặc nhọn “ =sum(E2:E5)6” để chỉ ra các cột mà chúng tôi muốn trả về và áp dụng ARRAYFORMULA, để Google Trang tính biết chúng tôi đang làm việc với một đầu ra phạm vi, không phải một giá trị. Bảng dữ liệu của tôi nằm trong phạm vi A2. G17 và giá trị tìm được ở A13 nên công thức sẽ như sau =B2+C2+D26 Thực tế, đây là một công thức VLOOKUP thông thường, nhưng thay vì một cột đơn lẻ, chúng tôi sử dụng một mảng các cột trong dấu ngoặc nhọn. =sum(E2:E5)7 VLOOKUP và ARRAYFORMULA Google Trang tính trong một ví dụ thực tếHãy cũng xem xét một trường hợp khác, nâng cao hơn. Ví dụ: hãy xem cách người dùng Pipedrive có thể nhập dữ liệu hỗn hợp (một số trường từ thực thể này và một số trường từ thực thể khác) với sự trợ giúp của tổ hợp ARRAYFORMULA-VLOOKUP Bản thân Pipedrive là một công cụ xuất sắc nhưng vẫn không cung cấp đủ tùy chọn để tùy chỉnh báo cáo và cấu hình luồng dữ liệu. Và để có thể sử dụng sức mạnh báo cáo của bảng tính, đồng thời tránh sao chép thủ công, chúng tôi đã thiết lập một tích hợp chuyên dụng được hỗ trợ bởi Coupler. io Đặc biệt, tích hợp Pipedrive vào Google Trang tính cho phép bạn lấy dữ liệu riêng biệt từ các thực thể sau. Giao dịch, Người, Tổ chức, Hoạt động và Tệp.
Mặc dù đôi khi bạn có thể cần trích xuất dữ liệu từ các thực thể Pipedrive khác nhau và kết hợp nó thành một trang tính duy nhất. Trong trường hợp như vậy, ARRAYFORMULA kết hợp với VLOOKUP phù hợp với hóa đơn Hãy xem cách nó thực hiện công việc. Điều đầu tiên cần làm là nhập các trường
Bước 1. Thiết lập hai trình nhập PipedriveTham khảo Khớp nối. io để biết chi tiết về cách thiết lập tích hợp Pipedrive Các tham số cho trình nhập Pipedrive #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
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ínhKhi 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ínhMộ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 SheetsARRAYFORMULA 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ínhKhi 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ínhVì 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. ” Bạn có thể sử dụng công thức mảng với VLOOKUP không?Lợi ích của việc sử dụng ARRAYFORMULA là chúng ta có thể thực hiện VLOOKUP cho từng giá trị trong E2. E11 mà không cần viết công thức VLOOKUP riêng lẻ cho từng ô trong cột E . Ví dụ sau đây cho thấy cách sử dụng công thức này trong thực tế.
VLOOKUP có thể trả về mảng Google Trang tính không?Chúng tôi sử dụng ký tự mảng {} để cho biết cột nào chúng tôi muốn trả về, sau đó chuyển đổi công thức thành Công thức mảng để cho Google Trang tính biết rằng chúng tôi đang làm việc với một đầu ra phạm vi, không phải một giá trị đơn lẻ. |
