Excel 見出しタイトルが一致する列のデータを、関数で取得する

かなり久し振りの投稿となりますが、今の会社のExcel仕事でしょっちゅう必要になって使うテクニックがあるので、自分の備忘も兼ねて書いてみます。

やりたいこと

下の図1はS列まで表示されていて、更にその右にも多数の列がある。まず元データは、このように多くの列があるものを想定する。

その元データの中から、必要な列だけ(たとえば図1でタイトル行を青くしている列とか)、タイトル文字を「転記」シートの1行目に予めまとめておく。
そうしたときにExcel関数で、元データから、タイトル行の文字が「転記」シートと合致するデータを抜き出して転記することができるようにしたい。

下の図2でいうと、1行目は予め書いておくのだが、2行目以降のデータは、最小限の関数を入れるのみで引っ張って来られるようにしたいということだ。

他にも幾つか条件らしきものを書くと、次のような感じ。

旧来からの関数でやる方法

ではまず、Excel2003の頃から存在する、旧来からのExcel関数だけでやってみる方法から。

と言っても、それら旧来からの関数の力をスピルで引き出すので、スピルが無い古いバージョンのExcelでは使えない方法となる。

ではまず、今回の元データは「どこからどこまでなのか」をはっきりさせて、そのデータを過不足なく抽出するところからやってみたい。
「テーブル」を使えば、テーブルが同時にそのデータ範囲として決まるのだが、今回はテーブルを使わない縛りでいくので、データ範囲は関数で求める。

Excel 「テーブル」機能のメリット・デメリット概要

2020年3月22日

OFFSET関数

旧来からの関数で、縦横のデータを一発で取得する用途に使えるものといえば、OFFSET関数だ。

OFFSET関数の引数構成は

となっている。

まず①基準となるセル は、データが開始されるA1セルを指定すれば良いだろう。

次に ②基準からいくつ縦にズレるか だが、今回はタイトル行を外したデータ部分だけ引っ張りたい(タイトル行は、既に転記シートに用意している)。だから①基準となるセル A1セルから1つ縦にスライドさせるため「1」を指定すれば良い。

③基準からいくつ横にズレるか  は、今回は別に横にズレなくて良いので、引数としては「0」を入れるか空欄にしておけば良い。

④範囲の行数  については、データがどれだけの範囲にわたっているかを指定するわけだが、データ数を数えるCOUNTA関数と組み合わせるのがセオリーだ。
今回の行数は、ひとまずA列から取得することにする。これは、空白でないデータ数をCOUNTA関数で取得するわけなので、空白データがあり得る列を選んでは駄目だ。だから場合によっては、B列などを指定することになる。
そしてタイトル行の1行分は除外する。結論として、
COUNTA(元データ!$A:$A)-1
の式を、ここには入れれば良い。

⑤範囲の列 については、同様にCOUNTA関数を使って、タイトル行である1列目から取得すれば良いだろう。つまり
COUNTA(元データ!$1:$1)
の式を入れれば良い。 

スピル

ここまでをまとめたOFFSET関数は

となるが、作業用シートを用意して、この式を実際に書いて結果を確かめてみよう。

上記の式を、作業用シートのA2セルに書いてみる(1行目は、タイトル行のため空けておく)。するとそれだけで、他の幅広いセルに結果が表示されるはずだ。

これが「スピル」の素晴らしいところで、ごく少数のセルに書くだけで済むようにデータを作れるようになるので、メンテもしやすくなる。

古いバージョンのExcelだと、OFFSET関数を同じように書いても、こういうことはできなかった。

Excel 「スピル」機能について

2020年9月13日

そして作業用シート1行目は空けたけど、ここに元データのタイトル行を持ってきたい。

A1セルに「=元データ!A$1」と書いて、その式を横にコピペしていく・・・という方法でも良いことは良いが、A1セルに
=元データ!$A$1:$AZ$1
とでも書くだけで良い。ここでもスピルの活用だ。

転記

では実際の転記をしてみるが、ここではINDEX関数を活用してみる。

まず転記シートA2セルを選んで、
=INDEX(
まで入力する。

そこから「作業用」シートを選択し、そのA2セルをクリック。
＜Ctrl+Shift+下キー＞、続けて ＜Ctrl+Shift+右キー＞としてみよう。

すると、「作業用」シートA2セルに入れたOFFSET関数の効果範囲が選択され、INDEX関数の中に
=INDEX(作業用!A2#
と表示されるはずだ。この「#」マークが、スピルの効果範囲を簡単に示す便利な記号だ。

この
作業用!A2
というセルは、今後、縦にも横にも一切ズラされず固定したいセルなので、上記のスピル式は
=INDEX(作業用!$A$2#
と絶対参照に直しておこう。

そしてINDEX関数の構成として、上図のヒント欄に出ているように、行番号・列番号と指定するわけだが、今回は「行番号」は空欄で良い。縦のデータを1列まるごと取得するつもりなので、行番号を指定する意味はないからだ。

そして肝心なのが「列番号」の方なのだが、これは、「転記」シートと「作業用」シートでタイトル行の文字が一致する列番号を取得することになる。

そこで使うのがMATCH関数で、これはINDEX関数と組み合わせるのは鉄板だ。もっとも通常は、行番号の方を取得するのに使うことが多いけど。

ここで入れるMATCH関数は、
MATCH(A$1,作業用!$A$1#,0)
というようになる。

「作業用」シートのA1セルには先ほど
=元データ!$A$1:$AZ$1
とスピルの式を入れたので、ここでもスピルの#記号を使って簡単な式にできる。

以上をまとめると、「転記」シートのA2セルに入れる式は

とすれば良い。

そしてその式を、「転記」シート2行目に、横方向にコピーしていけば、ひとまず完成だ。

旧来からの関数だけ使うって方向だったけど、MATCH関数の代わりにXMATCH関数を使うのも良いだろう。

XMATCH関数とMATCH関数の違いは、細かい要素ばっかりなのでここでは割愛するが、

• MATCH関数→MATCH(A$1,作業用!$A$1#,0)
• XMATCH関数→XMATCH(A$1,作業用!$A$1#)

と、最後の引数0を省略できるのがXMATCH関数のメリットの1つ。
XMATCH関数は新しめの関数だが、スピルが使えるバージョンのExcelでなら必ず使えるはずなので、使っても良いだろう。

XMATCH関数を使った式は

となる。

新しい関数も活用した方法

では、作業用シートに書いたOFFSET関数なんかはいったん全て消して、けっこう新しい関数を使う方法もやってみる。

Excel(Microsoft365)2022/08新規追加された関数(1)配列操作系の関数

2023年9月9日

FILTER関数

OFFSET関数を使ったのは、データの範囲を絞り込むためだが、今回はそういうのに強力なFILTER関数を使ってみる。

FILTER関数なら「金額が10万円以上」などの条件も設定できるし、OFFSET関数+COUNTA関数 のような関数の組み合わせよりは少しは見やすい。少しだけど。

まず作業用のシートはいったん全てクリアし、FILTER関数を使って

と入れる。これは、元データのA列が空欄でないという単純な条件を指定したもので、この場合はタイトル行まで含めてデータが取得される。

ひとまず今回は、タイトル行は除いたデータ部分だけを取得したい。その方法の一つとして、行や列を削除するDROP関数を使って

としてみる。これで、最初の1行目だけが削除されたデータになる。

あるいはたとえば、A列の勘定科目が「建物」のデータだけに絞るなら

といった式で良い。この場合は、タイトル行は含まれずデータが取得される。

CHOOSECOLS関数

そして次に、転記シートにデータを引っ張る。先程はINDEX関数を入れて、それを横方向にコピーしたわけだが、今回はその横方向コピーも省略して数式を1個入れるだけで良いようにする。

今回のように、＜元データがある→その中から指定した列番号のデータを引っ張ってくる＞という目的に使う関数がCHOOSECOLS関数だ。

CHOOSECOLS関数は
=CHOOSECOLS(元データ,1,3,5,7,9)
みたいに列番号を指定してデータ取得する関数だが、こんな風に列番号を直接書くやり方はしない方が良いかと思う。
VLOOKUP関数でも、そういう風に列番号を直接書くのはスマートさに欠けるのだけど、それと同じ。

ではどうするかというと、列番号は予め1行目の作業セルに書いておくとか、先程もやったようにMATCH関数やXMATCH関数などを使うと良い。

今回はXMATCH関数を使って
=CHOOSECOLS(作業用!$A$1#,XMATCH($A$1:$J$1,元データ!$1:$1))
とすれば一発で終了する。

XMATCH関数の第一引数には検索値を入れるけど、それにタイトル行を丸ごと入れて、スピルにより一度にデータ取得できる。

なおこのCHOOSECOLS関数について、代わりにINDEX関数を使って
=INDEX(作業用!$A$1#,,XMATCH($A$1:$J$1,元データ!$1:$1))
と済ませようとしたら、最初の1行しかデータを取得できない。