Chào mừng đến với Bolamarketing.com! Hôm nay chúng ta sẽ khám phá cách lấy ngày lớn nhất trong mỗi hàng sử dụng một công thức mảng trong Google Sheets. Hãy cùng tìm hiểu nhé!
Cách dùng công thức Max và Large
Trong Google Sheets, chúng ta có thể sử dụng một số công thức hoặc kết hợp các công thức mảng và không mảng để lấy ngày lớn nhất trong mỗi hàng. Các công thức chính để làm điều này là Max và Large. Tuy nhiên, chúng ta cũng có thể sử dụng Query và Dmax.
Dưới đây là một số kết hợp công thức mà chúng ta có thể thử:
- Sortn + Transpose.
- Sort + Transpose + Array_Constrain.
- Dmax + Transpose.
Tất cả các thảo luận trên đều liên quan đến các công thức không mảng.
Vậy còn công thức mảng để trả về ngày lớn nhất trong mỗi hàng trong Google Sheets?
Trong trường hợp này, chúng ta có thể thử kết hợp công thức Dmax + Transpose.
Tuy nhiên, từ khi giới thiệu gói công thức mới, chúng ta có thể sử dụng công thức Max kết hợp với Byrow Lambda để lấy ngày gần đây/nhất theo hàng.
Trong bài hướng dẫn này, bạn sẽ được tìm hiểu về ba công thức tốt nhất để trả về ngày gần đây/nhất trong mỗi hàng trong một cột.
Các công thức đó là:
- Max – Công thức không mảng (chúng ta sẽ sử dụng công thức này trong một công thức kết hợp để loại bỏ chuỗi văn bản, các ô trống và giá trị bằng không trong phạm vi).
- Dmax + Transpose – Công thức mảng.
- Max + Byrow (phiên bản mảng của tùy chọn số 1 ở trên) – Được đề xuất!
Dữ liệu mẫu và công thức không mảng
Hãy tưởng tượng bạn có một bảng chứa một số cột dữ liệu.
Cột đầu tiên dành cho các số hiệu, và cột thứ hai dành cho tên nhân viên.
Dòng đầu tiên, sau hai cột đầu tiên, chứa tên hoạt động. Ngày hoàn thành bởi các nhân viên/assignees nằm bên dưới chúng.
Bạn muốn tìm ngày hoàn thành gần đây nhất của các hoạt động bởi assignees trong mỗi hàng.
Trong trường hợp như vậy, chúng ta có thể sử dụng một công thức kết hợp dựa trên Max để tìm ngày lớn nhất trong mỗi hàng như sau:
Formula #1:
=iferror(MAX(filter(C2:G2, datevalue(C2:G2))))Với năm hoạt động của “Ryan” (xem phạm vi B2:G2), ngày hoàn thành gần đây nhất của anh ta là 10/09/2021.
Anh ta đã hoàn thành ba hoạt động khác trước đó.
Tại sao tôi lại sử dụng công thức kết hợp thay vì chỉ sử dụng Max một mình?
Dữ liệu trên không đủ để sử dụng Max hoặc Large một mình do phạm vi ngày chứa các ô trống, các chuỗi văn bản và giá trị bằng không.
Khi chúng ta sử dụng các công thức như Max hoặc Large với các loại dữ liệu như vậy, chúng ta có thể gặp hai loại lỗi – #NUM! và một ngày không hợp lệ, tức là 30/12/1899.
Để tránh chúng, tôi đã sử dụng các công thức FILTER và DATEVALUE để lọc ra những giá trị không mong muốn đó trước khi tìm giá trị lớn nhất.
Nếu bạn muốn tìm ngày gần nhất thứ hai hoặc thứ n trong hàng, thay thế Max bằng LARGE.
Sử dụng công thức DMAX để lấy ngày lớn nhất trong mỗi hàng trong Google Sheets
Như bạn đã biết, DMAX là một hàm cơ sở dữ liệu. Nhiệm vụ của nó là trả về giá trị lớn nhất từ các cột, chứ không phải từ các hàng.
Tuy nhiên, chúng ta có thể sử dụng TRANSPOSE để thay đổi hướng dữ liệu từ hàng sang cột và sử dụng Damx trên các dữ liệu như vậy.
Tôi đã giải thích việc sử dụng như vậy ở đây – Trả về Giá trị Lớn Nhất Thứ Nhất và Thứ Nhì Trong Mỗi Hàng Trong Google Sheets.
Chúng ta sẽ áp dụng cùng một phương pháp ở đây.
Formula #2 (I2:I10 phải trống trước đó):
=ArrayFormula( TO_DATE( DMAX( transpose({C1:C10,C1:G10}), sequence(rows(C2:C10),1,2), {"Date 1";if(,,)} ) ) )Nó sẽ trả về ngày lớn nhất trong mỗi hàng nhưng với một số ngày không hợp lệ.
Xin hãy xem các ô I5, I6, I8, I9 và I10 cho ngày 30/12/1899.
Điều đó là do các người được giao không có ngày gửi dưới hoạt động của họ.
Chúng ta muốn công thức trả về ô trống trong những hàng đó.
Làm thế nào?
Hãy gọi công thức trên là base_dmax.
Chúng ta có thể sử dụng một kiểm tra IF để giải quyết vấn đề của chúng ta.
IF(base_dmax >0, base_dmax,)Hãy viết một công thức mới dựa trên nó.
=ArrayFormula( TO_DATE( if( DMAX( transpose({C1:C,C1:G}), sequence(rows(C2:C),1,2), {"Date 1";if(,,)} )>0, DMAX( transpose({C1:C,C1:G}), sequence(rows(C2:C),1,2), {"Date 1";if(,,)} ), ) ) )Thay thế công thức mảng I2 bằng công thức trên.
Sử dụng công thức mảng MAX Lambda – Mới!
Xin hãy cuộn lên và xem công thức đầu tiên dưới phụ đề “Dữ liệu mẫu và công thức không mảng”.
Chúng ta có thể sử dụng Byrow Lambda Helper function để mở rộng kết quả công thức Max đó.
Vì vậy, để lấy ngày hoạt động lớn nhất trong mỗi hàng, chúng ta có thể sử dụng công thức mảng sau đây.
Formula #3 (I2:I10 phải trống trước đó):
=byrow(C2:G10,lambda(r, iferror(MAX(filter(r,datevalue(r))))))Làm thế nào để lấy ngày gần nhất thứ hai hoặc thứ n trong mỗi hàng trong Google Sheets?
Sau đó, hãy sử dụng Byrow với công thức #1.1 sau đây.
=byrow(C2:G10,lambda(r, iferror(LARGE(filter(r,datevalue(r)),2))))Vậy là xong. Cảm ơn đã quan tâm và theo dõi bài viết. Chúc bạn thật vui vẻ!
