Live-Forum - Die aktuellen Beiträge
Datum
Titel
24.04.2024 20:38:58
Summe mit Bedingung, nur letzte Ergebnisse anzeigen
24.04.2024 19:29:30
Formel filter
24.04.2024 18:49:56
merge & select
24.04.2024 17:19:09
Formel mit Index-Funktion
Du findest keine passende Antwort auf Deine Frage?
Stelle Deine Frage in unserem Live-Forum
Anzeige
Archiv - Navigation
1892to1896
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Vorheriger Thread
Vorheriger Thread
Rückwärts Blättern
1892to1896
1892to1896
Aktuelles Verzeichnis
Verzeichnis Index
Verzeichnis Index
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

kleinster negativer Wert

kleinster negativer Wert
05.08.2022 13:32:52
Holger
Hallo an das Forum,
dies ist mein erster Beitrag und möchte mich an das geballte Wissen der Forumsanwender richten.
Ich habe folgendes Problem:
Ich möchte aus einer Tabelle mit Bestandsdaten mit den Bedingungen 1. =ArtikelNr. und 2. =nur negative Werte den ersten in der Ergebnisliste befindlichen negativen Wert auslesen, um das Datum der ersten Bestandsunterschreitung zu erhalten (negativer Bestand). Meine bisherigen Versuche haben leider kläglich versagt -> Formel =MAX(WENN((tttt[Teil]=$C13)*(tttt[Bestand] Mit der angegebenen Formel erhalte ich aus der Ergebnisliste immer den grössten negativen Wert - allerdings ist es nicht der erste negative Wert.
Kann hier jemand unterstützen.
https://www.herber.de/bbs/user/154524.xlsx

33
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: kleinster negativer Wert
05.08.2022 13:43:25
ChrisL
Hi Holger
Hier eine Idee:

=INDEX(A:A;VERGLEICH(-0.0001;B:B;-1)+1;1)
Vielleicht könnte man den 3. Parameter von VERGLEICH (-1 = grösser als) nutzen.
cu
Chris
AW: ermittle doch gleich nur das Datum ...
05.08.2022 13:43:47
neopa
Hallo Holger,
... z.B. in Zeile 3 wie folgt: =WENNFEHLER(AGGREGAT(15;6;tttt[Termin]/(tttt[Teil]=C3)/(tttt[Bestand]&lt0);1);"")
Gruß Werner
.. , - ...
AW: dies jedoch ...
05.08.2022 13:55:40
neopa
Hallo nochmal,
... sollte in Deiner XL-Version (die ich nicht habe) anstelle mit AGGREGAT() auch mit MINWENNS() ermittelbar sein.
Gruß Werner
.. , - ...
AW: ermittle doch gleich nur das Datum ...
05.08.2022 14:35:30
Holger
Hallo Werner,
mit einer so schnellen Antwort habe ich nicht gerechnet :-)).
Es funzt. Super, danke.
Zum Verständnis:
Es wird durch die Division innerhalb von ARRAY ein Fehler (#Div/0!) pro Datensatz produziert, der nicht übereinstimmt, und die Aggregats-Funktion filtert dann die fehlerbehafteten Datensätze aus und gibt den kleinsten Wert aus. Doch wie ist sichergestellt, dass die Reihenfolge der Daten unberührt bleibt? die Funktion KKLEINSTE
liefert doch innerhalb eines ARRAYs den kleinsten Wert - ob nun negativ oder positiv!
Anzeige
Das hast Du alles richtig erklärt...
05.08.2022 14:50:46
{Boris}
Hi,
...bis auf die Tatsache

ob nun negativ oder positiv
Dafür hat Werner vorgesorgt mit der zusätzlichen Bedingung (tttt[Bestand]
Aber Du hast Recht damit, dass nicht der ERSTE negative Wert gefunden wird, sondern nur der kleinste.
VG, Boris
AW: Das hast Du alles richtig erklärt...
05.08.2022 15:07:45
Holger
Hallo Boris,
genau darin besteht mein Problem, dass der kleinste Wert ermittelt wird:
Da es sich hier um Bestanddaten handelt, die den terminlichen Verlauf von Zu- und Abgängen darstellt, benötige ich zwingend den ersten negativen Bestand innerhalb von [MatStuli]. Bei der Ermittlung von [zu Datum] scheint es zu funktionieren. Wenn ich aber den Bestand betrachte und folgende Formel einsetze,
=WENNFEHLER(AGGREGAT(15;6;tttt[Bestand]/(tttt[Teil]=$C4)/(tttt[Bestand] Gibt es hierfür auch eine Lösung?
Anzeige
AW: Das hast Du alles richtig erklärt...
05.08.2022 15:10:19
onur
"Gibt es hierfür auch eine Lösung?" - Lies dir doch mal endlich ALLE Antworten durch.
Nich ganz so laut...
05.08.2022 15:15:30
{Boris}
Hi Onur,
...brüllen ;-)
Ob MINWENNS / MAXWENNS / AGGREGAT - es geht um den ERSTEN (von oben) Treffer - und den erlegt bisher nix ;-)
VG, Boris
AW: Nich ganz so laut...
05.08.2022 15:20:20
onur
Trotzdem sollte man jedem Helfer zumindest antworten (wenn schon nicht bedanken). :)
Das stimmt natürlich...
05.08.2022 15:29:41
{Boris}
Hi Onur,
...ändert in diesem Fall aber nix an der Tatsache, dass die Lösung noch aussteht ;-) Ich selbst will mich hier aber nicht lösungstechnisch einbringen, denn zu viele Köche...usw... Das bekommt ihr mit Sicherheit hin ;-)
VG, Boris
Die INDEX-Variante...
05.08.2022 15:32:17
{Boris}
Hi,
...von Rudi hatte ich überlesen - genau das ist es natürlich.
VG, Boris
Anzeige
AW: erster negativer Wert
05.08.2022 13:52:16
Rudi
Hallo,
=WENNFEHLER(INDEX(tttt[Bestand];AGGREGAT(15;6;ZEILE(tttt[Bestand])/(tttt[Bestand]&lt0) /(tttt[Teil]=C3)-1;1));"")
Gruß
Rudi
AW: erster negativer Wert
05.08.2022 15:29:04
Holger
Hallo Rudi,
wow, das ist es. Nun bekomme ich tatsächlich den ersten negativen Wert aus dem [ARRAY] der Bestände.
Vielen Dank.
Bei meinen Versuchen habe ich auch immer wieder einmal mit den Funktionen Aggregat und Index gearbeitet. Allerdings ist mir nicht der Gedanke gekommen beides miteinander zu kombinieren. Das ist ganz schön gewieft.
Ich möchte mich an dieser Stelle an alle Beteiligten bedanken.
Ich bin wirklich überrascht wie schnell Lösungsvorschläge hier gepostet werden. Ich habe dadurch eine Menge an Zeit gewonnen, die für weitere Experimente notwendig gewesen wären.
Anzeige
AW: stimmt so nur bedingt ...
05.08.2022 15:57:08
neopa
Hallo Holger,
... als ich eben die Beiträge seit meinem gelesen hatte, wollte ich Dir zuerst zu der Lösung von Rudi raten, wenn Dich wirklich der erste Wert Minuswert interessiert.
Allerdings würde diese Formel ein falsches Ergebnis ergeben können, wenn Deine Datentabelle nach anderen Kriterien sortiert wird (z.B. nach "Beleginfo")
Sicherer wäre, für den von Dir gesuchten Wert .z.B. die Ermittlung mit folgende Formel:
{=MAX(WENNFEHLER(AGGREGAT(15;6;tttt[Bestand]/(tttt[Teil]=$C4)/(tttt[Bestand]&lt0);ZEILE(A1:A9));""))}
Wobei ich allerdings jetzt nicht sagen kann, ob in Deiner XL-Version der Matrixformelabschluss dieser Formel notwendig ist oder nicht, In meiner älteren XL-Version schon.
Gruß Werner
.. , - ...
Anzeige
AW: damit die Formel kopiert werden kann ...
05.08.2022 16:08:41
neopa
Hallo,
... muss in dieser natürlich ZEILE(A1:A9) noch fixiert werden.
Also so: {=MAX(WENNFEHLER(AGGREGAT(15;6;tttt[Bestand]/(tttt[Teil]=$C4)/(tttt[Bestand]&lt0);ZEILE(A$1:A$99));""))}
Gruß Werner
.. , - ...
AW: kleinster negativer Wert
05.08.2022 14:19:00
Daniel
Hi
ich hab jetzt nicht deine tolle Excelversion aber mit 365 müsste das ganze doch reicht einfach mit
- FILTER nach Artikelnummer und &lt 0
- SORTIEREN nach Datum aufsteigend
- INDEX erste Zeile, Datumsspalte
lösbar sein.
Excel 365 bietet ja mit den neuen Funktionen die Möglichkeit, diese Aktionen innerhalb von Formeln durchzuführen.
Gruß Daniel
Anzeige
AW: kleinster negativer Wert
05.08.2022 15:54:12
Daniel
Hi
die Daten der ersten Zeile von oben mit Negativbestand kannst du mit dieser Formel auslesen:
die -1 bezieht sich auf die Zeilennummer, in welcher die tabelle tttt mit der Überschrift beginnt.

=WENNFEHLER(INDEX(tttt[Termin];MIN(WENN((tttt[Teil]=C4)*(tttt[Bestand]
für ältere Excelversionen ist der Formelabschluss mit STRG+SHIFT+ENTER erforderlich
Gruß Daniel
Das erste Filterergebnis....
05.08.2022 16:11:56
{Boris}
Hi Onur,
...mit INDEX zu ermitteln, ist ein gute Lösung!
Allerdings würde ich nicht den ganzen Bereich filtern, um daraus Zeile 1 und Spalte 12 auszulesen, sondern nur den Zahlenbereich filtern - dann ist es INDEX(1,1):

=WENNFEHLER(INDEX(FILTER(tttt!$L$2:$L$216;(tttt!$A$2:$A$216=$C3)*(tttt!$L$2:$L$216
VG, Boris
Anzeige
AW: nachgefragt ...
05.08.2022 16:22:57
neopa
Hallo Boris,
... da ich es nicht nachstellen kann. Ich würde momentan vermuten, dass noch SORTIEREN() zum Einsatz kommen müsste (möglicherweise nicht in Beispieldatei, aber wenn jemand die Quelldaten anders sortiert; dazu sieh auch mal mein Beitrag von 15:57) Oder?
Gruß Werner
.. , - ...
Alles eine Frage der Aufgabenstellung...
05.08.2022 16:29:32
{Boris}
Hi Werner,
...die Formel von Onur findet in der bestehenden Tabelle den ersten negativen Eintrag zu Suchwert - also weder den kleinsten, noch den größten - eben nur den ersten (von oben).
Kommt halt drauf an, was der TE möchte - aber bisher hab ich es so verstanden, dass es genau so sein soll.
VG, Boris
Anzeige
AW: möglich oder auch nicht ...
05.08.2022 16:42:19
neopa
Hallo Boris,
... aber Holger hat ja jetzt zunächst ausreichend zu lesen und danach genügend Infos, um auf dieser Basis seine Entscheidung zu treffen. In dem Zusammenhang gleich noch ein weiterer kleiner Tipp an ihn. Wenn an einem Tag mehre Bewegungen stattfinden, wie das hier offensichtlich der Fall ist (was ich bei meinen ersten Beitrag noch übersehen hatte) würde ich zum Datum auch die Uhrzeit noch mit erfassen (welches auch gleich in einer Zelle erfolgen kann) und dann ist die Wertentwicklung über den Bezug auf die Zeitangabe eineindeutig.
Gruß Werner
.. , - ...
Ich fürchte....
05.08.2022 16:59:28
{Boris}
Hi Werner,
...dass Holger durch den Threadverlauf eher irritiert als informiert ist....
Too much informations...
VG, Boris
Anzeige
AW: Das erste Filterergebnis....
05.08.2022 16:28:00
onur
Hi, Boris,
Hast Recht - Ist ja nicht so wie bei SVerweis, dass man den GANZEN Bereich nehmen muss, wenn die Suchspalte A und Ergebnisspalte J ist. Die neuen Funktionen habe ich auch nocht nicht so oft benutzt.
Aber Du machst Dich formelmäßig gut :-)
05.08.2022 16:30:58
{Boris}
Hi Onur,
...dafür, dass Du ja eher VBA-lastig bist :-)
VG, Boris
AW: Noch eine Version (365)
05.08.2022 16:59:58
Daniel
schon, dass du meinen Vorschlag, die neuen Excelfunktionen zu verwenden, aufgegriffen hast.
ich wollte die Formel nicht schreiben, da ich das nicht testen kann.
Gruß Daniel
AW: Noch eine Version (365)
05.08.2022 17:12:09
onur
Schon mal auf die Idee gekommen, dass manche Leute auch so auf ähnliche Ideen kommen wie du, vor allem, da es nicht allzu viele Alternativen gibt und die Funktion "Zeilenwahl" noch nicht allen zur Verfügung steht?
AW: Noch eine Version (365)
05.08.2022 17:18:44
Daniel
aber du hast meinen Beitrag sicherlich gelesen, oder?
AW: Noch eine Version (365)
05.08.2022 17:28:17
onur
Jetzt schon, vorher nur überflogen, um zu sehen, ob das Problem bereits gelöst wurde. Da offenbar immer noch niemand eine sichere Lösung gepostet hatte (und meine erste Lösung nur die kleinste Negativzahl fand), habe ich begonnen, mir ein paar Gedanken zu mache, wie es zu lösen wäre.
Was ich mich aber jetzt frage: Wozu die Sortieren-Funktion?
AW: Noch eine Version (365)
05.08.2022 17:52:15
Daniel
die Frage ist, was gesucht ist:
a) das erste vorkommen in der Liste
b) das älteste Datum, an dem das vorkommt.
die bisherigen Lösungen (Min(Zeile()) finden immer das erste Vorkommen in der Liste.
sollte das älteste Datum gesucht sein, wäre das mit den anderen Lösungen nur gegeben, wenn die Liste nach Datum sortiert ist und bei einer anderen Sortierung wäre das Erebnis u.U. falsch.
wenn man das mit dem Sortieren in die Formel mit aufnimmt, wäre das Ergebnis dann von der Sortierung der Liste unabhängig.
Gruß Daniel
AW: kleinster negativer Wert
05.08.2022 17:47:14
Holger
Hallo zusammen,
ich habe nun alle Beiträge gelesen und ich bin über die Vielzahl an Beiträgen schlichtweg überwältigt. Ich werde nun alle Beiträge testen und mich für die beste Lösung entscheiden. Bis dahin bedarf es allerdings noch ein wenig Zeit. Zu einigen Beiträgen habe ich bereits direkt geantwortet. An Alle, die von mir noch keine direkte Antwort bekommen haben, möchte ich mich jetzt schon einmal für ihre Beiträge bedanken.
Hier noch eine Information über die Quelle der Bestandsdaten. Diese werden als Export viertelstündlich erzeugt und beim Öffnen der Exceldatei aktualisiert. Die Mappe und die einzelnen Arbeitsblätter sind geschützt. Ein zusätzlicher Button erlaubt eine Aktualisierung der Daten.
Ein schönes Wochenende
AW: kleinster negativer Wert
08.08.2022 06:19:02
Holger
Guten Morgen zusammen,
ich habe mich für die Variante von Rudi entschieden!

=WENNFEHLER(INDEX(tttt[Bestand];AGGREGAT(15;6;ZEILE(tttt[Bestand])/(tttt[Bestand]
Sie ist für mich nachvollziehbar und arbeitet sehr schnell.
Ich brauche im Übrigen keinen Abschluss über Strg/Shift und Return in meiner Excel Version um eine Matrixformel abzuschließen.
Klasse Forum! Sehr engagierte Forumsteilnehmer. Daher nochmals vielen Dank an Alle für ihre Beiträge.
Ich kann dieses Forum bedenkenlos weiterempfehlen.
Holger

Beliebteste Forumthreads (12 Monate)

Anzeige
Jetzt das Excel-Portal bewerten: Deine Stimme zählt!

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige