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

Formelvereinfachung

Formelvereinfachung
11.02.2013 14:34:42
kalle
Hallo liebes Forum!
Ich habe einen DIenstplan erstellt. Eine Funktion ist, dass die Zellen rot aufleuchten falls jemand "doppelt gebucht" ist. Das habe ich sehr umständlich mit einer ellenlangen Formel und 20 Hilfsblättern gelöst. Ich bin mir siher, dass das auch einfacher zu lösen ist,z.B. mit einer MAtrixformel. Navh einer durchwachten Nacht habe ich jetzt erstmal aufgegeben und bitte Euch um Hilfe. EIn Teil der Arbeitsmappe ist hochgeladen:
https://www.herber.de/bbs/user/83853.xlsx
Im oberen Bereich sind die Placierungen auf bestimmten Stellen, im unteren Bereich verschiedene Abwesenheiten. Die Zellen sollen also rot werden, wenn jemand Auf zwei Stellen gebucht ist, oder zeitglich auf einer Stelle und abwesend ist. Ich arbeite mit 2-Buchstabenkombinationen fürdie Mitarbeiter, erschwerend ist, dass manchmal 2 mitarbeiter in einer Zelle stehen (AS/PR). Ich nehme an, dass die Kalenderfunktion durch gfehlenden Bezug nicht funktioniert, das sollte aber zu Übungszwecken zu verschmerzen sein.
Grüsse, Karlsson

17
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
Alter Schwede :-) - owT
11.02.2013 14:56:34
Rainer

AW: Formelvereinfachung
11.02.2013 15:09:07
Klaus
Hi,
das hier:
=OR(AND('C:\Andreas\Documents\Schema\[test.xls]JK'!B$4>1;COUNTIF(C7;$B$24)>0);(AND('C:\Andreas\Documents\Schema\[test.xls]PR'!B$4>1;COUNTIF(C7;$B$25)>0));(AND('C:\Andreas\Documents\Schema\[test.xls]AE'!B$4>1;COUNTIF(C7;$B$26)>0));(AND('C:\Andreas\Documents\Schema\[test.xls]MS'!B$4>1;COUNTIF(C7;$B$27)>0));(AND('C:\Andreas\Documents\Schema\[test.xls]MP'!B$4>1;COUNTIF(C7;$B$28)>0));(AND('C:\Andreas\Documents\Schema\[test.xls]AS'!B$4>1;COUNTIF(C7;$B$29)>0));(AND('C:\Andreas\Documents\Schema\[test.xls]RH'!B$4>1;COUNTIF(C7;$B$30)>0));(AND('C:\Andreas\Documents\Schema\[test.xls]EH'!B$4>1;COUNTIF(C7;$B$31)>0));(AND('C:\Andreas\Documents\Schema\[test.xls]HA'!B$4>1;COUNTIF(C7;$B$32)>0));(AND('C:\Andreas\Documents\Schema\[test.xls]TR'!B$4>1;COUNTIF(C7;$B$33)>0));(AND('C:\Andreas\Documents\Schema\[test.xls]NP'!B$4>1;COUNTIF(C7;$B$34)>0));(AND('C:\Andreas\Documents\Schema\[test.xls]LA'!B$4>1;COUNTIF(C7;$B$35)>0));(AND('C:\Andreas\Documents\Schema\[test.xls]TL'!B$4>1;COUNTIF(C7;$B$36)>0));(AND('C:\Andreas\Documents\Schema\[test.xls]AB'!B$4>1;COUNTIF(C7;$B$37)>0));(AND('C:\Andreas\Documents\Schema\[test.xls]SA'!B$4>1;COUNTIF(C7;$B$38)>0));(AND('C:\Andreas\Documents\Schema\[test.xls]PS'!B$4>1;COUNTIF(C7;$B$39)>0));(AND('C:\Andreas\Documents\Schema\[test.xls]AN'!B$4>1;COUNTIF(C7;$B$40)>0));(AND('C:\Andreas\Documents\Schema\[test.xls]XX'!B$4>1;COUNTIF(C7;$B$41)>0));(AND('C:\Andreas\Documents\Schema\[test.xls]SE'!B$4>1;COUNTIF(C7;$B$42)>0));(AND('C:\Andreas\Documents\Schema\[test.xls]YY'!B$4>1;COUNTIF(C7;$B$43)>0)))
verstehe ich so: (bleiben wir mal im kleinen)
in C5 ist der Name(?) "JK" rot markiert. Ich nehme an, das liegt daran dass er bereits in C4 auftaucht. Ich denke, dass soll nicht nur über C4 geprüft werden, sondern über C4 in den Tabellen PR, ME, AS ....
Ich lehn mich jetzt mal weit aus dem Fenster: Per Formel geht das nicht kürzer. Und für ein VBA-Script weiss ich nicht genug über deinen Dateiaufbau.
Grüße,
Klaus M.vdT.

Anzeige
AW: Formelvereinfachung
11.02.2013 16:23:50
kalle
Hallo!
Teilweise richtig, C4 wird jedoch nicht beachtet. C5 ist rot weil JK bereits unter C24 auftaucht(also frei ist - sollte auch rot werden). Allerdings sollte auch eine Doppelbuchung C5 und C6 ror werden. Die von dir ziiterte Formel prüft den oberen Teil (C5 - C22), Der untere Hilfsformelteil (C81-CX100) prüft den unteren Bereich (C24-CX43). Jeder Mitarbeiter hat ein eigenes Hilfsblatt, das auch so heisst (z.B. "JK" und per VBA den Nemen ändert bei änderung der Mitarbeiter. In diesen Blättern rechne ich auch durch ob jeweils pro Tag nur eine "Buchung" vorkommt.
/Karlsson

Anzeige
Sorry, ich steige aus ... und wieder offen.
11.02.2013 20:48:22
Klaus
Hey Kalle,
sei mir nicht böse, aber das ist mir zu viel. Zu viele Blätter (die ich nicht habe) = eine zu lange Formel (aus der ich nix testen kann). Dass das ganze auf schwedisch (?) ist, macht es dann auch nicht leichter ...
Frage wieder offen, für die anderen Antworter.
Grüße,
Klaus M.vdT.

(andererseits: wenn es funktioniert, warum willst du es dann ändern?)

AW: Sorry, ich steige aus ... und wieder offen.
11.02.2013 21:15:14
kalle
Ja, das hat meine Frau auch schon gesagt... Es geht um die Schönheit der Formel und die Eleganz der Einfachheit. Ich bin überzeugt, dass ich auf die 20 Hilfsblätter verzichten kann. Wie Du (Ihr) in der Mappe siehst (seht), habe ich in den Zeilen 102-142 schon eine Auswertung gemacht, wie oft der jeweilige Kollege im ersten und zweiten Teil des Dienstplans vorkommt. Es fehlt nur die Formel, diese Information als bedingte Formatierung in den Plan einzubauen, sodass sich die entsprechenden Zellen rot verfärben.
Weiterhin offen und dankbar für alle Anregungen!
/Karlsson

Anzeige
Psst, der Haken!
11.02.2013 21:23:41
Klaus
Weiterhin offen
Karlsson,
wenn du möchtest, dass der Beitrag bei "offene Beiträge" steht und gesehen wird, dann musst du auch den Haken bei "noch offen" setzen. Hab ich jetzt gemacht.
Grüße,
Klaus M.vdT.

AW: Psst, der Haken!
11.02.2013 21:28:59
kalle
Danke

Svenska hin oder her, ...
12.02.2013 02:10:45
Luc:-?
…Karlsson,
das Problem ist doch eher, was auf zwei Stellen gebucht heißt → vermute an einem Tag der jeweiligen Woche (vecka), also in einer Spalte in Zeile 5:22. Das wäre ja wohl noch recht leicht mit Formel ist… =ZÄHLENWENN(C$5:C$22;"*"&C5&"*")<>1 zu lösen, sofern nur ein Namenskürzel in der Zelle steht. Mit 2 Kürzeln (NKzl) wird's schon schwieriger, da der StandardFmlApparat hier zu einer komplizierteren und längeren Fml führt. Könnte so aussehen:
=ODER(ZÄHLENWENN(C$5:C$22;"*"&LINKS(C5;2)&"*")<>1;ZÄHLENWENN(C$5:C$22;"*"&RECHTS(C5;2)&"*")< >1)
Jetzt fehlt natürlich noch die Abwesenheit. Falls der unterste Teil des Blattes die darstellen soll (ab Zeile 102) müsste nun wohl 2x in der Spalte ab B102 (nach linkem und rechtem NKzl) gesucht wdn und in der Fundzeile die entsprechende Tagesspalte geprüft wdn. Falls 1 anwesend bedeutet, könnten die Vergleichs-1en in der vorherigen Fml durch diese Zahl ersetzt wdn. Folglich vergleicht die 1.Fml bei Nichtanwesenheit mit 0, sonst mit 1. Das würde bedeuten, dass die Zelle rot wird, wenn im Anwesenheitsfall mehr als 1 NKzl-Auftreten oder im Abwesenheitsfall überhaupt ein NKzl-Auftreten gezählt wird. Allerdings gibt's noch das Problem, wenn das NKzl fehlt, obwohl anwesend. Dann würde hier die Zelle auch rot wdn!
So, jetzt müsstest du dir eigentlich nur noch diesen FmlTeil überlegen (SVERWEIS, VERGLEICH, INDEX o.ä), worauf ich hier verzichte, weil ich nicht genau weiß (20 Hilfsblätter!), ob das in deinem Endprodukt auch so aussieht bzw ob du lieber Formelteile separat in Hilfszellen anlegen willst).
Will mal hoffen, dass das deinen Intentionen entspricht.
Gruß Luc :-?

Anzeige
AW: Svenska hin oder her, ...
12.02.2013 08:51:06
kalle
Hej Luc!
Vielen Dank får die Anregungen. Einen grossen Teil der Auswertung habe ich schon gemacht. Der Dienstplan sieht folgendermassen aus:
Zeilen 5 bis 22 sind Placierungen auf verschiedenen Stellen. Hier schreibe ich die Kürzel der Namen Tag für Tag in die Jeweilige Spalte (AS, PR, JK, etc).
Zeilen 24 bis 43 sind Abwesenheiten, hier stehen verschiede Arten von Abwesenheit (SEM, KOMP, EXT, etc.) hinter den Namenskürzeln.
Zeilen 58 bis 100 sind alte Hilfzeilen, die können gelöscht werden, ebenso will ich auf die 20 Hilfsblätter verzichten.
In Zeile 102-121 zähle ich wie oft der jeweilige Mitarbeiter im oberen Teil (Zeilen 5-22) vorkommt(Ähnlich Deinem Vorschlag).
In Zeile 122-141 zähle ob der jeweilige Mitarbeiter abwesend ist. Ich werde wahrscheinlich auch die Summe bilden um "Überbuchungen" zu finden).
Das Hauptproblem ist also wahrscheinlich die SVERWEIS, VERGLEICH, INDEX Funktion, ich kriege sie einfach nicht hin. Wenn Du Lust und Zeit hast, kannst Du ja mal in folgende Testmappe gucken:
https://www.herber.de/bbs/user/83864.xlsx
Vielen Dank für Deine Bemühungen,
Karlsson

Anzeige
Zur neuen Datei
13.02.2013 02:04:04
Luc:-?
Hej, Kalle;    → Achtung! Erst ganz unten lesen!
das wirft nun wieder neue Fragen auf! Habe den Eindruck, die innere Logik der Tabellen nicht nachvollziehen zu können.
1. Ging davon aus, das Zeilen 5:10, 12:15, 17:22 ab Spalte C die Tagesbelegung bestimmter Einsatzorte enthalten. Warum bezieht sich in allen Feldern die BedingtFormatierung stets auf C62? Oder liegt das nur an der Darstellung unter Xl14 (ohnehin völlig unmöglich, was sich MS da unnötigerweise von Version zu Vs leistet!)?
2. Die Bedeutung des genauso einheitlichen Bezugs auf C$60 in der 2.Bedingung ist genausowenig klar. Was repräsentiert die eigentlich?
3. Es scheint kein Fehler in der Tabelle zu sein. Ein solcher wäre aber fürs Testen hilfreich! Oder ist die gesamte Bedingt­formatierung der Tabelle nur veralteter Schrott? Wenn ja, warum ist sie dann noch drin?
4. Zeilen 24:43 ab Spalte C sind mir völlig unverständlich. Wozu soll hier die Bedingt­Formatierung dienen?
5. Was bedeuten jetzt die manuell gefüllten „Summen“-Zeilen 51:70? Ursprünglich ging ich von einer Signierung der Anwesenheit aus. Wo ist die jetzt zu finden? Oder drückt alles >0 An- und alles =0 Abwesenheit aus?
6. Ab Zeile 71 scheint dein Umsetzungsversuch für SVERWEIS auf die vorgenannte Summen-Hilfstabelle für Woche 19 zu stehen. Allerdings ist der 4.Parameter falsch → FALSCH bzw 0 wäre richtig. Außerdem muss hier natürlich noch mit LINKS und RECHTS gearbeitet wdn. Als Ergebnis wäre dann nur 0 oder 1 zulässig, was man über Vergleichs­operatoren (erzeugen Booleschen Wert) und Multiplikation mit 1 erreichen kann (alternativ --) wie in der nachfolgenden Formel zu sehen ist.
Unter den in den Punkten 5/6 angenommenen Voraussetzungen könnte der gesuchte rechte Teil der Formel wie folgt lauten: (Hier als vollständige Formel in C71ff getestet!)
WENN(C5="";0;--UND(SVERWEIS(LINKS(C5;2);$B$51:$CX$70;SPALTE(B:B);0)>0;
SVERWEIS(RECHTS(C5;2);$B$51:$CX$70;SPALTE(B:B);0)>0))
Die Formel kann (mit vorangestelltem =) zum Test von Zelle C71 aus über alle relevanten Zellen gezogen (bzw geklickt) wdn.
Wenn meine früheren Ausführungen zu Wiederholungen von NKürzeln in einer Spalte dem entsprachen, was du erwartest, könntest du die Bedingt­Format-Formel für Rot jetzt zusammenstellen.
So, und jetzt habe ich deine letzte AW noch mal gelesen und bin etwas schlauer. Hatte mich zu sehr auf die Mappe konzentriert. Du hast ja doch Einiges ziemlich anders gemacht als von mir vorgeschlagen bzw vermutet! Da muss die Fml zur Anwesenheits­kontrolle natürlich anders aufgebaut wdn. Und insgesamt wird's für die Bedingt­Format­Formel selbstverständlich auch anders. Willst du da lieber mit deinen separaten Zählungen arbeiten? Halte das zwar für umständlich, aber übersichtlicher mag's ja sein.
Bei den Abwesenheitsgründen muss jetzt analog Oberem auf LEER verglichen wdn, um 0/1 zu erhalten …
⇒ WENN(C5="";0;--UND(ISTLEER(SVERWEIS(LINKS(C5;2);$B$24:$CX$43;SPALTE(B:B);0));
ISTLEER(SVERWEIS(RECHTS(C5;2);$B$24:$CX$43;SPALTE(B:B);0))))
…falls die Zelle nur bei Anwesenheit leer ist (kannst du aber auch anders lösen, wenn du bei deinen Zählern bleiben willst). Die Sterne um die NKürzel in B24:B43 stören hier aber! Die waren nur als Platzhalter für den Vergleich in ZÄHLENWENN vorgesehen. Bleiben dann nur ggf fehlende Eintragungen in den Tagesspalten, die lt o.g.Fml Vergleichswert 0 ergeben.
Zu deinen Hilfstabellen kann ich mangels Kenntnis nichts weiter sagen!

So, und nun ist mir auch ein weiterer Grund für die Verwirrung klar! Die in deiner letzten AW gemachten Angaben zur Tabelle stimmen nicht mit deinem UpLoad überein!
Bei Rückfragen in diesem Thread (bzw nach 7 Tagen in einem neuen) noch mal melden.
Gruß Luc :-?

Anzeige
Habe dir mal dargestellt, ...
13.02.2013 19:20:23
Luc:-?
…Kalle,
wie ich mir das vorstelle. In der hier eingestellten Bearbeitung deiner letzten Mappe ist der Wochenarbeitsbereich neu nur für beide Überprüfungen (roter Hintergrund) in einer Formel formatiert. Die Hilfstabellen darunter dienen nur der Erläuterung des Prinzips. Ihre Fmln sind nicht 100%ig mit der BedingtFormat-Fml der HauptTabelle identisch.
Gruß Luc :-?

AW: Habe dir mal dargestellt, ...
15.02.2013 01:31:24
kalle
Hej Luc!
Vielen Dank für die pädagogische Hilfe! Ich habe das Blatt noch etwas angepasst, so dass auch unten die Felder rot leuchten, falls jemand sowohl frei als auch im Dienst ist. Versuche gerade wieder einige Funktionen herzustellen und muss dabei feststellen, dass mir folgendes Makro Deine Formel zerschiesst:
Private Sub Worksheet_Change(ByVal Target As Range)
'   alle Buchstaben Groß in einem bestimmten Bereich
Dim RaBereich As Range, RaZelle As Range
Application.EnableEvents = False
'   Bereich der Wirksamkeit
Set RaBereich = Range("C4:CX43")
'    ActiveSheet.Unprotect
Application.EnableEvents = False
For Each RaZelle In Range(Target.Address)
If Not Intersect(RaZelle, RaBereich) Is Nothing Then
RaZelle.Value = UCase(RaZelle.Value)
End If
Next RaZelle
Application.EnableEvents = True
'    ActiveSheet.protect
Set RaBereich = Nothing
Dim KeyCells As Range
Set KeyCells = Range("B24:B43")
If Not Application.Intersect(KeyCells, Range(Target.Address)) _
Is Nothing Then
End If
End Sub
Die rote Zellenmarkierung verschwindet dann nicht mehr, auch wenn ich die Doppelbuchung beseitige. Irgendeine Idee?
/Karlsson
https://www.herber.de/bbs/user/83915.xlsm

Anzeige
Vorerst nur soviel, ...
15.02.2013 18:14:36
Luc:-?
…Kalle,
weil ich auf Linux bin und für Win (später) neu starten muss:
For Each RaZelle In Range(Target.Address) bringt nichts, da Target bei diesem Ereignis immer nur eine Zelle sein kann, nämlich die, in der die Änderung erfolgt. Das Ereignis wird bei Abschluss sofort ausgelöst. Eine Ausnahme davon könnte höchstens der Eintrag einer MatrixFormel bilden, aber nicht ihre Aktualisierung, denn die löst das Ereignis gar nicht erst aus.
Also könntest du auch gleich If Not Intersect(Target, RaBereich) Is Nothing Then schreiben. Außerdem ist es von Vorteil, feste Werte wie Bereichsadressen am ProgrammAnfang (dahin gehören übrigens auch alle Dim-Zeilen) als Konstanten zu deklarieren → macht evtl spätere Änderungen leichter, weil man so nicht das ganze Programm durchsuchen muss.
RaZelle.Value = UCase(RaZelle.Value) dürfte übrigens genausowenig wie Target.Value = UCase(Target.Value) die Formeln der BedingtFormatierung löschen. Anders wäre es, wenn hierhin vollständige Zellinhalte kopiert oder Formate mit dem Pinsel übertragen wdn. Letzteres hast du wohl zuvor an einigen Stellen gemacht, weshalb sich die Anzahl der FormatBedingungen vervielfacht hatte.
Statt mit .Unprotect/.Protect kannst du auch bei Blattaktivierung (da, wo der Blattschutz gesetzt wird) der .Protect-Anweisung den Parameter UserInterfaceOnly:=True hinzufügen, dann sind Änderungen durch Makros generell möglich.
Wie gesagt, die Mappe sehe ich mir später an.
Gruß Luc :-?

Anzeige
Habe deine Datei mit 2 Fehlern ...
16.02.2013 03:47:49
Luc:-?
…in dem, was ich berücksichtigen konnte, getestet, Kalle;
danach scheint die BedingtFormatierung ordnungsgemäß zu fktionieren. Warum dein Makro die BedFormatierung stört, ist mir nicht ersichtlich, habe es dir aber mal etwas vereinfacht.
Option Explicit
Private Sub Worksheet_Activate()
'    Me.Protect "xyz", True, True, True, True
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
Const adRelBer$ = "C4:CX43"     '1.WirkBereich: alle Buchstaben groß
Const adKeyBer = "B24:B43"      '2.WirkBereich: ?
Dim raBereich As Range, keyCells As Range
Set raBereich = Me.Range(adRelBer)
'    Me.Unprotect
With Application
.EnableEvents = False
If Not (Intersect(Target, raBereich) Is Nothing Or _
IsNumeric(Target)) Then Target = UCase(Target)
.EnableEvents = True
End With
'    Me.Protect
Set keyCells = Me.Range(adKeyBer)
If Not Intersect(Target, keyCells) Is Nothing Then
End If
Set raBereich = Nothing: Set keyCells = Nothing
End Sub
Mit der 1.Ereignisprozedur kannst du den Blattschutz so einstellen, dass die Makros auch in geschützter Mappe wirksam wdn können (4.True). Das fktioniert jedenfalls ohne das BedFormat auszuhebeln.
Me ist ein VBA-Schlüsselwort für Klassenmodule, dass sich stets auf das klassenbildende Objekt (hier das ereignishabende Blatt) bezieht. Target bezieht sich hier auf die ereignisauslösende Zelle (kann bei anderen Ereignissen auch ein Zellbereich sein bzw ganz fehlen).
Gruß Luc :-?

Anzeige
AW: Habe deine Datei mit 2 Fehlern ...
17.02.2013 21:25:03
kalle
Hej!
Vielen Dank nochmal. Habe das Makro irgendwie nicht ans laufen bekommen. Habe aber eine andere Lösung mit einer UCase Funktion gefunden, die mein Problem löst. Wenn ich mich recht erinnere, hast Du mir vor einigen Jahren schon einmal mit diesem Projekt geholfen. Die Dateigrösse ist jetzt auf jeden Fall von 2800kB auf 350kB bei gleichem Funktionsumfang geschrumpft :-)
/Karlsson

AW: Habe deine Datei mit 2 Fehlern ...
17.02.2013 21:31:35
kalle
Und da habe ich es auch gefunden:
Hej Daniel,
Da ja wohl Franz das Problem in rationeller Weise gelöst hat (tja WF, nicht alles geht gut mit Formeln...;-), hier nur zur Ansicht mein Versuch mit eigenen udFktt (samt Hinweisen):
Gruß & "Goda Jul"!
Luc :-?
PS: Danke für die interessante Testmöglichkeit! ;-)
:-?

Ab Montag ist d.Thread endgültig im Archiv, ...
17.02.2013 14:23:35
Luc:-?
…Kalle,
dann kannst du nicht mehr antworten, sondern musst neu anfragen. Dann aber bitte einen Link auf den archivierten Beitrag setzen!
Vackra Soldag! Luc :-?

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige