エクセルで父の仕事を手伝いたい

質問

自営業の両親を少し楽にしたいと思い、
仕事の合間に事務作業をホンの少しだけ手伝おうと思います。
まずは、次の作業をエクセルで自動化したいのです。

1、父の店に問屋から定期的に品物が送られてきて、その品物ごとに取り扱いが決まっています。
たとえば商品Aは○○さんに配達、Bは棚にとって置くなどです(もう少し複雑ですが）。

2、問屋からは、ネット経由で納品書（CSV)をダウンロードすることができ、どの商品が何日に届くかわかります。

3、商品には固有の商品コードが付いていて、
12345-67890
というように5桁の数字ー(ハイフン)　5桁の数字という組み合わせで出ています。
そのうち必要な数字は
一番左側の数字が2の場合、左端の4桁
一番左端の数字が2以外の場合、左端の5桁で、その他の数字は無視します。

4、そこであらかじめ
商品コードの一番左側が2345x-xxxxの商品はAさんに配達
　　　同　 56789-xxxxの商品はとり置き
というように商品コードと品物の取り扱いの対応表を作っておいて、

5、納品書と取り扱い対応表を照らし合わせて
納品書に「商品の取り扱い」という項目を自動で付け加えるようにしたいのです。

以上、長々と申し訳ありません。なお、エクセルについてのスキルは、かんたんな集計表を作ったりする程度です。マクロもかんたんなものは使ったことがあります

回答No.3

tinu 2000（@tinu2000） · ベストアンサー

親孝行の本屋の兄ちゃんのために一肌脱ぐか！（笑）

CSVファイルはダブルクリックしたらエクセル形式に表示されることは分っていると思います。
この納品書の横展開がどこまであるか不明ですので、J列まであると仮定します。
商品コードがA列にあると仮定します。

新しいエクセルを立ち上げます。
一行目はタイトル行として、使いましょう。
A1に　商品コード
B1に　商品名
C1に　納品日
D1に　ほにゃら
・
J1に　にゃんにゃん
K1に　Key
L1に　商品の取り扱い
X1に　対応Key
Y1に　取り扱い方法

といった具合に必要なところまでタイトルを入れておきます。
バックカラーを付けて枠を付けると、それらしくなります。

取り扱い対応表を作っておきます。
X2　に　2345　　Y2　に　Aさんに配達
X3　に　2444　　Y3　に　Bさんに配達
X4　に　34567　 Y4　に　棚Ａに取り置き
X5　に　45678　 Y5　に　棚Ｂに取り置き
・
・
・
X10 に　99999　 Y10 に　問屋に返品

と作っておきます。
ここでは9種類を例にしますが、本当は必要数の行数になります。

K2に　「=if(left(A2,1)="2",left(A2,4),left(A2,5))」
L2に　「=VLOOKUP(K2,$X$2:$Y$10,2,FALSE)

K2とL2を選択して、問屋から来る納品書の最大件数以上の行まで下へコピーします。
L行に「#N/A」が表示されますが、それで正常です。
表示させない方法もありますが、ここでは省略します。

このエクセルを保存して置きます。（名前は「取り扱い表」とでも自由に付けて下さい。
この「取り扱い表」をテンプレートとして、これからいつも使うことになります。

ここから、納品書をダウンロードするたびに行なう定期処理
１．取り扱い表を開きます。
２．問屋から来るCSVの納品書をダブルクリックで開きます。
３．納品書のA1をクリックして、
４．Ctrl＋End　でデータの最後まで選択する。
５．Ctrl＋C　でコピーする。
６．取り扱い表のA2をクリックして
７．Ctrl＋V　で貼り付けします。

これでL列に取り扱いの指示が表示されます。
必要のない列や行は非表示にして印刷するなり、名前を別にして保存するなり、
あとは、よしなに！

取り扱い表は保存しないで置くと、毎回前回のデータを消さなくて済みます。
横の位置は納品書の項目数によって読み方をズラして貰って良いです。

お礼

ご回答からお礼まで大変遅くなってしまいました。
この質問は、母が入院したのを機に、父の仕事を少し楽にさせようと思ってのことでしたが、
その後母の様態が急変、帰らぬ人となってしまいました。

今は母の四十九日も終わり、母の仕事の引継ぎもなんとか済みましたので、いろいろと試行錯誤しながら質問の式を完成させていきたいと思います。
方法としてはtinu2000さんのご回答を基礎に、他の皆さんの方法と自分なりの工夫も加えたやり方になると思います。（頭のなかでは、一応の方針は出来ているつもりです。）
それまでは、なんとか手作業で仕事をしていくつもりです。
どうもありがとうございました。

補足

>ここから、納品書をダウンロードするたびに行なう定期処理

ありがとうございます!!!
じつは、この質問をする前に、沢山の解説書を読んだんですが、ひとつの表として完結して様々な計算や処理を行う例はたくさん書いてあるのに、ご説明のような定期処理をする方法が見つからなかったのです。
ANo.2さんへの補足に書いたのですが、今回の私の質問を整理すると、3つのパートに分けられると思うのですが、
tinu2000さんのご回答で、全てのパートの答えが出揃いました。

>親孝行の本屋の兄ちゃんのために
そうです。既にお気づきかもしれませんが、実は「商品コード」というのは、雑誌の後ろ一番下あたりに書いてある「雑誌コード」のことなのです。

仕事の合間に作って、完成したら改めてお礼をさせていただきます。

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

回答No.2

imogasi

私見ですが
エクセルを仕事に使えるように・使うには
（１）エクセルとしての細かい質問にブレークダウンする技量
表現仕方も含む
本質問はやりたいことだけが、多数の質問店点が羅列状態です。
質問を１行程度で箇条書きし、別々の質問でOKWAVEに出すことを勧めます
本質問では、「（商品）コードから文字列（「取り扱い」を示す漢字）を
割り出すにはどうするか？」で済みます。
（２）少し先の目標でしょうが、VBAを勉強すべきです。
仕事で省力化してスムーズにこなすには、関数だけでは、不十分です。
ーーー
本件整理すると
シートは
（１）納品表シート
（２）対応表シート
の２つが問題になってます
（１）納品表しーとは
A列　商品コード　　
B列　修正商品コード（これが合ったほうが良いように思う）
多分=IF(LEFT(A1,1)="2",LEFT(A1,4),LEFT(A1,5)のようなもの。
C列　商品取り扱い　　VLOOKUP関数で表を引く。
（２）対応表シート
VLOOKUP関数を使うための対応表です
修正商品コード　取り扱い内容（漢字が良かろう）
＃１で出ているコード順にするのは必要ないとおもう（FALSE型の場合）。
ーーーー
対応表の具体例を挙げてみてください。
VLOOKUP関数は
=VLOOKUP(A1,Sheet2!$A$1:$B$3,2,FALSE)
のSheet1は対応表シート名で置き換えてください。
ーーー
不要な表現が有ります
１．の終わりまでは不要です。VLOOKUP関数の対応表を実例で示せば
判ることですから。

お礼

ご回答からお礼まで大変遅くなってしまいました。
この質問は、母が入院したのを機に、父の仕事を少し楽にさせようと思ってのことでしたが、
その後母の様態が急変、帰らぬ人となってしまいました。

今は母の四十九日も終わり、母の仕事の引継ぎもなんとか済みましたので、いろいろと試行錯誤しながら質問の式を完成させていきたいと思います（それまでは、なんとか手作業でやっていくつもりです）
どうもありがとうございました。

追伸：質問内容へのご回答に加えて、excelで関数を作る時の考え方の整理法など大変勉強になりました。

補足

ご回答及びご指摘ありがとうございます。
実際に表を作って検証できるようになるまで時間がかかりそうですので、とりあえずこの補足欄を使ってお礼をさせてもらい、この仕事が完成してから、あらためてお礼を投稿したいと思います。
１、
>（１）エクセルとしての細かい質問にブレークダウンする技量

>表現仕方も含む
>不要な表現
まったく仰るとおりです。
質問を整理できないまま、背景説明だけして回答者にまる投げするようなやり方にしてしまい、申し訳ありませんでした。
アドバイスを元に質問を整理してみると、
A.
2種類の商品コードを同時に利用できる形にする方法
→ご回答の(1)ですね
商品コード～対応表の間に修正商品コードを挟むというのは、目からウロコの発想でした（わかっている人には当然のことなんでしょうが・・・）
2種類の商品コードをひとつの関数で処理しようとして、わけがわからなくなっていましたので。

B.
修正商品コードに対応する取り扱いを呼び出す関数（→(2)）

C.
日常業務として日々送られてくる納品書を自動的に変換、加工する方法
→ANo.3さんがお答えくださいました。
となるでしょうか。
こう整理してみると、なるほど、問題を解く上での糸口のようなものが見えてきた気がします。

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

回答No.1

nikenike1

ご質問の内容より
4.の対応表（マスタ）の作成と
5.の式の作成が重要かと思われます

4.マスタについて
例
シート２に以下のようなマスタを作成します（例ではA1からB4の範囲へ入力したものと仮定します）
コード　　扱い
1　　　　取置
2　　　　配達
3　　　　取置
※コードが昇順で並んでいる必要があります（←特に重要）。品目が増えたらソートしなおします。

5.数式について
納品書のCSVをエクセルデータに取り込んで、新たに”扱い”の列を設け、そこに式を入力します
※イメージ（コードはＡ列に入っているものとします）
コード　扱い
2　　　（式）
3　　　（式）
1　　　（式）
式にはデータを参照するvlookupを使用します。
書式は
vlookup（検索元、マスタの範囲（絶対指定）、表示するマスタの列、false）
となるので、例では
VLOOKUP(A2,シート2!$A$2:$B$4,2,FALSE)
を各行にコピー貼り付けすれば目的の動作がすると考えられます。

あとは上記例の参照部分などを御使用のシートにあわせて編集してください。

お礼

ご回答からお礼まで大変遅くなってしまいました。
この質問は、母が入院したのを機に、父の仕事を少し楽にさせようと思ってのことでしたが、
その後母の様態が急変、帰らぬ人となってしまいました。

今は母の葬儀も終わり、母のやっていた仕事の引継ぎもなんとか済みましたので、いろいろと試行錯誤しながら質問の式を完成させていきたいと思います（それまでは、なんとか手作業でやっていくつもりです）
どうもありがとうございました。

追伸：教えていただいたvlookup関数ですが、調べてみるといろいろと応用が出来そうです。
どうもありがとうございました。

補足

早速のご回答をしかも深夜にわざわざいただき、ありがとうございました。
まだ全体像がつかみきれていないのですが、なんとか試行錯誤すればできそうです。
(仕事の合間に造りますので、検証には時間がかかりそうですが)

ひとつわからない点は、質問の３にある通り、
コードの左4桁または5桁だけを使い、後の桁(とハイフン）を無視したい
(マスタのほうでは左4桁または5桁だけを記述）
のですが、この方法ではどうなりますでしょうか。
お手数をおかけして申し訳ありません。