Live-Forum - Die aktuellen Beiträge
Datum
Titel
15.07.2024 16:00:57
15.07.2024 15:41:38
Anzeige
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Inhaltsverzeichnis

Zahl u. Text in einer Zelle, mit Zahl rechnen

Zahl u. Text in einer Zelle, mit Zahl rechnen
26.01.2018 00:26:31
Katko
Moin !
Ich bewege mich jetzt gerade von OpenOffice nach MS Excel, da ich in OpenOffice ein template erstellt habe, das ich jetzt nach Excel übernehmen will. Leider will Excel eine Formel nicht verarbeiten. Hab mir in Calc damit schon einen abgebrochen.
Kann mir jemand die Formel einfach nach Excel übersetzen ?
In der Beispieldatei sollen per Matrixabfrage die Beträge in Spalte D in Abhängigkeit von Spalte C "Einzahlung" und Spalte A "Monat" für Monat 8 summiert werden.
Das Problem für mich ist, daß ich nicht weiß, wie ich die Umwandlung aus dem Text in eine Zahl und die Ersetzung des Punktes durch ein Komma in die Abfrage bekomme.
Für eine Übersetzung wäre ich schon super dankbar, für eine Erklärung würde ich sogar Freudentränen vergießen.
Vielen Dank !
https://www.herber.de/bbs/user/119276.xlsx

12
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Zahl u. Text in einer Zelle, mit Zahl rechnen
26.01.2018 01:18:38
Sepp
Hallo Katko,
warum tut man sich einen solchen Tabellenaufbau an?
Tabelle1

 ABCDEFG
116.01.17 20:21 Auszahlung50.00 EUR CreditCard   
227.02.17 16:21 Einzahlung600.00 EUR CreditCard   
305.07.17 08:19 Einzahlung4000.00 EUR CreditCard   
414.08.17 13:54 Auszahlung2500.00 EUR CreditCard   
522.08.17 09:36 Auszahlung50.00 EUR CreditCard   
625.08.17 09:03 Auszahlung1000.00 EUR CreditCard   
731.08.17 08:17 Auszahlung450.00 EUR CreditCard Ergebnis :4.000,00 €

Formeln der Tabelle
ZelleFormel
G7=SUMMENPRODUKT((WECHSELN(LINKS(D1:D7;SUCHEN(" ";D1:D7)-1); ".";",")*1)*(MONAT(A1:A7)=8)*(C1:C7="Auszahlung"))


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4.8
Gruß Sepp

Anzeige
AW: Zahl u. Text in einer Zelle, mit Zahl rechnen
26.01.2018 01:18:52
Daniel
HI
SummeWenns kann in Excel keine Berechnungen mit den Werten aus den Spalten vornehmen, da müsstest du Summenprodukt verwenden:
=SUMMENPRODUKT((WERT(WECHSELN(LINKS(D1:D7;SUCHEN(" ";D1:D7;1)-1);".";",";1))*(MONAT(A1:A7)=8)*( _ C1:C7="Auszahlung")))
Gruß Daniel
AW: Zahl u. Text in einer Zelle, mit Zahl rechnen
26.01.2018 08:15:11
Jürgen
Vielen Dank, da hätte ich eigentlich auch selbst drauf kommen können. Manchmal sieht man halt den Wald vor lauter Bäumen nicht.
Warum man sich so einen Tabellenaufbau antut. Weiß ich auch nicht, was das soll.
Ist aber leider so vorgegeben. Es handelt sich um einen Online-Kontoauszug eines ausländischen Unternehmens.
Dafür hab ein Template gebastelt, das alles mögliche ausliest, gruppiert, berechnet und bewertet.
Nochmals vielen Dank !!!
Im Übrigen habe ich noch nie so ein Bedienerunfreundliches Forum erlebt ! Geht das hier noch anderen so ?
Anzeige
AW: Zahl u. Text in einer Zelle, mit Zahl rechnen
26.01.2018 10:01:50
Jürgen
Also verflucht nochmal, warum will das bei mir nicht ?
In der ersten Beispieldatei klappt´s, in der zweiten nicht. Dabei sind doch nur die Zellbereiche und der Suchtext ein anderer.
Kann das mit den Formaten der Zellen zu tun haben ?
https://www.herber.de/bbs/user/119282.xlsx
Bitte nochmals um Hilfe ! Ich find den Fehler nicht !
Anzeige
AW: Zahl u. Text in einer Zelle, mit Zahl rechnen
26.01.2018 10:26:12
Sepp
Hallo Jürgen,
Tabelle1

 ABCDEFGH
213.01.17 22:11-3,23Rollover FeeWeekend fee -2,55  
313.01.17 22:11-3,7Rollover FeeWeekend fee -0,47  
416.01.17 20:2148,06Deposit50.00 EUR CreditCard 51,76  
516.01.17 22:1047,9Rollover FeeOver night fee -0,16  
624.02.17 22:1141,5Rollover FeeWeekend fee -2,55  
727.02.17 16:21663,58Deposit600.00 EUR CreditCard 622,08 3.000,00 €
84.07.17 22:1035,58Rollover FeeOver night fee -0,25  
95.07.17 08:194476,78Deposit4000.00 EUR CreditCard 4441,2  
1014.08.17 13:543305,6Deposit2500.00 EUR CreditCard 2884,25  
1122.08.17 09:3644,87Deposit50.00 EUR CreditCard 57,57  
1230.08.17 22:1142,57Rollover FeeOver night fee -0,02  
1330.08.17 22:1142,57Rollover FeeOver night fee -0,01  
1431.08.17 08:17566,23Deposit450.00 EUR CreditCard 523,66  
15        

Formeln der Tabelle
ZelleFormel
H7{=SUMMENPRODUKT((WENNFEHLER(WECHSELN(LINKS(D2:D3000;SUCHEN(" ";D2:D3000)-1); ".";",")*1;0))*(MONAT(A2:A3000)=8)*(C2:C3000="Deposit"))}
Enthält Matrixformel:
Umrandende
{ } nicht miteingeben,
sondern Formel mit STRG+SHIFT+RETURN abschließen!
Matrix verstehen


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4.8
Gruß Sepp

Anzeige
AW: Zahl u. Text in einer Zelle, mit Zahl rechnen
26.01.2018 12:01:10
Jürgen
Danke Sepp, das war´s ! Könnt dich knutschen, aber du bist bestimmt nicht rasiert ;-)
Eins noch. Wie wird in dieser Formel der Zahlentext in einen rechenbaren "Wert" gewandelt ?
Gruß Jürgen
*1 o.T.
26.01.2018 12:11:46
Sepp
Gruß Sepp

AW: Zahl u. Text in einer Zelle, mit Zahl rechnen
26.01.2018 12:13:29
Daniel
HI
Die Umwandlung von Text in Zahl macht Excel automatisch, wenn der Text, der wie eine Zahl aussieht, in eine direkte Rechenoperation (+-*/) eingebunden ist, oder die Funktion WERT angewendet wird
hier wird also aus "1234"*Wahr*Falsch das gemacht: 1234*1*0 und das kann berechnet werden.
Gruß Daniel
Anzeige
AW: Zahl u. Text in einer Zelle, mit Zahl rechnen
26.01.2018 11:15:55
Daniel
Hi
die Frage ist, ob man hier, wenn man die Datei erhält, nicht lieber einmal die Funktion
DATEN - DATENTOOLS - TEXT IN SPALTEN mit der Spalte D anwendet.
hier bei:
Schritt 1: getrennt
Schritt 2: Leerzeichen
Schritt 3: Spalten 2 und 3 abwählen (nicht importieren) und unter "weitere" angeben, dass der Punkt das Dezimalzeichen sein soll
dann hätte man in Spalte D nur die Zahlenwerte stehen und könnte mit deutlich einfacheren Formeln arbeiten.
Gruß Daniel
AW: Zahl u. Text in einer Zelle, mit Zahl rechnen
26.01.2018 12:14:47
Jürgen
Ja Daniel, wäre einfacher. Da aber eventuell auch die Spalte nach anderem Text durchsucht werden soll, hätte ich da vllt ein Problem.
Ich bin euch jedenfalls super dankbar und hätte nie gedacht, daß das hier so fix geht.
Gruß
Anzeige
~OT: Für Interessenten an UDF-Alternativen
27.01.2018 02:43:23
Luc:-?
Voraussichtl abundan werde ich mir erlauben, Fml-LösungsAlternativen unter Nutzung von UDFs vorzustellen, die sich in 1.Linie an diejenigen wenden, die bereits über die jeweils aktuellen UDF-Versionen verfügen bzw wissen, wo sie zu finden sind (deshalb dann auch keine Links). Diese werde ich idR auch erläutern.
Für das zuletzt von Sepp gebrachte Bsp sähe das dann im Prinzip* so aus:
 ABCDEFGH
11
13.01.2017 22:11-3,23Rollover FeeWeekend fee -2,55  13.01.2017 22:11-3,7Rollover FeeWeekend fee -0,47  16.01.2017 20:2148,06Deposit50.00 EUR CreditCard 51,76  16.01.2017 22:1047,9Rollover FeeOver night fee -0,16  24.02.2017 22:1141,5Rollover FeeWeekend fee -2,55  27.02.2017 16:21663,58Deposit600.00 EUR CreditCard 622,08 3 000,00 €04.07.2017 22:1035,58Rollover FeeOver night fee -0,25  05.07.2017 08:194476,78Deposit4000.00 EUR CreditCard 4441,2  14.08.2017 13:543305,6Deposit2500.00 EUR CreditCard 2884,25  22.08.2017 09:3644,87Deposit50.00 EUR CreditCard 57,57  30.08.2017 22:1142,57Rollover FeeOver night fee -0,02  30.08.2017 22:1142,57Rollover FeeOver night fee -0,01  31.08.2017 08:17566,23Deposit450.00 EUR CreditCard 523,66          August 2017InsgesamtDeposit3 000,00 €  Fmllängen:FktsAnzahl:Fml D25: {=SUMME(VSplit(DataSet(NoErrRange(D11:D23;;(MONAT(A11:A23)=8)*(C11:C23=C25));"  ";2;;1);;1))}65 : 1205 : 6
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
* Für die Fml-Längen-Ermittlung habe ich in Sepps Fml SUMME statt SUMMENPRODUKT angesetzt, da das bei der von ihm gewählten Form in einer MatrixFml ausreichend ist.
Erläuterung:
• Die UDF NoErrRange erzeugt hier einen diskontinuierlichen Bereich, der aus dem Bereich lt ihrem Argu­ment1 nur die Zellen aus­wählt, die die Bedingung lt ihrem Argument3 erfüllen.
• Die UDF DataSet ist eine Fkt, die Operationen mit Mengen durchführt. Hier fasst sie aber nur das Ergebnis der vorher­gehenden Fkt so zusammen, dass ein kontinuierliches Datenfeld (Vektor) entsteht, was für die Anwendung der nächsten UDF (lt aktueller Ver­sion) erforderlich ist. Da stets 2 Mengen angegeben wdn müssen (Argument1/2), wird als 2. ein Text aus 2 Leerzeichen (das 2. ist in der Fml ein Zeichen160, weil die Browser sonst eines unter­schlagen würden, und muss durch Zeichen32 ersetzt wdn) ver­wen­det, damit die nächste UDF in allen Zeilen gleich­lange Vektoren erzeugen kann, was die zwingend benötigte recht­eckige Matrix ergibt.
• Die UDF VSplit teilt hier die gelieferten TextZeilen auf den LeerZeichen (Default-Wert für Argu­ment2) in 3 Elemente, wobei ihr Argu­ment3 hier dafür sorgt, dass rein numerische Texte (TextZahlen) in echte Zahlen umgewandelt wdn (auch bei DezimalPkt!). Damit wdn letztlich auch nur diese summiert, die Texte ignoriert, was eine zusätzliche Anwendung von INDEX erspart.
Morrn, Luc :-?
Anzeige

300 Forumthreads zu ähnlichen Themen

Anzeige
Anzeige
Anzeige
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige