Excel 行列入れ替え

質問

行列入れ替えを行いたいのですが、良い方法はありますでしょうか？

A列　　　B列　　　C列　　　D列　　　E列　　　F列　　　G列
111　　　222　　　　あ　　　　い　　　　う　　　　え
333　　　444　　　　あ　　　　い　　　　う　　　　え　　　　お

↓

A列　　　B列　　　C列
111　　　222　　　　あ
111　　　222　　　　い
111　　　222　　　　う
111　　　222　　　　え
333　　　444　　　　あ
333　　　444　　　　い
333　　　444　　　　う
333　　　444　　　　え
333　　　444　　　　お

C列以降は内容により、フィールド数が異なります。

最終的にはA列ごとB列ごとに各値を含む割合を出したいと考えています。
ピボットテーブルで集計をしようと考えているのですが、横方向にデータが
入っていると上手く出来ませんでした。

もし、データの入れ替えをせず集計が出来れば、そういった方法も教えて
頂きたいです。

宜しくお願いいたします。

回答No.2

tom04 · ベストアンサー

こんにちは！
VBAになってしまいますが、一例です。
Sheet1のデータをSheet2に表示するようにしてみました。
Sheet1のデータは1行目からあるとします。

Alt+F11キー　→　メニュー　→　挿入　→　標準モジュール　→　VBE画面に
↓のコードをコピー＆ペーストしてマクロを実行してみてください。
（Alt+F8キー　→　マクロ　→　マクロ実行です）

Sub 入れ替え() 'この行から
Dim i As Long, j As Long, cnt As Long
Dim wS1 As Worksheet, wS2 As Worksheet
Set wS1 = Worksheets("Sheet1")
Set wS2 = Worksheets("Sheet2")

Application.ScreenUpdating = False
wS2.Cells.ClearContents
For i = 1 To wS1.Cells(Rows.Count, 1).End(xlUp).Row
For j = 3 To wS1.Cells(i, Columns.Count).End(xlToLeft).Column
cnt = cnt + 1
With wS2.Cells(cnt, 1)
.Value = wS1.Cells(i, 1)
.Offset(, 1) = wS1.Cells(i, 2)
End With
wS2.Cells(cnt, Columns.Count).End(xlToLeft).Offset(, 1) = wS1.Cells(i, j)
Next j
Next i
Application.ScreenUpdating = True
End Sub 'この行まで

こんな感じではどうでしょうか？
※　とりあえず第一段階の行・列の表示だけです。m(_ _)m

お礼

早々にご回答いただき、ありがとうございます。
実行してみて上手くいきました。

空白行がありますが、並べ替えなどを実行し削除すれば何とか
なりそうです。

VBAも基本は勉強しましたが、なかなか使いこなすのは難しいです。
意味を理解して、使えるようにしたいと思います。

ありがとうございました。

回答No.5

kagakusuki

回答:No.3です。

>数式をコピーしてやってみたのですが、シート名の後ろにある「’」がエラーとなってしまいました。

　申し訳御座いません、何でこの様なミスを犯したのか、参照先のシート名を随所で間違えておりました。
　ですから、正しくは以下の様になります。

　まず、適当な未使用シート（ここでは仮にSheet3とします）のA1セルに次の数式を入力して下さい。

=IF($U1="","",MATCH("*?",INDEX(Sheet1!$C:$C,ROW()):INDEX(Sheet1!$IV:$IV,ROW()),-1))

　次に、Sheet3のB1セルに

0

と入力して下さい。
　次に、Sheet3のB2セルに次の数式（ここは間違えておりません）を入力して下さい。

=IF($A1="","",SUM($A$1:$A1)+0.5)

　次に、Sheet3のA1セルをコピーして、Sheet3A2セルに貼り付けて下さい。
　次に、Sheet3のA2～B2の範囲をコピーして、同じ列の3行目以下に貼り付けて下さい。

　次に、Sheet2のA1セルに次の数式を入力して下さい。

=IF(ROWS($1:1)>SUM(Sheet3!$A:$A),"",INDEX(Sheet1!$A:$A,MATCH(ROWS($1:1),Sheet3!$B:$B)))

　次に、Sheet2のB1セルに次の数式を入力して下さい。

=IF(ROWS($1:1)>SUM(Sheet3!$A:$A),"",INDEX(Sheet1!$B:$B,MATCH(ROWS($1:1),Sheet3!$B:$B)))

　次に、Sheet2のC1セルに次の数式を入力して下さい。

=IF(ROWS($1:1)>SUM(Sheet3!$A:$A),"",INDEX(Sheet1!$C:$IV,MATCH(ROWS($1:1),Sheet3!$B:$B),ROWS($1:1)-INT(LOOKUP(ROWS($1:1),Sheet3!$B:$B))))

　次に、Sheet2のA1～C1の範囲をコピーして、同じ列の2行目以下に貼り付けて下さい。

　以上です。

お礼

度々ありがとうございました。

再度やってみましたが、数式を確定しようとすると「値の更新」ダイアログが表示され、「キャンセル」すると（「OK」はクリックできない）やはり何も表示されませんでした。

回答No.6

MarcoRossiItaly

No.4さんの、空白の行のみ削除する方法あたりが、応用範囲が広いかなと。

＞最終的にはA列ごとB列ごとに各値を含む割合を出したいと考えています。
　……
　もし、データの入れ替えをせず集計が出来れば、そういった方法も教えて頂きたいです。

それは、「111」とか「444」とかの個数が分かればいいという意味でしょうか？そうだとすると、見たところ、質問文の最初の表で 1 行の中に載っている「あ」?「お」の個数がそのまま「111」とかの個数になるようなので、「あ」?「お」を数えればいいですね。

H1　=count(c1:g1)　　…… C ? G 列に入力される値は数値のみである場合
　　あるいは
　　=counta(c1:g1)　　…… C ? G 列に入力される値として文字列も考えられる場合

COUNTA 関数は空文字列「""」も数えてしまうので、ご注意。これが既に入力されている場合は、2回置換するなどして除去しておきます。

こうして H 列に個数がズラッと並んだら、「111」のみの個数の合計は、

=sumif(A$1:A$100,111,H$1:H$100)

のような感じの数式で求められますね。「111」の部分は勿論、セル参照で書いても差し支えありません。

お礼

ご回答ありがとうございました。

参考にさせて頂きます。

回答No.4

noname#204879

以下は Sheet2 おける手順
１．A1: =OFFSET(Sheet1!$A$1,(ROW(A1)-1)/5,COLUMN(A1)-1)
２．セル A1 を右２列にドラッグ＆ペースト
３．セル C1 の式
　　=OFFSET(Sheet1!$A$1,(ROW(C1)-1)/5,COLUMN(C1)-1)
　　を次のように変更
　　=OFFSET(Sheet1!$A$1,(ROW(C1)-1)/5,COLUMN(C1)-1+MOD(ROW(A1)-1,5))&""
　　ここで、上式中の 5 は Fig-1 のＣ列以右の最大列数
４．範囲 A1:C1 を下方にズズーッとドラッグ＆ペースト(Fig-2)
５．Ａ～Ｃ列全体を選択して、[コピー]→[値の貼り付け]
６．１行目で[オートフィルタ]を設定して、Ｃ列の“(空白セル)”で
　　レコードを抽出(Fig-3)
７．全抽出レコードを選択((Fig-4)
８．[編集]→[行の削除]を実行後に[データ]→[フィルタ]→[すべて
　　表示]を実行(Fig-5)

お礼

ご回答ありがとうございます。

関数が複雑ですが、理解して使いたいと思います。

回答No.3

kagakusuki

今仮に、元の表がSheet1に存在していて、行列を入れ替えた表をSheet2に表示させるものとします。

　まず、適当な未使用シート（ここでは仮にSheet3とします）のA1セルに次の数式を入力して下さい。

=IF($U1="","",MATCH("*?",INDEX(Sheet1'!$C:$C,ROW()):INDEX(Sheet1'!$IV:$IV,ROW()),-1))

　次に、Sheet3のB1セルに

0

と入力して下さい。
　次に、Sheet3のB2セルに次の数式を入力して下さい。

=IF($A1="","",SUM($A$1:$A1)+0.5)

　次に、Sheet3のA1セルをコピーして、Sheet3A2セルに貼り付けて下さい。
　次に、Sheet3のA2～B2の範囲をコピーして、同じ列の3行目以下に貼り付けて下さい。

　次に、Sheet2のA1セルに次の数式を入力して下さい。

=IF(ROWS($1:1)>SUM(Sheet3'!$A:$A),"",INDEX(Sheet51!$A:$A,MATCH(ROWS($1:1),Sheet3'!$B:$B)))

　次に、Sheet2のB1セルに次の数式を入力して下さい。

=IF(ROWS($1:1)>SUM(Sheet3'!$A:$A),"",INDEX(Sheet51!$B:$B,MATCH(ROWS($1:1),Sheet3'!$B:$B)))

　次に、Sheet2のC1セルに次の数式を入力して下さい。

=IF(ROWS($1:1)>SUM(Sheet3'!$A:$A),"",INDEX(Sheet51!$C:$IV,MATCH(ROWS($1:1),Sheet3'!$B:$B),ROWS($1:1)-INT(LOOKUP(ROWS($1:1),Sheet3'!$B:$B))))

　次に、Sheet2のA1～C1の範囲をコピーして、同じ列の2行目以下に貼り付けて下さい。

　以上です。

補足

ご回答ありがとうございます。

数式をコピーしてやってみたのですが、シート名の後ろにある「’」がエラーとなってしまいました。

「’」を削除してみたのですが、結果は何も表示されませんでした。

何か、方法が良くなかったのでしょうか？

回答No.1

noname#224282

コピーして、編集から形式を選択して貼り付け、行列入れ替え。

補足

ご回答ありがとうございます。

ただ、「形式を選択して貼り付け」だと、もともと入力されているデータが上書きされてしまいます。
出来ればA列とB列は自動でデータを補いたいです。