Microsoft Excel

Herbers Excel/VBA-Archiv

Informationen und Beispiele zum Thema Werkzeug
BildScreenshot zu Werkzeug Werkzeug-Seite mit Beispielarbeitsmappe aufrufen

kopieren wenn

Betrifft: kopieren wenn von: Falk
Geschrieben am: 28.09.2020 16:07:32

Hallo,


zu dieser Frage hatte mir Ende August 2020 „neopa C“ sehr geholfen.

Ich möchte noch zwei weitere Werte aus den Blättern Tag!, Sekeins! und Abend! auslesen, was mir die Arbeit sehr erleichtern würde, weil ich zurzeit noch die Klasse und den „Block“ eines Kurses nachträglich mit dem Bleistift in den Ausdruck eintrage. Das wird bei Stundenplanänderungen schnell unübersichtlich und ist auch ein Zeitfresser.


Folgendes schwebt mir vor:

Wenn in Lehrerstundenplan! die Blätter Tag!, Sekeins! und Abend! ausgelesen werden, wäre es bei einem Treffer prima, wenn dann aus der erste Zeile der jeweiligen Spalte die Klasse ausgelesen würde. Und großartig wäre es, wenn bei einem Treffer im Blatt Tag! auch noch der Block ausgelesen würde, der in derselben Zeile aber eine bzw. zwei Spalten vor dem Treffer angegeben wurde.


Zum Beispiel:

In Tag! I47 steht: LK Eng Osw R07

Im günstigsten Fall soll dann in Lehrerstundenplan! in F6 stehen: 13 (aus Tag! I1) dann in Klammern 1 (für Block (1) aus Tag! G47) und dann der Rest, den wir schon haben, also LK Eng Osw R07.


"13 (1)

LK Eng Osw R07"


Ginge das?

In den Blättern Blätter Sekeins! und Abend! sind keine „Blöcke“ auszulesen.

Vielen Dank für die Hilfe.

Falk

https://www.herber.de/bbs/user/140498.xlsx

Betrifft: AW: hierzu zunächst nur angemerkt ...
von: neopa C
Geschrieben am: 28.09.2020 16:30:08

Hallo Falk,

... in Tag!I47 steht aber nicht LK Eng Osw R07 sondern LK Eng Osw R13 und in in Tag!I47 steht auch nicht 13 sondern 12.

Unabhängig davon solltest Du nun in einer weiteren Datei in mind. drei verschiedenen Zellen der Tab. Lehrerstundenplan (mit unterschiedlicher Art des Ergebnisses) die angestrebten Ergebniswerte ohne Formel einschreiben und diese Zellen z.B. gelb hinterlegen (damit sie sofort erkenntlich sind). Dann sehen wir weiter.

Gruß Werner
.. , - ...

Betrifft: AW: hierzu zunächst nur angemerkt ...
von: Falk
Geschrieben am: 28.09.2020 18:03:02

Hallo Werner,
vielen Dank für die Nachricht. Die Konzentration ...
Um die Markierungen kümmere ich mich. Vielen Dank.
Viele Grüße
Falk

Betrifft: AW: hierzu zunächst nur angemerkt ...
von: Falk
Geschrieben am: 29.09.2020 12:28:08

Hallo Werner,
ich habe im Blatt Lehrerstundenplan! für Auswahl "Osw" wie folgt markiert:
grün: die Werte, die zusätzlich aus Blatt Tag! kopiert werden sollen (dort drei Werte)
blau: die Werte, die zusätzlich aus Blatt Sekeins! kopiert werden sollen (zwei Werte)
rot: die Werte, die zusätzlich aus Blatt Abend! kopiert werden sollen (auch zwei Werte)
https://www.herber.de/bbs/user/140513.xlsx

Ich hoffe, es ist verständlich. Es scheint mir eben so kniffelig, weil im Blatt Tag! die Spalte mit den Blöcken entweder 1 oder 2 Spalten vor dem Treffer steht.

Kannst ja mal schauen, was hier möglich ist. Vielen Dank und Grüße
Falk

Betrifft: AW: da gibt es noch eine kleine Differenz ...
von: neopa C
Geschrieben am: 29.09.2020 13:49:37

Hallo Falk,

... meine Auswertungsformel (siehe unten) ermittelt momentan in F6 keine 12(1) sondern nur 12.

Arbeitsblatt mit dem Namen 'Lehrerstundenplan'
 ABCDEF
1LehrerOsw    
2Stunden85    
3      
4 MontagDienstagMittwochDonnerstagFreitag
5112
LK Eng Osw R16

12
Eng Osw R02


6212
LK Eng Osw R16

10T2
Eng Osw R07 (R08)

12
LK Eng Osw R13
7310T1
Eng Osw R05 (R04)

10T2
Eng Osw R07 (R08)

10T2
Eng Osw R04 (R05)
8410T1
Eng Osw R05 (R04)
10T1
Eng Osw R04 (R05)
10T1
Eng Osw R16 (R17)


9513
Fra Osw R09
10T2
Eng Osw R05 (R04)
12
LK Eng Osw R04


10613
Fra Osw R09
13
Fra Osw R09
12
LK Eng Osw R04


117
12
Eng Osw R08



128
12
Eng Osw R08



139




1410




1511




16A1


9A
Eng Osw R16

17A2
9A
Eng Osw R16

9A
Eng Osw R16

18A3
9A
Eng Osw R16




NameBezug
__St_Plan=WENNFEHLER(INDEX(Tag!$A:$L;1;_Tag_Sp);WENNFEHLER(INDEX(SEKeins!$A:$H;1;_SEK_Sp);WENNFEHLER(INDEX(Abend!$A:$L;1;_Abd_Sp);"")))&ZEICHEN(10)&WENNFEHLER(INDEX(Tag!$A:$L;_Tag_Zl;_Tag_Sp);WENNFEHLER(INDEX(SEKeins!$A:$H;_SEK_Zl;_SEK_Sp);WENNFEHLER(INDEX(Abend!$A:$L;_Abd_Zl;_Abd_Sp);"")))

ZelleFormel
B5=__St_Plan

ZelleGültigkeitstypOperatorWert1Wert2
B1Liste =Kürzel!$B$2:$B$27 
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
.. , - ...

Betrifft: AW: hier fehlen noch ben. Formeln der 2. Ebene ...
von: neopa C
Geschrieben am: 29.09.2020 13:59:03

Hallo Falk,

... offensichtlich kann das Tool von Gerd diese benannten Formeln die Bestandteil der übergeordneten benannten Formel (hier dargestellten Formel) nicht ausgeben.

Die kann ich noch nachreiche, wenn Deinerseits geklärt ist, dass Du die Differenz in F6 durch Abänderung der Eingabe in Tag!I1 so vornimmst.

Gruß Werner
.. , - ...

Betrifft: AW: hier fehlen noch ben. Formeln der 2. Ebene ...
von: Falk
Geschrieben am: 30.09.2020 08:27:30

Guten Morgen Werner,
Wow! Jetzt bitte nicht ungeduldig werden. Aber ich weiß nicht, wie ich vorgehen soll. Möglicherweise weil ich nicht weiß, wie mit den benannten Zellen umzugehen ist.

"Die kann ich noch nachreiche, wenn Deinerseits geklärt ist, dass Du die Differenz in F6 durch Abänderung der Eingabe in Tag!I1 so vornimmst." Wie sollte ich die Zellen in Zeile 1 von Tag! ändern? Eine Nachricht von Gerd kann ich nicht sehen.

Könnetest du mir kleinschrittig erklären, was zu tun ist. Oder eine Datei hochladen, damit ich nachvollziehen kann, wie das mit den benannten Zellen in Lehrerstundenplan! aussieht. Vielen Dank für deine Geduld.

Viele Grüße
Falk

Betrifft: AW: hierzu nun ...
von: neopa C
Geschrieben am: 30.09.2020 12:01:19

Hallo Falk,

... Du hattest für Lehrstundenplan!F6 als Ergebnis
12 (1)
Eng Osw R13

vorgegeben. In Tag!I1 steht jedoch lediglich 12 und nicht 12 (1)
Wo soll also das (1) herkommen?

Ich hab keine benannten Zellen sondern benannte Formeln eingesetzt. Formeln die im Namensmanager definiert werden (mehr dazu sieh mal hier: https://www.online-excel.de/excel/singsel.php?f=60 und ff)

Dazu hab ich die bisher bereits vorliegende Zellformel genommen und sie in Teilformeln "zerlegt", diese entsprechend im Namensmanager definiert und "benannt" und neu zu einer Formel zusammengesetzt, welche ich danach wiederum als eine Formel (__St_Plan) im Namensmanager definiert und dies dann in der Zelle eingesetzt habe.

Mit dem Tool von Gerd Bamberg kann ich Tabellenbereiche aus Excel hier im Forum darstellen und zwar inklusive deren dort verwendeten Formel und Formatierungen.

Doch meine Lösung für Dein Problem ist etwas komplexer als normal und dafür ist das Tool noch nicht ausgelegt. Deshalb fehlen die 6 (Teil-)Formeldefinitionen die für die Erstellung der benannten Endformel: __St_Plan notwendig sind.

Die reiche ich noch nach, schlage aber zunächst vor, dass Du Dich mit den Angaben dem oben angegebenen Link vertraut machst. Damit Du dann zumindest etwas die Lösung nachvollziehen kannst. Die komplette Datei hier eingestellt, würde Dir und mir zwar weniger Aufwand machen, würde Dir aber wohl kaum die Lösung erschließen.

Gruß Werner
.. , - ...

Betrifft: AW: hierzu nun ...
von: Falk
Geschrieben am: 01.10.2020 17:17:34

Hallo Werner,

danke für den Hinweis. Dem Link widme ich mich jetzt. Du hattest mir den Tipp schon mal gegeben, aber so richtig vertieft hatte ich mich nicht.



"Wo soll also das (1) herkommen?" Die (1) zur 12 befindet sich in Tag! G47.

Zelle Lehrerstundenplan!F6 ist wie folgt zu lesen: Jahrgang 12 (Tag!I1), Block 1 (Tag!G47) LK Eng Osw R13 (Tag!I47)



Wahrscheinlich wäre es vernünftiger, in Tag! auch vor den Spalten F, I und L eine Spalte für den jeweiligen Block einzufügen. Die Sache ist, dass die aktuelle Darstellung hier "Tradition" hat. Wenn du also einschätzt, dass es unter den gegebenen Umständen den Rahmen sprengt, den Block auszulesen, würde ich es bei deinem aktuellen Ergebnis belassen. Damit wäre dann schon sehr geholfen.



Ich melde mich, wenn ich mit dem Link durch bin.

Vielen Dank und Grüße

Falk

Betrifft: AW: jetzt ist auch dies nachvollziehbar ...
von: neopa C
Geschrieben am: 01.10.2020 17:40:43

Hallo Falk,

... und es wäre natürlich auch noch im Ergebnis durch Formelerweiterung zu erzielen. Ich schau es mir später dann noch einmal an.

Gruß Werner
.. , - ...

Betrifft: AW: die Block-Angabe jetzt ergänzt ...
von: neopa C
Geschrieben am: 01.10.2020 19:39:04

Hallo Falk,

... dazu muss (wieder bei aktivierter Zelle B5) die benannte Formel __St_Plan wie folgt ergänzt (fett dargestellt) werden:

=WENNFEHLER(INDEX(Tag!$A:$L;1;_Tag_Sp) &" ("&INDEX(Tag!$A:$L;_Tag_Zl;VERWEIS(_Tag_Sp;{4;7;10}))&") ";WENNFEHLER(INDEX(SEKeins!$A:$H;1;_SEK_Sp);WENNFEHLER(INDEX(Abend!$A:$L;1;_Abd_Sp);""))) &ZEICHEN(10)&WENNFEHLER(INDEX(Tag!$A:$L;_Tag_Zl;_Tag_Sp);WENNFEHLER(INDEX(SEKeins!$A:$H;_SEK_Zl; _SEK_Sp);WENNFEHLER(INDEX(Abend!$A:$L;_Abd_Zl;_Abd_Sp);"")))

Gruß Werner
.. , - ...

Betrifft: AW: nun ...
von: neopa C
Geschrieben am: 01.10.2020 16:47:03

Hallo Falk,

... zwischenzeitlich hast Du Dich bestimmt schon in die Thematik Einsatz des Namensmanagers etwas eingelesen.

Bevor ich evtl. die nächsten Tage nicht dazukomme, zeige ich nun nachfolgend die Definition aller benannten Formeln auf, damit Du meinen Lösungsvorschlag den ich im Beitrag am 29.09.20 13:59 aufgezeigt hatte, komplett nachstellen kannst.

Als erstes aktiviere die Zelle Lehrerstundenplan!B5 (wichtig!, in einer anderen Zelle würden einige Zelladressen in der Formeldefinition anders sein) und danach den Namensmanager.
Definiere dann dort nacheinander folgende benannte Formeln:

_Abd_Sp =AGGREGAT(15;6;SPALTE(!$C1:$L1)/(LINKS(!$A5;1)="A")/SUCHEN(!$B$1;Abend!$C$2:$L$40)^0/(VERGLEICH(!B$4;Abend!$A:$A;0)+RECHTS(!$A5;1)=ZEILE(!B$2:B$40));1)

_Abd_Zl =VERGLEICH(!B$4;Abend!$A:$A;0)+RECHTS(!$A5;1)

_SEK_Sp =AGGREGAT(15;6;SPALTE(!$D1:$H1)/SUCHEN(!$B$1;SEKeins!$D$2:$H$66)^0/(VERGLEICH(!B$4;SEKeins!$A:$A;0)+!$A5=ZEILE(!B$2:B$67));1)

_SEK_Zl =VERGLEICH(!B$4;SEKeins!$A:$A;0)+!$A5

_Tag_Sp =AGGREGAT(15;6;SPALTE(!$E1:$N1)/SUCHEN(!$B$1; Tag!$E$2:$L$59)^0/(VERGLEICH(!B$4;Tag!$A:$A;0)+!$A5=ZEILE(!B$2:B$67));1)

_Tag_Zl =VERGLEICH(!B$4;Tag!$A:$A;0)+!$A5

Dann die Gesamtauswertungsformel, die ich im betreffenden Beitrag schon dargestellt hatte. Hier der Übersichtlichkeit halber nochmal:

__St_Plan =WENNFEHLER(INDEX(Tag!$A:$L;1;_Tag_Sp);WENNFEHLER(INDEX(SEKeins!$A:$H;1;_SEK_Sp);WENNFEHLER(INDEX(Abend!$A:$L;1;_Abd_Sp);"")))&ZEICHEN(10)&WENNFEHLER(INDEX(Tag!$A:$L;_Tag_Zl;_Tag_Sp);WENNFEHLER(INDEX(SEKeins!$A:$H;_SEK_Zl;_SEK_Sp);WENNFEHLER(INDEX(Abend!$A:$L;_Abd_Zl;_Abd_Sp);"")))


Beende den Namensmanager.

Nun kannst Du die Zellformel =__St_Plan in B5 einsetzen und ziehend nach unten und rechts kopieren.

Gruß Werner
.. , - ...

Betrifft: AW: nun ...
von: Falk
Geschrieben am: 01.10.2020 19:55:41

Hallo Werner,
Mann oh Mann.
Ich habe die 5 Folgen von Peter Haserodt gelesen und zumindest das Werkzeug "Definierte Namen" ist mir jetzt ein Begriff. Nicht alle Beispiele konnte ich in Excel rekonstruieren, aber nach einigen Youtubes sollte ich deinen Arbeitsauftrag bewältigen können. Ich versuche das mal.
Wenn ich Schwierigkeiten bekomme, melde ich mich. Die Antwort hat dann natürlich Zeit.
Mehr als Danke kann ich nicht sagen.
Also vielen Dank und Grüße
Falk

Betrifft: AW: bitteschön und Grüße zurück owT
von: neopa C
Geschrieben am: 01.10.2020 19:58:25

Gruß Werner
.. , - ...

Betrifft: AW: nun ...
von: Falk
Geschrieben am: 07.10.2020 09:28:44

Hallo Werner,

inzwischen hatte ich die Formeln nach deiner Hilfestellung in den Namensmanager eingegeben und auch die Formel in __St_Plan ergänzt. Funktioniert Bombe. Genau die Angaben die ich brauche.



Leider tauchen in den Lehrerstundenplänen jetzt aber nicht mehr die Angaben aus dem Blatt SEKeins! auf. Ich habe noch einmal geprüft, ob ich wirklich alle Formeln in den Namensmanager kopiert habe und ich meine, ja.



Kannst du noch einmal einen Blick darauf werfen? Die Abfrage erfolgt analog zur Abfrage im Blatt Abend!: Bei einem Treffer in SEKeins! in den Spalten D bis H soll der Wert aus Zeile 1 im Lehrerstundenplan angegeben werden (Im Blatt SEKein! blau markiert).

https://www.herber.de/bbs/user/140711.xlsx




Wieder vielen Dank und Grüße

Falk