Live-Forum - Die aktuellen Beiträge
Datum
Titel
28.03.2024 21:12:36
28.03.2024 18:31:49
Anzeige
Archiv - Navigation
1736to1740
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Inhaltsverzeichnis

Datenlisten

Datenlisten
28.01.2020 14:55:04
Peter
Ich habe mehrere Arbeitsmappen für verschiedene Räume eines Gebäudes mit identischer Struktur: Pro Monat ein Arbeitsblatt mit gleichlautendem Monatsnamen (Januar 2020; Februar 2020 ...)
Jedes Arbeitsblatt hat gleiche Spalten mit (alphabetisch geordnet) Nachname, Vorname und Gruppenzugehörigkeit.
Die Nachnamen usw. sind in jeder Arbeitsmappe im Januar gelistet und in allen Mappen gleich. Die Folgeblätter (Februar, März ...) beziehen (Zellbezug) sich auf Januar und haben daher auch die gleichen Nachnamen etc.
Die weiteren Spalten der Blätter haben alle Wochentage des Monats und für die Anwesenheitserfassung wird je Name eine Eins beim entsprechenden Namen (Zeile) und Tag (Spalte) eingetragen. Eigentlich sehr primitiv.
Ich habe zwei Fragen:
Erweiterung der Namensliste:
Wenn ich in der Namensliste einen zusätzlichen Namen alphabetisch einfügen will, füge ich also eine Zeile (z.Bsp. Zeile 53) ein und trage den Namen ein. Alle Bezüge der anderen Blätter haben die Verschiebung der Zeilen 53 bis xx natürlich registriert und die Zellbezüge um 1 erhöht. Jedoch fehlt natürlich die neue Zeile 53 in den Listen und ich muss nun die Listen aller Monate anpassen (Zeile einfügen und Bezüge eintragen). =WENN('Januar 2020'!A53="";"";'Januar 2020'!A53)
Gibt es eine Möglichkeit andere Bezüge zu verwenden, bei denen diese Lücken nicht auftreten und zusätzliche Arbeit erfordern?
Konsolidierung der Anwesenheiten:
Das ist mein größtes Problem, an dem ich schon graue Haare verloren habe.
Es gibt also mehrere Workbooks - pro Raum eins. In jedem Raum werden nun die Anwesenheit eingetragen.
Zum Schluss sollen alle Arbeitsmappen zusammengefasst werden, um zu prüfen, ob eine Person an allen Tagen - egal in welchem Raum - anwesend war. Das heißt die Arbeitsblätter (Monate) der Arbeitsmappen (Räume) werden übereinandergelegt und pro Name (Zeile) und Tag(Spalte) die Ziffern addiert.
Ich habe bereits mit Power Query alle Mappen eines Datei-Ordners geöffnet und zusammengefasst. Jedoch werden nun nur alle Namenlisten aufgelistet. Das heiß die ersten 20Zeilen sind von Raum 1, die nächsten 20 von Raum 2 usw. Ich müsste jetzt erst mühsam mit Pivot oder sonstwas die Daten zusammenfassen. Gibt es eine bessere Möglichkeit? Außerdem erlaubt das "kombinieren" nur ein Blatt. Also z.Bsp. den Januar. Ich muss für jeden Monat eine komplett neue Abfrage erstellen.
Ich würde mich über eine Lösung ohne Makro oder VBA freuen, da ich sicher bin, das Excel dazu in der Lage ist, komplett identische Arbeitsblätter mit Boardmitteln zu mergen.
Ich sag schonmal Danke!!!

6
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: wäre wie angestrebt möglich ...
28.01.2020 15:34:49
neopa
Hallo Peter,
... zu Deiner 1. Frage: anstelle Deines direkten Formelbezuges auf die Januar-Datenwerte rate zu einem Bezug über INDEX().
Also in A3 der Folgemonate folgende Formel =INDEX('Januar 2020'!A:A;)&"" und dies nach rechts kopiert und danach weit genug nach unten.
zu Deiner 2. Frage:
Entweder Du arbeitest von Haus aus mit nur einer Arbeitsmappe und kennzeichnest Deine Tabellenblätter über eine Schlüsselnummer (für Raum und Monat) oder "sammelst" alle Datentabellen in einer entsprechen Arbeitsmappe. Wenn das nicht gewollt ist, kannst Du bei entsprechender Anordnung der Tabellenblätter ( "Blockweise" für einen Raum über alle 12 Monate) aller Daten über eine INDEX()-Formel (s.o.) "einsammeln". Da Deine Anwesenheit je Person, Tag und Raum mit einer 1 registriert wird, kannst Du dies nun "Blockweise" über eine simple "3D-Summenformel" je Person und Tag ausgewertet.
Gruß Werner
.. , - ...
Anzeige
AW: wäre wie angestrebt möglich ...
29.01.2020 09:31:10
Peter
Hallo Werner
Also Antwort 1 klappt super. Danke. Wieder was gelernt.
zu 2. Die Mappen für die Räume liegen auf einem zentralen Server, werden aber zeitgleich geöffnet und beschrieben. Wenn man Filesharing hätte, wäre das auch kein Problem und ich könnte mir die ganze Arbeit sparen. Das geht aber aus technischen Gründen nicht. Ich muss also die Mappen nach dem Beschreiben "zusammenfassen". Dazu kommt, das dies immer möglich sein muss und nicht nur z.Bsp. am Monatsende.
Es muss also möglich sein die aktuellen Daten aller Workbooks abzufragen und zusammen zu fassen, um jederzeit prüfen zu können, ob eine bestimmte Person am Tag x egal wo anwesend war.
Gruß Peter
Anzeige
AW: das sollte zu 2.) so auch funktionieren ...
29.01.2020 12:21:56
neopa
Hallo Peter,
... wie dort von mir "skizziert". Hast Du das mal geprüft?
Gruß Werner
.. , - ...
AW: wäre wie angestrebt möglich ...
30.01.2020 09:23:21
Peter
Hallo Werner
(immer noch zu 2.)
Ich geb mal wieder, wie ich deinen Vorschlag verstehe. Ich soll in irgendeiner Form die Monatsblätter der Raummappen in eine zentrale Mappe kopieren und dann auswerten. Es gibt mehrere Sachen die dagegen sprechen.
1. Mal eben eine Prüfung ob Hans am Montag anwesend war, kann ich nicht machen, da ich immer erst wieder alle Dateien öffnen und den Inhalt zusammenkopieren muss, um einen aktuellen Status zu bekommen.
2. Die zu verarbeitenden Daten sind unverhältnissmäßig hoch, da ich ja eigentlich nur die "Einsen" wissen und addieren will.
3. Für den Kopiervorgang benötige ich ein Makro oder ähnliches.
4. Und am Ende muss ich ein Sheet in dieser Mappe bauen, das von x Seiten Werte zusammenkopiert. Den Ansatz hatte ich schonmal gefahren und bin bei unendlich langen Summenformeln (trotz Verwendung von Variablen) gelandet.
Entweder ich versteh dich falsch oder es ist nicht der richtige Weg.
Im Grunde habe ich die Daten ja schon über Power Query zusammen gezogen. Jedoch habe ich die Abfrage nicht dazu bewegen können, die Abfragen gleich zu addieren. Die Abfrage listet einfach allen Input nacheinander auf. Und so taucht Hans dann je Zimmer einmal auf.
Und ich habe es nicht hinbekommen, den Monat variabel zu gestalten, so das ich also gezielt den Januar oder den Februar abfragen kann.
LG Peter
Anzeige
AW: zu Deinen Fragen ...
30.01.2020 11:35:01
neopa
Hallo Peter,
... ich hatte Dich so verstanden, dass Du oein paar Probleme mit PQ hattest und nach Alternativen ohne VBA gesucht hast. Hatte selbst nur in meinem XL2010 über eine möglich Lösung nachgedacht. Dies sollte aber so gehen, wie von mir "skizziert". Das holen der Daten geht mit INDEX() auch auf geschlossene Dateien. Wobei natürlich der Weg über PQ bei vielen Daten der effektivere sein dürfte.
Zu 1. ergo, musst die Dateien nicht immer wieder öffnen, weil Deine Dateien Dir ja bekannt sind und somit nicht INDIREKT() notwendig ist.
zu 2. Das sollte trotzdem mit ZÄHLENWENNS() noch kein Problem sein.
zu 3.) Du wolltest kein VBA und bräuchtest es auch nicht wirklich, auch wenn es mit so einem für Dich einfacher einfacher sein könnte. Ich selbst halte mich aber aus VBA-Lösungen grundsätzlich außen vor.
zu 4. Ich erkenne momentan nicht, dass zu Deiner bisher beschriebenen Auswertung "ellenlange" Summenformeln notwendig sein sollten. Siehe meine Antwort zu 2.)
Gruß Werner
.. , - ...
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige