Power Query und eindeutige Zeilen zuordnung

[Knochenjonny]

Schönen guten Tag,
ich habe ein Problem mit meiner Excel Tabelle (wer hätte das gedacht ^^)
Ich habe dieses Problem bereits mit G. M. von excel-ist-sexy.de diskutiert und den Hinweis bekommen mich lieber an ein größeres Forum zu wenden.

Was ich vorhabe und was bereits funktioniert:
Excel liest mir einen Ordner auf meiner Festplatte aus und gibt mir den Inhalt hübsch sortiert wieder.
Lösung- Power Query.

Es handelt sich übrigens um mein Anime Backup Archiv (es geht also um Filme/Serien)

Was ich möchte:
zusätzlich zu den erzeugten Spalten (was eben mit Power Query möglich war, Größe, Pfad, Name, Atributte ob die Datei versteckt ist und Dateityp) eine Spalte mit dem Inhalt um welche Sprache es sich handelt.

Schreibe ich per Hand in die nächste freie Spalte als Überschrift "Sprache" und darunter in jeder Zelle die entsprechende Sprache die zur gesamten Zeile gehört geht das auch!!!.
Aber: jetzt habe ich ja Power Query genommen um mit einem klick auf Aktualisieren die Liste zu erweitern falls es neue Dateien in den Ordnern gibt und genau jetzt versagt Excel :'(

Als Beispiel habe ich eine Test Excel erstellt mit den selben Paramtern, um diese zu nutzen muss auf Laufwerk C ein Ordner namens "test" erstellt werden mit leeren Textdokumenten
EP01.txt, EP02.txt, ..... EP10.txt, Special01.txt, Special02.txt  die Special sind wichtig um mein Problem zu verbildlichen.

So lange wie keine neue Datei hinzugefügt wird ist alles toll, jetzt erstelle ich einfach eine neue Testdatei in meinem Ordner "Neues Textdokument.txt", diese Datei liegt nun zwischen EP10 und Special01.
Jetzt drücke ich in Excel Power Query aktualisieren und er fügt eine komplett neue Zeile hinzu an genau der Stelle zwischen EP10 und Special01.
ABER, was Excel dabei nicht berücksichtigt sind meine von Hand erstellten Zellen, diese verbleiben an Ort und Stelle und werden nicht (obwohl ja eine neue Zeile hinzu kam) hoch oder runter verschoben.

Jetzt hat Neues Textdokument.txt plötzlich eine Sprache zugeordnet und die darunter liegenden Episoden sind auch nicht mehr richtig benannt.

Ich habe es mit SVERWEIS probiert mit der Vergabe eindeutiger ID mit INDIREKT, es bleibt immer beim letzten Schritt hängen, Excel muss die Zeilen zusammen behalten auch wenn es neue hinzufügt müssen meine Zuordnungen erhalten bleiben quasi festgeklebt. Das geht aber so überhaupt garnicht :(

Als Beispiel was ich Herrn M. geschrieben hatte

Original Ordner hat 2 Dateien (text1 & text3)


A1 text1 size.5 Sprache.x
A2 text3 size.6 Sprache.y
A3 leer

Neue Datei text2 wird im Quellordner erstellt (text2)
Sortiert nach Alphabet liegt text2 nun in Spalte A2

A1 text1 size.5 Sprache.x
A2 text2 size.2 Sprache.y
A3 text3 size.6

Die Query (a* bis size.*) wird automatisch von excel verschoben bzw ergänzt ABER meine per Hand beschriftete Spalte "Sprache" wird nicht hoch oder runter geschoben, Obwohl es die selbe Tabelle ist.

Es gibt keinen Weg die Zeilen meiner Spalte mit den Zeilen der Query fest zu verankern das im Falle einer neuen Datei diese ebenfalls mit nach oben oder unten wandert und dadurch immer eindeutig bleibt.

Es hätte dann wie folgt auszusehen

A1 text1 size.5 Sprache.x
A2 text2 size.2
A3 text3 size.6 Sprache.y

Da ich ja vorher noch keine A2 text2 hatte gibts dafür auch keine sprache, das würde ich dann nachtragen.

Ich würde einfach nur gerne wissen ob ich hier an die Grenze von Excel selbst stoße und mein Vorhaben eventuell nicht umsetzbar ist.

Mit freundlichen Grüßen 


Edit:
Aus Datenschutzgründen den Namen durch Initialen ersetzt.
(Mod)

[shift-del]

Hallo

Deine Verweistabelle darf in der ersten Spalte keine Formeln haben sondern nur Eingaben.
Dann sollte diese Aufstellung als Excel-Tabelle formatiert werden (tbl_Sprachen).
Und dann lautet dein SVERWEIS() so:

Code:

=SVERWEIS([@Name];tbl_Sprachen;2;FALSCH)

[Knochenjonny]

Vielen dank für die schnelle Hilfestellung.
Also ich hoffe ich habe das jetzt richtig verstanden,
in der Tabelle1 (da wo Name und Sprache steht) soll die gesamte Spalte A nicht als Formel sein sondern es müssen per Hand eingegeben werden ?
Habe ich erledigt.
Dann soll ich das als Tabelle formatieren, ich habe also jetzt die Matrix Spalte A1:A14 bis B1:B14 markiert und oben auf als Tabelle formatieren geklickt.
Ich konnte jetzt nur irgend ein Design wählen und eben die Matrix auswahl.
Einen Namen konnte ich aber irgendwie nicht zuordnen. tbl_sprache

Die Formel funktioniert daher noch nicht.

Auf jedenfall bin ich unendlich dankbar das mir jemand versucht zu helfen 
MfG

[Knochenjonny]

Ohgott es klappt °_°
Tabellenname steht ganz oben Links in der Symbolleiste wen ein Teil der Tabelle makiert wurde :D
Nu habe ich angeblich schon den Namen tbl_Sprachen gehabt   egal ist nur zu testzwecken, einfach in tbl_test und SVERWEIS angepasst und läuft ^^

Aber ich hatte die Formel nur bis Zeile 13 kopiert, als nun eine neue Datei im Ordner erstellt wurde und ich die Query aktualisierte hatte der noch einen Fehler gemacht.

Was mich interessiert, warum dürfen die Namen in der tbl_sprache / tbl_test nicht als Verknüpfung aus der ersten Tabelle übernommen werden ?
8734 Zeilen händisch schreiben wäre doof aber ich glaube wenn ich die Spalte kopieren kann ich das bestimmt als reinen Text kopieren ohne auf die Tabelle zu verweisen.

Also wenn jetzt was neues hinzugefügt wird was noch nicht vorhanden ist steht #NV, ist zumindestens sofort erkennbar das dort noch was fehlt :)

Was genau ist aber jetzt der Unterschied zu meiner SVERWEISS Variante ? Bzw es lag bestimmt daran das ich das nicht als Tabelle formatiert habe richtig ?

Ich muss das unbedingt verstehen und bin grade ganz dolle happy :)

Tausendfach gedankt  :17:

[Jockel]

...
Was genau ist aber jetzt der Unterschied zu meiner SVERWEISS Variante ? 

Ich muss das unbedingt verstehen und bin grade ganz dolle happy :)

Tausendfach gedankt  :17:

Hallo, der SVERWEIS() hat eine online-Hilfe... 

dein SVERWEIS() --> =SVERWEIS(B:B;Tabelle1!A:B;2;FALSCH) hat als Suchkriterium ein Bereich... Das soll laut Hilfe nicht. Über diese Funktion kannst du auch hier --> einiges nachlesen...

[Jockel]

Hallo, ich habe (noch) nicht verstanden, warum du überhaupt den SVERWEIS() brauchst, wenn du doch Power Query verwendest...????

[Knochenjonny]

Öhm weil ich keine Ahnung habe wie Power Query funktioniert.
Ich habe ewig daran rumgebastelt mir eigene Spalten anzuzeigen in denen ein Wert umgerechnet wird :D
Ich bekomme es zum Beispiel auch nicht hin Spalten der power Query auszublenden, ich brauche die Spalten zum sortieren aber in der Tabelle siehts doof aus naja das sind andere Sorgen ^^

Habe mal kurz zwei Screenshots beigefügt wie es jetzt gerade aussieht :)

MfG

PS: habe den SVERWEIS aus Neugierde natürlich auch nochmal mit meiner Schreibweise getestet (also Bereichsangabe), funktioniert genauso.
Ist keine Kritik an der Funktionsschreibweise aber ich musste das testen um  zu verstehen warum meins nicht geklappt hatt.
Ich habe es verstanden, es muss sich auf eine Tabelle beziehen bzw es dürfen im Verweis nicht Automatisch neue Zeilen eingefügt werden sonst verschiebt es sich halt wieder und passt am Ende nicht.
Nun sehe ich halt wenn was neu ist bei Sprache #NV und füge einfach in der Tabelle mit dem Verweis ne neue Zeile manuell hinzu ^^

[Jockel]

Hallo, dass es genauso funktioniert, stimmt so nicht. Weil die Syntax deiner Formel nicht korrekt ist, aber ein Blick in die Formelauswertung zeigt ja was passiert... Und es scheint, als hättest du weder in die Hilfe, noch auf die gepostete Seite geguckt...

[shift-del]

Was mich interessiert, warum dürfen die Namen in der tbl_sprache / tbl_test nicht als Verknüpfung aus der ersten Tabelle übernommen werden ?

Durch den direkten Bezug geht der Zusammenhang mit der nebenstehenden Spalte verloren, weil der Inhalt sich ändern kann.

Auf die falsche Anwendung des SVERWEIS() wurde ja schon hingewiesen. Dass die Formel trotzdem funktioniert hat liegt an der "impliziten Schnittmenge".

[Knochenjonny]

Wie gesagt ich habe keine Ahnung von allen Funktionen in Excel und musste mir das durch lesen von Beispielen selber zusammen basteln.
Und ich habe es auf sehr vielen Seiten so gelesen,mal als Beispiel was ich noch in meiner Lesezeichen Leiste habe:
www.excellernen.de/excel-sverweis
www.heise.de/tipps-tricks/Excel-SVERWEIS-einrichten-3971576.html

Und es hatt wunderbar funktioniert   
Habe extra eben nochmal bei Excel auf fx geklickt und SVERWEIS gewählt und alles ausgefüllt, Excel schlägt mir selber diese Schreibweise vor.
Bin kein Pro und muss das nutzen was das Programm mir anbietet wieso erzeugt mir Excel denn eine Falsche Formel die aber soweit funktioniert 

Egal ich bin für den extrem hilfreichen Tipp mit der Tabelle und dem funktionierendem Ergebnis sehrrrr dankbar.

Ich klicke dann mal auf erledigt  :15:
Dankeschön.

EDIT:
Jetzt habe ich gesehen was Ihr meint, das Suchkriterium darf natürlich KEIN Bereich sein sondern sollte EIN Wert sein, das habe ich bereits verstanden, ich hatte bloß zu schnell gelesen und hatte das Rot markierte von Jockel als Matrix interpretiert, klar erste Stelle der Formel Suchkriterium  :19:
Noch ein Nachtrag: in der Test Tabelle die als Beispiel diente hatte ich das tatsächlich falsch geschrieben gehabt   :20: ... aber in der eigentlichen Tabelle stand das 100% richtig ich schwöre, nur da hatte ich ja das Problem mit dem hochschieben der Zeilen beim hinzufügen neuer Dateien und das konnte erfolgreich gelöst UND erklärt werden ^^