Live-Forum - Die aktuellen Beiträge
Anzeige
Archiv - Navigation
1744to1748
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

benachbarte Zellen auswerten "2"

benachbarte Zellen auswerten "2"
12.03.2020 16:46:13
Max
Hallo zusammen,
ich möchte benachbarte Zellen auswerten und komme leider nicht mit Hilfe der Suche weiter. In meinem letzten Post hatte ich den Anhang nicht hinbekommen. Konkret geht es um einen Stundenplan, bei dem ich mehrere Dinge wissen möchte:
1. welcher Lehrer wieviele Stunden in welchem Fach unterrichtet
2. welcher Lehrer wieviele Stunen durchgestrichen ist
Gemeint ist der orange eingefärbte Bereich, für alle Felder hätte ich so gern Formeln, wenn das geht.
Beste Grüße
Max O.
https://www.herber.de/bbs/user/135808.xlsx

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

Betreff
Datum
Anwender
Anzeige
AW: hierzu festgestellt...
12.03.2020 17:14:36
neopa
Hallo Max,
... Deine vorhanden Datenstruktur ist für die von Dir angestrebte Zielstellung einer Formellösung suboptimal.
Momentan sähe ich hierfür lediglich eine Lösung über die Erstellung einer Hilfstabelle und deren Auswertung. Wie soll denn letztere überhaupt erfolgen? Je Klasse oder über alle Klassen hinweg?
Gruß Werner
.. , - ...
AW: hierzu festgestellt...
12.03.2020 17:27:27
Max
Hallo Werner,
die Auswertung soll sowohl für eine Klasse als auch über alle Klassen hinweg stattfinden.
Konkret:
Für eine Klasse: welcher Lehrer ist wie viele Stunden in einem Fach?
Über alle Klassen hinweg: Welcher Lehrer hat wie viele Stunden insgesamt und welcher Lehrer ist wieviele Stunden durchgestrichen (und muss daher vertreten werden?
Gruß
Max O.
Anzeige
AW: dann als Formellösung wie folgt ...
12.03.2020 20:19:49
neopa
Hallo Max,
... und zwar nachfolgend nur für eine Klasse. Mein Ergebnis für Lehrer SW ist ein ein anderes als Du vorgegeben hast. War sicherlich ein Schreibfehler. Oder?
Die Hilfsspaltenformel X2 ziehend nach rechts bis AA und danach nach unten bis Zeile 26 kopieren.
Die Ergebnisformeln R2:W2 ziehend weit genug nach unten kopieren. Auf ein alphabetisches Sortieren der Lehrer in Spalte R hab ich verzichtet, weil die Formel dann zu komplex würde.
Für die anderen Klassen lassen sich analog auswerten.
Arbeitsblatt mit dem Namen 'Tabelle1'
 ABCDEFGHIJKLMNOPQRSTUVWXYZAA
1Klasse: Am 1B6  Raum S 0.01      LStU AusStHilfsspalten
2U-StdSa 19. 01.So 20. 01.Mo 21. 01.Di 22. 01.Mi 23. 01. 18 Ga2 8
30.                1Engl    Engl
41.+2. 8Sw 8Zi 8GaHd8Sw 8 Sw68   Sw 8Sw
53.+4. EnglBr SportZi 8GaHd8Sw 8 Br1Sport   Sw 8Sw
65.+6.Sw 8Zi WiPoSw WiPoZi 8Sw 8 Zi2WiPo       
77.+8.Sw 8Zi WiPoSw WiPoZi 8    Zi48   Sw 8Sw
89.+10.                Sw2WiPo   Br SportBr
9                 Hd28   Zi WiPoZi
10                       Zi WiPoZi
11                           
12                       Zi 8Zi
13                       Zi 8Zi
14                       Sw WiPoSw
15                       Sw WiPoSw
16                           
17                       HdHd8Ga
18                       HdHd8Ga
19                       Zi 8Zi
20                       Zi 8Zi
21                           
22                       Sw 8Sw
23                       Sw 8Sw
24                       Sw 8Sw
25                           

ZelleFormel
R2=WENNFEHLER(INDEX(X:X;AGGREGAT(15;6;ZEILE(X$2:X$26)/(X$2:X$26>"")/(ZÄHLENWENNS(R$1:R1;X$2:X$26;T$1:T1;Z$2:Z$26)=0);1));"")
S2=WENN(R2="";"";SUMMENPRODUKT(((B$4:N$8=R2)+(C$4:O$8=R2))*(D$4:P$8&""=T2)))
T2=WENN(R2="";"";INDEX(Z:Z;AGGREGAT(15;6;ZEILE(Z$2:Z$26)/(X$2:X$26=R2)/(ZÄHLENWENNS(R$1:R1;X$2:X$26;T$1:T1;Z$2:Z$26)=0);1)))
V2=WENNFEHLER(INDEX(AA:AA;AGGREGAT(15;6;ZEILE(Y$2:Y$26)/(Y$2:Y$26>"")/(ZÄHLENWENN(V$1:V1;AA$2:AA$26)=0);1));"")
W2=WENN(V2="";"";SUMMENPRODUKT((B$4:N$8=V2)*(C$4:O$8>"")))
X2=WENN((SPALTE(A3)=1)*(Y2>"");Y2;INDEX($B$4:$P$8;REST(ZEILE(A1)-1;5)+1;KÜRZEN((ZEILE(A1)-1)/5;0)*3+SPALTE(A1))&"")
Verwendete Systemkomponenten: [Windows (32-bit) NT 6.02] MS Excel 2010
Diese Tabelle wurde mit Tab2Html (v2.6.0) erstellt. ©Gerd alias Bamberg

Gruß Werner
.. , - ...
Anzeige
AW: dann als Formellösung wie folgt ...
13.03.2020 10:51:40
Max
Guten Morgen Werner,
das ist schon klasse, doch leider funktioniert das bei mir nur zu 50%.
Im einzelnen:
Zelle V2 zeigt bei mir ein anderes Ergebnis (siehe Bild)
Ga muss gezählt werden, da ursprünglich im Plan, dann aber krank geworden und daher durchgestrichen. Stunden müssen aber angerechnet werden.
Und wenn Hd nicht vertreten muss, wird alles korrekt gezählt ;) - das ist schon einmal ein großer Gewinn!!!
Problematisch wird es, wenn folgende Fälle auftreten:
1. Reguläre Doppelbesetzungen
2. Reguläre Dreifachbesetzungen
3. Reguläre Doppelbesetzungen werden durch Krankheit eines Kollegen zu anderen regulären Doppelbesetzungen
Und weil sich diese Fälle alle sicher nur Mega kompliziert lösen lassen, habe ich gleich darauf verzichtet, nachzufragen.
Was aber bleibt, ist die Zählweise, die ich eingangs beschrieb: Ein Kollege wird krank und wird durch einen anderen ersetzt, beide Stunden müssen gezählt werden, jedoch müssen zusätzlich die Stunden des durchgestrichenen Kollege gezählt werden.
Gruß
Max O.
Userbild
Userbild
Userbild
Anzeige
AW: meine Antwort von heute Vormittag ...
13.03.2020 19:17:41
heute
Hallo Max,
... ist offensichtlich im Nirwana gelandet, wie ich gerade feststellen musste. Sorry.
Ich hatte Dir darin sinngemäß geschrieben, dass meine aufgezeigte und nachstellbare Formellösung ein mE korrektes Ergebnis liefert und ich den Fehler an Hand Deines Bildchen nicht nachvollziehen kann.
Nun hab ich aber selbst meine hier im thread gestern aufgezeigten Formeln mit meiner gestrigen Lösungsvorschlag verglichen und musste feststellen, ich hatte für mich offensichtlich gestern vergessen meine in AA nachjustierte Formel nach Spalte X zurück zu kopieren.
Also in X2 lautet die richtige Formel:
=WENN((SPALTE(A3)=1)*(Y2"");Y2;INDEX($B$4:$P$8;REST(ZEILE(A1)-1;5)+1;KÜRZEN((ZEILE(A1)-1)/5;0)*3+SPALTE(A1)-(SPALTE(A1)=4)*3)&"")
und diese dann ziehend nach AA kopieren und dann alle nach unten. Nur diese ergibt dann auch das Listing in Spalte AA, wie gestern Abend hier aufgezeigt und dadurch auch die Ergebnisse in V2:W2 wie Du leicht vergleichen kannst.
Gruß Werner
.. , - ...
Anzeige
AW: benachbarte Zellen auswerten "2"
14.03.2020 09:42:10
Sulprobil
Hallo Max,
wie Werner bereits meinte, ist Deine Datenstruktur für eine allgemeine Auswertung nicht günstig. Es empfiehlt sich also zunächst eine Umformung auf eine günstige Tabellenstruktur, die eine allgemeine Auswertung z. B. mit Pivottabelle ermöglicht.
Anbei eine erste Beispieldatei mit Umformung und Pivottabelle. Diese Datei wird ohne jegliche Gewährleistung zur Verfügung gestellt (aber ich verwende einen aktuellen Virenscanner).
https://www.herber.de/bbs/user/135854.xlsm
Bedenke, dass Du wahrscheinich auch Auswertungen über mehrere Wochen hinweg durchführen möchtest. In diesem Fall ist ein Formelansatz wenig zielführend. Mit dem anliegenden Beispiel kannst Du über Wochen hinweg die Daten sammeln und dann zusammen auswerten.
Viele Grüße,
Bernd P
Anzeige
AW: dazu angemerkt ...
14.03.2020 11:31:06
neopa
Hallo Bernd,
... das Erstellen Datenbasis, für die eine Pivotauswertung vorgenommen werden kann, ist natürlich mit VBA wesentlich effektiver möglich. Gefragt war eine Formellösung.
Bei Deinem Lösungsvorschlag müsstest Du Max nun möglicherweise noch erklären, wie er die Vertretungsstunden berechnen kann und solltest auch noch einen abschließenden Befehl zum Löschen von evtl. vorhandenen Duplikaten einbauen. Letzteres kann Max zwar auch von Hand tun, wenn er z.B. aus Versehen die gleichen Daten in die Datentabelle geschickt hat, er muss halt daran denken.
Gruß Werner
.. , - ...
Anzeige
AW: dazu angemerkt ...
14.03.2020 15:18:35
Sulprobil
Hallo Werner,
klar, meine erste Beispieldatei könnte um all das was Du anmerktest und noch vieles mehr leicht erweitert werden.
Aber ich schau' lieber erst einmal, was aus Deiner 50% Lösung wird.
Da hab' ich mehr zu lachen :-)
Viele Grüße,
Bernd P
AW: möglicherweise ...
14.03.2020 17:47:18
neopa
Hallo Bernd,
... hast Du überlesen, dass ich meinen Darstellungskopierfehler von vor knapp zwei Tagen gestern Abend korrigiert hatte. Danach entspricht mein Lösungsvorschlag mE 100% dessen, was gefragt war. Aber auf jeden Fall hättest Du Dir Deine sarkastische Bemerkung ersparen können.
Gruß Werner
.. , - ...
Anzeige
AW: benachbarte Zellen auswerten "2"
14.03.2020 19:20:20
Max
Hallo Werner,
vielen Dank für die schöne Arbeit, ich habe versucht, das in meine Originaldatei zu implementieren, dabei ist mir leider "Gü" verlorengegangen. Warum, kann ich bei bestem Willen nicht herausfinden. Der Versatz der Zeilen und Spalten muss sein, weil ich dazwischen andere Berechnungen platziert habe.
Userbild
Ich hatte oder habe heute auch plötzlich ganz andere Probleme mit einer Abwasserleitung, 8 m Auffahrt aufgegraben, sch...
@ Bernd: Deine Lösung sieht auch prima aus, das wäre was, wenn unsere wöchentlich wechselnden komplexen Pläne so ausgewertet oder sogar erstellt werden könnten.
Mir wird klar, dass ich mir da eine Tabelle zurecht "gebastelt" habe, mit der ich ganz gut klar komme, die allerdings nicht optimal ausgewertet werden kann, sondern noch auf händisches Zählen angewiesen ist. Dazu kommt, dass ich ja lange nicht alle Besonderheiten mitgeteilt habe, weil ich ja eigentlich nur eine "Kleinigkeit" wollte und aufgrund der Antworten sehe, dass mein Problem viel besser gelöst werden könnte.
Leider kann ich die Pivot-Tabelle so nicht verwenden, da meine Originaldatei 43 Tabellenblätter enthält, von denen sicherlich 30% eingespart werden könnten.
Ich bin gerade etwas ratlos und vertage dieses Problem auf die nächsten Tage, mein Abwasserrohr hat leider oberste Priorität.
Herzlichen Dank bis hier!!! Es ist großartig, zu wissen, dass Ihr helft.
Gruß
Max O.
Anzeige
AW: für Deine veranderte Datenstruktur ...
14.03.2020 19:52:50
neopa
Hallo Max,
... ist eine nur minimale Formelanpassung notwendig. Die Formeln bleiben im Prinzip die gleichen, nur der Bezug auf die auszuwertenden Daten wird angepasst und zwar nur in der Formel X2, S2 und W2.
Deine Daten für Dein erste Klassendaten beginnen ja nun nicht mehr in Zeile A1 sondern A6, dementsprechend ändert sich der Bereichsbezug, aber nicht der Zähler ZEILE(A1).
Siehe mal nachfolgend (in Deine Daten hab ich mal willkürlich in F12 "Hd" eingesetzt, damit Du siehst, wie sich die Egebnisdaten dem anpassen):
Arbeitsblatt mit dem Namen 'Tabelle1_neu'
 ABCDEFGHIJKLMNOPQRSTUVWXYZAA
1                 LStU AusStHilfsspalten
2                 18 Zi1 8
3                 1Engl Ga2 Engl
4                 Sw68   Sw 8Sw
5                 Br1Sport   Sw 8Sw
6Klasse: Am 1B6  Raum S 0.01      Zi2WiPo       
7U-StdSa 19. 01.So 20. 01.Mo 21. 01.Di 22. 01.Mi 23. 01. Hd1WiPo   Sw 8Sw
80.                Zi48   Br SportBr
91.+2. 8Sw 8Zi 8GaHd8Sw 8 Sw2WiPo   Zi WiPoZi
103.+4. EnglBr SportZi 8GaHd8Sw 8 Hd28   HdHdWiPoZi
115.+6.Sw 8Zi WiPoSw WiPoZi 8Sw 8           
127.+8.Sw 8ZiHdWiPoSw WiPoZi 8          Zi 8Zi
139.+10.                      Zi 8Zi
14                       Sw WiPoSw
15                       Sw WiPoSw
16                           
17                       HdHd8Ga
18                       HdHd8Ga
19                       Zi 8Zi
20                       Zi 8Zi
21                           
22                       Sw 8Sw
23                       Sw 8Sw
24                       Sw 8Sw
25                           

ZelleFormel
R2=WENNFEHLER(INDEX(X:X;AGGREGAT(15;6;ZEILE(X$2:X$26)/(X$2:X$26>"")/(ZÄHLENWENNS(R$1:R1;X$2:X$26;T$1:T1;Z$2:Z$26)=0);1));"")
S2=WENN(R2="";"";SUMMENPRODUKT(((B$9:N$13=R2)+(C$9:O$13=R2))*(D$9:P$13&""=T2)))
T2=WENN(R2="";"";INDEX(Z:Z;AGGREGAT(15;6;ZEILE(Z$2:Z$26)/(X$2:X$26=R2)/(ZÄHLENWENNS(R$1:R1;X$2:X$26;T$1:T1;Z$2:Z$26)=0);1)))
V2=WENNFEHLER(INDEX(AA:AA;AGGREGAT(15;6;ZEILE(Y$2:Y$26)/(Y$2:Y$26>"")/(ZÄHLENWENN(V$1:V1;AA$2:AA$26)=0);1));"")
W2=WENN(V2="";"";SUMMENPRODUKT((B$9:N$13=V2)*(C$9:O$13>"")))
X2=WENN((SPALTE(A8)=1)*(Y2>"");Y2;INDEX($B$9:$P$13;REST(ZEILE(A1)-1;5)+1;KÜRZEN((ZEILE(A1)-1)/5;0)*3+SPALTE(A6)-(SPALTE(A6)=4)*3)&"")
Verwendete Systemkomponenten: [Windows (32-bit) NT 6.02] MS Excel 2010
Diese Tabelle wurde mit Tab2Html (v2.6.0) erstellt. ©Gerd alias Bamberg

Gruß Werner
.. , - ...
Anzeige
AW: benachbarte Zellen auswerten "2"
14.03.2020 23:54:22
Sulprobil
Hallo Max,
wenn alle Deine 43 Tabellenblätter dieselbe oder wenigstens hinreichend ähnliche Struktur aufweisen, kannst Du die Basisdaten für alle Pivottabellen mit VBA leicht in einer Tabelle sammeln.
Viele Grüße,
Bernd P
AW: Stundenplanstatistik
15.03.2020 13:07:21
Sulprobil
Hallo Max,
hier mal als kleines Beispiel für nur ein Stundenplantabellenblatt (Du hast ja 43) eine Auswertung, die meines Erachtens ausbaufähig ist.
Gib doch einmal 2-3 weitere Stundenplantabellenblätter an, die man einbauen sollte.
Dazu vielleicht noch 1-2 Sonderfälle, die Du mit berücksichtigen willst.
Mein Beispiel könnte z. B. dahingehend erweitert werden, dass alle Stundenplantabellenblätter ihre Statistik in eine CSV Datei in einem gemeinsamen Unterverzeichnis speichern, aus dem ein Auswertungsprogramm die Statistiken einliest und dann die Pivots erstellt.
https://www.herber.de/bbs/user/135872.xlsm
Das Beispielprogramm wird wie immer ohne Gewähr zur Verfügung gestellt.
Ggf. benötigst Du auch ein professionelles Programm (z. B. https://www.sked.de - ich kenne diese Software nicht und habe keinerlei Interesse daran; google einfach mal nach "Stundenplan filetype:xls" o. ä.).
Viele Grüße,
Bernd P
AW: Stundenplanstatistik
15.03.2020 14:50:16
Max
Hallo Bernd,
danke, das ist ein Angebot, welches ich gerne annehme.
Ich habe nun einmal einen ganzen Stundenplan (wöchentlich wechselnd)anonymisiert und hänge ihn an.
Zur Erläuterung:
Die Blätter 1-4 sind die regulären Stundenpläne für die Lehrer. Für sie brauche ich eine statistische Auswertung im Prozess des Erstellens.
Die Blätter 5-10 (.1 - .6) sind die resultierenden Pläne für die Klassen.
Die Blätter Ap - Zi sind die restultierenden Pläne für die Kollegen (in denen es ein nettes Gimmick wäre, wenn in ihnen das zu unterrichtende Fach stehen würde, ist aber weniger wichtig)
Die Blätter 24V1 - V4 sind die Vertretungspläne, die im nachhinein statistisch ausgewertet werden müssen.
Besonderheiten:
1. Die 0. Stunde zählt als 1 Stunde
2. Alle weiteren Stunden sind Doppelstunden, zählen also doppelt
3. Es gibt reguläre Doppel- und auch Dreifachbesetzungen, jede Stunde davon muss gezählt werden
4. Wenn ein Lehrer in 2 Klassen gleichzeitig ist, darf er aber nur 1x abgerechnet werden (Beispiel Zi immer am Mittwoch)
5. Wird ein Lehrer aber doppelt eingesetzt, werden mir die Zellen farbig markiert, daher tippe ich den Plan manuell und trage keine Lehrer per copy & paste ein - dann würden mir die farbigen Markierungen fehlen
6. Wir haben eine Liste von ca. 90 einzutragenden "Fächern" (Tabelle L)
Was ich für Auswertungen implementiert habe:
7. Unterrichtsfreie Tage von Kollegen
8. Auswertung der Überstunden (bzw. Unterstunden)
9. Welche Klasse wie viele Stunden in welchem Fach erhält
10. Wie viele Stunden welcher Kollege am Tag unterrichtet
Was mir in meinen Tabellen fehlt:
11. Eine Auswertung, welcher Lehrer wie viele Stunden in einem Fach eingesetzt ist (der eigentliche Grund für meine Anfrage hier in diesem Forum)
12. Eine Auswertung, welcher Lehrer wie viele Stunden aufgrund von Krankheit nicht unterrichtet hat
Ich hoffe, ich habe nichts vergessen.
Beim Hochladen der Datei stelle ich fest, dass sie mit 707 KB zu groß ist, ich habe daher viele Tabellenseiten gelöscht. Sie sind für meine Fragen unerheblich.
Hier die Datei: https://www.herber.de/bbs/user/135875.xlsx
Gruß
Max O.
AW: Stundenplanstatistik
15.03.2020 16:36:01
Sulprobil
Hallo Max,
das nimmt die Ausmaße eines Programmierauftrags an.
Die Grundstruktur Deiner Daten müsste standardisiert und alle notwendigen Auswertungen müssten definiert werden.
So etwas geht, aber nur, wenn Dir jemand hilft, der Excel, VBA, und Pivottabellen gut kennt.
Ich möchte Dir kein 30% System hinstellen, welches Du nicht weiter entwickeln und pflegen kannst. Und meinen Aufwand möchte ich auch im Rahmen von 30 min Hilfestellungen halten.
Falls Du jemanden mit den notwendigen Skills zur Hand hast, gebe ich gern kostenlos grundsätzliche Tipps (siehe Kontakt unter www.sulprobil.com) und weitere Anregungen.
Viele Grüße,
Bernd P

302 Forumthreads zu ähnlichen Themen

Anzeige
Anzeige
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige