Office: (Office 365) Suche nach eindeutigen Werten

Helfe beim Thema Suche nach eindeutigen Werten in Microsoft Excel Hilfe um das Problem gemeinsam zu lösen; 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... Dieses Thema im Forum "Microsoft Excel Hilfe" wurde erstellt von Itchley, 19. Januar 2021.

  1. Itchley Neuer User

    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.

    Suche nach eindeutigen Werten ?temp_hash=8243ea6437a4b60ef2b384ce05efe059.png

    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
     
    Itchley, 19. Januar 2021
    #1
  2. steve1da Office Guru
    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
     
    steve1da, 19. Januar 2021
    #2
  3. Exl121150 Erfahrener User
    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.
     
    Exl121150, 19. Januar 2021
    #3
  4. Itchley Neuer User

    Suche nach eindeutigen Werten

    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
     
    Itchley, 20. Januar 2021
    #4
  5. Exl121150 Erfahrener User
    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.
     
    Exl121150, 20. Januar 2021
    #5
    1 Person gefällt das.
  6. Itchley Neuer User
    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
     
    Itchley, 21. Januar 2021
    #6
Thema:

Suche nach eindeutigen Werten

Die Seite wird geladen...
  1. Suche nach eindeutigen Werten - Similar Threads - Suche eindeutigen Werten

  2. Outlook 2021 Suche nicht nutzbar

    in Microsoft Outlook Hilfe
    Outlook 2021 Suche nicht nutzbar: Es gibt folgende Probleme: 1. Die Sortierreihenfolge im Suchfenster springt ständig hin und her, so dass man immer wieder prüfen muss, ob die angezeigten Elemente auch noch richtig sortiert sind....
  3. Teil Suchen

    in Microsoft Excel Hilfe
    Teil Suchen: Hallo Zuammen, ich dachte eigentlich die folgende Formel hätte ich von hier gehabt, konnte aber keinen Beitrag dazu finden: In Zelle O5 steht: =IF(LEFT(N5,1)="J",MID(TRIM(N5),SEARCH("...
  4. Suche Datum mit >= "Startdatum" und <= "Enddatum

    in Microsoft Excel Hilfe
    Suche Datum mit >= "Startdatum" und <= "Enddatum: Hallo Forengemeinde, ich versuche derzeit eine Tabelle mit Daten mit Daten aus einer anderen Tabelle zu kombinieren. Hierzu soll ein Wert, in Abhängigkeit des Datums herausgesucht werden. In...
  5. Formel für gleiche Werte suchen und bei übereinstimmung einen andern Wert schreiben

    in Microsoft Excel Hilfe
    Formel für gleiche Werte suchen und bei übereinstimmung einen andern Wert schreiben: Hallo, sorry für den Thread Name aber wusste nicht wie ich es beschreiben soll. Ich suche eine Formel wo nach 2 gleichen Werten in Zelle A und C gesucht wird und bei Übereinstimmung der Wert der...
  6. Werte aus einer Tabelle suchen, zusammenzählen und wiedergeben

    in Microsoft Excel Hilfe
    Werte aus einer Tabelle suchen, zusammenzählen und wiedergeben: Hallo Zusammen Ich bin neu hier und habe leider nichts zu meinem Thema gefunden oder falsch gesucht. Ich bitte euch um Hilfe bei meinem Problem, da es mich schon fast wahnsinnig macht. Mein...
  7. Excel - nach mehreren Wörtern suchen und diese farbig markieren?

    in Microsoft Excel Hilfe
    Excel - nach mehreren Wörtern suchen und diese farbig markieren?: Hallo, ich möchte in Excel ein Dokument immer nach den gleichen ganzen Wörtern durchsuchen und die Ergebnisse unterschiedlich farbig markieren. Beispiel: Auto - Bus - Flugzeug - grün markieren...
  8. Word - nach mehreren Wörtern suchen und diese farbig markieren?

    in Microsoft Word Hilfe
    Word - nach mehreren Wörtern suchen und diese farbig markieren?: Hallo, ich möchte in Word ein Dokument immer nach den gleichen ganzen Wörtern durchsuchen und die Ergebnisse unterschiedlich farbig markieren. Beispiel: Auto - Bus - Flugzeug - grün markieren...
  1. Diese Seite verwendet Cookies, um Inhalte zu personalisieren, diese deiner Erfahrung anzupassen und dich nach der Registrierung angemeldet zu halten.
    Auf dieser Website werden Cookies für die Zugriffsanalyse und Anzeigenmessung verwendet.
    Wenn du dich weiterhin auf dieser Seite aufhältst, akzeptierst du unseren Einsatz von Cookies.
    Information ausblenden