星期五的Excel函数：ISREF和IS函数

ISREF and the IS Functions -- although it sounds like the name of a geeky band, it's not. Today we'll take a look at ISREF, which is one of the 9 IS functions that are lumped together in the Excel Help files.

ISREF和IS功能-尽管听起来像是一个怪异乐队的名字，但事实并非如此。 今天，我们将看一下ISREF，它是在Excel帮助文件中集中在一起的9个IS函数之一。

How sad -- 9 functions that never get to shine on their own. We'll give ISREF a few minutes in the spotlight, to see how it works.

多么可悲-9种功能永远无法发光。 我们将重点介绍ISREF，以了解其工作原理。

And remember, you can learn more about functions in my 30 Functions in 30 Days ebook kit.

请记住，您可以在我的30天30天功能电子书工具包中了解有关功能的更多信息。

Excel IS函数 (The Excel IS Functions)

The following IS functions are listed on one page in Excel Help:

Excel帮助的一页上列出了以下IS功能：

• ISBLANK(value)
  ISBLANK(值)
• ISERR(value)
  ISERR(值)
• ISERROR(value)
  ISERROR(值)
• ISLOGICAL(value)
  ISLOGICAL(值)
• ISNA(value)
  ISNA(值)
• ISNONTEXT(value)
  ISNONTEXT(值)
• ISNUMBER(value)
  ISNUMBER(值)
• ISREF(value)
  ISREF(值)
• ISTEXT(value)
  ISTEXT(值)

They all work in the same way -- the function tests a value, and returns TRUE if the value passes the test. NOTE: There is also a new ISFORMULA function for Excel 2013 and later versions.

它们都以相同的方式工作-函数测试一个值，如果该值通过测试，则返回TRUE。 注意：Excel 2013和更高版本也有一个新的ISFORMULA函数。

IS功能实例 (IS Function Examples)

For example, if cell B2 contains a number, the ISNUMBER formula will result in TRUE:

例如，如果单元格B2包含数字，则ISNUMBER公式将得出TRUE：

=ISNUMBER(B2)

= ISNUMBER(B2)

If cell B3 contains anything except text, even if cell B3 is blank, the ISNONTEXT formula will result in TRUE:

如果单元格B3包含除文本以外的任何内容，即使单元格B3为空，ISNONTEXT公式也将得出TRUE：

=ISNONTEXT(B3)

= ISNONTEXT(B3)

The other IS functions work the same way, and give the expected results -- except for ISREF.

其他IS函数以相同的方式工作，并给出预期的结果-ISREF除外。

ISREF功能问题 (ISREF Function Problems)

With a reference in cell B2, the ISREF function returns TRUE as the result. However, it also returns TRUE when there is a number in cell B3.

使用单元格B2中的引用，ISREF函数将返回TRUE作为结果。 但是，当单元格B3中有数字时，它也会返回TRUE。

The ISREF function isn't testing what's in the referenced cell, it's testing the reference within the formula. And because both B2 and B3 are references, the result is TRUE.

ISREF函数不测试引用单元格中的内容，而是测试公式中的引用。 并且因为B2和B3都是引用，所以结果为TRUE。

So, the ISREF function won't help you assess whether there is a reference another cell.

因此，ISREF函数无法帮助您评估是否有另一个单元格的引用。

ISREF用途 (ISREF Uses)

If you can't use ISREF to detect a reference in another cell, how can you use it? Well, it can check the results of other formulas, to see if they have returned a valid reference. You could use ISREF, instead of ISERROR, in your formulas that need references.

如果您不能使用ISREF来检测另一个单元格中的引用，那么如何使用它？ 好吧，它可以检查其他公式的结果，以查看它们是否返回了有效的引用。 您可以在需要引用的公式中使用ISREF而不是ISERROR。

For example, the INDIRECT function returns a reference, and INDIRECT("D1") creates a valid reference. If cell B2 contains the text "D1", this formula results in TRUE:

例如，INDIRECT函数返回一个引用，而INDIRECT(“ D1”)创建一个有效的引用。 如果单元格B2包含文本“ D1”，则此公式的结果为TRUE：

=ISREF(INDIRECT(B2))

= ISREF(INDIRECT(B2))

The first OFFSET formula show below is not valid, because there is no cell that is 1 column to the left of cell A1, so the ISREF result is FALSE

下面显示的第一个OFFSET公式无效，因为没有任何单元格位于单元格A1左侧的1列，因此ISREF结果为FALSE

=ISREF(OFFSET(A1,0,-1))

= ISREF(偏移(A1,0，-1))

The second OFFSET formulas returns a valid reference to cell B1, so the ISREF result is TRUE.

第二个OFFSET公式返回对单元格B1的有效引用，因此ISREF结果为TRUE。

ISREF功能还有其他用途吗？ (Any Other Uses for ISREF Function?)

Do you use ISREF in your formulas? Can you think of any other examples for using it? ______________ Save

您在公式中使用ISREF吗？ 您能想到使用它的其他示例吗？ ______________ 保存

翻译自: https://contexturesblog.com/archives/2011/03/04/excel-function-friday-isref-and-the-is-functions/