Lọc khi ghép ô trong cột trên Google Sheets

GiGiKing·Tháng ba 17, 2024·0 comments

As you may know, when trying to filter merged cells in columns on Google Sheets, we often encounter problems and fail to get the desired results. But don’t worry, in this article, I will show you the solution to this issue.

Sự cần thiết của việc lọc ghép ô trong cột trên Google Sheets

Khi lọc dữ liệu trên Google Sheets, chúng ta có hai phương pháp: sử dụng menu Data hoặc hai công thức Filter và Query. Tuy nhiên, khi sử dụng các công thức này, chúng ta chỉ nhận được hàng đầu tiên trong ô ghép, không phải toàn bộ dữ liệu.

Trường hợp này khác biệt hoàn toàn khi sử dụng menu Data để tạo bộ lọc hoặc lệnh Filter views. Khi đó, hệ thống sẽ trả về thông báo lỗi “You can’t create a filter over a range containing vertical merges.”

Vậy làm sao để lọc ô ghép theo chiều dọc trên Google Sheets một cách hiệu quả?

Giải pháp tốt nhất

Giải pháp tốt nhất là sử dụng các cột trợ giúp hoặc các cột ảo tương ứng với phương pháp lựa chọn của bạn. Dù bạn chọn phương pháp nào, các bước thực hiện gần như tương tự.

Hãy bắt đầu với một ví dụ cụ thể để lọc các ô ghép trên Google Sheets.

Tôi có một dữ liệu mẫu gồm ba cột: Mặt hàng, Khách hàng và Số lượng (đơn vị là Kg) trong các cột B, C và D tương ứng.

Filter merged cells in a column in Google Sheets

Tôi muốn lọc tất cả khách hàng yêu cầu sỏi trắng mài 20-40 mm. Chúng ta có thể thử hai công thức FILTER hoặc QUERY.

Dùng công thức FILTER:

=filter(B2:D14,B2:B14="Polished White Pebbles 20-40 mm")

Dùng công thức QUERY:

=query(B2:D14,"Select * where B='Polished White Pebbles 20-40 mm'")

Cả hai công thức này sẽ trả về hàng đầu tiên trong phạm vi, nghĩa là dữ liệu trong ô B2:D2 thay vì B2:D5 như chúng ta mong muốn.

Ví dụ về việc lọc các ô ghép theo chiều dọc trên Google Sheets

Bước đầu tiên là tạo một cột trợ giúp sử dụng các công thức dựa trên LOOKUP.

Sau đó, chúng ta có thể sử dụng cột đó trực tiếp với menu Data hoặc Filter() và Query() functions.

Cột trợ giúp và ba phương pháp

Trong ô F1, chèn công thức Lookup sau đây:

={"Mặt hàng";ArrayFormula(lookup(row(B2:B),if(len(B2:B),row(B2:B)),B2:B))}

Trước khi lọc các ô ghép trong cột B, chúng ta cần giải quyết một vấn đề với đầu ra của công thức trên.

Vấn đề là gì?

Hãy xem dữ liệu trong phạm vi được đánh dấu bằng hình tròn màu xám. Đó là chuỗi văn bản “Crushed Glass” trong cột F15:F. Chúng ta không muốn chúng.

Có ba cách để loại bỏ các giá trị không mong muốn này:

  1. Sử dụng phạm vi đóng B2:B14 thay vì B2:B trong công thức ở ô F1.
  2. Điều chỉnh ô B15 bằng cách nhấn phím cách. Điều này đồng nghĩa với việc bạn không cần thay đổi công thức Lookup ở ô F1.
  3. Thay thế phạm vi B2:B bằng indirect("B2:B14"), sau đó thay thế nó bằng indirect("B2:B"&MATCH(2,1/(C:C<>"")‌​,1)).

Lưu ý: Phần MATCH của công thức trong phương án 3 trả về số hàng của ô không trống cuối cùng trong cột C.

Nếu bạn tuân theo bước/khả năng thứ ba, các công thức ở ô F1 sẽ trở thành:

={"Mặt hàng";ArrayFormula(lookup(row(indirect("B2:B"&MATCH(2,1/(C:C<>""),1))),if(len(indirect("B2:B"&MATCH(2,1/(C:C<>""),1))),row(indirect("B2:B"&MATCH(2,1/(C:C<>"")‌​,1)))),indirect("B2:B"&MATCH(2,1/(C:C<>"")‌​,1))))}

Trong ví dụ của tôi, tôi sẽ tuân theo phương án thứ hai, nghĩa là sử dụng công thức Lookup hiện có ở ô F1.

Lọc các ô ghép theo chiều dọc trong một cột – Menu và hàm

Hãy bắt đầu với lệnh menu.

Chỉ cần lọc cột F1:F thay vì B2:F để lấy tất cả các hàng liên quan đến “Polished White Pebbles 20-40 mm”.

Cách thực hiện:

  1. Chọn cột F.
  2. Chọn Data > Create a filter.
  3. Nhấp vào mũi tên xuống trong ô F1 để xem các tùy chọn lọc.
  4. Nhấp vào “Clear”, chọn “Polished White Pebbles 20-40 mm” và nhấp OK.

Còn Query và Filter functions để lọc các ô ghép trên Google Sheets?

Nếu bạn không muốn sử dụng một cột trợ giúp, hãy sử dụng công thức tương ứng trong ô F1 bên trong các hàm.

Chúng tôi chỉ cần một phần của công thức ở ô F1.

Nếu bạn dùng Filter:

=filter(B2:D,<mark style="background-color:#7bdcb5" class="has-inline-color">lookup(row(B2:B),if(len(B2:B),row(B2:B)),B2:B)</mark>="Polished White Pebbles 20-40 mm")

Nếu bạn dùng Query:

=ArrayFormula(query({B2:D,<mark style="background-color:#7bdcb5" class="has-inline-color">lookup(row(B2:B),if(len(B2:B),row(B2:B)),B2:B)</mark>},"Select Col1,Col2,Col3 where Col4='Polished White Pebbles 20-40 mm'"))

Đó là tất cả. Cảm ơn đã theo dõi. Chúc bạn thành công!

GiGiKing

Related posts