【Excel】代表者の下の行に、それぞれの同行者の情報が来るようにしたいです。

私の環境は Microsoft365 なので確認できませんが、下記のようにすると Office2019 でも作成できるかもしれません。

Sheet1 元シートとします。

(1) Work1 (氏名、氏名（カナ）、年齢を格納します) を作成します。

■ 代表者
A1 =Sheet1!A:C
■ 同行者１
D1 =Sheet1!F:F
E1 =Sheet1!I:I
F1 =Sheet1!J:J
■ 同行者２
G1 =Sheet1!M:M
H1 =Sheet1!P:P
I1 =Sheet1!Q:Q
■ 同行者３
J1 =Sheet1!T:T
K1 =Sheet1!W:W
L1 =Sheet1!X:X

以降 同行者の人数分作成しておきます。

■ 2026/5/23 修正

Office2019 の場合、上記のままでは１つのセルだけしか表示されないようです。

下記のようにオートフィルしてください。

• A1のセルをB1,C1にオートフィルする。
• 1行目に数式を設定したセルをSheet1 の行数分オートフィルする。

これでSheet1 からコピーできます。

(2) Work2（行・列の相対位置）を作成します。

F1  代表者＋同行者数を格納しておく。

A2(0から連番)                 =ROW(A2)-2
B2(Sheet1!A2からのオフセット) =INT(A2/$F$1)
C2(Work1!D2からのオフセット)  =MOD(A2,$F$1)*3-3
A2～C2 を 元シートの行数＊（代表者＋同行者数）行以上に貼り付けます。
（多めにしておくと元シートの行数が増えてもWork3が自動的に計算されます）
A2～C2をコピーしてから、Shift+Pgdn で必要行数分以上選択して Ctrl+Vで貼り付けると便利だと思います。

(3) Work3(代表者の後に同行者が並んだシート)を作成します。

A2  =IF(Work2!C2=-3,OFFSET(Sheet1!$2:$2,Work2!B2,0),OFFSET(Work1!$D$2:$F$2,Work2!B2,Work2!C2))
A2 を Work2と同じ行数貼り付けます。

(4) Work3 にフィルタを設定して代表者=0を除外します。

　　[データ]メニュー > フィルター > 代表者で 0 のチェックを外す

(5) 必要なら Work3 でフィルタした行をコピーして新しいシートに貼りつける。

イメージです。

Sheet1

Work1

Work2

Work3

Work3(フィルター後)

私の環境では Office2019 がないので確認していません。（Microsoft365 で動作確認しています）

最後の行にSheet1の最後の同行者が表示されない場合はWork2、Work3の行数を増やしてください。

わかりにくいですが、参考になれば幸いです。

※Excel 365やExcel 2021は使っていません。

Excel 2019だと難しいかもしれませんが、Microsoft 365 for the web（無料）をEdgeで使用すれば下記のようにしてできるかもしれません。

- 無料の Microsoft 365 Online | Word、Excel、PowerPoint - Microsoft.

私の環境です。

• Windows 11 Pro 25H2 26200.8457
• Microsoft® Excel® for Microsoft 365 MSO (バージョン 2604 ビルド 16.0.19929.20172) 64 ビット Sheet1 元シートとします。(Max1000行とします)

また、代表者+同行者がMax１０名とします。

作業シートを Work1 から Work4 まで作成して下記のように数式を設定します。

※ Work1 (氏名、氏名（カナ）、年齢を格納します)

■ 代表者
A1 =Sheet1!A:C
■ 同行者１
D1 =Sheet1!F:F
E1 =Sheet1!I:I
F1 =Sheet1!J:J
■ 同行者２
G1 =Sheet1!M:M
H1 =Sheet1!P:P
I1 =Sheet1!Q:Q
■ 同行者３
J1 =Sheet1!T:T
K1 =Sheet1!W:W
L1 =Sheet1!X:X

以降 同行者の人数分作成しておきます。

※ Work2

A1 =ROW(A1:A10000)-1
B1 =INT(A1:A10000/10)
C1 =(A1:A10000-(B1:B10000*10))
D1 =C1:C10000*3-3

• A1～ ... 0から連番
• B1～ ... 10件毎にから連番(Sheet1 行2からの相対行位置)
• C1～ ... 0～9 の繰り返し
• D1～ ... -3,0,3,6,9, ...,24 の繰り返し（Work1 D列からの相対列位置）

※ Work3

A1  =IF(Work2!D1=-3,OFFSET(Sheet1!$A$2:$X$2,Work2!B1,0),OFFSET(Work1!$D$2:$F$2,Work2!B1,Work2!D1))

A1 を Work2と同じ行数分貼り付けます。

A1をコピーしてから、Shift+Pgdn で10000行選択して Ctrl+Vで貼り付けると便利だと思います。

（10000行作成すると、行が追加された時、自動で計算されます）

※ Work4 Work3でA行が0 の行を除いたシートを作成

A2 =FILTER(IF(Work3!A1:BU10000="","",Work3!A1:BU10000),Work3!A1:A10000<>0)

結果のシートをコピーしてローカルのエクセルシートへ値だけ貼り付けます、

わかりにくいですが、参考になれば幸いです。

また、Work1 から Work13 のシートを作成せずに MAKEARRAY 関数を使用した下記のような方法でもできます。

※ 2026/5/19 修正しました。

　 元行,(r-1)/MAX同行者数+1, → 元行,QUOTIENT(r-1,MAX同行者数)+1,

/ だと結果が小数になります。INDEXの行として使用する場合は端数が切り捨てられますが、意図している数値ではないので QUOTIENT 関数で割り算の商を求めるように修正しました。

=LET(
 元範囲,Sheet1!$A$2:$BU$1000,
 行数,ROWS(元範囲),
 列数,COLUMNS(元範囲),
 MAX同行者数,(COLUMNS(元範囲)-3)/7+1,
 仮配列,MAKEARRAY(行数*MAX同行者数,列数,LAMBDA(r,c,
   LET(
     元行,QUOTIENT(r-1,MAX同行者数)+1,
     同行者INDEX,MOD(r-1,MAX同行者数),
     IF(同行者INDEX=0,INDEX(元範囲,元行, c),
     IF(c=1,INDEX(元範囲,元行,(同行者INDEX-1)*7+6),
     IF(c=2,INDEX(元範囲,元行,(同行者INDEX-1)*7+9),
     IF(c=3,INDEX(元範囲,元行,(同行者INDEX-1)*7+10),
     "")))))))
,FILTER(IF(仮配列="","",仮配列),(TAKE(仮配列,,1)<>"")))

前提

• 行内では代表者が最初の３列、同行者はその後７列ずつ使用しています。
• 元の範囲からその行数、列数を最初に計算しています。
• MAX同行者数は代表者を含みます。
• MAKEARRAYで空白を含めたセルを作成しています。そして、FILTERで名前が空白の行を削除しています。

MAKEARRAYの簡単な説明です、

• 最初に作成する配列の行数と列数を指定しています。
• LAMBDAではr行c列のセルの値を計算しています。
• MAX同行者数ずつグループ化して、最初の行は代表者です。
• グループ内の2行目以降は同行者です。

わかりにくいですが、参考になれば幸いです。

不明点があれば、コメントでお知らせください。

<モデレーター注>
この投稿は、スパムフィルターの誤判定により削除されていましたが投稿を復元させて頂きました。

まずこのようなデータに入れ方は Excel の使い方としてよろしくない方法です。

以下のような正規化されたデータを作成しましょう。

そうすれば後の加工は楽になります。

作成したい表のイメージが正確には分かりませんが、
　①下のサイトの方法で空白行を挿入
　②挿入した空白行に代表者（氏名・フリガナ・年齢）をコピー
　③下の行の代表者を空欄にする
という方法で意図どおりになりませんか。

キーボードにテンキーがあれば、［Ctrl］キー＋［＋］キーで１つずつ空白行を挿入する方法もあります。
　　一行おきに「空白行」をまとめて挿入する方法

代表者と同行者のセットを横の行でなく縦の列で表示して、代表者が縦に並ぶのではなく、横に代表者を並べて、代表書の下へ同行者が並ぶようにする、ということですか？

もし、氏名、ふりがな、年齢の3項目以外は表示しなくよいのであれば・・・

表から不必要な列を削除して、項目別に行と列を入れ替えて合成する、すなわち

●氏名だけに注目して、それだけの表を作成して行と列を入れ替えると氏名が縦に並んだ表が作成できる

●ふりがなと年齢に関しても同様、

●氏名とふりがなと年齢は縦に並んでいるので、列の隙間を作って順番に列でコピーして並べればできあがり

例えば・・・

1　必要の無い列のデータを削除して左づめした表を新規タブに作成

2　列単位でコピーして氏名だけの表を新規タブに作成　

3　2の行とと列を入れ替えてコピーした表を新規タブに作成

4　ふりがなと年齢についても、2，3要領で表を新規タブに作成

5　3，4で作成した行と列が入れ替わった3つの氏名だけ、ふりがなだけ、年齢だけの表から、

　氏名の表を新規タブにコピーして横方向の氏名の右隣に2つ列を挿入してふりがなと年齢のスペースを作成して、

　列単位でふりがなと年齢をコピーするとできあがり、ではどうでしょう。