As you may know, Google Sheets Index function allows us to offset rows. Trong bài viết này, chúng ta sẽ tìm hiểu cách chỉ mục khoảng đã lọc trong Google Sheets. Điều này có nghĩa là sử dụng Index để dịch chuyển hàng trong một khoảng đã lọc đến các ô chính xác.
Điều thú vị là chỉ mục hàng dựa trên số lượng điều kiện trong công thức đã lọc. Tuy nhiên, việc giải thích ý nghĩa của chủ đề khá khó khăn. Đừng lo! Tôi sẽ giải thích nó bằng một ví dụ. Trước khi đi vào đó, hãy cùng tìm hiểu các hàm sẽ được sử dụng trong công thức chỉ mục khoảng đã lọc trong Google Sheets.
Các hàm mà chúng ta sẽ sử dụng trong công thức này là Index, Filter và Countif. Ngoài ra, để công thức Filter hữu ích, tôi cũng sẽ bao gồm Regexmatch.
Hãy hiểu về sự kết hợp trên và cách chỉ mục khoảng đã 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 cách kết hợp này hữu ích trong việc sử dụng thực tế.
Chỉ Mục Khoảng Đã Lọc trong Google Sheets – Giải thích chủ đề
Chủ đề này liên quan đến việc dịch chuyển kết quả đã lọc đến vị trí đúng trong một khoảng điều kiện.
Ở đâ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 (khoảng nhiều cột) với công thức. Tôi cũng sẽ cố gắng bao gồm phần này trong bài viết này.
Dưới đây là ví dụ.
Danh sách cần lọc nằm trong ô A2:A6 và chứa tên một số loại trái cây, ví dụ như “Cam” và “Táo” kèm theo cấp độ trái cây ở Mỹ. Ngoài ra, khoảng C2:C7, đó là khoảng điều kiện, chứa một số tên trái cây mà không có cấp độ.
Vì vậy, chúng ta có hai khoảng (mảng) chứa tên trái cây – một khoảng với cấp độ trái cây (khoảng đã lọc) và một khoảng khác mà không có cấp độ.
Hãy cùng xem xét trái cây “Cam” nằm ở các ô C2, C3 và C7 và tạm thời bỏ qua trái cây “Táo”. Trên hình ảnh, bạn chỉ cần 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 được những gì đang xảy ra phía sau.
Công thức chỉ mục khoảng đã lọc ở những ô đó (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 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ề kết quả khác nhau.
Tương tự cũng xảy ra với tiêu chí khác “Táo”. Hãy đi vào công thức để chỉ mục khoảng đã lọc như trên (dựa trên số lượng) trong Google Sheets.
Kết hợp Index, Filter và Countif trong Google Sheets
Tôi hy vọng bạn đã dành thời gian để hiểu về chủ đề. Nếu bạn đã hoàn tất và muốn tiếp tục, hãy làm theo hướng dẫn từng bước dưới đây để viết công thức kết hợp Index, Filter và Countif.
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ác cột A và C như hình ảnh trên. Bạn đã hoàn thành bước 1.
Bước tiếp theo là viết công thức chỉ mục khoảng đã lọc. Chúng ta có thể bắt đầu với Filter để sau đó sử dụng Index để dịch chuyển các hàng.
Công thức Filter (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 này lọc $A$2:$A$6 cho các hàng trong khoảng này phù hợp một phần với tiêu chí trong ô C2. Regexmatch trong Filter là để phù hợp một phần.
Trong công thức, tôi đã giữ khoảng đã lọc là tham chiếu tuyệt đối và tham chiếu ô tiêu chí là tham chiếu tương đối. Điều này có thể thực hiện bằng cách sử dụng đúng các dấu $.
Phần trên giúp chúng ta giữ nguyên khoảng đã lọc 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 Khoảng Đã Lọc Dựa Trên Số Lượng Điều Kiện
Ở bước thứ ba này, chúng ta có thể chỉ mục khoảng đã lọc ở Google Sheets.
Hàm Index 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 Index.
Hãy xem cú pháp của hàm Index trước để hiểu vị trí (thứ tự) của hai sự dịch chuyển nêu trên.
INDEX(reference, [row], [column])
Khi chúng ta bỏ qua sự dịch chuyển cột, công thức sẽ trả về tất cả các cột trong ‘reference’. ‘Reference’ ở đây là kết quả của công thức lọc đã nêu trên.
Chúng ta chỉ quan tâm đến sự dịch chuyển hàng, đúng không?
Countif Dùng làm Sự Dịch Chuyển Hàng Trong Index
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ử dụng nó như sự dịch chuyển hàng trong Index.
=countif($C$2:C2,C2)
Công thức trả về số lượng trái cây đang tăng.
Điều đó có nghĩa, với mục “Cam”, chúng ta sẽ sử dụng 1 làm sự dịch chuyển hàng trong Index ở ô D2, 2 ở ô D3 và 3 ở ô D7.
Các công thức Countif trên chỉ để giải thích. Vì vậy, bạn có thể loại bỏ các công thức Countif đã nêu trên.
Công Thức Chỉ Mục Khoảng Đã Lọc Dựa Trên Số Lượng trong Google Sheets
Bây giờ tôi sẽ kết hợp Index, Filter 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 vào ô 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 (“Apple U.S. No. 1” và “Apple U.S. Fancy”). Vì vậy, Index không thể dịch chuyển 3 hàng.
Để tránh lỗi và loại trừ các ô trống, nếu có, trong C2:C7, chúng ta có thể sửa đổi công thức như dưới đây.
=if( len(C2), iferror( index( filter($A$2:$A$6,regexmatch($A$2:$A$6,C2)), countif($C$2:C2,C2) ) ), )
Điều này giúp chúng ta chỉ mục khoảng đã lọc trong Google Sheets.
Mẹo (Công thức trong 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 cần thay đổi công thức như thế nào?
Thay đổi khoảng lọc $A$2:$A$6 thành $A$2:$B$6 như được nhấn mỗi trên hoặc nếu bạn chỉ muốn số lượng, thay đổi khoảng lọc thành $B$2:$B$6. Không có thay đổi trong phần Index hay Countif.
Đó là tất cả. Chúc vui vẻ! Nếu bạn có bất kỳ câu hỏi nào, đừng ngần ngại để lại bình luận. Tôi rất vui được giúp bạn. Hãy đảm bảo rằng bạn đã đăng ký để nhận thông báo về các bài viết mới nhất từ Crawland.
