結合されたセルのオートフィルについて質問です。（長文です）

質問

結合されたセルのオートフィルについて質問です。（長文です）

いつもお世話になります。
Excel2000を利用して，住所一覧表を作成しています。

【sheet１】には，データ入力用として手入力していき，
【sheet２】には，sheet１のデータが反映されるように数式を入れ，
一覧表として表示されるように利用します。

例えば，【sheet１】ですが，

１行目には項目を，
Ａ１「番号」　Ｂ１「氏」　Ｃ１「名」　Ｄ１「〒」　Ｅ１「住所」　Ｆ１「ビル名」
という風に入力し，

２行目からはデータとして
Ａ２「１」　Ｂ２「山田」　Ｃ２「太郎」　Ｄ２「123-4567」　Ｅ２「*市*町」　Ｆ２「山田ビル」

Ａ３「２」　Ｂ３「山本」　Ｃ３「花子」　Ｄ３「987-6543」　Ｅ３「*群*町」　Ｆ３「山本ビル」

Ａ４　下に続く--

という風に，１行ずつ１つのセルごとに入力していきます。

そして【sheet２】には
「氏名」で1つのセルに，また「〒住所ビル名」として１つのセルに表示されるようにします。

【sheet２】例↓

Ａ１「番号」　Ｂ１「氏名」　Ｃ１「〒住所ビル名」　Ｄ１「備考」
Ａ２３４「１」　Ｂ２３４「山田太郎」　Ｃ２３４「123-4567**市**町山田ビル」　Ｄ２３４「備考」
Ａ５６７「２」　Ｂ５６７「山本花子」　Ｃ５６７「987-6543**群**町山本ビル」　Ｄ５６７「備考」

となります。

※【sheet２】の２行目以降は，３行ごとにセルを結合させます！
※表にするとこんな風になります↓（わかりにくいかも知れませんが...）

　　　　Ａ　｜　　Ｂ　　 　｜　　　Ｃ　　　　　｜　　Ｄ
-----------------------------------------------------　
１　　番号　｜ 　氏名　　　｜　　　住所　　　　｜　　備考
-----------------------------------------------------
２　　　　　｜　　　　　　 　　｜　〒123-4567　 　｜
３　　　1　 ｜　山田太郎　 ｜　　　**市**町　｜　　備考　　　　　　
４　　　　　｜　　　　　　 　　｜　　　　山田ビル ｜
-----------------------------------------------------

と表示されるようにしました。
まず氏名のセルにおいての式としては，

=IF(sheet1!B2="","",CONCATENATE(sheet1!B2,CHAR(10),sheet1!C2))

の式を【sheet２】のＢ２のセルに入れました。
すると，無事に山田太郎君が統合されて出てきてくれました！
ただ，この先がわかりません。。

この式を下の行にも反映させようとオートフィル機能を使って，
【sheet２】のＢ２３４のセルを下にドラッグしていくと，
式は出てきてくれたのですがセルが結合されている為

【sheet２】のＢ５６７には
=IF(sheet1!B5="","",CONCATENATE(sheet1!B5,CHAR(10),sheet1!C5))

【sheet２】のＢ８９１０には
=IF(sheet1!B8="","",CONCATENATE(sheet1!B8,CHAR(10),sheet1!C8))

と，当然ではあるのですが３行ごとの式としてでてしまいます。

上述した通り，
【sheet１】には１行に１人という入力方法なので，
上の式だと，【sheet２】のＢに表示されない人が出てきてしまいます。
山本花子さんが出てきてくれません（泣）

長くなりましたが，ここで質問です。

Ｂ２の式の
=IF(sheet1!B2="","",CONCATENATE(sheet1!B2,CHAR(10),sheet1!C2))を
オートフィルで
Ｂ５
=IF(sheet1!B3="","",CONCATENATE(sheet1!B3,CHAR(10),sheet1!C3))
Ｂ８
=IF(sheet1!B4="","",CONCATENATE(sheet1!B4,CHAR(10),sheet1!C4))
Ｂ１１
=IF(sheet1!B5="","",CONCATENATE(sheet1!B5,CHAR(10),sheet1!C5))
と１つづつあげていく事はできるでしょうか？
手入力が一番早いのでしょうか？

又，他に何かいい方法はありますでしょうか？

長文になってしまい申し訳ございません；；
又，初心者の為わかり辛い箇所があるかもしれません。
都度補足致しますので，
おわかりになる方がいらっしゃいましたら，宜しくお願い致します

回答No.4

keithin · ベストアンサー

まさに「数合わせの勘定をして自分で数式を考えるのはメンドクサイです。」のその部分で数合わせが間違ってたみたいでゴメンナサイ，ご迷惑をお掛けしました。

えっとそれで。。。

今の式で位置合わせのカギになっている部分を数式の中から取り出してみると，
=(ROW()+1)/3
これを２行目のセルに入れて３行セットで下にコピーしてみます。
確かに１，２，３…が出てて，これじゃだめですね。これが２，３，４…になるように数合わせをします。
手っ取り早く
=(ROW()+4)/3
にしてしまいましょう。

変更前：
=IF(sheet1!B2="","",CONCATENATE(sheet1!B2,CHAR(10),sheet1!C2))
修正後：
=IF(INDEX(sheet1!B:B,(ROW()+4)/3)="","",CONCATENATE(INDEX(sheet1!B:B,(ROW()+4)/3),CHAR(10),INDEX(sheet1!C:C,(ROW()+4)/3)))

お礼

２度もご回答頂き，
ありがとうございます！！
とんでもないです。
全然迷惑じゃないです，恐縮です(＞_＜)

そして…できました！！
感動です！
ちゃんと２行目からの山本君が出てきました…！

どうしてその部分を変更するのかという説明も，
すごくわかりやすく教えていただきありがとうございました！
なるほどーすごいですね…。。

他のお二方の回答者様にも教えて頂いたように，
１つの結果を出す際にも，
色々な方法があるのですね…。
今回でまた少しExcelの能力が少しだけ前進したような気がします(自分なりに…＾＾；)

２度もご回答頂き，本当にありがとうございました。
がんばって，皆様のような知識をつけられるよう，
少しずつがんばります（＾＾）v
ありがとうございました！

補足

すいません，
この場をお借りして…

皆様にポイントを差し上げたいのですが，
どうやらお一人にしかポイントは付与できないようになったみたいですね…。
（以前に，違う質問をした際，
２０ポイント付与した後，違う回答者様に１０ポイントを付与しようと思ったら出来ないことに気がつき，
あわてて教えてgooに，ある方に１０ポイントを付与したい旨のメールをしたところ，
仕様変更の為できないと教えられ，初めて気がつきました（＾＾；））

わかりにくく説明不足な私の質問にも，
素早く的確にお答えくださいました，
３人のご回答者さま，本当にありがとうございました。
かなり勉強になりました。
皆様のお力をお借りし，がんばって一覧表作成してみます！
ありがとうございました！！

回答No.3

mu2011

一例です。
Ｂ列の部分
=IF(OFFSET(Sheet1!$B$1,INT(ROW()/3),0)<>"",OFFSET(Sheet1!$B$1,INT(ROW()/3),0)&CHAR(10)&OFFSET(Sheet1!$B$1,INT(ROW()/3),1),"")

Ｃ列の部分
=IF(OFFSET(Sheet1!$B$1,INT(ROW()/3),0)<>"",OFFSET(Sheet1!$B$1,INT(ROW()/3),2)&CHAR(10)&OFFSET(Sheet1!$B$1,INT(ROW()/3),3)&CHAR(10)&OFFSET(Sheet1!$B$1,INT(ROW()/3),4),"")

お礼

素早いご回答ありがとうございました！

Ｂ列とＣ列の式につき，それぞれ教えていただき，
ありがとうございます！

このように作成すればいいのですね…。。
本当に勉強になります。

=IF(OFFSET(Sheet1!$B$1,INT(ROW()/3),0)<>"",OFFSET(Sheet1!$B$1,INT(ROW()/3),0)&CHAR(10)&OFFSET(Sheet1!$B$1,INT(ROW()/3),1),"")
をsheet２のＢ２に入れてみますと，
sheet１の１行目の氏名等の項目行が表示されたのですが，
「$B$1」の部分を「$B$2」にそれぞれ変更すると，できました！！！！
ちゃんと氏名が表示されました！
ありがとうございます！

Excelにも色々な式の作成方法があるんだなーと感心しています。
またお世話になることもあると思いますが，宜しくお願いいたします！

本当に助かりましたし，勉強になりました。
ありがとうございました！

回答No.2

keithin

＞又，他に何かいい方法はありますでしょうか？

一番手っ取り早い手は，シート１の手入力欄についてもまた３行一揃えにしておきます。
つまり
A2からA4をセル結合して番号の１を記入
B2からB4をセル結合して山田を記入
C2からC4をセル結合して太郎を記入
D2からD4をセル結合して123-4567を記入
E2からE4をセル結合して*市*町を記入
F2からF4をセル結合して山田ビルを記入

このように入力欄もセル結合して作成しておき，下向けにつるつるっとオートフィルドラッグして縦３横１セルの入力リストを大きく作っておけば，何も考えずにシート２もまたオートフィルドラッグで数式を展開できます。

参考：
既にシート１のデータを沢山埋めてしまったので，今さらシート１をやり替えたくありませんという場合。
もちろんデータを打ち換える必要も，また手作業で２行ずつ行を挿入していく必要もありません。

手順：
１行目タイトル行
２行目データ記入済
３行目データ記入済
４行目データ記入済　とすると
今ちょうどＡ列に連番が１２３と入っています。A2,A3,A4に。
A2:A4をコピーしてA5に貼り付け，更にA8に貼り付けると１２３１２３１２３と縦に並びます。
表全体を選択，A列の昇順で並べ替えると
番号は１１１２２２３３３となり，各１行目にデータが入って並べ変わります。

ついでに。
任意のあるJ1:J3の３つのセルを縦に１つにセル結合しておきます
J1:J3をコピー，A2:F10を選び，編集メニューの型式を選んで貼り付けの書式のみ貼り付けると，完成です。
続きの行も同様に３行ずつセル結合しておき，シート１全体は適宜行高さを整えて見やすく使いやすくしてください。

シート２の数式は最初の行に数式を記入，セル結合と共に２，３，４行をまとめて選択した状態からえいやっとオートフィルドラッグすることで，ご相談にかかれたように２行目５行目８行目を参照する式が続きます。

＃参考
ご質問の直接の回答としては
変更前：
=IF(sheet1!B2="","",CONCATENATE(sheet1!B2,CHAR(10),sheet1!C2))
変更後：
=IF(INDEX(sheet1!B:B,(ROW()+1)/3)="","",CONCATENATE(INDEX(sheet1!B:B,(ROW()+1)/3),CHAR(10),INDEX(sheet1!C:C,(ROW()+1)/3)))
などのように作れば出来ます。理屈自体は簡単ですが，数合わせの勘定をして自分で数式を考えるのはメンドクサイです。

お礼

おはようございます！

今やってみました。
めっちゃすごいです！！
「へぇぇぇ～！！！！！！」と朝から感動してしまいました。
このような方法があるのですね。。
すごく勉強になります！

sheet1を３行ごとにする方法は私も考えたのですが，
実は，sheet1は宛名ラベル作成（Wordを利用しての差し込み印刷）用に
利用しようと考えておりまして，３行結合させると，
ラベルがうまく表示されないのです（；_；）
セルを結合させることにより，Excelで空白の行（Ａ３Ａ４）は
ラベル側でも空白と認識されてしまい，
一人の宛先につき２つ空白のラベルができてしまうんですよね～。
（私の勉強不足で，もっと上手な方法があるのかもしれませんが…）

なので，
>♯参考
の式の方を利用させてもらいました。
この式だとちゃんと表示してくれます！！
ただ，この式をsheet２のＢ２に入れると，
sheet１の１行目の氏名等の項目行が表示されてしまいますよね？
sheet１の２行目から，sheet２のＢ２に表示させていきたいのですが，
その場合の式はどう変えたらいいのでしょうか？
ちょっと勉強してみます！

本当にありがとうございました！
またお世話になることがあるかもしれません。
その時はどうぞよろしくお願いいたします☆

ありがとうございました（＾▽＾）／

補足

素早いご回答ありがとうございます！

ご回答頂いた内容を上からゆ～っくり試しているのですが，
私の理解力が乏しく，まだ
> 番号は１１１２２２３３３となり，各１行目にデータが入って並べ変わります。
までしかすすんでおりません（＾＾；）

もう，このＰＣを閉めなければなりませんので，
明日また結果報告とお礼をさせて頂きます。

ごめんなさい！
ありがとうございました！

回答No.1

nattocurry

ROW関数を使っていろいろとやり方はあると思いますがその前に。

わざわざ3行を結合している理由は何ですか？
この質問文の例を見る限りでは、セルの高さを3倍にして1行に入力すれば良いだけだと思いますが・・・

あと、わざわざCONCATENATE関数を使っているのも不思議です。
氏名のところは、
CONCATENATE(sheet1!B2,CHAR(10),sheet1!C2)
の代わりに
sheet1!B2&sheet1!C2
で十分ですよね。
改行もしていないのに、CHAR(10)は何のために入れてあるのでしょうか？

住所の部分も
sheet1!D2&CHAR(10)&sheet1!E2&CHAR(10)&sheet1!F2
にして、セルの書式設定で、折り返して全体を表示する、にすれば良いだけだと思います。

お礼

素早いご回答ありがとうございます！
ごめんなさい，かなり説明足らずでしたね（；_；）

>　わざわざ3行を結合している理由は何ですか？

【sheet２】については，「この書式で使用してください！」と指定されている書式なのです。
これをそのまま表として使用する必要があるので，
あまり書式を動かしたくないというのが理由です。

>　改行もしていないのに、CHAR(10)は何のために入れてあるのでしょうか？

例では，個人名で表示させてもらったのですが，
実際に利用する時には，氏名だけではなく，
氏を＊＊会社　名を＊＊センターという感じで，会社名を入力する場合があります。
その際に，会社名と部署名を２行にして表示したいのでそのように式を入れました。

例でも，改行して書けば良かったですね。
完全に忘れていました…。。
ごめんなさい！

>　sheet1!D2&CHAR(10)&sheet1!E2&CHAR(10)&sheet1!F2
については，
セルを結合していない前提での式ですよね？
こういう方法もあるのですね…。
できれば，こちらを使いたいです（；_；）

Excelはまだまだ超初心者で，
わからないことも多く，おそるおそる式を作っている状態なのですが，
上記ご回答頂いた内容はとても勉強になりました。
色々な方法があるのですね！

説明不足ですいませんでした。
また何かありました時には，
お力添え頂ければ幸いです。
本当にありがとうございました！