PDA

Vollständige Version anzeigen : Verschiedene Währungen umrechnen


Mina
21.07.2014, 16:09
Hallo,

ich habe eine Frage. Ich habe eine Excel-Liste die Rechnungsbeträge in verschiedenen Währungen beinhaltet (SEK,GBP,USD;EUR,CHF und so weiter). Die Rechnungsbeträge sind in der Originalwährung (also unterschiedlich in USD,SEK,EUR usw.) in einer Spalte und sollen in einer weiteren 1. in CHF und 2. in USD umgerechnet werden. Ich habe mich schon an WENN-Abfragen versucht... Bin aber leider nicht wirkich ein Profi :) Ich habe bereits ein extra Arbeitsblatt mit den aktuellen Umrechnungskursen angelegt auf die sich die Formel dann beziehen könnte. Daher klappt so rein gar nichts! Kann mir bitte jemand behilflich sein? Ich habe noch einen Screenshot von meiner Tabelle erstellt.

Hajo_Zi
21.07.2014, 16:12
Du bist im falsche Forum, dies ist ein Excel Forum. Da lädt man Excel Dateien hoch.

<img src="http://Hajo-Excel.de/images/grusz1.gif" align="middle" height="40" alt="Grußformel"><a href="http://Hajo-Excel.de/index.htm"><img border="0" src="http://Hajo-Excel.de/images/logo_hajo3.gif" align="middle" height="40" alt="Homepage"></a>

GMG-CC
21.07.2014, 19:51
Moin,

dann stelle doch einfach einmal das Blatt mit den Umrechnungskursen als Excel-Datei hier ein, dann können wir dir auch helfen.

Mina
22.07.2014, 08:01
Hallo, vielen Dank für die Antworten! Ich bin auch kein Forum-Profi, daher tut es mir leid, dass ich ein falsches Thema eröffnet habe. Im Anhang habe ich nun die Excel-Datei. Vielen Dank für eure Unterstützung im Voraus!

Mc Santa
22.07.2014, 08:12
Hallo,

deine Umrechnungstabelle sollte wie folgt aussehen (Reihenfolge der Währungen egal), damit man richtig damit arbeiten kann:
<br/><b><em>Currency Exchange</em></b><table border="1" cellspacing="0" cellpadding="0" style="border-color:#000000; border-width: 1px; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "><colgroup><col style="font-weight:bold; width:40px;" /><col style="width:97px;" /><col style="width:97px;" /><col style="width:97px;" /><col style="width:97px;" /></colgroup><tr style="background-color:#99CCFF; text-align:center; font-weight:bold; "><td>&nbsp;</td><td>A</td><td>B</td><td>C</td><td>D</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">1</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; "><b>Date:</b></td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; "><b>'21.07.2014</b></td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">&nbsp;</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">&nbsp;</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">2</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">&nbsp;</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">&nbsp;</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">&nbsp;</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">&nbsp;</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">3</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#33CCCC ;; text-align:left; "><b>Currency</b></td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#33CCCC ;; text-align:right; ">&nbsp;</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#33CCCC ;; text-align:center; "><b>USD</b></td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#33CCCC ;; text-align:center; "><b>CHF</b></td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">4</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; ">USD</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; "><b>1</b></td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; "><b>1</b></td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; "><b>0,898125611848073</b></td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">5</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; ">CHF</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; "><b>1</b></td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; "><b>1,11343</b></td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; "><b>1</b></td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">6</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; ">EUR</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; "><b>1</b></td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; "><b>1,35279</b></td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; "><b>1,21519</b></td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">7</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; ">TRY</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; "><b>1</b></td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; "><b>0,47101</b></td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; "><b>0,42351</b></td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">8</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; ">SEK</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; "><b>1</b></td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; "><b>0,14627</b></td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; "><b>0,13152</b></td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">9</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; ">GBP</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; "><b>1</b></td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; "><b>1,70932</b></td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; "><b>1,53594</b></td></tr></table>

Anschließend kannst du in deiner Tabelle folgende Formeln verwenden:
<br/><b><em>All AM Invoices 2014</em></b><table border="1" cellspacing="0" cellpadding="0" style="border-color:#000000; border-width: 1px; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "><colgroup><col style="font-weight:bold; width:40px;" /><col style="width:60px;" /><col style="width:134px;" /><col style="width:133px;" /><col style="width:134px;" /></colgroup><tr style="background-color:#99CCFF; text-align:center; font-weight:bold; "><td>&nbsp;</td><td>A</td><td>B</td><td>C</td><td>D</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">3</td><td style=" color:#000000; border-color:#000000; background-color:#969696 ;; text-align:left; "><b>Curr</b></td><td style=" color:#000000; border-color:#000000; background-color:#969696 ;; text-align:left; "><b>Sub Total Curr</b></td><td style=" color:#000000; border-color:#000000; background-color:#969696 ;; text-align:left; "><b>Sub Total CHF</b></td><td style=" color:#000000; border-color:#000000; background-color:#969696 ;; text-align:left; "><b>Sub Total USD</b></td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">4</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">EUR</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; "><b>728</b></td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; "><b>984,83112</b></td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; "><b>884,65832</b></td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">5</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">EUR</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; "><b>5506</b></td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; "><b>7448,46174</b></td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; "><b>6690,83614</b></td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">6</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">EUR</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; "><b>669,37</b></td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; "><b>905,5170423</b></td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; "><b>813,4117303</b></td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">7</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">EUR</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; "><b>267,75</b></td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; "><b>362,2095225</b></td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; "><b>325,3671225</b></td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">8</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">EUR</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; "><b>1756,87</b></td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; "><b>2376,6761673</b></td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; "><b>2134,9308553</b></td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">9</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">EUR</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; "><b>932,25</b></td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; "><b>1261,1384775</b></td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; "><b>1132,8608775</b></td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">10</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">EUR</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; "><b>669,37</b></td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; "><b>905,5170423</b></td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; "><b>813,4117303</b></td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">11</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">EUR</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; "><b>1892,25</b></td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; "><b>2559,8168775</b></td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; "><b>2299,4432775</b></td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">12</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">EUR</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; "><b>13656,52</b></td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; "><b>18474,4036908</b></td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; "><b>16595,2665388</b></td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">13</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">EUR</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; "><b>1740</b></td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; "><b>2353,8546</b></td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; "><b>2114,4306</b></td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">14</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">EUR</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; "><b>2320</b></td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; "><b>3138,4728</b></td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; "><b>2819,2408</b></td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">15</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">EUR</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; "><b>28968,19</b></td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; "><b>39187,8777501</b></td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; "><b>35201,8548061</b></td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">16</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">SEK</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; "><b>14075</b></td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; "><b>2058,75025</b></td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; "><b>1851,144</b></td></tr></table><br/><table border="1" cellspacing="0" cellpadding="0" style="border-color:#000000; border-width: 1px;font-size:11pt; background-color:#ffffff; width:800px;padding-left:2pt; padding-right:2pt; "><tr style="background-color:#FFCC66; text-align:center; font-weight:bold; "><td colspan="3" > verwendete Formeln </td><td> </td></tr><tr valign="top" style="background-color:#FFCC66; text-align:center; font-weight:bold; "><td> Zelle </td><td> Formel</td><td> Bereich </td> <td>N/A</td></tr><tr><td>C4:C16</td><td>=$B4*SVERWEIS($A4;'Currency Exchange'!$A$4:$D$9;3;0)</td><td>&nbsp;</td><td>&nbsp;</td></tr><tr><td>D4:&nbsp;D16</td><td>=$B4*SVERWEIS($A4;'Currency Exchange'!$A$4:$D$9;4;0)</td><td>&nbsp;</td><td>&nbsp;</td></tr></table><table cellspacing="0" cellpadding="0"><tr style="text-align:left; font-weight:bold; " class="style21"><td style="text-align:left; font-size: xx-small" ><a href="http://excel-inn.de/dateien/vba_beispiele/tabellenanzeige_in_html_addin.zip" >Excel-Inn.de</a></td></tr> <tr style="text-align:left; font-weight:bold; " class="style21"><td style="text-align:left; font-size: xx-small" ><a href='http://Hajo-Excel.de/tools.htm' >Hajo-Excel.de</a></td></tr><tr style="text-align:left; font-weight:bold;" ><td style="text-align:left; font-size: xx-small" >XHTML-Tabelle zur Darstellung in Foren, einschl. der neuen Funktionen ab Version 2007</td></tr><tr style="text-align:left; font-weight:bold; " ><td style="text-align:left; font-size: xx-small" > Add-In-Version 18.01 einschl. 64 Bit</td></tr></table><br/>

Hilft dir das weiter?

VG

Mina
22.07.2014, 08:47
Danke, soweit war ich auch schon. Allerdings möchte ich das Excel eine Prüfung macht welche die Spalte A identifiziert, also je nach Originalwährung (CHF,SEK,GBP) die Umrechnung in CHF und USD automatisch macht. Manuell eingeben ist auf Dauer keine Lösung, da die Datei im Schnitt 500 Rechnungen beinhaltet :)

Mc Santa
22.07.2014, 08:50
Hallo,

meine Formel prüft automatisch nach Spalte A und erkennt die Währung. Gebe die Formel in die obere Zelle und und kopiere sie nach unten.
Du hast zwei unterschiedliche Formeln, da du einmal nach USD und einmal nach CHF umrechnest.

VG

Mina
22.07.2014, 10:26
Hi! Müssen die Zellen in Spalte A eine bestimmte Formatierung erhalten? Ich bekomme nur eine Fehlermeldung bei der $A4 markiert ist. Vielen Dank für deine Hilfe!!!!

Mc Santa
22.07.2014, 11:04
Hallo,

gehe einmal auf Formeln > Namensmanager und lösche den Namen "Eur" und versuche es dann erneut.

VG

Anbei auch meine Beispielmappe

Mina
22.07.2014, 13:08
1000 Dank! :) Du hast mir wirklich meinen Tag gerettet! Unglaublich! Bin mega froh!