文字列検索しセルの位置が知りたい

質問

指定範囲内で文字列を検索し、見つかったセルの行番号、列番号を知りたいのです。
マクロでなく関数で出来るでしょうか？

回答No.2

tom04 · ベストアンサー

こんばんは！
具体的な範囲が判らないので、やり方だけ・・・

↓の画像でA1～D10セル内データでG1セルに入力したデータの
「行番号」と「列番号」を表示するようにしてみました。

尚、範囲内に重複データはない！という前提です。

画像ではG2セルに
=SUMPRODUCT((A1:D10=G1)*ROW(A1:A10))

G3セルに
=SUMPRODUCT((A1:D10=G1)*COLUMN(A1:D1))

という数式を入れています。

※　該当データがない場合は「0」が表示されます。m(_ _)m

お礼

ありがとうございます。
出来ました。機能としては十分です。しかし、これはどういう理屈で計算出来ているのでしょうか？sumproductは調べましたが分かりませんでした。
・A1:D10=G1は論理式の扱いなのか？どういう動作？
・ROW(A1:A10)は10個のセルの行番号を示すの？
・両者が掛け算されており、配列としては１個だと思いますが、sumproductの引数として１個の配列を使うのはナニ？

すみませんが、教えて下さい。

回答No.7

bunjii

回答No.6の補足として別の角度から検討してみました。

貼付画像はExcel 2013で行ったものですがExcel 2003にも組み込まれた関数で処理しています。

行番号
H4=MOD(MIN(IF((A2:E11=H2),ROW(A2:A11)+COLUMN(A2:E2)*1000,"")),1000)
列番号
H5=INT(MIN(IF((A2:E11=H2),ROW(A2:A11)+COLUMN(A2:E2)*1000,""))/1000)
この2つの式は入れ子のIF関数で配列値を返す必要があるため数式バーへ入力後Ctrl+Shift+Enterで確定してください。
左上から右下へ検索して最初に見つかったセルが結果として返されます。

I4とI5の計算式はMIN関数をMAX関数に置き換えたもので、左上から右下へ検索して最後に見つかったセルが結果として返されます。

検証（別解）はSUMPRODUCT関数を使うことで式の確定時に通常のEnterキーのみで確定する扱いにしてみました。

行番号
L4=SUMPRODUCT(LARGE((INDEX(A2:E11,,SUMPRODUCT(LARGE((A2:E11=L2)*COLUMN(A2:E2),COUNTIF(A2:E11,L2))))=L2)*ROW(A2:A11),COUNTIF(INDEX(A2:E11,,SUMPRODUCT(LARGE((A2:E11=L2)*COLUMN(A2:E2),COUNTIF(A2:E11,L2)))),L2)))
L5=SUMPRODUCT(LARGE((A2:E11=L2)*COLUMN(A2:E2),COUNTIF(A2:E11,L2)))
この数式は論理が複雑なのでお勧めできません。

お礼

詳細な回答をありがとうございます。
見た感じ「これは無理」と思ってしまいました。Match関数で攻めてみます。

回答No.6

bunjii

>平たく言うと何番目に見つかったか。
MATCH関数は指定範囲（1列または1行）の中から検索値の位置を検出します。
何番目かは指定できませんので検索値と同じ値のセルが複数のときは最初に見つかった位置になります。

>これにaddressをかけてなぜセル位置が分かるのでしょうか？
MATCH関数は複数列または複数行を対象にできませんので検出範囲は限定されています。
今回の質問で「指定範囲内で文字列を検索し」とのことですが複数行×複数行の場合は別の方法を使います。
また、「見つかったセルの行番号、列番号」は「A1」や「R1C1」のようにセルのアドレスとして検出したかったのではないのでしょうか？
条件が曖昧だったので勝手解釈の検証結果を提示しました。
尚、ADDRESS関数は行番号と列番号を指定することでセルの位置が返されます。
今回の検証では列は固定で行番号をMATCH関数で検出した結果を提示しました。

MATCH("第3項",B1:B14,0)　→　4
ADDRESS(4,2)　→　$B$4　引数の2番目はB列（第2列）を意味します。
ADDRESS(4,2,4)　→　B4　引数の3番目は省略すると上記のようになり、$を除いた形式にするときは4を指定します。

複数列×複数行からの位置検出ではSUMPRODUCT関数を使えますが検索値と同じ値が複数のとき無指定で優先的に選択することができませんので条件の提示が必要です。

回答No.5

tom04

No.2です。

数式の意味が知りたいというコトですので・・・
画像の場合で説明しますと。

大前提としてデータ範囲内には重複データはない！というのは前回書いた通りです。
（万一同じデータが複数ある場合はとんでもない数値になります）

行方向の
＞=SUMPRODUCT((A1:D10=G1)*ROW(A1:A10))
の意味
必ず範囲指定した行数と　ROW(A1:A10)　の行数は一致させる必要があります。
A1～D10セル内にG1と一致するデータがある場合（画像では範囲指定内の5行目）ですので、
5行目だけが「TRUE」=1　となり　他の行はすべて「FALSE」=0となります。
配列数式のSUMPRODUCT関数で、ROW(A1:A10)　をかけていますので、
TRUEの5行目=1　×　行番号（A5）の結果が表示されます。

列に関しても同じ意味です。

※　注意点　※
万一　仮にデータ範囲がA1セルからではなく、C3セルから始まる範囲が前回のような表がある場合は
=SUMPRODUCT((C4:F13=G1)*ROW(A1:A10))
のように範囲指定した行数と同じ数だけ1行目（A列でなくても構いません）から同じ行数分だけ掛け算します。

この程度でよろしいでしょうかね？m(_ _)m

お礼

ありがとうございます。
これは今までに経験のない使い方です。
A1:D10=G1 まずこれは論理式で結果は配列となりFalse,F,F,F,True,F,F,F,F,F
ROW(A1:A10) これは行番号の配列1,2,3,4,5,6,7,8,9,10
両者を掛け算すべく*を使った時点でTrueとFalseはそれぞれ1と０に変わり、結果は配列となり、0,0,0,0,5(行番号),0,0,0,0,0
sumproductなので0+0+0+0+5+0+0+0+0+0=5が戻り値になる。

なかなか奥が深い使い方ですね。

回答No.4

MackyNo1

関数で行番号や列番号を取得することもできますが、そのデータをどのように使用するかによって、最も効率的な数式が異なります。

このようなご質問では、操作の目的やシートのレイアウトを含めて、ご希望の操作をもう少し具体的に例示されたほうが、より的確な回答が寄せられると思います。

お礼

ありがとうございます。

回答No.3

inakanoit

置換ではなく、検索を指定すればOKです。

お礼

ありがごうございます。
エクセルの機能ではなくサードパーティのアドインか何かのようですね。

回答No.1

bunjii

>マクロでなく関数で出来るでしょうか？
ADDRESS関数を使うとセルの列記号と行番号を取得できます。

貼付画像はExcel 2013で検証した結果です。
MATCH関数を使って目的の値で行番号を取得し、ADDRESS関数に渡してセルの位置を取得しました。

=ADDRESS(MATCH("第3項",B1:B14,0),2,4)

お礼

ありがとうございます。
machを調べましたが、指定範囲にある検索文字列を探し「相対的な順番を返す」関数だったと思います。平たく言うと何番目に見つかったか。これにaddressをかけてなぜセル位置が分かるのでしょうか？