Bài viết cho thấy một vài cách khác nhau để có được giá trị tối đa trong Excel dựa trên một hoặc một số điều kiện mà bạn chỉ định. Show
Trong hướng dẫn trước của chúng tôi, chúng tôi đã xem xét các sử dụng phổ biến của Hàm MAX được thiết kế để trả về số lớn nhất trong bộ dữ liệu. Tuy nhiên, trong một số trường hợp, bạn có thể cần đi sâu vào dữ liệu của mình hơn nữa để tìm giá trị tối đa dựa trên các tiêu chí nhất định. Điều này có thể được thực hiện bằng cách sử dụng một vài công thức khác nhau và bài viết này giải thích tất cả các cách có thể. Cho đến gần đây, Microsoft Excel không có chức năng MAX IF tích hợp để có được giá trị tối đa dựa trên các điều kiện. Trước đây, họ đã giới thiệu MAXIFS và bây giờ, người dùng Excel 2019 và Excel 2016 có kèm theo đăng ký Office 365 có thể thực hiện tối đa điều kiện một cách dễ dàng. Trong Excel 2013 và các phiên bản trước đó, bạn vẫn phải tạo riêng cho mình công thức mảng bằng cách kết hợp hàm MAX với câu lệnh IF: {= MAX (NẾU (tiêu chí= =tiêu chí, Phạm vi tối đa))} Để xem công thức MAX IF chung này hoạt động như thế nào trên dữ liệu thực, vui lòng xem xét ví dụ sau. Giả sử, bạn có một bảng với kết quả nhảy xa của nhiều sinh viên. Bảng bao gồm dữ liệu cho ba vòng và bạn đang tìm kiếm kết quả tốt nhất của một vận động viên cụ thể, Jacob nói. Với tên sinh viên trong A2: A10 và khoảng cách trong C2: C10, công thức có hình dạng này:
Hãy nhớ rằng một công thức mảng phải luôn luôn được nhập bằng cách nhấn Ctrl + Shift + Enter Phím đồng thời. Kết quả là, nó được tự động bao quanh với dấu ngoặc nhọn như hiển thị trong ảnh chụp màn hình bên dưới (nhập niềng răng bằng tay sẽ không hoạt động!). Tôi là bảng tính thực tế, sẽ thuận tiện hơn khi nhập tiêu chí vào một số ô, để bạn có thể dễ dàng thay đổi điều kiện mà không cần thay đổi công thức. Vì vậy, chúng tôi nhập tên mong muốn trong F1 và nhận được kết quả sau:
 Công thức này hoạt động như thế nàoTrong bài kiểm tra logic của hàm IF, chúng tôi so sánh danh sách các tên (A2: A10) với tên đích (F1). Kết quả của hoạt động này là một mảng TRUE và FALSE, trong đó các giá trị TRUE biểu thị các tên khớp với tên đích (Jacob):
Cho giá trị_ if_true đối số, chúng tôi cung cấp kết quả nhảy xa (C2: C10), vì vậy nếu kiểm tra logic đánh giá thành TRUE, số tương ứng từ cột C được trả về. Các giá trị_ if_false đối số bị bỏ qua, nghĩa là sẽ chỉ có giá trị FALSE trong đó điều kiện không được đáp ứng:
Mảng này được đưa đến hàm MAX, trả về số lượng tối đa bỏ qua các giá trị FALSE. Tiền boa. Để xem các mảng nội bộ được thảo luận ở trên, chọn phần tương ứng của công thức trong bảng tính của bạn và nhấn nút F9 Chìa khóa. Để thoát chế độ đánh giá công thức, nhấn nút Trốn Chìa khóa. Công thức MAX IF với nhiều tiêu chíTrong trường hợp khi bạn cần tìm giá trị tối đa dựa trên nhiều điều kiện,
bạn có thể: {= MAX (NẾU (iteria_range1= =tiêu chí1, NẾU(iteria_range2= =tiêu chí2, Phạm vi tối đa)))} Hoặc xử lý nhiều tiêu chí bằng cách sử dụng thao tác nhân: {= MAX (NẾU ((iteria_range1= =tiêu chí1) * (iteria_range2= =tiêu chí2), Phạm vi tối đa))} Giả sử bạn có kết quả nam và nữ trong một bảng duy nhất và bạn muốn tìm ra bước nhảy dài nhất trong số các cô gái ở vòng 3. Để hoàn thành, chúng tôi nhập tiêu chí đầu tiên (nữ) vào G1, tiêu chí thứ hai (3) trong G2 và sử dụng các công thức sau để tính giá trị tối đa:
Vì cả hai đều là công thức mảng, hãy nhớ nhấn Ctrl + Shift + Enter để hoàn thành chúng một cách chính xác. Như được hiển thị trong ảnh chụp màn hình bên dưới, các công thức tạo ra kết quả tương tự, do đó, việc sử dụng cái nào là vấn đề sở thích cá nhân của bạn. Đối với tôi, công thức với logic Boolean dễ đọc và xây dựng hơn – nó cho phép thêm bao nhiêu điều kiện bạn muốn mà không cần lồng các hàm IF bổ sung. Những công thức này hoạt động như thế nàoCông thức đầu tiên sử dụng hai hàm IF lồng nhau để đánh giá hai tiêu chí. Trong thử nghiệm logic của câu lệnh IF đầu tiên, chúng tôi so sánh các giá trị trong cột Giới tính (B2: B16) với tiêu chí trong G1 (“Nữ”). Kết quả là một mảng các giá trị TRUE và FALSE trong đó TRUE đại diện cho dữ liệu phù hợp với tiêu chí:
Theo cách tương tự, hàm IF thứ hai kiểm tra các giá trị trong cột Round (C2: C16) theo tiêu chí trong G2. Cho value_if_true đối số trong câu lệnh IF thứ hai, chúng tôi cung cấp kết quả nhảy xa (D2: D16) và bằng cách này, chúng tôi nhận được các mục có TRUE trong hai mảng đầu tiên ở các vị trí tương ứng (ví dụ: các mục có giới tính là “nữ” và làm tròn là 3):
Mảng cuối cùng này đi đến hàm MAX và nó trả về số lớn nhất. Công thức thứ hai đánh giá các điều kiện tương tự trong một thử nghiệm logic đơn và hoạt động nhân giống như toán tử AND: Khi các giá trị TRUE và FALSE được sử dụng trong bất kỳ hoạt động số học nào, chúng sẽ được chuyển đổi thành 1 và 0, tương ứng. Và bởi vì nhân với 0 luôn cho số không, mảng kết quả chỉ có 1 khi tất cả các điều kiện là TRUE. Mảng này được đánh giá trong bài kiểm tra logic của hàm IF, trả về khoảng cách tương ứng với các phần tử 1 (TRUE). MAX IF không có mảngNhiều người dùng Excel, bao gồm cả tôi, có thành kiến với các công thức mảng và cố gắng loại bỏ chúng bất cứ khi nào có thể. May mắn thay, Microsoft Excel có một vài chức năng xử lý mảng nguyên bản và chúng ta có thể sử dụng một trong các chức năng đó, cụ thể là TÓM TẮT, như một loại “trình bao bọc” xung quanh MAX. Công thức MAX IF chung không có mảng như sau: = TỔNG HỢP (MAX ((iteria_range1= =tiêu chí1) * (iteria_range2= =tiêu chí2) * Phạm vi tối đa)) Đương nhiên, bạn có thể thêm nhiều cặp phạm vi / tiêu chí nếu cần. Để xem công thức hoạt động, chúng tôi sẽ sử dụng dữ liệu từ ví dụ trước. Mục đích là để có được cú nhảy tối đa của một vận động viên nữ ở vòng 3:
Công thức này được cạnh tranh với một bình thường Đi vào tổ hợp phím và trả về kết quả giống như công thức MAX IF của mảng: Nhìn kỹ hơn vào ảnh chụp màn hình ở trên, bạn có thể nhận thấy rằng các bước nhảy không hợp lệ được đánh dấu bằng “x” trong các ví dụ trước có 0 giá trị trong các hàng 3, 11 và 15 và phần tiếp theo giải thích lý do. Công thức này hoạt động như thế nàoNhư với công thức MAX IF, chúng tôi đánh giá hai tiêu chí bằng cách so sánh từng giá trị trong các cột Giới tính (B2: B16) và Vòng (C2: C16) với các tiêu chí trong các ô G1 và G2. Kết quả là hai mảng giá trị TRUE và FALSE. Nhân các phần tử của mảng trong cùng một vị trí sẽ chuyển TRUE và FALSE thành 1 và 0 tương ứng, trong đó 1 đại diện cho các mục đáp ứng cả hai tiêu chí. Mảng nhân thứ ba chứa kết quả nhảy xa (D2: D16). Và bởi vì nhân với 0 sẽ cho 0, chỉ các mục có 1 (TRUE) trong các vị trí tương ứng tồn tại:
Trong trường hợp Phạm vi tối đa chứa bất kỳ giá trị văn bản nào, phép toán nhân trả về lỗi #VALUE do toàn bộ công thức sẽ không hoạt động. Hàm MAX lấy nó từ đây và trả về số lớn nhất đáp ứng các điều kiện đã chỉ định. Mảng kết quả bao gồm một phần tử duy nhất {4,63} đi đến hàm SUMPRODVEL và nó xuất ra số lượng tối đa trong một ô. Ghi chú. Do logic cụ thể của nó, công thức hoạt động với các cảnh báo sau:
Công thức Excel MAX IF với logic ORĐể tìm giá trị tối đa khi bất kì trong số các điều kiện đã chỉ định được đáp ứng, hãy sử dụng công thức MAX IF đã quen thuộc với logic Boolean, nhưng thêm các điều kiện thay vì nhân chúng. {= MAX (NẾU ((iteria_range1= =tiêu chí1) + (iteria_range2= =tiêu chí2), Phạm vi tối đa))} Ngoài ra, bạn có thể sử dụng công thức không phải là mảng sau: = TỔNG HỢP (MAX (((iteria_range1= =tiêu chí1) + (iteria_range2= =tiêu chí2)) * Phạm vi tối đa)) Ví dụ: chúng ta hãy tìm ra kết quả tốt nhất trong vòng 2 và 3. Xin lưu ý rằng trong ngôn ngữ Excel, tác vụ được tạo theo cách khác: trả về giá trị tối đa nếu vòng là 2 hoặc 3. Với các vòng được liệt kê trong B2: B10, kết quả trong C2: C10 và tiêu chí ở F1 và H1, công thức diễn ra như sau:
Nhập công thức bằng cách nhấn Ctrl + Shift + Enter tổ hợp phím và bạn sẽ nhận được kết quả này: Giá trị tối đa với cùng điều kiện cũng có thể được tìm thấy bằng cách sử dụng công thức không phải là mảng này:
Tuy nhiên, chúng ta cần thay thế tất cả các giá trị “x” trong cột C bằng các số 0 trong trường hợp này vì SUMPRODVEL MAX chỉ
hoạt động với dữ liệu số: Những công thức này hoạt động như thế nàoCông thức mảng hoạt động chính xác giống như MAX IF với logic AND ngoại trừ việc bạn tham gia các tiêu chí bằng cách sử dụng thao tác bổ sung thay vì nhân. Trong các công thức mảng, phép cộng hoạt động như toán tử OR: Việc thêm hai mảng TRUE và FALSE (kết quả của việc kiểm tra các giá trị trong B2: B10 theo các tiêu chí trong F1 và H1) tạo ra một mảng 1 và 0 trong đó 1 đại diện cho các mục mà điều kiện là TRUE và 0 đại diện cho các mục trong đó cả hai điều kiện là FALSE. Kết quả là, hàm IF “giữ” tất cả các mục trong C2: C10 (value_if_true) với bất kỳ điều kiện nào là TRUE (1); các mục còn lại được thay thế bằng FALSE vì value_if_false đối số không được chỉ định. Công thức không mảng hoạt động theo cách tương tự. Sự khác biệt là thay vì kiểm tra logic của IF, bạn nhân các phần tử của mảng 1 và 0 với các phần tử của mảng kết quả nhảy xa (C2: C10) ở các vị trí tương ứng. Điều này vô hiệu hóa các mục không đáp ứng bất kỳ điều kiện nào (có 0 trong mảng đầu tiên) và giữ các mục đáp ứng một trong các điều kiện (có 1 trong mảng đầu tiên). MAXIFS – cách dễ dàng để tìm giá trị cao nhất với các điều kiệnNgười dùng Excel 2019 và Excel cho Office 365 không gặp rắc rối trong việc thuần hóa mảng để xây dựng công thức MAX IF của riêng họ. Các phiên bản Excel này cung cấp hàm MAXIFS được chờ đợi từ lâu, giúp tìm giá trị lớn nhất với điều kiện chơi của trẻ. Trong đối số đầu tiên của MAXIFS, bạn nhập phạm vi cần tìm giá trị tối đa (D2: D16 trong trường hợp của chúng tôi) và trong các đối số tiếp theo, bạn có thể nhập tối đa 126 cặp phạm vi / tiêu chí. Ví dụ:
Như được hiển thị trong ảnh chụp màn hình bên dưới, công thức đơn giản này không có vấn đề gì với việc xử lý phạm vi chứa cả giá trị
số và văn bản: Để biết thông tin chi tiết về chức năng này, vui lòng xem Hàm MAXIFS với các ví dụ về công thức. Đó là cách bạn có thể tìm thấy giá trị tối đa với các điều kiện trong Excel. Tôi cảm ơn bạn đã đọc và hy vọng sẽ gặp bạn trên blog của dịch vụ kế toán trọn gói – ketoanmvb vào tuần tới! |
