画像: muchomor、ゲッティイメージズ/iStockphoto
TechRepublicのメンバーであるJeffさんは、Microsoft Excelで興味深くも難しい問題を抱えています。毎日更新されるデータセットの最後の7行に条件付きカウントを適用したいのですが、そのためには動的なソリューションが必要です。現状では条件付きカウントは適用できますが、毎日数式を更新しなければならず、非常に面倒です。そこで、私がJeffさんに提案した解決策をご紹介します。OFFSET()関数を使って、変化するデータ範囲に対応しましょう。
Windows 10 64ビット版システムでOffice 365デスクトップ版のMicrosoft Excelを使用していますが、このソリューションは以前のバージョンやブラウザでも動作します。ご自身のデータを使用することも、デモ用の.xlsxファイルをダウンロードすることもできます（.xls形式はこのソリューションをサポートしていません）。
なぜOffset()が必要なのでしょうか?
データセットはシンプルにしました。各日を2列1行で表しています（図A）。ステータス値は条件値（yesとno）です。また、Tableオブジェクトです。
図A
ジェフは、最後の7行の「はい」の値の数を返すカウント関数を使用しています。セル参照を使用する場合、毎日の新しいレコードに対応するために参照を毎日更新する必要があります。ジェフは、新しいレコードが追加されたときにデータ範囲を自動的に調整する動的な数式を必要としています。そのためには、Offset()関数を使用します。
Offset() は何をしますか?
ExcelのOffset()関数は、アンカーセルに基づいて、特定の行数と列数で指定された範囲を返します。この関数の構文は次のとおりです。
OFFSET(参照, 行数, 列数, [高さ], [幅])
簡単に言えば、範囲を構築するということです:
- 参照はデータセットの最初のセルです。この例では、最初のステータスセルであるC3です。
- rows は、参照に基づいて参照する上または下の行数です。
- cols は、 referenceに基づいて参照する左または右の列数です。
- height は、返される参照の行数を指定します。これはオプションの引数であり、正の数である必要があります。
- width は、返される参照の列数を指定します。これはオプションの引数であり、正の数である必要があります。
ここで、返される範囲が動的になるように入力セルから始めて、Jeff が使用できる Offset() 関数を作成しましょう。
入力セルの使い方
ジェフは、返される範囲を使ってデータセットの最後の7行を評価したいと考えています。式に「7」と入力することもできますが、代わりに入力セルを使って、返される範囲のサイズをジェフが即座に変更できるようにします。ジェフはこの柔軟性を必要としませんが、皆さんは必要かもしれません。図Bは新しい入力セルを示しています。E3はn_rowsという名前付き範囲です。（E3を選択し、「名前ボックス」に「n_rows」と入力します。）
図B
入力セルを配置したら、カウントを実行する式の準備が整います。
式の書き方
これから使用する式は、CountIf()とOffset()を組み合わせたものです。Offset()関数は、CountIf()で評価される範囲を返します。G3に次の式を入力してください（図C）。
=COUNTIF(OFFSET(C3,COUNTA(Table1[ステータス]),0,-n_rows-1),”はい”)
図C
おそらく、これがどのように機能するかを説明する最も簡単な方法は、それを部分的に評価することです。
- 参照は C3 のアンカー セル (左上のセル) です。
- rowsはCountA () 関数で、Status 列のセルの数 (15) を返します。この値は新しいレコードごとに増加します。Table[Status] 参照は従来のセル参照でも可能ですが、この手法を機能させるには Table オブジェクトの動的プロパティが必要です。
- カウントは Status 列で行われるため、cols は0 になります。返される範囲を列単位で拡張する必要はありません。
- 高さは -n_rows-1、つまり入力セルから 1 を引いた値で、6 を返します。
- 列を拡張しないので、幅は必要ありません。
それでは、すべてをまとめて、どのように機能するかを見てみましょう。
=COUNTIF(OFFSET(C3,COUNTA(Table1[ステータス]),0,-n_rows-1),”はい”)
=COUNTIF(OFFSET(C3,15,0,6), “はい”)
=COUNTIF(C11:C17, “はい”)
=3
セルの総数は15で、返される範囲はそこから9行下、 C11から始まります（15-(7-1) = 9）。返される範囲はC11:C17です。これがデータセットがTableオブジェクトである理由です。Tableオブジェクトは、新しい行を追加すると最後のセル参照を自動的に調整します。通常のデータ範囲を使用している場合、この方法は機能しません。
もう一つの入力セルを試してみましょう
Jeff はそれ以上の柔軟性を必要としませんが、あなたには必要かもしれません。そこで、条件値 Yes と No の入力セルをもう 1 つ追加しましょう。この目的のために、検証コントロールを使用します。
- F3を選択します。「データ」タブをクリックし、「データツール」グループの「データの入力規則」をクリックします。必要に応じて、ドロップダウンから「データの入力規則」を選択します。
- 表示されるダイアログで、[許可] コントロールから [リスト] を選択し、[ソース] リストに[はい、いいえ]と入力します (図 D )。
- [OK]をクリックします。
図D
次に、新しい入力セルを参照するように式を変更します。
=COUNTIF(OFFSET(C3,COUNTA(テーブル1[ステータス]),0,-n_rows-1),F3)
「はい」をF3に置き換えます。変更点はこれだけです。これで、関数が「はい」と「いいえ」のどちらをカウントするか、また何行をカウントするかを決定できます（図E）。
図E
E3とは異なり、F3には名前付き範囲を使用していないことにお気づきでしょう。これはどちらの方法でも機能します。つまり、両方の入力セルに実際の参照を使用することも、名前付き範囲を使用することもできます。名前付き範囲を使用するには十分な理由がありますが、この場合はどちらでも構いません。名前付き範囲の方がセル参照よりも柔軟性が高い状況に遭遇するかもしれません。
Jeff さんのための別の解決策がある場合は、下のコメント セクションでアイデアを共有してください。
Officeに関するご質問をお送りください
読者の質問には可能な限りお答えしますが、必ずお答えできるとは限りません。リクエストがない限り、ファイルは送信しないでください。添付ファイル付きの初回サポートリクエストは未読のまま削除されます。ご質問を明確にするために、データのスクリーンショットを送信していただくことも可能です。お問い合わせの際は、できるだけ具体的にご記入ください。例えば、「ワークブックのトラブルシューティングを行い、問題点を修正してください」といった質問では返答がないかもしれませんが、「この数式が期待どおりの結果を返さない理由を教えていただけますか？」といった質問であれば、回答が得られるかもしれません。ご使用のアプリとバージョンを明記してください。読者サポートにあたり、TechRepublicから時間や専門知識の報酬を受け取ることはありません。また、サポートした読者から料金を請求することもありません。お問い合わせは[email protected]までお願いいたします。
