せっかく習得した技を忘れないように説明つきで保存しています。

Excelワークシート

入力欄が空白のとき、合計欄にゼロを表示させない

summery.xls

 数値が未入力の表で、合計欄にゼロを表示させないようにする場合、一般的にはif文を使って入力セルが空白かどうかを判断するが、この方法では特定のセルが空白かを判断するだけで、範囲全体が空白かどうかを判断することはできない。またこの方法では小計をさらにまとめて総計を出す時点で結果的にエラーになってしまう。

 これらのエラーを回避して、どんなケースでも「未入力時はすべて空白表示」「1箇所でも入力されたら計算結果を表示」させる式の例。なお、Excel2000以降では、オプション設定で「ゼロ値を表示させない」ようにしたほうが手っ取り早い。

部品一覧表を参照し必要な部品数を計算する

lookuptable.xls
 ひとつの商品が複数の部品で構成されていて、さらに複数の商品で同じ部品を使う場合に、商品の数に応じて必要な部品とその点数を割り出す表の例。VLOOKUP関数を使用して部品一覧表を参照し、商品ごとに必要な部品数を算出する。

横並びのセルを縦並びに変換して転記する

transpose.xls
 縦方向の合計が何列か並んでいるような場合に、その合計欄(横に並んでいる)を、縦並びに変換して別の場所へ転記する方法。

商品一覧表を参照し、請求書を作成する

seikyuu1.xls
 商品一覧表を早見表として参照し、請求書の作成を簡略化する。商品名→入力規則を元にリストから入力、商品単価→早見表から自動転記、数量→手作業で入力。商品別の金額、消費税、合計請求額を自動計算する。

条件付き書式の複写方法

 
 条件付き書式を複写する場合、Excel2000ではコピー&ペーストが使えない(とヘルプには書かれているが、ドラッグコピーとかできているような気もする)。複写の手順は、ツールバーの[書式のコピー/貼り付け]ツールを使って元のセルの書式をコピーしたのち、元のセルまで含めて複写先セルを指定し、(貼り付けではなく)[条件付き書式]コマンドを実行する。ただし、条件にセル参照を使った式を指定する場合、自動的に絶対参照になっているので注意が必要。

シート保護時の注意

 
 シートを保護していると、オートフィルタ機能や条件付き書式の設定ができない。

任意の月の日数を求める

daysofmonth.xls

 ある月が何日あるかを、ルックアップテーブルやうるう年計算を使わずに求める方法。このやり方については、うるう年の計算方法を調べているときに某Webページで見つけたコメントを参考にした。

 分析ツールアドインを組み込んでいる場合は、EOMONTH(End Of Month)関数で簡単に最終日を求めることができる。

 =EOMONTH([日付],[オフセット])

 [オフセット]は関数入力ダイアログでは「月」と表示されている部分で、指定した[日付]から何ヶ月前または後かを数値で指定する。2なら2ヵ月後、-1なら1か月前、0ならばその月の最終日の日付が戻り値となる。

入力規則で大区分→小区分と絞込みを行う

daysofmonth.xls

 入力規則を使って項目リストからの入力を行う際に、ふたつのリストを使ってひとつを大きな分類、もうひとつをそれより小さな分類とする。このようなケースで、大分類の項目を選ぶと、それに応じて小分類のリストの内容が自動的に制限される。

 小分類の内容を、大分類ごとに分けて整理することで全体の入力が簡単になる。データベースではクエリーで抽出した結果だけを表示項目とすることができるが、見かけ上似た処理をExcelで実現する。

入力規則の制限

 入力規則でリストを使う場合、他のシート上のデータを[元の値]として直接指定することはできない(シートの切り替えができない)。この場合、作成中のシートで、[元の値]にしたいシートとそのセル範囲に名前をつけることで、別のシートの範囲でも参照可能になる。

入力規則とマクロ

 Excel2000では、入力規則のコピー→[形式を選択して貼り付け]で[入力規則]のみのコピー&ペーストができる。ところがこれをマクロに記録しても、実際に実行するときエラーになって使えない。Excel2002では改善されているらしい。