Jun 09, 2009, updated at Jun 10, 2009 (UTC)

7711

Wert in Zelle abhängig von eingegebenen Werten

Hallo,

zu folgendem Problem suche ich einen Lösungsansatz oder die Lösung.
Habe eine Tabelle (pro Monat), darin die Tage je Zeile und die Stunden von 0 bis 23 Uhr
als Spalten (z.B. am 1. des Monats: E3 = 0 Uhr, H3 = 1 Uhr, K3 = 2 Uhr, N3 = 3 Uhr.....
am 2. des Monats E4 = 0 Uhr, H4 = 1 Uhr, K4 = 2 Uhr, N4 = 3 Uhr..... usw. )
Ich möchte, wenn ich in den entsprechenden Zellen der Stunden bestimmte Werte
(wer1 bis wer5) eintrage, dass der Wert TT = 1 ist. (BY3 für den 1., BY4 für den 2. usw.)
Es ginge mit einer ewig langen WENN-Abfrage für jede einzelne TT-Zelle:
=WENN(ODER(E3=wer1;E3=wer2;E3=wer3;E3=wer4;E3=wer5);1;(WENN(ODER(H3=wer1;H3=wer2;H3=wer3;H3=wer4;H3=wer5);1;(WENN(ODER(K3=wer1;K3=wer2;K3=wer3;K3=wer4;K3=wer5);1;0))))) .....

Aber es gibt sicherlich auch eine VBA-Lösung dafür. Jedoch reichen meine
"Kenntnisse" dazu nicht ganz

Gruß
mag

Please also mark the comments that contributed to the solution of the article

Content-Key: 117789

Url: https://administrator.de/contentid/117789

Printed on: April 19, 2024 at 16:04 o'clock

32 Comments

Latest comment

Hallo mag-lion!

Also, die ODER-Funktion ist der richtige Ansatz, nur reicht z.B. ODER(E3>0;H3>0;K3>0;...)

Gruß Dieter

Hallo Dieter,

leider nicht, denn da würden alle Werte gelten.
Ich möchte aber 5 bestimmte nur zulassen und die entsprechende Zelle TT
des jeweiligen Tages mit "1" füllen.

Gruß
mag

Hallo mag-lion!

Gut, dann ginge es auch so:

=WENN(ODER(UND(E3>=wer1;E3<=wer5);UND(H3>=wer1;H3<=wer5);.....);1;0)

Aber das wird auch ziemlich lang. Ich probiers mal mit nem Makro. Dauert etwas

Gruß Dieter

Hallo mag-lion!

Kopiere den Quelltest im VB-Editor in ein Modul und schreibe in die Zelle BY "=TestDay()"

Option Explicit
Option Compare Text

Const Start = 5     'Spalte E  
Const Count = 3     'Zähler Spalten (E, H,...)  

Const Werte = "[atibn]"  

Function TestDay() As Integer
    Dim Line As Integer, i As Integer
        
    Application.Volatile:  Line = Application.Caller.Row
    
    For i = Start To 23 * Count + Start Step Count
        If Cells(Line, i) Like Werte Then TestDay = 1: Exit For
    Next
End Function

Gruß Dieter

[Edit] Vergleich... geändert. Werte = Oder(a;b;i;n;t) und Oder(A;B;I;N;T) [/Edit]

Hallo Dieter,

ja, genau so ... jedenfalls fast.
Die Werte wer1 bis wer5 sind Buchstaben (a, t, i, b, n).
Und nun???

Gruß
mag

PS:
Danke.
Find ich immer wieder gut, dass manche sowas
"einfach so" schreiben können.

Hallo mag-lion!

Und nun, wird der Code (oben geändert) etwas kürzer

Werte = Oder(a;b;i;n;t) und Oder(A;B;I;N;T)

Gruß Dieter

Guten Morgen Dieter,

ja, genau so ... und diesmal so wie gewünscht.

Recht vielen Dank.

Gruß
mag

Hm... doch noch nicht ganz.

Hab doch für jeden Monat eine Tabelle.
Und zusätzlich eine Tabelle "Jahr".
Also läuft die Funktion auch in dieser.
Dort sollen aber nur die Summen der "TT"-Werte stehen.

Bitte noch mal um Gedankenanstoß vom Profi.

Gruß
mag

Guten Morgen mag-lion!

Ääh, ja die Formel taugt nur für die Monatsblätter.

Für Jahressummen brauchst Du eine andere Formel.

Wie heissen die Monats-Tabellenblätter? Monat01 - Monat12 oder wie?

Und was für Summen sollen das genau sein?

Muss jetzt aber leider Weg, daher wird es etwas dauern.

Gruß Dieter

Hallo mag-lion!

Nachrichten haben sich überschnitten, siehe Nachricht von 08:13

Gruß Dieter

Die Formel find ich gut.

Die Monatsblätter tragen die Monatsnamen.

Ich habe bei anderen Werten in den jeweiligen
Monatsblättern Summen gebildet und diese im
Blatt "Jahr" verwiesen.

Der Hintergrund ist folgender:
Wenn einer der Werte [atibn] an einem Tag (eine Zeile) für
irgendeine Stunde eingetragen wird (mehrfach spielt ja keine
Rolle), dann ist z.B. BY = 1. Soweit ist deshalb diese Variante
ok. Außerhalb des aktiven (sicht- bzw. druckbaren) Bereiches
werden diese Werte addiert und in die Jahresübersicht "Jahr"
"übertragen". So sehe ich dann in der Tabelle "Jahr" wie oft in
welchem Monat dieser Fall (aus [atibn]) zutraf.

Ich hoffe, ich konnte mich verständlich ausdrücken.

Warte auf deine Rückkher / Antwort.

Gruß
mag

Hallo mag-Ion!

Kapier ich nicht so ganz?

Du hast für jedem Monat bereits eine Summe gebildet und willst diese Summen im Tabellenblatt Jahr für jeden Monat anzeigen. Wo liegt denn jetzt das Problem?

Gruß Dieter

Hallo Dieter,

zu:
"Ich habe bei anderen Werten ... Summen
gebildet ..."
Zu den "Stunden-Spalten" gehört z.B. für 0 Uhr nicht nur E3,
sondern auch D3 (für 1 Uhr G3 und H3... usw) Und in diesen
"Vorspalten" (also D, G, J, M...) stehen Zahlen drin. Diese
addiere ich ganz normal in der BX-Spalte für jeden Tag.
Z.B. BX3 = 1.Januar bis BX33 = 31. Januar => Gesamtsumme
ist in Zelle BX35. Über diese BX35 gibt es einen Verweis zur
Jahresübersicht "Jahr" für Monat Januar.
Ist vielleicht auch etwas umständlich, aber für mich die einfachste
Lösung.

Jedoch mit oben beschriebenen Problem komme ich damit nicht
weiter. Deshalb auch erst meine Ansatz mit der WENN(ODER...
Variante.

Kann ich denn diese Funktion nicht abhängig vom Blatt (also nur
in den "Monats-Blättern") machen?

Ich hoffe, dass es jetzt verständlicher ist.

Gruß
mag

Hallo mag-lion!

Verstehe ich das jetzt richtig? Sollen waagerecht die Anzahl der Einträge [abint] gezählt werden?

Oder soll diese Funktion für die D, G, ... > BX-Spalte sein?

Gruß Dieter

Nicht ganz. (Das hab ich schon für jeden der Werte einzeln.)
Nicht die Anzahl, sondern wenn in der Zeile (also an dem
Tag - also schon waagerecht) einer dieser Werte [abint] auftritt,
dann soll z.B. in Zelle BY eine 1 stehen, damit ich weiss, dass an
diesem Tag einer der Werte eingetragen wurde (egal welcher).
Und so soll es für alle Tage des Monats sein. Letztendlich weiss
ich so, an wieviel Tagen diese Werte eingetragen wurden.

Nein, nein, nicht für die "D, G, ... > BX-Spalte", sondern schon für
die E, H, K, ...

Gruß
mag

Hallo mag-lion!

Also, irgendwie stehe ich im Wald. Wenn Du in jeder Tag-Zeile bei allen Monatenblättern die Formel "=TestDay()" in Spalte BY reinkopiert hast, sollte doch genau das funktionieren und Du musst nur noch die Summe für BY bilden.

Gruß Dieter

Ja, es funktioniert ja auch. Wenn ich ins "Januar"-Blatt gehe,
zeigts mir brav die Werte an. Dann gehe ich ins "Jahr"-Blatt
dort sehe ich kurz die Werte, die dann aber gleich darauf
"verschwinden" ??? Das verstehe ich nicht.

Gruß
mag

Nun noch mehr Verwirrung:
Hab jetzt für "Januar" und "Februar" die BY-Spalte gefüllt.
Schaue ich in "Januar" (Anzahl 5) und dann ins "Jahr" -
dann steht dort "5" - auch für "Februar"
Schaue ich in "Februar" (Anzahl 4) und dann ins "Jahr" -
dann steht dort "4" bei Januar und Februar.

Und nun???

Gruß
mag

Hallo mag-lion!

Ja, Du hast Recht , da stimmt was nicht. Muss ich selbst mal schauen, was das ist?

Gruß Dieter

Hallo Dieter,

na dann bin ich gespannt.
Evtl. wegen der globalen Definition?

Gruß
mag

Hallo nochmal!

Also, ich habe jetzt schon einige Dinge versucht und es will einfach nicht funktionieren.

Ich versuche mal einen Experten aus diesem Forum zu kontaktieren. Vielleicht hat er eine
Lösung für diese Problem.

Ich melde mich, sobald ich mehr weiß.

Gruß Dieter

Guten Morgen Dieter,

ok, dann hoffe ich, dass es kein Bug ist und er eine Lösung hat.
Danke erst mal und bis dahin.

Gruß
mag

Ein schönen guten Tag mag-lion!

Also, dank "bastla's" Unterstützung stehen jetzt 3 Varianten zur Auswahl.

Leider gibt es für die jetzige Lösung keine Möglichkeit, diese so zu ändern, dass sie funktioniert.

Variante 1: Das Makro TestDay in 12-facher Ausfertigung für jedes Monatsblatt.(TestDay1,TestDay2...)

Variante 2: Falls nur in den Summe BY-Spalten [abint] vorkommen, dann über die Formel "ZÄHLENWENN"

Variante 3. Eine spezielles Makro, das automatisch aufgerufen wird, wenn eine Zelle verändert wird.

Falls die Variante 2 für Dich in Frage kommt, würde ich diese empfehlen

=WENN(ODER(ZÄHLENWENN(E3:BV3;"a");ZÄHLENWENN(E3:BV3;"b");ZÄHLENWENN(E3:BV3;"i");ZÄHLENWENN(E3:BV3;"n");ZÄHLENWENN(E3:BV3;"t"));1;0)  

Ansonsten ist Variante 3 wohl die beste Lösung.

Was meinst Du?

Gruß Dieter

Hallo Dieter,

hm, ist es denn ein Bug? Oder warum geht diese Lösung nicht?

Mit Variante 1 hab ich schon rumprobiert. Was müsste ich denn da ändern,
damit diese so funktioniert.

Bei Variante 2 ist der gesamte Bereich dabei, also nicht direkt von den jeweiligen
Zellen abhängig. Würde aber auch gehen.

Zu Variante 3 - wie würde dieses Makro aussehen?

Gruß
mag

Hallo mag-lion!

Ja, wie bereits erwähnt funktioniert die bisherige Lösung nicht so wie gewünscht (Zirkelfunktion). Das lässt sich nunmal nur ändern, wenn für jedes Tabellblatt eine eigene Funktion existiert D.h. in Spalte BY für Januar z.B. "=TestDayJanuar", für Februar "=TestDayFebruar" usw. Also 12 mal das gleiche Makro mit jeweils einem anderen Namen. Diese Lösung findet weder der Kollege "bastla" noch ich besonders toll

Die Formel "ZÄHLENWENN ist daher die einfachste Lösung. Die Idee kommt von "bastla".

Bei einem Makro muss, falls die Tabelle geändert wird, auch das Makro geändert werden (Konstanten Zeilen, Spalten, Zähler...).

Die Variante 3 entspricht im Prinzip der bisherigen Lösung, aber mit dem Unterschied, dass das Makro automatisch mit der Adresse einer Zelle aufgerufen wird, sobald eine Zelle geändert wurde und der Wert direkt in die Zelle BY geschrieben wird. D.h. in der Zelle BY steht keine Formel.

Der Code folgt, sobald ich diesen - im Gegensatz zur ersten Lösung - ausgiebig getestet habe.

Gruß Dieter

Hallo nochmal!

Falls Du die Variante 3 verwenden möchtest, brauch ich explizit alle Tabellenblattnamen oder besser noch ein eindeutiges Indiz bzw. einen Eintrag in einer bestimmten Zelle, der nur in den Monatsblätter vorhanden ist. z.B. ein Wort in Text o.ä., das in Zelle XY steht.

Gruß Dieter

PS. Der Code funktioniert. Nun fehlen mir nur noch Deine Angaben, um sicherzustellen, dass nur Monatsblätter bearbeitet werden.

Guten Morgen Dieter,

hört sich ja gut an.

Also:
- es gibt 14 Tabellenblätter:

"Januar" bis "Dezember" - Zeilen 3-33=Tage, Spalten D,E,F für 0 Uhr

"Jahr" - als Zusammenfassung / Übersicht
"Werte" - hab hier u.a. diese 5 Buchstaben als Variablen hinterlegt

Guten Morgen mag-lion!

Mit diesen Info's, kann ich recht wenig anfangen

Was ich brauche, ist ein Konstanter Text z.B. eine Überschrift, die in allen Monatsblättern zumindest Teilgleich ist, also ausserhalb von veränderbaren Zellen.

Der Sinn ist der, dass ich die Möglichkeit haben muss, zu erkennen ob das aktuelle Tabellenblatt ein Monatsblatt ist.

Wenn ich eine feste Konstante habe, die nur in den Monatsblätter vorhanden ist, dann brauchte ich nur einen Wenn-Test durchführen z.B. Zelle A1 = Überschrif "Monatsübersicht 2009". Irgendwas in der Art.

Ansonsten wird es einaufwendiger Test, der von der Funktion her, in etwa so aussieht:

Wenn(Oder(Sheet="Januar";Sheet="Februar";Sheet="März";....);Dann mach was;Sonst mach nix)

Weniger aufwendig währe z.B. ein Test wie: Wenn(A5="Konstanter (Teil)-Text nur in Monatsblätter vorhanden";Dann mach was;Sonst mach nix)

Hoffe Du hast es so besser verstanden, was ich meine?

So und jetzt muss ich schon wieder für ein paar Stunden weg!

Gruß Dieter

Hallo Dieter,

ok, jetzt hab ich's verstanden. Glaub ich

Nehmen wir in den Monatsblättern Zelle A35 mit Inhalt "Monat"

Gruß
mag

Hallo mag-lion!

Das alte Makro bzw. Modul Im VB-Editor entfernen und diesen Quelltext in "DieseArbeitsmappe" kopieren.

Option Explicit
Option Compare Text

Const RangeOK = "A35"       'Test Monatsblatt: Zelle.Adresse  
Const SheetOK = "*Monat*"   'Test Monatsblatt: Zelle.Inhalt z.B. "Monat" oder "Text Monat Text"  

Const RowPos1 = 3           'Zeile 3  
Const RowEnde = 33          'Zeile 33  
Const ColPos1 = 5           'Spalte E  
Const ColEnde = 74          'Spalte BV  
Const ColWert = 77          'Spalte BY  

Const Counter = 3           'Zähler Spalten (E, H,...)  
Const Zeichen = "[atibn]"   'Gültige Zeichen Groß/Klein  

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Source As Range)
    
    Dim Row As Integer, Col As Integer, Result As Integer, i As Integer
    
    If Not Sh.Range(RangeOK) Like SheetOK Then Exit Sub
    
    Row = Source.Row:  Col = Source.Column

    If Col < ColPos1 Or Col > ColEnde Or Row < RowPos1 Or Row > RowEnde Then Exit Sub
    
    For Row = RowPos1 To RowEnde 'Alle aktualisieren, sonst Bug wenn mehrere Zellen markiert sind  
        Result = 0
        For i = ColPos1 To ColEnde Step Counter
            If Cells(Row, i) Like Zeichen Then Result = 1: Exit For
        Next
        Cells(Row, ColWert) = Result
    Next
End Sub

Schritt 1: Kopieren und Schließen des VB-Editors.
Schritt 2: In jedem Monatsblatt-Bereich "E3:BV33" in einer freien Zelle die Entfernen-Tase betägigen. Dadurch werden die Werte in der Spalte "BY" entsprechend aktualisiert.

Die Konstanten zur Identifizierung eines Monatsblattes können beliebig geändert werden. In der Konstanten
SheetOK ist darauf zu achten, dass die "*"-Zeichen erhalten bleiben. Dadurch kann das Wort auch innerhalb
von Text gefunden werden.

Generell wird nicht zwischen Groß/Kleinschreibung unterschieden.

Mehr fällt mir im Moment nicht ein

Gruß Dieter