Ich habe in Excel verschiedene Nummern mehrfach in der ersten Spalte. Nun möchte ich eine Nummer suchen und von dieser den Maximalwert und Minimalwert suchen?

...komplette Frage anzeigen

6 Antworten

Hallo,

vorab: Wenn man Hilfe möchte, sollte man die Schilderung so verfassen, dass jemand, der die Tabelle nicht kennt, nachvollziehen kann, wie diese aufgebaut ist. Das beugt unnötigen Nachfragen vor.

Zum Thema: Ich interpretiere mal als Beispiel wie folgt. Du hast in Spalte A Kundennummern, in Spalte B Umsatz des Kunden und zumindest in Spalte C noch eine Zuordnung des Umsatzes, z.B.: Monat oder KW.

Du willst wissen, welcher der höchste und der niedrigste Umsatz des Kunden war.

Formel:

{=KGRÖSSTE(WENN(A$1:A$1000=1;B$1:B$1000);1)}
{=KKLEINSTE(WENN(A$1:A$1000=1;B$1:B$1000);1)}

Matrixformel: Eingabe erfolgt ohne {}, Abschluss der Formel mit STRG + ALT + ENTER.

Falls du den 2.-größten/-kleinsten Wert auch wissen willst, kannst du  einfach hinten die 1 zur 2 machen. Ansonsten könnte man auch mit Max und Min, statt Kgrößte udn Kkleinste arbeiten.

LG, Chris

ich1weiss1nicht 15.06.2016, 10:23

Danke, das ist meine erste Frage hier. Ich habe nicht gesehen, wo ich die Frage genauer beschreiben kann.

Funktioniert das dann hier auch mit kgrösste?

Beispiel:

1        50

1        80

1        30

2        20

2        80

3        90

3        60

3        50

Jetzt habe ich eine extra Zelle in der ich beispielsweise 1 eintrage, dann möchte ich als Maximalwert 80 und als Minimalwert 30 ausgegeben haben.

0
Funfroc 15.06.2016, 10:27
@ich1weiss1nicht

Meine Formel macht im Endeffekt nichts anderes als {=Max(Wenn...))}.

Wie im anderen Kommentar schon geschrieben, welche Fehlermeldung bekommst du bei Max(wenn) bei mehr als 99 Zeilen? Es gibt keinen Grund, weshalb Max(Wenn) ab Zeile 100 nicht mehr funktionieren sollte.

Gib mal deine genaue Formel, die du benutzt.

0
Funfroc 15.06.2016, 10:37
@ich1weiss1nicht

ok, deine Formel im anderen Comment gesehen...

Ergebnis #NV kann ich nicht wirklich reproduzieren. Selbst wenn man eine komplett leere Tabelle durchsucht, bekommt man halt als Ergebnis 0, aber nicht #NV.

Sind die zu durchsuchenden Bereiche eingegebene Werte oder werden die aus einer anderen Tabelle mittels Formel gezogen? Ist im Suchbereich bereits ein #NV-Fehler vorhanden?

Wäre die einzige Erklärung die mir einfälllt.

LG, Chris

1

Du hast in der ersten Spalte Zahlen und in der zweiten Spalte Werte zu den Zahlen, deren Maximum du pro Zahl in der ersten Spalte herausfinden willst? Dann könntest du es so versuchen:

=MAX(WENN($A$1:$A$10=A1;$B$1:$B$10;""))

Schreibe sie in die dritte Spalte.
Diese Formeleingabe musst du mit Strg + Shift + Enter abschließen.
Danach kannst du die Formel herunter ziehen.

MAX ignoriert Leerzellen. Deswegen dürfte das kein Problem darstellen.

Für das Minimum einfach MAX durch MIN ersetzen.

ich1weiss1nicht 15.06.2016, 10:11

Danke, genau das war auch mein Ansatz.

Hier bin ich aber auf ein Problem gestoßen.

Bis Zeile 99 funktioniert die Formel einwandfrei. Nach 99 Zeilen funktioniert die Formel nicht mehr.

Gibt es hier dann einen Lösungsansatz?

Oder eine Alternative?

0
Funfroc 15.06.2016, 10:23
@ich1weiss1nicht

Warum muss man den Leuten immer alles aus der Nase ziehen? Und... Wenn du diese Formel sowieso schon hattest, warum lässt du zig Leute drüber nachdenken und schreibst es nicht einfach vorweg?

Edit: offenbar inzwischen ergänzt.



Nach 99 Zeilen funktioniert die Formel nicht mehr.

Was genau funktioniert nicht mehr? Was bekommst du als Fehlermeldung?


Hast du die Adressierung (evtl. $-Zeichen) richtig gemacht? Wie lautet deine Formel?

2
augsburgchris 15.06.2016, 10:24
@ich1weiss1nicht

Die Formel funktioniert auch für die ganze Spalte (ok, je nach Rechnerleistung). Wichtig ist das beide Bereiche gleich groß sind.


=MAX(WENN($A$1:$A$5000=A1;$B$1:$B$5000;""))



0
Suboptimierer 15.06.2016, 10:26
@ich1weiss1nicht

Ich wüsste nicht, was an Zeile 100 so besonders sein sollte, dass das ab dieser Zeile auf einmal nicht mehr funktioniert.

Hast du da eventuell einen Text drin stehen?

Eine Alternative könnte sein, dass du einen Filter auf die Spalten legst, nach der Zahl in der ersten Spalte filterst, deren Minimum und Maximum du suchst und dann mit TEILERGEBNIS dir das jeweilige ausgeben lässt.

https://support.office.com/de-de/article/TEILERGEBNIS-Funktion-7b027003-f060-4ade-9040-e478765b9939

=TEILERGEBNIS(104;B2:B100)
0
ich1weiss1nicht 15.06.2016, 10:27
@Funfroc

{=MAX(WENN(B2:B99=G3;E2:E99))}

ab 100 kommt als Ergebnis #NV

vielleicht gibt es einen andere Möglichkeit zu diesem Ergebnis zu kommen?

0
augsburgchris 15.06.2016, 10:29
@ich1weiss1nicht

Und wieso wundert dich das, wenn du nur den Bereich bis Zeile 99 abfragst? Das ist so, wie wenn du dich fragst, warum in einer Deutschlandkarte New York nicht verzeichnet ist.



{=MAX(WENN($B$2:$B$5000=G3;$E$2:$E$5000))}


und setz die Dollarzeichen!


2
Suboptimierer 15.06.2016, 10:31
@Jackie251

Was meinst du mit Oder-Teil?

Achso, ja, richtig. Der SONST-Zweig kann weggelassen werden, da FALSCH ignoriert wird von MAX.

1
Funfroc 15.06.2016, 10:42
@augsburgchris

Er/Sie schreibt aber von einem #NV-Fehler und der kann dadurch nicht zustande kommen.

Vermute im Suchbereich ist bereits ein #NV-Fehler (mutmaßlich in Zeile 100) vorhanden, der sich hier dann im Ergebnis auch wiederspiegelt.

1
PWolff 15.06.2016, 12:00
@augsburgchris

Eine ganze Spalte gibt man in Excel am einfachsten an in der Form

$A:$A

0
augsburgchris 15.06.2016, 12:05
@Funfroc

Außerdem würde das bedeuten das Spalte A Werte über 100 enthält.

Stimmt der NV dürfte nicht auftauchen, maximal 0.

0
augsburgchris 15.06.2016, 12:06
@PWolff

Da würde ich aber bei Arrayformeln tunlichst drauf verzichten. Es sei denn du hast einen Megarechner. Nur weil man es neuerdings KANN heisst das nicht das man es SOLLTE.

1

Pivottabelle anlegen zu deinem Zahlenbereich und da dann zu jedem Wert aus der 1. Spalte das "Maximum" und "Minimum" der 2. Spalte anzeigen lassen.


mach das doch mit einer Matrix auswertung

=MAX(WENN(A1:A8=1;B1:B8))

Formel mit shift+strg + Enter abschließen

für den Min Wert einfach MIN statt MAX verwenden

ich1weiss1nicht 15.06.2016, 10:14

Danke, genau das war auch mein Ansatz.

Hier bin ich aber auf ein Problem gestoßen.

Bis Zeile 99 funktioniert die Formel einwandfrei. Nach 99 Zeilen funktioniert die Formel nicht mehr.

Gibt es hier dann einen Lösungsansatz?

Oder eine Alternative?

0
Jackie251 15.06.2016, 10:24
@ich1weiss1nicht

max funktioniert für 255 Einzelelemente, für Bereiche kenne ich jedoch keine Grenze. Es dürfte daher ein Fehler an der Datenbasis vorliegen wenn es ab 99 nicht mehr geht.

1

Hey,

schau doch mal, ob dir das hier weiterhilft:

http://www.excel-helfer.de/funktionen-von-excel-mittelwert-max-und-min.html

Grüße

ich1weiss1nicht 15.06.2016, 10:02

Danke, aber ganz so trivial ist es nicht.

Beispiel:

1        50

1        80

1        30

2        20

2        80

3        90

3        60

3        50

Jetzt habe ich eine extra Zelle in der ich beispielsweise 1 eintrage, dann möchte ich als Maximalwert 80 und als Minimalwert 30 ausgegeben haben.

Wie kann ich dies mir ausgeben lassen?

0
Hask1989 15.06.2016, 10:11
@ich1weiss1nicht

Achso...puh...da bin ich leider überfragt, tut mir leid. 

Jedoch bin ich neulich in einem Excel-Forum gelandet, da wurden "spezifischere" Formeln von der Community gebastelt. Also auf Wunsch angefertigt, wenn man so möchte. :-P

Vllt findest du dort schnelle Hilfe! ;-)

Toi, toi toi und Grüße

0

Was möchtest Du wissen?