Chạy các giá trị Max trong Google Sheets, chúng ta có thể sử dụng hai loại công thức – kéo xuống (copy-paste) hoặc công thức mảng.
Với công thức kéo xuống, chúng ta chỉ cần sử dụng công thức MAX đơn giản.
Trong chuỗi giá trị sau đây {3;5;4;6;6;8;7;9;1;1} trong một cột, các giá trị Max liên tục là chuỗi giá trị {3;5;5;6;6;8;8;9;9;9}.
Giả sử chuỗi giá trị trên đang nằm trong phạm vi ô B2:B11.
Bạn có thể sử dụng công thức dưới đây trong ô C2 và kéo xuống (sử dụng fill handle trong ô C2) cho đến ô C11 để có được bộ giá trị Max liên tục thứ hai.
=max($B$2:B2)
Như một lưu ý phụ, chúng ta có thể trả về các High-Water Marks từ chuỗi số trong C2:C11. Tôi sẽ giải thích nó sau.
Về mảng công thức, có nhiều phương pháp khác nhau.
- DMAX (cũ)
- SCAN (mới) – Được đề nghị!
Công thức mảng Max liên tục trong Google Sheets – DMAX
Chúng ta có thể sử dụng hàm cơ sở dữ liệu DMAX để viết công thức mảng Max liên tục trong Google Sheets. Tuy nhiên, nó hơi phức tạp.
Cú pháp: DMAX(database, field, criteria)
Chúng ta chỉ có một dải ô (mảng) để sử dụng, tức là B2:B11. Vì vậy, bạn có thể nghĩ rằng chúng ta không thể sử dụng hàm DMAX.
Bạn đúng một phần bởi vì hàm trên yêu cầu cơ sở dữ liệu hoặc dữ liệu có cấu trúc.
Để giải quyết vấn đề đó, chúng ta sẽ sử dụng một số hàm khác để định dạng dải ô thành một cơ sở dữ liệu ảo.
Công thức sau đây có thể không mới đối với độc giả của tôi.
Điều này vì tôi đã sử dụng các hàm cơ sở dữ liệu để có kết quả mảng theo hàng tương tự trước đây.
Làm trống dải ô C2:C11 và chèn công thức mảng Max liên tục sau đây vào ô C2.
Công thức mảng Max liên tục chính:-
=ArrayFormula(dmax(transpose({B2:B11,if(sequence(10,10)/sequence(10,10)+sequence(1,10,row(A2)-1)<=row(A2:A11),transpose(B2:B11))}),sequence(rows(B2:B11),1),{if(,,);if(,,)}))
Công thức DMAX trên là cho các giá trị trong phạm vi kín B2:B11. Sau khi học cách sử dụng, chúng ta sẽ thay thế B11 bằng B (một phạm vi mở).
Giải thích công thức
Có ba đối số trong hàm, đó là database, field và criteria.
Dưới đây là chúng trong công thức DMAX (phần đậm chữ).
DATABASE
=ArrayFormula(transpose({B2:B11,if(sequence(10,10)/sequence(10,10)+sequence(1,10,row(A2)-1)<=row(A2:A11),transpose(B2:B11))}))
FIELD
=ArrayFormula(sequence(rows(B2:B11),1))
CRITERIA
{if(,,);if(,,)}
Vì mục đích giải thích, tôi đã nhập chúng vào các ô E2, P2 và R2 tương ứng. Vui lòng xem hình ảnh dưới đây.
Lưu ý: Để chúng hoạt động độc lập, tôi cũng sử dụng thêm hàm ArrayFormula.
Tôi đã sử dụng các kết quả trên trong công thức DMAX mới trong ô C2 như sau để trả về Max liên tục của các giá trị trong B2:B11.
=ArrayFormula(dmax(E2:N12,P2:P11,R2:R3))
Không cần phải nói, trong công thức mảng Max liên tục chính của chúng ta, chúng ta đã thay thế E2:N12, P2:P11 và R2:R3 bằng các công thức tương ứng và do đó tránh các phạm vi cột trợ giúp.
Giải thích về đối số (Field và Criteria)
Tôi sẽ giải thích đối số database sau. Đây là những giải thích cho hai đối số khác, tức là field và criteria.
Trong cơ sở dữ liệu E2:N12, có mười cột. Vì vậy, field P2:P11 chứa các số từ 1 đến 10, biểu thị cho từng cột.
Chúng ta không có cột criteria trong cơ sở dữ liệu. Vì vậy, theo tiêu chuẩn trong các hàm cơ sở dữ liệu, sử dụng hai ô trống dọc, tức là R2:R3, để biểu thị nó.
Liên quan: Hai cách để chỉ định ô trống trong Công thức Google Sheets.
Bây giờ chúng ta cần đi vào chi tiết về database, đó là phần trung tâm và phức tạp nhất của công thức mảng Max liên tục trong Google Sheets.
Database và Logic
Trước tiên, tôi sẽ giải thích logic để giúp bạn hiểu rõ về ‘database’ được sử dụng trong E2:N12.
Chúng ta có thể có được giá trị Max liên tục trong Google Sheets theo cách sau.
Đây là các giá trị {3;5;4;6;6;8;7;9;1;1} trong B2:B11.
Để trả về giá trị Max liên tục, chúng ta có thể sử dụng các công thức =max(3), =max(3,5), =max(3,5,4), =max(3,5,4,6), =max(3,5,4,6,6), =max(3,5,4,6,6,8), =max(3,5,4,6,6,8,7), =max(3,5,4,6,6,8,7,9), =max(3,5,4,6,6,8,7,9,1), =max(3,5,4,6,6,8,7,9,1,1) trong các ô C2, C3, C4, C5, C6, C7, C8, C9, C10 và C11, tương ứng.
Logic nằm ở việc viết công thức mảng Max liên tục trong Google Sheets.
Thử giải thích nó.
Chúng ta sẽ sử dụng một công thức duy nhất để có được phạm vi được sử dụng trong mười công thức Max trên trong 10 cột như sau.
Đây là công thức đó.
=ArrayFormula(transpose({if(sequence(10,10)/sequence(10,10)+sequence(1,10,row(A2)-1)<=row(A2:A11),transpose(B2:B11))}))
Bạn có thể tìm thấy giải thích công thức này ở đây – Cách nhận mẫu 1-12-123-1234 bằng công thức mảng trong Google Sheets.
Bây giờ chúng ta phải làm nó trở thành cơ sở dữ liệu cho DMAX.
Bằng cách thêm một hàng tiêu đề, chúng ta có thể định dạng bảng trên thành cơ sở dữ liệu.
Vì vậy, tôi đã chỉnh sửa công thức trên như sau.
=ArrayFormula(transpose({B2:B11,if(sequence(10,10)/sequence(10,10)+sequence(1,10,row(A2)-1)<=row(A2:A11),transpose(B2:B11))}))
Tôi chỉ thêm các giá trị B2:B11 làm nhãn trường (thậm chí một hàng trống sẽ phục vụ mục đích).
Trong hình ảnh trên, xin vui lòng xem phạm vi E2:N2 cho các nhãn trường.
Phía trên là công thức đang được sử dụng trong ô E2 (database).
DMAX ‘mới’ trong ô C2 sử dụng cơ sở dữ liệu trên và trả về giá trị Max của từng cột theo chiều dọc vì chúng ta đã chỉ định các trường (số cột) theo chiều dọc trong P2:P11.
Làm thế nào để sử dụng phạm vi mở trong công thức này?
Khi chúng ta sử dụng phạm vi mở dưới đây, hiệu suất của Bảng đang xét có thể bị ảnh hưởng xấu.
=ArrayFormula(if(B2:B="",,dmax(transpose({B2:B,if(sequence(rows(B2:B),rows(B2:B))/sequence(rows(B2:B),rows(B2:B))+sequence(1,rows(B2:B),row(A2)-1)<=row(A2:A),transpose(B2:B))}),sequence(rows(B2:B),1),{if(,,);if(,,)})))
Vì vậy, chúng tôi sẽ thực hiện một phương án tạm thời sẽ đảm bảo cải thiện hiệu suất.
Đây là cách.
Chúng tôi sẽ sử dụng một công thức để tìm ô cuối cùng không trống mà bỏ qua các ô trống trong cột B.
Công thức =ArrayFormula(MATCH(2,1/(B:B<>""),1)) sẽ trả về 11, là số hàng của ô không trống cuối cùng hiện tại trong cột B.
Chúng tôi sẽ sử dụng nó (phần in đậm) để sửa đổi công thức mảng Max liên tục theo bảng dưới đây.
Tôi sẽ thực hiện những thay đổi trên và bao gồm công thức vào bảng mẫu của tôi dưới đây.
Tương tự như Cummin, chúng ta có thể sử dụng hàm Lambda SCAN để viết công thức mảng Max liên tục (Cummax) trong Google Sheets.
Đây là công thức đó.
=scan(B2,B2:B,lambda(a,v,if(and(a>=v,v<>""),a,v)))
Nó có hai ưu điểm chính so với công thức DMAX trước.
- Đây là code sạch sẽ.
- Hoạt động trong dữ liệu lớn.
Công thức SCAN duyệt qua các giá trị trong B2:B, từ B2, từng hàng một, thay thế các giá trị bằng giá trị cao nhất cho đến nay, kết thúc với một dải ô mà hàng cuối cùng chứa giá trị cao nhất.
Trong công thức Max liên tục SCAN trên, a (accumulator) bằng B2 trong hàng đầu tiên.
Đối với v, đó là giá trị từng hàng để đánh giá. Trong hàng đầu tiên, v sẽ là B2, hàng thứ hai sẽ là B3, và cứ thế.
Nếu a >= v, công thức trả về giá trị accumulator, ngược lại trả về giá trị hàng. Giá trị max trả về sẽ là giá trị accumulator trong hàng tiếp theo.
Các điểm High-Water của các giá trị hiện tại trong B2:B11 là {3;5;6;8;9}.
Theo như tôi biết, đó là các giá trị duy nhất của các giá trị Max liên tục. Vì vậy, chúng ta chỉ cần bọc công thức mảng của chúng tôi trong UNIQUE để nhận được nó.
Cú pháp: unique(running_max_formula)
Công thức:
=unique(scan(B2,B2:B,lambda(a,v,if(and(a>=v,v<>""),a,v))))
Đó là tất cả. Cảm ơn bạn đã đọc. Chúc bạn có những giây phút vui vẻ!
Ví dụ_191121
