ある数値が何個目にあるか調べる関数ありますでしょうか？

質問

タイトルにありますとおり
ある数値が何個目にあるか調べる方法
または関数がないか悩んでおります＾＾；
文章だけではわかりにくいので以下に例を示します。

　　Ａ　Ｂ　Ｃ　Ｄ　
１　２
２　０
３　１
４　８
５　２
６　９
７　５
８　３

このような表があったとします。
Ａ列に適当な数字が並んでいます。
Ａ列で３が何個目にあるか？という場合は
ＭＡＴＣＨ関数でMATCH(3,A1:A8)で８と表示されると思います。
調べたい数値が1個だけしか存在しない場合はいいのですが
複数ある場合、ＭＡＴＣＨ関数だとうまくいきません。
例えば、Ａ列の２を調べる場合は1行目と5行目にあるため使えません。

このような場合、使えそうな関数、またはＭＡＴＣＨ関数でも便利な活用方法など
ご存知の方いらっしゃいましたら、ご教示いただけませんでしょうか＾＾；

回答No.4

zap35 · ベストアンサー

一例です
B1に検索したい値「２」が入力されているとき、適当なセルに以下の式を貼り付けて下方向にコピーしてください
　=LARGE(INDEX((A$1:A$50=$B$1)*ROW(A$1:A$50),),COUNTIF(A$1:A$50,$B$1)-ROW(A1)+1)

結果は
　1
　5
　#NUM!
になります

この式では３つ目以降はエラーになります。エラーを表示したくないときは条件判定を加えて以下の式になります
　=IF(COUNTIF(A$1:A$50,$B$1)<ROW(A1),"",LARGE(INDEX((A$1:A$50=$B$1)*ROW(A$1:A$50),),COUNTIF(A$1:A$50,$B$1)-ROW(A1)+1))

範囲はA1:A50にしていますので実際のシートに合わせて変更してください

お礼

zap35様、ご回答ありがとうございます＾＾

返答遅れまして失礼いたしました＾＾；
質問後寝てしまい、本日は先ほど帰ってきたばかりで式を確認させていただきました＾＾

当方の意図した結果が得られました。
また、エラーを表示したくない旨は質問時点では考えておりませんでしたが、表示されない方がすっきりして見えますので、助かりました＾＾

今後応用できるよう努力したいと思います＾＾

回答No.6

noname#204879

A　B　C　 D　 E　F　G　H
1　 2　　　0　 1　2　3　4　5
2　 0
3　 1　　　2　 3　1　8　　 7
4　 8　　　9　10　5
5　 2　　 11　12
6　 9　　 13
7　 5
8　 3
9　 0
10　1
11　0
12　1
13　0
14

１．Ｃ列以右の２行目は空白のまま
２．セル C3 に次の[条件付き書式]を設定
　　数式が　　　　=ISERROR(C3)
　　フォント色　　白
３．セル C3 に次式を入力して、此れを右方および下方にズズーッとドラッグ＆ペースト　　=IF(C$1="","",MATCH(C$1,OFFSET($A$1,C2,,100,),0)+C2)

お礼

mike_g様、ご回答ありがとうございます＾＾

意図した結果が得られました＾＾
今回教えていただきました式や考え方を今後活用できるかわかりませんが、とても勉強になりました

回答No.5

imogasi

ありそうに思うかと思うが、エクセルの関数では、質問にぴったりの関数はなく、ＭＡＴＣＨ関数の使い方によっても対処できない。
エクセルの関数利用で、「抜き出し問題」の質問が毎日のように出るが、式が難しくなる原因の１つです。
（本件は行番号数を求めているが、行番号がわかればＩＮＤＥＸ関数で、同行他列のデータが取れるので、実質は、抜き出し問題と同じ型だ。）
（１）作業列を使う方法
（２）作業列を使わない方法
ーー
（２）のタイプの１つだが、こんなのもあるということで、
Ｂ１に
=SMALL(IF($A$1:$A$10=2,ROW($A$1:$A$10),"9999"),ROW())
と入れて、ＳＨＩＦＴ、ＣＴＲＬ，ＥＮＴＥＲキーを同時押しする。
この式を下方向に複写する。（配列数式という）
結果
質問例の場合
1
5
#NUM!
・・
#NUM!を消すのは
=IF(ROW()>COUNTIF($A$1:$A$10,2),"",SMALL(IF($A$1:$A$10=2,ROW($A$1:$A$10),"9999"),ROW()))
と入れて、ＳＨＩＦＴ、ＣＴＲＬ，ＥＮＴＥＲキーを同時押しする。
この式を下方向に複写する。
＝＝＝＝＝＝＝＝＝＝＝
（１）では
第１行目に行挿入して
空き列のＤ２に=IF(A2=2,MAX($D$1:D1)+1,"")と入れて下方向に式を複写。
第2行　　１
第６行　　２
になる。
空き列Ｅ列ｆ２に
=MATCH(ROW()-1,$D$2:$D$11,0)
と入れて、下方向に式を複写。
結果
1
5
#N/A
#N/Aを消す方法は前述を参考に。
ーー
このやり方は自称「imogasi方式」。Ｇｏｏｇｌｅで紹介すれば、質問例が
多数出ます。

お礼

imogasi様、ご回答ありがとうございます＾＾

ご指摘のとおり、いろいろなケースに完全に対応している方法を見つけるのはとても難しくスキルのいる作業だと思います＾＾；

当方エクセルを少々かじっている程度ですので
エキスパートの方々の回答にはとても助かっております＾＾

ご教示いただけました式ですが、当方の意図した結果が得られました。
今後できるだけ自力で解決できるよう、いただけました式を理解する努力をしようと思います＾＾；

回答No.3

RH01

こんばんは。

ちょっと無理やりな感じですが
こんなのはいかがでしょう。

まずD列1行目に調べたい数字を入れます。
D1「2」
そして、C列の各行に↓こんな式を入れます。
=IF(A2=$D$1,ROW(A2),"")
ROWは参照した行数を返してくれる関数なので
A1の値がD1と同じなら行数を返す
という式です。

これをやると↓こんな感じになります。

　　Ａ　Ｂ　Ｃ　Ｄ　
１　２　　　１　２
２　０
３　１
４　８
５　２　　　５
６　９
７　５
８　３

mazdaFD3さんのご希望は、C列の1行目に1、2行目に5と
出したいということなので
そこがクリアされていない回答です。。

なお、ご希望の状態に近づけるには
ちょっとひと手間掛かってしまいますが以下の方法もあります。

C列全部をコピーして
E列に「形式を選択してコピー」で「値」を選びます。
その後E列を並び替え（昇順）
にすると↓こんな感じになります。

　　Ａ　Ｂ　Ｃ　Ｄ　Ｅ　
１　２　　　１　２　１
２　０　　　　　　　５
３　１
４　８
５　２　　　５
６　９
７　５
８　３

お礼

RH01様、ご回答ありがとうございます＾＾

返答大変遅れまして失礼いたしました＾＾；
質問後、寝てしまい、本日は先ほど帰宅したものですから・・・

ご指摘のとおり、上から順番に隙間なく結果を表示したいので
ご教示いただけました式は少し当方の意図とはずれるのですが
ＲＯＷ関数の使い方は良く知りませんでしたので
とても勉強になりました＾＾
自分なりに、いただけました式を改良してみようと思います＾＾

回答No.2

saburo0

横レスで申し訳ありませんが・・

C1セルに下記式を入力します。
=MATCH(2,$A$1:$A$8,0)
C2セルに下記式を入力します。
=IF(C1>=8,NA(),MATCH(2,INDIRECT("$A$"&C1+1&":$A$8"),0)+C1)
３箇所以上ある場合は、C2セルを必要な個数分だけ、下へコピーします。

ご確認お願いいたします。

お礼

saburo0様、ご回答ありがとうございます＾＾

返答遅れまして、大変失礼いたしました＾＾；
質問後寝てしまい、本日は先ほど帰宅したため、式の確認も先ほどさせていただきました。

当方の意図した結果が得られました＾＾
少しの変更でそのまま使用できそうです。

改めて、ご回答ありがとうございました＾＾

回答No.1

ASIMOV

対象が複数有った場合は、どう表示させたいのでしょうか？

補足

ASIMOV様、ご返答ありがとうございます＾＾

質問の例で説明いたします。

Ａ列の２を調べる場合ですが
Ｃ列で上から順に表示するとして

　　Ａ　Ｂ　Ｃ　Ｄ　
１　２　　　１
２　０　　　５
３　１
４　８
５　２
６　９
７　５
８　３

このような感じで考えています＾＾；