Trong bài viết này, chúng ta sẽ khám phá cách tạo liên kết hyperlink cho kết quả của hàm FILTER trong Google Sheets.
Giới thiệu
Hàm FILTER được sử dụng để trích xuất dữ liệu cụ thể từ một tập dữ liệu lớn. Tuy nhiên, một hạn chế của việc sử dụng hàm FILTER là không thể chỉnh sửa trực tiếp kết quả. Điều này khiến việc chỉnh sửa trở nên khó khăn khi chúng ta không biết chính xác ô nào trong dữ liệu được trích xuất bằng công thức.
Đây là lúc chúng ta cần sử dụng HYPERLINK để tạo liên kết cho kết quả của hàm FILTER. Liên kết cho phép chúng ta nhấp vào các giá trị trong kết quả của công thức FILTER và chuyển trực tiếp đến ô tương ứng trong kết quả đã lọc.
Bài viết này sẽ giải thích cách sử dụng hàm HYPERLINK với hàm FILTER, và công thức này được thiết kế để hoạt động một cách mượt mà ngay cả với kết quả 2D.
Liên kết Kết quả Công thức FILTER với Dữ liệu Nguồn: Dữ liệu Mẫu
Trong ví dụ sau, chúng ta có dữ liệu nguồn trong dải ô B2:E, đại diện cho tình trạng cung cấp của vật liệu tổ hợp.
Dữ liệu được định dạng với các cột cho Mục, Số lượng, Vị trí và Ngày. Mục tiêu của chúng ta là lọc các bản ghi trong bảng mà khớp với mục “Cát” trong cột đầu tiên.
Dòng đầu tiên của bảng, B2:E2, chứa nhãn trường. Do đó, dải lọc là B3:E, và dải tiêu chí là B3:B.
Chúng tôi đã nhập tiêu chí “cát” vào ô G2.
Để lọc các bản ghi khớp với mục “Cát”, chúng ta có thể sử dụng công thức sau trong ô H2:
=FILTER(B3:E, B3:B=G2)
Tuy nhiên, kết quả không được liên kết với dữ liệu nguồn. Mặc dù thay đổi trong dữ liệu nguồn sẽ phản ánh trong kết quả, chúng ta không thể nhấp vào kết quả và nhảy đến dữ liệu nguồn.
Làm thế nào để sử dụng hàm HYPERLINK với hàm FILTER để giải quyết vấn đề này?
Sử dụng HYPERLINK với Hàm FILTER: Công thức
Đây là công thức được sửa đổi để tạo liên kết cho kết quả của hàm lọc với dữ liệu nguồn trong Google Sheets:
=ArrayFormula(LET(
f_range, B3:E,
c_rangeA, B3:B,
cA, G2,
ft_row, B3:E3,
url, "original_URL_here",
HYPERLINK( url& REGEXREPLACE(ADDRESS(ROW(ft_row), COLUMN(ft_row), 4), "[0-9]", "")& FILTER(ROW(c_rangeA), c_rangeA=cA), FILTER(f_range, c_rangeA=cA) )
))Trong công thức:
- f_range đại diện cho dải lọc, là B3:E.
- c_rangeA đại diện cho dải tiêu chí 1, là B3:B.
- ft_row đại diện cho hàng đầu tiên trong f_range, là B3:E3.
- cA đại diện cho tiêu chí 1, là G2.
- url đại diện cho URL của ô A1 trong bảng dữ liệu nguồn, và bạn nên thay thế “original_URL_here” bằng URL thực tế.
Để lấy URL, nhấp chuột phải vào ô A1 trong bảng dữ liệu nguồn, chọn “Xem thêm hành động của ô”, sau đó nhấp vào “Lấy liên kết đến ô này” trong menu ngữ cảnh.
Thay chuỗi “original_URL_here” bằng URL vừa sao chép và loại bỏ A1 ở phần cuối của nó. Xem ví dụ dưới đây.
https://docs.google.com/spreadsheets/d/12pYMR3StdSQ6aKyww4x_bgicYKjm59Bv_n_qkyFekTQ/edit#gid=3223244&range=A1 // actual
https://docs.google.com/spreadsheets/d/12pYMR3StdSQ6aKyww4x_bgicYKjm59Bv_n_qkyFekTQ/edit#gid=3223244&range= // required
Nếu bạn muốn xem sự kết hợp của HYPERLINK và FILTER trong thực tế, hãy nhấp vào nút bên dưới để truy cập vào liên kết và sao chép bảng.
Ví dụ về Lọc Các Cột Cụ thể và Liên kết
Công thức trên là động. Trong ví dụ này, nó lọc các bản ghi khớp với mục “Cát”.
Làm thế nào để chỉ lọc “Vị trí” và “Ngày” từ bảng thay vì tất cả các cột, tập trung vào hai cột cụ thể?
Bạn cần thực hiện hai thay đổi trong công thức:
- Thay thế B3:E, là dải lọc (f_range), bằng D3:E.
- Thay thế B3:E3, hàng đầu tiên trong f_range, bằng D3:E3.
Vậy nếu muốn lọc từng cột xa nhau?
Bằng cách sử dụng HYPERLINK với hàm FILTER, chúng ta có thể lọc các cột xa nhau. Ví dụ, để lọc các cột “Mục” và “Ngày” và tạo liên kết, hãy làm theo cách tiếp cận dưới đây:
CHOOSECOLS(hyperlink_filter_formula, {column1, column2, …})
Nghĩa là lọc tất cả các bản ghi, giống như ví dụ ban đầu của chúng ta sử dụng sự kết hợp HYPERLINK và FILTER, và bọc nó bằng hàm CHOOSECOLS để chọn các cột yêu cầu trong kết quả.
Lọc và Liên kết với Nhiều Tiêu chí
Khi sử dụng hàm HYPERLINK với hàm FILTER, bạn có thể bao gồm nhiều tiêu chí, tương tự như công thức lọc thông thường.
Trong đoạn mã trên, các công thức lọc như sau (vâng, chúng ta đã sử dụng hai công thức lọc):
FILTER(ROW(c_rangeA), c_rangeA=cA)
FILTER(f_range, c_rangeA=cA)
trong đó dải tiêu chí và tiêu chí là:
- c_rangeA là tiêu chí 1
- cA là tiêu chí 1
Bạn có thể chỉ định các dải tiêu chí và tiêu chí bổ sung, ví dụ:
FILTER(ROW(c_rangeA), c_rangeA=cA, c_rangeB=cB)
FILTER(f_range, c_rangeA=cA, c_rangeB=cB)
Ngoài những thay đổi trên, bạn cũng nên chỉ định chúng trong LET như sau ở đầu công thức. Đây là phần hiện tại của nó:
LET( f_range, D3:E, c_rangeA, B3:B, cA, G2, ft_row, D3:E3, url, “url_here”,…
Sau khi chỉ định các tiêu chí và tiêu chí mới (ví dụ):
LET( f_range, D3:E, c_rangeA, B3:B, cA, G2, c_rangeB, C3:C, cB, G3, ft_row, D3:E3, url, “url_here”,…
Đó là tất cả về cách sửa đổi công thức để trả về toàn bộ hoặc một phần của bảng và cũng thêm các điều kiện bổ sung. Giờ chúng ta hãy đi vào phân tích công thức.
Phân tích công thức
Chúng ta đã sử dụng hàm LET để định nghĩa các tên cho các dải (biểu thức giá trị).
LET( f_range, D3:E, c_rangeA, B3:B, cA, G2, ft_row, D3:E3, url, "url_here",...Cú pháp:
LET(name1, value_expression1, [name2, …], [value_expression2, …], formula_expression)Hãy tiếp tục với phần biểu thức công thức, nơi chúng ta tạo liên kết cho kết quả của hàm lọc.
Công thức này về cơ bản là một hàm HYPERLINK sử dụng địa chỉ ô cụ thể để liên kết với nhãn liên kết tương ứng.
Cụ thể, địa chỉ ô cụ thể là địa chỉ của các giá trị đã được lọc và nhãn liên kết là các giá trị tương ứng.
Công thức Liên kết Hypelink:
HYPERLINK( url& REGEXREPLACE(ADDRESS(ROW(ft_row), COLUMN(ft_row), 4), "[0-9]", "")& FILTER(ROW(c_rangeA), c_rangeA=cA), FILTER(f_range, c_rangeA=cA) )Nó tuân theo cú pháp của HYPERLINK(URL, [link_label]).
Trong đó URL là:
url& REGEXREPLACE(ADDRESS(ROW(ft_row), COLUMN(ft_row), 4), "[0-9]", "")& FILTER(ROW(c_rangeA), c_rangeA=cA)Trong đó, “url” là URL được sao chép từ ô A1, với việc loại bỏ chuỗi “A1” từ phần cuối.
REGEXREPLACE(ADDRESS(ROW(ft_row), COLUMN(ft_row), 4), “[0-9]”, “”) trả về các chữ cái cột của dải để lọc.
FILTER(ROW(c_rangeA), c_rangeA=cA) là công thức FILTER đầu tiên, trả về số hàng của các hàng đã lọc.
Lưu ý: Để biết cách sử dụng từng hàm một, vui lòng tham khảo hướng dẫn về các hàm Google Sheets.
Chúng ta kết hợp tất cả các yếu tố này (URL, chữ cái cột và số hàng) trong một công thức mảng bằng cách sử dụng dấu và (&) để lấy URL của các ô kết quả.
Trong đó Nhãn Liên kết là:
FILTER(f_range, c_rangeA=cA)Đây là công thức lọc thứ hai, trả về các giá trị để sử dụng làm nhãn liên kết (công thức lọc đầu tiên chỉ trả về số hàng).
Đó là cách sử dụng hàm HYPERLINK với hàm FILTER trong Google Sheets.
Tài liệu tham khảo
Tương tự như việc sử dụng HYPERLINK với hàm FILTER, chúng ta cũng có thể sử dụng nó với VLOOKUP, INDEX-MATCH, MIN, MAX, UNIQUE, v.v. Dưới đây là các hướng dẫn chi tiết.
- Tìm giá trị và tạo liên kết đến ô được tìm thấy trong Google Sheets.
- Tạo liên kết đến ô kết quả của Vlookup trong Google Sheets.
- UNIQUE Duplicate Hyperlinks trong Google Sheets – Cùng nhãn, URL khác nhau.
- Hai cách tạo liên kết đến địa chỉ email trong Google Sheets.
- Liên kết giá trị tối đa và tối thiểu trong cột hoặc hàng trong Google Sheets.
- Liên kết đến kết quả Index-Match trong Google Sheets.
- Chèn nhiều liên kết trong một ô trong Google Sheets.
- Liên kết để nhảy đến ô ngày hiện tại trong Google Sheets.
- Nhảy đến ô cuối cùng chứa dữ liệu trong một cột trong Google Sheets (Liên kết).
- Liên kết ngày trong lịch với sự kiện trong Google Sheets.
