[[20160202191748]] 『複数あるシートデータからマトリクスを作成したい』(素人) ページの最後に飛ぶ

[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]

 

『複数あるシートデータからマトリクスを作成したい』(素人)

 出発先から到着先までの距離と時間、高速代をマトリクス表にしたいのですが分からないので宜しくお願いします。
 シートですが、データーの入ったシートが60枚位あります。
      A    B    C       D      E    F     G     H
 1
 2
 3       発       着           距離  時間  高速
 4       
 5       990          010           50    50    2000
 6       990             020             40      40     1000
 7       990             030             30      30      500
 8       
 こちらの表からマトリクス表の距離のみ

      A    B    C      D      E    F     G     
 1
 2
 3
 4                       990    010     020      030    
 5       
 6       990             0     50      40       30           
 7       010             50    0       50     
 8       020          
 9       030
 10

 距離と時間 
       A    B    C      D      E    F     G     
 1
 2
 3
 4                       990    010     020      030    
 5       
 6  距離     990          0     50      40       30    
 7  時間                        50      40       30
 8       010         50     0      
 9                       50     0      
 10      020    
 11

  距離と時間 と高速
       A    B    C      D      E    F     G     
 1
 2
 3
 4                       990    010     020      030    
 5       
 6  距離     990          0     50      40       30    
 7  時間                        50      40       30
 8  高速           2000   1000  500
 9       010         50     0      
 10                       50     0      
 11      020    
 12

 説明が下手ですが宜しくお願いします。

< 使用 Excel:unknown、使用 OS:unknown >


60枚のシートを統合したデータをもとに、マトリクス表を作りたいということですか?

(1)一つのシートにデータを集め、
(2)それを元に、ピボットテーブルを利用する、
というのはいかがでしょうか。

マクロで個別的なコードを書くよりも、
Excelに備わった機能をできるだけ活かすことを考えた方がよいと思います。
そのほうがメンテナビリティも向上するはずです。

(余談メモ:

  2003当時のピボットウイザードではデフォルトで複数シートが使えました。
  2010ではデフォルトではそれが表示されていませんが、マクロボタンそのものは残っていて、
  クイックアクセスツールバーに登録して使えますが、使い勝手がちょっと予想と違いました。
  マクロで一つのシートに統合したほうが使い易い感じです(あくまで私見))

(γ) 2016/02/02(火) 21:39


XからYへのデータに加えて、それを逆転させたデータも増幅すればよいと思います。
また、XからXへの距離を0表示することも、必須とも思えませんがいかがですか?

実現したいことを少し緩めることで、負荷が大きく軽減され、しかも効果にさほど差がない、
ということはよくあることですから、総合的に検討する必要があると思います。

(γ) 2016/02/03(水) 07:31


 余談へのコメントだが。

 Alt+D、Pで以前のピボットテーブル/ピボットグラフウィザードが表示される。
(ねむねむ) 2016/02/03(水) 09:11

バラバラなシートから探すより、1つのマトリクスにまとめられるなら、その方が数段使いやすいですね。

新しいシートを作成後、そのシートモジュールに以下のマクロを貼り付け、実行してみてください。
すると、全シートのデータを1シートにまとめます。

 Sub test()
    Dim i As Long
    Dim j As Long
    Dim iC As Long
    Dim iR As Long
    Dim iMax As Long

    Cells.ClearContents
    Range("A1").Resize(1, 5) = Array("発", "着", "距離", "時間", "高速")
    iR = 1

    For i = 1 To Sheets.Count
        If Sheets(i).Name <> Me.Name Then
            With Sheets(i)
                iMax = .Cells(.Rows.Count, "B").End(xlUp).Row
                For j = 0 To 4
                    iC = Array(2, 4, 6, 7, 8)(j)
                    .Range(.Cells(5, iC), .Cells(iMax, iC)).Copy Cells(iR + 1, j + 1)
                Next j
            End With
            iR = iR + iMax - 4
        End If
    Next i
 End Sub

次に、データを全選択後、重複の削除機能で、同じ値のものを無くしてください。
発着の関係が一意になった後、ピボットテーブルの作成を行ってみてください。ラベル指定は以下。

行ラベル:発
列ラベル:着
Σ値:時間、距離、高速
(列ラベルに「Σ値」が自動追加された場合、これを行ラベルにドラッグドロップ)
(???) 2016/02/03(水) 09:31


ねむねむさん、
> Alt+D、P
そのようですね。
ありがとうございました。

(???)さんからコードの提示がありましたので、
まずはひとつのシートにまとめると良いでしょうね。

ピボットテーブルは食わず嫌いのかたが結構おられるけれど、
是非活用して頂きたいですね。

(γ) 2016/02/03(水) 21:40


 (γ)さん(???)さん
 お世話になります。
 コードを入れてみました。

	列ラベル			
	990			010
行ラベル	データの個数 / 距離	データの個数 / 時間	データの個数 / 高速	データの個数 / 距離
010	1	1		
013	1	1		1
018	1	1		1
020	1	1		1
030	1	1	1	1
040	1	1	1	
050	1	1	1	

 こんな感じに出たのですが?何だかよく分かりません。1をダブルクリックすると
 発	着	距離	時間	高速
010	990	46.8	0:38:00	
 新しいシートに表示されます。
(素人) 2016/02/04(木) 00:28

「フィールドの設定」で「データの個数」ではなく、「合計」を選択してください。

http://www4.synapse.ne.jp/yone/excel2010/excel2010_pivot1.html
などを参考にして、ピボットテーブルの取扱方法を見てください。

なお、できあがりの表のレイアウト方式が色々ありますので、
「デザイン」の「レポートのレイアウト」のところを触って
研究してみると良いと思います。

ピボットテーブルは機能が色々ありますので、
それを分かりにくいとだけ評価せずに、
多機能で便利だね、と捉える方向で考えてみてください。

(γ) 2016/02/04(木) 07:11


データの個数表示を選択したために、例えば010-990は1個、という表になっちゃってますね。まぁ、ちゃんと一意になっているのは確認できた、ということで。

個数以外にすれば目的の情報になるとして、あとは私が最後に書いた、「列ラベルにΣ値」状態になっているかと思います。
これを行ラベルに移動させれば、3種のデータが縦に並びますよ。

あとは、不要な情報は表示しない、とか、ラベルの文字列を変える、とか、ピボットと格闘してみてください。
(???) 2016/02/04(木) 09:09


 (γ)さん(???)さん有難うございます。
 やって見たのですが何か考えているのと違う感じです。

 複数あるシートを一枚することが出来たので、ここからマトリクス表に
 		着		
	発	990	010	020
距離	990	0km	10km	20km
時間		0分	10分	20分
高速		0円	100円	200円
	010	10	0	30
		10	0	30
		100	0	300
	020	20	30	0
		20	30	0
		200	300	0
 こんな感じにしたいのです。
 それで、発着を入力してマトリクス表から距離、時間、高速を検索したい。					
	発	着	距離	時間	高速	
	990	010	10km	0時10分	100円	
 すみません説明が下手で 
(素人) 2016/02/04(木) 11:05

折角1つのマトリクス表にしたのに、検索する必要は無いのでは? 縦横の関係で、一目瞭然ですよね?
どうしても1行検索したいならば、今回1つにまとめたシートを対象にした方が良いでしょう。

例えば、G2とH2に発着入力し、I2以降は以下、とか。
=INDEX(C:C,SUMPRODUCT(($A:$A=$G2)*($B:$B=$H2)*ROW($A:$A)))
(???) 2016/02/04(木) 12:18


 (???)さん
 何度もすみません。

 まだ、マトリクス表になってないのです。
 複数あるシートを一枚にできたのでそこからマトリクス表にしたいのですが出来ないでいます。

 マトリクス表

 距離のみのシート D7:BG6に着をC8:C63に発をセットします。D8:BG63の中に一覧表のデータを入れたい

 距離と時間のシート D7:BG6に着をC8に発 D8に距離 D9に時間と入るようにしたい

 距離と時間と高速のシート D7:BG6に着をC8に発  D8に距離 D9に時間 D10に高速 と入るようにしたい

 検索シートに発着を入れ距離と時間と高速を検索したい

 こんな感じにしたいと考えています。申し訳ありません

(素人) 2016/02/04(木) 13:16


すみません質問を変えて登録し直します。申し訳ありません。
(素人) 2016/02/04(木) 19:11

それで結局ピボットテーブルを利用した、あなたのいうマトリックス表はできたのですね。
出来ない出来ないと言っていたので、
[[20160204191210]]でああした回答をしたのだけれど。

出来たのなら、それなりに報告をしてくださいよ。
ピボットテーブルを使ってマトリクス表は作れたのですね?

(γ) 2016/02/05(金) 21:49


コメント返信:

[ 一覧(最新更新順) ]


YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki. Modified by kazu.