Excel: Umrechnung wenn Einheit mit in Zelle steht?

Computer: Software & Programmierung Office Software
#1

Hallo zusammen,
ich habe eine einzige Spalte in Excel 2013 mit mehreren hundert Datensätzen. Also ein Datensatz pro Zelle (Zeile). Hier ein Auszug:

40 KB
2,71 MB
4,01 MB
4 MB
78 KB
4 MB
4MB
1,44 MB
37 KB
4 MB

Leider stehen die Werte zusammen mit den Einheiten (KB oder MB) in einer Zelle. Ich will am Ende die Werte addieren, muss sie dazu aber zunächst in die gleiche Einheit bringen, also in KB oder MB. Das Format der Zelle ist zunächst Standard. Hat jemand eine Idee? Ich denke mir, man müsste entweder die Werte von den Einheiten zunächst trennen und in eine separate Spalte ausgliedern und dann umrechnen. Oder eine Art Parser finden, der auch innerhalb einer Zelle damit klar kommt.

Gruß
Jens

#2

Hallo Jens,

ich habe eine einzige Spalte in Excel 2013 mit mehreren
hundert Datensätzen. Also ein Datensatz pro Zelle (Zeile).

Hier ein Auszug:

4 MB
4MB

wenn das so ist, ist das blöd. Ansonsten hätte man mit TextInSpalten, Trennzeichen das Leerzeichen, das aufsplitten können. Dann mit Wenn() in einer dritten Spalte aufgrund der Masseinheit in B, den Masswert in A, korrekt anzeigen könnte.

Frage meinerseits, ist das so daß da NICHT IMMER ein Leerzeichen Wert und Einheit den Zellwert trennt?

Gruß
Reinhard

#3

Hallo Jens,

angenommen, die Zellen stehen in Spalte A ab Zelle A1. Dann schreibe in Zelle B1

=WENN(NICHT(ISTFEHLER(FINDEN("KB";A1)));WERT(WECHSELN(A1;"KB";""));WERT(WECHSELN(A1;"MB";""))\*1000)

und kopiere Zelle B1 nach unten.
In Spalte B sollten jetzt alle Werte in KB stehen.
Je nachdem wie du rechnen willst (1 MB=1000 KB oder 1 MB=1024 KB) musst du die 1000 am Ende der Formel durch 1024 ersetzten.
Mit =SUMME(… in irgend einer Zelle kannst du die KB-Werte dann addieren.

Gruß, Andreas

#4

Hallo,
vielen Dank für Deine Antwort. Ich konnte die Formel mit der 1024er-Umwandlung verwenden und komme nun zum gewünschten Ergebnis!

Gruß
Jens

#5

kilo mega KB MB kibi mebi KiB MiB

vielen Dank für Deine Antwort. Ich konnte die Formel mit der
1024er-Umwandlung verwenden und komme nun zum gewünschten
Ergebnis!

Hallo Jens,

bei 1024er Umrechnung sind das KiB und keine KB.
Quelle: http://de.wikipedia.org/wiki/Bin%C3%A4rpr%C3%A4fix
Wenn ich mich nicht verrechnet habe sieht das so aus:

Tabellenblatt: [Mappe1]!Tabelle1
 │ A │ B │ C │ D │ E │ F │ G │
───┼────────────┼──────────┼──────────┼───┼────────────┼───────────┼───────────┤
 1 │ Wert [dez] │ KB │ MB │ │ Wert [bin] │ KiB │ MiB │
───┼────────────┼──────────┼──────────┼───┼────────────┼───────────┼───────────┤
 2 │ 40 KB │ 40 KB │ 0,040 MB │ │ 40 KiB │ 40 KiB │ 0,039 MiB │
───┼────────────┼──────────┼──────────┼───┼────────────┼───────────┼───────────┤
 3 │ 2,71 MB │ 2.710 KB │ 2,710 MB │ │ 2,71 MiB │ 2.775 KiB │ 2,710 MiB │
───┼────────────┼──────────┼──────────┼───┼────────────┼───────────┼───────────┤
 4 │ 4,01 MB │ 4.010 KB │ 4,010 MB │ │ 4,01 MiB │ 4.106 KiB │ 4,010 MiB │
───┼────────────┼──────────┼──────────┼───┼────────────┼───────────┼───────────┤
 5 │ 4 MB │ 4.000 KB │ 4,000 MB │ │ 4 MiB │ 4.096 KiB │ 4,000 MiB │
───┼────────────┼──────────┼──────────┼───┼────────────┼───────────┼───────────┤
 6 │ 78 KB │ 78 KB │ 0,078 MB │ │ 78 KiB │ 78 KiB │ 0,076 MiB │
───┼────────────┼──────────┼──────────┼───┼────────────┼───────────┼───────────┤
 7 │ 4 MB │ 4.000 KB │ 4,000 MB │ │ 4 MiB │ 4.096 KiB │ 4,000 MiB │
───┼────────────┼──────────┼──────────┼───┼────────────┼───────────┼───────────┤
 8 │ 4MB │ 4.000 KB │ 4,000 MB │ │ 4MiB │ 4.096 KiB │ 4,000 MiB │
───┼────────────┼──────────┼──────────┼───┼────────────┼───────────┼───────────┤
 9 │ 1,44 MB │ 1.440 KB │ 1,440 MB │ │ 1,44 MiB │ 1.475 KiB │ 1,440 MiB │
───┼────────────┼──────────┼──────────┼───┼────────────┼───────────┼───────────┤
10 │ 37 KB │ 37 KB │ 0,037 MB │ │ 37 KiB │ 37 KiB │ 0,036 MiB │
───┴────────────┴──────────┴──────────┴───┴────────────┴───────────┴───────────┘
Benutzte Formeln:
B2 : =LINKS(A2;LÄNGE(A2)-2)\*(((RECHTS(A2;2)="MB")\*999)+1)
B3 : =LINKS(A3;LÄNGE(A3)-2)\*(((RECHTS(A3;2)="MB")\*999)+1)
usw. in B

C2 : =LINKS(A2;LÄNGE(A2)-2)/(((RECHTS(A2;2)="KB")\*999)+1)
C3 : =LINKS(A3;LÄNGE(A3)-2)\*1/(((RECHTS(A3;2)="KB")\*999)+1)
usw. in C

F2 : =LINKS(A2;LÄNGE(A2)-2)\*(((RECHTS(A2;2)="MB")\*1023)+1)
F3 : =LINKS(A3;LÄNGE(A3)-2)\*(((RECHTS(A3;2)="MB")\*1023)+1)
usw. in F

G2 : =LINKS(A2;LÄNGE(A2)-2)/(((RECHTS(A2;2)="KB")\*1023)+1)
G3 : =LINKS(A3;LÄNGE(A3)-2)/(((RECHTS(A3;2)="KB")\*1023)+1)
usw. in G

Zahlenformate der Zellen im gewählten Bereich:
A1:A10,B1:smiley:1,E1:E10,F1:G1
haben das Zahlenformat: Standard
B2:B10,D3:smiley:10
haben das Zahlenformat: #.##0" KB"
C2:C10
haben das Zahlenformat: #.##0,000" MB"
D2
haben das Zahlenformat: #.##0,000
F2:F10
haben das Zahlenformat: #.##0" KiB"
G2:G10
haben das Zahlenformat: #.##0,000" MiB"

Tabellendarstellung erreicht mit dem Code in FAQ:2363

Gruß
Reinhard

#6

Hallo Jens,

Excel ist zu physikalisch/technischen Multiplikatoren sowie zu Einheiten leider nicht in der Lage - vielleicht in den übernächsten Versionen …
Deshalb musst Du diese Interpretation selbst übernehmen oder programmieren, entweder als Text oder als Format-Ergänzung
Zum Zusammenzählen musst Du natlich dieselbe Einheit verwenden.

Ich empfehle Dir die Verwendung der „Originalzahl“ ohne Multiplikator, zur Kennzeichnung kannst Du zum Beispiel folgendes als Format verwenden :  *.**0 " B"
Diese entsprechende Spalte muss nicht sichtbar sein, also zum Beispiel ausgeblendet.

Falls Du eine Eingabezelle ( hier B3 ) haben möchtest, brauchst Du also eine Umrechnung in diese Schatten-Zelle :

WENN(ISTZAHL(B3);B3;
   WENN(ISTFEHLER(WERT(LINKS(B3;LÄNGE(B3)-2)));„Fehler“;
      WENN(KLEIN(RECHTS(B3;2))=„kb“;1024*WERT(LINKS(B3;LÄNGE(B3)-2));
         WENN(KLEIN(RECHTS(B3;2))=„mb“;1024*1024*WERT(LINKS(B3;LÄNGE(B3)-2));„Fehler“
))))

Ich hoffe, diese Formel ohne Druckfehler übertragen zu haben - Copy/Paste ist nämlich im AntwortFeld von w-w-w nicht vorgesehen - was ich bei jeder meiner Antworten erneut bemeckere …
Rückwärts geht’s aber :  Du kannst die obige Formel also trotz Mehrzeiligkeit direkt in Excel übernehmen

Mit Gruß,
                   Frank-Thomas

#7

Ich hoffe, diese Formel ohne Druckfehler übertragen zu haben -
Copy/Paste ist nämlich im AntwortFeld von w-w-w nicht
vorgesehen - was ich bei jeder meiner Antworten erneut
bemeckere …

Hallo Frank,

ich habe FF 21.0 auf Win XP, sehe da keine Probleme beim Einfügen.

=WENN(ISTZAHL(B3);B3;WENN(ISTFEHLER(WERT(LINKS(B3;LÄNGE(B3)-2)));„Fehler“;WENN(KLEIN(RECHTS(B3;2))=„kb“;1024*WERT(LINKS(B3;LÄNGE(B3)-2));WENN(KLEIN(RECHTS(B3;2))=„mb“;1024*1024*WERT(LINKS(B3;LÄNGE(B3)-2));„Fehler“))))

Gruß
Reinhard