Excel轻松搞定多币种转换，一键汇总为单一币种

摘要：

在全球化商业环境或个人财务管理中,我们常常会遇到需要处理多种货币的情况，跨国公司的财务报表需要将不同子公司的收入（以不同货币计）统一转换为报告货币；个人投资者追踪海外资产组合时，也希望将不同市场的投资...

欧意交易所
全球三大交易所之一，注册领50 USDT数币盲盒！
注册链接 APP下载

在全球化商业环境或个人财务管理中,我们常常会遇到需要处理多种货币的情况，跨国公司的财务报表需要将不同子公司的收入（以不同货币计）统一转换为报告货币；个人投资者追踪海外资产组合时，也希望将不同市场的投资价值以本币呈现，Excel作为强大的数据处理工具，提供了多种方法来实现多种币种向单一币种的转换，本文将详细介绍几种实用方法，帮助您高效完成这一任务。

核心挑战：获取实时或准确的汇率数据

无论采用何种方法,汇率数据的准确性和时效性都是关键，您可以通过以下途径获取汇率：

1. 手动输入：适用于少量币种或固定汇率场景，但效率低且易出错。
2. 使用Excel内置的“获取与转换”功能（Power Query）：可以从网络数据源（如雅虎财经、欧洲央行等）获取实时或历史汇率。
3. 使用第三方插件或服务：一些专业的金融数据插件提供更全面的汇率数据。
4. 引用外部汇率表：如果有专门的汇率数据源，可以将其导入Excel。

使用VLOOKUP/XLOOKUP函数与汇率表（经典方法）

这是最常用且易于理解的方法,适用于已有汇率表或可以手动维护汇率的情况。

步骤：

1. 准备汇率表：

   在一个工作表中（汇率表”），列出“币种代码”和“对应人民币（或您的目标币种）汇率”。 | 币种代码 | 汇率 (兑CNY) | | :------- | :----------- | | USD | 7.2000 | | EUR | 7.8000 | | JPY | 0.0480 | | GBP | 9.1000 |

2. 录入原始数据：

   在另一个工作表中（数据汇总”），记录您的交易或资产数据，包括“币种”、“金额”等列。 | 交易ID | 币种 | 金额 | | :----- | :--- | :----- | | A001 | USD | 1000 | | B002 | EUR | 500 | | C003 | JPY | 100000 | | D004 | GBP | 200 |

3. 使用VLOOKUP或XLOOKUP函数进行转换：

   • 在“数据汇总”表中，新增一列“人民币金额”。
   • 假设“币种”在B列，“金额”在C列，从第2行开始，在D2单元格输入公式：
     • 使用VLOOKUP：

       =VLOOKUP(B2, 汇率表!A:B, 2, FALSE) * C2

       • B2：需要查找的币种代码。
       • 汇率表!A:B：汇率表中包含币种代码和汇率的区域。
       • 2：返回汇率表中第2列（汇率列）的数据。
       • FALSE：表示精确匹配。
     • 使用XLOOKUP（推荐，Excel 365/2021及更新版本）：

       =XLOOKUP(B2, 汇率表!A:A, 汇率表!B:B, 0) * C2

       • B2：查找值（币种代码）。
       • 汇率表!A:A：查找数组（币种代码列）。
       • 汇率表!B:B：返回数组（汇率列）。
       • 0：如果未找到，返回0（可根据需要调整）。
   • 将D2单元格的公式向下填充至所有需要转换的行。

4. 汇总单一币种总额：

   • 在“数据汇总”表的末尾，使用SUM函数对“人民币金额”列求和：

     =SUM(D2:D5)

使用Power Query获取汇率并转换（自动化程度高）

如果您需要频繁处理多币种数据,并且希望汇率能自动更新，Power Query是更好的选择。

步骤：

1. 获取汇率数据（以雅虎财经为例）：

   • 在Excel中,点击“数据”选项卡 -> “获取数据” -> “自其他来源” -> “自网络”。
   • 输入雅虎财经汇率数据的URL（获取USD/CNY汇率：https://query1.finance.yahoo.com/v8/finance/chart/USDCNY=X），注意：Yahoo Finance的API可能会变化，请使用当前可用的URL或数据源。
   • 加载后,Power Query会解析JSON数据，您需要提取最新的汇率值，这可能需要一些JSON解析技巧。
   • 将处理后的汇率数据（币种和汇率）加载为Excel连接表，例如命名为“汇率查询”。

2. 准备原始数据并加载到Power Query：

   • 将您的多币种原始数据（包含币种和金额）区域选中，点击“数据”->“从表格/区域”（确保数据有标题行）。
   • 这将打开Power Query编辑器。

3. 在Power Query中合并汇率数据：

   • 在Power Query编辑器中，确保您有“币种”列。
   • 点击“主页”选项卡->“合并查询”->“作为新查询”。
   • 选择您的原始数据表和之前加载的“汇率查询”表，匹配条件是基于“币种”列（确保两表列名一致或正确映射）。
   • 在合并对话框中,选择“左外”（保留所有原始数据行），然后展开“汇率查询”表中的“汇率”列。

4. 添加计算列进行转换：

   • 在合并后的查询中,点击“添加列”选项卡->“自定义列”。
   • 输入新列名,人民币金额”，公式为：=[金额] * [汇率]（假设原始金额列名为“金额”，汇率列名为“汇率”）。

5. 加载结果并汇总：

   • 点击“关闭并上载”，将处理后的数据加载到Excel工作表中。
   • 您已经有了转换后的单一币种金额列,使用SUM函数即可轻松汇总总额。

使用Excel的“货币”格式与公式结合（仅显示转换，不实际计算数值）

此方法主要用于显示目的,不改变实际存储的数值，但也可以结合公式实现简单转换。

1. 设置单元格格式：

   • 选中包含外币金额的单元格区域。
   • 右键点击 -> “设置单元格格式” -> “货币”。
   • 在“符号”下拉菜单中选择目标货币（如人民币¥），并选择合适的小数位数。
   • 注意：这只是格式化，单元格内存储的仍是原始外币数值。

2. 结合汇率手动输入或引用转换：

   • 在旁边的列,可以手动输入汇率，然后用公式（如=[外币金额]*[汇率]）计算目标币种金额，再对计算结果设置货币格式。
   • 这种方法不如前两种灵活,适用于少量且汇率固定的情况。

注意事项：

• 汇率时效性：务必关注汇率的更新频率，尤其是对于财务报表等正式用途，应使用指定日期的官方汇率或实时汇率。
• 汇率波动：如果数据跨越多个日期，且汇率波动较大，可能需要记录每笔交易的汇率进行转换。
• 币种代码：确保币种代码（如USD, EUR）的格式与汇率表中的格式一致，避免匹配失败。
• 错误处理：在使用VLOOKUP/XLOOKUP时，如果币种未找到，公式会返回错误值（如#N/A），可以使用IFERROR函数进行处理，=IFERROR(XLOOKUP(B2, 汇率表!A:A, 汇率表!B:B, 0) * C2, 0)，这样未匹配到的会显示为0。

Excel提供了从简单到复杂的多种多币种转换方案,对于一次性或少量数据，VLOOKUP/XLOOKUP结合汇率表简单有效；对于需要自动化、频繁更新汇率和复杂数据处理的场景，Power Query则是更优选择，选择适合您需求的方法，可以大大提高工作效率，轻松实现多种币种向单一币种的准确转换和汇总，希望本文介绍的方法能对您有所帮助！

币安交易所
币安交易所是国际领先的数字货币交易平台，低手续费与BNB空投福利不断！
注册链接 APP下载