社会福祉法人Sの会様の講座の続きを書きます。
この問題は前回紹介しましたように、日経BP社発行の雑誌「PC21」の腕自慢大会（1999年2月号？）に掲載された問題で、非常に実務的でいい問題だっとので、講座での使用許諾のお願いをしたところ、日経BP様のご好意により使えるようになった問題をもとに変形した問題です。
Sの会講座から（エクセル1）、Sの会講座から（エクセル2）では、
上の表のように、該当する時間には「■■■」を表示しましたが、今回は塗りつぶしてしまうという問題です。
どのような機能を使うか、条件付き書式を使えばいいことはご存じのとおりです。
塗りつぶすだけなら条件付き書式を設定すれば済むのですが、その時間帯に勤務可能な人、合計人数をカウントする必要があります。
マクロを使えばできるようですが、マクロを知らなくてもできます。
セル[D2]で考えると条件「AND($B2<=D$1,$C2>D$1)」を満足するときに、カウントできる文字や数字を表示させればいいですよね。
一番簡単な方法は数字の「1」を表示させれば、SUM関数を使えるので楽かもしれません。
最もわかりやすい方法で説明すると、セル[D2]に「=IF(AND($B2<=D$1,$C2>D$1),1,"")」と入力してそのほかのセルにコピーします。
このとき、「１」の両側にダブルコーテーションがついていませんが、それは「1」を数字として扱うためです。もし両側にダブルコーテーションをつめると、文字列の「1」として扱われます。
上の表のようになるので、この状態で条件付き書式を設定してみます。
セル範囲［D2:R9］を選択して、[ホーム]タブ→[スタイル]グループの[条件付き書式]クリック、[新しいルール]クリックして開く、[新しい書式ルール]
ダイアログボックスが開きます。
[ルールの種類]として[数式を使用して書式設定するセルを決定]を選択、[次の数式を満たす場合に値を書式設定]欄に「=D2=1」と入力、さらに[書式]クリックして塗りつぶしの色を選択して[OK]クリックすれば条件に合致するセルが塗りつぶされます。
しかしながら、「1」が表示されてしまっています。
この「1」を表示させないようにするには？
よくつかわれる方法は「1」を塗りつぶした色を同じ色にすることですよね。表示されているが、見えないようにしてしまうわけです。
もうひとつは「1」を表示させない方法です。
セル範囲［D2:R9］を選択して、Ctrl+１キーを押して、開く［セルの書式設定］ダイアログボックスで[表示型式]タブを選択、[種類]の欄に「;;](
セミコロン2つ→数字を非表示）、あるいは「;;;」(セミコロン3つ→数字も文字も非表示）を入力して[OK]クリックすれば、「1」を非表示にすることができます。
Sの会でもこの問題を生徒のみなさんにやってもらいました。
Y君はノーヒントで正解しました。彼はセル[D2]に「=IF(AND($B2<=D$1,$C2>D$1),1,"")」と入力していたので、「1」の両側にダブルコーテーションをつけたらどうなるか聞いてみたら、文字列の「1」になります。ちゃんと理解していました。さらに「1」を表示させない方法で、［セルの書式設定］ダイアログボックスで上に説明した「;;]を使って非表示にしてました。見事なものです。
ほかの諸君もヒントを与えたので、ほとんどの人が正解しました。
あとは、時間体ごとに該当する人の合計をさせばいいわけですが、数字の「1」の場合はSUM関数で合計すればいいわけなので、簡単ですが、文字列の「1」の場合はすこしばかりやっかいです。まず、条件付き書記設定する際に、[次の数式を満たす場合に値を書式設定]欄に「=D2="1"」と入力する必要があり、さらに「1」を表示させないようにする際に、塗りつぶした色と同色にすらばいいのですが、非表示にする場合には[セルの書式設定]ダイアログボックスで「;;;」トする必要があります。さらにSUM関数は使えず、「=COUNTIF(D2:D9,"1")」とCOUNTIF関数を使う必要があります。この場合には「1」の両側のダブルコーテーションは省いてもいいようですが・・・つけた方が無難です。
上の例では「=IF(AND($B2<=D$1,$C2>D$1),1,"")」を使いましたが、「=($B2<=D$1)*($C2>D$1」を使ってもOKです。
この場合には以前で説明したことがありますが、条件式を加減乗除に使用すると、満足する場合（TRUE)「1」として、満足しない場合(FALSE)[0]として扱われますので、両方の条件を満足する場合のみその時間に勤務可能となり「1」となりますが、それ以外は「0」になります。
すなわち勤務可能な場合のみ「1」ですが、それ以外のセルはすべて「0」になります。
したがって、条件付き書式はセルの値が1の場合に書式を設定し、「1」と「0」を非表示にするのには、［セルの書式設定］ダイアログボックスで、「;;]、あるいは「;;;」を入力すれば非表示にすればOKです。
この問題は日経BP社のご好意により、使わせていただいた問題ですが、実用的でいろいろ示唆に富んだ問題だったので3回にわたり説明させていただきました。
「MOS受験に向けて」はグラフなどまだのこっています。
次回以降、できるだけ早い時期に続きを書くつもりです。
この問題は前回紹介しましたように、日経BP社発行の雑誌「PC21」の腕自慢大会（1999年2月号？）に掲載された問題で、非常に実務的でいい問題だっとので、講座での使用許諾のお願いをしたところ、日経BP様のご好意により使えるようになった問題をもとに変形した問題です。
Sの会講座から（エクセル1）、Sの会講座から（エクセル2）では、
上の表のように、該当する時間には「■■■」を表示しましたが、今回は塗りつぶしてしまうという問題です。
どのような機能を使うか、条件付き書式を使えばいいことはご存じのとおりです。
塗りつぶすだけなら条件付き書式を設定すれば済むのですが、その時間帯に勤務可能な人、合計人数をカウントする必要があります。
マクロを使えばできるようですが、マクロを知らなくてもできます。
セル[D2]で考えると条件「AND($B2<=D$1,$C2>D$1)」を満足するときに、カウントできる文字や数字を表示させればいいですよね。
一番簡単な方法は数字の「1」を表示させれば、SUM関数を使えるので楽かもしれません。
最もわかりやすい方法で説明すると、セル[D2]に「=IF(AND($B2<=D$1,$C2>D$1),1,"")」と入力してそのほかのセルにコピーします。
このとき、「１」の両側にダブルコーテーションがついていませんが、それは「1」を数字として扱うためです。もし両側にダブルコーテーションをつめると、文字列の「1」として扱われます。
上の表のようになるので、この状態で条件付き書式を設定してみます。
セル範囲［D2:R9］を選択して、[ホーム]タブ→[スタイル]グループの[条件付き書式]クリック、[新しいルール]クリックして開く、[新しい書式ルール]
ダイアログボックスが開きます。
[ルールの種類]として[数式を使用して書式設定するセルを決定]を選択、[次の数式を満たす場合に値を書式設定]欄に「=D2=1」と入力、さらに[書式]クリックして塗りつぶしの色を選択して[OK]クリックすれば条件に合致するセルが塗りつぶされます。
しかしながら、「1」が表示されてしまっています。
この「1」を表示させないようにするには？
よくつかわれる方法は「1」を塗りつぶした色を同じ色にすることですよね。表示されているが、見えないようにしてしまうわけです。
もうひとつは「1」を表示させない方法です。
セル範囲［D2:R9］を選択して、Ctrl+１キーを押して、開く［セルの書式設定］ダイアログボックスで[表示型式]タブを選択、[種類]の欄に「;;](
セミコロン2つ→数字を非表示）、あるいは「;;;」(セミコロン3つ→数字も文字も非表示）を入力して[OK]クリックすれば、「1」を非表示にすることができます。
Sの会でもこの問題を生徒のみなさんにやってもらいました。
Y君はノーヒントで正解しました。彼はセル[D2]に「=IF(AND($B2<=D$1,$C2>D$1),1,"")」と入力していたので、「1」の両側にダブルコーテーションをつけたらどうなるか聞いてみたら、文字列の「1」になります。ちゃんと理解していました。さらに「1」を表示させない方法で、［セルの書式設定］ダイアログボックスで上に説明した「;;]を使って非表示にしてました。見事なものです。
ほかの諸君もヒントを与えたので、ほとんどの人が正解しました。
あとは、時間体ごとに該当する人の合計をさせばいいわけですが、数字の「1」の場合はSUM関数で合計すればいいわけなので、簡単ですが、文字列の「1」の場合はすこしばかりやっかいです。まず、条件付き書記設定する際に、[次の数式を満たす場合に値を書式設定]欄に「=D2="1"」と入力する必要があり、さらに「1」を表示させないようにする際に、塗りつぶした色と同色にすらばいいのですが、非表示にする場合には[セルの書式設定]ダイアログボックスで「;;;」トする必要があります。さらにSUM関数は使えず、「=COUNTIF(D2:D9,"1")」とCOUNTIF関数を使う必要があります。この場合には「1」の両側のダブルコーテーションは省いてもいいようですが・・・つけた方が無難です。
上の例では「=IF(AND($B2<=D$1,$C2>D$1),1,"")」を使いましたが、「=($B2<=D$1)*($C2>D$1」を使ってもOKです。
この場合には以前で説明したことがありますが、条件式を加減乗除に使用すると、満足する場合（TRUE)「1」として、満足しない場合(FALSE)[0]として扱われますので、両方の条件を満足する場合のみその時間に勤務可能となり「1」となりますが、それ以外は「0」になります。
すなわち勤務可能な場合のみ「1」ですが、それ以外のセルはすべて「0」になります。
したがって、条件付き書式はセルの値が1の場合に書式を設定し、「1」と「0」を非表示にするのには、［セルの書式設定］ダイアログボックスで、「;;]、あるいは「;;;」を入力すれば非表示にすればOKです。
この問題は日経BP社のご好意により、使わせていただいた問題ですが、実用的でいろいろ示唆に富んだ問題だったので3回にわたり説明させていただきました。
「MOS受験に向けて」はグラフなどまだのこっています。
次回以降、できるだけ早い時期に続きを書くつもりです。
※コメント投稿者のブログIDはブログ作成者のみに通知されます