Nếu bạn đang sử dụng Google Sheets và muốn kết hợp tên tiêu đề với giá trị của các ô dưới chúng, thì với sự trợ giúp của các công thức Filter và Textjoin, điều đó trở nên dễ dàng. Tuy nhiên, đây sẽ không phải là một công thức mảng (kéo và thả).
Với một công thức mảng dựa trên hai công thức chính: Query và IF, chúng ta có thể đạt được cùng một loại kết quả kết hợp mảng trong Google Sheets.
Hãy bắt đầu bằng cách giải thích vấn đề chi tiết hơn. Chúng ta có thể dựa vào phạm vi bảng dưới đây để mô tả vấn đề:
Bảng dữ liệu của tôi nằm trong phạm vi A1:D và trong đó có một số ô chứa giá trị và một số ô thì không.
Tôi chỉ muốn nhận được kết quả dựa trên dữ liệu trong cột F1:F. Điều đó có nghĩa là tôi muốn được kết hợp tên tiêu đề với các ô không trống phía dưới chúng.
Nếu bất kỳ ô nào trống, tôi không muốn có tên tiêu đề tương ứng trong đầu ra.
Tôi có thể làm rõ hơn.
Vì mục đích giải thích, chúng ta có thể xem xét các tên tiêu đề trong A1:D1 và các giá trị trong A2:D2. Hãy quên các hàng khác (A3:D) trong thời gian này.
Nếu chúng ta kết hợp các tên tiêu đề với các giá trị của các ô dưới chúng trong A2:D2, tôi muốn nhận được “100 Quả xoài, 25 Quả chuối” thay vì “100 Quả xoài, Quả cam, 25 Quả chuối, Quả táo”.
Tôi hy vọng bạn hiểu sự khác biệt này.
Công thức để kết hợp Tên tiêu đề với Giá trị của các ô dưới chúng
Làm thế nào để kết hợp tên tiêu đề với các giá trị của các ô dưới chúng trong Google Sheets?
Hãy để tôi giải thích.
Như tôi đã đề cập, tôi có một công thức mảng và một công thức không phải mảng. Bạn có thể chọn một trong những công thức tùy thuộc vào số lượng dữ liệu của bạn.
Vì nó dễ giải thích công thức không phải mảng, tôi sẽ bắt đầu với công thức đó.
Nếu bạn đã biết cách sử dụng công thức không phải mảng để kết hợp tên tiêu đề với các ô không trống phía dưới chúng, thì bạn có thể bỏ qua phần công thức không phải mảng sau đây.
Công thức không phải mảng sử dụng Filter và Textjoin
Trong ô F2, nhập công thức không phải mảng sau đây:
=ifna( textjoin( ", ", true, filter(A2:D2&" "&$A$1:$D$1,A2:D2>0) ) )Sau đó, sao chép và dán xuống (kéo biểu tượng hình vuông màu xanh lá cây ở cạnh dưới) như được hiển thị dưới đây:
Trong công thức này, bạn nên hiểu về công thức Filter trước.
Phần đầu tiên của công thức Filter (phạm vi lọc) là A2:D2&” “&$A$1:$D$1.
Nó sẽ trả về kết quả dưới đây:
![]()
Phía trên là ‘phạm vi’ trong Filter.
Lưu ý: Nếu bạn thử công thức trên trong bất kỳ ô nào, chẳng hạn như =A2:D2&" "&$A$1:$D$1, nó sẽ trả về lỗi #VALUE! Vì khi sử dụng độc lập (ngoài việc sử dụng Filter), dấu & với các phạm vi/mảng yêu cầu sử dụng ArrayFormula như =ArrayFormula(A2:D2&" "&$A$1:$D$1).
Cú pháp của Filter cho tham khảo nhanh của bạn:
FILTER(range, condition1, [condition2, ...])
Filter sẽ lọc ‘phạm vi’ nếu các giá trị trong A2:D2 lớn hơn 0. Vì vậy, kết quả sẽ là mục đầu và mục cuối cùng từ ‘phạm vi’.
Textjoin kết hợp hai giá trị này và đặt dấu phẩy làm dấu phân cách.
IFNA trong công thức này được sử dụng để trả về trắng (trong quá trình kéo xuống) nếu bất kỳ hàng nào hoàn toàn trống.
Đó là tất cả về công thức không phải mảng để kết hợp tên tiêu đề với các ô không trống phía dưới chúng trong Google Sheets.
Công thức mảng sử dụng IF và Query như các hàm chính
Công thức:
=ArrayFormula( {"Total"; REGEXREPLACE( trim( transpose( query( transpose(if(A2:D<>"",A2:D&" "&A1:D1&",",)),, 9^9 ) ) ), "(.)z","" ) } )Chèn đơn giản công thức này vào ô F1 (nếu F2:F trống, khác trường hợp, hãy trống ô F2:F trước tiên) để bao quát phạm vi A1:D. Vì vậy chúng ta có thể gọi nó là một công thức mảng. Không cần phải kéo và thả biểu tượng hình vuông.
Làm thế nào công thức mảng này kết hợp các tên tiêu đề với các giá trị của các ô (phạm vi) phía dưới chúng trong Google Sheets?
Hãy xem giải thích công thức dưới đây.
Phần_1_Công thức
Có hai phần chính trong công thức. Ở đây tôi bắt đầu với phần công thức IF.
Vui lòng lưu ý rằng, khác với công thức chính, bạn có thể thấy công thức ArrayFormula được sử dụng ở đây. Nó là bắt buộc khi sử dụng độc lập.
=ArrayFormula(if(A2:D<>"",A2:D&" "&A1:D1&",",))Công thức IF kết hợp các tên tiêu đề với các giá trị phía dưới chúng. Nhưng các giá trị được đặt trong nhiều cột, không phải là một cột duy nhất như yêu cầu của chúng ta.
Chúng ta muốn các giá trị được kết hợp tương ứng với A2:D2 trong F2, A3:D3 trong F3, vv Phần thứ hai của công thức sẽ xử lý điều đó.
Phần_2_Công thức
Chúng ta có thể sử dụng Query để kết hợp các cột trong Google Sheets.
Đối với điều đó, chỉ Mở rộng các kết quả IF trên, và Query nó và lại Mở rộng nó. Dưới đây là Công thức Chung.
=transpose(query(transpose(Part_1_Formula),,9^9))Dưới đây là Công thức Phần_2_Công thức theo công thức chung trên và kết quả của nó.
=transpose(query(transpose(ArrayFormula(if(A2:D<>"",A2:D&" "&A1:D1&",",))),,9^9))Công thức trên là đủ để kết hợp tên tiêu đề với các giá trị của các ô dưới chúng.
Tuy nhiên, kết quả không hoàn hảo vì nó chứa các khoảng trắng thừa và các dấu phân cách bổ sung ở cuối.
Trong công thức cuối cùng, TRIM loại bỏ các khoảng trắng thừa, trong khi Regex loại bỏ các dấu phân cách bổ sung từ cuối.
Ngoài ra, ArrayFormula bên trong đã được di chuyển ra phía ngoài.
Đó là tất cả. Chúc bạn vui vẻ!
Sample_Sheet_121120
Nguồn: Crawlan.com
