同じ値が入力されているセルの相対位置を取得したい

質問

エクセル初心者です。

同列内で、自セルと同じ値が入力されているセルの相対位置を取得したいので、
関数を教えて頂けないでしょうか。

たとえば、検索範囲が　A1:A25　で、各セルの値はランダムに並んでいて、
A3と同じ値がA8とA17にも入力されている場合に、「8」、「17」を得たいのですが、
A1からA25のすべてに対して同条件を満たすような配列式を教えて頂けたらとても有難いです。

宜しくお願いします。

回答No.4

KURUMITO

相対位置とはどんな意味でしょう。ご質問の意味から判断するとＡ1セルからＡ25セルまでのデータについて例えばＡ3セルと同じ値がＡ8セルとＡ17セルに有った場合には8と17の数値を表示させたいと解釈しました。
そのためＨ列には作業列を作って対応することとして、Ｂ列からＧ列の間に該当する行番号を表示させるように式を示しております。
式を下方にドラッグコピーする場合にはＡ1セルからＡ25セルまでを対象としているのですから当然25行まで下方にドラッグコピーすればよいわけで遥か下方のセルに同じデータが有ったとしてもその行番号は表示されませんね。
教えてくださった式では、返される値は絶対参照値になりますね。との意味が分かりません。該当する行番号を表示させればよいように理解していました。
Ａ3セルと同じ値がＡ8セルとＡ17セルに有った場合には8と17の数値がＢ3セルとＣ3セルに表示されるようにしています。

補足

再度のご回答、有難うございます。

まず、はるか下方にあるセルの行番号が・・・、という点については、ドラッグコピーの範囲を間違えたためにおきた現象で、私のミスです。
申し訳ありません。

返される値は絶対参照値になりますね・・・、については、
検査範囲がA1:A25で検査値がA3、A3とのダブりがA8とA17の場合に、A3に対して8と17の数値を表示させるのは、ご理解の通りです。
ですが、得たいのは、検査範囲の先頭を1とした、ダブりの相対的な位置（行）番号です。
つまり、検査範囲をA11:A35で検査値がA13、A13とのダブりがA18とA27だった場合にも、
18と27ではなく8と17を表示させたいのです。

昨日の補足にも書きましたが、検査範囲をA1:A25としたら絶対参照でも相対参照でも同じ値が得られるので、
参照の仕方にこだわらずに該当行番号を表示させたいのだ、と誤解させたかもしれません。
この点においても、私の質問の仕方が不適切でした。
申し訳ありませんでした。

教えて頂いた式をいじってみてるのですが、COLUMN関数があまりわかってないからか、うまくいきません。
もう少し頑張って、また報告します。

有難うございました。

回答No.3

KURUMITO

配列数式でそこまで対応するのには無理があるでしょうし、配列数式といった難しい式を使わなくても作業列を使うことで分かり易い式を使って表示させることができます。
例えば検索範囲がＡ１セルからＡ25としてダブりの数が多くあっても対応できる方法として例えばＨ列を作業列としてＨ1セルには次の式を入力して下方にドラッグコピーします。

=IF(A1="","",A1&COUNTIF(A$1:A1,A1))

そこでダブりのデータがある行をＢ列からＧ列までに表示させるとしたらＢ1セルには次の式を入力してＧ1セルまで右横方向にドラッグコピーしたのちに下方にもドラッグコピーします。

=IFERROR(MATCH($A1&(COLUMN(A1)+IF(COUNTIF($A$1:$A1,$A1)<=COLUMN(A1),1,0)),$H:$H,0),"")

なお、上のＩＦＥＲＲＯＲ関数はエクセル2007以降で有効ですがそれ以前のバージョンでしたら次の式をＢ1セルに入力します。

=IF(ISERROR(MATCH($A1&(COLUMN(A1)+IF(COUNTIF($A$1:$A1,$A1)<=COLUMN(A1),1,0)),$H:$H,0)),"",MATCH($A1&(COLUMN(A1)+IF(COUNTIF($A$1:$A1,$A1)<=COLUMN(A1),1,0)),$H:$H,0))

作業列のＨ列が目障りでしたらＨ列を選択してから右クリックして「非表示」を選択してＯＫします。

お礼

ご回答有難うございます。

教えてくださった式では、返される値は絶対参照値になりますね。
質問時に、検査範囲をA1:A25、としたのがいけなかったのかもしれませんが、
求めたいのは相対参照値です。
また、検査範囲をA1:A25に限定したいのに、たとえばA56にA8と同じ値が入力されていた場合に、
A8に対して56が返されます。

質問した時点で、式を一つにまとめられない上に作業列を使って式を書いていました。
たとえば、B1:B24には2～25(検査開始行番号）が、C2:C25には1～24（検査終了行番号）が
D列には1～25（検査値の入っているセルの相対的な位置）が入力されていて、
E列とF列に結果をかえすとして、

(E1)=if(ISERROR(MATCH(A1,INDIRECT("A"&B1):A$25),0)),"",if(D1<D$25,MATCH(INDIRECT("A"&B1):A$25,0)+D1,""))
(F1)=if(ISERROR(MATCH(A1,A$1:INDIRECT("A"&C1),0)),"",if(D1>1,MATCH(A1,A$1:INDIRECT("A"&C1),0),""))

この煩雑で冗長な式を、分かりやすく一つの式にまとめ、ダブりが複数あった場合にもすべて表示するようにしたかったのです。

おかげさまで、複数あった場合についてはヒントを頂きましたので、自分でももっと調べてみます。

ありがとうございました。

回答No.2

noname#203218

下記式をコピペ後下方へオートフィルで全ての行番号取得出来ます。
但し、検索データが検索範囲に含まれていますので、A３の３も表示されます。
A1～Ａ３データが検索範囲に含まれ無のならA$1:A$25→A$3:A$25として下さい。
=IF(COUNTIF(A$1:A$25,A$3)<ROW(A1),"",SMALL(INDEX((A$1:A$25<>A$3)*10^5+ROW($A$1:$A$25),),ROW(A1)))

補足

ご教示有難うございます。
ですが、私の説明不足から質問の要点が伝わらなかったようで、申し訳ありません。
検査範囲は　A1:A25 で、範囲内のすべてのセルの値が検査値。
また、自セルは検査範囲に含みません。　
したがって、検査範囲自体を変数で指定する必要があると思うのですが・・・。

回答No.1

tem（@mstmstmst）

ま、DGET関数でとれるけど、結果は１つだけだよね。
エクセルで複数の回答を得るのは、関数ではできないよ。
ていうか、関数ってそういうもんでしょ。
マクロ組めばいいけど、それしきのこと、
マクロ組むほどのことじゃないような気もする。