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

Question

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

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にしたいです。

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

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

keithin · Accepted Answer

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

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

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

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

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

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

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

tom04 · Answer

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

keithin · Answer

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

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

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

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

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

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

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

tom04 · Answer

こんにちは！
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

MarcoRossiItaly · Answer

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

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

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

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

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

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

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

keithin · Answer

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

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

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

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

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

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

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

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

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

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

No.3です。

再掲：

こんにちは！

ピボットテーブルと数式による処理の合わせ技というのもお手軽です。

こちらの相談室でもちょっと検索してみると、多数の類似ご相談がヒットします。

似たような質問が見つかりました

関連するカテゴリからQ&Aを探す

デイリーランキングこのカテゴリの人気デイリーQ&Aランキング

マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング