Trong Google Sheets, XLOOKUP và chế độ khớp 1 là một phần quan trọng của công thức để điền các ô trống với văn bản ngay phía dưới nó. Công thức này có những đặc điểm sau đây:
Đây là một công thức mảng được đặt trong ô trên cùng và kéo xuống dưới.
Công thức sẽ tự động lấp đầy bất kỳ giá trị mới (trong tương lai) ở cuối hoặc đầu của phạm vi.
Kết quả của công thức không được hiển thị trong cột nguồn, mà được hiển thị trong một phạm vi cột mới.
Công thức này hoạt động trên cột có kiểu dữ liệu là số, ngày tháng, văn bản hoặc kiểu dữ liệu kết hợp. Trong tiêu đề, tôi chỉ sử dụng thuật ngữ “văn bản” cho sự minh bạch.
Vì sao chúng ta muốn điền các ô trống với văn bản từ các ô bên dưới?
Nếu một giá trị lặp lại trong một cột, chúng ta có thể để trống nó để tăng tốc độ nhập dữ liệu.
Ví dụ, chúng ta đã nhập tên của hai nhân viên, “Ben” trong ô A2 và “Lisa” trong ô A11.
Nếu phạm vi là A2:A20, các ô trống sẽ là A3:A10 và A12:A20.
Để điền A3:A10 với “Ben” và A12:A20 với “Lisa”, chúng ta có thể sử dụng công thức của tôi để điền các ô trống với các giá trị ở phía trên.
Nếu chúng ta nhập “Ben” trong ô A10 và “Lisa” trong ô A20, các ô trống sẽ là A2:A9 và A11:A19.
Ở đây, chúng ta có thể điền các ô này với tên “Ben” và “Lisa” tương ứng.
Trong trường hợp này, chúng ta có thể cần điền các ô trống với văn bản từ các ô bên dưới.
Hãy đi vào công thức và sau đó giải thích.
Điền các ô trống với văn bản, số hoặc ngày ngay phía dưới chúng
Ô D1 trong ví dụ trên chứa công thức sau đây.
=ArrayFormula(xlookup(row(A1:A),row(A1:A)/if(A1:A<>"",1,0),A1:A,"",1,1))
Hãy kiểm tra bằng cách thêm giá trị ở cuối và đầu của phạm vi.
Thêm một ô dưới A1 và để trống hoặc nhập bất kỳ giá trị nào. Công thức trên sẽ xem xét ô đó khi điền văn bản từ dưới lên.
Bạn có thể thêm các giá trị mới sau A20. Công thức cũng sẽ xem xét chúng.
Vì vậy chúng ta có thể gọi công thức điền các ô trống với văn bản từ phía dưới là linh hoạt.
Làm thế nào để công thức này hoạt động?
Giải thích công thức
Tôi đã sử dụng một công thức mảng XLOOKUP.
Cú pháp: XLOOKUP(search_key, lookup_range, result_range, [missing_value], [match_mode], [search_mode])
Hãy trích xuất từng tham số từ công thức XLOOKUP và đây là chúng.
search_key: row(A1:A)
Lưu ý: Đòi hỏi hỗ trợ mảng. Vì vậy, chúng tôi đã bọc XLOOKUP bằng chức năng ArrayFormula.
Khóa tìm kiếm là các số hàng từ 1 đến n (hãy cuộn xuống dưới và xem ảnh chụp màn hình).
lookup_range: row(A1:A)/if(A1:A<>"",1,0)
Ở đây cũng áp dụng lưu ý ở trên!
Nó trả về các số hàng tương ứng với các ô không trống trong cột A và #DIV/0! trong tất cả các ô trống (hãy cuộn xuống dưới và xem ảnh chụp màn hình).
result_range: A1:A
missing_value: ""
match_mode: 1
Match mode 1 dùng cho việc khớp chính xác hoặc giá trị tiếp theo lớn hơn search_key.
search_mode: 1
Nó có nghĩa là tìm kiếm lookup_range từ mục nhập đầu tiên đến mục nhập cuối cùng.
Logic đằng sau việc điền các ô trống với văn bản từ các ô bên dưới
Chúng tôi đã đến phần quan trọng của công thức, điền các ô trống với văn bản từ các ô bên dưới.
Bạn có thể xem ảnh chụp màn hình này.
XLOOKUP tìm kiếm các số hàng (C1:C) trong D1:D. Ở những nơi khớp, nó trả về tên từ cột A.
Một số khóa tìm kiếm trong C1:C không có khớp trong D1:D. Ví dụ, ô C4 chứa # 4. Nó không có sẵn trong D1:D.
Giá trị tiếp theo lớn hơn # 4 là 20 trong D20. Vì vậy, công thức trả về tên “Lisa” từ result_range (A1:A).
Kết quả là công thức điền các ô trống với văn bản từ các ô bên dưới.
