冒頭

この動画では、

エクセルで

毎日の売上を入力すると、

『年間』『四半期』『月』ごとに自動集計表とグラフを作成する方法を紹介します。

また、

• 目標を設定してその達成率を自動で計算して表示したり
• 来客数を入れると自動で客単価を計算して表示する方法についても紹介していきます。

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

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

項目名

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

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

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

日付

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

まず、日付列の1番上に2020/8/1と入力します。

そしてそのセルを選択した状態で、ホームタブのフィルをクリックして、連続データの作成をクリックします。

すると連続するデータを入力するメニューが出てきます。

範囲は縦と横どちらに入力するかを選択します。今回は縦に日付を入力していきたいので、列を選択します。

種類は日付データなので、日付を選択します。

日付を1日ずつ増やしていきたいので、増加単位は日、増加値は1と入力します。これで、日付を1日ずつ増やすことができます。

停止値は連続データをどこで止めるかを入力します。今回は、1年分の2021年7月31日で止めたいので、2021/7/31と入力します。

これでOKをクリックします。すると1年分の日付を入力することができます。

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

曜日

次に曜日を入力します。2020/08/01は土曜日なので土と入力し、確定します。

次に今、入力した曜日セルを選択して、選択セルの右下にある小さい四角をダブルクリックすると、1年分の曜日が入力されます。

達成率

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

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

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

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

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

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

客単価

次に客単価を自動計算する計算式を入れたいので、来客数を仮で入力しておきます。客単価は売り上げ÷来客数なので、I2セルに、=E2/H2と入力します。これでE2÷H2の計算結果が表示されます。

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

来客数を入れると客単価が表示されます。

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

四半期

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

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

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

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

順番に解説していきます。まず、第と四半期は頭に第を付けて、後ろに四半期を付けるという意味になります。

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

最後にCHOOSE関数は、1つ目の引数の数字に応じて、2つ目以降の引数のいくつめの引数を表示するかを指定します。つまり、ここが1なら、2つ目以降の引数の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列の月に応じて、第何四半期(だいなにしはんき)かが入力されます。

ピボットテーブル

次に集計表を作っていくんですが、計算されているかを確認するために売上と目標と来客数を2カ月分くらい仮で入れておいてください。

この動画では完成例を見せるために1年分入力します。

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

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

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

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

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

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

• 行は左側の項目名、
• 列は上側の項目名、
• 値は表示したい項目名、
• フィルターは絞り込みたい項目名

を表示します。

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

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

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

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

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

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

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

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

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

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

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

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

反映させるには、

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

反映されます。

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

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

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

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

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

保存

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

見た目向上

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

テーブル設定

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

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

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

Ctrlキーを押しながらTキーを押すと、このように、テーブルを設定する範囲を選択するメニューが表示されます。基本的にはこのように自動で選択されますが、希望通り選択されていない場合は、ドラッグで表を選択し直してください。ここでは正常に選択されているので、そのままOKをクリックします。

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

この動画では黄色に変えます。

エラー非表示

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

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

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

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

さきほどのテーブルの設定をしていない場合は、その列の全てにIFERROR関数が入らないので、その際は、セルの右下の四角をダブルクリックしたり、ドラッグで他の行にも反映させてください。

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

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

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

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

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

曜日

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

まず、E列を選択し、Ctrlキーを押しながら、F列と、I列をクリックします。すると、複数の列を選択した状態になります。

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

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

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

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

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

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

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

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

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

シート名

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

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

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

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

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

使いやすさ向上

数値

入力規則

次に

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

の欄に数字以外を入力できないように設定していきます。

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

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

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

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

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

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

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

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

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

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

リスト

次に天気の欄を

• 晴れ
• 曇り
• 雨

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

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

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

次は、

• 晴れ
• 曇り
• 雨

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

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

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

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

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

保護

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

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

この動画では、

• 項目名の行
• 四半期の列
• 日付の列
• 曜日の列
• 達成率の列
• 客単価の列

を保護していきます。

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

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

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

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

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

まずロックをかけるセルを選択します。今回は、

• 1行
• A列
• B列
• C列
• G列
• I列

の列を選択します。

まず1行目をクリックして選択します。次にCtrlキーを押しながらA~Cをドラッグで複数列を選択します。次にCtrlキーを押しながらGとIをクリックして選択します。

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

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

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

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

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

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

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

保存

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

便利なテクニック Ctrl+↓

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

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

• 天気
• 売り上げ
• 目標

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

まとめ

以上が毎日の売上を入力すると、

『年間』『四半期』『月』ごとに自動集計表とグラフを作成する方法でした。

学べること

• 日付のオートフィル
• 列幅の調整
• 曜日のオートフィル
• 計算式(割り算)
• 計算式のオートフィル
• 表示形式(%)
• 列の挿入
• CHOOSE関数
• MONTH関数
• ピボットテーブル
• ピボットテーブルのグラフ
• IFERROR関数
• Ctrl + X
• Ctrl + V
• テーブル
• 条件付き書式
• 表示形式(￥)
• 表示形式(ピボットテーブル)
• シート名の変更
• シート名の並び替え
• ウィンドウ枠の固定
• 入力規則(整数)
• 入力規則(リスト)
• 保護
• Ctrl + 方向キー