excel関数 matchの使い方について

質問

早速質問します。

行　Ａ　Ｂ　Ｃ
１　２　２
２　４
３　５
４　３
５　８
６　２

C1セルにMATCH(B1,A1:A6,0)を式を入れるとA1セルに「２」という値があるので、C1には戻り値として「１」が返ると思うのですが、続けてC2セルに続きの検索を行いたい（A6セルに「２」というデータが入っているのでC2セルには「５(=6-1)」という結果を期待してます）のですが、うまくいく方法が思いつきません。
知恵をお貸しいただけないでしょうか？
MATCH(B2,A1:A6,0)と第１セクションに手動で「B2」といわれと、目的の結果が得られますが、汎用性を持たせるため自動で結果が入れれるようにしたいのでお願いします。

回答No.3

s_yoshi_6 · ベストアンサー

＃１です。
失礼しました。ご質問の意図、了解しました。
そういうことであれば、
C1に
=MATCH(B$1,A1:A$6,0)
C2に
=IF(SUM(C$1:C1)+1>6,"",MATCH(B$1,INDIRECT("A"&SUM(C$1:C1)+1):A$6,0))
でC3以下にコピー　でいいと思います。

上記式中で、SUM(C$1:C1)+1>6 の「6」は、検索範囲の最下セルの行番号です。この処理をしないと、最下セルに検索値があった場合に正しい結果が出てきません。
もし、検索範囲の一つ下のセル（A7）に、検索値と同じ数字が入ることがないのであれば、検索範囲を１行分広げて
=MATCH(B$1,INDIRECT("A"&SUM(C$1:C1)+1):A$7,0)
としても構いません。

なお、検索値が見つからなかった際の#N/Aについて、もし非表示にされたい場合、＃２さんの使われた方法の他に、条件付き書式で見えなくする方法もあります。
C1:C6を選択して、書式→条件付き書式で
「数式が」「=ISNA(C1)」
として、「書式」でフォントの色をセルの背景色と同色にします。

お礼

わざわざ２度も回答を下さりありがとうございました。
無事解決いたしました。
INDIRECT関数の存在を知りませんでしたので、今回非常に勉強になりました。
ありがとうございました。

回答No.2

imogasi

A2:A13に
2
3
4
5
6
2
3
2
2
3
4
2
C1に０を入れる。
C2に
=MATCH(2,INDIRECT("A"&SUM($C$1:C1)+1&":$A$20"),0)
と入れてC6まで複写。
結果はC1:C6に
0
2
5
2
1
3
C7以下は２のカウント数６を越えるので、エラーになります。
出したくなければ、それをIF文に組み込んでください。
ROW()がCountIf(A2:A20,2)+1より大なら""を、でないときは上記式を。
次は難行下にあるかを出しているが、質問の題意にあっているかな。

お礼

どうもわざわざご返事ありがとうございました。
無事、問題が解決いたしました。
重ね重ねありがとうございました。

回答No.1

s_yoshi_6

＞MATCH(B2,A1:A6,0)と第１セクションに…
の部分の数式は
「MATCH(B2,A2:A6,0)」ではないでしょうか。

数式をコピーする時に、A6の部分だけを固定されたいのであれば、そこのセル番地の列番号を絶対参照にして、C1に、
=MATCH(B1,A1:A$6,0)
として以下コピーされると良いと思います。

（または、数式をＣ列以外にもコピーされるのであれば、列も絶対参照として
=MATCH(B1,$A1:$A$6,0）
とします）

参考：相対参照と絶対参照について
http://homepage1.nifty.com/kenzo30/ex_kisosyo/ex_ks_syokyu3.htm

お礼

わざわざ未熟者にお答えくださいましてありがとうございました。
また間違い箇所の指摘もありがとうございました。

わたしの質問に不備がありましたので、よろしければもう一度回答願えたらと思います。
お手数をかけまして誠にすみませんでした。

補足

お答えありがとうございます。
すいません、言葉が足りませんでした。(汗)

＞「MATCH(B2,A2:A6,0)」ではないでしょうか。
その通りです。　御指摘ありがとうございます。

で、例が悪かったのでもう一度、
行　Ａ　　Ｂ　　Ｃ
１　　９　　７
２　　７
３　　５
４　　３
５　　８
６　　７

C1セルにMATCH(B1,A1:A6,0)を式を入れるとA2セルに「７」という値があるので、C1には戻り値として「２」が返ると思うのですが、続けてC2セルに続きの検索を行いたい（A6セルに「７」というデータが入っているのでC2セルには「５(=6-1)」という結果を期待してます）のですが、うまくいく方法が思いつきません。
MATCH関数の第１セクションに自動に１回目の検索結果以後の値が検索できるよう、（今回の場合で言うと、１回目の検索で２行目を返したので、C2にC1式をコピー＆ペーストした際に第１セクションに「B3」という値が自動ではいるよう）にしたいんです。
よろしくお願いいたします。