Excel2000 VLOOKUPで検査値は一緒で、列内容が違うものの全てをひっぱりたい

質問

初心者です。
似たような質問はいくつか見つかるのですが、理解力不足で自分に当てはめることが出来ません。
どうか、宜しくお願い致します。

sheet1
A　　　　　B　　　　D　　（←列番号）
No.1　　　　田中　　資格あ　　
No.1　　　　田中　　資格い
No.1　　　　田中　　資格う
No.2　　　　佐藤　　資格あ
No.2　　　　佐藤　　資格い
No.3　　　　鈴木　　資格あ
No.3　　　　鈴木　　資格い
No.3　　　　鈴木　　資格う
No.3　　　　鈴木　　資格え
No.3　　　　鈴木　　資格お
No.5　　　　西野　　資格あ

sheet1に上記のデータがあるとして、
ｓｈｅｅｔ2に検査値（No.）を入力し、その値と同じ人の資格名（C列）をひっぱりたいのです。

↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓
sheet2
A　　　　　B　　　　D　　（←列番号）
No.3（検査値）
資格あ
資格い
資格う
資格え
資格お
↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓

A1：=vlookup(A1,sheet1!A2:D6,3,)とすると、
1行目の「資格あ」だけは出てきます。
A2：資格い
A3：資格う・・・・と表示させたいのですが、
A2以降はどのような式を入れればよいのでしょうか。
どなたか教えて下さい。

１つ条件として、作業列は作れないということです。

どうか、宜しくお願い致します。

回答No.6

kaisendon

tasyさんありがとうございます。
作業列を使用できない理由を答えて頂き、感謝しています。
すっきりしました。

No1さんのお礼に所に『式の解説を…』と書いてあったので、
作業列を使えない理由を教えて頂いたお礼の意味を含めて、
私の式でよければ私の式の意味を解説してみたいと思います。

=IF(COUNTIF(Sheet1!$A$1:$A$15,$A$1)<ROW()-1,"",INDEX(Sheet1!$C$1:$C$15,SMALL(IF(Sheet1!$A$1:$A$15=$A$1,ROW(A$1:A$15)),ROW()-1)))

ポイントとしては、配列の範囲の行数を揃えるという事。
私の式では１５行分の配列を式に入れています。
No.4さんの式では、A2～A100とA1～A99で９９行分の配列です。

No4さんの式の場合、
Sheet1　A2　　　ROW(A1）
　　　　　　：　　　　　：
同　　　A100　　　ROW（A99）
各配列同士で、それぞれの行はこのように対応しています。

もう一つのポイントは、
この式はROW関数が３箇所に使われています。
このROW関数で作られる連番の数字をどう使っているのかを理解することが、この式を応用する為の早道だと思います。

質問の表で、列番号　D　が　C　だとして、
式を分解してみます。

COUNTIF(Sheet1!$A$1:$A$15,$A$1)
（Sheet2の）A1セルに入力された『検索値』が
Sheet1のA1～A15にいくつあるか数えます。
この数がSheet2でデータを表示させるのに必要な行数です。

IF(COUNTIF(Sheet1!$A$1:$A$15,$A$1)<ROW()-1,"",
先に数えた数を、表示される列の行番号を比較します。
この式は（Sheet2の）A2セルに入れたので、
A2セル　＝　表示させたいセルの一行目　＝　ROW()-1　＝　1
A3セル　＝　表示させたいセルの二行目　＝　ROW()-1　＝　2
と、変化していきます。
（後で出てくるSMALL関数の第二引数[順位]も、同じように）

質問文にあるデータでNo.5だと
A2セルでは　IF(1<{1}　→　{FALSE}　→　[偽の場合]のINDEX関数へ
A3セルでは　IF(1<{2}　→　{TRUE}　→　[真の場合]で　""に。

ここまでは、「エラー処理」と「表示されるのに必要な行を確保」をしているだけです。

INDEX(Sheet1!$C$1:$C$15,SMALL(IF(Sheet1!$A$1:$A$15=$A$1,ROW(A$1:A$15)),ROW()-1)))

INDEX(Sheet1!$C$1:$C$15
抽出したいSheet1のC1～C15のデータ範囲を配列として指定しています。

IF(Sheet1!$A$1:$A$15=$A$1,ROW(A$1:A$15)
私が作業シートのA列に入れた式
=IF(Sheet1!A1=Sheet2!$A$1,ROW(),"")　と同じです。
便宜上、『存在しない架空の作業列』とでも言えばいいでしょうか。
（Sheet2の）A1セルに入力された値と等しいものがSheet1のA1～A15にあった場合、ROW(A$1:A$15)で、その行に対応する行番号を『架空の作業列』に求め出しています。
（このIF関数の[偽の場合]は、不要なので省略しています）

元データが２行目からなら、
INDEX(Sheet1!$C$2:$C$16,SMALL(IF(Sheet1!$A$2:$A$16=$A$1,ROW(A$1:A$15))
３行目からなら、
INDEX(Sheet1!$C$3:$C$17,SMALL(IF(Sheet1!$A$3:$A$17=$A$1,ROW(A$1:A$15))
とかにすればよいでしょう。
ここのROW関数の配列は、元データの一行目を　１　としたいので、
数式を修正させる場合でも変化させません。
この場合、それぞれの１５行分の配列が、
先に書いた『No4さんの式の場合』のように、対応する配列になっています。

SMALL(……,ROW()-1)
作業シートのB列　=SMALL(A:A,ROW())　と同じです。
先ほど『架空の作業列』として求めた行の番号を小さい順上から詰めて並べるものです。
作業シートのB列の結果がどのようになっていたのかを見ると判りやすいかと思います。

結果を表示するセル（Sheet2のA2）が２行目なので、
SMALL関数の第二引数[順位]を、ROW()-1 として、
一行目は　SMALL(……,1)　二行目は　SMALL(……,2)　になるように調整しています。
（結果を表示するセルが３行目からならば、SMALL(……,ROW()-2)にするとか）
これで、数式を下にコピーする事により『架空の作業列』の値も、作業シートのB列のように並び変わります。

あとは、INDEX(Sheet1!$C$1:$C$15,行番号
これで、必用なデータを抽出して終わりです。
作業シートB列の値を見ながら、Sheet2のA2以下のセルで、
INDEX(Sheet1!$C$1:$C$15,作業シート!B1)
これを使い抽出していたのと同じです。

まあまあ、長々となってしまいましたが、
ざっくりと言えば、

=IF(COUNTIF(Sheet1!$A$1:$A$15,$A$1)<ROW()-1,"",INDEX(Sheet1!$C$1:$C$15,SMALL(IF(Sheet1!$A$1:$A$15=$A$1,ROW(A$1:A$15)),ROW()-1)))

（Sheet2の）A1と同じものが、Sheet1のA1～A15にいくつあるか数えて、
表示させるのに不必要な分は非表示に、
（Sheet2の）A1に入力された値と同じ値がSheet1のA1～A15にあれば、
それが、配列の何行目なのか、行番号を求めて、
その数字（行番号）を小さい順に（フィルコピーで）上から詰めるように並べる。
小さい順に並べられた数字が、INDEX関数で指定した配列（C1～C15）の行番号に相当するので、それを抽出する。

以上です。
お疲れ様でした(^_^;)

回答No.5

tom04

No.4です！
たびたびごめんなさい。

投稿した後他の方の回答を読ませていただいたところ
No.1さんとダブっていました。

前回の回答は無視してください。

どうも失礼しました。m(__)m

回答No.4

tom04

こんばんは！
今回は氏名は関係なく、検査値Noだけで資格を表示すれば良いのですね？

作業列を使わないのが希望のようなので・・・

配列数式になってしまいますが、

↓の画像で説明させていただくと

Sheet2のA2セルに
=IF($A$1="","",IF(COUNTIF(Sheet1!$A$2:$A$100,$A$1)>=ROW(A1),INDEX(Sheet1!$C$2:$C$100,SMALL(IF(Sheet1!$A$2:$A$100=Sheet2!$A$1,ROW($A$1:$A$99)),ROW(A1))),""))
という数式を入れて
Shift＋Ctrl＋Enterキーを押します。

すると数式の前後に｛　｝　マークが入り配列数式になります。
それをオートフィルで下へコピーすれば
画像のような感じになります。

尚、この画面から数式をコピー＆ペーストする場合は
貼り付けただけではエラーになると思いますので
F2キーを押すか、数式バー内で一度クリックし、編集可能にした後に
Shift＋Ctrl＋Enterキーを押します。

尚、数式は100行目まで対応できる数式にしていますが、
データ量によって範囲指定はアレンジしてみてください。

以上、参考になれば幸いですが、
他に良い方法があれば読み流してくださいね。m(__)m

回答No.3

某HN クロメート(Chromate)（@CoalTar）

作業セルならいいのかな？
もちろん、使わなくても良いのですが同じ計算を何回もするので作業セルを使ってみました。
Sheet1のNo.は添付図のように数値とします
また、No.で昇順(降順)で並んでいるとします。
H1セルは =COUNTIF(A:A,F1)
J1セルは =MATCH(F1,A:A,0)
F2セルは
=IF($H$1>=ROW()-1,INDEX(D:D,$J$1+ROW()-2),"")
下へオートフィル
ちなみに、No.は数値でなくても良いのですが、No.を使ってまとめる作業に昇順(降順)が都合が良いので、数値としています

No.でまとまっていない場合は、オートフィルタやフィルタオプションも検討してみてください。

回答No.2

kaisendon

tasyさんすみません、後学の為に教えてください。
なぜ、作業列は作れないのですか？

tasyさんが仰っている通り、似たような質問はいくつか見かけます。
そして、こういった物を作業列を使わずに行おうとすると、
No1さんのような感じになるのがお決まりのパターンです。

私が考えたのも同じような式です。
=IF(COUNTIF(Sheet1!$A$1:$A$15,$A$1)<ROW()-1,"",INDEX(Sheet1!$C$1:$C$15,SMALL(IF(Sheet1!$A$1:$A$15=$A$1,ROW(A$1:A$15)),ROW()-1)))
（Ctrl + Shift Enter で配列数式として確定 → 下にコピー）

この式って難しいですよね？
作業列を使わないで、一発で求める事ができる式が
必ずしも「いい式」だとは思えません。

この式は、作業列を使いたくないが為に、
無理やりまとめて肥大化した式のような気がしてならないのです。

求めたい通りの結果が返ってくればそれでOK、ならば、
それはそれでいいのですが、
後々のメンテナンス性とか、別の方に引き継ぐ時とかの事を考えると、
好ましい式とは言えないと思います。

誰かがSheet2のA列をうっかり触ってしまって編集状態になり、
慌ててEnterで確定したら、式の｛　　｝が外れてしまって、
配列数式の意味を成さなくなってしまう、とか往々にしてありそうで怖いです。

レイアウトの関係とか、見た目とかの問題で作業列を使いたくないのであれば、『作業シート』を作ってそれを利用するのも一つの手だと思います。

例えば、作業シートのA1に
=IF(Sheet1!A1=Sheet2!$A$1,ROW(),"")
同じくB1に
=SMALL(A:A,ROW())
それぞれ、下に必用なだけコピー

Sheet2のA2に
=IF(COUNT(作業シート!A:A)<ROW()-1,"",INDEX(Sheet1!$C$1:$C$15,作業シート!B1))

このようにしておけば、作業シートのA列、B列の式が、
どのような処理を行っているのか明確になります。
ROW関数の返り値だけ気をつけていけば、
さほど難しい処理をしているわけではありません。

このように、式の可読性を高めて、自分が、あるいは他の人が見た時に、
少しでもわかりやすい処理にしておいた方が、後々何かと楽なのではないかと個人的には思っています。

まあ、こういったQ&Aの場合、
質問者は、一発で求めたい結果が返る式であれば、
数式が多少長くても、配列数式でも、なんでもいい、という傾向があるのは否めないですし、
回答者は、一発でドーンと答えの出る配列数式の方が回答しやすいという感じもあります。
まあ、私もその回答者の一人なんですけど(^_^;)
（実際、作業列の式がどうだこうだ書くより、先に書いた配列数式だけ書いて終わり、の方がよっぽど楽ですしｗ）

ただ、
> １つ条件として、作業列は作れないということです。
これがどうにも引っかかって仕方がないのです。
もちろん社内で使うのであれば、何かしらの制限やら、ご事情やらがある事でしょうから、表の構成の自由度が低くなるのは仕方のない事なのでしょうけれども、
冒頭に書いたように、
『何故作業列が使えないのか？』
これがどうしてもわからないのです。
後学の為に、作業列が作れない訳を教えて頂けませんでしょうか？

お礼

kaisendonさん、ありがとうございます。

そうですね。作業列や作業シートを使用できればもっと明確なのかもしれません。

今回、それが出来ないのは、
データをMSQueryでMSAccessからひっぱってきており、
指定したsheet1にデータを出力させ、フォーマットとしてあるsheet2に式で反映させているのですが、
そのフォーマットを作成し、印刷後、次の分を作成する為にsheet1の全データを削除するようになっているからです。
これは仕様の為、修正できません。

シートを勝手に増やすことも出来ない決まりなのです。

フォーマットのsheet2の中に作業列を作るのも、禁止となっています。

これがどんなおかしな理由であっても、
どんなに正当性があっても動かせない決まりなので、
今回はどうすることも出来ないのです。

ですが、kaisendonさんの教えてくださった作業シート版は大変参考になります。
ありがとうございました。

回答No.1

mu2011

VLOOKUP関数では１つしか抽出できません。
一例です。
sheet2のA2に以下の数式を設定、必要分下方向にコピーして下さい。
尚、配列数式の為、入力完了時にshift＋ctrl＋enterキーを同時押下して下さい。
=IF(COUNTIF(Sheet1!A:A,$A$1)>=ROW(A1),INDEX(Sheet1!C:C,SMALL(IF(Sheet1!$A$1:$A$1000=$A$1,ROW(Sheet1!$A$1:$A$11),99999),ROW(A1))),"")

補足

mu2011さんありがとうございます。

上記の簡単なデータで当てはめると出来るのですが、
本データでやってみるとエラーになってしまいます。

式の解説をお願いできませんでしょうか？

どうかお願いします。