Excel 空白セルを詰めて表示する方法

質問

＜図１＞
　　　　A　　　　　B
1 　　あいう
2 　　　　　　　　えお
3 　　かきく
4 　　　　　　　　けこ
5 　　さしす

＜図２＞各行の空白セルを詰めて表示させる
　　　　A　　　　　B　　　　
10　　あいう　　けこ
11　　かきく
12　　さしす
13　　えお

以前の投稿で以下のようなものはあったのですが、
上記のようにしたい場合、どの様な関数を組めばいいのでしょうか？
------以下，参考にした例------

＜図１＞
　　　　A　　　　　B
1 　　あいう
2 　　　　　　　　えお
3 　　かきく
4 　　　　　　　　けこ
5 　　さしす

＜図２＞各行の空白セルを詰めて表示させる
　　　　A　　　　　B　　　　
10　　あいう　　　えお
11　　かきく　　　けこ
12　　さしす
13
14

【関数】

A10: =IF(ROW(A1)>COUNTA(A$1:A$5),"",INDEX(A$1:A$5,SMALL(IF(A$1:A$5<>"",ROW($A$1:$A$5),""),ROW(A1))))

回答No.12

Nouble

お恥ずかしい限りですね
もう解決しているとされる下段の式を書いていましたね、

取り急ぎ
上段用の式を５行表示ではありますが作りましたので
お確かめください。

=OFFSET($A$1,MOD(10-LARGE(INDEX(($A$1:$B$5<>"")*(11-ROW($A$1:$B$5))-($A$1:$B$5<>"")*(COLUMN($A$1:$B$5)-1)*5,,),ROW(A1)+(COLUMN(A1)-1)*5),5),TRUNC((10-LARGE(INDEX(($A$1:$B$5<>"")*(11-ROW($A$1:$B$5))-($A$1:$B$5<>"")*(COLUMN($A$1:$B$5)-1)*5,,),ROW(A1)+(COLUMN(A1)-1)*5))/5),1,1)
A10に此を入力、
A10:B14にフィルなどでコピーしてください

エラー処理はしていませんが
イレギュラー時はＣ１を参照するだけですから
そもそもC1がヌルならばエラー処理も要らない…
と、いうことで　敢えてしていません。

長くなってしまいましたが致し方ありませんね

ところで
お示し頂いた式
=IF(ROW(A1)>COUNTA(A$1:A$5),"",INDEX(A$1:A$5,SMALL(IF(A$1:A$5<>"",ROW($A$1:$A$5),""),ROW(A1))))
は、私の環境Ｅｘｃｅｌ２００３では、動作確認が取れませんでした。

大体ＩＦ構文の使い方がめちゃくちゃなので、
はっと見で「ダメダメだ」と解る位の
低い次元の話しなのですが、
確認してみたらやはり駄目ですね。

恐らく２００７でも駄目でしょう…

式中に配列数式のＩＦを使うのは本当に難しいレベル、
私自身避けて通る物ですから、
ね。

回答No.11

Nouble

済みません、式間違えていました。
お詫びの上訂正します。

誤記
=IF(SUMPRODUCT((A$1:A$5<>"")+0)<ROW(A1),"",OFFSET(A$1,LARGE(INDEX((A$1:A$5<>""),,)*(6-ROW(A$1:A$5)),A1)-1,0,1,1))

正記
=IF(SUMPRODUCT((A$1:A$5<>"")+0)<ROW(A1),"",OFFSET(A$1,6-LARGE(INDEX((A$1:A$5<>"")*(6-ROW(A$1:A$5)),,),ROW(A1))-1,0,1,1))

A10に記入後
フィルなどでA10:B15にコピーしてください。

失礼しました。

回答No.10

KURUMITO

最初の列では４行以下になってもよいものとして答えてましたが、４行にとどめたいとのことですね。
その場合にはＣ１セルに次の式を入力して、式を確定する段階でＣｔｒｌキーとＳｈｉｆｔキーを押しながらＥｎｔｅｒキーを押します。
その後に式をＤ１セルまで横にドラッグコピーしたのちに下方にもドラッグコピーします。

=IF(COLUMN(A1)=1,IF(ROW(A1)>COUNTA($A$1:$A$5)+COUNTA($B$1:$B$5),"",IF(ROW(A1)<=COUNTA($A$1:$A$5),INDEX($A$1:$A$5,SMALL(IF($A$1:$A$5<>"",ROW($A$1:$A$5),""),ROW(A1))),IF(AND(ROW(A1)<=4,ROW(A1)<=COUNTA($A$1:$A$5)+COUNTA($B$1:$B$5)),INDEX($B$1:$B$5,SMALL(IF($B$1:$B$5<>"",ROW($B$1:$B$5),""),ROW(A1)-COUNTA($A$1:$A$5))),""))),IF(ROW(A1)<=COUNTA($A$1:$A$5)+COUNTA($B$1:$B$5)-4,INDEX($B$1:$B$5,SMALL(IF($B$1:$B$5<>"",ROW($B$1:$B$5),""),ROW(A1))),""))

回答No.9

Nouble

失礼しました、式の使い方説明をし忘れました。
A10に先の式を書き込んで
A10:B15の全体に
フィルやコピーなどで広げてお使いください。

回答No.8

Nouble

=IF(SUMPRODUCT((A$1:A$5<>"")+0)<ROW(A1),"",OFFSET(A$1,LARGE(INDEX((A$1:A$5<>""),,)*(6-ROW(A$1:A$5)),A1)-1,0,1,1))
これで行けると思うのですけど…

如何でしょうか？
お役に立てていたなら幸いです。

以下お遊び
Option Base 1
Sub 抽出ベタ方()
Dim カウンタ3 As String, カウンタ2 As Long, カウンタ1 As Long, 目 As Range, レンジ2 As Range, レンジ1 As Range, 字(1 To 5, 1 To 2) As String, 本頁 As WorkSheets
　　Set 本頁 = ThisWorkBook.ActiveSheets
　　With 本頁
　　　　Set レンジ1 = .Range("A1:B5")
　　　　Set レンジ2 = .Range("A10:B15")
　　End With
　　For カウンタ1 = 1 To 2
　　　　カウンタ3 = 0
　　　　For カウンタ2 = 1 To 5
　　　　　　If レンジ1.Cells(カウンタ2, カウンタ1) <> "" _
　　　　　　Then
　　　　　　　　カウンタ3 = カウンタ3 + 1
　　　　　　　　Let 字(カウンタ3, カウンタ1) = CStr(レンジ1.Cells(カウンタ2, カウンタ1).Value)
　　　　　　End If
　　　　Next カウンタ2
　　Next カウンタ1
　　For カウンタ1 = 1 To 2
　　　　For カウンタ2 = 1 To 5
　　　　　　レンジ2.Cells(カウンタ2, カウンタ1).Value = 字(カウンタ2, カウンタ1)
　　　　Next カウンタ2
　　Next カウンタ1
End Sub

Sub 抽出()
Dim レンジ1, As Range, レンジ2 As Range, カウンタ2 As Long, カウンタ1 As Long, 本頁 As WorkSheets
　　Set 本頁 = ThisWorkBooks.ActiveSheets
　　For カウンタ1 = 1 To 2
　　　　カウンタ2 = 0
　　　　Set レンジ2 = 本頁.Range("A1:A5").Offset(0,カウンタ1 - 1).SpecialCells(xlCellTypeFormulas)
　　　　If Not レンジ2 Is Nothing _
　　　　Then
　　　　　　For Each レンジ1 In レンジ2
　　　　　　　　カウンタ2 = カウンタ2 +1
　　　　　　　　Set 本頁.Range("A10").Cells(カウンタ2, カウンタ1) = レンジ1
　　　　　　Next レンジ1
　　　　End if
　　Next カウンタ1
End Sub

お礼

回答ありがとうございます。

回答No.7

tom04

こんばんは！
VBAでやれば一発でできそうですが・・・
関数での方法をご希望だというコトですので、

↓の画像のようにD・E列を作業用の列とします。

D1セルに
=IF(A1="","",COLUMN(A1)*100+ROW())
という数式を入れE5セルまでオートフィルでコピーしておきます。

そしてA10セルに
=IF(COUNT($D$1:$E$5)<ROW(A1)+(COLUMN(A1)-1)*4,"",INDEX($A$1:$B$5,MOD(SMALL($D$1:$E$5,ROW(A1)+(COLUMN(A1)-1)*4),100),INT(SMALL($D$1:$E$5,ROW(A1)+(COLUMN(A1)-1)*4)/100)))
という数式を入れ、列・行方向にオートフィルでコピーで画像のような感じになります。m(_ _)m

回答No.6

KURUMITO

回答Ｎｏ２，３，４です。

Ｃ１セルには次の式を入力して、式を確定する段階でＣｔｒｌキーとＳｈｉｆｔキーを押しながらＥｎｔｅｒキーを押します。
その後にその式を下方にドラッグコピーします。

=IF(ROW(A1)>COUNTA(A$1:A$5)+COUNTA(B$1:B$5),"",IF(ROW(A1)<=COUNTA(A$1:A$5),INDEX(A$1:A$5,SMALL(IF(A$1:A$5<>"",ROW($A$1:$A$5),""),ROW(A1))), IF(ROW(A1)<=COUNTA(A$1:A$5)+COUNTA(B$1:B$5),INDEX(B$1:B$5,SMALL(IF(B$1:B$5<>"",ROW(B$1:B$5),""),ROW(A1)-COUNTA(A$1:A$5))),"")))

お礼

回答本当にありがとうございます。
以下で試したところ
　　　　A　　　　　B
1 　　あいう
2 　　　　　　　　えお
3 　　かきく
4 　　けこ
5 　　さしす

　　　　A　　　　　B
1 　　あいう　　えお
2 　　かきく　　あいう
3 　　けこ　　　かきく
4 　　さしす　　けこ
5 　　えお　　　さしす
となってしまいます。

回答No.5

mt2008

そんなややこしいのを関数でやったら、検証もメンテナンスも大変です。
マクロでやってみました。

Sub Sample()
　　Dim rRange As Range
　　Set rRange = Range("A1:B5") '対象範囲を設定
　　nStartRow = 10 '貼り付け開始行
　　nCount = 4 '何行まで貼り付けるか

　　nDatRow = nStartRow
　　nDatCol = 1
　　For i = 1 To rRange(rRange.Count).Column
　　　　For j = 1 To rRange(rRange.Count).Row
　　　　　　If rRange(j, i) <> "" Then
　　　　　　　　Cells(nDatRow, nDatCol) = rRange(j, i)
　　　　　　　　nDatRow = nDatRow + 1
　　　　　　　　If nDatRow >= (nStartRow + nCount) Then
　　　　　　　　　　nDatRow = nStartRow
　　　　　　　　　　nDatCol = nDatCol + 1
　　　　　　　　End If
　　　　　　End If
　　　　Next j
　　Next i
End Sub

お礼

回答ありがとうございます。
事情があってマクロではなく関数で行いたいんです。

回答No.4

KURUMITO

回答Ｎｏ２です。
Ａ列やＢ列の１０行以内にデータが入力されているのでしたらＣ１セルには次の式を入力しＤ１セルまで横にドラッグコピーしたのちに下方にもドラッグコピーします。

=IF(ROW(A1)>4,"",IF(COLUMN(A1)=1,IF(ROW(A1)<=COUNTIF($A$1:$A$10,"*?"),INDEX($A$1:$A$10,ROW(A1)*2-1),INDEX($B$1:$B$10,(ROW(A1)-COUNTIF($A$1:$A$10,"*?"))*2)), IF(COLUMN(A1)=2, IF(ROW(A1)<=COUNTIF($A$1:$A$10,"*?")-4,INDEX($A$1:$A$10,8+ROW(A1)*2-1), IF(AND(ROW(A1)>COUNTIF($A$1:$A$10,"*?")-4,ROW(A1)<=COUNTIF($A$1:$A$10,"*?")-4+COUNTIF($B$1:$B$10,"*?")),INDEX($B$1:$B$10,(ROW(A1)-(COUNTIF($A$1:$A$10,"*?")-4))*2),"")))))

前回回答２の式でうまくいかなかったのはＡ列やＢ列でお示しのデータ以外に下の行の方にデータが入力されていたことによるものでしょう。

お礼

何度も回答を入れてくださり本当にありがとうございます。
　　　　A　　　　　B
1 　　あいう
2 　　　　　　　　えお
3 　　かきく
4 　　けこ
5 　　さしす

で試したところ
（これ以下のセルには一切にデータはありません。）
以下の様になってしまいうまくいきません。
　　　　A　　　　　B
1 　　あいう　　　えお
2 　　かきく　　　
3 　　さしす
4 　　0
5

回答No.3

KURUMITO

回答Ｎｏ２です。こちらでは式を入力して正しく表示されることを確認した上で答えています。ご自分で直接セルそれぞれにデータを入力して試験してみてください。Ａ列やＢ列にはお示しのデータ以外にはないことを確認してください。
Ｃ１セルには次の式を入力します。

=IF(ROW(A1)>4,"",IF(COLUMN(A1)=1,IF(ROW(A1)<=COUNTIF($A:$A,"*?"),INDEX($A:$A,ROW(A1)*2-1),INDEX($B:$B,(ROW(A1)-COUNTIF($A:$A,"*?"))*2)), IF(COLUMN(A1)=2, IF(ROW(A1)<=COUNTIF($A:$A,"*?")-4,INDEX($A:$A,8+ROW(A1)*2-1), IF(AND(ROW(A1)>COUNTIF($A:$A,"*?")-4,ROW(A1)<=COUNTIF($A:$A,"*?")-4+COUNTIF($B:$B,"*?")),INDEX($B:$B,(ROW(A1)-(COUNTIF($A:$A,"*?")-4))*2),"")))))

お礼

再度回答ありがとうございます。
確かに
　　　　A　　　　　B
1 　　あいう
2 　　　　　　　　えお
3 　　かきく
4 　　　　　　　　けこ
5 　　さしす

の場合はうまくいきます。
説明が不足し申し訳ございません。
空セルの位置は可変となります。
つまり
　　　　A　　　　　B
1 　　あいう
2 　　　　　　　　えお
3 　　かきく
4 　　けこ
5 　　さしす
の場合は
　　　　A　　　　　B
1 　　あいう
2 　　かきく
3 　　けこ
4 　　さしす
5 　　えお　　　　　　

の様に空セルの位置関係なく詰めて表示したいのです。