エクセル表の作成

質問

下記のCSVデータから、希望の表形式にしたいです。
ご教授ください。

【CSVデータ】
A列　　　　B列　　　　　C列　　　　　D列　　　 　E列
タイトル　　(1)　　　　　(2)　　　　　(3)　　　　　 (4)
1　　　　　　0 　 100 　 150 　 0　
2　　　　　100 　 0 　 150 　 0　
3　　　　　　0 　100 　 0 　 100　
4　　　　　　0 　 100 　 150 　 0　
5　　　　　100 　 100 　 150 　 0　

【希望の表形式】
A列　　B列　　C列
1 　　 (2)　　100
　　　　(3)　　150
２　　　(1)　　100
　　　　(3)　　150
３　　　(2)　　100
　　　　(4)　　100

【希望の表形式】は多少違う形でも構いません。
作成方法は関数やピポット、なければ（マクロ）で教えてください。

宜しくお願い致します。

回答No.4

bunjii · ベストアンサー

>エラーの種類は「#N/A」となります。
>また、B9を含むB10以下、B列全てエラーとなります。
>CSVデータの範囲に合わせる形で、INDEXの参照範囲を変更致しました。
変更した参照範囲に誤りがあると思います。

>B1が（1）となり、右へ連続して、（1600）まであります。
>320行目まであります。
データ量が多すぎるので自動再計算をOFFにしないと作業が滞るでしょう。
データ量を1600列×10行で検証してみました。
貼付画像でSheet1は模擬データですがF列からBIM列を非表示にしてあります。
Sheet2は希望の配列で計算結果は途中までです。
また、中間を非表示にしています。
Sheet2の数式は下記のようにしました。
A2=IF(AND(E1>1,ISNUMBER(E1)),"",IF(MAX(Sheet1!A$2:A$11)=MAX(A$1:A1),"",MAX(A$1:A1)+1))
B2=IF(E2="","",INDEX(Sheet1!B$1:BIO$1,LARGE(INDEX((INDEX(Sheet1!B$2:BIO$11,MATCH(MAX(A$2:A2),Sheet1!A$2:A$11,0),0)<>0)*COLUMN(A2:BIN2),0),E2)))
C2=IF(B2="","",INDEX(Sheet1!B$2:BIO$11,MATCH(MAX(A$2:A2),Sheet1!A$2:A$11,0),MATCH(B2,Sheet1!B$1:BIO$1,0)))
E2=IF(A2="",IF(SUM(E1)>1,SUM(E1,-1),""),COUNTIF(INDEX(Sheet1!B$2:BIO$11,MATCH(A2,Sheet1!A$2:A$11,0),0),">0"))
E2の数式はSUMPRODUCT関数をCOUNTIF関数に置き換えてみました。
再計算の負荷状況を確かめてください。

お礼

返事が遅くなってしまい申し訳ございません。
修正していただきました、関数で問題なく使用することが出来ました。
度々、お手数をお掛けして申し訳ございません。
丁寧に教えていただきありがとうございました（^^）

回答No.3

bunjii

>手持ちの表で試してみましたが、下記のB9のみエラーとなってしまいました。。。
エラーの種類は？、B9を下へコピーしたときB10以下はエラーにならないでしょうか？

>エラーの原因は何でしょうか？
B9へ入力した数式はどのように修正したかを提示してください。

>私の説明が足りなくて申し訳ございませんが、１行目は（４）までではなく、1600番目まであります。（右に長い形です。）
(1)～(1600)がB2セルから右へ連続していると言うことでしょうか？

>※A列は5以上ありますが、範囲指定を変更したらエラーが出ませんでしたので、5以上に増加しても問題ないかと思います。
念のために最大何行まであるかを補足してください。
データの量が多くなると関数では処理時間が掛かり過ぎて実用にならないかも知れません。
後出し条件は極力避けないと無駄な検証が増えます。
他人が組み立てた数式を論理的に理解できない場合は応用力でカバーできませんので、あなたの理解の程度を補足してください。

お礼

ありがとうございます。

補足

ご返信ありがとうございます。
お手数をお掛けして申し訳ございません。

＞エラーの種類は？、B9を下へコピーしたときB10以下はエラーにならないでしょうか？
エラーの種類は「#N/A」となります。
また、B9を含むB10以下、B列全てエラーとなります。

＞B9へ入力した数式はどのように修正したかを提示してください。
CSVデータの範囲に合わせる形で、INDEXの参照範囲を変更致しました。
具体的に、CSVデータはsheet1、希望の表形式はsheet2で作成する形で、sheetをまたぎます。

＞(1)～(1600)がB2セルから右へ連続していると言うことでしょうか？
B1が（1）となり、右へ連続して、（1600）まであります。

＞念のために最大何行まであるかを補足してください。
320行目まであります。

私の理解度ですが、基本的なエクセル関数（INDEX等は問題ございません）
につきましては理解しております。

度々申し訳ございませんが、宜しくお願い致します。

回答No.2

Prome_Lin

Ｄドライブに「Data.csv」という名前で、ファイルが存在する、という前提条件で、いったん、エクセルに読み込んでから、その下にご希望の並びを作っています。

参考にして頂ければ、幸いです。

Option Explicit
Sub Test()
Dim a, s As String
Dim c, i, j, k, v As Integer
Open "D:\Data.csv" For Input As #1
i = 0
Do Until EOF(1)
i = i + 1
Line Input #1, s
a = Split(s, ",")
For j = 0 To UBound(a)
Cells(i, j + 1).Value = "'" & Replace(a(j), Chr(34), "")
Next j
Loop
Close #1
k = 7
For i = 2 To Range("A1").End(xlDown).Row
c = 0
For j = 2 To Range("A1").End(xlToRight).Column
v = Val(Cells(i, j).Value)
If v > 0 Then
k = k + 1
c = c + 1
If c = 1 Then
Cells(k, 1).Value = i - 1
End If
Cells(k, 2).Value = "'(" & CStr(j - 1) & ")"
Cells(k, 3).Value = Cells(i, j).Value
End If
Next j
Next i
End Sub

お礼

ご回答いただきありがとうございます。
現在、マクロを勉強中でもし解る範囲内で作成・変更が出来ればと思い、教えていただきましたが、まだまだ力不足のようでした。。
部分的には理解できるものの、エラーが発生した場合に自分で修正できない可能性があり、今回は関数を使用して対応したい思います。

ご教授いただきありがとうございます。
参考にさせていただき、勉強させていただきます。

回答No.1

bunjii

CSVデータとして提示の値をA1:E6に読み込んだ状態で検証しました。
希望の表形式はA8:C20とし、E8:E20を作業用に使用しています。
E9=IF(A9="",IF(SUM(E8)>1,SUM(E8,-1),""),SUMPRODUCT((INDEX(B$2:E$6,MATCH(INDEX(A$9:A9,MATCH(MAX(A$2:A$6),A$9:A9)),A$2:A$6,0),0)<>0)*1))
A9=IF(AND(E8>1,ISNUMBER(E8)),"",IF(MAX(A$2:A$6)=MAX(A$8:A8),"",MAX(A$8:A8)+1))
B9=IF(E9="","",INDEX(B$1:E$1,LARGE(INDEX((INDEX(B$2:E$6,MATCH(MAX(A$9:A9),A$2:A$6,0),0)<>0)*COLUMN(A9:D9),0),E9)))
C9=IF(B9="","",INDEX(B$2:E$6,MATCH(MAX(A$9:A9),A$2:A$6,0),MATCH(B9,B$1:E$1,0)))
各列の9行目を下へ20行目までコピーしました。
但し、A2:A6の値は数値で、1から5まで値とし昇順の配置とします。
文字の場合はA9の数式を全面的な見直しが必要です。
貼付画像は検証結果です。

お礼

ありがとうございます。

補足

ご回答ありがとうございます。
手持ちの表で試してみましたが、下記のB9のみエラーとなってしまいました。。。
エラーの原因は何でしょうか？

B9=IF(E9="","",INDEX(B$1:E$1,LARGE(INDEX((INDEX(B$2:E$6,MATCH(MAX(A$9:A9),A$2:A$6,0),0)<>0)*COLUMN(A9:D9),0),E9)))

私の説明が足りなくて申し訳ございませんが、１行目は（４）までではなく、1600番目まであります。（右に長い形です。）

※A列は5以上ありますが、範囲指定を変更したらエラーが出ませんでしたので、5以上に増加しても問題ないかと思います。

宜しくお願い致します。