根据条件格式化货币 [英] Format currency based on condition

问题描述

对Google Apps脚本不熟悉，但具有一定的VBA编码体验。

我希望能够根据不同的数据格式来决定单元格的数字格式单元格输入。

例如

• 列A - 客户名称（已验证的范围）


• 上限B - 货币类型（英镑，美元，欧元 - 经过验证的清单）

根据Col B中的选择，它需要将col C的格式设置为：

£100.00或

$ 100.00或

€100.00。

应该这样做，即Google表格底部显示的autosum功能仍然可以添加值，或者如果使用sumif函数将导致美元，英镑和欧元的总数是正确的。 I.E.它不能是一个基于文本的解决方案，或者至少不是一个这不是结果的解决方案。 尝试使用

通过这种方式，您可以隐藏列中的条目并显示带有格式的文本。总和可以用于更多的公式。

还有一个简单的ArrayFormula：
= ArrayFormula（TEXT（C2，VLOOKUP（OFFSET（B2 ,,, COUNTA（B2：B）），H：I，2,0）&0.00））

粘贴到D2，它会自动扩展。

New to Google Apps Script, but have some VBA coding experience.

I am looking to be able to have the numeric format of a cell decided based on a different cell input.

eg.

• Col A - Client Name (Validated range)
• Col B - Currency Type (GBP, USD, EUR - validated list)
• Col C - Fee (100 - free type by user)

Based on the selection in Col B it needs to put the format in col C as either:

£100.00 or
$100.00 or
€100.00.

This should be done in such a way that the "autosum" function displayed on the bottom of google sheet is still able to add the values, or if using a sumif function will result in the USD, GBP and EUR totals being correct. I.E. it cannot be a text based solution or at least not one where this is not the result.

解决方案

Try using onEdit trigger, and set custom number format.

Here's code sampe for currency formats:

function customNumberFormat() {

  var SS = SpreadsheetApp.getActiveSpreadsheet();
  var ss = SS.getSheetByName('Sheet1');

  var range1 = ss.getRange("C2");
  var range2 = ss.getRange("C3");
  var range3 = ss.getRange("C4");

  var format1 = "£ 00.00"
  var format2 = "$ 00.00"
  var format3 = "€ 00.00"

  range1.setNumberFormat(format1);
  range2.setNumberFormat(format2);
  range3.setNumberFormat(format3);

}

Make script to check current entry in column with sum and format it in proper currency.

---

But text solution with formula looks better for me:

This way you are hiding the entries in column with sum, and show text with format. Sums can be used in further formulas.

There's also simple ArrayFormula: =ArrayFormula(TEXT(C2,VLOOKUP(OFFSET(B2,,,COUNTA(B2:B)),H:I,2,0)&" 0.00"))

If you paste it in D2, it'll expand automatically.

这篇关于根据条件格式化货币的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！