Hướng dẫn cách dùng và ví dụ về hàm MAX có điều kiện trong Excel

Hướng dẫn cách dùng và ví dụ chi tiết về hàm MAX có điều kiện trong Excel. Bạn đã biết cấu trúc, cách sử dụng khi dùng hàm MAX để thay thế cho các hàm IF trong Excel như thế nào chưa?

Bạn đã học Excel thành thạo chưa? Trong quá trình tích lũy những thủ thuật Excel hữu ích, bạn đã biết cách dùng hàm Max hoặc Min để thay thế cho hàm IF chưa? Hãy cùng tìm hiểu hàm max có điều kiện trong Excel để hiểu hơn về cách sử dụng hàm này trong các hàm cơ bản của excel tin học văn phòng. Chắc chắn bài viết này sẽ hữu ích đối với những người là sinh viên, nhân viên văn phòng hoặc làm những công việc của nhân viên văn phòng nói chung. 

 I. Định nghĩa hàm max có điều kiện MAXIFS

Định nghĩa hàm max có điều kiện MAXIFS

1. Cú pháp hàm max có điều kiện MAXIFS

=MAXIFS(max_range,criteria_range1,criteria1,[criteria_range2, criteria2],…)

Trong đó, các đối số của hàm max có điều kiện là:

• max_range: mảng các giá trị số để từ đó trả lại giá trị lớn nhất nếu điều kiện được thỏa mãn.
• criteria_range1: mảng các giá trị được kiểm tra dựa trên criteria1 (mảng này nhất định phải có cùng số lượng ô giá trị với max_range).
• criteria1: điều kiện được mang ra kiểm tra so với những giá trị trong criteria_range1.
• [criteria_range2, criteria2],[criteria_range3, criteria3]: những mảng giá trị khác (nếu có) được kiểm tra và những điều kiện được kiểm tra tương ứng.

2. Cách sử dụng hàm max có điều kiện MAXIFS

Hàm hàm Max có điều kiện MAXIFS trong Excel sẽ trả về giá trị cực đại được lấy từ một tập hợp con những giá trị được xác định theo một hay nhiều tiêu chí.

3. Một số lỗi liên quan đến hàm max có điều kiện MAXIFS

Một số lỗi gặp phải trong quá trình học excel và thực hành hàm max có điều kiện đó là:

• #VALUE!: Lỗi này xảy ra nếu những mảng max_range và criteria_range được cung cấp trong công thức hàm max có điều kiện MAXIFS không có độ dài bằng nhau.
• #NAME?: Lỗi này xảy ra nếu người dùng đang sử dụng các phiên bản Excel cũ (trước phiên bản 2016) và không được hỗ trợ hàm max có điều kiện MAXIFS.

II. Hướng dẫn sử dụng hàm MAX thay thế IF 

Hàm max có điều kiện - sử dụng hàm MAX thay thế IF

1. Dùng hàm MAX thay IF trong trường hợp thẻ giảm giá 

Giả sử bạn đang có một Voucher trị giá 100.000đ. Khi tiến hành thanh toán, nhân viên sẽ thực hiện tính xem bạn cần phải trả thêm bao nhiêu tiền bằng cách dùng công thức:

Số tiền còn lại phải trả = Tổng hóa đơn – Giá trị voucher 

Trong trường hợp tổng hóa đơn lớn hơn giá trị voucher thì tiến hành thanh toán bình thường. Tuy nhiên, khi tổng hóa đơn nhỏ hơn giá trị voucher, bạn cần phải trả âm tiền. Lúc này chúng ta hiểu rằng người bạn không cần trả thêm tiền, tuy nhiên, kết quả mà excel hiện là số tiền âm thì không phù hợp. Vậy những người học excel nên dùng thủ thuật excel thì để giải quyết vấn đề này?

Hàm max có điều kiện - Dùng hàm MAX thay hàm IF

Lúc này cần thiết lập điều kiện. Đối với điều kiện khi học excel và thực hành với nó thì người dùng phải nhớ đến hàm IF. Đây là công thức sử dụng trong trường hợp này của hàm IF:

=IF(tổng hóa đơn – voucher > 0, tổng hóa đơn – voucher, 0) với ý nghĩa là nếu tổng hóa đơn trừ đi voucher ra một số lớn hơn 0 thì máy tính hiện kết quả là con số lớn hơn 0 đó, còn nếu không thì tại ô đó giá trị hiện sẽ là 0 như trong ảnh:

Hàm max có điều kiện - Dùng hàm hàm IF

Đây đã phải là cách ngắn nhất chưa? Trong trường hợp này, có một thủ thuật excel được áp dụng nhiều hơn đó là dùng công thức  hàm MAX thì công việc sẽ đơn giản hơn nhiều.

Chuyển từ tư duy: Nếu tổng hóa đơn – voucher > 0 thì sẽ hiển thị kết quả lớn hơn 0 đó, ngược lại hiển thị 0 thành tư duy so sánh tổng hóa đơn – voucher với 0 và kết quả trả về là số lớn hơn. Từ đó, ta có hàm MAX như sau: =MAX(tổng hóa đơn – voucher,0) như trong ảnh

Hàm max có điều kiện - Dùng hàm hàm MAX

2. Sử dụng hàm MAX và MIN thay thế hàm IF lồng nhau – Câu chuyện “xin” điểm

Ngoài hàm MAX thay thế hàm IF, người dùng có thể sử dụng hàm MIN trong một vài trường hợp cụ thể và cũng có thể dùng kết hợp cả hàm MAX với MIN để thay thế hàm IF lồng nhau.

Chắc hẳn trong quá trình học tập bạn đều trải qua chuyện nâng điểm cho học sinh sinh viên.  Ví dụ nhằm đạt chỉ tiêu về học sinh khá và nâng đỡ học sinh có điểm thấp trong học tập,giáo viên B đã đưa ra ý kiến: Nếu điểm thi trên 6 sẽ giữ nguyên, còn nếu điểm dưới 6 thì được cộng 1 điểm và số điểm sau cộng không vượt qua 6. Có thể hiểu như sau: nếu 7 điểm thì giữ nguyên 7, nếu 5.5 thì được lên 6, nếu 4 điểm thì được lên 5.

Trong trường hợp này, khi học excel thì người dùng thiết lập điều kiện như sau: =IF(Điểm SV1> 6, Điểm SV1, IF(Điểm SV1 +1 > 6, 6, Điểm SV1+1)) và có kết quả sau:

Hàm max có điều kiện -  sử dụng hàm IF

Hãy xem khi sử dụng thủ thuật Excel ta có thể rút gọn công thức này bằng cách dùng MIN và MAX như sau: =MAX(Điểm SV1, MIN(Điểm SV1, 6)) và được thể hiện qua công thức là:

Hàm max có điều kiện -  sử dụng hàm MAX và MIN

III. Tìm giá trị nhỏ nhất dùng hàm MINIFS 

Cũng tương tự như hàm max có điều kiện MAXIFS, hàm MINIFS cũng có công thức tương tự = MINIFS(min_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)

• min_range: mảng các giá trị số để từ đó trả lại giá trị nhỏ nhất nếu điều kiện được thỏa mãn.
• criteria_range1: mảng các giá trị được kiểm tra dựa trên criteria1 (mảng này nhất định phải có cùng số lượng ô giá trị với min_range).
• criteria1: điều kiện được mang ra kiểm tra so với những giá trị trong criteria_range1.
• [criteria_range2, criteria2],[criteria_range3, criteria3]: những mảng giá trị khác (nếu có) được kiểm tra và những điều kiện được kiểm tra tương ứng.

IV. 7 cách giúp thực hành Excel hiệu quả hơn

1. Dùng mẫu có sẵn trong quá trình học Excel và thực hành (template)

Người dùng Excel không phải lúc nào cũng có nhiều thời gian cho thành lập các bảng excel, do vậy, sử dụng các template có sẵn là một trong những sự lựa chọn. Excel cung cấp cho người dùng template như dự án, lịch, bảng ngân sách, hóa đơn giúp tiết kiệm công sức, thời gian và trình bày khoa học.

2. Cố định cột và hàng khi sử dụng Excel

Muốn cố định hàng và cột khi dùng Excl thì ta làm như sau: Vào View --> nhấn Freeze Panes nằm trên dải Ribbon. Trong menu thả xuống có các lựa chọn:

• Freezze Panes: đóng băng cả hàng trên cùng và cột đầu tiên
• Freeze Top Row: đóng băng hàng trên cùng 
• Freeze First Colum: đóng băng cột đầu tiên

3. Dùng Fill Handle

Fill Hand là dấu cộng mù đen khi người dùng đặt con trỏ chuột vào góc dưới bên phải của ô. Thủ thuật Excel này được sử dụng nhiều khi muốn tạo một danh sách số thứ tự theo một quy luật như cách đều 1 đơn vị, 2 đơn vị, số lẻ, số chẵn, 0.1 đơn vị hay muốn điều tất cả các công thức tương tự công thức đầu tiên cho các ô phía dưới mà không cần lặp lại công thức tương tự.

4. Chuyển đổi giữa cột và hàng khi sử dụng Excel

Khi người dùng lập bảng nhưng lại phát hiện nếu thay đổ vị trí hàng và cột thì bảng trông trực quan hơn, khi này có thể làm các bước như sau:

• Bôi đen toàn bộ bảng muốn đổi cột và hàng
• Nhấn trở chuột sang khoảng trống đủ để đặt bảng mới, nhấn chuột phải chọn Paste Special.
• Hộp thoại Paste Special hiện ra, nhấn và ô trống Transpose ở góc dưới bên phải và nhấn OK để thực hiện 

5. Truy cập máy tính khi sử dụng Excel

Thêm máy tính vào Excel ta chọn File > Options >

• chọn Customize Ribbon: thêm máy tính vào dải Ribbon 
• Quick Access Toolbar: thêm vào Quick Access

Trong Choose commands from: > All Commands > Calculator và nhấn Add >> để thêm máy tính vào thanh công cụ Quick Access. Nếu thêm vào Ribbon thì người dùng cần phải tạo một nhóm tùy chỉnh rồi thêm máy tính vào đó.

6. Liên kết các tab hoặc các cột

Nếu file Excel có chứa nhiều bảng tính với dữ liệu sử dụng được tham chiếu chéo thì việc tạo ra liên kết trực tiếp sẽ giúp truy cập nhanh hơn. Điều này còn đặc biệt hữu ích nếu cần chia sẻ với những người khác để có thể di chuyển đến ô dữ liệu được tham chiếu nhanh chóng.

Để tạo liên kết người dùng thực hiện theo các bước dưới đây :

• Chọn ô chứa dữ liệu muốn liên kết.
• Nhấn chuột phải chọn Hyperlink 
• Trong cửa sổ pop-up --> chọn Place in this Document.
• Sau đó nhập văn bản hiển thị trong ô, nhập tham chiếu ô cụ thể nếu muốn và bảng tính trong tệp Excel với dữ liệu đang liên kết đến và nhấn OK

7. Sử dụng phím tắt của Excel

Những phím tắt người dùng bắt buộc phải biết khi học excel để các thủ thuật excel được thực hiện chính xác:

• F4 hoặc Fn + F4 để lặp lại lệnh cuối cùng tực hiện
• Alt + H, E, A để xóa nội dung, định dạng, dữ liệu đính kèm khác trong ô giá trị được chọn
• Ctrl + W: đóng bảng tính và Ctrl + O: mở bảng mới
• Ctrl + Shift + : nhập thời gian hiện tại và Ctrl + Shift + ; nhập vào ngày hiện tại
• Shift + F9: tính các bảng tính đang hoạt động
• Shift + Tab: di chuyển đến ô trước hay tùy chọn
• Ctrl + Home: đi đến đầu bảng tính
• Ctrl + Page Down: đi đến bảng tiếp theo trong file Excel
• Ctrl + Page Up đi đến bảng tính trước đó

V. Kết luận

Ngoài các hàm cơ bản đã được giới thiệu trong các bài học Excel trước hay trong những thủ thuật excel cơ bản cần biết như: kết hợp hàm Vlookup và hàm IF, vẽ biểu đồ trong Excel, sử dụng Data Validation,  hàm COUNT nâng cao, các hàm đếm trong Excel thì việc hiểu biết kỹ hàm max có điều kiện sẽ mang lại cho người dùng nhiều kỹ năng tin học văn phòng chuyên nghiệp.