Excel。日付+連番の管理番号を作りたいけど、注意が必要です。
＜TEXT関数&COUNTIF関数＞
日付+連番という形で管理番号を作る表があります。
A列の管理番号を作りたいのですが、ちょっと注意しないと、難しく考えてしまう恐れがあります。
例えば、日付から月を抽出するならば、MONTH関数。
日を抽出するならば、DAY関数を使ってと考えると思います。
確かに、これらの関数を使うのもいいのですが、数式が煩雑になりがちです。
考え方として、日付のところは、0付数値の年月が抽出できれば、いいわけですよね。
MONTH関数などをつかっても、結局0付数値にすることができませんので、さらに＋αをしないといけません。
そこで、0付数値で年月を抽出場合には、TEXT関数を使うといいわけですね。
TEXT関数は、表示形式を指定したい形にすることができる関数ですね。
なので、今回は、TEXT関数とCOUNTIF関数だけを使って、日付連番の管理番号をつくっていきます。
一発で数式を作れるのですが、説明を兼ねて、まずは、E列以降を使って確認していきましょう。
E2をクリックして、TEXT関数ダイアログボックスを表示しましょう。
値には、B2。
表示形式を”mmdd”。
これで月日のデータを抽出することができます。
MONTH関数とか使うよりも簡単でわかりやすいですね。
しかも、”0”をつけた形で対応しています。
それでは、OKボタンをクリックして、オートフィルで数式をコピーしましょう。
E2の数式は、
=TEXT(B2,"mmdd")
となっていますね。
【連番はCOUNTIF関数】
同じ日の場合は、01～という連番を設定したいわけですね。日付が変わるまで連番を設定する場合には、COUNTIF関数を使っていきます。
F2をクリックして、COUNTIF関数ダイアログボックを表示しましょう。
範囲には、$B$2:B2。
起点となるB2を絶対参照にしておいて、オートフィルで数式をコピーすると、起点のB2からの範囲が自動的に延長されて、その範囲内の対象データを数える動きをします。
検索条件は、B2。
OKボタンをクリックして、オートフィルで数式をコピーしましょう。
F2の数式は、
=COUNTIF($B$2:B2,B2)
あとは、0付数値に変える必要がありますので、TEXT関数を使って、数式を修正していきます。
=TEXT(COUNTIF($B$2:B2,B2),"00")
こうすることで、0付数値に変えることができましたね。
E列とF列で作成した数式を結合した形の数式をA列に作ればいいわけですね。
A2の数式は、
=TEXT(B2,"mmdd")&TEXT(COUNTIF($B$2:B2,B2),"00")
と設定してあげることで、日付＋連番の管理番号を作ることができましたね。
TEXT関数は意外と使い勝手がいい関数なので、表示形式を使えればと思える場合には、TEXT関数を使うことで、比較的容易に解決できることもありますので、覚えておくといい関数の一つですね。
