Google表格公式可将货币从€或£提取并转换为USD [英] Google Sheets Formula to Extract and Convert Currency from € or £ to USD

问题描述

我正在尝试执行以下操作:

I'm trying to do the following:

1. 检查单元格中的N/A或No;如果具有以上任何一个，则应输出N/A或No
2. 检查单元格中的£或€或Yes；如果它具有其中之一，则将继续执行步骤3.如果它具有$，则应重复与输出相同的输入.
3. 使用REGEXEXTRACT(A1, "\$\d+")或REGEXEXTRACT(A1, "\£\d+")从单元格中提取货币(我认为这是最好的方法)
4. 使用GoogleFinance("CURRENCY:EURUSD")或GoogleFinance("CURRENCY:GBPUSD")
将其转换为$ USD
5. 输出原始单元格，但将步骤3中提取的货币替换为步骤4中的输出.

1. Check the cell for N/A or No; if it has either of these then it should output N/A or No
2. Check the cell for either £ or € or Yes; If it has one of these then it would continue to step 3. If it has $ then it should repeat the same input as the output.
3. Extract currency from cell using: REGEXEXTRACT(A1, "\$\d+") or REGEXEXTRACT(A1, "\£\d+") (I assume that's the best way)
4. Convert it to $ USD using GoogleFinance("CURRENCY:EURUSD") or GoogleFinance("CURRENCY:GBPUSD")
5. Output the original cell but replacing the extracted currency from step 3 with the output from step 4.

示例:(原始->输出)

• N/A-> N/A
• No-> No
• Alt-> Alt
• Yes-> Yes
• Yes £10-> Yes $12.19
• Yes £10 per week-> Yes $12.19 per week
• Yes €5 (Next)-> Yes $5.49 (Next)
• Yes $5 22 EA-> Yes $5 22 EA
• Yes £5 - £10-> Yes $5.49 - $12.19

Examples: (Original --> Output)

• N/A --> N/A
• No --> No
• Alt --> Alt
• Yes --> Yes
• Yes £10 --> Yes $12.19
• Yes £10 per week --> Yes $12.19 per week
• Yes €5 (Next) --> Yes $5.49 (Next)
• Yes $5 22 EA --> Yes $5 22 EA
• Yes £5 - £10 --> Yes $5.49 - $12.19

我无法使用有效的IF语句，我可以用普通代码执行此操作，但无法针对电子表格公式进行计算.

I am unable to get a working IF statement working, I could do this in normal code but can't work it out for spreadsheet formulas.

我已经尝试过多次修改@Rubén的答案以包含N/A，因为这不是工作表错误，我也尝试过相同的方法来使任何美元输入都以美元的形式出现(没有变化)，但我确实可以做到.在Excel/Google表格中无法理解IF/OR/AND.

I've tried modifying @Rubén's answer lots of times to including the N/A as it's not the Sheets error, I also tried the same for making any USD inputs come out as USD (no changes) but I really can't get the hang of IF/OR/AND in Excel/Google Sheets.

=ArrayFormula(
  SUBSTITUTE(
   A1,
   OR(IF(A1="No","No",REGEXEXTRACT(A1, "[\£|\€]\d+")),IF(A1="N/A","N/A",REGEXEXTRACT(A1, "[\£|\€]\d+"))),
   IF(
    A1="No",
    "No",
    TEXT(
     REGEXEXTRACT(A1, "[\£|\€](\d+)")*
     IF(
      "€"=REGEXEXTRACT(A1, "([\£|\€])\d+"),
      GoogleFinance("CURRENCY:EURUSD"),
      GoogleFinance("CURRENCY:GBPUSD")
     ),
   "$###,###"
   ) 
  )
 )
)

上面，我尝试在第一个IF语句之前添加OR()尝试并包括N/A作为选项，在下面我尝试了它，如下面所示，您可以通过各种不同的方式看到(将第4行替换为这个)

The above, I tried to add an OR() before the first IF statement to try and include N/A as an option, in the below I tried it as you can see below in various different ways (replace line 4 with this)

IF(
  OR(
    A1="No",
    "No",
    REGEXEXTRACT(A1, "[\£|\€]\d+");
    A1="No",
    "No",
    REGEXEXTRACT(A1, "[\£|\€]\d+")
  )
)

但这也不起作用.我以为使用;是分隔OR表达式的一种方法，但显然不是.

But that doesn't work either. I thought using ; was a way to separate the OR expressions but apparently not.

我已将其修改为= ArrayFormula( IF(NOT(ISBLANK(A2))，

I've modified it to =ArrayFormula( IF(NOT(ISBLANK(A2)),

IF(IFERROR(SEARCH("$",A2),0),A2,IF(A2="N/A","N/A",IF(A2="No","No",IF(A2="Alt","Alt",IF(A2="Yes","Yes",
    SUBSTITUTE(
      A2,
      REGEXEXTRACT(A2, "[\£|\€]\d+"),
      TEXT(
        REGEXEXTRACT(A2, "[\£|\€](\d+)")
        *
        VLOOKUP(
          REGEXEXTRACT(A2, "([\£|\€])\d+"),
            {
             {"£";"€"},
               {GoogleFinance("CURRENCY:GBPUSD");GoogleFinance("CURRENCY:EURUSD")}
            },
        2,0),
        "$###,###"
      )
    )
  )))))
,"")
 )

此修复程序:

• 空白单元格不再抛出#N/A
• Yes只有单元格不再抛出#N/A
• 添加了另一个文本值Alt
• 将货币格式更改为0个小数位，而不是我最初要求的2个小数位.
• Blank cells no longer throw #N/A
• Yes only cells no longer throw #N/A
• Added another text value Alt
• Changes the format of the currency to 0 decimal places rather than my original request of 2 decimal places.

正如您在图像下面看到的那样，由于我从未想过这种情况，所以两个红色单元格不太正确，两个值中的第二个仍停留在其输入形式中，而未转换为USD.

As you can see in the image below the two red cells aren't quite correct as I never thought of this scenario, the second of the two values is staying in it's input form and not being converted to USD.

推荐答案

直接回答

尝试

=ArrayFormula(
  IF(IFERROR(SEARCH("$",A1:A6),0),A1:A6,IF(A1:A6="N/A","N/A",IF(A1:A6="No","No",
    SUBSTITUTE(
      A1:A6,
      REGEXEXTRACT(A1:A6, "[\£|\€]\d+"),
      TEXT(
        REGEXEXTRACT(A1:A6, "[\£|\€](\d+)")
        *
        VLOOKUP(
          REGEXEXTRACT(A1:A6, "([\£|\€])\d+"),
            {
             {"£";"€"},
               {GoogleFinance("CURRENCY:GBPUSD");GoogleFinance("CURRENCY:EURUSD")}
            },
        2,0),
        "$###,###.00"
      )
    )
  )))
 )

结果

+---+------------------+---------------------+
|   |        A         |          B          |
+---+------------------+---------------------+
| 1 | N/A             | N/A                |
| 2 | No               | No                  |
| 3 | Yes £10          | Yes $12.19          |
| 4 | Yes £10 per week | Yes $12.19 per week |
| 5 | Yes €5 (Next)    | Yes $5.49 (Next)    |
+---+------------------+---------------------+

说明

OR函数

或者使用OR函数，以上公式使用嵌套的IF函数.

Explanation

OR function

Instead or using OR function, the above formula use nested IF functions.

不是对每个货币符号使用REGEXEXTRACT函数，而是使用了regex OR运算符.例子

Instead of using a REGEXEXTRACT function for each currency symbol, a regex OR operator was used. Example

REGEXEXTRACT(A1:A6, "[\£|\€]\d+")

使用了三个正则表达式，

Three regular expressions were used,

• 获取货币符号和金额[\£|\€]\d+
• 获取金额[\£|\€](\d+)
• 获取货币符号[(\£|\€])\d+
• get currency symbol and the amount [\£|\€]\d+
• get the amount [\£|\€](\d+)
• get the currency symbol [(\£|\€])\d+

使用VLOOKUP和数组代替使用嵌套的IF来处理货币转换率.假设将来可能会添加更多货币，则可以更轻松地维护公式.

Instead of using nested IF to handle currency conversion rates, VLOOKUP and array is used. This could be make easier to maintain the formula assuming that more currencies could be added in the future.

这篇关于Google表格公式可将货币从€或£提取并转换为USD的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！