Live-Forum - Die aktuellen Beiträge
Anzeige
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender

Forumthread: externer, indirekter und dynamischer Dateibezug

externer, indirekter und dynamischer Dateibezug
23.05.2016 09:45:48
Robert
Hallo lieber Herber Forum,
mein Name ist Robert, ich bin 26 Jahre alt, Student und arbeite zur Zeit ein Praktikum ab. Hier habe ich die Aufgabe eine wöchentliche Auswertung vorzunehmen weshalb ich auch hier bin. Ich habe hier leider ein Problem wobei ich mit meinem Latein am Ende bin.
Problembeschreibung:
Ein Programm liefert mir jede Woche eine Excel-Datei die benannt ist als "KWxx-2016". Diese Datei wird in einem Laufwerk abgelegt und nicht mehr verändert, sie dient nur als Datensatz. Eine zweite Datei, in der die Auswertung der Datensätze stattfindet, wird wöchentlich aktualisiert. In dieser Auswertung gibt es pro Abteilung ein Tabellenblatt. In jedem Tabellenblatt wiederum gibts es 52 Zeilen für jede Kalenderwoche (KW1-2016;KW2-2016;etc.). Es gibt 4 Spalten, Spalte A umfasst alle I-Fälle, Spalte B umfasst alle L-Fälle und Spalte c umfasst alle C-Fälle.
Die Datensätze sind also genauso benannt wie die Zeilen in der Auswertung. Jetzt würde ich gerne eine Formel erstellen welche ich nur "runterziehen" brauche und für eine Spalte des Tabellenblattes gültig ist.
Im Moment habe ich folgendes:
Meine Zählenwenns-Formel:
=ZÄHLENWENNS('[KW22-2016.xlsx]Reklamationen gesamt'!$N$1:$N$700;"K"
Diese Formel funktioniert auch und macht was sie Soll.
Meine zweite Formel für die dynamische KW lautet:

=INDIREKT(ADRESSE(ZEILE();1;3;1))&".xlsx
"
auch diese Formel zeigt mir genau das an was ich will. Mir ist bewusst dass ich den Datensatz geöffnet haben muss, damit die Formel funktioniert.
Jetzt kommt hoffentlich Ihr ins Spiel :D, wie verbinde ich diese beiden Formeln? Wie kann ich die KW22-2016 durch die Indirekt Formel ersetzen? Ich habe schon zig Klammern und " ' " Konstrukte getestet, alles ohne Erfolg.
Ich hoffe ich konnte mein Problem schildern und jemand kann mir helfen.
Gruß
Robert

Anzeige

16
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: externer, indirekter und dynamischer Dateibezug
23.05.2016 10:00:51
Robert
*EDIT:
Es gibt 4 Spalten, Spalte A umfasst alle I-Fälle, Spalte B umfasst alle L-Fälle und Spalte C umfasst alle K-Fälle.

AW: wenn die Datendatei geöffnet ist ...
23.05.2016 10:24:28
...
Hallo Robert,
... für die 22.KW 2016 und "k" so: =ZÄHLENWENN(INDIREKT("'[KW"&ZEILE(A22)&"-2016.xlsx]Reklamationen gesamt'!$N$1:N700");"K")
Gruß Werner
.. , - ...

Anzeige
AW: wenn die Datendatei geöffnet ist ...
23.05.2016 11:00:17
Robert
Hallo Werner,
das funktioniert schon mal super :)
Wie muss ich vorgehen wenn ich mehrere Bedingungen habe? Irgendwie bekomme ich das noch nicht auf die gesamte Formel übertragen.
Gesamte Formel:
=ZÄHLENWENNS('[KW22-2016.xlsx]Reklamationen gesamt'!$J$1:$J$700;"Abteilung1"; '[KW22-2016.xlsx]Reklamationen gesamt'!$N$1:$N$700;"K"; '[KW22-2016.xlsx]Reklamationen gesamt'!$O$1:$O$700;""&"Abteilung2")
Tausend Dank schon mal,
Gruß
Robert

Anzeige
AW: wenn die Datendatei geöffnet ist ...
23.05.2016 11:06:55
Robert
Ich habe zu früh geantwortet. Das ganze funktioniert so doch nicht, da nicht in Zeile 22 die KW22 Daten eingetragen sind. Diese sind in verschiedenen Zeilen, deswegen wollte ich das ganze mit der der Formel =Indirekt(Adresse()) angehen

Musterdatei?
23.05.2016 11:36:23
UweD
?

AW: Musterdatei?
23.05.2016 12:25:03
UweD
Hallo nochmal
Ich denke meine Formel (abgeändert jetzt auf deine Musterdatei) klappt..

Abteilung1
 ABCDEF
3 ILKNeu Gesamt:
4      
5      
6KW21-2016    0
7      
8KW22-201611186117 314
9      

verwendete Formeln
Zelle Formel Bereich N/A
B6: D6,B8: D8=WENNFEHLER(ZÄHLENWENNS(INDIREKT("'["&$A6&".xlsx]Reklamationen gesamt'!$c$1:$c$700");B$3);"")  
F6,F8=SUMME(B6: D6)  
Excel-Inn.de
Hajo-Excel.de
XHTML-Tabelle zur Darstellung in Foren, einschl. der neuen Funktionen ab Version 2007
Add-In-Version 21.10 einschl. 64 Bit


Gruß UweD

Anzeige
AW: Musterdatei?
23.05.2016 13:07:06
Robert
Hallo Uwe,
also Abteilung A hat in der Musterdatei nur 34 Einträge, davon sind 9 I; 0 L und 23 K. Somit müsste bei KW22 stehen: 9-0-23 in B8, C8 und D8.
Gruß
Robert

AW: Musterdatei?
23.05.2016 13:09:00
UweD

Abteilung1
 ABCDEF
3 ILKNeu Gesamt:
4KW21-2016    0
5      
6KW22-20169023 32

verwendete Formeln
Zelle Formel Bereich N/A
B4: D4,B6: D6=WENNFEHLER(ZÄHLENWENNS(INDIREKT("'["&$A4&".xlsx]Reklamationen gesamt'!$c$1:$c$700");B$3;INDIREKT("'["&$A4&".xlsx]Reklamationen gesamt'!$B$1:$B$700");"A");"")  
F4,F6=SUMME(B4: D4)  
Excel-Inn.de
Hajo-Excel.de
XHTML-Tabelle zur Darstellung in Foren, einschl. der neuen Funktionen ab Version 2007
Add-In-Version 21.10 einschl. 64 Bit


Anzeige
AW: Musterdatei?
23.05.2016 13:15:06
UweD
Hi
Wenn du den Blattnamen (hier A) direkt als Abteilung verwenden möchtest.
B6:D6 =WENNFEHLER(ZÄHLENWENNS(INDIREKT("'["&$A6&".xlsx]Reklamationen gesamt'!$c$1:$c$700");B$3;INDIREKT("'["&$A6&".xlsx]Reklamationen gesamt'!$B$1:$B$700");RECHTS(ZELLE("Dateiname");LÄNGE(ZELLE("Dateiname"))-FINDEN("]";ZELLE("Dateiname"))));"")
Gruß UweD

Anzeige
AW: Musterdatei?
23.05.2016 13:32:18
Robert
Hallo Uwe,
die Formel von dir bekomme ich nicht zum laufen, allerdings weiß ich auch ned woran es liegt da ich nicht mehr folgen kann. Die Zelle bleibt einfach weiß, #Bezug!, kein #Wert!, nur weiß.
Gibt es eine Möglichkeit in

=ZÄHLENWENNS('[KW22-2016.xlsx]Reklamationen gesamt'!$J$1:$J$700;"Abteilung1";
'[KW22-2016.xlsx]Reklamationen gesamt'!$N$1:$N$700;"K";
'[KW22-2016.xlsx]Reklamationen gesamt'!$O$1:$O$700;""&"Abteilung2") 
das [KW22-2016.xlsx] durch
 =INDIREKT(ADRESSE(ZEILE();1;3;1))&".xlsx" 

zur ersetzen?
Das =indirekt(adresse()) gibt mir eigentlich den genauen Namen des Datensatzes wieder. Somit müsste ich dieses nur noch oben einsetzen und es sollte dann auch funktionieren.
Falls das ganze nicht funktioniert, kannst du mir erklären warum du WENNFEHLER noch vornehin genommen hast? Außerdem stehen in der Auswertung anstatt dem I,L und K die Ausgeschriebenen Abkürzungen, also Intern Lieferant und Kunde drinnen. Jedoch steht im Datensatz nur I,L und K. Das ganze habe ich in der Musterdatei falsch reingeschrieben. Sry :(
Gruß
Robert

Anzeige
AW: Musterdatei?
23.05.2016 13:44:48
UweD
Du hast jetzt "Abteilung 1 reingeschrieben.
Die Zellen bleiben leer, weil nichts gefunden wird.
In der "Reklamations gesamt"- Tabelle steht bei Abteilung A drin.
Also Entweder dort auch Abteilung1 reinschreiben oder die Formel auf "A" abändern
Gruß UweD

AW: Musterdatei?
23.05.2016 14:39:05
Robert
Hallo Uwe,
habe es jetzt hinbekommen. Die Formel lautet wie folgt:
=

ZÄHLENWENNS(INDIREKT("'["&$A97&".xlsx]Reklamationen gesamt'!$J$1:$J$700");"Abteilung1";
INDIREKT("'["&$A97&".xlsx]Reklamationen gesamt'!$N$1:$N$700");"I";
INDIREKT("'["&$A97&".xlsx]Reklamationen gesamt'!$N$1:$N$700"); ""&"Abteilung2"&"Abteilung3" )
Das einzige Problem was ich jetzt habe, ist der dynamische Bezug. Sobald ich die Excel Arbeitsmappe "KW22-2016" schließe und z.B. "KW19-2016" öffne, ist der Wert in KW22 weg. Gibt es hier eine Möglichkeit den Wert zu "fixieren"?
Falls es nicht möglich ist, ist VBA für mein Unterfangen zu umfangfreich? Kenne mich leider mit VBA nicht gut aus.
Gruß
Robert

Anzeige
AW: Musterdatei?
23.05.2016 13:07:27
UweD
Wenn du weitere Bedingungen einbauen möchtest dann so..
Beispiel für Abteilung A
=WENNFEHLER(ZÄHLENWENNS(INDIREKT("'["&$A4&".xlsx]Reklamationen gesamt'!$c$1:$c$700");B$3; INDIREKT("'["&$A4&".xlsx]Reklamationen gesamt'!$B$1:$B$700");"A");"")
LG UweD

Anzeige
AW: externer, indirekter und dynamischer Dateibezug
23.05.2016 10:26:34
UweD

=ZÄHLENWENNS(INDIREKT("'[" & A2& ".xlsx]Reklamationen gesamt'!$N$1:$N$700");"K")

AW: externer, indirekter und dynamischer Dateibezug
23.05.2016 11:28:10
Robert
Hallo Uwe,
danke für deine Antwort, leider funktioniert diese nicht. Die Kalenderwoche ist nicht immer identisch angeordnet, sprich es ist nicht Zeile 22 = KW 22. Ich muss den Datensatz, auf den ich mich beziehen will, aus der Spalte A und der aktuellen Zeile entnehmen. Deswegen wollte ich den Ansatz mit Indirekt und Adresse verwenden, damit mir der Wert in der Zelle in die Formel rückgegeben wird.
Hoffe ich kann meinen Gedankengang hier nachvollziehbar hinterlegen.
Gruß
Robert
Anzeige
;

Forumthreads zu verwandten Themen

Anzeige
Anzeige
Entdecke relevante Threads

Schau dir verwandte Threads basierend auf dem aktuellen Thema an

Alle relevanten Threads mit Inhaltsvorschau entdecken
Anzeige
Anzeige

Infobox / Tutorial

Externer, indirekter und dynamischer Dateibezug in Excel


Schritt-für-Schritt-Anleitung

  1. Erstelle eine ZÄHLENWENNS-Formel: Beginne mit einer Standard-ZÄHLENWENNS-Formel, die den externen Bezug auf eine andere Datei herstellt:

    =ZÄHLENWENNS('[KW22-2016.xlsx]Reklamationen gesamt'!$N$1:$N$700;"K")
  2. Füge den dynamischen Verweis hinzu: Um den Bezug dynamisch zu gestalten, kannst du die INDIREKT-Funktion verwenden. Hier ist eine modifizierte Formel:

    =ZÄHLENWENNS(INDIREKT("'[KW"&ZEILE(A22)&"-2016.xlsx]Reklamationen gesamt'!$N$1:$N$700");"K")
  3. Verwendung von ADRESSE: Wenn die Kalenderwoche nicht in einer festen Zeile steht, kombiniere die INDIREKT und ADRESSE-Funktion:

    =ZÄHLENWENNS(INDIREKT("'["&INDIREKT(ADRESSE(ZEILE(); 1; 3; 1))&".xlsx]Reklamationen gesamt'!$N$1:$N$700");"K")
  4. Teste die Formel: Stelle sicher, dass die Datei, auf die verwiesen wird, geöffnet ist, da INDIREKT nur funktioniert, wenn die Datei offen ist.


Häufige Fehler und Lösungen

  • #BEZUG! Fehler: Dieser Fehler tritt auf, wenn der dynamische Bezug nicht korrekt ist. Überprüfe, ob die Dateinamen und Pfade genau stimmen.

  • Leere Zellen: Wenn das Ergebnis der Formel leer bleibt, könnte dies daran liegen, dass die Bedingungen nicht erfüllt sind. Überprüfe die Eingabewerte in deiner Tabelle.

  • Falsche Zeile: Stelle sicher, dass die Zeilenbezüge korrekt sind. Wenn die KW nicht in der erwarteten Zeile steht, passe die Formel entsprechend an.


Alternative Methoden

  • VBA verwenden: Wenn du häufig mit dynamischen Bezügen arbeitest, könnte ein VBA-Skript hilfreich sein. Damit kannst du mehr Flexibilität und Automatisierung erreichen.

  • Power Query: Eine weitere Möglichkeit ist die Nutzung von Power Query, um Daten aus verschiedenen Excel-Dateien zusammenzuführen und dynamisch zu verarbeiten.


Praktische Beispiele

  1. ZÄHLENWENNS mit mehreren Bedingungen:

    =ZÄHLENWENNS(INDIREKT("'[KW"&ZEILE(A2)&"-2016.xlsx]Reklamationen gesamt'!$J$1:$J$700");"Abteilung1"; INDIREKT("'[KW"&ZEILE(A2)&"-2016.xlsx]Reklamationen gesamt'!$N$1:$N$700");"K")
  2. Dynamische Abteilungsbezüge: Wenn du die Abteilung dynamisch beziehen möchtest, kannst du die Zelle, in der der Abteilungsname steht, referenzieren:

    =ZÄHLENWENNS(INDIREKT("'["&$A2&".xlsx]Reklamationen gesamt'!$J$1:$J$700");B$3)

Tipps für Profis

  • Verwende Namensbereiche: Um die Lesbarkeit deiner Formeln zu verbessern, benenne Bereiche in Excel, die du häufig verwendest.

  • Vermeide unnötige Berechnungen: Verwende die WENNFEHLER-Funktion, um die Berechnung effizienter zu gestalten und Fehlerausgaben zu vermeiden:

    =WENNFEHLER(ZÄHLENWENNS(...); 0)
  • Dokumentiere deine Formeln: Halte fest, was jede Formel macht, damit du und andere Benutzer die Logik leicht nachvollziehen können.


FAQ: Häufige Fragen

1. Warum funktioniert die INDIREKT-Funktion nicht, wenn die Datei geschlossen ist?
Die INDIREKT-Funktion benötigt den Zugriff auf die Datei, um den Bezug zu bestätigen. Wenn die Datei geschlossen ist, kann Excel die Informationen nicht abrufen.

2. Kann ich auch auf andere Tabellenblätter innerhalb der gleichen Datei dynamisch zugreifen?
Ja, du kannst die Formel anpassen, um auf andere Tabellenblätter zuzugreifen. Ersetze einfach den Tabellennamen in der INDIREKT-Formel entsprechend.

3. Wie kann ich mehrere Bedingungen in einer ZÄHLENWENNS-Formel verarbeiten?
Du kannst einfach weitere Bedingungen in die ZÄHLENWENNS-Formel einfügen, indem du die nötigen Argumente hinzufügst. Achte darauf, dass jede Bedingung korrekt referenziert wird.

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Entdecke mehr
Finde genau, was du suchst

Die erweiterte Suchfunktion hilft dir, gezielt die besten Antworten zu finden

Suche nach den besten Antworten
Unsere beliebtesten Threads

Entdecke unsere meistgeklickten Beiträge in der Google Suche

Top 100 Threads jetzt ansehen
Anzeige