Frage von Greeamer, 106

Excel Verweis von vertikaler Spalte auf horizontale Zeile - KEINE Matrix?

Okay, ich weiß nicht ob man aus der Überschrift verstehen kann worum es geht, aber hier ein "bisschen" detaillierter. Ich möchte gerne das Excel auf Blatt 1 im Bereich "D1:S7" nach einer Artikelnummer sucht und dann die jeweiligen Werte aus dem Bereich "D8:S60" in eine Zeile in Blatt 2 kopiert.

Eigentlich sollte es SEHR simpel sein da "D8:D60" die Werte für den Artikel D1 sind (Wöchentlicher Umsatz) und so weiter. Wenn Excel also die Artikelnummer finden (Bspl. in D5), sollte es lediglich die Spalte "D8:D60" abgehen und die Zahlenwerte nach Blatt 2 "A1:xx1".

Ich werde mich vermutlich wenn die Lösung kommt in Grund und Boden schämen müssen, aber das nehme ich gerne in Kauf wenn ich dieses Dilemma endlich gelöst bekomme...

Danke im Voraus!

Antwort
von BadLuck86, 74

wenn ich das richtig verstanden habe hilft dir ein Sverweis

=SVERWEIS(Suchkriterium;Matrix"D8:S60";Spaltenindex;0)

Suchkriterium ist dann wohl auf deinem Blatt die Artikelnummer, die wird in der Matrix gesucht, dann wird aus der Matrix die Spalte X aus dem Spaltenindex ausgegeben, und hinten die 0 steht für genaue Übereinstimmung

Ist aber nur pro Zeile, wenn du also 20 Artikelnummern in A1:A20 hast, musst du diese Formel in jeder Spalte anwenden. dazu hilft es aber wenn du die Matrix mit $ fest machst ($D$8:$S$60) dann kann man es besser kopieren 


Kommentar von Greeamer ,

Die Verweis-Funktion war auch das erste was mir in den Sinn kam, allerdings habe ich keine normale Matrix wo der Spaltenindex funktionieren könnte. Ich habe unten auf eine andere Antwort nochmal versucht mich genauer auszudrücken.
Die Artikelnummer die es zu finden gilt, steht in der selben Spalte wie die Werte die es übertragen soll. Bspl. Artikelnummer 12345 soll gesucht werden auf Blatt 1. 12345 steht im Feld D1 und die Werte für jede Kalenderwoche gehen von D8 bis D60 und nicht jeder Wert hat in der nebenstehenden Spalte die 12345 um den Wert zu finden.

Eigentlich bräuchte ich eine Option die die Spalte mit einer Nummer finden und dann ab einer bestimmten Zeile alle Folgewerte horizontal kopiert. Es müsste also nur einmal pro Produkt etwas "gefunden" werden um dann die Spalte einmal komplett durchzugehen...

Klingt wahrscheinlich unnötig kompliziert wie ich es versuche zu formulieren...
Danke für deine Hilfe aber schon mal vorweg!!

Kommentar von BadLuck86 ,

Blatt 1:
    A              B            C

1                           123456 

2                            Produkt 1  

3 W             1            0
4 W             2            0 
5 W             3            50.000
6 W             4            30.000

Blatt 2:
       A              B                     C                                   D

3 123456  (Formel1)    (Formel2)        (Formel 3)

 Formel1 (=WVERWEIS($A3;Blatt1!$C$1:$E$6;2;0))

Formel2 (=WVERWEIS($A3;Blatt1!$C$1:$E$6;5;0))

Formel3 (=WVERWEIS($A3;Blatt1!$C$1:$E$6;6;0))

Expertenantwort
von Iamiam, Community-Experte für Excel, 43

Füge in Products ganz oben eine neue Zeile ein

schreibe im anderen Blatt nach A3 Deine Identifizierende Sach-Nr und in B3 die Formel:

=Index(Indirekt("PRODUCTS!"&Spalte(B$1)&":"&Spalte(B$1));Vergleich($A3;PRODUCTS!$3:$3;0))

Diese Formel nach rechts kopieren.

In C wiederholt sich die IS-Nr - wenn dich das stört, Formel in C löschen und die Formeln rechts daneben eins nach links verschieben (nicht kopieren oder die Spalte löschen, das gäbe große Verwirrung!).

Du kannst auch die Original-Nr an diese Stelle schieben, im Ggs zu SVerweis&Co braucht index die Referenzspalte nicht links.

Hast Du eine Zeile korrekt erstellt, kannst Du sie auch nach unten kopieren

Die Nummern kannst Du als Zeile (nunmehr 3) kopieren und in Dein Blatt mit Inhalte-einfügen-Aktion: transponieren auf Spalte A bringen [wähle zum Einfügen aber nicht die ganze Spalte A aus, sondern nur A1! Ansonsten wird das zig-mal wiederholt!]. Alle fehlenden Nummern ergeben eben dann #NV! in allen Formeln, aber die schon vorhandene Liste ist vollständig und in der ursprünglichen Reihenfolge - Du siehst sofort, was/wieviel noch fehlt!.

Achtung: Indirekt passt sich bei Blattnamensänderungen NICHT an , da aus Texten generiert. Du kannst aber einen geänderten Blattnamen per suchen&ersetzen  für die gesamte Tabelle in einem Aufwasch ändern.

Kommentar von Iamiam ,

Wenn Du den Blattnamen der Quelldaten selbstanpassend haben willst, brauchst Du noch eine weitere Zelle im Auswerteblatt (also nicht in Products!). In dieser muss der Text PRODUCTS! stehen, das sei hier die Zelle B2.

Die Formel heißt dann:

=INDEX(INDIREKT($B$2&SPALTE(PRODUCTS!B$1)&":"&SPALTE(PRODUCTS!B$1));VERGLEICH($A3;PRODUCTS!$3:$3;0))

Das PRODUCTS! erhältst Du mit der etwas komplizierten Adressenauflösung:

=TEIL(ZELLE("Adresse";PRODUCTS!A1);FINDEN("]";ZELLE("Adresse";PRODUCTS!A1);1)+1;LÄNGE(ZELLE("Adresse";PRODUCTS!A1))-FINDEN("]";ZELLE("Adresse";PRODUCTS!A1);1)-6)&"!"

(die Formel funktiomiert NICHT im Blatt Products, da dort der Datei-und-Blattname unterschlagen wird!)

Kommentar von Iamiam ,

Jetzt hab ich die Formel(n) noch so angepasst, dass Du in der Quelltabelle KEINE ZEILE EINFÜGEN MUSST.

Die IS-Nr jetzt in Spalte B, hier für B3. In A3 die Formel

=INDEX(INDIREKT("PRODUCTS!"&SPALTE(PRODUCTS!A$1)&":"&SPALTE(PRODUCTS!A$1));VERGLEICH($B3;PRODUCTS!$2:$2;0))

liefert Dir den Produktnamen, die IS-Nr wie schon gesagt in B3, in C3:

=INDEX(INDIREKT("PRODUCTS!"&SPALTE(PRODUCTS!C$1)&":"&SPALTE(PRODUCTS!C$1));VERGLEICH($B3;PRODUCTS!$2:$2;0))

ab da nach rechts rüberziehen, fkt bei mir einwandfrei.

Melde Dich über eine Bewertung von irgendwas, zB dieses Kommentars und gib bitte Rückmeldung, evtl auch weitere Fragen dazu.

Kommentar von Iamiam ,

Hat mir keine Ruhe gelassen, und ich hab tatsächlich noch eine viel einfachere Formel gefunden: in A3 die IS-Nr, in B3:

=INDIREKT(Quellblattname&ADRESSE(SPALTE(A$2);VERGLEICH($A3;indirekt(Quellblattname&"2:2";0)))

Ich hab hier zusätzlich noch die Zelle mit PRODUCTS! als Bereich Quellblattname benannt, ist aber nicht essentiell und könntest auch ein Kürzel, zB QBN verwenden (Das ist die Zelle, die NICHT in Products steht, weil nur ausserhalb die Adressen-Auflösung richtig funktioniert.

alles nach rechts kopieren, ggf das IS.Nrn-Doppel wieder löschen und IS-Nr von A3 dahin verschieben (ist ja auch kein MUSS)

So, und jetzt reichts!

Expertenantwort
von Iamiam, Community-Experte für Excel, 24

Damit die Kommentare nicht endlos werden, hier noch eine neue AW:

Wenn Du tatsächlich weder Zeile noch Spalte weißt, in der die IS-Nr steht: xl hat immer noch eine Möglichkeit:

Die SpaltenNummer erhältst Du mit (in Zeile3, IS-Nr in A3)

=Summenprodukt((Produkte!$D$1:$S$7=$A3)*Spalte(Produkte!$D$1:$S$7))

Die Werte in der gefragten Position dann mit

=indirekt(Adresse(Spalte()+6;Summenprodukt((Produkte!$D$1:$S$7=$A3)*Spalte(Produkte!$D$1:$S$7))))

(Trockenübung, werds morgen kontrollieren,stimmt aber wahrscheinlich)

Der Ausdruck Spalte()+6 liefert -entgegen dem Anschein- die Zeile, Summenprodukt die Spalte. Die IS-Nr darf aber nur EINMAL drinstehen, ansonsten müsste noch ein Zählenwenn=1 als Prüfung dazu

Kommentar von Iamiam ,

Da waren doch noch Fehler drin, so gehts richtig (in A3 die Nummer, in B3 die Formel):

=INDIREKT(Quellblattname&ADRESSE(SPALTE()+6;SUMMENPRODUKT((PRODUCTS!$D$1:$S$7=$A3)*SPALTE(PRODUCTS!$D$1:$S$7))))

Liefert bei Nichtvorhandensein im Suchbereich #WERT!

Formel nach rechts kopieren, so viele Zeilen wie ausgewertet werden sollen, und dann alles nach unten kopieren so viele IS-Nrn gefragt.

zu Quellblattname siehe meine vorige AW, ist ja "nur" zum Automatisieren bei Umbenennung des Blatts PRODUCTS nötig.

Indirekt ist da sehr heikel, da es den Blattnamen als Text liest und "unkritisch" übernimmt (ist ja auch die Aufgabe von indirekt(), eben NICHTS anzupassen), wohingegen das PRODUCTS! in Summenprodukt automatisch nachgeführt wird.

Für Open Office / Libre Office müsste man wegen der anderen Adressen-Syntax einiges umschreiben.

Gegen mehrfaches Auftreten einer IS-Nr im Suchbereich kann

=Wenn(Zählenwenn(PRODUCTS!$D$1:$S$7;$A3)>1;"Nr mehrfach vorhanden!";...Formel...)

helfen

Kommentar von Iamiam ,

übrigens: Schämen musst du Dich überhaupt nicht, ich hab im Beruf viel mit xl gearbeitet, aber das hätte ich damals auch nicht gekonnt. Matrixformeln (und Summenprodukt ist als verkappte Matrixformel brauchbar) hab ich erst mit der Muße eines Rentners hier gelernt.

Kommentar von Iamiam ,

zwei Alternativen für mehrfach auftretende IS-Nrn im Bereich:

1.: Füge ein Max( an dieser Stelle ein: SUMMERPRODUKT(Max(... ,dann wird nur die höchste der Fund-Spalten genommen. Min geht nicht so einfach (da evtl viele 0-Werte als Min genommen werden, es gibt kompliziertere Lösungen, aber umständlich)

2. Suche die IS-Nr und trag die SpaltenNr manuell in den Spaltenteil von Adresse ein, also anstatt SUMMENPr...))) (zwei Klammern müssen übrigbleiben). Wird ja nicht so häufig vorkommen und Du kannst Dir das mit der Vorschaltfunktion

=Wenn(Zählenwenn($D$1:$S$1;$A3)>1;"IS-Nr Mehrfach";...Formel...)

anzeigen lassen.

3. Ansonsten einen reduzierten Bereich spaltenweise oder zeilenweise erweitern. Ist aber umständlicher als 2.

Kommentar von Iamiam ,

und immer nochmal was:

die -zweit-/dritt-höchste Spaltennummer eines mehrfachen Eintrags finden: Summenprodukt akzeptiert auch anstatt Max(...) die Fkt. KGrösste(...;1) oder ;2) etc, man braucht dann nur eine einzige Ziffer der Formel in B zu ändern und dann die Formel zu kopieren, um das Ergebnis für mehrere gleiche Einträge erst mal anzusehen und dann endgültig zu belassen oder nicht.

(Das wäre auch nochmal zu vereinfachen, indem man ;1) durch eine $Zelladresse oder Bereichsnamen ersetzt, das wirkt sich dann allerdings auf ALLE Ergebnisse aus.)

Expertenantwort
von Suboptimierer, Community-Experte für Excel, 39

Gib mal zwei drei übersichtliche Beispielzeilen zur Visualisierung des Problems. In etwa so:

     D       E ...
1 | ... | ... | 2 | ... | ... | 3 | ... | ... |

Oder in Form einer Tabelle z. B. auf https://ethercalc.org/

Beschreib dann nochmal genau anhand einer Beispielartikelnummer, zu welchem Ergebnis du kommen möchtest.

Kommentar von Greeamer ,

Das hier wäre eine Produktspalte auf Blatt 1 aus der oben der Code "123456" gesucht werden soll um dann auf Blatt 2 die Werte (50000 in W 5) horizontal wiederzugeben.

Kommentar von Greeamer ,

Hat ja nicht wirklich geklappt mit dem Bild.
Ethercalc.org scheint bei mir einige Probleme zu haben, da ich alle 5 Sekunden disconnected werde.

Also noch einmal in verständlich:
Blatt 1:
    A              B            C

1                           Produkt 1

2                            123456

3 W             1            0
4 W             2            0
5 W             3            50.000
6 W             4            30.000

Blatt 2:
       A              B                     C                                   D

1

3 123456  Produkt 1    (Wert Blatt1 C3)        (Wert Blatt1 C4)

 

Ist das verständlich oder hab ich's noch schlimmer gemacht?

Es kann NUR nach der Nummer (123456) gesucht werden da die Produktnamen oftmals unterschiedlich sind.

Kommentar von Suboptimierer ,

Auf Blatt 2 ist Produkt 1 und 123456 gedreht.

Dies berücksichtigend auf Blatt 2:

A3: 123456
B3: =INDEX(Tabelle1!$A1:$C100;1;VERGLEICH($A3;Tabelle1!2:2;0))
C3: =INDEX(Tabelle1!$A1:$C100;SPALTE();VERGLEICH($A3;Tabelle1!2:2;0))

C3 nach rechts ziehen.

Kommentar von Suboptimierer ,

Also die Pivotzelle kennst du? Das ist deine Artikelnummer. Auf Blatt 1 folgen die Daten dazu vertikal, auf Blatt 2 sollen sie Horizontal stehen?

Blatt 2:

A1: =INDEX(Tabelle1!$A1:$A10;SPALTE();1)

und nach rechts ziehen.

Die Artikelnummer (die Pivotzelle) findest du normal mit VERGLEICH.

Kommentar von Greeamer ,

Also einen Schritt weiter bin ich schon mal!

=INDEX(PRODUCTS!$D$8:$AY$61;VERGLEICH($A$77;PRODUCTS!$D$5:$AY$5;0);VERGLEICH(F71;PRODUCTS!$B$8:$B$61;0))

damit funktioniert's den ersten Wert von KW1 zu bekommen. Sobald ich das nach Rechts ziehe (F71=1; G71=2) und Excel den zweiten Wert abrufen soll, bin ich wieder bei "#NA"... irgendwas übersehe ich hier sturr die ganze Zeit...

Keine passende Antwort gefunden?

Fragen Sie die Community