エクセル 検索と表示

質問

エクセル２０１０を使っています

画像の様なデータがあり、Ｃ１～Ｌ１に文字が入力された時に、Ａ列から同じ文字を検索し、Ｂ列の文字を【上から順に】表示させたいと思います。

出来れば簡単な関数でお願いします。

詳しいかたよろしくお願いいたします。

回答No.3

keithin · ベストアンサー

エクセル２０１０ではもっと簡単な数式で、

Ｃ２：
=IFERROR(INDEX($B:$B,SMALL(IF($A:$A=C$1,ROW(C:C)),ROW(C1))),"")
と記入し、必ずコントロールキーとシフトキーを押しながらＥｎｔｅｒで入力
下にコピー、右にコピー

のようにできます。

お礼

回答ありがとうございます。

関数を極めればこんなに簡単なんですね。

助かりました。

回答No.2

bunjii

>画像の様なデータがあり、Ｃ１～Ｌ１に文字が入力された時に、Ａ列から同じ文字を検索し、Ｂ列の文字を【上から順に】表示させたいと思います。
画像ではD1～M1に名前が入力されているように見えますので画像に沿って回答します。

>出来れば簡単な関数でお願いします。
簡単な関数と言う訳にはいかないようですので解説を付けさせて頂きます。
A列の名前と1行目の名前が一致する論理式を使い一致する行番号を配列で取り出します。
=（$A$2:$A$1000=D$1)*ROW(A$2:A$1000)
この配列から小さい値の順に該当の行番号を取り出します。
=SMALL(($A$2:$A$1000=D$1)*ROW(A$2:A$1000),COUNTIF($A$2:$A$1000,"<>"&D$1)+ROW(A1))
此処で1行目の名前とA列が一致しない行は0になりますのでその分の数を追加した順番にしなければなりません。
数式の中で配列値を扱うときは数式の確定時にCtrl+Shift+Enterの打鍵が必要です。
しかし、SUMPRODUCT関数を使うと通常通りのEnterキーによる確定でも正常に動作します。
よって、今回の数式はSUMPURODUCT関数を使うこととします。
D2=IF(D$1="","",IFERROR(INDEX($B:$B,SUMPRODUCT(SMALL(($A$2:$A$1000=D$1)*ROW(A$2:A$1000),COUNTIF($A$2:$A$1000,"<>"&D$1)+ROW(A1))),1),""))
上記数式でIFERROR関数はExcel 2007以降のバージョンに組み込まれたもので、2003以前のバージョンでは利用できません。
D2セルを右と下へ必要数コピーすれば目的達成です。
尚、元データの最大行番号を1000としていますので実際のデータに合わせて修正してください。

回答No.1

MackyNo1

データ数が少なければ補助列を使わない関数も考えられますが、すべてのデータを組み替えて表示することをしたいようなので、以下のような補助列を使った関数が簡単かもしれません。

提示されたデータなら、A列を選択して右クリック「挿入」でA列を挿入し、A2セルに以下の式を入力し下方向にオートフィルコピーします。

=COUNTIF($B$2:B2,B2)&B2

E1セルから右方向に名前が入力されているなら、E2セルに以下の式を入力し、右方向および下方向にオートフィルコピーします。

=IFERROR(VLOOKUP(ROW(1:1)&E$1,$A:$C,3,0),"")

必要に応じてA列を非表示にすれば、元のデータと同じレイアウトになります。

ちなみに、このデータ範囲を「テーブルとして書式設定」しておけば、データの追加に対応してA列の数式が自動的に入力されます。