VBAを使わずに複数シートの列データを縦に並べたい

質問

フォーマットの異なる表が4種類あり、集計用シートと合わせて5シートを1つのブックにまとめています。各表はカテゴリーが異なるWebサイトのURLリストの列を持っていて、そのURLを集計用シートにまとめたい（縦に並べたい）のですが、Web検索で調べながらOFFSET、ROW、MATCH関数などで挑戦するも、関数の整理がつかず、セル位置の指定と入れ子の式が複雑でうまくいきません。
シート1のURL列の2行目（タイトル行があるので）から最終行までを参照した後、シート2の頭から順番に最終行まで参照し、それをシート4まで繰り返し行うというものです。
VBAは使わずに関数だけで完結させたいです。

どうかご教示ください。
よろしくお願いいたします。

◆環境：Excel 2016／Windows10

◆ブックの構成（添付画像ご参照）
　・シート1：参照元シート(1)
　・シート2：参照元シート(2)
　・シート3：参照元シート(3)
　・シート4：参照元シート(4)
　・シート5：参照先集計シート

　※シート1～4はフォーマット及び行数がバラバラの表。
　※各表にはWebサイトのURL一覧の列がそれぞれ含まれている。
　※各表のURL列の1行目のタイトルは「URL」で共通。（列番号はバラバラ）

◆やりたいこと
　・シート1～4のURLの列をまるごとシート5に縦に並べてまとめて参照したい。
　・マクロではなく関数で完結させたい。
　・シート1～4をそれぞれ更新（新データをペースト）すれば、シート5も最新に更新される運用としたい。

回答No.3

SI299792 · ベストアンサー

関数が若干短くなりました。シートをまたぐ計算を利用しました。カウントはどこでやっても同じなので、全てＡ列にしました。
必要なら、A2に=HYPERLINKを付けて下さい。

A2 =○1!H2&IF(ROW()>COUNTA(○1!A:A),OFFSET(○2!A2,1-COUNTA(○1!A:A),0),"")&IF(ROW()>COUNTA(○1:○2!A:A)-1,OFFSET(○3!A2,2-COUNTA(○1:○2!A:A),0),"")&IF(ROW()>COUNTA(○1:○3!A:A)-2,OFFSET(○4!I2,3-COUNTA(○1:○3!A:A),0),"")
B2 =○1!G2&IF(ROW()>COUNTA(○1!A:A),OFFSET(○2!B2,1-COUNTA(○1!A:A),0),"")&IF(ROW()>COUNTA(○1:○2!A:A)-1,OFFSET(○3!B2,2-COUNTA(○1:○2!A:A),0),"")&IF(ROW()>COUNTA(○1:○3!A:A)-2,OFFSET(○4!H2,3-COUNTA(○1:○3!A:A),0),"")

位置が変わるだけだから、
○1!H2→○1!G2
○2!A2→○2!B2
○3!A2→○3!C2
○4!I2→○4!H2
に置き換えるだけです。

この内、COUNTAはどこでも一緒です。これを別セルに置けばさらに簡単になります。
D1 =COUNTA(○1!A:A)
E1 =COUNTA(○1:○2!A:A)
F1 =COUNTA(○1:○3!A:A)
A2 =○1!H2&IF(ROW()>$D$1,OFFSET(○2!A2,1-$D$1,0),"")&IF(ROW()>$E$1-1,OFFSET(○3!A2,2-$E$1,0),"")&IF(ROW()>$F$1-2,OFFSET(○4!I2,3-$F$1,0),"")
B2 =○1!G2&IF(ROW()>$D$1,OFFSET(○2!B2,1-$D$1,0),"")&IF(ROW()>$E$1-1,OFFSET(○3!B2,2-$E$1,0),"")&IF(ROW()>$F$1-2,OFFSET(○4!H2,3-$F$1,0),"")

＞関数ごとに分解して勉強してみようと思います。
と書いてあったので、関数を分解しました。入力すれば動きか判ると思います。
D1～F1も入れて下さい。
C2 =ROW()
D2 =OFFSET(○2!A2,1-$D$1,0)
E2 =OFFSET(○3!A2,2-$E$1,0)
F2 =OFFSET(○4!I2,3-$F$1,0)
G2 =○1!H2&IF(C2>$D$1,D2,"")&IF(C2>$E$1-1,E2,"")&IF(C2>$F$1-2,F2,"")
H2 =OFFSET(○2!B2,1-$D$1,0)
I2 =OFFSET(○3!B2,2-$E$1,0)
J2 =OFFSET(○4!H2,3-$F$1,0)
K2 =○1!G2&IF(C2>$D$1,H2,"")&IF(C2>$E$1-1,I2,"")&IF(C2>$F$1-2,J2,"")
私はこのようにバラバラに作り、最後に結合しています。手間ですが、私はこうしないと作れません。（多分こういう作り方は、私でだけだと思います）
結合するのは人に出す為で、自分用ならバラバラのままにします。その方がメンテナンスがしやすいので。

前の式は、シート名の()と数式の()がごちゃごちゃになり、入力が大変だったと思います。そのままコピペすればエラーになるし。
(1) だと判りにくいので、○1 にしました。

お礼

SI299792さん

おはようございます。
改善バージョンの計算式を作ってくださりありがとうございます！
A列、B列ともにバッチリ反映ができました。

また、計算式の分解についても大変参考になります。
解りやすい説明をありがとうございます。

この度は早々にご回答をいただき本当に助かりました。
重ねて御礼申し上げます。

回答No.5

Chiquilin

数式でやるのは処理が複雑になるだけで 何のメリットもあり
ません。

Excel2016なら PowerQueryを使えるのですから 指定列のデー
タを繋いで吐き出すくらい クエリを作ればあっという間です。
今後 シート1～4が変化しても更新するだけでよくなります。
数式もマクロもいりません。

お礼

アドバイスいただきありがとうございます。

回答No.4

SI299792

※行ズレなどを防ぐため、必ずA列のURLを検索値としてタイトル情報を参照したいです。
見落としていました。でも、どのような状況を想定していますか。
INDEX とMATCH を使った所で、行ずれすれば、違う値が返ってきます。同じではないですか。
何の為にそうするのか解りません。

お礼

そうですね。
式が複雑なので、URLを参照しておけば不具合が起きても原因を特定しやすいかなと思ったのですが、そもそも式がズレてしまえば同じですよね。

回答No.2

SI299792

すみません。ここは○付の文字が()に変換されるのですね。
○１は表現できません・
(1) は○１だと思ってください。
画像で出します。コピペできませんが。

お礼

SI299792さん

すごいです。あっという間に解決しました。ありがとうございます！

　＃こちらでのマナーがよく分かっておらず、とりいそぎお礼申し上げます。
　＃もう一つアドバイスいただきたい件を追記しております。よろしくお願いいたします。

補足

SI299792さん

すごいです。あっという間に解決しました。ありがとうございます！

レベルが低くお恥ずかしいですが、いただいた計算式を理解できるよう関数ごとに分解して勉強してみようと思います。

実はこの続きをチャレンジ中で、誠に恐縮ですが、
もうひとつアドバイスをいただいてもよろしいでしょうか？
シート5のB列にページタイトルも参照表示させたいのですが、(SI299792さんのご教示により表示できた)A列のURLを検索値として、シート1～4からタイトル情報を引っ張ってきたいのです。

ページタイトル情報の取得にあたり、URLを検索値とする場合、URLよりも左側に
タイトルがあるフォーマットも混在するため、VLOOKUPではなくINDEXとMATCH関数
の組み合わせ、もしくはMATCHとOFFSETの組み合わせがベターらしい。。ということは
調べたのですが、計算式がトンチンカンなことになってしまい立ち往生中です。

何卒よろしくお願いいたします。

(懸案事項)
※シート1～4は普段はそれぞれ個別のリストとして運用されており、本集計シートでは、
　メンテナンス性向上のため、できれば各々のフォーマットのままコピー＆ペーストして
　利用できる形にしておきたいという理由があります。

※行ズレなどを防ぐため、必ずA列のURLを検索値としてタイトル情報を参照したいです。
　（B列に対して、教えていただいたURL取得の計算式を応用したくない）

回答No.1

SI299792

シート名は(1)～(4)ですか。
集計シート!A2 に

表示だけでいいなら
=(1)!H2&IF(ROW()>COUNTA((1)!H:H),OFFSET((2)!A2,1-COUNTA((1)!H:H),0),"")&IF(ROW()>COUNTA((1)!H:H,(2)!A:A)-1,OFFSET((3)!A2,2-COUNTA((1)!H:H,(2)!A:A),0),"")&IF(ROW()>COUNTA((1)!H:H,(2)!A:A,(3)!A:A)-2,OFFSET((4)!I2,3-COUNTA((1)!H:H,(2)!A:A,(3)!A:A),0),""

ハイパーリンクを付けたいなら
=HYPERLINK((1)!H2&IF(ROW()>COUNTA((1)!H:H),OFFSET((2)!A2,1-COUNTA((1)!H:H),0),"")&IF(ROW()>COUNTA((1)!H:H,(2)!A:A)-1,OFFSET((3)!A2,2-COUNTA((1)!H:H,(2)!A:A),0),"")&IF(ROW()>COUNTA((1)!H:H,(2)!A:A,(3)!A:A)-2,OFFSET((4)!I2,3-COUNTA((1)!H:H,(2)!A:A,(3)!A:A),0),""))