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

MITTELWERTWENNS...

MITTELWERTWENNS...
26.03.2019 17:58:50
cH_rI_sI
Hallo liebe Excelfreunde,
ich hätte wieder einmal Euren Rat gebraucht - ich habe einen Projektplan, wo der Projektstatus des jeweiligen Tasks in Prozent angegeben wird - natürlich gibt es auch Hauptpunkte bei welchen am besten der Mittelwert der Unterpunkte automatisch berechnet wird. Das funktioniert aber nur bedingt...
Derzeit habe ich es so gelöst:

=MITTELWERTWENNS($L$22:$L$1007;$A$22:$A$1007;$A33&"."&"*")
In Spalte L steht der Status in Prozent, in Spalte A ist die Nummerierung, welche aber mehrere Ebenen haben kann.
Ich möchte natürlich, dass der Mittelwert nur für die nächste Ebene und nicht tiefer berechnet wird - wenn es darunter weitere Ebenen gibt, dann wiederum nur für die nächste Ebene usw.
Das Ergebnis sollte bei u.a. Beispiel so aussehen:
Userbild
Kann man das überhaupt mit Formeln lösen?
Wäre Euch sehr dankbar, wenn Ihr mir hier helfen könnt.
Schönen Abend noch!
Lg,
Chrisi

25
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: möglich ist vieles ...
26.03.2019 18:07:44
neopa
Hallo Chrisi,
... doch lade bitte eine Beispielarbeitsmappe hoch.
Gruß Werner
.. , - ...
AW: da wäre zunächst zu klären ...
26.03.2019 20:11:51
neopa
Hallo Chrisi,
... wo Du welche (Prozent-)Werte fix eingibst. Normalerweise in den der jeweils untersten Ebene.
In Deiner Beispieldatei sieht das jedoch anders aus.
Jedenfalls in C2: gehört dann: =WENNFEHLER(MITTELWERTWENNS(C3:C1000;A3:A1000;A2&"."&"?");0) und diese Formel kannst Du nach C4 kopieren.
Gruß Werner
.. , - ...
Anzeige
AW: da wäre zunächst zu klären ...
27.03.2019 06:28:13
cH_rI_sI
Guten Morgen Werner,
funktioniert wie erwartet - bist echt ein Genie - vielen Dank und schönen Tag!!!
AW: dito owT
27.03.2019 09:27:00
neopa
Gruß Werner
.. , - ...
AW: dito owT
27.03.2019 11:08:12
cH_rI_sI
Hallo Werner und auch alle anderen die helfen möchten,
ich habe leider nun doch noch ein Problem festgestellt - ich habe ein Beispiel mit mehreren Ebenen - es beginnt bei 2.3 und geht bis 2.3.31 - dazwischen gibt es hin und wieder noch tiefere Ebenen wie 2.3.7.1 usw.
Ich habe die Formel überall eingefügt und es kommt bei 2.3 4% raus - ist aber auf dieser Ebene nur 1%.
Anbei die Datei: https://www.herber.de/bbs/user/128690.xlsx
Vielen Dank schonmal für die erneute Unterstützung!
Lg,
Chrisi
Anzeige
AW: nachgefragt ...
27.03.2019 11:24:19
neopa
Hallo Chrisi,
... die Ursache für den falschen Mittelwert ist darin begründet, dass ich gemäß Deines bisherigen Beispiels davon ausgegangen war, dass max. jeweils nur 9 Unterebenen je Ebene vorliegen. Nun sind es aber in der 3. Ebene über 10. Können es denn in einer Ebenenstufe auch noch mehr als 99 werden?
Wenn stets nur max 99 Unterebenen je Stufe möglich sind, wäre die nächste Frage, ob Deine Gliederung generell stets zwei Stellen je Unterebene vorsehen könnte? Also anstelle 2.3.1 so: 2.03.01?
Gruß Werner
.. , - ...
AW: nachgefragt ...
27.03.2019 12:06:59
cH_rI_sI
Hallo Werner,
das ging aber schnell... Es ist leider so, dass die Stufen immer so geschrieben werden: 2.3.1 - anders ist das leider nicht möglich, da ich den Code, welcher die Nummerierung macht nicht verstehe:
Sub Nummerierung_erweitern()
Dim a(), i&, k&, maxZ&, ll&, aus$()
Const maxArr = 4
'maxZ = (Range("B" & Rows.Count).End(xlUp).Row)
maxZ = Cells(Rows.Count, Range("Activities").Column).End(xlUp).Row
ReDim a(1 To maxZ, maxArr)
ReDim aus(2 To maxZ, 0)
For k = 0 To maxArr: For i = 1 To maxZ: a(i, k) = 0: Next: Next
ll = 1
For i = 2 To maxZ
'a(i, 0) = Range("B" & i + 20).IndentLevel + 1
a(i, 0) = Cells(i + 20, Range("Activities").Column).IndentLevel + 1
If a(i, 0) > maxArr Then _
MsgBox "Indent level > 3": a(i, 0) = maxArr
If a(i, 0) = ll Then
For k = 1 To maxArr: a(i, k) = a(i - 1, k): Next
a(i, ll) = a(i - 1, ll) + 1
Else
If a(i, 0)  ll Then
For k = 1 To a(i, 0) - 1: a(i, k) = a(i - 1, k): Next
a(i, a(i, 0)) = a(i - 1, a(i, 0)) + 1
ll = a(i, 0)
End If
End If
End If
For k = 1 To ll: aus(i, 0) = aus(i, 0) & a(i, k) & ".": Next
aus(i, 0) = Left(aus(i, 0), Len(aus(i, 0)) - 1)
Next
'Range("A22:A" & maxZ) = aus
Range(Range("StartID"), Cells(maxZ, Range("SpalteID").Column)) = aus
End Sub
Weiters gehe ich davon aus, dass es je Ebene max. 99 Stufen gibt.
Anzeige
AW: nachgefragt ...
27.03.2019 12:07:00
cH_rI_sI
Hallo Werner,
das ging aber schnell... Es ist leider so, dass die Stufen immer so geschrieben werden: 2.3.1 - anders ist das leider nicht möglich, da ich den Code, welcher die Nummerierung macht nicht verstehe:
Sub Nummerierung_erweitern()
Dim a(), i&, k&, maxZ&, ll&, aus$()
Const maxArr = 4
'maxZ = (Range("B" & Rows.Count).End(xlUp).Row)
maxZ = Cells(Rows.Count, Range("Activities").Column).End(xlUp).Row
ReDim a(1 To maxZ, maxArr)
ReDim aus(2 To maxZ, 0)
For k = 0 To maxArr: For i = 1 To maxZ: a(i, k) = 0: Next: Next
ll = 1
For i = 2 To maxZ
'a(i, 0) = Range("B" & i + 20).IndentLevel + 1
a(i, 0) = Cells(i + 20, Range("Activities").Column).IndentLevel + 1
If a(i, 0) > maxArr Then _
MsgBox "Indent level > 3": a(i, 0) = maxArr
If a(i, 0) = ll Then
For k = 1 To maxArr: a(i, k) = a(i - 1, k): Next
a(i, ll) = a(i - 1, ll) + 1
Else
If a(i, 0)  ll Then
For k = 1 To a(i, 0) - 1: a(i, k) = a(i - 1, k): Next
a(i, a(i, 0)) = a(i - 1, a(i, 0)) + 1
ll = a(i, 0)
End If
End If
End If
For k = 1 To ll: aus(i, 0) = aus(i, 0) & a(i, k) & ".": Next
aus(i, 0) = Left(aus(i, 0), Len(aus(i, 0)) - 1)
Next
'Range("A22:A" & maxZ) = aus
Range(Range("StartID"), Cells(maxZ, Range("SpalteID").Column)) = aus
End Sub
Weiters gehe ich davon aus, dass es je Ebene max. 99 Stufen gibt.
Anzeige
AW: weiter nachgefragt ...
27.03.2019 13:09:40
neopa
Hallo Chrisi,
... wie viele Datensätze sind denn wirklich max. ca. auszuwerten?
Da ich mich grundsätzlich nicht mit VBA beschäftige, kann ich Dir dazu auch nicht raten.
Deshalb würde ich weiter nach einer Formellösung suchen. Doch eine solche könnte bei einer sehr großen auszuwertenden Datenmenge nicht effektiv sein.
Gruß Werner
.. , - ...
AW: weiter nachgefragt ...
27.03.2019 13:49:06
cH_rI_sI
Ich würde sagen, dass max. dürfte 50 sein... Danke, dass Du Dir dazu Gedanken machst!
AW: weiter nachgefragt ...
27.03.2019 13:49:45
cH_rI_sI
je ebene meine ich
AW: eine Möglichkeit ...
27.03.2019 14:14:58
neopa
Hallo Chrisi,
... ohne VBA wäre mit Hilfsspalten (die Du ausblenden kannst). Wobei ich noch dazu raten würde, die Statusvorgabewerte in einer separaten Spalte zu erfassen (oder dort mit Bezug auf andere Daten zu ermitteln). Nachfolgend hab ich das in Spalte E vorgenommen.
Dann kannst Du nachstehende Formel C2 einfach durch Doppelklick nach unten kopieren, wenn Du die Hilfspaltenformeln schon ziehend nach unten kopiert hast.
Nachfolgend für bis zu 5 Ebenenstufen. Formeln einfach nach unten ziehend kopieren.
Arbeitsblatt mit dem Namen 'Tabelle1'
 ACDEFGHIJKLM
1IDSTATUS (%) Vorgaben        
22.31%   23    02.03
32.3.10%   231   02.03.01
42.3.220% 20% 232   02.03.02
52.3.30%   233   02.03.03
62.3.3.10% 0% 2331  02.03.03.01
72.3.415%   234   02.03.04
82.3.4.115% 15% 2341  02.03.04.01
92.3.50%   235   02.03.05
102.3.5.10% 0% 2351  02.03.05.01
112.3.5.20%   2352  02.03.05.02
122.3.60%   236   02.03.06
132.3.70%   237   02.03.07
142.3.7.10%   2371  02.03.07.01
152.3.80%   238   02.03.08
162.3.90%   239   02.03.09
172.3.9.10%   2391  02.03.09.01
182.3.100%   2310   02.03.10

ZelleFormel
C2=WENN(E2>"";E2;WENNFEHLER(MITTELWERTWENNS(C3:$C$1000;M3:M$1000;M2&"."&"?");0))
G2=WENN(A2="";"";LINKS(A2;FINDEN(".";A2&".")-1)+0)
H2=WENNFEHLER(TEIL(A2;LÄNGE(G2)+2;FINDEN("!";WECHSELN(A2&".";".";"!";2))-LÄNGE(G2)-2)+0;"")
I2=WENNFEHLER(TEIL(A2;LÄNGE(G2&H2)+3;FINDEN("!";WECHSELN(A2&".";".";"!";3))-LÄNGE(G2&H2)-3)+0;"")
J2=WENNFEHLER(TEIL(A2;LÄNGE(G2&H2&I2)+4;FINDEN("!";WECHSELN(A2&".";".";"!";4))-LÄNGE(G2&H2&I2)-4)+0;"")
K2=WENNFEHLER(TEIL(A2;LÄNGE(G2&H2&I2&J2)+5;FINDEN("!";WECHSELN(A2&".";".";"!";5))-LÄNGE(G2&H2&I2&J2)-5)+0;"")
M2=WENN(G2="";"";TEXT(G2;"00")&WENN(H2="";"";"."))&WENN(H2="";"";TEXT(H2;"00")&WENN(I2="";"";"."))&WENN(I2="";"";TEXT(I2;"00")&WENN(J2="";"";"."))&WENN(J2="";"";TEXT(J2;"00")&WENN(K2="";"";"."))&WENN(K2="";"";TEXT(K2;"00")&WENN(L2="";"";"."))
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
.. , - ...
Anzeige
AW: eine Möglichkeit ...
27.03.2019 16:28:54
cH_rI_sI
Hallo Werner,
Kompliment für deine Formeln - ich habe es jetzt noch nicht probiert, da ich erst schauen muss, ob wir in den Projektplan die Hilfsspalten einbauen können.
Warum funktionierte die einfache Formel, aber nur bis 9 Positionen je Ebene und das aber in allen Ebenen? Das habe ich noch nicht verstanden...
Vielleicht kannst mir das näher erklären - Danke!
Lg, und schönen Abend!
AW: zu Deiner Frage ... und ...
27.03.2019 16:45:51
neopa
Hallo Chrisi,
... mit dem Formelteil meiner einfachen Formel in meinem ersten Beitrag ... A2&"."&"?")... prüfe ich nur ein beliebiges Zeichen nach dem Punkt ab, dadurch werden die Ebenen mit einer tieferen Struktur als 9 nicht mehr berücksichtigt. Würde ich in dieser Formel mit ... A2&"."&"?")... zwei beliebige Zeichen abprüfen, würden dagegen die Ebenen mit nur einem Zeichen nicht richtig berücksichtigt.
Deshalb der Umweg.
Nach welchen Kriterien wird denn Deine ID-Struktur gebildet? Wenn ich diese kenne, kann ich möglicherweise diese auch mit einer Formel generieren und es würde der Umweg über die Hilfsspalten entfallen. Jedoch zur Spalte E würde ich Dir auch weiterhin raten.
Gruß Werner
.. , - ...
Anzeige
AW: zu Deiner Frage ... und ...
27.03.2019 17:06:49
cH_rI_sI
Hallo Werner,
die ID-Struktur wird anhand dem Indent-Level gebildet - also wie weit die Beschreibung nach rechts eingerückt wurde (die Einrückung ist bei der Beispieldatei nicht vorhanden, da manuell erstellt).
Also wenn Du das schaffst, bin ich sprachlos...
Lg
AW: zu Deiner Frage ... und ...
27.03.2019 17:09:55
cH_rI_sI
Möglicherweise hilft das, da man hiermit den Indent-Level via Formel abfragen kann:
https://professor-excel.com/how-to-return-the-indentation-of-a-cell-in-excel/
AW: das kann man nur mit VBA lösen owT
27.03.2019 17:31:25
neopa
Gruß Werner
.. , - ...
AW: das kann man nur mit VBA lösen owT
27.03.2019 17:40:47
cH_rI_sI
Hallo Werner,
das stimmt schon, ganz ohne VBA gehts nicht - man könnte aber mit z.B.

=PROFEXIndentLevel(A3) 
das Indent-Level einer Zelle abfragen und dies in den Formeln verwenden...
Ich vermute mal, Du möchtest alles ganz ohne VBA und nur mit Formeln lösen können, ansonsten kein Support wenn VBA im Spiel ist - richtig?
Trotzdem Danke für deine Mühe!
Lg,
Chrisi
Anzeige
AW: im Prinzip schon ...
27.03.2019 18:08:24
neopa
Hallo Chrisi,
... weil ich wie ich bereits schrieb, mich in Foren grundsätzlich nur mit Lösungen ohne VBA beschäftige.
Excel hat in jedem Tabellenblatt genügend Spalten zur Verfügung, so dass Hilfsspalten kein Problem ist. Diese können ja irgendwo angelegt sein und wenn es z.B. in den Spalten EEE:EEH ist.
Allerdings könnte ich bei Bedarf auch über ein Ersatz der Formel-Hilfsspalten mittels benannter Formel(n) nachdenken.
Gruß Werner
.. , - ...
AW: im Prinzip schon ...
28.03.2019 08:36:30
cH_rI_sI
Guten Morgen Werner,
ich habe es jetzt ganz ohne VBA-Änderungen und nur mit Formeln hinbekommen:
=WENNFEHLER(SUMME(SUMMEWENN(A47:$A$1011;$A46&"."&"?";L47:$L$1021);WENNFEHLER(SUMMEWENN(A47:$A$1011; $A46&"."&"?";L47:$L$1021);0))/SUMME(ZÄHLENWENNS(A47:$A$1011;$A46&"."&"?"); WENNFEHLER(ZÄHLENWENN(A47:$A$1011;$A46&"."&"?");0));0) 
Bis jetzt stimmts, solange ich je Ebene 2-stellig bleibe - dies könnte ich jedoch auch einfach erweitern...
Was sagst? ;-)
Lg,
Chrisi
Anzeige
AW: ein kurzer Check Deiner Formel ...
28.03.2019 13:09:12
neopa
Hallo Chrisi,
... den ich vorhin mit en mir von Dir vorliegenden Beispieldatenwerten vorgenommen hatte, ergab aber fehlerhafte Ergebniswerte. Die Datenstruktur hatte ich vor meinem Check natürlich an die Deiner eingestellten Formel angepasst.
Gruß Werner
.. , - ...
AW: ein kurzer Check Deiner Formel ...
31.03.2019 14:02:26
cH_rI_sI
Hallo Werner,
ich kann dein fehlerhaftes Ergebnis nicht ganz nachvollziehen, weil bei mir stimmts eigentlich:
https://www.herber.de/bbs/user/128779.xlsx
Was stimmt deiner Meinung nach nicht? Kann sein, dass ich mich doch irre, deshalb bitte um Rückmeldung - Danke!
Lg,
Chrisi
Anzeige
AW: Formel ergibt fehlerfreie Ergebniswerte ...
31.03.2019 16:35:16
neopa
Hallo Chrisi,
... sorry, leider hatte ich in meiner überschlägigen Check am Freitag offensichtlich meinerseits einen Fehler eingebaut. So kam ich da zu meiner fehlerhaften Einschätzung, weil ich außerdem mir nicht Deine Formeldefinition näher angesehen hatte. Man könnte in Deiner Formel zwar noch ein paar überflüssige $-Zeichen löschen, sie ist so aber die einfachste Art Dein Problem zu lösen. Gratuliere.
Gruß Werner
.. , - ...
AW: Formel ergibt fehlerfreie Ergebniswerte ...
31.03.2019 18:37:44
cH_rI_sI
Vielen Dank nochmal für deine Mühe, die Du Dir gemacht hast! Wünsche Dir einen schönen Abend!
Lg

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige