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

excel函数:汉字转全拼

Yesterday, in the 30XL30D challenge, we found text strings with the FIND function, and learned that it is case sensitive, unlike the SEARCH function. 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).

昨天，在30XL30D挑战中，我们找到了具有FIND函数的文本字符串，并了解到它与SEARCH函数不同，区分大小写。注意：您可以在一个易于使用的单个参考文件中获得这30个函数的全部内容-30天电子书工具包中的30个Excel函数 (10美元)。

索引功能 (INDEX Function)

For day 24 in the challenge, we'll examine the INDEX function. Based on a row and column number, it can return a value or reference to a value. We've already used INDEX several times, with other functions in the challenge:

在挑战的第24天，我们将检查INDEX函数。基于行号和列号，它可以返回值或对值的引用。我们已经使用INDEX几次，挑战中还使用了其他功能：

with EXACT to find name for the password with exact match
用EXACT 查找完全匹配的密码的名称
with AREAS to find the last area in a named range
与AREAS 查找指定范围内的最后一个区域
with COLUMNS to calculate sum of last column in range
用COLUMNS计算范围内最后一列的总和
with MATCH to find the name for closest guess in a contest
与MATCH查找比赛中最接近的猜测的名称

So, let's take a look at the INDEX information and examples, and if you have other tips or examples, please share them in the comments.

因此，让我们看一下INDEX信息和示例，如果还有其他提示或示例，请在评论中分享。

功能24：INDEX (Function 24: INDEX)

The INDEX function returns a value or reference to a value. Combine it with other functions, like MATCH, for powerful formulas.

INDEX函数返回一个值或对值的引用。将其与其他功能(例如MATCH)结合使用，可获得强大的公式。

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

The INDEX function can return a value or reference to a value, so you can use it to:

INDEX函数可以返回值或对值的引用，因此可以将其用于：

Find sales amount for selected month
查找选定月份的销售额
Get reference to specified row, column, area
获取对指定行，列，区域的引用
Create a dynamic range based on count
根据计数创建动态范围
Sort column of text in alphabetical order
按字母顺序对文本列进行排序

INDEX语法 (INDEX Syntax)

The INDEX function has two syntax forms -- Array and Reference. With Array form, a value is returned, and with Reference form, a reference is returned. The Array form has the following syntax:

INDEX函数具有两种语法形式-数组和引用。对于数组形式，将返回一个值，对于引用形式，将返回一个引用。数组形式具有以下语法：

- array is an array constant or range of cells
  数组是数组常数或单元格范围
- if array has only 1 row or column, corresponding row/column number argument is optional
  如果数组只有1行或1列，则相应的行/列号参数是可选的
- if array has >1 row or column, and only row_num or column_num is used, array of entire row or column is returned
  如果数组的行或列> 1，并且仅使用row_num或column_num，则返回整个行或列的数组
- row_num – if omitted, column_num is required
  row_num –如果省略，则为column_num
- column_num – if omitted, row_num is required
  column_num –如果省略，则必须为row_num
- if both the row_num and column_num arguments are used, returns value in cell at intersection of row_num and column_num
  如果同时使用row_num和column_num参数，则在row_num和column_num的交集处返回单元格中的值
- if row_num or column_num are zero, returns array of values for entire column or row
  如果row_num或column_num为零，则返回整个列或行的值数组
INDEX(array,row_num,column_num)
INDEX ( 数组，row_num，column_num )

The Reference form has the following syntax:

参考表单具有以下语法：

- reference can refer to one or more cell ranges – enclose nonadjacent ranges in parantheses
  引用可以引用一个或多个单元格范围–用括号括住不相邻的范围
- if each area in reference has only 1 row or column, corresponding row/column number argument is optional
  如果参考中的每个区域只有1行或1列，则相应的行/列号参数是可选的
- area_num selects range in reference from which to return row and column intersection
  area_num选择参考中要返回的行和列交点的范围
- area_num – if omitted, area 1 is used
  area_num –如果省略，则使用区域1
- if row_num or column_num are zero, returns reference for entire column or row
  如果row_num或column_num为零，则返回整个列或行的引用
- result is a reference, and can be used by other functions
  结果是参考，可以供其他功能使用
INDEX(reference,row_num,column_num,area_num)
INDEX(reference ，row_num，column_num，area_num )

索引陷阱 (INDEX Traps)

If the row_num and column_num don't point to a cell within the array or reference, the INDEX function returns a #REF! error.

如果row_num和column_num没有指向数组或引用中的单元格，则INDEX函数将返回#REF！错误。

示例1：查找选定月份的销售额 (Example 1: Find sales amount for selected month)

Enter a row number, and the INDEX function returns the sales amount from that row in the reference. Here the month number is 4, so the April sales amount is returned.

输入一个行号，然后INDEX函数返回参考中该行的销售额。这里的月数是4，因此返回了4月的销售金额。

=INDEX($C$2:$C$8,F2)

= INDEX($ C $ 2：$ C $ 8，F2)

To make the formula more flexible, you could use MATCH to return the row number, based on the month that was selected from a drop down list.

为了使公式更加灵活，您可以使用MATCH根据从下拉列表中选择的月份返回行号。

=INDEX($C$2:$C$8,MATCH($F$2,$D$2:$D$8,0))

= INDEX($ C $ 2：$ C $ 8，MATCH($ F $ 2，$ D $ 2：$ D $ 8,0))

示例2：获取对指定行，列，区域的引用 (Example 2: Get reference to specified row, column, area)

In this example, there is a named range, MonthAmts, which consists of 3 non-contiguous ranges. The MonthAmts range has 3 areas -- one for each month -- and there are 4 rows and 2 columns in each area. Here is the formula for the MonthAmts name:

在此示例中，有一个命名范围MonthAmts，它由3个非连续范围组成。 MonthAmts范围有3个区域(每个月一个)，每个区域有4行2列。这是MonthAmts名称的公式：

='Ex02'!$B$3:$C$6,'Ex02'!$E$3:$F$6,'Ex02'!$H$3:$I$6

='Ex02'！$ B $ 3：$ C $ 6，'Ex02'！$ E $ 3：$ F $ 6，'Ex02'！$ H $ 3：$ I $ 6

With the INDEX function, you can return the cost or revenue amount for a specific region and month.

使用INDEX功能，您可以返回特定区域和月份的成本或收入金额。

=INDEX(MonthAmts,B10,C10,D10)

= INDEX(MonthAmts，B10，C10，D10)

The INDEX function result can be multiplied, as in the Tax calculation in cell F10:

可以将INDEX函数的结果相乘，如单元格F10中的计税：

=0.05*INDEX(MonthAmts,B10,C10,D10)

= 0.05 * INDEX(MonthAmts，B10，C10，D10)

or, it can return a reference for the CELL function, to show the address of the result, in cell G10.

或者，它可以返回单元格G10中CELL函数的引用，以显示结果的地址。

=CELL("address",INDEX(MonthAmts,B10,C10,D10))

= CELL(“地址”，INDEX(MonthAmts，B10，C10，D10))

示例3：基于计数创建动态范围 (Example 3: Create a dynamic range based on count)

You can also use the INDEX function to create a dynamic range. In this example, I've created a name, MonthList, with this formula:

您也可以使用INDEX函数创建动态范围。在此示例中，我使用以下公式创建了名称MonthList：

='Ex03'!$C$1:INDEX('Ex03'!$C:$C,COUNTA('Ex03'!$C:$C))

='Ex03'！$ C $ 1：INDEX('Ex03'！$ C：$ C，COUNTA('Ex03'！$ C：$ C))

If another month is added to the list in column C, it will automatically appear in the data validation drop down list in cell F2, which uses MonthList as its source.

如果将另一个月添加到C列的列表中，它将自动出现在单元格F2中的数据验证下拉列表中，该单元格使用MonthList作为其来源。

示例4：按字母顺序对文本列进行排序 (Example 4: Sort column of text in alphabetical order)

In the final example, the INDEX function is combined with several other functions, to return a list of months, sorted in alphabetical order. The COUNTIF function shows how many month names come before a specific month name. SMALL returns the nth smallest item in the list, and MATCH returns the row number for that month.

在最后一个示例中，INDEX函数与其他几个函数结合使用，以返回按字母顺序排序的月份列表。 COUNTIF函数显示在特定月份名称之前有多少个月份名称。 SMALL返回列表中的第n个最小项，而MATCH返回该月的行号。

In the video, you can see the formula broken down into steps.

在视频中，您可以看到该公式分为多个步骤。

This formula is array-entered, by pressing Ctrl + Shift + Enter.

通过按Ctrl + Shift + Enter，可以输入此公式。

=INDEX($C$4:$C$9,MATCH(SMALL( COUNTIF($C$4:$C$9,"<"&$C$4:$C$9),ROW(E4)-ROW(E$3)), COUNTIF($C$4:$C$9,"<"&$C$4:$C$9),0))

= INDEX($ C $ 4：$ C $ 9，MATCH(SMALL(COUNTIF($ C $ 4：$ C $ 9，“ <”＆$ C $ 4：$ C $ 9)，ROW(E4)-ROW(E $ 3))， COUNTIF($ C $ 4：$ C $ 9，“ <”＆$ C $ 4：$ C $ 9)，0))

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

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

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