Hallo!
Wie kann ich in einer Matrix (bzw. Spalte) mit NICHT-SORTIERTEN Zahlen diejenige Zahl finden, die die Nächstgrößere zu einer gegebenen Zahl ist?
Beispiel:
6,3,9,6,7,4,4,6,5,4,2,6,3,3,9
gegeben sei die Zahl 5. Die nächst größere Zahl in der Reihe oben wäre dann die 6.
Ich kann sowas wohl lösen, wenn ich ein paar Dummi-Spalten anlege etc, aber das ist alles viel zu umständlich. Es sollte eine Formel geben, die in einer Zelle steht - keine Dummi-Spalten, kein VB-Skript.
LG
Jochen
Wie kann ich in einer Matrix (bzw. Spalte) mit
NICHT-SORTIERTEN Zahlen diejenige Zahl finden, die die
Nächstgrößere zu einer gegebenen Zahl ist?Beispiel:
6,3,9,6,7,4,4,6,5,4,2,6,3,3,9
gegeben sei die Zahl 5. Die nächst größere Zahl in der Reihe
oben wäre dann die 6.Ich kann sowas wohl lösen, wenn ich ein paar Dummi-Spalten
anlege etc, aber das ist alles viel zu umständlich. Es sollte
eine Formel geben, die in einer Zelle steht - keine
Dummi-Spalten, kein VB-Skript.
Ich habe die Zahlen in die Zellen A1 bis und mit A15 eingetippt. Danach kannst du in einer beliebigen Zelle (bei mir A16) folgende Formel eingeben:
=VERWEIS(5+1;A1:A15)
Du beauftragst damit Excel, in den Zellen A1 bis A15 nach der Zahl 6 zu suchen. Kleiner Schönheitsfehler: Wenn die gegebene Zahl ändert, muss du auch die Formal anpassen 
Hi,
danke für den Tipp.
Die Zahlen waren blöd. Natürlich handelt es sich in Wirklichkeit nicht um ganze Zahlen, sondern um irgendwelche reellen Zahlen. Die nächstgrößere Zahl als X ist also NICHT X+1, sondern X+delta (mit delta>0).
=VERWEIS(5+1;A1:A15)
Du beauftragst damit Excel, in den Zellen A1 bis A15 nach der
Zahl 6 zu suchen. Kleiner Schönheitsfehler: Wenn die gegebene
Zahl ändert, muss du auch die Formal anpassen
WENN die gesuchte Zahl immer genau 1 größer wäre, dann brauchte ich auch die Verweis-Formel nicht. Das Ergebnis wäre dann ja im Beispiel sowieso schlicht und einfach 5+1 (=6).
Zusem klappt VERWEIS nur, wenn die Daten aufsteigend sortiert sind.
Trotzdem, danke!
Jochen
Die Zahlen waren blöd. Natürlich handelt es sich in
Wirklichkeit nicht um ganze Zahlen, sondern um irgendwelche
reellen Zahlen. Die nächstgrößere Zahl als X ist also NICHT
X+1, sondern X+delta (mit delta>0).
Hi Jochen,
sofern hier keine® eine Lösung zaubert wirste wohl doch Hilfsspalten oder Vba-Code benutzen müssen *schätz*
May be, du findest hier was, was dir weiterhilft:
Gruß
Reinhard
Hallo Jochen,
wenn ich die Aufgabe richtig interpretieren, sollte z.B. als Ergebnis von 7 die 9 erscheinen, weil die 8 nicht vorhanden ist. Hier fehlt für Deine gewünschte Ein-Zellen-Formel eine Angabe, wie groß die Lücke zwischen den Zahlen werden kann. Die Formel
=VERWEIS(b1+1;A1:A15) würde ich übrigens nicht so schnell verstossen, denn wir haben ja noch die Funktion Ganzzahl, nicht wahr? Etwas kombiniert mit istfehler einige wenn-Funktionen, kann man locker Zahlenlücken bis zur Größe 7 überspringen. Ein Makro wäre mir aber wirlich lieber 
.
MfG Georg V.
[Bei dieser Antwort wurde das Vollzitat nachträglich automatisiert entfernt]
Hallo!
wenn ich die Aufgabe richtig interpretieren, sollte z.B. als
Ergebnis von 7 die 9 erscheinen, weil die 8 nicht vorhanden
ist.
Korrekt.
Hier fehlt für Deine gewünschte Ein-Zellen-Formel eine
Angabe, wie groß die Lücke zwischen den Zahlen werden kann.
Die Größe der Lücke ist in der Tat beliebig.
Die Zahlen sind beliebige reelle Zahlen in einer beliebigen Größenordnung.
Ein Makro wäre mir aber wirlich lieber
Mir auch. Trotzdem ein wenig Hintergrund:
Ich würde gerne Studis das Erstellen von Boxplots beibringen. Sie kennen Excel und ich will dafür (auch noch aus anderen Gründen) eben Excel verwenden (ich weiß - es gibt Statistikprogramme, die das einfach so können und noch viel mehr; es geht hier aber um Excel).
Es gibt „Krücken“ (Workaraounds), mit Excel auch Boxplots zu erstellen. Alle diese Krücken verwenden als Maßzahlen Minimum, 1.Quartil, Median, 2.Quartil und Maximum. Klar kann man mit diesen Werten etwas zeichnen (auch mit Excel), was so aussieht wie ein Boxplot. Allerdings sind die falsch. Anstelle des Minimums und Maximums werden nämlich andere Werte benutzt (ich spare mir die Rechnung und Bezeichnung hier!), die nicht notwendigerweise dem Minimum und Maximum entsprechen. Diese beiden Werte definieren dann eine Spanne, welche mögliche Ausreißer NICHT mit einschließen sollen. Um genau das zu sehen (nämlich ob und wie viele mögliche Ausreißer es in der Verteilung gibt), werden die betreffenden Werte als Datenpunkte angezeigt. Um ebendiese Datenpunkte zu finden, brauche ich diesen Vergleich.
Die Studis können keine Makros programmieren und das sollen sie auch nicht lernen brauchen. Sie sollen auch keine „3rd-party tools“ installieren müssen, um mit Excel korrekte Boxplots zeichnen zu können. Dann könnten wir gleich eine ganz andere Software benutzen.
Nein, sie sollen lernen, in Excel Formeln zu benutzen. Dabei soll auch gleich was Sinnvolles rauskommen, nämlich zB. ein Boxplot. Ich hatte gehofft, es sei hinzukriegen. Wenn das nicht geht, ist das zwar nicht wirklich schön, ist aber auch nicht schlimm. Es gibt ja noch genug andere Sachen.
Danke trotzdem für eure Hilfe!!
LG
Jochen
Hallo!
Dank deines Links habe ich was gefunden und es funktioniert sogar!
Wenn die Werte in A1:A9 stehen, dann kann ich in Spalte B die Werte Filtern, welche größer max und kleiner min sind, und zwar mit folgender (Matrix-)Formel:
{=INDEX(A$1:A$9;KKLEINSTE(WENN((A$1:A$9max);ZEILE($1:blush:9));ZEILE()))}
Bin aber nachwievor gespannt, ob jemand eine einfachere Lösung hat.
LG
Jochen
Excel und Boxsplot s
Ein Makro wäre mir aber wirlich lieber
Hi Jochen,
ich habe
http://de.wikipedia.org/wiki/Boxplot
überflogen und wage mich da nicht ernstlich an ein Makro mangels mathematischem Hintergrund.
Aber auf der Seite sind unten bei Weblinks für dich sehr interessante weiterführende Links z.B. „Wie kann man mit Excel Boxsplots erstellen“, dort dann wieder auf „mehr lernen“ oder wars „mehr erfahren“? klicken, usw.
So wie ich das überflogen habe geht das ohne Makros, mit Median() und Diagrammen u.ä.
Gruß
Reinhard
Hi Jochen,
Dank deines Links habe ich was gefunden und es funktioniert
sogar!
meinst du excelfomeln.de? Dann war das von mir, also der Link:smile:
Und da funktioniert alles *sicherbin".
Leider blicke ich bei dem dortigen Matrixkram kaum durch, und wenn ich auch nur versuche die seltsamen Formeln auf andre Zellenbereichen umzubauen, geht oft was schief und es tuts sich erst mal wenig, abgesehen von Fehlermeldungen *gg*
Wenn die Werte in A1:A9 stehen, dann kann ich in Spalte B die
Werte Filtern, welche größer max und kleiner min sind, und
zwar mit folgender (Matrix-)Formel:{=INDEX(A$1:A$9;KKLEINSTE(WENN((A$1:A$9max);ZEILE($1:blush:9));ZEILE()))}
Bin aber nachwievor gespannt, ob jemand eine einfachere Lösung
hat.
Die 3 Jungs dort sind Spitzenklasse, wenn es eine kürzere Formel gäbe würde sie dort stehen.
Ausnahme ist, da anscheinend die Formel für eine bestimmte Filterung geschrieben wurd, daß jmd. eine Formellösung bringt die für Boxsplots geschrieben wurde.
Gruß
Reinhard
Hallo,
…
So wie ich das überflogen habe geht das ohne Makros, mit
Median() und Diagrammen u.ä.
Hab ich, weiß ich, danke!
Da war aber eben nichts dabei. Alle dort referenzierten Lösungen für Excel zeichnen den Boxplot mit den Whiskern zu den Extremwerten. Das ist nicht nur nicht ganz korrekt, sondern es eliminiert eine wichtige Eigenschaft der Plots, nämlich grafisch das Vorhandensein möglicher Ausreißer zu erkennen.
LG
Jochen
…
So wie ich das überflogen habe geht das ohne Makros, mit
Median() und Diagrammen u.ä.Da war aber eben nichts dabei. Alle dort referenzierten
Lösungen für Excel zeichnen den Boxplot mit den Whiskern zu
den Extremwerten. Das ist nicht nur nicht ganz korrekt,
sondern es eliminiert eine wichtige Eigenschaft der Plots,
nämlich grafisch das Vorhandensein möglicher Ausreißer zu
erkennen.
Hi Jochen,
das ist mir alles zu speziell, kann da nix mehr für dich tun, einzig, google mal im Web oder in Groups nach den Boxsplots oder Whiskern, aber nicht in deutsch sondern in allen Sprachen.
Wer weiß, vielleicht hat jmd. in Kirgisien eine Lösung
Ich fand da schon öfters z.B. Vba-Code, wobei ich keinerlei Ahnung hatte was die da so reden, wird ja schon bei Niederländisch kritisch für mich, geschweige denn kyrillisch, aber der Vba-Code , den ich ja will, der ist englisch.
Bei Excelformeln, wenn du „fremd“ nach z.B. Excel Boxsplot gesucht hast, kann es schon passieren, eher sehr wahrscheinlich, daß du Excelformeln kriegts die man erst übersetzen muß, aber das kann man lösen.
Ich wollte mal einem sehr guten türkischen Freund eine Grußpostkarte zukommen lase, der Text war fertig, da habe ich einfach beim türkischen Konsulat hier angerufen und gebeten mir den 3Zeiler zu übersetzen auf Türkisch, klappte wunderbar.
Und wenn du da Studennten unterrichtest, ist es ja höchstwahrscheinlich daß auch ein russisch sprechender Student dabei ist, der dir ggfs. eine russische Seite zu Boxsplots erläutert.
Gruß
Reinhard
Hallo Jochen,
Wie kann ich in einer Matrix (bzw. Spalte) mit
NICHT-SORTIERTEN Zahlen diejenige Zahl finden, die die
Nächstgrößere zu einer gegebenen Zahl ist?Beispiel:
6,3,9,6,7,4,4,6,5,4,2,6,3,3,9
nehmen wir an, diese Zahlen stehen in A2:A16
gegeben sei die Zahl 5.
nehmen wir weiter an, diese Zahl steht in B2
Die nächst größere Zahl in der Reihe
oben wäre dann die 6.Ich kann sowas wohl lösen, wenn ich ein paar Dummi-Spalten
anlege etc, aber das ist alles viel zu umständlich. Es sollte
eine Formel geben, die in einer Zelle steht - keine
Dummi-Spalten, kein VB-Skript.
dann heißt die Formel:
=KGRÖSSTE(A2:A16;RANG(B2;A2:A16;0)-1)
wenn du jetzt in B2 eine andere Zahl einträgst, sollte Excel dir dazu den gesuchten Wert liefern.
Gruß
Marion
die formel ist korrekt - nur meine weitere Erklärung
wenn du jetzt in B2 eine andere Zahl einträgst, sollte Excel
dir dazu den gesuchten Wert liefern.
ist eine blöde Formulierung - geb ich zu
besser ist:
die Formel liefert für den Wert in Zelle B2 den nächstgrößeren Wert aus der Liste in A2:A16
oder so ähnlich
Gruß
Marion
OT war doch alles korrekt!?
wenn du jetzt in B2 eine andere Zahl einträgst, sollte Excel
dir dazu den gesuchten Wert liefern.ist eine blöde Formulierung - geb ich zu
Hallo Marion,
ist doch unblöd formuliert, ich gebe in B2 eine Zahl ein und Excel liefert mir den gesuhcten Wert (aus der Liste).
Was ist daran missverständlich oder gar blöde?
Und zur Anfrage, würde er die Liste sortieren hätte ich auch eine Lösung, aber wird wohl Gründe geben warum er nicht sortiert.
Lieben Gruß
Reinhard
Hallo Marion,
Deine Lösung funktioniert fast.
WENN der Wert in B2 NICHT selbst in den Daten vorkommt, dann liefert die Formel als Ergebnis #NV.
LG
Jochen
Hallo Jochen,
WENN der Wert in B2 NICHT selbst in den Daten vorkommt, dann
liefert die Formel als Ergebnis #NV.
stimmt, allerdings war das aus der Aufgabenstellung nicht ersichtlich.
jetzt auf die Schnelle habe ich keine Lösung. Ich muß mir erst einen neuen Lösungsansatz überlegen. Tut mir leid, ich hätte dir gern geholfen.
Gruß
Marion
Hallo Jochen,
WENN der Wert in B2 NICHT selbst in den Daten vorkommt, dann
liefert die Formel als Ergebnis #NV.
wenn du die Spalte mit dem Suchwert (in diesem Beispiel Spalte B) nicht mit weiteren Werten (Zahlen) gefüllt hast, kannst du den Bezug in der Formel einfach auf die Spalte B erweitern.
Die Formel heißt dann:
=KGRÖSSTE(A2:A16;RANG(B2;A2:B16;0)-1)
Das Ergebnis der Formel ist dann ebenfalls korrekt. Evtl. kannst du aber auch eine andere angrenzende Zelle für den Suchwert benutzen und diese Zelle in den Bezug mit einbinden. Übrigens werden mit Text gefüllte Zellen ignoriert, ebenso leere Zellen.
Leider habe ich noch keine Möglichkeit gefunden, in den Bezug verschiedene Bereiche einzubinden. Insofern ist das zur Zeit die schnellste und einfachste Lösung. Vielleicht kannst du es realisieren.
Gruß
Marion