Bạn có thể sử dụng hàm Large để trích xuất ngày lớn nhất thứ n trong một nhóm. Tuy nhiên, khi bạn muốn trích xuất ngày lớn nhất trong mỗi nhóm trong Google Sheets, hàm Large sẽ không hữu ích. Vì sao?
Hàm Large nhận một mảng (dữ liệu) làm đối số và trả về một giá trị duy nhất.
Ví dụ, bạn có thể sử dụng công thức này trong ô E2 và điền xuống E3 và E4 bằng cách sử dụng fill handle.
=large(filter($A$2:$B,$B$2:$B=D2),1)
Công thức trên sẽ trả về các ngày gần đây nhất của mỗi sản phẩm. Nghĩa là nó sẽ trả về ngày lớn nhất là 3/6/19 trong ô E2, 6/6/19 trong ô E3 và 8/6/19 trong ô E4.
Lưu ý: Đôi khi công thức có thể trả về giá trị ngày. Nếu vậy, hãy định dạng dải đầu ra E2:E4 thành ngày – Format > Number > Date.
Sau đó, thay đổi số hạng (lớn nhất đến nhỏ nhất) từ 1 thành 2 để trả về ngày lớn nhất thứ hai.
=large(filter($A$2:$B,$B$2:$B=D2),2)
Vậy có giải pháp thay thế không? Có công thức mảng nào để tìm ngày lớn nhất trong mỗi nhóm trong Google Sheets không?
Trong một nhóm dữ liệu như trên hoặc thậm chí một tập dữ liệu không được phân loại, bạn có thể sử dụng hàm Vlookup kết hợp với Sort để trả về ngày lớn nhất trong mỗi nhóm trong Google Sheets.
Tuy nhiên, có một vấn đề với sự kết hợp trên. Kết hợp trên chỉ trả về ngày lớn nhất đầu tiên trong mỗi nhóm.
Nếu bạn muốn tìm ngày lớn nhất thứ n trong mỗi nhóm, nghĩa là ngày lớn nhất thứ nhất, thứ hai hoặc thứ ba trong mỗi nhóm, bạn có thể cần tìm một giải pháp khác. Tôi có tất cả những thông tin (công thức) cần thiết để đáp ứng yêu cầu của bạn.
Kết hợp Vlookup + Sort để tìm ngày lớn nhất thứ nhất trong mỗi nhóm trong Google Sheets
Như tôi đã nói ở trên, công thức sau chỉ hoạt động để trích xuất ngày lớn nhất thứ nhất trong tất cả các nhóm (danh mục).
Hãy xem xét ví dụ tương tự trên nơi tôi đã sử dụng kết hợp Large + Filter. Đó là một công thức không mảng.
Bạn có thể thay thế nó bằng công thức mảng kết hợp Vlookup + Sort dưới đây.
=ArrayFormula(IFERROR(vlookup(D2:D,Sort({B2:B,A2:A},2,0),2,0)))
Nếu bạn biết cách sử dụng hàm Vlookup phổ biến trong Google Sheets, bạn chắc chắn sẽ thấy công thức trên dễ hiểu hơn rất nhiều.
VLOOKUP(search_key, range, index, [is_sorted])
search_key = D2:D – tên sản phẩm.
range = Sort({B2:B,A2:A},2,0) – Thay đổi thứ tự cột, tức là di chuyển cột tên sản phẩm từ cuối (cột thứ hai) ra đầu. Đây là vì khóa tìm kiếm của Vlookup là tên sản phẩm. Sắp xếp cột thứ hai, tức là cột ngày, theo thứ tự giảm dần (lớn nhất đến nhỏ nhất).
index = 2 – Cột chỉ mục chứa ngày.
Tôi muốn tìm ngày lớn thứ hai trong mỗi nhóm. Vậy phải làm gì? Công thức Vlookup trên sẽ không hoạt động ở đây. Tôi có một công thức mới cho bạn. Xem bên dưới.
Công thức mảng để tìm ngày lớn nhất thứ n trong mỗi nhóm trong Google Sheets
Tôi có một công thức kết hợp tìm các ngày lớn nhất bất kể yếu tố lớn nhất thứ n của nó.
Điều đó có nghĩa là công thức dưới đây rất linh hoạt và có thể được sử dụng để trả về các ngày lớn nhất thứ nhất, thứ hai hoặc thứ ba trong Google Sheets.
Đây là một công thức phức tạp một chút. Vì vậy, hãy để tôi giải thích cách mã hóa công thức này từng bước.
Vui lòng nhập các công thức dưới đây (các công thức từ bước # 1 đến 6) vào các ô quy định. Chúng ta sẽ kết hợp tất cả các công thức này sau đó.
Bước # 1 (Ô G2) – Sắp xếp Sản phẩm và Ngày
Sắp xếp phạm vi A2:B bằng hàm SORT. Nó phải được sắp xếp theo sản phẩm (cột 2) theo thứ tự giảm dần và sau đó theo ngày (cột 1) cũng theo thứ tự giảm dần.
=sort(A2:B,2,0,1,0)
Bước # 2 (Ô J2) – Trích xuất Cột thứ hai
Sử dụng hàm Index, chúng ta có thể trả về cột thứ hai (B2:B) từ kết quả đã được sắp xếp ở trên.
=index(sort(A2:B,2,0,1,0),0,2)
Bước # 3 (Ô L2) – Cột ảo thứ ba mới chứa Running Count
Công thức sau sẽ đếm các sản phẩm và đặt số thứ tự tuần tự theo nhóm. Nghĩa là nếu sản phẩm “Apple” lặp lại hai lần, nó sẽ trả về số 1 cho lần xuất hiện đầu tiên và số 2 cho lần xuất hiện thứ hai và cứ tiếp tục.
=ARRAYFORMULA(COUNTIFS(B2:B,B2:B,ROW(B2:B),"<="&ROW(B2:B)))
Thực ra, đây là một loại đếm chạy (xếp hạng theo nhóm). Tôi đã trình bày chi tiết ở đây – Running Count in Google Sheets – Formula Examples (vui lòng xem phần bình luận trong bài đăng đó cho công thức trên). Bạn có thể đạt được cùng một kết quả bằng các công thức khác nhau và bài đăng được liên kết ở trên chứa nó. Trong đó, COUNTIFS là công thức đơn giản nhất.
Bước # 4 (Ô N2) – Phạm vi đã được sắp xếp trong đếm chạy
Trong công thức COUNTIFS ở trên, hãy thay thế phạm vi B2:B (với công thức bước # 2) như sau. Bạn có thể để B2:B trong công thức ROW như cũ.
=ARRAYFORMULA(COUNTIFS(index(sort(A2:B,2,0,1,0),0,2),index(sort(A2:B,2,0,1,0),0,2),ROW(B2:B),"<="&ROW(B2:B)))
Bước # 5 (Ô P2)
Kết hợp công thức bước # 1 và bước # 4 để tạo một mảng 3 cột như dưới đây.
Công thức chung:
=ArrayFormula({công thức bước #1,công thức bước #2})
Công thức:
=ArrayFormula({sort(A2:B,2,0,1,0),COUNTIFS(index(sort(A2:B,2,0,1,0),0,2),index(sort(A2:B,2,0,1,0),0,2),ROW(A2:A),"<="&ROW(A2:A))})
Bước # 6 (Ô T2) – Lọc ngày lớn nhất thứ n trong mỗi nhóm
Sử dụng dữ liệu bước # 5 trên làm dữ liệu (phạm vi) trong truy vấn và lọc cột 3 = giá trị lớn nhất thứ n. Bạn chỉ cần chọn các cột 2 và 1.
=ArrayFormula(Query({sort(A2:B,2,0,1,0),COUNTIFS(index(sort(A2:B,2,0,1,0),0,2),index(sort(A2:B,2,0,1,0),0,2),ROW(A2:A),"<="&ROW(A2:A))},"Select Col2,Col1 where Col3=1"))
Trong đó, phần cuối của mã col3=1 ổn định giá trị lớn nhất thứ n. 1 có nghĩa là giá trị lớn nhất đầu tiên, 2 có nghĩa là giá trị lớn nhất thứ hai và cứ tiếp tục.
Bước cuối cùng trong ô E2 – Công thức mảng để trích xuất ngày lớn nhất trong mỗi nhóm trong Google Sheets
Hãy đi đến đầu bài viết này. Dưới tiêu đề “Tổ hợp Vlookup + Sort…” bạn có thể tìm thấy công thức dưới đây.
=ArrayFormula(IFERROR(vlookup(D2:D,Sort({B2:B,A2:A},2,0),2,0)))
Trong đó, thay thế mã Sort({B2:B,A2:A},2,0) bằng công thức mà tôi đã cung cấp ở bước # 6 ở trên. Bạn có thể bỏ qua việc sao chép ArrayFormula bên ngoài trong công thức đó.
=ArrayFormula(IFERROR(vlookup(D2:D,Query({sort(A2:B,2,0,1,0),COUNTIFS(index(sort(A2:B,2,0,1,0),0,2),index(sort(A2:B,2,0,1,0),0,2),ROW(A2:A),"<="&ROW(A2:A))},"Select Col2,Col1 where Col3=1"),2,0)))
Trên ảnh chụp màn hình, tôi đã đánh dấu phần tử điều khiển giá trị lớn nhất thứ n trong công thức. Đó là tất cả về việc trích xuất ngày lớn nhất trong mỗi nhóm trong Google Sheets.
Tài liệu tham khảo bổ sung:
- Extract the Earliest or Latest Record in Each Category Based on Timestamp.
- How to Find the Highest N Values in Each Group in Google Sheets.
- Sum Large/Max n Values Based on Criteria in Google Sheets.
- How to Rank Group Wise in Google Sheets in Sorted or Unsorted Group.
