Excel:条件付き文字列の結合方法

質問

Excel本当の初心者です。ヘルプなど使って、自分なりにがんばって
考えたのですが、やりたいことがどうしてもできず、お力を貸して
ください！
やりたいのは、下記の通りです。

Sheet1のような表があります。カテゴリー毎にID が
付いていて、表は20,000行位あります。
同じID（カテゴリー）のものは、C列の商品名を”、”で
結合して、1行にまとめたいのです。
Sheet2のような結果を得たいのです。

Sheet1
Ａ列　　　　　Ｂ列　　　　　Ｃ列
ID　　　　　　カテゴリー　　商品名

12345　　　　くだもの　　　りんご
12345　　　　くだもの　　　みかん
12345　　　　くだもの　　　イチゴ
67891　　　　野菜　　　　　いんげん
23456　　　　お菓子　　　　チョコ
23456　　　　お菓子　　　　お煎餅

Sheet2
Ａ列　　　　　Ｂ列　　　　　Ｃ列
ID　　　　　　カテゴリー　　商品名

12345　　　　くだもの　　　りんご、みかん、イチゴ
67891　　　　野菜　　　　　いんげん
23456　　　　お菓子　　　　チョコ、お煎餅

Sheet1のＡ列とB列を「フィルタの重複レコードを無視する」を
したものをSheet2のＡ列とＢ列に貼り付けて、ISNUMBERとFIND
を使ってと思ったのですが、Sheet2のＣ列で商品名の結合を"、 "
で結合しようと思ったのですが、重複IDがない物もあれば、
10個も20個もあるものもあるので、CONCATENATEではできなくて...

どんな関数を使えば良いのでしょうか？
それと、事前にSheet1にフィルタをかけてSheet2に貼り付けたり
ぜずに、Sheet2のＡ列とＢ列とも関数でやる事はできるのでしょうか？

あともうひとつ、教えてただきたい事があります。
いつも一番上の行に式を入れて、下にドラッグすることで
下の行にコピーしているのですが、20,000行もあるので
この方法だと大変なので、行数をしていして、コピーする
方法とかあれば教えてください！

一度にいくつもすみませんが、教えてくださると
嬉しいです。

回答No.8

noname#52504 · ベストアンサー

#7です。すいません、忘れてました。

>事前にSheet1にフィルタをかけてSheet2に貼り付けたりぜずに、
>Sheet2のＡ列とＢ列とも関数でやる事はできるのでしょうか？

●関数によるフィルタ
#7の作業列ア（D列)を引き続いて使います。

５．作業列エ　[ID別の連番を使って、ID毎に連番を振る]
　Sheet1!G2：=COUNTIF($D$2:D2,1)

６．ID　[自セルの行番号を使って、IDを引く]
　Sheet2!A2：=OFFSET(Sheet1!$A$1,MATCH(ROW()-1,Sheet1!$G$2:$G$29999,0),)
　※自セルが４行目であれば、３番目のIDを探す。

７．カテゴリー　[IDを使って、カテゴリーを引く]
　Sheet2!B2：=VLOOKUP(A2,Sheet1!$A$2:$B$29999,2,0)

お礼

できました、嬉しいです！
ありがとうございました。
自分のなじみのある、関数という形でできて
感激です。でも、マクロのが向いてるというのも
両方やってみて、わかった気がします。
マクロ勉強しようという気になりました。
でも、難しそうですね...

回答No.7

noname#52504

横から失礼します。

作業列を使ってもよければ、数式でもできなくはありませんが、
#4さんがおっしゃるように、20,000行ともなると相当重たい処理になります。
また、数式とはいえ、必ずしも判りやすいとは言えないかもしれません。

私自身マクロによる解決をお奨めしますが、
「内容がわからないものを使うのに抵抗がある」というのもごもっともだと思いますので、
参考までに、数式による解決法を例示します。

以下、２行目からデータが入っているとして、

１．作業列ア　[ID別に連番を振る]
　Sheet1!D2：=COUNTIF($A$2:A2,A2)
　として下方にフィル。　

２．作業列イ　[IDと作業列アの連番を連結した検索コードを生成する]
　Sheet1!E2：=A2&" "&D2
　として下方にフィル。

３．作業列ウ　[ID毎に、商品名を結合していく]
　Sheet1!F2：=IF(D2=1,"",OFFSET($F$2,MATCH(A2&" "&(D2-1),$E$2:E2,0)-1,)&"、")&C2
　※そのIDの最初の商品なら、その商品名だけを返す。
　　そのIDのN番目の商品であれば、検索コードからN-1番目を探して、その作業列ウの値に自分を付け加える。

４．結果列　[ID毎に検索して、作業列ウの最後の値を返す]
　Sheet2!C2：=VLOOKUP(A2&" "&COUNTIF(Sheet1!$A$2:$A$29999,A2),Sheet1!$E$2:$F$29999,2,0)
　※IDの数をカウントして、最後の検索コードを割り出す。
　　検索コードを検索して、作業列ウの値を返す。

回答No.6

hiyaku

#3のHIYAKUです。

セルの右あるいは左側どちらかに空白セルがなくデータが全て入っているのでしょうか？

通常は空白のセルが無ければＷクリックでコピーされますが・・・・

お礼

すみません、空白セルがありました。
その手前まではできていました...
よく確認せず、すみません。
これで、20,000行も怖くありません(笑)
どうもありがとうございました。

回答No.5

koko88okok

＃２です。補足に回答します。

> ヘルプや「表示→ツールバー→書式設定」だけ表示させてみたのですが、「名前ボックス」が見つからなくて...

「名前ボックス」とは、新しくExcelを起動したとき、書式設定ツールバーの左端に「A1」と表示されているボックスのことです。

「Excelについて」
http://www.wanichan.com/pc/excel/excel01.htm

お礼

わー、できました。
これでしっかり「名前ボックス」覚えられました。
ありがとうございました！

回答No.4

n-jun

ANo.1です。

>私がやりたいことは、関数ではできないのでしょうか？
出来るのかも知れませんが、複雑な関数は苦手でして。
更に20，000行というデータ数からすると、複雑な関数を
入れていくと動作が重くなってしまうのでは？と
感じたので、この方法を提示してみました。

お礼

重くなってしまったりするんですね...
やりたいことがちゃんとできて嬉しかったです。
教えていただいたプログラムを調べて
この行はこんなことをしてるんだーとか
ちょっと勉強してみようと思います♪
ありがとうございました！

回答No.3

hiyaku

＞あともうひとつ、教えてただきたい事があります。
＞いつも一番上の行に式を入れて、下にドラッグすることで
＞下の行にコピーしているのですが、20,000行もあるので
＞この方法だと大変なので、行数をしていして、コピーする
＞方法とかあれば教えてください！

行数指定ではありませんが、沢山の行数がある場合下記の方法があります。
ただ２つの条件がありますが・・・

Ａ．セルの右、あるいは左側どちらかに空白セルがなくデータが全て入っている事
Ｂ．コピーする行は、一番下の行までする事

１．コピー元のセルを選択しクリックする
２．マウスをそのセルの右下角にあわせると、マウスが白十字から黒十字に変わる
３．そのままの状態で、Ｗクリックする　　　以上

★もし元データが「数値」でしたら、1つずつ増えますので
もう一行だけその下にコピーし、セルを２ケ（２行）選択し上記の方法を実行する。
★何万行あっても、瞬時にコピーされます。
★右あるいは左に空白行があれば、そこまでの部分だけにコピーされます。
★逆に言えば、コピーしたくない行に先に１行だけ空白行を入れてから
コピーすると、その行でコピーはストップしますので、後は応用次第です。。。。

お試しあれ～

補足

セルを２行選択して、黒十字の状態でＷクリックしたのですが、
何事も起こりません。どうしてか分かりますか???
すみません、丁寧に書いて下さったのに、
理解できていないようで。。。

回答No.2

koko88okok

もうひとつの質問だけですが・・・

1) 元のセルを含めて、コピーしたい範囲を選択します。
2) 「編集」メニュー→「フィル」→「連続データの作成」→「オートフィル」のラジオボタンをオンにして「OK」を押します。
3) オートフィルコピーされます。

多数行の選択方法（２例）
1) 最初のセルをクリックして、スクロール後「Shift」キーを押しながら、最後のセルをクリックします。
2) 最初のセルをクリックして、書式設定ツールバーの「名前ボックス」に最後のセル番号を入力して、「Shift」キーを押しながら「Enter」キーを押します。

補足

回答くださって、ありがとうございます。
書式設定ツールバーの「名前ボックス」というのが分からないのですが...
ヘルプや「表示→ツールバー→書式設定」だけ表示させて
みたのですが、「名前ボックス」が見つからなくて...
すみません、ド素人なので...
教えてくださいませんか？

回答No.1

n-jun

VBE(Visual Basic Editor/VBエディタ)の画面について
http://www.eurus.dti.ne.jp/~yoneyama/Excel/vba/vba_vbe.html
・[Alt]+[F11]または[ツール]→[マクロ]→[Visual Basic Editor]を選択します。
⇒ＶＢＥ画面を呼び出します。

左側の
VBAProject～を右クリック－挿入－標準モジュールで
上から３枚目の画像にある白い画面が出てきたら
下の Sub test()　～ End Sub までをコピー・貼り付ける。

ＶＢＥ画面を閉じてから、Alt＋F8で「マクロ」と言うのが
出てきたら、testを選択してＯＫを押す。

必ずファイルをコピーしてからテストして下さい。
（物は試しに・・・）

Sub test()

Dim Dic As Object, key As Variant
Dim WS1 As Worksheet, WS2 As Worksheet
Dim r As Range, rr As Range
Dim st As String
Dim v As Variant

Set Dic = CreateObject("Scripting.Dictionary")
Set WS1 = Worksheets("Sheet1")
Set WS2 = Worksheets("Sheet2")
Set rr = WS2.Range("A2")

With WS1
For Each r In .Range(.[A2], .Cells(Rows.Count, "A").End(xlUp))
st = r.Value & "_" & r.Offset(, 1).Value
Dic(st) = Dic(st) & r.Offset(, 2).Value & "、"
Next
End With

With WS2
WS1.Range("A1:C1").Copy WS2.Range("A1")

For Each key In Dic.keys
v = Split(key, "_")
rr.Resize(, 2) = v
rr.Offset(, 2).Value = Left(Dic(key), Len(Dic(key)) - 1)
Set rr = rr.Offset(1)
Next
End With
End Sub

補足

「実行」ボタン押しただけで、sheet2が
できちゃいました。嬉しいです！
ありがとうございました。
図々しくさらに、質問良いですか？
私がやりたいことは、関数ではできないのでしょうか？
やっぱりマクロでないと...　
実は、Excel自体もろくにできず、マクロはやったことが
ないので、教えていただいてコピーしたプログラムは
内容がまったく分からずなので、それを使うのがちょっと
抵抗がありまして。
もし可能なら、自分で理解できる方法（関数）などでやりたいなぁと
思ったりしてるのですが。
やっぱり、マクロ勉強しないといけないですかね。