Với chức năng của MINIFS trong công thức mảng, chúng ta sẽ kết hợp nó với hàm MAP Lambda trong Google Sheets. Điều này là cần thiết vì MINIFS thường chỉ trả về một giá trị tối thiểu duy nhất cho mỗi bộ tiêu chí.
Chúng tôi sẽ hiển thị các ví dụ thực tế sử dụng một bộ dữ liệu đa dạng với các loại giá trị khác nhau. Điều này giúp bạn hiểu cách sử dụng MINIFS một cách hiệu quả với nhiều cột tiêu chí để mở rộng kết quả.
Hãy tham khảo ảnh chụp màn hình dưới đây cho dữ liệu mẫu, trong đó cột A chứa ngày bán hàng (Ngày), cột B chứa tên trái cây (Mục), cột C chứa mức độ trái cây (Grade) và cột D chứa số lượng bán hàng (Qty).
Hãy đi vào một số ví dụ cụ thể về công thức mảng MINIFS trong Google Sheets.
MINIFS Array Formula: Các ví dụ cơ bản cho Kết quả Mở rộng
Ví dụ 1: Tìm số lượng bán hàng thấp nhất của Apple và Orange
Bước:
- Chỉ định các tiêu chí, tức là “Apple” và “Orange”, trong các ô F2 và F3 tương ứng.
- Chèn công thức mảng MINIFS sau đây vào ô G2, nó sẽ trả về kết quả trong dải ô G2:G3:
=MAP(F2:F3, LAMBDA(r, MINIFS(D1:D, B1:B, r)))
Lưu ý: Hãy cuộn lên để xem ảnh chụp màn hình mô tả các ô đầu vào cho tiêu chí và kết quả công thức trong dải ô F2:G3.
Giải thích công thức:
Trong công thức MINIFS, tức là MINIFS(D1:D, B1:B, r), ‘r’ đại diện cho phần tử hiện tại, sẽ là F2 và F3 trong các hàng tương ứng.
Hàm MAP lặp lại qua dải tiêu chí F2:F3, cho phép MINIFS mở rộng kết quả.
Ví dụ 2: Tìm số lượng bán hàng thấp nhất trong tháng 12 năm 2023 và tháng 1 năm 2024
Lưu ý: Dữ liệu nguồn bao gồm một cột ngày, không phải là một cột tháng. Chúng tôi sẽ chuyển đổi tất cả các ngày trong cột ngày thành ngày đầu tiên của tháng và sử dụng các ngày bắt đầu (01/12/2023 và 01/01/2024) làm tiêu chí trong công thức mảng MINIFS. Quá trình này tương đương với việc tìm các giá trị thấp nhất trong những tháng đó.
Bước:
- Nhập tiêu chí, 01/12/2023 và 01/01/2024, vào các ô F6 và F7 tương ứng.
- Nhập công thức mảng MINIFS sau đây vào ô G6, nó sẽ trả về kết quả trong dải ô G6:G7:
=ArrayFormula(MAP(F6:F7, LAMBDA(r, MINIFS(D1:D, EOMONTH(A1:A, -1)+1, r))))
Lưu ý: Hãy cuộn lên để xem ảnh chụp màn hình mô tả các ô đầu vào cho tiêu chí và kết quả công thức trong dải ô F6:G7.
Trong công thức MINIFS, EOMONTH(A1:A, -1)+1 chuyển đổi các ngày trong cột A thành ngày đầu tiên của tháng. Hàm EOMONTH yêu cầu sự mở rộng của hàm ARRAYFORMULA, và hàm MAP lặp lại qua dải tiêu chí, tương tự như ví dụ trước đó.
Đây là hai ví dụ cơ bản về việc sử dụng hàm MINIFS trong công thức mảng trong Google Sheets. Bây giờ, chúng ta hãy khám phá một số tình huống phức tạp hơn một chút.
Các ví dụ nâng cao
Trong ví dụ đầu tiên dưới đây, công thức MINIFS hoạt động trên các tiêu chí từ hai cột: tên trái cây và các mức độ tương ứng của chúng.
Nó tận dụng logic VÀ để trả về giá trị thấp nhất thỏa mãn cả tên trái cây được chỉ định và mức độ liên quan của nó.
Ví dụ thứ hai cũng liên quan đến tiêu chí từ hai cột, nhưng nó giới thiệu logic HOẶC trong cột mức độ.
Ở đây, công thức MINIFS trả về giá trị thấp nhất phù hợp với tên trái cây được chỉ định trong một cột và bất kỳ mức độ nào được liệt kê trong cột khác.
Hãy xem hai ví dụ dưới đây.
Logic VÀ trong MINIFS Array Formula trong Google Sheets
Làm thể nào chúng ta có thể xác định số lượng bán hàng thấp nhất của táo và cam cấp 1?
Bước:
- Chỉ định các tiêu chí “táo” và “cam” trong dải ô F2:F3.
- Chỉ định tiêu chí “Gr. 1” trong dải ô G2:G3.
- Chèn công thức mảng MINIFS sau đây vào ô H2:
=MAP(F2:F3, G2:G3, LAMBDA(r_1, r_2, MINIFS(D1:D, B1:B, r_1, C1:C, r_2)))
Lưu ý: Hãy cuộn lên để xem ảnh chụp màn hình mô tả các ô đầu vào cho tiêu chí và kết quả công thức trong dải ô F2:H3.
Giải thích công thức:
Trong công thức MINIFS, tức là MINIFS(D1:D, B1:B, r_1, C1:C, r_2), ‘r_1’ và ‘r_2’ đại diện cho cặp phần tử hiện tại, sẽ là {F2, G2} và {F3, G3} trong các hàng tương ứng.
Hàm MAP lặp lại qua dải tiêu chí F2:G3, cho phép MINIFS mở rộng kết quả.
Logic HOẶC trong MINIFS Array Formula trong Google Sheets
Làm thể nào chúng ta có thể xác định số lượng bán hàng thấp nhất của táo và cam, xem xét việc chúng có thể là cấp 1 hoặc cấp 2?
Hàm MINIFS thiếu hỗ trợ tích hợp cho logic HOẶC. Để giải quyết vấn đề này, chúng ta sẽ sử dụng một hàm trợ giúp để so khớp các mức độ và tạo một cột với TRUE cho các sự khớp và FALSE cho các không khớp.
Phương pháp này đáp ứng yêu cầu MINIFS bằng cách sử dụng công thức này như một cột tiêu chí ảo, với TRUE là tiêu chí. Do đó, chỉ có một tiêu chí từ một cột – một giá trị boolean TRUE.
Bước:
- Chỉ định các tiêu chí “táo” và “cam” trong dải ô F6:F7.
- Chỉ định tiêu chí “Gr. 1” trong dải ô G6:G7.
- Chỉ định tiêu chí “Gr. 2” trong dải ô H6:H7.
- Chèn công thức mảng MINIFS sau đây vào ô I6:
=ArrayFormula(MAP(F6:F7, G6:G7, H6:H7, LAMBDA(r_1, r_2, r_3, MINIFS(D1:D, B1:B, r_1, REGEXMATCH(C1:C, r_2&"|"&r_3), TRUE))))
Lưu ý: Hãy cuộn lên để xem ảnh chụp màn hình mô tả các ô đầu vào cho tiêu chí và kết quả công thức trong dải ô F6:I7.
Giải thích công thức:
Trong công thức MINIFS, tức là MINIFS(D1:D, B1:B, r_1, REGEXMATCH(C1:C, r_2&”|”&r_3), TRUE), ‘r_1’ và TRUE đại diện cho cặp phần tử hiện tại, sẽ là {F6, TRUE}, và {F7, TRUE} trong các hàng tương ứng.
Hàm REGEXMATCH khớp các mức độ (tức là r_2&”|”&r_3) trong cột tương ứng C1:C, trả về TRUE hoặc FALSE. Ở đây, ‘r_2’ và ‘r_3’ đại diện cho cặp phần tử hiện tại, sẽ là G6 và H6 trong hàng đầu tiên và G7 và H7 trong hàng thứ hai.
Ký tự “|” được sử dụng để phân tách mỗi tiêu chí. Để thêm nhiều tiêu chí làm phần của logic HOẶC, bạn có thể sử dụng nó như r_2&”|”&r_3&”|”&tiêu_chí_tiếp_theo.
Cài đặt này cho phép phù hợp một phần và phân biệt HOA/thường trong các mức độ. Để làm cho nó không phân biệt HOA/thường, sử dụng mẫu “(?i)”&r_2&”|”&r_3, và để phù hợp chính xác, sử dụng mẫu “(?i)^”&r_2&”$|^”&r_3&”$.
Kết luận
Trên đây là bốn ví dụ về công thức mảng MINIFS sử dụng hàm MAP lambda. Chúng là độc đáo về chức năng vì tôi đã bao gồm nhiều điều kiện sử dụng cả logic VÀ và HOẶC.
Làm thế nào chúng ta có thể tìm các giá trị lớn nhất thay vì các giá trị nhỏ nhất theo điều kiện? Dưới đây là các hướng dẫn liên quan:
