Direkt zum Inhalt

Wie finde ich den Mindestwert in einem Bereich ohne Nullwert in Excel?

Normalerweise soll der Wert Null der Mindestwert unter den positiven Zahlen sein. In einigen Fällen müssen Sie jedoch den Mindestwert in einem Bereich ohne den Nullwert finden. Dieser Artikel zeigt Ihnen die Methode zum Ermitteln des Mindestwerts ohne Null in Excel.

Finden Sie den Mindestwert ohne Null mit der Formel

Pfeil blau rechte Blase Finden Sie den Mindestwert ohne Null mit der Formel

Angenommen, Ihre Zahlenwerte befinden sich im Bereich A1: E7 (siehe Abbildung unten). Wenden Sie die folgende Formel an, um den Mindestwert in diesem Bereich ohne Null in Excel zu erhalten.

1. Wählen Sie eine leere Zelle (H1) aus, um den Mindestwert zu platzieren, und geben Sie die Formel ein =SMALL(A1:E7,COUNTIF($A$1:$E$7,0)+1) in die Formelleiste und drücken Sie dann die Enter Key.

Dann wird der Mindestwert des angegebenen Bereichs ohne Null in die ausgewählte Zelle eingetragen, wie im obigen Screenshot gezeigt.

Note: Sie können auch die Array-Formel verwenden =MIN(IF($A$1:$E$7 <>0,$A$1:$E$7)) + Ctrl + Shift + Enter Tasten, um den Mindestwert eines Bereichs ohne Null in Excel abzurufen.

Beste Office-Produktivitätstools

🤖 Kutools KI-Assistent: Revolutionieren Sie die Datenanalyse basierend auf: Intelligente Ausführung   |  Code generieren  |  Erstellen Sie benutzerdefinierte Formeln  |  Analysieren Sie Daten und erstellen Sie Diagramme  |  Rufen Sie Kutools-Funktionen auf...
Beliebte Funktionen: Suchen, markieren oder identifizieren Sie Duplikate   |  Leere Zeilen löschen   |  Kombinieren Sie Spalten oder Zellen, ohne Daten zu verlieren   |   Runde ohne Formel ...
Super-Lookup: VLookup mit mehreren Kriterien  |   VLookup mit mehreren Werten  |   VLookup über mehrere Blätter hinweg   |   Unscharfe Suche ....
Erweiterte Dropdown-Liste: Erstellen Sie schnell eine Dropdown-Liste   |  Abhängige Dropdown-Liste   |  Mehrfachauswahl Dropdown-Liste ....
Spaltenmanager: Fügen Sie eine bestimmte Anzahl von Spalten hinzu  |  Spalten verschieben  |  Schalten Sie den Sichtbarkeitsstatus ausgeblendeter Spalten um  |  Vergleichen Sie Bereiche und Spalten ...
Ausgewählte Funktionen: Rasterfokus   |  Designansicht   |   Große Formelleiste   |  Arbeitsmappen- und Blattmanager   |  Ressourcen (Autotext)   |  Datumsauswahl   |  Arbeitsblätter kombinieren   |  Zellen verschlüsseln/entschlüsseln   |  Senden Sie E-Mails nach Liste   |  Superfilter   |   Spezialfilter (Filter fett/kursiv/durchgestrichen...) ...
Top 15 Toolsets:  12 Text Tools (Text hinzufügen, Zeichen entfernen, ...)   |   50+ Chart Typen (Gantt-Diagramm, ...)   |   40+ Praktisch Formeln (Berechnen Sie das Alter basierend auf dem Geburtstag, ...)   |   19 Einfügen Tools (QR-Code einfügen, Bild aus Pfad einfügen, ...)   |   12 Umwandlung (Conversion) Tools (Zahlen zu Wörtern, Currency Conversion, ...)   |   7 Zusammenführen & Teilen Tools (Erweiterte Zeilen kombinieren, Zellen teilen, ...)   |   ... und mehr

Verbessern Sie Ihre Excel-Kenntnisse mit Kutools für Excel und erleben Sie Effizienz wie nie zuvor. Kutools für Excel bietet über 300 erweiterte Funktionen, um die Produktivität zu steigern und Zeit zu sparen.  Klicken Sie hier, um die Funktion zu erhalten, die Sie am meisten benötigen ...

Beschreibung

Office Tab Bringt die Oberfläche mit Registerkarten in Office und erleichtert Ihnen die Arbeit erheblich

  • Aktivieren Sie das Bearbeiten und Lesen von Registerkarten in Word, Excel und PowerPoint, Publisher, Access, Visio und Project.
  • Öffnen und erstellen Sie mehrere Dokumente in neuen Registerkarten desselben Fensters und nicht in neuen Fenstern.
  • Steigert Ihre Produktivität um 50 % und reduziert jeden Tag Hunderte von Mausklicks für Sie!
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