excel函数:汉字转全拼_30天中30个Excel函数：06

excel函数:汉字转全拼

Yesterday, in the 30XL30D challenge, we picked an item from a list with the CHOOSE function, and learned that other functions might be a better MATCH when doing a LOOKUP. For day 6 in the challenge, we'll examine the FIXED function, which formats a number with decimals and commas, and returns the result as text.

昨天，在30XL30D挑战赛中，我们从一个带有CHOOSE功能的列表中选择了一个项目，并了解到其他功能在执行LOOKUP时可能是更好的MATCH。对于挑战的第6天，我们将检查FIXED函数，该函数会格式化带有小数和逗号的数字，并以文本形式返回结果。

That's not too surprising, I guess, because the FIXED function is from the Text category. But is the FIXED function worth using? You can judge for yourself in the examples shown below.

我猜这并不奇怪，因为FIXED函数来自Text类别。但是FIXED函数值得使用吗？您可以在以下示例中自行判断。

NOTE: You can have all of the 30 Functions content in an easy-to-use single reference file -- the 30 Excel Functions in 30 Days eBook Kit ($10).

注意：您可以在一个易于使用的单个参考文件中获得这30个函数的全部内容-30天电子书工具包中的30个Excel函数 (10美元)。

So, let's take a look at the FIXED information and example, and if you have other tips or examples, please share them in the comments. Maybe I'm overlooking some awesome power that this function has, and this blog post needs to be FIXED!

因此，让我们看一下FIXED信息和示例，如果还有其他提示或示例，请在评论中分享。也许我忽略了此功能的强大功能，并且此博文需要修复！

功能06：固定 (Function 06: FIXED)

The FIXED function rounds a number to a specified number of decimals, with or without comma separators, and returns the formatted result as text.

FIXED函数将数字四舍五入为指定的小数位数(带或不带逗号分隔符)，并将格式化后的结果作为文本返回。

您如何使用固定的？ (How Could You Use FIXED?)

The FIXED function can change numbers to text, formatted with a set number of decimals. This could help in limited situations, such as:

FIXED函数可以将数字更改为文本，并以设置的小数位数格式设置。这在某些情况下可能会有所帮助，例如：

Matching numbers imported as text from another application
匹配作为文本从另一个应用程序导入的数字
formatting numbers in a text string
格式化文本字符串中的数字

固定语法 (FIXED Syntax)

The FIXED function has the following syntax:

FIXED函数具有以下语法：

- Number is the number you want to round and convert to text.
  数字是您要四舍五入并转换为文本的数字。
- Decimals is the number of digits to the right of the decimal point.
  小数是小数点右边的位数。
- If omitted, decimals defaults to 2
  如果省略，则小数默认为2
- If negative, decimals round to the left of decimal point
  如果为负，则小数点将舍入到小数点的左侧
- If no_commas is FALSE or omitted, the result includes commas as usual
  如果no_commas为FALSE或省略，则结果将照常包含逗号
- If no_commas is TRUE, commas are not included in the result.
  如果no_commas为TRUE，则逗号不包含在结果中。
FIXED(number,decimals,no_commas)
已修正(数字，小数，无逗号)

固定陷阱 (FIXED Traps)

The FIXED function changes a number to text, so don't use it anywhere that you want to keep numbers as numbers. For example, if you want to limit the number of decimal places for a chart's data, use number formatting in the cells, or use the ROUND function instead.

FIXED函数将数字更改为文本，因此不要在要保留数字作为数字的任何地方使用它。例如，如果要限制图表数据的小数位数，请在单元格中使用数字格式，或者改为使用ROUND函数。

示例1：格式舍入数字 (Example 1: Format Rounded Number)

The FIXED function lets you take a number, round it to a specific number of decimals, add commas, and return the result as text. For example, with the number 32187.5689231 in cell C2, you could use the FIXED function to round it to 2 decimal places, and include a comma.

FIXED函数使您可以取一个数字，将其四舍五入为特定的小数位数，添加逗号，然后以文本形式返回结果。例如，对于单元格C2中的数字32187.5689231，您可以使用FIXED函数将其舍入到小数点后两位，并包含一个逗号。

=FIXED(C2,2,FALSE)

= FIXED(C2,2，FALSE)

Instead of using FIXED, you could simply format the cell as Number format, with 2 decimal places, and a comma. That would leave the cell value unchanged -- only its appearance would be different.

除了使用FIXED，您还可以简单地将单元格设置为Number格式，带有2个小数位和一个逗号。这将使单元格值保持不变-只是其外观会有所不同。

Or, you could use the ROUND function, combined with cell formatting, to return a number that is rounded to two decimal places, and has a comma separator.

或者，您可以将ROUND函数与单元格格式结合使用，以返回四舍五入到小数点后两位并带有逗号分隔符的数字。

=ROUND(C2,2)

=圆(C2,2)

Finally, if you really want the result as text, use the TEXT function. Its formatting options are much more flexible, and if you need rounding to the left, you can combine it with the ROUND function.

最后，如果您确实希望将结果作为文本，请使用TEXT函数。它的格式化选项更加灵活，如果需要向左舍入，可以将其与ROUND函数结合使用。

=TEXT(C2,"#,###.00?)

= TEXT(C2，“＃，###。00？)

示例2：向左舍入 (Example 2: Round Number to the Left)

With the FIXED function, you can also round to the left of the decimal point, by using a negative number for the decimal places argument. To remove commas, use TRUE in the no_commas argument. Again, the result is text.

使用FIXED函数，还可以通过对小数位数参数使用负数来舍入到小数点左边。要删除逗号，请在no_commas参数中使用TRUE。同样，结果是文本。

=FIXED(C2,-2,TRUE)

= FIXED(C2，-2，TRUE)

Or, you could use the ROUND function, to return a number that is rounded to the left.

或者，您可以使用ROUND函数返回一个舍入到左侧的数字。

=ROUND(C2,-2)

=圆(C2，-2)

示例3：将数字显示为数千美元 (Example 3: Show Number as Thousands of Dollars)

The FIXED function has limited formatting options, and only lets you control the number of decimal places, and the commas. You could use FIXED to round a number to the thousands, but can't add a dollar sign, or hide the zeros.

FIXED函数具有有限的格式设置选项，仅允许您控制小数位数和逗号。您可以使用FIXED将数字四舍五入到数千，但不能添加美元符号或隐藏零。

=FIXED(C2,-3)

=固定(C2，-3)

Instead of using FIXED, you could format the cell with a Custom Number format of $#, that would leave the cell value unchanged -- only its appearance would be different.

可以使用“ $＃”的“自定义数字”格式来格式化单元格，而不必使用FIXED，这将使单元格值保持不变-只是其外观会有所不同。

Or, for a text result, use the TEXT function, which lets you format the number as thousands of dollars.

或者，对于文本结果，请使用TEXT函数，该函数可将数字格式设置为数千美元。

=TEXT(C3,"$#,")

= TEXT(C3，“ $＃，”)

下载FIXED功能文件 (Download the FIXED Function File)

To see the formulas used in today's examples, you can download the FIXED function sample workbook. The file is zipped, and is in Excel 2007 file format.

要查看当今示例中使用的公式，可以下载FIXED函数示例工作簿 。该文件已压缩，并且为Excel 2007文件格式。