Chuỗi trang tính Google thành mảng

Nếu bạn làm việc với dữ liệu cần được phân tách như thế này—chẳng hạn như tách biệt họ và tên hoặc tách địa chỉ trong một cột thành các cột địa chỉ đường phố, thành phố, tiểu bang và Mã ZIP—bảng tính sẽ xử lý việc đó giúp bạn

Dưới đây là cách tách văn bản trong Excel và Google Sheets

Cách tách văn bản trong Microsoft Excel

Chúng tôi sẽ sử dụng tên cho ví dụ của chúng tôi, để giữ cho mọi thứ đơn giản. Nếu tên đã có trong bảng tính, hãy mở bảng tính trong Excel—hoặc nếu danh sách ở một nơi khác, chẳng hạn như trong tài liệu hoặc trên trang web, hãy tạo một bảng tính mới trong Excel, sau đó sao chép và dán văn bản vào bảng tính

Mẹo. Trước khi bạn bắt đầu, hãy đảm bảo có một vài cột trống ở bên phải văn bản của bạn để Excel có chỗ để tách văn bản của bạn. Nếu không, nó có thể sẽ ghi đè lên dữ liệu khác của bạn

1. Chọn cột có văn bản muốn tách

2. Nhấp vào tab Dữ liệu trong menu trên cùng và nhấp vào Văn bản thành Cột—bạn sẽ thấy tab này ở giữa thanh công cụ. Thao tác đó sẽ mở hộp thoại tách văn bản

3. Tùy chọn Phân cách phải được chọn theo mặc định—tùy chọn này chỉ định rằng văn bản bạn muốn phân tách sử dụng khoảng trắng, tab hoặc các dấu phân cách khác. Bấm tiếp

4. Bây giờ, hãy chọn những gì phân tách văn bản của bạn. Đối với danh sách tên của chúng tôi, chúng tôi sẽ chọn hộp bên cạnh Không gian. Nếu bạn có một danh sách các giá trị được phân tách bằng dấu phẩy, hãy chọn tùy chọn Dấu phẩy để thay thế. [Kiểm tra Khác nếu ký tự của bạn không được liệt kê, sau đó nhập ký tự đó vào hộp. Chẳng hạn, bạn muốn tên miền từ danh sách địa chỉ email. Bạn sẽ chọn Khác, nhập ký hiệu

   =sum[E2:E5]

   5 vào hộp và sau đó Excel có thể tách tên người dùng và tên miền khỏi địa chỉ email. ]

5. Sau đó, Excel sẽ hiển thị cho bạn bản xem trước dữ liệu đã hoàn thành trông như thế nào và sẽ hỏi bạn xem văn bản được phân tách là văn bản thuần túy hay ngày tháng—và nếu là ngày tháng, bạn có thể đặt định dạng ngày chính xác

6. Excel có thể yêu cầu bạn chọn nơi bạn muốn cột mới, trong trường hợp đó, chỉ cần nhấp vào từng cột được chỉ định trong trang tính của bạn

7. Nhấp vào Kết thúc, và bạn đã hoàn tất. Dữ liệu của bạn sẽ được chia thành các cột bạn muốn

Ghi chú. Nếu tất cả dữ liệu trong cột đầu tiên không tuân theo định dạng chuẩn—có lẽ trong đó một số tên bao gồm dữ liệu bổ sung như

=sum[E2:E5]

6 hoặc

=sum[E2:E5]

7—Excel sẽ chia dữ liệu bổ sung đó thành các cột bổ sung. Bạn có thể cần thực hiện một số thao tác dọn dẹp trừ khi tất cả dữ liệu của bạn khớp với cùng một kiểu

Thay vào đó, hãy sử dụng Apple Numbers?

sử dụng tập lệnh Automator này để tách văn bản. Hoặc nếu đang sử dụng Microsoft Word, bạn có thể chuyển đổi văn bản thành cột—chọn văn bản, sau đó bấm vào tab Chèn, sau đó bấm vào Bảng > Chuyển đổi. Sau đó, bạn có thể phân tách văn bản bằng dấu cách, dấu phẩy, tab, ký tự đặc biệt và thậm chí cả đoạn văn

Cách tách văn bản trong Google Sheets

Thậm chí còn ít bước hơn để chia văn bản trong một ô thành nhiều ô trong Google Trang tính

1. Nhập bảng tính của bạn vào Google Trang tính hoặc tạo một bảng tính mới và dán dữ liệu bạn muốn tách vào

2. Chọn văn bản hoặc cột, sau đó nhấp vào menu Dữ liệu và chọn Tách văn bản thành cột

3. Google Trang tính sẽ mở một menu nhỏ bên cạnh văn bản của bạn, nơi bạn có thể chọn phân tách bằng dấu phẩy, dấu cách, dấu chấm phẩy, dấu chấm hoặc ký tự tùy chỉnh

4. Chọn dấu phân cách mà văn bản của bạn sử dụng và Google Trang tính sẽ tự động phân tách văn bản của bạn

Bạn muốn tự động hóa quy trình này để dữ liệu được lấy từ một ứng dụng, chia nhỏ rồi gửi sang ứng dụng khác? . Tìm hiểu thêm về cách tách họ và tên khỏi các trường trong Zaps của bạn—quy trình công việc tự động mà bạn tạo bằng Zapier

Zapier là một công cụ tự động hóa không dùng mã cho phép bạn kết nối các ứng dụng của mình vào quy trình làm việc tự động, để mọi người và mọi doanh nghiệp có thể phát triển với tốc độ tăng trưởng. Tìm hiểu thêm về cách nó hoạt động

Bài viết này ban đầu được xuất bản vào tháng 9 năm 2017 và kể từ đó đã có sự đóng góp của Elena Alston

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 ẩn

ARRAYFORMULA trong Google Trang tính là gì?

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

Ví dụ về ARRAYFORMULA trên Google Trang tính

ARRAYFORMULA với các chức năng Google Trang tính khác nhau

ARRAYFORMULA trên Google Trang tính với hàm IF

SUMIF & SUMIFS với ARRAYFORMULA trong Google Trang tính

SUMIF + CÔNG THỨC Mảng trong Google Trang tính

SUMIFS + ARRAYFORMULA Google Trang tính không mở rộng

Tôi có thể sử dụng QUERY thay thế cho SUMIFS+ARRAYFORMULA trong Google Trang tính không?

VLOOKUP và ARRAYFORMULA trong Google Trang tính

VLOOKUP và ARRAYFORMULA Google Trang tính trong một ví dụ thực tế

FILTER và ARRAYFORMULA trong Google Trang tính

Cách dùng ARRAYFORMULA gộp cột trong Google Sheets

Thử thách các ô trống trong đầu ra ARRAYFORMULA của Google Trang tính

Cách xóa các ô trống thừa trong đầu ra ARRAYFORMULA Google Trang tính

Đó là bức màn?

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+D2
7
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+D2
8
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+D2
9
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. ”