excel函数:汉字转全拼_30天中的30个Excel函数：20 –地址

excel函数:汉字转全拼

Yesterday, in the 30XL30D challenge, we found items in an array with the MATCH function, and learned that it plays nicely with other functions, like VLOOKUP and INDEX. For day 20 in the challenge, we'll examine the ADDRESS function.

昨天，在30XL30D挑战中，我们使用MATCH函数在数组中找到了项目，并了解到它可以与其他函数(例如VLOOKUP和INDEX)很好地配合使用。 在挑战的第20天，我们将检查ADDRESS功能。

The ADDRESS function returns a cell address as text, based on a row and column number. Do you need ADDRESS? Do other functions do the same thing, but better?

ADDRESS函数基于行和列号以文本形式返回单元格地址。 您需要地址吗？ 其他功能也可以做同样的事情，但是更好吗？

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 ADDRESS information and examples, and if you have other tips or examples, please share them in the comments.

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

功能20：地址 (Function 20: ADDRESS)

The ADDRESS function returns a cell address as text, based on a row and column number. It can return an absolute or relative address, in A1 or R1C1 style. A sheet name can also be included in the result.

ADDRESS函数根据行和列号以文本形式返回单元格地址。 它可以返回A1或R1C1样式的绝对或相对地址。 工作表名称也可以包含在结果中。

您如何使用地址？ (How Could You Use ADDRESS?)

The ADDRESS function can return a cell address, or combine with other functions to:

ADDRESS函数可以返回单元地址，或与其他函数组合以：

• Get cell address from row and column number
  从行号和列号获取单元格地址
• Find cell value from row and column number
  从行号和列号中查找单元格值
• Return address of cell with highest value
  返回值最大的单元格的地址

地址语法 (ADDRESS Syntax)

The ADDRESS function has the following syntax:

ADDRESS函数具有以下语法：

• • abs_num -- if 1 or omitted, Absolute ($A$1), for Relative (A1), use 4. Other options, 2=A$1, 3=$A1
    abs_num-如果为1或省略，则绝对值($ A $ 1)，相对值(A1)使用4。其他选项，2 = A $ 1，3 = $ A1
  • a1 -- if TRUE or omitted, returns A1 style, FALSE for R1C1 style
    a1-如果为TRUE或省略，则返回A1样式，对于R1C1样式为FALSE
  • sheet_text -- sheet name can be included, for address on different sheet
    sheet_text-可以包含工作表名称，用于不同工作表上的地址

  ADDRESS(row_num,column_num,[abs_num],[a1],[sheet_text])

  地址( 第 _num 列 ， 第 num 列 ，[abs_num]，[a1]，[sheet_text])

地址陷阱 (ADDRESS Traps)

The ADDRESS function only returns the cell address as text. If you need the cell value, use the INDIRECT function around the ADDRESS function, or use one of the alternative formulas shown in Example 2.

ADDRESS函数仅将单元格地址返回为文本。 如果需要单元格值，请在ADDRESS函数周围使用INDIRECT函数，或使用示例2中所示的替代公式之一。

示例1：从行号和列号获取单元格地址 (Example 1: Get cell address from row and column number)

With the ADDRESS function, you can get a cell address, as text, based on a row number and column number. If you enter just those two arguments, the result is an absolute address, in A1 style.

使用ADDRESS功能，您可以基于行号和列号获取单元格地址(作为文本)。 如果仅输入这两个参数，则结果是A1样式的绝对地址。

=ADDRESS($C$2,$C$3)

=地址($ C $ 2，$ C $ 3)

绝对或相对 (Absolute or Relative)

By omitting the abs_num argument in the formula above, the result was the default, absolute reference.

通过省略上面公式中的abs_num参数，结果是默认的绝对引用。

To see the address as a relative reference, you can use 4 in the abs_num argument.

要将地址视为相对引用，可以在abs_num参数中使用4。

=ADDRESS($C$2,$C$3,4)

=地址($ C $ 2，$ C $ 3,4)

A1或R1C (A1 or R1C)

To see R1C1 style, instead of the default A1 style, you can add FALSE in the a1 argument.

要查看R1C1样式，而不是默认的A1样式，可以在a1参数中添加FALSE。

=ADDRESS($C$2,$C$3,1,FALSE)

=地址($ C $ 2，$ C $ 3,1，FALSE)

Sheet Nam (Sheet Nam)

The final argument is sheet, and you can include a sheet name, if you want it in the result.

最后一个参数是工作表，如果需要，可以包含工作表名称。

=ADDRESS($C$2,$C$3,1,TRUE,"Ex02")

=地址($ C $ 2，$ C $ 3,1，TRUE，“ Ex02”)

示例2：从行号和列号查找单元格值 (Example 2: Find cell value from row and column number)

The ADDRESS function returns the cell address as text, not as an actual reference. If you want to return the cell's value, you can wrap the ADDRESS function with an INDIRECT function. We'll learn more about INDIRECT, later in the 30XL30D challenge.

ADDRESS函数以文本形式而不是实际引用形式返回单元格地址。 如果要返回单元格的值，则可以使用INDIRECT函数包装ADDRESS函数。 我们将在30XL30D挑战赛中进一步了解INDIRECT。

=INDIRECT(ADDRESS(C2,C3))

=间接(地址(C2，C3))

The INDIRECT function will work without the ADDRESS function too. Here, the & operator is used to create an R1C1 style address, and the cell value is returned.

INDIRECT功能也可以不使用ADDRESS功能。 在这里，＆运算符用于创建R1C1样式地址，并返回单元格值。

=INDIRECT("R" & C2 & "C" & C3,FALSE)

= INDIRECT(“ R”＆C2＆“ C”＆C3，FALSE)

The INDEX function can also return a cell's value, based on a row and column number.

INDEX函数还可以根据行号和列号返回单元格的值。

=INDEX(1:500,C2,C3)

= INDEX(1：500，C2，C3)

示例3：具有最高值的单元格的返回地址 (Example 3: Return address of cell with highest value)

In this example, we'll find the cell with the highest value, and use the ADDRESS function to get its address.

在此示例中，我们将找到具有最高值的单元格，并使用ADDRESS函数获取其地址。

The MAX function finds the highest number in column C.

MAX函数在C列中找到最大的数字。

=MAX(C3:C8)

=最大(C3：C8)

Then, the ADDRESS function is combined with MATCH, which finds the row number, and COLUMN, which gets the column number.

然后，将ADDRESS函数与MATCH(用于查找行号)和COLUMN(用于获取列号)组合在一起。

=ADDRESS(MATCH(F3,C:C,0),COLUMN(C2))

=地址(MATCH(F3，C：C，0)，COLUMN(C2))

下载地址功能文件 (Download the ADDRESS Function File)

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

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

观看地址视频 (Watch the ADDRESS Video)

To see a demonstration of the examples in the ADDRESS function sample workbook, watch this short video.

要观看ADDRESS函数示例工作簿中示例的演示，请观看此简短视频。

演示地址

翻译自: https://contexturesblog.com/archives/2011/01/21/30-excel-functions-in-30-days-20-address/

excel函数:汉字转全拼