Frage von daryo27, 136

Automatisierung bei Excel?

Hey Leute,

ich brauche dringend eure Hilfe, da ich am Rande der Verzweiflung stehe. Also ich arbeite an meiner Masterarbeit und habe dazu verschiedene Arbeitsmappen mit unterschiedlichen Datensätzen. Mein Problem ist, dass ich bei der Portfoliobildung es nicht hinkriege folgende Formel zu basteln: Also die Formel soll auf einer anderen Arbeitsmappe nach einer bestimmten Zelle gucken und sich den Wert rauspicken. Dabei soll die Formel sich jedoch die zu betrachtende Spalte aus einer anderen Zelle ziehen. Zeile kann ich angeben, nur soll er sich die Spalte die er betrachten soll, um daraus den Wert aus der anderen Arbeitsmappe zu entnehmen, selber aus einer anderen Zelle oder besser noch aus der Formel entnehmen.

Danke im Voraus für jeden hilfreichen Beitrag

Hilfreichste Antwort - ausgezeichnet vom Fragesteller
von Iamiam, Community-Experte für Excel, 64

also, geht doch! versuchs mal mit

=INDIREKT("TRS!"&C4&4)

Wenn  das nicht hinhaut, musst Du nochmals die Beschreibung spezifizieren, ich glaube, mich sehr genau daran gehalten zu haben.

Aber was hast Du nur für Tabellen? KX, das ist Spalte 310 !

Und versuche, die Leerzeichen aus den BlattNamen wegzukriegen, die könnten Probleme machen (KGV (2) => KGV2 und ist obendrein kürzer.

(bin vorr. sehr spät abends wieder am Platz)

Kommentar von daryo27 ,

wow wow weißt gar nicht wie dankbar ich dir bin. Bist der erste, der mein Dilemma lösen konnte.

hehe ja ist eine empirische Arbeit mit über 381 Unternehmen und einer Zeitreihe von 43 Jahren.

Danke vielmals für die Hilfe

Kommentar von daryo27 ,

lässt sich die formel eigentlich auch so abändern, dass ich ihm nicht die spalte angeben muss, also c4, sondern er die sich aus der formel in c3 selber rausnimmt? Das ist die formel in c3: ='jährliche preise'!KX6/eps!KX5 .Also ich hatte in c4 kx geschrieben, aber wäre zeitsparender, wenn er sich das "kx" aus der formel aus c3 selber nehmen könnte. kx steht an der 21te stelle..wäre super, die formel so noch zu verbessern wäre

Kommentar von daryo27 ,

lässt sich die formel eigentlich auch so abändern, dass ich ihm nicht
die spalte angeben muss, also c4, sondern er die sich aus der formel in
c3 selber rausnimmt? Das ist die formel in c3: ='jährliche
preise'!KX6/eps!KX5 .Also ich hatte in c4 kx geschrieben, aber wäre
zeitsparender, wenn er sich das "kx" aus der formel aus c3 selber nehmen
könnte. kx steht an der 21te stelle..wäre super, die formel so noch zu
verbessern wäre

Kommentar von Iamiam ,

darüber habe ich auch schon nachgedacht. Du möchtest, dass xl das KX als Spalte erkennt in der Formel in c3:

='jährliche preise'!KX6/eps!KX5

Das könnte mit einer Makrofunktion gehen, muss ich aber entwickeln, dauert etwas! (nur mit Makros kann man auf einen Formeltext zugreifen). Ich versuch schon mal als Denkansatz:

Function SpalteAuslesenAusFormel()

SpalteAuslesenAusFormel = mid(worksheets("KGV2").range("c3").Formulalocal, 21, 2)

end function

Diese Formel (noch ungetestet) müsste man anstelle des C4 in obige indirekt-Formel einbetten.

Hast du schon mal mit einem Makro (Sub) oder einer Makroformel gearbeitet? (je nachdem muss ich meine Beschreibung ausführlicher gestalten). Du kannst Makroformeln allerdings nur in geringer Zahl einsetzen, sie sind langsam. Man kann aber -wenn jedes Berechnungsergebnis bleibend ist- den Formelteil "fixieren", sollte das aber vorher wissen wg. kleiner Variationen, zB könnte man dann in indirekt schreiben:
=INDIREKT("TRS!"&"""&SpalteAuslesenAusFormel&""" &4), dann den Kursivteil markieren und mit F9 fixieren.

eine weitere Komplikation ergibt sich, wenn man die Formel kopieren will, da "c3" ein Fixwert ist, den man nur umständlich relativieren kann und dazu genau wissen muss, wie (nach welchen Kriterien relativ: zu KGV2!b104 nur senkrecht? (oder nur/auch waagrecht? Würde mit jeder Anforderung komplexer/undurchschaubarer, aber möglich)

anstatt Range("c3") kann man auch Cells(3, 3) verwenden und das relativ auf die Zelle c103 bzw die Activecell beziehen, dann stimmt aber nur diese eine Formel etc, es gibt viele Möglichkeiten...

Kommentar von Iamiam ,

mir fällt gerade auf, dass Du jetzt auf ein anderes Blatt als vorher verweist. Wenn Du da schon die gesamte Adresse drinhast, kann man das auch in der VBA-Funktion nützen, denn die Länge ist hier anders als bei KGV2 oder TRS.

Man kann zB den gesamten String bis zum / auslesen oder auch den ab /

Da ich aber bei der Anlage Deiner Datei nicht durchblicke, warte ich erst mal auf Deine Rückmeldung!

xl braucht exakte Anweisungen!

Kommentar von daryo27 ,

Also in dem Arbeitsblatt TRS stehen in einer Tabelle alle Total Shareholder Renditen von 1973 bis 2016 in der Spalte und oben in der Zeile die Unternehmensnamen. Es geht mir darum, dass ich z.B. KGV´s die ich wo anders berechnet habe, aufsteigend sortieren musste, um daraus anschließend Portfolios zu bilden. Die untersten 10 zum Beispiel kommen in ein Portfolio. Nun stehen in der sortierten Reihe in den Zellen leider nur die KGV´s, aber um die Portfolios zu bilden, muss ich auf die Formel eines KGV´s zugreifen, denn dort steht drin in der Spalte, um welches Unternehmen es sich handelt, damit er dann weiß, wo also in welcher Spalte er nach dem TRS gucken muss. Mit deiner indirekten Formel hatte es ganz gut geklappt,solange ich dadrunter die Spalte aus der Formel angegeben hatte, aber bei der Menge an Portfolios usw, nimmt das enorm viel Zeit in Anspruch.

Leider habe ich bisher auch noch nie mit Makros gearbeitet, also ich wüßte nicht genau, wie ich die obere Formel da einbetten sollte. Aber ich würde die Formel schon bei mehreren Zellen einsetzen wollen, da die Reihen mit den KGV´s teilweise mit über 100 Unternehmen bestehen. Also ich hatte vorher die indirekt Formel einfach automatisch auf eine Zelle nach rechts übertragen.

Aber muss auch zugeben, bei manchen Sachen blicke ich jetzt auch nicht mehr durch VBA-Funktion,Strings usw..

Kommentar von Iamiam ,

Ob Du Renditen, Portfolios oder Nährwerte willst, ist xl völlig wurscht.

Aber ich muss wissen, in welcher Richtung Du kopieren willst, ob Du es so einrichten kannst (durch zB Einfügen von Zeilen), dass die Referenzformel immer zB 100 (oder immer 177 etc.) Zellen oberhalb der zu bearbeitenden (aktiven) Zelle steht, ob sich der Blattname ständig ändert und dergleichen mehr.

Eine Makroformel (Programmiersprache=VBA =VisualBasicforApplications, die Application ist in diesem Falle xl) einzufügen und anzuwenden ist leichter als sie zu verstehen oder zu entwickeln. Vor allem relative Bezüge sind umständlich und müssen sorgfältig geplant werden: c3 ist doch keine konstante Adresse, wie könnte man die definieren? Meine Idee: durch einen konstanten Versatz der Zelle, die Du gerade bearbeitest nach oben und ggf nach rechts/links im gleichen Blatt, das wäre machbar.

(Da gibts zB die VBA-Anweisung Zelle.offset(-177, 1).... nur zur Illustration). und die Formel
='jährliche preise'!KX6/eps!KX5
: soll sich das KX6 in der Auswerteformel auf 'jährliche Preise' beziehen oder auf eps (NB: mal mit, mal ohne ' ' ???das könnte Probleme machen!) oder immer auf KGV2 oder immer auf TRS ?

mit einer zwar etwas umständlichen, aber durchschaubaren Formel kann ich die Spaltennummer (als Zahl) erhalten, aber auch die Zeilennummer, wenn gewünscht.

Mir fällt grade auf: man kann mit einer sehr einfachen Makroformel den Formelstring auslesen und dann mit etwas komplizierteren Textauflösungsformeln daraus das KX isolieren. Ich denke, ich werde zunächst mal diesen Weg gehen!

Kommentar von Iamiam ,

Speichere die Datei erst mal als .xlsm

geh dann mit der rechten Maustaste auf einen Blattnamen und wähle CODE ANZEIGEN

im Menü Einfügen wählst du Modul: das fügt ein neues Makroblatt ein.

Dort fügst Du folgenden Code ein (hier kopieren):

Function FormelDE(Ort) 'alternativ FormelEN(Ort)
FormelDE = Ort.FormulaLocal '
FormelEN: nur Ort.Formula
End Function

nun gehst Du ins Blatt zurück, speicherst vorsichtshalber und fügst dort (aber noch nicht in c4!) folgende Formel ein:

=SPALTE(INDIREKT(TEIL(FormelDE(C3);2;FINDEN("/";FormelDE(C3);1)-2)))

Diese Formel liefert die SpaltenNUMMER (hier 310)

jetzt kannst du die Indirekt-Formel ganz oben abändern in

=INDIREKT("TRS!"&ADRESSE(4;SPALTE(INDIREKT(TEIL(FormelDE(C3);2;FINDEN("/";FormelDE(C3);1)-2)))))
ich gebe zu, liest sich etwas kompliziert, hab selber einige Stunden für die Entwicklung bzw ihre Irrwege gebraucht, aber versuchs mal!

Kommentar von daryo27 ,

also ich habe alles so gemacht wie beschrieben und es hat auf der Seite für die du es jetzt programmiert hast auch echt wunderbar funktioniert. Bloss habe versucht die von dir gebastelte Formel auch auf anderen Arbeitsblättern zu übertragen, aber dafür blicke ich nicht ganz durch deine Formel

=INDIREKT("TRS!"&ADRESSE(4;SPALTE(INDIREKT(TEIL(FormelDE(C3);2;FINDEN("/";FormelDE(C3);1)-2)))))

also TRS! ist das Arbeitsblatt, die 4 nach adresse nehme ich mal an ich die zeile die ich ihm vorgebe, also das er in TRS in Zeile 4 gucken soll, formelDE(c3) steht denke ich mal für die Zelle, in der er sich die Formel angucken soll. So ab hier blicke ich nicht mehr durch. Wofür steht die ;2 ? Für die 2 Buchstaben die in der Formel sind? Dann wofür steht das "/"? Müsste ich das auch abändern? Und zu guter letzt wofür die beiden Zahlen am Ende also 1)-2)))?

Also ich habe jetzt ein anderes Arbeitsblatt "Tabelle3", da soll in Zeile 135, Spalte AE die Zahl entnommen werden. Die Formel will ich auf das Arbeitsblatt "Form 1 Jahr" übertragen. In Zelle c58 befindet sich folgende Formel =Tabelle3!AE121. Er soll sich aus der Formel das AE, welches die Spalte angibt. Rauspicken.

Hab deine Formel bisher wie folgt geändert

=INDIREKT("Tabelle3!"&ADRESSE(135;SPALTE(INDIREKT(TEIL(FormelDE(C58);2;FINDEN("/";FormelDE(C58);1)-2)))))

Wie müsste ich die anderen Elemente abändern?

Kommentar von Iamiam ,

kann jetzt nicht mehr auf alles eingehen, aber die ;2;ist ein Argument von Teil(...;2;...). weil die Formel das = umfasst, das nicht Teil der Adresse ist. Also die Anweisung liest sich so:

  • Nimm in der lokalenFormel(DE) in C3 ab der 2. Stelle alles bis zum / und weil das dann 2 zu lang ist, 2 davor.)-2)
  • stelle von dieser Adresse fest, in welcher Spalte(Zahl) sie steht, das ist dann das 2 Argument von indirekt("Tab!"&Adresse(..;..). Die 135 ist vermutlich falsch, oder soll xl sich aus Zeile 135 den Wert holen?

Die zu analysierende Formel, aus der die Spalte rauszuholen ist, wäre in deiner Fassung C58, stimmt das?

Die Adresse über die Buchstaben ist sehr schwer zu realisieren, wenn die Länge der Buchstabenkombination schwanken kann: K, KX, KKX

werde mich morgen nochmals reinvertiefen, kostet mich auch immer Zeit und Konzentration, mich da einzudenken

Kommentar von daryo27 ,

Ja das stimmt aus Zelle C58 soll die Formel analysiert werden,um die Spalte rauszuholen.. Ja hatte ich mir auch schon gedacht, dass die unterschiedlichen Längen der Buchstaben Probleme machen könnten.. Schwankt halt immer zwischen einem Buchstaben und 2. Ja ich danke dir auf jeden Fall für deine Mühe, aber wenn es dich all zu viel Zeit kostet, dann lass es...Ich meine mit deiner oben erstellten Formel am Anfang geht es ja schließlich auch. Erfordert halt noch immer die manuelle Eingabe.

Kommentar von daryo27 ,

Ja genau der Wert steht in C58, in Zeile 135

Kommentar von Iamiam ,

Die Länge sollte keine Rolle mehr spielen. Das war anfangs, als Du die Pos. 21 für KW benannt hast. Aber kann es sein, dass der Formeltyp anders ist, dass die Formel zB kein / enthält? Dann sucht die xl-Formel natürlich vergeblich nach einer Längenbegrenzung des von der µ-Formel ausgelesenen Formelstrings. Wie heißt denn die Formel in C58?

Ich hab heut lange versucht, unabhängig von so einem / (Slash) die erste Adresse zu isolieren, aber da bring ich einen Fehler nicht weg. Wurmt mich selber!

Werd mich morgen nochmal dransetzen.

Kommentar von Iamiam ,

so, dettutetnu: (oda: mia hams gschafft!)

'nur, wenn vor der ersten Adresse nichts weiter als = steht oder sie richtig von einer Klammer eingerahmt wird(selten). Habe Namen gekürzt, bleibt hoffentlich verständlich

  • Function ErstAdIFo(Ort As Range) 'isoliert erste Adresse am Anfang einer FormelDim FoAb2 As String, i As Integer
    FoAb2 = Mid(Ort.FormulaLocal, 2, 999)
    For i = Len(FoAb2) To 1 Step -1
    Debug.Print i & " <i, Rest: " & Left(FoAb2, i)
    'nur zur Überprüfung, später Zeile mit ' davor "auskommentieren"On Error Resume Next
    If IsError(Range(Left(FoAb2, i)).Value) Then: GoTo SchleifeForts
    If Not IsError(Range(Left(FoAb2, i)).Value) Then: Exit For
    SchleifeForts:
    If i < 2 Then MsgBox "keine Adresse gefunden!"
    Next
    ErstAdIFo = Left(FoAb2, i)
    End Function

Die Funktion kannst Du in weitere Formeln einsetzen, aber nur mittels indirekt(ErstAdIFo(Ort)), zB in Spalte(...) etc

Du findest die Funktion im Typ Funktionen, benutzerdefiniert oder alle.

Da sie aber eine längere Schleife beinhaltet, dürfte bei vielfachem Einsatz die Performance schnell in die Knie gehen! (ausprobieren).

Viel Glück und melde Dich bitte, ob das hilfreich ist!

Kommentar von Iamiam ,

noch übersehen: vor Dim (2.Zeile) gehört ein Umbruch rein, ebenso vor On Error. Andere Umbrüche sollten beim Einkopieren ins Codeblatt verschwinden. Ich stells sicherheitshalber nochmals ein.

  • 'nur, wenn vor der ersten Adresse nichts weiter als = steht oder sie richtig von einer Klammer eingerahmt wird(selten, wozu auch?)
  • Function ErstAdIFo(Ort As Range) 'isoliert erste Adresse am Anfang einer Formel
  • Dim FoAb2 As String, i As Integer
  • FoAb2 = Mid(Ort.FormulaLocal, 2, 999)
  • For i = Len(FoAb2) To 1 Step -1
  • Debug.Print i & " <i, Rest: " & Left(FoAb2, i) 'nur zur Überprüfung, später Zeile mit ' davor "auskommentieren"
  • On Error Resume Next
  • If IsError(Range(Left(FoAb2, i)).Value) Then: GoTo SchleifeForts
  • If Not IsError(Range(Left(FoAb2, i)).Value) Then: Exit For
  • SchleifeForts:
  • If i < 2 Then MsgBox "keine Adresse gefunden!"
  • Next
  • ErstAdIFo = Left(FoAb2, i)
  • End Function

'hinter der Zeilenmarke SchleifeForts:  darf in der Zeile nichts mehr geschrieben werden!

Kommentar von daryo27 ,

uff blicke um ehrlich zu sein wieder nicht ganz durch^^ also wo müsste ich das ganze hinkopieren? Wieder code anzeigen lassen und da rein oder direkt als Formel verwenden? Und an welcher Stelle müsste ich ihm angeben, welche Zeile er sich angucken soll und wo er sich die Spalte ziehen soll?

Kommentar von daryo27 ,

kann man die indirekt funktion
=INDIREKT("TRS!"&C4&4)

eigentlich auch so abändern, dass die eine ganze Spalte wiedergibt und nicht nur die Zelle 4, also von 4-13 zum Beispiel

Kommentar von Iamiam ,

wohin? ja, unter die andere Funktion im neulich eingefügten Modulblatt setzen. Und dann probier sie erst mal aus, vllt erübrigt sich dann das Weitere.

Am besten setzt Du in einer unbelegten Spalte die Formel i'wo neben die auszuwertende. Dann kannst Du sie rauf- und runterziehen (in Maßen, wg. der Performance-Grenze).

Prüfe, ob da immer eine externe Adresse drinsteht.

Jetzt gehst Du wieder in die Zelle und schreibst zwischen = und Formel indirekt( und am Ende )

wieder rauf-/runterziehen (vorher kopierte Formeln damit überschreiben) Damit hast Du die Verweise gelistet, die Du aus den Formeln einer Spalte ausliest.

Wo es nicht geht, musst Du es wohl manuell machen.

Da Du nicht geschrieben hast, wie eine ggf abweichende Formel lautet, kann ich Dir jetzt nicht weiterhelfen.

Das indirekt() könnte man schon zum Auslesen einer ganzen Spalte verwenden, aber soll sich alles auf die in C4 genannten Spalte beziehen oder auf eine Liste, in der diverse Spalten stehen?

oder kannst du -viel einfacher- die Spalte nicht mit =Blattname!KX1 ansprechen und das runterziehen? Kenne ja Deine Arbeitsweise nicht.

Kommentar von daryo27 ,

Also ja es soll die in C4 genannte Spalte beziehen, aber nicht nur eine Zelle wiedergeben, also nicht nur die 4te, sondern 4-12 zum Beispiel.

Habe schon versucht die obere Formel
=INDIREKT("TRS!"&C4&4)
so zu ändern, dass ich statt 4 die Range 4:12 nehme, oder versucht das durch runterziehen automatisch auf die unteren Zellen zu übertragen, aber selbst, wenn ich c4 mit dollarzeichen fixiere, ändert er die 4 nicht...

Kommentar von Iamiam ,

=INDIREKT("TRS!"&C4&Zeile(C4) ) i'wo in Zeile4 und runterziehen.

Aber da muss in C4 der Spaltenbuchstabe (KX zB) stehen und es geht nur der Verweis auf Blatt TRS, weil das in der Formel fix drinsteht. ansonsten wieder manuell ändern.Zeile(C4) liefert eine Laufzahl, die der Zeile entspricht.

Kannst Di i'wie mal einen xl-Kurs ergattern? Du wirst nicht drumrumkommen, das auszubauen!

Kommentar von daryo27 ,

ja, aber wenn ich bei zeile(c4), den Buchstaben c mit angeben muss, dann macht es ja keinen Sinn. Also die Spalte. Genau das soll er ja automatisch machen. Kann zwar jetzt das durch das ziehen auf andere Zellen übertragen, sodass die Zeilenangabe sich verändert, aber nun stört der Buchstabe der geändert werden muss..

Ja muss ich in der Tat,bloss jetzt bleibt keine Zeit dafür bis zur Abgabe.

Kommentar von Iamiam ,

Bei indirekt Zeile(c4) spielt der Buchstabe keine Rolle, es muss nur einer drinstehen, damit eine Zelladresse in dieser Zeile zustandekommt. Aber in C4 hast Du doch den gewünschten Spaltenbuchstaben stehen? das war doch die erste Formel, wo Du den noch händisch eingegeben hast und wegen em wir den ganzen Zirkus veranstaltet haben! Ohne meine makroformeln fällst di immer wieder darauf zurück!

Und für nur indirekt("TRS!"&c4&Zeile(c4)) musst Du  Du doch ohnehin eine Spalte c haben, in der die Zielspaltenbuchstaben stehen, nur über die Auswertung der Formel durch meine Makroformel kommst Du zu einem Automatismius, der Dir den Spaltenbuchstaben angeben kann.

Nur durchs drüber reden lernt xl nichts!

Ich wünsch Dir noch viel Glück bei Deiner Arbeit!

Expertenantwort
von Iamiam, Community-Experte für Excel, 67

Die Verzweiflung ist ganz meinerseits:

"auf einer anderen Arbeitsmappe nach einer bestimmten Zelle gucken und sich den Wert rauspicken"
Die "andere" Arbeitsmappe muss geöffnet sein. die leider unbestimmte Zelle enthält also einen pickbaren Wert? Und wie findet man die Zelle- sprich was ist das Kriterium dafür, genau diese anzusteuern?

Da war Karl Valentins Anweisung an den Taxifahrer schon wesentlich genauer: Fahrens mich auf Hausnummer 18, die Straße sag ich ihnen später...

"sich jedoch die zu betrachtende Spalte aus einer anderen Zelle ziehen": bin ich auch dafür, nur auf die Kriterien müssen wir uns noch einigen!

"die Spalte die er betrachten soll, ... selber aus einer anderen Zelle oder besser
noch aus der Formel entnehmen": ich schlag mal vor: 23?

Versuchs mal mit index(Holbereich;Zeile;Spalte), indirekt() oder indirekt(Adresse(Zeile;Spalte))

Bei derart nebulösen Angaben kann ich leider nichts Genaueres raten!

Zeig, dass Du den Master verdienst!

Kommentar von daryo27 ,

Also ich habe einmal das Tabellenblatt TRS, wo in Spalte KX in Zeile 4, eine Rendite von 23,69 eingetragen ist. Nun möchte ich auf einem anderen Tabellenblatt (KGV (2)) in Spalte b, Zeile 104 eine Formel einfügen. Diese Formel soll auf dem Tabellentblatt TRS sich die oben genannte Rendite raussuchen, also =TRS!KX4. Bloss bei der von mir angegebenen Formel, habe ich selber die Spalte KX eingetragen. Die Spalte soll er sich im Idealfall automatisch raussuchen. Die Spalte habe ich auf dem Tabellenblatt KGV(2) in die Zelle C4 eingetragen. Also soll die Formel im Grunde auf Tabellenblatt TRS gucken, sich die Spalte aus Zelle C4 vom Tabellenblatt KGV (2) nehmen und Zeile 4, kann ich ihm angeben.

Keine passende Antwort gefunden?

Fragen Sie die Community