まりふのひと

iPS-77/Excel2007演習問題集のLesson81（改）のバグについて

Excel MAPS

いきさつ

　2015-12-03の山口ばあばさんの日記「ips-77 Excel演習問題集（Ｌesson81）家計簿4月分を作ってみたが？」

　家計簿4月分をコピーして作ってみました。
5月1日が入っていますがどこが違うのでしょうか？　2月分は28日まで表示、後は空白になっています。


　先日のiPS-77で、私が行った Lesson81改 の不具合報告だぁ〜〜〜

  • 標準回答では...
    1. ［1月］シートで、
      　1月29日の日付セル（A34）は “=A33+1”。
      　1月30日は“=A34+1”。
      　1月31日は“=A35+1”
    2. ［1月］シートをコピーして作った［2月］シートは、行34〜36は削除する‥‥ というもの。

      　この方法では、(1)［2月］シート（小の月）から［3月］シート（大の月）は作れない（行挿入等の補完が必要）。(2)「合計」行が毎月変わるため、前月残高（セルK2）を正しく修正出来るか‥‥ の問題がある。

  • これらの問題を改善した計算式
    1. ［1月］シートで、
      　1月29日の日付セル（A34）は “=IF(MONTH($A$33)+1)=MONTH($A$1),A33+1,"")”。
      　30日，31日はフィルハンドルでコピー。
    2. ［1月］シートから［4月］シートを作ると、4月30日の次に5月1日が出る‥‥ という指摘。
      　原因は、MONTH($A$33)+1)は 4 となり条件式は「真」となる。このため A35+1，すなわち 5月1日が表示されたもの。

日付バグ対応

　原因は “=IF(MONTH($A$33)+1)=MONTH($A$1),A33+1,"")” の $A$33 を絶対番地にしたため。

  • 案１：相対番地にする ‥‥ 失敗
    　“=IF(MONTH(A33+1)=MONTH($A$1),A33+1,"")”
    • 4月分はうまく作れるが、2月分は #Value! のエラーになる。
    • 原因は、セルA34が "" になるため、セルA35の “=IF(MONTH(A34+1)=MONTH($A$1),A34+1,"")” の赤字の計算（""+1）でエラーになる。
  • 案２：N関数を使い、""（文字）を数値（ゼロ）に変換する。
    　“=IF(MONTH(N(A33)+1)=MONTH($A$1),A33+1,"")”
    • セルA33が "" であれば、Month(N("")+1) は 1（1月）となる。
      　しかし、1月は日付が必ず入るので、実用上、不具合は出ない‥‥ はず。
  • N関数とは（Excel ヘルプより）
    説明
    　値 を数値に変換します。

    書式
    　N(値)

    N 関数の書式には、次の引数があります。
    • 値　必ず指定します。変換する値を指定します。N 関数では、次の規則に従って値が変換されます。






      値戻り値
      数値そのままの数値
      Excel の組み込み書式で表示された日付その日付のシリアル値
      TRUE1
      FALSE0
      #DIV/0! などのエラー値エラー値
      その他0

    解説
    • 数式中の値は必要に応じて自動的に変換されるため、N 関数を使用する必要はほとんどありません この関数は、他の表計算プログラムとの互換性を維持するために用意されています。
    • Excel では、日付を連続したシリアル値として処理することで、日付の計算が行われています。 既定では、1900 年 1 月 1 日がシリアル値 1 として保存され、2008 年 1 月 1 日は 1900 年 1 月 1 日から 39,448 日後に当たるので、シリアル値は 39448 になります。

    • 参考）“=""+1” は ＃Value! エラーになる。“=N("")+1” とすると回避できる。

前月残高（セルK2）

　合計の行を固定したので、前月残高も新しいシートを作る度に修正しなくてもいいよう INDIRECT関数を使う。

  • 前提条件
    1. シート名（シート見出し）は、半角数字の “1月”〜“12月” とする。（スペースはどこにも入れない）
    2. セルK37（合計行の残高）＝翌月繰越額を計算（“=C37-J37”）しておく。

  • 前月残高（セルK2）の計算式
    　“=IF(MONTH(A1)=1,0,INDIRECT(MONTH(A1)-1&"月!K37"))”
    • セルA1が 1月であれば 0 とする。
    • 1月以外であれば、セルA1の「月-1」の月のシート名のセルK37の値とする。

　上記織り込み済みの Excelワークシートは、下記よりダウンロードできます。

  1. 「麻里府発」の［まりふのひとの「公開」OneDrive］に入り
  2. 「Excel2007演習問題集」フォルダにある「Lesson81_改2」をダウンロード