今回はExcelのワークシート関数であるADDRESS関数について説明します。今回の記事では、INDIRECT関数など、他の関数との組み合わせで威力を発揮することを説明します。
ADDRESS関数とは何か
ADDRESS関数は、指定された行番号と列番号に基づいて、セル参照をテキスト文字列として返す関数です。他のセルの値に基づいて、動的にセル参照を生成する必要がある場合に便利な関数です。
ADDRESS関数の構文は以下の通りです。
＝ADDRESS(行番号,列番号,[参照の種類],[参照形式],[シート名])
これだけだとよくわからないと思うので、順に解説します。
まずはADDRESS関数の動きのイメージを得ます。引数として、行番号を2、列番号を4(=D列)、参照の種類を4(=相対参照)、参照形式を1(=A1形式)、シート名を”Sheet2″としています。その結果、以下のように指定したアドレスである”Sheet2!D2″が返ってきました。
参照の種類と参照形式についてもう少し説明します。
まず参照の種類ですが、以下のように選択肢が4つあります。
試しに“1”の絶対参照を選択すると、ADDRESS関数全体の返り値も以下のように変わります($マークが追加)。
なお、絶対参照と相対参照の違いについては以下の記事を参照ください。
次に参照形式ですが、以下の用にR1C1形式もしくはA1形式を選ぶことができます。
普段Excelを使うときには行番号は1,2,3…とアラビア数字を使い、列番号はA,B,C…とアルファベットを使うことが多いと思います。この形式をA1形式と呼びます。一方、実はExcelでは行も列もどちらもアラビア数字で表すこともでき、それをR1C1形式と呼びます。デフォルトではオフになっていますが、以下の場所から設定を変更できます。
試しにオンにしてみました。以下のように、行も列もアラビア数字で番号が振られています。
このモードを使っている場合には、参照形式もR1C1形式を指定することで以下のようにADDRESS関数の返り値も変わります。
以上がADDRESS関数の基本的な使い方でした。
ではこのADDRESS関数は、いったいどのようなケースで使えるのでしょうか。
INDIRECT関数との組み合わせ
まず紹介したいのが、INDIRECT関数との組み合わせです。INDIRECT関数は指定された文字列を参照することで、特定の場所にあるデータを引用することができます。詳しくは以下の記事を参照ください。
INDIRECT関数との組み合わせを紹介する前に、上で登場したSheet2の中身がどうなっているかを示します。以下のように、SEQUENCE関数を使って数字を埋め込んでいます。
SEQUENCE関数については以下の記事を参照ください。
このSheet2の任意のセル範囲の値を、INDIRECT関数とADDRESS関数を使って抽出してみましょう。
先ほどADDRESS関数を使って表示した”Sheet2!D2″を、INDIRECT関数が参照した結果、”14″という結果が返ってきました。
Sheet2のD2セルを確認すると、確かに”14″となっています。
もちろんINDIRECT関数とADDRESS関数を1つの式で組み合わせることもできます。
では次に、Sheet2の以下の範囲(D2:F3)の値を全て合計してみます。
以下のように、INDIRECTとADDRESSの組み合わせによってD2とF3を表現し、SUM関数でそれらの範囲をすべて合計しています。合計値は120となっています。
元シートで確認しても、合計値120は正しいようです。
COUNTA関数・UNIQUE関数との組み合わせ
続いて、COUNTA関数・UNIQUE関数との組み合わせを紹介します。COUNTA関数・UNIQUE関数については以下を参照ください。
今回は以下の元データを使用します。この表から、UNIQUE関数の記事で紹介したような、担当者の名前のUNIQUEな(重複のない一意な)、かつ動的なリストを作成します。
以下のように、UNIQUE関数・INDIRECT関数・ADDRESS関数・COUNTA関数との組み合わせにより、動的なユニークリストを作成できました。
INDIRECT関数とADDRESS関数の使い方は前項の”INDIRECT関数との組み合わせ”を参照ください。同じ方法を使っています。
今回の元データは1行目から空欄なく下までデータが埋まっていることを想定しているので、COUNTA関数を使うことで最下行を動的に指定することに成功しています。もし途中空白行がある可能性がある場合はCOUNTBLANK関数を使って確認しましょう。この点も詳細はCOUNTA関数の記事を参照ください。
コメント