時短エクセル（第42回）表の作成を通してExcelの必須機能を習得する（後編）

公開日：2021.05.17

　前回はスポーツチームのリーグ戦の対戦表を作成しながら、「Excelの必須機能を習得する」（前編）をお届けした。今回は後編としてその続きを見ていこう。

　前編では、IF関数を使って○○の場合はA、○○でなければBという、文字列を自動で表示するところまで紹介した。後編は、データの入力規則機能や「SUMIF」「COUNTIF」といった、仕事に役立つ関数の使い方を習得しよう。

※当記事では練習用のサンプルファイルがダウンロードできます（ページ下部）

完成した表。これをどれだけ効率的に作れるかを紹介する

右側の見出しは手入力ではなく転記で効率化＆ミス防止

　サンプルファイルの「Step2」までは前回紹介した内容だ。今回は「Step3」シートから解説する。右側の「SL」と「名前」は左側と同じにしたい。そこで、ここでは手入力ではなく、転記されるよう設定しよう。やり方は、「このセルと同じ内容を表示する」という設定をすればよい。シーズン中は成績によって「SL」が刻々と変わり、手入力ではその度に2カ所ずつ修正しなくてはならず、修正忘れが生じるかもしれない。転記方式にしておけば、効率が良いだけでなく、間違いも防止できる。

SLは「=B7」、名前は「=A7」と入力する。あとはL7とM7をドラッグして選択し、下方向にドラッグしてコピーするだけ

出場選手決めは選択式＆色分けで分かりやすく

　試合に出場する人は○、その日都合が悪い人は×、それ以外の人は△とする。1試合に出場できるのは5人なので、×の人を除き、出場回数をなるべく平均化できるように割り振っていく。

　C7～J14をドラッグして範囲指定し、データの入力規則で「○、△、×」の選択方式にし、次に条件付き書式機能で、記号によって色を変えるように設定する。

範囲を選択し、「データ」タブの「入力規則」をクリックする

「設定」タブの「入力値の種類」で「リスト」を選び、「元の値」に「○,△,×」と入力する。リスト化したい数字や文字列、機能などを「,」（カンマ）で区切って入力する

リストから○、△、×を選べるようになる

次は条件付き書式で、記号によって色を変える。C7～J14をドラッグして範囲指定し、「ホーム」タブの「条件付き書式」→「新しいルール」を選択

「指定の値を含むセルだけを書式設定」を選び、ルール内容は「セルの値」「次の値に等しい」「=”○”」と設定して「書式」をクリック

「塗りつぶし」タブで色を指定して「OK」をクリックする

　上記を繰り返し、△と×も別の色で塗りつぶしを設定する。

合計SL（スキル）はSUMIF関数で計算、SLオーバーならすぐに分かる色味に

　これで出場する人を検討できる状態になった。次は合計SLの計算を自動的に行う設定をしよう。設定方法は、「○と書かれている人のSL（B列）を合計する」という関数を入力する。さらに、1試合の合計SLは23以内と定められているため、23をオーバーする場合はひと目でNGと分かるようにしたい。これには、先ほど記号の種類によって色を変えた「条件付き書式」機能を使う。

C15に「=SUMIF(C7:C14,”○”,$B7:$B14)」と入力し、J15までドラッグする

　SUMIFとは、「条件に合うデータを合計する」関数で、「C7～C14」で「○」と合致した場合、「B7～B14」の「○」と同じ行にある数字を合計するという意味だ。「$B7:$B14」となっているのは、右方向にセルをコピーしても参照するセルは移動しないようにするためだ。

次は条件付き書式で、合計SLが23を超えた場合にセルを目立つ色に設定する。C15からJ15をドラッグして指定し、「ホーム」タブの「条件付き書式」→「セルの強調表示ルール」→「指定の値より大きい」をクリックする

左を23に設定し、書式は「ユーザー設定の書式」を選ぶ

「フォント」タブでは赤文字のボールドを、「塗りつぶし」タブでは目の覚めるような黄色を選んだ

合計SLが23を超えると、セルが黄色の赤文字になるのですぐに分かる

出場回数はCOUNTIF関数でカウントする

　そのシーズンに各人が何回出場したかをカウントするにはCOUNTIF関数を使う。○の回数を数えればよい。

K7に「=COUNTIF(C7:J7, “○”)」と入力し、下方向にドラッグする

　得点は、試合が終わればその都度記入していく。合計点数はその時点までの得点なので、前回までの得点とその日の得点を合計すればよい。そして、強調したい部分に塗りつぶしを施すなど、表の体裁や装飾を整えれば完成だ。

C17に「=SUM($C16:C16)」と入力して、J17までドラッグしてコピーする

これで合計SLや出場回数をリアルタイムでチェックしながら出場者を決められ、戦略も立てやすくなる

　このテンプレートを作っておけば、次回から日付や対戦相手などを変更するだけでほとんどそのまま使い回せる。この表作りで学んだ機能や関数の使い方をぜひ仕事にも活用してほしい。

※解説用画面はMicrosoft 365のExcelを使用。一部メニュー名などが異なる場合がある
※第43回は2021年6月7日（月）公開

【ダウンロード】
「時短エクセル」　表の作成を通してExcelの必須機能を習得する（後編）　のお試しファイルのダウンロードはこちらから（Excelファイル）

執筆＝内藤由美

大手ソフト会社で14年勤務。その後、IT関連のライター・編集者として活動。ソフトウエアの解説本、パソコンやスマートフォンの活用記事などを執筆、編集している。

【T】

あわせて読みたい記事

目的別ショートカットまとめ（第44回）

【ファイル操作編】右クリックメニューやプロパティを表示するショートカットキー

パソコンスキルアップ

2025.01.24
時短エクセル（第86回）

都道府県別データを3Dマップで表現する

パソコンスキルアップ

2025.01.21
目的別ショートカットまとめ（第43回）

【Windows編】画面のスクリーンショットや切り抜きに便利なショートカットキー

パソコンスキルアップ

2025.01.10

「パソコン」カテゴリーから探す

連載バックナンバー

時短エクセル

時短エクセル（第42回） 表の作成を通してExcelの必須機能を習得する（後編）