はじめに

この動画では、エクセルで受注・売上管理表を作成して、請求漏れ・入金漏れを防いで、月ごと・担当ごとに集計する方法を紹介します。

まず完成形を確認します。

この表には受注情報を入力します。具体的には、受注日、営業担当者名、取引先名、受注金額、請求締切日、入金締切日を入力します。

すると、請求の欄が黄色くなります。請求の欄が黄色いことで請求をしないといけない、ということがわかります。請求が終わったら済を入力することで、黄色くなくなります。このようにして、請求漏れを防ぐことができます。

また、請求をしないまま請求締切日が過ぎるとその日付が赤くなるので、請求日が過ぎてしまった場合に気づくことができます。

次に、入金が確認できたら入金額を入力します。入金締切日を過ぎても入金が無い場合や、入金額に不足がある場合は、不足金額が赤くなるので、入金漏れを防ぐことができます。

また、請求漏れや入金漏れの情報のみ絞り込むこともできるので、情報が多い場合も安心です。

更に動画の後半では、自動で月ごとや担当ごとの集計表を作成して、グラフを表示する方法や、意図しない情報は入力できないように制限をかけて、エラーが起こる可能性を減らす方法についても紹介します。

項目名

それでは作成していきましょう。

まずは、受注・売上管理表の土台となる、項目名を入力していきます。

右へ情報を入力していく際は、Tabキーを押すと、セルの入力を確定して選択セルを右に移動することができるので、素早く入力することができます。

列幅の調整

列幅が足りず情報が表示しきれていない場合はその列名の右側の境目をダブルクリックすると、情報量に合わせて自動で、列幅を調整することができます。

仮

次に、各挙動を確認するために、仮で情報を入れておきます。

仮の情報は後に作成する、月ごとの集計が正しいかを確認するためにも、受注日は2ヶ月分入力してください。

(スキップ)

ここでは、2020年8月と9月が入力されています。

保存

ここで一旦Ctrlキーを押しながらSキーを押して、ファイルを保存しておきましょう。

不足

次に、不足金額を自動で算出できるように設定します。

不足金額は受注金額-入金額なので、

J2セルに、

=E2-I2

と入力します。Enterで確定したら、そのセルを選択した状態で、セルの右下の小さい四角をダブルクリックすることで、他の行にも適用します。

これで、不足金額を表示することができました。

次に、入金締切日が過ぎていない行は不足金額を表示する必要が無いので、入金締切日が過ぎていない行は”入金締切日前”と表示するように設定していきます。

今、入力した数式をドラッグで選択して、Ctrlキーを押しながらXで切り取ります。

そのまま、IF(H2>TODAY(),”入金締切日前”,

と入力して、Ctrlキーを押しながらVキーで先程切り取った数式を貼り付けます。

そのまま、

)

と入力します。

=IF(H2>TODAY(),”入金締切日前”,E2-I2)

IF関数では、

1つ目の引数に、条件

2つ目の引数に、その条件を満たした際の処理

3つ目の引数に、その条件を満たさなかった際の処理を入力します。

条件はH2セルが今日より大きい場合、つまり、入金締切日になっていない場合、という意味になります。

つまり、入金締切日になっていない場合は、”入金締切日前”と表示する、そうでなければE2-I2、つまり不足金額を表示する、という意味になります。

Enterで確定したら、そのセルを選択して、セルの右下の小さい四角をダブルクリックすることで、他の行にも適用します。

これで、入金締切日までは、”入金締切日前”と表示して、入金締切日が過ぎた場合は不足金額を表示することができるようになりました。

列の境目をダブルクリックして列幅を調整しておきます。

保存

ここで一旦Ctrlキーを押しながらSキーを押して、ファイルを保存しておきましょう。

請求

次に、請求に済が入っていない場合は黄色くなるように設定します。

まずは、設定するセルを選択します。今回は請求の列に済が入っていない場合に黄色くなるように設定したいので、G列を選択します。次に、請求という文字は黄色くならなくて良いので、Ctrlキーを押しながら請求が入ったセルをクリックして、選択を解除します。

次に上のホームタブから条件付き書式をクリックします。

条件付き書式メニューでは、特定の条件に応じて、書式を変更することができます。エクセルにおける書式というのはセルや文字の見た目のことです。つまり、指定した条件に応じて、セルや文字の見た目を変えることができます。

条件付き書式メニューをクリックしたら、新しいルールをクリックします。ここで、書式を変える条件と、どのように書式を変えるかを設定していきます。

数式を使用して、書式設定するセルを決定をクリックします。

まず、書式を変える条件を入力します。

ここでは、請求締切日が入力されている場合かつ、請求の欄が済以外の場合、という条件を設定します。

=AND(NOT(ISBLANK(F2)),G2<>”済”)

AND関数では、引数に入力した条件を全て満たす場合、

という条件を作成することができる関数です。

ここでは、

• F2セルが空白でない場合、つまり請求締切日が入力されている場合

という条件と、

• G2セルが”済”以外の場合、つまり請求の欄が”済”以外の場合

という2つの条件を入力しているので、その2つの条件を満たす場合のみ、書式を変更します。

今回指定したF2セルとG2セルは相対参照なので、

• G2セルは、
  • F2セルが空白ではない場合かつ、
  • G2セルが”済”以外の場合に書式を変更
• G3セルは、
  • F3セルが空白ではない場合かつ、
  • G3セルが”済”以外の場合に書式を変更
• G4セルは、
  • F4セルが空白ではない場合かつ、
  • G4セルが”済”以外の場合に書式を変更

という意味になります。

相対参照とは何かについて知りたい方は、他の動画で紹介してるので参考にしてみてください。

次に、条件を満たした場合に、どのように書式を変えるかを設定していきます。

ここでは、背景色を黄色に変える設定をします。書式をクリックして、塗りつぶしタブから、黄色を選択します。選択できたら、OK、OKをクリックします。

これで、請求に済が入っていない場合は黄色くなるように設定することができました。

請求締切日

次に、請求をしないまま請求締切日が過ぎた場合に、その日付が赤色になるように設定していきます。

請求締切日の列を選択したら、Ctrlキーを押しながら請求締切日の文字が入ったセルをクリックして、選択を解除します。次に、上のホームタブから条件付き書式をクリックして、新しいルールをクリックします。

次に、数式を使用して、書式設定をするセルを決定をクリックします。

今回は、

• 請求締切日を過ぎてる場合かつ
• 請求の欄に済みが入っていない場合かつ
• 請求締切日が入力されている場合

という条件を設定したいので、

次の式を入力します。

=AND(F2<TODAY(),G2<>”済”,NOT(ISBLANK(F2)))

AND関数は、先程と同じく、引数に入力した条件を全て満たす場合、

という条件を作成することができる関数です。

ここでは、

• F2セルが今日より小さい場合、つまり請求締切日を過ぎてる場合かつ
• G2セルに済が入っていない場合、つまり請求が終わっていない場合、かつ
• F2セルが空白でない場合、つまり請求締切日が入力されている場合

という3つの条件を設定しているので、その3つの条件を満たす場合のみ、書式を変更します。

指定したF2セルとG2セルは先ほどと同じく相対参照で入力しています。

次に、条件を満たした場合の書式を設定します。書式をクリックして、塗りつぶしタブから赤色を選択して、OK、OKをクリックします。

これで、請求締切日が過ぎた場合に、その日付が赤色になるように設定することができました。

条件付き書式の閲覧・編集・削除

現在設定している条件付き書式を閲覧・編集・削除したい場合は、条件付き書式が設定されているセルを選択した状態で、上のホームタブから、条件付き書式、ルールの管理をクリックすると、現在設定している条件付き書式を表示して、編集したり削除することができます。

そのシートの全ての条件付き書式を表示したい場合は、書式ルールの表示の欄をこのワークシートの変更すると、そのシートの全ての条件付き書式を表示することができます。

不足金額

次に、入金締切日を過ぎても入金が無い場合や、入金額に不足がある場合は、不足金額が赤くなるように設定していきます。

不足金額の列を選択したら、Ctrlキーを押しながら不足金額の文字が入ったセルをクリックして選択を解除します。次に、上のホームタブから条件付き書式をクリックして、新しいルールをクリックします。

次に、数式を使用して、書式設定をするセルを決定をクリックします。

不足金額は、入金締切日までは、”入金締切日前”と表示して、入金締切日が過ぎた場合は不足金額を表示するように設定していますので、

• 不足金額が数字かつ
• 不足金額が0より大きい場合

という条件を設定します。

=AND(ISNUMBER(J2),J2>0)

AND関数は、先程と同じく、引数に入力した条件を全て満たす場合、

という条件を作成することができる関数です。

ここでは、

• J2セルが数字の場合、つまり入金締切日が過ぎていて、不足金額の欄に数字が表示されている場合かつ
• J2セルの数字が0より大きい場合

という2つの条件を設定しているので、その2つの条件を満たす場合のみ、書式を変更します。

指定したJ2セルは先ほどと同じく相対参照で入力しています。

次に、条件を満たした場合の書式を設定します。書式をクリックして、塗りつぶしタブから赤色を選択して、OK、OKをクリックします。

これで、入金締切日を過ぎても入金が無い場合や、入金額に不足がある場合は、不足金額が赤くなるように設定することができました。

保存

ここで一旦Ctrlキーを押しながらSキーを押して、ファイルを保存しておきましょう。

集計

次にこの表を元に月ごとや担当ごとの集計表を作成していきます。

集計元となる表のどこかのセルを選択した状態で、上の挿入タブから、ピボットテーブルをクリックします。ピボットテーブルとは簡単に集計表を作ることができる機能です。

ピボットテーブルをクリックするとピボットテーブル作成の設定画面が表示されます。

設定画面が表示されたら、まず集計の元となるデータの表を選択します。通常は自動で選択されますが、もし適切に選択されていない場合は、ドラッグで選択し直してください。

次に、ピボットテーブルを作成するシートを選択します。今回は集計元となる表のシートとは分けて集計したいので、新規ワークシートを選択した状態で、OKをクリックします。

すると、新しいシートが開いて、集計表の設定画面が表示されます。

設定画面を簡単に紹介すると、上のボックスの項目名を、下のボックスにこのようにドラッグして使います。

• 行のボックスには左側に表示したい項目名、
• 列のボックスには上側に表示したい項目名、
• 値のボックスには算出したい項目の項目名、
• フィルターのボックスには絞り込みをしたい項目名

を入力します。

今回は、左側に月の項目を表示したいので、受注日をドラッグして行のボックスに入れます。すると、左側に、月の項目が表示されました。

次に、担当ごとの集計をしたいので、担当をドラッグして列のボックスに入れます。

最後に、受注金額を集計したいので、受注金額をドラッグして値のボックスに入れます。

これで、月ごとや担当ごとに売上を集計することができました。

月の横のプラスボタンをクリックすると、日ごとの集計を確認することもできます。

マイナスボタンで畳むことができます。

ここで、ピボットテーブルの注意点をお伝えしておきます。

ピボットテーブルで作成した集計表は、元となるデータが更新されても自動で反映されません。

反映させるには、

• ピボットテーブルで作成した集計表を選択して、ピボットテーブル分析から更新をクリックするか
• ファイルを保存して閉じて、ファイルを開き治すことで

反映させることができます。

ピボットテーブルのグラフ

次に、この情報を元にグラフを作ります。表のどこかを選択した状態で、ピボットテーブル分析タブからピボットグラフをクリックします。

するとどのようなグラフを作成するかの選択画面が表示されるので、今回は縦棒グラフの中の、集合縦棒グラフを選択してOKをクリックします。するとグラフを作成することができました。

グラフのドラッグして移動したり、四隅をドラッグすることでサイズを変えることができます。

また、右下の、プラスクリックすることで、表と連動して、内訳を表示することができます。

マイナスをクリックすることで非表示になります。

シート名の変更とシートの並び替え

次に、シート名を分かりやすいように変更しておきます。

シートのタブをダブルクリックすることで変更することができます。

今回は

• 集計と
• 入力

という名前をつけます。

シートのタブをドラッグで並び替えることもできます。

入力シートが左にある方が使いやすいそうなので、左にくるようにしておきます。

保存

ここで一旦Ctrlキーを押しながらSキーを押して、ファイルを保存しておきましょう。

表示形式

次に、表が見やすくなるようにデザインを整えていきます。

まずは、通貨を表す数字に円記号と3桁ごとのカンマを付けます。

今回、通貨を表す項目は3つあるので、まず1つ目の列をクリックで選択したら、2つ目以降はCtrlキーを押しながら列をクリックして、追加します。

選択できたら、Ctrlキーを押しながら1キーを押します。

するとセルの書式設定というメニューが開くので、表示形式のタブを設定していきます。表示形式機能ではその名の通り表示する形式を設定することができます。今回は通貨の表示形式を設定したいので、通貨を選択します。

小数点以下は表示する必要が無いので0

記号は円記号が選択されていることを確認します。

負の数の表示形式では、数値がマイナスの場合の表示形式を設定することができます。

過払いがあった場合、不足金額がマイナスになるので

-を付けて赤文字になるように設定しておくと、

過払いに気づくことができます。

設定が終わったらOKをクリックします。

これで、通貨を表す数字に円記号と3桁ごとのカンマを付けることができました。

表示形式(ピボットテーブル)

次に、集計表の通貨を表す数字に円記号と3桁ごとのカンマを付けます。

ピボットテーブルで作成した表に表示形式を設定する場合は、少し設定方法が異なります。

表示形式を変更したいセルを1つ選択して、右クリックします。メニューが開いたら値フィールドの設定を開きます。

値フィールドの設定メニューが開いたら、左下の表示形式をクリックします。ここからは先程と同じです。通貨を選択して、各種設定を行ったらOK、OKをクリックします。すると、集計表の通貨を表す数字に円記号と3桁ごとのカンマを付けることができました。

テーブル

次に、入力表にテーブルを設定します。

テーブルというのは表という意味で、これを設定することで、表に縞々のデザインを適用したり、絞り込みや並び替えができるようになります。

テーブルを設定したい表のどこかをクリックして選択したら、Ctrlキーを押しながらTキーを押します。するとテーブル作成メニューが表示されます。基本的にはこのように自動で選択されますが、希望通り選択されていない場合は、ドラッグで表を選択し直してください。先頭行は見出しなので、先頭行をテーブルの見出しとして使用するにチェックが入っていることを確認したら、OKをクリックします。

これでテーブルを設定することができました。

データを絞り込んだり並び替えたりしたい場合は項目名の横の下三角形からデータを絞り込んだり並び替えたりすることができます。

もし表のデザインを変更したい場合は、テーブルデザインタブのテーブルスタイルのオプションとテーブルスタイルのグループから変更することができます。

この動画ではそのままのデザインで進めます。

寄せ

次に、セル内の文字を寄せる方法を紹介します。

この動画では日付データを中央寄せにしますが、お好みで設定してください。

日付データは複数あるので、1つ目の列をクリックした後、2つ目以降はCtrlキーを押しながらクリックして選択します。

次に、項目名は中央寄せにはしたくないので、Ctrlキーを押しながら項目名をクリックして、選択を解除します。

セルを選択できたら、上のホームタブから配置グループで寄せる方向を決めることができます。

ここでは中央寄せボタンをクリックします。

すると、選択されているセル内の文字を中央寄せにすることができました。

保存

ここで一旦Ctrlキーを押しながらSキーを押して、ファイルを保存しておきましょう。

入力制限(日付)

次に、日付データの入力欄に日付データ以外は入力できないように設定していきます。日付データ以外は入力できないように設定することで、

日付データを元に設定している条件分岐や条件付き書式、また集計などでエラーが起こる可能性を減らすことができます。

入力制限をかけるには、まず入力制限をかけたいセルを選択します。今回、日付データは複数あるので、先程と同じように1つ目の列をクリックした後、2つ目以降はCtrlキーを押しながらクリックして選択します。

次に、項目名には入力制限をかける必要が無いので、Ctrlキーを押しながら項目名をクリックして、選択を解除します。

入力制限をかけたいセルを選択できたら、上のデータタブからデータの入力規則をクリックします。

データの入力規則は、データの入力に制限をかけることができるメニューです。

データの入力規則メニューを開いたら、設定タブから入力値の種類を日付に設定します。

これで、日付データ以外は入力できないように設定することができます。

次に、入力できる日付の範囲を設定する必要があるので、データを次の値以上に設定し、開始日を入力する可能性がある日付の1番過去の日付を入力してください。

ここでは、2020/8/1と入力します。

設定できたら、OKをクリックします。

これで、日付以外のデータを入力しようとすると、エラーメッセージが表示されて、入力できないように設定することができました。

入力制限(数字のみ)

次に、Noの欄と金額を入力する欄に数字以外は入力できないように設定します。

Noの欄に数字以外は入力できないように設定することで、並び替えエラーが起こる可能性を減らすことができます。

金額を入力する欄に数字以外は入力できないように設定することで、不足金額の算出や集計の計算でエラーが起こる可能性を減らすことができます。

特に金額欄は、表示上は円記号が付いているので、仕様が分かっていない人が入力しようとすると、数字だけでなく円記号も入力してしまう可能性が高いので、数字以外は入力できないように設定しておくと安心です。

入力制限をかけるには、先ほどと同じように、まず入力制限をかけたいセルを選択します。先ほどと同じように制限をかけたい項目が複数あるので、1つ目の列をクリックした後、2つ目以降はCtrlキーを押しながらクリックして選択します。

次に、項目名には入力制限をかける必要が無いので、Ctrlキーを押しながら項目名をクリックして、選択を解除します。

入力制限をかけたいセルを選択できたら、上のデータタブからデータの入力規則をクリックします。

データの入力規則メニューを開いたら、設定タブから入力値の種類を整数に設定します。

これで、整数の数字以外は入力できないように設定することができます。

次に、入力できる数字の範囲を設定する必要があるので、データを次の値以上に設定し、最小値を0に設定します。

これで0以上の数字のみ入力できる状態になります。

設定できたら、OKをクリックします。

これで、数字以外のデータを入力しようとすると、エラーメッセージが表示されて、入力できないように設定することができました。

入力制限(リスト)

次に、担当欄に担当者名以外は入力できないように設定します。

担当者名の入力で漢字の変換ミスなどがあると、集計表で正しく集計されないので、入力制限をかけることで、集計エラーが起こる可能性を減らすことができます。

また、このように選択肢から入力できるようにする方法も紹介します。

担当者名のように、複数の選択肢以外の入力を制限する場合は、その選択肢のリストを作成しておくと、後から追加や編集・削除ができて便利です。

ここでは別のシートに担当者リストを作成します。左下のシートタブの横のプラスボタンをクリックして新しいシートを作成します。

シートのタブをダブルクリックしてシート名を担当者リストと変更しておきます。こちらは編集頻度が低いと思うので、ドラッグして一番右に移動しておきます。

A列に担当者名を入力します。

次に、先ほどと同じように、入力制限をかけたいセルを選択します。

項目名には入力制限をかける必要が無いので、Ctrlキーを押しながら項目名をクリックして、選択を解除します。

入力制限をかけたいセルを選択できたら、上のデータタブからデータの入力規則をクリックします。

データの入力規則メニューを開いたら、設定タブから入力値の種類をリストに設定します。

次に、元の値のボックスの中をクリックしてカーソルを入れたら、担当者リストのシートをクリックして、先程入力した担当者リストの列を選択します。

すると、元の値のボックスに

=担当者リスト!$A:$A

数式が入力されます。これは担当者リストシートのA列という意味になります。

これで、担当者リストシートのA列にある文字以外は入力できないように設定することができます。

ドロップダウンリストから選択するにチェックが入っていると、先程紹介したように選択肢から入力できるので便利です。

設定できたら、OKをクリックします。

これで、担当者リストにある名前以外を入力しようとすると、エラーメッセージが表示されて、入力できないように設定することができました。

セルの右にある下三角形をクリックすると、選択肢から選んで入力することもできます。

キーボードで入力したい場合は、Altキーを押しながら下キーを押すことで選択肢を表示することができるので、そのままカーソルキーで選択し、Enterで確定して入力することができます。

保存

ここでCtrlキーを押しながらSキーを押して、ファイルを保存しておきましょう。

おわりに

以上がエクセルで受注・売上管理表を作成して、請求漏れ・入金漏れを防いで、月ごと・担当ごとに集計する方法でした。