Trong bài viết này, hãy cùng tìm hiểu cách lấy kết quả LOOKUP từ một cột động trong Google Sheets.
Một trong các mục đích của hàm LOOKUP là, trong một bảng dữ liệu, tìm kiếm xuống một cột để tìm sự xuất hiện cuối cùng của một khóa đã cho. Sau đó, lấy kết quả từ một cột cụ thể hoặc từ cột cuối cùng của bảng đó.
Dưới đây là một ví dụ:
Table #1 (Phạm vi dữ liệu A2: D9)
| Ngày | Atlat Axít | Lỏng Acrilamid | Bột Acrilamid |Tôi muốn tìm kiếm xuống sự xuất hiện cuối cùng của ngày 1-Jan-2021 trong phạm vi cột A2: A9 và trả về kết quả từ phạm vi cột đã biết, tức C2: C9 (“Lỏng Acrilamid”).
Để làm điều này, tôi có thể sử dụng công thức LOOKUP sau trong Google Sheets.
=lookup(date(2021,1,1),A2:A9,C2:C9)Kết quả: 349.25
Lưu ý: Tôi đã chỉ định ngày trong định dạng DATE(năm, tháng, ngày) theo hàm DATE.
Để lấy kết quả từ phạm vi cột cuối cùng (D2: D9), chúng ta có thể sử dụng LOOKUP sau.
=lookup(date(2021,1,1),A2:D9)Kết quả: 122.60
Trong công thức đầu tiên, tôi đã riêng biệt chỉ định phạm vi tìm kiếm (A2: A9) và phạm vi kết quả (C2: C9). Trong khi trong công thức thứ hai, tôi đã sử dụng toàn bộ bộ dữ liệu.
Cú pháp công thức #1 là LOOKUP(search_key, search_range, result_range) và cú pháp công thức #2 là LOOKUP(search_key, search_result_array).
Để lấy kết quả LOOKUP từ một cột động trong Google Sheets, chúng ta sẽ sử dụng cú pháp thứ hai.
Hãy cùng tìm hiểu về cột động trong LOOKUP.
Ý nghĩa của Kết quả LOOKUP từ một Cột Động
Cột động: – LOOKUP sẽ trả về kết quả từ một cột dựa trên một tìm kiếm khác trên tiêu đề cột.
Ví dụ, hãy xem xét kho hàng tồn kho của một số sản phẩm (bảng # 1 ở trên).
Tôi muốn tìm kiếm xuống cột A để tìm sự xuất hiện cuối cùng của ngày 1-Jan-2021 trong cột A và trả về giá trị từ cột B (“Atlat Axít”), C (“Lỏng Acrilamid”), hoặc D (“Bột Acrilamid”).
Trong đó, tôi muốn chỉ định các cột B, C hoặc D một cách động.
Dưới đây là hình vẽ minh họa (hình # 1).
Khi tôi thay đổi giá trị trong ô F4 thành “Lỏng Acrilamid”, kết quả trong ô G4 sẽ là 349.25 (giá trị từ ô C5).
Đó là ý nghĩa của việc lấy kết quả LOOKUP từ một cột động.
Hơn nữa, nếu tôi thay đổi ngày trong ô G1 thành 2-Jan-2021, tôi sẽ nhận được 300.85, tức là tồn kho của sản phẩm “Lỏng Acrilamid” vào ngày đó.
Hai Công thức để Lấy Kết quả LOOKUP từ một Cột Động trong Google Sheets
Để lấy kết quả LOOKUP từ một cột động như trên, chúng ta có thể sử dụng QUERY hoặc FILTER trong LOOKUP trong Google Sheets.
Như tôi đã đề cập ở đầu, chúng ta sẽ tuân thủ cú pháp LOOKUP(search_key, search_result_array) cho mục đích này.
Trong LOOKUP, chúng ta có thể sử dụng một biểu thức để điều khiển mảng kết quả tìm kiếm. Tôi có nghĩa là làm cho mảng kết quả tìm kiếm trở thành động.
Kết quả tìm kiếm động trong LOOKUP
Chìa khóa để tìm giá trị cuối cùng từ một cột động trong Google Sheets liên quan đến việc sử dụng công thức FILTER hoặc QUERY (biểu thức) trong mảng kết quả tìm kiếm của LOOKUP.
Logic như sau –
Thay vì chỉ định A2: D9, toàn bộ bộ dữ liệu là mảng kết quả tìm kiếm, chúng ta nên lọc các cột cần thiết để chỉ định.
Tôi có nghĩa là, chúng ta cần sử dụng A2: A9 (cột ngày) và lọc một cột khác dựa trên mục trong F4 (nhãn cột). Đó sẽ là mảng kết quả tìm kiếm để sử dụng.
Các công thức dưới đây làm điều đó.
Nếu chúng ta sử dụng bộ lọc;
={$A$2:$A,filter($B$2:$D,$B$2:$D$2=$F4)}Nếu chúng ta sử dụng truy vấn;
=query({$A$2:$D},"Select Col1,Col"&match(F4,$A$2:$D$2,0))Cả hai công thức sẽ trả về cùng một kết quả như dưới đây. Vì vậy, bạn có thể chọn bất kỳ cái nào để sử dụng làm mảng kết quả tìm kiếm trong LOOKUP.
Trong công thức này, cột ngày là cố định và cột mục (tồn kho) là động.
Nếu chúng ta thay đổi giá trị F4 thành “Bột Acrilamid”, công thức sẽ trả về giá trị từ cột đó thay vì cột “Atlat Axít”.
Cột ngày sẽ không thay đổi.
Đó là hai công thức chính để sử dụng để lấy kết quả LOOKUP từ một cột động trong Google Sheets.
Hãy để tôi chỉ bạn cách sử dụng chúng để viết các công thức cần thiết.
Công thức để Lấy Kết quả LOOKUP từ Cột Động
Bây giờ, hãy viết mã LOOKUP sử dụng bộ lọc trước.
Công thức để sử dụng trong ô G4:
=lookup($G$1,{$A$2:$A,filter($B$2:$D,$B$2:$D$2=$F4)})Đây là cách thay thế.
=lookup($G$1,query({$A$2:$D},"Select Col1,Col"&match(F4,$A$2:$D$2,0)))Trong các công thức trên, chúng ta đang điều khiển cột động từ giá trị trong ô F4. Đây là một cách tiếp cận hơi khác nhau sẽ hiệu quả hơn trong việc sử dụng thực tế.
Tìm tồn kho của Tất cả Sản phẩm
Trong ví dụ trên, thay vì thay đổi giá trị F4, chúng ta có thể sử dụng chúng như sau trong phạm vi F4: F6 và sau đó kéo công thức G4 đến G6.
Điều này cho phép chúng ta xem tồn kho của tất cả các mặt hàng vào ngày 1-Jan-2021.
Khi chúng ta muốn xem tồn kho của tất cả các mục vào một ngày khác, hãy làm như sau.
Thay đổi ngày trong ô G1 từ 1-Jan-2021 thành 2-Jan-2021 để có được tồn kho của ngày đó cụ thể.
Cách này, chúng ta có thể lấy kết quả LOOKUP từ một cột động trong Google Sheets.
Nhưng tôi đã không nhắc một điểm quan trọng rằng công thức trên chỉ đúng cho một tập dữ liệu đã được sắp xếp.
Các ngày trong cột A phải theo thứ tự thời gian để công thức trả về kết quả chính xác. Bởi vì LOOKUP chỉ dành cho một phạm vi đã được sắp xếp.
Tôi có thể chỉnh sửa và sử dụng hai công thức trên trong một tập dữ liệu chưa được sắp xếp không?
Có! Chúng ta cần chỉnh sửa một chút Query hoặc Filter được sử dụng làm LOOKUP search_result_array. Tìm các mẹo dưới đây.
Dữ liệu chưa được sắp xếp trong Mảng Kết quả Tìm kiếm
Hãy xem xét phạm vi G4: H11 trong hình 2 ở trên.
Bạn có thể thấy rằng công thức mảng kết quả tìm kiếm trong ô G4 trả về tất cả các hàng từ tập dữ liệu.
Chúng ta muốn các hàng phù hợp với ngày trong ô G1. Vì vậy, chúng ta có thể tránh vấn đề hàng không được sắp xếp này.
Chúng ta có thể sử dụng tiêu chí ngày trong Query để lọc ra các hàng không phù hợp với ngày trong ô G1.
=QUERY({$A$2:$D},"Select Col1,Col"&match(F4,$A$2:$D$2,0)&" where Col1=date '"&TEXT(&G&1,"yyyy-mm-dd")&"'")Trên sẽ là mảng kết quả tìm kiếm trong LOOKUP. Nếu bạn ưa thích sử dụng FILTER, hãy sử dụng cái sau đây.
=filter({$A$2:$A,filter($B$2:$D,$B$2:$D$2=$F4)},$A$2:$A=$G$1)Điều đó có nghĩa là chúng ta có thể sử dụng bất kỳ hai công thức dưới đây để lấy kết quả LOOKUP từ một cột động trong một tập dữ liệu được sắp xếp hoặc chưa được sắp xếp (bảng) trong Google Sheets.
Công thức # A
=lookup($G$1,QUERY({$A$2:$D},"Select Col1,Col"&match(F4,$A$2:$D$2,0)&" where Col1=date '"&TEXT($G$1,"yyyy-mm-dd")&"'"))Công thức # B
=lookup($G$1,filter({$A$2:$A,filter($B$2:$D,$B$2:$D$2=$F4)},$A$2:$A=$G$1))Để kiểm tra, làm như sau.
-
Chọn A3: D9.
-
Đi đến menu DỮ LIỆU và ấn vào “Ngẫu nhiên hóa phạm vi”.
Chỉ vậy thôi. Cảm ơn đã lưu lại, Chúc bạn vui vẻ.
- Tìm kiếm phần tử cuối cùng trong một danh sách trong Google Sheets.
- Tìm kiếm để tìm sự xuất hiện cuối cùng của Nhiều Tiêu chí trong Google Sheets.
- [Lọc theo hai chiều trong Google Sheets [Bộ lọc Thứ tự dọc và Ngang Động]].](https://crawlan.com)
- Làm thế nào để Tìm Giá Trị phù hợp Cuối cùng trong Google Sheets.
