[エクセル上級] 空白セルを詰める関数(配列数式使用)

      2016/07/01

空白セルを詰める(関数のみ)では、作業用セルとして2列(応用編では1列)を使っていました。

ここでは、「配列数式」という方法を使って、作業用セルを使わずにデータを詰める方法を紹介します。もちろんマクロも使いません。

1

SPONSORED LINK

SMALL関数で小さいものから列挙

解説の都合上、まず数字を並べた列(C列)を作ります。この列は後で削除します。

次に、D列に「n番目の『空白じゃないセル』は何行目にあるか?」を表示させます。A列の各行に対して、「文字が入っていればその行番号」「空白なら空白」を求め、その中でn番目に小さい数を表示させます。

数式を入力したらCtrl+Shift+Enterを押します。

1

すると配列数式となり、数式が中括弧 { } で囲まれます。

2

SPONSORED LINK

配列数式でやっていること

この配列では、「文字が入っていればその行番号」「空白なら空白」を計算した作業列Bが、配列で書かれたIF文に埋め込まれています。

3

ここでは(C1=)1番目に小さい数なので、「1」が算出されます。

下までコピーすれば、「n番目の『空白じゃないセル』は何行目にあるか?」の結果が入ります。例えばD4では、「4番目の『空白じゃないセル』」すなわち「DDD」は7行目なので、「7」が結果となります。

今回は『空白じゃないセル』が6個なので、7番目からはエラーとなります。このエラーはあとで処理します。

4

INDEX関数でデータの取り出し

ここまで来ればあとは簡単。A列から1,2,4,7,…番目のデータをINDEX関数で取り出します。

INDEX関数の行番号にさっき求めた「n番目」が入るようにし、A列からn番目のデータを取り出します。

Ctrl+Shift+Enterを押すのをお忘れなく!

5

下までコピーすると、空白を詰めたデータが取り出せました。

6

仕上げ

最後に、最初に作った作業セル(何番目のデータが欲しいか)を埋め込みます。

自分自身のセルの行番号(ROW関数)と、先頭の行番号を比較し、何番目かを求めます。先頭の行は絶対参照にします。

8

Ctrl+Shift+Enterを押し、下までコピーしたら、作業用セルが不要になります。

9

これで作業用セルをまったく使わず、データを詰めることができました!

itjo レスポンシブ 本文下

 - Excel , ,