テストの点数表をExcelで作成することになった。４０点未満の赤点を実際に赤くするように指示されたけど、手作業でやるのは面倒臭いなあ
と思ったときに使える機能が、Excelにはあります。「条件付き書式」という機能です。
こちらで定めた数字より小さい数値の文字色を変える
数値を入力した後、条件付き書式を適用したい範囲のセルを選択します。今回は点数を記したBの列(縦のライン)全体を選択することにします。
画面上部のホームタブの右のほうにある、「条件付き書式」と書かれたボタンをクリックします。
クリックして現れたメニューの一番上の「セルの強調表示ルール」にカーソルを置くと、さらに右にメニューが現れます。
今回は、「４０未満の数字を赤くする」という条件を付けたいので、「指定の値より小さい」を選んでクリックします。
現れたボックスの中にある「次の値より小さいセルを書式設定」の下の欄に、数字を入力します。今回は「４０」と入力します。数値は半角で入力しましょう。
次に、書式の欄の右端にある三角をクリックすると、画像のようなメニューが現れます。文字の色を変えるだけでなく、セルの背景を変えることもできます。今回は単純に数字の色を変えたいだけなので「赤の文字」を選びます。
選んでから「OK」ボタンをクリックします。
これで４０未満の数字が自動的に赤くなりました。
条件付き書式の機能にはほかにも多くの機能があります。
大きい数字にも色を付けたり上位１０名を強調させたり条件を消したり
小さい数字に色をつけれるなら、もちろん大きい数字にも色が付けられます。
「セルの強調表示ルール」メニューの「指定の値より大きい」をクリックして
左の欄に「80」という数値を入力してから、今度は「濃い緑の文字、緑の背景」を選んでみましょう。
この通り条件通りの書式になりました。先ほどの「４０点未満の数字を赤くする」という条件もそのままです。
今度は「点数の高い上位１０名を強調する」という条件を付けてみましょう。
「条件付き書式」のメニューから「上位/下位ルール」にカーソルを置き、右側に現れたメニューから「上位１０項目」をクリックします。
現れたウインドウボックスの左にある欄に、数値を入力したり右端の三角形をクリックして数値を増減させることで上位１０位以上、あるいは以下の数値も条件に含むこともできます。
今回は上位１０名のまま、書式は「濃い赤の文字、明るい赤の背景」を選んで「OK」をクリックします。
この通り条件に従って、セルが強調されました。
おいおい。「上位１０名」と「８０以上」の条件が重なってしまったセルがあるぞ。文字色とセルの背景色は、後で設定した条件の方を優先して変えるぞ。だいたい、強調する条件が多すぎて見にくいぞこの表は。
ごもっともです。なので一旦、セルに設定した条件を消去しましょう。
条件付き書式の一番下から2番目にある「ルールのクリア」にカーソルを置き、右にメニューが現れます。
「選択したセルからルールをクリア」はセルをあらかじめ選択してからクリックすると、選択した範囲のセルに定められた条件が消去されます。
「シート全体からルールをクリア」は文字通り、現在作業しているシートに定められた全ての条件を消去します。
今回はB列全体に条件を定めていたので、「シート全体からルールをクリア」をクリックします。
これで定めた条件が消去され、文字色とセルの背景色が元通りになりました。
条件付き書式のルールを変更する
強調するのは、上位３名までにしたいな。Excelでは上位１０名の強調しか設定できないの？
強調する上位の数を変更することもできます。
この画像では「上位１０名の文字色を緑に背景を青」と「４０点未満の数値の文字色を赤」の条件が設定されています。
条件付き書式の一番下にある「ルールの管理」をクリックします。
「条件付き書式ルールの管理」のウィンドウが現れます。最初は選択範囲に定められている条件を表示する設定になっているので、何も選択していない状態では何も表示されません。
「現在の選択範囲」の右に三角形をクリックして、現れるメニューの中から「このワークシート」をクリックします。
すると現在定められている、全ての条件が表示されます。この中からルールを変更したい条件、今回は「上位１０位」をクリックして選択します。その次に、「ルールの編集」をクリックします。
現れた「書式ルールの編集」ウィンドウの赤い四角で囲われた欄に「３」と入力します。これで上位３名が強調される条件に変更できます。
ついでに、強調する際の文字色と背景色を変えてみましょう。青い四角で囲われた「書式」ボタンをクリックします。
まずは背景色を変更します「塗りつぶし」タブをクリックして、「背景色」の欄にあるカラーパレットから塗りたい色を選んでクリックします。今回は赤い四角で囲った、「薄い緑」を選びます。
次に文字色を変更します。色の欄の三角形をクリックして、現れたカラーパレットから変えたい色をクリックします。今回は「濃い青」を選びます。
プレビューが変更されているのを確認したら、「OK」をクリックします。
「条件付き書式ルールの管理」に戻ります。ルールと書式が変更されているのを確認したら、「OK」ボタンをクリックします。
これで条件は変更され、上位３名のセルが強調されるようになり、文字色と背景色も変更されました。
上位３名という条件なのに、画像では２つしかセルが強調されてないのは、画像に載ってない下の方に「９０」という数値があるからですよ。
数式を使って条件を設定する
さて、今度は店舗別の売上を作成しているとしましょう。７００万円以上の売上のセルを強調したいので、条件付き書式を使って設定してみましょう。
先述したとおりに「条件付き書式」メニューの「セルの強調表示ルール」から「指定の値より大きい」をクリックして、数値を７００と入力し、書式は「濃い緑の文字、緑の背景」とします。
「万円」という単位はExcelに自動で入力させているので、入力する必要はありません。詳しくはこちらの記事で解説しています。
条件が設定されました。しかし、ここで困ったことが起きました。
「７００万円以上」という設定にしたつもりなので、「７００万円」と書かれたセルも強調させたいのにそのままです。
実は先ほどの手順だと「７００よりも大きい」数字が条件となるので、「７００」ちょうどの数値は条件にならず、セルの強調をしてくれないのです。
じゃあ手作業でやるしかないのか？ そんなことはありません。
まずは、条件を定めたい範囲のセルを選択します。
次に「条件付き書式」メニューの下から３番目にある「新しいルール」をクリックします。
現れた「新しい書式ルール」のウィンドウの「ルールの種類を選択してください」の欄の、一番下にある「数式を使用して、書式設定するセルを決定する」をクリックして選択します。
下に「次の数式を満たす場合に値を書式設定」の欄があります。
ここに「＝B3＞＝７００」と入力します。これは選択した範囲の７００以上の数値を表す数式です。
数式について解説しますと、「B3」は選択した範囲の一番上、のセルを表します。今回は縦の範囲にセルを選択したので一番上のセルを入力しています。横に選択した場合は、一番左のセルを入力します。四角形の場合は、一番左上のセルを入力です。
次に「＞＝」とは「≧」という不等号を横書きで表した数値です。これを数値の前に入力することで、その数値以上という意味になります。
セルを複数選択せずに「B3」と入力すると、「B3」のセル一つだけが対象になってしまうから注意だぞ。条件を設定する前に、必ずセルを範囲選択しておこう。
数式を入力したら、「書式」をクリックして文字色と背景色を変えることを忘れずに。そのまま「OK」をクリックしても何も変わりません。
文字色を変えて
背景色も変えて
プレビューを確認したら「OK」をクリック。
これで「７００」も含めた数値が強調されるようになりました。
関数を使って条件を設定する
表を１行ごとに色付けするように指定されたけど、セルの背景色を変えるのを手作業でやるのは面倒だなあ。
そんなときも、条件付き書式で自動で入力ができます。
そのために、関数を用いる必要があります。
まずは条件を設定したい範囲のセルを選択します。
条件付き書式メニューの「新しいルール」をクリック
現れたウィンドウの「数式を使用して、書式設定するセルを決定」をクリックします。
その下の欄に「=MOD(ROW(),2)=0」という関数を入力します。
この関数の意味をざっくりと説明すると「２で割ってあまりが０になる行番号」を表す関数になります。さらにざっくり言えば、偶数の行に条件を適用するという意味になります。
関数を入力して、書式を設定すればこの通り、一行ごとに色分けされたセルが自動的に入力されます。
一番最初の行に色をつけたかったら、最初の行番号が偶数になるように、表の位置を調整しましょう。
関数を使った条件付けを利用した、便利な自動入力は他にもあります。
日付を入力する際、土日を色づけすることができます。
まずは条件を付けたい範囲のセルを選択。
「条件付き書式」のメニューから新しい書式ルールをクリックして、ウィンドウを出します。
「一番下の数式を使用して、書式設定セルを決定」をクリックして、現れた「次の数式を満たす場合に値を書式設定」の欄に「＝WEEKDAY(A2,2)>=6」と入力します。
関数は半角英数であれば大文字でも小文字でも構いません
この関数の意味は後で解説するとして、
OKをクリックする前に、書式をクリックして塗りつぶしたい色をちゃんと設定しましょう。
色を決めてから「OK」をクリックすればこのように、土日に色を自動的に塗りつぶすことができます。ちなみにこの表では２０１９年７月の日付に従って色付けされています。
さて、この「＝WEEKDAY(A2,2)>=6」という数式について説明しますと、まずWEEKDAYとは、PCのカレンダーを参考にして曜日を計算する関数です。（A2,2)のA2とは、選択範囲の一番上のセルのことです。この部分は選択したセルの位置によって変える必要があります。
２はWEEKDAY関数には３つの種類があり、そのうちの２番目を適用するという意味の２です。これは月曜から日曜までの曜日に１～７の数字を当てはめて計算するという意味になります。
「１」の場合は土曜を「１」として日曜までに１～７までの数字を当てはめる。３の場合は月曜日は「０」として日曜までに０～６の数字を当てはめる式になるぞ。
そして「＞＝６」とは「≧６」という意味で、６以上の値であることが条件という意味になります。この関数では土曜と日曜にそれぞれ、「６」と「７」という数字が当てはめられているため、土曜と日曜の日付が条件になるという意味になるのです。
ややこしいけど、数式の意味を知っておけば理解が早くなると思うぞ。
