VLookup関数でのソートについて

Question

お世話になります。
エクセルのVLookup関数でのソートについて質問いたします。検索して、似たような質問がなかったので、こちらで質問させていただきます。

画像をご覧ください。sheet１とsheet２の２つがあります。sheet１が元データで、sheet２にIDを入力すれば、その該当IDのみ、sheet１でソートされ、sheet２に値で張り付けられるような、関数？やり方は何か
ないでしょうか？簡単に出来そうですが、なかなかよいやり方が見つかりません。

どうぞよろしくお願いします。

tom04 · Accepted Answer

No.2です。
Sheet1の「○」部分は実際は数値だというコトなので・・・
もう一度画像をアップしてみます。
今回は作業用の列を設けて配列数式にしないようにしてみました。

画像のF2セルに
=IF(A2=Sheet2!$A$1,ROW(),"")
という数式を入れオートフィルでずぃ～～～！っとしっかり下までコピーしておきます。

Sheet2のA4セルに
=IF(COUNT(Sheet1!$F:$F)<ROW(A1),"",INDEX(Sheet1!A:A,SMALL(Sheet1!$F:$F,ROW(A1))))

このままではSheet1が空白の場合は「0」が表示されてしまいますので、
A4セルの表示形式をユーザー定義から

G/標準;G/標準;

としておきます。
（オプションから「ゼロ値」のチェックを外せば、セルの表示形式には手を加える必要はありません）
これを列方向・行方向にオートフィルでコピーすると画像のようになります。

※　前回の数式では空白セルに「0」を表示させないようにしたため表示されたデータは文字列になっていました。

次に
＞上からコピーして値で張り付ければよいのですが、IDの数が数百ありますので、その都度教えていただいた関数を張り付けるのは大変ですので

に関してですが、
数式はそのままでSheet2のA1セルのIDを入力すればそのデータが表示されます。

仮に、IDすべてを順番に表示したい！という場合は
VBA等別の方法を考える必要があると思います。

この程度でごめんなさいね。m(_ _)m

KURUMITO · Answer

どうやらシート1のB列からE列に入力されているデータは文字列で入力されているようですね。そのためにシート2に表示されたデータでもSUM関数などが機能しないのですね。
データをシート2に取り込む段階で数字を数値に変えるために＊1などの工夫をすればよいでしょう。
配列数式などの難しい関数を使うことなく、作業列を作ってい対応することにします。
シート2のA1セルには抽出のためのIDを入力しるとして、シート1ではお示しのようにデータが有るとしたらF4セルには次の式を入力して下方にドラッグコピーします。

=IF(A4="","",IF(A4=Sheet2!A$1,MAX(F$3:F3)+1,""))

そこで抽出の結果をシート2の2行目は項目名として、A3セルには次の式を入力してD3セルまでドラッグコピーしたのちに下方にもドラッグコピーします。

=IF(ROW(A1)>MAX(Sheet1!$F:$F),"",INDEX(Sheet1!$B:$E,MATCH(ROW(A1),Sheet1!$F:$F,0),COLUMN(A1))*1)

文字列に変える手段は他にもいろいろあるでしょう。ただ、シート2で計算ができなかった問題は上記の方法で解決するでしょう。

kagakusuki · Answer

　今仮に、IDは数値データであり、Sheet2においてIDを入力するのがA2セルであり、Sheet2において「条件1」という項目名が入力されているのがA6セルであるものとします。

【方法その1】作業列を使用する方法です。
　まず、適当な未使用シート（ここでは仮にSheet3とします）のA4セルに次の関数を入力して下さい。

=IF(AND(COUNTIF(Sheet1!$A:$A,Sheet2!$A$2),INDEX(Sheet1!$A:$A,ROW())=Sheet2!$A$2),COUNTIF(Sheet1!$A$3:INDEX(Sheet1!$A:$A,ROW()),Sheet2!$A$2),"")

　そして、Sheet3のA4セルをコピーして、Sheet3のA5以下に貼り付けて下さい。

　次に、Sheet2のA7セルに次の関数を入力して下さい。

=IF(ISERROR(1/(INDEX(Sheet1!B:B,MATCH(ROWS($7:7),Sheet3!$A:$A,0))<>"")),"",INDEX(Sheet1!B:B,MATCH(ROWS($7:7),Sheet3!$A:$A,0)))

　次に、Sheet2のA7セルをコピーして、Sheet2のB7～D7の範囲に貼り付けて下さい。
　次に、Sheet2のA7～D7の範囲をコピーして、同じ列の8行目以下に貼り付けて下さい。

【方法その2】作業列を使わずに、全自動で処理を行いますが、元データの表の行数が数千行にもなる場合には、処理が重くなってしまう方法です。
　まず、Sheet2のA7セルに次の関数を入力して下さい。

=IF(OR(ROWS($7:7)>COUNTIF(Sheet1!$A:$A,$A$2),ISERROR(1/(INDEX(Sheet1!B:B,SUMPRODUCT(ROW(Sheet1!$A$3:INDEX(Sheet1!$A:$A,MATCH(9E+307,Sheet1!$A:$A)))*(Sheet1!$A$3:INDEX(Sheet1!$A:$A,MATCH(9E+307,Sheet1!$A:$A))=$A$2)*(COUNTIF(OFFSET(Sheet1!$A$3,,,ROW(Sheet1!$A$3:INDEX(Sheet1!$A:$A,MATCH(9E+307,Sheet1!$A:$A)))-ROW(Sheet1!$A$3)+1),$A$2)=ROWS($7:7))))<>""))),"",INDEX(Sheet1!B:B,SUMPRODUCT(ROW(Sheet1!$A$3:INDEX(Sheet1!$A:$A,MATCH(9E+307,Sheet1!$A:$A)))*(Sheet1!$A$3:INDEX(Sheet1!$A:$A,MATCH(9E+307,Sheet1!$A:$A))=$A$2)*(COUNTIF(OFFSET(Sheet1!$A$3,,,ROW(Sheet1!$A$3:INDEX(Sheet1!$A:$A,MATCH(9E+307,Sheet1!$A:$A)))-ROW(Sheet1!$A$3)+1),$A$2)=ROWS($7:7)))))

　次に、Sheet2のA7セルをコピーして、Sheet2のB7～D7の範囲に貼り付けて下さい。
　次に、Sheet2のA7～D7の範囲をコピーして、同じ列の8行目以下に貼り付けて下さい。

keithin · Answer

＞この「○」は実際は数字（１～３）のどれかに変わります。

ウソ情報に伴う典型的な二度手間ですね。

ではとりあえず簡単な方法として、方法１と２のどちらも、数式に「＆””」と記入してあるこの３文字を「消去」してください。
２番目の方法ではＣｔｒｌ＋Ｓｈｉｆｔ＋Ｅｎｔｅｒを忘れず行ってください。

空白欄には数字の「ゼロ」が現れますので、数式を記入したセル範囲を選択
セルの書式設定の表示形式でユーザー定義を開始
#
と設定してＯＫしてください。

＃合計の求め方
ふつーにSUM関数でもとめます

＃平均の求め方
=SUM(範囲)/COUNTIF(範囲,">0")
と求めます

それとも。数式が簡単じゃなくてももう構わないからふつーに合計や平均を計算したいなら
方法１では、シート２のＡ４に
=IF(ROW(A1)>COUNTIF(Sheet1!B:B,$A$1),"",IF(VLOOKUP($A$1&TEXT(ROW(A1),"-000"),Sheet1!$A:$F,COLUMN(C1),FALSE)=0,"",VLOOKUP($A$1&TEXT(ROW(A1),"-000"),Sheet1!$A:$F,COLUMN(C1),FALSE)))
と記入、右にコピー、下にコピーします。

方法２はこれ以上複雑になるとどのみち応用も効かないので、諦めましょう。

tom04 · Answer

こんばんは！
一例です。

データ量が多い場合はあまりオススメできませんが・・・

↓の画像でSheet2のA4セルに
=IF(COUNTIF(Sheet1!$A:$A,$A$1)<ROW(A1),"",INDEX(Sheet1!A$1:A$1000,SMALL(IF(Sheet1!$A$1:$A$1000=$A$1,ROW($A$1:$A$1000)),ROW(A1)))&"")

これは配列数式になってしまいますので、Shift＋Ctrl＋Enterキーで確定します。
これを列方向・行方向にオートフィルでコピーすると
画像のような感じになります。

※　この画面から数式をコピー＆ペーストする場合は、セルに貼り付けただけでは配列数式になりませんので
貼り付け後数式バー内で一度クリック！
編集可能になりますので、Shift＋Ctrlキーを押しながらEnterキーで確定！
数式の前後に｛　｝マークが入り配列数式になります。

※　とりあえずSheet1の1000行まで対応できる数式にしていますが、
もっとデータがある場合は別に作業用の列を設けた方が良いと思います。

参考になりますかね？m(_ _)m

keithin · Answer

非常にしばしば寄せられるご相談の一種で、他の計算ソフトなら簡単にできるものもありますが、エクセルのVLOOKUP関数をはじめとする検索関数は、答えを「１つだけ」しか取ってこれないので、ご質問のようなことを簡単な関数でキレイに片づける方法はありません。

○簡単にVLOOKUP関数でやっつける方法（推奨）
シート１のＢ列にＩＤを記述、ＣからＦ列にデータを記入
１行目はタイトル行、２行目からデータ
Ａ２に
=IF(B2="","",B2&TEXT(COUNTIF($B$2:B2,B2),"-000"))
と記入、以下コピー。

シート２のＡ１にＩＤを記入
シート２のＡ４に
=IF(ROW(A1)>COUNTIF(Sheet1!B:B,$A$1),"",VLOOKUP($A$1&TEXT(ROW(A1),"-000"),Sheet1!$A:$F,COLUMN(C1),FALSE)&"")
と記入、右にコピー、下にコピー。

○お勧めではない方法
シート１のＡ：Ｅ列に元データ
１行目はタイトル行、２行目からデータ

シート２のＡ１にＩＤを記入
シート２のＡ４に
=INDEX(Sheet1!B:B,SMALL(IF(Sheet1!$A:$A=$A$1,ROW(Sheet1!B:B),9999),ROW(A1)))&""
と記入し、必ずコントロールキーとシフトキーを押しながらＥｎｔｅｒで入力
右にコピー、下にコピー。

VLookup関数でのソートについて

No.2です。

どうやらシート1のB列からE列に入力されているデータは文字列で入力されているようですね。

今仮に、IDは数値データであり、Sheet2においてIDを入力するのがA2セルであり、Sheet2において「条件1」という項目名が入力されているのがA6セルであるものとします。

＞この「○」は実際は数字（１～３）のどれかに変わります。

こんばんは！

このQ&Aを見た人はこんなQ&Aも見ています

関連するカテゴリからQ&Aを探す

今、見られている記事はコレ!

このQ&Aを見た人がよく見るQ&A

デイリーランキングこのカテゴリの人気デイリーQ&Aランキング

マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング

　今仮に、IDは数値データであり、Sheet2においてIDを入力するのがA2セルであり、Sheet2において「条件1」という項目名が入力されているのがA6セルであるものとします。