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

excel函数:汉字转全拼

Yesterday, in the 30XL30D challenge, we got details on our operating environment, with the INFO function, and learned that it can no longer help with our memory issues. (Neither ours, nor Excel's!) For day 5 in the challenge, we'll examine the CHOOSE function.

昨天，在30XL30D挑战中，我们使用INFO函数获得了有关操作环境的详细信息，并了解到它不再可以解决内存问题。 (不是我们的，也不是Excel的！)在挑战的第5天，我们将研究CHOOSE函数。

Excel选择功能 (Excel CHOOSE Function)

The CHOOSE function, from the Lookup and Reference category, picks from a list of options, based on a number. In most cases though, it would be better to CHOOSE a different function, like INDEX and MATCH, or VLOOKUP. We'll investigate those functions later in the challenge.

从“查找”和“参考”类别的“选择”功能基于数字从选项列表中进行选择。不过，在大多数情况下，最好选择其他函数，例如INDEX和MATCH或VLOOKUP 。在挑战赛中，我们将研究这些功能。

So, let's take a look at the CHOOSE information and examples, and see what it can do, and what its weaknesses are. If you have other tips or examples, please share them in the comments.

因此，让我们看一下CHOOSE的信息和示例，看看它可以做什么，以及它的弱点是什么。如果您还有其他提示或示例，请在评论中分享。

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美元)。

功能05：选择 (Function 05: CHOOSE)

The CHOOSE function returns a value from a list, based on an index number.

CHOOSE函数基于索引号从列表中返回一个值。

您如何使用CHOOSE？ (How Could You Use CHOOSE?)

The CHOOSE function can return a specific numbered item from a list, such as:

CHOOSE函数可以从列表中返回特定编号的项目，例如：

For month number, return the fiscal quarter number
对于月份号，返回会计季度号
Based on starting date, calculate the next Monday
根据开始日期，计算下一个星期一
For store number, show a sum of sales
对于商店编号，请显示销售总额

选择语法 (CHOOSE Syntax)

The CHOOSE function has the following syntax:

CHOOSE函数具有以下语法：

- index_number must be between 1 and 254 (or 29 in Excel 2003 and earlier)
  index_number必须介于1到254之间(在Excel 2003及更早版本中必须为29)
- index_number can be typed in the function, or can be a formula or cell reference
  index_number可以在函数中键入，也可以是公式或单元格引用
- index_number fractions will be truncated to the lowest integer, before using
  index_number分数将被截断为最小整数，然后再使用
- value arguments can be numbers, cell references, defined names, formulas, functions, or text
  值参数可以是数字，单元格引用，定义的名称，公式，函数或文本
CHOOSE(index_num,value1,value2,...)
选择(index_num，value1，value2，...)

选择陷阱 (CHOOSE Traps)

In Excel 2003, and earlier versions, the CHOOSE function is limited to numbers between 1 and 29. Lookup lists are usually easier to manage on a worksheet, instead of having them typed in a formula. With VLOOKUP or MATCH functions, you can refer to a worksheet list.

在Excel 2003和更早版本中，CHOOSE函数仅限于1到29之间的数字。查找列表通常更容易在工作表上进行管理，而不是在公式中键入查找列表。使用VLOOKUP或MATCH函数，您可以参考工作表列表。

示例1：月份数字的财政季度 (Example 1: Fiscal Quarter for Month Number)

The CHOOSE function works well with a simple list of numbers as the values. For example, if cell B2 contains a month number, a CHOOSE formula can calculate the fiscal quarter for that month. In the example shown below, the fiscal year starts in July.

CHOOSE函数可以很好地将简单的数字列表用作值。例如，如果单元格B2包含月份数字，则CHOOSE公式可以计算该月份的会计季度。在下面的示例中，会计年度从7月开始。

There are 12 values listed in the formula, representing the months from 1 to 12. The fiscal year starts in July, so months 7, 8 and 9 are in quarter 1. In the table below, you can see the fiscal quarter below each month number. For the CHOOSE function, you'll enter the quarter value for each month, in the order that they appear in the table above. In the list of values for the CHOOSE function, items 7, 8 and 9 -- for July, August and September, have a value of 1.

公式中列出了12个值，代表1到12的月份。会计年度从7月开始，因此第7、8和9个月位于第1季度。在下表中，您可以看到每个月下面的会计季度数。对于CHOOSE功能，您将按照在上表中显示的顺序输入每个月的季度值。在CHOOSE函数的值列表中， 7、8、9月份的项目7、8和9的值为1。

=CHOOSE(C2,3,3,3,4,4,4,1,1,1,2,2,2)

=选择(C2,3,3,3,4,4,4，1,1,1，2,2,2)

Enter a month number in cell C2, and the Fiscal Quarter is calculated by the CHOOSE function, in cell C3

在单元格C2中输入月份号，并通过CHOOSE函数在单元格C3中计算财政季度

示例2：计算下周一的日期 (Example 2: Calculate Date of Next Monday)

The CHOOSE function can be combined with other functions, like WEEKDAY, to calculate upcoming dates. For example, if you're in a club that meets every Monday night, you can find next Monday's date, based on today's date.

CHOOSE函数可以与WEEKDAY等其他函数结合使用，以计算即将到来的日期。例如，如果您所在的俱乐部在每个星期一晚上开会，您可以根据今天的日期找到下一个星期一的日期。

In the table below, you can see the weekday number for each day. In column H, you can see the number of days to each weekday, to get to the next Monday. On Sunday, you would need to add 1 day. If it's Monday, there are 7 days till next Monday, and so on.

在下表中，您可以看到每天的工作日数。在H列中，您可以看到到下一个星期一的每个工作日的天数。在星期天，您需要增加1天。如果是星期一，那么到下周一还有7天，依此类推。

With the current date in cell C2, the formula in cell C3 uses the WEEKDAY and CHOOSE functions to calculate the next Monday.

对于单元格C2中的当前日期，单元格C3中的公式使用WEEKDAY和CHOOSE函数计算下一个星期一。

=C2+CHOOSE(WEEKDAY(C2),1,7,6,5,4,3,2)

= C2 + CHOOSE(WEEKDAY(C2)，1,7,6,5,4,3,2)

示例3：显示选定商店的销售总额 (Example 3: Show Sales Total for Selected Store)

You can use CHOOSE in formulas with other functions too, like SUM. In this example, we can get a total sales for a specific store, by entering its number in the CHOOSE function, and list the ranges to total for each store.

您也可以在公式中将CHOOSE与其他函数一起使用，例如SUM。在此示例中，我们可以通过在CHOOSE函数中输入特定商店的编号来获得其总销售额，并列出每个商店的合计范围。

In this example, the store number (101, 102 or 103) is entered in cell C2. To get the index_number value, as 1, 2 or 3, instead of 101, 102, or 103, you can use a formula: C2-100.

在此示例中，商店编号(101、102或103)输入到单元格C2中。要获取index_number值(1、1、2或3)，而不是101、102或103，可以使用公式： C2-100 。

The sales numbers for each store are in a separate column, as show below.

每个商店的销售数量在单独的列中，如下所示。

Inside the SUM function, the CHOOSE function will be evaluated first, and returns the correct range for the SUM, for the selected store.

在SUM函数中，将首先评估CHOOSE函数，并为所选商店返回SUM的正确范围。

=SUM(CHOOSE(C2-100,C7:C9,D7:D9,E7:E9))

= SUM(选择(C2-100，C7：C9，D7：D9，E7：E9))

This is example of a situation where other functions, like INDEX and MATCH, would be more efficient, and we'll see how they work, later in the challenge.

这是一种情况的示例，其中其他功能(例如INDEX和MATCH)将更加高效，我们将在挑战的后面看到它们的工作原理。

下载选择功能文件 (Download the CHOOSE Function File)

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

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