はじめに
スプレッドシートで同じヘッダーを持った表が複数あるとき、それぞれの表のヘッダーの座標（行番号）を取得する方法です。
例えば「表の中で」上から３行目の値を取得したいとき、シートの中に表が１個しかなければ単純にヘッダー行の下から数えて３行目（画像だとシートの５行目）ということになります。
ところが表が２つある場合だと、１つ目の表の中で３行目ならシート全体の５行目だったものが、２つ目の表の中で３行目だとシート全体の13行目といったことが起こりえます。
本記事では、関数を書く行が属する表を自動的に判定して、そのテーブルのヘッダー行の行数を取得するまでの手順を解説します。
下記画像の例だと、３～５行目に関数を書いた場合は２、11～15行目に書いた場合は10、21～24行目は20を取得するのが目的となります。
MATCH関数で条件に合う２番目以降の座標が取得できたらそれで済むのですが、残念ながら１番目しか取得できないので、複数の関数を組み合わせていきます。
本記事作成にあたっては下記ページを参考とさせていただきました。
INDEX＋MATCH関数で複数該当の２番目以降を抽出する方法 – 病院SEにゃんとのPCトラブル解決＆Excel関数等活用術
スプレッドシートで表が複数あるときのヘッダーの座標を取得する方法
スプレッドシートで表番号を取得
ここでは、表が上から何個目かを表番号と呼ぶことにします。
COUNTBLANKを使って表番号を取得する方法
下記２つの条件を満たす必要がありますが、COUNTBLANKを使う方法があります。
- どの表にも空欄のない列が最低１つ存在していること
- 表と表の間隔が一定であること
メリットは表によってヘッダーが異なっていてもカウントできる点ですが、割り算をしないといけないデメリットがあります。ヘッダーが一致しているのであれば、後から紹介するCOUNTIFを使った方法の方がシンプルです。
これからお見せする例では、A列は空欄がなく、
表と表の間隔（空行）は３行です。
COUNTBLANKでシートの一番上の表の開始位置から、現在の行までの範囲を指定します。
=COUNTBLANK($A$1:$A3)
すると、このように同じ表の行では同じ値が返ってくるのが分かります。
数字が０、３、６と３ずつ増えていくのは表と表の間の空白行が３行だからです。
まず３で割って、
そのままだと０、１、２と０始まりになるので＋１して１始まりになるように調節します。
=J3/3+1
これで目的の値が得られました。
COUNTIFを使って表番号を取得する方法
表のヘッダーが共通である前提で、空白行を使わずに表番号を取得する方法をご紹介します。
COUNTIFで、一番上の表の冒頭から現在の行までを範囲に設定します。
条件はヘッダーに必ず含まれる名称（ここでは「AAA」）を設定します。
すると、このように目的の値が得られます。
=COUNTIF($A$1:$A3,"AAA")
表ヘッダーの行番号を取得する
INDEX関数で行が条件に合うか判定
INDEX関数を使って、対象範囲のどの行が条件に合うか（ヘッダー行かどうか）判定することができます。
例として、セルの値が「AAA」と一致するかどうか判定してみます（「AAA」がヘッダー行でしか使われていない前提）。
INDEX関数の参照で範囲を指定します。「$A1:$A」としてもよいのですが、延々と結果が下に伸びていってしまうのでここでは「$A1:$A40」と40行までで切っています。
さらに、続けて「=”AAA”」と書き込みます。
すると、このように結果が配列としてずらっと縦に表示されます。
TRUEは条件を満たしている、FALSEは満たしていないという意味で、２行目、10行目、20行目は「A列の値がAAA」という条件を満たしているためTRUEになっています。
ROW関数で行番号を取得
次に、隣のセルに「=ROW($A1:$A40)」と入力してみます。
すると、このように行番号が表示されるのですが、INDEXの時とは違って１行分しか表示されません。
このようにARRAYFORMULAで囲ってやることで、
配列の中身がすべて表示されました（配列の中身を見るためにARRAYFORMULAを使用しましたが、後ほどの計算では使わずにROW単体で行います）。
ここで、V列に表示されているFALSEやTRUEというのは、そういう文字が入っているわけではなく、○なのか×なのかという真偽値です。真偽値を数字に置き換えると、FALSEは０、TRUEは１となります。
この性質を利用して、セルの値が「AAA」のときは列番号を、それ以外のときは０を返す数式を作ります。
そのためには、INDEXの中で「$A1:$A40=”AAA”」（V列）と「ROW($A1:$A40)の結果」（W列）を掛け算してやればよいです。
=INDEX(($A1:$A40="AAA")*ROW($A1:$A40))
結果はこのように、V列が０（FALSE）の行はXが０になっています。
この場合も、１行目は０×１で０が表示されました。
しかし、１行目しか結果が表示されないのでROWの時と同様に配列の中身を見るためにはARRAYFORMULAで全体を囲ってやる必要があります。
すると、このように「AAA」の行だけ行番号が表示され、他の行は０が表示されています（V列の値×W列の値＝X列の値となっているのが分かるかと思います）。
=ARRAYFORMULA(INDEX($A1:$A40="AAA")*ROW($A1:$A40))
ヘッダー以外の行にもヘッダーの行番号を表示する
関数を組み合わせる
以上を踏まえて、ヘッダー以外の行にヘッダー行の行番号を表示していきます。
表番号については先にCOUNTBLANKまたはCOUNTIFを使う方法で求めました。
先ほど掛け算で計算したヘッダー行の番号が含まれる配列ですが、小さい順に並べると
0,0,…,0,2,10,20,…
というように、０から数字が大きくなっていきます。
小さい方から２番目の数（２）が表番号１のヘッダー行、３番目の数（10）が表番号２のヘッダー行、４番目の数（20）が表番号３のヘッダー行です。
このように、小さい方から何番目というときに使える関数がSMALLです。
まずは、SMALL関数のデータの部分に「INDEX(($A1:$A40=”AAA”)*ROW($A1:$A40))」を入力します。
n のところには「何番目のデータか」の番号を入れるので、テーブル番号である「COUNTIF($A$1:A3,”AAA”)」を入れていきます。
先ほど確認したようにヘッダー行の配列は１番小さい値が０で２番目以降が使いたい値なので、テーブル番号に＋１しておきます。
これで良いと思いきや、テーブル番号に＋１したにも関わらず結果は１番小さい値である０になってしまいました。
UNIQUE関数を使って重複する値を取り除く
原因はヘッダー行の番号を小さい順に並べた配列の中に、
0,0,…,0,2,10,20,…
というように、０がたくさんあることです。
そこでUNIQUE関数を使います。この関数は重複する値がある場合に１つにまとめてくれる効果があります。
結果、このように想定通りの値が返ってくるようになりました。
=SMALL(UNIQUE(INDEX(($A:$A="AAA")*ROW($A:$A))),COUNTIF($A$1:$A3,"AAA")+1)
セルをコピーして下の行に貼り付けると、このように表ヘッダーの変わり目で値が切り替わっているのが分かります（COUNTIFの中にある「$A3」の数字部分$がついていないので、数式を貼り付けるときに行に応じて自動で書き換わります）。
もしかすると、表タイトルの行で数字が切り替わらないのが気になったかもしれません。
普通は計算値を使うのは表のデータ行だと思うので問題にならないと思うのですが、気になる場合はテーブル番号をCOUNTIFではなくCOUNTBLANKで取得する方式にすれば解決します。
冒頭ご紹介した参考サイト様の書き方をそのまま踏襲すると下記コードになります。
=1/LARGE(INDEX(($A:$A="AAA")/ROW($A:$A)),COUNTIF($A$1:$A3,"AAA"))
私はINDEXの中でROWを掛け算したのですが、そこを割り算にすることで、UNIQUEを使わずに済ませることができます。この場合はSMALLではなくLARGEを用い、得られる値は行番号の逆数となるため式の冒頭に「1/」が入ることになります。
割り算が入るのは直感的に意味が分かりにくいと思ったため、本記事では掛け算を使う方法としました。
まとめ
本記事ではCOUNTIF（またはCOUNTBLANK）INDEX、ROW、SMALL（またはLARGE）、UNIQUEを使って表番号、さらには表番号に応じたヘッダーの行番号までを取得する方法を解説しました。
下記記事でご紹介したOFFSET関数と組み合わせることで、かなり応用範囲が広がると思います。
