冒頭

この動画では、エクセルで毎日の売上を入力すると、店舗ごとに『年間』『四半期』『月』ごとに自動集計する表とグラフを作成する方法を紹介します。

完成形の確認

まず完成形を確認しましょう

この表で、毎日の売上日報を店舗ごとに記録します。

• 売上を目標を入力すると目標の達成率が自動で表示されます。
• また、来客数を入力することで客単価も算出されます。

このように日報を入力すると、自動で

• 店舗ごと
• 四半期ごと
• 月ごと

に売上を集計し、グラフでも表示することができます。

更に後半ではこのように縞々のデザインにして見やすさを整えたり、意図しない情報は入力できないように入力制限をかける方法についても紹介しますので、是非最後までご覧ください。

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

項目名

まずは毎日の売上を入力する表の土台となる、項目名を入力していきます。

セルを確定して、右のタブへ移動する場合はTabキーで右に移動することができます。

• 四半期
• 日付
• 曜日
• 店舗名
• 天気
• 売上
• 目標
• 達成率
• 来客数
• 客単価
• 備考

日付

次に日付から入力していきます。この動画では7月決算を想定して、8月から始まり7月で終わるように、入力します。ご自身の決算月に置き換えて作成してみてください。

今回は3店舗の集計を作成していきたいので、1つの日付につき、3行分入力していきます。

まず、日付列の1番上に2020/8/1と入力します。入力できたらそのセルを選択し、Ctrlキーを押しながらCを押してコピーします。次に貼り付けたいセル、つまりその下のセル2つをドラッグで選択します。そのままCtrlキーを押しながらVキーを押すと貼り付けることができます。

次にB5に「=B2+1」と入力します。これで、3つ上の日付にプラス1日して表示することになるで、オートフィル機能を使ったときに、このように1つの日付を3つずつ並べることができます。

これを、1年後の2021年の7月31日まで作るには、今、入力したセルを選択した状態で、Ctrlキーを押しながらCキーを押してコピーします。

次に日付の入力が終わる行まで移動します。今回は365日×3店舗分+1なので、1096行目に移動します。素早く移動するには、マウスのスクロールホイールを押し込んでマウスカーソルでスクロールできる状態にします。この状態で、スクロールしたい方向へマウスカーソルを動かすと素早くスクロールすることができます。

1096行目に移動したら、再び、スクロールホイールを押し込んで、マウスカーソルでスクロールできる状態を解除します。

次にShiftキーを押しながらB1096セルをクリックします。すると、B5セルからB1096セルを選択した状態になります。

この状態のままCtrlキーを押しながらVキーを押すと、2021年7月31日までの日付を入力することができます。

B列のセルを選択した状態で、Ctrlキーを押しながら方向キーの下キーを押すと、このように連続するデータの端まで移動し、入力を確認することができます。

このようにシャープが表示されるのは、列の幅が足りなくて情報を表示し切れない場合に起こります。列名のBとCの間をダブルクリックすると列を自動調整することができます。

曜日

次に日付に応じて自動で曜日が入力されるように設定していきます。

Ctrlキーを押しながら方向キーの上キーを押して1番上のセルに戻りましょう。

日付に応じて自動で曜日が表示されるようにするには、TEXT関数を使ってこのように入力します。

=TEXT(B2,”aaa”)

TEXT関数は、数値に表示形式を適用して、数値を変換することができます。1つ目の引数に変換したい数値を入力し、2つ目の引数ではどのような表示形式を適用するかを指定することができます。

今回は曜日を表示したいので“aaa”と入力しています。

もし、曜日まで表示したい場合はaを4つにすると、曜日まで表示することができます。

他にも、

• ddddと入力すれば、曜日を英語で表示することができますし、
• dddと入力すれば、短縮形で表示することができます。

TEXT関数を入力できたら、そのセルを選択して、右下の小さい四角をダブルクリックすると、他の列の1番下まで伸びてる行、つまり2021年7月31日の行まで自動でコピーされます。日付に応じて曜日が自動で表示されていることが確認できると思います。

店舗名

店舗名の欄は、まず最初の日付にそれぞれの店舗名を入れます。

• 日本橋店
• 飯田橋店
• 水道橋店

店舗名を入れたら、その店舗名をドラッグで複数選択して、右下の四角をダブルクリックすると、1年分の店舗名を入力することができます。

達成率

次に達成率を自動で計算する計算式を入れていきたいので、売上と目標を仮で5つほど入力しておきます。

達成率は売り上げ÷目標なので、=F2/G2と入力します。エクセルでは÷はスラッシュを入力します。これで、F2のセル÷G2のセルを計算するという意味になります。本来、達成率を計算する場合は、売り上げ÷目標に100をかけますが、エクセルの機能でパーセントの単位をつけて×100にして表示する機能があるので、ここでは100をかける必要は無いです。

パーセントをつけて×100にして表示するには、まず対象のセルを選択します。そして、ホームタブの数値グループにある％ボタンをクリックします。すると％がついて数字が×100にされていると思います。

こちらも入力したセルを選択して、右下にある小さい四角をダブルクリックすることで、1年分の数式を入力することができます。

実際に、売り上げと目標を入力すると、自動で、計算結果が表示されます。

まだ入力がない行は割り算ができないためエラーが発生していますが、エラーの非表示方法については、後半で紹介します。

客単価

次に客単価を自動計算する計算式を入れたいので、来客数を5つほど仮で入力しておきます。客単価は売り上げ÷来客数なので、J2セルに、=G2/I2と入力します。これでG2÷I2を計算することができます。

こちらも入力したセルを選択して、右下にある小さい四角をダブルクリックすることで1年分の計算式を入力することができます。

割り切れない場合は小数点以下が表示されますが、後ほどの設定で非表示になるので、今はそのままで大丈夫です。

実際に、来客数を入力すると、自動で、計算結果が表示されます。

こちらもまだ入力がない行はエラーが発生していますが、非表示方法について後半で紹介します。

四半期

次にこのあと、集計表を作る際に、四半期(しはんき)ごとの集計も取りたいので、四半期の項目を埋めていきます。

四半期は関数を使って、日付に応じて自動で入力されるようにします。難しく感じる方は関数を使わず、手動で入力しても大丈夫です。

四半期を関数で自動入力するには、このように入力します。

=”第”&CHOOSE(MONTH(B2),2,3,3,3,4,4,4,1,1,1,2,2)&”四半期”

順番に解説していきます。まず、第は&で最初にくっつけています。四半期も&で最後にくっつけています。

次にMONTHは月の数字を取得する関数です。このA2のセルではB2のセルの月を取得しているので、8になります。

最後にCHOOSE関数は、1つ目の引数の数字に応じて、2つ目以降の引数のいくつめの引数を表示するかを指定します。つまり、1つ目の引数が1なら、2つ目以降の引数の1つ目。1つ目の引数が3なら、2つ目以降の引数の3つ目が表示されます。

つまり、2つ目の引数に1月の四半期、3つ目の引数に2月の四半期・・・というように入力していきます。

例えば、6月決算だったらこのようになります。

=”第”&CHOOSE(MONTH(B2),3,3,3,4,4,4,1,1,1,2,2,2)&”四半期”

これで、B列の月を取得して、その月に応じて第何四半期(だいなにしはんき)かを表示することができます。

入力し終わったら先ほどと同じように、右下の四角をダブルクリックして、1年分の関数を入力します。すると、B列の月に応じて、第何四半期(だいなにしはんき)かが入力されます。

ピボットテーブル作成の準備

次に集計表を作成していくので、計算されているかを確認するために売上を仮で1年分入力していきます。

売上はRANDBETWEEN関数を使うと数字をランダムで入力することができます。難しく感じる方は関数を使わず、手動で入力しても大丈夫です。

例えば=RANDBETWEEN(5000,150000)と入力すると、5千から15万の数字をランダムで入力することができます。

こちらも右下の四角をダブルクリックすることで他の行にコピーされます。

ただ、このままでは、他のセルを操作するたびに、このように数字が変わってしまうので、現在表示されている数字でRANDBETWEEEN関数を上書きすることで、数字を固定していきます。

まず、F8セルをクリックして、RANDBETWEEN関数が入力されているデータの一番上端をクリックします。

その状態で、CtrlキーをShiftキー押しながら、方向キーの下キーを押すと、一番下のデータの端を選択することができます。

この状態で、Ctrlキーを押しながらCを押して、データをコピーします。

そのまま、そのコピーしたデータの上を右クリックして、貼り付けのオプションの値をクリックします。

すると、数字が貼り付けられて、RANDBETWEEN関数が無くなりました。

これで、売上の数字をランダムに1年分入力することができました。

曜日・天気・目標・達成率・来客数・客単価・備考は今回、集計には使わないので、一旦入力しなくても大丈夫です。

保存

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

任意のファイル名と保存場所を指定してください。

ピボットテーブル

次に、集計表を作成していきます。

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

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

設定画面が表示されたら、まず集計の元となるデータの表を選択します。通常は自動で選択されますが、もし適切に選択されていない場合は、表の左上をクリックして、CtrlキーをShiftキーを押しながら方向キーの右キーを押して、下キーを押して、表を選択しなおしてください。

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

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

設定画面の上のボックスには、先ほど表で設定した項目名が表示されています。

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

• 行(軸(分類項目))は左側の項目、
• 列(凡例(はんれい)(系列))は上側の項目、
• 値は表示したい項目、
• フィルターは絞り込みたい項目

を設定します。

今回は、左側に四半期の項目を表示したいので、行のボックスに四半期をドラッグして入れます。

すると左側に四半期が表示されました。

次に売り上げの数字を知りたいので、値のボックスに売り上げの項目をドラッグして入れます。

すると四半期ごとの総売り上げが集計されます。

他にも集計したい情報があれば、値ボックスに追加することで表示することができます。

今回は売上のみ表示したいので、項目名の右の逆三角形をクリックして、フィールドの削除をクリックして削除しておきます。

次に、月毎の集計も見れるようにしたいので、日付をドラッグして行のボックスの四半期の下に入れます。この時、四半期の下に入力することが重要で、ボックス内の並び順は親子関係になっているので、上が親、下が子になるように、入れてください。

行のボックスに日付を入力すると、自動で年と四半期2という項目が追加されています。四半期2という集計はエクセルが自動で、1月-3月を第1四半期として、集計したものになるので、今回は不要です。四半期2の下三角形をクリックし、フィールドの削除をクリックして削除します。

すると、売り上げが四半期ごとに集計されて、年をまたぐ場合は年ごとに分かれた集計表が完成しました。

年のプラスボタンをクリックすると、月ごとの内訳も見ることができます。

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

次に、店舗ごとの内訳を確認したいので、列のボックスに、店舗名の項目をドラッグで入れます。

すると店舗ごとの集計も表示することができました。

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

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

反映させるには、

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

反映されます。

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

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

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

グラフの四隅をドラッグすることでサイズを整えることができます。

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

保存

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

見た目向上

次に、集計の元となる表を見やすくなるようにデザインを整えていきましょう。

下のシート一覧のSheet1をクリックして、集計の元となる表を表示します。

テーブル設定

まず、このように表を見やすく縞々にしていきます。

表のどこかを選択して、Ctrlキーを押しながらTキーで、テーブルを作成します。

テーブルというのは表という意味で、これを設定することで、表のデザインを変更したり、データを並び替えたりできるようになります。

Ctrlキーを押しながらTキーを押すと、このように、テーブルを設定する範囲を選択するメニューが表示されます。基本的にはこのように自動で選択されますが、希望通り選択されていない場合は、表の左上をクリックして、CtrlキーをShiftキーを押しながら方向キーの右キーを押して、下キーを押して、表を選択しなおしてください。

先頭の行は見出しなので「先頭行をテーブルの見出しとして使用する」にチェックが入っていることを確認したら、OKをクリックします。

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

この動画では後ほど、曜日の土曜日を青色に、日曜日を赤色に変えるので、分かりやすくなるように黄色に設定しておきます。

エラー非表示

達成率

次にエラーを非表示にしていきます。エラーを非表示にするには、IFERROR関数を使います。

実際に、H8にIFERROR関数を入力するには、H8セルをダブルクリックかF2で編集モードにして、=と計算式の間にIFERROR(と入力します。次に、計算式の後に、,”エラーが発生しました”)と入力します。

Enterで確定すると、自動でその列全てにIFERROR関数が入ります。

売り上げ÷目標を計算して、エラーが発生したセルには「エラーが発生しました」と表示されるようになっています。

IFERROR関数では1つ目の引数にそのセルに表示したい数式を入れて、2つ目の引数には、その数式がエラーを起こした時に表示する値を入力します。

“エラーが発生しました”ように数字やセル名でない文字を引数に入力する場合はダブルクォーテーションで挟むというルールがあります。これが無いとエラーが発生するので気を付けてください。

今回は、エラー時は何も表示しない、という設定にしたいので、ダブルクォーテーションの中は削除して何もない状態にします。これでダブルクォーテーションの間に何も入っていないので、エラー時は何も表示しないという意味になります。

客単価

客単価も同じように、IFERROR関数を入れていきます。=と計算式の間にIFERROR(と入力し、計算式の後に、カンマ、ダブルクォーテーション2つ、括弧閉じを入力します。

これで客単価のエラーも非表示にすることができました。

曜日

次に、曜日の列で、日曜日を赤色、土曜日を青色に変えていきます。C列を選択して、ホームタブから条件付き書式をクリックします。

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

条件付き書式メニューをクリックしたら、セルの強調表示ルールから、指定の値に等しいをクリックします。

すると、書式を変える条件と、どのような書式に変えるかを設定する画面が表示されるので、まずは日曜日を赤色に変えていきましょう。

左のボックスに「日」と入力します。右のボックスは下矢印をクリックすると書式をどのように変更するかが選択できるので「濃い赤の文字、明るい赤の背景」を選択し、OKをクリックします。

すると日曜日のセルの背景と文字を赤にすることができました。

次に、土曜日を青色に変えていきます。

C列を選択した状態で、ホームタブから条件付き書式、セルの強調表示ルールから、指定の値に等しいをクリックします。

左のボックスに「土」と入力します。右のボックスは下矢印をクリックして、青い文字を選択したいんですが、選択肢にないので、ユーザー設定の書式をクリックしてカスタマイズしていきます。

セルの書式設定メニューが開いたら、まず、文字の色を変えていきます。

フォントタブから色の欄を青に変えます。

次にセルの背景色を水色に変えます。塗りつぶしタブから水色をクリックします。

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

これで土曜日と日曜日の色を変えることができました。

￥マークをつける(日報)

次に、表示形式という機能を使って、売り上げと目標と客単価の数字の前に、見た目上で￥の単位をつけて、3桁ごとにカンマをつけていきます。

まず、F列とG列をドラッグで選択します。次にCtrlキーを押しながらJ列をクリックします。すると、複数の列を選択した状態になります。

この状態で、ホームタブから、数値グループのこの通貨のアイコンをクリックします。すると円マークの単位と3桁ごとにカンマがつきました。

ついでに客単価の小数点以下も省くことができました。

￥マークをつける(ピボットテーブル)

次にピボットテーブルで作成した表の売上欄に円マークと、3桁ごとのカンマをつけていきます。

下のシート一覧からSheet2をクリックして、ピボットテーブルで作成した表を表示します。

ピボットテーブルで作成した表の表示形式を変える場合は、先ほどとは少し設定方法が変わり、このピボットテーブルのフィールド設定メニューを操作していきます。

もし、この設定メニューが表示されていない場合は、ピボットテーブルで作成した表を選択すると表示されます。それでも表示されない場合は、上のピボットテーブル分析タブから、フィールドリストをクリックすると表示することができます。

表示形式を変えたいセルを1つ代表で選択して、右クリックから値フィールドの設定を開きます。

そして表示形式をクリックすると、どのような表示をするかの設定画面が表示されるので、通貨を選択します。

そして、OK、OKをクリックするとこのように円マークと、3桁ごとのカンマをつけることができました。

シート名

次にシート名を変更して並び替える方法を紹介します。

変更したいシート名をダブルクリックするとシート名が変更できます。

ここでは、日報、集計と名前をつけます。

シートを並び替えるには、クリックしながらマウスカーソルを動かす、ドラッグで並び替えることができます。

日報が左にある方が使いやすそうなので、今回は左に配置します。

保存

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

使いやすさ向上

入力規則

数値

次に日報の

• 売り上げ
• 目標
• 来客数

の欄に数字以外を入力できないように設定する方法を紹介します。

入力制限をかけることで、このように数字以外のものが入って、エラーが起こってしまう可能性を減らすことができます。

入力制限をかけるには、まず、入力制限をかけるセルを選択します。

F列からG列をドラッグして選択します。次にCtrlキーを押しながら、I列をクリックして複数選択します。次に項目名は制限をかける必要が無いので、Ctrlキーを押しながら、売上、目標、来客数をクリックして選択から外します。

入力制限をかけるセルを選択できたら、データタブからデータ入力規則の上半分のアイコンの部分をクリックします。

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

データの入力規則メニューを開いたら、入力制限のルールを設定していきます。

今回は数字以外の入力を制限したいので、入力値の種類を整数に設定します。次に、数字の範囲を設定することができます。

• 売り上げ
• 目標
• 来客数

の3つはマイナスになることは無いので、データを次の値以上、最小値を0に設定し、0以上の数字のみ入力できるように設定します。

この状態でOKをクリックします。すると0以上の数字以外を入力しようとすると、このようにエラーを表示して、入力できないように制限をかけることができました。

リスト

次に天気の欄を

• 晴れ
• 曇り
• 雨

以外は入力できないように設定していきます。

E列を選択したら、同じく、天気のセルはCtrlキーを押しながらクリックして省きます。

データタブからデータの入力規則を開きます。

次は、

• 晴れ
• 曇り
• 雨

以外の入力を制限したいので、入力値の種類はリストを選択し、元の値の欄に、晴れ,(カンマ)曇り,(カンマ)雨と入力します。

複数の入力を許可するには、このようにカンマで区切ります。

また、ドロップダウンリストから選択するにチェックを入れておくと、このように文字を直接入力しなくても、選択肢から入力できるようになります。

この状態で、OKをクリックします。

すると、このように選択肢から入力できるようになり、この選択肢以外の入力は出来ないように制限をかけることができます。

保護

次に、編集する必要が無いセルをエクセルの保護機能を使って、編集できないように設定していきます。

編集する必要のないセルを保護しておくことで、数式を編集してしまったり、削除してしまったりして、作り直す手間などを減らすことができます。

この動画では、

• 項目名
• 四半期
• 日付
• 曜日
• 店舗名
• 達成率
• 客単価

を保護していきます。

特定のセルを保護するには、大きく2つの手順を踏みます。

まず保護したいセルにロックという設定をします。その後、保護を設定をすると、ロックをかけたセルが保護されます。逆にロックをかけていないセルは保護されていないので、編集できる状態のままになります。

なのでまずは編集できないように設定したいセルにロックをかけていきます。初期状態では全てのセルにロックがかかってるので、特定のセルのみロックする場合は、一度全てのセルのロックを解除してから、保護するセルのみロックの設定を行います。

全てのセルのロックを解除するには、セルの一番左上をクリックし、全てのセルを選択した状態にします。次にCtrlキーを押しながら1を押して、セルの書式設定を開きます。保護のタブから、ロックのチェックを外し、OKをクリックします。

これで、全てのセルのロックが解除されました。次に保護したいセルのみロックをかけていきます。

まずは保護したいセルを選択していきます。1行目をクリックして、次にCtrlキーを押しながらA~Dをドラッグで複数列を選択します。次にCtrlキーを押しながらHとJをクリックして選択します。

ロックしたいセルが選択されたら、Ctrlキーを押しながら1を押して、セルの書式設定を開きます。保護のタブを開き、ロックの項目にチェックを入れてOKをクリックします。これで、保護したいセルがロック状態になりました。

ロックをかけただけではまだ編集できる状態なので、次にロックをかけたセルに保護をかけます。上の校閲タブのシートの保護をクリックします。すると、保護に関する設定ウィンドウが表示されます。

1つ目の項目では保護を解除する際のパスワードを設定することができます。パスワードを設定しなくても、保護の解除ボタンを押さない限り編集はできないので、基本的にはパスワードを設定する必要は無いかと思います。もし、会社のファイルにパスワードを設定する場合は、万が一の際に会社に迷惑をかけることのないように、上司やシステム管理者などにパスワードを共有しておくようにしましょう。今回はパスワードは設定しないので空白のまま進めます。

次に「シートとロックされたセルの内容を保護する」にチェックが入ってることを確認します。これにチェックが入ってることでセルを保護することができます。

次に、保護状態でも、許可する操作を選択します。基本的に全て外れていても大丈夫なんですが、選択ができないと使いにくくなってしまうので、選択を可能にするこの2つの項目のみチェックを入れた状態で、OKをクリックします。

これで、セルを保護することができました。保護したセルを編集するとこのようにエラーが表示されます。

もし保護を解除したい場合は、校閲タブからシート保護の解除をクリックすると解除することができます。

これで店舗ごとに『年間』『四半期』『月』ごとに自動集計する表とグラフの完成です。

仮データの削除

仮で入れた天気と売上と来客数の数字を削除しておきましょう。

削除したいデータの端をクリックして、CtrlキーとShiftキーを押しながら、データのもう片端の方向の方向キー、つまりここでは下を押すと連続するデータの端から端まで選択することができるので、この状態で、Deleteキーを押します。

目標は任意の数値を入れておいてください。

保存

完成したら、Ctrlキーを押しながらSを押してファイルを上書き保存しておきましょう。

便利なテクニック Ctrl+↓

最後に便利なテクニックを紹介します。

このようにデータが増えて来た際に、一瞬で次の入力欄に飛ぶには、

• 天気
• 売り上げ
• 目標

などの毎日入力する列のセルを選択し、Ctrlキーを押しながら下キーを押すと、連続するデータの端まで移動することができます。このテクニックを使って素早く入力するようにしましょう。

まとめ

以上がエクセルで毎日の売上を入力すると、店舗ごとに『年間』『四半期』『月』ごとに自動集計する表とグラフを作成する方法でした。