☰

Ist es möglich, in Excel auf zwei Zellen zu verweisen, um eine dritte Zelle zu finden?

tag-icon • tag-icon microsoft-excel microsoft-excel-2010 worksheet-function
Ist es möglich, in Excel auf zwei Zellen zu verweisen, um eine dritte Zelle zu finden?

Ich habe eine Tabelle mit einer beträchtlichen Menge an Daten. Ich muss einige dieser Daten in bestimmte Zellen zurückgeben. Die Daten, die ich zurückgeben muss, befinden sich immer in der Nähe einer Zelle mit „Angefügten Komponenten“. Das Problem ist, dass es mehrere Zellen mit „Angefügten Komponenten“ gibt. Ich habe beispielsweise zwei Teile, „Teil 1“ und „Teil 2“, und jedes der beiden Teile hat einen Abschnitt „Angefügte Komponenten“ relativ nahe beieinander. Die Zellen, in denen sie sich befinden, bleiben auch nicht gleich, sonst würde ich einfach auf diese Zellen verweisen. Hier ist die Formel, die ich derzeit verwende, um die Daten in der Nähe von „Angefügten Komponenten“ für EIN Teil zurückzugeben:

=IFNA(INDEX(L15:R46,MATCH("Attached Components",M15:M46,0)+2,3),"0")

Zusammenfassend brauche ich eine Formel, die Daten aus einer Zelle zurückgibt, die auf „Angefügte Komponenten“ verweist, die dann auf „Teile-Nr._“ verweist.

Hier sehen Sie ein Beispiel, wie sich die Position der „Angefügten Komponenten“ ändern könnte und wo sie sich in Bezug auf „Teil Nr. 1“ befindet.

Bildbeschreibung hier eingeben

Dies ist ein ziemlich spezielles Problem und ich weiß, dass meine Erklärung nicht die klarste ist. Ich bin für die Hilfe dankbar und kann gerne nach genaueren Einzelheiten fragen!

Antwort1

Ich habe versucht, es unter der Annahme zum Laufen zu bringen, dass:

  • „Angefügte Komponenten“ steht immer in der gleichen Spalte.
  • Sie versuchen tatsächlich, die Beschreibung für jede „Materialnummer“ nachzuschlagen.

Und ich werde dieses Blatt verwenden, um an Folgendem zu arbeiten:

Beispiel

Dies ist möglicherweise nicht genau das, was Sie brauchen, aber ich kann versuchen, meine Antwort mit Ihren Anmerkungen dazu zu verbessern.

Indem Sie Ihre Formel erneut verwenden, um zu ermitteln, wo sich in der Spalte „Angefügte Komponenten“ befindet, und dann 2 hinzufügen, erhalten Sie die relative Zeile, in der die Materialbeschreibung beginnt:

=MATCH("Attached Components",B1:B32,0)+2

Das Ergebnis ist im Beispiel „7“.

Anschließend müssen Sie die letzte Zeile mit den Beschreibungen identifizieren. Um im richtigen Bereich zu suchen, muss die Formel je nachdem, in welcher Zeile „Angefügte Komponenten“ steht, geändert werden. Die Kombination aus MATCH, ADDRESS und CONCATENATE erstellt den Bereich neu.

MATCH gibt Ihnen die relative Zeile, ADDRESS wandelt eine Zeilennummer und eine Spaltennummer in einen String mit dem Zellennamen um ( ADDRESS(1,1)="$A$1" ), CONCATENATE fügt die Strings zusammen, um einen Bereich zu erstellen.

CONCATENATE(ADDRESS(MATCH("Attached Components",B1:B32,0)+2,3),":",ADDRESS(MATCH("Attached Components",B1:B32,0)+20,3))

Dies gibt eine Zeichenfolge wie „$C$7:$C$25“ zurück. Es deckt also die Beschreibungsspalte ab und beginnt bei der Zeile, in der Sie Ihre Werte haben, bis 18 Zeilen darunter. Um mehr oder weniger Zeilen abzudecken, ändern Sie einfach „+20“ in der Formel in den entsprechenden Wert.

Um die letzte Zeile zu finden, müssen Sie lediglich mit WENN und MIN die erste leere Zelle finden.

{=MIN(
    IF(
        INDIRECT(CONCATENATE(ADDRESS(MATCH("Attached Components",B1:B32,0)+2,3),":",ADDRESS(MATCH("Attached Components",B1:B32,0)+20,3)))="",
        ROW(INDIRECT(CONCATENATE(ADDRESS(MATCH("Attached Components",B1:B32,0)+2,3),":",ADDRESS(MATCH("Attached Components",B1:B32,0)+20,3))))
     )
)-1}

Diese Formel ist eine Array-Formel. Deshalb ist sie in Klammern gesetzt (tippen Sie die Klammern nicht ein, sie erscheinen, wenn Sie die Formel eingeben und dann Strg+Umschalt+Eingabe drücken).

INDIREKT transformiert den von uns eingebauten String in einen Zellbezug. ROW gibt die Zeilennummer als Ergebnis aus. MIN nimmt den kleinsten Wert im zurückgegebenen Bereich. Die "-1" am Ende bedeutet, dass die Zeilennummer der letzten Beschreibung und nicht die der ersten leeren Zeile enthalten ist.

Im Beispiel gibt diese Formel „9“ zurück.

Jetzt haben wir die Zeilennummer der ersten und der letzten Beschreibung, 7 bis 9. Wir können diese Zahlen beliebig kombinieren, indem wir ADDRESS, CONCATENATE und INDIRECT verwenden, um alle gewünschten Operationen durchzuführen. Aber dieses Mal haben Sie einen bestimmten Zellbezug, mit dem Sie arbeiten können.

Beispielsweise eine Material-Nr.-Suche:

VLOOKUP-Beispiel

In diesem letzten Beispiel enthalten die Zellen

E2:

=MATCH("Attached Components",B1:B32,0)+2

F2 (Zur Eingabe mit Strg+Umschalt+Eingabe):

=MIN(
    IF(
        INDIRECT(CONCATENATE(ADDRESS(MATCH("Attached Components",B1:B32,0)+2,3),":",ADDRESS(MATCH("Attached Components",B1:B32,0)+20,3)))="",
        ROW(INDIRECT(CONCATENATE(ADDRESS(MATCH("Attached Components",B1:B32,0)+2,3),":",ADDRESS(MATCH("Attached Components",B1:B32,0)+20,3))))
    )
)-1

F7:

=VLOOKUP(E7,INDIRECT(CONCATENATE(ADDRESS(E2,1),":",ADDRESS(F2,3))),3,FALSE)

Wenn Sie auf diese Weise eine Materialnummer in Zelle E7 eingeben, wird die Beschreibung in Zelle F7 angezeigt.

BEARBEITEN:

Den Ausführungen folgend kann die Lösung folgendermaßen erarbeitet werden:

Verwenden Sie ein komplizierteres Beispiel:

Kompliziertes Beispiel

Der Zeilenabgleich ist lediglich eine Kaskade von zwei MATCH-Funktionen. Verwenden Sie die erste MATCH-Funktion, um die Teilenummer zu finden, und dann die zweite, um den gewünschten Abschnitt zu finden:

Die 2 passenden

F3: eine Zeichenfolge des gesuchten Teils

F4: die Formel zum Suchen der „Teilenummer“ in der ersten Spalte.

=MATCH($F$3,A1:A32,0)

F6: der Name des gesuchten Abschnitts

F7: die Formel zum Suchen des Abschnitts im zuvor identifizierten Teil. Die Übereinstimmung wird in einem Bereich durchgeführt, der in der Zeile „Teilenummer“ (gespeichert in Zelle F4) beginnt. Der Bereich wird mit derselben Art von Formel erstellt, die INDIREKT, VERKETTEN, ADRESSE verwendet. Anschließend wird die von MATCH zurückgegebene relative Zeile um F4-1 verschoben, um die absolute Zeilennummer zu erhalten.

=MATCH($F$6,INDIRECT(CONCATENATE(ADDRESS(F4,2,1),":",ADDRESS(F4+20,2,1))),0)+F4-1

Um nun die erste und letzte Zeile der Beschreibung zu identifizieren, können wir dieselben Formeln wie zuvor verwenden:

erste und letzte Zeile für Beschreibungen

F9: Addieren Sie 2 zur Zeilennummer der Zeile „Angefügte Komponenten“, um die erste Beschreibungszeile zu erhalten.

=F7+2

F10: Suche nach der ersten leeren Zeile im Beschreibungsbereich (beginnend bei der in F9 gespeicherten Zeile). Dies ist eine Array-Formel, die mit eingegeben werden mussCTRL+SHIFT+ENTER

=MIN(
    IF(
        INDIRECT(CONCATENATE(ADDRESS($F$9,3),":",ADDRESS($F$9+20,3)))="",
        ROW(INDIRECT(CONCATENATE(ADDRESS($F$9,3),":",ADDRESS($F$9+20,3))))
     )
)-1

Um dann die Beschreibung anzuzeigen, können wir INDIRECT und eine Indexspalte verwenden:

Anzeigematrix

F15:

=IF($F$9+$E15-1<=$F$10,INDIRECT(ADDRESS($F$9+$E15-1,1)),"")

G15:

=IF($F$9+$E15-1<=$F$10,INDIRECT(ADDRESS($F$9+$E15-1,3)),"")

Diese Formeln zeigen die Materialnummer und die Beschreibung für eine Zeile an, die durch einen Index in der Spalte E identifiziert wird. Die IF-Anweisung stellt sicher, dass wir die Zeilen unterhalb der letzten Zeilen nicht anzeigen. Im Beispiel werden nur 5 Zeilen angezeigt, aber Sie können diese Formel einfach kopieren, indem Sie die erste Zeile nach unten ziehen und neue Indizes hinzufügen, um mehr zu erhalten.

verwandte Informationen