In Excel muss ich Daten auf Blatt3 anhand von Daten auf Blatt1 finden

Question

Ich habe eine Excel-Arbeitsmappe mit mehreren Blättern. Die Spalte 'SheetA'!W:Wenthält Text, der in einer Spaltenüberschrift in Zeile 1 zu finden ist 'Sheet3'!1:1. Enthält beispielsweise SheetA'!W42„B16“. Die Zelle Sheet3'!CB1hat den Überschriftentext „B16: Beispiel 40“, also wäre dies die Zielspalte.

Beispiel: Probe von'SheetA'!W42

In 'SheetA'!CD42gibt es eine Formel, die die Probennummer berechnet, die in zu finden ist 'Sheet3'!A:A. Im folgenden Beispiel CD42wird der berechnete Wert „30“ angezeigt, der in zu finden ist 'Sheet3'!A32.

Beispiel: Beispieldaten aus Blatt 3 mit Anzeige der Überschriften und Schnittpunkte der Spaltenüberschrift (B16) und der Zeile mit der betreffenden Beispielnummer:

Ziel ist es, den Inhalt der Zelle auf Blatt3 abzurufen, in der sich die gesuchten Spalten und Zeilen schneiden, und ihn in einer Zelle auf BlattA anzuzeigen. Im bereitgestellten Beispiel 'Sheet3'!CB:CBwürde an den Schnittpunkten Zeile 32 und BlattA der Wert „0,1950581843“ angezeigt.

Weiß jemand, wie das erreicht werden kann?

Answer 1

Angesichts Ihrer obigen Daten werde ich einige Annahmen treffen. Wenn alle davon zutreffen, HLOOKUPkann ich Ihr Problem leicht lösen. Andernfalls ist möglicherweise eine komplexere Funktionskette erforderlich.

Annahmen:

1. Alle relevanten Kopftexte in 'Sheet3'!1:1sindabsolut einzigartiginnerhalb dieser Zeile.
2. Alle relevanten Header-Texte 'Sheet3'!1:1haben das Format "[X]: [Y]", wobei:
   • [X] ist ein Wert, der in zu finden ist 'SheetA'!W:W.
   • [Y] ist der Wert, 'SheetA'!X:Xder in der gleichen Zeile steht wie [X].
3. Alle möglichen Werte in 'SheetA'!CD:CDsind durchsuchbar 'Sheet3'!A:A, sodass sie sich in derselben Zeile befinden wie die abzurufenden Daten.
   • Ich füge das nur ein, weil mir aufgefallen ist, dass es in Blatt3 einige Wiederholungen dieser Werte zu geben scheint, und ich wollte sicherstellen, dass es keine Bedingungen gibt, unter denen Sie möglicherweise nach einem Wert in suchen, der 'SheetA'!CD:CDstattdessen mit einer anderen Spalte abgeglichen werden sollte (und daher bei einer Übereinstimmung in möglicherweise ungenaue Ergebnisse liefern könnte 'Sheet3'!A:A).
   • Wenn diese Wiederholungen nur der besseren Lesbarkeit dienen, darf ich dann stattdessen die Verwendung der Funktion „Fenster fixieren“ vorschlagen?
4. Alle relevanten Werte in 'Sheet3!A:A'sind perfekt sequenziell, beginnend mit "1" in 'Sheet3'!A3, wiederholen sich nie innerhalb dieser Spalte undstetsaufsteigend sortiert.

Unter Berücksichtigung der oben genannten Annahmen habe ich meine eigenen Tabellen mit Beispieldaten erstellt, die Ihr Szenario in etwa darstellen.

Hier ist ein Teil-Screenshot meines „Sheet3“.

Und eine Teilaufnahme meines „SheetA“.

Liste der Analoga:

• Blatt3
  • Mein A:A = Dein A:A, BY:BY:, CA:CA:, ...
  • Mein B:B, C:C, D:D, ... = Dein B:B, BZ:BZ, CB:CB, ...
• BlattA
  • Mein A:A = Dein W:W
  • Mein B:B = Dein X:X
  • Mein C:C = Dein CD:CD
  • Mein D:D = Die Spalte, in die Sie die gefundenen Daten ablegen möchten.

Wie Sie im zweiten Screenshot sehen können, lautet die Formel für D2:

=HLOOKUP(CONCATENATE(A2,": ",B2),Sheet3!A:Y,C2+2,FALSE)

Schritt-für-Schritt-Anleitung zur Formel:

HVERWEISermöglicht Ihnen, horizontal in einem Zellbereich nach einem Wert zu suchen und dann den Wert einer Zelle in derselben Spalte basierend auf einer relativen Zeilenposition zurückzugeben. Es werden vier Argumente benötigt, von denen drei obligatorisch sind:Lookup-Wert,Tabellenarray,Zeilenindexnummer,[Bereichssuche]. Dies ist die Formel, die die Arbeit erledigt, die gewünschten Daten in Blatt3 zu finden und in eine Zelle in BlattA zu ziehen.

• Lookup-Wertist der Wert, den Sie HLOOKUPzuerst finden möchten. Dieser Wertmussin der ersten Reihe anwesend seinTabellenarray, da dies die einzige Zeile ist, die durchsucht wird. HLOOKUPgibt auch nur die erste Übereinstimmung zurück, daher sollten diese Werte ebenfalls eindeutig sein. Hier verwenden wir, CONCATENATEum unsere Suchzeichenfolge zu erstellen.
  
  • VERKETTENermöglicht Ihnen, verschiedene Zeichenfolgen und Werte zu einer Zeichenfolge zusammenzufassen. Es akzeptiert eine Reihe von Argumenten, sortiert nach der Reihenfolge, in der sie in der resultierenden Zeichenfolge eingefügt werden sollen.
    
    • A2ist unser erstes Argument für CONCATENATE. Der erste Teil unserer Zeichenfolge ist der „Beispielname“ in A2.
    • ": "ist unser zweites Argument für CONCATENATE. Dies fügt den Doppelpunkt und das Leerzeichen in die Zeichenfolge ein, um dem Format der Werte in zu entsprechen 'Sheet3'!1:1.
    • B2ist unser letztes Argument für CONCATENATE. Es zieht die „Sample-ID“ aus B2, um die für Header in verwendete Syntax zu vervollständigen 'Sheet3'!1:1.
• Tabellenarrayist ein Verweis auf einen Zellbereich, HLOOKUPmit dem Sie arbeiten möchten. Denken Sie daran, dass die erste Zeile diejenige sein muss, dieLookup-WertDieser Bereich muss auch alle möglichen Werte für umfassenZeilenindexnummer.
  • Tabelle3!A:Yist ein Verweis auf alle Zellen in den Spalten A bis Y (die einzigen, die in meinem Sheet3 ausgefüllt sind) von Sheet3. Dadurch wird sichergestellt, dass alle Daten, die später in neuen Zeilen hinzugefügt werden, auch im Suchbereich liegen. Wenn Daten in neuen Spalten statt in Zeilen hinzugefügt werden sollten, würde ich den Verweis verwenden wollen Sheet3!1:32(derzeit ist 32 die letzte ausgefüllte Zeile in meinem Sheet3). Wenn Daten durch neue Spalten hinzugefügt werden könntenUndneue Zeilen, würde ich mit Sheet3!1:1048576oder auf das gesamte Blatt verweisen Sheet3!A:XFD. (Hinweis: Der Verweis auf „gesamtes Blatt“ gilt für Excel 2013. Frühere Versionen haben möglicherweise kleinere Zeilen-/Spaltenbeschränkungen – passen Sie 1048576diese XFDentsprechend an.)
• Zeilenindexnummerist eine positive Ganzzahl, die eine Zeilenposition relativ zur obersten Zeile darstellt inTabellenarray. Dies gibt an HLOOKUP, welche Zelle Sie aus der übereinstimmenden Spalte zurückgeben möchten. Beachten Sie, dass HLOOKUPnur nachLookup-Wertin der obersten Reihe vonTabellenarray, UndZeilenindexnummerdarf nicht negativ sein, Sie können nicht verwenden HLOOKUP(zumindest nicht allein), um Informationen aus Zellen zurückzugeben, die überLookup-Wert.
  • C2+2- Da alle Werte in 'Sheet3'!A:Aperfekt sequenziell sind, keine ganzen Zahlen übersprungen werden und immer aufsteigend sortiert werden, können wir diese Werte (auch in dargestellt 'SheetA'!C:C) als Indikatoren für die Zeilennummern der Daten verwenden, die wir finden möchten. Das +2ist vorhanden, um der Tatsache Rechnung zu tragen, dass die Nummerierung in Zeile 3 von Blatt3 mit „1“ beginnt.
• [Bereichssuche]ist ein optionales Argument für HLOOKUP. Die Optionen sind TRUE oder FALSE, um anzugeben, ob ungefähre Übereinstimmungen gültig sein sollen oder ob nur genaue Übereinstimmungen zulässig sein sollen. Excel verwendet standardmäßig TRUE (ungefähre Übereinstimmung), wenn dieses Argument weggelassen wird, was häufig zu unerwünschtem Verhalten führen kann – insbesondere, wenn Ihr Blatt nicht auf bestimmte Weise sortiert ist. Daher geben wir anFALSCHhier, um sicherzustellen, dass HLOOKUPnur eine exakte Übereinstimmung ausgewählt wird.

Wenn Sie das Obige an das Layout Ihres Blattes anpassen, ist dies meines Erachtens die Formel, die Sie für die Zelle benötigen 'SheetA'!CE42(vorausgesetzt, Sie möchten die Daten dort einfügen):

=HLOOKUP(CONCATENATE(W42,": ",X42),Sheet3!A:CB,CD42+2,FALSE)

Beachten Sie, dass, wenn Ihre Daten in Sheet3 weiter rechts als Spalte CB liegen und/oder Daten zu weiteren Spalten hinzugefügt werden, Sie diese anpassen müssen.Tabellenarrayentsprechend.