Hàm MAXIFS trong Excel
Hàm MAXIFS của Excel được sử dụng để lấy các giá trị lớn nhất dựa trên các tiêu chí khác nhau. Đôi khi, chúng tôi phải phân tích dữ liệu dựa trên các tiêu chí. Chúng tôi có thể sử dụng MAXIFS với tiêu chí dựa trên ngày, số, văn bản và các điều kiện khác.
Chúng ta có thể sử dụng tất cả các loại toán tử logic như>, =, <= ,, = và các ký tự đại diện như *,? để khớp từng phần. Điều kiện “B? E” sẽ khớp với tất cả các ô chứa chuỗi văn bản bắt đầu bằng “A” và kết thúc bằng “e” và chỉ được có một ký tự ở giữa.
Điều kiện “H * e” sẽ khớp với tất cả các ô chứa chuỗi văn bản bắt đầu bằng “H” và kết thúc bằng “e” với nhiều ký tự ở giữa. Việc tìm ra điểm tối đa cho học sinh nữ và nam riêng biệt cho một lớp là một trong những tình huống có thể sử dụng chức năng này.
Cú pháp
Công thức cho hàm này là:
Hàm MAXIFS trả về giá trị lớn nhất từ một phạm vi dựa trên nhiều tiêu chí để kiểm tra trong nhiều phạm vi.
Giải thích đối số
- Phạm vi tối đa: Đây là phạm vi từ đó chúng ta cần tìm ra giá trị lớn nhất.
- Phạm vi 1: Đây là phạm vi mà từ đó chúng tôi sẽ so sánh các giá trị với tiêu chí để nhận được giá trị lớn nhất.
- Tiêu chí 1: Đây là đối số cho điều kiện để chỉ định.
- Phạm vi 2: Đây là một phạm vi khác, trong đó chúng tôi sẽ so sánh các giá trị với tiêu chí để nhận được giá trị lớn nhất. Bằng cách này, chúng tôi có thể cung cấp 126 cặp phạm vi / tiêu chí.
- Tiêu chí 2: Đây là đối số cho điều kiện thứ hai để chỉ định.
Làm thế nào để sử dụng hàm MAXIFS trong Excel?
Hãy để chúng tôi hiểu về hàm MAXIFS với một số ví dụ trong Excel.
Ví dụ 1
Giả sử chúng ta có dữ liệu sau về học sinh của một lớp mà chúng ta có điểm của học sinh với chi tiết giới tính của họ.
Để trao giải cho học sinh, chúng ta cần tìm điểm tối đa cho nam và nữ riêng biệt.
Để tìm, các bước sẽ như sau:
Chúng tôi đã tạo định dạng sau để hiển thị điểm tối đa.
Bây giờ, để đạt điểm tối đa cho học sinh nữ và nam, chúng tôi sẽ áp dụng công thức MAXIFS.
- Như chúng ta đã biết về cú pháp, chúng ta sẽ tham chiếu đến các ô mà dấu được viết cho đối số đầu tiên.
- Bây giờ chúng ta cần chọn phạm vi cho đối số thứ hai, nơi chúng ta cần kiểm tra các tiêu chí.
- Đối với đối số thứ ba, chúng tôi sẽ tham chiếu đến ô bên trái của ô công thức. Một ô bên trái chứa 'Nữ', là giới tính mà chúng ta cần so sánh đầu tiên và sau đó lấy điểm tối đa.
Bây giờ chúng ta sẽ đóng dấu ngoặc cho hàm và sẽ lấy điểm tối đa của một nữ sinh.
Như chúng ta thấy trong hàm trên, chúng ta đã sử dụng tham chiếu ô tương đối trong excel cho cả ba đối số, có nghĩa là nếu chúng ta sao chép và dán hàm vào một ô khác, thì tham chiếu sẽ bị thay đổi. Nếu chúng ta muốn giữ nguyên tham chiếu, thì chúng ta cần sử dụng tham chiếu tuyệt đối .
- Để làm cho tham chiếu là tuyệt đối, chúng tôi sẽ sử dụng dấu '$' trước bảng chữ cái cột và số hàng.
- Vì vậy, điểm tối đa của Nữ sinh là 82.
- Bây giờ chúng ta sẽ sao chép công thức và dán nó vào ô dành cho học sinh nam. Đảm bảo rằng chúng tôi sử dụng 'Chỉ dán công thức' của 'Dán đặc biệt' vì chúng tôi cũng không muốn sao chép định dạng.
Một nam sinh cũng đạt điểm tối đa, là 91.
Ví dụ số 2
Trong phiên bản Excel trước, chúng tôi không có hàm MAXIFS làm hàm tích hợp sẵn. Trong trường hợp đó, chúng tôi đang sử dụng công thức mảng dựa trên Hàm MAX và IF .
Giả sử chúng ta có dữ liệu sau cho một tổ chức.
Trong dữ liệu này, chúng ta có thể thấy rằng các mặt hàng khác nhau đã được bán bởi nhân viên bán hàng ở các khu vực khác nhau. Nếu chúng ta muốn tìm số lượng tối đa của một mặt hàng đã bán, thì chúng ta có thể sử dụng công thức mảng dựa trên Hàm MAX và IF .
Các bước:
Chúng ta cần tạo danh sách thả xuống cho các tên mục để người dùng có thể chọn từ danh sách; anh ta sẽ không phải nhập vì có thể có lỗi chính tả trong khi nhập. Để tạo danh sách sổ xuống trong excel, trước tiên chúng ta cần tạo định dạng.
Để tạo danh sách thả xuống, chúng tôi sẽ chọn ô và sau đó nhấp vào lệnh 'Xác thực Dữ liệu trong Excel' có sẵn trong nhóm 'Công cụ Dữ liệu' trong 'Dữ liệu'.
Chọn 'Danh sách' cho tùy chọn 'Cho phép' , nhập tên mục vào hộp văn bản 'Nguồn' và nhấp vào 'OK.'
Một danh sách thả xuống sẽ được tạo như sau:
Bây giờ để có được doanh số bán hàng tối đa, chúng tôi sẽ áp dụng công thức như sau:
Trong khi nhập công thức, chúng ta cần nhấn Ctrl + Shift + Enter vì đây là công thức mảng.
Vì chúng tôi đã đưa ra một phạm vi gồm nhiều ô cho 'logic_test' cho hàm IF và đã so sánh cùng với Tên mục được viết trong ô ở trên, hàm IF trong excel sẽ trả về nhiều hơn một giá trị và sau đó hàm MAX sẽ loại bỏ giá trị lớn nhất. Vì hàm IF trả về nhiều hơn một giá trị, đó là lý do tại sao nó được gọi là công thức mảng trong excel.
Ví dụ # 3
Giả sử chúng ta cũng muốn tìm ra doanh số bán hàng tối đa cho một mặt hàng dựa trên khu vực. Để làm tương tự, chúng ta sẽ sử dụng công thức NESTED IF với hàm MAX.
Các bước sẽ là:
Đầu tiên, chúng ta cần tạo định dạng như sau:
Chúng ta cần tạo menu thả xuống cho 'Tên mặt hàng' và 'Khu vực.'
Bây giờ chúng ta sẽ áp dụng công thức mảng dựa trên NESTED IF trong Excel và hàm MAX.
Đầu tiên, chúng ta cần so sánh tên mặt hàng với giá trị được viết bằng 'G7' và sau đó là khu vực với giá trị được viết bằng 'G8' . Hàm IF sẽ trả về giá trị cho một mục và khu vực cụ thể. Hàm MAX sẽ trả về giá trị lớn nhất.
Doanh số tối đa của Bàn phím ở khu vực phía Tây là 34.
Đảm bảo rằng chúng tôi nhấn Ctrl + Shift + Enter khi nhập công thức vì đây là công thức mảng.
Bây giờ chúng tôi sẽ chọn tên mặt hàng và khu vực để tìm ra doanh số bán hàng tối đa.
Những điều cần ghi nhớ
- Trong khi nhập công thức mảng, hãy nhấn đồng thời Control Shift-Enter trong Excel .
- Với các hàm IF lồng nhau và MAX, chúng ta có thể chỉ định bất kỳ số lượng điều kiện nào để nhận giá trị lớn nhất.
- Kích thước và hình dạng của các đối số max_range và tiêu chí_rangeN phải giống nhau; nếu không, các hàm MAXIFS trả về giá trị lỗi #VALUE! Lỗi.
