Office: (Office 365) Suche nach eindeutigen Werten

Moin zusammen,

ich stehe irgendwie auf dem Schlauch. Eigentlich kann ich ganz gut mit Excel, aber seit paar Stunden sitz ich vor einem kleinen Problem und ich kann keine Lösungen finden die bei mir funktionieren - vielleicht hab ich auch einfach gerade einen Knoten zwischen den Ohren.



Ziel ist es, in Zelle EB100 den Text aus BD100; EB101 den Text aus BA101; EB102 den Text aus BB101 usw. zu bekommen.
Ich suche also den Wert aus EA100 im Bereich DA100:DP200 und möchte den Spalten- und Zeilenbezug dazu nutzen den Wert aus BA100:BP200 zu erhalten.
Lösung darf kein VBA enthalten.

Gruß
Itchley

 

Hola,

lade eine Exceldatei hoch und trage hier händisch dein Wunschergebnis ein. Hilfreich wäre auch eine Erklärung warum welcher Wert genau wo erscheinen soll.

Gruß,
steve1da

 

Hallo,

in der beiliegenden Excel-Datei habe ich dein Modell nachgebaut und 2 Formelvarianten eingebaut:

1. Variante(Spalte EB) mit 2 Hilfspalten für Zeilennr.(Spalte EC) und Zeilennr. (Spalte ED) im Arbeitsblatt Tabelle1:
   Zelle ED100: =MIN(WENN(ZÄHLENWENN( BEREICH.VERSCHIEBEN(DA$100:DA$200;;SPALTE($A1:$P1)-1;;); $EA100)>0;SPALTE($A1:$P1)))
   Zelle EC100: =VERGLEICH($EA100; BEREICH.VERSCHIEBEN($DA$100:$DA$200;;$ED100-1;;) ;0)
   Zelle EB100: =INDEX($BA$100:$BP$200;EC100;ED100)
   
2. Variante(Spalte EB) ohne Hilfsspalten im Arbeitsblatt Tabelle2:
   Zelle EB100: =LET(K;$EA100; B;DA$100:DA$200; SS;SPALTE($A1:$P1); SN;MIN(WENN(ZÄHLENWENN( BEREICH.VERSCHIEBEN(B;;SS-1;;);K)>0;SS)); ZN;VERGLEICH(K; BEREICH.VERSCHIEBEN(B;;SN-1;;);0); INDEX($BA$100:$BP$200;ZN;SN))

Beide Formelvarianten einfach in weitere Zellen unterhalb kopieren, soweit als benötigt.

 

Moin,

@steve1da
Es geht um Netzwerke in Stern- und Ringtopologie. Gerade bei der Ringtopologie gibt es Netzgeräte von denen noch ein Strang mit Satelliten abgeht. Hier in der Frage habe ich bei 100 Switchen alle Hopps dargestellt, dann die Ketten herausgehoben die zum Endgerät laufen und diese dann mit eindeutigen Zahlen belegt und wollte daraus dann eine Liste machen die ich dann für Dokumentationszwecke in einer Dropdownliste zur Auswahl stelle. Gleichzeitig benötige ich diese Liste um die Satelliten an diese Ketten anzuknüpfen.

@Anton Exl
Erstmal vielen Dank für die beiden Vorschläge - die Funktionieren beide soweit ich das bisher sehen kann auch einwandfrei.
Index und Vergleich war auch mein Ansatz bis dahin, aber ich gebe zu das mir die Funktion Bereich.Verschieben bisher leider komplett an mir vorbei gegangen ist.
Variante1
Die Formel für EB100 ist klar, EC100 konnte ich mir selbst erklären aber ED100 kann ich mit den Spalte-Funktionen nicht nachvollziehen. Wenn hier meine Wissenslücke noch geschlossen werden könnte, wäre das echt Weltklasse.
Variante 2 geht irgendwie komplett an mir vorbei.

Besten Dank
Itchley

 

Hallo,

in der beiliegenden Excel-Datei habe ich dein Modell wie gehabt belassen, jedoch die verwendeten Formeln zur Ermittlung der Zeilennr. und Spaltennr. wesentlich vereinfacht. Damit fällt auch dein Formelverständnisproblem (Spaltennr.) weg. Ein weiteres Problem fällt auch weg, was die Funktion "BEREICH.VERSCHIEBEN(...)" betrifft: Diese ist nämlich eine volatile Funktion, d.h. sie wird jedesmal berechnet, auch wenn sich in der Formel kein Wert geändert hat, was bei häufiger Verwendung dieser Funktion im Arbeitsblatt zu einer Verlangsamung der Berechnung führt.

1. Variante(Spalte EB) mit 2 Hilfspalten für Zeilennr.(Spalte EC) und Spaltennr. (Spalte ED) im Arbeitsblatt Tabelle1:
   Zelle EC100: =SUMME(($DA$100:$DP$200=$EA100)*(ZEILE($1:$101)))
   Zelle ED100: =SUMME(($DA$100:$DP$200=$EA100)*(SPALTE($A:$P)))
   Zelle EB100: =INDEX($BA$100:$BP$200;EC100;ED100)
   
2. Variante(Spalte EB) ohne Hilfsspalten im Arbeitsblatt Tabelle2:
   Zelle EB100: =LET(K;$EA100;M;($DA$100:$DP$200=K); ZN;SUMME(M*ZEILE($1:$101)); SN;SUMME(M*SPALTE($A:$P)); INDEX($BA$100:$BP$200;ZN;SN))

Die Funktion SPALTE($A1:$P1)-1 produziert einen Zeilenvektor mit den 16 Werten {0.1.2. ... .14.15}. Dieser Vektor wird verwendet, um mit der Funktion BEREICH.VERSCHIEBEN(DA$100:DA$200;;SPALTE($A1:$P1)-1;;) den Spaltenvektor DA$100:DA$200 spaltenweise horizontal jeweils um 1 Spalte nach rechts zu verschieben, wobei jedesmal getestet wird, ob das Kriterium $AE100 mittels der Funktion ZÄHLENWENN(Bereich; Kriterium)>0 mindestens 1x gezählt werden konnte. Die umschließende Funktion WENN(....>0; SPALTE($A1:$P1)) überprüft, ob dies der Fall ist, und gibt in diesem Fall die Spaltennr. an die MIN(...)-Funktion weiter bzw. falls >0 FALSCH war, erhält die MIN(..)-Funktion den log. Wert FALSCH. Schlussendlich erhält so die MIN(.....)-Funktion eine Liste mit gefundenen Spaltennr.-Werten (falls es mehrere Spaltenfunde geben sollte), wovon sie die Spalte mit der kleinsten Nummer zurückgibt.

 

Hallo,
vielen Dank für die ausführliche Erklärung. Ich konnte die Formel inzwischen auch schon (zwar noch etwas mühselig) anpassen und habe das Prinzip verstanden. Paar mal damit noch hantieren und es läuft hoffentlich flüssig von der Hand. Auch die angepasste Lösung ist super.
Vielen Dank noch einmal dafür
Gruß
Itchley