Formel vereinfach / kürzen möglich!?

Johannes H. · 10. Januar 2017

Hallo zusammen,

ich habe hier eine ziemlich lange formel, die ich gerne vereinfachen würde, wenn möglich.

es ist alles konstant bis auf den Bereich E3:E30 (alternativ auch über tabellenformatierung zu erreichen unter: "Parameter[Formel zum Zusammenführen der Datei]")

=WENNFEHLER(INDEX(INDIREKT(Parameter!$E$3&Planbereich_Suche);VERGLEICH($A6;INDIREKT(Parameter!$E$3&Planbereich_Gruppe);0);VERGLEICH(E$5;INDIREKT(Parameter!$E$3&Planbereich_Monat);0));0)

+WENNFEHLER(INDEX(INDIREKT(Parameter!$E$4&Planbereich_Suche);VERGLEICH($A6;INDIREKT(Parameter!$E$4&Planbereich_Gruppe);0);VERGLEICH(E$5;INDIREKT(Parameter!$E$4&Planbereich_Monat);0));0)

+WENNFEHLER(INDEX(INDIREKT(Parameter!$E$5&Planbereich_Suche);VERGLEICH($A6;INDIREKT(Parameter!$E$5&Planbereich_Gruppe);0);VERGLEICH(E$5;INDIREKT(Parameter!$E$5&Planbereich_Monat);0));0)
+WENNFEHLER(INDEX(INDIREKT(Parameter!$E$6&Planbereich_Suche);VERGLEICH($A6;INDIREKT(Parameter!$E$6&Planbereich_Gruppe);0);VERGLEICH(E$5;INDIREKT(Parameter!$E$6&Planbereich_Monat);0));0)
+WENNFEHLER(INDEX(INDIREKT(Parameter!$E$7&Planbereich_Suche);VERGLEICH($A6;INDIREKT(Parameter!$E$7&Planbereich_Gruppe);0);VERGLEICH(E$5;INDIREKT(Parameter!$E$7&Planbereich_Monat);0));0)
+WENNFEHLER(INDEX(INDIREKT(Parameter!$E$8&Planbereich_Suche);VERGLEICH($A6;INDIREKT(Parameter!$E$8&Planbereich_Gruppe);0);VERGLEICH(E$5;INDIREKT(Parameter!$E$8&Planbereich_Monat);0));0)
+WENNFEHLER(INDEX(INDIREKT(Parameter!$E$9&Planbereich_Suche);VERGLEICH($A6;INDIREKT(Parameter!$E$9&Planbereich_Gruppe);0);VERGLEICH(E$5;INDIREKT(Parameter!$E$9&Planbereich_Monat);0));0)

.....

danke für eure indeen, Johannes

Johannes H.

vereinfacht ausgedrückt sieht es so aus:

=WENNFEHLER(INDEX(INDIREKT(Datei1.xlsx&"!$d$6:$az$23");VERGLEICH($A6;INDIREKT(Datei1.xlsx&!$c$6:$c$23);0);VERGLEICH(E$5;INDIREKT(Datei1.xlsx&!$d$5:$az$5);0));0)

+WENNFEHLER(INDEX(INDIREKT(Datei2.xlsx&"!$d$6:$az$23");VERGLEICH($A6;INDIREKT(Datei2.xlsx&!$c$6:$c$23);0);VERGLEICH(E$5;INDIREKT(Datei2.xlsx&!$d$5:$az$5);0));0)

+WENNFEHLER(INDEX(INDIREKT(Datei3.xlsx&"!$d$6:$az$23");VERGLEICH($A6;INDIREKT(Datei3.xlsx&!$c$6:$c$23);0);VERGLEICH(E$5;INDIREKT(Datei3.xlsx&!$d$5:$az$5);0));0)

+WENNFEHLER(INDEX(INDIREKT(Datei4.xlsx&"!$d$6:$az$23");VERGLEICH($A6;INDIREKT(Datei4.xlsx&!$c$6:$c$23);0);VERGLEICH(E$5;INDIREKT(Datei4.xlsx&!$d$5:$az$5);0));0)

.....

formel sucht in separater Mappe (zb datei1.xlsx) nach wert in A6 (hier "PM") > vergleicht ihn mit c6:c23

weiterhin vergleich monatszeile (hier nur jan 17) > vergleich mit d5:az5 (in datei1)

der gesuchte wert (hier 22) wird mit den werten aus allen weiteren Dateien (datei2, datei3, datei4....) addiert.

[table][tr][td]Ressource[/td][td] [/td][td] [/td][td]Berichtszeitraum[/td][td]Jan 17[/td][/tr][tr][td]PM[/td][td]0[/td][td]Agreed[/td][td]0[/td][td]=FORMEL[/td][/tr][/table]

Quelldatei (zb Datei1.xlsx)

[table][tr][td] [/td][td]Jan 17[/td][td]Feb 17[/td][td]Mrz 17[/td][/tr][tr][td]Planung ("agreed"; in MT)[/td][td] [/td][td] [/td][td] [/td][/tr][tr][td]PM[/td][td]22 (gesuchter wert)[/td][td][td][/tr][tr][td]Benutzerdokumentation[/td][td][td][td][/tr][/table]

Claus Busch

Hallo Johannes,

in der Formel fehlt noch eine Klammer. Außerdem muss bei geschlossenen Mappen noch der Pfad mit eingegeben werden und der FullName in Apostrophe gesetzt werden.

Also probiere es mal so:

Sub Test()

Dim Res As Double

Dim varNamen As Variant

Dim i As Long

'Hier Pfad zu den Dateien anpassen

Const Pfad = "C:\Users\DeinName\Desktop\"

varNamen = Range("E3:E30")

For i = LBound(varNamen) To UBound(varNamen)

    Res = Res + Evaluate("=IFERROR(INDEX('" & Pfad & varNamen(i, 1) & "'!D6:AZ23,MATCH(A6,'" _

        & varNamen(i, 1) & "'!C6:C23,0),MATCH(E5,'" & Pfad & varNamen(i, 1) & "'!D5:AZ5,0)),0)")

Next

'Hier Ausgabezelle anpassen

Range("A1") = Res

End Sub

Falls du es mit der Formel versuchen möchtest, probiere mal folgendes:

Sub Test2()

Dim Formel As String

Dim varNamen As Variant

Dim i As Long

Const Pfad = "C:\Users\DeinName\Desktop\"

varNamen = Range("E3:E30")

For i = LBound(varNamen) To UBound(varNamen)

    Formel = Formel & "+IFERROR(INDEX('" & Pfad & varNamen(i, 1) & "'!D6:AZ23,MATCH(A6,'" _

        & Pfad & varNamen(i, 1) & "'!C6:C23,0),MATCH(E5,'" & varNamen(i, 1) & "'!D5:AZ5,0)),0)"

Next

'Hier Ausgabezelle anpassen

Range("A1").Formula = "=" & Mid(Formel, 2)

End Sub

Claus

Johannes H.

ok,

also die Dateinamen+mappenname stehen bereits im Bereich E3:E30 (siehe ursprgl. anfrage) - ich referenziere bisher darauf + dann zb $d$6:$az$23

wie gehe ich da am besten vor?

Gruß, johannes

Johannes H.

was meinst du mit indexiert? was hab ich mir darunter vorzustellen bzw. wie müssen die Daten aussehen?

klingt auf jeden fall interessant - die geöffneten Dateien nerven eh

danke, johannes

Claus Busch

Hallo Johannes,

probiere mal folgendes Makro (ungetestet, da kein Beispiel vorhanden). Das Makro errechnet dir gleich das Ergebnis.

Sub Test()

Dim Res As Double

Dim varNamen As Variant

Dim i As Long

varNamen = Range("E3:E30")

For i = LBound(varNamen) To UBound(varNamen)

    Res = Res + Evaluate("=IFERROR(INDEX(" & varNamen(i, 1) & "!$d$6:$az$23,MATCH($A6," _

        & varNamen(i, 1) & "!$c$6:$c$23,0),MATCH(E$5," & varNamen(i, 1) & "!$d$5:$az$5,0),0)")

Next

'Hier Ausgabezelle anpassen

Range("A1") = Res

End Sub

Claus

Hi,

willkommen im Windows Forum!

Formel vereinfach / kürzen möglich!? - Similar Threads - Formel vereinfach kürzen	Forum	Datum
Querverweise auf Formel nicht möglich Querverweise auf Formel nicht möglich: Hallo zusammen,ich schreibe derzeit meine Masterarbeit, darin möchte ich mittels der Querverweis-Funktion im Text auf Formeln verweisen. Sobald ich Querverweis-Funktion aufrufe erscheint das...	Microsoft Office	21. Juni 2024
Formel "=DATEDIF" nicht möglich, was kann man tun? Formel "=DATEDIF" nicht möglich, was kann man tun?: Hallo,leider funktioniert die o. g. Formel nicht. Was kann man tun, damit es doch noch funktioniert?Vielen Dank im Voraus!MfG	Microsoft Office	6. Juli 2023
Tabelleneingabe vereinfachen Tabelleneingabe vereinfachen: Gibt es eine Tastenkombination, mit der man in der letzten Zeile einer Tabelle am Ende einer Zeileneingabe mehrere Zelleninhalte wurden per Tastatur eingegeben eine Kopie dieser lfd. Zeile als...	Microsoft Office	21. Dezember 2020
Datumsangabe nicht möglich! Rechtschreibfehler in Ihrer Formel gefunden Datumsangabe nicht möglich! Rechtschreibfehler in Ihrer Formel gefunden: Hallo zusammen, seit heute habe ich folgendes Problem in Excel 365 ProPlus Version 1808 Ich gebe, wie sonst immer z.B., 24-8 in die Zeile ein und erwarte, dass sich daraus ein Datum ergibt...	Microsoft Office	18. August 2020
Formel in Formel eingeben Formel in Formel eingeben: Hallo Ich versuche gerade eine Arbeitszeiterfassung in Excel zu erstellen. Unter Kommen und Gehen werden die Zeiten manuell eingetragen und sollen dann unter der Berücksichtigung der pausen in...	Microsoft Office	19. März 2019
Lange Excel-Formel vereinfachen? Lange Excel-Formel vereinfachen?: Hallo, nach einiger Arbeit habe ich eine recht lange Excel-Formel gebaut. Sie funktioniert, und ich bin soweit zufrieden damit. Jetzt frage ich mich, ob es etwas bringt, wenn ich diese Formel...	Microsoft Office	26. April 2018
Kopiere eine Zelle mit Formel nach Update nicht möglich Kopiere eine Zelle mit Formel nach Update nicht möglich: Hallo Leute, ich benutze excel 365 auf dem iPhone und kann seid dem Update keine Zelle mit Formel kopieren. Er übernimmt immer nur den Inhalt (Zahlenwert). Vielleicht kann mir ja einer helfen.	Microsoft Office	14. April 2017

Formel vereinfach / kürzen möglich!?