納品書ってのは、商売で使う書類やで。
商品を売ったときや、サービスを提供したときに、お客さんに渡すんや。
中身は、商品やサービスの名前や数量、価格なんかが書いてあって、取引の証拠としても使われるねん。
せやから、「ひな型」シートを作りながら、メニューと関数、そして両者の”組み合わせ”のワザがあればいうことなしや。
マウス操作だけで入力できる、実用的なシートを作ってみよう。
最強の納品書「ひな型」を作ってみる
ひな型づくりのポイントは
- 商品名や日付の表示、消費税などの計算を、”自動化”すること。関数をセルに入力して実現する。
- 数値や品番をマウスだけで入力できるようにする。リストやチェックボックス、3択ボタンなどマウスで入力仕掛けをメニューでつくる。
- シートに入力した式や書式を誤って変更されないように「シート保護」というメニューを設定する。
せやな、納品書を作るときに気をつけるポイントやねんけど、まず大事なのは正確さや。ちゃんと商品の名前や数量、値段を書かなあかんねん。それから、ちゃんと日付や会社の情報も入れて、ちゃんとしたフォーマットで作らなあかんで。ミスがあったらアカンねん、ちゃんと確認してから出すんやで。
まずは、ちゃんとお客さんの情報をチェックしとかなあかんで。間違った住所や名前が入ってたら、もめ事の元になるやろ。次に、商品の内容を正確に書かなあかんねん。ほんまに届いたもんがちゃんと書いてあらへんと、トラブルになりかねんで。
最後に、金額や支払い条件ははっきりと書いとかなあかん。ハッキリした数字や条件を示さなあかんねん。これらの注意点を守っとけば、トラブルも防げるし、お客さんも安心やで。
１．関数で計算やデータの表示を”自動化”
納品書を作るとき、「商品名・単価などいちいち手入力するの面倒だ」と思ってやしませんか？
品番で、別表の商品名を「自動抽出」
そこで関数を使って「品番」を入力すると商品一覧表で該当データを検索し、自動表示する仕掛けを作ります。こんなとき便利なのがVLOOKUP関数です。
=VLOOKUP(B8,H8:J16,2,FALSE)
=IF(B8=””,””,VLOOKUP(B8,$H$8:$J$16,2,FALSE))
論理式 真の場合 偽の場合
「品番」欄が空欄 セルを空欄に 「品番」を検索して「商品名」を表示
表の2行目以下にも、VLOOKUP 関数の式をコピーします。参照範囲がずれないよう、セル番地に「＄」をつけて（F4キーを押す）固定（絶対値参照）してからコピーしました。でもエラーが表示されました。
VLOOKUP 関数は、引数「検索値」に指定したセルが空欄だとエラーになるのです。そこで解消するにはIF関数を使い、「検索値のセルが空欄ならセルを空欄に、そうでないならVLOOKUP関数の検索結果を表示する」という式を立てたわけです。
=INT(F14*0.1)
数値
「商品合計」額の10％
=SUM(F14:F15)
消費税計算はINT関数で少数を切り捨て
「消費税」は、合計金額の10％にあたる金額を求め、1円未満を切り捨てる。ここでは小数点以下を切り捨てるINT関数を使って、消費税を求めました。
=VLOOKUP(F14,H20:J22,3,TRUE)
検索値 範囲 列番号 検索の型
「商品」「配送料金表」3列目の 検索値以下で
合計」額 とH列の検索「配送料金」最も近い値を検索
3列目の配送料金を取り出す
購入額に応じて配送料を決める
VLOOKUP 関数の引数「検索の型」を「TRUE」に指定すると、「検索値」以下で最も近い数値を、別表で検索できます。検索する表の左端は数値でなければならないので、H列に検索用の数値を書き出し、「商品合計」額で「配送料金表」（H20～J22セル）を検索して「配送料」を求めます。
=DATE(YEAR(F2),MONTH(F2)+1,10) =TODAY()
年 月 日 今日の日付を表示
同じ年 「１」を足して翌月に 10日
「翌月10日」を自動算出
「発行日」と「お支払期限」を自動表示したいと思います。「発行日」には今日の日付を表示するTODAY関数を入力します。「翌月10日」を求めるには、日付関数で「発行日」を「年」「月」「日」に”分解”して計算します。「月」に１を足せば「翌月」が求められます。「お支払期限」の「年」「月」「日」を求めたら、DATE関数で日付に戻します。
名前を入れると「様」がつく
関数だけでなく、メニューで表示を自動化もできます。たとえば、セルに顧客の氏名を入力すると、末尾に「様」を付けて表示させるのです。これにはセルの「表示形式」を使います。
表示形式とは、セルに入力したデータそのものは変更せず、見た目だけを変える機能です。あらかじめエクセルが用意したもののほか、書式記号を組み合わせて、「ユーザー定義」の表示形式も作れます。「様」のほかに、「台」や「円」などの単位を数値の末尾に表示することもできます。
セルに入力した名前の末尾に「様」を自動表示するには、セルに表示形式を指定します。顧客名を入力したA2せるを右クリックし、「セルの書式設定」を選びます。
「表示形式」タブの「分類」欄で「ユーザー定義」を選び、「種類」欄に「@”様”」と入力します。「@」は入力文字をそのまま表示する書式記号です。「”」で挟んだ文字列は、セルに文字を入力すると、自動表示されます。
「品番」を入力したときだけ「No」を表示
＝IF(B8=””,””,1)
B列に「品番」を入力したときだけA列の「No」を表示するには、IF関数で「品番のセルが空欄なら空欄に、それ以外は番号を表示する」という式を立てます。「品番」を入力すると「No」が自動表示されます。
２．品番入力は「リスト」「三択ボタン」で楽々
入力データがそれほど多くなく、あらかじめ決まっているなら、「リスト」を作って、マウスでえらべるようにすると便利です。リストの作成には「入力規則」メニューを使います。設定画面で「入力値の種類」を「リスト」にし、リストに表示するデータを指定します。
入力用の「リスト」を用意するには「入力規制」メニューを使う
リストを設定したいセル範囲を選択し、データメニューの「入力規則」を選びます。
開いた画面で「設定」タブを開き、まず「入力値の種類」欄の「▼」ボタンを押し、「リスト」を選びます。次に「元の値」欄右端のボタンを押すと、設定画面が小さくなります。「品番」が入力されているセル範囲（H8～H16）セルを選択し、「OK」で確定して設定画面を閉じます。
リストに使うデータは表で用意し、セル範囲で指定します。リストに変更があっても、表を書き換えるだけで対応できるからです。ひな型を他の人も使うなら、品番の入力セルを選択したときに、入力方法のガイド文を自動表示させるとより親切です。
リストを設定したB8～B13セルを選択状態にすると「リストから品番を選ぶ」というガイドが自動表示されるようにします。これも「入力規則」メニューでできます。
「入力規制」をクリックしたら、設定画面を呼び出し、「入力時メッセージ」タブを開きます。「セルの選択時にメッセージを表示する」をチェックし、「メッセージ」欄に表示したい文字列を入力します。「エラーメッセージ」を表示し、再入力させるようにすることもできます。
設定画面を開き、「エラーメッセージ」タブで「不正なデータが入力されたらエラーメッセージを表示する」をチェックします。「該当ページがありません。キャンセルを押してリストから選びなおしてください」等、表示したい文章を入力するだけです。
「スピンボタン」で数量入力
［開発］タブの［コントロール］グループにある［挿入］をクリックします。［開発］タブが表示されていない場合は、［オプション］ダイアログボックスの［リボンのユーザー設定］で設定します。
スピンボタンを作るには、「フォームコントロール」を使います。スピンボタンやチェックボックス、三択ボタンなど、データを入力するにに便利な”部品”が用意されています。作り方は「スピンボタン」を押し、シートでドラグするだけです。
ボタンができたら、次に「押したときの動作」を指定します。入力結果を表示するセルや入力できる値などです。ボタンを右クリックして設定画面を開き、「コントロール」タブで指定します。スピンボタンなら、入力できる最大値と最小値、1回クリックするごとに増える数量などを決めます。
会員割引をマウスでオンオフ
複数の選択肢から１つを選ぶと表示が切り替わる仕掛けも作れます。「会員は配送料金無料」という切り替えにつかいます。会員か非会員かの選択肢を「チェックボックス」で作ります。
「フォーム」ツールバーを使ってチェックボックスを作ったら、右クリックして設定画面を開き、動作を指定します。チェックボックスは、チェックを「オン」にすると「TRUE」、「オフ」にすると「FALSE」をリンク先のセルに表示します。
支払方法の”3択”で期限を切り替え
複数の選択肢から1つを選ぶと表示が切り替わるようにしたいと思います。
たとえば、配送料金「800円」「400円」「0円」の切り替えには、IF 関数を使います。引数「論理式」に「会員割引のチェックがオン」、つまり「E22セルがTRUE」と指定し、これが成り立つときに”無料扱い”とすればいいわけです。
3つ以上の選択肢から1つだけを選ぶなら「オプションボタン」が有効です。オプションボタンを必要な個数だけ作ったら、そのうちの1つを右クリックして設定画面を開き、動作を指定します。それに合わせて「支払期限」が切り替わるようにするのです。
オプションボタンは「フォーム」ツールバーで「オプション」ボタンを押し、マウスでドラッグして作成します。これをコピーして３つのオプションボタンを用意します。英語を日本語名にします。
表示の切り替えにはCHOOSE（チューズ）関数を使います。引数（インデックス）の数値が変わると、それに合わせて表示する値を切り替える関数です。
=CHOOSE（B22,”翌月10日”,”振込用紙に別記”,”配送当日”)
インデックス 値１ 値２ 値３
オプションボタンの クレジット コンビニ決済 代引き
番号 カードの期限 の期限 の期限
３．入力欄以外は変更できないようにする
ひな型がほぼ完成しました。しかしこのままでは、誤って式を削除してしまったり、書式を変更してしまうことがなきにしもあらずです。
そこで、品番や数量などの入力欄以外は入力済みの内容を変更できないように、シートを「保護」します。入力欄だけセルの「ロック」をしておけばシートを保護することができます。チェックボタンとオプションボタンは、リンク先のセルのロックを外しておきます。
赤枠で囲んだところ以外は変更できないようにします。
手順
- １入力ができるようにしたいセルを選択し、「ロック」を外します。
- シートを「保護」して、入力セル以外は変更できないようにします。
シートを保護する前に、入力欄の「ロック」を外します。セルを「Ctrl」キーを押しながら選択します。チェックボックスとオプションボタンは「リンク先」に設定したセルを選択します。
セルを選択した状態で、ホーム→「書式」→「セルの書籍設定」から「保護」タブで「ロック」のチェックを外し空白にします。
ロックを外したら、シートに保護をかけます。
シートに保護をかけて入力欄以外は変更できないようにするのです。
開いた画面で「シートの保護を解除するためのパスワード」は任意です。「シートとロックされたセルの内容を保護する」にチェックが入っていることを確認し、「OK」を押せば完了です。
シートが保護できたかを確認してみます。
保護したセルをダブルクリックすると「変更しょうとしているセルやグラフは保護されているシート上にあります。」というエラーメッセージが表示され、セルを編集状態にできないようになっています。
シートを保護すると、ロックを外した入力可能なセルにだけ「Tab」キーを使って選択枠を次々と移動できます。効率よく入力欄を移動できるのでとても便利です。
あとはひな型をもとにして納品書をつくればよいのですが、面倒くさいのがデータの記録と保存なんですね。ぎょうさん溜まった納品書のデータを、一つひとつ「台帳ファイル」にコピーするのって大変じゃないですか。
４．「データ追跡機能付きテンプレートウィザード」の代わり
以前のExcel では「データ追跡機能付きテンプレートウィザード」という便利な機能があって、納品書の保存時に、台帳ファイルに必要なデータを自動転記できたのです。それが最近のExcel ではできなくなっているのです。Excel もバカなことをするものです。
あれやな、昔のExcelには「データ追跡機能付きテンプレートウィザード」っていうめっちゃ便利な機能があったんやけど、最近のバージョンではそれがなくなってしもたんやな。ほんまに不便やんな。
でも心配せんといてな、代わりにできる方法があるから。ちょっと手間かかるけど、VBA（Visual Basic for Applications）を使って同じようなことができるんやで。
１．まずはVBAを有効にする：
- Excelを開いて、「開発」タブがない場合は、「ファイル」→「オプション」→「リボンのユーザー設定」で「開発」タブにチェックを入れて表示させるんや。
２．VBAでスクリプトを書く
- 「開発」タブから「Visual Basic」をクリックしてVBAエディタを開く。新しいモジュールを追加して、以下のようなスクリプトを書くんや。これは、納品書から台帳にデータを転記する簡単な例やで。
Sub 転記する()
Dim 納品書シート As Worksheet
Dim 台帳シート As Worksheet
Dim 最終行 As Long
' シートの設定
Set 納品書シート = ThisWorkbook.Sheets("納品書")
Set 台帳シート = ThisWorkbook.Sheets("台帳")
' 台帳の最終行を取得
最終行 = 台帳シート.Cells(台帳シート.Rows.Count, "A").End(xlUp).Row + 1 '
データを転記する
台帳シート.Cells(最終行, 1).Value = 納品書シート.Cells(1, 1).Value '例：納品書番号
台帳シート.Cells(最終行, 2).Value = 納品書シート.Cells(2, 1).Value '例：日付
台帳シート.Cells(最終行, 3).Value = 納品書シート.Cells(3, 1).Value '例：顧客名
' 必要なデータを続けて転記する...
End Sub
３．vbaコードをコピーする
４．マクロを実行する：
- スクリプトを書いたら、保存してVBAエディタを閉じる。
- 「開発」タブの「マクロ」ボタンから「転記する」マクロを選んで実行する。
この方法で納品書のデータを台帳に自動的に転記できるんやで。慣れたらそんなに難しくないし、他の作業も自動化できるようになるから、ちょっと勉強してみるとええで。頑張りや！
何なりとお申し付けください。