Bạn có câu hoặc cụm từ trong mỗi hàng trong một cột có thể chứa hoặc không chứa nhiều dấu ngoặc vuông. Trong các dấu ngoặc vuông này, có thể có các số. Làm thế nào để trích xuất số trong dấu ngoặc vuông trong mỗi hàng trong Google Sheets. Đó là điều mà tôi sẽ trình bày trong bài viết hướng dẫn Sheets Google này.
Công thức Mảng để Trích Xuất Số Trong Dấu Ngoặc Vuông
Nếu có nhiều giá trị trong một phạm vi như B1:B, không có nghi ngờ gì, bạn có thể chuyển đổi công thức trên thành Công thức Mảng.
=ArrayFormula(iferror(REGEXEXTRACT(B1:B, "[(.+)]")))
Bạn có thể sử dụng công thức trên để trích xuất số trong dấu ngoặc vuông trong mỗi hàng trong Sheets. Nhưng còn về việc trích xuất số trong nhiều dấu ngoặc vuông trong cùng một ô?
Không còn nghi ngờ, công thức trên sẽ thất bại! Đến đây là lý do quan trọng của bài hướng dẫn này.
Trích xuất số trong nhiều Dấu Ngoặc Vuông trong Mỗi Hàng
Dữ liệu mẫu và kết quả mong muốn:
Xem các dữ liệu mẫu trên (B2:B) với các số trong nhiều dấu ngoặc vuông trong mỗi hàng.
Kết quả được trải ra trên phạm vi C2:E. Nếu bạn muốn, bạn có thể lấy tổng các giá trị trong dấu ngoặc vuông trong mỗi hàng bằng cách sử dụng công thức MMULT sau. Trước hết, hãy cho tôi giải thích chi tiết cách lấy kết quả như trên.
Các bước để Trích Xuất Số Trong Nhiều Dấu Ngoặc Vuông
Hãy làm theo các hướng dẫn từng bước dưới đây. Tôi sẽ chỉ cho bạn từng bước để tạo ra công thức kết hợp trích xuất nhiều giá trị từ dấu ngoặc vuông.
Bước 1: Lọc ra các hàng trống ở cuối phạm vi dữ liệu nguồn.
=filter(B2:B,B2:B<>"")
Khi sử dụng công thức Lọc trên, hãy đảm bảo rằng bạn không để lại bất kỳ ô trống nào ở giữa. Có nghĩa là, theo phạm vi hiện tại B2:B, không được có ô trống nào trong phạm vi B2:B5.
Bước 2: Chia các chuỗi văn bản bằng dấu “[” làm dấu phân cách.
Ở đây, trong công thức Chia dưới đây, phạm vi (các văn bản để chia bằng dấu phân cách) là công thức Lọc ở trên.
=ArrayFormula(split(filter(B2:B,B2:B<>""),"["))
Bước 3: Trích xuất các số với Dấu Ngoặc Vuông Đóng.
Ở đây, chúng ta có thể sử dụng công thức RegexExtract ‘giống như ở đầu. Nhưng đợi đã!
Trong quá trình chia tách, tôi đã sử dụng dấu ngoặc vuông mở làm dấu phân cách. Kết quả là bị thiếu dấu ngoặc vuông mở.
Ở đầu bài hướng dẫn này, tôi đã sử dụng biểu thức chính quy “[(.+)]” để trích xuất số trong một dấu ngoặc vuông mở và đóng.
Do sự vắng mặt của dấu ngoặc vuông mở, lần này chúng ta phải sửa biểu thức chính quy này. Xem bên dưới.
Trước tiên, chúng ta có thể sử dụng phạm vi D2:F5 (vui lòng xem hình ảnh ở trên). Sau đó, chúng ta có thể thay thế phạm vi này bằng công thức trong ô D2.
=ArrayFormula(iferror(REGEXEXTRACT(D2:F5, "(.+)]")))
Ở đây, tôi thay thế D2:F5 bằng công thức liên quan mà tôi đã nhập trong ô D2 trước đó.
=ArrayFormula(iferror(REGEXEXTRACT(split(filter(B2:B,B2:B!=""),"["), "(.+)]")))
Đây là cách bạn có thể trích xuất số trong nhiều dấu ngoặc vuông trong Google Sheets. Nhưng đây không phải là công thức cuối cùng của chúng ta. Tại sao?
Xem kết quả. Các số được định dạng dưới dạng văn bản (căn trái). Điều này là do sự sử dụng của hàm RegexExtract, là văn bản.
Công Thức Cuối Cùng:
Nhân kết quả trên với số 1 để biến số thành số. Đồng thời, điều này sẽ đặt giá trị 0 trong các ô trống. Điều này là cần thiết nếu bạn muốn tổng các giá trị đã trích xuất trong mỗi hàng bằng cách sử dụng hàm MMULT.
=ArrayFormula(iferror(REGEXEXTRACT(split(filter(B2:B,B2:B!=""),"["), "(.+)]"))*1)
Cách Tính Tổng Các Số Đã Trích Xuất Trong Mỗi Hàng Trong Google Sheets
Đây là một gợi ý bổ sung. Bằng cách sử dụng MMULT, bạn có thể dễ dàng tính tổng các giá trị trên trong mỗi hàng.
Lấy công thức cuối cùng (kết quả công thức) làm Ma trận 1 trong MMULT.
Cú pháp:
MMULT(matrix1, matrix2)
Vì có 3 cột trong kết quả trên, tạo một mảng ảo gồm 3 hàng như sau để sử dụng làm Ma trận 2.
={1;1;1}
Tôi không sẽ đi vào logic. Đây là công thức MMULT đó.
=MMULT(ArrayFormula(iferror(REGEXEXTRACT(split(filter(B2:B,B2:B!=""),"["), "(.+)]"))*1),{1;1;1})
Công thức trên không linh hoạt do sử dụng mảng ảo (Ma trận 2). Thay vì sử dụng;
={1;1;1}
Chúng ta có thể sử dụng công thức ROW dưới đây. Cả hai đều tương đương.
=ArrayFormula(row(A1:A3)^0)
Ngay cả khi bạn sử dụng công thức Hàng như ma trận 2, chúng ta không thể nói rằng MMULT trên là linh hoạt. Vì ma trận 2 là một loại công thức thủ công nào đó.
Trong ma trận 2, điều chúng ta muốn là một mảng ảo. Trong đó, số hàng (mỗi hàng chứa giá trị 1) phải bằng số cột trong “Công thức cuối cùng”.
Có 3 cột trong “công thức cuối cùng”. Đó là lý do tôi đã tạo ra một cột ảo với ba hàng.
Nhưng số lượng cột trong “công thức cuối cùng” có thể thay đổi tùy thuộc vào dữ liệu trong B2:B.
Vì vậy, trong công thức Hàng ở trên, chúng ta có thể tìm cách thay thế A3 bằng A’n’. Làm thế nào để tìm ‘n’ sau đó?
Bao bọc “công thức cuối cùng” bằng hàm COLUMNS. Nó sẽ trả về số cột một cách động.
=columns(ArrayFormula(iferror(REGEXEXTRACT(split(filter(B2:B,B2:B!=""),"["), "(.+)]"))*1))
Sửa công thức Hàng như sau.
Từ này;
=ArrayFormula(row(A1:A3)^0)
đến đây.
=ArrayFormula(row(indirect("A1:A"&columns(ArrayFormula(iferror(REGEXEXTRACT(split(filter(B2:B,B2:B!=""),"["), "(.+)]"))*1))))^0)
Bạn có thể sử dụng cái này làm Ma trận 2 trong MMULT. Đó là tất cả.
