エクセルで自動に行選び(空き行を取り除く)

場合によってエクセルで一定のデータを集めたいです。493-initial-table例えば、右のような表が有ります。この表の一列目が埋まっている行を集めたいとします。もし単純にコピペーで一回だけで行を集めるので有れば、結構にいい教材が有ります。しかし、時に動的に自動に更新して欲しいです。つまり、例えば、E10に「Michael」を入れたとします。希望としては、行を集めた結果に自動に「Michael」の行を追加されます。また、フィルターのように行を表示しないではなく、本当に連続の行でデータだけ集めたいです。この記事はこういう方法をご紹介します。

エクセルは計算表です。言い換えれば、計算データの変換表です。今回の行集めの根拠はEの列なので、当面の目的としては、E列の埋まっている行の番号を洗い出します。この番号一覧さえ分かれば、元の各行からデータをとればいい話しになります。最終的に下記のAF7:AJ17のテーブルにしたいです。以下はステップ分けて説明します。

493-final-tableステップ1

初めは、E列にどのセルが埋まっているかを得たいです。これは簡単です。ISBLANK関数を使用します。

この数式をT7に埋め込み、T17に引っ張ります。Eの列に埋まっている/埋まってないは1/0で表示されます。

ステップ2

Uの列をこの行まで一番近い埋まっている行はどの行かに設定します。例えば、9行目は埋まっているので、U9を9にします。10行目は埋まってないので、一番近くて埋まっている行は9行目ので、U10を9にします。7行前は一行もないので、U7を0にします。数式的に考えますと、同行のT列のセルが1の場合は、現在行のU列のセルを現在行の番号にし、そうでないと、上の行のU切のセルの値を取ります。数式で実装すれば、U7は下記のものになります。

その他のセルは同様に引っ張って作ります。

ステップ3

U列の番号を結果表の何行目に表示させるべきかを決めたいです。U列を観察してみれば、前行と現在行の数字が相違していれば、結果表の行数は増えます、そうでなければ、増えません。V7は下記の数式になります。

ステップ4

変換は後半になります。このステップではU列の重複値を取り除きます。ここはVLOOKUP関数を活用します。VLOOKUP関数は、一定の範囲内で縦で指定の値を検索し、相応の値を右の列から取り出すことはできます。ただ、相応の値を左から取り出すことはできないようです。V列は1,2,3…になっていて、V列から順番を検索して、U列から相応の値を取り出したいです。VLOOKUPを使いたいので、U列とV列の位置を反転させてもいいですが、説明しやすさのため、U列の内容を「=U7」のようにW列で引用します。Uで上り順の順番を検索したいので、X列で重複しない上り順の数字を用意します。そして、Y7を次のような数式にします。

Y行の他の列は引っ張ってできます。

ステップ5

所要の列番号は集まりました。必要なセル位置を割り出すことは簡単になります。Z7を下記のような数式にします。

Z7:AD17は引っ張ってできます。この範囲のすべての列はY列を参照するため、Yの前に$をつけてから引っ張ります。

ステップ6

いよいよ最後になります。Z7:AD17のセルを値を変換します。ここに二個の落とし穴を注意する必要が有ります。例えば「=A1」の場合、A1に何もかかれていなければ、「=A1」の値は0になります。空きではないです。空きを空きのままに維持したいので、判断を入れます。もう一個の落とし穴はISBLANKは使えません。ISBLANKは数式を検知した場合は、空きだと認識できません。このため、比較演算子を使用します。AF7に下記の数式を入れます。

AF7:AJ17に他のセルはAF7を引っ張って作ります。

できました!!

纏め

この技術はかなりパワーを発揮する場合が有ります。例えば、一年中の営業日の日付を集めたい場合は使えます。スケジュール管理の効率向上にかなり役立ちます。実際の例としては、「簡単ガント図」の記事を参考してください。

Leave a Reply

Your email address will not be published. Required fields are marked *