エクセルでロット番号を作成したい。

私の使っているロット番号は、6C041M999で最初の６は、２０１６年の６で次のアルファベットは、一週目が、Aで二週目がB三週目がCのようにその月の週を表し、次の０４は、月を表し、次の１は、曜日で、１が月曜日２が火曜日３が水曜日で、次のM９９９は、私の意味です。ですから6C041M999は、２０１６年４月１２日月曜日に私M９９９が作った製品を表しています。これをエクセルで、4/12 Enterキーを押せば、半角英数字で変換される表計算式を教えて下さい。

質問者からの補足コメント

• すいません２０１６年４月１２日は火曜日ですから、6C042M999でした。6C041M999は、２０１６年４月１１日月曜日なので4/11Enterでお願いします。

    補足日時：2016/04/17 15:29

• 

  ご回答ありがとうございます。その月の第１週目がA、第２週目がB、第３週目がC、第４週目がD、第５週目がEに、うまく変換されません。そこ以外は、うまくいきました。もう一度ご回答お願いします。

    補足日時：2016/04/22 22:19

No.5 回答者： 銀鱗 回答日時： 2016/04/23 04:00

失礼しました。

今年の1月31日と2月1日は同じ週にあるとして、どちらも「E」と表記されると思います。
自分の回答は、その年の第一週からの連番です。（その年の第1木曜日のある週を第1週目としてカウントする）
自分が使用していたシリアル番号の体系がこれだったもので勘違いしていました。

今年の5月1日(日)は5月の1週目、5月2日(月)は2週目としてよいのであれば、次のように計算できます。
月曜日から曜日をカウントしているようなので…、月曜を「0」～日曜を「6」とし、
その月の初日の曜日に対するWEEKDAY関数が返す数値（初日の曜日に対する数値）を日付に加えることで
その月の何週目になるのかを判断します。
判断方法は、その月の初日の曜日に対するWEEKDAY関数が返す数値を日付に加え７で割る。
すると、割った余りを切り捨てた値は、
　1週目は0
　2週目は1
　3週目は2
　4週目は3
　5週目は4
になる。
そこで前の回答に準じてCHAR関数を使うと、
＝CHAR( INT( A1+WEEKDAY( DATE(YEAR(A1),MONTH(A1),1) ,3)/7)+65)
CHOOSE関数を使うと
＝CHOOSE(INT(A1+WEEKDAY(DATE(YEAR(A1),MONTH(A1),1),3)/7)+1 , "A","B","C","D","E","F")
になります。（たぶん…未検証）


・・・
覚えておくと良いかもしれない知識。
WEEKDAY関数は1900年3月1日より前の日付では多くのパソコンにおいて誤った値を返します。
これは、1900年は閏年ではない（※）のですが、
Excel開発当時に表計算ソフトで絶対的シェアを持っていたLotus社の「1-2-3」が1900年1月1日を日付計算の起点 "1" とし、さらに閏年として扱っていたため
Microsoft社は「Excel」に「1-2-3」と互換性を持たせるためにこのシステムを採用しているためです。
そのため 1904年1月1日を起点 "1" とする設定もありますが、DOS版やWindows版では1900年1月1日を起点にする設定が初期設定になっています。
「1-2-3」と競合しないMac版では1904年1月1日が初期設定になっています。
ただし1900年を起点とするシステムと1904年を起点とするシステムでそれぞれ作成された物は
日付（シリアル値）に互換性がないため、異なるシステムで使用すると日付や曜日にずれが生じます。注意してください。

このため他のパソコンで表示させると日付や曜日が異なることがあるときは、
オプションで日付の起点が1900年になっているか1904年になっているかの違いが原因になっている可能性があるので、
データが作られたパソコンでの設定を確認し、同じ設定にすることを勧めます。

※
「西暦が100で割り切れる年は閏年としない。ただし、西暦が400で割り切れる年は閏年とする」
というルールがあります。そのため2000年は閏年だったのですが、1900年は閏年にはなりません。

No.4 回答者： 銀鱗 回答日時： 2016/04/17 16:15

失礼しました。

E1セルのWEEKDAY関数で指定を間違えていました。
これでは日曜日が１になり、月曜日が２になります。
　=WEEKDAY((A1),2)
と置き換えてください。

No.3 回答者： 銀鱗 回答日時： 2016/04/17 16:10

C1セルに入力する ISOWEEKNUM関数は、Excel 2013から追加された関数なので、バージョンによっては使えないかもしれません。

1月の第1木曜日がある週をその年の第1週とするルールに基づいて数値を返す関数です。

Excel 2010では
WEEKNUM(A1,21)
と置き換えてください。

この回答へのお礼

すばやい回答、ありがとうございます。さっそく明日、職場のパソコンで試してみます。

お礼日時：2016/04/17 18:15

No.2 回答者： hallo-2007 回答日時： 2016/04/17 16:08

仮に　Ａ2セルに　日付　4/12　と入っているとして

空いているセルに
=RIGHT(TEXT(A2,"yy"),1)　で　６が表示
=CHOOSE(WEEKDAY(A2),"A","B","C","D","E","F","G","H")　で　Ｃが表示
=TEXT(A2,"mm")　で　０４が表示

これらを　＆　でつなげて　最後に　& "M999"　を付けくわえる事で可能かと。

この回答へのお礼

すばやい回答、ありがとうございます。さっそく明日、職場のパソコンで試してみます。

お礼日時：2016/04/17 18:18

No.1 回答者： 銀鱗 回答日時： 2016/04/17 16:02

A1セルに4/11と入力するとする。

B1セルに
　=RIGHT(YEAR(A1),1)
C1セルに
　=CHAR(ISOWEEKNUM(A1)+64)
D1セルに
　=TEXT(A1,"mm")
E1セルに
　＝WEEKDAY(A1,1)
F1セルに
　M999
と、それぞれ入力。
B２セルに
　=B1&C1&D1&E1&F1
と入力すると求めたい結果を得られます。

この回答へのお礼

すばやい回答、ありがとうございます。さっそく明日、職場のパソコンで試してみます。

お礼日時：2016/04/17 18:16