Summe von Zellen die "+" und somit 2 Werte enthalten können

Moderator: ModerationP

Summe von Zellen die "+" und somit 2 Werte enthalten können

Beitragvon Loxxx » 23. Sep 2018, 09:45

Hallo zusammen,
ich bekomme es einfach nicht gebacken, folgendes Problem:
Ich habe eine Reihe, z.B. C10:AH10. In dieser Reihe stehen in den Zellen entweder Buchstaben oder Zahlen(jede Zahl steht für eine best. Tour, Buchstaben für z.B. Frei oder Urlaub etc.). Ich brauche jetzt die Summe der Zellen, die eine Zahl enthalten. Ich addiere also quasi die Touren-Nummern. Da die Touren-Nummern und die Anzahl der Touren immer gleich sind, erhalte ich hier immer denselben Wert, ich prüfe so, ob jeden Tag jede Tour belegt ist.
Soweit so gut, bis hierher kein Problem.
Jetzt können aber Zellen auch 2 Touren enthalten, da an Montagen 2 Touren von einem Mitarbeiter gefahren werden, das wird dann z.B. folgendermaßen ausgefüllt: 11+12. Steht jetzt also in C10 "9+10" und in D10 "1+3", bräuchte ich die Summe aller vier Zahlen, also hier 23. Steht dann in E10 nur die Zahl 5, sollte die Summe 28 rauskommen. Aufteilen auf mehrere Spalten kommt leider nicht in Frage, für jeden Mitarbeiter habe ich genau eine Spalte zur Verfügung. Eine Hilfsspalte wäre allerdings möglich.
Jemand eine Idee?
Loxxx
Im Profil kannst Du frei den Rang ändern
 
Beiträge: 15
Registriert: 13. Dez 2015, 15:57

Re: Summe von Zellen die "+" und somit 2 Werte enthalten kön

Beitragvon Klaus-Dieter » 23. Sep 2018, 09:47

Hallo,

zeig doch mal eine Beispieldatei.
Viele Grüße
Klaus-Dieter
Lösungsvorschläge sind, wenn es keinen anders lautenden Hinweis gibt, von mir getestet.
Künstliche Intelligenz ist besser als natürliche Dummheit.
Benutzeravatar
Klaus-Dieter
Im Profil kannst Du frei den Rang ändern
 
Beiträge: 18182
Registriert: 27. Nov 2003, 23:03
Wohnort: Sassenburg

Re: Summe von Zellen die "+" und somit 2 Werte enthalten kön

Beitragvon Ralf A » 23. Sep 2018, 09:52

Hi Lox,

der Wert 11 + 12 ist für Excel Text und Text lässt sich nicht addieren. Ausweg ohne Hilfszellen: Du schreibst in die Zelle:
= 11 + 12

oder schreibst Dir eine benutzerdefinierte Funktion, die den Term in seine Bestandteile zerlegt, wenn ein + darin gefunden wird.
Ciao, Ralf
Die Freiheit des Menschen liegt nicht darin, daß er tun kann, was er will, sondern daß er nicht tun muß, was er nicht will. (Jean-Jacques Rousseau)
Bild
Ralf A
Im Profil kannst Du frei den Rang ändern
 
Beiträge: 860
Registriert: 17. Mär 2018, 11:47

Re: Summe von Zellen die "+" und somit 2 Werte enthalten kön

Beitragvon Loxxx » 23. Sep 2018, 10:36

Ich habe jetzt mal die ersten beiden Zellen (C10 und D10) in Ihre Bestandteile zerlegt (In C10 steht 9+10, in D10 "FW"):
=RECHTS(C10;FINDEN("+";C10))+LINKS(C10;FINDEN("+";C10)-1)
+WENN(D10="*+*";RECHTS(D10;FINDEN("+";D10))+LINKS(D10;FINDEN("+";D10)-1))

Das funktioniert auch, müsste ich allerdings für 32 Spalten machen, also +WENN(E10... +WENN(F10..
Die erste Zeile müsste ich auch noch in eine WENN Bedingung packen, falls da ein Buchstabe stehen würde.
Loxxx
Im Profil kannst Du frei den Rang ändern
 
Beiträge: 15
Registriert: 13. Dez 2015, 15:57

Re: Summe von Zellen die "+" und somit 2 Werte enthalten kön

Beitragvon Loxxx » 23. Sep 2018, 11:23

ich glaub ich hab's:

=WENN($AI10="Montag";SUMME(WENNFEHLER(WENN(ISTZAHL(C10:AH10);C10:AH10;LINKS(C10:AH10;LÄNGE(C10:AH10)-3)*1);0))+SUMME(WENNFEHLER(WENN(ISTZAHL(C10:AH10);C10:AH10;LINKS(C10:AH10;LÄNGE(C10:AH10)-2)*1);0));SUMME(WENNFEHLER(WENN(ISTZAHL(C10:AH10);C10:AH10;LINKS(C10:AH10;LÄNGE(C10:AH10)-1)*1);0)))

wohl nicht sehr elegant, dafür schlag ich mit "dieser Klappe gleich noch ein paar andere Fliegen" auch.
Danke allen!
Loxxx
Im Profil kannst Du frei den Rang ändern
 
Beiträge: 15
Registriert: 13. Dez 2015, 15:57

Re: Summe von Zellen die "+" und somit 2 Werte enthalten kön

Beitragvon BoskoBiati » 23. Sep 2018, 11:25

Hi,

Code: Alles auswählen
=SUMME(WENN(ISTZAHL(SUCHEN("+";$C$10:$AH$10));LINKS($C$10:$H$10;SUCHEN("+";C10:AH10)-1)+TEIL(C10:AH10;SUCHEN("+";C10:AH10)+1;5);C10:AH10))


Matrixformel! Mit STRG+SHIFT+ENTER abschließen.
Gruß

Bosko

Ich würde mich freuen zu erfahren, ob ich helfen konnte.
BoskoBiati
Im Profil kannst Du frei den Rang ändern
 
Beiträge: 8172
Registriert: 26. Jul 2009, 21:46

Re: Summe von Zellen die "+" und somit 2 Werte enthalten kön

Beitragvon juvee » 23. Sep 2018, 11:37

Hi,

meine Lösung wäre eine UDF, die nimmt auch mehr als 2 Touren in einer Zelle

Code: Alles auswählen
Function toursumme(rng As Range)
Dim c As Range
For Each c In rng
  If InStr(1, c, "+") Then toursumme = toursumme + Evaluate("sum( " & c.Value & ")")
  If IsNumeric(c) Then toursumme = toursumme + c
Next
End Function


VG Juvee
Ich habe "juvee" am 25.01.2018 gegoogelt. Ich distanziere mich von den ungefähr 252 Einträgen ausdrücklich.
Da binich nich dabei, dat is pri ima, vivaaaaa....lalalala!
juvee
Im Profil kannst Du frei den Rang ändern
 
Beiträge: 2932
Registriert: 05. Jun 2014, 12:48

Alternativen

Beitragvon Luc$:-? » 24. Sep 2018, 00:46

Morhn, Juvee;
deine UDF könnte man auch etwas einfacher gestalten: ZellFml: =TourSumme(C10:AH10)
Code: Alles auswählen
Function TourSumme(Bezug)
    Dim c As Variant
    For Each c In Bezug
        If InStr(1, c, "+") Then c = ActiveSheet.Evaluate("+" & c)
        If IsNumeric(c) Then TourSumme = TourSumme + c
    Next
End Function
Aber, obwohl du einen rationalen Ansatz drin hast, warst du dabei nicht konsequent. Folglich wäre das auch nicht mein Favorit. Der könnte so aussehen: ZellFml: =TourSum(C10:J10) bzw {=TourSum(""&C10:AH10)}
Code: Alles auswählen
Function TourSum(Bezug)
    On Error Resume Next
    With WorksheetFunction
        If TypeOf Bezug Is Range Then Bezug = .Transpose(Bezug)
        If IsError(LBound(Bezug, 2)) Then
        Else: Bezug = .Transpose(Bezug)
        End If
    End With
    TourSum = ActiveSheet.Evaluate(Join(Bezug, "+"))
End Function
Es ginge sogar noch einfacher, mit einem 1Zeiler, aber das würde die Umwandlung des Arguments in ein Datenfeld schon in der Fml und deshalb (singulare) MatrixFmlForm voraussetzen (wie auch bei obiger ZweitFml): ZellFml: {=TSum(""&C10:AH10)}
Code: Alles auswählen
Function TSum(Bezug): TSum = ActiveSheet.Evaluate(Join(Bezug, "+")): End Function
Aber im Grunde genommen wäre mir das zu speziell, so dass ich Loxxx für den geschilderten primären Zweck den Einsatz universellerer UDFs (für ein etwas breiteres Spektrum primäraufgabenähnlicher anderer Zwecke einsetzbar) empfehlen würde:
Var1 (singulare MatrixFml mit 1 UDF): {=SUMME(TxEval(0&C10:AH10))} bzw
Var2 (NormalFml mit 2 UDFs): =TxEval(VJoin(C10:AH10;"+";-2))
Die UDF VJoin könnte dabei durch die neue XlFkt TEXTVERKETTEN ersetzt wdn, während soetwas bei TxEval kaum oder nicht möglich wäre, da die alte XLM-Fkt AUSWERTEN (aus Xl4, aktuell nur in benannter Fml als .xlsm/b) hierbei sehr sperrig ist.
UDF-PgmCodes:
TxEval (Vs1.0) http://www.herber.de/forum/archiv/1476t ... ml#1477400
VJoin (Vs1.4) w³.herber.de/bbs/user/99024.xlsm (in BspDatei)
Gruß, Luc :-?
Luc$:-?
 

ohne VBA

Beitragvon lupo1 » 24. Sep 2018, 05:22

=SUMMENPRODUKT(--TEIL(WECHSELN(TEXTVERKETTEN("+";;A1:A4)&WIEDERHOLEN("+0";29);"+";WIEDERHOLEN(" ";99));ZEILE($1:$29)*99-98;99))
  • hier 1 bis max. 29 (in der Formel jedoch einfach zu erweiternde!) Summanden pro Zelle (hier: A1:A4)
  • in meinem Beitrag erklärt
  • Office 365 (kostet!), Excel-Online, Docs oder LO (alle kostenlos!) nötig

2+3+8
1
6+9+7
4+5

ergibt somit 45.
lupo1
Im Profil kannst Du frei den Rang ändern
 
Beiträge: 9271
Registriert: 25. Okt 2012, 13:38

Re: Summe von Zellen die "+" und somit 2 Werte enthalten kön

Beitragvon Loxxx » 24. Sep 2018, 17:13

wow Leute, vielen Dank für die Vorschläge!
VBA kommt leider nicht in Frage, da ich die Tabelle auch mobil nutzen möchte, trotzdem Danke Ihr beiden und sorry: Hätte ich wohl gleich erwähnen sollen.
@lupo1: Sehr interessant, habe mir auch den Beitrag angesehen, das schau ich mir genauer an, kommt allerdings in diesem Fall nicht so in Frage, sollte auch auf älteren Excel Versionen funktionieren
@Bosko: Wollte Deine Lösung schon in die Tonne kloppen, weil ständig eine Fehlermeldung kam. ABER: Du hattest lediglich einen Tippfehler drin ($H$10 statt$AH$10)!
Korrigiert, und scheint perfekt zu funktionieren! Vielen Dank hierfür!
Loxxx
Im Profil kannst Du frei den Rang ändern
 
Beiträge: 15
Registriert: 13. Dez 2015, 15:57

Re: Summe von Zellen die "+" und somit 2 Werte enthalten kön

Beitragvon lupo1 » 24. Sep 2018, 17:28

Mobil kannst Du ja Excel-online nutzen. Möglicherweise geht TEXTVERKETTEN auch in den Offlineversionen für Mobilgeräte Android.

Ansonsten: Google oder LO.

Meine Formel geht aber ja auch über die Anforderung hinaus, da 1 oder 2 Summanden Dir ausreichen.
Zuletzt geändert von lupo1 am 24. Sep 2018, 17:29, insgesamt 1-mal geändert.
lupo1
Im Profil kannst Du frei den Rang ändern
 
Beiträge: 9271
Registriert: 25. Okt 2012, 13:38

Re: Summe von Zellen die "+" und somit 2 Werte enthalten kön

Beitragvon slowboarder » 24. Sep 2018, 17:29

HI

noch ne andere Lösungsvariante:

gehe auf ein zweites Tabellenblatt und markiere die Zelle C10
erstelle dort über den Namensmanager einen Namen (z.b. "xxx" du darfst dir selber was schönes ausdenken) mit folgender Formel bei Bezieht sich auf:

=Auswerten(Tabelle1!C10)

für Tabelle1 setzt du den Namen des Tabellenblatts ein, das du auswerten willst, der zellbezug muss relativ sein (ohne $)

dann schreibst du in die Zelle C10 des zweiten Tabellenblatts die Formel: =Wenn(Istfehler(xxx);0;xxx)
und ziehst diese soweit nach links und nach unten, wie du auf dem ersten Tabellenblatt Daten hast.
Die Summen berechnest du dann in diesem zweiten Tabellenblatt

das Auswerten ist eine alte Excel4Makro-Funktion, welche über NAMEN noch verwendet werden kann, aber leider nicht mehr als normale funktion zur verfügung steht.
damit lassen sich Texte, die wie Berechungen aussehen, berechnen. Im Prinzip analog zu Indirekt mit Zellbezügen). In wie weit das mit Mobil- oder Androidsystemen geht weiß ich nicht, aber es sollte zumindest problemlos auf älteren Excelversionen funktionieren.

Gruß Daniel



Gruß Daniel
slowboarder
Im Profil kannst Du frei den Rang ändern
 
Beiträge: 26948
Registriert: 18. Apr 2009, 13:33


Zurück zu Excel Forum (provisorisch)

Wer ist online?

Mitglieder in diesem Forum: 0 Mitglieder und 2 Gäste