# 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.

### 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.

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.

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).

### 功能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.

### 示例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.

=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

### 示例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.

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

=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.

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.

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(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.

### 下载选择功能文件 (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.

### 观看选择视频 (Watch the CHOOSE Video)

To see a demonstration of the examples in the CHOOSE function sample workbook, you can watch this short Excel video tutorial.

excel函数:汉字转全拼

• 0
点赞
• 0
评论
• 0
收藏
• 扫一扫，分享海报

11-11 700
11-08 793
©️2022 CSDN 皮肤主题：编程工作室 设计师：CSDN官方博客

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、C币套餐、付费专栏及课程。