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

... komplette Frage anzeigen

4 Antworten

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 


Antwort bewerten Vielen Dank für Deine Bewertung
Kommentar von Greeamer
17.12.2015, 16:39

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!!

0

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.

Antwort bewerten Vielen Dank für Deine Bewertung
Kommentar von Greeamer
17.12.2015, 16:22

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.

0

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

Antwort bewerten Vielen Dank für Deine Bewertung
Kommentar von Iamiam
20.12.2015, 00:22

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

0

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.

Antwort bewerten Vielen Dank für Deine Bewertung
Kommentar von Iamiam
18.12.2015, 12:54

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!)

0

Was möchtest Du wissen?