エクセル：複数行のセル値を、対応した項目に従って一つのセルにまとめたい

質問

いつもお世話になっています。
早速ですが、下記のようなことをしたいのですが、
関数でできるでしょうか？

　│　Ａ　　　 　│　Ｂ
-----------------------
1│日本太郎 │ ばら
-----------------------
2│日本太郎 │ さば
-----------------------
3│山田花子 │ キリン
-----------------------
4│山田花子 │ 米
-----------------------
5│日本太郎 │ イス
-----------------------
6│山田花子 │ インク
-----------------------

↓

　│　　Ａ　 　│　Ｂ
-----------------------
　│ 　　　　　 │ ばら
1 │日本太郎│ さば
　│　　　 　 　│ イス
-----------------------
　│ 　　　　 　│ キリン
2 │山田花子│ 米
　│ 　　　 　　│ インク
-----------------------

Ａ列に人物名、Ｂ列に品物を入力します。
誰かが何かを入手した際、その順に入力していきますので、
同一の人物が複数の行に亘って入力された表（上の表）があります。

これを基に「誰が何を持っているか」をまとめる表（下の表）を作りたいのですが、
上の表を基に下の表が自動的に出来上がるような関数はあるでしょうか？
VLOOKUPを使っても上手く出来ず、しかし他に思い浮かびません。

何か良い知恵がございましたら、お教え下さい。

回答No.3

noname#52504 · ベストアンサー

関数でもできなくはないですが、
下記のように作業列を複数使ったかなり複雑な処理になります。

以下、質問文上の表がSheet1にあり、下の表をSheet2に作るとします。

Sheet1!C1セル：=COUNTIF($A$1:A1,A1)
Sheet1!D1セル：=A1&C1
Sheet1!E1セル：=IF(C1=1,"",OFFSET($E$1,MATCH(A1&(C1-1),$D$1:D1,0)-1,)&CHAR(10))&B1
Sheet1!F1セル：=COUNTIF($C$1:C1,1)
として、C,D,E,F列を適当な行までフィル。
必要に応じて列を非表示に。

Sheet2!A1セル：=IF(ISNA(MATCH(ROW(),Sheet1!$F$1:$F$1000,0)),"",OFFSET(Sheet1!$A$1,MATCH(ROW(),Sheet1!$F$1:$F$1000,0)-1,))
Sheet2!B1セル：=IF(A1="","",VLOOKUP(A1&COUNTIF(Sheet1!$A$1:$A$1000,A1),Sheet1!$D$1:$E$1000,2,0))
として、適当な行までフィル。
Sheet2!B列について、セルの書式設定>配置>折り返して全体を表示する　にチェック

Excel2003で動作確認。

Sheet2の行の高さは元データが変更されても自動では変わらないので、
その都度調整するか、あらかじめ十分な高さに設定しておく必要があります。

お礼

ありがとうございます。
ご教示の関数で、ほぼ私の考えていたことができました。
そこで少し拡張しようと思い、Sheet1の人物名の前に列を挿入して法人名を付けた場合（人物名をB列にして代表者の列とする）で、法人別でSheet2をまとめようとすると、
「法人名は違うが代表者名が同じ」と言う項目は上位の代表者にまとめられ、下位の代表者名や品名は「N#A」となり、どうしても上手く出来ませんでした。
下位の代表者名にスペースを加えると上手く分類できましたが、人物名に手を加えないで出来る方法を模索中です。
しかし、それでも思っていたことがほぼできたので、非常に助かりました。
ありがとうございました。

回答No.4

noname#52504

#3です。

●法人名を加える件について。

新たに作業列を加えて「法人名」と「代表者名」を結合した「一意な名前」を生成し、
#3の処理を「人物名」ではなく「一意な名前」をキーにして行えば、
代表者名が重複した場合もカバーできます（動作確認済）

また、法人ごとに並べることも可能ですが、
関数によるソートは別個の大きなハナシになりますので割愛します。

●蛇足アドバイス

(#3の時点で既にそうですが)こういった複雑な処理になりますと、
作成した時点ではともかく、将来的に保守や引継ぎの面で問題が発生しそうな気がします。
一長一短ありますが、この際マクロ(VBA)による処理を検討された方が良いかもしれません。

また、この処理でネックになっているのは、「一つのセルに纏める」という要件です。
おそらく「見た目」あるいは「セルを結合する手間」の問題だろうと推察しますが、
この点については、ソート結果に対し「条件付書式」で枠線処理をするというアプローチもあります。
「枠線非表示」にするかあるいは対象範囲を白背景で塗り潰してから、
「上隣セルと同値なら白字」＆「上隣セルと異値なら上辺罫」などとすれば、
「結合して上詰め」と同様の見た目で、かつ、孫引き・ソート・フィルタも可能なので何かと潰しが効きます。
（後になって「法人の区切は二重線に」とか「縦位置は中央揃えに」とか気軽に言われて泣くこともありますが…）

以上、ご参考まで。

お礼

お礼の連絡、大変遅くなってしまい申し訳ありませんでした。

今やっている作業を申し上げますと、
法人名と代表者名（個人の場合は営業者名と店舗名）等を入力したシート、その会社が所持している品物と特徴を入力したシート、品物の型番と製造元を入力したシート、更に帳簿のテンプレートをそれぞれ作成し、
法人（又は店舗）毎に、Vlookupを使って帳簿シートに入力して印刷する、と言うことをしています。

> …の問題だろうと推察しますが、…
上記作業で、品物は随時増えていき、それを入手時期に応じて入力しますので、同一法人（又は営業者）の行がどんどん増えていくのですが、
帳簿シートの品名欄は１つですので、セルを纏める作業をしています。

前回ご回答の関数、私の思っていたことがほぼ（１００パーセント近く）できて非常に助かり、現在は大変重宝しています。
前回の解答で追加要望を書き込みましたが、しかし関数をご教示頂く前に比べたら些細なことと思うようになりました。
今は現状で続けたいと考えています。

本当にありがとうございました。
また、何かの折に質問させて頂くと思いますが、その時はよろしくお願いします。

回答No.2

mu2011

以下の様な抽出表となりますがが次の方法は如何でしょうか。
（品物が重複しても抽出します事ご承知下さい。）
　　　　Ａ　　　Ｂ　　Ｃ　　　Ｄ
１　日本太郎　ばら　　さば　　イス
２　山田花子　キリン　米　　　インク

（１）元表をシート１として、抽出表をシート２のＢ１セルに次の数式を設定し、縦横に必要分コピー
　　元表範囲をA1:B200としていますので調整して下さい。
　　=IF(COLUMN(A1)-1<COUNTIF(Sheet1!$A$1:$A$200,$A1),INDEX(Sheet1!$B$1:$B$200,SMALL(IF(Sheet1!$A$1:$A$200=$A1,ROW(Sheet1!$A$1:$A$200),9999),COLUMN(A1))),"")
　（２）抽出表のＡ列は、元表のＡ列を選択→データ→フィルタオプションの設定で重複レコードを無視をチェック→ＯＫでコピー＆ペーストして下さい。

お礼

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

早速試してみましたが「N/A」が返されて上手く出来ませんでした。
ただ、#1の方にも返信したように、品名は1つのセルに纏めたいと考えています。
教えて頂いた関数についてはもう少し試行錯誤してみたいと思いますが、別案も待ってみたいと思います。

ありがとうございました。

回答No.1

ka_na_de

関数ではないですが、これじゃだめ？

（１）「データ」「並べ替え」でA列を基準に並べ替える。

（２）「データ」「フィルタ」「フィルターオプションの設定」で
　　　重複するレコードは無視するにチェックを入れて「OK」

（３）同じ名前をセル結合

お礼

早速のご回答、ありがとうございます。
ただ、品名は出来れば一つのセルに纏めたいと思っています。
となりますと、（出来るかどうか判りませんが）関数かな？と考えますので、
別案を待ってみたいと思います。

ありがとうございました。