Live-Forum - Die aktuellen Beiträge
Anzeige
Archiv - Navigation
1308to1312
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

komplizierte Formel

komplizierte Formel
12.04.2013 12:08:45
e-mo

Hallo,
stehe hier vor einem Problem :-S (wie immer!)
ich kann das Problem lösen, aber leider nur in sehr vielen Schritten. Möchte das gerne aber in einer Formel lösen...hoffentlich ist das machbar.
Hier ein Beispiel: (Ergebnis lautet: 9)
Habe eine Zahl: 1230456
jeder dieser Zahl wird in Abwechslung mit 2 und 3 multipliziert:
--> 1*2=2; 2*3=6; 3*2=6; 0*3=0; 4*2=8; 5*3=15; 6*2=12
aus dem jeweiligen ergebnis wird nun jeweils die quersumme gebildet (2,6,6,0,8,6,3)und dann im anschluss die Summe (31)
Das Ergebnis ist nun die Differenz dieser Zahl zu der nächsten aufgerundeter Zahl (40) = 9
Kann man das mit einer Formel hinbekommen?
danke,
e-mo

17
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: komplizierte Formel
12.04.2013 12:51:02
JACKD
hallo e-mo
ist die Zahl immer gleich lang?
wenn ja, dann kann man das, wenn auch umständlich mit ner Formel abbilden.
Wenn die länge variabel ist, dann wird es wohl eher eine makrolösung
grüße

so in etwa
12.04.2013 13:08:10
JACKD
=(AUFRUNDEN(SUMME(SUMMENPRODUKT((0&TEIL(TEIL(A2;1;1)*2;SPALTE(2:2);1))*1); SUMMENPRODUKT((0&TEIL(TEIL(A2;2;1)*3;SPALTE(2:2);1))*1); SUMMENPRODUKT((0&TEIL(TEIL(A2;3;1)*2;SPALTE(2:2);1))*1); SUMMENPRODUKT((0&TEIL(TEIL(A2;4;1)*3;SPALTE(2:2);1))*1); SUMMENPRODUKT((0&TEIL(TEIL(A2;5;1)*2;SPALTE(2:2);1))*1); SUMMENPRODUKT((0&TEIL(TEIL(A2;6;1)*3;SPALTE(2:2);1))*1); SUMMENPRODUKT((0&TEIL(TEIL(A2;7;1)*2;SPALTE(2:2);1))*1))/10;0)*10) -(SUMME( SUMMENPRODUKT((0&TEIL(TEIL(A2;1;1)*2;SPALTE(2:2);1))*1); SUMMENPRODUKT((0&TEIL(TEIL(A2;2;1)*3;SPALTE(2:2);1))*1); SUMMENPRODUKT((0&TEIL(TEIL(A2;3;1)*2;SPALTE(2:2);1))*1); SUMMENPRODUKT((0&TEIL(TEIL(A2;4;1)*3;SPALTE(2:2);1))*1); SUMMENPRODUKT((0&TEIL(TEIL(A2;5;1)*2;SPALTE(2:2);1))*1); SUMMENPRODUKT((0&TEIL(TEIL(A2;6;1)*3;SPALTE(2:2);1))*1); SUMMENPRODUKT((0&TEIL(TEIL(A2;7;1)*2;SPALTE(2:2);1))*1)))

Anzeige
AW: so in etwa
12.04.2013 15:05:04
e-mo
Hallo alle,
erstmal vielen dank an alle! alle lösungen sind wunderbar...und es funktioniert auch alles bestens!
habe aber noch eine zusätzliche frage...bzw...ein zusätzliches problem.
kann man die leerzeichen in der zahlenfolge bei der berechnung unterbinden...bzw...so dass die formel auch funktioniert? wenn die zahlen hintereinander geschrieben sind, dann funktionieren alle formeln sehr gut, aber wenn ein leerzeichen irgendwo ist, dann nicht mehr.
Leerzeich ist hier ungleich null. D.h. es könnte mal sein, dass in der tabelle zahlen so aussehen könnten: 12 30 456, 1 2 30 4 45, 12 30 45 6 etc...
prinzipiell soll leerzeichen also kein anderes ergebnis liefern bzw. #wert :-)
Danke,
e-mo

Anzeige
Lösung per MATRIX-Funktion
12.04.2013 13:54:15
NoNet
Hallo e-mo,
für eine beliebig lange ganzzahlige positive Zahl in A2 klappt das mit folgender Funktion :
=AUFRUNDEN(SUMME(GANZZAHL(INDEX((TEIL(A2;ZEILE(INDIREKT("1:"&LÄNGE(A2)));1)*(3-REST(ZEILE(INDIREKT("1:"&LÄNGE(A2)));2)));;ZEILE(INDIREKT("1:"&LÄNGE(A2))))/10)+REST(INDEX((TEIL(A2;ZEILE(INDIREKT("1:"&LÄNGE(A2)));1)*(3-REST(ZEILE(INDIREKT("1:"&LÄNGE(A2)));2)));;ZEILE(INDIREKT("1:"&LÄNGE(A2))));10));-1)-SUMME(GANZZAHL(INDEX((TEIL(A2;ZEILE(INDIREKT("1:"&LÄNGE(A2)));1)*(3-REST(ZEILE(INDIREKT("1:"&LÄNGE(A2)));2)));;ZEILE(INDIREKT("1:"&LÄNGE(A2))))/10)+REST(INDEX((TEIL(A2;ZEILE(INDIREKT("1:"&LÄNGE(A2)));1)*(3-REST(ZEILE(INDIREKT("1:"&LÄNGE(A2)));2)));;ZEILE(INDIREKT("1:"&LÄNGE(A2))));10))
Funktion bitte mit Strg+Shift+ENTER abschliessen !
Hier ein paar Beispiele :
AB
1
2
3
4
5
6
7
8
9
10
11
12

Gruß, NoNet
Hinweis : Diese MATRIX-Funktion beinhaltet insgesamt 8 Verschachtelungen (wenn ich mich nicht verzählt habe ;-) - m.E. sind bis Excel 2003 nur 7 (oder 8 ?) Verschachtelungen möglich, daher kann es bei Excel-Versionen bis 2003 möglicherweise zu Problemen kommen !
Für Excel-Versionen ab 2007 sind mehr Verschachtelungen möglich, hier gibt es keine Probleme !

Anzeige
keine Formel aber UDF
12.04.2013 13:57:48
Bertram
Hallo,
du fragtest zwar nach ner Formel, aber die hab ich grad nicht hingekriegt:-)
Also nur damit es nicht für'n Müll ist heir noch meine UDF
Function DifferenzZurAufgerundeten(rngZahl As Range) As Integer
Dim intAnz As Integer
Dim strMulti As String
Dim intQS As Integer
Dim intSumme As Integer
Dim i As Integer
Dim j As Integer
intAnz = Len(rngZahl)
For i = 1 To intAnz
If i Mod 2 = 0 Then
strMulti = CStr(CInt(Mid(rngZahl, i, 1)) * 3)
Else
strMulti = CStr(CInt(Mid(rngZahl, i, 1)) * 2)
End If
For j = 1 To Len(strMulti)
intQS = intQS + CInt(Mid(strMulti, j, 1))
Next j
intSumme = intSumme + intQS
intQS = 0
Next i
DifferenzZurAufgerundeten = Application.WorksheetFunction.RoundUp(intSumme, -1) - intSumme
End Function

Gruß
Bertram

Anzeige
AW: komplizierte Formel
12.04.2013 15:32:09
e-mo
Hallo alle,
erstmal vielen dank an alle! alle lösungen sind wunderbar...und es funktioniert auch alles bestens!
habe aber noch eine zusätzliche frage...bzw...ein zusätzliches problem.
kann man die leerzeichen in der zahlenfolge bei der berechnung unterbinden...bzw...so dass die formel auch funktioniert? wenn die zahlen hintereinander geschrieben sind, dann funktionieren alle formeln sehr gut, aber wenn ein leerzeichen irgendwo ist, dann nicht mehr.
Leerzeich ist hier ungleich null. D.h. es könnte mal sein, dass in der tabelle zahlen so aussehen könnten: 12 30 456, 1 2 30 4 45, 12 30 45 6 etc...
prinzipiell soll leerzeichen also kein anderes ergebnis liefern bzw. #wert :-)
Danke,
e-mo

Anzeige
WECHSELN(A2;" ";"") anstatt A2
12.04.2013 16:00:01
NoNet
Hallo e-mo,
tausche in der von mir vorgeschlagenen MATRIX-Funktion einfach A2 durch WECHSELN(A2;" ";"") aus :
=AUFRUNDEN(SUMME(GANZZAHL(INDEX((TEIL(WECHSELN(A2;" ";"");ZEILE(INDIREKT("1:"&LÄNGE(WECHSELN(A2;" ";""))));1)*(3-REST(ZEILE(INDIREKT("1:"&LÄNGE(WECHSELN(A2;" ";""))));2)));;ZEILE(INDIREKT("1:"&LÄNGE(WECHSELN(A2;" ";"")))))/10)+REST(INDEX((TEIL(WECHSELN(A2;" ";"");ZEILE(INDIREKT("1:"&LÄNGE(WECHSELN(A2;" ";""))));1)*(3-REST(ZEILE(INDIREKT("1:"&LÄNGE(WECHSELN(A2;" ";""))));2)));;ZEILE(INDIREKT("1:"&LÄNGE(WECHSELN(A2;" ";"")))));10));-1)-SUMME(GANZZAHL(INDEX((TEIL(WECHSELN(A2;" ";"");ZEILE(INDIREKT("1:"&LÄNGE(WECHSELN(A2;" ";""))));1)*(3-REST(ZEILE(INDIREKT("1:"&LÄNGE(WECHSELN(A2;" ";""))));2)));;ZEILE(INDIREKT("1:"&LÄNGE(WECHSELN(A2;" ";"")))))/10)+REST(INDEX((TEIL(WECHSELN(A2;" ";"");ZEILE(INDIREKT("1:"&LÄNGE(WECHSELN(A2;" ";""))));1)*(3-REST(ZEILE(INDIREKT("1:"&LÄNGE(WECHSELN(A2;" ";""))));2)));;ZEILE(INDIREKT("1:"&LÄNGE(WECHSELN(A2;" ";"")))));10))
Die Funktion natürlich wieder mit Strg+Shift+ENTER bestätigen !
Hier ein paar Beispiele :
AB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

Salut und schönes WE, NoNet
PS : Damit sind es dann auch schon bis zu 9 verschachtelte Funktionen ;-)

Anzeige
AW: WECHSELN(A2;" ";"") anstatt A2
12.04.2013 21:53:22
e-mo
hallo NoNet,
deine matrixformel hat funktioniert...jetzt kann ich beliebige leerzeichen haben..
und noch eine frage, wenn du nichts dagegen hast...
wie sieht es mit dieser zahl als: 12 30 456-3 oder 123 04 56-4 oder 12 30 456 -7 oder 12 304 56 - 8 etc..
im prinziep ist es wie deine lösung, nur dass hier nur -3, oder -4 oder -7 oder - 8 nicht mit einbeziehen soll.
e-mo
ps: kommt jetzt noch mehr verschachtelungen rein? :-)

interessante Aufgabe
13.04.2013 14:18:08
Josef
Hallo e-mo
Kannst du dazu noch einige Fragen beantworten.
Wenn "-" vorkommt, ist das immer nur vor der Zahl ganz rechts, oder ist das ganz unterschiedlich?
Kann das "-" mehrmals in einer Zelle vorkommen?
Ausserdem wäre wiederum ein Beispiel mit den Rechnungsschritten, wie in deinem ersten Post von Vorteil.
Und aus reiner Neugier, wozu braucht man so was?
Gruss Sepp

Anzeige
AW: interessante Aufgabe
13.04.2013 15:27:55
e-mo
hallo sepp,
hm..auch sehr interessant deine frage...daran habe ich auch noch nicht gedacht...
laut meiner beobachtung ist das "-" immer wie im beispiel rechts gewesen und danach eine ziffer..oder leerzeichen und dann eine leerzeichen (siehe beispiel)...
aber es kam auch schon vor, dass kein "-" da war...und die zahlen nacheinander geschrieben worden sind.
prinzipiell sollte vor der letzten ziffer immer ein "-" sein.
zu deiner letzten frage....
das ist einer von vielen plausibilisierungscheck, den ich machen muss. meine absicht ist eigentlich noch etwas komplizierter...möchte das auch sehr gerne hier im nachhinein anfragen.. :-) aber erst eine lösung nach der anderen... :-)
e-mo

Anzeige
AW: interessante Aufgabe
13.04.2013 16:14:22
Josef
Hallo e-mo
Deinem Beispiel entsprechend würde das so funktionieren.
Das Minuszeichen, und alles was danach kommt wird einfach abgeschnitten.
 AB
112 30 456-59

Formeln der Tabelle
ZelleFormel
B1=REST(SUMMENPRODUKT(-TEIL((0&TEIL(WECHSELN(LINKS(A1;FINDEN("-";A1&"-")-1); " ";""); ZEILE(1:20); 1))*(3-REST(ZEILE(1:20); 2))&0;{1\2};1)); 10)


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4
Gruss Sepp

Anzeige
Achtung Fehler in Formel
13.04.2013 16:22:11
Josef
Hallo
Ich habe leider die Formel mit falschem Spaltentrennzeichen gepostet.
so ist's richtig.
 AB
112 30 456-59

Formeln der Tabelle
ZelleFormel
B1=REST(SUMMENPRODUKT(-TEIL((0&TEIL(WECHSELN(LINKS(A1;FINDEN("-";A1&"-")-1); " ";""); ZEILE(1:20); 1))*(3-REST(ZEILE(1:20); 2))&0;{1.2};1)); 10)


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4

AW: komplizierte Formel
14.04.2013 10:36:14
e-mo
Morgen alle zusammen...
ich habe die formel nun angewendet...und es scheint nicht zu funktionieren :-S
was habe ich getan?
nunja, ich habe die zahl erweitert und einen anderen faktor benutzt.
es ist jetzt eine ganze reihe von zahlen... spricht mehr als 20, die so aussehen können:
123045678901362794578
12304567890-1362794578
12 30 45 67 8 90 1362794578
12 30 45 678 9 0 -13 62 794578
12 3045 6 7 8 90 -1 362794578
123 0 45 67 8 90 - 136 2794 578
wichtig ist es...nur die ersten 11 zahlen wie in meiner voherigen beschreibung zu berechnen, wo leerzeichen nicht berücksichtigt werden und das "-" sowie die zahlen dahinter auch nicht mit einbezogen werden. als faktor hatte ich 3 und 2 angegeben, aber wenn ich das mit dem faktoren 2 und 1 umgestellt habe, hat das leider nicht mehr funktioniert...mit keinen der angebenen formel...insbesonders, wenn ich die formel runterkopiere...
e-mo

Immer noch keine Formel,...
15.04.2013 10:41:52
Bertram
... aber ne flexible UDF.
Hallo,
habe meine UDF etwas abgeändert, dann kannst du auch flexibel die Faktoren angeben.
https://www.herber.de/bbs/user/84880.xlsm
Gruß
Bertram

AW: Immer noch keine Formel,...
15.04.2013 11:12:32
e-mo
Hallo Bertram,
vielen dank! ich habe das mal durchgetestet.
Ich kenne mich leider nicht so gut aus...aber ich vermute, das was du geschickt hast in der Zelle mit:
=DifferenzZurAufgerundeten(B11;2;1).-..wobei ;2;1...die flexibelen faktoren sind, oder?
habe aber festgestellt, dass bei deiner angabe..als 2;1...ich zu einem anderen ergebnis gekommen bin, als ich das erwartet abe...als ich das umgestellt habe, also 1;2..dann waren die ergebnisse korrekt..
was muss ich bei dir abändern, so dass bei 2;1 das richtige ergebnis zustande kommt wie ich das derzeit mit 1;2 bekomme?
plus:
kann ich dieses script irgendwie oben einbinden lassen, als buttom, wo ich nur anklicken brauche, wenn ich die exceldatei aufmachen...und wo das script die zahlen aus der Spalte B überprüft, und mir das im selben sheet in der Spalte V ausgibt?
Danke,
e-mo

AW: Immer noch keine Formel,...
15.04.2013 12:37:48
Bertram
Hallo,
z.B. so: https://www.herber.de/bbs/user/84883.xlsm
Falls die Faktoren verkehrt herum sind, kannst du das im Code ändern.
Der Code wird nach dem klick auf den Button ausgeführt, nicht beim Öffnen der Mappe. Dann kannst du vor dem Asuführen noch die Faktoren ändern falls gewünscht.
Gruß
Bertram

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige