Excelで「売上集計表」を作ってみよう(No.3/3)

Excel

» 前回の内容(「売上集計表」No.2/3 )

新人店長「よし、もう少しで完成だ!これで上司をギャフンと言わせてやるぞ!」

今回の内容

  1. 「前年比」を計算しよう
  2. 値を「%表示」にしてみよう
  3. 「COUNTA関数」を使ってみよう
  4. 「INDIRECT関数」を使ってみよう
  5. 「INDEX関数」を使ってみよう
  6. 罫線を変更して表を見やすくしよう
  7. 「条件付き書式」を使ってみよう
  8. セルを「ロック」しよう
  9. シート名の変更やシートの削除・複製をしよう

完成形

完成形
この章で売上集計表を完成させるよ!!!

第3章『売上集計表を仕上げよう!』

H列(前年売上の列)の作成

H列に数値(とりあえず適当でOK)を入力して、H6〜H36セルをB2セルを塗った「薄い黄色」で塗りつぶそう。
前年売上

H37セルにはH6〜H36セルの合計を表示させよう。

H37セル
=SUM(H6:H36)

H37セルの単位を『千円』にしよう。
H37セルを右クリックして、”セルの書式設定”→”ユーザー定義”→『♯,♯♯0”千円”』を選択して”OK”をクリック。

文字数が多くて「♯♯♯♯♯」になってしまったので、右クリックして”セルの書式設定”→”配置”→”文字の制御”の”縮小して全体を表示する”にチェックを入れて”OK”をクリック。

前年売上合計

1. 「前年比」の計算

I列には前年比を表示させるよ!

前年比=今年の(累計)売上÷前年の(累計)売上×100
Excelでの割り算は「/」(半角入力で「め」)を使うよ。
G15セル÷H15セルの場合は=G15/H15だ!

この表の前年売上の欄には累計の列が無いので、割り算の数式にSUM関数も組み込むよ!

I15セル
  今年の累計売上:G15
  前年の累計売上:SUM(H6:H15)
=G15/SUM(H6:H15)
  ※前年比の式の「×100」は今は無視してOKだよ
前年比

1日〜31日にコピーするのでH6は絶対参照にしておこう。

I15セル
 『=G15/SUM(H6:H15)』
    ⬇︎
 =G15/SUM($H$6:H15)

また当日の売上が入っていない所は空白にしたいのでIF関数を使って更に修正しよう。

I15セル
 『=G15/SUM($H$6:H15)』
    ⬇︎
 =IF(G15=””,””,G15/SUM($H$6:H15))
前年比2

2. %表示

出てきた値を%表示にするのは簡単だ!
”パーセントスタイル”をクリックするだけ!

また小数点第1位まで表示させたいので”小数点以下の表示桁数を増やす”をクリックしよう。

前年比3

I15セルが完成したのでI6〜I36セルにコピーするよ。

I15セルの右下にカーソルを合わせ➕の形になったら、I6セルまでドラッグしてコピーしよう。またI6〜I15セルの適当なセルの右下にカーソルを合わせ➕の形になったら、I36セルまでドラッグしてコピーしよう。

前年比コピー

3. COUNTA(カウントエー)関数

I37セルに数式を入れるよ!

I37セルには今日が2月10日だったら2月10日時点の前年比、2月20日だったら2月20日時点の前年比のように、今分かっている最新の前年比を表示させたいんだけどどうしたらいいかな?

まずは当日売上(F列)のデータが入力されている一番下の行を探してみよう。COUNTA関数は指定したセル範囲の中にデータが入力されているセルの個数を数えてくれる関数だよ。

I37セル
 =COUNTA(F6:F36)
 ➡︎10

1日〜10日にデータが入っているので「10」になったね。10日は15行目なので上記の数式に5を足せば、データが入力されている一番下の行を表すことが出来るね。

I37セル
 『=COUNTA(F6:F36)』
    ⬇︎
 =COUNTA(F6:F36)+5

4. INDIRECT(インダイレクト)関数

F6〜F36セルに入力されている一番下の行がどこか分かったので、次は列と行を指定して最新の前年比をI37セルに表示させよう。INDIRECT関数を使えばI列のデータのある一番下の行の値が取り出せるぞ!

I37セル
 =INDIRECT(“I”&COUNTA(F6:F36)+5)

Iをダブルクォーテーション(””)で囲むのを忘れないようにね。
出来たら”パーセントスタイル””小数点以下の表示桁数を増やす”を適用して完成だ。
前年比累計欄

J列(計画労働時間の列)の作成

J6セルに計画労働時間(今は適当でOK)を入力して、H列を塗った「薄い黄色」で塗りつぶそう。
次に小数点第2位まで表示させたいので”小数点以下の表示桁数を増やす”2回クリックしよう。出来たらJ6セルをJ36セルまでコピーだ。
計画労働時間

J37セルにはJ6〜J36セルの合計を表示させよう。

J37セル
=SUM(J6:J36)

単位は時間(hour)なのでJ37セルの単位を『H』にしよう。
J37セルを右クリックして、”セルの書式設定”→”ユーザー定義”→『♯,♯♯0”千円”』を選択して”種類”のボックス内を『♯,♯♯0.00”H”』に変更して”OK”をクリック。

文字数が多くて「♯♯♯♯♯」になってしまったので、右クリックして”セルの書式設定”→”配置”→”文字の制御”の”縮小して全体を表示する”にチェックを入れて”OK”をクリック。

労働時間の単位

K列L列(労働時間実績と累計の列)の作成

K6セルに適当な数値を入力して”桁区切りスタイル”2回適用、塗りつぶしを”アクア”にしてK36セルまでコピーしよう。
客数と売上は2月10日までしか入力していないので、K16〜K36セルの値を削除するよ。K16〜K36セルを選んでから右クリックして”数式と値のクリア”をクリックして数値を消しておこう。

L列にはK列の累計を表示させるよ。

L6セル
=IF(K6=””,””,SUM($K$6:K6))

L6セルをL36セルまでコピーしよう。
労働時間累計
K37セルとL37セルは”結合”して、=SUM(K6:K36)と入力。
K37セルを右クリックして、”セルの書式設定”→”ユーザー定義”→『♯,♯♯0.00”H”』を選択して”OK”をクリックしよう。

労働時間の累計

M列(労働時間計画差の列)の作成

M列には、労働時間の実績と計画労働時間との差を表示させるよ。

M6セル
  累計の労働時間実績:L6
  累計の計画労働時間:SUM($J$6:J6)
 =L6-SUM($J$6:J6)

当日の労働時間が入っていない所は空白にしたいので下記のように修正しよう。

M6セル
 『=L6-SUM($J$6:J6)』
    ⬇︎
 =IF(K6=””,””,L6-SUM($J$6:J6))

出来たらM6セルをM36セルまでコピーしよう。
計画差

5. INDEX(インデックス)関数

M37セルはI37セルと同じで6行目〜36行目のデータが入力されている一番下の行を探して表示させるよ。今度はINDEX関数を使ってみよう!

INDEX関数は指定した範囲の中から指定した位置のデータを取り出すことが出来るよ。範囲は『M6:M36』、データが入力されている一番下の行は『COUNT(M6:M36)』で探し出そう。

M37セル
=IF($K$37=0,””,INDEX(M6:M36,COUNT(M6:M36)))

M37セルを右クリックして、”セルの書式設定”→”ユーザー定義”→『♯,♯♯0.00”H”』を選択して”OK”をクリック。

「♯♯♯♯♯」になってしまうので、右クリックして”セルの書式設定”→”配置”→”文字の制御”の”縮小して全体を表示する”にチェックを入れて”OK”をクリック。

計画差の累計

N列(人時売上の列)の作成

N列には人時売上を表示させるよ!

人時売上=(当日の)売上÷(当日の)労働時間

当日の売上は千円単位にしているので、円単位にする為に、売上に「1000」を掛けるよ。Excelでの掛け算は「*」(半角入力でShiftを押しながら「け」)だ!

N6セル
  当日の売上:F6*1000
  当日の労働時間:K6
 =(F6*1000)/K6

当日の労働時間が入っていない所は空白にしたいので修正しよう。

N6セル
 『=(F6*1000)/K6』
    ⬇︎
 =IF(K6=””,””,(F6*1000)/K6)

カンマを付けるので”桁区切りスタイル”を適用してから、N37セルまでコピーしよう。
人時売上

O列(人件費率の列)の作成

O列には人件費率を表示させるよ。

人件費率=(累計の)人件費÷(累計の)売上×100

今回、人件費は労働時間×平均時給で算出するよ。平均時給はN2セルの値を使おう。またN2セルは日にちに関係なくいつでも使うので絶対参照にしよう。

O6セル
   累計の人件費:L6*$N$2
   累計の売上:G6*1000
 =(L6*$N$2)/(G6*1000)
   ※人件費率の式の「×100」は無視してOKだよ

当日の労働時間が入っていない所は空白にしたいので修正しよう。

O6セル
 『=(L6*$N$2)/(G6*1000)』
    ⬇︎
 =IF(K6=””,””,(L6*$N$2)/(G6*1000))

”パーセントスタイル””小数点以下の表示桁数を増やす”を適用して、O6セルをO36セルまでコピーしよう。
人件費率
O37セルはM37セルをコピーして、”パーセントスタイル””小数点以下の表示桁数を増やす”を適用しよう。

O37セル
=IF($K$37=0,””,INDEX(O6:O36,COUNT(O6:O36)))
人件費率の累計欄

P列(当日廃棄の列)の作成

P6セルに適当な数値を入力して”桁区切りスタイル”を適用、塗りつぶしを”アクア”にしてP36セルまでコピーしよう。
P16〜P36セルは右クリックして”数式と値のクリア”を押下して数値を消しておこう。
当日廃棄

Q列(累計廃棄の列)の作成

Q列にはP列の累計を表示させるよ。

Q6セル
=IF(P6=””,””,SUM($P$6:P6))

”桁区切りスタイル”を適用してから、Q36セルまでコピーしよう。
累計廃棄
P37セルとQ37セルは”結合”して、=SUM(P6:P36)と入力。
P37セルを右クリックして、”セルの書式設定”→”ユーザー定義”→『♯,♯♯0”千円”』を選んでから”種類”のボックス内を『♯,♯♯0”円”』に修正して”OK”をクリックしよう。

廃棄の累計

R列(廃棄率の列)の作成

R列には廃棄率を表示させるよ。

廃棄率=(累計の)廃棄額÷(累計の)売上×100
R6セル
=Q6/(G6*1000)
   ※廃棄率の式の「×100」は無視してOKだよ

当日の廃棄額が入っていない所は空白にしたいので修正しよう。

R6セル
 『=Q6/(G6*1000)』
    ⬇︎
 =IF(P6=””,””,Q6/(G6*1000))

”パーセントスタイル”を適用、また”小数点以下の表示桁数を増やす”2回クリックして、R6セルをR37セルまでコピーしよう。
廃棄率

R37セルが「♯DIV/0!」となってしまった!
「♯DIV/0!」はエラーという意味なので数式を修正しよう。

R37セルの数式を見ると『=IF(P6=””,””,Q37/(G37*1000))』になっている。Q37セルとG37セルはそれぞれP37セルとF37セルに結合したので、今は存在しないような状態になっているからエラーになってしまったんだ。

R37セルは下記のように修正しよう!

R37セル
 『=IF(P6=””,””,Q37/(G37*1000))』
    ⬇︎
 =IF(P6=””,””,P37/(F37*1000))

これでちゃんと表示出来たね!
廃棄率の累計

6. 罫線の調整

B4〜R37セルの罫線を太線や点線を使用して見やすくしてみよう!

  1. B4〜B37(C37)セルを選択して”外枠太罫線”を適用
  2. D4〜I37セルを選択して”外枠太罫線”を適用
  3. J4〜O37セルを選択して”外枠太罫線”を適用
  4. P4〜R37セルを選択して”外枠太罫線”を適用
  5. B4〜R5セルを選択して”外枠太罫線”を適用

外枠太罫線
36行目の下は二重罫線にするよ。
B36〜R36セルを選択して”下二重罫線”を適用しよう。
下二重罫線
次は点線の罫線を作ってみよう!
点線は”セルの書式設定”から変更するよ。
E5〜E36セルを選択してから右クリックして、”セルの書式設定”→”罫線”→”線のスタイル”から”点線”を選択しよう。下画像を参照して、②の紫の部分をクリックすればE列の左側に点線が適用されるぞ!
点線の罫線

他の部分も点線にしていくよ!
同じ動作を繰り返すショートカットキーは覚えているかな?

そう[F4]だ!

  1. G5〜G36セルを選択して[F4]
  2. I5〜I37セルを選択して[F4]
  3. L5〜L36セルを選択して[F4]
  4. Q5〜Q36セルを選択して[F4]
  5. R5〜R37セルを選択して[F4]

繰り返し
繰り返しをしたせいで、罫線が少しおかしくなってしまったので修正するよ。

  1. B4〜R37セルを選択して”外枠太罫線”を適用
  2. D4〜I37セルを選択して”外枠太罫線”を適用
罫線の修正

7. 条件付き書式

日付(B列)と曜日(C列)の列を修正するよ。

今はB35セルとB36セルに3月1日と2日が表示されてしまっているので、これが見えないように修正するよ。今回は条件付き書式でやってみよう!

条件付き書式は、例えばテスト結果の一覧表があった時に、1位の人のセルに色を塗ったり、上位10人のセルに色を塗ったり、平均点以下の人を赤字にしたり出来るんだ!

今回の場合は、表の中で次の月になっているセルを見つけてそのセルの文字を白色にするよ。”次の月になっている”かどうかはB列の日付がG2セルと同じ月かどうかで判定するよ。

ここで等号・不等号の使い方を覚えておこう!

AとBは等しい A=B AとBは等しく無い A<>B
AはBより大きい A>B AはBより小さい A<B
AはBより大きいか等しい A>=B AはBより小さいか等しい A<=B

B34セルの”月”の算出方法は=MONTH(B34)だよ。G2セルと”月”が異なっているか判定する数式は=MONTH(B34)<>$G$2だ!

B34〜C36セルを選択して”条件付き書式”→”新しいルール”→”数式を使用して、書式設定するセルを決定”をクリックしよう。
”次の数式を満たす場合に値を書式設定”のボックスに、
=MONTH(B34)<>$G$2と入力して、”書式”をクリックだ!
条件付き書式
”フォント”の”色”から”白”を選んで”OK”をクリックしよう。
条件付き書式2
これでどの月にしても翌月の日付が出てこなくなったね!

枠線の非表示

Excelには元々薄い線が引いてあるけど、この表にはもう罫線や数式を入力することは無いので消してしまおう!
枠線の非表示
枠線を非表示にするには、”表示”のタブから”枠線”のチェックを外すだけでOKだよ!
枠線の非表示2
最後に文字を微調整して仕上げるよ!
B6〜C36セルを選択して”中央揃え”にしよう。
中央揃え
B37〜R37セルは”太字”にしよう。
太字

8. セルのロック

表が完成したので、普段入力するセル以外は選択出来ないようにしてしまおう! これで間違えて数式を消してしまうことが防げるぞ。

”セルの書式設定”の”保護”のタブから”ロック”の有無を選べるよ。初期設定はすべてのセルがロックになっているので、普段入力するセルを選択してロックを解除しよう。

離れたセルを一度に選択するのは、[CTRL]を押しながらセルを選択していくのは覚えているかな!?
黄色とアクアに塗りつぶしたセルが入力するセルなので、まずB2セルをクリックしてから[CTRL]を押しながら、E2、G2、J2、N2、D6〜D36、F6〜F36、H6〜H36、J6〜K36、P6〜P36セルを選択しよう。選択されたセルの上で右クリックして”セルの書式設定”→”保護”のタブから”ロック”のチェックを外そう。
ロック

今はロックがされていてもされていなくても違いが分からないよね?

ロックされたセルを選択出来ないようにするには、”校閲”のタブから”シートの保護”をクリックして、”ロックされたセル範囲の選択”のチェックを外して、”ロックされていないセル範囲の選択”だけにチェックが入っている状態にして、”OK”を押下だ!
シートの保護
これで表は完成だ!

9. シート名の変更、シートの削除・複製

今は2月だけしかシートが無いので、1月〜12月まで入力出来るようにシートを増やしておこう!

シート名の変更方法
”Sheet1”をダブルクリックして『2月』と入力。または”Sheet1”を右クリックして”名前の変更”をクリックして『2月』と入力。
シートの削除方法
”Sheet2”を右クリックして”削除”をクリック。
”Sheet3”も”削除”しておこう。
シートの複製方法
”2月”のシートをクリックしたままの状態にすると、カーソルに白い紙みたいなものが表示される。その状態で[CTRL]を押下すると白い紙に➕が表示されるんだ。左クリックと[CTRL]を押下したまま、右にドラッグして、左クリックを離そう。”2月(2)”というシートが出来たかな?

”2月(2)”のシートの黄色とアクアの数値をクリアしてから、”2月(2)”のシートを12ヶ月分複製しよう。複製出来たらシート名を『1月』〜『12月』に変更しよう!
シートの複製
ついに売上集計表が完成したよ!
よくがんばったね!

完成形

完成形

上司「ギャフン」



Archive