Bạn đang sử dụng Google Sheets và muốn trích xuất giá trị cuối cùng từ mỗi cột? Bài viết này sẽ hướng dẫn bạn cách làm điều đó một cách dễ dàng.
Lấy giá trị cuối cùng từ một cột duy nhất
Trước khi đi vào trích xuất giá trị cuối cùng từ mỗi cột, chúng ta hãy hiểu cách lấy giá trị cuối cùng từ một cột duy nhất bằng cách sử dụng công thức LOOKUP. Giá trị cuối cùng được hiểu là giá trị từ ô không trống cuối cùng trong cột.
Dưới đây là một ví dụ để tìm giá trị cuối cùng trong cột B:
=ArrayFormula(lookup(9^9,if(len(B1:B),row(B1:B)),B1:B))
Lưu ý: Bạn có thể điều chỉnh phạm vi thành một phạm vi cố định như B2:B, B2:B100, vv.
Để lấy giá trị từ ô không trống cuối cùng trong cột tiếp theo, chỉ cần kéo công thức sang bên phải hoặc sử dụng Ctrl + R. Dễ dàng, phải không?
Trích xuất giá trị cuối cùng từ mỗi cột
Bây giờ, chúng ta sẽ đi vào chủ đề chính – trích xuất giá trị cuối cùng từ mỗi cột trong Google Sheets. Mặc dù Google Sheets không cung cấp một hàm cụ thể cho việc này, nhưng chúng ta có thể thực hiện nó bằng cách viết một công thức kết hợp nhiều hàm.
Để trích xuất giá trị cuối cùng từ các cột D, E, F, G và H (D2:H), bạn có thể sử dụng công thức sau trong ô B1:
=ArrayFormula(lookup(9^9,if(len(B2:B),row(B2:B)),B2:B))
Sau đó, chọn B1:H1 và nhấn Ctrl + R (Windows) hoặc ⌘ + R (Mac) để điền công thức vào phạm vi đã chọn.
Công thức mảng để lấy giá trị cuối cùng từ mỗi cột trong Google Sheets
Bây giờ, hãy khám phá một công thức phức tạp hơn có thể lấy giá trị cuối cùng từ mỗi cột trong Google Sheets. Làm theo các bước sau:
Bước 1: Lấy các địa chỉ ô của các ô không trống
Đối với bước này, hãy giả sử chúng ta muốn lấy các giá trị cuối cùng từ các cột B, D, E, F, G và H (phạm vi: B2:H10). Chúng ta có thể sử dụng công thức sau để lấy các địa chỉ ô của các ô không trống trong phạm vi này:
=ArrayFormula(if(B2:H10<>"",ADDRESS(row(B2:B10),column(B2:H2),4),))
Bước 2: Lấy các địa chỉ ô cao nhất từ mỗi cột
Bước này liên quan đến việc trích xuất các địa chỉ ô cao nhất từ mỗi cột, từ đó chúng ta có thể biết được số dòng cuối cùng đã sử dụng trong mỗi cột. Hãy chia nhỏ nó thành các bước nhỏ:
Loại bỏ chữ cái của các địa chỉ cột
Để loại bỏ chữ cái của các địa chỉ ô, sử dụng công thức sau:
=ArrayFormula(regexreplace(if(B2:H10<>"",ADDRESS(row(B2:B10),column(B2:H2),4),),"[A-Z]","")*1)
Lấy giá trị lớn nhất từ mỗi cột
Để tìm giá trị lớn nhất (số dòng cuối cùng) từ mỗi cột, sử dụng hàm QUERY:
=query(ArrayFormula(regexreplace(if(B2:H10<>"",ADDRESS(row(B2:B10),column(B2:H2),4),),"[A-Z]","")*1),"Select max(Col1),max(Col2),max(Col3),max(Col4),max(Col5),max(Col6),max(Col7)")
Công thức trên không linh hoạt. Để làm cho nó có thể mở rộng, chúng ta có thể tạo các chuỗi truy vấn một cách động bằng cách sử dụng công thức REGEXREPLACE. Hãy xem các hướng dẫn khác của tôi để biết thêm chi tiết.
Thay thế nhãn cột bằng chữ cái cột
Để loại bỏ hàng đầu tiên của đầu ra truy vấn (chứa các nhãn không mong muốn), sử dụng công thức sau:
=ArrayFormula(query(query(regexreplace(if(B2:H10<>"",ADDRESS(row(B2:B10),column(B2:H2),4),),"[A-Z]","")*1,"Select "&TEXTJOIN(",",TRUE,REGEXREPLACE(sequence(1,columns(B2:H2),1),"(d+)", "max(Col$1)"))),"Select * offset 1",0))
Bước 3: Sử dụng địa chỉ ô cao nhất gián tiếp để lấy giá trị cuối cùng đã sử dụng
Bây giờ chúng ta đã có các địa chỉ ô cuối cùng đã sử dụng từ mỗi cột, chúng ta có thể sử dụng hàm INDIRECT để lấy giá trị. Do giới hạn của INDIRECT, chúng ta sẽ cần sử dụng một phương pháp thông qua. Hãy xem các hướng dẫn khác của tôi về cách chuyển đổi địa chỉ ô thành giá trị trong Google Sheets để biết các bước hoàn chỉnh.
Tóm lại, bạn có thể sử dụng công thức sau để trích xuất giá trị cuối cùng đã sử dụng từ mỗi cột trong Google Sheets:
=ArrayFormula(SPLIT(TEXTJOIN("|",TRUE,TRANSPOSE(if(regexmatch(if(B2:H10<>"",ADDRESS(row(B2:B10),column(B2:H2),4),),"^"&textjoin("$|^",true,regexreplace(query({regexreplace(ADDRESS(row(B2),column(B2:H2),4),"[0-9]","");query(query(regexreplace(if(B2:H10<>"",ADDRESS(row(B2:B10),column(B2:H2),4),),"[A-Z]","")*1,"Select "&TEXTJOIN(",",TRUE,REGEXREPLACE(sequence(1,columns(B2:H2),1),"(d+)", "max(Col$1)"))),"Select * offset 1",0)},,9^9)," ",""))&"$")=TRUE,B2:H10,))),"|"))
Chạm khắc cuối cùng
- Để tính tổng các giá trị không trống cuối cùng trong mỗi cột, đơn giản chỉ cần bọc công thức cuối cùng trong hàm SUM.
- Điều chỉnh các tham chiếu phạm vi (ví dụ: H10 thành H và B10 thành B) để bao gồm tất cả các dòng nếu cần.
- Hãy nhớ rằng công thức có thể có giới hạn nếu tập dữ liệu rất lớn.
Đó là tất cả! Bây giờ bạn đã biết cách trích xuất giá trị cuối cùng từ mỗi cột trong Google Sheets. Hãy thỏa sức khám phá các khả năng!
Bảng tính với Công thức:
Crawlan.com
Tài nguyên
- Tìm địa chỉ ô cuối cùng đã sử dụng trong Google Sheets
- Cách tìm giá trị cuối cùng trong từng hàng trong Google Sheets
- Tìm cột không trống cuối cùng trong một hàng trong Google Sheets
- Đếm từ ô không trống đầu tiên đến ô không trống cuối cùng trong một hàng trong Google Sheets
- Cách tìm giá trị đầu tiên và cuối cùng trong một hàng trong Google Sheets
