Supportnet Computer
Planet of Tech

Supportnet / Forum / Tabellenkalkulation

Addition von bestimmten Werten aus einer Zelle einer Excel-Tabelle





Frage

Hallo, ich habe folgendes Problem: Ich möchte Werte in eine Excel-Tabelle eingeben, die folgendes Format haben (in einer einzigen Zelle): z.B. in Zelle [B4]: EK-1=4,2; EK-2=3; EK-3=2,8; z.B. in Zelle [B5]: EK-1=4; EK-2=1,2; EK-3=8,3; EK-4=1; Darunter sind Zellen zur statistischen Auswertung. Diese sollen angeben wieviele Einheiten der jeweiligen EKs benötigt werden. Also z.B. [A26]: EK-1 [B26]: 8,2 [A27]: EK-2 [B27]: 4,2 [A28]: EK-3 [B28]: 11,1 [A29]: EK-4 [B29]: 1 (Werte in [ ] sind Zellenangaben) Ich hoffe ich konnte verständlich machen worin mein Problem liegt. Vielen Dank vorab für eure Hilfe. Gruss, braincracker

Antwort 1 von rainberg

Hallo brainchracker,

ich muss schon sagen, dein Name passt zu deiner Frage :-)))))

Nun die Lösung:
Kopiere folgende Formel in Zelle B26 und kopiere sie anschließend bis B29 runter.

=WENN(ISTFEHLER(TEIL(B$4;FINDEN(A26;B$4)+5;FINDEN(";";B$4;FINDEN(A26;B$4))-(FINDEN(A26;B$4)+5)));0;TEIL(B$4;FINDEN(A26;B$4)+5;FINDEN(";";B$4;FINDEN(A26;B$4))-(FINDEN(A26;B$4)+5)))+WENN(ISTFEHLER(TEIL(B$5;FINDEN(A26;B$5)+5;FINDEN(";";B$5;FINDEN(A26;B$5))-(FINDEN(A26;B$5)+5)));0;TEIL(B$5;FINDEN(A26;B$5)+5;FINDEN(";";B$5;FINDEN(A26;B$5))-(FINDEN(A26;B$5)+5)))

Wenn du keinen Kopierfehler gemacht hast und die Schreibweise der Ausgangsdaten deinem Muster entsprechen, erhältst du das richtige Ergebnis.

Gruß
Rainer

Antwort 2 von Saarbauer

Hallo,

leider nicht ganz verständlich.

Vielleicht , wenn möglich, was ermittelt/angezeigt werden soll.

Eine Eingabe
Zitat:
z.B. in Zelle [B4]: EK-1=4,2; EK-2=3; EK-3=2,8;

ist so möglich, aber nur als Text. Ich nehme aber an, dass hier vermutlich eine Wenn()-funktion zum Einsatz kommen soll.

Zitat:
[A26]: EK-1 [B26]: 8,2

Woher kommen die 8,2 ? Zusammenhang nicht herstellbar

Wie wäre es mit einem besseren Beispiel

Gruß

Helmut

Antwort 3 von braincracker

Hallo,

vielen Dank für Ihre Antworten.

Diese Sache ist die, dass in den Zeilen Produktlinien Stehen und in den Spalten der Eingabetabelle über 3 Jahre hinweg die Monate. Wobei jeweils eine Zeile einer Produktlinie entspricht und eine Spalte einem Monat.

In den durch Produktlinie und Monat bestimmten Zelle sollen nun die Anzahlen der benötigten Arbeitskräfte stehen, wobei beispielsweise aus dem Bereich EK-1 4 volle Arbeitskräfte benötigt werden und eine, die zu 20% an der Aufgabe teilnimmt, z.B. eine Führungskraft. Daher ergibt sich dann der Wert 4,2.
In die gleiche Zelle muss nun auch die Menge des Arbeitseinsatzes aus EK-2 und EK-3 und EK-4 usw. eingetragen werden.
In der darunterliegenden Zelle stehen die Einheiten der Arbeit der nächsten Produktlinie, z.B. eben EK-1=4.

Einige Zeilen weiter unten, z.B. in Zeile 26 sollen die oben eingegebenen Daten ausgewertet werden.
In meinem Beispiel ergibt sich für den Bereich EK-1 im Monat X ein Gesamtarbeitseinsatz von 8,2 (nämlich 4,2 und 4 Einheiten summiert aus zwei Produktlinien).

Vielen Dank für deine Lösung rainberg, allerdings funktioniert diese nur, wie du richtig gesagt hast, für mein kleines Erklärungsbeispiel. Die tatsächliche Tabelle enthält aber weitaus mehr Produktlinien und Monate als im Beispiel, ebenso können welche hinzukommen oder gelöscht werden.

Ich habe überlegt, ob eine Lösung evtl. durch eine Funktion gebracht werden kann.
Dieser Funktion übergebe ich den zu bearbeitenden Bereich und die jeweilige Produktlinie, z.B. EK-2, und sie sucht den String nach dem "=" der Produktlinie bis zum ";", wandelt diesen in eine Zahl um und addiert diese Werte aus dem vorgegebenen Bereich.

Danke und viele Grüße,
braincracker

Antwort 4 von rainberg

Hallo brainchracker,

so wie du dein Problem beschreibst, musst du wahrscheinlich eine andere Tabellenstruktur wählen.

Leider kann ich dir dazu keine Ferndiagnose stellen.
Falls es dir möglich ist, die Datei aus der Hand zu geben, würde ich mal sehen, was man da machen kann, ansonsten kann ich dir nicht weiter helfen.

Gruß
Rainer

Antwort 5 von braincracker

Naja eine andere Struktur ist, glaube ich, kaum möglich.

Kann man die Lösung mit einer selbst erstellten Funktion nicht weiterverfolgen?
Ich bin mir ziemlich sicher, dass das funktionieren könnte, nur bin ich leider auf noch keine zufriedenstellende Lösung gekommen.

Gruss,
braincracker

Antwort 6 von braincracker

So ich bin es nochmal.

So wirklich weiter bin ich bei meinem Problem nicht gekommen zumal ich zugeben muss, dass meine Erfahrung mit VBA nicht gerade berauschend sind.
Hat wirklich niemand eine Idee wie ich dieses Problem mittels einer Funktion lösen kann?

Beispiel: Aufruf: =WERTERMITTLUNG(B4:B5;EK-1)

Function WERTERMITTLUNG(Bereich As Range, Abteilung)
...
...
[Funktion, welche die Werte ermittelt und addiert]
...
...
End Function

Danke und Grüße,
braincracker

Antwort 7 von Saarbauer

Hallo,

sowas ist grundsätzlich möglich, aber wie @rainberg schon schreibt
Zitat:
Falls es dir möglich ist, die Datei aus der Hand zu geben, würde ich mal sehen, was man da machen kann, ansonsten kann ich dir nicht weiter helfen.

trifft auch für andere zu

Gruß

Helmut

Antwort 8 von braincracker

Ich habe derzeit keinen Webspace zur Verfügung, könnte die Datei folglich nur per Email versenden.

Gibt´s da Vorschläge?

Gruss,
braincracker

Antwort 9 von Saarbauer

Hallo,

teile deine Email mal mit werde mich zurückmelden

Gruß

Helmut

Antwort 10 von rainberg

Halo brainchracker,

kannst sie mir auch mal schicken.

rainberg@freenet.de

Gruß
Rainer

Antwort 11 von Saarbauer

Hallo @braincracker,

kannst dir jetz überlegen wie du es machen willst. Glaube aber, da ich die Lösungen von @rainberg auch kenne, dass seine Lösung meiner wahrscheinlich nicht nachsteht. Habe von ihm auch schon manches genutzt und in einem anderen Fall haben wir das Problem gemeinsam gemeistert.

Gruß

Helmut

Antwort 12 von braincracker

Hallo nochmal.

Vielen Dank für die Hilfe.

Bitte gehe doch einfach auf die eBay-Seite und schreibe mir eine Nachricht mit deiner Emailadresse, dann schicke ich dir die Datei zu und es stehen keine Mails in öffentlichen Foren.

Gruss,
braincracker

Antwort 13 von rainberg

Hallo brainchracker,

da du mir keine Datei geschickt hast, habe ich ein fach mal eine Funktion entsprechend meiner, eher geringen VBA-Kenntnisse, und deines Ausgabgsbeispieles zusammen genagelt.

Bei mir hat sie funktioniert mit Dummy-Daten im Bereich B4:B25.

Der Funktionsaufruf lautet in diesem Falle

=wertermittlung(B$4:B$25;A26)

Ab A26 abwärts stehen die Abteilungen. Die Funktion lässt sich wie jede Formel kopieren.


Public Function WERTERMITTLUNG(Bereich As Range, Abteilung As String)
    Dim c As Range, wert As String, zahl As Double
    For Each c In Bereich
        On Error Resume Next
        wert = (WorksheetFunction.Substitute( _
        WorksheetFunction.Substitute(WorksheetFunction.Substitute( _
        Mid(c.Value, WorksheetFunction.Find( _
        Abteilung, c.Value), 8), Abteilung, ""), "=", ""), ";", ""))
        zahl = zahl + wert
    Next
    WERTERMITTLUNG = zahl
End Function


Gruß
Rainer

Antwort 14 von rainberg

Hallo brainchracker,

habe nun doch deine Datei erhalten und muss sagen, dass diese entschieden anders aussieht, als ich mir sie vorgestellt habe.

Da funktioniert natürlich mein Code nicht, werde es mir aber ersparen ihn zu ändern, da das Problem einfacher mit einer Matrixformel zu lösen ist.

Schreibe in D35 folgende Formel und schließ die Formeleingabe mit der Tastenkombination Strg+Shift+Enter ab.


=SUMME((LÄNGE(D$4:D$33)-LÄNGE(WECHSELN(D$4:D$33;$B35;"")))/LÄNGE($B35))


Anschließend kannst du diese Formel beliebig vertikal sowie horizontal kopieren.

Falls nötig schicke ich dir deine geänderte Datei wieder zurück.

Gruß
Rainer

Antwort 15 von braincracker

Hallo Rainer,

erst einmal recht herzlichen Dank für deine Bemühungen.

Leider habe ich die oben benannte Funktion noch nicht wirklich zum funktionieren bewegen können.
Bitte sende mir doch die geänderte Excel-Tabelle einmal zu.

Vielen Dank.

Gruss,
braincracker

Antwort 16 von braincracker

Wenn ich das soweit richtig verstanden habe bezieht sich diese Matrixformel aber auf den Bereich der Zeile "KI" in meiner Beispieldatei.

Sie liefert diesselben Ergebnisse wie die von mir angewandte Funktion ZÄHLENWENN( )

Angedacht war an sich, dass er die Werte, welche man in die Zeilen 4 bis 28 einträgt, addiert. Wie diese genau zu trennen sind, ist nicht festgelegt. Ich möchte lediglich, dass ich mehrere, nicht zueinander gehörige Werte in eine Zelle eintragen kann, welche dann durch eine Funktion ausgewertet werden können.

Grüsse,
braincracker

Antwort 17 von rainberg

Hallo braincracker,

leider zu spät, da von dir keinerlei Reaktion kam, habe ich gestern deine Datei gelöscht.

Du hast es richtig erkannt dass die Formel die gleichen Ergebnisse liefert wie dein ZÄHLENWENN, nur dass sich meine Formel in beide Richtungen kopieren lässt.

Zu dem was du da noch "angedacht" hast, zerbreche ich mir keinen Kopf mehr, da das wie schon gesagt in meinen Augen ein falscher Tabellenaufbau ist.

Es ist nun mal so eine Tabelle sollte so aufgebaut sein, dass man alle gewünschten Auswertungen mit einem mehr oder weniger vertretberem Aufwand bewerkstelligen kann.

Vielleicht findest du noch jemanden der das anders sieht.

Gruß
Rainer

Antwort 18 von braincracker

Hallo Rainer,

bitte entschuldige, dass ich mich nicht gemeldet habe, aber ich hatte in den letzten Tagen keine Möglichkeit auf diese Datei und das Internet zuzugreifen.
Ich habe die Funktion, die du mir vorgeschlagen hast nun verwendet und es funktioniert alles dahingehend, dass ich nun in eine Zelle schreiben kann: EK-1, EK-1, EK-1, EK-2, EK-2, EK-3
Die Auswertung gibt dann für EK-1 "3" an und für EK-2 "2" sowie für EK-3 "1".
An sich ist diese Funktionalität ausreichen, nur kann ich eben nur ganze Arbeitseinsätze damit zählen und keine 0,2 oder ähnliche.

Du schreibst, dass eine Tabelle so aufgebaut sein sollte, dass man mit möglichst wenig Aufwand das gewünschte Ergebnis erreichen kann.
Nun ich bin bei dem Layout der Tabelle nicht sonderlich festgelegt. Nur weiss ich nicht, wie ich diese Tabelle einfacher strukturieren soll, da sie sonst sehr schnell sehr unübersichtlich wird.

Eine Funktion, die ähnlicher deiner, den Zelleninhalt nach einem vorgegeben String absucht, dann aber den Wert zwischen dem nachfolgenden "=" und einem";"ausliest und diese Werte auf einem vorgegeben Bereich addiert, schien für mich eine geeignete und vergleichsweise einfache Lösung.

Ich kann dir die aktuelle Tabelle gern noch einmal zusenden. Ich würde mich freuen, wenn ich doch noch zu einem Ergebnis kommen könnte, bei dem ich reelle Arbeitseinsätze kalkulieren kann.

Viele Grüsse und vielen Dank für alles,
braincracker

Antwort 19 von Saarbauer

Hallo @braincracker,

du hattest mir ja die Tabelle zugesandt und ich habe ähnliche Probleme wie @rainberg, da deine Dateneingaben sehr unkonventionell ist. Es ist in die Daten sehr schwer ein Prinzip rein zu kriegen ist. Ich möchte für @rainberg und mich behaupten, dass wir ganz gute Kenntnisse in Excel haben, aber hier bin ich überfordert und wie ich aus AW 17 entnehme @rainberg auch.

Es ist für ihn wahrscheinlich, wie für mich dieser Tabellenaufbau auch nur schwer nachvollziebar, da wir aus der Tabelle natürlich keine Zusammenhänge herstellen können.

Ein Verbesserungsvorschlag für die Tabelle ist nur schwer machbar, da wir, wie bereits geschrieben keine Zusammenhänge mit anderen Unterlagen kennen zur Tabelle kennen. Grundsätzlich ist die Lösung mehrere Daten in einer Zelle unterzubringen ungewöhnlich und für die weitere Verarbeiung der Daten ungünstig.

Gruß

Helmut

Antwort 20 von rainberg

Hallo braincracker,

hier mein letzter !!!! Versuch.

Option Explicit

Public Function WERTERMITTLUNG(Bereich As Range, Abteilung As String)
Dim c As Range, wert As String, zahl As Double
On Error Resume Next
For Each c In Bereich
If c <> "" Then
If Mid(c, WorksheetFunction.Find(Abteilung, c) + 5, 3) = True Then
wert = Mid(c, WorksheetFunction.Find(Abteilung, c) + 5, 3)
zahl = zahl + Val(Str(wert))
End If
End If
Next
WERTERMITTLUNG = zahl
End Function


Funktionsaufruf für C35:
=wertermittlung($C$4:$C$33;B35)

Bedingungen:
Für die EK´s gelten folgende Schreibweisen
EK-1=0,0; EK-2=2,0;EK-3=2,7;EK-4=0,9 usw.
also immer 3-stellige Dezimalwerte auch wenn eigentlich 0 infrage käme.

Die auszuwertenden Abteilungen stehen ab B35 abwärts.

in meiner Dummy-Datei funktioniert es so.

Gruß
Rainer

Antwort 21 von rainberg

Hallo braincracker,

habe den Code noch etwas übersichtlicher gemacht, indem ich die Tabellenfunktion FINDEN() durch die VBA-Funktion InStr() ersetzt habe.


Option Explicit

Public Function WERTERMITTLUNG(Bereich As Range, Abteilung As String)
    Dim c As Range, wert As String, zahl As Double
    On Error Resume Next
    For Each c In Bereich
    If c <> "" Then
        If Mid(c, InStr(1, c, Abteilung) + 5, 3) = True Then
            wert = Mid(c, InStr(1, c, Abteilung) + 5, 3)
            zahl = zahl + Val(Str(wert))
        End If
        End If
    Next
    WERTERMITTLUNG = zahl
End Function


Gruß
Rainer

Antwort 22 von braincracker

Hallo Rainer,

nochmals vielen Dank für deine Mühe.

Diese Funktion klappt. Super! Das war genau das was ich mir vorgestellt habe. Musste nur leichte Modifikationen vornehmen aber es funktioniert jetzt absolut problemlos.

Danke !!!

Vielen Dank auch an Helmut.

Gruss,
braincracker