エクセル関数で商品に区別符号をつける方法

質問

• エクセルの関数を使って商品のサイズと色にそれぞれ区別する記号番号を付ける方法を教えてください。
• 商品種類が3000以上、商品数は15000種類を超えるため、関数を使用して短時間で作業を行いたいです。
• サイズ名とカラー名を別々に番号付けして、同じ商品種類に対して記号番号を割り当てています。

回答No.4

kagakusuki · ベストアンサー

今仮に、質問者様の添付画像にあるような表がSheet1に存在しているものとします。
　又、Sheet1のF列に廃版となった商品であるか否かを区別するために、取扱いの有無を記入する欄を追加するとともに、Sheet2のA列を作業列として使用するものとします。

　まず、Sheet1のF列にも表の枠線を設定して下さい。
　次に、Sheet2のA2セルに次の関数を入力して下さい。

=IF(INDEX(Sheet1!$A:$A,ROW())="","",INDEX(Sheet1!$A:$A,ROW())&"☆"&INDEX(Sheet1!$B:$B,ROW())&"☆"&COUNTIF(A$1:A1,INDEX(Sheet1!$A:$A,ROW())&"☆"&INDEX(Sheet1!$B:$B,ROW())&"☆*")+1&"★"&INDEX(Sheet1!$D:$D,ROW())&"★"&COUNTIF(A$1:A1,INDEX(Sheet1!$A:$A,ROW())&"☆*★"&INDEX(Sheet1!$D:$D,ROW())&"★*")+1)

　次に、Sheet2のA2セルをコピーして、Sheet2のA3以下に(Sheet1において表の枠が引かれている行の行数を上回るのに十分な行数となる様に)貼り付けて下さい。
　次に、Sheet1のC2セルに次の関数を入力して下さい。

=IF(OR(INDEX($A:$A,ROW())="",INDEX($B:$B,ROW())=""),"",IF(COUNTIF(Sheet2!$A$1:INDEX(Sheet2!$A:$A,ROW()),INDEX($A:$A,ROW())&"☆"&INDEX($B:$B,ROW())&"☆1★*"),IF(COUNTIF(INDEX(Sheet2!$A:$A,ROW()),"*☆1★*"),CHAR(96+COUNTIF(Sheet2!$A$1:INDEX(Sheet2!$A:$A,ROW()),INDEX($A:$A,ROW())&"☆*☆1★*")),INDEX($C:$C,MATCH(INDEX($A:$A,ROW())&"☆"&INDEX($B:$B,ROW())&"☆1★*",Sheet2!$A:$A,0))),"【作業列の関数に欠損があります】"))

　次に、Sheet1のC2セルをコピーして、Sheet1のC3以下に貼り付けて下さい。
　次に、Sheet1のE2セルに次の関数を入力して下さい。

=IF(OR(INDEX($A:$A,ROW())="",INDEX($D:$D,ROW())=""),"",IF(COUNTIF(Sheet2!$A$1:INDEX(Sheet2!$A:$A,ROW()),INDEX($A:$A,ROW())&"☆*★"&INDEX($D:$D,ROW())&"★1"),IF(COUNTIF(INDEX(Sheet2!$A:$A,ROW()),"*★1"),COUNTIF(Sheet2!$A$1:INDEX(Sheet2!$A:$A,ROW()),INDEX($A:$A,ROW())&"☆*★1"),INDEX($E:$E,MATCH(INDEX($A:$A,ROW())&"☆*★"&INDEX($D:$D,ROW())&"★1",Sheet2!$A:$A,0))),"【作業列の関数に欠損があります】"))

　次に、Sheet1のE2セルをコピーして、Sheet1のE3以下に貼り付けて下さい。

　以降は、Excelのバージョンによってやり方が異なって来るのですが、Excelのバージョンが不明ですので、取り敢えずはExcelのバージョンがExcel2007以降のものである場合の方法を回答致します。

　次に、以下の操作を行って、Sheet1のE2以下のセルに入力規則を設定して下さい。

Sheet1のE2セルを選択
　　↓
Excelウィンドウの上の方にある[データ]タブをクリック
　　↓
現れた[データの入力規則]ボタンをクリック
　　↓
現れた選択肢の中にある[データの入力規則]をクリック
　　↓
現れた「データの入力規則」ダイアログボックスの[設定]タブをクリック
　　↓
「入力値の種類」欄をクリック
　　↓
現れた選択肢の中にある[リスト]をクリック
　　↓
現れた「元の値」欄に次の様に入力

無し,有り

　　↓
「データの入力規則」ダイアログボックスの[OK]ボタンをクリック
　　↓
Sheet1のE2セルをコピーして、Sheet1のE3以下に貼り付け

　次に、以下の操作を行って、Sheet1のB列～D列の2行目以下のセルに入力規則を設定して下さい。

Sheet1のA2セルを選択
　　↓
[ホーム]タブ内の「スタイル」グループの中にある[条件付き書式]ボタンをクリック
　　↓
現れた「スタイル」グループの中にある[条件付き書式]ボタンをクリック
　　↓
現れた選択肢の中にある[ルールの管理]をクリック
　　↓
現れた「条件付き書式ルールの管理」ダイアログボックスの中にある[新規ルール]ボタンをクリック
　　↓
現れた「新しい書式ルール」ダイアログボックスの「ルールの種類を選択して下さい」欄の中にある[数式を使用して、書式設定するセルを決定]をクリック
　　↓
現れた「次の数式を満たす場合に値を書式設定」欄の中に

=INDEX($F:$F,ROW())="無し"

と入力
　　↓
「新しい書式ルール」ダイアログボックスの[書式]ボタンをクリック
　　↓
現れた「セルの書式設定」ダイアログボックスの[フォント]タブをクリック
　　↓
現れた「色」欄をクリック
　　↓
現れた背景色のサンプルの中にある薄い灰色の四角形をクリック

「文字飾り」欄の[取り消し線]と記されている箇所をクリックする事でチェックを入れる
　　↓
「セルの書式設定」ダイアログボックスの[OK]ボタンをクリック
　　↓
「新しい書式ルール」ダイアログボックスの[OK]ボタンをクリック
　　↓
「条件付き書式ルールの管理」ダイアログボックスの「適用先」欄をクリック
　　↓
Sheet1のA2セルからSheet1のD列における表の枠がある最終行の所にあるセルまでの範囲をまとめて選択
　　↓
「条件付き書式ルールの管理」ダイアログボックスの[適用]ボタンをクリック
　　↓
「条件付き書式ルールの管理」ダイアログボックスの[OK]ボタンをクリックック

　これで、サイズ番号やカラー番号が自動的に決められる様になります。
　尚、廃版となった商品に関しましては、商品名を削除する事は絶対にしないで下さい。
　その代わりに、E列に「無し」と入力する事で区別して下さい。(「無し」と入れますと、自動的に取り消し線が引かれるとともに、表示が薄くなります)

補足

細かく設定までご教授いただき感謝しております。
関数でここまで出きるのだと驚くとともに奥深さと習熟の必要性を強く感じております。

当方が使っているExcelのバージョンはExcel2003 SP3で、共通の方法で行える部分であるサイズ記号とカラー記号を付けていくところまで実現できております。

お教えいただいた以下の部分で「条件付き書式」にて選択肢の[ルールの管理]が見つけられず、以降が進んでいない状態です。
-------------------------------
Sheet1のA2セルを選択
　　↓
[ホーム]タブ内の「スタイル」グループの中にある[条件付き書式]ボタンをクリック
　　↓
現れた「スタイル」グループの中にある[条件付き書式]ボタンをクリック
　　↓
現れた選択肢の中にある[ルールの管理]をクリック
-------------------------------

作業の内部実情まではお伝えしていなかったのですが、深いところまでご配慮いただき感謝しております。
実は販売サイト用プログラムから吐き出したCSVを今回のようにエクセル上で表示させ記号の割り振りを行っています。在庫管理そのものを行っていないため商品削除等の操作まで考えていなかったのですが、これから同じようにCSVデータを利用する際に、前データとの参照の際に有効になってくると思います。

実現、即使用できるためものをお教えいただいて非常に感謝致しております。