エクセルの表作成(横のデータを縦にしたい）

質問

こんにちは、いつもお世話になっております。

エクセルの表作成で困っています。

　A B C 　D　E　F　G　H　I　・・・
1　7 ○ 東京 A　1　B　2　C　1　・・・
2　5 △ 札幌　 C　5　B　8　D　6
3
このようなデータがあり、これを別シートに転記(数式でリンクを貼りたい）を
したいのですが、
A～Cは固定で、DEがセット、FGがセット・・・といった感じで
横になっているものを縦にしたいのです。
(解かりにくくて申し訳ありません)
↓
　A B C 　D　E　F　G　H　I
1　7 ○ 東京　A　1
2　7 ○ 東京　B　2
3　7 ○ 東京　C　1
4　5 △ 札幌　C　5
6　5 △ 札幌　B　8

（具体的にはD～のデータは9セットあります）
一個分のデータの数式は組めたのですが、(=でつないでるだけです）
縦にもデータがいくつもあり、(MAX200程度ですが)
オートフィルで上手く数式をコピーすることが出来ません。

マクロは使用不可なので、何とか関数で上手く作成ができないものか
悩んでおります。
どうか、お知恵を拝借できれば幸いです。
どうぞよろしくお願いいたします。

回答No.3

higekuman · ベストアンサー

Sheet2のA1～E1それぞれに、

=INDEX(Sheet1!$A:$U,INT((ROW()-1)/9)+1,1)
=INDEX(Sheet1!$A:$U,INT((ROW()-1)/9)+1,2)
=INDEX(Sheet1!$A:$U,INT((ROW()-1)/9)+1,3)
=INDEX(Sheet1!$A:$U,INT((ROW()-1)/9)+1,MOD(ROW()-1,9)*2+4)
=INDEX(Sheet1!$A:$U,INT((ROW()-1)/9)+1,MOD(ROW()-1,9)*2+5)

と書いて、それを下にコピー。

補足

ありがとうございます。
/9するという発想がありませんでした。
これで問題なく出来そうです！
・・・と思っていた矢先に、上に2行タイトルを追加したいと
要望が発生してしまいました。
A~CはROW()-1を-16に変えたところ対応できたのですが
D以降が上手く表示されなくなってしまいました。^^；；；
INDEX、ROW関数は初めて使うため、アレンジの際に間違ったかも
知れないのですが・・・。

MODの中のROW関数の割られる数を調整すればいいのでしょうか？

度々申し訳ありません・・・。

回答No.4

higekuman

> A~CはROW()-1を-16に変えたところ対応できたのですが

本当に対応できていますか？
その変更で対応できているとは、とても思えないのですが。

変更した数式全体を書いてくれないと、実際にどう変えたのかも判りません。

> 上に2行タイトルを追加したいと要望が発生してしまいました。

どっちのシートにですか？

何のための-1なのか、何のための+1なのか、何のための*2なのか、何のためのINT関数なのか、何のためのMOD関数なのか、何のためのINDEX関数なのか、ちゃんと解析(というほどのことでもないけど)をして、理解してください。

タイトル行を追加しなかった場合に、-1をしないとどうなるのか、+1をしないとどうなるのか、*2をしないとどうなるのか、・・・・、を試してみましたか？
いろいろ試してみて、数式の意味を理解すれば、タイトル行が追加になっても対応できるはずです。

理屈は解らないままに直感で数字を変えてみて、うまくいかなかったから安直に質問する、というのでは、何も身に付きません。
何も試さないまま簡単に質問するよりはマシですが。

仕様変更のたびに対応するわけにはいかないので、ご自分で理解してください。

お礼

回答ありがとうございます。
こちらの都合でかなり急ぎで業務を進めなければならなかったため、
安直に数式を解析しすぎておりました。
私としても、100％依存するつもりは無く（結果的にそうなってしまいましたが）
実際の表も、横にもっとさまざまなデータが入っており、
それを自分で理解しようと思い、簡略化したデータで質問をさせていただいたのですが、
結果的に回答してくださった方に不快感を与えてしまったこと、申し訳なく思っています。

初めて使う関数で、私なりに頑張って解析しようと思いましたが、
時間もない中で、あせってしまい、甘えてしまったためこのような質問に
なってしまって申し訳ありません。

後はこちらで再度頑張ります。
ヒントを下さったこと、感謝します。
ありがとうございます。

申し訳ありませんでした。

補足

=INDEX(Sheet1!$A:$V,INT((ROW()-2)/9)+3,1)
元データのシートに二行追加、加工後のシートに一行追加だったので
このような式に変えました。
-16で、うまく行ったのはたまたま同じデータがあったからだったようです。
また、*2はデータの組数の数だと思いましたので、こちらは組み数の
データ分だけ変更することで対応できそうです。

申し訳ありませんでした。
ありがとうございます。

回答No.2

papiyonys

説明がよく分かりませんが、単純に縦と横を替えるなどの操作するのであれば、ツール　→　ピポットテーブルでウィザードが出ますからその指示に従ってやればデータを替えられると思いますので、一度やってみたらどうでしょうか？

補足

解かりにくくて申し訳ありません。＾＾；；；
A～Cは後のD移行のセットにかかるので、
単純に縦横をかえるわけではないのです・・・。
Dからの列に入っている個数分だけA～Cのデータをくっつけて
縦にデータを作りたいのですが、ピボットで可能でしょうか？
私のピボット技量では出来ませんでした。

回答No.1

higekuman

ROW関数、COLUMN関数、INDEX関数、を駆使しましょう。

補足

下にコピーすると、どの数式を使っても
A～Cの部分が上手くコピーが出来ません。＾＾；；；
頑張ってみます。
ありがとうございます。