Excel。関東と東北のデータ全体を手早く塗りつぶしたい【Format】

Excel。関東と東北のデータ全体を手早く塗りつぶしたい

＜条件付き書式＋OR関数＞

売上表があります。

C列には地域が入力されています。

この地域が、関東と東北ならば、該当するデータ全体。

つまり、行全体をわかりやすいように、手早く塗りつぶしたいわけです。

このような場合、どのようにしたらいいのでしょうか。

関東と東北という条件があって、塗りつぶしたいという書式のリクエストということから、使う機能は「条件付き書式」ということになります。

あとは、条件式をどのようにするのかということですが、関東と東北ということで、同じ列内のデータですから、「関東または東北」というOR条件の条件式をつくればいいわけです。

OR関数をつかった条件式をつくります。

では、A2:D9を範囲選択して、ホームタブの条件付き書式にある、「新しいルール」をクリックします。

新しい書式ルールダイアログボックスが表示されます。

「数式を使用して、書式設定するセルを決定」を選択したら、条件式を設定します。

条件式は、

=or($c2="関東",$c2="東北")

あとは、書式ボタンをクリックして、塗りつぶしたい色を設定します。

設定後、新しい書式ルールダイアログボックスに戻ってきますので、OKボタンをクリックします。

これで、地域が、関東または東北　という条件で、行全体を塗りつぶすことができました。

では、条件式を確認しておきましょう。

=OR($C2="関東",$C2="東北")

OR関数なので、引数を関東と東北と設定するだけです。

なお、C2ではなくて、$C2とすることで、行全体を対象とすることができます。

さて、この数式、IF関数をつかっておりません。

なんとなく、IF+OR関数をつかった条件式にするように思えますが、

=OR($C2="関東",$C2="東北")

この式が成立したらTRUE。成立しなければFALSEと判断されます。

TRUEならば、条件成立ということで、塗りつぶしをしてくれます。

そのため、IF関数は不要ということになります。

Excel。入力できるセルはどこ。シートの保護で入力できるセルをわかるようにしたい【Protecting a Sheet】

Excel。入力できるセルはどこ。シートの保護で入力できるセルをわかるようにしたい

＜シートの保護・条件付き書式・CELL関数＞

シートの保護をすることで、セルに入力することを防ぐことができます。

ただ、単純にシートの保護をおこなってしまうと、シート全体に保護がされてしまうので、全く入力することができません。

そこで、一部だけ入力することができるようにするのが、「セルのロック」をオフにします。

なお、シートの保護も、セルのロックも、ホームタブの書式の中にあります。

セルのロックをオフにして解除したら、シートの保護をおこないます。

これで、セルのロックをオフにしたセルのみ入力することができるのですが、見た目、どこのセルに入力することができるのか、全くわかりません。

そこで、セルのロックをオフにしているセルがわかるように、セルを塗りつぶしたいわけですが、どのようにしたいいのでしょうか。

セルのロックがオフという条件ということなので、条件付き書式をつかいます。

問題となるは、条件付き書式の条件をどのようにしたらいいのかということです。

セルの状態を確認するには、CELL関数をつかうことで対応することができます。

では、条件付き書式を設定していきましょう。

シート全体を範囲選択します。

ホームタブの条件付き書式にある、新しいルールをクリックします。

新しいルールダイアログボックスが表示されます。

数式を使用して、書式設定するセルを決定　を選択します。

条件式を設定します。

=cell(“protect”,a1)=0

あとは、塗りつぶしたい色を設定したら、OKボタンをクリックします。

これで、シートの保護がされても、入力することができる、ロックの解除がオフのセルを見つけることができました。

条件式にCELL関数をつかっていますが、その数式を確認しておきましょう。

CELL関数は、セルの状況を確認することができる関数です。

引数に、protect　をつかうことで、セルのロックがオンかオフかを確認することができます。

1ならば、セルのロックはオンになっています。

よって、今回はオフなのを探したいので、「=0」とします。

Excel。2列1組で塗り分けるにはどうしたらいい【column】

Excel。2列1組で塗り分けるにはどうしたらいい

＜条件付き書式＋MOD+COLUMN関数＞

2列で1組になっている表があります。

わかりにくいので、次のように塗りつぶしをしたいのですが、どのようにしたら、手早く設定することができるのでしょうか。

 結合したセルもあるので、テーブルにすることもできませんので、単純に列方向で縞々というわけにはいきません。

このような場合、条件付き書式をつかうことで解決できます。

ただし、どのような条件式を設定したらいいのかが、ポイントになります。

そこで、列番号をつかうことで、対応することができます。

列番号を求めるのは、COLUMN関数です。

では、8行目にCOLUMN関数をつかって列番号を求めてみます。

B8に設定した数式は、

=COLUMN()

オートフィルで数式をコピーしています。

これで、列番号を求めることができました。

求めた値を4で除算して、その余りを求めてみます。

余りを求めるのはMOD関数です。

9行目に

=MOD(B8,4)

というMOD関数の数式をつくり、オートフィルで数式をコピーしてみましょう。

これで、0,1,2,3　という余りを求めることができました。

よくみてみると、0,1　と　2,3　がペアになっていることがわかります。

MOD関数の値が2より小さければという条件にすることで、2列1組で塗りつぶすことができそうです。

改めて、B1:I6を範囲選択します。

ホームタブの条件付き書式にある、「新しいルール」をクリックします。

新しい書式ルールダイアログボックスが表示されます。

「数式を使用して、書式設定するセルを決定」を選択します。

条件式のボックスには、

=mod(column(),4)<2

2より小さいとすることで、余りが0,1の組を対象として塗りつぶす条件式にしました。

あとは、書式ボタンをクリックして、塗りつぶしたい色を選択しましょう。

 

これで完成です。

Excel。最大値のデータのある行全体を塗りつぶしたい。【MAX】

Excel。最大値のデータのある行全体を塗りつぶしたい。

＜条件付き書式＋MAX関数＞

最大値のデータを見つけるには、条件付き書式にある、「上位10項目」をつかうことで手早く書式を設定することができます。

ただ、該当するデータのみなので、大きな表だった場合、どのデータなのかわかりにくいことがあります。

そこで、行全体を塗りつぶすことで、わかりやすくすることができます。

今回用意したのが、次の表です。

D列の販売金額の最大値の行全体を塗りつぶしたいわけです。

そこで、ホームタブの条件付き書式にある「新しいルール」をつかって設定をします。

A2:D8を範囲選択します。

ホームタブの条件付き書式にある「新しいルール」を選択します。

新しいルール書式設定ダイアログボックスが表示されます。

「数式を使用して、書式設定するセルを決定」を選択して、条件のボックスに数式を設定します。

=MAX($D$2:$D$8)=$D2

あとは、書式ボタンをクリックして、塗りつぶしたい色を設定したら、OKボタンをクリックします。

これで、最大値のデータ。行全体を塗りつぶすことができました。

では、設定した条件式を確認してみましょう。

=MAX($D$2:$D$8)=$D2

最大値を算出する必要がありますので、MAX関数を使います。

その値が、D2と等しいのかを確認します。

条件に合致すれば、1。

合致しなければ0を返します。

Excelでは、1=TRUEなので、条件を満たしたということですから、書式が反映されるというわけです。

なお、$D2という列固定の複合参照にすることで、行全体を対象にすることができます。

Excel。条件付き書式のカラースケールの色をアレンジしたい【Color scale】

Excel。条件付き書式のカラースケールの色をアレンジしたい

＜条件付き書式＞

ホームタブにある条件付き書式。

その中に、カラースケールというのが用意されています。

「緑・黄・赤のカラースケール」を設定してみると、数値の大小で色分けしてくれるという条件付き書式です。

数値の大小はわかりやすくなるのですが、用意されている色が、資料のイメージ合わないなど、変更したい場合、どのようにしたらいいのでしょうか。

確かに、単色のグラデーションは用意されていますが、種類も少ないですし、緑・黄・赤であっても、ちょっと派手な感じもします。

カラースケールを設定した後に、再度範囲選択をして、ホームタブの条件付き書式にある「ルールの管理」をクリックします。

条件付き書式ルールの管理ダイアログボックスが表示されます。

設定してあるカラースケールを選択して、ルールの編集ボタンをクリックします。

書式ルールの編集ダイアログボックスが表示されます。

ルールの内容を編集します。

最小値・中間値・最大値のそれぞれの色を設定します。

用意されていない、紫色のグラデーションにしてみます。

設定が終わりましたら、OKボタンをクリックします。

条件付き書式ルールの管理ダイアログボックスに戻ります。

改めてOKボタンをクリックします。

カラースケールをアレンジすることができました。

Excel。前年比で、今年のほうが大きければ、行全体を塗りつぶしたい【Year-on-year change】

Excel。前年比で、今年のほうが大きければ、行全体を塗りつぶしたい

＜条件付き書式＞

今年と前年の売上金額の一覧表があります。

今回のやりたいことは、数値を比べて2025年（今年）のほうが大きければ、レコード全体（行全体）を塗りつぶして、視覚的にわかりやすくしたい。

条件が合致したら、塗りつぶすということですから、条件付き書式をつかえばいいわけです。

あとは、条件式。

ここは、単純に比較演算子をつかった比較で対応します。

では、A2:C5を範囲選択します。

ホームタブの条件付き書式から「新しいルール」を選択します。

行全体を対象にする場合には、用意されているプリセットでは対応できませんので、新しいルールで条件式を作る必要があります。

新しい書式ルールダイアログボックスが表示されます。

条件式に、

=$B2>$C2

と設定します。

書式ボタンをクリックします。

塗りつぶしの色を設定してOKボタンをクリックすると、元のダイアログボックスに戻りますので、改めて、OKボタンをクリックします。

これで、2025年（今年）のほうが大きいデータ全体を塗りつぶすことができました。

設定した、条件式

=$B2>$C2

ですが、列固定の複合参照にすることで、行全体を対象にすることができます。

そして、比較しているわけですが、IF関数とか使う必要はありません。

=$B2>$C2

という条件が成立しているならば、TRUE。

成立していなければFALSEと判断されます。

TRUEならば、条件に合致していますから、行全体が塗りつぶされるという仕組です。

Excel。カレンダー。隔週ごとに手早く塗りつぶしたいけど、どうしたらいい【Every other week】

Excel。カレンダー。隔週ごとに手早く塗りつぶしたいけど、どうしたらいい

＜条件付き書式＋MOD+WEEKNUM関数＞

次のような簡易カレンダーがあります。

わかりやすいように、週ごと。隔週で塗り分けたいと考えました。

では、どのようにしたら、手早く隔週で塗り分けることができるのでしょうか。

隔週という条件なので、条件付き書式をつかいます。

そこで、問題になるのが、条件式です。

隔週をどのように確認したらいいのでしょうか。

週番号を求めることができる、WEEKNUM関数というのがあります。

求めた週番号が、奇数か偶数かを求めれば、隔週で塗り分けることができそうです。

奇数か偶数かを求めるには、除算した余りを求めることができるMOD関数をつかいます。

早速、設定していきます。

A2:B16を範囲選択します。

ホームタブの条件付き書式から「新しいルール」を選択します。

新しい書式ルールダイアログボックスが表示されます。

「数式を使用して、書式設定するセルを決定」をクリックして、条件式を設定します。

条件式のボックスには、

=mod(weeknum($A2,1),2)

と数式を設定したら、塗りつぶしたい色を設定して、OKボタンをクリックします。

ご覧のように、隔週で塗り分けることができました。

条件式の確認をしましょう。

数式全体ですが、

=mod(weeknum($A2,1),2)

数式の最後に「=0」をつけてもいいのですが、省略しています。

=mod(weeknum($A2,1),2)

この数式が成立しているならば、「1」と算出され、TRUEという判断になります。

また、成立していないのなら「0」と算出され、FALSEという判断になります。

このため、「=0」は省略することができます。

MOD関数は、除算した余りを求めることができます。

2で除算することで、奇数か偶数かを求めることができます。

WEEKNUM関数は、週番号を求めることができます。

最初の引数は、シリアル値。日付です。

列固定の複合参照にすることで、行全体を対象にすることができます。

2つ目の引数は、「週の基準」です。

今回は、1をつかいました。1は、週の最初を日曜日として設定することができます。

用途によって、基準を変えることで、最初の曜日に対応させることができます。

このWEEKNUM関数ですが、週の基準の21をみてみると、（システム2）と表示されています。

このシステム1とシステム2の違いは何かというと、システム2は、その年の最初の木曜日を含む週をその年の第1週の場合でつかうことができるというのが、システム2です。

外資系の企業さんで使うことが多いのが、システム2のようです。

これらの関数を組み合わせてつかうことで、隔週で塗り分けることができる条件式を設定することができます。

Excel。罫線を5行ごとに区切りとわかるように罫線を変更したい【Border】

Excel。罫線を5行ごとに区切りとわかるように罫線を変更したい

＜条件付き書式+MOD+ROW関数＞

表に格子の罫線を設定しました。

大きな表だと、区切りがわかりにくいので、5件（5行）ごとに罫線を変更することにしました。

ただ、どのようにしたら、手早く設定できるのでしょうか。

そこで、条件付き書式をつかって、対応することにしました。

条件付き書式は、塗りつぶし以外にも、罫線を設定することができるからです。

A2:B11を範囲選択します。

ホームタブの条件付き書式にある「新しいルール」をクリックします。

新しい書式ルールダイアログボックスが表示されます。

「数式を使用して、書式設定するセルを決定」を選択して、条件式を設定します。

=mod(row(),5)=1

書式ボタンをクリックします。

セルの書式設定ダイアログボックスの罫線タブに移動して、任意の線種を設定します。

今回は、わかりやすいように、赤色にしてみました。

そして、罫線の下線ボタンをクリックして設定したら、OKボタンをクリックします。

新しいルールダイアログボックスに戻りましたら、OKボタンをクリックして、完成です。

 

5件後に、設定した罫線に変わったことが確認できました。

ただ、用意されているものでないといけないので、異なったデザインで罫線を設定したい場合にはExcel VBAで対応するのもいいかもしれません。

さて、条件式を確認しておきましょう。

=mod(row(),5)=1

MOD関数は、除算の余りを算出する関数です。

ROW関数は、行番号を求めることができる関数です。

この2つの関数と条件付き書式で、一行おきに塗り分けるという時に、よく使うのですが、そのアレンジで、罫線にしてみました。

行番号を、5で割って、余りが1だったら、罫線を変更するということができるというわけです。

Excel。範囲のすべてが空白の場合、行単位で塗りつぶしをしたい【All blank】

Excel。範囲のすべてが空白の場合、行単位で塗りつぶしをしたい

＜条件付き書式＋CONCAT関数＞

データの全てが空白の場合、わかりやすいように、そのデータ全体を塗りつぶして確認できるようにしたいのですが、どのようにしたら、いいのでしょうか。

今回はどのような表を用意しました。

1回目から5回目のデータすべてが空白だった、そのデータ全体、つまり行全体を塗りつぶしたいわけです。

条件で塗りつぶすということから、条件付き書式をつかうわけです。

そこで、問題になるのが、条件式です。

AND条件で、セルひとつずつが、空白かどうかを確認するのは、大変です。

また、COUNTBLANK関数で、空白の件数を求めて、全体の件数と空白の件数が合致するのかを確認するというのも、面倒です。

そこで、文字結合のCONCAT関数をつかうことで、楽に条件式を設定することができます。

A2:F7を範囲選択します。

ホームタブの条件付き書式にある「新しいルール」をクリックします。

すると、新しい書式ルールダイアログボックスが表示されます

「数式を使用して、書式設定するセルを決定」を選択したら、条件式を設定します。

=concat($B2:$F2)=""

あとは、書式ボタンをクリックして、塗りつぶしたい色を設定したら、OKボタンをクリックして、完成です。

条件式を確認しておきましょう。

=concat($B2:$F2)=""

ポイントは、引数です。

$B2:$F2　の列固定した複合参照にすることで、行全体を対象にすることができます。

CONCAT関数は、セル結合なので、結合した結果、空白ならばという条件式をつくればいいわけです。

なお、行固定の複合参照にすれば、列を対象に塗りつぶすことができます。

Excel。文字列の一部が該当したら行全体をぬりつぶしたい【Fill】

Excel。文字列の一部が該当したら行全体をぬりつぶしたい

＜条件付き書式+FIND関数＞

B列の住所。

その中で、横浜市ならば、行全体を塗りつぶしたいのですが、どのようにしたらいいのでしょうか。

横浜市ならば、塗りつぶすということは、条件付き書式をつかって対応します。

あと、問題になってくるのが、条件式です。

どのような条件式を作ればいいのでしょうか。

横浜市ならばということですから、横浜市をどのように確認させるかということになります。

ただ、住所の列は、横浜市　だけではなく、都道府県を含め、市区町村などもすべて含まれているデータです。

横浜市　という条件。

つまり完全一致のデータではなく、部分一致の条件式をどのように作るのかというのが、ポイントになるわけです。

さて、部分一致ということならば、「ワイルドカード」をつかいたくなりますが、今回は、文字を検索する「FIND関数」だけをつかって、対応していきます。

A2:B6を範囲選択します。

ホームタブの条件付き書式にある、「新しいルール」をクリックします。

新しい書式ルールダイアログボックスが表示されます。

「数式を使用して、書式設定するセルを決定」をクリックして、条件式を設定します。

=FIND("横浜市",$B2)

あとは、書式ボタンをクリックして、セルを塗りつぶす色を設定します。

OKボタンをクリックして、完成です。

このように、横浜市が含まれている行全体を塗りつぶすことができました。

では、条件式を確認しておきます。

=FIND("横浜市",$B2)

FIND関数は、セル内に、最初の引数で設定した文字が最初に出てくる文字数を返す。

すなわち、含まれているかを確認することができる関数です。

最初の引数は、「横浜市」と設定します。

2つ目の引数で、検索対象のセルを設定します。

また、列固定の複合参照にすることで、行全体を対象にすることができます。

行全体を塗りつぶすことができるというわけです。

Excel。条件付き書式の順番を注意しないと、すべて同じ書式になってしまう。【order】

Excel。条件付き書式の順番を注意しないと、すべて同じ書式になってしまう。

＜条件付き書式＞

次の得点表。

B列の得点を、次の条件で塗りつぶすことで、わかりやすくしたいと思います。

その条件は、得点が50以上なら赤色で80以上なら緑色に設定します。

では、B2:B11を範囲選択して、ホームタブの条件付き書式にある、新しいルールをクリックします。

今回の条件が以上なので、用意されているものでは対応できません。

新しいルールダイアログボックスが表示されます。

「指定の値を含むセルだけを書式設定」をクリックして、条件を設定していきます。

セルの値　が　次の値以上　を選び、80と設定します。

あとは、書式ボタンをクリックして、塗りつぶす色を設定します。

設定が完了したら、OKボタンをクリックします。

80以上が塗りつぶされました。

同じように50以上を塗りつぶしてみましょう。

それでは、OKボタンをクリックします。

すると、先程、80以上で塗りつぶされていたセルまで、50以上の色で塗りつぶされてしまいました。

どうしてこのようになってしまったのかというと、優先順位は、あとから設定した条件の方が上になるようになっているからです。

そこで、このような場合、条件の順位を変えることで、対応することができます。

条件付き書式の「ルールの管理」をクリックして、条件付き書式ルールの管理ダイアログボックスを表示します。

条件の順番を変えるボタンをつかって、優先順位を設定したら、OKボタンをクリックします。

これで、希望通りの条件でセルを塗りつぶすことができました。

設定したのに、おかしい場合には、優先順位が影響している可能性があります。

その場合は、確認してみるといいかもしれません。