エクセル

友人の女の子からの依頼です。

作業工程表を毎月作るのが面倒なので、年と月を選択する事で自動で土曜日は青、日曜日は赤で表示されるカレンダーを作りたいそうです。

普通のカレンダーのように１週間毎に行が変わるカレンダーを作るのは少々面倒ですが、縦一列または横一列の場合は簡単です。

例えばこんな感じです。

まず、考え方ですが、日付の数字は固定されています。
ですから、セルに直接数字を入力します。

日付の数字の下の曜日ですが、これは年と月によって変化します。
A1セルの年と、B1セルの月の名前と、日付の数字からDATE関数でシリアル値を返します。
そのシリアル値をセルの書式設定で日本語の曜日名で表示するように設定します。
Format関数を使う手もありますが、関数一覧には出てこないので今回は使いません。

曜日の文字色は、条件付書式を使って設定します。。

では、実際に作ってみましょう。

手順1
A1セルを選択します。
セルの書式設定を開きます。（メニュー書式＞セル　表示形式タブ）

分類から「ユーザー定義」を選択し、種類の下のテキストボックスに「#"年"」と入力します。
（「#」でも「0」でもいいです。「#」と「0」の違いは今回は割愛します。）

「OK」をクリックしてウィンドウを閉じます。
同様にB1セルにも「#"月"」と設定します。

これで、A1セルに「2008」と数字を入力するだけで、「2008年」と表示されるようになります。
「2008年」と表示されていますが、A1セルの値は「2008」という数字だけです。
これは、A1セルB1セルの値を数字だけにし、DATE関数の引数に使うためです。

手順2
日付の値を入力します。
「オートフィル」を使って、連続データを入力しましょう。
連続データの入力はhttp://hamachan.fun.cx/excel/renzoku.htmlのサイトを参考にしてください。
今回は横一列です。横一列はウィンドウ内にすべて表示されれないので面倒ですね。
余談ですが、Exelはマウスではなくて、キーボードで操作しなければ扱いづらいです。
途中でセルのコピーを行うので、セルの罫線は後から引いたほうが楽でしょう。

手順3
曜日を表示させます。
B4のセルに式を入力して、それをAF4までコピーしましょう。
B4のセルを選択して、数式バーの横の「fx」と書いてあるボタンを押して、「関数の挿入」ダイアログボックスを表示します。

「関数の検索」ボックスに「DATE」と入力して検索します。
「関数名」リストに「DATE」と表示されますので、それを選択して「OK」をクリックします。

「関数の引数」ダイアログボックスが表示されます。
「年」にはA1セルを参照します。「年」ボックスをクリックし、カーソルがボックス内に入ったのを確し、A1セルをクリックします。ここでA1セルは、絶対参照をしたいので「F4」キーを押します。すると「$A$1」と表示が切り替わります。
同様に、「月」のボックスに「$B$1」を入力します。

絶対参照と相対参照については、
掻い摘んでいうと、今B4セルに入力した式はこの後AF4セルまでコピーします。
コピーした際、相対参照だとセルが移動した分だけ、参照元のセルも移動します。
例えば、B4セルからA1セルを参照している式を、横に2つ移動したD4セルにコピーした場合、A1セルの参照は、横2つ移動してC1セルを参照するようになります。

ここでは、コピー先のどのセルからも「年」はA1セル、「月」はB1セルを参照したいのでA1セルとB1セルの参照は固定する必要があります。ですので、絶対参照を指定します。
絶対参照を指定するには、列を表すアルファベットの前と行を表す数字の前に「$」（ドルマーク）を入力します。ドルがついている方のみ絶対参照、つまり固定されます。
厳密には、今回は横にしかコピーをしないので行を固定する必要はありません。つまり数字の前の「$」は必要ありませんが、つけていても差し支えがないのでそのままにしています。
因みに、「F4」キーを繰り返し押せば「行列固定」「行のみ固定」「列のみ固定」「固定なし」と変化します。

「日」のボックスには「B3」を入力します。ボックスをクリックして、その後にセルをクリックすれば入力されますよ。このセルは固定（絶対参照）してはいけません。なぜだかわかりますね？。
例えば、式をE4セルにコピーしたら、E4セルから日付はE3セルの「4」を参照しなければなりません。ですので、コピー先のセルに合わせて、参照元のセルも移動してもらいたいから相対参照にします。

全部入力できたら、ここを確認してみてください。

「39448」と表示されていますよね。
この「39448」が日付を表すシリアル値です。
Excelでは、日付を1900年1月1日を「１」とする、数字で日付を管理しています。
ですので、「2008年1月1日」はその日から数えて、39448日目ということです。

では「OK」をクリックします。
B4セルには「2008/1/1」と表示されているはずです。
これは、先ほどのシリアル値「39448」を表示形式で「2008/1/1」と表示するように設定されているからです。この設定はExcelが自動でします。Excelは「日付のデータ」だなと思ったら、自動で表示形式を変更します。試しに、「セルの書式設定」の「表示形式」で確認してみてください。「日付」になっているはずです。

この「表示形式」を変更して、シリアル値を曜日名で表示するように設定します。
「セルの書式設定」の「表示形式」をタブをクリックし、「分類」リストから「ユーザー定義」を選択します。
「種類」の下のテキストボックスに、今度は「aaa」と入力します。上のサンプルに「火」と表示されるはずです。

では次に、B4セルをAF4セルまでコピーします。すると、こんな感じ。

手順4
曜日によって、日付の文字色を変更します。
まず、曜日によって文字色を変更したいセル範囲を選択します。
もし、下の行まで文字色を変更したいなら、そこも選択範囲に指定してください。
注意が必要なのはB3セルがアクティブな状態にしておいてくさい。（厳密には、B列のセルなら、どのセルでも構いませんが）

アクティブな状態とは、選択範囲は青く表示されますが、その中に一つだけ白いセルがあると思います。それが、アクティブな状態のセルです。

メニュー「書式」＞「条件付き書式」をクリック
「条件付き書式」ダイアログボックスが表示されます。

「セルの値が」と表示されているコンボボックスがありますので、そこを「数式が」に変更します。

その右のテキストボックスに「=weekday(B$4,1)=1」と数式を入力します。
「この数式が成り立つなら」という条件になります。
ここで、また相対参照、絶対参照が出てきます。
ここでは、条件付き書式を一つのセルに対してではなく、複数のセルに同時に設定しようとしています。

今回、B3セルをアクティブな状態で条件付き書式を設定しています。
複数のセルを選択して条件付き書式を設定すると、まずアクティブセルのB3セルに書式を設定し、その後書式をコピーするという手順を一度で行う事ができます。
しかし、セルの表示設定もコピーされてしまうので、もう一度設定し直さなければならなくなるでしょう。
ですので、今回は複数のセルに同時に条件付き書式を設定します。

しかし、考え方としてはまずB3に条件式を設定し、それをAF4までコピーするわけですから、AF4セルの判定は、B4セルに表示されている曜日ではなくて、AF4に表示されている曜日で判定したいわけです。
B3セルとAF4セルは、横に移動しているし、縦にも移動しています。しかし、参照するセルは、横には移動しますが、縦には移動しません。
ですので、列は固定せず、行だけを固定します。
したがって、「=weekday(B$4,1)=1」となるわけです。

WEEKDAY関数の説明
WEEKDAY関数は引数を2つ取ります。
第1引数には、日付を表すシリアル値。第2引数には、曜日番号のパターンを指定します。
WEEKDAY関数の曜日番号のパターン

普通は1または省略を使っていればいいと思います。
というか、固定していないと直ぐに頭がごちゃごちゃになってしまします。

WEEKDAY関数は理解しましたか？つまり、「=weekday(B$4,1)=1」は
「B4セルが日曜日だったら」という条件になります。

では、次に「書式」ボタンをクリックして書式を設定します。
今回は、赤文字で表示したいので、「フォント」タブの「色」で赤を選択して、「OK」をクリックします。

追加をクリックして、同様に土曜日の条件も設定します。
条件式は「=weekday(B$4,1)=7」ですね。
上の式をコピーすると楽ですよ。
コピーできない？
式を選択して「Ctrl」＋「C」キーでコピーできますよ。
貼り付けは「Ctrl」＋「V」です。
土曜日の書式設定ができたら、「OK」をクリックします。

後は、B1セルに入力規則を設定して、ドロップダウンで選択するように工夫をすると使い勝手が良くなるでしょう。

しかし、30日までしかない月や、2月も31日まで表示されるのは頂けませんね。
では、もう一工夫しましょう。

これには、まずその月の最終日のシリアル値を得る必要があります。
そして、その最終日のシリアル値と比較して、表示・非表示を振り分けましょう。

まず、最終日のシリアル値の取得です。
どこか適当なセルを、作業セルにします。僕はA2にしました。
ここに、最終日のシリアル値を返す式を入力します。
考え方は、翌月の1日から、1日戻るです。
翌月の1日のシリアル値を得る式は、
「=DATE($A$1,$B$1+1,1)」です。
第2引数の「月」に1を加えています。

それから、1日戻るので
「=DATE($A$1,$B$1+1,1)-1」
これをDAY関数を使って日付の数字だけを取り出します。
「=DAY(DATE($A$1,$B$1+1,1)-1)」
変な日付が、表示された場合は「セルの書式設定」で「表示形式」を「標準」に設定してください。
B1セルの値を変化させると「30」「31」と変化します？

では、次に29日、30日、31日のセルに式を代入します。
まずは29日のAD3セルから
「=IF(29>$A$2,"",29)」

では、AE3セルとAF3セルにもコピーしますが、コピーした後に
29の部分を30、31に書き換えてくださいね。

では、その下の曜日が表示されているセルの式も書き換えましょう。
日付のセルと同じ条件を使ってもいいです。
「=IF(29>$A$2,"",DATE($A$1,$B$1,AD3))」
でも、長いし、コピーした後にまた書き換えなきゃならないので、
次のようにしました。
「=IF(AD3="","",DATE($A$1,$B$1,AD3))」
これが、「29日」の曜日のセル、つまりAD4セルの式です。

これを、そのまま横にコピーすれば、30日、31日も設定終わりです。

これで後は罫線を引きましょうか。

因みに、罫線の表示非表示も条件付き書式で設定できます。
ただ、一番最後のセルの外枠を太線にする場合、もう一つ条件が必要です。
条件付き書式は3つまでしか、設定できないので、そこは無理です。
VBAを使うことになりますね。

投稿日:2008年01月14日|カテゴリー:エクセル