跳到主要內容

如何在Excel中查找除零值以外的範圍內的最小值？

通常，零值應被認為是正數中的最小值。 但是在某些情況下，您需要在除零值之外的範圍內找到最小值。 本文將向您展示在Excel中查找最小值（不包括零）的方法。

用公式找出不包括零的最小值

箭頭藍色右氣泡 用公式找出不包括零的最小值

假設您的數字值位於A1：E7範圍內，如下圖所示。 請應用以下公式以獲取該範圍內的最小值（Excel中不包括零）。

1.選擇一個空白單元格（H1）作為最小值，然後輸入公式 =SMALL(A1:E7,COUNTIF($A$1:$E$7,0)+1) 進入編輯欄，然後按 Enter 鍵。

然後在選定的單元格中填充指定範圍的最小值（不包括零），如上圖所示。

備註：您也可以使用數組公式 =MIN(IF($A$1:$E$7 <>0,$A$1:$E$7)) + 按Ctrl + 轉移 + Enter 鍵以獲取Excel中不包含零的範圍的最小值。

最佳辦公生產力工具

🤖 Kutools 人工智慧助手：基於以下內容徹底改變數據分析： 智慧執行   |  生成代碼  |  建立自訂公式  |  分析數據並產生圖表  |  呼叫 Kutools 函數...
熱門特色: 尋找、突出顯示或識別重複項   |  刪除空白行   |  合併列或儲存格而不遺失數據   |   沒有公式的回合 ...
超級查詢: 多條件VLookup  |   多值VLookup  |   跨多個工作表的 VLookup   |   模糊查詢 ....
高級下拉列表: 快速建立下拉列表   |  依賴下拉列表   |  多選下拉列表 ....
欄目經理: 新增特定數量的列  |  移動列  |  切換隱藏列的可見性狀態  |  比較範圍和列 ...
特色功能: 網格焦點   |  設計圖   |   大方程式酒吧   |  工作簿和工作表管理器   |  資源庫 （自動文字）   |  日期選擇器   |  合併工作表   |  加密/解密單元格   |  按清單發送電子郵件   |  超級濾鏡   |   特殊過濾器 （過濾粗體/斜體/刪除線...）...
前 15 個工具集:  12 文本 工具 (添加文本, 刪除字符，...）   |   50+ 圖表 類型 (甘特圖，...）   |   40+ 實用 公式 (根據生日計算年齡，...）   |   19 插入 工具 (插入二維碼, 從路徑插入圖片，...）   |   12 轉化 工具 (數字到單詞, 貨幣兌換，...）   |   7 合併與拆分 工具 (高級合併行, 分裂細胞，...）   |   ... 和更多

使用 Kutools for Excel 增強您的 Excel 技能，體驗前所未有的效率。 Kutools for Excel 提供了 300 多種進階功能來提高生產力並節省時間。  點擊此處獲取您最需要的功能...

產品描述

Office選項卡為Office帶來了選項卡式界面，使您的工作更加輕鬆

  • 在Word，Excel，PowerPoint中啟用選項卡式編輯和閱讀，發布者，Access，Visio和Project。
  • 在同一窗口的新選項卡中而不是在新窗口中打開並創建多個文檔。
  • 將您的工作效率提高 50%，每天為您減少數百次鼠標點擊！
Comments (29)
Rated 5 out of 5 · 1 ratings
Denysptwn
Denysptwn
This comment was minimized by the moderator on the site
Saya mencari nilai alpa predikat dan tentunya nilai 0 sangat dibutuhkan, kenapa fungsi min tidak mendeteksi nilai 0 di excel saya?
Reply
0
0
crystal
crystal     Denysptwn
This comment was minimized by the moderator on the site
Hi Denysptwn,
The formula in this post helps to find the smallest value excluding zero.
If you want to include zero in the result, just use this formula: =MIN($A$1:$C$7)
Reply
Report
0
0
JavidP
JavidP
This comment was minimized by the moderator on the site
Hi Dear Srystal
Many thanks for you kindness and the time you spend for my problem and for solving it,
I didn't knew this way and this is very useful for future working.
As i have more than 700 rows in my sheet and needed to sort my data too and because of some merged cells it was very hard to do it, therefore i askd fir help to have some VBA code and some kind friends kindly sent me a code and soled my problem.
I am Sorry, I forgot to let you know that the problem has been resolved.
Thank you again and hope to be able to repay the love of all of you friends.
Good luck dear
Sincerely yours, Javid
Reply
Report
0
0
crystal
crystal     JavidP
This comment was minimized by the moderator on the site
Hi Javid,
You may receive an error message when you sort a range that contains merged cells in Excel. There is no directly way to handle this issue. You need to manually unmerge the merged cells first to sort all cells. You can following the methods in the following article. Hope I can help. Thank you for your comment.
How To Sort Data With Merged Cells In Excel?
Reply
Report
1
0
JavidP
JavidP     crystal
This comment was minimized by the moderator on the site
Hi dear Crystal,
Good to see your comment again :)
Many Thanks for your useful information and suggestion.
After facing many problems, I decided to change my strategy and the first step was to remove the merged cells and replace them with new cells and new numbers.
I'm making some changes, including the one I mentioned.
I hope I will face less problems from now on.
Thanks again with love,
Have a nice times.
Reply
Report
0
0
Rated 5 out of 5
JavidP
JavidP
This comment was minimized by the moderator on the site
Hi
Can anyone help me to use formula to find small value except zero and highlight it with conditional format?
Reply
Report
0
0
crystal
crystal     JavidP
This comment was minimized by the moderator on the site
Hi,
Please follow the steps below to get it done.
1. Click Home > Conditional Formatting > New Rule.
2. In the Edit Formatting Rule dialog box, select Format only cells that contain in the Select a Rule Type list box; Select equal to in the second drop-down list, and then enter the formula below (don't forget to change the cell references):
=SMALL($F$5:$O$21, COUNTIF($F$5:$O$21, "<=0")+1)
3. Click the Format button to specify a fill color to highlight the cells.
4. Click OK.
See the uploaded screenshot below.
View Attachments (1 / 5)
Reply
Report
1
0
Zingo
Zingo
This comment was minimized by the moderator on the site
Hi, how to search for the minimum of values present in multiple matrices, always excluding 0 if the matrices are on different sheets?
Reply
Report
0
0
crystal
crystal     Zingo
This comment was minimized by the moderator on the site
Hi Zingo,Sorry I can't help to solve this problem. Here suggest you post the problem to the forum below to get help from other Excel enthusiasts. https://www.extendoffice.com/forum/kutools-for-excel.html 
Reply
Report
1
0
Justin
Justin
This comment was minimized by the moderator on the site
I have a formula that looks the the lest number in a seperate workbook, but if the data is not fully entered or collected yet, the return value is 0, rather than the smallest number. is there a way to solve this via the formula or a feature in exceel?
=MIN(IF('Input Data 54 mm'!$D:$D='Index Values 54 mm'!$A7,('Input Data 54 mm'!J:J)*95%))
Reply
0
0
crystal
crystal     Justin
This comment was minimized by the moderator on the site
Hi,
Sorry I can't solve this problem. You can post the problem to the forum below to get help from other Excel enthusiasts.
https://www.extendoffice.com/forum/kutools-for-excel.html 
Reply
Report
0
0
Dop
Dop
This comment was minimized by the moderator on the site
How do I find the small/lowest number that is not 0 based on certain conditions, e.g.: find the minimum number in a row, based on the columns with "covid"?
I typed this =MIN(IF($C$8:$AF$8=$AK$3,C10:AF10<>0,C10:AF10)) but it doesn't evaluate this $C$8:$AF$8=$AK$3, it only returns C10:AF10<>0,C10:AF10. where AK3 = covid.
Reply
Report
0
0
Hafeez ur rehman
Hafeez ur rehman
This comment was minimized by the moderator on the site
what to do if you have zeros in a row. for example comparative statement with not quoted items?
Reply
0
0
francis
francis
This comment was minimized by the moderator on the site
update on the formula from Crystal: the formula works but the it counts the blank cells as the lowest in the range. how do we fix the formula not to count for zero value or empty cells?
Reply
0
0
francis
francis
This comment was minimized by the moderator on the site
Crystal, you are amazing! thank you thank you so much for the help :-) made my life/work easier
Reply
0
0
francis
francis
This comment was minimized by the moderator on the site
This was very useful. how do we revise the formula that instead of giving me the lowest value, it would identify under which heading is the lowest value on. Scenario is i have 3 suppliers, providing 3 different prices. i would like the formula to tell me which supplier has the lowest price (i.e. supplier 1, supplier 2, etc) and gives me that supplier's name so i could sort it later. Thank you
Reply
0
0
crystal
crystal     francis
This comment was minimized by the moderator on the site
Hi francis,
Please apply this formula: =INDEX($A$1:$C$1,0,MATCH(MIN($A2:$C2),$A2:$C2,0)) as the below screenshot shown.
View Attachments (1 / 5)
Reply
Report
0
0
octavia
octavia     crystal
This comment was minimized by the moderator on the site
Hi Crystal, how to excludie zero value in this formula
Reply
0
0
There are no comments posted here yet
Load More
Please leave your comments in English
Guest
Posting as Guest
×
Rate this post:
Reset
0   Characters
Suggested Locations