找到的第一个错误单元格的返回地址(在A1和X500之间)

If you got a way, give me a bit of a breakdown so I can learn it instead of just copying it. -Thx

如果你有办法，给我一点故障，这样我可以学习它，而不是复制它。谢谢

=ADDRESS(MATCH("keyword",B:B,0),3,1,1)

This handy thing nets me things I'm looking for in a column and I can adjust it to work for 1 column but it seems searching for errors in multiple columns and rows is above my pay grade.

这个便利的东西让我可以在一个列中找到我想要的东西，我可以调整它为一个列，但它似乎可以在多个列中查找错误，并且行高于我的支付等级。

............
For those interested in the how to:

............对如何:

You can further help your self by links to the address you get with your searches... (It reallly handy jumping to spots of interest provided your document doesn't require you to leave errors behind, so you can't get to the next one this way)

你还可以通过搜索地址的链接来帮助自己……(如果你的文档不需要你留下错误，所以你无法通过这种方式进入下一个文档，那么跳转到感兴趣的地方就非常方便了。)

(example searches)

(示例搜索)

Simple version

简单的版本

=HYPERLINK(CONCATENATE("#",a1),"Title of Link")

It sends you to whatever address it finds in A1 when clicked (if you want to enter the cell holding the link just hold leftclick longer.)

当点击时，它会将你发送到它在A1中找到的任何地址(如果你想输入包含链接的单元格，只需按住leftclick更久一点)。

=HYPERLINK(CONCATENATE("#",CELL("Address",INDEX($B:$B,MATCH
("keyword your search for",$B:$B,0)+0))),"Title of Link")

breakdown: hyperlink, # = internal link - to this file or sheet(whatever it was), use address, found by index match criteria, name - what text appears in the cell.

细分:超链接，# =内部链接-到这个文件或表格(不管它是什么)，使用地址，根据索引匹配标准找到，名称-单元格中出现的文本。

2 个解决方案

#1

I would not recommend using full column references with the AGGREGATE¹ function but cutting down the column references to a more reasonable representation of your actual data area works well.

我不建议使用全列总¹函数引用,但减少的列引用一个更合理的表示实际数据区域工作。

'first error
=ADDRESS(AGGREGATE(15, 6, ROW(1:9999)/ISERROR(B1:B9999), 1), COLUMN(B:B), 1, 1)
'second error
=ADDRESS(AGGREGATE(15, 6, ROW(1:9999)/ISERROR(B1:B9999), 2), COLUMN(B:B), 1, 1)
'last error
=ADDRESS(AGGREGATE(14, 6, ROW(1:9999)/ISERROR(B1:B9999), 1), COLUMN(B:B), 1, 1)

'first error in column B:D from B1, B2, ... D9998, D9999
=IFERROR(ADDRESS(AGGREGATE(15, 6, ROW(1:9999)/ISERROR(B1:B9999), 1), COLUMN(B:B), 1, 1),
  IFERROR(ADDRESS(AGGREGATE(15, 6, ROW(1:9999)/ISERROR(C1:C9999), 1), COLUMN(C:C), 1, 1),
  IFERROR(ADDRESS(AGGREGATE(15, 6, ROW(1:9999)/ISERROR(D1:D9999), 1), COLUMN(D:D), 1, 1), "no errors")))

In the last example the IFERROR function is used to pass control over to the next sub-formula referencing another column if no errors are found.

在上一个示例中，如果没有发现错误，则使用IFERROR函数将控制权传递给引用另一列的下一个子公式。

¹ _{The AGGREGATE function was introduced with Excel 2010. It is not available in earlier versions.}

用Excel 2010¹介绍了聚合函数。它在早期版本中是不可用的。

#2

Tony Valko of the www.excelforum.com has nailed this with a master stroke - Here it is for anybody else who wants to use it:

www.excelforum.com的托尼•瓦尔科(Tony Valko)用一种全能型的笔法把这句话给了所有想要使用它的人:

=IFERROR(ADDRESS(MOD(SMALL(IF(ISERROR($A$1:$D$5),COLUMN($A$1:$D$5)*10000+ROW($A$1:$D$5)),ROWS(F$1:F1)),10000),TRUNC(SMALL(IF(ISERROR($A$1:$D$5),COLUMN($A$1:$D$5)*10000+ROW($A$1:$D$5)),ROWS(F$1:F1))/10000),4),"")

** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key.

**数组公式需要使用CTRL、SHIFT、ENTER(不只是ENTER)的组合键进行输入。按住CTRL键和SHIFT键。

Additionally you can substitute the end "") with "Some word") to not lose the formula from view when you have no errors..

另外，你可以用“Some word”来代替“end”(“”)(用“Some word”)来代替“Some word”(“Some word”)。

#1