この動画では、エクセルでQSC情報を管理して、集計したり順位を付ける方法を紹介します。

QSCとは

• クオリティ
• サービス
• クレンリネス

の頭文字を取ったもので、主に飲食店の店舗レベルを数値化し、向上させるための指標です。

まずは完成形を確認します。この表には、いつ、どの店の、各評価項目が何点かを5点満点で入力します。後からいつの時間帯の評価かが分かるように、日付だけでなく、時間も記録しておくのがおすすめです。

各評価項目を入力すると、QSCそれぞれの合計点数と全ての合計点数が自動で算出されます。

また、相対的に点数が高い場合は緑色、相対的に点数が低い場合は赤色に自動で背景色が変わるように設定しておくことで、ひと目で改善が必要な項目が分かります。

別のシートには、各店舗ごとのQSCの平均点を確認することができます。

また、店舗に順位を付けることで、表彰制度を導入したりして、各店舗のQSCレベルの向上に活用することができます。

また、動画の後半では、入力の選択肢を用意したり、意図しない情報は入力できないように入力制限をかけたりする方法についても紹介します。

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

項目名

まずは、QSCの点数を入力する表の項目名の欄を作成していきます。

(2行目から)

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

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

(1行目入力)

セルをまたいで文字を表示したい場合はセルを結合します。セルを結合するには、結合したいセルをドラッグで選択して、上のホームタブのセルを結合して中央揃えをクリックすると、セルを結合することができます。

Ctrlキーを押しながらYキーを押すと、以前と同じ操作を行うことができるので、素早く結合していくことができます。

列の固定

次に、評価項目が多い場合、横に情報が増えると見づらくなってしまうので、ウィンドウ枠を固定します。

ウィンドウ枠を固定しないままだと、スクロールした際にこのように、どこの店の点数かが分かりにくくなってしまいます。

ウィンドウ枠を固定すると、このようにセルの一部分が固定されるので、見やすくなります。

ウィンドウ枠を固定するには、固定したい列と行の境目の次のセルをを選択した状態で、上の表示タブからウィンドウ枠の固定をクリックし、出てきた選択肢の中からウィンドウ枠の固定をクリックします。

罫線

次に各項目をカテゴライズするために、セルの境目に線を引きます。

エクセルではセルの境目のことを罫線といい、罫線を黒色にすることで、線を引くことができます。

罫線の書式を変えるには、まず書式を変えたい罫線の隣接するセルを選択します。

今回は複数列変更したいので、1つ目の列を通常通りクリックしたあと、2つ目の列をCtrlキーを押しながらクリックすることで、複数列を同時に選択した状態にすることができます。

セルを選択できたら、上のホームタブのフォントグループの罫線メニューで罫線の書式を変更することができます。今回は左側に罫線を引きます。

セルを結合している場合は、罫線がうまく適用されない場合があるので、その場合は、列選択ではなく個別で選択して、設定する必要があります。

1つ目のセルを通常通りクリックしたあと、2つ目のセルをCtrlキーを押しながらクリックし、複数列を選択した状態にして、罫線を引きます。

保存

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

任意のファイル名と保存先を指定します。

仮

次に、集計などの各挙動を確認するために、店舗名とQSCの点数を仮で入力していきます。

店舗名

店舗名は、Ctrlキーを押しながらCでコピーして、Ctrlキーを押しながらVキーで貼り付けて2周分ほど作成しておきます。

QSC

QSCの点数は半角数字で入力します。今回は5段階評価制を想定していますので、1~5の間で入力します。

ランダムな数字を入力したい場合は、

=RANDBETWEEN(1,5)

というように入力します。

RANDBETWEEN関数では、1つ目の引数にはランダムに入力したい数字の最小値、2つ目の引数にはランダムに入力したい数字の最大値を入力することで、その範囲の数字をランダムに表示することができる関数です。

RANDBETWEEN関数を1つ入力できたら、そのセルを選択した状態で、セルの右下にある小さい四角を右にドラッグすることで、他の列にも適用することができます。さらにそのままセルの右下にある小さい四角をダブルクリックすることで、他の行にも適用することができます。

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

RANDBETWEEN関数が入力されているデータをドラッグで選択した状態でCtrlキーを押しながらCを押して、データをコピーします。

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

すると、RANDBETWEEN関数がただの数字に書き換わり、1~5の範囲でランダムな数字を入力することができました。

保存

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

SUM関数

次に、入力した点数を元に、QSCそれぞれの合計点数が自動で算出されるように設定します。

セル内の数字を合計するには、

=SUM(D3:E3)というように、入力します。

SUM関数は、引数の中にある数字を足すことができる関数です。

D3:E3はD3セルからE3セルという意味なので、この数式はD3セルからE3セルの数字を足すという意味になります。

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

ここで、エクセルの設定やバージョンによってはエラーが発生します。これは、隣接したセルの数字を全て足していないことを警告するものです。

この数式はQualityの点数のみを足せば良いので、エラーは無視して大丈夫です。

エラーが発生しているセルを選択した状態で、左に表示されている、ビックリマークをクリックして、エラーを無視するをクリックすると、エラーを非表示にすることができます。

同じようにServiceやCleanlinessの合計点数も算出していきます。

=SUM(F3:G3)

と入力して、Serviceの合計点数を算出します。

セルの右下の小さい四角をダブルクリックして他の行にも適用します。

エラーは後ほどまとめて、非表示にするので、先に

=SUM(H3:I3)

と入力してCleanlinessの合計点数を算出します。

セルの右下の小さい四角をダブルクリックして他の行にも適用します。

QSCそれぞれの合計点数を算出できたら、エラーが発生しているセルを選択して、左に表示されている、ビックリマークをクリックして、エラーを無視するをクリックして、エラーを非表示にします。

次に、全ての項目の点数の総合計が自動で算出されるように設定します。

=SUM(J3:L3)

というように入力して、同じようにセルの右下の小さい四角をダブルクリックして他の行にも適用します。

これで全ての項目の点数の総合計が自動で算出されるように設定することができました。

全ての項目の点数の総合計は

=SUM(D3:I3)

でも算出することができますが、保守の観点からはおすすめしません。

=SUM(D3:I3)と設定した場合、例えば、Qualityのカテゴリに新しい評価項目が追加された時に、

(ファーストドリンク)

Qualityの合計と、総合計の両方の数式を変える必要があります。

これを、=SUM(J3:L3)というように、QSCの各項目を合計したものを、合計するように設定しておけば、Qualityの合計の数式を変えるだけで、総合計の数字も正しく集計することができます。

エクセルでは、このように後からの変更にも対応できるように、保守の観点から数式を作成していくことも重要です。

保存

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

テーブル

次に表のデザインをこのように縞々にして、見やすくします。

表を縞々のデザインにするには、その表にテーブルという設定をする必要があります。

テーブルというのは日本語で表という意味で、これを設定することで、表のデザインを簡単に整えたり、データを並び替えたり絞り込んだりできるようになります。

テーブルを設定するには、テーブルを設定したい表のどこかを選択した状態で、Ctrlキーを押しながらTキーを押します。

すると、テーブルの設定メニューが表示されます。

テーブルの設定メニューが表示されたら、まずテーブルを設定する範囲を選択します。通常は自動で選択されるのですが、正しく選択されていない場合は、ドラッグで選択し直す必要があります。

テーブルの見出しは1行しか設定することができないですが、今回は2行分選択されているので、選択し直す必要があります。

今回はこのようにドラッグして選択し直します。

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

これで、縞々のデザインになり、テーブルを設定することができました。

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

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

配置

次にセル内の文字や数字をセルの中央に寄せて見やすくします。

文字や数字を中央寄せにしたいセルを選択した状態で、上のホームタブから配置グループの中央寄せのアイコンをクリックします。

これで、セル内の文字や数字をセルの中央寄せにすることができました。

次に、テーブルの見出し欄を左寄せにします。

テーブルの見出し欄は絞り込みや並び替えを操作するボタンが表示されているため、中央寄せだと見にくくなる場合があるので、左寄せにしておくと見やすくなります。

左寄せにしたいセルを選択した状態で、上のホームタブから配置グループの左寄せのアイコンをクリックします。

幅を調整

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

保存

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

ピボットテーブル

次に、入力した表を元に、平均点の集計と順位を算出します。

集計表を作成するには、ピボットテーブルという機能を利用します。ピボットテーブルは簡単に集計表を作ることができる機能です。

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

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

設定画面が表示されたら、まず集計の元となるデータの表を選択します。

集計元の表にテーブルを設定している場合は、自動でテーブル名が入力されます。

表にテーブルを設定すると、そのテーブルには自動でテーブル名が設定されます。テーブル名は変更することもできますが、今回は変更していないので、テーブル1という名前が設定されていて、そのテーブル名が入力されています。

通常は自動で入力されますが、もし適切に入力されていない場合は、ドラッグして入力し直してください。

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

すると、新しいシートが開いて、右側にピボットテーブルの設定画面が表示されます。

上のボックスには、集計元の表の項目名が表示されています。

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

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

を入力します。

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

次に、Qualityの平均点を算出したいので、Qをドラッグして値のボックスに入れます。

すると、店舗ごとにQualityの合計点が表示されました。

これを合計点ではなく、平均点が表示されるように変更します。集計方法を変更したい項目を右クリックして、値の集計方法から平均をクリックします。すると、店舗ごとにQualityの平均点が表示されました。

表示桁数が多いので設定を変更します。先程と同じように設定を変更したい項目を右クリックして、表示形式をクリックします。表示形式機能では、その名の通り、表示する形式を変更することができます。

ここでは、数値を選択して、小数点以下の桁数を1に設定します。これで、小数第一位まで表示することができます。設定できたら、OKをクリックすると、Qualityの平均点を小数第一位まで表示することができました。

同じように、Serviceの平均点も算出していきます。

ピボットテーブルの集計表を選択した状態で、ピボットテーブルの設定メニューの上のボックスからSの項目を値のボックスに入れます。ボックス内の順番で、集計表も並びます。

Serviceの項目を右クリックして、値の集計方法を平均に設定します。もう一度右クリックして表示形式から、数値を選択し、小数点以下の桁数を1に設定してOKをクリックします。

次に、Cleanlinessの平均点も算出します。

ピボットテーブルの集計表を選択した状態で、ピボットテーブルの設定メニューの上のボックスからCの項目を値のボックスに入れます。

Cleanlinessの項目を右クリックして、値の集計方法を平均に設定します。もう一度右クリックして表示形式から、数値を選択し、小数点以下の桁数を1に設定してOKをクリックします。

次に、総合計の平均点も算出します。

ピボットテーブルの集計表を選択した状態で、ピボットテーブルの設定メニューの上のボックスから総合計の項目を値のボックスに入れます。

総合計の項目を右クリックして、値の集計方法を平均に設定します。もう一度右クリックして表示形式から、数値を選択し、小数点以下の桁数を1に設定してOKをクリックします。

次に、順位を算出します。

まず、Qualityの順位を算出します。

ピボットテーブルの集計表を選択した状態で、ピボットテーブルの設定メニューの上のボックスからQの項目を値のボックスの「平均 / Q」の項目の下に入れます。

そして、その項目を右クリックして、計算の種類から降順での順位をクリックします。すると、Qualityの平均点に応じて順位を付けることができました。

同じようにServiceの順位も算出します。

ピボットテーブルの集計表を選択した状態で、ピボットテーブルの設定メニューの上のボックスからSの項目を値のボックスの「平均 / S」の項目の下に入れます。

そして、その項目を右クリックして、計算の種類から降順での順位をクリックします。これで、Serviceも平均点に応じて順位を付けることができました。

次にCleanlinessの順位も算出します。

ピボットテーブルの集計表を選択した状態で、ピボットテーブルの設定メニューの上のボックスからCの項目を値のボックスの「平均 / C」の項目の下に入れます。

そして、その項目を右クリックして、計算の種類から降順での順位をクリックします。これで、Cleanlinessも平均点に応じて順位を付けることができました。

次に総合計の順位も算出します。

ピボットテーブルの集計表を選択した状態で、ピボットテーブルの設定メニューの上のボックスから総合計の項目を値のボックスの「平均 / 総合計」の項目の下に入れます。

そして、その項目を右クリックして、計算の種類から降順での順位をクリックします。これで、総合計も平均点に応じて順位を付けることができました。

次に各項目欄を分かりやすいように変更しておきます。

セルを編集モードにするには、編集したいセルを選択した状態で、F2キーを押すことで、編集モードにすることができます。

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

次に、各項目をカテゴライズして、見やすくします。

結合したいセルをドラッグで選択して、上のホームタブからセルを結合して中央揃えをクリックして、セルを結合します。

Ctrlキーを押しながらYキーを押すと、以前と同じ操作を繰り返すことができるので、セルを結合していきます。

更に見やすくなるように罫線を引きます。

今回は複数セルを同時に罫線を引きたいので、1つ目のセルの範囲を通常通りドラッグして選択したら、2つ目のセルの範囲をCtrlキーを押しながらドラッグして選択して、複数セルを同時に選択した状態にします。

セルを選択できたら、上のホームタブのフォントグループの罫線メニューで、今回は外枠を選択します。

同じように横にも線を引きます。

1つ目のセルの範囲を通常通りドラッグして選択したら、2つ目のセルの範囲をCtrlキーを押しながらドラッグして選択します。

セルを選択できたら、Ctrlキーを押しながらYキーを押して、先程と同じ操作を繰り返して外枠に罫線を引くことができます。

配置

次にセル内の文字や数字をセルの中央に寄せて見やすくします。

文字や数字を中央寄せにしたいセルを選択した状態で、上のホームタブから配置グループの中央寄せのアイコンをクリックします。

シート

シートをダブルクリックしてシート名を変更します。

集計のシートのシート名を集計と変更して、記録用のシートのシート名を記録と変更しておきます。

ドラッグで使いやすいように並び替えておきます。

保存

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

条件付き書式

次に記録シートの点数で、相対的に点数が高い場合は緑色、相対的に点数が低い場合は赤色にグラデーションで背景色が変わるように設定します。

相対的にグラデーションで背景色が変わるように設定するには、条件付き書式という機能を使います。

条件付き書式機能は、条件によって書式が自動で変わるように設定することができる機能です。

つまりここでは、相対的に大きい数字は緑色、相対的に小さい数字には赤色に背景色が変わるように設定します。

相対的な評価で背景色を変えるので、同じ評価段階の項目ごとに設定していきます。

今回は、各項目が5段階評価なので、QSCそれぞれが10段階評価、総合計が30段階評価になっています。

まず、5段階評価である各項目の列をドラッグで選択します。

次に、上のホームタブから条件付き書式をクリックします。出てきたメニューのカラースケールから上が緑色、下が赤色のグラデーションになっているアイコンを選択します。

これは、相対的に大きい数字は緑色、相対的に小さい数字には赤色背景色が変わることを意味します。

これで、相対的に点数が高い場合は緑色、相対的に点数が低い場合は赤色にグラデーションで背景色が変わるように設定することができました。

同じように、10段階評価であるQSCの各合計にもグラデーションをかけます。

QSCの列をドラッグで選択したら、上のホームタブから条件付き書式をクリックします。出てきたメニューのカラースケールから上が緑色、下が赤色のグラデーションになっているアイコンを選択します。

これで、QSCの各合計にもグラデーションをかけることができました。

同じように、30段階評価である総合計にもグラデーションをかけます。

総合計の列を選択したら、上のホームタブから条件付き書式をクリックします。出てきたメニューのカラースケールから上が緑色、下が赤色のグラデーションになっているアイコンを選択します。

これで、点数が高いところや低いところが、ひと目で分かるようになりました。

次にピボットテーブルで作成した平均点にもグラデーションをかけます。

平均点が10段階で算出されるセルの範囲をドラッグで選択し、2つ目以降のセルの範囲をCtrlキーを押しながらクリックして、複数選択します。

ピボットテーブルに条件付き書式を適用する場合は、列で選択すると、正しく機能しない場合があるので、範囲で選択します。

選択できたら、上のホームタブから条件付き書式をクリックします。出てきたメニューのカラースケールから上が緑色、下が赤色のグラデーションになっているアイコンを選択します。

これで、平均点が10段階で算出される範囲にグラデーションをかけることができました。

次に、平均点が、30段階で表示される範囲にグラデーションをかけます。

平均点が30段階で算出されるセルの範囲をドラッグで選択して、上のホームタブから条件付き書式をクリックします。出てきたメニューのカラースケールから上が緑色、下が赤色のグラデーションになっているアイコンを選択します。

これで、平均点が30段階で算出される範囲にグラデーションをかけることができました。

次にピボットテーブルで作成した順位の1位に色を付けます。順位が入力されているセルの範囲をドラッグで選択し、2つ目以降のセルの範囲をCtrlキーを押しながらクリックして、複数選択します。

選択できたら、上のホームタブから条件付き書式をクリックします。次に表示されたメニューからセルの強調表示ルールから指定の値に等しいを選択します。

次の値に等しいセルを書式設定の欄には1を入力して、書式は、濃い黄色の文字、黄色の背景を選択します。

これで、セル内のデータが1の場合、濃い黄色の文字、黄色の背景に変更するという意味になります。

OKをクリックすると設定完了です。

データの入力規則

次に、意図しない情報は入力できないように入力制限をかけていきます。

店舗名の欄は、1文字でも相違があると正しく集計されないため、予め用意された選択肢のみ、入力を許可することで、集計ミスを防ぐことができます。

また、点数を入力する欄も5段階評価なので、数字の1~5以外は入力できないように設定することで、6以上の数字が入ったりするのを防ぐことができます。

まずは、点数を入力する欄に数字の1~5以外は入力できないように設定します。

意図しない情報は入力できないように入力制限をかけるには、データの入力規則機能を使います。

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

入力制限をかけたいセルを含む列をドラッグで選択します。次に項目名の欄は入力制限をかける必要が無いので、Ctrlキーを押しながらドラッグして、選択を解除します。

次に、上のデータタブから、データの入力規則を選択します。

するとデータの入力規則メニューが開きます。

設定タブから、入力値の種類を整数に設定し、データは次の値の間のままで、最小値を1、最大値を5に設定します。

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

OKをクリックして、1~5の整数以外を入力しようとすると、このようにエラーが発生して、入力できないように設定することができました。

次に、店舗名の欄を予め用意された選択肢以外は入力できないように設定します。

選択肢以外は入力できないように設定することで、店舗名の入力ミスを防ぎ、集計ミスも防ぐことができます。

選択肢以外は入力できないように設定するには、まずこのように選択肢を用意する必要があります。

今回は新しいシートに選択肢を用意します。

左下のシートタブの右にある、+ボタンをクリックして新しいシートを作成します。シート名をダブルクリックして、店舗リストと変更してEnterで確定したら、ドラッグで並び替えておきます。

次に新しいシートに店舗名を入力していきます。

記録のシートに、店舗一覧がある場合はそれをCtrlキーを押しながらCでコピーして、店舗リストのシートにアプリケーションキーを押してからVキーで、書式は貼り付けず、値のみ貼り付けることができます。

店舗リストを作成できたら、記録のシートに戻り、入力制限をかけたいセルを含む列を選択します。項目名の欄は制限をかけないので、Ctrlキーを押しながらドラッグして、選択を解除します。

次に、上のデータタブから、データの入力規則をクリックします。

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

次に、元の値のボックスをクリックしてカーソルを入れたら、店舗リストのシートをクリックし、先程作成した店舗リストの列をクリックします。

すると、店舗リストのA列にあるデータ以外は入力できないように設定することができます。

これは、ドラッグして範囲を指定することもできますが、列で指定しておけば、後から店舗を追加したい際に、設定し直す必要がなくなり便利です。

入力できたら、ドロップダウンリストから選択するにチェックが入っていることを確認します。ドロップダウンリストというのは、このような選択肢のメニューのことです。

チェックを外していれば、選択肢以外の入力ができないように制限をかけるだけですが、チェックを入れておくと、選択肢からの入力ができるようになります。

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

これで、逆三角形から選択肢を表示して入力できるようになりました。

選択肢以外の文字を入力しようとすると、

(秋葉原店)

このようにエラーが発生して、入力できないようになっています。

店舗リストに店舗名を追加すると、

(秋葉原店)

選択肢に追加され、入力できるようになることも確認します。

選択肢は、そのセルを選択した状態で、Altキーを押しながら下キーを押すことでも表示することができ、方向キーで選択して、Enterキーで入力することができるので、状況に応じて使ってみてください。

仮のデータを削除する

最後に、仮のデータを削除しておきます。データの1行目以外の行を選択して、右クリックから削除をクリックして削除します。

次に情報を入力するセルを選択して、Deleteキーで削除しておきます。この時、数式が入っているセルは削除しないように気をつけてください。

データを入力すると、自動でテーブルの範囲が広がり、数式も追加されていることが分かります。

Ctrlキーを押しながらZキーで元に戻ります。戻りすぎた場合はCtrlキーを押しながらYキーで戻すことができます。

さいごに

最後にピボットテーブルの注意点をお伝えしておきます。

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

反映させるには、

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

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

保存

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

以上が、エクセルでQSC情報を管理して、集計したり順位を付ける方法でした。