リスト範囲の自動設定 - Excelのムダを省く「JIMOVE」

Youtubeで公開している内容をまとめたものです。

データ入力規則の範囲の自動判定

こんにちは、JIMOVEの山口です。

データ入力規則のリスト範囲の自動判別に関する発展的な内容をお伝えします。このトピックは専門的なものですので、関心のある方のみご覧ください。

まず、前回の動画で取り上げた「会社名リスト」の自動更新について詳しく説明します。

会社名をデータ入力規則に基づいてリスト化し、特定の列の範囲を指定しています。この設定により、新たな会社名が追加された場合でも、リストは自動的に更新されます。

しかし、このシステムには欠点も存在します。例えば、リストの先頭に位置する株式会社が削除された場合、リストが空白状態になることがあります。

この問題を解決するためには、範囲選択を再度行う必要があります。たとえば、「会社リストの2列目から4列目」といった具体的な範囲を選択することが求められます。

これを改善する方法として、関数を用いて自動的に範囲を判別できる仕組みを説明します。

使用する関数として「OFFSET関数」があります。

この関数を使えば、「Aの3列目から5列目」といった範囲を指定できます。しかし、この関数は単独で使用することはできません。

実際にセルに「=OFFSET」と関数を入力し、内容（引数）を入れても「VALUE」というエラーが発生します。

この関数は他の関数と組み合わせて使用することが一般的です。今回は、OFFSET関数をデータの入力規則のリスト範囲に応用して使用します。

OFFSET関数の基本的な考え方について説明します。

まず、基準となるセルを設定します。例えば、基準のセルを「A1」とします。

次に、行方向の移動範囲を設定します。「Aの3列目から5列目」を選択する方法をOFFSET関数を使って実施します。

基準セルからの行方向の移動は、下方向はプラス、上方向はマイナスで表現します。

列方向の移動は、右はプラス、左はマイナスです。

今回のケースでは、基準セル（A3）から行方向に2行、列方向に0列（動かない）を設定します。

この設定により、OFFSET関数は「Aの3列目から5列目（A1から行方向：下方向に2行、列方向：右方向へは0列）」という範囲を選択します。

このOFFSET関数の仕組みを利用して、請求書のデータ入力に応用します。

例えば、基準セルを「A2」とし、終了セルを「A4」と設定することもできます。数が増えた場合は、「A5」まで、「A6」といった形で対応可能です。

そのためには終了セルの位置を設定する必要があります。

次に、基準セルから見て終わりのセルを判断させる必要があります。

ここでは、空白でないセルの数を数えるためにCOUNTA関数を使います。

=COUNTA(セル範囲）　空白ではないセルの数を表示する

会社名の行数を計算して、1行分を減らします。（タイトル分を1と数えているため、減らします）

例えば、会社名が3つのデータが入っている場合、COUNTA関数は4つと計算し、タイトル分を1減らして3となります。このことから「-1」という操作を行います。

仮にデータが増えて「A5」までに会社名が入った場合は、COUNTA関数は5と計算し、「-1」することで実際のデータ数を4とします。

これにより、「A2」から4つ分の範囲、つまり会社名の範囲としては「A2からA5」を範囲として選択できます。

行方向の設定が完了したら、列の範囲を設定しますが、列は動かないので0と入力します。

この設定により、会社名などが適切に表示されます。

会社名が増えた場合でも、COUNTA関数により自動的に範囲が調整されます。これにより、請求書におけるデータ入力規則の範囲を自動的に判断することができます。

これは少し難しい内容ですが、この方法を組み合わせることで、さまざまな作業を効率的に行うことができます。色々と試してみてください。

リストを作成する上で、空欄があるとプルダウンの一番上が空欄になったり、初期の選択が空欄になる、多めに範囲を取るとプルダウンの下の方に空白の内容が表示されるなど、見た目上良くありません。

OFFSET関数、COUNTA関数を利用して、スマートに対応しましょう。