Cách Lọc Tuần Hiện Tại Theo Chiều Ngang Trong Google Sheets

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

Đầu tiên, bạn phải hiểu ý nghĩa của việc lọc tuần hiện tại theo chiều ngang trong Google Sheets.

Giả sử bạn có một tập dữ liệu chứa nhiều cột và hàng tiêu đề là các ngày. Điều này có nghĩa rằng hàng đầu tiên trong tập dữ liệu của bạn sẽ là hàng tiêu đề với các ngày.

Bạn muốn lọc chỉ các cột mà hàng tiêu đề chứa ngày rơi vào tuần hiện tại. Đó là ý nghĩa của việc lọc tuần hiện tại theo chiều ngang trong Google Sheets.

Những Hàm Phù Hợp

Để đạt được mục đích của chúng ta, có hai hàm có khả năng lọc dữ liệu. Đó chính là hàm Query và Filter.

Ngoài ra, để tìm số tuần của tuần hiện tại, chúng ta cũng cần sử dụng hàm Weeknum.

Vì hàm Weeknum không phải là một phần của Query, do đó chúng ta có thể cần phải làm một số thay đổi cho việc lọc dữ liệu này.

Nhưng hàm FILTER hỗ trợ việc lọc theo chiều ngang cũng như hàm Weeknum. Vì vậy, chúng ta có thể sử dụng trực tiếp tiêu chí dựa trên Weeknum trong Filter.

Trong bài viết này, hãy tìm hiểu cách lọc các ngày rơi vào tuần hiện tại trong hàng tiêu đề của Google Sheets. Trước tiên, tôi sẽ sử dụng FILTER, sau đó chúng ta có thể đi vào QUERY.

Dữ liệu mẫu:
Data Sample

Hàm Filter để Lọc Tuần Hiện Tại Theo Chiều Ngang

Có hai bước liên quan đến việc lọc chiều ngang kiểu này trong Google Sheets.

  1. Lọc các cột chứa ngày rơi vào tuần hiện tại trong hàng tiêu đề.
  2. Kết hợp cột văn bản (ở đây là cột đầu tiên) với kết quả đã lọc.

Hãy lọc tuần hiện tại theo chiều ngang trong Google Sheets bằng công thức Filter dưới đây.
=filter(B2:AF,weeknum(B2:AF2)=weeknum(today()))

Chèn công thức Filter trên vào ô AH2 để lọc tập dữ liệu theo chiều ngang dựa trên tiêu chí Weeknum.

Cú pháp của hàm FILTER (theo công thức trên) để tham khảo nhanh:

  • range (phạm vi cần lọc): B2:AF
  • condition: weeknum(B2:AF2)=weeknum(today())

Cuối cùng, kết hợp cột văn bản đầu tiên với kết quả đã lọc. Để làm điều đó, chỉnh sửa công thức Filter ở trên như sau:
={A2:A,filter(B2:AF,weeknum(B2:AF2)=weeknum(today()))}

Filter Current Week Horizontally in Google Sheets

Điều này giúp chúng ta sử dụng hàm Filter để lọc tuần hiện tại (dữ liệu tuần hiện tại) theo chiều ngang trong Google Sheets.

Lọc Tuần Hiện Tại Theo Chiều Ngang Bằng Hàm Query

Thường, chúng ta sử dụng hàm Query để lọc theo chiều dọc. Đó là lọc một cột hoặc nhiều cột dựa trên giá trị trong các cột đó.

Nếu cột ngày là cột đầu tiên, chúng ta có thể dễ dàng sử dụng Query để lọc các hàng dựa trên các ngày trong cột đó. Nhưng không có hàm scalar Weeknum trong Query.

Điều này có nghĩa là để lọc hoặc truy vấn tuần hiện tại theo chiều ngang hoặc dọc, chúng ta cần phụ thuộc vào hàm Weeknum.

Ở đây tôi sẽ sử dụng một phương pháp tạm thời để lọc/truy vấn tuần hiện tại theo chiều ngang. Dưới đây là cách thực hiện.

Logic

Trong mệnh đề SELECT trong hàm Query, chúng ta có thể chỉ định các cột cần lọc.

Ví dụ:
=query({A2:AF},"Select Col1, Col2")

Đầu tiên, xem kết quả công thức Filter trên. Dựa trên đó, chúng ta cần lọc Cột 1 và các cột từ 21 đến 27.

Ô U2 (cột thứ 21) chứa ngày bắt đầu của tuần hiện tại và ô AA2 (cột thứ 27) chứa ngày kết thúc của tuần hiện tại.

Để truy vấn (lọc) tuần hiện tại theo chiều ngang, chúng ta phải trước tiên tìm các số cột trên theo cách đó.

Dưới đây là các bước để thực hiện.

Lưu ý:
Tôi đề xuất bạn nên sử dụng phương pháp Filter. Phương pháp Query này chỉ dùng cho mục đích giáo dục vì nó khá phức tạp.

Các bước:

Hai Công thức tìm các số cột

Match_Formula_1

  1. Chèn công thức MATCH dưới đây vào ô AH1 để trả về số cột bắt đầu của tuần hiện tại. Công thức sẽ trả về 21.
    =ArrayFormula(match(weeknum(today()),weeknum(A2:AF2),0))

Match_Formula_2
2. Chèn công thức MATCH dưới đây vào ô AI1 để trả về số cột kết thúc của tuần hiện tại. Công thức sẽ trả về 27.
=ArrayFormula(match(weeknum(today()),weeknum(A2:AF2),1))

Cả hai công thức Match giống nhau ngoại trừ đối số cuối cùng.

Bạn có thể giải thích công thức cho chúng tôi được không?

Dĩ nhiên! Đây là công thức.

Cú pháp của hàm MATCH cho sự tham khảo nhanh:
MATCH(search_key, range, search_type)

Đối số cuối cùng trong công thức, search_type, 0, xử lý phạm vi như là không được sắp xếp (ngay cả khi nó là một phạm vi được sắp xếp). Vì vậy nó sẽ trả về giá trị khớp chính xác của giá trị (Weeknum) được tìm thấy ở đầu phạm vi (kết quả là 21).

Nếu nó là 1 (search_type), công thức xử lý phạm vi như là một phạm vi đã được sắp xếp và sẽ trả về giá trị lớn nhất nhỏ hơn hoặc bằng search_key (Weeknum) được tìm thấy. Vậy nên kết quả là kết quả cuối cùng (kết quả là 27).

Lưu ý:
Hàng đầu tiên trong tập dữ liệu chứa các ngày phải được sắp xếp theo thứ tự tăng dần. Nếu không, các công thức Match trên sẽ không hoạt động chính xác.

Mệnh đề truy vấn để lọc tuần hiện tại theo chiều ngang

Bây giờ chúng ta cần viết công thức như sau để lọc tuần hiện tại theo chiều ngang trong Google Sheets.
=query({A2:AF},"Select Col1, <strong>Col21, Col22, Col23, Col24, Col25, Col26, Col27</strong>",1)

Để làm cho phần công thức in đậm là động, chúng ta có thể sử dụng các công thức trùng khớp ở trên như sau.

Các bước để làm theo:

Hãy viết một công thức Sequence để thay thế phần Col21, Col22, Col23, Col24, Col25, Col26, Col27 trong công thức trên.
=sequence(AI1-AH1+1,1,AH1)

Cú pháp của hàm SEQUENCE cho sự tham khảo nhanh:
SEQUENCE(rows, columns, start)

  • rows (số hàng để trả về): AI1-AH1+1 (nghĩa là match_formula_2-match_formula_1+1)
  • columns (số cột để trả về) – 1
  • start (chuỗi bắt đầu từ) – AH1 (match_formula_1)

Để bỏ qua việc sử dụng các ô hỗ trợ AH1 và AI1, chúng ta có thể viết lại công thức trên như sau.
=sequence( ArrayFormula(match(weeknum(today()),weeknum(A2:AF2),1))- ArrayFormula(match(weeknum(today()),weeknum(A2:AF2),0))+1, 1, ArrayFormula(match(weeknum(today()),weeknum(A2:AF2),0)) )

Query to Filter Current Week Horizontally - Dynamic Select

Chúng ta cần sửa công thức trên một chút để làm nó trở thành phần văn bản của mệnh đề truy vấn Col21, Col22, Col23, Col24, Col25, Col26, Col27.

Dưới đây là công thức được sửa đổi.
=textjoin( ",",true, ArrayFormula( "Col"&amp;sequence( ArrayFormula(match(weeknum(today()),weeknum(A2:AF2),1))- ArrayFormula(match(weeknum(today()),weeknum(A2:AF2),0))+1, 1, ArrayFormula(match(weeknum(today()),weeknum(A2:AF2),0)) ) ) )

Chúng ta chỉ cần một ArrayFormula duy nhất trong công thức trên. Vì vậy, chúng ta có thể rút gọn công thức đó như sau và gọi nó là dynamic_select_formula.
Dynamic_Select_Formula
=ArrayFormula( textjoin( ",",true, "Col"&amp;sequence( ArrayFormula(match(weeknum(today()),weeknum(A2:AF2),1))- ArrayFormula(match(weeknum(today()),weeknum(A2:AF2),0))+1, 1, ArrayFormula(match(weeknum(today()),weeknum(A2:AF2),0)) ) ) )

Dưới đây là công thức Query không động để lọc dữ liệu tuần hiện tại theo chiều ngang trong Google Sheets.
=query({A2:AF},"Select Col1, <strong>Col21, Col22, Col23, Col24, Col25, Col26, Col27</strong>",1)

Chỉ việc xóa phần công thức “Col21, Col22, Col23, Col24, Col25, Col26, Col27”,1).

Vậy công thức sẽ trông như sau.
=query({A2:AF},"Select Col1,

Thay đổi như sau.
=query({A2:AF},"Select Col1,"&amp;

Sau đó, sao chép dán công thức dynamic_select_formula ở phần cuối của công thức trên.
=query({A2:AF},"Select Col1,"&amp;ArrayFormula(textjoin(",",true,"Col"&amp;sequence(match(weeknum(today()),weeknum(A2:AF2),1)-match(weeknum(today()),weeknum(A2:AF2),0)+1,1,match(weeknum(today()),weeknum(A2:AF2),0)))))

Đó là tất cả. Đó là hai phương pháp để lọc tuần hiện tại theo chiều ngang trong Google Sheets.

Hãy trải nghiệm và tận hưởng!

GiGiKing

Related posts