EXCEL 行内のデータを2行に分けて、表を作り直したいのです。教えてください。

先の質問で説明不足ですみません。

以下の表で書き直します。
（表１）
列　 A　B C D
---+---+---+----
行1:あ ア 10　100
2:い イ　 20 200
3:う　ウ　30 300　
:
の表(sheet1)から他（sheet2）にコピーして表の並びを以下のように変更した表を作りたいのです。

（表２）
列　 A B C D
---+---+---+---
行1:あ ア
2:10 100
3:い　イ
　4:20 200
5:う　ウ
　6:３30 300

そこで目的とする表（表２）は、次の関数で作りました。

（表３）
列　 A B C D
--------------+------------+---+---
列1: =sheet1!A1 =sheet1!B1
2: =sheet1!C1 ＝sheet1!D1
3: =sheet1!A2 =sheet1!B2
4: =sheet1!C2 =Sheet1!D2
　　　：
そして、１列２列がひとまとまりなので、
A1 B1 A2 B2 をひとまとまりで広げてコピーすると、以下になってしまいます。

（表４）
列　 A B C D
--------------+------------+---+---
列1: =sheet1!A1 =sheet1!B1
2: =sheet1!C1 ＝sheet1!D1
3: =sheet1!A3 =sheet1!B3
4: =sheet1!C3 =Sheet1!D3
5: =sheet1!A5 =sheet1!B5
6: =sheet1!C5 =Sheet1!D5
:

表２を作りための表３に示した関数をどのように作るのか
教えてください。よろしくお願いいします。

No.2 ベストアンサー 回答者： fujillin 回答日時： 2023/06/25 15:29

こんにちは

順序を入れ替えてフィルコピーをしたいのなら、その様な位置関係になるように計算で求めてあげる必要があります。
例えば、参照元の行・列何号を（行、列）で表すなら、ご質問の場合であれば、
　(1,1) (1,2)
　(1,3) (1,4)
　(2,1) (2,2)
　(2,3) (2,4)
　　・・・・
のような順序になるように計算すれば良いことになります。

行番号は、参照先で2行増加すると、参照元が1行増加するようになれば良いので
　=INT(ROW(A2)/2)
のような計算で求められます。
同様に、列番号は
　=COLUMN(A1)+MOD(ROW(A2),2)*2
のような計算で求められます。

あとはINDEX関数やOFFSET関数を利用して、該当するセルを参照すれば良いだけです。
=INDEX(Sheet1!$A:$D,INT(ROW(A2)/2),COLUMN(A1)+MOD(ROW(A2),2)*2)
などを入力して、必要範囲にフィルコピーすれば宜しいでしょう。


ご質問文のように、4セルずつをコピペする方式ならA1:B2セルに
　=INDEX(Sheet1!$A:$D,INT(ROW(A2)/2),1)
　=INDEX(Sheet1!$A:$D,INT(ROW(A2)/2),2)
　=INDEX(Sheet1!$A:$D,INT(ROW(A2)/2),3)
　=INDEX(Sheet1!$A:$D,INT(ROW(A2)/2),4)
をそれぞれ入力して、4セルをまとめてコピペして行っても同様の結果を得られます。


※ 参照式を直接文字列で直接作成してしまう方法や、INDIRECT関数で参照する方法などいろいろと考えられると思いますので、上記はあくまでも一例です。

この回答へのお礼

丁寧な解説ありがとうございます。教えて頂いたように作ったところ、うまくできました。コピーの範囲も拡大して使うことができるようになりました。
EXCEL の奥の深さを教えられました。
他の方法もあるとのこと、さらに調べていきたいと思います。
ありがとうございました。■

お礼日時：2023/06/25 17:14

No.5 回答者： goomania 回答日時： 2023/06/26 19:05

先のご質問↓のNo.6の回答者です。

https://oshiete.goo.ne.jp/qa/13511130.html
その回答で、

>この数式はsheet2の表示範囲外にコビーしたときのエラーチェック等
>は一切行っていませんので、ご承知おきください。

と書きました。

他の回答者さんの回答を見ても、sheet1の元データの範囲外参照やsheet2の表示範囲外への数式コピーに対して、エラーチェックを行った数式は無いように思います。

恐らく、数式が冗長になるのを嫌っているのだと思われます。
※試しに、他の回答者さんの回答数式を表示対象範囲外のsheet2のC1セルにコピーして見てください。

そこで、sheet1の元データの範囲外の参照やsheet2の表示範囲外への数式コピーに対しては「空白」を表示するようなエラーチェックを付けてできるだけ短い数式を考えてみました。

sheet1のA列～D列に100行目まで、元データがあるとして、1行分をsheet2のA列、B列に2段に分けて表示するものとします。
sheet2のA1セルに、

=IFERROR(INDEX((Sheet1!$A$1:$B$100,Sheet1!$C$1:$D$100),ROW(A2)/2,COLUMN(A1),ISEVEN(ROW(A1))+1),"")&""

という数式を記述して、右方向、下方向へコピーする方法になります。

なお、この数式の結果は文字列となりますので、表示結果が文字列では不都合がある場合は末尾の「&""」を削除してください。
但し、削除した場合sheet1の最終行以前に空白データがあると、「0」が表示されてしまいます。

No.4 回答者： mike_g 回答日時： 2023/06/25 18:25

》 先の質問で説明不足ですみません

先の質問(https://oshiete.goo.ne.jp/qa/13511130.html)のどの部分を仰っているのでしょうか？とても興味があるので、具体的に教えてください。
それにも拘わらず、[No.5]を投稿した私は身勝手な忖度をし、笑止千万、滑稽極まりなし、とお笑いになりますか？
コメントをお待ちしています。m(_._)m

この回答へのお礼

ありがとうございます。教えて頂いた関数を使って自分のデータで試しましたところ、セル番号を間違えたのか、違う表示が出たものですから、うまくいかない部分もあると表現しました。これは私のミスでした。
皆さんからも異なる関数を教えて頂きましたが同じ結果になることを確認させていただきました。奥の深いことを知りました。仕事で複数の人が作った表からデータを抜き出すのに関数を探していましたがうまくいかなかったところ、本当に助かりました。mike_g様からはさらに説明をいただき、とても助かりました。ご親切に感謝いたします。
私の知っているEXCELなんてまだ氷山の一角なんだとわかり、さらに学んでいこうと思いました。機会がございましたら、またよろしくお願いいたします。
この度はどうもありがとうございました。■

お礼日時：2023/06/25 23:52

No.3 回答者： 真魚 回答日時： 2023/06/25 16:06

仮に、Sheet1の表の範囲が「$A$1～$D$100」として、

① Sheet2の4つのセル（A1,B1,A2,B2）に、
　図のように数式を入力します。
A1　=INDEX(Sheet1!$A$1:$D$100,ROUND(ROW()/2,0),1)
B1　=INDEX(Sheet1!$A$1:$D$100,ROUND(ROW()/2,0),2)
A2　=INDEX(Sheet1!$A$1:$D$100,ROUND(ROW()/2,0),3)
B2　=INDEX(Sheet1!$A$1:$D$100,ROUND(ROW()/2,0),4)

②Sheet2の4つのセルを選択して、
　B2セルの右下の「■」にポインターを合わせ、
　「╋」に変わったら下方へドラグして、数式をコピーします。

＊Sheet1の表の範囲に任意の名前（「表」とか）を定義しておくと、
　Sheet2の数式の「Sheet1!$A$1:$D$100」の部分をその名前で置き換えることができます。

この回答へのお礼

ありがとうございます。いろいろなテクがあるのですね。今回の質問は皆さんに教えて頂き、無事に解決することができました。図までつけていただきどうもありがとうございました。■

お礼日時：2023/06/25 23:34

No.1 回答者： taglet 回答日時： 2023/06/25 14:57

Sheet2の各セルに、以下の式を入れてください。

A1
=INDIRECT("Sheet1!r" & (ROW()+1)/2 & "c1",FALSE)
B1
=INDIRECT("Sheet1!r" & (ROW()+1)/2 & "c2",FALSE)
A2
=INDIRECT("Sheet1!r" & (ROW())/2 & "c3",FALSE)
B2
=INDIRECT("Sheet1!r" & (ROW())/2 & "c4",FALSE)

この回答へのお礼

ありがとうございます。皆さんから教えて頂き、違う関数を使っても結果は同じになることが分かりました。taglet様の方法も参考にさせていただきます。とても助かりました。ありがとうございました。■

お礼日時：2023/06/25 23:32