Có thể có những lúc bạn cần lấy giá trị nhỏ nhất (hoặc lớn nhất)

giá trị từ một phạm vi, trừ khi giá trị nhỏ nhất (hoặc lớn nhất) bằng 0. Ví dụ: bạn có thể có một loạt các giá trị như \ {0, 3, 1, 4, 2}. Trong trường hợp này, giá trị thấp nhất là 0, nhưng giá trị bạn thực sự muốn trả về là 1.

Không có hàm nội tại nào trong Excel để trả về một giá trị như quy định ở đây. Tuy nhiên, bạn có thể tạo một công thức để thực hiện thủ thuật. Giả sử rằng phạm vi giá trị bạn muốn phân tích nằm trong C4: C8, công thức sau sẽ trả về giá trị khác 0 thấp nhất:

=IF(MIN(C4:C8)=0,SMALL(C4:C8,COUNTIF(C4:C8,"=0")+1),MIN(C4:C8))

Công thức này sử dụng hàm MIN để xác định xem giá trị thấp nhất trong phạm vi có bằng 0 hay không. Nếu đúng như vậy, thì hàm SMALL được sử dụng để lấy giá trị thấp nhất, loại trừ các số không. (Hàm COUNTIF trả về số lượng các số không trong phạm vi và do đó cho SMALL biết mục nào từ phạm vi cần chọn.)

Một thay đổi nhỏ đối với công thức cho phép nó được sử dụng để trả về số khác 0 lớn nhất trong một dải:

=IF(MAX(C4:C8)=0,LARGE(C4:C8,COUNTIF(C4:C8,"=0")+1),MAX(C4:C8))

Các công thức này sẽ hoạt động cho bất kỳ phạm vi nào, trừ khi phạm vi được tạo thành hoàn toàn từ các số không. Trong trường hợp đó, lỗi #NUM! lỗi được trả lại.

Nếu bạn đang sử dụng Excel 2019 hoặc Excel trong Office 365, bạn có thể sử dụng hàm MINIFS mới. Nó sẽ được sử dụng theo cách này cho ví dụ này:

=MINIFS(C4:C8,C4:C8,"<>"&0)

Bạn có thể tìm thêm thông tin về hàm MINIFS trên trang hỗ trợ Microsoft Office này:

https://support.office.com/en-gb/article/minifs-function-6ca1ddaa-079b-4e74-80cc-72eef32e6599

Nếu bạn thích sử dụng công thức mảng, thì bạn có thể làm cho công thức ngắn hơn nhiều. Phiên bản này trả về giá trị khác 0 thấp nhất:

=MIN(IF(C4:C8=0,9^9,C4:C8))

Hãy nhớ nhập nó bằng Ctrl + Shift + Enter. Nó cũng gặp sự cố nếu tất cả các giá trị trong phạm vi là 0; trong trường hợp đó, nó trả về 387420489, là 9 ^ 9. (Nó cũng sẽ trả về giá trị đó nếu tất cả các giá trị trong phạm vi lớn hơn 387420489.)

ExcelTips là nguồn của bạn để đào tạo Microsoft Excel hiệu quả về chi phí.

Mẹo này (9750) áp dụng cho Microsoft Excel 2007, 2010, 2013, 2016, 2019 và Excel trong Office 365. Bạn có thể tìm thấy phiên bản của mẹo này cho giao diện menu cũ hơn của Excel tại đây: