VLOOKUPとCEILINGを使い、絶対参照と相対参照を駆使して10個で1カウントアップしてナンバリングしていくシステムをつくる

関連記事：VLOOKUPの検索値にワイルドカードをつかってみた/Excel応用

シチュエーションを考えてみた

ある会社の拠点にシリアル番号のついた商品を送る

シリアル番号で発送拠点は決まっていて、その指示はExcelで受け取っている

各拠点毎に1から始まる３ケタの通し番号をつけて、出荷の箱単位に任意フォーマットでユニークの番号をつける約束になっている

シリアル番号と通し番号の紐付けはコッチでやる事

１つの段ボール箱に10個までしか商品は入れられない

荷受けの検品が楽にできるように、10個を満たしてから次の箱に入れる事

つまり10個未満の箱は各拠点1箱以下でなければならない

ここまでがルール

Excelを見てみよう

解説します

受け取ったデータは黄色に着色されたAとBカラム

C列以降はこちらで作成した

まず拠点毎の管理番号

=COUNTIF($B$2:B2,B2)

B列の拠点名をカウントしていくのだが、範囲に注目してほしい

最初のB2は$B$2で絶対参照になっているから固定

範囲の右側B2は相対参照となっているので、下のセルに行くに連れてB3 B4 B5...となっていく

これによって拠点名を数えながらカウントアップしていくことができる

002と３ケタ表示されているのはセルの書式設定でユーザー定義で0を選択し、000と入力して３ケタ表示するように設定している

次に出荷箱番号

(以下2013/01/04関数にて解決。関連記事参照)
まずB列の拠点名を全てF列にコピーして重複をなくす
コピーしたFを列ごと選択して【データ　⇒　重複を削除】するのだが、その時「現在選択されている範囲を並べ替える」を選択することを忘れずに

G列に上から順に番号を振って、念のため拠点毎の数をH列に出しておく
H2セルの構文

=COUNTIF(B:B,F2)

B列全体からFの値をカウントしているF2 F3...

さあここからが本番

D列の出荷番号だが、まずは演算式を見てみよう

=VLOOKUP(B2,$F$2:$G$6,2,0)&"-"&CEILING(COUNTIF($B$2:B2,B2),10)/10

VLOOKUPでB列の拠点をF列の拠点番号から探す

間にハイフンを入れて、管理番号と同じようにB列をカウントしていくのだが、箱の中には10個入れるので、10個カウントしたら1を足していきたい

ここでCEILINGを使って、カウントした個数を10個単位で繰り上げていく

1～10までは10

11～20までは20　ってな感じ

これだと2桁になってしまうので、10で割っておく

これで出荷先と箱番号が一目でわかるような出荷箱番号ができた

できればこれをバーコードフォントでテプラにプリントして、出荷箱毎に張り付ければ、箱を閉じた後でもどこの拠点に行く箱か？中にどのシリアル番号の商品が入っているか?がわかるので、出荷管理には良いと思う

CODE39なら英数字大文字とハイフンも使える

フォントで出す時はチェックデジットが使えないから前後に*(アスタリスク)を入れる事を忘れずに

目視文字付のCODE39ならバーコードの下にデフォのフォントが表示されるから、目視用に別途表示する必要もなし

無料フォントがたくさんあるからググってみるべし

こんな感じでどうでしょう。osamushi

オサムシじゃーなる