Excelシートは、新しい機能が追加されることもありますが、ほとんどの場合、新しいデータが追加されるだけで成長します。適切な設計であれば、ほとんどの成長と変更に対応できますが、多くの場合、必要な作業に合わないデータセットで作業することになります。幸いなことに、少しのノウハウがあれば、目的を達成できます。それができない場合は、データの制限を検討する必要があるかもしれません。この例では、データ範囲を拡張したときに従属関数が自動的に更新されるようにする、同じことを実現する3つの方法を見ていきます。具体的には、1つの列ではなく複数の列にわたる文字列の出現回数をカウントします。独自のシンプルなデータセットで作業することも、.xlsxデモファイルをダウンロードすることもできます。
数えるのは簡単
図Aは、複数の列と行からなるシンプルなデータセットを示しています。名前は複数の列と行にまたがって出現するため、範囲を参照します。そのため、皆さんが慣れているように単一の行または列ではなく、範囲全体を評価するシンプルなCOUNTIF()を使用しています。
図A
COUNTIF() は、範囲内で各名前が出現する回数を返します。
この関数は次の構文を使用します。
=COUNTIF(範囲, 条件)
最初の関数「=COUNTIF($B$2:$E$9,$H2)」をセルI2に入力した後、残りの一意の名前のリストにコピーしました。範囲参照は絶対参照ですが、条件参照では列のみが絶対参照になっていることに注意してください。行や列ではなく範囲を評価する際に正確なカウントを得るためには、絶対参照が必要です。
データを追加するとカウントが複雑になる
次元が増加しない安定した範囲で作業している場合は、この解決策はうまく機能します。しかし、新しい行または列を追加するとすぐに問題が発生します。COUNTIF()関数は新しいデータを自動的に更新しないためです。図Bに示すように、新しい行と列を追加しても、カウントは変わりません。
図B
新しいデータを追加しても、COUNTIF() 関数は更新されません。
この場合、範囲参照には絶対参照が必要です。絶対参照であるため、関数は毎日追加される新しいデータを自動的に更新しません。
簡単な解決策
自動参照を利用するために、範囲に行または列を挿入できることはご存知でしょう。図Cは、新しい行を追加した結果を示しています。ご覧のとおり、関数は更新されますが、新しい行によって新たな問題が発生します。
- データセットは日付順に並んでいません。通常はデータを並べ替えることができるので、これは問題になりません。
- 新しく挿入された行により、一意の名前リストに空白が作成されます。一意の名前リストとCOUNTIF()関数の列を別のシートに移動すれば、この問題は解消されます。
図C
行または列を挿入することは、新しい問題が発生しない限り、簡単な解決策です。
サンプルデータセットでは示していませんが、列の挿入時にも同様の問題が発生する可能性があります。しかし、これらの小さな問題は簡単に解決できます。データセットと同じシートに他のデータを保存しないようにすれば、挿入は驚くほど簡単になります。ユーザーのために新しい列や行を挿入するマクロを作成することもできます。
テーブルレスキュー
ユーザーの知識レベルに応じて、データセットをTableオブジェクト（Excel 2007以降でサポートされています）に変換することを検討してください。列と行を挿入すると、参照は自動的に更新されます。
データ セットをテーブルに変換するには、次の手順を実行します。
- データ セット内をクリックします。
- [挿入] タブで、[表] グループの [表] をクリックします。
- 表にヘッダーがあるかどうかを指定します。この場合、データセットにはヘッダーがあります（図D）。
図D
- [OK]をクリックします。
新しいTableオブジェクトを配置すれば、新しい行を素早く簡単に挿入できます。表の最初の空白セルを選択し、データの入力を開始するだけです。ExcelはTableオブジェクトを自動的に拡張して新しい行を追加し、COUNTIF()関数の参照を更新します（図Eを参照）。
図E
Excel のテーブル オブジェクトは柔軟性が高く、新しいデータを追加したり、依存する関数を更新したりできます。
テーブル配置を最大限に活用するには、COUNTIF()関数で構造化参照を使用します。具体的には、図Fに示すように、絶対範囲参照を新しいテーブルの名前に置き換えます。すると、右側に新しい列を挿入すると、COUNTIF()関数もそれに応じて更新されます。
図F
データ セットをテーブルに変換した後、依存参照を更新します。
テーブルオブジェクトを作成すると、Excelが自動的に名前をつけます。その名前を使用するか、より分かりやすい名前を自分で付けてください。テーブル内の任意の場所をクリックし、「デザイン」コンテキストタブをクリックします。図Gに示すように、テーブル名が左端の「テーブル名」コントロールに表示されます。テーブル名を変更するには、テーブル内をクリックして新しい名前を入力します。
図G
Excel のデフォルト名を使用するか、名前を変更します。
再編
テーブルを使ったソリューションは私のお気に入りですが、皆さんにとっては現実的ではないかもしれません。Excelのテーブルオブジェクトは強力ですが、いくつかの制限があります。そこで、もう一つ、後から実装するのがより難しいソリューションをご紹介したいと思います。
現状では、データ範囲の構造が少し曖昧です。より安定した構造を図Hに示します。名前を複数の列に分散させるのではなく、長い列に追加することで、他のExcel機能の実装が少し容易になります（並べ替えが簡単に行えるため、過去の日付の新しいレコードを追加しても問題ありません）。
図H
渡るのではなく、下ります。
図Iに示す列参照は、新しい行をそのまま処理します。列参照は列全体（ヘッダーセルを含む）を評価するため、シートにはデータ範囲の下に他のデータが存在しない必要があります。
図I
構造化列参照は新しい行を評価します。
特にデータ量が多い場合、構造の再構築は面倒です。最初にこの構造を実装できれば、ワークブックが大きくなるにつれて問題が発生するのを回避できます。
Officeに関するご質問をお送りください
読者からの質問には可能な限りお答えしますが、必ずお答えできるとは限りません。ご連絡の際は、できるだけ具体的にご記入ください。例えば、「ワークブックのトラブルシューティングをして、問題点を修正してください」といった質問ではおそらく回答が得られないでしょうが、「この数式が期待どおりの結果にならない理由を教えていただけますか？」といった質問であれば、回答が得られるかもしれません。私はTechRepublicから時間や専門知識に対する報酬を受け取っておらず、読者から料金を請求することもありません。ご連絡は[email protected]までお願いいたします。
