Live-Forum - Die aktuellen Beiträge
Datum
Titel
28.03.2024 21:12:36
28.03.2024 18:31:49
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
Inhaltsverzeichnis

Das Problem mit der Null

Das Problem mit der Null
05.01.2022 10:44:48
Daniel
Werte Foren-Gemeinschaft,
Excel differenziert die Null als echten Wert und andererseits auch als fehlenden Wert. Das ganze wird gut nachvollziehbar an Patient Zander (Name geändert): https://www.herber.de/bbs/user/150152.xlsm
Für das Monitoring (m)einer Therapiegruppe gibt es Wochen, in denen das Medienlogbuch (noch) nicht geführt wird. Diese Zellen werden leer gelassen und als Null codiert. Alle anderen; auch unvollständig geführte Wochen fließen als Mittelwert in die Auswertung ein.

=WENN(ANZAHLLEEREZELLEN(M283:S283)=7;"0";MITTELWERT(M283:S283))
Es gibt aber Patienten, die es schaffen in manchen Wochen tatsächlich nicht täglich zu spielen, was als Erfolgskontrollwert als explizite Null eingetragen wird. Excel kann natürlich keinen Mittelwert daraus bilden; aber zumindest wird die Wochensumme als eindeutige Null erfasst.

=WENN(ANZAHLLEEREZELLEN(M283:S283)=7;"0";SUMME(M283:S283))
Der hier im Forum von Sigi und Chris_L entwickelte VBA Code zur Auswertung des Therapieerfolges (Link: https://www.herber.de/forum/archiv/1860to1864/1862412_Formelberechnung_via_VBA.html#1862412) weist in Spalte W die prozentuale Veränderung zur Vorwoche und in Spalte die Veränderung zur ersten Datenwoche nach Therapiestart aus. Die Prozedur kann mit dem Ereignis, dass echte Nullstunden eingetragen werden, nicht richtig umgehen. In der Bsp.-Datei ist bei Pat. Zander in Woche 11 eine Totalreduktion erfolgt, die mit -100% Verhaltensmaß ausgewiesen werden müsste. Anstelle greift die Nullrunden-Schleife.
Frage:
Wie bringe ich Excel bzw. dem Code bei, echte Null-Wochen von Missing-Weeks zu unterscheiden? Dazu müsste man dem Code von Sigi ggf. anpassen, was meine Fähigkeiten aber deutlich übersteigt. Als Denkansatz müsste man vielleicht Missing Weeks als Text-String codieren als - oder sonst ein Zeichen und den VBA für dieses Szenario flott machen.
Vielleicht habt Ihr - oder speziell Sigi/Chris_L wieder eine Idee dazu.
Freundliche Grüße
Daniel Jäger

38
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: ... braucht keins zu sein ...
05.01.2022 10:52:35
neopa
Hallo Daniel,
... wenn Du keine 0 ermittelst
Ohne jetzt alles mir anzusehen (den VBA-Code habe ich sowie so schon deaktiviert), mit folgender Formel in T4:
=WENN(ANZAHL(M4:S4)&gt0;MITTELWERT(M4:S4);"")
kannst Du zumindest ein Problem umgehen.
Gruß Werner
.. , - ...
AW: ... braucht keins zu sein ...
05.01.2022 11:13:49
Daniel
Hi MCO,
leider haben wir diese Funktion nicht bei unserem 2016 Betriebs-Excel mit Firmenlizenz; daheim nutze ich 365, was mir aber recht wenig bringt. Aber die Idee ist nach ersten Recherchen natürlich gut!
LG Daniel Jäger
AW: also die von mir genutzten Funktionen ...
05.01.2022 11:17:25
mir
Hallo Daniel,
... in der aufgezeigten Formel, gibt es schon seit anno dazumal und auf jeden Fall auch in XL2016.
Gruß Werner
.. , - ...
Anzeige
AW: ... braucht keins zu sein ...
05.01.2022 11:17:46
Daniel
... mist falscher Thread; Entschuldigung!
Danke für die Idee. Im Grunde wird dadurch die Missing Week als "leer" deklariert, welche aber den VBA Auswertungscode massiv in die Irre führt. Bin natürlich für weitere Impulse dankbar.
LG Daniel Jäger
AW: dann passe Deinen VBA - Code an owT
05.01.2022 11:19:12
neopa
Gruß Werner
.. , - ...
nur, weil ich neugierig bin...
05.01.2022 11:20:34
Oberschlumpf
Hi Daniel
...würd ich gern mal wissen, was Dein Excel anzeigt, wenn du Werner's Idee ausprobierst.
Vielleicht könntest du ja per Upload eine Bsp-Datei zeigen, in der du Werners Idee "eingebaut" hast.
Denn ich nutze a) auch Office 2016 und b) hab =WENNFEHLER(...) schon x mal erfolgreich eingesetzt.
Ciao
Thorsten
Anzeige
AW: nur, weil ich neugierig bin...
05.01.2022 12:18:05
Daniel
Hi Thorsten,
ich habe Dir Werners Ansatz in eine abgespeckte Tabelle gepackt. Die Formel funktioniert prima und flott - bis auf zwei Dinge, die mich stören:
- alle Folgewochen mit Daten nach einer Missing Week werden mit +100% ausgewiesen; hier hatte ich mir den Bezug zur letzten Datenreihe/Woche gewünscht, um das naheliegendste reale Verhaltensmaß im Prozess zu quantifizieren
- entsprechend wird auch die erste Wochenmessung wird mit +100% ausgewiesen und somit als Totalrückfall; inhaltlich ist das falsch; dort soll 0% als Base-Rate stehen (was z.B. der Sigi Code macht)
https://www.herber.de/bbs/user/150164.xlsm
Man denkt immer, ach die paar Summen und Prozente ... aber es ist viel komplexer als man denkt.
Freundliche Grüße
Daniel Jäger
P.S. Die Office Antwort bezog sich auf eine andere Frage, die ich heute schon stellte. Bin vorhin nur durcheinander gekommen. Sorry
Anzeige
AW: dazu nun ...
05.01.2022 13:14:36
neopa
Hallo Daniel,
... Du meinst die Werte , die mit der Formel in Spalte W ermittelt werden?
Ersetze dazu lediglich in der Formel in W4 (diese kommt mir irgendwie bekannt vor) die letzte 1 durch eine 0. also so:
... ;WENNFEHLER(SUMME(M4:S4)/SUMME(M3:S3)-1;0))
und kopiere diese nach unten.
Gruß Werner
.. , - ...
AW: dazu nun ...
05.01.2022 16:10:30
Daniel
Hi Werner,
ja, es ist auch Deine Formel aus dem Dezember-Thread (habe Dich auch mehrfach lobend erwähnt). Dein Tipp löst die Sache mit der Base-Rate zu Staffelbeginn. Sehr gut!
Zwei Dinge funktionieren noch nicht:
- nach einer expliziten Null-Woche und einer Missing Week startet der Prozentwert wieder bei Null;
- nach einer Missing soll aber der nächst zurückliegende Wochenwert als Vergleich herangezogen werden (siehe VBA Code von Sigi in der Tabelle; den verstehe ich aber nicht vollends)
- nach einer expliziten Null-Woche müsste der Prozentwert dann auf +100 Prozent springen, da jeder noch so kleine Wert dann als Dysregulation gewertet wird (ist streng, aber inhaltlich stringent)
Und eine Frage noch: Wieso prüft Deine Formel vorne die Spalte C ab? Dorst steht nur einer der Trainer Namen, die eigentlich die gesamte Staffel konstant bleiben. Der Wochen-Zähler steht in Spalte L. Meine Anpassungsversuche geben aber leere Ergebnisse aus ...
Hier eine schlanke Fassung mit allen Änderungen (aus beiden Threads): https://www.herber.de/bbs/user/150171.xlsm
Viele Grüße
Daniel Jäger
Anzeige
AW: schau ich mir ewohl erst morgen an ...
05.01.2022 16:46:19
neopa
Hallo Daniel,
.. hab jetzt gleich einen Termin. Nur soviel vorab. Mit VBA-Lösungen beschäftige ich mich nicht und lade auch aus Sicherheitsgründen mir derartige Dateien stets ohne VB-Code aktiviere.
Gruß Werner
.. , - ...
AW: schau ich mir ewohl erst morgen an ...
05.01.2022 18:12:28
Daniel
Alles klar; ich finde das Problem so ambivalent; mathematisch schlicht, aber in Excel so unbequem ...
Ich freue mich auf Deine/Eure weiteren Ideen ...
LG Daniel Jäger
AW: evtl. hilfreich(er) wäre ...
05.01.2022 19:47:35
neopa
Hallo Daniel,
... wenn Du konkret mit Zelladresse angeben könntest, welche Werte falsch sind und für zwei/drei Zellen den da von Dir erwarteten Wert.
Dann sehe ich morgen weiter.
Ach so. wenn in Spalte C immer der gleiche Name steht, braucht dieser natürlich in der Auswertung nicht explizit beachtet werden. Die Formel wird dann dafür natürlich einfacher.
Gruß Werner
.. , - ...
Anzeige
Helfershelfer ...
05.01.2022 22:59:31
Daniel
Hi Werner,
ich habe Dir ein schlankes File zurecht gemacht und die letzten Stunden Deinen Rat hinsichtlich intelligenter Tabellen befolgt. Link: https://www.herber.de/bbs/user/150185.xlsm
Du findest Deine zur prozentualen Veränderung in Spalte W, welche ich in Spalte X kommentiert habe. Spalte AA ist die Hilfsspalte zu der anderen Fragestellung von heute.
Direkt daneben siehst Du die VBA-Lösung von Sigi. Sein Code liest sowohl die die Veränderung zur Vorwoche (Y) als auch zur ersten Messwoche (Z) aus, kommt aber mit expliziten Nullwochen nicht klar (Zeile 13). Diese sind natürlich als therapeutisches Ziel relevant und dürfen nicht wie eine Missing Week behandelt werden.
Mal sehen, mit welcher Lösung wir der Lösung näher kommen.
Schönen Abend und viele Grüße aus Berlin
Daniel Jäger
Anzeige
AW: zur Ermittlung der prozentualen Änderung ...
06.01.2022 10:12:58
neopa
Hallo Daniel,
... für das von Dir angestrebte in Spalte W ist nun für mich mit Deiner neu eingestellten Datei nun einfacher nachvollziehbar. Allerdings ist für mich noch etwas unverständlich Deine Vorgabe für W14. Denn dort müsste nach Deinen sonstigen Vorgabewerten mE an sich ein größerer Wert (an sich =unendlich) stehen als z.B. in W20. Dies hab ich jetzt auch so in meiner geänderten Auswertungsformel vorgesehen.
Zunächst sind für eine korrekte Auswertung aber Deine Formeln in Spalte T und U noch anzupassen (Änderungen sind fett markiert)
In T3: =WENN(ANZAHL(M3:S3)&gt0;MITTELWERT(M3:S3);"-")
in U3: =WENN(ANZAHL(M3:S3)&gt0;SUMME(M3:S3);0)
Dann folgende geänderte Formel für Spalte W und alle Formeln nach unten kopieren.
In W3:
=WENN((ANZAHL(M3:S3)=0)+(D3="");"";WENN(D3D2;0;WENN(ANZAHL(M3:S3)&lt7;1;MIN(SUMME(M3:S3)/VERWEIS(9;1/(U2:U$30)/(D2:D$3=D3);U$3:U3)-1;100))))
Hierzu noch anzumerken ist, dass ich bewusst die geschachtelten WENN()-Formelteile nicht durch den Einsatz von WAHL() verkürzt habe und ich die Formel noch nicht für die nun vorhandene "intelligente" Datentabelle definiert habe. Das kann in einem nächsten Schritt nachgeholt werden.
Deine Ausführungen zu den Werten den Spalten Y und Z kann ich nicht nachvollziehen. Ich schrieb Dir ja schon, dass ich jede XLSM bzw. XLSB lediglich ohne Makros aktiviere und ich demzufolge bei mir keine Ergebniswerte sehen kann sondern immer nur "#NAME?". Du müsstest also erklären, was dort wie berechnet werden soll, wenn Du dafür auch noch nach einer Formellösung suchen solltest.
Gruß Werner
.. , - ...
Anzeige
AW: zur Ermittlung der prozentualen Änderung ...
06.01.2022 11:28:04
Daniel
Hi Werner,
ich habe Deine angepassten Formeln übernommen. Es klappt alles bis auf eine Ausnahme, die Du bereits ansprachst: Nach einer expliziten Nullwoche ist gemäß der Mengenlehre jeder Folgewert im Grunde unendlich skaliert. Die Formel nimmt den letzten verfügbaren Wochenmesswert analog wie bei einer Missing Week. Man sieht das schön in Zeile 14, die die prozentuale Veränderung zu Zeile 12 ausweist. Zeile 13 ist die explizite Null-Woche.
Wie sähe denn Deine Formel für die Berechnung der prozentualen Veränderung zur jeweiligen ersten Messwoche aus? Beim Versuch Deine Formel anzupassen ist mir aufgefallen, dass sich der Bezug zur ersten Zelle immer mit verändert ...
Um Deinem Sicherheitsbedürfnis nachzukommen, kopiere ich Dir gerne die VBA Lösung von Sigi hier rein. Die Ergebnisse der zwei Funktionen werden via Matrixformel ausgelesen.
Spalte X (Veränderung zur Vorwoche):

=AendVorwoche($A$3:A6;$D$3:D6;$T$3:T6;0)

Function AendVorwoche(BereichStaffel As Range, BereichNamen As Range, BereichDaten As Range, Optional DefaultProz As Variant) As Variant
Dim Z1 As Double, Z2 As Double, c As Long, n As Long, i As Long, s As Long, Stf As Long
Dim gef1 As Boolean, gef2 As Boolean, Nm As String, Dflt As Variant
Application.Volatile
If Not IsMissing(DefaultProz) Then
If IsNumeric(DefaultProz) Then
Dflt = DefaultProz / 100
Else
Dflt = ""
End If
Else
Dflt = ""
End If
c = BereichDaten.Count
n = BereichNamen.Count
s = BereichStaffel.Count
If n = c And n = s Then
If BereichDaten(c)  0 Then
Stf = BereichStaffel(c)
Nm = BereichNamen(c)
For i = c To 1 Step -1
If BereichDaten(i) > 0 And Stf = BereichStaffel(i) And Nm = BereichNamen(i) And Not gef1 Then
Z1 = BereichDaten(i)
gef1 = True
ElseIf gef1 Then
If BereichDaten(i) > 0 And Stf = BereichStaffel(i) And Nm = BereichNamen(i) And Not gef2 Then
Z2 = BereichDaten(i)
gef2 = True
End If
End If
If gef1 And gef2 Then Exit For
Next i
If gef1 And gef2 Then
AendVorwoche = (Z1 - Z2) / Z2
Else
AendVorwoche = Dflt
End If
Else
AendVorwoche = ""
End If
Else
AendVorwoche = "#WERT!"
End If
End Function
Spalte Z (Veränderung zur 1. Woche):

=Aend1Woche($A$3:A6;$D$3:D6;$T$3:T6;0)

Function Aend1Woche(BereichStaffel As Range, BereichNamen As Range, BereichDaten As Range, Optional DefaultProz As Variant) As Variant
Dim Z1 As Double, Z2 As Double, c As Long, n As Long, i As Long, s As Long, Stf As Long
Dim gef1 As Boolean, gef2 As Boolean, Nm As String, Dflt As Variant
Application.Volatile
If Not IsMissing(DefaultProz) Then
If IsNumeric(DefaultProz) Then
Dflt = DefaultProz / 100
Else
Dflt = ""
End If
Else
Dflt = ""
End If
c = BereichDaten.Count
n = BereichNamen.Count
s = BereichStaffel.Count
If n = c And n = s Then
If BereichDaten(c)  0 Then
Stf = BereichStaffel(c)
Nm = BereichNamen(c)
For i = c To 1 Step -1
If BereichDaten(i) > 0 And Stf = BereichStaffel(i) And Nm = BereichNamen(i) And Not gef1 Then
Z1 = BereichDaten(i)
gef1 = True
ElseIf gef1 Then
If BereichDaten(i) > 0 And Stf = BereichStaffel(i) And Nm = BereichNamen(i) Then
Z2 = BereichDaten(i)
gef2 = True
End If
End If
Next i
If gef1 And gef2 Then
Aend1Woche = (Z1 - Z2) / Z2
Else
Aend1Woche = Dflt
End If
Else
Aend1Woche = ""
End If
Else
Aend1Woche = "#WERT!"
End If
End Function
Ich habe den Eindruck, dank Deiner Expertise der Lösung jetzt schon sehr nahe zu sein ...
Viele Grüße
Daniel Jäger
Anzeige
AW: hierzu ...
06.01.2022 14:22:55
neopa
Hallo Daniel,
... möglicherweise hab ich mich bzgl. VBA noch nicht richtig verständlich gemacht. Das ich mir keine Dateien mit Makros aus dem Internet aktiviere, hat nur zum Teil mit "Sicherheitsbedürfnis" zu tun. Ich beschäftige mich einfach nicht mit VBA, dies u.a. weil ich es nicht selbst brauche.
Einfacher wäre es deshalb, wenn Du mir für mindestens drei relevante Wochen je Name angibst, welche Werte Du in Deiner bereits eingestellten Datei erwartest und warum dies genau so sein soll bzw. woraus diese sich ergeben.
Gruß Werner
.. , - ...
AW: Das Problem mit der Null
06.01.2022 15:25:14
Sigi.21
Hallo Daniel,
wie du richtig feststellst, ist für Excel die Unterscheidung zwischen „0“ und „leerer“ Zelle nicht immer gewährleistet. Die Summe ergibt immer 0. Dies war der Grund, warum in der VBA-Fkt. diese Fälle nicht ermittelt werden.
Die VBA-Fkt. prüft ja nur die „Summe Woche“ oder ggf. „Durchschnitt Tag“, aber nicht Do-Mi, die ja leer sein können. In der Summe bzw. beim Durschnitt steht aber immer (!) eine Zahl. Eine echte Zahl, echte Null, unechte Null oder durch Formatierung bedingt ein „-„ das aber eigentlich auch eine Null ist.
Sollte die VBA-Fkt. unterscheiden müssen zwischen 0 und leer, dann müsste die Summe auch 0 oder leer („“) oder blank liefern! Das könnte ich in die Fkt. einbauen.
Du willst aber auch die mathematischen Gesetze verbiegen. Die Fkt. liefert Änderungen in %:
Von einem beliebigen Wert auf 0 ist „immer“ eine Reduzierung um 100%. Das könnte ich als Standard einbauen.
Eine Steigerung von 0 auf einen beliebigen Wert wäre eine „unendliche“ Steigerung, da aber die Division durch 0 „(akt. Wert - Vorwert) / Vorwert“ mathematisch nicht zulässig ist, kann keine Steigerung ermittelt werden! Du willst hier aber „100%“ stehen haben. Ich könnte dir evtl. einen 2. Defaultwert für die „unendliche“ Steigerung einbauen. Da könntest du dann 100 eingeben.
Fazit: Eine Fkt. liefert immer einen definierten Wert und folgt einem def. Algorithmus. Den sollte man nicht verbiegen, denn sonst wird das Coding schnell zum Spaghetticode nicht mehr nachvollziehbar.
Gruß Sigi
Anzeige
AW: Das Problem mit der Null
06.01.2022 17:32:41
Daniel
Hallo Werner, hallo Sigi,
schön Euch jetzt sogar wieder gemeinsam im Boot zu wissen. Ich habe Eure beiden aktuellsten Lösungen in einer Tabelle vereint und die Ergebnisse in Spalte X kommentiert gegenübergestellt (egal ob VBA aktiviert ist oder nicht, wird das angepeilte Format klar). https://www.herber.de/bbs/user/150207.xlsm
Formel von Werner:
- sobald die erste Woche einer beliebigen Staffel (Spalte A) mit eine Missing Week (Zeile 18) beginnt, wird in der zweiten Woche (Zeile 19) #NV bzw. Div 0 als Fehler ausgegeben; dort soll aber die 0% als optisch schöne Startmessung stehen, wie sonst regulärer erster Woche mit Messdaten
VBA von Sigi
- Deine Funktionen kommen noch nicht mit einer expliziten Null-Woche klar (Zeile 13); wo -100% stehen muss, bleiben sie leer wie bei einer missing week; Hinweis: mit Werners Formeln in den Spalten T und U werden die Null-Ereignisse als Text "-" bzw. als Zahl "0,0" kodiert. Wie man das in Deinen Funktionen nutzen könnte, weiß ich aber nicht ...
Formel + VBA
- in beiden Ansätze muss das mathematische Problem der unendlichen Steigerung von Null auf Wert X durch einen Standardwert 100% (Zeile 14) umgangen werden
Dann wäre die Sache eigentlich perfekt gelöst :-)
Liebe Grüße
Daniel Jäger
AW: Formel für Änderung letzte Woche ...
06.01.2022 18:27:29
neopa
Hallo Daniel,
... natürlich kann die von Dir angestrebten 100% anstelle von unendlich ausgewiesen werden. Dazu bedarf es nur einer kleinen Formelerweiterung und das MIN() ist dafür nicht mehr nötig. Eine weitere kleine Formelerweiterung hab ich für den Fall eingebaut, wenn es eine "missing-week" nach der 1. Woche gibt. Die Erweiterungen in der Formel hab ich fett markiert
In W3:
=WENN((ANZAHL(M3:S3)=0)+(D3="");"";WENN((D3D2)+(T2="-")*(L2=1);0; WENN((ANZAHL(M3:S3)&lt7)+(T2=0);1;SUMME(M3:S3)/VERWEIS(9;1/(U2:U$30)/(D2:D$3=D3); U$3:U3)-1)))
Ist Deinerseits auch eine Formel für die Änderung zur 1. Woche gefragt?
Gruß Werner
.. , - ...
AW: Formel für Änderung letzte Woche ...
06.01.2022 18:59:09
Daniel
... stark, es funktioniert alles! Und ob ich die Formel für die Erstwochenvergleiche will; sehr gerne! Mir war bis dato garnicht so klar, dass man via Formel so komplexe Abfragen bauen kann.
LG Daniel Jäger
AW: dann ...
06.01.2022 19:59:54
neopa
Hallo Daniel,
... unter der Voraussetzung, dass die gleichen Ergebniswerte wie in Spalte Z ermittelt werden sollen, dann folgende Formel in Z3:
=WENN((T3=0)+(T3="-")+(D3="");"";WENNFEHLER(U3/INDEX(U:U;AGGREGAT(15;6;ZEILE(U$3:U3)/(D2:D$3=D3)/(U$3:U3&gt0);1))-1;0))
soll damit in Z13 auch -100% ermittelt werden dann lösche aus vorgehender Formel einfach (T3=0)+
Die Formel in Spalte W kann auch noch etwas gekürzt werden, in W3 zu:

=WENN((T3="-")+(D3="");"";WENN((D3D2)+(T2="-")*(L2=1);0;WENN((ANZAHL(M3:S3)&lt7)+(T2=0);1; U3/VERWEIS(9;1/(U2:U$30)/(D2:D$3=D3);U$3:U3)-1)))

Gruß Werner
.. , - ...
AW: dann ...
06.01.2022 21:27:20
Daniel
Guten Abend Werner,
... klasse! Die Formeln laufen ziemlich flott durch. Bei der Überprüfung der Formel für W3 auf den ganzen Datensatz mit fast 700 Zeilen, ist mir aber noch etwas aufgefallen:
1. ) Der Umgang mit fehlenden Tagen pro Woche.
In Wochen, bei denen nur einige, aber nicht alle Tage, fehlende Werte besitzen, solle die +100% nicht zugewiesen werden. Daher habe ich die Formel leicht angepasst:
=WENN((T3="-")+(D3="");"";WENN((D3D2)+(T2="-")*(L2=1);0;WENN((ANZAHL(M3:S3)=0)+(T2=0);1; U3/VERWEIS(9;1/(U2:U$30)/(D2:D$3=D3);U$3:U3)-1)))
2. Zwei oder mehr Missing Weeks ab der ersten Woche pro Staffel
Leider gibt es Patienten, die erst ab der zweiten oder dritten oder gar erst ab dem 4. Modul das Medienlogbuch führen. Ich grüble, wie man mit weiteren Verschachtelungen mit
=WENN((T3="-")+(D3="");"";WENN((D3D2)+(T2="-")*(L2=1);0;WENN((ANZAHL(M3:S3)=0)+(T2=0) ;1; U3/VERWEIS(9;1/(U2:U$30)/(D2:D$3=D3);U$3:U3)-1)))
zusätzliche Ereignisse bis zur 5. Woche abbilden könnte, damit nicht #NV bzw. Div 0 erscheint? Wenn das noch ginge, wäre dann wirklich jedes Szenario berücksichtigt.
Liebe Grüße
Daniel Jäger
AW: stelle wieder eine entsprechende Datei ein ...
07.01.2022 09:33:03
neopa
Hallo Daniel,
... an Hand dessen Du aufzeigst, wo es es neue Probleme gibt und welche Ergebnisse Du da erwartest. Dann sehen wir weiter.
Gruß Werner
.. , - ...
AW: Das Problem mit der Null
07.01.2022 09:05:10
Sigi.21
Hallo Daniel,
anbei die Mappe mit korrigiertem VBA-Code, soweit ich es verstanden habe. (s. Mappe)
Bitte testen.
Gruß Sigi
https://www.herber.de/bbs/user/150219.xlsm
AW: Das Problem mit der Null
07.01.2022 12:38:50
Daniel
Hi Sigi,
kann die Datei leider nicht öffnen. Excel sagt, die Datei sei beschädigt ...
LG Daniel Jäger
AW: Das Problem mit der Null
07.01.2022 14:02:33
Sigi.21
Hallo Daniel,
es war deine Datei, deshalb kann ich es mir nicht erklären, warum sie defekt sein sollte.
Aber es geht ja nur um den Code. Den findest du in der beiliegenden Mappe (s. Mappe).
Bei größer werdenden Tabellen wird die Performance deutlich langsamer. Wenn du die Staffeln sowieso alle untereinander stehen hast, würde ich die Bereiche für die Funktion auch jeweils nur auf diesen Bereich setzten. Das erspart unnötige Rechenzeit, denn fremde Staffeln haben keinen Einfluss auf das Ergebnis. Ferner kannst du bei DS und Ergebnissen, die sich nicht mehr ändern, die ermittelte Ergebnisse durch deren Werte ersetzten.
Die Fkt. unterscheidet jetzt zwischen 0 und leerer Zelle (bei Wochensumme bzw. Tagesdurchschnitt). Du musst halt dafür sorgen, dass die Summenformel ggf. "" (blank/leer) zurückgibt.
Gruß Sigi
https://www.herber.de/bbs/user/150233.xlsm
AW: Das Problem mit der Null
07.01.2022 15:37:34
Daniel
Hi Sigi (und auch Werner),
Danke für die Anpassung und die Mühe! Der Code läuft jetzt perfekt durch und löst sogar das Problem, was ich mit Werners Formel noch habe, wenn ein Patient in einer Staffel mit mehr als einer missing week startet. @Werner: ohne jetzt eine neue Datei hochzuladen, kann man das Szenario am bestehenden Datensatz nachvollziehen, wenn Du bspw. die Konsumwerte eines Patienten der Wochen 1 bis 3 einfach kurz entfernst.
Da ich jetzt das Luxusproblem der Wahl zwischen zwei potenten Lösungen habe, mache ich ein paar Performance Tests. Wie kann man den Formelergebnisse als Werte "ummünzen", damit sie nicht bei jeder Filter-Aktion neue Rechenlast erzeugen? Wie kann Excel dann unterscheiden, wenn man bspw. fehlende Wochen mal nachträglich einpflegt? Vielleicht kommt Euch die Frage auch naiv vor, aber ich habe mich erst in den vergangenen 4 Monaten autodidaktisch in höhere Excel Regionen vorgewagt.
Derweil beschäftigen mich bereits schon neue Fragestellungen zu den Hauptstatistiken, die nicht uneingeschränkt mit meinen Filter-Ambitionen harmonieren - aber dazu dann anderer Stelle mehr bzw. weiter.
Liebe Grüße
Daniel Jäger
P.S: Ich hoffe inständig, dass ihr mit Euren Fähigkeiten an anderer Stelle Geld verdient. Eure Unterstützung ist für meine Arbeit von unschätzbarem Wert und kommt am Ende auch bei stark betroffenen jungen Menschen an. Die Agenda für dieses Tool hat noch viele offene Punkte und soll die Durchführung und Auswertung der Therapie erleichtern, damit mehr Zeit bei den Patienten investiert werden kann. Ich freue mich jedenfalls, hier weiter mit Euch und von Euch zu lernen.
AW: Das Problem mit der Null
07.01.2022 16:24:48
Sigi.21
Hallo Daniel,
Formelergebnisse in Werte "ummünzen":
Du markierst den Bereich mit den Formeln und kopierst sie (Strg + c). Dann an gleicher Stelle - rechte Maustaste - Inhalte einfügen - Werte - ok.
Wenn du nachträglich noch Ergebnisse einpflegen willst, kannst du ja jederzeit wieder die Fkt. reinziehen und neu berechnen.
PS: Nein, ich verdiene damit kein Geld. Ich mache es "just for fun", in meiner Freizeit, freiwillig und unverbindlich (wie viele andere hier).
Gruß Sigi
AW: Das Problem mit der Null
08.01.2022 12:24:34
Daniel
Hallo Sigi,
ok, die Variante kenne ich natürlich. Ich dachte es gibt auch eine Excel Funtion oder Methode, wie eine Formel sich einmal selbst berechnet und danach zurückhält. Oder man baut einen Button, um die sichtbaren bzw. alle Zeilen via Makro bei Bedarf mit Deiner Funktion einmal durchzurechnen. So blieben ja die Werte auch einfach stehen bis man eine Aktualisierung braucht. Aber das wäre eine reine Fleißarbeit ...
Auf jeden Fall erfüllt Deine Funktion alle gewünschten Szenarien. Zur Performance Deiner Funktion: sie ist tatsächlich etwas langsam, wenn man alle Staffeln im Datensatz berechnet; die Formelvariante von Werner schneidet auf meinem i7-4Kern vergleichsweise etwas besser ab. Eine abschließende Entscheidung werde ich noch treffen.
Viele Grüße
Daniel Jäger
AW: Das Problem mit der Null
08.01.2022 14:46:25
Sigi.21
Hallo Daniel,
es gibt für jede Mappe eine Berechnungsoption (manuell od. automatisch):
MENÜ - Optionen - Formeln - Berechnungsoptionen - automatisch/manuell
Wenn du manuell wählst, kannst du über Taste F9 ( neu berechnen ) die Berechnung jederzeit veranlassen. (Vor dem Speichern wird auch berechnet.)
Gruß Sigi
AW: Das Problem mit der Null
07.01.2022 15:32:05
Oberschlumpf
Hi Daniel
ja, ich kann bestätigen, dass das Anklicken von Sigis Dateilink mit dem Wunsch, die Datei direkt in Excel zu öffnen, auch bei mir mit einem Fehler endet.
Wenn du aber die Datei zuerst auf deiner Festplatte speicherst und dann von dort, im Windows-Explorer!, mit einem Doppelklick öffnest, dass das dann nicht mit einem Fehler endet.
Auch der VBE lässt sich öffnen.
Ciao
Thorsten
AW: Das Problem mit der Null
07.01.2022 15:40:14
Daniel
Hi Thorsten,
Sigi hat mir seine Probedatei hochgeladen und habe den VBA von dort übernommen und angepasst.
Aber selbst mit Deinem Tipp klappte es auf meinem Arbeitsrechner nicht; mit meinem Privat-Laptop hingegen schon. Merkwürdig (vermutlich die Firewall ...)
Danke trotzdem :)
Liebe Grüße
Daniel Jäger
AW: Das Problem mit der Null
07.01.2022 15:43:59
Oberschlumpf
dann ist deine (Excel)Frage ja beantwortet - daher musst du nicht mehr die Option "Frage noch offen" aktivieren...
AW: auch ein späterer Auswertungsbeginn ...
08.01.2022 09:52:12
neopa
Hallo Daniel,
... lasst sich durch eine einfache Anpassung der Formel berücksichtigen.
Formel in W3 nun neu:
=WENN((T3="-")+(D3="");"";WENN(D3D2;0;WENN((T3&gt0)*(T2=0);1;WENNFEHLER(U3/VERWEIS(9;1/(U2:U$30)/(A2:A$3=A3)/(D2:D$3=D3)/(E2:E$3=E3);U$3:U3)-1;0))))
und nach unten kopieren.
Allerdings solltest Du noch einmal prüfen/klären, ob die Auswertung weiter angepasst werden muss, für den Fall dass in manchen Wochen nur für einige Wochentage Datenwerte erfasst wurden/werden. Also ob da trotzdem weiter ausschließlich die Gesamtsumme in Spalte U maßgebend ist oder z.B. ein Wert, der um das Verhältnis 7/Anzahl der erfassten Tage erhöht werden soll, oder anders? Die dazu evtl. notwendige Formelanpassung sollte auch ohne größere Probleme möglich sein.
Anschließend wäre gleiches auch noch für die Formel in Spalte X zu klären.
Gruß Werner
.. , - ...
AW: auch ein späterer Auswertungsbeginn ...
08.01.2022 10:48:38
Daniel
Guten Morgen Werner,
Deine Formel funtioniert fast perfekt. Missing Week(s) zu Beginn der Staffel werden jetzt korrekt behandelt. Aber nach Missing Weeks, die im weiteren Verauf zwischendurch auftreten (egal ob 1 oder mehrere), wird nicht mehr die Veränderung zur letzten Woche mit Messwerten ausgegeben (die Vorgängerversion Deiner Formel konnte das schon).
Ich konnte die Logik Deiner Formel soweit folgen:

=WENN((T63="-")+(D63=""); ""
als Szenario für Missing Weeks mit blank egal ob zu Beginn oder zwischendurch

;WENN(D63D62;0;
als Szenario für 0% bei erster Datenreihe

WENN((T63>0)*(T62=0);1;WENNFEHLER(U63/VERWEIS(9;1/(U$3:U620)/(A$3:A62=A63)/(D$3:D62=D63)/(E$3:E62=E63);U$3:U63)-1;0))))
als Szenario für +100% wenn Steigerung von 0 aus Vorwoche

WENNFEHLER(U63/VERWEIS(9;1/(U$3:U620)/(A$3:A62=A63)/(D$3:D62=D63)/(E$3:E62=E63);U$3:U63)-1;0))))
als Szenarien für alle Veränderungen zur jeweiligen Vorwoche, wenn keine Sonderbedingungen oder Fehler erfüllt sind. Ungefähr richtig? Mit Verweis habe ich mich bisher noch nicht beschäftigt.
Übrigens läuft Deine Formel im Vergleich zu Sigis Matrix Funktion erheblich schneller durch, obwohl im Prinzip die selben Prüfparameter abgeklapptert werden. Woran liegt das nur (4-Kern i7 mit 32 GB RAM System)
Ich habe jetzt schon ein schlechtes Gewissen, welche Lösung ich am Ende einsetze, weil Ihr beide Euch so viel Mühe gemacht habt.
Liebe Grüße heute mal aus Südthüringen
Daniel Jäger
AW: festgestellt und nachgefragt ...
08.01.2022 12:59:20
neopa
Hallo Daniel,
... als bei mir funktioniert die Ermittlung der Veränderung zur letzten Woche mit Messwerten nach Missing Weeks auch dann, wenn solche im weiteren Verlauf zwischendurch auftreten (egal ob 1 oder mehrere). Kann es evtl. sein. dass bei Dir die Formeln in Spalte T geändert sind?
Wie auch immer, stell doch bitte noch mal eine Beispieldatei, wo bei Dir etwas falsches ermittelt wird, sonst argumentieren wir möglicherweise auf verschiedener Basis. In dieser neu einzustellender Datei baue auch gleich ein paar Beispiele mit ein, wo in einer oder mehreren Woche zwar Datenwerte erfasst wurden aber nicht an allen Tagen der jeweilige Woche. Damit gleich noch meine Fragen:

Allerdings solltest Du noch einmal prüfen/klären, ob die Auswertung weiter angepasst werden muss, für den Fall dass in manchen Wochen nur für einige Wochentage Datenwerte erfasst wurden/werden. Also ob da trotzdem weiter ausschließlich die Gesamtsumme in Spalte U maßgebend ist oder z.B. ein Wert, der um das Verhältnis 7/Anzahl der erfassten Tage erhöht werden soll, oder anders? Die dazu evtl. notwendige Formelanpassung sollte auch ohne größere Probleme möglich sein.
Anschließend wäre gleiches auch noch für die Formel in Spalte X zu klären.
von heute Vormittag geklärt werden können.
Gruß Werner
.. , - ...
AW: festgestellt und nachgefragt ...
08.01.2022 16:30:07
Daniel
Hi Werner,
Du hattest Recht: für Sigis Matrix musste ich Deine Formeln aus T und U anpassen, damit diese wie gewünscht laufen. Mit den ursprünglich vorgesehenen Formeln klappt jetzt jedes Szenario wie gewünscht. Eine tolle Sache! Damit sind beide Lösungen für die Auswertungsphasen in jedem Modul einsetzbar. Die Anpassung für Spalte
Zur Frage von heute Vormittag bezüglich dem Umgang mit fehlenden Tagen bzw. lückenhaften Wochen: Für die Quantifizierung des Verhaltensmaßes ist primär die Wochenspielsumme ausschlaggebend und auch für den Patienten gut (be)greifbar. Ich gebe Dir insofern Recht, dass Missing Days das Wochenergebnis unverhältnismäßig auf- bzw. abwerten. Da in der Therapie fehlende Werte immer eine Aussagekraft besitzen (Überforderung, Blockade, Dekompensation, Komplianzmangel), wären diese im arithmetischen Mittel doch besser gewichtet, oder? Deine Formel habe ich testweise angepasst und über Spalte T auf einem Fantasie-Datensatz mit konstanter Wochensumme 24 laufen lassen. Der prozentuale Unterschied differenziert den Prozess hinter dem Verhaltensmaß deutlich besser (jemand der 24h in zwei Tagen spielt hat sich weniger im Griff als jemand der 24 an sieben Tagen spielt usw. ...). Und, die Statistik in W1 und X1 trifft nunmehr stichhaltigere Aussagen. Anbei die Testdatei: https://www.herber.de/bbs/user/150257.xlsm
Fällt Dir noch eine elegantere Lösung ein?
Wie Du/Ihr seht, funktioniert das Statistik-Band in Zeile A noch nicht ganz, aber dazu mache ich bald einen neuen Beitrag auf. Trotz Deiner Vorarbeit stagniere ich da momentan ...
Liebe Grüße
Daniel Jäger
AW: ist wohl eher eine Frage an Statistiker ...
08.01.2022 19:27:39
neopa
Hallo Daniel,
... wenn man es differenzierter auswerten muss oder möchte. Denkbar wäre, dass die Anzahl der Datenwerte in der Woche (6 Werte sind anders zu wichten als nur 1 oder 2 Werte) und oder ein Trend der Daten (ab- bzw. zunehmende Wertgrößen) mit einfließen. Eine solche zusätzliche Berücksichtigung von Einflußgrößen könnte aber auch für Deine Zielstellung überzogener werden. Jedenfalls bin ich dazu nicht der richtige Ansprechpartner. Ich kann behilflich sein, wenn Du (D)eine Entscheidung vorgibst und sich daraus eine Formeländerung erforderlich machen sollte.
Die anzupassenden Formeln in Zeile 1 sollten und können auf jeden Fall Bezug nehmen auf die nun vorhandene "intelligente" Tabelle nehmen und INDIREKT() müsste entfallen, wenn Bezug genommen wird auf die Hilfsspalte hc in Spalte A:A.
Gruß Werner
.. , - ...

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige