| |||||
VLOOKUPとCEILINGを使い、絶対参照と相対参照を駆使して10個で1カウントアップしてナンバリングしていくシステムをつくる関連記事:VLOOKUPの検索値にワイルドカードをつかってみた/Excel応用シチュエーションを考えてみた ある会社の拠点にシリアル番号のついた商品を送る シリアル番号で発送拠点は決まっていて、その指示はExcelで受け取っている 各拠点毎に1から始まる3ケタの通し番号をつけて、出荷の箱単位に任意フォーマットでユニークの番号をつける約束になっている シリアル番号と通し番号の紐付けはコッチでやる事 1つの段ボール箱に10個までしか商品は入れられない 荷受けの検品が楽にできるように、10個を満たしてから次の箱に入れる事 つまり10個未満の箱は各拠点1箱以下でなければならない ここまでがルール Excelを見てみよう ![]() 解説します 受け取ったデータは黄色に着色されたAとBカラム C列以降はこちらで作成した まず拠点毎の管理番号 =COUNTIF($B$2:B2,B2) B列の拠点名をカウントしていくのだが、範囲に注目してほしい 最初のB2は$B$2で絶対参照になっているから固定 範囲の右側B2は相対参照となっているので、下のセルに行くに連れてB3 B4 B5...となっていく これによって拠点名を数えながらカウントアップしていくことができる 002と3ケタ表示されているのはセルの書式設定でユーザー定義で0を選択し、000と入力して3ケタ表示するように設定している 次に出荷箱番号 (以下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 オサムシじゃーなる
by 03musi
| 2012-12-24 21:06
| Excel 応用
| |||||
ファン申請 |
||