検索値から、その行の抽出。

質問

検索値から、その行の抽出。

シート１のデータがあります。
シート２のA１に検索値を置き、“あああ”の行をシート２に抽出。
シート２のA列に任意の番号を振った後、その番号順での表示（ピンク部分）を希望。

自分でいろいろと調べて試行錯誤したのですが、
思うようにいかず、ご質問させていただきます。
ご回答よろしくおねがいします。

回答No.5

tom04 · ベストアンサー

No.1です！
再びおじゃまします。
前回は先走った方法で希望とはかけ離れていたみたいでごめんなさい。

もう一度画像をアップします。下側がSheet2でSheet2のA列に関しては番号を手入力するわけですよね？
その数値の小さい順に表示したい！ということだと思います。

またまた、余計なお世話になるかも知れませんが、Sheet2のA列に入力する数値は1からの連番であれば
もっと簡単な数式でよいのですが、とりあえず、連番でなくても対応できるようにしてみました。

今回は作業用の列は使っていません。
Sheet2のB3セルに
=IF(COUNTIF(Sheet1!$B$2:$B$1000,$A$1)<ROW(A1),"",INDEX(Sheet1!A$2:A$1000,SMALL(IF(Sheet1!$B$2:$B$1000=$A$1,ROW($A$1:$A$999)),ROW(A1))))

これは配列数式になってしまいますので、この画面からB3セルに貼り付け後、
F2キーを押す、または貼り付けセルをダル部クリック、または数式バー内で一度クリックします。
編集可能になりますので、Shift+Ctrlキーを押しながらEnterキーで確定します。
数式の前後に｛　｝マークが入り配列数式になります。
これを列方向と行方向にオートフィルでコピーし、A1セルに入力した銀行名の項目だけを表示させます。
その後、質問者さんがA列に数値を入力します。

そして、F3セルに
=IF(COUNT($A$3:$A$100)<ROW(A1),"",INDEX(A$3:A$100,MATCH(SMALL($A$3:$A$100,ROW(A1)),$A$3:$A$100,0)))
これは配列数式ではありません。このままコピー＆ペーストだけでOKです。

これを列方向と行方向にオートフィルでコピーすると画像のような感じになります。
日付の列はセルの表示形式から「日付」にしておいてください。

尚、数式はSheet1の1000行目まで、そして、抽出後のデータの100行目まで対応できるようにしています。

以上、長々と書きましたが
参考になれば幸いです。m(__)m

お礼

大変おそくなりました。
仕事で使うデータで困っていたので大変助かりました。
本日また、質問をしています。
tom04さんから教えてもらった関数を見て、自分なりに
応用して作ろうと試みたのですが、やはりできませんでした。
似たような関数だと思いますが、もしよろしければ、またご回答お願いします。
ありがとうございました。

回答No.8

MackyNo1

Sheet2のA列に任意の数字を入力するような設定にするなら、以下のような関数でご希望のデータを表示することになります。

B3セルに以下の式を入力し、右方向にE列まで、および下方向に適当数オートフィルします。
（ただし、この数式は多用するとシートの動きが重くなるので注意が必要です）

=IF(COUNTIF(Sheet1!$B$2:$B$１00,$A$1)<ROW(A1),"",INDEX(Sheet1!A:A,SMALL(INDEX((Sheet1!$B$2:$B$100=$A$1)*ROW($A$2:$A$100),),ROW(A1)+COUNTIF(Seet1!$B$2:$B$100,"<>"&$A$1))))

F3セルには以下の式を入力し下方向にオートフィルします。

=IF(A3="","",SMALL($A$3:$A$100,ROW(A1)))

G3セルに以下の式を入力し、右方向にI列まで、下方向に適当数オートフィルします。

=IF($F3="","",VLOOKUP($F3,$A:$E,COLUMN(B1),0))

回答No.7

tom04

No.1・5・6です！
前回の投稿がダブってしまいました。
ダブりすべてを削除できませんでしたので、後のほうの画像だけ削除させていただきました。
本文も画像がある方を読んでください。

何度もごめんなさいね。m(__)m

回答No.6

tom04

No.1です！
前回はかなり先走ってしまって、ご希望とはかけ離れたものになってしまっていたようで
失礼しました。

もう一度画像をアップします。
↓の画像でSheet2の左側のようにA1セルに入力した銀行名の項目を抽出し、A列の番号は質問者さんが手入力し、
それを番号の小さい順に表示したい！ということですよね？
今回は作業列を使わずにできると思います。

Sheet2のB3セルに
=IF(COUNTIF(Sheet1!$B$2:$B$1000,$A$1)<ROW(A1),"",INDEX(Sheet1!A$2:A$1000,SMALL(IF(Sheet1!$B$2:$B$1000=$A$1,ROW($A$1:$A$999)),ROW(A1))))

これは配列数式になってしまいますので、この画面からコピー＆ペーストしただけではエラーになると思います。
B3セルに貼り付け後、F2キーを押す、または貼り付けセルをダブルクリック、または数式バー内で一度クリックします。
編集可能になりますので、Shift+Ctrlキーを押しながらEnterキーで確定します。
数式の前後に｛　｝マークが入り配列数式になります。
このB3セルを列方向と行方向にオートフィルでコピーします。
その後、A列に番号を入力。

もしA列の番号が1からの連番であればもう少し単純な式で大丈夫なのですが、とりあえず、どんな数値を入力しても入力した数値の小さい順に表示するようにしてみました。

F3セルに
=IF(COUNT($A$3:$A$100)<ROW(A1),"",INDEX(A$3:A$100,MATCH(SMALL($A$3:$A$100,ROW(A1)),$A$3:$A$100,0)))
（これは配列数式ではありません。）
として列方向と行方向にオートフィルでコピーします。
日付列は表示形式で「日付」を選択。

尚、数式はSheet1の1000行目・Sheet2に抽出したデータの100行目まで対応できるようにしています。
以上、長々と失礼しました。
参考になればよいのですが・・・m(__)m

この投稿のマルチメディアは削除されているためご覧いただけません。

回答No.4

MackyNo1

例えば、任意の数字が１から始まる連番で、表示条件が日付の若い順に抽出したいなら、以下のような数式で直接A3セル以下にご希望のリストを表示することができます。

A3セル
=IF(COUNTIF(Sheet1!$B$2:$B$100,$A$1)<ROW(A1),"",ROW(A1))

B3セル（セルの書式設定で表示形式を日付にする）
=IF(A3="","",SMALL(INDEX((Sheet1!$B$2:$B$100<>$A$1)*10^10+Sheet1!$A$2:$A$100,),ROW(A1)))

C3セル
=IF(A3="","",$A$1)

D3セル
=IF($A3="","",INDEX(Sheet1!C:C,MAX(INDEX((Sheet1!$A$2:$A$100=$B3)*(Sheet1!$B$2:$B$100=$C3)*ROW($A$2:$A$100),))))

E3セル
D3セルの式を右方向にオートフィルコピー

これら5つのセルを下方向に適当数オートフィルコピーすれば、A列に１から始まる連番でA1セルに入力された銀行のデータが表示されます。

補足

迅速なご回答ありがとうございます。
説明の画像で偶然日付順になったのですが、
日付順というわけではなく、私が付けた番号順での
並び替えができる関数を探しています。

回答No.3

MackyNo1

どのような目的でSheet2のA3以下に「任意」の数字を入力するのでしょうか？

この数字が、単純に１からの連番なのか、それとも全くばらばらの「任意の数字」なのかによって使用する数式が異なります。
また、この数字が例えば日付の早い順に並べ替えたいなどの基準があるなら、該当データをわざわざA列からE列までに抽出しなくても、直接F列からJ列の表を作成することもできます。

ご希望の操作を、目的を含めて補足説明してください。

補足

迅速なご回答ありがとうございます。
説明の画像で偶然日付順になったのですが、
日付順というわけではなく、私が付けた番号順での
並び替えができる関数を探しています。

また、連番ではなく数字の小さいもの順から
並び替えたいと考えています。

下の数字は横並びですが、エクセルで縦並びと考えた時に
15　2　9　6　5　1　この数字を　1　2　5　6　9　15の順に
並び替えたいと希望しています。（任意の番号の部分の説明です）

説明が下手ですいませんがよろしくお願いします。

回答No.2

KURUMITO

Ｆ3セルには次の式を入力してＪ３セルまでオートフィルドラッグしたのちに下方にもオートフィルドラッグします。

=IF(ROW(A1)>MAX($A:$A),"",IF(COLUMN(A1)=1,ROW(A1),VLOOKUP(ROW(A1),$A:$E,COLUMN(A1),FALSE)))

なおＧ列についてはセルの表示形式を日付にします。

回答No.1

tom04

こんばんは！
一例です。
↓の画像で説明します。
とりあえずSheet1の1000行目まで対応できる数式にしてみました。

Sheet1に作業用の列を1列設けています。
作業列E2セルに
=IF(OR(B2="",Sheet2!$A$1=""),"",IF(B2=Sheet2!$A$1,RANK(A2,$A$2:$A$1000,1)*1000+ROW(A1),""))
という数式をいれ、オートフィルで下へずぃ～～～！っとコピーします。

そして、Sheet2のA3セルに
=IF(B3="","",ROW(A1))

B3セルに
=IF(COUNT(Sheet1!$E$2:$E$1000)<ROW(A1),"",INDEX(Sheet1!A$2:A$1000,MOD(SMALL(Sheet1!$E$2:$E$1000,ROW(A1)),1000)))
という数式をいれ、B3セルを列方向にE3セルまでオートフィルでコピーします。
尚、列方向にコピー後、日付の列は表示形式で「日付」にしておきます。

最後にA3～E3セルを範囲指定し、E3セルのフィルハンドルで下へコピーすると
画像のような感じになります。

以上、参考になればよいのですが・・・m(__)m

補足

迅速なご回答ありがとうございます。
説明の画像で偶然日付順になったのですが、
日付順というわけではなく、私が付けた番号順での
並び替えができる関数を探しています。

また、連番ではなく数字の小さいもの順から
並び替えたいと考えています。

下の数字は横並びですが、エクセルで縦並びと考えた時に
15　2　9　6　5　1　この数字を　1　2　5　6　9　15の順に
並び替えたいと希望しています。（任意の番号の部分の説明です）

説明が下手ですいませんがよろしくお願いします。