Excel - Verweis in einer Formel

[BigPsycho]

Hallo,

ich habe folgdendes Problem:
Ich trainiere eine Mannschaft und will für jeden Spieler einen Verlauf der Trainingswerte (Laufzeiten etc.) erstellen, dazu habe ich in der ersten Arbeitsmappe "Stammdaten" das jeweilige Datum und die Werte untereinander.

Die folgenden Arbeitsmappen sollen für jeden Spieler einzeln sein, das heißt eine Übersicht von SEINEN Leistungen, auch inklusive eines Diagramms.

Das Problem ist nun, wie ich diese Arbeitsmappe mit möglichst wenig Aufwand (sind viele Spieler) duplizieren kann. Für die reinen Zeiten ist das kein Problem, die Diagramme einzeln zu ändern ist jedoch mehr Arbeit.

Meine Idee war deswegen: Jeder Zeile im Blatt "Stammdaten" den Spielernamen zu geben und dann in der jeweiligen Spielermappe den Verweis automatisch zu ändern.

Beispiel:
Mappe eins ist für Caro. Die Zeile mit ihren Laufzeiten heißt: "Caro". Das Diagramm gibt die Werte richtig an für: ='Mappe1.xlsx'!Caro .

Wenn ich diese Mappe kopiere, dann stehen im Diagramm logischerweise immer noch "Caros Zeiten" und nicht Markus (nächster Name).
Kann ich jetzt den Befehl der "Reihenwerte" so ändern, dass er sich immer die Zeile aus den Stammdaten sucht, die den Namen aus einer Zelle in der entsprechenden Tabelle enthält, also z.B.:
In Zelle C1 steht immer der Name um den es geht. Kann ich ='Mappe1.xlsx'!Caro in sowas wie ='Mappe1.xlsx'!C1 ändern, also dass er das Diagramm so umbaut, dass die Reihenwerte übernommen werden?

So müsste in dann in jeder Arbeitsmappe nur noch einmal den Namen ändern und die Diagramme wären automatisch aktualisiert. Ist das irgendwie möglich?

Gruß und Dank

 

[Herbboy]

schreib mal auf, welche daten du alle erhebst und welche dann im stammblatt UND bei der jeweiligen mappe (ich nenn das hier tebelle, da es bei mir bei excel auch so heißt) stehen sollen.

und wo notierst du die neuen zeiten? auf dem stammblatt oder der jeweiligen tabelle?

stehen die namen neben- oder untereinander?

ich hab dir mal ein beispiel hochgeladen: http://www.siteupload.de/p867727-Saufzeitenrar.html als rar_datei mit excel97-datei.

hab zwei namen als beispiel, kannst die namen ja auf tabelle 2 oder 3 mal ändern, damit du siehst dass/ob es klappt.

Erklärung:

Im Stammblatt steht in Zeile 5 ab Spalte B die Datumsangaben rechts nebeneinander, In Zeile 6 Spalte A die namen untereinander. rechts von den Namen und unter dem passenden Datum dann die jeweilige zeit.

Die einzelnen tabellenblätter pro Läufer(?) sehen genauso aus, nur dass in A1 "Name" stehe und in B1 dann der Name, und es stehen nur in Zeile 6 Zeiten drin, darunter nix mehr, da es ja nur um EINEN löufer geht.


a) Datum übernehmen für alle Mappen:

Auf der nächsten Tabelle, Tabelle2 steht dann abenfalls ab Zeile 5, spalte B das Datum, und zwar einfach "rübergeholt" vom Stammblatt mit:

=Tabelle1!B5

das markier zieh ich dann nach rechts, so dass die formel "nach rechts" weiterkopiert wird, kennst du ja - so hast du in Zeile B6 dann halt "=Tabelle1!B6" und in B7 dann B7 usw. usw. .... also die gleichen Datumangaben wie im Stammblatt.

Wenn du danach dann in tabelle2 die zeile 5 einfach komplett markierst und kopierst, das auch in zeile 5 von tabelle3 kopierst, dann hast du die FORMELN mitkopiert, und mappe 3 bezieht sich trotzdem auf das Stammblatt, so dass du - was auch immer du am Stammblatt änderst, die Daten übernommen werden für tabelle2, tabelle3 und wietere tabellen.


b) Die Zeiten der leute automatisch vom Stammblatt zu den tabellen kopieren

ich hab bei Tabelle2 Links oben in A1 ja "name" stehen, in B1 musst du dann den namen jeweils per hand eingeben oder dir vom stammblatt rüberkopieren (anders geht es da AFAIK nicht).

dann hab ich in der zelle direkt unter dem jeweiligen Datum, also zum ersten mal in B6, folgendes stehen:

=SVERWEIS($B$1;Tabelle1!$A$6J$28;2)

erklärung: die formel checkt auf der GLEICHEN tabellenblatt, also dem blatt des EINZELNEN Läufers oder was auch immer, die Zelle B1, sie findet dort also den namen vor. Damit es auch beim späteren umkopieren der Formel bei B1 bleibt, stehen da Dollarzeichen vor dem B und der 1.

Dann prüft die Formel, ob und wo der name in dem Bereich vorkommt, in dem auf dem STAMMBLATT die Namen und zeiten stehen. also von A6 bis <rechte untere Ecke des Datenbereiches, also die allerletzte zeit des allerletzten Läufers> wird nach dem Namen gesucht, der in B1 steht. auch hier dollarzeichen, damit der bezug bestehen bleibt.

Wenn der Name gefunden wurde, dann soll der Wert übermittelt werden, der in der betreffenden zeile an der 2. Stelle von rechts kommt (der Name selbst zählt schon als erste stelle!) - d.h. wenn die Formel den namen findet, dann wird dort die erste Zeit des betreffenden Namens stehen.

wenn statt der 2 eine 3 dort steht, dann isses die zweite zeit. steht dort eine 4, dann die dritte zeit usw usw.

wenn die formal fertig da steht, dann zieh sie zum kopieren wie gewohnt nachts rechts bis zum letzten datum. leider musst du die 2, 3, 4, usw. danach aber jeweils manuell ändern.

aber du kannst an sich, sobald du die erste "persönliche" tabellenmappe fertig hast, alles markieren, also oben pber der 1 und links vom A klicken, das ALLES kopieren und inkl. den ganzen formatierungen und farben zur nächsten tabelle einfügen. du musst dann nur jeweils den namen in B1 selber ändern auf jeder tabelle.

so hättest du alle daten, die du im stammblatt eingibst, automatisch auf jedem einzelnen persönlichen tabellenblatt.



wenn du noch nicht weißt, wieviele läufer und datumsangaben noch dazukommen, dann wähle des bereich der formel absichtlich "zu groß", damit du nen puffer hast. wenn du nämlich irgendwann mal zB 30 namen hast, aber die formel geht nur bis A29, dann musst du alles wieder fast von vorn machen...

 

[BigPsycho]

...

Herrlichst, vielen Dank.
Du hast mein Problem verstanden und soweit ich sehe auch gelöst...
Hier nochmal meine Datei, ich hab versucht noch einen Namen hinzuzufügen. Hab den Bereich dafür bis "J8" erweitert. Wieso übernimmt er jetzt Dieters Zeiten in Tabelle 4 nicht? Eigentlich müsste es klappen oder nicht?

http://www.siteupload.de/p868064-Saufzeitenrar.html

Soweit Teil 1. Jetzt brauch ich noch ne Möglichkeit das auch mit Diagrammen vernünftig hinzukriegen, das sollte dann aber nicht mehr sooo schwierig sein, das versuch ich erstmal selbst... sonst meld ich mich nochmal...

Kannst ja nochmal kurz drüber schauen wieso "Dieter" nicht übernommen wird. Das wär super.
Danke nochmals!

 

[Herbboy]

keine ahnung... is mir ein rätsel.

ich hatte vor 2 jahren auch mal ein problem mit sverweis, hab es damals nohmal komplett von vorn neu gemacht, dann ging es ^^ c

 

[BigPsycho]

keine ahnung... is mir ein rätsel.

ich hatte vor 2 jahren auch mal ein problem mit sverweis, hab es damals nohmal komplett von vorn neu gemacht, dann ging es ^^ c

Also irgendwie komm ich da noch nicht so ganz dahinter... ab und zu gehts wie gewünscht, ab und zu übernimmt er halt die falschen Werte. Einen Sinn dahinter habe ich noch nicht gefunden....scheint irgendwas mit der Reihenfolge zu tun zu haben ob man erst den Namen in den Stammdaten einträgt oder in den Spielerblättern, aber so richtig auch nicht. Irgendwie komisch....

aber der Befehl ist nett.

edit: Hm, erstaunlich...also wenn ich nicht gesamte Namen verwende, sondern nur einzelne Buchstaben scheint es zu gehen...

 

[Herbboy]

edit: Hm, erstaunlich...also wenn ich nicht gesamte Namen verwende, sondern nur einzelne Buchstaben scheint es zu gehen...

seltsam.... teste mal, was passiert, wenn du zahlen statt namen nimmst.

 

[BigPsycho]

Hallo,

ich hab das ganze jetzt soweit eigentlcih gelöst, aber ein Problem bleibt noch.

Und zwar:

Wenn ich eine Tabelle kopiere, dann kann ich zwar die Werte mit dem Feld links oben (der SVERWEIS-Bezug) ändern, aber das Diagramm bezieht sich dann immer noch auf die vorherige Tabelle.

Hier mal mein File:

http://www.siteupload.de/p883766-trainingszeitenrar.html

Wenn man jetzt Tabelle 2 kopiert und in der neuen Tabelle 3 dann oben links den Buchstaben ändert, dann soll sich das Diagramm in Tabelle 3 automatisch auch auf die Werte in Tabelle 3 beziehen und nicht mehr auf die in Tabelle 2. Wie krieg ich das hin? Google hat mir nicht wirklich geholfen.

Danke

Psycho

 

[BigPsycho]

Hallo,

habs mittlerweile selbst gelöst und werds der Vollständigkeit halber hier mal posten, evtl. stößt ja jemand auf diesen Thread wenn er nach dem Problem googelt. Und zwar ist das nur mit nem kleinen Makro möglich, das ich hier mal poste:

Dim i As Integer
Dim k As Integer
For i = 1 To 1
k = 1
    Worksheets.Add.Move after:=Worksheets(Worksheets.Count)
    ActiveSheet.Previous.Select
    Range("A1:K5").Select
    Selection.Copy
    ActiveSheet.Next.Select
    Range("A1").Select
    ActiveSheet.Paste
    ActiveSheet.Previous.Select
    ActiveSheet.ChartObjects("Diagramm " & k).Activate
    ActiveChart.ChartArea.Copy
    ActiveSheet.Next.Select
    Range("A8").Select
    ActiveSheet.Paste
    ActiveSheet.ChartObjects("Diagramm " & k).Activate
    ActiveChart.SeriesCollection(1).Select
    ActiveSheet.ChartObjects("Diagramm " & k).Activate
    ActiveChart.SetSourceData Source:=Range("B5:K5")
    Dim xachse As Range
    Set xachse = Range("B3:K3")
    ActiveSheet.ChartObjects("Diagramm " & k).Activate
    ActiveChart.SeriesCollection(1).XValues = xachse
    Set xachse = Nothing
    ActiveSheet.ChartObjects("Diagramm " & k).Activate
    ActiveChart.PlotArea.Select
    Range("A1").Select
Next i
End Sub

Ist ein total unsauberes Makro denk ich, weil ich mir das selbst nur zusammengeklaubt hab, aber es tut seinen Dienst.

Von A1 bis K5 stehen die zu kopierenden Diagrammdaten. In B5 bis K5 die Werte und in B3 bis K3 die x-Achsen Beschriftung.

Wenn man das Makro ausführt wird die Tabelle inkl. Diagramm eins zu eins kopiert.

Gruß
Psycho