Sử dụng hàm DMAX, chúng ta có thể trả về giá trị lớn nhất và thứ hai trong mỗi hàng trong Google Sheets. Bài viết này sẽ giải thích cách thực hiện điều đó.
Hàm đặc biệt để lấy giá trị lớn thứ nth trong Google Sheets là LARGE. Tuy nhiên, nó sẽ không hoạt động như một công thức mảng.
Tại thời điểm viết hướng dẫn này, chưa có chức năng tích hợp nào khác để trả về giá trị lớn nhất và thứ hai trong mỗi hàng. Vì vậy, tôi đã quyết định viết một công thức sử dụng hàm DMAX cho mục đích nói trên.
Công thức mà tôi sẽ viết sẽ đơn giản để hiểu. Nhưng để giúp bạn hiểu rõ công thức, tôi sẽ hướng dẫn bạn qua từng bước.
Hãy mã hoá một công thức mảng để trả về giá trị lớn nhất và thứ hai hoặc chúng ta có thể nói MAX1 và MAX2 trong Google Sheets.
Dữ liệu mẫu:
Công thức mảng để lấy giá trị lớn nhất và thứ hai trong mỗi hàng:
Để lấy giá trị lớn thứ hai trong mỗi hàng bằng một công thức mảng thì rất đơn giản nếu bạn biết cách lấy giá trị lớn nhất trong mỗi hàng.
Vì vậy trước tiên chúng ta sẽ viết công thức để trả về giá trị lớn nhất trong mỗi hàng.
Trên trang web này đã có một hướng dẫn về mục đích nói trên tại đây – Cách tìm giá trị lớn nhất trong mỗi hàng trong Google Sheets [Công thức mảng].
Nhưng tôi không đề xuất việc đó. Nó có thể không hoạt động nếu bảng của bạn chứa hàng trăm hàng. Thay vào đó, tôi sẽ sử dụng DMAX tương tự như DMIN mà tôi đã sử dụng trước đây ở đây – MIN theo hàng sử dụng DMIN trong Google Sheets.
Công thức này được gửi đến bởi lightmare, một trong số độc giả của tôi.
Đây là công thức để trả về giá trị lớn nhất đầu tiên trong mỗi hàng. Để thực hiện công thức này, sao chép và dán công thức sau vào ô H3, sau đó kéo xuống.
=large(B3:G3,1)Đây là công thức để trả về giá trị lớn thứ hai trong mỗi hàng. Để thực hiện công thức này, sao chép và dán công thức sau vào ô I3, sau đó kéo xuống.
=large(B3:G3,2)Sử dụng công thức không phải là một vấn đề nếu bạn có vài hàng. Nhưng nếu dữ liệu của bạn có nhiều hàng, tốt nhất là sử dụng công thức mảng.
Nó sẽ mang lại lợi ích cho bạn theo nhiều cách khác nhau. Ví dụ, nếu bạn chèn hàng, công thức mảng sẽ trả về kết quả trong hàng đó. Nhưng trong trường hợp các công thức không phải là mảng, bạn có thể cần sao chép và dán công thức từ hàng trên hoặc dưới.
Công thức mảng để lấy giá trị lớn nhất trong mỗi hàng (DMAX):
Cú pháp: DMAX(database, field, criteria)
Thông thường, tất cả các chức năng cơ sở dữ liệu đều yêu cầu nhãn trường (hàng tiêu đề) để hoạt động chính xác. Trong dữ liệu mẫu của chúng ta ở trên, chúng ta có nhãn trường trong B2: G2 (tên người bán).
Trước khi tiến hành công thức mảng, bạn cần hiểu một số kiến thức cơ bản. Đó là gì?
Chúng ta có thể sử dụng DMAX cho max theo cột trong một mảng như sau.
=ArrayFormula(dmax(A2:G7,{2,3,4,5,6,7},A2:A7))Trong công thức này, khoảng A2:G7 là cơ sở dữ liệu, {2,3,4,5,6,7} (các cột mà chúng ta muốn trả về giá trị max) là trường và A2:A7 là tiêu chí.
Ở đây, bạn có thể thay thế số trường {2,3,4,5,6,7} bằng trình tự công thức (1,columns(B2:G2),2) cũng được.
Đó là những kiến thức cơ bản để hiểu cách trả về giá trị lớn nhất và thứ hai trong mỗi hàng trong Google Sheets.
Để trả về Giá trị lớn nhất trong mỗi hàng, chúng ta có thể chuyển vị khoảng B3:G7 và sau đó áp dụng công thức max theo cột đã nói ở trên. Đó là cách lô-gic tôi đang theo dõi và sẽ giải thích ở dưới.
Khi làm như vậy, chúng ta có thể không có các nhãn trường (hàng tiêu đề) hoặc tiêu chí yêu cầu. Vì vậy, chúng ta sẽ sử dụng một số công cụ phụ. Dưới đây là những bước.
Tôi hy vọng các hình ảnh kèm theo lời giải thích sẽ hữu ích cho bạn hiểu các bước.
Các bước để làm theo (Với hình ảnh minh họa):
Các bước dưới đây chỉ để giải thích mục đích. Chúng tôi sẽ kết hợp các bước trong công thức cuối cùng để loại bỏ các ô hoặc phạm vi ô thừa.
- Trong ô A10, thêm một cột số hàng với phạm vi B3:G7. Bởi vì chúng ta chỉ sử dụng các cột số trong DMAX.
=ArrayFormula({sequence(rows(B3:B7),1),B3:G7})Chúng ta đã thay thế tên trái cây trong phạm vi A3: A7 bằng các số tuần tự từ 1 đến 5.
- Đơn giản chỉ cần chuyển vị dữ liệu ở bước 1.
=ArrayFormula(TRANSPOSE({sequence(rows(B3:B7),1),B3:G7}))Trên là bảng (B10: E16) để sử dụng DMAX.
- Hãy tìm giá trị lớn nhất của từng cột trong bảng này. Kết quả sẽ bằng với giá trị lớn nhất trong mỗi hàng của bảng B3: G7. Vì vậy, chúng ta nên chèn công thức vào ô H3.
Các tham số sẽ là;
database: B10: E16
field: sequence(rows(B3:B7),1) là cột đầu tiên được thêm vào bảng ở bước 1.
tiêu chí: {IF(,,);IF(,,)} – Chúng ta không có cột tiêu chí. Vì vậy, chúng tôi sử dụng hai ô trống (các nhãn trường và tiêu chí đều trống). Tôi nhận được ý tưởng này từ người dùng “nightmare”.
Vì vậy, công thức để sử dụng trong ô H3 sẽ là;
=ArrayFormula(dmax(A10:E16,sequence(rows(B3:B7),1),{IF(,,);IF(,,)}))Thêm vào đó, để loại bỏ phạm vi trợ giúp A10:E16, thay thế cùng trong công thức bằng công thức từ ô A10.
=ArrayFormula(dmax(TRANSPOSE({sequence(rows(B3:B7),1),B3:G7}),sequence(rows(B3:B7),1),{IF(,,);IF(,,)}))Trên chính là công thức để lấy giá trị lớn nhất trong mỗi hàng trong Google Sheets.
Công thức mảng để lấy giá trị lớn thứ hai trong mỗi hàng (DMAX):
Bây giờ, việc lấy giá trị lớn thứ hai trong mỗi hàng rất đơn giản.
Chèn cùng công thức trên vào ô I3.
Sau đó, chúng ta nên thay thế B3:G7 trong công thức I3 bằng một công thức khác.
Chưa hiểu?
Chúng ta đã có giá trị lớn nhất trong ô H3:H7 đúng không? Chúng ta nên kiểm tra các giá trị này trong phạm vi B3:G7. Sau đó thay thế các giá trị khớp bằng 0.
Nghe có vẻ phức tạp, nhưng một công thức IF đơn giản có thể làm điều đó.
Dưới đây là công thức đó;
if(B3:G7=H3:H7,0,B3:G7)Vì vậy, công thức để trả về giá trị lớn thứ hai trong mỗi hàng như sau (chèn công thức này vào ô I3).
=ArrayFormula(dmax(TRANSPOSE({sequence(rows(B3:B7),1),if(B3:G7=H3:H7,0,B3:G7)}),sequence(rows(B3:B7),1),{IF(,,);IF(,,)}))Làm thế nào để bao gồm thêm hàng trong các công thức DMAX?
Tôi hiểu rằng bạn có thể muốn sử dụng các công thức trên trong một phạm vi dữ liệu lớn.
Các công thức DMAX trên sẽ hoàn hảo cho một tập dữ liệu lớn. Hãy mở rộng phạm vi trong các công thức.
Lưu ý: Xóa tất cả các cột và hàng mà không sử dụng. Chèn hàng và cột khi cần thiết.
Công thức phạm vi mở H3:
=ArrayFormula(dmax(TRANSPOSE({sequence(rows(B3:B),1),B3:G}),sequence(rows(B3:B),1),{IF(,,);IF(,,)}))Công thức phạm vi mở I3:
=ArrayFormula(dmax(TRANSPOSE({sequence(rows(B3:B),1),if(B3:G=H3:H,0,B3:G)}),sequence(rows(B3:B),1),{IF(,,);IF(,,)}))Các công thức này có thể trả về các giá trị 0 như giá trị lớn nhất và giá trị lớn thứ hai đối với các hàng hoàn toàn trống.
Vì vậy, giống như thuộc tính của mọi người dùng Google Sheets, chúng ta có thể sử dụng hàm IF với công thức trên để điều chỉnh.
Chỉ cần chèn if(B3:B=””, hoặc if(A3:A=””, vào đầu công thức.
Bạn có thể tìm thấy nó trong bảng mẫu của tôi bên dưới.
Sử dụng LARGE trong mỗi hàng để trả về giá trị lớn thứ nth
Một trong những vấn đề với DMAX trên là nó không đáp ứng yêu cầu khi chúng ta muốn tìm giá trị lớn thứ nth trong mỗi hàng vì chúng ta không có cách để chỉ định n trong hàm cơ sở dữ liệu nói trên.
Hãy xem phương pháp chúng ta đã áp dụng ở trên để tìm giá trị lớn thứ hai trong mỗi hàng.
Bây giờ, nó rất đơn giản khi chúng ta có thể sử dụng hàm LARGE trong mỗi hàng và mở rộng nó bằng hàm BYROW.
Trong ô J3, hãy nhập công thức sau đây.
=byrow(B3:G,lambda(row,iferror(LARGE(row,1))))Phần màu đỏ rực rỡ ở phần cuối xác định ‘n’ trong công thức.
Để lấy max 3 trong mỗi hàng, hãy thay thế 1 bằng 3.
Đó là tất cả. Cảm ơn đã theo dõi. Chúc bạn vui vẻ!
SAMPLE_SHEET_16221
Đọc thêm tại Crawlan.com
