条件によるデータの仕分け＜Excel＞

質問

はじめまして、
今回はお世話になります。

Excel（2007/2010）関数についての質問となります。

商品データをメーカー毎に整理しようと
IFとCOUNTIFを組合せてみましたが、勉強不足でうまくできませんでした。

行/列→
↓

《種類》　　《メーカー》　　《商品名》
アウター　　メーカーname1　　商品名A
アウター　　メーカーname1　　商品名B
アウター　　メーカーname2　　商品名C
アウター　　メーカーname1　　商品名D
アウター　　メーカーname2　　商品名E
アウター　　メーカーname1　　商品名F
アウター　　メーカーname3　　商品名G
アウター　　メーカーname3　　商品名H
アウター　　メーカーname3　　商品名I
・
・
・
インター　　メーカーname1　　商品名J
インター　　メーカーname1　　商品名K
インター　　メーカーname2　　商品名L
・
・

上記のデータから以下の結果を求めたいのですが、
良い方法がありましたらご教授ください。

アウター　　メーカーname1　　商品名A,商品名B,商品名D,商品名F
アウター　　メーカーname2　　商品名C,商品名E
アウター　　メーカーname3　　商品名G,商品名H,商品名I
インター　　メーカーname1　　商品名J,商品名K
インター　　メーカーname2　　商品名L
・
・
・

商品の種類(例：アウター)をメーカー毎にひとまとめにしたいのです。

補足といたしまして、
商品の種類は数十種類、メーカーは100程度あります。
また、異なる商品種類の中に、同一のメーカーもあります。
例：アウター(メーカーname1)、インナー(メーカーname1)

商品データは数千個以上あります。
可能なら、データと結果は別のSheetにしたいです。

データ量が多いので、マクロではなく関数で処理した方が良いと思うのですが？
お分かりの方がいらっしゃいましたら助けてください。

宜しくお願いいたします。

回答No.6

keithin · ベストアンサー

＞Ｃのセル内に商品名を表示したいです。

どうしてもそうしたいなら、まぁマクロを使った方が手早いは手早いです。
それでも数式を併用したほうが、遥かに簡単ですが。

状況：
シート１のＡＢＣ列からシート２のＡＢＣ列に抽出する
１行目はタイトル行
２行目から実データ。

手順：
ＡＬＴ＋Ｆ１１を押す
現れた画面で挿入メニューから標準モジュールを挿入する
現れたシートに下記をコピー貼り付ける

sub macro1()
　dim LastRow as long
　dim ResRow as long
　worksheets("Sheet1").select
　worksheets("Sheet2").cells.clearcontents
　application.screenupdating = false

’抽出と調査
　range("A:B").advancedfilter _
　　action:=xlfiltercopy, _
　　copytorange:=worksheets("Sheet2").range("A1"), _
　　unique:=true

　lastrow = range("B65536").end(xlup).row
　resrow = worksheets("Sheet2").range("B65536").end(xlup).row

’数式の投入
　worksheets("Sheet2").range("C2:C" & resrow).formula = "=ROW(C1)"
　range("D2:D" & lastrow).formula = "=SUMIFS(Sheet2!C:C,Sheet2!A:A,A2,Sheet2!B:B,B2)"
　range("E2:E" & lastrow).formula = "="",""&C2&IFERROR(VLOOKUP(D2,D3:E$" & (lastrow+1) & ",2,FALSE),"""")"
　with worksheets("Sheet2").range("D2:D" & resrow)
　　.formula = "=MID(VLOOKUP(C2,Sheet1!D:E,2,FALSE),2,99999)"
　　.value = .value
　end with

’片づけ
　range("D:E").delete shift:=xlshifttoleft
　worksheets("Sheet2").range("C:C").delete shift:=xlshifttoleft
　application.screenupdating = true

end sub

ファイルメニューから終了してエクセルに戻る
ＡＬＴ＋Ｆ８でマクロを実行する。

＃ループとか一つも回してないでも、十分作成できます。
＃このマクロで記入させた数式を手で記入しても、もちろん出来ます。

お礼

100点満点のご回答をいただきありがとうございます。
今までの苦労が嘘の様に、あっと言う間に仕分け作業が完了しました。

先にご回答いただきましたtom04様のマクロでも
望む結果が得られましたが、処理的にはkeithin様のマクロ式の方が早かったです。
20000行を超えるデータが3分程で処理できました。

私のために何度もお手数おかけし、大切なお時間を費やしていただいたことに
大感謝と同時に申し訳なく思っています。

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

回答No.5

tom04

No.3です。
＞欲を言えば、
＞Ｃのセル内に商品名を表示したいです。
とありますので・・・

商品名はC列のみに表示したいというコトですね？

今一度コードを載せてみますので、前回同様の操作でマクロを実行してみてください。

Sub test2() 'この行から
Dim i, k As Long
Dim str As String
Dim ws As Worksheet
Set ws = Worksheets("Sheet2")
Application.ScreenUpdating = False
k = ws.Cells(Rows.Count, 1).End(xlUp).Row
If k > 1 Then
ws.Rows(2 & ":" & k).ClearContents
End If
Columns(1).Insert
For i = 2 To Cells(Rows.Count, 2).End(xlUp).Row
Cells(i, 1) = Cells(i, 2) & Cells(i, 3)
If WorksheetFunction.CountIf(Columns(1), Cells(i, 1)) = 1 Then
With ws.Cells(Rows.Count, 1).End(xlUp).Offset(1)
.Value = Cells(i, 2)
.Offset(, 1) = Cells(i, 3)
End With
End If
Next i
For k = 2 To ws.Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 To Cells(Rows.Count, 1).End(xlUp).Row
If Cells(i, 1) = ws.Cells(k, 1) & ws.Cells(k, 2) Then
str = str & Cells(i, 4) & ","
End If
Next i
ws.Cells(k, 3) = Left(str, Len(str) - 1)
str = ""
Next k
ws.Columns.AutoFit
Columns(1).Delete
Application.ScreenUpdating = True
End Sub 'この行まで

こんな感じではどうでしょうか？m(_ _)m

お礼

素晴らしいマクロを作っていただきありがとうございます。
20000行を超えるデータを問題なく処理できました。
実に爽快でした^^

素人の私でも、丁寧なご回答のお蔭で迷うことなく望む結果が得られました。
tom04様にご回答いただけ事は大変幸運だったと思います。

貴重な時間を割いてお付き合いいただきましたことに大変感謝しています。
ありがとうございました!!

tom04様のご回答にもベストアンサーを付けたかったのですが、
２者をベストアンサーに選ぶ方法が分かりませんでした。
申し訳なく思います。

回答No.4

keithin

再掲：
＞シート２のＣ２に
＞=IFERROR(VLOOKUP($A2&"_"&$B2&"_"&COLUMN(A2),Sheet1!$A:$E,5,FALSE),"")
＞と記入し、右に下にコピー貼り付ける。

「右にコピー」し、「下にコピーする」と回答しています。
回答した通りに行ってください。

＞C列関数式は以下のとおりです。

どうして２行目の数式の次が４行目、８行目になるのですか？
下にコピーすれば、２行目の１つ下には３行目の数式になるはずですが。

それとも。
再掲：
＞５．シート２のＡ：Ｂ列でデータタブの「重複の削除」を使い、一意のリストを作成する

この手順をスルーして行っていないのですか。

なお。
念のため言わずもがなですが、「１つのＣ列のセルの中に」カンマでつないで商品Ａ，Ｂ，Ｃが羅列されるみたいな計算の仕方は、無駄すぎるのでそういう事はしません。

お礼

大変失礼しました。
ちゃんとできました。

ご指摘どおり、右へ(列方向)のコピーをしていませんでした。
Ｃ列に結果が出ると考えていましたので。。。申し訳ありません。

＞一意のリストを作成する
上記は、理解していたのですが、
行削除後でも数式の値が反映されていたので問題ないと判断しました。
実際、それでもできました。

本当にありがとうございます！
助かりました。

欲を言えば、
Ｃのセル内に商品名を表示したいです。

商品名が10点程なら、ご教授いただいた方法が見やすいのですが、
実際には、100点近いものもあります。
その際、右へスクロールして確認するより、
一つのセルをクリックして、数式バーを展開した方が見やすいと考えてのことです。

ですが、自身で結合式を入れれば良いだけのことですので
こちらで十分活用できます。

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

回答No.3

tom04

こんにちは！
VBAになってしまいますが・・・
一例です。
Sheet1のデータをSheet2にまとめるようにしてみました。
Sheet2の1行目は項目行でA1に「種類」・B1に「メーカー」と入力済みだとします。

画面左下のSheet1のSheet見出し上で右クリック　→　コードの表示　→　VBE画面に
↓のコードをコピー＆ペーストしてマクロを実行してみてください。
（Alt＋F8キー　→　マクロ　→　マクロ実行です）

Sub test() 'この行から
Dim i, k As Long
Dim ws As Worksheet
Set ws = Worksheets("Sheet2")
Application.ScreenUpdating = False
k = ws.Cells(Rows.Count, 1).End(xlUp).Row
If k > 1 Then
ws.Rows(2 & ":" & k).ClearContents
End If
Columns(1).Insert
For i = 2 To Cells(Rows.Count, 2).End(xlUp).Row
Cells(i, 1) = Cells(i, 2) & Cells(i, 3)
If WorksheetFunction.CountIf(Columns(1), Cells(i, 1)) = 1 Then
With ws.Cells(Rows.Count, 1).End(xlUp).Offset(1)
.Value = Cells(i, 2)
.Offset(, 1) = Cells(i, 3)
End With
End If
Next i
For i = 2 To Cells(Rows.Count, 1).End(xlUp).Row
For k = 2 To ws.Cells(Rows.Count, 1).End(xlUp).Row
If Cells(i, 2) = ws.Cells(k, 1) And Cells(i, 3) = ws.Cells(k, 2) Then
ws.Cells(k, Columns.Count).End(xlToLeft).Offset(, 1) = Cells(i, 4)
End If
Next k
Next i
ws.Columns.AutoFit
Columns(1).Delete
Application.ScreenUpdating = True
End Sub 'この行まで

※　関数でないので、すぐにSheet2に反映されません。
Sheet1のデータ変更があるたびにマクロを実行する必要があります。

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

お礼

ありがとうございます。
見事にできました^^

まだ、実用のデータでは試したいませんが、
「凄く楽だなー」と感じました。

得られた結果は、
No.1回答者のkeithin様と同じものですので、

手間と処理時間から、どちらが実用的か比べてみたいと思います。

下記は、No.4のお礼書いたものと重複しますが、

欲を言えば、
Ｃのセル内に商品名を表示したいです。

商品名が10点程なら、ご教授いただいた方法が見やすいのですが、
実際には、100点近いものもあります。
その際、右へスクロールして確認するより、
一つのセルをクリックして、数式バーを展開した方が見やすいと考えてのことです。

もし、ご迷惑でなければ、この件に関してもご考慮いただけないでしょうか？
ご無理なら結構です。

先のご回答でも十分助かっています。

回答No.2

MarcoRossiItaly

ピボットテーブルと数式による処理の合わせ技というのもお手軽です。ご相談のデータに対して、試しにちょっと、ピボットテーブルをいじくってみてください。種類ごと、メーカーごとの一覧がすぐに入手できると思います。あとはそうして抽出された商品名を、セル内で一つにつなげればいいですね。

=c1&"、"&c2&"、"&c3

のような計算をすると、C1・C2・C3セルの値を一つにつなげることができますよ。

＞データ量が多いので、マクロではなく関数で処理した方が良いと思うのですが？

？？？
通常、「データ量が多いからマクロ」とおっしゃる方はよくいらっしゃるんですが、逆のパターンは珍しいですね。

手作業でも簡単にできてしまうようなような処理なら、マクロで行ってもそれほど価値はないのかな、と。まあ易しいコードでも、マクロを使わないことにはできないことというのもありますけれども。確かに、データ量が多ければ関数よりマクロのほうが適切と言えるわけでもありません。関数など数式による処理でも、パッとできてしまうことも多くあります。そのような場合、マクロコードを書いていたら、かえって時間かかって仕方ないということにも。何でも関数、何でもマクロではなく、Excelには様々な機能があるので、それらをあれこれ活用すると、結構いろいろできます。できるだけ簡単な処理を目指しましょう。

マクロが向いているのは、数式を書こうとすると複雑になってしまうけれど、内容としてはおんなじ演算をひたすらひたすら繰り返すようなとき。また、将来、何度も同じ作業を繰り返すことが予定されているような事務とかは、マクロを使えば自動化されるので、効率化につながります。たとえ数分で終わるような作業でも、それを毎日行わないといけないとしたら、ボタン1つクリックすれば数秒で終わり、とかのほうがラクですね。「繰り返し」がキーワードです。

お礼

御教授ありがとうございました。
私の勉強不足で、皆様にはお手間取らせて申しわけございません。

ピボットテーブルですか。
やってみます！

回答No.1

keithin

こちらの相談室でもちょっと検索してみると、多数の類似ご相談がヒットします。そういったご相談ではしばしばよく、イミフメイだけどとにかくコピーすれば動く関数式が紹介されています。
が、
＞商品データは数千個以上あります

というお話では、そういった計算式は実用的に役に立ちません。

現実的な方策としては
１）マクロを使う
２）丁寧に作業列を追加して、簡単な関数で計算する
のどちらかになります。

関数の方がいいのでしたら、次のようにします。

１．シート１のＣ列に種類、Ｄ列にメーカ、Ｅ列に商品名とする
２．シート１のＢ列に
　Ｂ２：
　=C2&"_"&D2
　として以下コピーする
３．シート１のＡ列に
　=B2&"_"&COUNTIF($B$2:B2,B2)
　として以下コピーする

４．シート１のＣ：Ｄ列を列コピーし、シート２のＡ列に貼り付ける
５．シート２のＡ：Ｂ列でデータタブの「重複の削除」を使い、一意のリストを作成する

６．シート２のＣ２に
　=IFERROR(VLOOKUP($A2&"_"&$B2&"_"&COLUMN(A2),Sheet1!$A:$E,5,FALSE),"")
　と記入し、右に下にコピー貼り付ける。

＃マクロを使いたいのでしたら、上述の手順を「新しいマクロの記録」でマクロに録れば、ほぼそのまま利用できるマクロを採取できます。必要に応じて、作業列として追加したシート１のＡ：Ｂ列を取り除くとか、計算式の結果を値化するなどの作業を追加します。

お礼

早速の御教授ありがとうございました。

質問時の例の通り、アウター部分だけに試したところ

Sheet2は、

A列　　　　　 B列　　　　　　　　　C列
アウター　　メーカーname1　　商品名A
アウター　　メーカーname2　　商品名C
アウター　　メーカーname3　　商品名G

となりました。

C列関数式は以下のとおりです。

=IFERROR(VLOOKUP($A2&"_"&$B2&"_"&COLUMN(A2),Sheet1!$A:$E,5,FALSE),"")
=IFERROR(VLOOKUP($A4&"_"&$B4&"_"&COLUMN(A4),Sheet1!$A:$E,5,FALSE),"")
=IFERROR(VLOOKUP($A8&"_"&$B8&"_"&COLUMN(A8),Sheet1!$A:$E,5,FALSE),"")

恐縮ですが、当方の望む結果

アウター　　メーカーname1　　商品名A,商品名B,商品名D,商品名F
アウター　　メーカーname2　　商品名C,商品名E
アウター　　メーカーname3　　商品名G,商品名H,商品名I

とは、なりませんでした。
(何度か試したので、手順に間違いはないと思うのですが・・・)

引き続きご指導いただければ幸いです。