Dropdownliste sortieren ohne VB

[schauan]

Hallo Peter,
die Datei ist nicht da
Links wählst Du nur die Datei aus und rechts erfolgt erst das Hochladen und Einfügen.

[peschiber]

Danke für den Hinweis

ich hoffe, dass Anhang jetzt hochgeladen wird.
  Dropdownliste Formelfehler.xlsx (Größe: 18,06 KB / Downloads: 4)

[schauan]

Hallöchen,

da hätte ich mal mehr zum INDEX schreiben sollen

ALso, Deine Formel in E10 lautet

=INDEX(C:C;VERGLEICH(KKLEINSTE(D$10:D$20;ZEILE());D$10:D$20;0))

Da sind 2 Fehler drin.

1) C:C ist der Datenbereich. Der ist aber zu groß. Es geht genau wie beim Vergleich nur um die Zeilen 10 bis 20.
2) ZEILE() ergibt hier in E10 10. Du willst dort aber den "kleinsten", also die "Startnummer" 1. 10 wäre der vorletzte bei den 11 Einträgen.
--> damit das klappt, müsstest Du entweder 9 abziehen, also ZEILE()-9, oder Du nimmst die Zeilennummer von A1.

Hier mal die geänderte Formel für E10. Ich würde in KKLEINSTE ZEILE(A1) nehmen. Wenn Du runter ziehst, wird A2, A3 usw. draus.

=INDEX(C$10:C$20;VERGLEICH(KKLEINSTE(D$10:D$20;ZEILE(A1));D$10:D$20;0))

[peschiber]

Hallo André,

ganz herzlichen Dank.
Jetzt klappt es schon viel besser!

Ich muss ehrlich gestehen, dass ich etwas begriffsstutzig bin und die Formeln immer noch nicht wirklich nachvollziehen kann.
Sicher auch ein Grund, warum es mir nicht gelungen ist, den offensichtlichen Fehler zu finden.

Als Anlage erneut die geänderte Excel-Datei.


  Dropdownliste Formelfehler 02.xlsx (Größe: 15,86 KB / Downloads: 4)

Mit freundlichen Grüßen Peter

[schauan]

Hallöchen,

wenn man die INDEX-Formel mal Zeile für Zeile anschaut, kann man ein paar "Abweichungen" der Bereichsgrenzen (Zeilennummern) sehen, erst mal egal, wieso. So was gibt auf jeden Fall auch Probleme beim Erweitern. Bei diesen "intelligenten" Tabellen oder Listen kann man so was umgehen bzw. sollte man besser mit den Spaltenbezeichnungen arbeiten.

Wenn man eine Formel eingibt kann man ja bei der Eingabe statt Zellen oder Bereiche hinzuschreiben auch auf die Zelle klicken oder den Bereich markieren. Du könntest also =INDEX( schreiben und dann z.B. den Datenbereich der Tabelle in Spalte C markieren, dann die Eingabe fortsetzen mit dem ; usw.

Dadurch erhältst Du dann diese Formel

=INDEX([NameVorname];VERGLEICH(KKLEINSTE([Sorieren];ZEILE(A1));[Sorieren];0))

Übrigens, wenn Du dann z.B. in D9 den Rechtschreibfehler korrigierst ändert sich das in der Formel gleich mit.

[peschiber]

Hallo Schauan,

ganz herzlichen Dank, dass du dir soviel Mühe gemacht hast, nicht entnervt bist und so humorvoll antwortest:

"Übrigens, wenn Du dann z.B. in D9 den Rechtschreibfehler korrigierst ändert sich das in der Formel gleich mit."

Abweichungen
Dein Hinweis:
" ... wenn man die INDEX-Formel mal Zeile für Zeile anschaut, kann man ein paar "Abweichungen" der Bereichsgrenzen (Zeilennummern) sehen, .."

war mir auch aufgefallen, konnte ich mir aber nicht erklären.

Während in der ersten Zeile der Quellbereich noch richtig angegeben wurde, war der Quellbereich ab Zeile 2 um  "-1" vermindert.
Beispiel:
Zelle E10 > =INDEX(C$10:C$20 ...
Zelle E11 > =INDEX(C$10:C$19 ...

Mögliche Ursache für den Fehler
Nachdem ich die Formel/ Funktion in Zelle E10 häufiger geändert habe, habe ich sie in die anderen Zeilen kopiert > E11 bis E 30.

Nachdem ich alle Einträge bis auf Zeile 10 gelöscht habe und ab Zeile E11 neu eingetragen habe, funktioniert es wunderbar.

Nochmals danke.

Bezüglich des Quellbereichs habe ich noch eine Frage.
Gibt es eine Möglichkeit den Quellbereich, statt zum Beispiel =INDEX(C$10:C$20 ... anders zu definieren?
Meine Überlegung:
den Startwert kenne ich, zum Beispiel C$10.
Da ich nicht weiß, wie viele Zeilen in Spalte C ausgefüllt sind, würde ich mir die Anzahl der ausgefüllten Zeilen ausgeben lassen:
zum Beispiel durch die Funktion: =ANZAHL2(C:C)-5 > Ergebnis = 28
Und dieses Ergebnis setze ich als "Ende" des Datenbereichs ein.

Wie ich inzwischen gelernt habe, ist das für "intelligente" Tabellen überflüssig.
Aber wie sieht das aus, wenn keine "intelligente" Tabelle angelegt wurde?
Wäre das vielleicht eine Möglichkeit, das Ende des Datenbereichs zu berechnen und sie in die Formel einzusetzen?
Wenn ja, wie?
Bislang habe ich nur Fehlermeldungen bekommen.


Mit herzlichen Grüßen Peter

[schauan]

Hallöchen,

hier mal ein Ansatz. ich hab das jetzt mal am Beispiel einer Summe gemacht. Ich hatte ja schon geschrieben, das INDEX auch was anderes als den Zellinhalt zurückgeben kann. Ich hatte weiter oben Adresse geschrieben, das war per Definition eigentlich nicht korrekt, müsste Bereich sein. Bei Formeln erwartet man ja oft eine Adresse, deswegen die Formulierung . Man sieht es auch bei der Summe, es fehlt eigentlich die zweite Zelladresse.

Ich begrenze hier den Summenbereich von Spalte A durch Auswahl eines Buchstabens aus Spalte B. Im ersten schritt stelle ich fest, in welcher Zeile der ist (D1), im zweiten Schritt nehm ich das Ergebnis für die Bestimmung der Abschlusszelle per INDEX (E1). In E2 hab ich die beiden Formeln mal gleich zusammengefasst.

Du kannst den INDEX natürlich auch in einem anderen INDEX wie hier in der Summe verwenden.

Arbeitsblatt mit dem Namen 'Tabelle1'
	A	B	C	D	E
1	1	A	D	4	10
2	2	B			10
3	3	C
4	4	D
5	5	E

Zelle	Formel
D1	=VERGLEICH(C1;B:B;0)
E1	=SUMME(A1:INDEX(A:A;D1;1))
E2	=SUMME(A1:INDEX(A:A;VERGLEICH(C1;B:B;0);1))

Verwendete Systemkomponenten: [Windows (32-bit) NT 10.00] MS Excel 2016

Diese Tabelle wurde mit Tab2Html (v2.6.2) erstellt. ©Gerd alias Bamberg

den Startwert kenne ich, zum Beispiel C$10.
Da ich nicht weiß, wie viele Zeilen in Spalte C ausgefüllt sind, würde ich mir die Anzahl der ausgefüllten Zeilen ausgeben lassen:

Bei so was musst Du aber genau aufpassen. Sind denn ab Zeile 1 alle Zellen der Spalte gefüllt?

Im Prinzip ginge das so - jetz mal ohne weitere Verrechnungen:

=INDEX(C10:INDEX(C:C;ANZAHL2(C:C);1);...

oder auch eingeschränkter

=INDEX(C10:INDEX(C:C;ANZAHL2(C1:C20);1);...

und vielleicht auch noch mit $-fixierten Zeilennummern

[peschiber]

Danke,

=INDEX(C10:INDEX(C:C;ANZAHL2(C1:C20);1);...
werde ich ausprobieren.

Für einen Einsteiger ganz schön tricky.

[peschiber]

Hallo Schauan,

danke auch für deinen letzten Hinweis.

Auch mit der von dir aufgezeigten Funktion

=INDEX([NameVorname];VERGLEICH(KKLEINSTE([Sorieren];ZEILE(A1));[Sorieren];0))

klappt das einwandfrei.

Was ich nicht nachvollziehen kann:
Warum steht in der Funktion  "ZEILE(A1)"

Der INDEX Breich ([NameVorname] fängt bei mir erst in Zelle "D10" an.
Ich wäre nie auf die Idee gekommen, als Parameter in die Funktion ZEILE(A1) einzusetzen.

Unabhängig davon habe ich eben eine möglicherweise viel elegantere, einfachere Lösung entdeckt.

Du hast mich indirekt darauf gestoßen, weil in deiner Funktion der Name "Sorieren" aufgetaucht ist.
(Sorry, war mein Tippfehler.)

Die Hilfs-Spalte zum Errechnen der alphabetischen Reihenfolge kann ich mir theoretisch schenken.
Beispiel: =SUMME(WENN(D$10:D$31< D10;1))
stattdessen nur:
=SORTIEREN(Tabelle6[NameVorname])

Hinweis für andere Leser*nnen:
Funktion "Sortieren" scheint es erst ab der Version Office 365 zu geben.

Wie es scheint, leider nur theoretisch beziehungsweise nur eingeschränkt.
Warum?
Die Funktion scheint nur außerhalb einer "intelligenten" Tabelle zu funktionieren.
Zumindest bekomme ich eine "Fehlermeldung", wenn ich die Funktion innerhalb einer "intelligenten" Tabelle verwende.
Außerhalb, mit Verweis auf die "intelligente" Tabelle kein Problem.

Aber möglicherweise bin ich mal wieder über einen Syntaxfehler gestolpert.

Falls es eine Möglichkeit gibt, die Sortierfunktion in eine "intelligente" Tabelle zu integrieren, lasst es mich wissen.

Danke Peter

[RPP63]

Moin!
ZEILE(A1) ist ein Zähler.
Kopierst Du die Formel in eine Zelle und ziehst sie herunter, dann erhältst Du die Reihe 1, 2, 3, … n
Der Spaltenbuchstabe ist übrigens egal, ich nehme in Foren immer =ZEILE(X1), damit der geneigte Leser dieses hinterfragt.

Die Hilfs-Spalte zum Errechnen der alphabetischen Reihenfolge kann ich mir theoretisch schenken.

In der Threaderöffnung hast Du geschrieben, dass Du die Datei auch unter Libre Office nutzen willst.
Am 5.1. in meiner ersten Antwort #3 [klick] habe ich Dir bereits geschrieben, dass es SORTIEREN() erst in Excel 365 gibt und es daher ausscheidet!
Außerdem vertragen sich sämtliche neuen Formeln, die eine Ergebnismatrix ausgeben
(z.B. EINDEUTIG(), FILTERN() o.ä.) konstruktionsbedingt nicht mit intelligenten Tabellen!
Da würde sich die Katze nämlich in den Schwanz beißen.

Apropos intelligente Tabelle:
Auch diese gibt es in Libre Office nicht, daher hilft sie Dir hier nicht weiter.

Gruß Ralf