- ベストアンサー
Excelデータの仕分け方法 | 商品データの整理方法を教えてください
- Excelで商品データをメーカー毎に整理する方法について教えてください。IFとCOUNTIFを使って試みましたがうまくいきませんでした。
- データの種類ごとにメーカーを絞り、商品名を一覧する方法が知りたいです。商品データは数千個以上あるため、関数で処理したいです。
- 商品データをアウターやインターなどの種類ごとに整理し、メーカーと商品名を一覧したいです。どのような関数を使用すれば良いでしょうか？
- mihotan117
- お礼率100% (6/6)
- オフィス系ソフト
- 回答数6
- ありがとう数6
- みんなの回答 （6）
- 専門家の回答
質問者が選んだベストアンサー
その他の回答 (5)
- tom04
- ベストアンサー率49% (2537/5117)
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様のご回答にもベストアンサーを付けたかったのですが、 ２者をベストアンサーに選ぶ方法が分かりませんでした。 申し訳なく思います。
- keithin
- ベストアンサー率66% (5278/7941)
再掲： ＞シート２のＣ２に ＞=IFERROR(VLOOKUP($A2&"_"&$B2&"_"&COLUMN(A2),Sheet1!$A:$E,5,FALSE),"") ＞と記入し、右に下にコピー貼り付ける。 「右にコピー」し、「下にコピーする」と回答しています。 回答した通りに行ってください。 ＞C列関数式は以下のとおりです。 どうして２行目の数式の次が４行目、８行目になるのですか？ 下にコピーすれば、２行目の１つ下には３行目の数式になるはずですが。 それとも。 再掲： ＞５．シート２のＡ：Ｂ列でデータタブの「重複の削除」を使い、一意のリストを作成する この手順をスルーして行っていないのですか。 なお。 念のため言わずもがなですが、「１つのＣ列のセルの中に」カンマでつないで商品Ａ，Ｂ，Ｃが羅列されるみたいな計算の仕方は、無駄すぎるのでそういう事はしません。
お礼
大変失礼しました。 ちゃんとできました。 ご指摘どおり、右へ(列方向)のコピーをしていませんでした。 Ｃ列に結果が出ると考えていましたので。。。申し訳ありません。 ＞一意のリストを作成する 上記は、理解していたのですが、 行削除後でも数式の値が反映されていたので問題ないと判断しました。 実際、それでもできました。 本当にありがとうございます！ 助かりました。 欲を言えば、 Ｃのセル内に商品名を表示したいです。 商品名が10点程なら、ご教授いただいた方法が見やすいのですが、 実際には、100点近いものもあります。 その際、右へスクロールして確認するより、 一つのセルをクリックして、数式バーを展開した方が見やすいと考えてのことです。 ですが、自身で結合式を入れれば良いだけのことですので こちらで十分活用できます。 ありがとうございました^^
- tom04
- ベストアンサー率49% (2537/5117)
こんにちは！ 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点近いものもあります。 その際、右へスクロールして確認するより、 一つのセルをクリックして、数式バーを展開した方が見やすいと考えてのことです。 もし、ご迷惑でなければ、この件に関してもご考慮いただけないでしょうか？ ご無理なら結構です。 先のご回答でも十分助かっています。
- MarcoRossiItaly
- ベストアンサー率40% (454/1128)
ピボットテーブルと数式による処理の合わせ技というのもお手軽です。ご相談のデータに対して、試しにちょっと、ピボットテーブルをいじくってみてください。種類ごと、メーカーごとの一覧がすぐに入手できると思います。あとはそうして抽出された商品名を、セル内で一つにつなげればいいですね。 =c1&"、"&c2&"、"&c3 のような計算をすると、C1・C2・C3セルの値を一つにつなげることができますよ。 ＞データ量が多いので、マクロではなく関数で処理した方が良いと思うのですが？ ？？？ 通常、「データ量が多いからマクロ」とおっしゃる方はよくいらっしゃるんですが、逆のパターンは珍しいですね。 手作業でも簡単にできてしまうようなような処理なら、マクロで行ってもそれほど価値はないのかな、と。まあ易しいコードでも、マクロを使わないことにはできないことというのもありますけれども。確かに、データ量が多ければ関数よりマクロのほうが適切と言えるわけでもありません。関数など数式による処理でも、パッとできてしまうことも多くあります。そのような場合、マクロコードを書いていたら、かえって時間かかって仕方ないということにも。何でも関数、何でもマクロではなく、Excelには様々な機能があるので、それらをあれこれ活用すると、結構いろいろできます。できるだけ簡単な処理を目指しましょう。 マクロが向いているのは、数式を書こうとすると複雑になってしまうけれど、内容としてはおんなじ演算をひたすらひたすら繰り返すようなとき。また、将来、何度も同じ作業を繰り返すことが予定されているような事務とかは、マクロを使えば自動化されるので、効率化につながります。たとえ数分で終わるような作業でも、それを毎日行わないといけないとしたら、ボタン1つクリックすれば数秒で終わり、とかのほうがラクですね。「繰り返し」がキーワードです。
お礼
御教授ありがとうございました。 私の勉強不足で、皆様にはお手間取らせて申しわけございません。 ピボットテーブルですか。 やってみます！
- keithin
- ベストアンサー率66% (5278/7941)
こちらの相談室でもちょっと検索してみると、多数の類似ご相談がヒットします。そういったご相談ではしばしばよく、イミフメイだけどとにかくコピーすれば動く関数式が紹介されています。 が、 ＞商品データは数千個以上あります というお話では、そういった計算式は実用的に役に立ちません。 現実的な方策としては １）マクロを使う ２）丁寧に作業列を追加して、簡単な関数で計算する のどちらかになります。 関数の方がいいのでしたら、次のようにします。 １．シート１のＣ列に種類、Ｄ列にメーカ、Ｅ列に商品名とする ２．シート１のＢ列に Ｂ２： =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 とは、なりませんでした。 (何度か試したので、手順に間違いはないと思うのですが・・・) 引き続きご指導いただければ幸いです。
お礼
100点満点のご回答をいただきありがとうございます。 今までの苦労が嘘の様に、あっと言う間に仕分け作業が完了しました。 先にご回答いただきましたtom04様のマクロでも 望む結果が得られましたが、処理的にはkeithin様のマクロ式の方が早かったです。 20000行を超えるデータが3分程で処理できました。 私のために何度もお手数おかけし、大切なお時間を費やしていただいたことに 大感謝と同時に申し訳なく思っています。 本当にありがとうございました。