Excelで空白のセルを詰めたいのですが・・・

質問

空白のセルを詰めたいのですが・・・
次のようにしたいのですが書籍やネット等で調べたのですが、よく解らないのでどなたかご教示お願いします。

OS WinXP ExcelXP使用です。

Seet1で簡単な関数を作り、データを集計したのですが、関係のない文字や数字が入っているので、
このデータをSeet2に整理して奇数行に名前、偶数行に数値を表示させ、下記の様に空白のセルを詰めたいのです。

これを
　　Ａ　　　Ｂ　　　Ｃ　　　Ｄ　　　Ｅ　　　Ｆ
１　山田　　田中　　橋本　　山田　　山本
２　５　　　２　　　８　　　５　　　６
３　　　　　木村　　北野　　橋本　　　　　　木村
４　　　　　４　　　６　　　６　　　　　　　３

この様に
　　Ａ　　　Ｂ　　　Ｃ　　　Ｄ　　　Ｅ　　　Ｆ
１　山田　　田中　　橋本　　山田　　山本　　木村
２　５　　　２　　　８　　　５　　　６　　　４
３　北野　　橋本　　木村
４　６　　　６　　　３
空白はランダムに入っています。
A1の山田はA1=Sheet1!D2　B2の5はA2=Seet!D3、E5の山田はSeet!1=F6、E2の5はSeet1!=F7、というように同じ名前や数値でも
Seet1の別々のセルを参照しています。
私の質問の仕方に不備がありましたらご指摘いただき、お解りになる方ご教示お願いします。

回答No.7

KURUMITO

シート1では2行目から下方にお示しのデータがあるとします。
作業列としてＨ2セルには次の式を入力して下方にオートフィルドラッグします。
=INDEX($A:$F,ROUNDUP(ROW(A1)/6,0)*2,MOD(ROW(A1)-1,6)+1)
Ｉ2セルには次の式を入力して下方にオートフィルドラッグします。
=INDEX($A:$F,ROUNDUP(ROW(A1)/6,0)*2+1,MOD(ROW(A1)-1,6)+1)
Ｊ2セルには次の式を入力して下方にオートフィルドラッグします。
=IF(H2<>0,MAX(J$1:J1)+1,"")
シート2では2行目からデータを表示させるとしてＡ2セルには次の式を入力してＦ2セルまでオートフィルドラッグしたのちに下方にもオートフィルドラッグします。
=IF(COUNTIF(Sheet1!$J:$J,COLUMN(A1)+ROUNDDOWN((ROW(A1)-1)/2,0)*6)=0,"",IF(MOD(ROW(A1),2)=1,INDEX(Sheet1!$H:$I,MATCH(COLUMN(A1)+ROUNDDOWN(ROW(A1)/2,0)*6,Sheet1!$J:$J,0),1),INDEX(Sheet1!$H:$I,MATCH(COLUMN(A1)+ROUNDDOWN((ROW(A1)-1)/2,0)*6,Sheet1!$J:$J,0),2)))

お礼

ご指示の通りやってみましたが、上手くいきませんでした。
私の質問の仕方がマズかったようです。
一度質問を締切り、質問の仕方を整理し、再度質問させていただきます。
この度はありがとうございました

回答No.6

MackyNo1

>1つのタグに複数のセルが入っておりSeet1の計算結果をSeet3の製品添付タグに直接参照させると空白のタグがかなりあり、これを印刷すると用紙が無駄になってしまうのです。

1つのタグに複数のセルが入っているとはどのような状況で、それを参照させるとどうして空白セルを参照することになるのでしょうか？

もし、空白セルで参照したくないなら、直接必要なデータだけを表示するような設定にしたほうが（おそらく配列数式を使う必要はありますが）簡単だと思うのですが・・・

>そこで、確認用と印刷の際に用紙を無駄にしないためSeet2を挿み、空白を詰めるようにしたかったのです。

もし、空白セルになる場合は参照したくないなら、いったん別シートに（2段に表示してそのデータを詰めで）表示するのではなく、直接必要なデータだけを直接印刷用シートに表示するような設定にしたほうが数式が簡単になると思います。

いずれにしろ元データのシートのレイアウトとまとめたいシートのレイアウトを具体的に例示されたほうが、皆さんから効率的な回答が寄せられると思います。

お礼

私の質問の仕方がマズかったようです。
一度質問を締切り、質問の仕方を整理し、再度質問させていただきます。
この度はありがとうございました。

回答No.5

某HN クロメート(Chromate)（@CoalTar）

読み返したら訂正部分が。。。m(_ _)m
>>A6:F9セルまでを選択してから、数式バーに下記セルをコピペ
×数式バーに下記セルを
○数式バーに下記数式を

>>名前のある場所を(列番号*10+行番号)の数値に置き換え、昇順にしている。
×(列番号*10+行番号)
○(行番号*10+列番号)

エラー処理をするとこんな感じかな？
=IF(ISERROR(SMALL(IF((A1:F4<>"")*(MOD(ROW(A1:F4),2)=1),10*ROW(A1:F4)+COLUMN(A1:F1)),INT((ROW(A1:A4)-1)/2)*6+COLUMN(A1:F1))+(MOD(ROW(A1:F4),2)=0)*10),"",
INDEX(A1:F4,
INT((SMALL(IF((A1:F4<>"")*(MOD(ROW(A1:F4),2)=1),10*ROW(A1:F4)+COLUMN(A1:F1)),INT((ROW(A1:A4)-1)/2)*6+COLUMN(A1:F1))+(MOD(ROW(A1:F4),2)=0)*10)/10),
MOD(SMALL(IF((A1:F4<>"")*(MOD(ROW(A1:F4),2)=1),10*ROW(A1:F4)+COLUMN(A1:F1)),INT((ROW(A1:A4)-1)/2)*6+COLUMN(A1:F1))+(MOD(ROW(A1:F4),2)=0)*10,10)))

同じ数式が多くあるので名前を定義した方がわかりやすそう
A6セルにおいて名前の定義
名前 数式
=SMALL(IF((A1:F4<>"")*(MOD(ROW(A1:F4),2)=1),10*ROW(A1:F4)+COLUMN(A1:F1)),INT((ROW(A1:A4)-1)/2)*6+COLUMN(A1:F1))+(MOD(ROW(A1:F4),2)=0)*10

A6:F9セルを選択して
=IF(ISERROR(数式),"",INDEX(A1:F4,INT(数式/10),MOD(数式,10)))
[Ctrl]+[Shift] ＋[Enter] で確定、配列数式です({}で囲まれる)

お礼

ご指示の通りやってみましたが、上手くいきませんでした。
私の質問の仕方がマズかったようです。
一度質問を締切り、質問の仕方を整理し、再度質問させていただきます。
この度はありがとうございました

回答No.4

某HN クロメート(Chromate)（@CoalTar）

こんな数式を立てるほうがどうかと思いますが、
A6:F9セルまでを選択してから、数式バーに下記セルをコピペ
=INDEX(A1:F4,INT((SMALL(IF((A1:F4<>"")*(MOD(ROW(A1:F4),2)=1),10*ROW(A1:F4)+COLUMN(A1:F1)),INT((ROW(A1:A4)-1)/2)*6+COLUMN(A1:F1))+(MOD(ROW(A1:F4),2)=0)*10)/10),MOD(SMALL(IF((A1:F4<>"")*(MOD(ROW(A1:F4),2)=1),10*ROW(A1:F4)+COLUMN(A1:F1)),INT((ROW(A1:A4)-1)/2)*6+COLUMN(A1:F1))+(MOD(ROW(A1:F4),2)=0)*10,10))
[Ctrl]+[Shift] ＋[Enter] で確定、配列数式です({}で囲まれる)

名前のある場所を(列番号*10+行番号)の数値に置き換え、昇順にしている。
F列(6列分)で区切っている
エラー処理なし。
添付図参照。参考まで

ついでに「Seet」ではなく「Sheet」

回答No.3

MackyNo1

質問文を読んでも、そのようなデータ処理をしたい目的がよくわかりません。

イメージとしては、元のデータで処理したほうが簡単なような気がするのですが、どうして参照するセルに空白セルがあるのでしょうか？（数式を削除したのでしょうか？）

本当に空白セルなら（数式で空白文字列ではないなら）、空白を除去して左に詰めて表示するなら、データ範囲を選択して、Ctrl+Gでジャンプダイアログを出し「セル選択」で「空白セル」にチェックを入れ「OK」し、選択された空白セル上で右クリックして「削除」で「左方向にシフト」にしてみて下さい。

この操作で不都合があるなら、元のデータのデータベースシートと整理したいリストのレイアウトを提示されたほうがよいと思います。

補足

MackyNo1様
ご回答有難うございます。
ここでの文字のみの質問では空白が勝手に詰められてしまうので、文章ではお解り頂けづらいと思い、画像を添付いたしました。
質問には関係ないと思ったので、書かなかったのですが、何故、このようなことをするのかと言いますと、本来、Seet3があり、そこには製品添付用のタグがありまして
1つのタグに複数のセルが入っておりSeet1の計算結果をSeet3の製品添付タグに直接参照させると空白のタグがかなりあり、これを印刷すると用紙が無駄になってしまうのです。
そこで、確認用と印刷の際に用紙を無駄にしないためSeet2を挿み、空白を詰めるようにしたかったのです。
一度質問を投稿すると回答されるまで、補足や訂正が出来ないため、そのままでしたが、Seet2のA1：F4にはSeet1の参照セルが未入力の場合"0"を表示させないため、例えば、Seet2のA1セルには「IF(Seet1!D9="", "",Seet1!D9)」
を入れています。そのため、Ctrl+G～の技は使えないのです。ちなみに質問文での（E5の山田）は（E1の山田）の誤りです訂正いたします。
私の説明不足により、随分ややこしくなってしまい失礼いたしました。

回答No.2

tom04

No．1です！
たびたびごめんなさい。
前回の数式は単純に列の空白を詰めて、左に表示させる方法でした。

質問の画像を拝見すると、B3セルの木村さんがF1セルに表示されていますね？

前回の方法ではこのようにはなりませんので
読み流してください。
どうも何度も失礼しました。m(__)m

回答No.1

tom04

こんにちは！
参考になるかどうか判りませんが・・・
一例です。
質問の画像どおりの配置とします。
Sheet1の元データをSheet2に表示させるようにしています。

↓の数式をSheet2のA1セルにコピー＆ペーストしてみてください。
尚、配列数式になってしまいますので
そのままではエラーになると思います。
貼り付け後、F2キーを押すか、数式バー内で一度クリックします
編集可能になりますので
Shift＋Ctrl＋Enterキーで確定してください。
数式の前後に｛　｝マークが入り配列数式になります。

A1セルに入れる数式は
=IF(COUNTA(Sheet1!1:1)<COLUMN(A1),"",INDEX(Sheet1!1:1,,SMALL(IF(Sheet1!1:1<>"",COLUMN(Sheet1!1:1)),COLUMN(A1))))
です。

このA1セルの数式を列方向と行方向にオートフィルでコピーすると
希望に近い形にならないでしょうか？

以上、参考になれば幸いです。m(__)m