Hallo alle zusammen!
Ich suche nach einer Möglichkeit, in Excel 2007 eine Wenn-dann-Funktion zu bilden, die - je nach dem, ob eine anderen Zelle eine bestimmte Zeichenfolge enthält - einen definierten Wert ausgibt.
Konkretes Beispiel:
Eine Tabelle enthält eine Spalten mit den Wochentagen eines Monats (nach dem Muster „Montag, 04.04.2011“ formatiert). Die Soll-Arbeitszeit an den Werktagen beträgt 8,5 h mit Ausnahme der Mittwoche, an denen nur 8 h gearbeitet wird, und der Wochenenden mit 0 h Arbeitszeit. Parallel zu der Spalte mit den Wochentagen soll nun eine Spalte mit der Sollarbeitszeit des jeweiligen Tages erscheinen. In deren Zellen möchte ich eine Formel generieren, die dem Sinne nach folgendes tut: „Falls Zelle XY die Zeichenfolge ‚Mittwoch‘ enthält, dann =8:00:00; falls Zelle XY die Zeichenfolge ‚Samstag‘ oder ‚Sonntag‘ enthält, dann =0:00:00; ansonsten =8:30:00“. Mit dem Ausgabewert sollen dann anderen Zellen arbeiten können (um z.B. Differenz zwischen Soll- und Ist-Arbeitszeit auszugeben).
Kann man das irgendwie realisieren?
Vielen Dank im Voraus und guten Start in die Woche!
MacBarney
Hallo Mark,
Excel „weiß“ an welchen Wochentag ein bestimmtes Datum fällt.
Außerdem steht in der Zelle vom Datum nicht „Montag 04.04.2011“
sondern die Zahl 40637 die nur im Zellenformat Datum „TTTT TT.MM.JJJJ“ formatiert ist.
das kommt so:
Es sind vom 1.1.1900 bis zum 04.04.2011 genau 40637 Tage vergangen.!!
Daher ist es viel einfacher das Datum direkt abzufragen.
Angenommen das Datum steht in A1:
=WAHL(WOCHENTAG(A1;2);1;2;3;4;5;6;7)
Ersetze die Ziffern 1;2;3;4;5;6;7 durch deine Soll-Stunden von Mo.-So.
Gruß Holger
Nachtrag:
Verwendest du 8:30 statt Dezimalwerte [8,5] dann gibt es zwei Möglichkeiten:
=WAHL(WOCHENTAG(A1;2);„8:30“;2;3;4;5;6;7)*1
oder
=WAHL(WOCHENTAG(A1;2);ZEIT(8;30;0);2;3;4;5;6;7)
Grüezi zusammen
Daher ist es viel einfacher das Datum direkt abzufragen.
Angenommen das Datum steht in A1:
=WAHL(WOCHENTAG(A1;2);1;2;3;4;5;6;7)
Ersetze die Ziffern 1;2;3;4;5;6;7 durch deine Soll-Stunden von
Mo.-So.
Aus Gründen der Übersichtlichkeit und der einfachen Änderung würde ich hier sogar noch einen Schritt weiter gehen und eine kleine Auflistung der Soll-Zeiten pro Wochentag erstellen:
Tabellenblatt: [Mappe1]!Tabelle1
│ F │ G │
──┼───────────┼───────────┤
1 │ Wochentag │ Soll\_Zeit │
──┼───────────┼───────────┤
2 │ 1 │ 8:30:00 │
──┼───────────┼───────────┤
3 │ 2 │ 8:30:00 │
──┼───────────┼───────────┤
4 │ 3 │ 8:00:00 │
──┼───────────┼───────────┤
5 │ 4 │ 8:30:00 │
──┼───────────┼───────────┤
6 │ 5 │ 8:30:00 │
──┼───────────┼───────────┤
7 │ 6 │ 0:00:00 │
──┼───────────┼───────────┤
8 │ 7 │ 0:00:00 │
──┼───────────┼───────────┤
9 │ Total: │ 42:00:00 │
──┴───────────┴───────────┘
Benutzte Formeln:
G9: =SUMME(G2:G8)
Zahlenformate der Zellen im gewählten Bereich:
G2:G9 haben das Zahlenformat: [h]:mm:ss
Dann kann mit einem einfachen SVERWEIS() die Soll-Zeit in die Spalte geholt werden. Wenn es einer Änderung bedarf, dann nur in der ersten Hilfstabelle.
Tabellenblatt: [Mappe1]!Tabelle1
│ A │ B │
───┼───────────────┼───────────┤
1 │ Datum │ Soll-Zeit │
───┼───────────────┼───────────┤
2 │ Fr 01.04.2011 │ 8:30:00 │
───┼───────────────┼───────────┤
3 │ Sa 02.04.2011 │ 0:00:00 │
───┼───────────────┼───────────┤
4 │ So 03.04.2011 │ 0:00:00 │
───┼───────────────┼───────────┤
5 │ Mo 04.04.2011 │ 8:30:00 │
───┼───────────────┼───────────┤
6 │ Di 05.04.2011 │ 8:30:00 │
───┼───────────────┼───────────┤
7 │ Mi 06.04.2011 │ 8:00:00 │
───┼───────────────┼───────────┤
8 │ Do 07.04.2011 │ 8:30:00 │
───┼───────────────┼───────────┤
9 │ Fr 08.04.2011 │ 8:30:00 │
───┼───────────────┼───────────┤
10 │ Sa 09.04.2011 │ 0:00:00 │
───┼───────────────┼───────────┤
11 │ So 10.04.2011 │ 0:00:00 │
───┼───────────────┼───────────┤
12 │ Mo 11.04.2011 │ 8:30:00 │
───┼───────────────┼───────────┤
13 │ Di 12.04.2011 │ 8:30:00 │
───┼───────────────┼───────────┤
14 │ Mi 13.04.2011 │ 8:00:00 │
───┼───────────────┼───────────┤
15 │ Do 14.04.2011 │ 8:30:00 │
───┼───────────────┼───────────┤
16 │ Fr 15.04.2011 │ 8:30:00 │
───┼───────────────┼───────────┤
17 │ Sa 16.04.2011 │ 0:00:00 │
───┴───────────────┴───────────┘
Benutzte Formeln:
B2 : =SVERWEIS(WOCHENTAG(A2;2);$F$2:blush:G$8;2;0)
Zahlenformate der Zellen im gewählten Bereich:
A2:A17 haben das Zahlenformat: TTT TT.MM.JJJJ
B2:B17 haben das Zahlenformat: [h]:mm:ss
Tabellendarstellung erreicht mit dem Code in FAQ:2363
Mit freundlichen Grüssen
Thomas Ramel
1 „Gefällt mir“
Vielen Dank für die guten Tipps! Das hat alles super geklappt! Jetzt habe ich die Tabelle so, wie ursprünglich geplant!
Zu Lernzwecken würde ich trotzdem gerne wissen, ob es dennoch einen Befehl gibt, der nach Zeichenfolgen filtert, d.h. so, wie ich es in meiner ursprünglich Frage geäußert haben. Angenommen, es ginge nicht um Datumsangaben, sondern um unsystematische Texte. Wie könnte man dann eine Auswahl danach gestalten, ob in einer Zelle z.B. ein bestimmtes Wort vorkommt?
Zu Lernzwecken würde ich trotzdem gerne wissen, ob es dennoch
einen Befehl gibt, der nach Zeichenfolgen filtert, d.h. so,
wie ich es in meiner ursprünglich Frage geäußert haben.
Angenommen, es ginge nicht um Datumsangaben, sondern um
unsystematische Texte. Wie könnte man dann eine Auswahl danach
gestalten, ob in einer Zelle z.B. ein bestimmtes Wort
vorkommt?
Hallo,
Thomas hat ja schon den Hinweis gegeben eine Liste per SVerweis() abzufragen.
das Geht auch mit Montag bis Sonntag als Suchkriterium.
Beispiel für die Anwendung: http://www.online-excel.de/excel/singsel.php?f=9
Eine verschachtelte Wenn()-Formel kann das auch:
=WENN(A1=„Mittwoch“;ZEIT(8;0;0);WENN((A1=„Samstag“)+(A1=„Sonntag“);0;ZEIT(8;30;0)))
Wenn der Suchtext in nicht alleine in der Zelle steht solltest du dir die Funktionen suchen / finden genauer ansehen.
Sehr gute Lösungen für diverse Probleme gibt es auch hier:
http://www.excelformeln.de/formeln.html
Gruß Holger