Home About Contact
Excel , Functional Programming , Spreadsheet

MS Excel で 空の行を詰めて別の列に表示する方法

たとえばB列にポケモンの名前が入っているとする。 ただし、途中に空白行があるので、そこは詰めてD列に表示するにはどうしたらよいか？という問題。

ポケモン名リスト1

この問題の回答を検索してみるとこれらしい。

配列数式と index, small, row などの関数を組み合わせることで実現できるらしい。 なるほどわからん。

普通に Javascript で発想したら:

const namelist1 = ['ピカチュー', '', 'イーブイ', 'ルカリオ', '', '', 'ミュー', 'ゼニガメ', '', 'ビチュー'];
const namelist2 = namelist1.filter( item=> item!='' )
console.log(namelist2); // [ 'ピカチュー', 'イーブイ', 'ルカリオ', 'ミュー', 'ゼニガメ', 'ビチュー' ]

これだけじゃないの？ つまり、namelist1 はB列（B2:B11）で、namelist2 が得られたら、これをエクセル関数 index(namelist2, row(A1)) とすればよいだけなのではないか？

この発想をそのまま実現する方法はないのか。

Filter 関数を使え

エクセルにも filter 関数があり、条件にマッチしたものだけをリストできる。 ただし、 filter 関数は Microsoft 365 サブスクリプションのエクセル でしか使えない。

2022-03-29 Update: 昨年リリースされた Excel 2021 (永続ライセンス版) でも filer 関数 サポートされました。ありがとうマイクロソフト。

ポケモン名リスト2

この filter 関数は結果がリストになって返るのだが、それがいわゆる spill されて、関数を入れていない下に続くセルに値が流し込まれていく。 こんな機能があるのですね。 だから、index(filter(ほにゃらら), row(A1)) などと書いて、D2:D11 までコピペする必要がないのであった。便利ですね。

しかし、この機能を使うには毎月のサブスクリプションを払う必要がある。 Microsoft 365 は、エクセル以外のオフィス製品（ワードとか）の使用料も含まれていて、エクセルの filter 関数のためだけにこの費用を毎月払い続けるのはちょっと受け入れがたい。

そこで、自分のエクセル能力レベルで理解できる方法で filter 関数などを使わない従来からあるエクセル関数だけで実現できる方法を考えてみた。

countif, vlookup などの組み合わせ

結論としては以下のようになりました。

ポケモン名リスト3

本当は作業用の列を使いたくはないのだが、やむを得ない。たぶん、配列数式をマスターすればこれを回避できるのだろうが。

まずA列に中間計算結果を置く作業用の列とします。 B列が空欄の場合はカウントアップしないで、値（ポケモン名）がある場合だけカウントした値を入れていきます。

A2 セルに入れている関数はこれ:

=row(A1) - countif($B$2:B2,"")

row(A1) で 1,2,3,4... という連番を作り出す（定石らしい）、そしてその行までの範囲におけるB列内の空セルを数えるために、countif を使います。 ただし、このままでは、A列の3,6,7,10行のようなB列が空行部分にも index値 が入るため、次にD列で vlookup を使う上で不都合。 そこで、実際には A2 セルには、B列が空値なら、こっちも空値を入れる、という条件を追加:

=if(B2<>"", row(A1) - countif($B$2:B2,""), "")

これでA列に意図した値を表示することができました。

次は、結果を出すD列です。 これはみんな大好き vlookup を使えばよいだけです。つまり D2 セルには:

=vlookup(row(A1), $A$2:$B$11, 2, false)

例によって row(A1) しているので、この関数を D2:D11 までコピペすれば、1,2,3,4... の値を作り出すことができます。 あとは、A2:B11 のテーブル範囲を指定するだけです。実際にD列に表示したいのは、A2:B11 のテーブル範囲の2列目なので、2 を入れています。 最後は false で完全一致を指定。

ただし、D8:D11 の範囲は該当する値がないため、vlookup がエラーを返します。これを隠すために、iferror 関数でラップして完成です。

=iferror(vlookup(row(A1), $A$2:$B$11, 2, false), "")

vlookup を回避する

vlookup 関数の制限はマッチさせるキー列が必ず指定したテーブル範囲のもっとも左列に存在しなければならないことです。

エクセルシート設計者にとっては、A列は単なる作業用の列なので、できればユーザーの目に触れさせたくない。 たとえば、H列など右側の方の列に作業列を配置して、目につかないようにできないか？

となると、vlookup の限界につきあたります。 しかし、vlookup 相当の機能を match, index, row 関数などを使えば実現できるらしいので、それで代替してみます。

結果はこれ:

ポケモン名リスト4

H列は、先に説明した A列と同じです。H2セルは以下の関数が入っています:

=IF(B2<>"",ROW(A1)-COUNTIF($B$2:B2,""),"")

D列は vlookup を使わないで、次のようにします:

=IFERROR(INDEX($B$2:$B$11,MATCH(ROW(A1),H:H,0)-1,1),"")

match と index 関数の連携はポインタのポインタみたいな感じでかなり分かり辛いですね。 空のセルを取り除いたリストを得たいだけなのに、これだけややこしいことをしなければならないのはうれしくない。

まとめ

filter 関数はとても便利。これを、一回払い版の エクセルで使えるようにしてほしい。 または、サブスクリプションのエクセルのみの製品を出してもらいたい。(エクセルしか使わないから）

もうこれは、知恵を出すかお金を出すか、という問題になってしまった。

追伸

Google Spreadsheet にも filter 関数があり、同じように処理することができました。 filter 関数のためだけに MS-Office 365のサブスクリプションを払いたくない場合はこちらを使いましょう。