Bạn đã biết, chúng ta có thể sử dụng hàm Index (Index function) trong Google Sheets để dịch chuyển các hàng. Trong bài hướng dẫn này, hãy tìm hiểu cách chỉ mục phạm vi đã lọc trong Google Sheets. Điều đó có nghĩa là, sử dụng Index, chúng ta sẽ dịch chuyển các hàng trong một phạm vi đã lọc đến các ô chính xác.
Điều thú vị là chỉ mục dòng lọc của kết quả công thức bộ lọc sẽ được dựa trên số tiêu chí.
Tôi hiểu rằng giải thích ý nghĩa chủ đề này rất khó khăn. Đừng lo! Tôi sẽ giải thích nó với một ví dụ. Trước đó, đây là các hàm mà chúng ta sẽ sử dụng trong công thức chỉ mục phạm vi đã lọc trong Google Sheets.
Các hàm mà chúng ta sẽ sử dụng trong công thức là Index, Filter và Countif. Ngoài ra, để làm cho công thức Filter hữu ích, tôi cũng sẽ bao gồm Regexmatch.
Hãy hiểu sự kết hợp trên và cách chỉ mục phạm vi đã lọc trong Google Sheets (dựa trên số lượng). Tôi hy vọng một số bạn sẽ thấy sự kết hợp này hữu ích trong việc sử dụng thực tế của mình.
Chỉ mục phạm vi đã lọc trong Google Sheets – Giải thích chủ đề
Chủ đề này liên quan đến việc dịch chuyển đầu ra đã lọc đến vị trí chính xác trong một phạm vi tiêu chí.
Ở đây, tôi chỉ sử dụng một danh sách đơn giản để giải thích chủ đề. Sau khi hiểu mục đích, bạn có thể sử dụng một bảng (phạm vi nhiều cột) với công thức. Tôi sẽ cố gắng bao gồm điều đó trong bài đăng này.
Dưới đây là ví dụ.
Danh sách cần lọc nằm trong A2:A6, chứa tên của một số loại trái cây như “Cam” và “Táo” với các mức đánh giá trái cây của Mỹ. Hơn nữa, phạm vi C2:C7, đó là phạm vi tiêu chí, chứa một số tên trái cây không có mức đánh giá.
Vì vậy, chúng ta có hai phạm vi (mảng) chứa tên trái cây – một với mức đánh giá trái cây (phạm vi bộ lọc) và một phạm vi khác không có mức đánh giá trái cây (tiêu chí bộ lọc).
Hãy xem xét trái cây “Cam” nằm trong các ô C2, C3 và C7 và tạm thời bỏ qua trái cây “Táo”. Trên hình ảnh, xin hãy tập trung vào kết quả công thức trong các ô tương ứng D2, D3 và D7.
Tôi hi vọng bạn có thể hiểu điều gì đang diễn ra phía sau.
Công thức chỉ mục phạm vi đã lọc trong các ô đó (D2, D3 và D7) trong Google Sheets, lấy tiêu chí từ cùng một hàng (C2, C3 và C7) để lọc A2:A6 và dịch chuyển kết quả dựa trên số lượng tiêu chí.
Công thức dịch chuyển các loại trái cây đã lọc như sau – dịch chuyển 1 hàng trong D2, 2 hàng trong D3 và 3 hàng trong D7. Vì vậy, ngay cả khi giá trị trong C2, C3 và C7 là giống nhau, công thức trả về các kết quả khác nhau.
Trường hợp tương tự xảy ra với tiêu chí khác “Táo”. Hãy xem công thức để chỉ mục phạm vi đã lọc như trên (dựa trên số lượng) trong Google Sheets.
Kết hợp chỉ mục, bộ lọc và đếm số trong Google Sheets
Tôi hy vọng bạn đã dành thời gian của riêng mình để hiểu chủ đề. Nếu bạn đã sẵn sàng tiếp tục, hãy làm theo các hướng dẫn từng bước dưới đây của tôi để viết công thức chỉ mục, bộ lọc và đếm số.
Dữ liệu và tiêu chí mẫu
Trước tiên, hãy mở một tệp Google Sheets và nhập các giá trị vào cột A và C theo ảnh chụp màn hình ở trên. Vì vậy, bạn đã hoàn thành bước 1.
Bước tiếp theo là viết công thức chỉ mục phạm vi đã lọc. Chúng ta có thể bắt đầu với Bộ lọc để sau đó chúng ta có thể sử dụng Chỉ mục để dịch chuyển các hàng.
Công thức bộ lọc (Bao gồm Regexmatch)
Tiêu chí đầu tiên nằm trong ô C2. Nhập công thức dưới đây vào ô D2.
=filter( $A$2:$A$6, regexmatch($A$2:$A$6,C2) )
Công thức lọc $A$2:$A$6 cho các hàng trong phạm vi này phần nào phù hợp với tiêu chí trong ô C2. Regexmatch trong Bộ lọc dùng cho phần phù hợp một phần.
Trong công thức, tôi đã giữ phạm vi bộ lọc như là một tham chiếu tuyệt đối và tham chiếu ô tiêu chí như là một tham chiếu tương đối. Điều này là có thể với việc sử dụng đúng ký hiệu $.
Phần trên giúp chúng ta giữ phạm vi bộ lọc giống nhau và chỉ thay đổi tiêu chí thành C3, C4… khi kéo công thức xuống.
Công thức chỉ mục phạm vi đã lọc dựa trên số lượng tiêu chí
Trên bước thứ ba này, chúng ta có thể chỉ mục phạm vi đã lọc trên trong Google Sheets.
Hàm chỉ mục cho phép chúng ta dịch chuyển các hàng và cột. Ở đây chúng ta chỉ sử dụng dịch chuyển hàng và bỏ qua dịch chuyển cột trong chỉ mục.
Hãy xem cú pháp của hàm chỉ mục trước để hiểu vị trí (thứ tự) của hai dịch chuyển đó.
INDEX(reference, [row], [column])
Khi chúng ta bỏ qua dịch chuyển cột, công thức sẽ trả về tất cả các cột trong ‘reference’. ‘Reference’ ở đây chính là kết quả của công thức bộ lọc ở trên.
Chúng ta chỉ quan tâm đến dịch chuyển hàng, phải không?
Countif làm dịch chuyển hàng chỉ mục
Trước khi tiếp tục, bạn nên thử công thức Countif dưới đây trong bất kỳ ô nào, ví dụ như ô B2 và kéo xuống vì chúng ta sẽ sử dụng nó làm dịch chuyển hàng trong Chỉ mục.
=countif($C$2:C2,C2)
Công thức trả về số lần xuất hiện liên tiếp của các loại trái cây.
Điều đó có nghĩa, cho mục “Cam”, chúng ta sẽ sử dụng 1 làm dịch chuyển hàng trong Chỉ mục ở ô D2, 2 trong ô D3 và 3 trong ô D7.
Các Countif trên chỉ dùng cho mục đích giải thích. Vì vậy, bạn có thể loại bỏ các công thức Countif trên.
Công thức để chỉ mục phạm vi đã lọc dựa trên số lượng trong Google Sheets
Bây giờ tôi sẽ kết hợp chỉ mục, bộ lọc và Countif theo công thức chung dưới đây.
=index( filter_formula, countif_formula )
Vì vậy, công thức trong ô D2 sẽ là như sau.
=index( filter($A$2:$A$6,regexmatch($A$2:$A$6,C2)), countif($C$2:C2,C2) )
Công thức đã được sao chép dán trong D3:D7.
Trong ô D6, công thức trả về lỗi #REF! vì trái cây “Táo” lặp lại lần thứ ba trong cột C. Nhưng trong bộ lọc, chỉ có hai hàng (“Táo Mỹ No. 1” và “Táo Mỹ Fancy”). Vì vậy, chỉ mục không thể dịch chuyển 3 hàng.
Để tránh lỗi và loại bỏ các ô trống, nếu có, trong C2:C7, chúng ta có thể sửa đổi công thức như sau.
=if( len(C2), iferror( index( filter($A$2:$A$6,regexmatch($A$2:$A$6,C2)), countif($C$2:C2,C2) ) ), )
Như vậy chúng ta có thể chỉ mục phạm vi đã lọc trong Google Sheets.
Mẹo (Công thức trong một danh sách hai cột)
Tôi có một danh sách hai cột chứa một cột B bổ sung với số lượng. Tôi nên thay đổi công thức như thế nào?
Thay đổi phạm vi bộ lọc $A$2:$A$6 thành $A$2:$B$6 như đã được tô sáng ở trên, hoặc nếu bạn chỉ muốn số lượng, thay đổi phạm vi bộ lọc thành $B$2:$B$6. Không có thay đổi nào trong phần chỉ mục hoặc Countif.
Đó là tất cả. Thưởng thức!
