Tạo danh sách từ nhiều cột hộp kiểm được chọn trong Google Sheets

GiGiKing·Tháng tư 1, 2024·0 comments

Với sự trợ giúp của hàm FILTER, chúng ta có thể tạo danh sách từ một cột hộp kiểm duy nhất trong Google Sheets. Khi có nhiều cột với các hộp kiểm (checkboxes), chúng ta có thể sao chép công thức cho mỗi cột. Nhưng có phương pháp thay thế nào có thể xử lý tất cả các cột hộp kiểm một lúc không? Đúng vậy! Mặc dù sử dụng nhiều công thức FILTER là cách dễ nhất, chúng ta có thể viết một công thức linh hoạt để tạo danh sách từ nhiều cột hộp kiểm được chọn trong Google Sheets. Dưới đây là một ví dụ.

Tôi có một danh sách các loại rau (mục) trong cột đầu tiên và lựa chọn của khách hàng ở ba cột tiếp theo. Vậy làm thế nào để tạo danh sách các mục đã được khách hàng chọn?

Tạo danh sách từ các hộp kiểm đã chọn trong Google Sheets (Phiên bản không linh hoạt)

Trong ô F2, nhập công thức FILTER sau đây:

= {B2; filter ($ A $ 3: $ A $ 7, B3: B7 = true)}

Sao chép công thức này vào ô G2 và H2. Công thức này lọc các loại rau trong A3: A7 nếu được kiểm tra (được chọn) bởi khách hàng trong B3: B7. Phạm vi tham chiếu đến danh sách các loại rau là tuyệt đối. Đó là $ A $ 3: $ A $ 7, không chỉ là A3: A7. Vì vậy, khi bạn sao chép công thức qua các ô khác nhau, nó sẽ không thay đổi tham chiếu cột. Nhưng điều này không xảy ra với phạm vi hộp kiểm B3: B7. Phạm vi này là tương đối, vì vậy khi bạn sao chép công thức qua các ô khác nhau, nó sẽ thay đổi cột.

Dấu ngoặc nhọn giúp thêm tiêu đề của mỗi cột hộp kiểm vào đầu kết quả đầu ra. Đây là cách đơn giản nhất để tạo danh sách từ các cột hộp kiểm duy nhất / nhiều cột đã kiểm tra trong Google Sheets.

Các giải pháp dưới đây dành cho những người không muốn sao chép công thức qua các ô khác nhau. Có hai phương pháp: một sử dụng các hàm thường (cũ) và một sử dụng giải pháp Lambda (mới).

Tôi ưa thích phương pháp sau vì nó đơn giản hơn so với phương pháp trước đó.

Tạo danh sách linh hoạt từ nhiều cột hộp kiểm đã chọn (Phương pháp cũ)

Ở đây, chúng tôi sẽ tuân theo phương pháp “unpivot, running count và pivot max” (tổng hợp chuỗi).

Công thức tạo danh sách linh hoạt từ nhiều cột hộp kiểm đã chọn không phức tạp nhưng nếu tôi chỉ đưa ra công thức mà không giải thích, bạn không thể hiểu đúng nó. Vì vậy, hãy để tôi áp dụng phương pháp từng bước để viết mã / công thức cho bạn.

Có tổng cộng ba bước. Bạn sẽ nhận được mỗi phần mã trong từng bước và chúng tôi sẽ kết hợp chúng.

1. Unpivot danh sách

Công thức:

= ArrayFormula (query (split (flatten (B2: D2 & "|" & nếu (B3: D = true, A3: A7,)),"|"), "Chọn * nơi Col2 không rỗng"))

Công thức trên sẽ tạo ra một danh sách UNPIVOT của các hộp kiểm đã kiểm chứng. Chúng tôi đã sử dụng một câu lệnh logic IF, tức là nếu (B3: D = true, A3: A7,), trong FLATTEN để chỉ tạo hiệu ứng unpivot cho các hộp kiểm đã kiểm chứng (giá trị TRUE).

2. Running Count của giá trị TRUE

Ở bước này, chúng ta nên tạo một cột ảo (hỗ trợ).

Nội dung trong cột đó sẽ là đếm chạy của các khách hàng trong phạm vi cột F2: F9.

Chúng tôi sẽ kết hợp nó với đầu ra từ Bước 1 theo cú pháp sau:

{running_count_of_true_values, unpivoted_list}

Tương đương với {step_2, step_1}.

Dễ dàng tạo một cột hỗ trợ vật lý với Running Count của giá trị TRUE (hộp kiểm đã chọn), như sau:

Công thức dưới đây sẽ chịu trách nhiệm về điều đó trong ô E2.

Cột hỗ trợ vật lý

= ArrayFormula (countifs (row (F2: F9), "<=" & hàng (F2: F9), F2: F9, F2: F9))

Làm thế nào để chuyển nó thành một cột hỗ trợ ảo?

Cột hỗ trợ ảo

Cột hỗ trợ ảo sẽ làm cho công thức tạo danh sách động từ hộp kiểm đã chọn có vẻ phức tạp hơn một chút.

Điều đó xảy ra vì chúng tôi sẽ thay thế các tham chiếu phạm vi trực tiếp bằng các công thức dài tương đương.

Chúng tôi nên thay thế các phần tham chiếu / công thức sau đây.

  1. row (F2: F9) - Nó xuất hiện hai lần trong công thức.

Công thức trên trả về số hàng của phạm vi F2: F9.

Số hàng trong F2: F9 bằng số lượng hộp kiểm đã kiểm chứng trong B3: D7.

Vì vậy, chúng tôi có thể thay thế công thức trên bằng sequence (countif (B3: D7, true)).

Vui lòng thay thế cả hai lần xuất hiện của các phần công thức.

  1. F2: F9 - Nó cũng xuất hiện hai lần trong công thức.

Để lấy giá trị trong F2: F9, chúng tôi có thể trích xuất cột đầu tiên của kết quả công thức bước 1.

Để làm điều đó, chỉ cần thay thế Select * bằng Select Col1 trong công thức bước 1. Đây là cách làm.

ArrayFormula (query (split (flatten (B2: D2 & "|" & if (B3: D = true, A3: A7,)),"|"),"Chọn Col1 nơi Col2 không rỗng")

Chú ý: Bạn cứ thoải mái xóa ArrayFormula và dấu ngoặc đóng cuối cùng.

Đây là công thức Bước 2 sau các thay đổi đã nêu.

= ArrayFormula (countifs (sequence (countif (B3: D7, true)), "<=" & sequence (countif (B3: D7, true)), query (split (flatten (B2: D2 & "|" & if (B3: D = true, A3: A7,)),"|"),"Chọn Col1 nơi Col2 không rỗng"), query (split (flatten (B2: D2 & "|" & if (B3: D = true, A3: A7,)),"|"),"Chọn Col1 nơi Col2 không rỗng")))

Bây giờ chúng ta hãy biến nó thành một cột hỗ trợ ảo.

Đối với điều đó, kết hợp công thức trên với công thức Bước 1 theo cú pháp {step_2_formula, step_1_formula}.

3. Pivot Max để tạo danh sách từ nhiều cột hộp kiểm đã chọn

Trong bước cuối cùng dưới đây, chúng ta sẽ sử dụng đầu ra từ Bước 2 như ‘dữ liệu’ trong công thức QUERY.

Tóm lại, để tạo danh sách động từ nhiều cột hộp kiểm đã chọn, chúng ta sẽ làm ba điều.

  1. Unpivot dữ liệu nguồn (chúng tôi sẽ loại bỏ các hộp kiểm không được chọn).
  2. Thêm một cột hỗ trợ ảo chạy đếm vào ‘dữ liệu’ đã unpivot.
  3. Pivot ‘dữ liệu’ đã unpivot trở lại.

Bây giờ chỉ còn bước thứ ba. Ở đây:

Chúng tôi sẽ tổng hợp các chuỗi (rau trong cột 3) trong dữ liệu unpivot bằng cách sử dụng hàm Max.

Phương pháp tổng hợp sẽ dựa trên việc gom nhóm các giá trị cột 1 (đếm chạy).

Chúng tôi sẽ pivot các giá trị cột 2 là khách hàng.

Cú pháp: Query(data, “Chọn Col1, max (Col3) nhóm theo Col1 pivot Col2”)

Công thức (nên nhập vào ô F2):

=query({ArrayFormula(countifs(sequence(countif(B3:D7,true)),"<="&sequence(countif(B3:D7,true)),query(split(flatten(B2:D2&"|"&if(B3:D=true,A3:A7,)),"|"),"Chọn Col1 nơi Col2 không rỗng"),query(split(flatten(B2:D2&"|"&if(B3:D=true,A3:A7,)),"|"),"Chọn Col1 nơi Col2 không rỗng"))),ArrayFormula(query(split(flatten(B2:D2&"|"&if(B3:D=true,A3:A7,)),"|"),"Chọn * nơi Col2 không rỗng"))},"Chọn Col1, max (Col3) nhóm theo Col1 pivot Col2")

Tạo danh sách động từ nhiều cột hộp kiểm đã chọn (Phương pháp mới)

Đây là công thức hiện đại sử dụng hàm BYCOL Lambda helper.

Bạn có thể thay thế công thức phức tạp trên ô F2 bằng công thức dưới đây.

= {B2:D2; ArrayFormula (ifna(transpose(split(transpose(bycol(B3:D7,lambda(c, join("|",filter(A3:A7,c)))))), "|")))}
Cho phép tôi giải thích từng bước cho bạn.

  1. Chúng ta có thể sử dụng =filter(A3:A7,B3:B7) hoặc =filter(A3:A7,B3:B7=TRUE) để nhận được các mục đã chọn bởi “Khách hàng 1” (B3:B7).

  2. Chúng ta có thể sử dụng BYCOL để nhận tất cả các khách hàng như sau =bycol(B3:D7,lambda(c, join(“|”,filter(A3:A7,c)))).

  3. Transpose> Split> Transpose đầu ra của bước 2.

  4. Thêm hàng tiêu đề (B2:D2) lên đầu thông qua Dấu ngoặc nhọn.

Đó là tất cả về cách tạo danh sách (động) từ các hộp kiểm đã chọn trong Google Sheets. Cảm ơn bạn đã ở lại. Thưởng thức nhé!

Example Sheet 11121

Tài liệu tham khảo:

GiGiKing

Related posts