Excel Daten übertragen in geschlossene Mappe

Computer: Software & Programmierung Office Software
#1

Guten Tag Zusammen,

habe eine Frage, da ich überhaupt nicht weiß, wie ich mein Vorhaben in Excel verwirklichen kann :wink:

Ich habe eine Excel Datei ( Excel 2007 ). Schichten 1. KW heißt die Datei.

Dort werden von Mitarbeitern die Arbeitszeiten erfasst.
Von Mo-Fr. die Anfangszeit sowie die Endzeit. z.B. 10:00 Uhr bis 18:00 Uhr.

Jeden Tag werden 2 Personen eine Endzeit bis 18:00 Uhr eintragen. Diese sollen nun in eine externe Exceldatei übernommen werden, damit man später zählen kann, wer bereits wieviele „Spätschichten“ eingetragen hat.

Ich hatte mir das mit der Zählenwenn Funktion vorgestellt, dies hatte ersteinmal auch schön funktioniert, jedoch müssen hierfür alle Dateien geöffnet sein. Das Problem bei dieser Sache ist, dass es jede Woche eine neue Datei gibt. Schichten 1. KW, nächste Woche gibt es eine weitere Datei mit Schichten 2. KW usw…

Zusammengefasst: Wie kann ich veranlassen dass ich aus bestimmten Tabellen alle Einträge, wo 18:00 Uhr steht, die Werte in eine neue Tabelle verlinken kann…

Hoffe ihr habt mich verstandenund eine Idee :smile: Danke

#2

Hallo kolri,

grundsätzlich kann man auch Auswertungen in geschlossenen Dateien machen.
Im Gegensatz zu ZÄHLENWENN funktioniert z.B. die Funktion SUMMENPRODUKT, mit der man auch Zählen kann, auch bei geschlossenen Dateien. Formelbeispiel:

Tabellenblattname: Tabelle1

 A B C D 
1 Summe nach 18:00 KW 1 KW2 
2 Name01 4 3 1 
3 Name02 2 2 0 

Benutzte Formeln:
B2: =SUMME(C2:Z2)
B3: =SUMME(C3:Z3)
C2: =SUMMENPRODUKT(($A2='C:\Users\Public\Test\[Schichten KW01.xlsx]Tab1'!$C$2:blush:E$2)\*('C:\Users\Public\Test\[Schichten KW01.xlsx]Tab1'!$C$3:blush:E$12\>=ZEIT(18;0;0))\*1)
C3: =SUMMENPRODUKT(($A3='C:\Users\Public\Test\[Schichten KW01.xlsx]Tab1'!$C$2:blush:E$2)\*('C:\Users\Public\Test\[Schichten KW01.xlsx]Tab1'!$C$3:blush:E$12\>=ZEIT(18;0;0))\*1)
D2: =SUMMENPRODUKT(($A2='C:\Users\Public\Test\[Schichten KW02.xlsx]Tab1'!$C$2:blush:E$2)\*('C:\Users\Public\Test\[Schichten KW02.xlsx]Tab1'!$C$3:blush:E$12\>=ZEIT(18;0;0))\*1)
D3: =SUMMENPRODUKT(($A3='C:\Users\Public\Test\[Schichten KW02.xlsx]Tab1'!$C$2:blush:E$2)\*('C:\Users\Public\Test\[Schichten KW02.xlsx]Tab1'!$C$3:blush:E$12\>=ZEIT(18;0;0))\*1)

Die Formeln werten den Namen und die Endezeit aus.
Beispiel des zugehörigen Tabellenblattsin „C:\Users\Public\Test\Schichten KW01.xlsx“ mit den Eingabedaten

Tabellenblattname: Tab1

 A B C D E F G 
 1 KW1 Mo 02.01.2012 bis Fr 06.01.2012 
 2 Name01 Name02 Name03 
 3 Mo kommt 8:30 9:00 8:30 
 4 geht 16:00 18:45 16:00 
 5 Di kommt 8:30 8:30 7:00 
 6 geht 18:00 18:00 16:00 
 7 Mi kommt 8:15 8:15 7:00 
 8 geht 18:15 16:45 15:00 
 9 Do kommt 9:00 7:30 9:00 
10 geht 18:30 16:00 18:30 
11 Fr kommt 7:30 7:30 9:00 
12 geht 14:00 14:00 18:45 

Benutzte Formeln:
F1: =C1+4

Du muss jedoch nach jeder KW, wenn die Daten aus einer weiteren Datei eingelesen werden sollen, die Formeln einer schon eingelesenen KW in die Zellen der neuen KW kopieren und mit Suchen/Ersetzen den Dateinamen in den Formeln anpassen.

Andere Varianten der Auswertung erfordern Makros.
Variante 1
Das Makro wertet sämtlich KW-Dateien aus und erstellt eine Übersicht der Tage mit Arbeitsende nach 18:00.
Das erfordert aber ein aufwendiges Makro und wenn dann mal 52KW erfasst sind auch eine entsprechend lange Makrolaufzeit. Die Übersichtsdatei bleibt dafür relativ klein.

Variante 2
Ein Makro kopiert nach jeder KW die Eingaben in die Auswerte-Datei. In der Auswerte-Datei werden dann per Formel oder bei entsprechender Anordnung der Daten auch per Pivot-Tabellenbericht die Anzahl der Tage mit Arbeitsende nach 18:00 ermittelt.
Ein entsprechendes Makro ist weniger aufwendig als für Variante 1. Da immer nur die Datei einer KW bearbeitet werden muss ist Makrolaufzeit relativ kurz. Da alle Daten in der Übersicht gesammelt werden wird diese Datei im Laufe eines Jahres relativ groß (pro Mitarbeiter müssen ja ca. 220 Eingabetage erfasst werden).

Insgesamt richtet sich dann der Programmieraufwand dan stark danach wie einfach/kompliziert die Eingabetabellenblätter aufgebaut sind.

Gruß
Franz

#3

Guten Morgen Franz!
WOW, vielen vielen Dank für die Hilfe und für die super ausformulierte Lösung :smile:Ich werde mich jetzt mal dran setzen und deine Lösung ausprobieren und mich dann aufjedenfall wieder melden :wink:

Danke

#4

Hallo nochmal :smile:

Also, ich glaube ich bin bisschen blöde, aber irgendwie komme ich schon mit dem Anfang nicht ganz klar :smile: Daher habe ich eine Frage:

Wie kann ich folgende Zählenwenn Formel in eine Summenprodukt Formel ändern?

=ZÄHLENWENN(’[Schichtplan 1. KW.xlsm]Gesamtübersicht’!$B$5:blush:U$5;„18:00“)

Also wenn ich die Zählenwenn Formel einsetze und die andere Datei aufhabe, dann wird mir auch ein Wert angezeigt.

Wenn die Datei geschlossen ist, dann natürlich nicht, da hast du mir ja gesagt, ich sollte es mit dem Summenprodukt versuchen… Wie kann ich die o.g. Formel entsprechend ändern? Wenn ich das hinbekomme, dann kann ich auch mit deiner ersten Lösung weiterversuchen :wink:

Danke vielmals für die Mühe

#5

Excel Daten übertragen aus geschlossener Mappe
Hallo kolri,

in meinen Beispielformeln ist des der 2. Formelteil, wobei dann >= durch = ersetzt werden muss.

=SUMMENPRODUKT(('[Schichtplan 1. KW.xlsm]Gesamtübersicht'!$B$5:blush:U$5=ZEIT(18;0;0))\*1)

Gruß
Franz

#6

PERFEKT, danke dir nochmal, so hats funktioniert :wink: Werde jetzt mal weiter basteln.

Danke :wink:

#7

Zählenwenn und Summewenn mit Summenprodukt
Hallo Kolri,

Also, ich glaube ich bin bisschen blöde,

k.A., mir noch nicht aufgefallen, frag die die dich real kennen :smile:

Wie kann ich folgende Zählenwenn Formel in eine Summenprodukt
Formel ändern?
=ZÄHLENWENN(’[Schichtplan 1.KW.xlsm]Gesamtübersicht’!$B$5:blush:U$5;„18:00“)

Franz und einige andere hier sind in der Theorie viel fitter als ich.
Ich bin mehr für die Praxis, learning by doing, um dann wenn man
verschiedenes getestet hat und ein Aha-Erlebnis hatte nochmals
die Hilfe zu dem Befehl durchzulesen. Erst dann kapiere ich was
da steht, bei „seltsamen“ Befehlen.

So auch bei Summenprodukt, was auch Produktsumme heißen könnte
aber das ist egal, sehr schwierig dafür einen verständlichen Namen zu finden *find*

Bei Summenprodukt gehe ich gedanklich so vor,
MEINE Syntaxen dafür ist
=Summenprodukt ( (…) )
=Summenprodukt ( (…) * (…) )
=Summenprodukt ( (…) * (…) * (…) )
usw. Das kannste fortsetzen, wie oft steht ggfs. in www.xlam.ch.

Zählenwenn mit Summenprodukt:
Dann müssen in jeder Einzelklammer „Vergleiche“ stehen, also A1:A5=7

Summewenn mit Summenprodukt:
Dann in der hintersten Klammer nur den zu summierenden Bereich
angeben.

Konkrtes Beispiel, lege dir ne Winztabelle an mit Daten in A1:C6.

Zählenwenn Wert in A = 5 ist:
=summenprodukt(A1:A6=5)

Zählenwenn Wert in A = 5 UND Wert in B = 8 ist:
=summenprodukt((A1:A6=5)*(B1:B6=8))

Summewenn aus C wenn Wert in A = 5 ist:
=summenprodukt((A1:A6=5)*(C1:C6))

Summewenn aus C wenn Wert in A = 5 UND Wert in B = 8 ist:
=summenprodukt((A1:A6=5)*(B1:B6=8)*(C1:C6))

Probier bitte das an dieser Tabelle. Teste auch rum wenn du den
Grundfehler begehst und unterschiedlich große Bereiche angibst.
Das ist meine Art zu lernen.

Gruß
Reinhard

1 Like
#8

Hallo Kolri,

hab was vergessen. Dieses „* 1“ dient dazu um „etwas“ in eine Zahl
umzuwandeln.

Dieses etwas ist z.B. ein boolscher Ausdruck wie Falsch oder Wahr.
Das sind keine Zahlen obwohl in Excel Falsch den Zahlenwert 0 hat
und Wahr 1. (In Vba ist das anders)
Durch „* 1“ erzeugst du daraus die Zahlen 0 bzw. 1.

Gruß
Reinhard

#9

Hi Reinhard, vielen Dank für die Hilfe :wink: Habe mir deinen Artikel direkt abgespeichert und jetzt werde ich etwas basteln :wink:

Habe nun in meiner Tabelle das Problem, dass wenn ich folgende Formel in eine Zelle eingebe:

=SUMMENPRODUKT(('G:\Test\Allgemein\Schichtplan\Sicherung der erledigten Pläne\[Schichtplan\_ 1.\_2012 KW.xlsm]Gesamtübersicht'!$B$5:blush:U$5=ZEIT(18;0;0))\*1)

dann klappt auch alles wunderbar.

Jedoch hat ein Jahr 52 KW´s und jede Woche kommt eine neue Tabelle hinzu. D.h. Schichtplan KW1, nächste Woche komm in den o.g. Ordner dann Schichtplan KW2 usw.

Meine Tabelle ist z.B. so aufgebaut:
A B C D E F G
1 GESAMT KW1 KW2 KW3 KW4 usw.
2 Name1 2 1 0 1

In die Zelle C2 kommt die o.g. Formel mit dem Verweis auf: „Schichtplan_1._2012“

In Zelle D2 möchte ich die Daten aus „Schichtplan _ 2._ KW“

Aber wenn es die Datei noch garnicht gibt, dann sagt er mir natürlich !Bezug und rechnet dann nicht…

Suche gerade hierfür eine Lösung, vielleicht hast du / ihr ja eine Idee :wink: Bin schon den ganzen Morgen am testen, aber na ja leider ohne Erfolg :smile:

Werde aber auch weiter testen und suchen :wink:

#10

Hallo kolri,

du kannst in Formeln keine Verweise auf Dateien einbauen, die noch nicht existieren.

Du kannst den Dateinamen auch nicht per Formel zusammenbasteln.
Da ginge zwar mit Funktion INDIREKT. Diese hat aber das gleiche Händicap wie SUMMEWENN, nämlich dass sie auf externe Dateien nur zugreifen kann, wenn diese geöffnet sind.

Dir bleibt also nichts anderesübrig, als nach jeder KW die Formeln einer vorhandenen Woche zu kopieren und dann mit Suchen/Ersetzen in den Formeln den alten Dateinamen durch den neuen zu ersetzen.

Diesen Vorgang könnte man natürlich per Makro auch automatisieren.

Gruß
Franz

#11

Aber wenn es die Datei noch garnicht gibt, dann sagt er mir
natürlich !Bezug und rechnet dann nicht…

Hallo Kolri,

teste mal die nachfolgenden Ansatz.
Kalenderwoche ist hier nur als Beispiel, so rechnet sie falsch.
Suche in der Suche von www.excelformeln.de nach „Kalenderwoche“.
Beachte die Formatierung in der zweiten Zeile.

Tabellenblatt: [Mappe1]!Tabelle1
 │ A │ B │ C │ D │ E │
──┼────────┼─────┼─────┼──────┼──────┤
1 │ │ 1 │ 1 │ 0 │ 0 │
──┼────────┼─────┼─────┼──────┼──────┤
2 │ GESAMT │ KW1 │ KW2 │ KW51 │ KW52 │
──┼────────┼─────┼─────┼──────┼──────┤
3 │ Name1 │ 2 │ 1 │ 0 │ 1 │
──┼────────┼─────┼─────┼──────┼──────┤
4 │ │ 0 │ 0 │ nix │ nix │
──┴────────┴─────┴─────┴──────┴──────┘
Benutzte Formeln:
B1: =(KALENDERWOCHE(HEUTE())\>B2)\*1
B4: =WENN(B1=1;SUMMENPRODUKT(('K:\[kwPauli1.xls]Tabelle1'!$B$5:blush:U$5=ZEIT(18;0;0))\*1);"nix")
C1: =(KALENDERWOCHE(HEUTE())\>C2)\*1
C4: =WENN(C1=1;SUMMENPRODUKT(('K:\[kwPauli2.xls]Tabelle1'!$B$5:blush:U$5=ZEIT(18;0;0))\*1);"nix")
D1: =(KALENDERWOCHE(HEUTE())\>D2)\*1
D4: =WENN(D1=1;SUMMENPRODUKT(('K:\[kwPauli51.xls]Tabelle1'!$B$5:blush:U$5=ZEIT(18;0;0))\*1);"nix")
E1: =(KALENDERWOCHE(HEUTE())\>E2)\*1
E4: =WENN(E1=1;SUMMENPRODUKT(('K:\[kwPauli52.xls]Tabelle1'!$B$5:blush:U$5=ZEIT(18;0;0))\*1);"nix")

Zahlenformate der Zellen im gewählten Bereich:
A1:A4,B1,B3:B4,C1,C3:C4,D1,D3:smiley:4,E1,E3:E4
haben das Zahlenformat: Standard
B2:E2
haben das Zahlenformat: "KW"0

Tabellendarstellung erreicht mit dem Code in FAQ:2363

Gruß
Reinhard

#12

Hi Reinhard!

TOP, PERFEKTE Lösung, funnktioniert wunderbar. Danke vielmals für die Formeln und auch die Tipps… Muss jetzt die Tabelle nur noch anpassen und fertigstellen.

VIELEN VIELEN Dank

Gruß