エクセルで空白セルを寄せたい

質問

エクセル２００２使用です
ＶＢＡは使わずに関数だけで空白セルがあるセルを詰めて表示したいのですが・・・
　　Ａ｜Ｂ｜Ｃ｜Ｄ｜Ｅ｜Ｆ｜Ｇ
１　あ　い　う　え　お　か　き
２　○　　　○　○　　　○
の表を
　　Ａ｜Ｂ｜Ｃ｜Ｄ｜Ｅ｜Ｆ｜Ｇ
１　あ　う　え　か　
２　○　○　○　○
のように表を書き換えたいのです。

1行目には必ず文字が入力されています。
2行目には入力されているセルと空白セルが不定期に入力されています。

2行目のセルが空白ならば、1行目のセルも削除して左へ詰めて
表を転記したいのです。

よろしくお願いします。

回答No.3

odessa7 · ベストアンサー

すいません。★1の関数が間違ってました。
　正しくは
=IF(A3="",100,COLUMN(A3))
　です。

　上記の式をコピーして、Ａ1セル～その行の最後まで貼り付けです。
　これで表示されるはずです。

【解説】
　まず★1の行では「2つ下のセルが空白なら100を、空白でなければ列番号を返す」という関数を組んでいます。この関数により、お示しの表の場合ですと「1、100、3、4、100、5、100」となります。（下記参照）

　　Ａ｜Ｂ｜Ｃ｜Ｄ｜Ｅ｜Ｆ｜Ｇ
１　１　百　 ３　４　百　５　百
２　あ　い　う　え　お　か　き
３　○　　　○　○　　　○

　この数値を、SMALL関数で小さい順に並びかえれば、「1、3、4、5、100、100」となりますね？ですから、あとはHLOOKUP関数で対応するセルを参照してあげれば良いわけです。

　具体的には★2関数ですと、例えばA4セルは
=HLOOKUP(1,検索範囲,2)
　という意味です。つまり一番上の行から「1」を探して、見つかればその2つ下の値（＝あ）を返す、と言う式です。

　その隣のB4セルは
=HLOOKUP(3,検索範囲,2)
　という意味です。つまり一番上の行から「3」を探して、見つかればその2つ下の値（＝う）を返す、と言う式です。

　このとき、「1」や「3」をどうやって探すのか？と言うことですが、これが前述したSMALL関数の役目です。つまり、「1、3、4、5、100、100」の中から1番小さい数（＝1）、2番目に小さい数（＝3）を返すわけです。

　ちょっとややこしい説明ですが、ご理解いただけましたでしょうか？

お礼

odessa7様　何度もお手数をおかして申し訳ございませんでした。
完璧です。たいへん助かりました。
今後ともよろしくお願いします。

回答No.2

odessa7

関数を使う方法をご紹介します。（作業セルを使います）

　まずお示しの表の上に1行挿入し、下記の関数を入れます。
=IF(A2="",100,COLUMN(A2))・・・★1
　この関数をコピーして、行の終わりまで貼り付けてください。

　その後、お示しの表の下の行に次の関数を入れます。
=IF(SMALL($A$1:$G$1,COLUMN(A2))=100,"",HLOOKUP(SMALL($A$1:$G$1,COLUMN(A2)),$A$1:$G$3,2,FALSE))・・★2

　その下の行に次の関数。
=IF(SMALL($A$1:$G$1,COLUMN(A3))=100,"",HLOOKUP(SMALL($A$1:$G$1,COLUMN(A3)),$A$1:$G$3,3,FALSE))・・★3
　（★2、★3とも行の最後まで貼り付けです）

　おさらいですが、表の構成は下記のようになります。
　　Ａ｜Ｂ｜Ｃ｜Ｄ｜Ｅ｜Ｆ｜Ｇ
１　★1
２　あ　い　う　え　お　か　き
３　○　　　○　○　　　○
４　★2
５　★3

　★1の関数の「A2="",100」の100の部分は、大きい数字であれば何でも構いません。

補足

odessa7　さま早速のご回答ありがとうございます。
空白が左によらないのですが・・・
うまく出来そうな気がするのですが、うまくいきません。
お時間ございましたらご指導お願いします。

回答No.1

5656flyfish

以下の手順で行ってみてはいかがでしょう?

1.対象セル範囲をコピーする

2.他のシートなどに形式を選択して貼り付け.
(ダイアログボックスが出たら,”行列を入れ替える”にチェックを入れる)

3.行列が入れ替わったらオートフィルタを掛けてリストから○を選択.

4.フィルタが掛けられた(○のみ抽出されている)ことを確認して,範囲選択.
メニューバーの編集からジャンプを選択.
セル選択のボタンを押下し,ダイアログボックスにて可
視セルにチェックを入れOKボタン押下

5.範囲選択されたままコピーして,手順2同様に貼り付け.

お礼

5656flyfish 様早速のご回答ありがとうございます。
こんな方法があるのですね。
うまくできました。ありがとうございます。
が、今回は自動的に処理させたいのでなんとか関数のみで行いたいのです。もし、コピーするセル範囲にもともとオートフィルを設定していたら手作業はなくなるでしょうか？
勉強してみます。
今後ともよろしくお願いします。ありがとうございました。