Live-Forum - Die aktuellen Beiträge
Datum
Titel
26.04.2024 16:22:47
Aktualisierung Verknüpfung bei geschlossener Quelldatei
26.04.2024 16:05:28
PivotChart-Stapeldiagramm erstellen
26.04.2024 15:34:42
Überlagernde Textbox-Ansicht
26.04.2024 15:27:20
Excel öffnet nach Auswahl einer Excel diese leer
Du findest keine passende Antwort auf Deine Frage?
Stelle Deine Frage in unserem Live-Forum
Anzeige
Archiv - Navigation
1860to1864
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
Vorheriger Thread
Vorheriger Thread
Rückwärts Blättern
1860to1864
1860to1864
Aktuelles Verzeichnis
Verzeichnis Index
Verzeichnis Index
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

Fehlerteufel in Formel

Fehlerteufel in Formel
27.12.2021 17:24:47
Frank
habe folgendes Problem mit einer Excel-Formel. Habe mir eine Datei für Werbungskosten erstellt. Tabellenblatt „H“ dient nur als Hilfstabelle zur Berechnung. Dort wird nur in Zelle A20 das Jahr eingegeben,dass dann im Tablelenblatt „Monat“ Zelle C2 übernommen. In Zelle A7 steht nun die Formel für das Datum: 

=WENN($B$2=0;"";WENN($B$2+0=SVERWEIS($B$2+0;Feiertag;1);SVERWEIS($B$2+0;Feiertag;3);$B$2+0))
Das würde nun den 01. Januar ergeben,was soweit auch funktioniert. Bis A34 funktioniert die Formel auch, aber in A35 gibt sie mir einen Fehler aus:. 


=WENN($B$2="";"";WENN($B$2+28>MONATSENDE($B$2;0);"";WENN($B$2+28=SVERWEIS($B$2+28;Feiertag;1);  SVERWEIS($B$2+28;Feiertag;3);$B$2+28)))
Im Januar gibt die Formel mir den 29. aus, aber im Februar erscheint dort #NAME? obwohl da dann eigentlich nichts drin stehen dürfte, solange es kein Schaltjahr ist. Entsprechende Fehler erscheinen auch in den Zellen A36 und A37. Das wäre der erste Teil meines Problems, zum zweiten käme ich dann,wenn das erste gelöst ist.
Wer weiß,wo ich den Fehler gemacht habe?
Habe eine Beispielmappe mal hochgeladen https://www.herber.de/bbs/user/150022.xls
Lg Frank

39
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: die Formel sollte anders definiert werden
27.12.2021 17:59:44
neopa
Hallo Frank,
... um sie gleichzeitig kopierfähig zu machen in A7 so (auf Kürzung hab ich verzichtet):
=WENN($B$2=0;"";WENN($B$2++ZEILE(A1)-1=SVERWEIS($B$2+ZEILE(A1)-1;Feiertag;1);SVERWEIS($B$2+ZEILE(A1)-1;Feiertag;3);$B$2+ZEILE(A1)-1))
und diese nach unten ziehend kopieren.
Die bedingte Formatierung für die Feiertage ändern auf einfach: =ISTTEXT(A7)
Gruß Werner
.. , - ...
AW: die Formel sollte anders definiert werden
27.12.2021 18:15:29
Frank
Hallo Werner,
zumindest bekomme ich jetzt keinen Fehler. Im Monat Februar zeigt Excel mir aber den 01., 02 und 03. März an, was ja nicht sein soll.
AW: die bedingte Formatierung ...
27.12.2021 18:41:10
neopa
Hallo Frank,
... die dafür von mir vorgesehen war, hatte ich vergessen, Dir vorhin noch mit anzugeben. Sorry.
Einfach so: =MONAT(B$2+ZEILE()-7)MONAT(B$2) mit Zahlenformat: ;;;;
Gruß Werner
.. , - ...
Anzeige
AW: die bedingte Formatierung ...
27.12.2021 18:47:11
Frank
Wie sieht denn die Formal dann im Ganzen aus? Bin grad ein bissl irritiert bzw. überfordert? Muss mich erstmal wieder rein finden, hab Jahre nix mehr gemacht in Excel
AW: genau so wie geschrieben ...
27.12.2021 18:51:05
neopa
Hallo Frank,
... ist die von mir zuvor angegebene Formel in einer zusätzlichen bedingten Formatierungsregel einzugeben und dort das angegebene benutzerdefinierte Zahlenformat zuzuweisen.
Gruß Werner
.. , - ...
Das geht nicht in Xl-Versionen vor 12/2007, ...
27.12.2021 19:11:11
Luc:-?
…Werner,
denn ein ZahlenFormat kann man darin nicht bedingt einstellen. Das müsste dann generell fallweise ausgetauscht wdn, was VBA erforderte.
Gruß, Luc :-?
AW: wenn dies so ist, ...
27.12.2021 19:18:16
neopa
Hallo Luc, hallo Frank,
... dann eben ohne bedingte Formatierung und dafür eben mit folgender entsprechenden Erweiterung der Zellformel.
In A7:
=WENN(($B$2=0)+(MONAT(B$2+ZEILE()-7)MONAT(B$2));"";WENN(B$2+ZEILE(A1) -1=SVERWEIS(B$2+ZEILE(A1)-1;Feiertag;1);SVERWEIS(B$2+ZEILE(A1)-1;Feiertag;3);B$2+ZEILE(A1)-1))
und diese nach unten kopieren,
Gruß Werner
.. , - ...
Anzeige
AW: wenn dies so ist, ...
27.12.2021 19:22:44
Frank
Werner, genauso funktioniert es. Besten Dank
AW: bitteschön owT
27.12.2021 19:24:46
neopa
Gruß Werner
.. , - ...
AW: bitteschön owT
27.12.2021 19:50:39
Frank
Jetzt zu meinem anderen Problem: Bisher kam ich immer mit der Formel für eintägige Dienstreisen aus. jetzt kommen aber mehrtägige Dienstreisen hinzu und somit brauche ich ne Idee: C7= Abfahrt Zuhause, D7= Ankunft Zuhause 

E7=WENN(ODER(C7="";D7="");"";REST(D7-C7;1))



F7=WENN(UND(C7;D7="");"";WENN(UND((24*E7)>8;(24*E7)
für die Berechnung der Verpflegungspauschale. Soweit so gut. Da ich aber nun mehrere Tage unterwegs bin, brauch ich ne Möglichkeit die 28€ pro Tag unterzubringen. Mein Gedanke war, die Formel von F7 zu erweitern, was aber nicht funktionieren sollte,denn wenn ich in C7= 00:00 und D7=24:00 eingebe,so sind es nicht mehr als 24 Stunden. Mein zweiter Gedanke war, aus der Steuerelementen-Toolbox eine Umschaltfläche oder ähnliches einzubauen. Das bedarf aber wieder einer VBA-Programmierung.
Hat jemand ne einfachere Idee?
Anzeige
AW: bitteschön owT
28.12.2021 07:04:38
Luschi
Hallo Frank,
habe Werner's Formel getestet und sie klappt nat+rlich. Nun mußt Du nur noch die bedingte Formatierung anpassen von
=SVERWEIS($B$2+1;Feiertag;1;FALSCH) zu =SVERWEIS($B$2+ZEILE(A1)-1;Feiertag;1;FALSCH)
Gruß von Luschi
aus klein-Paris
Nächstes Problem
28.12.2021 08:12:40
Frank
Guten Morgen zusammen,
Luschi, auch deine Lösung funzt. Vielen Dank. Eventuell hast du auch ne Lösung für nachfolgendes Problem: Zeile E7 berechnet die Stunden 

=WENN(ODER(C7="";D7="");"";REST(D7-C7;1))
Für den Betrag des Verpflegungsmehraufwand in F7 habe ich folgende Lösung gefunden 


=WENN(E7>=24/24;28;WENN(E7>8/24;14;0))
wenn ich nun aber in C7=00:00 und in D7=24:00 eingebe, rechnet Excel aber nicht 24 Stunden, sondern 00:00
Hast du dafür eventuell ne Lösung?
Gruß Frank
Anzeige
AW: wozu dies so? ...
28.12.2021 09:21:56
neopa
Hallo Luschi,
... zur Markierung der Feiertage hatte ich als bedingten Formatierungsformel doch schon einfach =ISTTEXT(A7) angegeben.
Gruß Werner
.. , - ...
AW: genau so wie geschrieben ...
27.12.2021 19:21:02
Frank
So jetzt mal Schritt für Schritt:
1. 

=WENN($B$2=0;"";WENN($B$2++ZEILE(A1)-1=SVERWEIS($B$2+ZEILE(A1)-1;Feiertag;1);SVERWEIS($B$2+ZEILE(A1) -1;Feiertag;3);$B$2+ZEILE(A1)-1))
Formel in Zelle A7 eingegeben, wobei ich jetzt mal vermute, dass das zweite PLUS-Zeichen zuviel ist, oder?
2. Mit +Zeile(A1) kann ich gar nichts anfangen und verwirrt mich total.
3. 

=ISTTEXT(A7)
auf die bedingte Formatierung "Feiertag" angewandt.
4. da ich schon 3 bedingte Formatierungen habe, kann ich keine weitere hinzufügen. Habe dann eine gelöscht und stattdessen 

=MONAT(B$2+ZEILE()-7)MONAT(B$2)
eingefügt. Jedoch kann ich in Excel 2003 einer bedingten Formatierung kein Zahlenformat zuweisen, wobei sich mir auch der Sinn dieses Zahlenformats nicht ergibt. Dein erster Lösungsansatz funzt ja auch soweit, nur dass im Monat Februar der 1. 2 und 3. März nix zu suchen haben. Sorry dass ich grad nen Brett vorm Kopf habe
Gruß Frank
Anzeige
AW: Fehlerteufel in Formel
27.12.2021 18:07:57
ralf_b
füge die Monatsende Funktion der Formel nochmal ein. Kann sein du hast da einen Bereichsnamen oder so mit dem gleichen Namen.
AW: Fehlerteufel in Formel
27.12.2021 18:17:18
Frank
Hallo Ralf,
kann dir grad nicht wirklich folgen, was du mit dem Einfügen meinst.
Gruß Frank
AW: Fehlerteufel in Formel
27.12.2021 18:36:55
ralf_b
in der Beispieldatei wird mit bei der Formelüberprüfung der Fehlerwert #Name für die Funktion Monatsende angezeigt. Durch erneutes Eintragen der Funktion mit Hilfe der Autovervollständigung von Excel, war der Fehler plötzlich weg.
AW: Fehlerteufel in Formel
27.12.2021 18:49:29
Frank
Bei mir ändert sich gar nix. Selbes Ergebnis. Irgendwo steckt doch ein fehler in der Formel
AW: Fehlerteufel in Formel
27.12.2021 18:53:17
SF
Habe mir die Datei nicht angesehen, aber ich glaube Monatsende() gibt es erst ab Excel2007.
Anzeige
AW: meine Lösung kommt ohne MONATSENDE() aus owT
27.12.2021 18:56:40
neopa
Gruß Werner
.. , - ...
...benötigt aber letztlich VBA (s.oben)! owT
27.12.2021 19:13:29
Luc:-?
:-?
AW: VBA wäre hier mE nur dann notwendig, ...
27.12.2021 19:23:51
neopa
Hallo Frank,
... wenn unbedingt erzwungen werden soll, dass der Blattname mit dem Monatsnamen und Jahr bezeichnet werden soll, was aber ja nicht wirklich notwendig ist. OSTERN kann man ja auch ohne VBA ermitteln.
Gruß Werner
.. , - ...
Meine Anmerkung ...
28.12.2021 18:42:39
Luc:-?
…bezog sich auf den FormatAustausch zur Realisierung Deiner 1.Fml unter Xl11/2003-Bedingungen, Werner!
Luc :-?
AW: was meinst Du mit "Formataustausch"? ...
28.12.2021 19:01:46
neopa
Hallo Luc,
... in meiner ersten Formel hier im thread nehme ich doch einen solchen nicht vor.
Gruß Werner
.. , - ...
Doch, Werner, ...
29.12.2021 01:42:54
Luc:-?
…nur nicht in der HauptFml, sondern in der RegelFml des BedingtFormats. Hierbei wird sogar die echte ZahlenFormat-Eigenschaft der Zelle benutzt, wie wir vor Jahren, als das noch recht neu war (Xl12/2007 - der lang verschollene Gandalf von OL), schon mal feststellen konnten. Und genau das hätte man auch schon früher mit externen Bedingungen und VBA machen können. Ich hatte das seinerzeit sogar mal demonstriert, auch für andere Eigenschaften, und so schon damals mit mehr als 3 RegelFmln arbeiten können, den 3 seinerzeit möglichen internen als HptRegeln und weiteren UnterRegeln extern über davon abhängigen FormatAustausch (andere Farben u.ä.), was heute wg der fixierten Geltungsbereiche nicht mehr so oW möglich ist (man müsste Letztere in Einzel­Zell­Regeln aufspalten). „Leider“ deshalb, weil so bspw Farben aus Zellen übernommen u/o per FarbCode eingestellt wdn konnten, was heute ebenfalls nur mit Setzen der BedingtFormate per VBA möglich ist.
Normalerweise kann man alles, was manuell machbar ist, von wenigen Ausnahmen abgesehen auch mit VBA erreichen. Umgekehrt gilt natürlich auch, wobei es hierbei mehr Ausnahmen gibt, zB bei Verlaufsfarben in Zellen, obwohl Xl eigentlich alles bereit stellt, was auch in FormObjekten diesbzgl möglich ist (heißt in VBA nur etwas anders) aber nicht manuell anbietet (zumindest nicht bis Xl15/2013).
Morhn, Luc :-?
Anzeige
AW: nein, denn es ist mE etwas anders ...
29.12.2021 10:56:06
neopa
Hallo Luc,
... Du hattest am 27.12. um 19:13 geschrieben: "...benötigt aber letztlich VBA (s.oben)! owT" und dem hatte ich widersprochen. Dies weil
die von mir eingesetzte bed. Formatierungsformel meiner Erinnerung nach mindestens schon ab XL2000 die Funktion ISTTEXT() kannte und somit auch kein VBA bedarf. Eine noch ältere XL-Version als XL2000 wird wohl niemand mehr aktiv im Einsatz haben, der sich hier im Forum informiert.
Gruß Werner
.. , - ...
AW: was meinst Du damit? ...
29.12.2021 16:20:50
neopa
Hallo Luc,
... die Funktion MONAT() sollte doch auch eine in älteren ExcelVersion vorkommende Funktion sein. Oder nicht?
Gruß Werner
.. , - ...
Sicher, aber ich meine genau wie oben ...
29.12.2021 22:56:10
Luc:-?
…doch das einzustellende ZahlenFormat, Werner,
was vor Xl12/2007 nunmal nicht per BedingtFormatRegelFml eingestellt wdn kann. Das müsste dann per VBA geschehen!
Ich dachte, das wäre leicht verständlich, zumal ich ja s.oben in Klammern nachgesetzt hatte…
Luc :-?
AW: hab keine ältere Excelversion als XL2010, ...
30.12.2021 10:02:27
neopa
Hallo Luc,
... wenn in Excel-Versionen vor Xl12/2007 in bedingten Formatierungen kein benutzerdefiniertes Zahlenformat: ;;;; einstellbar sein sollte, dann bin ich mir aber ziemlich sicher, dass dann zumindest weiße Schrift auf weißen Hintergrund definierbar ist, welches der von TE angestrebten Darstellung auch entsprechen würde. Sollte anstelle dessen dort ein echter Zellwert =""" gewünscht sein, dann würde ich eben die Zellformel dementsprechend erweitern. Somit ist also mE hierfür kein VBA notwendig.
Gruß Werner
.. , - ...
Anzeige
Ich kann mich noch gut an ältere Versionen ...
30.12.2021 14:41:28
Luc:-?
…erinnern, Werner,
denn für die hatte ich den beschriebenen Aufwand treiben müssen/wollen (umschaltbare WertDarstellung als BruchZahl). Außerdem wurde Dir das im Thread auch von anderer Seite bestätigt!
Dass man so etwas in diesem speziellen Fall auch formelmäßig anders lösen könnte, tut hierbei nichts zur Sache, denn das wäre dann ja eine andere Fml. Dein ursprünglicher Weg würde VBA benötigen (!), etwas Anderes habe ich auch nicht aussagen wollen!
Eine Alternative ist eine Alternative, ein anderer Weg, und niemals dasselbe wie das Original. Das erkennt man schon daran, dass bei Deiner Alternative tatsächlich ein anderer Wert eingetragen wird, während Dein Original nur ein Format setzt, wodurch weiter auf den Originalwert zugegriffen wdn kann, weil das BedingtFormat nicht automatisch in eine abfragende Zelle übernommen wird (im Ggsatz zu einem DirektFormat, das aber den Zugriff auf den OriginalWert letztlich auch nicht verhindern kann, sondern ggf nur seine DirektDarstellung). Das wäre auch bei der hier durchaus möglichen zellfarbigen Schrift der Fall (falls nicht mal umgefärbt wird). Und in allen diesen FormatFällen wäre ein direkter WertEintrag auch in der Edit-Zeile zu sehen, falls das nicht anderweitig verhindert wird. Auf jeden Fall können solche Lösungen zu bösen Fallen wdn und sollten nur zum Ausblenden von unschädlichen, aber unerwünschten Anzeigen benutzt wdn wie bspw für 0. Letztere wäre dann, neben evtl 1, ggf auch eine bessere Alternative, unsichtbar formatiert.
Luc :-?
AW: Ich kann mich noch gut an ältere Versionen ...
30.12.2021 19:43:44
Frank
Hallo,
also ich kann ein bissl Licht in Dunkel bringen, da ich immer Excel 2003 nutze und wie ich schon geschrieben habe, bei einer bedingten Formatierung kein Zahlenformat definieren kann.
AW: nun, das war mittlerweile geklärt ...
30.12.2021 20:01:14
neopa
Hallo Frank,
... durch die Aussagen von Luc. Aber es gäbe ja auch ohne VBA noch die von mir benannten Alternativen (also mit bedingter Formatierung weiße Schrift auf weißen Untergrund) oder eine entsprechende Zellformelerweiterung.
Gruß Werner
.. , - ...
AW: zu Deinen "nächsten Problem" ...
28.12.2021 09:09:20
neopa
Hallo Frank,
... antworte ich Dir im thread auf Deine entsprechenden Beiträge von gestern 19:50 und heute 8:12 an dieser Stelle.
Da Du bisher nur eine diesbzgl. unvollständige Beispieldatei eingestellt hast, gehe ich mal von meinen Annahmen aus und habe dafür nachfolgende Formel aufgestellt, die Du einfach nach unten ziehend kopieren kannst.
Arbeitsblatt mit dem Namen 'Februar 2021'
 CDE
4Abfahrt
Zuhause		Ankunft
Zuhause		Stunden
unterwegs
5
6
7   
807:00 17:00
9  24:00
10  24:00
11 20:0020:00
12   
13   
14   
1512:0017:3005:30
16   

ZelleFormel
E7=WENN((ANZAHL(C7:D7)=2)+(D7>"");(0&D7)-(0&C7);WENN(ISTZAHL(C7);1-(0&C7);WENN(VERWEIS(9;1/(D$1:D7>"");ZEILE(D$1:D7))&gt=VERWEIS(9;1/(C$1:C7>"");ZEILE(C$1:C7));"";1)))
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
.. , - ...
AW: zu Deinen "nächsten Problem" ...
28.12.2021 09:55:14
Frank
Hallo Werner,
ja da hast du recht, da mir eine Lösung - zumindest hatte ich gedacht, ich hätte die Lösung, vorhin erst eingefallen ist. Hier nochmal die aktuelle Datei https://www.herber.de/bbs/user/150032.xls Zelle F7 enthält nun folgende Formel: 

=WENN(E7>=24/24;28;WENN(E7>8/24;14;0))
soweit schön und gut, nur dass mir ohne dass in C7,D7 und E7 irgendetwas steht. Eigentlich müsste doch F7 leer sein,oder? Ansonsten passt deine Lösung jetzt perfekt. Frank ist happy. Vielen lieben Dank dafür.
Gruß Frank
AW: in Spalte E stehen Formeln ...
28.12.2021 12:44:26
neopa
Hallo Frank,
... die ein Ergebnis haben. Ein Ergebnis kann natürlich auch ="" sein und dies wird von Excel als Textwert angesehen und da für Excel jeder Textwert größer als die größte Zahl ist, ergibt sich in Spalte F das, was Du in Deiner Beispieldatei zu stehen hast.
Mit dieser Formel in F7: =WENN(E7="";0;(E7=1)*28+(E7&gt1/3)*(E7&lt1)*14) mit benutzerdefinierten Zahlenformat: #.##0 €;; erhältst Du das von Dir angestrebte, wenn Du diese dann auch noch nach unten kopierst.
Gruß Werner
.. , - ...
AW: in Spalte E stehen Formeln ...
28.12.2021 17:38:51
Frank
Hallo Werner,
hab die Formel getestet und sie macht genau das, was sie machen soll. Ich habe jetzt mal angefangen meine Steuererklärung zu machen und es passierte genau das, womit bzw. woran ich gar nicht gedacht habe. Am 28. Februar reise ich an, also gebe ich 10:00 in C34 ein. Dann drück ich den Button und wähle in B2 den Monat März aus der Liste aus. dort gebe ich dann in D11 20:00 ein und siehe da, die Formel reagiert nicht so, wie ich gedacht habe, denn die Montsübergreifende Anreise bzw. Dienstreise hatte ich gar nicht auf dem Schirm. Die Formel an sich macht ja nur das, was sie machen soll. Ich teile dir das nur mit für den Fall, dass einer der ehrgeizigen Excel-Profis bist, die das noch austüfteln wollen. Ich behebe das Problem, indem ich in D34 einfach 24:00 und im Tabellenblatt März in C7 00:00 eingebe, denn dann passt wieder alles.
Jedenfalls bedanke ich mich recht herzlich für deine Mühe und sollte noch was sein, werde ich mich melden
Gruß Frank
AW: eine monatsübergreifende Lösungsformel ...
28.12.2021 18:56:46
neopa
Hallo Frank,
... ist bei Dir gewählten Datenstruktur mit keiner Formel realisierbar. Die von Dir als "Ersatz" dafür gewählte entsprechende Datenerfassung ist mE eine ausreichende Lösung.
Gruß Werner
.. , - ...
AW: Fehlerteufel in Formel
01.01.2022 12:30:51
Herbert_Grom
Hallo Frank,
hast du schon mal überlegt, deine Tagesdaten auf einem Blatt fortlaufend zu erfassen und dann auf einem separaten Blatt deine Auswertung zu machen? Falls du daran interessiert bist, zeige ich dir gerne, wie ich mir das denke. Da der Post aber bald aus der Liste herausfällt, kannst du mir gerne eine eMail senden. Meine eMail-Adr. findest du hier bei den "Profilen". Übrigens, ich war 35 Jahre ADM und kenne das Problem von meiner eigenen Anwendung.
Servus

Beliebteste Forumthreads (12 Monate)

Anzeige
Jetzt das Excel-Portal bewerten: Deine Stimme zählt!

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige