如何在多行/多列 Excel 电子表格中查找值的位置? [英] How to Find the location of a value in a multi row/column Excel spreadsheet table?

问题描述

如何找到单元格地址，或者最好是值 119 的行和列，或者下表中的任何其他数字?

How do I find either the cell address or preferably the row and column of the value 119, or any other number in the table below?

表格不包含行标题或列标题，只有显示的数字.

The table does not contain row or column titles, only the numbers as shown.

我正在寻找工作表解决方案(公式)而不是 VBA 解决方案.

I am looking for a worksheet solution (formula) and not a VBA solution.

推荐答案

数组公式

这是一个数组公式，必须用control shift enter输入，即按住control shift然后按回车.

An Array Formula

This is an array formula and it has to be entered with control shift enter i.e. hold control shift and then press enter.

=MAX(IF(A1:J34=119,ROW(A1:J34)-ROW(A1)+1))

备注:

按列搜索值，即 A1, A2, ... B1, B2 ... 即如果您在单元格 D1<中有另一个 119/code> 结果仍然是 2，如果您在单元格 c1 中有一个 119 那么结果将是1.

The value is searched by column i.e. A1, A2, ... B1, B2 ... i.e. if you had another 119 in cell D1 the result would still be 2, and if you had a 119 in cell c1 then the result would be 1.

对于列版本，只需将 ROW 替换为 COLUMN:

For a column version just replace ROW with COLUMN:

=MAX(IF(A1:J34=119,COLUMN(A1:J34)-COLUMN(A1)+1))

这篇关于如何在多行/多列 Excel 电子表格中查找值的位置?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！