- ベストアンサー
エクセルで商品を振り分ける方法とは？
- エクセルを使って商品を振り分ける方法について教えてください。
- 商品名が単語になっていない場合に、キーワードを抽出して振り分ける方法を知りたいです。
- VBAを使わずに、変更が容易な関数で商品を振り分けたいです。
- queschoooon
- お礼率97% (46/47)
- その他MS Office製品
- 回答数10
- ありがとう数10
- みんなの回答 （10）
- 専門家の回答
質問者が選んだベストアンサー
>先に重複しないものについて取り急ぎご連絡させていただきたいと思います。確実なのは商品名とコードになります。 >この二つは重複しないものとなります。 Sheet1において、商品名か商品コードどちらかが同じものが2つ以上入力される事は無いという事ですので、商品コードを利用する方法に関して回答致します。 今仮に、Sheet1や各商品別のシートにおいて、A列には商品名を、B列には商品コードを、C列には金額を、それぞれ入力するものとします。 又、Sheet1の１行目には A1セル B1セル C1セル 商品名 商品コード 金額 という具合に各項目名が入力されていて、実際のデータは2行目以下に入力されているものとし、 同様に各商品別のシートの2行目には A2セル B2セル C2セル 商品名 商品コード 金額 という具合に各項目名が入力されていて、実際のデータは3行目以下に表示されるものとします。 まず、使用するExcelファイルを一旦保存して下さい。（←もし、使用するExcelファイルが、既にパソコン内の何処かのフォルダーに保存済みである場合には、この部分の操作は不要です） 次に、各商品別のシートの雛型となる「雛型」というシート名のシートを作成して下さい。 次に、雛型シートのA1セルに次の関数を入力して下さい。 =REPLACE(CELL("filename",A1),1,FIND("]",CELL("filename",A1)),)&"シート" 次に、雛型シートのA2セルに次の関数を入力して下さい。 =Sheet1!$A$1 次に、雛型シートのB2セルに次の関数を入力して下さい。 =Sheet1!$B$1 次に、雛型シートのC2セルに次の関数を入力して下さい。 =Sheet1!$C$1 次に、雛型シートのB3セルに次の関数を入力して下さい。 =IF(OR(ROWS($1:1)>COUNTIF(Sheet1!$A:$A,"*"&REPLACE(CELL("filename",B3),1,FIND("]",CELL("filename",B3)),)&"*"),COUNTIF(B$2:B2,"↑【重複あり】")),"",IF(COUNTIF(Sheet1!$B:$B,B2)>1,"↑【重複あり】",INDEX(Sheet1!$B:$B,MATCH("*"&REPLACE(CELL("filename",B3),1,FIND("]",CELL("filename",B3)),)&"*",INDEX(Sheet1!$A:$A,MATCH(B2,Sheet1!$B:$B,0)+1):INDEX(Sheet1!$A:$A,ROWS($B:$B)),0)+MATCH(B2,Sheet1!$B:$B,0)))) 次に、雛型シートのA3セルに次の関数を入力して下さい。)) =IF(AND($B3<>"",COUNTIF(Sheet1!$B:$B,$B3)),INDEX(Sheet1!$A:$C,MATCH($B3,Sheet1!$B:$B,0),MATCH(A$2,Sheet1!$A$1:$C$1,0)),"") 次に、雛型シートのA3セルをコピーして、雛型シートのC3セルに貼り付けて下さい。 次に、雛型シートのA3～C3の範囲をコピーして、同じ列の4行目以下に貼り付けて下さい。 次に、雛型シートのコピーシートを、商品の種類の数だけ複製して下さい。 そうして作った雛型シートの各コピーシートのシート名を、「桃」や「みかん」、「さくらんぼ」といった、各商品毎のキーワードと同じ名称（鉤括弧は無用）に変更して下さい。 以上で準備は完了で、後はSheet1にNo.、商品名、商品コード、金額といった各データを入力しますと、各キーワード毎に振り分けられたデータが、各商品別のシートに自動的に表示されます。 尚、万が一、Sheet1の商品コード欄に同じコードが２つ以上入力されていた場合には、 ↑【重複あり】 という表示が現れて、それより下の行においては振り分けた結果を表示できなくなりますので注意なさって下さい。 それから、各商品別のシートのA1セルには「『商品のキーワード』+『シート』」の形式を持つ文字列が表示されます。 例えば、みかんシートの場合には、 みかんシート と表示されます。 もし、「みかん」の前後に付く内容を変更して、例えば 商品名《みかん》リスト 等の様にする場合には、雛型シートのA1セルに関数を入力する際に、その関数を次の様なものとして下さい。 ="商品名《"&REPLACE(CELL("filename",A1),1,FIND("]",CELL("filename",A1)),)&"》リスト"
その他の回答 (9)
- kagakusuki
- ベストアンサー率51% (2610/5101)
回答:No.6です。 >文中記載のNo.を付けない方法をお願いできますでしょうか？ との事ですが、商品名か商品コードのどちらかが、Sheet1内に同じものが複数回重複して現れる事は無い事が保障されているのでしょうか？ もし、No.欄以外にSheet1内に同じものが複数回重複して現れる事は無い事が保障されている列がある場合には、その列を基準として利用する事で、回答:No.6と同様に作業列を必要としない方法とする事が出来ますので、どの列が重複無しの列なのかを御教え頂けましたら、その情報に合わせた関数のみによる方法を再度回答させて頂きます。 又、元データの中には重複無しの列が存在していない場合には、以下の様に作業列を使用する必要があります。 但し、以下の方法の場合には、商品を振り分ける際に使用するキーワードが、各商品別のシートのシート名によって自動的に決まる様になっていますから、商品の種類が新たに追加されたり、変更されたりした場合にも、雛型シートのコピーシートの名称をキーワードと同じものに変更するだけで良く、作業列中に「商品を振り分ける際に使用するキーワード」に関する情報を組み込んだり、入力したりする必要は御座いません。 今仮に、A列には商品名を、B列には商品コードを、C列には金額を、それぞれ入力するものとします。 又、Sheet1の１行目には A1セル B1セル C1セル 商品名 商品コード 金額 という具合に各項目名が入力されていて、実際のデータは2行目以下に入力されているものとし、 同様に各商品別のシートの2行目には A2セル B2セル C2セル 商品名 商品コード 金額 という具合に各項目名が入力されていて、実際のデータは3行目以下に表示されるものとします。 又、Sheet2のA列を作業列として使用するものとします。 まず、使用するExcelファイルを一旦保存して下さい。（←もし、使用するExcelファイルが、既にパソコン内の何処かのフォルダーに保存済みである場合には、この部分の操作は不要です） 次に、Sheet2のA1セルに次の関数を入力して下さい。 =IF(INDEX(Sheet1!$A:$A,ROW())="","",INDEX(Sheet1!$A:$A,ROW())&CHAR(1)&COUNTIF(Sheet1!$A$1:INDEX(Sheet1!$A:$A,ROW()),INDEX(Sheet1!$A:$A,ROW()))) 次に、Sheet2のA1セルをコピーして、Sheet2のA2以下に貼り付けて下さい。 次に、各商品別のシートの雛型となる「雛型」というシート名のシートを作成して下さい。 次に、雛型シートのA1セルに次の関数を入力して下さい。 =REPLACE(CELL("filename",A1),1,FIND("]",CELL("filename",A1)),)&"シート" 次に、雛型シートのA2セルに次の関数を入力して下さい。 =Sheet1!$A$1 次に、雛型シートのB2セルに次の関数を入力して下さい。 =Sheet1!$B$1 次に、雛型シートのC2セルに次の関数を入力して下さい。 =Sheet1!$C$1 次に、雛型シートのA3セルに次の関数を入力して下さい。)) =IF(ROWS($1:1)>COUNTIF(Sheet1!$A:$A,"*"&REPLACE(CELL("filename",A3),1,FIND("]",CELL("filename",A3)),)&"*"),"",INDEX(Sheet1!$A:$A,MATCH("*"&REPLACE(CELL("filename",A3),1,FIND("]",CELL("filename",A3)),)&"*",INDEX(Sheet2!$A:$A,MATCH(A2&CHAR(1)&COUNTIF(A$2:A2,A2),Sheet2!$A:$A,0)+1):INDEX(Sheet2!$A:$A,ROWS(A:A)),0)+MATCH(A2&CHAR(1)&COUNTIF(A$2:A2,A2),Sheet2!$A:$A,0))) 次に、雛型シートのB3セルに次の関数を入力して下さい。 =IF($A3="","",INDEX(Sheet1!$A:$C,MATCH($A3&CHAR(1)&COUNTIF($A$2:$A3,$A3),Sheet2!$A:$A,0),MATCH(B$2,Sheet1!$A$1:$C$1,0))) 次に、雛型シートのB3セルをコピーして、雛型シートのC3セルに貼り付けて下さい。 次に、雛型シートのA3～C3の範囲をコピーして、同じ列の4行目以下に貼り付けて下さい。 次に、雛型シートのコピーシートを、商品の種類の数だけ複製して下さい。 そうして作った雛型シートの各コピーシートのシート名を、「桃」や「みかん」、「さくらんぼ」といった、各商品毎のキーワードと同じ名称（鉤括弧は無用）に変更して下さい。 以上で準備は完了で、後はSheet1にNo.、商品名、商品コード、金額といった各データを入力しますと、各キーワード毎に振り分けられたデータが、各商品別のシートに自動的に表示されます。 それから、各商品別のシートのA1セルには「『商品のキーワード』+『シート』」の形式を持つ文字列が表示されます。 例えば、みかんシートの場合には、 みかんシート と表示されます。 もし、「みかん」の前後に付く内容を変更して、例えば 商品名《みかん》リスト 等の様にする場合には、雛型シートのA1セルに関数を入力する際に、その関数を次の様なものとして下さい。 ="商品名《"&REPLACE(CELL("filename",A1),1,FIND("]",CELL("filename",A1)),)&"》リスト"
お礼
お世話になります。先日はありがとうございました。 お陰様で、たくさんのご教示いただきましたことにいろいろ改変を加え、何とか仕事になっています。 御礼遅くなり失礼いたしました。 おそばせながら御礼申し上げます。
補足
お世話になります。遅くまでありがとうございます。帰宅したため、ご連絡が、遅くなり申し訳ございません。 ここまでして下さり、本当にありがたく思います。ひとつひとつ慎重にらやっておりますので結構、時間がかかってしまっており、上記にご教示頂きましたものにつきましてもまだテストできていないもので、すみません。 そのため、先に重複しないものについて取り急ぎご連絡させていただきたいと思います。確実なのは商品名とコードになります。 この二つは重複しないものとなります。 お手数お掛け致しますが、何卒よろしくお願い致します。
- MackyNo1
- ベストアンサー率53% (1521/2850)
例えば元データがSheet1のA２セル以下にあり、別シートのF1セルに抽出したい用語（例：桃）が入力してあるなら以下のような関数でご希望の表示(桃が入ったデータの抽出）ができます。 A2セルに以下の式を入力し下方向に適当数オートフィルコピーしてください。 =INDEX(Sheet1!A:A,SMALL(INDEX(ISERROR(FIND($F$1,Sheet1!$A$2:$A$100))*1000+ROW($A$2:$A$100),),ROW(1:1)))&"" B2セルに以下の式を入力し、右方向に1つ下方向に適当数オートフィルコピーしてください。 =IF($A2="","",VLOOKUP($A2,Sheet1!$A:$C,COLUMN(B:B),0)) ただし、上記の数式は必要以上にデータ範囲を大きくした入り、数式の数が多くなるとシートの動きが重くなるので、私なら検索用語を入力するF1セルに入力規則で用語をドロップダウンリストから選択できるようにしておいて、1枚のシートでデータの表示切り替えを行うようにした方が良いと思います。
お礼
MackyNo1 様 御礼遅くなりまして、恐縮いたします。ありがとうございます。 たくさんご回答をいただくことができ、全てやりきれておりませんが、先ずは御礼までと思いご連絡させて頂きました。 本当に感謝しております。 今後とも宜しくお願い致します。
- tom04
- ベストアンサー率49% (2537/5117)
こんばんは！ 横からおじゃまします。 No.5さんの方法と似たような感じになりますが、作業用の列を各Sheetにもうけます。 ↓の画像でSheet1に元データがあり、Sheet2～Sheet4のA1セルにそのSheetに表示したい「果物」を入力しておきます。 （作業用の列を各SheetのA列に設けます） 2行目の項目もそれぞれのSheetに入力。 この下準備ができたうえで、Sheet2を選択Shiftキーを押しながら最後のSheet見出しをクリック！ これでSheet2以降が作業グループ化されましたので、 そのままA2セルに =IF(COUNTIF(Sheet1!A2,"*"&A$1&"*"),ROW(),"") という数式を入れオートフィルでずぃ～～～！っと下へコピー！ （Sheet1のデータ以上コピーしておきます。） そしてB2セルに =IF(COUNT($A:$A)<ROW(A1),"",INDEX(Sheet1!A:A,SMALL($A:$A,ROW(A1)))) という数式を入れ、列方向・行方向にオートフィルでコピー！ 最後にSheet見出し上で右クリック → 作業グループ解除！ これで完了です。 ※ 今回は判りやすくするためにA列を作業列としましたが、もっと離れた列を作業列とした方が良いかもしれません。 ※ 数式が入っていれば作業列は非表示にしてもちゃんとデータが表示されるはずです。 以上、参考になりますかね？m(_ _)m
お礼
tom04 様 昨日はご回答誠にありがとうございました。すぐに御礼できず、大変失礼いたしました。 たくさんのご回答をいただくことができまだどれが良い方法なのかを試行している段階です。 tom04様はじめ皆様にも大変感謝しております。 取り急ぎの御礼で失礼ですが、また宜しくお願い致します。
- kagakusuki
- ベストアンサー率51% (2610/5101)
少々レイアウトを変更して、A列には「入力したデータが何番目であるのかを示す連番」を、B列には商品名を、C列には商品コードを、D列には金額を、それぞれ入力するものとします。 又、Sheet1の１行目には A1セル B1セル C1セル D１セル No. 商品名 商品コード 金額 という具合に各項目名が入力されていて、実際のデータは2行目以下に入力されているものとし、 同様に各商品別のシートの2行目には A3セル B3セル C3セル D3セル No. 商品名 商品コード 金額 という具合に各項目名が入力されていて、実際のデータは3行目以下に表示されるものとします。 まず、使用するExcelファイルを一旦保存して下さい。 次に、各商品別のシートの雛型となる「雛型」というシート名のシートを作成して下さい。 次に、雛型シートのA1セルに次の関数を入力して下さい。 =REPLACE(CELL("filename",A1),1,FIND("]",CELL("filename",A1)),)&"シート" 次に、雛型シートのA2セルに次の関数を入力して下さい。 =Sheet1!$A$1 次に、雛型シートのB2セルに次の関数を入力して下さい。 =Sheet1!$B$1 次に、雛型シートのC2セルに次の関数を入力して下さい。 =Sheet1!$C$1 次に、雛型シートのD2セルに次の関数を入力して下さい。 =Sheet1!$D$1 次に、雛型シートのA3セルに次の関数を入力して下さい。)) =IF(ROWS($1:1)>COUNTIF(Sheet1!$B:$B,"*"&REPLACE(CELL("filename",A3),1,FIND("]",CELL("filename",A3)),)&"*"),"",INDEX(Sheet1!$A:$A,MATCH("*"&REPLACE(CELL("filename",A3),1,FIND("]",CELL("filename",A3)),)&"*",INDEX(Sheet1!$B:$B,MATCH(A2,Sheet1!$A:$A,0)+1):INDEX(Sheet1!$B:$B,ROWS($A:$A)),0)+MATCH(A2,Sheet1!$A:$A,0))) 次に、雛型シートのB3セルに次の関数を入力して下さい。 =IF($A3="","",VLOOKUP($A3,Sheet1!$A:$D,MATCH(B$2,Sheet1!$A$1:$D$1,0),FALSE)) 或いは =IF($A3="","",INDEX(Sheet1!$B:$D,MATCH($A3,Sheet1!$A:$A,0),MATCH(B$2,Sheet1!$B$1:$D$1,0))) 次に、雛型シートのB3セルをコピーして、雛型シートのC3～D3の範囲に貼り付けて下さい。 次に、雛型シートのA3～D3の範囲をコピーして、同じ列の4行目以下に貼り付けて下さい。 次に、雛型シートのコピーシートを、商品の種類の数だけ複製して下さい。 そうして作った雛型シートの各コピーシートのシート名を、「桃」や「みかん」、「さくらんぼ」といった、各商品毎のキーワードと同じ名称（鉤括弧は無用）に変更して下さい。 以上で準備は完了で、後はSheet1にNo.、商品名、商品コード、金額といった各データを入力しますと、各キーワード毎に振り分けられたデータが、各商品別のシートに自動的に表示されます。 尚、No.を付けるのは、入力されている内容に重複するものが無い列が必要となるからで、もし、商品名か商品コードのどちらかが、Sheet1内に同じものが複数回重複して現れる事は無い事が保障されている場合には、「No.」欄を設けなくとも済む方法もありますので、もし、そちらの方法の方を御要望でしたら、その旨を御知らせ下さい。 それから、各商品別のシートのA1セルには「『商品のキーワード』+『シート』」の形式を持つ文字列が表示されます。 例えば、みかんシートの場合には、 みかんシート と表示されます。 もし、「みかん」の前後に付く内容を変更して、例えば 商品名《みかん》リスト 等の様にする場合には、雛型シートのA1セルに関数を入力する際に、その関数を次の様なものとして下さい。 ="商品名《"&REPLACE(CELL("filename",A1),1,FIND("]",CELL("filename",A1)),)&"》リスト"
お礼
kagakusuki様 図解までつけていただきまして本当にありがとうございます。 すごいです！ 後々のことまで考えますと良さそうです。 本当に助かりました。また宜しくお願い致します。
補足
kagakusuki様 お世話になります。先ほどはありがとうございました。少し見直していたのですが、文中記載のNo.を付けない方法をお願いできますでしょうか？ たびたび本当に申し訳ないのですが・・・取引先から送られてくる生データをそのままシートにコピーするだけという形にしたいと思っています。 1ヶ月に1回程度商品見直しがあり、一旦全ての商品がシート毎にわけることができましたら、セルに色を付けておいて並びかえれば前回から引き続きある商品、なくなった商品という形で判別しやすくなり、新しいものだけを残すことができるのではと思っています。 わがままを言ってすみませんが、何卒宜しくお願い致します。
- KURUMITO
- ベストアンサー率42% (1835/4283)
シート１ではA１セルに商品名、B１セルに商品コード、C１セルに金額の項目名が有り、それぞれのデータは２行目ではなく３行目から下方に入力されているとします。 そこで作業列を作って対応します。 D１セルには桃、D２セルにはももと入力します。このように２行目にはA列で入力されている文字でも２つのケースが考えられる場合にはその文字を入力します。 E１セルにはみかん、E2セルにはミカンなどと入力します。以下同様に右横のセルに必要なだけ果物の名前などを入力します。なお、２行目が特になければ空白のセルとします。 これらの作業が済んだうえでD３セルには次の式を入力して必要なだけ右横方向にドラッグコピーしたのちに下方にもドラッグコピーします。 =IF(OR($A3="",D$1=""),"",IF((COUNTIF($A3,"*"&D$1&"*")+IF(AND(D$2<>"",COUNTIF($A3,"*"&D$2&"*")>0),1,0))>0,MAX(D$1:D1)+1,"")) これでシート１での作業は終わります。 次にシート２の画面を表示してからシート見出しのSheet３,Sheet４,Sheet５などをCtrlキーを押しながらクリックします。 すべてのシートが同じ作業グループになります。 その上で表示されているシート２の画面でA１セルには桃と入力します。 A２セルには商品名、B２セルには商品コード、C２セルには金額と文字を入力します。 その後にA３セルには次の式を入力してC３セルまで横にドラッグコピーしたのちに下方向にもドラッグコピーします。 =IFERROR(INDEX(Sheet1!$A:$C,MATCH(ROW(A1),INDEX(Sheet1!$D:$XX,1,MATCH($A$1,Sheet1!$D$1:$XX$1,0)):INDEX(Sheet1!$D:$XX,10000,MATCH($A$1,Sheet1!$D$1:$XX$1,0)),0),COLUMN(A1)),"") 最後にシート１を選択することで作業グループが解除されます。 シート２ではA１セルに桃が入力されているので桃のデータが表示されますし、他のシートでA１セルのデータをみかんと入力すればみかんのデータが表示されます。A１セルに入力する果物によって表が変わります。 なお、A１セルに入力する果物の名前はシート１のD１セルから右横方向に入力したと同じ名前にしてください。例えばももの場合には表示が行われません。
お礼
KURUMITO 様 ありがとうございます。なるほどです。そこまで手間もかからなそうなので、試してみます。 本当にありがとうございます。
- kagakusuki
- ベストアンサー率51% (2610/5101)
少々確認したい事があります。 >シート２：桃（”桃”が入っている場合に振り分けられるようにしたいです。） との事ですが、そうしますと「桃・苺・オレンジのジャム詰合せセット」という商品を1つ付け加えた場合、「桃」というシートと「苺」というシートと「オレンジ」というシートの各々において表示される商品名が1行ずつ増えますから、合わせて3行増える事になりますが、その様な形で宜しいのでしょうか？（C列の金額も、3ヶ所において増える事になります）
お礼
いろいろ見返しておりましたら、とてもありがたく思えてきまして、改めて御礼申し上げます。本当にありがとうございます。
補足
気に留めていただきまして、ありがとうございます。 1行の中に複数のカテゴリーが入ることはないものとなります。 kagakusuki様にご質問いただきましたような、 新鮮な桃とみかん 農家直送大根とほうれん草 といったようなものはなく、必ず、 新鮮な桃 おいしいみかん 産地直送、甘～いイチゴ といった形で1行の中に含まれる桃やらみかんやらというのはひとつのみになります。 もし、そういった複数のカテゴリーを含むタイトルがある場合でしたら、kagakusuki様のおっしゃられます通り、それぞれに同じものが入って構いません。 宜しくお願い致します。
- sporespore
- ベストアンサー率30% (430/1408)
D列を作ってそこに桃やみかんといったキーワードだけを含むデータ系列を作れば、あとはピボットテーブルを使って簡単に作成できます。
お礼
sporespore様 昨日はピポットのご提案ありがとうございました。 別シートで商品振り分け後の作業がありますので、試してみて悪くなかったのですが、結論として元データが新しいものになった場合に、ピポットを更新すると作業した分との差異が分からなくなってしまうため何とか関数でやる方向にしたいと思いました。 ご提案ありがとうございました。
補足
ありがとうございます。 ピポットテーブルは考えたのですが使い慣れていないこともあり、関数でできればと思っていました。 良い機会なのでピポットもいじってはみたいと思います。
関数を用いて？ ユーザー定義の設定でソート機能を用いるのでなく？？？
お礼
お世話になります。先日は気に留めていただきまして、ありがとうございました。 お陰様で、皆様にたくさんのご教示をいただきまして、いろいろと改変を加えたところで何とか仕事になっています。 またもし別の機会がございましたらお助け下さい。 ありがとうございました。
補足
気を留めて頂いてありがとうございます。 関数で商品名の中の「桃」や「みかん」といったキーワードを見つけて、その文章をそのまま別シートに移すといった形です。
- yugere
- ベストアンサー率37% (48/127)
ご苦労さまです。 解凍の前に、一つご質問があります。 B列（商品コード）とありますが、このコードはどういったものでしょうか？ このコードにより商品を振り分けることは可能でしょうか？
お礼
お世話になります。先日は気に留めていただきまして、ありがとうございました。 お陰様で、皆様にたくさんのご教示をいただきまして、いろいろと改変を加えたところで何とか仕事になっています。 またもし別の機会がございましたらお助け下さい。 ありがとうございました。
補足
すみません、ありがとうございます。 そのデータはオリジナルでつけるコードなので一貫性がないものとなっています。 そのため、セル内にある商品名の一部で抽出したいといった内容となります。 すみませんが、宜しくお願い致します。
お礼
kagakusuki 様 昨日よりたくさんの方法をご教示いただきまして、本当にありがとうございます。たくさんの方法があり全てやりきれておりませんので、今日また進めていきたいと思います。 またわからない箇所がございましたらご教示いただきたく、ご連絡が遅くなり申し訳ございませんが、取り急ぎ御礼申し上げます。 本当にありがとうございます。