●結果を参照して1以下を非表示にする
セルC5〜C11に表示している日数をそのまま使用すれば、0や負数が日数として表示されてしまう。これでは少し違和感があるので、0や負数を表示しないように工夫する。IF関数を使って1以下を判断して日数を表示するかどうかを使い分ければよい。セルE5に入力する数式を例にすれば次のようになる。
=IF(C5<1,””,C5)
↑ ↑ ↑
1以下を判断 ↑ 1または以上のとき日数を表示(セルC5を参照する)
1以下のとき何も表示しない
この数式を下へ続くセルE6〜E11へ複写すればよい。単純なコピーと張り付けで操作すると罫線なども複写されるので、張り付けは[型式を選択して張り付け]の数式だけを指定すれば罫線などは複写されずに、数式だけが複写される。
r操作
@セルE5に数式「=IF(C5<1,"",C5)」を入力する
A1行目は−2なので非表示
BセルE6〜E11にも同様の数式を入力する
EX4C603Z
EX4C604Z EX4C605Z
●数式だけを複写する
セルE5に入力した数式を下に続くセルE6〜E11に複写する
r操作
@複写元セル(E5)をクリックして選択する
A[コピー]をクリックする
B複写先のセル範囲(E6〜E11)を選択する
C選択したセル範囲内を右クリックしてショートカットメニューを開く
D[型式を選択して張り付け]を選択する
E型式を選択して張り付けダイアログが開く
F[数式]をクリックして●印を付ける
G[OK]をクリックする
H選択したセル範囲に数式だけが複写される
EX4C607Z EX4C608Z EX4C609Z
EX4C610Z
表A
●99年各月1日の曜日●
1月:金 7月:木
2月:月 8月:日
3月:月 9月:水
4月:木 10月:金
5月:土 11月:月
6月:火 12月:水
●1日の表示位置を自動化する
このままでもセルC5に入力する値で日数が自動表示されるが、もう一工夫して自動表示させてみよう。年月を指定すれば自動的に1日の位置が決められるようにする。方法としては、月の1日のシリアル値をDATE関数で求めて、求めたシリアル値からWEEKDAY関数を使って曜日の番号を求める。この値を利用してセルB5に入力する−5〜1の値を計算で求めればよい。どちらの関数も以前に使用したが、WEEKDAY関数は引数の種類を省略した書き方で紹介したが、種類を指定する書き方があるので、ここで紹介しておく。それぞれの書き方で曜日に対する番号が違う。その場に適した種類を選択するのだが、今回は、週のはじまりを月にしているので、月曜からはじまる種類3を使うことにする。
●WEEKDAY関数
シリアル値を曜日に変換する
書式 WEEKDAY(シリアル値, 種類)
シリアル値 日付を表す番号
種類 戻り値の種類を指定する
種類 戻り値
1/省略 1(日曜)〜7(土曜)の範囲の整数
2 1(月曜)〜7(日曜)の範囲の整数
3 0(月曜)〜6(日曜)の範囲の整数
※シリアル値
Excelで日付や時刻の計算に使用される連続番号のこと。シリアル値には、"1965/4/16"、"昭和40年4月16日"のような日付を表す文字列を指定することもできる。
まず、指定する年月の1日のシリアル値を求めるために、年を入力するセルと月を入力するセルを決めて、それぞれの値(年数と月数)を入力しておく。そして、その年数と月数からDATE関数を使ってシリアル値を求める。ここでは、例として年数をセルB1、月数をセルB2、シリアル値を求める数式をセルB3に入力する。DATE関数の日数に1を入力するのは、1日の曜日を求めるためだ。日付に関係する数式を入力すれば表示形式は自動的に日付に変わる場合がある。そのままの日付表示でも計算に差し支えはないが、ここでは計算用の値であることを明示するためにツールバーの[桁区切りスタイル]を使って数値表示に変えている。
=DATE(B1,B2,1) =WEEKDAY(B3,3)
↑ ↑ ↑ ↑ ↑
年数 月数 日数 シリアル値 種類
r操作[1日のシリアル値を求める]
@セルB1に年数「99」を入力する
AセルB2に月数「7」を入力する
BセルB3に数式「=DATE(B1,B2,1)」を入力する
C指定した日付が表示される
DセルB3にセルポインタを合わせて[桁区切りスタイル]をクリックする
E数値(シリアル値)で表示される
FセルB4に曜日番号を求める数式「=WEEKDAY(B3,3)」を入力する
G1日の曜日番号が求められる
EX4C612Z EX4C613Z
EX4C615Z EX4C616Z
EX4C617Z
求めた曜日番号からセルC5に入力する値(−2)を求める数式を作ればよい。方程式を解けば数式が求められるが、ここは算数教室ではないので結果だけを紹介しておく。数式は次のようになる「=1−B4」。セルB5に数式を入力して、セルC5に参照式「=B5」を入力して完成させよう。セルB2に入力している月数を書き替えて、各月ともに1日の位置が正しく表示されるのかを確かめておこう。参考までに1999年度の各月1日の曜日を表Aに載せておく。完成後はBとC列を非表示にしてじゃまにならないようにすればよい。
r操作
@セルB5に数式「=1−B4」を入力する
A計算結果が求められる
BセルC5に参照式「=B5」を入力して結果を反映させる
CセルB2の月数を書き替える
D指定月の1日の曜日位置が1になる
EX4C618Z EX4C619Z EX4C620Z
●先頭を日曜にする
週のはじまりを日曜にした集計表にする場合は、WEEKDAY関数の種類を1にして、位置調整の計算式も次のように変更すればよい。
セルB4 =WEEKDAY(B3,1) セルB5 =2-B4
EX4C621Z
●月数を参照する
集計表のタイトルとして表示している「月買掛金」の月数を表示するセルにセルB2を参照する数式を入力すれば、セルB2を書き替えるだけでタイトルの月数も自動的に更新されるので便利だ。少しの工夫で余計な手間を省けることもある。
EX4C633Z EX4C634Z
月末の日数を正しく表示する
月初めの0や負数の表示をIF関数で日表示にしたが、月末の表示も同じように処理できる。簡単に処理するならIF関数の条件式に31以上は表示しない、にすればよい。毎月の月末を正しく表示するなら日付関数をうまく利用して目的の処理を施すことになる。
●31日以上を非表示にする方法
単純に31以上を非表示にするには、月初めの処理と同じように日数計算をC列で行い、E列はIF関数を使って処理すればよい。セルE37を例にすれば次のような数式になる。この数式をセルE37に入力して、月末の第5週(セルE38〜E43)と第6週(セルE45〜E51)に複写すればよい。
=IF(C37>31,"",C37)
r操作
@日数を計算する数式(前日+1)をC列に入力する
AセルE37に31日以上を非表示にする数式「=IF(C37>31,"",C37)」を入力する
BセルE37の数式を他のセルに複写する
C31以上は表示されない
EX4C627Z EX4C628Z
●大小の月や閏年の28日を判断する
月末は31までとは決まっていない。30日もあれば28日や29日もある。これを正しく判断させるには、以前に紹介した日付関数の特徴を使えばよい。あり得ない日付を指定すれば自動的に正しい日付に訂正してくれる機能のことだ。まず、指定する日付の月数をMONTH関数で求める。この求めた月数とセルB2に入力した月数が一致すれば当月の日付、一致しなければ次月の日付、だということが判断できる。セルE37を例にすれば次のような数式になる。下に続くセルも同じようにして月末の日数処理を完成させる。
セルB37 =MONTH(DATE($B$1,$B$2,C37)) 式A
セルE37 =IF(B37=$B$2,C37,””) 式B
r操作
@セルB37に数式「式A」を入力する
AセルE37に数式「式B」を入力する
BセルB37の数式をB列の下に続くセルに複写する
CセルE37の数式をE列の下に続くセルに複写する
DセルB2の月数を4に書き替えた結果
EX4C625Z EX4C626Z
EX4C629Z EX4C630Z
●月日で表示する
月末からはみ出した日数を非表示にするかわりに、月を含めた表示にすることも可能だ。数式を紹介すれば次のようになる。セルの表示形式を日付に設定してセル幅を少し広げ、日数計算の数式にセルB3に表示しているシリアル値を加算すればよい。セルE5の数式を例にすれば次のようになる。−1は計算上1日ずれるのでこれを補う処理だ。この方法の場合、下に続くセルの数式はすべて前日+1の数式(セルE6なら=E5+1)でよい。
=C5+$B$3−1
r操作
@E列の日数を表示するセルの表示形式を日付に設定する
AセルE5に数式「=C5+$B$3−1」を入力する
B月日で表示される
C下に続くセルには前日+1の数式を入力する
EX4C631Z EX4C632Z
作成手順
さて、どこから書き始めればいいのでしょう。
カレンダースタイルで集計表を作るなら図Aのように日数は連番、つまり1日ごとに+1した値(連続番号)になる。この日数を値として直接入力すると固定された値になり毎月の使い廻しができなくなる。そこで、各日数を計算で求めるようにする。1日ごとに+1すればよいので、各行の数式は1つ上のセル+1でよい。つまり次のような数式だ。第2週の火曜日(セルE14)を例にすれば数式は「=E13+1」になる。集計表の先頭行だけは前日がないので数値「1」を入力する。週ごとの集計行を設けているので月曜の行に入力する数式だけは他と異なるのでマウスのドラッグ操作でコピーする場合は注意しよう。また、第1週目の日数計算をC列に入力している理由は、後の処理で1日の位置を移動させたときに表れる0や負数を非表示にするための数式を集計表の日数を表示するE列に入力するためだ。各セルの入力内容を載せておくので参考に入力してほしい。最後の行まで入力済ますと、31以上の値(日数)が表示されて違和感があるが、これについては、後で非表示の方法を解説する。次に数行分の数式を載せておくので参考に日数を表示するすべての行に数式を入力してほしい。
なお、集計表内の金額の計算は単純な加減算とSUM関数を使って合計を求めるだけなので、今回は詳しい解説は省くことにする。各自で適切な計算式を入力してほしい。