エクセルで一つのセルに別Bookのデータを貼り付け

質問

エクセルで別Bookにある複数の参照データを一つのセルに
貼り付けたいのですが、

例えばBook1には以下のようなデータがあります。

　　　A列　　　　　　　B列
１　バナナ　　　　　カテゴリ１
２　もも　　　　　　　カテゴリ１
３　りんご　　　　　　カテゴリ１
４　メロン　　　　　　カテゴリ１
５　バナナ　　　　　カテゴリ２
６　みかん　　　　　カテゴリ２
７　もも　　　　　　　カテゴリ３
８　バナナ　　　　　カテゴリ３
９　いちご　　　　　カテゴリ３
１０　もも　　　　　　カテゴリ４

Book2にはB列にカテゴリ１・カテゴリ２・カテゴリ３というように、
項目が入力されています。

Book1を参照して、B列のカテゴリ１に当てはまるA列の値（例ではバナナ、もも、りんご、メロン）を
Book2のカテゴリ１の横（A１）の一つのセル内に貼り付けたいのですが
可能でしょうか？

　　　A列　　　　　　B列
１　バナナ
　　もも　　　　　　　カテゴリ１
　　りんご
　　メロン
２　バナナ　　　　　カテゴリ２
　　みかん　
３　もも　　　　　　　カテゴリ３
　　バナナ
　　いちご　　　　　　　　　　　　

つまり、Book2を上記のような形にできればと思います。

拡張子は　.xlsxです。
A列の項目とB列のカテゴリ数は数1000ほどあり、
各カテゴリの数は１つのものもあれば、１０数個あったりと不揃いです。

カテゴリに見合わせてコピペしていくのは大変なので
良い方法があれば、ご教授宜しくお願い致します。

回答No.2

MarcoRossiItaly · ベストアンサー

＞フィルターを使ったりもしましたが、目視で抽出する方が早かったです。

各カテゴリーのデータ数が揃っていないのなら、やはりNo.1さんのおっしゃるように、フィルタがいちばんラクだと思いますよ。データ量が多いなら、なおさら。目視のほうが早いとしたら、フィルタの使い方を十分に理解できていないか、入力されているデータの表記の方法がぐちゃぐちゃで不揃いかの、どちらかかと。

後者だとしたら、データ抽出よりも、データそのものを修正して揃えることのほうが先です。修正の作業も、フィルタを使いながら行うのが効率的なはずです。

さて、フィルタで「カテゴリ１」のレコードのみ抽出し、その表をコピーします。それをBook2の中の「カテゴリ１」というシートに貼り付けて保存します。次のとおりシート「カテゴリ１」に入力された状態にします。

　　　A列　　　　　　　B列
１　バナナ　　　　　カテゴリ１
２　もも　　　　　　　カテゴリ１
３　りんご　　　　　　カテゴリ１
４　メロン　　　　　　カテゴリ１

続いて、シート「カテゴリ１」に次の数式を入力。式を確定後、C2セルから下方向にC4までオートフィル。

C1　=a1
C2　=c1&"、"&a2

最後に、質問文中で「上記のような形」とおっしゃっているシートにおいて、次の数式を入力。

A1　=index(indirect(b1&"!c:c"),counta(indirect(b1&"!c:c")))

なお「カテゴリ１」というシート名を変更すると参照が機能しないので、注意。

お礼

大変ご丁寧なご回答、ありがとうございました。
結局、フィルターを使うことにしましたが、
こういうやり方もあると、勉強になりました。
ありがとうございました♪

回答No.3

MarcoRossiItaly

ああ、すみません。No.2です。最終結果は、改行を入れた形にしたいのでしたね。それでしたら、No.2の数式を、次のとおり差し替えてください。

シート「カテゴリ１」のC2

=c1&"
"&a2

改行を文字列や式の中に入れるには、キーボードでAlt+Enterと入力します。

その上で、INDEX関数が入力される列のセルの書式を、「折り返して全体を表示する」に設定してください。

回答No.1

chokotto0517

freak37様

手っ取り早い方法はフィルター機能を使って抽出することでしょうか。

カテゴリー１で抽出したデーターをBook2にテキスト貼り付け

この方法だと、Book2で昇順/降順に並べ替えたりもできますが。

お礼

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

フィルターを使ったりもしましたが、目視で抽出する方が早かったです。
でもデータ数が多くて大変なので、
出来ればマクロや関数を使って行いたいのですが・・・