Excel: Verknüpfung beibehalten obwohl in der Quelldatei zusätzliche Zeilen eingefügt

[Hitsch1]

Ich habe eine einfache Verknüpfung zwischen einer Produktkalkulation (Zieldatei) und einer Budgettabelle (Quelldatei). Es handelt sich um zwei verschiedene Dateien.

Dabei muss die Budgetmenge aus der Budgettabelle immer in die gleiche Zelle in der Produktkalkulation. Dazu habe ich eine Verknüpfung erstellt.

Jetzt kommt es öfters vor, dass in der Budgettabelle zusätzliche Artikel und damit hergehend zusätzliche Zeilen eingefügt werden müssen. Dadurch verschiebt sich die Quellzelle von z.B. AI143 auf AI152. Leider übernimmt die Zielzelle in der Produktkalkulation diese neue Position (AI152) nicht, sondern bleibt auf AI143 und übernimmt damit falsche Daten. Dies wenn beide Tabellen bearbeitet, geschlossen wieder geöffnet werden. Verknüpfungs-Akualisierung beim Öffnen mache ich natürlich.

Ich hoffe ihr könnt meiner Ausführung folgen… weiss jemand eine Lösung? Danke!

 

[Turakos]

Grüezi Hitsch1

Folgende Lösungsansätze:

Falls es wenig Zellen sind, ordne ich den Quell-Zellen je einen Namen zu - ist aufwändig, wenn viele Zellen verknüpft werden müssen.

In der Formel wird beim Verknüpfen ein absoluter Bezug gesetzt, also z.B. hier stehen die anderen Angaben zur Verknüpfung \[a Daten für Übungen 01.xlsx]data21'!$I$11 setze den Bezug auf relativ also ohne die beiden $-Zeichen. Kursiv sind meine Beispiel-Daten.

Zeige deine Formel und nenne deine Excel-Version, so kann dir besser geholfen werden.

 

[Hitsch1]

Danke Turakos.

Ich arbeite mit Excel 2013

Auf relativ setzen habe ich schon versucht. Funktioniert trotzdem nicht. Die Anpassung der Verknüpfung funktioniert nur solange ich beide Dateien offen habe.

Im Anhang habe ich drei Printscreens der Excel-Dateien. Beide nebeneinander offen. Darin sieht man die Formel.
Bei Produkt A müsste immer das Budget von Kunde C stehen, also 3000 (Datei "Verknüpfung1").

 

[Turakos]

Hallo Hitsch1

aus deinen Informationen ist ein eindeutiger Key nicht zu erkennen. Ich denke ein Key z.B. als eindeutiger Key könnte die Kundennummer dienen, das wäre eine Möglichkeit die Daten mit einer Funktion zu Verknüpfen z.B. SVERWEIS, Kombination von INDEX und VERGLEICH usw. Ich habe noch an INDIREKT gedacht, diese Funktion lässt keine Verbindung zu geschlossenen Arbeitsmappen zu oder an die Funktion ADRESSE um die Bezüge rechnerisch aufzubauen.

Weshalb sind die Daten in zwei verschiedenen Arbeitsmappen? Wie viele Datensätze sind in den Tabellen?

Die zur Verfügung gestellten Screens sind wohl kaum der ganze Datenbestand? Ein umfangreichere Beispiel-Datei könnte einer Lösung des Problems besser dienen!

Denke an die klassische Drei-Teilung der beabsichtigten Anwendung: Tabelle für die Daten, Tabelle für die Auswertung (Übersicht mit Formeln die notwendigen Daten gelesen) und Tabelle mit der Anzeige z.B. Pivot-Table, Pivot-Chart und/oder ein Diagramm.

 

[Hitsch1]

Hallo Turakos, danke für deine Bemühungen!

Mit den Screens wollte ich nur meine Problematik verdeutlichen. Die Problematik stellt sich etwas komplexer dar. Darum wollte ich eben zwei Excel Tabellen hochladen. Leider wird das in diesem Forum nicht unterstützt. Nur pdf, jpg etc. und die dürfen im Total nicht mal 1 MB gross sein! Jetzt habe ich nochmals zwei Screens gemacht und davon bringe ich auch nur noch eines hoch. Hast du vielleicht eine Emailadresse wo ich dir die zwei Excel Dateien hinsenden kann?

Nachtrag: habe ein zip-file hochgeladen

 

[Turakos]

Hallo Hitsch1

Das ZIP-File habe ich geöffnet und entpackt, darin enthalten sind:

Arbeitsmappe 1: "Absatzplanung 2015".xlsx mit einer Tabelle "Absatzplanung 2015"
Arbeitsmappe 2: "Produktkalkulation 2015".xlsx mit den Tabellen "Produkt A" und "Produkt B"

Ich gehe davon aus, dass in der "Produktkalkulation" für jedes Produkt und je Kunde eine Tabelle erstellt wird, falls das so ist, müssten die Tabellennamen eindeutig sein.

Arbeitsmappe 2 befindet sich die Ziel-Zelle oder -Zellen, welche? z.B. E5(?)
Arbeitsmappe 1 befindet sich die Quell-Zelle oder -Zellen, welche? z.B. AI16 für Produkt A und Kunde 1(?)

Noch eine Frage zu den Begriffen: Produkt A und Artikel A ist das das Gleiche?
Gibt es keine Artikelnummern z.B. mit allen Zeichen ein String, z.B. A145789?

Für eine Lösung sind zwei Suchkriterien notwendig, die beiden Begriffe müssen auf den Tabellenblättern vorkommen.

Als Lösung sehe ich nicht eine Verknüpfung auf eine bestimmte Zelle, sondern eher eine Funktion oder eine Kombination von mehreren Funktionen. Die Listen in den Tabellen müssten als Tabellen formatiert werden, so kann u.U. mit strukturierten Verweisen eine Lösung gefunden werden.

Meine private E-Mailadresse schreibe ich nicht in den Thread. Problemlösung auf privater Basis ist nicht der Zweck des PCtipp-Forums.

 

[Hitsch1]

Deine Annahmen sind soweit richtig.

Arbeitsmappe 1/"Absatzplanung 2015": Die pink gefärbten Zellen in der Spalte AI sind die Quell-Zellen (zB. AI5, AI8, AI14). Das ist auch die Tabelle in die die neuen Produkte und somit Zeilen eingefügt werden müssen.

Arbeitsmappe 2/"Produktkalkulation 2015": Die ebenfalls pink gefärbte Zelle E5 ist die Ziel-Zelle.

Produkt A und Artikel A ist das Gleiche.

Doch es gibt Artikelnummern. Wie mir erst jetzt auffällt sind diese aber nicht immer im Excelblatt aufgeführt. Die Dateien heissen z.B. "Kalk Frigemo 2014". Und innerhalb sind die Tabellen mit der Artikelnummer beschriftet (z.B. 11500). Aber es liesse sich problemlos nachholen, z.B. in die Zelle K1 die eindeutige Artikelnummer einzufügen (11500, 12000, 12015,...).

Die von dir skizzierte Lösung verstehe ich nicht. Wie meinst du das mit den Listen als Tabelle zu formatieren?

 

[Turakos]

Hallo Christian

Eine Liste als Tabelle formatieren - da mag ich nicht alles dazu schreiben. Google bitte, suche nach:
"Excel 2013 als Tabelle formatieren" da findest du eine Vielfalt von Informationen auch Videos.

Den Zugang zum "Tabelle formatieren" findest du im
Menü Start > Gruppe Formatvorlagen > Als Tabelle formatieren

Erstell einmal eine neue Arbeitsmappe und trage ein paar Daten ein, etwa 5 Spalten und etwa 10 Zeilen jede Spalte mit einer Überschrift (Spaltentitel = ein Wort). Dann kopierst du die Tabelle (ganzer Reiter kopieren) das gibt eine neue Tabelle, entsprechende Tabellennamen wählen.

In einer Tabelle markierst du die Zelle A1 dann formatierst du diese Liste (das sind die Daten, die du eingetragen hast) als Tabelle formatieren, es folgt ein Fenster, dieses beantwortest du. Die so formatierte Tabelle (die Liste mit den Daten) hat neu einen Autofilter. Vergleiche die beiden Tabellen, die formatierte Tabelle hat am Ende der Daten nach der rechten Spalte eine Markierung.

Nach meiner Auffassung ist der Umgang mit einer so formatierten Tabelle einfach und bietet auch funktionelle Vorteile. Selber probieren öffnet dir den Zugang zu diesem Feature.

 

[Hitsch1]

Danke für deine Hilfe Turakos. Eine schnelle Lösung scheint es nicht zu geben. Ich werde mich sobald es die Zeit zulässt mal reinknien.

 

[Turakos]

Guten Abend Christian

Ich habe die Arbeitsmappe "Absatzplanung 2015aa" mit der Tabelle Absatzplanung 2015 etwas umstrukturiert, so könnte der Zusammenzug möglicherweise etwas einfacher werden. Du findest das modifizierte Dokument im ZIP-File, was meinst du dazu? Besitzstand ist gewahrt, oben in der Tabelle ist der Zusammenzug je Produkt, unten sind die Detailwerte bezogen auf Produkt und Kunde, schau die Daten, die Formeln, die Zellformatierungen einmal an. Es sind noch keine Datenblöcke als Tabelle formatiert.

 

[Turakos]

Hallo Christian

Ich habe die 3 Tabellen Produkt A, Produkt B und Absatzplanung 2015 in eine Arbeitsmappe vereint.
In den beiden Produkt-Tabellen die Formeln eingefügt zum Anzeigen der Werte nach der Beziehung Produkt . zu Kunde . Entspricht dies deinen Vorstellungen. Beide Formeln sind Matrix-Formeln eingeschlossen in { ... }.
Das Dokument wurde formell etwas angepasst, wegen der Bearbeitung mit meinen Einstellungen.

 

[Hitsch1]

Hallo Turakos

Wir haben duzende kundenspezifische Kalkulationen und man wollte nun dass diese aus der Budgettabelle einzeln "gefüttert" werden (Zelle E5 in der Produktkalkulation). Nicht mehr nur als Summe.

Ich habe es nun mit "sverweis" gelöst. Dafür musste ich jedem Kunden und jedem Artikel einen unmissverständlichen Namen zuweisen (siehe Spalte C in der Budgetabelle und Zelle K1 in den Produktekalkulationen). Bei der Erstellung der Formel habe ich als Matrix die Spalten C bis AI bestimmt. Unbegrenzte Zeilen. Nun klappt es sehr gut, auch wenn ich in der Budgetabelle neue Artikel einfügen muss und bei geschlossener Datei.

Deinen Ansatz behalte ich in meiner Ablage für weitere zukünftige Herausforderungen. Macht ja schon fast Spass ;) !
Danke dir für die Hilfe!

 

[Turakos]

Hallo Hitsch1

Vielen Dank für die Rückmeldung. Excel lässt für Verweis-Funktionen einige Möglichkeit zu > es führen viele Wege nach Rom. INDEX mit VERGLEICH ist flexibler, z.B. bei der Anzahl Suchkriterien.