Google Sheets không cung cấp lệnh trình đơn để điền các ô trống bằng giá trị từ ô phía trên trong một cột. Vì vậy, nếu bạn muốn thực hiện một tác vụ như vậy, bạn phải dựa vào một công thức thông thường trong Google Sheets.
Với một công thức không-array, việc điền các ô trống trong một cột với giá trị ở phía trên rất đơn giản. Điều đó không có nghĩa là không có Array Formula cho việc này.
Hãy để tôi chia sẻ công thức không-array với bạn trước để bạn có thể hiểu về những gì sẽ xảy ra trong bài hướng dẫn này.
Ở đây, tôi đã sử dụng công thức sau trong ô C2 và sao chép xuống.
=if(len(B2),B2,C1)
Công thức này lặp lại các giá trị trong các ô trống từ các ô không trống phía trên.
Bất kể kiểu dữ liệu, công thức phải có thể sao chép giá trị trong ô phía trên và điền các ô trống xuống.
Vậy còn công thức mảng để điền các ô trống với giá trị từ ô không trống phía trên?
Chúng ta có thể tuân theo hai phương pháp chính.
- Sử dụng bất kỳ một trong các hàm tra cứu.
- Sử dụng hàm SCAN Lambda (phương pháp mới).
Chúng ta sẽ bắt đầu với một hàm tra cứu, nhưng phương pháp sau có vẻ đơn giản hơn để thực hiện.
Tự động điền các ô trống với giá trị từ phía trên trong Google Sheets – LOOKUP
Như bạn có thể biết, công thức LOOKUP nhận các đối số là search_key(s), search_range và result_range.
=LOOKUP(search_key, search_range|search_result_array, [result_range])
Chúng ta chỉ có các giá trị trong cột B để sử dụng. Điều đó có thể được sử dụng như là result_range.
Hãy chỉnh sửa các giá trị hiện có trong cột B để sử dụng chúng trong Lookup như là các search_keys và search_range (xem cú pháp ở trên).
Dữ liệu mẫu (sẽ sử dụng như là result_range trong công thức Lookup):
Hãy đưa bạn qua các bước để viết một công thức mảng Lookup để điền các ô trống với giá trị từ ô phía trên.
Các bước cần làm
- Các search_keys ảo:
Dữ liệu mẫu của tôi bắt đầu từ hàng số 2. Vì vậy, hãy sử dụng công thức ROW sau đây để tạo ra các số hàng từ hàng số 2 đến hàng cuối cùng trong Sheet.
=ArrayFormula(row(A2:A))
Những con số này sẽ đóng vai trò như các search_keys trong Lookup.
Tạm thời, hãy nhập công thức trên vào ô C2.
- Các search_range ảo:
Trong bước này, chúng ta có thể tạo ra một cột ảo chứa các số hàng của các giá trị trong cột B.
=ArrayFormula(row(A2:A)/if(B2:B<>"",TRUE,FALSE))
Tạm thời, hãy nhập công thức này vào ô D2.
Hãy nhớ rằng công thức trước của chúng ta nằm ở ô C2. Chúng ta sẽ “kết hợp” các công thức này trong bước tiếp theo (cuối cùng).
- Công thức mảng điền các ô trống với giá trị phía trên:
Trước tiên, hãy xem công thức thông thường.
=arrayformula(lookup(step # 1 formula,step # 2 formula,B2:B))
Công thức mảng để điền các ô trống với giá trị từ ô phía trên sẽ là như sau.
=ArrayFormula(lookup(ArrayFormula(row(A2:A)),ArrayFormula(row(A2:A)/if(B2:B<>"",TRUE,FALSE)),B2:B))
Bạn có thể rút ngắn công thức này vì không cần sử dụng nhiều hàm ArrayFormula trong một công thức kết hợp.
=ArrayFormula(lookup(row(A2:A),row(A2:A)/if(B2:B<>"",TRUE,FALSE),B2:B))
Công thức này còn một vấn đề nữa. Kết quả sẽ là số hàng vô hạn!
Mẹo bổ sung
Làm thế nào để rút ngắn công thức mở rộng? Tôi muốn công thức mở rộng cho đến ô không trống cuối cùng.
Dưới đây là công thức để tìm ô cuối cùng chứa giá trị trong một cột (ở đây là cột B).
=ArrayFormula(MATCH(2,1/(B:B<>""),1))
Muốn biết thêm về công thức khó hiểu này? Đây là đó – Địa chỉ của ô không rỗng cuối cùng mà bỏ qua ô trống trong một cột trong Excel.
Công thức trên hoạt động tốt trong Google Sheets. Hãy xem tôi sẽ sử dụng nó như thế nào trong công thức của chúng ta.
Công thức cuối cùng:
=ArrayFormula(if(row(B2:B)<=MATCH(2,1/(B:B<>""),1),lookup(row(A2:A),row(A2:A)/if(B2:B<>"",TRUE,FALSE),B2:B),))
Logic Lookup trong việc Tự động điền các ô trống với giá trị phía trên
Các search_keys là các số hàng trong cột C và search_range nằm trong cột D (vui lòng cuộn lên và xem hình ảnh #3).
Bất kỳ nơi nào các search_keys xuất hiện trong phạm vi tìm kiếm, các giá trị tương ứng từ B2:B (result_range) sẽ được trả về.
Trong một số ô, các search_keys (C2:C) không có sẵn trong phạm vi tìm kiếm (D2:D).
Trong trường hợp đó, công thức sẽ tuân theo tính năng Lookup dưới đây (qua Trợ giúp của Docs Editors).
Nếu không tìm thấy search_key, mục sử dụng trong tìm kiếm sẽ là giá trị ngay trước nhỏ hơn trong phạm vi cung cấp.
Mong rằng bạn có thể hiểu nó.
Tự động điền các ô trống với giá trị phía trên trong Google Sheets – SCAN
SCAN là một trong các hàm trợ giúp LAMBDA (LHF), được biết đến với việc trả về tổng chạy của một cột trong một lần duy nhất.
Chúng ta có thể sử dụng hàm đó để điền các ô trống giữa các ô không trống trong một cột.
Chúng ta có thể thay thế công thức LOOKUP ở trên (hình ảnh #4) bằng công thức SCAN dưới đây.
=scan(0,B2:B,lambda(a,v,if(v="",a,v)))
Để giới hạn sự mở rộng của công thức tương tự như hình ảnh #5, chúng ta có thể chọn logic dưới đây.
REDUCE sau đây (một LHF khác) có thể tìm số hàng tương ứng với ô chứa giá trị cuối cùng trong cột B.
=reduce(0,B:B,lambda(a,v,(if(v="",v&a,row(v)))))
Vì vậy, thay thế B2:B bằng indirect(“B2:B”&reduce(0,B:B,lambda(a,v,(if(v=””,v&a,row(v)))))) trong công thức trên.
=scan(0,indirect("B2:B"&reduce(0,B:B,lambda(a,v,(if(v="",v&a,row(v)))))),lambda(a,v,if(v="",a,v)))
Đó là tất cả về cách điền các ô trống trong một cột với giá trị từ ô phía trên.
