Rang erstellen aus mehreren Kriterien

[Binary91]

Guten Tag zusammen,

ich habe zwar schon die SuFu genutzt da ich dachte, diese Frage sei bestimmt schon etliche Male gestellt worden, nur leider habe ich keine Antworten gefunden außer "Nutze doch die integrierte Sortier-Funktion". Leider ist es für mich notwendig, das Ganze mit Formeln zu erledigen.

Hier mein Problem: Ich möchte aus einer Liste mit n Datensäten eine fortlaufende Rang-ID erstellen, abhängig von mehreren Kriterien.
Erfundenes Beispiel: Autos. Die Kriterien (jeweils binär, also ist vorhanden oder nicht):
1. Preis >10.000€ / < 10.000€
2. Türen 5 / 3
3. Schaltung manuell / Automatic

Nach dieser Reihenfolge soll auch sortiert werden. D.h. zuerst alle Einträge Preis > 10.000€ & Türen 5 & Schaltung manuell, dann alle >10.000€ & Türen 5 & Schaltung automatic, dann das Ganze mit 3 Türen und dann das Ganze mit Preis < 10.000€.

Ich habe bisher leider keine andere Möglichkeit gefunden, als mit 6 Hilfsspalten zu arbeiten und in jeder Spalte die beschriebenen Bedingungen abzufragen.
Das sieht dann in Etwa so aus:
Hilfsspalte D:
Wenn(UND($A1>10000;$B1=5;$C1="manuell");MAX($D$1:$D1)+1;0)

In Hilfsspalte E erfrage ich dieselben Bedigungen außer $C1="automatic" und addiere zum Maximum von der gesamten Hilfsspalte D das Maximum der bisherigen Hilfsspalte E.

So fahre ich für alle 6 Hilfsspalten fort und habe nachher keine doppelten IDs, sondern eben jene die aufeinander aufbauen.

Problem: Ich habe mehr als nur diese 3 Kriterien (insgesamt sind es 26 Hilfsspalten) und mein Datensätze sind im 5-stelligen Bereich. Das sorgt mit dieser Methode für eine sehr langsam arbeitende Tabelle mit merkbarem Lagging bei Bearbeitung der Datensätze und abnorm langer Ladezeit beim Öffnen des Datenblattes.

Kennt denn nicht jemand zufälligerweise eine Matrixfunktion oder eine elegantere Methode, wie ich an Hilfsspalten und/oder an Rechenzeit einsparen kann? Sicher habe ich nur irgendwo einen Denkfehler und es ist gar nicht nötig, so viel miteinander kreuzzurechnen.

EDIT:
VBA ist leider keine Möglichkeit.

Vielen Dank bereits vorab für Eure Hilfe.

Grüße
Binary91

[Fennek]

Hallo,

der folgende Ansatz ist vollkommen un-ausgereift und inspiriert bei deinem Psydonym:

Für jedes Kriterium wird eine 0 bzw 1 vergeben auf einer anderen Stelle. So ähnlich

Code:

> 10000    : 100
< 10000 : 000
5T      : 010
3T      : 000
Manuell : 001
Auto    : 000

Autos teuer als 10.000 mit 5 Türen und manuellem Getriebe erhielten dann: 111

Danach kann man dann sortieren.

Jetzt bin ich auf die Schwarm-Intelligenz gespannt.

mfg

[WillWissen]

Hi,

mit entsprechenden Überschriften könntest du deine Liste nach den gewünschten Kriterien filtern. Möglicherweise geht es auch mit einer Pivottabelle. Dazu müsstest du aber eine vom Aufbau her deinem Original gleichende Beispieldatei hochladen. http://www.clever-excel-forum.de/Thread-...ng-stellen

[LCohen]

D2: =(A2>10000)*100+(B2>4)*10+(C2="manuell") am 3-Kriterienbeispiel

Bei 26 Kriterien gehst Du natürlich nicht im Zehnersystem vor, sondern ziehst die Kiste mathematisch zusammen. Oder Du verwendest Textzahlen, die größer als 15 Stellen sein können, musst aber akkurat Nullen auffüllen.

[Binary91]

Hallo zusammen,

erst einmal vielen Dank für die Antworten.

@LCohen:
Funktioniert Dein Beispiel denn auch bei mehr als 10 Datensätzen oder habe ich einen Denkfehler?
Angenommen, ich habe 11 Datensätze, bei denen nur Kriterium "C" zutrifft, dann habe ich doch bereits ID 1-11 belegt.
Jetzt kommt ein Datensatz, bei dem lediglich Kriterium B zutrifft, und schon habe ich ID 10 zweifach belegt?! Oder muss ich bei jeder Zeile noch einen extra Wert hinzuaddieren?

LG Kevin

[BoskoBiati]

Hi,

vielleicht hilft das weiter:

Arbeitsblatt mit dem Namen 'Tabelle1'
	A	B	C	D	E	F
1	Preis	Türen	Getriebe	Hilfe		Rang
2	12000	5	auto	111	111	1
3	12000	5	man	110	110	2
4	12000	3	auto	101	101	3
5	12000	3	man	100	100	4
6	9000	5	auto	011	11	5
7	9000	5	man	010	10	6
8	9000	3	auto	001	1	7
9	9000	3	man	000	0	8

Zelle	Formel
D2	=(A2>10000)*1&(B2=5)*1&(C2="auto")*1
E2	=(A2>10000)*100+(B2=5)*10+(C2="auto")
F2	=RANG(E2;E:E;)

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

Rang mit Spalte E ermitteln, Sortieren ginge auch mit Spalte D

[LCohen]

Gut aufgepasst!

Hänge bei mir noch 
D2: =bisherigeFormel+ZEILE(D2)%%% hinten dran.

Ich hatte Deinen Bedarf als Sort und nicht RANG verstanden. So klappt es nun auch eindeutig als Rang.

Dass es pro Feld 10 Ausprägungen geben darf, hatten wir ja schon. Du hast da nicht neu nachgefragt.

[Binary91]

Hoi,

ok, dann war mein Gedankengang schon einmal gar nicht schlecht.

Wenn ich mich nicht irre ist die Herangehensweise bei so etwas also die, dass man für jedes Kriterium (A, B, C) einen vollständigen Rangbereich mit Intervallgrösse entsprechend der maximalen Datensatzgrösse reserviert (bspw. bei 1000 Datensätzen jeweils 1000 Ränge), da man ja nicht ausschliessen kann, dass alle Datensätze zufällig dieselbe Kombination haben, korrekt?

Leider geht das nicht ganz auf, da meine Rangfolge ja entsprechend von alle Kriterien sind vorhanden bis zu kein Kriterium ist vorhanden laufen soll, sprich (für Kriterien A-C binarisiert):
1 - 1 - 1
1 - 1 - 0
1 - 0 - 1
1 - 0 - 0
0 - 1 - 1
0 - 1 - 0
0 - 0 - 1
0 - 0 - 0

Angenommen, in Datensatz (Zeile) #49 trifft Kriterium "0 - 0 - 1" zu, dann wäre der Rang = 0*1000000 + 0*1000 + 1 + 49 = 50.
Angenommen, in Datensatz (Zeile) #50 trifft Kriterium "0 - 0 - 0" zu, dann wäre der Rang = 0*1000000 + 0*1000 + 0+ 50 = 50.

Schon habe ich zwei überschneidende Ränge.

Selbiges Problem hätte ich, wenn bspw. in Datensatz #1 Kriterium "0 - 1 - 0" zutrifft --> Rang = 0*1000000 + 1*1000 + 0 + 1 = 1001.
Wenn dann in Datensatz #1000 Kriterium "0 - 0 - 1" zutrifft, habe ich ebenfalls Rang = 0*1000000 + 0*1000 + 1 + 1000 = 1001.

Wo liegt der Denkfehler bzw. wie schaffe ich hier elegant Abhilfe? Ist es vielleicht notwendig, gar für jede Kombinationsmöglichkeit einen vollständigen Rangbereich freizuhalten? Dann hätte ich immens grosse Ränge am Ende bei 8 Kombis = 1000^8, vielleicht könnte ich Abhilfe für Excel schaffen indem ich diese Zahl noch durch Faktor 1000 dividiere, dann haben die ersten Ränge eben Zahlen <1 aber das dürfte Excel doch nicht stören, oder?

Wer weiss mehr?

EDIT:
Oder alles um eine Potenz verschieben, quasi:
(A=1)*1000.000.000 + (B=1)*1000.000 + (C=1)*1000 + Zeile.

Das sollte doch funktionieren, oder?

Freundliche Grüsse
Binary91

[LCohen]

1 + 49 vs. 0 + 50 kommt bei meiner Empfehlung keine einzige Sekunde lang vor.

Die %%% standen da nicht zum Spaß.

[Binary91]

Für was stehen die drei %%%-Zeichen? Leider kommt hierzu nichts brauchbares wenn ich danach google.

EDIT:
Btw. habe ich mir mal eine allgemeingültige Formel für variable Datensatzgrössen mit variablen Kriterienzahlen ausgedacht (allerdings nur gültig für binäre Kriterienauswertung 0/1):
Kriterien = Buchstaben
Kriterienzahl = n
Datensatzgrösse = x

Hieraus folgt:
Rang = (A=1)*(x*2^(n-1)) + (B=1)*(x*2^(n-2)) + ... + (Z=1)*(x*2^(n-n)) + ZEILE()

EDIT2:
Hat ein Kriterium mehrere Ausprägungen als WAHR/FALSCH, kann man es ja theoretisch als extra Kriterium je nach gewünschter Rangfolge in die Formel packen:
Rang = (A=1)*(x*2^(n-1)) + (B=1)*(x*2^(n-2)) + (B=2)*(x*2^(n-3)) + ... + (Z=1)*(x*2^(n-n)) + ZEILE()

Was haltet ihr davon? Stimmt das?