+- Office-Fragen.de (https://office-fragen.de)
+-- Forum: Microsoft Office (https://office-fragen.de/forum-1.html)
+--- Forum: Excel (https://office-fragen.de/forum-2.html)
+--- Thema: Excel Verweis mit Versatz und Lücken (/thread-29005.html)
Excel Verweis mit Versatz und Lücken - MaLin - 08.12.2023
Hallo zusammen,
nachdem ich neulich eine Frage hatte zum Verschieben von Verweisen und dem Berücksichtigen von Versätzen, stoße ich nun erneut an meine Grenzen.
Habe folgendes Problem:
Für einen Code in der IT-Abteilung muss ich für ein einfaches copy-paste ein bestimmtes Schema einhalten.
D. h. aus einer Datentabelle will ich in einer Spalte auf entsprechende Codewerte verweisen.
Diese sind immer einem Artikel zugeordnet und sollen in einer langen Liste untereinander stehen.
Problem:
Die Datentabelle ist vertikal gegliedert nach Artikelname und den darunter liegenden Codes. --> soweit gut
Die Spalte die ich für den Kollegen benötige muss aber immer wiederkehrend eine Überschrift enthalten, darunter den Artikelnamen, darunter die Codewerte (immer die gleiche Anzahl an Spalten), danach ein Endsymbol und dann geht es genauso weiter.
Durch diese formativen Überschriften usw. habe ich natürlich Sprünge in den Verweisen auf die Codewerte.
Wie kann man das per Formel oder dergleichen anpassen, da wir hier in Richtung von 30.000 Zeilen gehen?
Hier ein Beispiel:
In Spalte "D" soll ich dieser Form immer auf die Werte aus Spalte "A2 verwiesen werden; aber immer mit den Fett und Kursiv eingefügten Unterbrechungen
![[Bild: Screenshot-2023-12-08-113104.png]](https://i.ibb.co/StTTQRf/Screenshot-2023-12-08-113104.png)
Ich hoffe es ist ersichtlich dass sich der Code-Bereich immer etwas nach unten verschiebt.
Wie kann ich da eine Formel einstellen ohne Werte auszulassen, die durch die Überschriften und Endsymbole entstehen?
Dankeschön
RE: Excel Verweis mit Versatz und Lücken - HKindler - 08.12.2023
Hi,
Für ein aktuelles Excel:
Code:
=LET(
a;B3:B99999;
b;15;
c;ÜBERNEHMEN(a;ANZAHL2(a));
d;SPALTENUMBRUCH(c;b;"");
e;MATRIXERSTELLEN(2;SPALTEN(d);LAMBDA(z;s;WAHL(z;"Ende";"Überschift")));
f;VSTAPELN(d;e);
g;ZUSPALTE(f;;1);
h;WEGLASSEN(g;-1);
h)a legt den Bereich fest
b Anzahl Zeilen von "Artikel" bis "code14"
c beschränkt den angegebenen Bereich auf gefüllte Zellen
d bricht die Spalte nach b Zeilen um, so dass man die Blöcke nebeneinder stehen hat
e erzeugt eine Matrik mit zwei Zeilen und der Anzahl Spalten von d mit den Texten "Ende" und "Überschrift"
f erzeugt eine Matrix mit d und e übereinander
g schnappt sich die einzelnen Spalten von f und stapelt diese übereinander
h schneidet das letzte "Überscrift" weg
Und das nächste mal bitte kein Bild, sondern eine Datei zur Verfügung stellen!
RE: Excel Verweis mit Versatz und Lücken - MaLin - 13.12.2023
klasse,
das funktioniert wunderbar.
Vielen Lieben dank.
wie kann man das noch erweitern wenn man immer an dritter stelle nach "Artikel" eine bestimmte Bezeichnung noch einfügen will und sich damit alles weitere nach unten verschiebt?
Ich hätte gerne immer direkt nach Artikel das Wort: Segment
und alles Weitere soll dann wie oben beschrieben so folgen
kann man das in die formel einbauen?
RE: Excel Verweis mit Versatz und Lücken - HKindler - 13.12.2023
Hi,
ist "Artikel" ein fester Text, wie in deinem Bild suggeriert, oder wechselt der Text?
Wo ist die Beispieldatei mit Wunschergebnis?
Muss man wieder eine Woche auf eine Rückmeldung warten?
RE: Excel Verweis mit Versatz und Lücken - MaLin - 13.12.2023
Hi,
sorry war eine Woche im Krankenstand.
Hier mal eine Beispieldatei.
In Spalte "F" spielt sich die Formel ab.
Im Grunde ist die soweit wunderbar.
Hatte nur vergessen dass ich immer nach der variierenden Artikelbezeichnung (<--Artikel)
immer den Zusatz "Segment" darunter benötige.
RE: Excel Verweis mit Versatz und Lücken - HKindler - 13.12.2023
Hi,
Code:
=LET(a;D4:D100000;
b;25;
c;ÜBERNEHMEN(a;ANZAHL2(a));
d;SPALTENUMBRUCH(c;b;"");
d_1;ÜBERNEHMEN(d;1);
d_2;WEGLASSEN(d;1);
e;MATRIXERSTELLEN(2;SPALTEN(d);LAMBDA(z;s;WAHL(z;"],),";"OptionCode(modelCode:")));
e_1;MATRIXERSTELLEN(1;SPALTEN(d);LAMBDA(z;s;"Segment"));
f;VSTAPELN(d_1;e_1;d_2;e);
g;ZUSPALTE(f;;1);
h;WEGLASSEN(g;-1);
h)Die neu hinzugekommenen bzw. ergänzten Variablen solltest du mittlerweile verstehen. Wenn nicht, einfach nochmals nachfragen.
RE: Excel Verweis mit Versatz und Lücken - MaLin - 13.12.2023
wow, ja funktioniert einwandfrei
danke schön
RE: Excel Verweis mit Versatz und Lücken - MaLin - 13.12.2023
welche Sphären von Excel hast du hier denn beschritten?
Ich muss gestehen, dass ich in diesen Bereich noch nie vorgedrungen bin.
RE: Excel Verweis mit Versatz und Lücken - HKindler - 13.12.2023
Ein recht spannendes Thema, diese neuen Spill-Funktionen. Und für mich auch noch relativ neu und spannend. Aber es lohnt sich, sich mit denen auseinander zu setzen.